IF Commons GitHub

The physical schema

0007Active

0007-physical · owner if · domain data

Names

FR-001checkpassing

A case style MUST be a value that writes a list of words as a name; snake, camel and pascal MUST be the library's own, snake case MUST be the default, and an extension MUST be able to add one through the engine's point physical.caseStyle; the words of a valid name of governance written in camel MUST be that name.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: the case style writes every name, and a snake case schema is the default; suite/physical-case.test.ts: snake, camel and pascal write words as names, a digit joining without a capital; suite/physical-case.test.ts: the words of a valid name written in camel are that name, for generated names; suite/physical-case.test.ts: an extension adds a case style through the engine, and snake case is the default naming

Proved by: data/suite/physical-bind.test.ts, data/suite/physical-case.test.ts

Written in: data/sql/physical/case.ts

Tests: passed: suite/physical-bind.test.ts the case style writes every name, and a snake case schema is the default; passed: suite/physical-case.test.ts an extension adds a case style through the engine, and snake case is the default naming; passed: suite/physical-case.test.ts snake, camel and pascal write words as names, a digit joining without a capital; passed: suite/physical-case.test.ts the words of a valid name written in camel are that name, for generated names

Cited by: Data, sql/physical/case

FR-002checkpassing

The stored value of a code (the code of a code table, a value of a check, a default) MUST be written by the case style of the codes, by default the style of the names, and MAY be the code's name as it is (0005-naming FR-006).

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: reference rows are upserts with the codes written by the style of the codes, in snake case, by another style, or as their names; suite/physical-case.test.ts: a code is stored by the style of the codes by default, by another style, or as its name

Proved by: data/suite/physical-bind.test.ts, data/suite/physical-case.test.ts

Written in: data/sql/physical/case.ts

Tests: passed: suite/physical-bind.test.ts reference rows are upserts with the codes written by the style of the codes, in snake case; passed: suite/physical-case.test.ts a code is stored by the style of the codes by default, by another style, or as its name

Cited by: Data, sql/physical/case

FR-003checkpassing

A written name longer than its dialect allows MUST be an error value naming it, and two elements whose written names are equal (two tables, two columns of a table, two constraints or indexes) MUST be an error value naming both.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: a name that is too long, and two names that are alike once written, are error values with the elements that clash

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/express.ts, data/sql/physical/statement.ts

Tests: passed: suite/physical-bind.test.ts a name that is too long, and two names that are alike once written, are error values with

Cited by: SQL features as extensions

The dialect

FR-004checkpassing

A dialect MUST be a value that says how its engine writes: how a name is quoted, how a literal is written, the type of each logical type, how the moment of the insert is written as a default, which checks it can express (a pattern only where it has a regular expression), whether it has COMMENT ON, and the longest name; PostgreSQL, SQLite and DuckDB MUST be provided, each as an extension in sql/dialects/ that contributes its dialect through the engine's point physical.dialect.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: the DDL of a small domain, written out for each dialect, with the types of each engine; suite/physical-bind.test.ts: the three dialects are extensions that contribute themselves through the engine

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/dialects/duckdb/mod.ts, data/sql/dialects/postgres/mod.ts, data/sql/dialects/sqlite/mod.ts, data/sql/physical/dialect.ts

Tests: passed: suite/physical-bind.test.ts the DDL of a small domain, written out for each dialect, with the types of each engine; passed: suite/physical-bind.test.ts the three dialects are extensions that contribute themselves through the engine

Cited by: Data, sql/dialects/postgres/mod, sql/physical/dialect

FR-005checkpassing

A logical element that a dialect cannot express MUST be returned as an error value that names the element and why, and bind MUST NOT write SQL that its engine would refuse at load.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: a pattern is a check where the engine has a regular expression, and an error value naming the check where it has none

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/dialect.ts, data/sql/physical/express.ts

Tests: passed: suite/physical-bind.test.ts a pattern is a check where the engine has a regular expression, and an error value naming

Binding

FR-006checkpassing

bind MUST make from a logical schema, a dialect and a naming the statements that create the schema, or all its errors together; a table MUST follow every table its foreign keys refer to, the indexes MUST follow the tables, and the reference data MUST follow both; a cycle of foreign keys that the dialect cannot break MUST be an error value.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: a table follows the tables its foreign keys refer to, indexes follow tables, data follows both, and the groups are in their order; suite/physical-bind.test.ts: a cycle of foreign keys is added afterwards where the engine can add a constraint, and is an error value where it cannot

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/statement.ts

Tests: passed: suite/physical-bind.test.ts a table follows the tables its foreign keys refer to, indexes follow tables, data follows ; passed: suite/physical-bind.test.ts a cycle of foreign keys is added afterwards where the engine can add a constraint, and is

