# gizdb:query

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 gizdb:typed.

```gizlang
import * as Query from "gizdb:query";
```

## Types

### `Prepared`

```gizlang
type Prepared<P, R> = { tree: Select; parameters: Vec<Slot>; outputs: Vec<Slot>; encode: (input: P) => Result<Vec<Value>, DbError> effects { fail }; decode: (row: Vec<Value>) => Result<R, DbError> effects { fail }; 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`: `_gizlang_lib_db__sql__ast.Expr`
- `slot`: `M54.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 }; }
```

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): Result<Statement, 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.

### `requiredRows`

```gizlang
function requiredRows(command: Statement): Result<Option<i64>, DbError>
```

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

Effects: fail.

### `rowPatchCommands`

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

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

Effects: fail.

### `checkBatch`

```gizlang
function checkBatch(slots: Vec<Slot>, batch: Batch): Result<bool, DbError>
```

Checks that the columns of BATCH match SLOTS in count and type.

Effects: fail.

### `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): Result<Expression<T>, 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.

### `table`

```gizlang
function table(name: string): Select
```

SELECT from the table NAME, with no columns yet.

Effects: fail.

### `where`

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

TREE with PREDICATE added to its WHERE (AND).

Effects: fail.

### `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): Result<Vec<Statement>, DbError>
```

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

Effects: fail.

### `sql`

```gizlang
function sql<P, R>(text: string, parameters: Vec<Slot>, outputs: Vec<Slot>, encode: (input: P) => Result<Vec<Value>, DbError> effects { fail }, decode: (row: Vec<Value>) => Result<R, DbError> effects { fail }): Result<Prepared<P, R>, 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>>): Result<Statement, DbError>
```

INSERT of ROWS into the columns NAMES of TABLE.

Effects: fail.

### `update`

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

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

Effects: fail.

### `remove`

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

DELETE from TABLE where PREDICATE holds.

Effects: fail.

### `upsert`

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

INSERT of ROWS into TABLE that, on a conflict on KEYS, sets the fields of PATCH.

Effects: fail.

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

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

### `prefix`

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

Escape the escape character before LIKE metacharacters.

Effects: fail.

### `orElse`

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

A OR B.

Effects: fail.

### `not`

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

NOT VALUE.

Effects: fail.

### `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): Result<Select, DbError>
```

TREE with LIMIT N. It fails when N is negative.

Effects: fail.

### `innerJoin`

```gizlang
function innerJoin<L, R>(left: Expression<L>, right: Expression<R>): Result<Select, 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>): Result<Select, 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.

### `sum`

```gizlang
function sum<T, I>(value: Expression<I>, codec: Codec<T>): Result<Expression<Option<T>>, 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>): Result<Expression<Option<T>>, DbError>
```

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

### `max`

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

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

### `avg`

```gizlang
function avg<T>(value: Expression<T>): Result<Expression<Option<f64>>, 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.

### `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): Result<Page<R>, 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>): Result<Vec<Included<P, C>>, 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): Result<Vec<Statement>, 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.

## Methods

### `Prepared.values`

```gizlang
function Prepared.values<P, R>(self: Prepared<P, R>, input: P): Result<Vec<Value>, 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): Result<Vec<R>, DbError>
```

The rows of BATCH, decoded into records.

### `Prepared.fetch`

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

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

### `Source.query`

```gizlang
function Source.query<P, R>(self: Source, q: Prepared<P, R>, input: P, rows: i64, bytes: i64): Result<Cursor, 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 = 1000
```

The most parents of one IN query of include.