# gizlang/sql

one SQL database by its location, with the calls that `sql` gives on Node and Bun. A location is a PostgreSQL server (postgres://, which reaches a GizDB server too), a SQLite file, or on giz an ODBC data source (odbc:, and mysql:// through the MariaDB driver). One SQL for all: parameters $1.., each value text and none for NULL; what differs (DDL types, locks) is the caller's, by `dialect`.

```gizlang
import * as Sql from "gizlang/sql";
```

## Types

### `Database`

```gizlang
type Database = | { kind: "sqlite"; db: Sqlite.Database; } | { kind: "postgres"; pool: Pg.Pool; } | { kind: "session"; connection: Pg.Connection; } | { kind: "odbc"; connection: i64; speaks: string; inside: bool; locked: Ref<bool>; }
```

A database: a SQLite file, a PostgreSQL pool, inside a transaction the one PostgreSQL connection it runs on, or an
ODBC connection that speaks the dialect SPEAKS (INSIDE in a transaction).

- `sqlite`
- `postgres`
- `session`
- `odbc`

### `Rows`

```gizlang
type Rows = { columns: Vec<string>; rows: Vec<Vec<Option<string>>>; affected: i64; }
```

A statement's column names, its rows (each column as text, none for NULL), and the rows an INSERT, UPDATE or
DELETE without RETURNING changed.

- `columns`: `Vec<string>`
- `rows`: `Vec<Vec<_gizlang_sdk_core.Option<string>>>`
- `affected`: `i64`

### `Reader`

```gizlang
type Reader = { columns: Vec<string>; step: () => Option<Vec<Option<string>>> throws SqlError; dispose: () => bool throws SqlError; }
```

A read stepped one row at a time: the columns the database named, the next row (none at the end) and the close.
SQLite gives one. The drivers whose answer comes as one read give none: refused, never approximated.

- `columns`: `Vec<string>`
- `step`: `() => _gizlang_sdk_core.Option<Vec<_gizlang_sdk_core.Option<string>>> throws gizlang_sql.SqlError`
- `dispose`: `() => bool throws gizlang_sql.SqlError`

### `SqlError`

```gizlang
type SqlError = { sqlstate: Option<string>; message: string; detail: string; position: Option<i64>; code: i64; }
```

A failure. PostgreSQL fills `sqlstate`, `detail` and `position` from the server's error; SQLite has none of them.
`code` is the engine's own number (the SQLite extended result code, a transport errno), else 0.

- `sqlstate`: `_gizlang_sdk_core.Option<string>`
- `message`: `string`
- `detail`: `string`
- `position`: `_gizlang_sdk_core.Option<i64>`
- `code`: `i64`

## Functions

### `flag`

```gizlang
function flag(text: string): bool
```

A boolean column of `Rows`: SQLite spells true "1", PostgreSQL "t".

### `sqlError`

```gizlang
function sqlError(message: string): SqlError
```

An error with MESSAGE only.

### `conflictError`

```gizlang
function conflictError(message: string): SqlError
```

A serialization failure (SQLSTATE 40001) with MESSAGE: a write that lost to another.

### `integer`

```gizlang
function integer(text: Option<string>): i64 throws SqlError
```

An integer column of `Rows` (decimal in both engines). NULL, or text that is not an i64, is an error, never a
silent 0.

Effects: fail, alloc.

### `poolOf`

```gizlang
function poolOf(location: string): i64
```

The pool size a postgres:// LOCATION asks for with its `pool=N` parameter (a role whose connection limit is low, such
as a read-only migration role), else POOL.

Effects: fail, alloc.

### `isSqlite`

```gizlang
function isSqlite(location: string): bool
```

Whether LOCATION names a SQLite file: `sqlite:PATH`, a path that ends in .db, or :memory:.

Effects: fail.

### `quota`

```gizlang
function quota(): i64
```

STORE_QUOTA in bytes, 0 when it is not set: past it a write to a SQLite file fails and reads go on.

Effects: fail, io, alloc.

### `open`

```gizlang
function open(location: string): Database throws SqlError
```

The database at LOCATION: postgres://USER:PASSWORD@HOST:PORT/DATABASE (no percent escapes; as libpq, a trailing
?host=/FOLDER names the folder of the server's unix-domain socket instead of HOST, which may then be empty;
&pool=N keeps fewer than POOL connections), or a SQLite file (isSqlite), made with its folder when missing.
A SQLite file is opened as each worker process (GIZLANG_WORKERS) needs it: WAL, so readers never wait for the writer;
a writer that finds another worker's write in progress waits up to 5 s for it instead of failing; commits sync at
checkpoints (synchronous NORMAL: a power loss may drop the last commits, never corrupt). SYS is the process's own
unless a .giz caller passes one: a .ts app never names System, as on Node and Bun.

