Bookclerk SQL contract v1

Development These docs follow the development version and change with every merge.

Last updated View as Markdown

This document is the normative definition of sqlContractVersion = 1. BookclerkSQL is a host-owned portable subset, not “SQLite everywhere.” Adapter admission is “passes Bookclerk SQL v1 conformance,” not “looks like SQLite or PostgreSQL.” Guests speak only this grammar. Adapters alone lower to SQLite, PostgreSQL 16+, or Cloudflare D1. Machine-readable vectors live under crates/bookclerk-db-exec/testdata/sql_v1/ and are consumed by bookclerk-db-exec lowering tests and library conformance.

Version compatibility

  • Contract versions are monotonic supersets: every statement form, type, and semantic guarantee in version N remains valid in version N+1.
  • Guests advertise the highest version they implement as sqlContractVersion.
  • Hosts require sqlContractVersion >= SQL_CONTRACT_VERSION (currently 1).
  • A non-superset redesign needs a new contract identity and a plugin ABI break (apiVersion / plugin.toml api_version). Do not increment this scalar and keep >= if N+1 would drop or change a guarantee of N.

Grammar (canonical form)

Guest SQL is admitted in this order:

  1. SQL-v1 grammar — positive/versioned Bookclerk SQL (not a SQLite dialect).
  2. Typed expressions — the host builds exactly one resolved typed proof (ResolvedStatement) per canonical statement, bound to the SHA-256 of that exact SQL string (after host semantic desugars). Authorization, schema companions, result expectations, and adapter lowering consume that proof; none re-resolve names or types. Proofs travel on host→adapter AdapterDatabaseSession.execute (AdapterExecuteRequest.proofs). GuestDatabase.execute stays untrusted ExecuteRequest without proofs. The proof is typed against a durable binding catalog (bookclerk_sql_catalog / bookclerk_sql_schema) plus in-request CREATE/DROP. Unknown tables/columns fail closed. Empty catalog means “no plugin tables,” not “unknown → NULL.” host_authoritative skips authorization only; typing and TEXT lowering still run (from the canonical host schema or an equivalent already-resolved proof). New atomic batches snapshot catalog and physical types in autocommit before BEGIN, so SQLite does not hold a reserved lock across paged catalog loads. On a SQLite file the SQLite adapter remembers that autocommit snapshot and reuses it while one small key read (the file, PRAGMA schema_version, the catalog row count and the bookclerk_sql_schema rows) is unchanged, so a batch does not reload the catalog and reparse every table; any DDL, by any connection, changes the key. Nested typed execute on an already-open transaction still snapshots inside that transaction, never from the memo. SchemaAction::Create stores the full CreateTableSchema (ordered columns, SQL-v1 types, nullability, identity, PK/UNIQUE/defaults/CHECK/FKs) plus fingerprint and no-op. Catalog companions and Postgres identity objects consume that schema; they do not reparse canonical CREATE text. Host catalog INSERT/DELETE DML is built with SeaQuery (SqliteQueryBuilder) from the proof. Adapter-private catalogs are reserved names; guests cannot grant themselves sqlite_master / information_schema probes.
  3. Binding scope/security — isolated schema, reserved-name, and ref checks using proof.physical_accesses and proof.functions (no second alias walk) when a type environment is present (binding-owned and library guests with catalog types). A projection wildcard (SELECT *, alias.*, RETURNING *) records each physical column plus "*" on that table; column-scoped grants reject the wildcard the same way the lexical authorizer does. column: None is table presence only (FROM / DML destination), not a star. Library guests without a type env still use lexical parse_guest_sql_refs so SQLite-only helpers remain authorizable without pretending they are portable SQL-v1 types.
  4. Capability/limit validation — binds, result selection, negotiated caps.
  5. Canonical ExecuteRequest — host wrap (receipts) without engine identity. Receipt wrappers get their own proofs bound to the wrapper SQL.
  6. Adapter-edge lowering — at execute, against the isolated physical DB, reading TEXT collation sites from the proof (never ident_is_text).

