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.