orm

Theme

Schema plans ​

A plan changes a database from one schema to the next. It holds the target schema and the decisions that a schema cannot express: renames and the permission to drop. Plans form a chain from an empty database; every client diffs the two schemas of a plan the same way and writes the same steps for each dialect. tests/dbspec/plans.json holds the shared cases.

Plan document ​

A plan is a text file, conventionally <name>.dbplan, in UTF-8 with LF line ends:

dbplan 1 add_clients
from sha256:3b1f…
rename table users clients
rename column clients.mail email
allow drop table legacy
allow drop column clients.fax

dbspec 1 schema

table clients {
  …
}
  • PLN-45 The first line is exactly dbplan 1 <name>, with a name of the name rules.
  • PLN-46 The second line is from empty for the first plan of a chain, or from <schemaHash> of the schema the plan starts from.
  • PLN-47 Then, one per line: rename table <old> <new>; rename column <table>.<old> <new>, where <table> is the table's name in the target; allow drop table <name>; and allow drop column <table>.<name>, with the names of the source.
  • PLN-48 A blank line ends the header. The rest is the target: a schema text, one document named schema in canonical form. The plan's to hash is the schemaHash of that text.

The canonical plan writes the header lines in the order rename table, rename column, allow drop table, allow drop column, each in name order. A plan is invalid, with a plan diagnostic at its line, when a line has another form, a name breaks the name rules, a line repeats, two renames give one name, or the target is not a schema text; a target diagnostic is located in the plan. A plan whose target hash equals its from is invalid too, and so is a target with a use line: a plan changes a whole database, and a set that uses external documents is installed and upgraded with install and addTablesAndColumns.

Chain ​

The plans of a directory form one chain: the plan from empty comes first and each next plan starts from the to hash of the one before. A directory without plans is the empty chain, whose database has no table. In a nonempty set of plans, a missing from empty plan, two plans with the same from, a plan that the chain does not reach, and a cycle are chain diagnostics that name the plans.

Diff ​

The diff of a plan compares its source, which is the target of the plan before it or the empty schema, with its target. The source must have the plan's from hash. Tables match by name, a renamed table by its new name; columns of matched tables likewise. A rename whose old name the source lacks, whose new name the source already has, or whose new name the target lacks is a plan diagnostic.

  • PLN-49 create_table — When: a table only in the target
  • PLN-50 drop_table — When: a table only in the source; Permission: allow drop table
  • PLN-51 rename_table, rename_column — When: a rename
  • PLN-52 add_column — When: a column only in the target
  • PLN-53 drop_column — When: a column only in the source; Permission: allow drop column
  • PLN-54 alter_column — When: a matched column whose type widens, or whose null or default changes
  • PLN-55 drop_*, add_* of unique, index, foreign_key and check — When: an object only on one side, or whose definition differs after the renames
  • PLN-56 drop_triggers, create_triggers — When: the rendered immutable or audit statements of a table differ in a dialect

A widening keeps every value on the three databases: i16 to i32 or i64, i32 to i64, varchar(n) to varchar(m) with m ≥ n or to text, decimal(p,s) to decimal(q,s) with q ≥ p, and time(p) or datetime(p) to the same type with a larger precision. These are plan diagnostics, because they need a new column or table filled between two plans or cannot keep the column order PostgreSQL gives:

  • PLN-57 a non-null column without a default added to a table;
  • PLN-58 any other type change, a change of identity and a change of the primary key;
  • PLN-59 matched columns in another order, and an added column before a matched one;
  • PLN-60 a drop without its permission, and a permission that drops nothing.

An object is dropped and added again even when its definition stays: a check that names a renamed or altered column, a foreign key one of whose columns, on either side, is altered, and a foreign key whose columns begin a dropped index or unique key, which MySQL would otherwise not drop. A renderer CHECK, named <table>$<column>, is dropped and added when its name or text changes. The diff of a schema with itself is empty.

Comparison ​