Hosts emit canonical Bookclerk SQL. Host semantic desugars (not adapter rewrites) make unspecified ORDER BY explicit (NULLS FIRST/LAST, SQLite default) and wrap / % divisors as NULLIF(x, 0). Adapters lower to the engine at execute time. Domain planners must not branch on engine identity.

  • DML and queries — bookclerk-db-exec::lower_canonical_sql_typed (placeholders, IFNULL, json_extract, INSERT OR IGNORE, 2+-arg min/max, json_valid, round/sum/avg wire types, LIKE→GLOB, proof-directed COLLATE "C" and INTEGER overflow). Default NULLS are not adapter rewrites.
  • DDL types and identity — adapter execution edge (realize_host_ddl / binding DDL helpers). The generic DML pass does not classify statements as DDL.
  • INSERT OR IGNORE … SELECT / WITH — adapters wrap the row source as SELECT * FROM (<source>) AS bookclerk_src WHERE true before ON CONFLICT DO NOTHING so SQLite/D1 UPSERT is unambiguous. VALUES is not wrapped. bookclerk_src is reserved like every bookclerk_-prefixed identifier.

Placeholders

  • Positional binds use bare ? only (1-based order of appearance in code spans).
  • $n, ?NNN, and other engine-specific bind spellings are outside SQL v1 and are rejected for every guest before adapter dispatch.
  • Placeholders inside string/identifier literals, line/block comments, and PostgreSQL dollar-quotes are not binds (adapters must preserve them).

Statement forms

Supported top-level forms for guest-authorized and host-authored plans (fail-closed recursive-descent grammar, not a denylist):

Form Notes
WITH CTEs + SELECT … CTE bodies are SELECT only (no nested DML). DISTINCT is admitted; DISTINCT ON is not. Mixed aggregate + non-aggregate select lists are SQLite-shaped and not in this contract; keep aggregates in their own statement (or GROUP BY).
SELECT clauses FROM / JOIN (INNER / LEFT / CROSS; not RIGHT / FULL), WHERE / GROUP BY / HAVING / ORDER BY [ASC|DESC] [NULLS FIRST|LAST] / LIMIT / OFFSET, UNION [ALL] (not EXCEPT / INTERSECT).
INSERT INTO … / INSERT OR IGNORE INTO … Optional RETURNING
UPDATE … / DELETE … Optional RETURNING
Schema DDL Host-only for library and session guests. GuestSqlPolicy::binding_owned admits idempotent CREATE/DROP TABLE/INDEX IF [NOT] EXISTS on a physically separate plugin database. Binding helpers are this contract (not a wider SQLite dialect). Postgres adapters lower canonical types (INTEGER PRIMARY KEY AUTOINCREMENT → BIGINT PRIMARY KEY plus a transactional identity trigger, BLOB → BYTEA, case-insensitive) at their execute edge.

Expression operators (closed): = <> != < > <= >=, AND OR NOT, IS [NOT] NULL, IN, BETWEEN, LIKE (not ILIKE), \|\| + - * / %, CASE, CAST(x AS INTEGER\|REAL\|TEXT\|BLOB\|BOOLEAN), policy-allowed function calls, . qualification, ?, literals. Rejected for every guest: ::, ILIKE, SIMILAR, OVER, FILTER, GLOB / MATCH / REGEXP. COLLATE is rejected except COLLATE NOCASE on an ORDER BY key or a CREATE INDEX key (the library title-order fold).

INSERT OR IGNORE is canonical and means unique / primary-key conflict only. NOT NULL and CHECK still abort on every backend. Adapters lower the form to INSERT … ON CONFLICT DO NOTHING (conflict clause before RETURNING) for SQLite, D1, and Postgres — SQLite OR IGNORE would otherwise swallow NOT NULL. When the source is SELECT/WITH, the wrap above is required. REPLACE INTO, INSERT OR REPLACE / OR ABORT / OR FAIL / OR ROLLBACK are outside SQL v1 and are rejected before adapter dispatch.

