rdbms-playground/docs/plans/20260525-adr-0035-sql-ddl-4a2.md

Plan: ADR-0035 Phase 4, sub-phase 4a.2 — per-column CHECK/DEFAULT + composite UNIQUE

The constraint slice's first half. Adds, to advanced-mode SQL CREATE TABLE, the constraints that need no new internal table: per-column CHECK (<expr>) and DEFAULT <expr> (via the ADR-0031 sql_expr surface, stored/echoed as raw SQL text), and composite UNIQUE (a, b). Table-level / multi-column CHECK is 4a.3 (it needs a new __rdbms_* metadata table — SQLite has no PRAGMA for CHECK). Builds directly on the 4a SqlCreateTable command + grammar.

1. Baseline

• Tests: 1739 passing, 0 failing, 0 skipped, 1 ignored; clippy clean. Branch main, last commit 631074f (4a). 4a.2 starts here.

2. Decisions locked with the user (do not re-litigate)

1. Scope (2026-05-25): 4a.2 = per-column CHECK/DEFAULT + composite UNIQUE(a,b). No new internal table. Table-level / multi-column CHECK → 4a.3 (new __rdbms_* metadata table).
2. CHECK/DEFAULT are stored as raw SQL text, not a compiled Expr: sql_expr is validate-only (no AST). Per-column CHECK reuses the existing __rdbms_playground_columns.check_expr column; DEFAULT round-trips via the engine's native PRAGMA table_info (dflt_value) — both echoed verbatim by schema_to_ddl.
3. Single-column table-level UNIQUE(a) normalises into the column's unique flag (so it round-trips via the existing single-column path, read_unique_columns); composite UNIQUE(a,b) is a new TableSchema.unique_constraints field, detected on read via the UNIQUE-constraint index (PRAGMA index_list origin u, >1 column).

3. Phase 1 — Requirements checklist (4a.2)

Functional

• Column constraint DEFAULT <sql_expr> parses (advanced mode) and is stored/emitted as raw SQL; round-trips via PRAGMA table_info.
• Column constraint CHECK (<sql_expr>) parses and is stored as raw SQL in __rdbms_playground_columns.check_expr; round-trips.
• CHECK/DEFAULT accept the full sql_expr surface (the same fragment WHERE/projections use), not the DSL subset.
• Table element UNIQUE (<col>, …): single-column normalises into the column's unique; composite (≥2) → unique_constraints.
• Composite UNIQUE emitted in the create DDL and the rebuild DDL (schema_to_ddl); detected by read_schema_snapshot.
• The 4a "not yet supported" parse-rejection is lifted for these forms (the grammar now admits them); table-level/multi-column CHECK still rejected (→ 4a.3).
• One undo step; structural execution reuses do_create_table.
• Engine-neutral errors; STRICT preserved.

Cross-cutting

• Round-trip: a table with per-column CHECK/DEFAULT and a composite UNIQUE survives save → load → rebuild (DDL + enforcement).
• history.log / replay unchanged (these are part of the same create write command).

Testing (ADR-0008 four tiers)

• Tier 1: builder tests — CHECK/DEFAULT raw text captured verbatim; composite vs single UNIQUE routing; the full sql_expr surface parses; table-level/multi-column CHECK still rejected.
• Tier 3 (tests/sql_create_table.rs): worker round-trip — CHECK enforced (a violating insert fails), DEFAULT applied (an omitted column gets it), composite UNIQUE enforced (dup rejected); rebuild preserves all three.

4. Architecture & design

4.1 Grammar (src/dsl/grammar/sql_create_table.rs)

• Column constraints — extend COL_CONSTRAINT_CHOICES with:
  • DEFAULT <value> where <value> is a literal (number / string / null / true / false) or a parenthesised ( <sql_expr> ) — not a bare sql_expr. This matches standard SQL (a complex default must be parenthesised) and resolves a real ambiguity found in testing: a bare sql_expr greedily consumes a following NOT (as NOT IN/NOT LIKE/NOT BETWEEN), breaking the common DEFAULT 0 NOT NULL. The parens give the expression a clean right edge. (See §6.3.)
  • CHECK ( <sql_expr> ): Seq[ Word("check"), Punct('('), Subgrammar(&sql_expr::SQL_OR_EXPR), Punct(')') ] — already paren-bounded, so no ambiguity.
• Table element — extend ELEMENT_CHOICES with table-level UNIQUE ( col, … ): Seq[ Word("unique"), Punct('('), Repeated(uniq_column, ',', 1), Punct(')') ]. (Distinct ident role, e.g. unique_column, so the builder routes it separately from pk_column.) No table-level CHECK element (→ 4a.3).
• Column-validation in CHECK at create time — verify by test (§6.1): the sql_expr column refs name columns being defined, which don't exist in the schema cache yet. Confirm they don't raise an unknown-column [ERR]; mirror whatever the simple-mode expr-based CHECK does.

4.2 Raw-text capture (the load-bearing mechanism)

