IF Commons GitHub

Ephemeral targets

0025Active

0025-sql-target · owner if · domain data

The build

FR-001checkpassing

targetBuild MUST make, from a logical schema, a dialect, a naming, a schema qualifier, the extensions' statement sources and rows (by table and column, by the words of their names), a build that holds the statements and the text of one SQL file, and MUST give errors as values for a table or a column that the schema does not have, a column a table requires (not optional and with no default) that a row leaves out, a primary key that is in the rows twice, and a cycle among tables of the foreign keys (a table that refers to itself is allowed).

Enforced (check): .github: cd commons/data && bun run verify — suite/target.test.ts: the file is a header, the structure, then rows with tables after the tables they refer to, rows in the order of their key, a fixed number to an INSERT, and the same bytes every time whatever the order the rows were given in; suite/target.test.ts: rows are checked when the file is made: an unknown table or column, a missing required column, a key twice, a column twice and a cycle among tables are errors as values

Proved by: data/suite/target.test.ts

Written in: data/sql/extensions/target/build.ts, data/sql/extensions/target/mod.ts

Tests: passed: suite/target.test.ts the file is a header, the structure, then rows with tables after the tables they refer to,; passed: suite/target.test.ts rows are checked when the file is made: an unknown table or column, a missing required col

FR-002checkpassing

The text MUST be a header (that it is generated and from which module, the dialect, the hash of the schema's state and the hash of the rows), the statements bind makes, then the rows: tables in the order of their foreign keys (a table after the tables it refers to, otherwise in the order of the schema), rows in the order of their primary key (numbers as numbers, text by its code units), batch rows to an INSERT (a hundred by default), every value written by the dialect.

Enforced (check): .github: cd commons/data && bun run verify — suite/target.test.ts: the file is a header, the structure, then rows with tables after the tables they refer to, rows in the order of their key, a fixed number to an INSERT, and the same bytes every time whatever the order the rows were given in

Proved by: data/suite/target.test.ts

Written in: data/sql/extensions/target/build.ts

Tests: passed: suite/target.test.ts the file is a header, the structure, then rows with tables after the tables they refer to,

FR-003checkpassing

The text MUST be the same bytes for the same source every time: the same text where rows are given in another order, and no value in it that comes from the clock, the machine or chance.

Enforced (check): .github: cd commons/data && bun run verify — suite/target.test.ts: the file is a header, the structure, then rows with tables after the tables they refer to, rows in the order of their key, a fixed number to an INSERT, and the same bytes every time whatever the order the rows were given in

Proved by: data/suite/target.test.ts

Written in: data/sql/extensions/target/build.ts

Tests: passed: suite/target.test.ts the file is a header, the structure, then rows with tables after the tables they refer to,

Making and keeping

FR-004checkpassing

materialize MUST make a database from a build, in one transaction that is rolled back where a statement fails, and give the error as a value.

Enforced (check): .github: cd commons/data && bun run verify — suite/target.test.ts: on three engines a database is made from the build in one transaction, with the rows as the source gives them, and a failure rolls everything back

Proved by: data/suite/target.test.ts

Written in: data/sql/extensions/target/make.ts

Tests: passed: suite/target.test.ts on three engines a database is made from the build in one transaction, with the rows as th

FR-005checkpassing

staleness MUST compare a committed file with what the source makes now and say whether it is current, and where it is not, the first line that differs, so that a file that was edited by hand or a source that changed without the file being made again fails a check.

Enforced (check): .github: cd commons/data && bun run verify — suite/target.test.ts: a committed file is current or it is stale at a line, and a table with more rows than a limit is reported

Proved by: data/suite/target.test.ts

Written in: data/sql/extensions/target/make.ts

Tests: passed: suite/target.test.ts a committed file is current or it is stale at a line, and a table with more rows than a li

FR-006checkpassing

sizeFindings MUST report each table of a build with more rows than a limit, so that an ephemeral target does not grow into a system of record unnoticed.

Enforced (check): .github: cd commons/data && bun run verify — suite/target.test.ts: a committed file is current or it is stale at a line, and a table with more rows than a limit is reported

Proved by: data/suite/target.test.ts

Written in: data/sql/extensions/target/build.ts

Tests: passed: suite/target.test.ts a committed file is current or it is stale at a line, and a table with more rows than a li

Out of scope

  • Reading the committed file from disk: the caller reads it and writes it.
  • Binary files of a database (.sqlite, .duckdb): a database is made from the text.
  • Changing a durable database: that is the lifecycle.

Edge cases

  • A table with no rows: structure only, per FR-002.
  • A table that refers to itself: its rows in the order of their primary key; a row must not refer to a later one, per FR-001.
  • A row that gives a column twice by two spellings of its name: the later wins is not allowed; it is an error value, per FR-001.

Assumptions

  • The database that is made is empty.

Open questions

OQ-1open question

Whether rows of a self-referencing table are ordered so that a referenced row comes first.

Key entities

  • Build - the statements and the text of one SQL file.
  • Staleness - whether a committed file is what the source makes.

Success criteria

SC-001success criterion

A database is a build product that can be thrown away and made again, and a pull request shows what changed in it.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Cited by

Data, sql/extensions/target/build, sql/extensions/target/make

Titles, headings, exports, tables, columns and requirements. Esc closes.