Binding CREATE TABLE / CREATE INDEX types are INTEGER, REAL, TEXT, BLOB, and BOOLEAN only. AUTOINCREMENT is valid once, immediately after PRIMARY KEY, and only when the column type is INTEGER (INTEGER PRIMARY KEY AUTOINCREMENT). INTEGER AUTOINCREMENT, REAL AUTOINCREMENT, and duplicate AUTOINCREMENT are rejected. BOOL is not an alias. Tails such as STRICT, WITHOUT ROWID, JSONB, NUMERIC(p,s), VARCHAR(n), CASCADE / RESTRICT on DROP, and backend-specific index clauses (USING, INCLUDE, and column COLLATE other than COLLATE NOCASE on an index key) are rejected. Adapter lowering of type/identity tokens and RETURNING is case-insensitive in code spans (the host write-gate constant is still matched exactly).

Identity after INTEGER PRIMARY KEY AUTOINCREMENT is SQLite-equivalent: after an explicit id insert, the next omit-id is max(committed id)+1 and deleted ids are not reused. Explicit identity values are admitted. Postgres adapters do not use BIGSERIAL/setval (those do not roll back with the ExecuteRequest). They keep a transactional BIGINT high-water row in adapter-private bookclerk_identity and a BEFORE INSERT trigger generated from the parsed identity column name. DROP TABLE deletes that table's catalog and identity rows in the same atomic request and drops bc_if_<digest>(); it does not emit DROP TRIGGER … ON after the relation is gone. Recreate starts at 1. SQLite/D1 keep native AUTOINCREMENT. Guests cannot name bookclerk_identity or bookclerk_sql_catalog.

Identifiers and quoting

  • Identifiers are canonical unquoted names: [A-Za-z_][A-Za-z0-9_]*, ≤ 63 bytes after case fold (PostgreSQL NAMEDATALEN-1). 63 accepted; 64 rejected. Quoted / mixed-case object names ("Foo", `Foo`, [Foo]) are rejected. Adapter-private Postgres identity objects use hash-based names with separate prefixes: bc_if_<digest> (function) and bc_it_<digest> (trigger), 16 hex digits of SHA-256 of the folded table name, always under 63 bytes. Two canonical names that would collide under Postgres 63-byte truncation produce distinct generated object names.
  • Unquoted names fold to lowercase in authorization (guest_sql); adapters must not reinterpret authorization. CREATE TABLE Foo and SELECT … FROM foo name the same object on every admitted backend.

Types and DbValue

Wire (DbValue) Canonical SQL expectation
null(expectedType) SQL NULL with typed metadata
bool Canonical SQL BOOLEAN (not INTEGER 0/1 storage leaking onto the wire)
int64 Exact 64-bit integer (not JSON number)
float64 IEEE float
text UTF-8 text: Unicode scalar values excluding U+0000
bytes Opaque byte string (NULs allowed)

Unknown Cap'n union members fail closed as unsupported.

Portable TEXT

BookclerkSQL TEXT is Unicode scalar values on the UTF-8 wire, excluding U+0000. UTF-8 is the encoding, not a smaller repertoire. The host rejects NUL in parser literals, DbValue::Text binds/results, and catalog TEXT cells before adapter dispatch, including SQLite. BYTES/BLOB may contain 0x00. PostgreSQL adapters require server_encoding / client_encoding UTF8 (or a strict UTF-8 equivalent) at connect; SQL_ASCII / LATINn fail closed.

Advertised numeric limits