MatchedItem carries span: (usize, usize) byte offsets into the source (confirmed via build_sql_insert, which slices source by a keyword's span). The builder (build_sql_create_table, which already receives source) captures each CHECK/DEFAULT expression's raw text as source[first_expr_terminal.span.0 .. last_expr_terminal.span.1]:

• CHECK ( … ) — paren-delimited: take the text between the matched ( and ) terminals.
• DEFAULT <expr> — the sql_expr match is maximal, so its terminals are exactly the default expression; take from the first expr terminal after the default keyword to the last before the next element boundary (comma / ) / next constraint keyword).

4.3 Column representation for raw CHECK/DEFAULT (implementer call)

ColumnSpec.check: Option<Expr> / default: Option<Value> can't hold raw SQL. Chosen: add check_sql: Option<String> and default_sql: Option<String> to ColumnSpec (the advanced-mode raw-text alternative). do_create_table prefers the raw _sql field when present, else compiles the Expr/Value (simple-mode path). ColumnSpec::new sets them None; struct-literal sites get check_sql: None, default_sql: None (compiler-found). This keeps SqlCreateTable { columns: Vec<ColumnSpec>, … } and reuses do_create_table rather than forking it.

4.4 Composite UNIQUE end-to-end

• Command::SqlCreateTable gains unique_constraints: Vec<Vec<String>>.
• do_create_table + schema_to_ddl emit UNIQUE (<cols>) table clauses (insertion points: after the table-level PK clause, before FK / STRICT).
• TableSchema.unique_constraints: Vec<Vec<String>> + RawTable.unique_constraints (#[serde(default)], optional-on-read); write_table emits only when non-empty.
• read_schema_snapshot detects them: read PRAGMA index_list origin u + index_info; single-column → ColumnSchema.unique (existing read_unique_columns path), multi-column → unique_constraints (lift the current len() == 1 filter that drops composites).

4.5 Worker / undo / persistence

Same as 4a: one create = one undo step (snapshot_then); structural execution; finalize_persistence writes yaml/CSV/journal. No new Request variant (still SqlCreateTable).

5. Out of 4a.2 scope

• Table-level / multi-column CHECK → 4a.3 (new metadata table).
• FK (4b); DROP (4c); indexes (4d); ALTER (4e–4h).

6. Open items / implementer calls

1. CHECK column-validation at create time — verify (test) that sql_expr column refs to columns-being-defined don't raise an unknown-column [ERR]. If they do, the grammar must suppress schema-existence checks in the CREATE-TABLE CHECK context (as the simple-mode path effectively does). Resolve during step 1.
2. DEFAULT grammar — resolved during implementation (§4.1). A bare DEFAULT <sql_expr> greedily ate a following NOT (start of NOT IN/LIKE/BETWEEN), so DEFAULT 0 NOT NULL failed to parse (caught by a builder test). Fixed by restricting a bare default to a literal, with complex defaults parenthesised (DEFAULT (expr)) — standard SQL, and the parens bound the expression. The raw-text capture keeps the parens so it re-emits as valid SQL.

7. Devil's Advocate review of this plan

• Raw text vs compiled — round-trip safe? CHECK raw text → the same check_expr column simple mode uses (echoed verbatim by schema_to_ddl); DEFAULT → engine PRAGMA. Both reuse proven round-trip paths. The new piece (composite UNIQUE) gets explicit rebuild tests. ✓
• Reuse vs fork? do_create_table is still the single executor; the check_sql/default_sql fields add a branch, not a fork. ✓
• Single vs composite UNIQUE consistency? Single normalises to column.unique so read-back (which maps single-column origin-u to column.unique) round-trips identically — no asymmetry (the 4a lesson). ✓
• Silent scope creep? Table-level CHECK is explicitly out (4a.3), rejected by the grammar, not silently half-done. ✓

8. Implementation sequence (test-first)

1. sql_expr CHECK column-validation probe (§6.1) → settle the grammar's schema-check behaviour before building the shapes.
2. Grammar + builder, CHECK/DEFAULT — Tier-1 tests (raw text captured verbatim; full sql_expr surface; still-rejected table-CHECK) → red → add the constraint shapes + raw-text capture + ColumnSpec.check_sql/default_sql + do_create_table branch → green.
3. Grammar + builder, composite UNIQUE — Tier-1 (single→column, composite→unique_constraints) → red → add the UNIQUE(…) element + Command/do_create_table/schema_to_ddl emission → green.
4. Persistence round-trip — extend TableSchema + YAML + read_schema_snapshot composite-UNIQUE detection → Tier-3 round-trip tests (CHECK enforced, DEFAULT applied, composite UNIQUE enforced; survive rebuild) → green.
5. Full sweep — cargo test (no regressions) + cargo clippy --all-targets -- -D warnings; engine-neutral string audit.
6. Docs — requirements.md Q1 note; ADR §13 already records 4a.2.

9. Exit gate

• All §3 checklist items satisfied; four tiers green, zero skips; no regression from the 1739 baseline; written DA pass; clippy clean.