IF Commons GitHub

Making a table again

0017Active

0017-sql-rewrite · owner if · domain data

The core's part

FR-001checkpassing

plan MUST offer a change that the core cannot make in place to a fallback its options give (and a composition's extensions contribute through the engine's point lifecycle.fallback) before it is an error value that asks for a person's step; the step the fallback makes MUST go through the transforms like any other step, and a step a person wrote for the change MUST take its place.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: a change the core cannot make is offered to the fallback before it is an error, the step goes through the transforms, and a person's step takes its place

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

Written in: data/sql/extensions/rewrite/mod.ts, data/sql/extensions/rewrite/rewrite.ts

Tests: passed: suite/rewrite.test.ts a change the core cannot make is offered to the fallback before it is an error, the step g

Cited by: sql/lifecycle/plan

Making a table again

FR-002checkpassing

For a dialect that says how a table is made again, a change of whether a column may be null, of a default, of the primary key, of a constraint that is not an index, or a column that is required and has no default, MUST be made by making the table again: the statements the dialect needs first, a table of the state after the change under a name of its own, the rows of the columns the two tables have in common copied into it, the old table dropped, the new one renamed to the old name, and the indexes made.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: on SQLite a key is added by making the table again, the rows and what refers to the table are kept, the new definition is enforced, and the step says what it is; suite/rewrite.test.ts: a column made required is made by making the table again where the engine cannot, a row that does not meet it rolls the step back whole, and it succeeds once the row does; suite/rewrite.test.ts: the statements are written from the states: the indexes are made after, the primary key is kept, and a column the tables have in common is copied

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

Tests: passed: suite/rewrite.test.ts on SQLite a key is added by making the table again, the rows and what refers to the table ; passed: suite/rewrite.test.ts the statements are written from the states: the indexes are made after, the primary key is; passed: suite/rewrite.test.ts a column made required is made by making the table again where the engine cannot, a row th

FR-003checkpassing

The step MUST say that it makes the table again, MUST be data-dependent where the change may be refused by the rows (a constraint added, a column made required) and safe where it may not, MUST be lossless-reversible with the statements that make the table again as it was, and MUST be only as safe to run twice as the ledger makes it.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: on SQLite a key is added by making the table again, the rows and what refers to the table are kept, the new definition is enforced, and the step says what it is; suite/rewrite.test.ts: the protection layer's views are dropped before and made again after a table is made again, and a step that makes a table again relies on the ledger alone

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

Tests: passed: suite/rewrite.test.ts on SQLite a key is added by making the table again, the rows and what refers to the table ; passed: suite/rewrite.test.ts the protection layer's views are dropped before and made again after a table is made again

Cited by: sql/lifecycle/plan

FR-004checkpassing

The feature MUST leave the change to a person, and the error value MUST remain, where a column of the table after the change is required, has no default and is not in the table before; where the dialect stops a table that other tables' foreign keys refer to from being dropped and one does; or where a column's type changes and casts are not allowed.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: what cannot be made safely is left to a person with the reason: a required column with no default, a table that others refer to where the engine stops it, and a type that is not allowed to be cast

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

Tests: passed: suite/rewrite.test.ts what cannot be made safely is left to a person with the reason: a required column with no

FR-005checkpassing

The statements MUST be written from the state, with names and types as written, and MUST be the same for the same states every time.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: on SQLite a key is added by making the table again, the rows and what refers to the table are kept, the new definition is enforced, and the step says what it is; suite/rewrite.test.ts: the statements are written from the states: the indexes are made after, the primary key is kept, and a column the tables have in common is copied

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

Tests: passed: suite/rewrite.test.ts on SQLite a key is added by making the table again, the rows and what refers to the table ; passed: suite/rewrite.test.ts the statements are written from the states: the indexes are made after, the primary key is

Changing a type

FR-006checkpassing