A comparison lists every difference between two schemas without a plan, for a tool that shows how a database differs from a model. It has no renames and no permissions: tables and columns match by name only. Both sides are documents of schema texts; a side whose canonical emission is not the schema text of that document alone is a compare diagnostic at line 1, column 1 that names the side, the source before the target, and the comparison then has no differences. A difference is [kind, table, name], where name is the column or object name and empty for a table, key, order or setting kind:

  • PLN-61 create_table: a table only in the target
  • PLN-62 drop_table: a table only in the source
  • PLN-63 drop_column: a column only in the source
  • PLN-64 add_column: a column only in the target
  • PLN-65 alter_column: a column of both whose type widens, or whose null or default changes
  • PLN-66 change_column_type: a column of both whose type changes without widening
  • PLN-67 change_column_identity: a column of both whose identity changes
  • PLN-68 reorder_columns: the columns of both come in another order, or a column only in the target comes before a column of both
  • PLN-69 change_primary_key: the primary key has other columns or another column order
  • PLN-70 drop_unique, add_unique, drop_index, add_index, drop_foreign_key, add_foreign_key, drop_check, add_check: an object only in the source or only in the target; both when its definition differs
  • PLN-71 drop_immutable, add_immutable: the immutable setting only in the source or only in the target
  • PLN-72 drop_audit, add_audit: the audit setting only in the source or only in the target; both when its history table, audit column, audit record table, action or previous column differs or it records another set of the columns that both tables have (a column on one side only is an add_column or drop_column)

The kinds that a plan diff also has keep their meaning, and widening is that of "Diff"; change_column_type, change_column_identity, reorder_columns and change_primary_key name the changes that a plan refuses. A column can have several kinds, such as alter_column and change_column_type when its null and its type change. A definition compares as written, so an object whose definition stays is not dropped and added again, and a setting is compared instead of its rendered triggers. A created or dropped table lists nothing more. The differences come in table name order; within a table in the order of the table above, read row by row and left to right; within a kind in name order. The comparison is empty exactly when the two schema texts, and so their schemaHash, are equal.

Go has CompareSchemas, PHP Dbspec::compareSchemas, TypeScript compareSchemas, Rust compare_schemas and Python compare_schemas; each takes the source and the target document and returns the differences or the diagnostics.

Steps ​

A plan runs as steps, one statement each. Every step has its statement, its rollback statement, which undoes exactly what the statement did, and its effect, which tells whether the statement took effect (see "Effects"); some steps also have a restore statement and a null check. Go PlanSteps, PHP Dbspec::planSteps, TypeScript planSteps, Rust polyspec_orm_schema::dbspec::plan_steps and Python plan_steps take the source document (none for the empty schema), the plan and a dialect and return the steps or the plan diagnostics; tests/dbspec/plans.json lists the steps of every case and dialect as objects with statement, rollback or irreversible, effect, and when they apply restore with restore_if, null_checks and finalize: true.

The steps come in this order, tables and objects in name order within each group:

  1. PLN-73 drop the triggers that change, those of the tables that SQLite rebuilds and those of the dropped tables, with their PostgreSQL functions;
  2. PLN-74 drop the foreign keys that change, and those of the dropped tables;
  3. PLN-75 drop the checks, renderer CHECKs, unique keys and indexes that change, and those of the dropped tables;
  4. PLN-76 rename tables, then columns;
  5. PLN-77 hide the dropped columns (MySQL, PostgreSQL), then the dropped tables;
  6. PLN-78 create tables as the renderer writes them;
  7. PLN-79 add and alter columns, and on SQLite rebuild tables;
  8. PLN-80 add unique keys, indexes, checks and renderer CHECKs;
  9. PLN-81 add the foreign keys of the created tables and those that change;
  10. PLN-82 create the triggers of the created tables, of the rebuilt tables and those that change;
  11. PLN-83 finalize: drop the hidden tables and columns.

MySQL alters a column with MODIFY COLUMN and its full rendered definition; PostgreSQL with ALTER COLUMN TYPE, SET NOT NULL, DROP NOT NULL, SET DEFAULT and DROP DEFAULT, in that order.

Rollback statements. A drop of a trigger, PostgreSQL function, foreign key, check, renderer CHECK, unique key or index is undone by the statement that creates the source object; a rename by the opposite rename; a created table, index, key, check, trigger or function by its drop. A MySQL MODIFY COLUMN is undone by MODIFY COLUMN with the source definition, a PostgreSQL TYPE by the source type, SET NOT NULL by DROP NOT NULL and the reverse, and SET DEFAULT or DROP DEFAULT by setting the source default or dropping it. A rollback statement refers to tables and columns by the names they have when it runs, that is, after the renames for the steps after group 4.