FR-007checkpassing

A table MUST be made by CREATE TABLE IF NOT EXISTS with each column's type, NOT NULL and default, its primary key (inline where it is one column), and named constraints for its foreign keys, uniques and checks; an index MUST be made by CREATE INDEX IF NOT EXISTS.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: the DDL of a small domain, written out for each dialect, with the types of each engine; suite/physical-bind.test.ts: a table follows the tables its foreign keys refer to, indexes follow tables, data follows both, and the groups are in their order; suite/physical-bind.test.ts: the case style writes every name, and a snake case schema is the default

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/express.ts

Tests: passed: suite/physical-bind.test.ts the case style writes every name, and a snake case schema is the default; passed: suite/physical-bind.test.ts the DDL of a small domain, written out for each dialect, with the types of each engine; passed: suite/physical-bind.test.ts a table follows the tables its foreign keys refer to, indexes follow tables, data follows

FR-008checkpassing

The rows of a code table MUST be loaded by an upsert that, run twice, leaves the same rows, with the codes written by FR-002.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: reference rows are upserts with the codes written by the style of the codes, in snake case, by another style, or as their names; suite/physical-engines.test.ts: ${name}: the reference data is loaded, in the codes of the model it replaces, and loaded twice leaves the same rows

Proved by: data/suite/physical-bind.test.ts, data/suite/physical-engines.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/upsert.ts

Tests: passed: suite/physical-bind.test.ts reference rows are upserts with the codes written by the style of the codes, in snake case; passed: suite/physical-engines.test.ts duckdb: the reference data is loaded, in the codes of the model it replaces, and loaded tw; passed: suite/physical-engines.test.ts postgres: the reference data is loaded, in the codes of the model it replaces, and loaded ; passed: suite/physical-engines.test.ts sqlite: the reference data is loaded, in the codes of the model it replaces, and loaded tw

Cited by: Data, sql/physical/upsert

FR-009checkpassing

Every statement MUST carry the identities of the logical elements it was made from, so that what is in a database is traced to its logical element and, through it, to governance.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: every statement carries the logical elements it was made from

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/statement.ts

Tests: passed: suite/physical-bind.test.ts every statement carries the logical elements it was made from

FR-010checkpassing

render MUST give the same text for the same schema, dialect and naming every time, one statement ending in ;, and bind MUST keep what an extension adds apart from the core's by group, in the order the groups say.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: the same input gives the same text, and an extension adds statements by group, being given the written names

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/statement.ts

Tests: passed: suite/physical-bind.test.ts the same input gives the same text, and an extension adds statements by group, being given

FR-011checkpassing

An extension MUST be able to add statements for a bound schema through the engine's point physical.statements, being given the written name of every table and column, and its errors MUST be returned with bind's.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-bind.test.ts: the same input gives the same text, and an extension adds statements by group, being given the written names

Proved by: data/suite/physical-bind.test.ts

Written in: data/sql/physical/bind.ts, data/sql/physical/statement.ts

Tests: passed: suite/physical-bind.test.ts the same input gives the same text, and an extension adds statements by group, being given

What the database holds

FR-012checkpassing

For each dialect, the statements bind makes for a schema, loaded into a real engine, MUST make the tables, columns, nullability, primary keys, foreign keys, uniques, checks and indexes of the logical schema; the engine MUST refuse what the constraints forbid (a party related to itself, a second identifier of a kind, a code that is not in its list, a reference to a row that is not there), and a load of the reference data twice MUST leave the same rows.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-engines.test.ts: ${name}: the statements load, and the engine holds the tables, columns, nullability, primary keys and indexes of the logical schema; suite/physical-engines.test.ts: ${name}: the engine refuses what the constraints forbid and accepts what they allow; suite/physical-engines.test.ts: ${name}: the reference data is loaded, in the codes of the model it replaces, and loaded twice leaves the same rows

Proved by: data/suite/physical-engines.test.ts

Tests: passed: suite/physical-engines.test.ts duckdb: the reference data is loaded, in the codes of the model it replaces, and loaded tw; passed: suite/physical-engines.test.ts postgres: the reference data is loaded, in the codes of the model it replaces, and loaded ; passed: suite/physical-engines.test.ts sqlite: the reference data is loaded, in the codes of the model it replaces, and loaded tw; passed: suite/physical-engines.test.ts duckdb: the engine refuses what the constraints forbid and accepts what they allow; passed: suite/physical-engines.test.ts duckdb: the statements load, and the engine holds the tables, columns, nullability, primar and 4 more

Extension