Where the settings allow casts, a change of a column's type MUST be made in place with SET DATA TYPE ... USING CAST for a dialect that can alter a column, and by making the table again with CAST of the column for one that cannot; the step MUST be data-dependent and say that values may change or be refused, and where casts are not allowed (the default) it MUST remain an error value that asks for a person.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: a type is cast in place where the engine can alter a column and by making the table again where it cannot, when casts are allowed, and says that values may change

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

Written in: data/sql/extensions/rewrite/rewrite.ts

Tests: passed: suite/rewrite.test.ts a type is cast in place where the engine can alter a column and by making the table again

Settings

FR-007checkpassing

The feature MUST be on by default and be switched off by a setting, and whether casts are allowed and the last word of the new table's temporary name MUST each have a default and be changeable.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: the feature is on by default, can be switched off, and its settings have defaults and can be changed

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

Tests: passed: suite/rewrite.test.ts the feature is on by default, can be switched off, and its settings have defaults and can

What the database holds

FR-008checkpassing

On SQLite and DuckDB, a constraint added to or taken from a table, a column made required and a type cast, each by making the table again, MUST leave the table with its rows, its indexes, its new definition enforced and the tables that refer to it still referring to it (where the dialect lets the table be dropped), and a step whose rows do not meet the change MUST be rolled back whole, leaving the old table; and the protection layer's views MUST be dropped before and made again after.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: on SQLite a key is added by making the table again, the rows and what refers to the table are kept, the new definition is enforced, and the step says what it is; suite/rewrite.test.ts: a column made required is made by making the table again where the engine cannot, a row that does not meet it rolls the step back whole, and it succeeds once the row does; suite/rewrite.test.ts: the protection layer's views are dropped before and made again after a table is made again, and a step that makes a table again relies on the ledger alone

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

Tests: passed: suite/rewrite.test.ts on SQLite a key is added by making the table again, the rows and what refers to the table ; passed: suite/rewrite.test.ts a column made required is made by making the table again where the engine cannot, a row th; passed: suite/rewrite.test.ts the protection layer's views are dropped before and made again after a table is made again

FR-009checkpassing

A step that makes a table again on an engine that drops a table's triggers with it MUST say, in its verification, that the number of the table's triggers is the same after as before, so that a table that has triggers is not made again without them: the step fails, is rolled back whole, and the triggers are as they were.

Enforced (check): .github: cd commons/data && bun run verify — suite/rewrite.test.ts: a table that has triggers is not made again without them: the step fails its verification, is rolled back whole, and the trigger and the rows are as they were

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

Tests: passed: suite/rewrite.test.ts a table that has triggers is not made again without them: the step fails its verification,

Cited by: sql/physical/dialect

Out of scope

  • Changing a type without loss (checking that every value survives the cast, then swapping): a later feature, on the roadmap.
  • Making a table again in PostgreSQL where it can alter in place: it never does.
  • A table made again while the application writes to it: a step takes the table's lock for its length.

Edge cases

  • A foreign key added on SQLite: the table is made again with the key, per FR-002.
  • A required column with no default added on SQLite: left to a person, per FR-004.
  • A table that other tables refer to on DuckDB: left to a person, per FR-004.
  • A type changed with casts refused: an error value that says casts are not allowed, per FR-006.
  • A column made required where a row has no value: the step fails and is rolled back, per FR-008.
  • The feature off: the error values of the core, per FR-007.

Assumptions

  • A transaction can make a table, copy rows, drop a table and rename one: it can in all three engines.
  • SQLite checks foreign keys at the end of the transaction when PRAGMA defer_foreign_keys is on.

Open questions

OQ-1open question

Whether a table made again keeps the grants and the policies made on the old one, which the protection layer makes again after the step but a project's own do not.

Key entities

  • Rebuild - the statements that make a table again as another.
  • Fallback - what makes a change the core cannot make in place.

Success criteria

SC-001success criterion

A change to the schema that governance asks for is a step the lifecycle can make on every engine, or an error value that says what a person must write and why.

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/rewrite/mod, sql/extensions/rewrite/rewrite, sql/lifecycle/plan, sql/physical/dialect

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