GizStack
View .mdOpen in ChatGPTOpen in Claude

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.

gizlang
import * as Tree from "gizmodel/sql/tree";

Types

Prepared

gizlang
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

gizlang
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

gizlang
type KeyField = { tree: Expr; slot: Slot; descending: bool; }

One key of keyset paging: its expression, slot and direction.

  • tree: M151.Expr
  • slot: M142.Slot
  • descending: bool

Page

gizlang
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

gizlang
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

gizlang
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

gizlang
function require(command: Statement, rows: i64): Statement throws DbError

Require 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

gizlang
function requiredRows(command: Statement): Option<i64> throws DbError

Decode the one DAL affected-row contract before the engine receives SQL.

Effects: fail, alloc.

rowPatchCommands

gizlang
function rowPatchCommands(table: string, patches: Vec<RowPatch>): Vec<Statement> throws DbError

One UPDATE of TABLE for each patch, keyed by the key fields of the patch.

Effects: fail, alloc.

field

gizlang
function field<T>(table: string, name: string, codec: Codec<T>): Expression<T>

The column NAME of TABLE, typed by CODEC.

parameter

gizlang
function parameter<T>(index: i64, codec: Codec<T>): Expression<T>

The parameter at INDEX (from 0), typed by CODEC.

constant

gizlang
function constant<T>(codec: Codec<T>, value: T): Expression<T> throws DbError

The constant VALUE, encoded by CODEC.

equalTo

gizlang
function equalTo<T>(a: Expression<T>, b: Expression<T>): Expression<bool>

A = B.

andAlso

gizlang
function andAlso(a: Expression<bool>, b: Expression<bool>): Expression<bool>

A AND B.

Effects: fail, alloc.

table

gizlang
function table(name: string): Select

SELECT from the table NAME, with no columns yet.

Effects: fail, alloc.

where

gizlang
function where(tree: Select, predicate: Expression<bool>): Select

TREE with PREDICATE added to its WHERE (AND).

Effects: fail, alloc.

projection

gizlang
function projection<T>(tree: Select, name: string, value: Expression<T>): Select

TREE with VALUE added to its columns as NAME.

statements

gizlang
function statements(text: string): Vec<Statement> throws DbError

The statements of a SQL text (a DDL script, an admin upsert), or a syntax error.

Effects: fail, read, alloc.

exists

gizlang
function exists(tree: Select): Expression<bool>

EXISTS (TREE): whether the query selects a row. not negates it.

Effects: fail, alloc.

scalar

gizlang
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

gizlang
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

gizlang
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 DbError

The typed query of the SELECT TEXT with its PARAMETERS and OUTPUTS and the two conversions. It fails unless the text is one SELECT.

insert

gizlang
function insert(table: string, names: Vec<string>, rows: Vec<Vec<Value>>): Statement throws DbError

INSERT of ROWS into the columns NAMES of TABLE.

Effects: fail, alloc.

update

gizlang
function update(table: string, patch: Patch, predicate: Expression<bool>): Statement throws DbError

UPDATE of TABLE that sets the fields of PATCH where PREDICATE holds.

Effects: fail, alloc.

remove

gizlang
function remove(table: string, predicate: Expression<bool>): Statement

DELETE from TABLE where PREDICATE holds.

Effects: fail, alloc.

upsert

gizlang
function upsert(table: string, names: Vec<string>, rows: Vec<Vec<Value>>, keys: Vec<string>, patch: Patch): Statement throws DbError

INSERT of ROWS into TABLE that, on a conflict on KEYS, sets the fields of PATCH (an empty one: DO NOTHING).

Effects: fail, alloc.

lessThan

gizlang
function lessThan<T>(a: Expression<T>, b: Expression<T>): Expression<bool>

A < B.

atMost

gizlang
function atMost<T>(a: Expression<T>, b: Expression<T>): Expression<bool>

A <= B.

greaterThan

gizlang
function greaterThan<T>(a: Expression<T>, b: Expression<T>): Expression<bool>

A > B.

atLeast

gizlang
function atLeast<T>(a: Expression<T>, b: Expression<T>): Expression<bool>

A >= B.

notEqual

gizlang
function notEqual<T>(a: Expression<T>, b: Expression<T>): Expression<bool>

A <> B.

equalValue

gizlang
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

gizlang
function isNull<T>(value: Expression<T>): Expression<bool>

VALUE IS NULL.

isNotNull

gizlang
function isNotNull<T>(value: Expression<T>): Expression<bool>

VALUE IS NOT NULL.

inList

gizlang
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

gizlang
function like<T>(value: Expression<T>, pattern: string, caseless: bool = false): Expression<bool>

VALUE LIKE PATTERN (ILIKE when CASELESS), with \ as its escape.

prefix

gizlang
function prefix<T>(value: Expression<T>, text: string): Expression<bool>

VALUE starts with TEXT.

contains

gizlang
function contains<T>(value: Expression<T>, text: string): Expression<bool>

VALUE holds TEXT.

value

gizlang
function value(v: Value): Expression<Value>

The constant V, typed by its own type.

