gizmodel/sql/tree
A stored table that follows a record type. The @was that a field rename records (giz edit), and by which live state migrates, renames the table's column, so a generation that renamed a field reads the rows the old one wrote. New fields become columns; a column no field names is kept, and its data with it. Typed queries and commands lower only to the existing Select tree and source cursor. Their value contracts are in gizmodel/typed.
import * as Tree from "gizmodel/sql/tree";Types
Prepared
type Prepared<P, R> = { tree: Select; parameters: Vec<Slot>; outputs: Vec<Slot>; encode: (input: P) => Vec<Value> throws DbError effects {fail, alloc}; decode: (row: Vec<Value>) => R throws DbError effects {fail, alloc}; sql: string; }An immutable definition. The binder checks outputs before any result decoder runs.
Opaque: create and use it through this module's functions.
Expression
type Expression<T> = { tree: Expr; codec: Codec<T>; }A typed SQL expression: its tree and the codec of its value.
Opaque: create and use it through this module's functions.
KeyField
type KeyField = { tree: Expr; slot: Slot; descending: bool; }One key of keyset paging: its expression, slot and direction.
tree:M151.Exprslot:M142.Slotdescending:bool
Page
type Page<R> = { rows: Vec<R>; next: Option<Vec<Value>>; }A page of rows and the key values after its last row, or none when it is the last page.
Opaque: create and use it through this module's functions.
Relation
type Relation<P, C, K> = { children: Prepared<bool, C>; foreignKey: Expression<K>; parentKey: Expression<K>; key: (parent: P) => K effects {fail, alloc}; }How children belong to parents: the child query, the child and parent keys, and the key of a parent record.
Opaque: create and use it through this module's functions.
Included
type Included<P, C> = { parent: P; children: Vec<C>; }A parent and its children.
Opaque: create and use it through this module's functions.
Functions
require
function require(command: Statement, rows: i64): Statement throws DbErrorRequire exactly rows affected by a data mutation. The DAL rolls back the whole batch or transaction on a mismatch (ErrorKind.conflict). Store a revision column. Use UPDATE records SET revision=revision+1, value=$1 WHERE key=$2 AND revision=$3, then require(statement,1). The expected revision is an integer parameter, never a float. The require node wraps the SQL statement; it is not SQL engine syntax.
Effects: fail, alloc.
requiredRows
function requiredRows(command: Statement): Option<i64> throws DbErrorDecode the one DAL affected-row contract before the engine receives SQL.
Effects: fail, alloc.
rowPatchCommands
function rowPatchCommands(table: string, patches: Vec<RowPatch>): Vec<Statement> throws DbErrorOne UPDATE of TABLE for each patch, keyed by the key fields of the patch.
Effects: fail, alloc.
field
function field<T>(table: string, name: string, codec: Codec<T>): Expression<T>The column NAME of TABLE, typed by CODEC.
parameter
function parameter<T>(index: i64, codec: Codec<T>): Expression<T>The parameter at INDEX (from 0), typed by CODEC.
constant
function constant<T>(codec: Codec<T>, value: T): Expression<T> throws DbErrorThe constant VALUE, encoded by CODEC.
equalTo
function equalTo<T>(a: Expression<T>, b: Expression<T>): Expression<bool>A = B.
andAlso
function andAlso(a: Expression<bool>, b: Expression<bool>): Expression<bool>A AND B.
Effects: fail, alloc.
table
function table(name: string): SelectSELECT from the table NAME, with no columns yet.
Effects: fail, alloc.
where
function where(tree: Select, predicate: Expression<bool>): SelectTREE with PREDICATE added to its WHERE (AND).
Effects: fail, alloc.
projection
function projection<T>(tree: Select, name: string, value: Expression<T>): SelectTREE with VALUE added to its columns as NAME.
statements
function statements(text: string): Vec<Statement> throws DbErrorThe statements of a SQL text (a DDL script, an admin upsert), or a syntax error.
Effects: fail, read, alloc.
exists
function exists(tree: Select): Expression<bool>EXISTS (TREE): whether the query selects a row. not negates it.
Effects: fail, alloc.
scalar
function scalar<T>(tree: Select, codec: Codec<T>): Expression<T>(TREE) as a value: the one column of the one row that the query selects, as CODEC reads it.
rows
function rows(tree: Select, outputs: Vec<Slot>): Prepared<bool, Vec<Value>>The query TREE, whose values are in its tree, as a typed read of rows of the slots OUTPUTS (fetch it with true).
Effects: fail, alloc.
sql
function sql<P, R>(text: string, parameters: Vec<Slot>, outputs: Vec<Slot>, encode: (input: P) => Vec<Value> throws DbError effects {fail, alloc}, decode: (row: Vec<Value>) => R throws DbError effects {fail, alloc}): Prepared<P, R> throws DbErrorThe typed query of the SELECT TEXT with its PARAMETERS and OUTPUTS and the two conversions. It fails unless the text is one SELECT.
insert
function insert(table: string, names: Vec<string>, rows: Vec<Vec<Value>>): Statement throws DbErrorINSERT of ROWS into the columns NAMES of TABLE.
Effects: fail, alloc.
update
function update(table: string, patch: Patch, predicate: Expression<bool>): Statement throws DbErrorUPDATE of TABLE that sets the fields of PATCH where PREDICATE holds.
Effects: fail, alloc.
remove
function remove(table: string, predicate: Expression<bool>): StatementDELETE from TABLE where PREDICATE holds.
Effects: fail, alloc.
upsert
function upsert(table: string, names: Vec<string>, rows: Vec<Vec<Value>>, keys: Vec<string>, patch: Patch): Statement throws DbErrorINSERT of ROWS into TABLE that, on a conflict on KEYS, sets the fields of PATCH (an empty one: DO NOTHING).
Effects: fail, alloc.
lessThan
function lessThan<T>(a: Expression<T>, b: Expression<T>): Expression<bool>A < B.
atMost
function atMost<T>(a: Expression<T>, b: Expression<T>): Expression<bool>A <= B.
greaterThan
function greaterThan<T>(a: Expression<T>, b: Expression<T>): Expression<bool>A > B.
atLeast
function atLeast<T>(a: Expression<T>, b: Expression<T>): Expression<bool>A >= B.
notEqual
function notEqual<T>(a: Expression<T>, b: Expression<T>): Expression<bool>A <> B.
equalValue
function equalValue<T>(a: Expression<T>, v: Value): Expression<bool>A = V, where a NULL V asks A IS NULL: a condition on a column that its name and a value give (Find.column).
isNull
function isNull<T>(value: Expression<T>): Expression<bool>VALUE IS NULL.
isNotNull
function isNotNull<T>(value: Expression<T>): Expression<bool>VALUE IS NOT NULL.
inList
function inList<T>(value: Expression<T>, parameters: Vec<Expression<T>>): Expression<bool>List entries are typed parameter expressions. An empty list is always false.
like
function like<T>(value: Expression<T>, pattern: string, caseless: bool = false): Expression<bool>VALUE LIKE PATTERN (ILIKE when CASELESS), with \ as its escape.
prefix
function prefix<T>(value: Expression<T>, text: string): Expression<bool>VALUE starts with TEXT.
contains
function contains<T>(value: Expression<T>, text: string): Expression<bool>VALUE holds TEXT.
value
function value(v: Value): Expression<Value>The constant V, typed by its own type.
Effects: fail, alloc.
lower
function lower<T>(value: Expression<T>): Expression<T>LOWER(VALUE): a text in lower case (ASCII letters in SQLite).
matches
function matches<T>(values: Vec<Expression<T>>, words: string, typeahead: bool = false): Expression<bool>The text columns VALUES, each with a full-text index (Model.Search), hold WORDS (GizDB's BM25 match). Several columns score as one (BM25F: each index's weight times its column's score). TYPEAHEAD also takes the last word as a prefix, typos, Hangul initials and the other keyboard layout: what a search box finds as people type.
score
function score(): Expression<f64>The BM25 score of the row for the matches of its query: order by it, descending, for the best first.
Effects: fail, alloc.
orElse
function orElse(a: Expression<bool>, b: Expression<bool>): Expression<bool>A OR B.
Effects: fail, alloc.
not
function not(value: Expression<bool>): Expression<bool>NOT VALUE.
Effects: fail, alloc.
orderBy
function orderBy<T>(tree: Select, value: Expression<T>, descending: bool, nullsFirst: bool): SelectTREE ordered by VALUE after its earlier keys, in the given direction and null order.
limit
function limit(tree: Select, n: i64, skip: i64 = 0): Select throws DbErrorTREE with LIMIT N and OFFSET SKIP. It fails when either is negative.
Effects: fail, alloc.
innerJoin
function innerJoin<L, R>(left: Expression<L>, right: Expression<R>): Select throws DbErrorThe inner join of the tables of LEFT and RIGHT on LEFT = RIGHT.
leftJoin
function leftJoin<L, R>(left: Expression<L>, right: Expression<R>): Select throws DbErrorProject right-hand fields with optionalCodec: unmatched children are NULL.
count
function count<T>(value: Expression<T>): Expression<i64>COUNT(VALUE): the rows where VALUE is not NULL.
countAll
function countAll(): Expression<i64>COUNT(*): the rows.
Effects: fail, alloc.
sum
function sum<T, I>(value: Expression<I>, codec: Codec<T>): Expression<Option<T>> throws DbErrorThe output codec excludes NULL; the aggregate adds Option for empty groups.
min
function min<T, I>(value: Expression<I>, codec: Codec<T>): Expression<Option<T>> throws DbErrorMIN(VALUE), read by CODEC; none on no rows.
max
function max<T, I>(value: Expression<I>, codec: Codec<T>): Expression<Option<T>> throws DbErrorMAX(VALUE), read by CODEC; none on no rows.
avg
function avg<T>(value: Expression<T>): Expression<Option<f64>> throws DbErrorAVG of an integer is an exact numeric in the PostgreSQL dialect, so the argument is cast to float and the average is a float in every dialect. Decimal averages need an exact engine type.
groupBy
function groupBy<T>(tree: Select, value: Expression<T>): SelectTREE grouped by VALUE after its earlier keys.
having
function having(tree: Select, predicate: Expression<bool>): SelectTREE with PREDICATE added to its HAVING (AND).
Effects: fail, alloc.
keyField
function keyField<T>(value: Expression<T>, descending: bool): KeyFieldVALUE as a key of keyset paging, in the given direction.
page
function page<P, R>(source: Source, query: Prepared<P, R>, input: P, keyFields: Vec<KeyField>, after: Option<Vec<Value>>, size: i64): Page<R> throws DbErrorHidden cursor projections permit keys that are absent from the public result.
include
function include<P, C, K>(source: Source, parents: Vec<P>, relation: Relation<P, C, K>): Vec<Included<P, C>> throws DbErrorOne IN query per parent chunk. Group once, then emit in parent order. The child query declares its table and projection and has no parameters.
defineCommands
function defineCommands(table: Schema.Table): Vec<Statement> throws DbErrorThe native commands that make a checked table when it is not there: CREATE TABLE IF NOT EXISTS with the key, unique, foreign key and check constraints, then CREATE INDEX IF NOT EXISTS for each index. No SQL text is parsed.
Effects: fail, alloc.
lambdaExpr
function lambdaExpr(node: Lambda.Node, names: Vec<string>, columns: Vec<string>, slots: Vec<Lambda.Constant>): Expr throws DbErrorThe SQL expression of a lambda's tree (gizlang/expr) over a record whose field NAMES[I] is the column COLUMNS[I], its slots read from SLOTS. The tree's operators, casts and known calls each have one SQL form; one the engine does not take fails there, as written SQL would.
Effects: fail, alloc.
aggregated
function aggregated(node: Lambda.Node): boolWhether NODE computes an aggregate of a group (gizlang/expr count, sum, avg, and min or max of one value).
Effects: fail.
Methods
Prepared.values
function Prepared.values<P, R>(self: Prepared<P, R>, input: P): Vec<Value> throws DbErrorThe values of the parameters of the query, encoded from INPUT.
Prepared.records
function Prepared.records<P, R>(self: Prepared<P, R>, batch: Batch): Vec<R> throws DbErrorThe rows of BATCH, decoded into records. Each value is read once, checked and decoded: a check of the whole batch first read every value twice, 3% of a comments page's CPU.
Prepared.fetch
function Prepared.fetch<P, R>(self: Prepared<P, R>, source: Source, input: P): Vec<R> throws DbErrorCollect through the existing source cursor. Close it on decode or poll failure.
Prepared.fetchBounded
function Prepared.fetchBounded<P, R>(self: Prepared<P, R>, source: Source, input: P, maxRows: i64): Vec<R> throws DbErrorCollect typed rows with a strict maximum: one row more is a limit failure, never a cut answer.
Prepared.stream
function Prepared.stream<P, R>(self: Prepared<P, R>, source: Source, input: P, each: (batch: Batch) => bool throws DbError): i64 throws DbErrorRead the answer a batch of typed columns at a time, through the source's one cursor: EACH takes a batch and says whether to go on. The cursor closes when EACH stops or fails and when the answer ends, so no cursor outlives the call; how many rows EACH took. Nothing is kept here: the memory of a read is one batch.
Prepared.fetchAcross
function Prepared.fetchAcross<P, R>(self: Prepared<P, R>, sources: Vec<Source>, input: P, maxRows: i64): Vec<R> throws DbErrorBounded read-only fan-out of one typed query across compatible sources. Results are concatenated in source order, not globally sorted, joined or aggregated. Each source reads independently; there is no global snapshot or ACID transaction.
Source.query
function Source.query<P, R>(self: Source, q: Prepared<P, R>, input: P, rows: i64, bytes: i64): Cursor throws DbErrorThe rows of query from the source as a cursor of at most rows rows and bytes bytes a page; its columns must be the query's outputs.
Constants
INCLUDE_CHUNK
const INCLUDE_CHUNK: i64 = 1000The most parents of one IN query of include.