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).
To use cql in this fashion, the sequence will be something like the below. See Appendix 1 for command line details.
cql --in input.sql --rt json_schema --cg out.json
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.
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" ]
}
],
It's sometimes useful to include some top level attributes about your system in the JSON. By convention all the attributes on any global variables
whose name ends in "database" (see example below) are emitted into the attributes section of the JSON. This lets you easily contribute to this section
from various schema fragments by providing "database" objects from each source. Any other globals are ignored.
NOTE: attributes are very flexible, even allowing nesting of arrays. Attribute values can either be any literal, or a name, or an array of values, recursively.
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
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.