sql.q

A SQL SELECT as a q tree, slot for slot the shape q's own select (?) takes: (`sql;source;where;by;columns;limit;order). .sql.render writes a tree as SQL text; .sql.parse reads SQL text into a tree. source the table: `t, a dotted `db.s.t, `t`u for the table t under the alias u, or a (`sql;...) tree for a derived table (SELECT ... FROM (...); .sql.render only, .sql.parse reads none) where a list of the terms SQL ANDs together, () for none: one term is still a one-item list by 0b, 1b for SELECT DISTINCT, or a dict of the GROUP BY expressions columns a dict of name to expression, ()!() for SELECT *. An unaliased column's name is its expression's SQL text, so a name that differs from that text is the column's AS (and SELECT a AS a cannot be carried) limit :: for none, n for LIMIT n, m n for LIMIT n OFFSET m (n 0W when there is no LIMIT); SQL applies it after grouping, wherever q would order a list of (direction;expression) or (direction;expression;nulls). direction is `$"<:" for ASC, `$">:" for DESC, or ` when the query names neither; nulls is `$"nulls first" or `$"nulls last" An expression is a column `price (`t.price qualified), a parameter `$"$name" or `$"$1", a literal (a long, a float, a boolean, :: for NULL, an enlisted symbol for a string: 'x' is ,`x) or an application (head;arg;...). The heads: `= `<> `< `> `<= `>= `+ `- `* `/ `% `// `|| `like `not like `ilike `not ilike `glob `is distinct from and `is not distinct from take two arguments, `- also one; `and `or take any number, `not `is null `is not null one; (`between;x;lo;hi), (`in;x;item;...), (`cast;`TYPE;x) and `count`* for count(*). Any other head is a call of the function it names. Spell a head that is not a q name with `$: `$"<=", `$"is null". Two forms .sql.render alone writes: (`in;x;tree) for x IN (SELECT ...) and a last argument (`$"order by";e;...) for f(x ORDER BY e, ...).

Examples

.sql.render (`sql;`t;enlist (`$">";`price;100);0b;`sym`p!(`sym;`max`price);5;enlist (`$">:";`p))

.sql.parse "SELECT sym, max(price) AS p FROM t WHERE price > 100 GROUP BY sym ORDER BY p DESC LIMIT 5"

Entity Summary

Entities	Short Description
.sql.parse[sql]	The tree of one SELECT, read by DuckDB's own parser, so it needs DuckDB: a tool for building and checking trees.
.sql.render[tree]	The SQL text of a tree, written by q alone.
.sql.translate[tree;schema]	The where terms, limit and order of a select that DuckDB can run as SQL, the rest left to q: (sqlTree;params;schema; qTree) for a ?

Entity Details

.sql.parse[sql]

The tree of one SELECT, read by DuckDB's own parser, so it needs DuckDB: a tool for building and checking trees. What the tree cannot carry signals 'sql: unsupported, naming it (a join, a subquery, HAVING, a window, UNION ...); text DuckDB cannot parse signals 'sql: with DuckDB's reason.

Parameters:	sql - (string)

Examples

.sql.parse "SELECT a FROM t WHERE a > 1 AND b IS NULL"

.sql.render[tree]

The SQL text of a tree, written by q alone. A tree the grammar above cannot read signals 'type, a head with the wrong number of arguments 'length.

Return:	string

.sql.translate[tree;schema]

The where terms, limit and order of a select that DuckDB can run as SQL, the rest left to q: (sqlTree;params;schema; qTree) for a ? parse tree over a table whose schema is the envelope .duckdb.getx answers (col dtype logical iskey), the row index i among its rows on a table that stores one. sqlTree selects the columns qTree names (every column when its select phrase is empty; i never, unless the phrase names it bare, then as i AS its q output name) under the moved terms, params binds their literals as $pq1 $pq2 ... in the order the SQL text reads them, schema is the rows of those columns, and qTree is the tree minus what moved with the table name still at slot 1, :: when nothing is left for q. A select phrase of aggregates, or a by, whose every item .sql.i.whole can spell moves whole with the entire where: sqlTree groups, its schema rows carry q's result types with the by keys marked, and qTree is ::. Five stages, each reading the tree and what the stages before it decided: the select phrase (.sql.i.phrase), the where (.sql.i.where), the whole push of aggregates and by (.sql.i.whole), the limit and order (.sql.i.limit) and the assembly of the answer (.sql.i.assemble), which numbers the literals each stage left as placeholders (.sql.i.number).

Examples

.sql.translate[parse "select max price by sym from t where qty>100, f price";
([] col:`sym`qty`price; dtype:("VARCHAR";"BIGINT";"DOUBLE"); logical:`symbol`int64`float64; iskey:000b)]