Hiding instead of dropping. A plan never destroys data before its finalize steps: a dropped table or column is renamed to a holding name and stays, with its rows and values, until finalize drops it, and a rollback renames it back in its place. A holding name is dbspec$hold$<h>$<n>, where <h> is the first 12 hexadecimal digits of the plan's to hash and <n> numbers from 1 the plan's dropped columns (in table name order, then source column order), then its dropped tables (in name order), then its added columns (in table name order, then target column order). It has at most 28 characters whatever the length of the hidden name, and a dbspec name has no $, so it never meets one. Introspection leaves out every table and every column whose name begins with dbspec$.

  • PLN-84 A dropped column c of a table t on MySQL and PostgreSQL: when c is non-null, ALTER TABLE t MODIFY COLUMN with c nullable (PostgreSQL ALTER TABLE t ALTER COLUMN c DROP NOT NULL), so that writes without it succeed; then ALTER TABLE t RENAME COLUMN c TO h. The rollbacks rename it back and restore the source definition. Its default stays, so a row written without it gets the default.
  • PLN-85 A dropped table t, after its triggers, foreign keys, checks, renderer CHECKs, unique keys and indexes are dropped (on SQLite its triggers and indexes): ALTER TABLE t RENAME TO h, undone by ALTER TABLE h RENAME TO t. A hidden SQLite table keeps its foreign keys, so deleting a parent row that it references acts as if the table were there; on MySQL and PostgreSQL the rollback adds the foreign keys back, which fails at its step when a parent row was deleted in between. Every connection of a command sets PRAGMA legacy_alter_table = OFF on SQLite, so a rename there carries the foreign keys of other tables, as on MySQL and PostgreSQL.
  • PLN-86 An added column c on MySQL and PostgreSQL is undone by ALTER TABLE t RENAME COLUMN c TO h, which keeps the values written to it. The step has a restore statement, ALTER TABLE t RENAME COLUMN h TO c, which apply and recover run instead when the table has the column h, so applying the plan again after its rollback brings the column back with its values.
  • PLN-87 SQLite changes a column only by rebuilding its table: a matched table rebuilds when its name, a column, a foreign key or a check changes; index and unique key changes alone, and trigger changes alone, do not rebuild. Renames run first, so other tables' foreign keys follow them. The rebuild copies the rows through the work table dbspec$rebuild and back, so that each statement has a rollback that carries the current rows: CREATE TABLE "dbspec$rebuild" with the new definition; INSERT INTO "dbspec$rebuild" (…) SELECT … FROM t; DROP INDEX of each unique key and index of t; DELETE FROM t; DROP TABLE t; CREATE TABLE t with the new definition; INSERT INTO t (…) SELECT … FROM "dbspec$rebuild"; DELETE FROM "dbspec$rebuild"; DROP TABLE "dbspec$rebuild"; and the unique keys and indexes of the new definition. For a table with an identity column, INSERT INTO sqlite_sequence (name, seq) SELECT … before each copy into an empty table and DELETE FROM sqlite_sequence WHERE name = … before each DELETE FROM of a table whose rows have moved carry the automatic key counter, the largest key ever given, even of a deleted row, so that no key is used again. The new definition is the target table with each dropped column added, nullable and without its CHECK, under its holding name; the old definition, which the rollback of DROP TABLE t creates, is the source table with the names that the renames changed and each renderer CHECK under its source name, as SQLite leaves them after a rename, with each added column, nullable and without its CHECK, under its holding name. The copy into the new definition takes a time(p) or datetime(p) column whose precision grows with zero fraction digits appended, and the added columns from their holding names when the table has them, which its restore statement does.
  • PLN-88 The finalize steps drop the holding tables and columns in the order of their numbers: DROP TABLE h and ALTER TABLE t DROP COLUMN h. They have no rollback.

Irreversible steps. A step has no rollback statement, and names why with irreversible, only when undoing it could change a value without an error: widening the precision of a time(p) or datetime(p) column, because narrowing it back rounds the values written since on MySQL and PostgreSQL and SQLite's CHECK would refuse them otherwise (narrowing the precision rounds the values written since); on SQLite the rebuild that widens it has no rollback for its DELETE FROM t. The other narrowings back, of a widened integer, varchar, text or decimal, fail on a value that does not fit, with MySQL in the strict sql_mode of dialects, and are rollbacks. Apply reports the irreversible steps before it runs a plan, and a rollback stops before one.

