# SQLite binding — test inventory

Date: 2026-10-26

Status update 2026-10-27: parameter introspection
(`sqlite3_bind_parameter_count/name/index`), statement
metadata/diagnostics, BLOB error handling and extended constraint
codes are implemented.  Covered by `tests/SQLiteParams.mod`,
`tests/SQLiteIntrospection.mod`, `tests/SQLiteBlobErrors.mod`,
`tests/SQLiteConstraints.mod`, `tests/SQLiteBackup.mod`, the broad
`tests/SQLiteCapi3c.mod` integration test and
`showcase/SQLiteFeatureShowcase.mod`.

This note records which existing tests are useful for hardening the V3
SQLite binding under `bindings/sqlite3`, and which APIs they would drive.

## Sources scanned

| Source | Count | Notes |
|---|---:|---|
| `bindings/sqlite3/tests` | 14 | Current V3 tests: smoke, prepared, params, introspection, scalar, errors, BLOB, BLOB errors, constraints, backup, C API integration, connection, callback, hooks |
| sibling `m2SQLITE/tests` | 7 | Original GNU Modula-2 binding tests; closest existing coverage |
| sibling `m2SQLITE/sqlite-master/test` | 1,195 `.test` files | Upstream SQLite TCL regression suite; useful as a specification |
| `compiler/tests` | 806 files | Compiler/ABI tests; not SQLite-specific, but several guard binding prerequisites |

The upstream SQLite tests are TCL-based and cannot run directly against
the V3 binding.  Port individual `do_test` scenarios, not the test
harness.

## Current V3 binding tests

- `SQLiteSmoke.mod`
- `SQLitePrepared.mod`
- `SQLiteParams.mod`
- `SQLiteIntrospection.mod`
- `SQLiteScalar.mod`
- `SQLiteErrors.mod`
- `SQLiteBlob.mod`
- `SQLiteBlobErrors.mod`
- `SQLiteConstraints.mod`
- `SQLiteBackup.mod`
- `SQLiteCapi3c.mod`
- `SQLiteConnection.mod`
- `SQLiteCallback.mod`
- `SQLiteHooks.mod`

## Original m2SQLITE tests

| Test | Useful coverage | Status in V3 binding |
|---|---|---|
| `test_prepare.mod` | `bind_parameter_count/index`, named parameters, 64-bit bind, scalar round trip | Implemented in `SQLiteParams.mod` |
| `test_func.mod` | scalar/aggregate UDFs, auxdata, numeric type, `result_value`, 64-bit text/blob, zeroblob, error paths | Missing several callback/result APIs |
| `test_blob.mod` | read-only BLOB write error, reopen, backup API, backup to file | Read-only/bounds/expiry and memory backup covered; file backup remains |
| `test_codes.mod` | extended constraint codes (unique/not-null/PK/FK), `get_table` | Extended codes implemented; `get_table` remains |
| `test_exec.mod` | exec, change counters, rowid, error capture | Already covered by current V3 tests |
| `test_open.mod` | `open` / `open_v2` / close and `errcode` | Already covered by current V3 tests |
| `test_version.mod` | `sqlite3_libversion*` | Implemented in `SQLiteIntrospection.mod` |

## Upstream SQLite tests worth mining

### Highest priority

| File | Interesting cases | What it drives |
|---|---|---|
| `bind.test` | `bind-1.*` named params/unbound NULL/reset; `bind-9.*` high parameter numbers; `bind-10.*` parameter index/name; 32/64/double/NULL/text/UTF-16/clear sections | Basic coverage added; high parameter numbers and UTF-16 remain |
| `incrblob.test` | sections `incrblob-3.*` read-only write, `4.*` bad row/table/column/type opens, `7.*` handle invalidation | Covered by `SQLiteBlobErrors.mod` |
| `types.test`, `types2.test`, `types3.test` | storage classes, affinity, integer/real/text conversions | `column_type`, numeric columns, bound values |
| `null.test`, `nulls1.test`, `nulls2.test` | NULL in expressions, comparisons, result types | `bind_null`, `column_type`, `value_type` |
| `auth.test` | deny at prepare, `SQLITE_IGNORE` for reads, action arguments, authorizer replacement | authorizer arguments and return semantics |
| `busy.test`, `busy2.test` | lock contention, busy handler invocation, timeout | actual busy-handler behavior (current test only registers one) |
| `progress.test` | callback count, nonzero return aborts query, nested query from callback | progress callback return handling |
| `trace.test`, `trace2.test`, `trace3.test` | statement/profile/row/close masks and callback arguments | trace masks beyond `SQLiteTraceStmt` |

### Medium priority