Host admission enforces advertised DbCapabilities before dispatch. Adapters must not over-advertise a physical engine limit they cannot hide. A guest reads the caps its session negotiated with GuestDatabase.limits (the binding's limits() in every SDK) and sizes requests and reads before sending; exceeding one fails with the typed limitExceeded error, never a truncated result.

Field Meaning
maxBinds / maxStatements / maxPayloadBytes Binds, statements, and canonical SQL+binds per statement
maxResultRows / maxResultBytes / maxCellBytes Result envelope (fail closed, never truncate)
maxRequestBytes / maxAtomicResultBytes Whole encoded request / reply
maxStreamedResultBytes / maxStreamedResultRows Whole reply and rows per statement when the result is read as a stream (executeStreamed); 0 on both means the adapter does not stream. When set, each is at least its inline counterpart and at most the host maximum (64 MiB, 250 000 rows), since the host holds the reply while it reassembles it
maxFunctionArgs Physical vararg ceiling after adapter hiding. Counts the arguments of one function call; the members of an IN list, a VALUES row, a column list or a subquery are not arguments. Hosts require at least 32 (the portable json_object maximum)
maxSchemaColumns Columns per CREATE TABLE / result width
maxPatternBytes UTF-8 bytes of a LIKE pattern value (literals and TEXT binds). Sqlite-family GLOB expansion is 3× for [ * ?; D1 advertises 16 so expanded GLOB stays ≤ 50

Streamed results

AdapterDatabaseSession.executeStreamed and GuestDatabase.executeStreamed run the same atomic batch as execute but answer it as a sequence of ExecuteStreamFrame messages instead of one ExecuteReply, so a result may grow to maxStreamedResultBytes / maxStreamedResultRows instead of the inline caps. The batch still commits (or rolls back) whole before the first frame is sent; streaming changes how the result crosses the wire, not its atomicity.

Each frame is one single-segment Cap'n Proto message, at most 256 KiB unless it carries a single larger row. The sequence is head (operationId, statement count), then per statement a statement frame (index, columns, rowsAffected) followed by zero or more rows frames, then end with the timing, or err with a PluginError at any point. The reply is inline bytes when the frames fit in one scalar, else a ByteSource. A receiver decodes frame by frame, holds the raw bytes to maxStreamedResultBytes plus framing slack, and fails closed with limitExceeded (maxStreamedResultRows / maxStreamedResultBytes) as soon as a budget is passed, so an oversized result never lands whole.

The whole-reply budget (maxAtomicResultBytes, or maxStreamedResultBytes for a streamed batch) is also enforced while the batch runs, not only on the encoded reply: an adapter holds every statement to the smaller of maxResultBytes and what is left of the whole-reply budget, so a batch of individually admissible results fails with limitExceeded (maxAtomicResultBytes) at the statement that would exceed it, and the remaining statements never run or occupy memory.

The host reads every standalone library and binding batch through executeStreamed when the adapter streams, and the SDKs read a guest's all() and iterate() the same way over the granted channel's POST /db/execute-streamed (see Working within database limits). An adapter that does not stream answers both methods with unsupported. An isolate (workerd) adapter's streamed reply travels inline through the launcher, so its frames must fit one scalar: the SDK clamps such an adapter's advertised maxStreamedResultBytes to 256 KiB so the host's budget is one the adapter can hold to before it commits, and frames past one scalar come back as limitExceeded (maxScalarBytes). First-party sqlite and Postgres advertise 64 MiB and 250 000 rows; D1 advertises 8 MiB and 50 000 rows, and reads its HTTP responses under a body ceiling sized for the budget of the attempt.

Isolate adapter batches and replay receipts

A guest batch reaches an adapter wrapped in a replay receipt: a prune DELETE and a prior-receipt SELECT come first, every guest DML write is gated on the receipt being absent, and a stub INSERT claims the receipt last (AdapterExecuteRequest.receipt gives the guest statement count as guestLen and the guest request's requestHash as guestHash). Guest DDL carries no receipt predicate: the host admits only idempotent DDL (CREATE/DROP … IF [NOT] EXISTS), and the SDK helpers skip all remaining guest work, DDL included, once the prior-receipt SELECT shows a completed or conflicting receipt. The adapter stores the guest reply on that receipt before COMMIT, so a retry with the same operationId after a lost reply replays the recorded result instead of running the writes again.

Isolate adapters get this from the SDKs instead of writing it themselves:

  • TypeScript: executeAdapterBatch and executeAdapterBatchStreamed from @bookclerk/plugin-sdk/workerd.
  • Python: execute_adapter_batch and execute_adapter_batch_streamed from bookclerk_plugin_sdk.db_value.

The adapter supplies a begin that opens a transaction with run, commit and rollback. The helper runs the wrapped statements, skips the guest work a completed prior receipt already covers, runs the finalize UPDATE that stores the reply (or the read-only marker for a batch of SELECTs), and commits. When the prior-receipt SELECT found nothing but the stub claimed nothing either, another attempt committed the receipt in between; the helper rolls back with unavailable so the host's retry replays that receipt. The receipt records the measured timing, which a replay answers with. A result cell the host would refuse (a non-finite float64 or TEXT with U+0000) rolls the batch back with invalidParams. The streamed variant takes the streamed budget the adapter advertises (executeStreamLimits(caps) / execute_stream_limits(caps)). It encodes the frames inside the transaction, stops with limitExceeded (maxScalarBytes) as soon as they pass one scalar, decodes them under that budget the way the host does, and holds the encoded ExecuteReply to the byte budget itself (the decoder allows framing slack past it). Any failure rolls back, so a reply the host would refuse never commits. A write whose reply is over the 64 KiB replay payload also rolls back, with internal. The SDK tests run both helpers on SQLite against a receipt-wrap vector the host generates (crates/bookclerk-library/fixtures/guest_receipt/isolate_adapter.json).

json_object even arity 2–32 is portable (16 key/value pairs). That matches D1’s physical maxFunctionArgs. Sqlite-family adapters must not nest json_patch to admit more pairs: JSON Merge Patch deletes null values and changes duplicate-key behavior. Scalar min/max / coalesce are already ≤ 32 in the helper table. Postgres json_build_object does not need nesting and is cast to TEXT ((json_build_object(…)::text)) except when a nested json_object is a constructor value (that call stays json so it nests as an object). Unproven LIKE pattern expressions fail closed when maxPatternBytes is finite. Do not use PRAGMA case_sensitive_like.

NULL and coercion

  • Typed null carries expectedType for empty-result metadata and decode.
  • Adapters must not collapse distinct Cap'n types through JSON number/array shims on the granted Cap'n path.
  • CAST matrix (proven only): same-type CAST; INTEGER ↔ REAL when the conversion is proven; typed NULL to any admitted type. Rejected: TEXT→INTEGER/BOOLEAN/BLOB (including CAST('1' AS INTEGER)), BLOB→TEXT/INTEGER, BOOLEAN↔numeric, and any untested coercion. Store the value in the destination type instead.
  • Unification (same rules on every backend): IFNULL/COALESCE/NULLIF/ CASE/UNION arms, comparisons, scalar min/max, arithmetic (INTEGER+REAL → REAL). sum(x) requires INTEGER. avg/round require numeric. lower/upper/LIKE require TEXT.
  • BOOLEAN contexts (BOOLEAN | NULL only): WHERE / HAVING / JOIN … ON / DELETE/UPDATE WHERE, searched CASE WHEN <pred>, NOT / AND / OR operands, and CHECK (…) on binding CREATE TABLE. SELECT 1 WHERE 1 is rejected. Simple CASE expr WHEN v unifies v with expr.
  • Assignment sites: INSERT destination list is the explicit column list or catalog declaration order. VALUES and SELECT/WITH must match arity and unify with each dest column. UPDATE … SET col = rhs uses the same dest-typing rule. Guest v1 does not admit ON CONFLICT … DO UPDATE SET (canonical form is INSERT OR IGNORE → adapter ON CONFLICT DO NOTHING).
  • Operators: + - * / are INTEGER/REAL/NULL (result REAL if any REAL). % is INTEGER/NULL only (REAL is not portable). || / LIKE are TEXT/NULL. Comparisons unify both sides.
  • Helpers (typed arguments): replace(TEXT, TEXT, TEXT) → TEXT; substr(TEXT, INTEGER [, INTEGER]) → TEXT; round(numeric [, INTEGER]) → REAL; lower/upper/trim TEXT-ish; length TEXT|BLOB; json_extract TEXT path; sum INTEGER-only. Unknown helper names are grammar-denied; typecheck does not Ok(NULL) for unknown calls.
  • Derived names / CTEs: visible FROM keys are AS alias (or the table name). WITH c(x, y) AS (…) honors explicit CTE output names. Recursive CTEs infer the anchor first, publish those concrete types into self-scope, then typecheck recursive arms and unify every arm with the anchor. Anonymous or duplicate derived columns are rejected when they must be addressable (SELECT 1 in a CTE later qualified, AS (a, a), expression columns without AS). Top-level SELECT lists may omit AS (cells are positional). Postgres labels those items ?column?; the adapter uniquifies colliding labels. Explicit duplicate aliases (SELECT x, x) still fail closed. Correlated subqueries resolve qualified aliases against enclosing FROM maps (nested EXISTS may name i.user_id after FROM portal_identities i).
  • Schema fingerprint: CREATE TABLE IF NOT EXISTS is keyed on a structured schema IR (column order/names/types, identity, DEFAULTs, and admitted constraints — not normalized CREATE text). Catalog has the table and the fingerprint matches → no-op (no catalog/identity companions). Fingerprint differs → reject. Catalog lacks the table → INSERT catalog/ schema rows (no ON CONFLICT DO UPDATE); identity companions only on this path. Physical reconciliation on SQLite, D1, and PostgreSQL fail-closes: catalog absent + physical exists (do not adopt orphans), catalog present + physical missing, or catalog vs physical column IR disagree. D1 uses the same sqlite_master fingerprint path as SQLite. Catalog load is fail-closed (Result): RPC/adapter/shape errors are Err; missing catalog objects are Ok(empty). Rows page by keyset (table_name, ordinal) at min(negotiated maxResultRows, FIRST_PARTY_MAX_RESULT_ROWS) with the binding cancel and deadline forwarded. Malformed cells / unknown sql_type fail closed.
  • Runtime semantics (portable): / and % by zero yield NULL. The host rewrites the divisor as NULLIF(x, 0) unless it is already structurally NULLIF(<expression>, 0). A NULLIF whose second argument is not 0 (for example NULLIF(0, 1), which evaluates to 0) still gets an outer guard. Adapters must not repeat this rewrite. INTEGER + - * and abs in DML/query SQL that would overflow i64 yield NULL (proof-directed CASE wraps; guests must not depend on wraparound or a backend error). Overflow wrapping is not applied to CREATE TABLE CHECK expressions; INTEGER + - * abs in CHECK is rejected at admission. round halfway rounds away from zero (round(1.5)=2, round(2.5)=3) via Postgres NUMERIC. substr is 1-based; portable vectors use start ≥ 1 and length ≥ 0. Negative/NULL substr indexes are outside the portable claim. Postgres substr(text, int, int) does not accept bigint, and a bound INTEGER parameter is bigint, so lowering clamps the start and length into the int4 range and then casts them to INTEGER. A length above 2³¹−1 still means the rest of the string. The argument is evaluated once, and NULL stays NULL. SQLite keeps substr unchanged. length of TEXT is characters; replace / lower / abs of values in range follow engine defaults locked by shared vectors. Host dispatch-latency SQL may call julianday(TEXT) → REAL; Postgres lowering rewrites that exact expression to EXTRACT(EPOCH …). Guests cannot call julianday (not in the binding helper allowlist).
  • DEFAULT must match the declared column type: integer literals for INTEGER, numeric for REAL, strings for TEXT, X'hex' for BLOB (kept in v1; Postgres DDL lowering rewrites DEFAULT X'deadbeef' to DEFAULT decode('deadbeef', 'hex') after BLOB→BYTEA), TRUE/FALSE for BOOLEAN (not 1). Parenthesized DEFAULT (…) is rejected unless the inner expression is a matrix-legal CAST.
  • Admitted helper signatures already land in the universal DbValue domain (round → Float64, integer sum/count → Int64, avg → Float64); callers are not required to CAST for those shapes. Other computed numerics that would be engine NUMERIC should still CAST to INTEGER or REAL.
  • Conformance vectors compare typed DbValue cells (Int64 is not Float64(n) or Text("n")). SQLite expression columns may still be DbType::Unspecified; Postgres OID mapping should be Int64 / Text.

Helpers (canonical)

Canonical Arity Wire Meaning
IFNULL(a, b) 2 (inputs) First non-null
json_extract(col, '$.path') 2 text JSON path extract
json_object(k, v, …) even 2–32 text Object constructor (key/value pairs). Portable maximum is 16 pairs (32 arguments), matching D1 maxFunctionArgs. Adapters must not emulate larger calls with json_patch.
json_valid(expr) 1 int64 0/1 Predicate (= 0 / = 1)
min(a, b, …) / max(a, b, …) 2+ (inputs) Scalar least/greatest. Any NULL argument yields NULL (NULL-poison). One-arg MIN/MAX stay aggregates.
round(x) / round(x, n) 1–2 float64 Round; Postgres CASTs through NUMERIC onto DOUBLE PRECISION.
avg(x) 1 float64 Average
sum(x) 1 int64 Integer aggregate (REAL-sum portability is not claimed)
count(*) / count(x) 1 int64 Count

Wrong arity is rejected before dispatch, including IFNULL/COALESCE/NULLIF (2 / 2+ / 2), abs/sum/avg/count/length/lower/upper/json_valid (1), and even-arity json_object (2–32 arguments, 16 key/value pairs). Postgres lowering maps IFNULL / json_* / 2+-arg min/max to COALESCE, jsonb operators, (json_build_object(…)::text) (nested json_object values stay json so they nest as objects), IS [NOT] JSON, and LEAST/GREATEST with an explicit NULL guard (CASE WHEN a IS NULL OR b IS NULL … THEN NULL ELSE LEAST/GREATEST(…) END). SQLite/D1 execute those canonical forms directly after the unique-conflict INSERT OR IGNORE rewrite. MAX(attempt_count, 1) in host plans is the same 2-arg scalar form.

Plugin-owned database bindings (GuestSqlPolicy::binding_owned) authorize these helpers plus portable scalars (abs, cast, coalesce, length, lower, nullif, replace, substr, trim, upper). hex is not portable (PostgreSQL has no hex()); it remains available to library guests. SQLite-only names (strftime, typeof, group_concat, iif, instr, quote, total, date, datetime, time, json_array) are denied so a binding cannot depend on a dialect the contract does not guarantee. See Isolated plugin database bindings.

ORDER BY

Default NULL ordering matches SQLite: NULL sorts as smallest (ASC NULLS FIRST, DESC NULLS LAST). The host desugars omitted NULLS into explicit clauses before typecheck. Explicit NULLS FIRST / NULLS LAST is admitted and left unchanged. Adapters must not repeat this rewrite.

Host library pages order titles with COLLATE NOCASE (ASCII case-fold, then code points). That spelling is admitted on ORDER BY and CREATE INDEX keys. Any other collation name is rejected. Postgres lowering rewrites the fold to lower(ident COLLATE "C") only in those key lists. A WHERE predicate that still contains COLLATE NOCASE is left unchanged.

Postgres CREATE INDEX keys each receive NULLS FIRST, matching the host ORDER BY desugar (NULL sorts as smallest). The check is the keyword NULLS, so an identifier such as nulls_flag still receives the clause. CREATE INDEX IF NOT EXISTS does not alter an index that already exists, so a database created before this lowering can keep a different NULL order until that index is rebuilt.

lower and upper of a TEXT column, a string literal, or a direct ? argument whose bind is TEXT or untyped are COLLATE "C" sites. A nested call (lower(substr(body, ?, ?))) and a parenthesized subquery do not collate their placeholders. An INTEGER, REAL, BLOB, or BOOLEAN bind is not a collate site, so Postgres does not see COLLATE on bigint. Postgres lower uses the argument collation's LC_CTYPE; without "C", a UTF-8 database locale folds É to é, which SQLite does not. A direct TEXT placeholder lowers to lower(($1 COLLATE "C")). The books expression index lowers to lower((uuid COLLATE "C")) on Postgres so the predicate matches the index. IF NOT EXISTS leaves an already-created lower(uuid) index unchanged until it is rebuilt. SQLite keeps lower(uuid).

A TEXT column compared with = to a placeholder, or with IN to a list of placeholders only (uuid IN (?, ?)), stays bare so the btree collation matches the UNIQUE(uuid) index. A literal in that comparison or list is still collated. Postgres DDL (lower_canonical_ddl_to_postgres and binding schema batches) fails closed when a CREATE INDEX COLLATE NOCASE tie-break or a lower/upper column argument cannot be typed from the catalog or an earlier CREATE TABLE in the same batch. An untyped key is not emitted as a different index. Query entry points (lower_canonical_sql, lower_canonical_to_postgres) stay infallible: an untyped index key sent through them remains bare.

TEXT ORDER BY is binary / code-point order. Postgres lowering applies COLLATE "C" only at TEXT expression spans recorded on the statement proof (comparisons, scalar min/max, ORDER BY keys, LIKE / NOT LIKE, lower/upper). INTEGER/REAL/BOOLEAN/BLOB expressions never receive COLLATE "C". json_extract path string arguments are omitted from the proof (so the path stays a bare literal for helper lowering); a comparison or ORDER BY on a '$.…' string still receives "C". Host-authored ORDER BY TEXT uses the same annotations. SQLite/D1 need no COLLATE rewrite (BINARY default). The conformance suite uses ASCII and non-ASCII vectors. Locale-sensitive Unicode order is outside v1.

Canonical LIKE is case-sensitive. Grammar keeps LIKE and still rejects ILIKE and ESCAPE. SQLite/D1 adapters rewrite LIKE/NOT LIKE to GLOB with bind-safe replace (escape [ * ?, then %→* _→?). Do not use PRAGMA case_sensitive_like. Guests cannot write GLOB.

Result semantics

  • rowsAffected: mandatory (affectedRows capability). Reports rows changed by DML; INSERT OR IGNORE / ON CONFLICT DO NOTHING that insert zero report 0.
  • RETURNING: mandatory (returning capability). Column order and names come from engine metadata; empty results still expose column metadata.
  • Transactions: one ExecuteRequest is one atomic batch. Failure before commit rolls back all statements in the request.
  • Errors: stable categories (conflict, deadline, invalidParams, internal, …). Ambiguous commit / lost response uses durable receipt replay of the original ExecuteReply. A batch that only reads stores a receipt without its rows and re-runs the reads on replay, so a read-only result is bounded by the result budget it was read under, not by the 64 KiB replay payload. A request or result over a negotiated cap is limitExceeded: its message is limit exceeded: <cap> is <n>; used <m> and its details JSON carries { "limit", "cap", "used" }, so callers branch on the cap name instead of matching text. Every emitter (adapter result capping, host admission, reply validation, the SDK request-size check) uses that one shape.
  • Bounds: maxResultRows, maxResultBytes, maxAtomicResultBytes, maxRequestBytes fail closed before commit / dispatch; a streamed read fails closed at maxStreamedResultRows / maxStreamedResultBytes instead. maxLoweredStatementBytes (0 unspecified) is compared to a proof-aware sqlite-family lowering upper bound after ResolvedStatement exists. D1 advertises 100000 for that ceiling; maxPayloadBytes remains a proven canonical cap so mechanical-only lowering of an admitted statement stays within 100 KiB.

Conformance admission

A new SQL adapter is admitted when it:

  1. Negotiates DbCapabilities with sqlContractVersion >= 1 and required semantic flags/limits (not by matching a plugin id or diagnostic engine).
  2. Executes the sql_v1 corpus (lowering + shared behavioral vectors) without host library changes keyed on adapter identity.
  3. Keeps sqlFamily / dialect on DbBootstrap (host-private bootstrap metadata) only — not on typed DbCapabilities. DbCapabilities @17 is pluginDatabases, @18–@20 are numeric caps, @21 is maxLoweredStatementBytes, and @22/@23 are consistentBackupRead / atomicUnitRestore. Reports any diagnostic engine string on DbBootstrap; the host always emits canonical ? SQL. Architecture lint forbids planner reads of bootstrap fields and host calls to physical lowering helpers.

Source: docs/plugins/sql-contract/v1.md at main · View as Markdown