Restoring non-null columns. A rollback statement that makes a column non-null again (of a dropped column, a column that the plan made nullable, or a SQLite rebuild that copies such a column back) carries a null check: the table, the column with its name in the applied plan, and the source default. Before a rollback of an applied plan runs any step, it counts the rows where that column is NULL: rows written in between. With a source default it sets them to the default with UPDATE t SET c = <default> WHERE c IS NULL; without one it stops with a nulls error that names the plan, the step, the table, the column and the number of rows, and changes nothing.

Effects. The effect of a step is one of: present or absent and a table, column (table, column), index (table, index), constraint (table, constraint), trigger (table, trigger), function (function), sequence (the sqlite_sequence row of a name) or rows (whether a table has a row); or repeat, for a statement that has the same result when it runs again (MODIFY COLUMN and ALTER COLUMN). A rollback statement takes the step back to the opposite of its effect, and a restore statement has the effect of its step. tests/dbspec/plans.json writes an effect as text: repeat, or the state, the kind and the names, such as present table users or absent column users nick, and a step's restore with the effect restore_if that selects it.

Apply ​

orm applies a schema change step by step on all three databases: every statement commits on its own, outside any transaction, and its history step is recorded after it. While a plan runs, other sessions see each step as it commits. An interrupted or failed plan stops at a known step; recover continues it and rollback undoes it step by step. A change that other sessions see all at once or not at all needs a maintenance window or a blue-green switch of databases, which the operator chooses; orm does not provide one. The commands treat the database as the schema of one document set: its state is the schema of all its tables, so the tables of several sets that share a database, one using external documents of another, are installed and upgraded with install and addTablesAndColumns instead.