Effects: fail, alloc.

lower

gizlang
function lower<T>(value: Expression<T>): Expression<T>

LOWER(VALUE): a text in lower case (ASCII letters in SQLite).

matches

gizlang
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

gizlang
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

gizlang
function orElse(a: Expression<bool>, b: Expression<bool>): Expression<bool>

A OR B.

Effects: fail, alloc.

not

gizlang
function not(value: Expression<bool>): Expression<bool>

NOT VALUE.

Effects: fail, alloc.

orderBy

gizlang
function orderBy<T>(tree: Select, value: Expression<T>, descending: bool, nullsFirst: bool): Select

TREE ordered by VALUE after its earlier keys, in the given direction and null order.

limit

gizlang
function limit(tree: Select, n: i64, skip: i64 = 0): Select throws DbError

TREE with LIMIT N and OFFSET SKIP. It fails when either is negative.

Effects: fail, alloc.

innerJoin

gizlang
function innerJoin<L, R>(left: Expression<L>, right: Expression<R>): Select throws DbError

The inner join of the tables of LEFT and RIGHT on LEFT = RIGHT.

leftJoin

gizlang
function leftJoin<L, R>(left: Expression<L>, right: Expression<R>): Select throws DbError

Project right-hand fields with optionalCodec: unmatched children are NULL.

count

gizlang
function count<T>(value: Expression<T>): Expression<i64>

COUNT(VALUE): the rows where VALUE is not NULL.

countAll

gizlang
function countAll(): Expression<i64>

COUNT(*): the rows.

Effects: fail, alloc.

sum

gizlang
function sum<T, I>(value: Expression<I>, codec: Codec<T>): Expression<Option<T>> throws DbError

The output codec excludes NULL; the aggregate adds Option for empty groups.

min

gizlang
function min<T, I>(value: Expression<I>, codec: Codec<T>): Expression<Option<T>> throws DbError

MIN(VALUE), read by CODEC; none on no rows.

max

gizlang
function max<T, I>(value: Expression<I>, codec: Codec<T>): Expression<Option<T>> throws DbError

MAX(VALUE), read by CODEC; none on no rows.

avg

gizlang
function avg<T>(value: Expression<T>): Expression<Option<f64>> throws DbError

AVG 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

gizlang
function groupBy<T>(tree: Select, value: Expression<T>): Select

TREE grouped by VALUE after its earlier keys.

having

gizlang
function having(tree: Select, predicate: Expression<bool>): Select

TREE with PREDICATE added to its HAVING (AND).

Effects: fail, alloc.

keyField

gizlang
function keyField<T>(value: Expression<T>, descending: bool): KeyField

VALUE as a key of keyset paging, in the given direction.

page

gizlang
function page<P, R>(source: Source, query: Prepared<P, R>, input: P, keyFields: Vec<KeyField>, after: Option<Vec<Value>>, size: i64): Page<R> throws DbError

Hidden cursor projections permit keys that are absent from the public result.

include

gizlang
function include<P, C, K>(source: Source, parents: Vec<P>, relation: Relation<P, C, K>): Vec<Included<P, C>> throws DbError

One 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

gizlang
function defineCommands(table: Schema.Table): Vec<Statement> throws DbError

The 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

gizlang
function lambdaExpr(node: Lambda.Node, names: Vec<string>, columns: Vec<string>, slots: Vec<Lambda.Constant>): Expr throws DbError

The 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

gizlang
function aggregated(node: Lambda.Node): bool

Whether NODE computes an aggregate of a group (gizlang/expr count, sum, avg, and min or max of one value).

Effects: fail.

Methods

Prepared.values

gizlang
function Prepared.values<P, R>(self: Prepared<P, R>, input: P): Vec<Value> throws DbError

The values of the parameters of the query, encoded from INPUT.

Prepared.records

gizlang
function Prepared.records<P, R>(self: Prepared<P, R>, batch: Batch): Vec<R> throws DbError

The 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

gizlang
function Prepared.fetch<P, R>(self: Prepared<P, R>, source: Source, input: P): Vec<R> throws DbError

Collect through the existing source cursor. Close it on decode or poll failure.

Prepared.fetchBounded

gizlang
function Prepared.fetchBounded<P, R>(self: Prepared<P, R>, source: Source, input: P, maxRows: i64): Vec<R> throws DbError

Collect typed rows with a strict maximum: one row more is a limit failure, never a cut answer.

Prepared.stream

gizlang
function Prepared.stream<P, R>(self: Prepared<P, R>, source: Source, input: P, each: (batch: Batch) => bool throws DbError): i64 throws DbError

Read 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

gizlang
function Prepared.fetchAcross<P, R>(self: Prepared<P, R>, sources: Vec<Source>, input: P, maxRows: i64): Vec<R> throws DbError

Bounded 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

gizlang
function Source.query<P, R>(self: Source, q: Prepared<P, R>, input: P, rows: i64, bytes: i64): Cursor throws DbError

The 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

gizlang
const INCLUDE_CHUNK: i64 = 1000

The most parents of one IN query of include.