Effects: fail, read, write, console, io, alloc.

## Methods

### `Database.reader`

```gizlang
function Database.reader(self: Database, sql: string, params: Vec<Option<string>> = []): Reader throws SqlError
```

The reader of QUERY, its parameters as text bound, on a database the driver steps rows of. PostgreSQL and an
ODBC source answer as one read here: they refuse, and a caller narrows the query or keeps the one read.

Effects: fail, read, write, console, io, alloc.

### `SqlError.toError`

```gizlang
function SqlError.toError(self: SqlError): Error
```

The error as TypeScript sees it: an Error named SqlError, with the SQLSTATE (or the engine's code) as its cause.

Effects: fail, alloc.

### `SqlError.conflicting`

```gizlang
function SqlError.conflicting(self: SqlError): bool
```

True when the write lost to another or broke a key or constraint: PostgreSQL class 23 (integrity) or 40 (rollback),
SQLite's constraint code.

Effects: fail.

### `SqlError.text`

```gizlang
function SqlError.text(self: SqlError): string
```

The failure as one line: its message, then its detail when it has one.

Effects: fail, alloc.

### `Database.dialect`

```gizlang
function Database.dialect(self: Database): string
```

The SQL dialect a Database speaks: "sqlite", "postgres", or an ODBC source's own (mysql, mssql, oracle, ...).

### `Database.remote`

```gizlang
function Database.remote(self: Database): bool
```

Whether a query crosses the network (PostgreSQL, a GizDB server, an ODBC source): a page then starts its reads
together (read).

### `Database.read`

```gizlang
async function Database.read(self: Database, sql: string, params: Vec<Option<string>> = []): Promise<Rows> throws SqlError
```

The rows of a query that reads, on a task of its own, so reads started together overlap on the pool.

Effects: fail, read, write, console, io, alloc.

### `Database.query`

```gizlang
function Database.query(self: Database, sql: string, params: Vec<Option<string>> = []): Rows throws SqlError
```

One statement with parameters $1.. (none for NULL).

Effects: fail, read, write, console, io, alloc.

### `Database.kept`

```gizlang
function Database.kept(self: Database, sql: string, params: Vec<Option<string>> = []): Rows throws SqlError
```

A read whose answer depends only on the data it reads (no clock, no random draw, no function with effects). SQLite
keeps its answer until the data changes (gizlang/sql/sqlite Database.kept); another database runs it as a query.

Effects: fail, read, write, console, io, alloc.

### `Database.version`

```gizlang
function Database.version(self: Database): string throws SqlError
```

The version of the data this connection reads now: two reads in one version see the same data. SQLite counts its
commits (gizlang/sql/sqlite Database.version); another database has none here, "".

Effects: fail, read, write, console, io, alloc.

### `Database.exec`

```gizlang
function Database.exec(self: Database, sql: string): bool throws SqlError
```

A script of statements without parameters or results.

Effects: fail, read, write, console, io, alloc.

### `Database.transaction`

```gizlang
function Database.transaction<T>(self: Database, work: (tx: Database) => T throws SqlError): T throws SqlError
```

Runs WORK once in a transaction. Commit conflicts are returned with their SQLSTATE; an application effect is never
implicitly replayed.

### `Database.lock`

```gizlang
function Database.lock(self: Database, name: string): bool throws SqlError
```

Inside a transaction: the lock named NAME until it ends, so transactions taking the same name run one after another
while readers go on reading (the name need not be a table yet). SQLite's writers already run one after another;
PostgreSQL takes a transaction advisory lock on the name; MySQL a session lock that the transaction's end frees.

Effects: fail, read, write, console, io, alloc.

### `Database.columns`

```gizlang
function Database.columns(self: Database, table: string): Vec<string> throws SqlError
```

The names of a table's columns; none when it does not exist. A table of a schema (auth.users) is looked up in that
schema; on SQLite that is the database attached as it.

Effects: fail, read, write, console, io, alloc.

### `Database.tables`

```gizlang
function Database.tables(self: Database): Vec<string> throws SqlError
```

The names of the tables in the current schema, in order.

Effects: fail, read, write, console, io, alloc.

### `Database.dispose`

```gizlang
function Database.dispose(self: Database): void
```

Close the database: a pool waits for its lent connections to come back.

Effects: fail, read, write, console, io, alloc.

## Constants

### `POOL`

```gizlang
const POOL: i64 = 8
```

Connections each worker process keeps to PostgreSQL, unless the location's `pool=N` names fewer.

