Crate keelson_sqlite

Expand description

The SQLite dialect for keelson.

Four statement types, each shaped by the syntax diagrams at https://www.sqlite.org/lang.html, plus the mods that fill them in and the expression starters they are filled in with.

use keelson_sqlite as sqlite;
use keelson_sqlite::{Chain, Query, arg, quote, select};

let q = sqlite::select((
    select::columns((quote("id"), quote("name"))),
    select::from(quote("users")),
    select::where_(quote("age").gte(arg(21i32))),
));

let (sql, args) = q.build()?;
assert_eq!(sql, r#"SELECT "id", "name" FROM "users" WHERE ("age" >= ?1)"#);
assert_eq!(args, vec![keelson_core::Value::I32(21)]);

§Where this sits

Layer 1 of keelson, for SQLite. It is a complete way to use keelson on its own: it depends only on keelson-core and produces a SQL string and an argument list, which you may run with any driver you like. To run it through keelson, add Layer 2 (keelson-exec plus a backend such as keelson-sqlx); to have typed models built out of these mods, add Layers 3 and 4 (keelson-models, keelson-gen). Because SQLite needs no server, it is also the engine keelson’s own always-on end-to-end tests run against. The whole map is the keelson facade crate.

§How it is put together

The assembly rules are the ones keelson_psql documents, because they are keelson’s rather than any dialect’s: a starter is a function of one mod, and a tuple of mods is a mod; a mod module shares its name with its starter, so sqlite::select is both a function and a module; a mod is written once, generic over the keelson_core::clause Has* trait it needs; and a raw &str works wherever an expression does, so select::from("users") writes FROM users while select::from(quote("users")) writes FROM "users".

§Where SQLite is not PostgreSQL

This crate is hand-written against SQLite’s grammar rather than derived from the PostgreSQL one, and the differences are load-bearing. The five worth knowing before writing a query:

A compound operand takes no parentheses. SQLite’s compound-select-stmt is a run of bare select-cores, so (SELECT 1) UNION (SELECT 2) is a syntax error. Pass a query or query to select::union, never subquery.
There is one ORDER BY/LIMIT per statement, and in a compound it belongs to the whole compound. PostgreSQL’s order_by_combined family has nothing to correspond to here.
OFFSET is part of the LIMIT production, so an offset with no limit is a build-time Error::Incomplete.
UNION ALL is the only ALL. INTERSECT ALL and EXCEPT ALL do not exist, which is why CompoundOp folds ALL into the operator instead of carrying it as a flag.
VALUES (…), (…) is a statement. select::values and select::rows fill the other alternative of select-core.

Beyond those: ?1 and :name placeholders, INDEXED BY/NOT INDEXED on any table reference, INSERT OR REPLACE and UPDATE OR IGNORE, several ON CONFLICT clauses on one INSERT, RETURNING on all three mutations, a CROSS JOIN that takes an ON — and no locking clause, no FETCH, no TABLESAMPLE, no LATERAL, no GROUPING SETS, no DISTINCT ON, and no USING on a DELETE.

§Sub-queries

The four query types implement IntoExpr, so one goes straight into any expression slot: select::union(other), select::with("c", other), insert::query(other). Those slots want the query bare — SQLite parenthesises neither a WITH body’s contents twice nor a compound operand at all. Where the parentheses belong to the sub-query itself — a FROM item, a scalar sub-expression, an IN (…) operand — use subquery. Placeholders re-index across the nesting on their own, because the counter belongs to the writer.

Modules§

delete: Mods for sqlite::delete.
frame: Mods for a window frame — which rows around the current one a window function sees.
insert: Mods for sqlite::insert.
select: Mods for sqlite::select.
shared: The mods, written once against the Has* traits and re-exported per statement.
update: Mods for sqlite::update.
window: Mods for a window definition — what goes inside OVER (…) or after WINDOW name AS.

Structs§

CaseBuilder: A CASE expression under construction — bob’s CaseChain.
Compound: One operand of a compound SELECT: UNION ALL <select-core>.
Compounds: Every compound operand chained onto one SELECT.
DeleteQuery: A SQLite DELETE.
Function: A SQLite function call, with the decorations SQLite’s grammar hangs off one.
InsertQuery: A SQLite INSERT.
RawQuery: A whole statement, written by hand.
SelectQuery: A SQLite SELECT.
Sqlite: The SQLite dialect: ?1 placeholders, :name named arguments, " quoting.
UpdateQuery: A SQLite UPDATE.

Enums§

CompoundOp: A compound-operator — the four SQLite has, and no more.
Error: Everything that can go wrong while building a query.
Expr: A SQL expression, as data.
Or: The conflict-clause of an INSERT or UPDATE: INSERT OR REPLACE INTO …, UPDATE OR IGNORE ….
QueryType: Which statement a query renders.
RawArg: One replacement for a ? in an Expr::Template.
Value: A bound argument.

Traits§

Chain: The operator chain: quote("age").gte(arg(21)).
HasCompounds: A SELECT other SELECTs can be compounded onto.
HasExtraTables: A statement whose from-item list may hold more than one entry.
HasOr: A statement that takes a conflict-clause: an INSERT or an UPDATE.
HasTargetTable: A statement whose target table is separate from its from-item: the table an UPDATE writes to, or the one a DELETE removes from.
HasUpserts: An INSERT’s upsert-clause list.
IntoExpr: Anything that can stand where an expression is expected.
IntoExprList: A list of expressions: a tuple, an array, a Vec, () for none, or a single expression standing for a one-element list.
IntoIdent: The parts of a qualified identifier: "age", or ("users", "id").
Mod: Something that modifies a query in place.
Query: A complete, runnable statement.
SqliteOps: The operators SQLite has and the other two dialects do not.

Functions§

and: (a AND b AND c).
arg: One bound argument, rendered ?n.
arg_group: Several bound arguments, parenthesised: (?1, ?2, ?3).
args: Several bound arguments, comma-separated and not parenthesised — for a slot that brings its own, such as VALUES (…).
case_: A CASE expression: case_().when(cond, then).else_(other).
cast: CAST(expr AS type_name).
delete: Build a DELETE from one mod.
excluded: excluded."col" — the row that would have been inserted, inside ON CONFLICT … DO UPDATE.
f: A function call: f("count", "*"), f("row_number", ()).over(()).
group: A parenthesised, comma-separated list: (a, b). One element gives plain parentheses.
insert: Build an INSERT from one mod.
named: A named parameter: named("cutoff") renders :cutoff.
not: NOT expr. The operand is parenthesised if it needs it; the result is not, because NOT binds looser than anything it can contain.
or: (a OR b OR c).
placeholders: n unbound ?n placeholders, each binding NULL, so a statement can be prepared now and its values supplied by whatever rebinds it.
query: A query as an expression, not parenthesised.
quote: A quoted identifier: quote("age") gives "age", quote(("users", "id")) gives "users"."id".
raw: Raw SQL, verbatim. ? is left alone — see template.
raw_query: A whole statement, written by hand, as a runnable query.
s: A single-quoted string literal. s("A") renders 'A'.
select: Build a SELECT from one mod — usually a tuple of them.
subquery: A parenthesised sub-query: (SELECT …).
template: Raw SQL whose ? are rewritten to ?1, ?2, … with args interleaved. Write \? for a literal question mark.
update: Build an UPDATE from one mod.

Type Aliases§

Result: The result type used throughout keelson.