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.

import * as Query from "gizdb:query";

Types

Prepared

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

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: _gizlang_lib_db__sql__ast.Expr
• slot: M54.Slot
• descending: 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 }; }

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): 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

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

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

Effects: fail.

rowPatchCommands

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

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

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): Result<Expression<T>, DbError>

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

table

function table(name: string): Select

SELECT from the table NAME, with no columns yet.

Effects: fail.

where

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

TREE with PREDICATE added to its WHERE (AND).

Effects: fail.

projection

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

TREE with VALUE added to its columns as NAME.

statements

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

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

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

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

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

DELETE from TABLE where PREDICATE holds.

Effects: fail.

upsert

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

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.

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.

prefix

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

Escape the escape character before LIKE metacharacters.

Effects: fail.

orElse

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

A OR B.

Effects: fail.

not

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

NOT VALUE.

Effects: fail.

orderBy

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

function limit(tree: Select, n: i64): Result<Select, DbError>

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

Effects: fail.

innerJoin

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

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

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

sum

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

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

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

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

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

TREE grouped by VALUE after its earlier keys.

having

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

TREE with PREDICATE added to its HAVING (AND).

Effects: fail.

keyField

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

VALUE 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): Result<Page<R>, DbError>

Hidden 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>): 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

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

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

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

The rows of BATCH, decoded into records.

Prepared.fetch

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

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

const INCLUDE_CHUNK = 1000

The most parents of one IN query of include.