IF Commons GitHub

sql-introspect

0055Active

0055-sql-introspect · owner if · domain data

Requirements

FR-001checkpassing

introspect MUST read, through a driver and changing nothing, the tables of a database with their columns (the type the engine reports, nullability, default), primary key, unique constraints, foreign keys (over more than one column too) and indexes, on SQLite, DuckDB and PostgreSQL, and MUST leave out the tables it is told to.

Enforced (check): .github: cd commons/data && bun run verify — suite/introspect.test.ts: a database made from a model is read back as what the model says: every table, column, nullability, primary key, foreign key, unique constraint and index, on each engine, and the drift from the model is none; suite/introspect.test.ts: composite keys, unique constraints, indexes, foreign keys over more than one column and defaults are read as they were written, on each engine

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

Written in: data/sql/dialects/duckdb/observe.ts, data/sql/dialects/postgres/observe.ts, data/sql/dialects/sqlite/observe.ts, data/sql/lifecycle/introspect.ts

Tests: passed: suite/introspect.test.ts a database made from a model is read back as what the model says: every table, column, nul; passed: suite/introspect.test.ts composite keys, unique constraints, indexes, foreign keys over more than one column and de

Open: issue data 00012: Two readers of a database's catalog: convergence's `catalog` and the dialects' `observe`

Cited by: sql/physical/dialect

FR-002checkpassing

expectedOf MUST write what a logical schema says a database holds, as introspect reads it, and a database made from the schema MUST show no drift from it on each engine.

Enforced (check): .github: cd commons/data && bun run verify — suite/introspect.test.ts: a database made from a model is read back as what the model says: every table, column, nullability, primary key, foreign key, unique constraint and index, on each engine, and the drift from the model is none

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

Written in: data/sql/lifecycle/introspect.ts

Tests: passed: suite/introspect.test.ts a database made from a model is read back as what the model says: every table, column, nul

FR-003checkpassing

drift MUST name every difference: a table or column that is missing or stray, a type of another family, a nullability, a primary key, and a foreign key, unique or index that is missing; a database of another version of the model and one changed by hand MUST be told apart.

Enforced (check): .github: cd commons/data && bun run verify — suite/introspect.test.ts: drift names what differs: a table or column that is missing or stray, a type of another family, a nullability, a key, a foreign key, a unique or an index that is missing; a database of another version of the model, and one that was changed by hand, are told apart

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

Written in: data/sql/lifecycle/introspect.ts

Tests: passed: suite/introspect.test.ts drift names what differs: a table or column that is missing or stray, a type of another fa

FR-004checkpassing

adopt MUST make a logical schema of what was read (names as words, logical types, keys, foreign keys, uniques, indexes, defaults), MUST report each column whose type is of no known family by its name, and the schema MUST make the same database again on its own engine and on each of the others.

Enforced (check): .github: cd commons/data && bun run verify — suite/introspect.test.ts: a database that was not modelled is adopted as a logical schema that makes the same database again on its own engine, and on each of the others; a type of no known family is an error that names it

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

Written in: data/sql/lifecycle/adopt.ts

Tests: passed: suite/introspect.test.ts a database that was not modelled is adopted as a logical schema that makes the same databa

Out of scope

  • Views, routines, grants, triggers and check constraints: what is read is the structure of the tables.

Edge cases

  • A column of an engine type of no known family is an error of adopt, per FR-004.
  • Names are adopted as words, so a camel-case name is written in the case style of the naming, per FR-004.

Assumptions

  • The engines of the tests are SQLite, DuckDB and PGlite (PostgreSQL).

Open questions

  • None.

Key entities

  • Observed - what a database holds: tables, columns, keys, constraints and indexes.

Success criteria

SC-001success criterion

Each requirement is proved by a test on real engines and bun run verify is green.

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/lifecycle/adopt, sql/lifecycle/introspect, sql/physical/dialect

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