feat: the embedding-API facade — Connection, Statement, typed rows (spec 013 Reqs 2/3/7)

[dpsiderius]

Description

The engine half of spec 013 is done — streaming (#683), Send result rows
(#688), a rows-changed count (#692), and the corruption fix that made
adoption safe (#685). None of it is reachable as an API. There is no
Connection, no Statement, no src/api.rs. A consumer still
hand-assembles pager, header, Program and a positional Vec<Value>, which
is exactly what spec 013 exists to end.

This ticket is the core facade: spec 013 Requirements 2, 3, 7, and
Requirement 1's Connection::changes. It is the minimum that lets SQE delete
its glue module.

The real cost is not the API surface — it is that statement dispatch lives in the binary

This is the finding that sizes the ticket. To prepare an arbitrary SQL string
you must dispatch between three different compilers, and the code that
already does this is in src/bin/sqlite-rs/, not in the library:

piece	lives in	needed by facade
compile_select_program, SelectOutcome	src/bin/sqlite-rs/query.rs	yes — the SELECT route
derive_headers	src/bin/sqlite-rs/repl.rs	yes — by-name column access
parse_pragma_query, execute_pragma_query	src/bin/sqlite-rs/pragma_query.rs	no — see the scope correction below
compile_statement	src/codegen/dispatch.rs	already public — write/DDL only
leading_keywords	src/codegen/dispatch.rs	already public

compile_statement handles write and DDL statements only; a SELECT
reaches the engine through compile_select_program with a resolved
TableSchema and loaded sqlite_stat1 stats. repl.rs::run_one_statement is
the only place that unifies them, and a library consumer cannot call it.

So the work is roughly: lift that dispatch into the library, then wrap it.
The wrapping is the easy half.

Scope correction: PRAGMA is not this ticket's problem

My first draft of this ticket had Connection::prepare dispatching the nine
introspection pragmas too, which would have pulled all 478 lines of
pragma_query.rs into the library. That is wrong — spec 013's own non-goals
say so:

The PRAGMA catalogue. plan.md V7 owns the list and its tiers;
Requirement 5 covers only what a pool sets and what durability requires.

And SQE's statement list (Req 6) contains no pragma at all: CREATE TABLE IF NOT EXISTS, a four-parameter INSERT, SELECT ... UNION, LIMIT 1 probes,
a conditional UPDATE, a DELETE.

So prepare dispatches two routes, not three, and
parse_pragma_query/execute_pragma_query stay in the CLI where they are.
A PRAGMA reaching prepare should return a clear "not supported by this
API, see V7" error rather than being quietly half-handled. Estimate revised
down accordingly.

Scope

New src/api.rs (plus src/api/*.rs submodules — no mod.rs, per #73):

• Connection::open(path) / open_with(path, OpenMode) — ReadOnly,
  ReadWrite, ReadWriteCreate. MUST NOT create a file when create was not
  asked for. DatabaseHeader::new_empty_page1 is already public
  (src/header.rs:319) and the CLI already uses it
  (src/bin/sqlite-rs/exec.rs:49); nothing offers it to a consumer. Locks
  release on drop, which Pager already does.
• Connection::prepare(sql) -> Statement — the unified dispatch above.
• Connection::changes() -> Option<u64> — feat: a rows-changed counter — the one spec 013 item a consumer cannot work around #692 made this a one-liner:
  store on Some, leave the stored value alone on None. That is
  sqlite3_changes()'s retention rule and the reason the engine returns
  Option rather than u64.
• Statement owning its compiled Program and parameter slots. Bind
  positional ?/?NNN (spec 009's Variable). Named forms (:name,
  @name, $name) MUST be rejected at prepare, not reach ADR-0015's
  always-NULL stub.
• Statement::next_row() -> Option<Row> built on
  vdbe::Execution::next_row, not on execute_with_db. spike: 014 embedding-API kernel — Send+Sync handle over a streaming VDBE #682's ordering
  finding: a facade retrofitted onto the materializing entry point cannot be
  made incremental afterwards. Dropping a partly-read statement releases its
  cursors.
• Row — typed access by index and by name over spec 008's storage
  classes.

Non-goals — each its own follow-up

• Requirement 4, the Send + Sync worker thread. Architectural, and it
  wraps this rather than mixing into it. Connection here is deliberately
  !Send: rows are Send as of feat: make Value Send by switching Text/Blob payloads from Rc to Arc #688, but Rc<RefCell<Pager>> is not, and
  the worker thread is the answer to that. Also carries an open design
  question for the consumer.
• Requirement 5 — transactions, the written durability contract, busy
  timeout and a retryable error variant. Note PRAGMA synchronous is already
  fully implemented (chore: PRAGMA synchronous is unimplemented, silently ignored #645, ADR-0036), so Req 5 is further along than spec 013
  claims.
• Requirement 6 — published-surface docs, stability policy, and the SQE
  consumer corpus family. The exit gate.
• Any sqlx driver. Out of tree per ADR-0041, so [dependencies] stays
  empty.

Acceptance Criteria

• Connection::open_with(path, ReadWriteCreate) on a missing path
  produces a database the pinned 3.53.4 oracle reads as an empty
  schema
• ReadWrite on a missing path fails and leaves no file behind
• One prepare, three bindings, three correct rows — and compilation
  happened once (assert on the retained Program, not on timing)
• SELECT, INSERT/UPDATE/DELETE and DDL all prepare through the
  same Connection::prepare — the dispatch-lift regression guard
• A PRAGMA through prepare returns a clear unsupported error naming
  V7, rather than being silently half-handled
• The CLI still behaves identically. compile_select_program moving
  into the library is a refactor of sqlite-rs exec/query/the REPL's
  core path; the existing CLI corpus tests are the regression evidence
• :name fails at prepare with an error naming the unsupported form,
  and never yields a NULL row
• A million-row table read ten rows deep then dropped: peak allocation
  independent of the table's row count (not proportional to ten —
  see docs: take over #678's embedding-API spec, reconciled with the tree (supersedes #678) #693's correction to Req 7), and the ten values match the oracle's
  first ten
• A dropped half-read Statement releases its cursors: a write on the
  same Connection immediately afterwards succeeds
• changes() survives an intervening SELECT and is zeroed by a
  DELETE that matched nothing
• tests/unit/api_*.rs per spec 013's Tests: links, and those links
  flip from (planned) to active in the same PR
• Nothing in the facade's own tests names pager, vdbe, codegen,
  dump or btree — the Req 6 scenario in miniature, as a design
  constraint on this ticket even though Req 6 is a non-goal
• Full suite green, make lint both clippy passes,
  make check-mod-files, make assurance with no dead links and
  Completeness moving (Reqs 2/3/7 flip from (planned))

Complexity

Estimate: medium
Reasoning: Revised down from large after the PRAGMA scope correction
above — that alone removed ~478 lines of CLI-shaped code from the lift.

The API surface is small and its design is settled: #682 prototyped and
measured it, and #693 corrected the spec's unsatisfiable acceptance
criterion. What remains non-trivial is lifting compile_select_program and
SelectOutcome out of src/bin/sqlite-rs/query.rs, which today return
Result<_, String> — a CLI-shaped error that a library API should not
expose, so the lift includes giving them a real error type. sqlite_stat1
stats loading and view resolution thread along with them. The CLI must
behave identically afterwards.

Depends on: #693 (the spec and its Req 7 correction), #690, #691, #692.
All four are open PRs; the work sits on a merge of them.

Refs: 013/Req-1, 013/Req-2, 013/Req-3, 013/Req-7, #682, #683, #688, #692