Apply, recover, rollback and finalize run on one connection that the caller owns, and restore its session settings at the end. The lock and the session settings belong to a server session, so on MySQL and PostgreSQL the connection must keep one server session of its own for the whole command: a direct or session-pooled connection. A transaction pooler such as PgBouncer in pool_mode = transaction picks a server connection for each transaction or statement, so the lock would not cover the steps that run outside transactions one by one; the command detects it and stops with a session error instead of running without the lock. Before it takes the lock, the command reads the server session (MySQL CONNECTION_ID(), PostgreSQL pg_backend_pid()) and whether that session already holds the lock (MySQL IS_USED_LOCK of the lock name, PostgreSQL the pg_locks row of the advisory key); a session that already holds it is shared with another client and is a session error. The lock statement returns the server session too (SELECT GET_LOCK(…, 0), CONNECTION_ID(), SELECT pg_try_advisory_lock(…), pg_backend_pid()), and before the statement of every step the command reads the server session again; a session other than the first is a session error naming the plan and the step, raised before the step's statement runs. The error message names the requirement: apply, recover, rollback and finalize need one server session of their own for the whole run: a direct or session-pooled connection. A connection that ProxySQL multiplexes keeps its server session once it has run GET_LOCK, so it passes these checks.

  1. PLN-89 Lock. The lock covers the one database that the command changes, so commands on other databases, or on other schemas of one PostgreSQL database, run at the same time. MySQL takes GET_LOCK(CONCAT('dbspec$plans$', LEFT(SHA2(DATABASE(), 256), 51)), 0) and releases it with RELEASE_LOCK of the same name: the name holds the first 51 hexadecimal digits of the SHA-256 of the current database's name and has 64 characters, the most MySQL accepts, whatever the length of that name. PostgreSQL takes pg_try_advisory_lock(hashtext('dbspec$plans'), hashtext(current_schema())) and releases it with pg_advisory_unlock of the same key; advisory locks belong to the current database, and two schemas whose names have the same hashtext share the lock. SQLite sets PRAGMA locking_mode = EXCLUSIVE and runs BEGIN EXCLUSIVE and COMMIT, so the connection keeps the exclusive lock of the file, and other connections can neither read nor write it, until the command sets the previous locking mode back and reads the database once; it then turns foreign keys off and sets PRAGMA legacy_alter_table = OFF. A lock that another session holds is a locked error; nothing changes. After a SQLite locked error the command restores the settings without waiting for the lock again: it sets PRAGMA busy_timeout = 0 first, and a lock result of its read means that the connection holds no lock to release. Any other result than MySQL 1 or 0 or PostgreSQL true or false, such as the NULL of a PostgreSQL connection without a current schema, is an error.
  2. PLN-90 Lock waits. A statement waits at most 5 seconds for a lock that another session holds: PostgreSQL SET lock_timeout = '5s', MySQL SET SESSION lock_wait_timeout = 5, innodb_lock_wait_timeout = 5, SQLite PRAGMA busy_timeout = 5000, which also bounds the wait for the SQLite lock. A statement that times out is a failed error at its step with the database's error.
  3. PLN-91 History. The table dbspec$plans records one row per plan: name, from_hash, to_hash, state, step (the number of steps in effect), steps and applied_at (the tool clock in UTC with six fraction digits, YYYY-MM-DDTHH:MM:SS.ffffffZ, stored as text on every database, so no database drops a digit; protocol states the clock; each command takes it as a function: Go time.Time, PHP DateTimeImmutable, Rust DateTime<Utc>, TypeScript microseconds since the epoch as a number, because a Date holds milliseconds, and Python microseconds since the epoch as an int). The state is applying, applied, finalizing, done or rolling_back. Every command creates the table when it is missing.
  4. PLN-92 State. The database is at the to_hash of the last row in chain order, or empty. A row in applying, finalizing or rolling_back is an interrupted plan: apply and finalize report it as an interrupted error naming the plan and step. Apply and finalize introspect the database and require its schemaHash to be the state and nothing unsupported, and the empty state to have no table; otherwise it is a drift error naming both hashes. A chain that does not contain the state is a chain error, and so is a row before the last that is not applied or done. Rollback, and recover of an interrupted plan, report a chain error for a row whose step lies outside its plan's steps.
  5. PLN-93 Apply. For each later plan, apply reports every step without a rollback with an irreversible event, writes the row (applying, step 0) and runs the steps before the finalize steps, recording step after each. SQLite then requires PRAGMA foreign_key_check to return no row. Apply introspects the database: its schemaHash must equal the plan's to hash, with nothing unsupported. Then the row becomes applied. A failed check is a verify error and the row stays applying with every step recorded.
  6. PLN-94 Finalize. Finalize runs the finalize steps of every applied plan in chain order: the row becomes finalizing, each step is recorded, and the row becomes done. A plan that hides nothing has no finalize step and becomes done at once.
  7. PLN-95 Recover. Recover continues the interrupted plan forward. Its step is k: the first k steps are in effect. An applying or finalizing row may have run step k, so recover reads the effect of step k and continues from k + 1 when it is there; a rolling_back row may have run the rollback of step k - 1, so recover continues from k - 1 when the effect of step k - 1 is gone. A repeat step runs again. Apply and recover run a step's restore statement instead of its statement when the table has the column of restore_if. Recover then runs the remaining steps as apply does, up to applied, or to done for a finalizing row. Without an interrupted plan recover changes nothing.
  8. PLN-96 Rollback. Rollback undoes the last plan in the history: an applied, done or interrupted one. It reads the effect as recover does, sets the row to rolling_back and runs the rollback statements from step k - 1 down to step 0, recording step after each; on SQLite PRAGMA foreign_key_check must then return no row, and the introspected schemaHash must equal the plan's from hash, or the database must have no table for the first plan. Then rollback deletes the row. A rollback of an applied plan first runs the null checks of its steps (see "Restoring non-null columns"). A step without a rollback stops it before that step with an irreversible error that names the plan and step; when it is the first step to undo, nothing changes. An applied plan is checked for drift first. Without a row rollback changes nothing. Rollback undoes one plan per call, so repeated calls take the database back plan by plan.

Each command reports each occurrence as an event: plan when apply or recover runs a plan forward, rollback and finalize when those start on a plan (each with its number of steps); irreversible with the index and statement of a step without rollback; statement before and applied after each statement (with the index of its step and the statement or rollback statement); verified after the introspection; and done when the plan reaches its state. An event handler that returns an error stops the command at that point, and the row keeps the last recorded step.

