# 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.

