rdbms-playground/docs/adr/0014-data-operations-and-value-model.md

ADR-0014: Data operations, value literals, and the auto-show pattern

Status

Accepted

Context

Schema operations (ADRs 0002, 0005, 0011, 0013) gave us tables, columns, and relationships. Without INSERT / UPDATE / DELETE, foreign-key behaviour is observable but not demonstrable — a learner can't yet trigger a CASCADE or watch a constraint catch a bad write. C5 closes that gap.

Several coupled questions:

• Value literals. How does the user write '2025-01-15' for a date column versus 42 for an int column? What gets validated where?
• Safe defaults for destructive operations. UPDATE and DELETE without WHERE are classic foot-guns.
• Auto-generation of shortid. T2 commits to client-side generation at insert time, which now becomes load-bearing.
• FK error clarity. SQLite reports FOREIGN KEY constraint failed with no detail; pedagogically that's nearly useless.
• Showing data back to the user. Without a SELECT-like surface, the user has no way to see what changed.

Decision

Grammar

insert into <Table> [(<col>, <col>...)] values (<val>, <val>...)
update <Table> set <col>=<val>[, <col>=<val>...] (where <col>=<val> | --all-rows)
delete from <Table>                              (where <col>=<val> | --all-rows)
show data <Table>

• INSERT short form (insert into T values (...)): values apply to non-auto-generated columns in schema declaration order. Serial columns are filled by SQLite; shortid columns are auto-generated by the executor.
• INSERT long form (with explicit column list): user controls exactly which columns receive values; auto-generated columns the user didn't list are still auto-filled.
• WHERE clause is required for UPDATE and DELETE by default. The --all-rows flag is the explicit opt-in to unfiltered operations, following ADR-0009 (-- reserved for opt-in flags). Specifying both WHERE and --all-rows is a parse error.
• WHERE this iteration is exactly <col>=<val>. Richer WHERE expressions (AND/OR/comparison/LIKE) are deferred — they are tracked as a future iteration and are intended as the bridge from DSL into real SQL fluency.
• show data <Table> joins the V5 show-family (with show table <Name>); auto-show after writes (below) means most users won't need to call it explicitly.

Value literals

The parser produces a small Value AST (Number(String), Text(String), Bool(bool), Null). Per-column-type validation lives in the executor where the schema is known:

User-facing type	Accepted literal
text	single-quoted string 'hello' ('' escapes a quote)
int	integer literal 42, -7
real	numeric literal 3.14, -0.5
decimal	numeric literal; stored as text to preserve precision
bool	true / false
date	quoted 'YYYY-MM-DD' (validated)
datetime	quoted `'YYYY-MM-DDTHH:MM:SS[.fff][Z
blob	DSL literal not supported this iteration
serial	normally omitted (auto-fill); explicit integer accepted
shortid	normally omitted (auto-generated); explicit base58 10–12
null	keyword null

Validation produces friendly errors that name the column and expected shape — e.g. "column Name expects a quoted string for text, got number".

Auto-generation for shortid

When an INSERT (in either form) does not provide a value for a shortid column, the executor calls the shortid generator and fills in a 10-character base58 value (no 0/O/I/l). Explicit values are accepted but validated against the same alphabet and length range (10–12 chars). The rand crate is the source of randomness.

FK error enrichment

SQLite reports FOREIGN KEY constraint failed without naming the offending constraint or value. The executor catches this class of error and appends the table's outbound relationships (via the metadata table from ADR-0013) to the message:

FOREIGN KEY constraint failed. Foreign keys on this table:
  - Orders.CustId → Customers.id
Check that each referenced value exists in the parent table.

Identifying the exact offending row is left to the H1 friendly error layer when that lands.

Auto-show after writes

INSERT, UPDATE, and DELETE successfully completing fetch the target table's full data and emit a DslDataSucceeded event carrying both rows_affected: Some(n) and the data view. The App renders both. Users see the result immediately without needing a follow-up show data command.

show data <Table> follows the same path with rows_affected: None.

The auto-show convention is reserved for DSL data ops. When advanced-mode SQL lands (Q1), arbitrary SELECT statements may opt out — large result sets shouldn't be implicitly inserted into the session log.

Tabular rendering

The data view is rendered as simple aligned-column text:

  id  | Name      | Email
  ----+-----------+-----------------
  1   | Alice     | a@b.com
  2   | Bob       | (null)

Pretty box-drawing renderings (with truncation, scroll indicators, wide-table handling) are deferred to V4. NULL cells render as (null) to be explicitly visible; booleans render as true/false despite their integer storage.

Consequences

• C5 is satisfied: INSERT/UPDATE/DELETE operate end-to-end with validation, auto-generation, FK enforcement, and visible feedback.
• T2 is satisfied: shortid auto-generation runs on insert.
• V2 partial: a usable tabular data view exists, with the pretty-rendering iteration still ahead.
• V5 partial: show data joins show table in the show family.
• H1 partial: FK-failure messages are enriched without introducing the full friendly-error layer.
• The --all-rows opt-in convention is now established for destructive-without-filter operations — future commands of the same shape (drop relationship --cascade?) follow the pattern.
• The runtime's CommandOutcome enum is extended with a Data variant. New data-emitting commands plug in there without reshaping the dispatch.
• Complex WHERE expressions (AND/OR/comparison/LIKE), bulk INSERT, ORDER BY, LIMIT, JOIN, and SELECT in advanced mode are explicitly out of scope for this iteration; richer DSL WHERE is the bridge iteration toward Q1's full SQL handling.

See also

• ADR-0005 (column types — value-literal mappings here mirror the storage choices)
• ADR-0009 (DSL command syntax conventions — -- flag rule)
• ADR-0011 (FK column type compatibility — used during the validation that runs before writes)
• ADR-0013 (relationships and rebuild-table — the FK metadata used by the error-enrichment path)