FR-013checkpassing

The physical schema's own code MUST name no engine: the name of an engine MUST appear only in its own dialect's folder, so that a fourth dialect is added without a change to this layer, and a dialect MUST NOT name another's engine.

Enforced (check): .github: cd commons/data && bun run verify — suite/physical-neutral.test.ts: the code of the physical core names no engine; suite/physical-neutral.test.ts: each dialect names its own engine and no other

Proved by: data/suite/physical-neutral.test.ts

Tests: passed: suite/physical-neutral.test.ts the code of the physical core names no engine; passed: suite/physical-neutral.test.ts each dialect names its own engine and no other

FR-014checkpassing

refsOf MUST give, for SQL that a person writes, the written, quoted and qualified name of a table and of a column from the words of their names (in any case style), the name of a table of the caller's own written by the schema's naming, and the tables asked for in the order first asked; and a table or column that the schema does not have MUST be an error value that names it, so that a name that does not exist is found when the SQL is made.

Enforced (check): .github: cd commons/data && bun run verify — suite/refs.test.ts: the written names of tables and columns are given by words in any case style, qualified where the schema is, and a name that is not there is an error value, the tables asked for being kept in order

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

Written in: data/sql/physical/refs.ts

Tests: passed: suite/refs.test.ts the written names of tables and columns are given by words in any case style, qualified wh

Cited by: Data, sql/physical/refs, Queries

FR-015checkpassing

parseCsv MUST read CSV text as RFC 4180 does (a quoted field that holds the delimiter, a newline or a doubled quote; \n, \r\n and \r as line ends; a delimiter that is a parameter) into records of fields, the first being the header, so that the extensions that read CSV share one reader.

Enforced (check): .github: cd commons/data && bun run verify — suite/refs.test.ts: CSV text is read as RFC 4180 does, with quotes, doubled quotes, any line end and a delimiter that is a parameter

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

Written in: data/sql/physical/csv.ts

Tests: passed: suite/refs.test.ts CSV text is read as RFC 4180 does, with quotes, doubled quotes, any line end and a delimit

Cited by: Data, sql/physical/csv, Kinds

Out of scope

  • The descriptions of tables and columns, as COMMENT or as documentation tables: an extension (0008).
  • Views, routines, row-level security and other features: extensions.
  • A migration of a database that exists: ISLM, an extension.
  • Queries and drivers: extensions.

Edge cases

  • pattern on SQLite: an error value naming the check, per FR-005.
  • A name of 70 characters on PostgreSQL: an error value, per FR-003.
  • A code employedBy: employed_by stored in snake case, employedBy in camel case, and as it is where the codes are kept by name, per FR-002.
  • camel of the words of line1: line1, and snake of them line_1, per FR-001.
  • A table that refers to itself: made by one statement, per FR-006.
  • Two tables that refer to each other: an error value where the dialect cannot add a constraint afterwards, per FR-006.
  • A fourth dialect written in a project, as an extension: bind takes it as it takes the three, per FR-013.
  • An extension that adds a comment for a column: it is given the column's written name, per FR-011.
  • A reference load of a code table run twice: the same rows, per FR-008.

Assumptions

  • Every name is quoted, so a reserved word is never a problem and the dialect has no list of them.
  • A dialect that cannot add a constraint to an existing table (SQLite) cannot break a cycle of foreign keys.
  • DuckDB does not enforce the length of a VARCHAR, and its dialect writes none, as the model it replaces did.
  • Statements are text made by the dialect, in groups; no tagged template or query builder is needed until queries are built.
  • PostgreSQL's engine is PGlite in the tests, DuckDB's is DuckDB's own Node client, and SQLite's is the runtime's node:sqlite; they are development dependencies of the tests only.

Open questions

OQ-1open question

Whether bind is to truncate a name that is too long, with a hash, where a project asks it to.

Key entities

  • Dialect - how one engine writes a logical schema.
  • Case style - how words are written as a name.
  • Naming - the case styles of the names and of the stored codes.
  • Statement - one piece of SQL, its group, and the logical elements it was made from.
  • Physical schema - the ordered statements bind makes.

Success criteria

SC-001success criterion

A governance domain becomes a database in any of three engines, by statements whose names read as that database's own and whose every object is traced to what the business said.

SC-002success criterion

A dialect that cannot express something says so before anything is run.

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/dialects/postgres/mod, sql/physical/bind, sql/physical/case, sql/physical/csv, sql/physical/dialect, sql/physical/express, sql/physical/refs, sql/physical/statement, sql/physical/upsert, Intellectual Frontiers Commons, SQL features as extensions, The lifecycle of a physical schema, Queries, Kinds

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