Failures. A failed statement is a failed error naming the plan, the step and the database error, and the row keeps the last recorded step. After a failure the command still restores the session settings, turns SQLite foreign keys back on and releases the lock, and it reports every error of these steps with the failure. An advisory unlock that released nothing is an error, and so is an effect query that returns no row. A failure without cleanup errors is reported unchanged: an apply error with its code, the database error of a history, lock or setting statement, or the error of an event handler. With cleanup errors each client keeps them all in its own form:

  • PLN-97 Go returns errors.Join of the failure and the cleanup errors in order; errors.Is and errors.As find each of them.
  • PLN-98 PHP throws an ApplyCleanupError whose previous throwable is the failure and whose cleanup lists the cleanup errors in order.
  • PLN-99 TypeScript rejects with an AggregateError whose errors are the failure and then the cleanup errors in order.
  • PLN-100 Rust returns ApplyError::Cleanup { error, cleanup } with the failure as error; each later cleanup error wraps the previous result as its error again. An unlock that released nothing is ApplyError::LockNotHeld.
  • PLN-101 Python raises the failure with a note cleanup failed: <error> added for each cleanup error in order (BaseException.add_note).
  • PLN-102 Every case of tests/dbspec/plans.json is applied on MySQL, PostgreSQL and SQLite, each run to an empty database, schema or file. The source is rendered and the before steps run; the steps before finalize run (on SQLite with foreign keys off, then PRAGMA foreign_key_check returns no row), and the introspected schema text equals the target with no unsupported object. Without an irreversible step, the rollback statements return to the source schema text and the steps run again. The after steps and the finalize steps then run, the target is introspected again, and no table or column whose name begins with dbspec$ remains.
  • PLN-103 For every ordered pair of the Go, PHP, PHP extension, TypeScript, Python and Rust clients on MySQL, PostgreSQL and SQLite, the first client applies the first plan of the chain and the second applies the rest. The first client stops the second plan after a statement, and the second rejects it as interrupted and recovers it; the first client stops it again, and the second rolls it back. The history rows and the introspected schema text equal the chain's in every run.
  • PLN-104 On PostgreSQL, with max_locks_per_transaction at its default of 64, the session that applies the stress plan holds no relation lock after any applied step, so the lock count does not depend on the plan size. Each applied step is recorded in the history of each database.
  • PLN-105 Through a transaction pooler (PgBouncer in transaction pooling mode, on a database of its own), an apply fails with a session error when its server connection was left holding the lock by another client, and when its next statement runs in another server session because another client's transaction took its server connection at the first statement event. The other scenarios run on the servers directly.
  • PLN-106 Every client reports the primary failure first, followed by every cleanup error in cleanup order; no cleanup error is lost when multiple cleanup steps fail.

Between an interruption and its recover or rollback, the plan's tables must not be written: a row written to a table that a SQLite rebuild has emptied, for example, is not in its work table. Apply on a database that has applied the whole chain changes nothing. Apply of the empty chain creates the history table, requires a database without a table, and reports no event; recover, rollback and finalize of the empty chain change nothing.

Go has Apply, Recover, Rollback and Finalize; PHP Dbspec::apply, recover, rollback and finalize; TypeScript applyPlans, recoverPlans, rollbackPlans and finalizePlans; Rust polyspec_orm::dbspec::apply, recover, rollback and finalize. Each takes the connection, the dialect, the plans of the chain, a clock for applied_at and an event handler. The TypeScript connection types (DbspecApplyMySqlConnection, DbspecApplyPostgresConnection, DbspecApplySqliteConnection, and DbspecMySqlConnection, DbspecPostgresConnection and DbspecSqliteConnection of introspectDbspec) name only the methods that the client calls, without the types of pg, mysql2 or node:sqlite: a mysql2 connection, a pg client and a node:sqlite database fit them, and code that installs only one driver type-checks the published declarations. Go's Apply returns an ApplyResult and an error. A stop by the event handler is Stopped with the handler's error as Stop, and an apply error alone is Failure; both come with a nil error. Any other failure is the error, and a cleanup failure after a stop or a failure is joined with it, as PLN-97 states.

Verification ​

