IF Commons GitHub

Names that carry a schema

0013Active

0013-sql-qualified-names · owner if · domain data

The schema

FR-001checkpassing

bind MUST take an optional schema, a name in governance's way of writing names, and where none is given the statements it makes MUST be the same as they were before this specification.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: with no schema given the statements make no schema and qualify nothing

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

Tests: passed: suite/qualified.test.ts with no schema given the statements make no schema and qualify nothing

FR-002checkpassing

For a dialect that has the feature schemas, bind MUST make the schema first (CREATE SCHEMA IF NOT EXISTS, in the group of setup) and MUST write every reference to a table qualified by it: in CREATE TABLE, in the REFERENCES of a foreign key, in ON of an index, in ALTER TABLE and in an upsert.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: where the dialect has schemas the schema is made first and every reference to a table is qualified

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

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

Tests: passed: suite/qualified.test.ts where the dialect has schemas the schema is made first and every reference to a table is q

Cited by: Data, sql/physical/qualify

FR-003checkpassing

For a dialect that lacks it, bind MUST write the words of the schema before the words of each table's name, with the case style, and MUST write no CREATE SCHEMA; the length of a name MUST be checked after the words are added.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: where the dialect has none the schema's words come before each table's name with the case style, the length is checked after they are added, and no schema is made

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

Tests: passed: suite/qualified.test.ts where the dialect has none the schema's words come before each table's name with the case

FR-004checkpassing

What a statement source is given (written) MUST give the reference to a table by its identity (ref), the reference to a table of an extension by the name it is written by (qualify) and the name a table of an extension is written by after the schema's words are added (tableName), so that an extension never builds a reference by hand.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: a statement source is given the reference to a table, to a table of an extension, and the name an extension's table is written by

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

Tests: passed: suite/qualified.test.ts a statement source is given the reference to a table, to a table of an extension, and the

Cited by: sql/physical/express, sql/physical/statement

The extensions

FR-005checkpassing

The comments, the documentation tables, the governance tables, the views, the access (policies and grants), the routines and the partitions MUST write every reference to a table through FR-004, and the tables that an extension makes MUST be made in the same schema as the tables they describe.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: the comments, the documentation tables, the governance tables and the views are made in the schema on every engine, and the tables are in none other; suite/qualified.test.ts: policies, routines and partitions refer to the tables in the schema on PostgreSQL

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

Tests: passed: suite/qualified.test.ts policies, routines and partitions refer to the tables in the schema on PostgreSQL; passed: suite/qualified.test.ts the comments, the documentation tables, the governance tables and the views are made in th

The lifecycle

FR-006checkpassing

A state MUST record the schema (qualifier) where the dialect has schemas, so that a state of one schema is another state than that of none; and a plan MUST write the references of its statements, and the probes of its guards, qualified by it.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: a state records the schema where the dialect has schemas, and a plan writes its statements and the probes of its guards qualified by it

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

Tests: passed: suite/qualified.test.ts a state records the schema where the dialect has schemas, and a plan writes its statements

Cited by: sql/lifecycle/state

FR-007checkpassing

The steps of a plan from empty, and then from each version to the next, MUST leave on SQLite, DuckDB and PostgreSQL the database that bind makes for the last version in the same schema.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: the steps from empty and from each version to the next leave the database that bind makes in the same schema, on three engines

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

Tests: passed: suite/qualified.test.ts the steps from empty and from each version to the next leave the database that bind makes

What the database holds

FR-008checkpassing

On DuckDB and PostgreSQL the tables, their foreign keys, their indexes, their reference rows, their comments and the tables of the documentation extensions MUST be in the schema and none in the default one; on SQLite the same tables MUST be there with the schema's words before their names.

Enforced (check): .github: cd commons/data && bun run verify — suite/qualified.test.ts: the comments, the documentation tables, the governance tables and the views are made in the schema on every engine, and the tables are in none other; suite/qualified.test.ts: policies, routines and partitions refer to the tables in the schema on PostgreSQL

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

Tests: passed: suite/qualified.test.ts policies, routines and partitions refer to the tables in the schema on PostgreSQL; passed: suite/qualified.test.ts the comments, the documentation tables, the governance tables and the views are made in th

Out of scope

  • A schema for each concept, or for each version of the API: 0014 and the parallel-versions feature make views in schemas of their own with this.
  • Moving a database from one schema to another: a rename of the schema is a step a person writes.
  • The ledger's table: it is in the connection's default schema, until a setting names another.

Edge cases

  • No schema given: the statements are as they were, per FR-001.
  • A schema named data on PostgreSQL: "data"."book", per FR-002; on SQLite data_book, and in camel case dataBook, per FR-003.
  • A table whose name with the schema's words is too long: an error value, per FR-003.
  • A table of an extension named like a table of the schema once the words are added: an error value naming both (0008 FR-007).
  • A policy on a table in a schema: ON "data"."document", per FR-005.

Assumptions

  • An engine with schemas looks a table up by its qualified name whatever its search path.
  • An index lives in its table's schema, so its name is not qualified where it is made and is qualified where it is dropped.

Open questions

OQ-1open question

Whether schema may be a list (a schema of tables and another of reference data).

Key entities

  • Schema - the place tables are kept, written as a qualifier or, where the engine has none, as the first words of each name.
  • Reference - a table as a statement names it.

Success criteria

SC-001success criterion

The same governance gives the same tables in any schema, and an extension never has to know whether the engine has schemas.

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/plan, sql/lifecycle/state, sql/physical/bind, sql/physical/express, sql/physical/qualify, sql/physical/statement, Protected access

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