| File | Interesting cases | What it drives |
|---|---|---|
| `func.test` … `func9.test` | function arg counts, NULLs, destructors, auxdata, aggregates, result types | aggregate/auxdata APIs, more result writers |
| `errors`/`errmsg.test` | error text and error offsets | `sqlite3_error_offset`, error-message behavior |
| `fkey1.test` … `fkey9.test` | foreign-key and not-null constraint codes | Implemented in `SQLiteConstraints.mod` |
| `openv2.test`, `uri.test`, `uri2.test` | open flags, URI filenames, read-only opens | `open_v2` flags and URI behavior |
| `limit.test`, `limit2.test` | `sqlite3_limit` | connection limits |
| `trans.test`, `trans2.test`, `trans3.test`, `rollback.test` | transaction boundaries and hooks | commit/rollback hook ordering |
| `backup.test`, `backup2.test`, `backup4.test`, `backup5.test` | online backup and incremental backup | Core API implemented; file backup remains |
| `attach.test`, `attach2.test` | multiple attached databases | BLOB `zDb` values, future helpers |

### Later / not useful yet

- `malloc*`, `*fault*`, `*ioerr*`: SQLite fault-injection harness.
- `wal*`, `shared*`, `lock*`: pager/WAL/concurrency internals.
- `window*`, `collat*`: APIs not exposed by the V3 binding yet.
- `select*`, `expr*`, `alter*`, `vacuum*`: SQL engine behavior, not binding behavior.
- `session`, `rbu`, `rtree`, `fts`: extension APIs.

## Compiler tests that matter as binding prerequisites

These are not SQLite tests, but they exercise compiler/ABI features the
binding relies on and should remain in the main gate:

- `compiler/tests/t_callback.mod` — top-level C callbacks
- `compiler/tests/cabi_scalars_prog.mod` — scalar C ABI
- `compiler/tests/cstr_prog.mod` — C strings
- `compiler/tests/cstruct_layout_prog.mod` — C record layout
- `compiler/tests/t_open_row_adr.mod` — `ADR` behavior
- `compiler/tests/t_libc2.mod` — raw C buffers
- `compiler/tests/t_byte.mod`, `t_real.mod`, `t_long.mod` — byte/REAL/LONGINT widths

## Recommended next test additions

### 1. `SQLiteParams.mod` — implemented 2026-10-27

Ported from `m2SQLITE/tests/test_prepare.mod` and `bind.test`.

Add APIs:

- `sqlite3_bind_parameter_count`
- `sqlite3_bind_parameter_name`
- `sqlite3_bind_parameter_index`

Cases:

- anonymous `?` parameters
- named `:name`, `@name`, `$name` parameters
- repeated named parameters
- unbound parameters read back as NULL
- `reset` and rebind reuse
- `clear_bindings`
- `LONGINT` value such as `9000000001`

### 2. `SQLiteBlobErrors.mod` — implemented 2026-10-27

Ported from `incrblob-3.*`, `incrblob-4.*` and `test_blob.mod`.

Cases:

- write to a read-only handle returns `SQLiteReadOnly`
- close after a read-only write error
- open nonexistent row/table/column
- open integer/real/NULL column as BLOB
- read/write outside bounds
- reopen behavior
- handle invalidation after a write to the same table

### 3. Extend `SQLiteHooks.mod`

Port from `progress.test` and `auth.test`.

Cases:

- progress callback returns nonzero and aborts the query
- progress callback with `nOps = 0` disabled
- authorizer `SQLiteAuthIgnore` on `SQLITE_READ` returns NULL
- authorizer action arguments
- busy handler actually invoked by a second connection

### 4. Extended-code tests — implemented 2026-10-27

Ported from `m2SQLITE/tests/test_codes.mod` into `SQLiteConstraints.mod`.

- `SQLiteConstraintNotNull` = 1299
- `SQLiteConstraintForeignKey` = 787
- `SQLiteConstraintCheck` = 275
- optionally trigger, rowid and datatype constraints

### 5. Later slices

- aggregate functions + auxdata (`test_func.mod`, `func*.test`)
- file-to-file backup coverage (`backup*.test`)
- `sqlite3_get_table` / `free_table`
- extension loading and WAL/checkpoint helpers
- deeper progress/authorizer/busy cases from upstream tests

## Related session note

See `docs/session-2026-10-26-sqlite-binding.md` for the binding work
completed in this session and the current verification state.

## Porting caveats

- Replace m2SQLITE's `libc.printf`/`HALT` style with the V3 test convention:
  set `ExitCode := 42` on success.
- m2SQLITE's `SQLiteUtils` helpers do not exist in the V3 binding; call
  the raw `SQLite` procedures directly.
- For a raw C pointer to a V3 array, use `ADR(a[0])`, not `ADR(a)`.
- V3 string literals in the current compiler can be truncated for long
  SQL text.  Keep individual literals short, or build the SQL through a
  character buffer/concatenation.  Adjacent string literal concatenation
  is not supported.
- The upstream tests are specifications, not a runnable harness for this
  binding.