Chapter 13: JSON Output

To help facilitate additional tools that might want to depend on CQL input files further down the toolchain CQL includes a JSON output format for SQL DDL as well as stored procedure information, including special information for a single-statement DML. "Single-statement DML" refers to those stored procedures that that consist of a single insert, select, update, or delete. Even though such procedures are just one statement, good argument binding can create very powerful DML fragments that are re-usable. Many CQL stored procedures are of this form (in practice maybe 95% are just one statement).

Below are some examples of the JSON output taken from a CQL test file. Note that the JSON has free text inserted into it as part of the test output, that obviously doesn't appear in the final output but it is especially illustrative here. This example illustrates almost all the possible JSON fragments.

{
"tables" : [

Each table appears fully formed in its own JSON hunk as below. isAdded and isDeleted correspond to the presence of an @create or @delete annotation respectively.

CREATE TABLE Foo(
id INTEGER NOT NULL,
name TEXT
)
{
"name" : "Foo",
"temp" : 0,
"ifNotExists" : 0,
"withoutRowid" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"columns" : [
{
"name" : "id",
"type" : "integer",
"isNotNull" : 1,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 0,
"isAutoIncrement" : 0
},
{
"name" : "name",
"type" : "text",
"isNotNull" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 0,
"isAutoIncrement" : 0
}
],
"primaryKey" : [ ],
"foreignKeys" : [
],
"uniqueKeys" : [
],
"indices" : [ "region_0_index", "MyIndex", "MyOtherIndex" ]
},

Here we introduce a primary key and its JSON.

CREATE TABLE T2(
id INTEGER PRIMARY KEY
)
{
"name" : "T2",
"temp" : 0,
"ifNotExists" : 0,
"withoutRowid" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"columns" : [
{
...
}
],
"primaryKey" : [ "id" ],
...
},

General purpose column information is also present. Again a fragment for brevity.

CREATE TABLE T3(
id INTEGER UNIQUE AUTOINCREMENT
)
{
"name" : "T3",
...
"columns" : [
{
"name" : "id",
"type" : "integer",
"isNotNull" : 1,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 1,
"isAutoIncrement" : 1
}
],
...
},

Columns and tables can have flexible attributes which are used downstream.

@ATTRIBUTE(foo=bar)
CREATE TABLE T4(
@ATTRIBUTE(cool)
id INTEGER
)
{
"name" : "T4",
...
"columns" : [
{
"name" : "id",
"attributes" : [
{
"name" : "cool",
"value" : 1
}
],
"type" : "integer",
...
}
],
...
"attributes" : [
{
"name" : "foo",
"value" : "bar"
}
]
},

Here's an example with revision marks

CREATE TABLE T8(
id INTEGER
) @CREATE(1) @DELETE(3)
{
"name" : "T8",
"temp" : 0,
"ifNotExists" : 0,
"withoutRowid" : 0,
"isAdded" : 1,
"isDeleted" : 1,
"columns" : [
{
"name" : "id",
"type" : "integer",
...
}
],
...
},

The usual constraints are also recorded. This example has a unqiue key on a column and foreign keys. Note that the unique key is reported the same as if it had been declared in a standalone fashion. There is a lot of stuff in this table...

CREATE TABLE T10(
id1 INTEGER UNIQUE,
id2 INTEGER,
id3 INTEGER,
id4 INTEGER UNIQUE,
PRIMARY KEY (id1, id2),
FOREIGN KEY (id1, id2) REFERENCES T9 (id2, id1),
CONSTRAINT uk1 UNIQUE (id2, id3),
CONSTRAINT uk2 UNIQUE (id3, id4)
)
{
"name" : "T10",
...
"columns" : [
{
"name" : "id1",
"type" : "integer",
"isNotNull" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 1,
"isAutoIncrement" : 0
},
{
"name" : "id2",
"type" : "integer",
"isNotNull" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 0,
"isAutoIncrement" : 0
},
{
"name" : "id3",
"type" : "integer",
"isNotNull" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 0,
"isAutoIncrement" : 0
},
{
"name" : "id4",
"type" : "integer",
"isNotNull" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 1,
"isAutoIncrement" : 0
}
],
"primaryKey" : [ "id1", "id2" ],
"foreignKeys" : [
{
"columns" : [ "id1", "id2" ],
"referenceTable" : "T9",
"referenceColumns" : [ "id2", "id1" ],
"onUpdate" : "NO ACTION",
"onDelete" : "NO ACTION",
"isDeferred" : 0
}
],
"uniqueKeys" : [
{
"name" : "id1_uk",
"columns" : [ "id1" ]
},
{
"name" : "id4_uk",
"columns" : [ "id4" ]
},
{
"name" : "uk1",
"columns" : [ "id2", "id3" ]
},
{
"name" : "uk2",
"columns" : [ "id3", "id4" ]
}
]
},

Foreign keys can include the full set of actions. Here are a couple of examples:

CREATE TABLE T11(
id1 INTEGER,
id2 INTEGER,
id3 INTEGER,
FOREIGN KEY (id1) REFERENCES T9 (id1) ON DELETE CASCADE,
FOREIGN KEY (id1) REFERENCES T9 (id1) ON UPDATE SET NULL
)
{
"name" : "T11",
...
"columns" : [
{
"name" : "id1",
"type" : "integer",
...
},
{
"name" : "id2",
"type" : "integer",
...
},
{
"name" : "id3",
"type" : "integer",
...
}
],
"primaryKey" : [ ],
"foreignKeys" : [
{
"columns" : [ "id1" ],
"referenceTable" : "T9",
"referenceColumns" : [ "id1" ],
"onUpdate" : "NO ACTION",
"onDelete" : "CASCADE",
"isDeferred" : 0
},
{
"columns" : [ "id1" ],
"referenceTable" : "T9",
"referenceColumns" : [ "id1" ],
"onUpdate" : "SET NULL",
"onDelete" : "NO ACTION",
"isDeferred" : 0
}
],
...
},

Deferred FK actions can also be specified. Note: per the SQLite documentation, the norm is immediate on everything except deferrable initially deferred.

CREATE TABLE T12(
id1 INTEGER,
id2 INTEGER,
id3 INTEGER,
FOREIGN KEY (id1) REFERENCES T9 (id1) ON DELETE SET DEFAULT
DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (id2) REFERENCES T9 (id1) ON UPDATE NO ACTION
)
{
"name" : "T12",
...
"columns" : [
{
"name" : "id1",
...
},
{
"name" : "id2",
...
},
{
"name" : "id3",
...
}
],
...
"foreignKeys" : [
{
"columns" : [ "id1" ],
"referenceTable" : "T9",
"referenceColumns" : [ "id1" ],
"onUpdate" : "NO ACTION",
"onDelete" : "SET DEFAULT",
"isDeferred" : 1
},
{
"columns" : [ "id2" ],
"referenceTable" : "T9",
"referenceColumns" : [ "id1" ],
"onUpdate" : "NO ACTION",
"onDelete" : "NO ACTION",
"isDeferred" : 0
}
],
...
},

Just like unique keys, foreign keys on the columns are moved down as though they had been independently declared. There are 3 foreign keys below.

CREATE TABLE with_fk_on_columns(
id1 INTEGER NOT NULL REFERENCES T2 (id) ON UPDATE CASCADE
DEFERRABLE INITIALLY DEFERRED,
id2 INTEGER NOT NULL REFERENCES T10 (id4) ON DELETE CASCADE,
FOREIGN KEY (id1, id2) REFERENCES T10 (id3, id4)
)
{
"name" : "with_fk_on_columns",
"temp" : 0,
"ifNotExists" : 0,
"withoutRowid" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"columns" : [
{
"name" : "id1",
"type" : "integer",
"isNotNull" : 1,
...
},
{
"name" : "id2",
"type" : "integer",
"isNotNull" : 1,
...
}
],
"primaryKey" : [ ],
"foreignKeys" : [
{
"columns" : [ "id1" ],
"referenceTable" : "T2",
"referenceColumns" : [ "id" ],
"onUpdate" : "CASCADE",
"onDelete" : "NO ACTION",
"isDeferred" : 1
},
{
"columns" : [ "id2" ],
"referenceTable" : "T10",
"referenceColumns" : [ "id4" ],
"onUpdate" : "NO ACTION",
"onDelete" : "CASCADE",
"isDeferred" : 0
},
{
"columns" : [ "id1", "id2" ],
"referenceTable" : "T10",
"referenceColumns" : [ "id3", "id4" ],
"onUpdate" : "NO ACTION",
"onDelete" : "NO ACTION",
"isDeferred" : 0
}
],
"uniqueKeys" : [
]
}

Columns can be marked with @sensitive for privacy reasons. This declaration flows to the column description as isSensitive. For economy, isSenstive is only emitted when true.

CREATE TABLE radioactive(
id INTEGER NOT NULL,
danger TEXT @SENSITIVE
)
{
"name" : "radioactive",
"isTemp" : 0,
"ifNotExists" : 0,
"withoutRowid" : 0,
"isAdded" : 0,
"isDeleted" : 0,
"isRecreated": 0,
"columns" : [
{
"name" : "id",
"type" : "integer",
"isNotNull" : 1,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 0,
"isAutoIncrement" : 0
},
{
"name" : "danger",
"type" : "text",
"isNotNull" : 0,
"isSensitive" : 1,
"isAdded" : 0,
"isDeleted" : 0,
"isPrimaryKey" : 0,
"isUniqueKey" : 0,
"isAutoIncrement" : 0
}
],
"primaryKey" : [ ],
"foreignKeys" : [
],
"uniqueKeys" : [
]
}
],

The next major section is the views. Each view includes its projection (that is the net columns it creates from the select clause) and its general statement information. One example tells the story pretty clearly. Views don't have arguments in any supported cases but the arguments are included for symmetry with the other forms. Note: projection columns can be sensitive and will be so-marked if they are.

"views" : [
CREATE VIEW MyView AS
SELECT *
FROM Foo
{
"name" : "MyView",
"temp" : 0,
"projection" : [
{
"name" : "id",
"type" : "integer",
"isNotNull" : 1
},
{
"name" : "name",
"type" : "text",
"isNotNull" : 0
}
],
"select" : "SELECT id, name FROM Foo",
"selectArgs" : [ ]
}
],

Likewise indices contain the table and indexed columns. This one example illustrates things fairly clearly.

"indices" : [
CREATE UNIQUE INDEX IF NOT EXISTS MyIndex ON Foo (name DESC, id ASC)
{
"name" : "MyIndex",
"table" : "Foo",
"isUnique" : 1,
"ifNotExists" : 1,
"columns" : [ "name", "id" ],
"sortOrders" : [ "desc", "asc" ]
}
],

The top level attributes go, by convention, on a the global variable named database of type object. These attributes move into the JSON. Other globals are ignored.

NOTE: attributes are very flexible, allowing nesting of arrays. Attributes values can either be any literal, or a name, or an array of values, recursively.

@ATTRIBUTE(my_other_attribute=('any', ('tree', 'of'), 'values'))
@ATTRIBUTE(dbname='fred.sql')
@ATTRIBUTE(dbfile='cg_test_mlite_query.sql')
DECLARE database OBJECT
"attributes" : [
{
"name" : "my_other_attribute",
"value" : ["any", ["tree", "of"], "values"]
},
{
"name" : "dbname",
"value" : "fred.sql"
},
{
"name" : "dbfile",
"value" : "cg_test_mlite_query.sql"
}
],

The queries section corresponds to the stored procedures with a SELECT statement. There is significant data provided about each one.

  • the name of the procedure
  • the number and type of arguments
  • the set of tables used anywhere in the query (for dependencies)
    • this includes tables used within views that were used
  • the query projection (aka the result shape of the select)
  • the full SQL statement and the arguments that should be bound to each ?

There are two examples below:

"queries" : [
CREATE PROC a_query (pattern TEXT NOT NULL, reject TEXT)
BEGIN
SELECT id
FROM Foo
WHERE name LIKE pattern AND name <> reject;
END
{
"name" : "a_query",
"args" : [
{
"name" : "pattern",
"type" : "text",
"isNotNull" : 1
},
{
"name" : "reject",
"type" : "text",
"isNotNull" : 0
}
],
"usesTables" : [ "Foo" ],
"projection" : [
{
"name" : "id",
"type" : "integer",
"isNotNull" : 1
}
],
"statement" : "SELECT id FROM Foo WHERE name LIKE ? AND name <> ?",
"statementArgs" : [ "pattern", "reject" ],
},
CREATE PROC bigger_query (pattern TEXT NOT NULL, reject TEXT)
BEGIN
SELECT DISTINCT *
FROM Foo
WHERE name LIKE pattern AND name <> reject
GROUP BY name
HAVING name > reject
ORDER BY pattern
LIMIT 1
OFFSET 3;
END
{
"name" : "bigger_query",
"args" : [
{
"name" : "pattern",
"type" : "text",
"isNotNull" : 1
},
{
"name" : "reject",
"type" : "text",
"isNotNull" : 0
}
],
"usesTables" : [ "Foo" ],
"projection" : [
{
"name" : "id",
"type" : "integer",
"isNotNull" : 1
},
{
"name" : "name",
"type" : "text",
"isNotNull" : 0
}
],
"statement" : "SELECT DISTINCT id, name FROM Foo WHERE name LIKE ? AND
name <> ? GROUP BY name HAVING name > ? ORDER BY ?
LIMIT 1 OFFSET 3",
"statementArgs" : [ "pattern", "reject", "reject", "pattern" ],
},

The section on insert statements is very similar in shape. Again the fields are:

  • the name of the procedure
  • the arguments and argument types
  • the tables used by the insert statement (usually just the one but value expressions can be select statements so it can be more)
  • the table we are inserting into (certainly present in usesTables)
  • the overall statement and its arguments (easiest form to use)
  • the statement type (e.g. INSERT or INSERT OR REPLACE)
  • the inserted columns
    • present even if the insert into table values (...) form was used
  • the array of value expressions and arguments, one for each value

Again, simple insert forms are readily recognized and complex forms are supported.

"inserts" : [
The statement ending at line 277
CREATE PROC insert_proc (id_ INTEGER NOT NULL, name_ TEXT)
BEGIN
INSERT OR REPLACE INTO Foo (id, name) VALUES (id_, name_);
END
{
"name" : "insert_proc",
"args" : [
{
"name" : "id_",
"type" : "integer",
"isNotNull" : 1
},
{
"name" : "name_",
"type" : "text",
"isNotNull" : 0
}
],
"usesTables" : [ "Foo" ],
"table" : "Foo",
"statement" : "INSERT OR REPLACE INTO Foo (id, name) VALUES (?, ?)",
"statementArgs" : [ "id_", "name_" ],
"statementType" : "INSERT OR REPLACE",
"columns" : [ "id", "name" ],
"values" : [
{
"value" : "?",
"valueArgs" : [ "id_" ]
},
{
"value" : "?",
"valueArgs" : [ "name_" ]
}
]
},

As another example, this fairly easy to write CQL transparently creates dummy values. Great for use in testing. The JSON shows the net insert created from the original source below.

CREATE PROC dummy_insert_proc (seed_ INTEGER NOT NULL)
BEGIN
INSERT INTO Foo () VALUES () @DUMMY_SEED(seed_) @DUMMY_NULLABLES;
END
{
"name" : "dummy_insert_proc",
"args" : [
{
"name" : "seed_",
"type" : "integer",
"isNotNull" : 1
}
],
"usesTables" : [ "Foo" ],
"table" : "Foo",
"statement" : "INSERT INTO Foo (id, name)
VALUES (?, printf('name_%d', ?))",
"statementArgs" : [ "_seed_", "_seed_" ],
"statementType" : "INSERT",
"columns" : [ "id", "name" ],
"values" : [
{
"value" : "?",
"valueArgs" : [ "_seed_" ]
},
{
"value" : "printf('name_%d', ?)",
"valueArgs" : [ "_seed_" ]
}
]
}
],

The above form can capture the simplest of the insert statements allowed in SQLite. This is especially interesting because the JSON above can cleanly capture each value and the only place where there might be references to the procedure arguments is in the valueArgs portion. There is simply no room for any other kind of variability. As a result, it's actually possible to take this type of insert and potentially re-codegen it into an upsert or something else starting from the JSON. This is isn't in general possible with the other forms of insert. More compilicated forms of insert go into a section called "generalInesrts" this includes any other single insert statement such as these forms:

  • insert from multiple value rows
  • insert from a select statement
  • insert using a WITH clause
  • insert using the upsert clause

The "generalInserts" section looks exactly like the "inserts" section except that it does not include "values".

Here's an example:

"generalInserts" : [
CREATE PROC insert_compound ()
BEGIN
INSERT INTO T3(id) VALUES(1)
UNION ALL
SELECT 1 AS column1;
END
{
"name" : "insert_compound",
"definedInFile" : "cg_test_json_schema.sql",
"args" : [
],
"insertTables" : [ "T3" ],
"usesTables" : [ "T3" ],
"table" : "T3",
"statement" : "INSERT INTO T3(id) VALUES(1) UNION ALL SELECT 1 AS column1",
"statementArgs" : [ ],
"statementType" : "INSERT",
"columns" : [ "id" ]
}
...
],

Update statements are handled very much like the others, but there are no statement fragments. You get these pieces:

  • the name of the procedure and its arguments
  • dependency information
  • the statement text and its arguments

This is the minimum information needed to bind and run the statement. Note that arguments can be in any part of the update.

"updates" : [
The statement ending at line 306
CREATE PROC update_proc (id_ INTEGER NOT NULL, name_ TEXT)
BEGIN
UPDATE Foo
SET name = name_
WHERE id = id_
ORDER BY name
LIMIT 1;
END
{
"name" : "update_proc",
"args" : [
{
"name" : "id_",
"type" : "integer",
"isNotNull" : 1
},
{
"name" : "name_",
"type" : "text",
"isNotNull" : 0
}
],
"usesTables" : [ "Foo" ],
"table" : "Foo",
"statement" : "UPDATE Foo SET name = ?
WHERE id = ? ORDER BY name LIMIT 1",
"statementArgs" : [ "name_", "id_" ]
}
],

The delete section looks exactly like the update section.

  • procedure name and arguments
  • dependency information
  • statement and arguments
"deletes" : [
The statement ending at line 297
CREATE PROC delete_proc (name_ TEXT)
BEGIN
DELETE FROM Foo WHERE name LIKE name_;
END
{
"name" : "delete_proc",
"args" : [
{
"name" : "name_",
"type" : "text",
"isNotNull" : 0
}
],
"usesTables" : [ "Foo" ],
"table" : "Foo",
"statement" : "DELETE FROM Foo WHERE name LIKE ?",
"statementArgs" : [ "name_" ]
}
],

And finally the section for procedures that were encountered that are not one of the simple prepared statement forms. The principle reasons for being in this category are:

  • the procedure has out arguments
  • the procedure uses something other than a single DML statement
  • the procedure has no projection (no result of any type)
"general" : [
CREATE PROC with_complex_args (OUT pattern TEXT NOT NULL, INOUT arg REAL)
BEGIN
SELECT 1 AS a;
END
{
"name" : "with_complex_args",
"args" : [
{
"name" : "pattern",
"type" : "text",
"isNotNull" : 1,
"binding" : "out"
},
{
"name" : "arg",
"type" : "real",
"isNotNull" : 0,
"binding" : "inout"
}
],
"usesTables" : [ ],
"hasSelectResult" : 1,
"projection" : [
{
"name" : "a",
"type" : "integer",
"isNotNull" : 1
}
],
"usesDatabase" : 1
},
CREATE PROC atypical_noreturn ()
BEGIN
DECLARE C CURSOR LIKE SELECT 1 AS A;
END
{
"name" : "atypical_noreturn",
"args" : [
],
"usesTables" : [ ],
"usesDatabase" : 0
},
CREATE PROC typical_outresult ()
BEGIN
DECLARE C CURSOR LIKE SELECT 1 AS A;
FETCH C (A) FROM VALUES (7);
OUT C;
END
{
"name" : "typical_outresult",
"args" : [
],
"usesTables" : [ ],
"hasOutResult" : 1,
"projection" : [
{
"name" : "A",
"type" : "integer",
"isNotNull" : 1
}
],
"usesDatabase" : 0
},

Some additional properties not mentioned above that are worth noting:

  • where usesTables appears there will also be more detailed information about how the tables were used
    • the usesViews key will give you an array of the views that were used (these lead to more views/tables also included)
    • the insertTables key will give you an array of the tables that were used as the target of an insert statement
    • the updateTables key will give you an array of the tables that were used as the target of an update statement
    • the deleteTables key will give you an array of the tables that were used as the target of an delete statement
    • the fromTables key will give you an array of tables that were used the the from clause of a select or some other select-ish context in which you only read from the table
  • the usesProcedures key for a given proc has an array of the procedures it calls, this allows for complete dependency analysis if needed

To use cql in this fashion:

cql --in input.sql --rt json_schema --cg out.json

NOTE: @ATTRIBUTE can be applied any number of times to the entities here, including the procedures (i.e. immediately before the CREATE PROCEDURE) . Those attributes appear in the JSON in an optional attributes chunk. Attributes are quite flexible (you can easily encode a lisp program in attributes if you were so inclined) so you can use them very effectively to annotate your CQL entities as needed for downstream tools.

Last updated on by Rico Mariani