make dbspec-go-check runs the cases of tests/dbspec/plans.json through the Go engine and requires the listed steps of every dialect. make dbspec-plan-check applies every case to MySQL, PostgreSQL and SQLite: it renders the source, runs the before steps, runs the steps before finalize (SQLite with foreign keys off, then PRAGMA foreign_key_check returns no row) and requires the introspected schema text to equal the plan's target; when no step is irreversible it runs the rollback statements back to the first step and requires the source schema text, then runs the steps again; it runs the after steps, the finalize steps, requires the target schema text again and no table or column whose name begins with dbspec$. make dbspec-apply-check applies chains with history, lock, drift, verification, recovery, rollback and finalize on the three databases. For the case representative of tests/dbspec/plans.json, applied after a first plan that creates its source, it stops the plan after the statement of every step k before it records the step, and then, each from that stop, recovers to applied and rolls back to the first plan, and it stops a rollback of the applied plan after the rollback statement of every step and continues it to the first plan, requiring the history rows and the introspected schema text each time; it then finalizes the plan and requires its done row, no hidden table or column and an irreversible rollback. From the applied plan it also writes rows through the target's columns, rolls the plan back, requires those rows with the dropped column's value restored or its default and the source schema text, applies the plan again and requires the values of the added column back; and it rolls back a plan whose hidden non-null column without default got a NULL row in between and requires a nulls error with the count and an unchanged database. make dbspec-apply-stress-bench, part of make bench, applies the 2000-table document of tests/dbspec/stress.mts as a first plan on the three databases, PostgreSQL with its default lock settings. make dbspec-ts-check and make dbspec-plan-ts-check do the same through the TypeScript client's parsePlan, emitPlan, chainPlans, diffPlan and planSteps. make dbspec-rust-check runs the same cases through the Rust client, and make dbspec-plan-rust-check applies them with the Rust renderer, plan steps and introspection. make dbspec-php-check runs the same cases through the PHP client's Polyspec\Orm\Dbspec\Dbspec (parsePlan, emitPlan, chain, diff, planSteps), and make dbspec-plan-php-check applies them through it to the three databases. make dbspec-apply-php-check, make dbspec-apply-ts-check and make dbspec-apply-rust-check run the scenarios of make dbspec-apply-check through the client's apply, recover, rollback and finalize. Each client's plan test also runs the parse cases, whose diagnostics carry rule, line, column and, for a plan diagnostic, the message, and the comparisons cases, whose differences or compare diagnostics with their messages must be exactly the listed ones. make dbspec-compare-check runs every entry of tests/dbspec/plans.json through the Go, PHP, PHP extension, TypeScript, Rust and Python clients and the PHP extension twice each and requires every output to equal the first Go output: the emitted plan, the changes and the steps of each dialect of every case with their statements, rollback statements, irreversible reasons, effects and finalize marks, the diagnostics of every invalid and parse case, the order or diagnostics of every chain, and the differences or diagnostics of every comparison, with the message of every plan, chain and compare diagnostic. Each apply check also runs, on PostgreSQL, an apply whose advisory lock an event released first, and requires the unlock that released nothing to fail; on the three databases, an apply to a second database, schema or file while the first apply holds its lock, which must not be locked; and the empty chain, which applies, recovers, rolls back and finalizes without an event and is drift on a database with a table. Each apply check also connects through PgBouncer (ORM_TEST_PGBOUNCER_DSN, transaction pooling) to a database of its own and requires a session error from an apply whose server connection another client left holding the lock, and from an apply whose next statement runs in another server session because another client's transaction took its server connection at the first statement event; the other scenarios run on the servers (ORM_TEST_MYSQL_SERVER_DSN, ORM_TEST_POSTGRES_SERVER_DSN). tests/dbspec/plans.json holds the empty chain as the chain case empty. make dbspec-go-check, make dbspec-php-check, make dbspec-ts-check and make dbspec-rust-check inject failures through a wrapped SQLite connection and a scripted MySQL connection and require each client to report a failure without cleanup errors unchanged and with the errors of a failed lock release, of a failed foreign key restore after a failed BEGIN EXCLUSIVE, and of an effect query without a row, in the form of "Apply". make dbspec-apply-pairs-check runs the apply runners of the Go, PHP, PHP extension, TypeScript, Rust and Python clients and the PHP extension (in tests/dbspec/apply, and in packages/python/tests/dbspec_apply_runner.py for Python): for every ordered pair of clients on MySQL, PostgreSQL and SQLite, the first client applies the first plan of the chain and the second applies the rest; the first client stops the second plan after a statement and the second rejects it as interrupted and recovers it; and the first client stops it again and the second rolls it back. The history rows and the introspected schema text must equal the chain's in each of the 270 runs.

MIT License · Go / PHP / Rust / TypeScript

polyspec · orm