Documentation

Migrations
in package

Lwt

Database migrations and initialization utilities.

Provides methods for updating database schema, running migrations, and initializing the database.

Constants

MAX_ATTEMPTS : mixed = 3: How many times a failed migration is retried before being given up on.
STATUS_APPLIED : mixed = 'applied': Status recorded for a migration whose statements all succeeded.
STATUS_FAILED : mixed = 'failed': Status recorded for a migration that had at least one failing statement.
HARMLESS_SQL_ERRORS : array<string|int, int> = [ 1007, // ER_DB_CREATE_EXISTS 1022, // ER_DUP_...: MySQL error codes that mean "nothing to do here", not "this broke".

Methods

alignReferenceColumnTypes() : void: Make every reference column use the same integer type as the key it points at.
calculateChecksum() : string: Calculate SHA-256 checksum for a migration file.
captureForeignKeys() : array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}>: Read back every foreign key in the database, in enough detail to rebuild it.
checkAndUpdate() : void: Check and/or update the database.
dropAllForeignKeys() : void: Drop all foreign key constraints from all tables in the database.
getAppliedMigrations() : array<string|int, string>: Get list of migrations that ran without any failing statement.
getFailedMigrations() : array<string|int, array{filename: string, attempts: int, error: string}>: Get the migrations that failed, with the error that made them fail.
getMigrationFiles() : array<string|int, string>: Get list of all migration files from the migrations directory.
getMissingForeignKeys() : array<string|int, array{name: string, table: string}>: The declared constraints that are still not there.
getRecordedMigrations() : array<string|int, string>: Get list of migrations that have an entry in `_migrations`, whatever their outcome.
getRetryableMigrations() : array<string|int, string>: Get migrations that failed and are still worth retrying.
prefixQuery() : string: Add a prefix to table in a SQL query string.
reconcileForeignKeys() : int: Add back the declared constraints an install has lost along the way.
recordMigration() : void: Record the outcome of a migration run.
reparseAllTexts() : void: Reparse all texts in order.
restoreForeignKeys() : int: Recreate captured foreign keys that are no longer there.
update() : void: Update the database if it is using an outdate version.
upgradeMigrationsTable() : void: Upgrade the _migrations table from old schema to new schema.
validateMigrationIntegrity() : array{valid: bool, errors: string[]}: Validate that applied migrations haven't been modified.
addMissingForeignKeys() : int: Create each of the given foreign keys that is not already there.
columnFamilyChanges() : array<string, array<string|int, string>>: Work out which columns of one family are out of step with their key.
columnsStillExist() : bool: Check that a table still has all of the given columns.
fetchMigrationNames() : array<string|int, string>: Run a query returning a `filename` column and collect the values.
isHarmlessFailure() : bool: Tell an expected statement failure apart from a real one.
runMigrationFile() : string|null: Run every statement of one migration file.
sanitizeReferentialAction() : string: Keep a referential action to the values MySQL accepts.

MAX_ATTEMPTS

How many times a failed migration is retried before being given up on.


    public
        mixed
    MAX_ATTEMPTS
    = 3

Retries only happen when new migrations appear (see update()), so this counts upgrades, not requests.

STATUS_APPLIED

Status recorded for a migration whose statements all succeeded.


    public
        mixed
    STATUS_APPLIED
    = 'applied'

STATUS_FAILED

Status recorded for a migration that had at least one failing statement.


    public
        mixed
    STATUS_FAILED
    = 'failed'

HARMLESS_SQL_ERRORS

MySQL error codes that mean "nothing to do here", not "this broke".


    private
        array<string|int, int>
    HARMLESS_SQL_ERRORS
    = [
    1007,
    // ER_DB_CREATE_EXISTS
    1022,
    // ER_DUP_KEY
    1050,
    // ER_TABLE_EXISTS_ERROR
    1060,
    // ER_DUP_FIELDNAME: baseline already has the column
    1061,
    // ER_DUP_KEYNAME
    1091,
    // ER_CANT_DROP_FIELD_OR_KEY: already dropped
    1146,
    // ER_NO_SUCH_TABLE: legacy table a fresh install never had
    1826,
]

Old migrations rename or alter tables that a fresh install never had, because db/schema/baseline.sql already creates the modern schema. Those statements fail by design, so they must not be reported as failures — otherwise every healthy install would show migration errors.

alignReferenceColumnTypes()

Make every reference column use the same integer type as the key it points at.


    public
            static        alignReferenceColumnTypes() : void

LWT names foreign key columns after the primary key they reference: texts.TxLgID points at languages.LgID, word_occurrences.Ti2TxID at texts.TxID, and so on. Over the years several of those primary keys were widened (languages.LgID went from tinyint(3) to int(11) in 20251221_120000_add_inter_table_foreign_keys.sql) without every referencing column following along.

A mismatched pair makes InnoDB reject the foreign key with errno 150, "Foreign key constraint is incorrectly formed" — even under FOREIGN_KEY_CHECKS = 0. When the constraint sits inside a CREATE TABLE, the whole table is never created, which is how installs ended up without books or local_dictionaries and crashed with "Table 'books' doesn't exist" (issue #247).

Each family is aligned on its widest member, so a column is only ever widened, never narrowed: no value can be truncated. Families that already agree are left alone, making this a no-op on healthy installs.

Callers must have dropped foreign keys first — ALTER TABLE MODIFY is refused on a column an FK points at.

calculateChecksum()

Calculate SHA-256 checksum for a migration file.


    public
            static        calculateChecksum(string $filepath) : string

Parameters

$filepath : string: Full path to the migration file

Return values

string —

SHA-256 hash or empty string if file not readable

captureForeignKeys()

Read back every foreign key in the database, in enough detail to rebuild it.


    public
            static        captureForeignKeys() : array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}>

dropAllForeignKeys() clears the way for the migration run, but only the migrations that happen to be pending put constraints back. Everything created by an already-applied migration would be gone for good, so an upgrade used to strip the database of its referential integrity (cascade deletes, orphan protection) without a word. Capturing the set first is what lets restoreForeignKeys() put back what the run did not.

Return values

array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}> —

The database's foreign keys

checkAndUpdate()

Check and/or update the database.


    public
            static        checkAndUpdate() : void

dropAllForeignKeys()

Drop all foreign key constraints from all tables in the database.


    public
            static        dropAllForeignKeys() : void

This is needed before running migrations from scratch because SET FOREIGN_KEY_CHECKS = 0 only affects INSERT/UPDATE/DELETE and DROP TABLE, not ALTER TABLE MODIFY on columns referenced by FKs.

Callers that drop keys to make way for schema changes must capture them first (see captureForeignKeys()) and restore them afterwards.

getAppliedMigrations()

Get list of migrations that ran without any failing statement.


    public
            static        getAppliedMigrations() : array<string|int, string>

Return values

array<string|int, string> —

List of successfully applied migration filenames

getFailedMigrations()

Get the migrations that failed, with the error that made them fail.


    public
            static        getFailedMigrations() : array<string|int, array{filename: string, attempts: int, error: string}>

Meant for administrative reporting: a non-empty result means the schema is incomplete and some features will fail at runtime.

Return values

array<string|int, array{filename: string, attempts: int, error: string}> —

Failed migrations

getMigrationFiles()

Get list of all migration files from the migrations directory.


    public
            static        getMigrationFiles() : array<string|int, string>

Return values

array<string|int, string> —

Sorted list of migration filenames

getMissingForeignKeys()

The declared constraints that are still not there.


    public
            static        getMissingForeignKeys() : array<string|int, array{name: string, table: string}>

A non-empty result means writes that should be refused are being accepted, so it is worth showing to an administrator.

Return values

array<string|int, array{name: string, table: string}> —

Missing constraints

getRecordedMigrations()

Get list of migrations that have an entry in `_migrations`, whatever their outcome.


    public
            static        getRecordedMigrations() : array<string|int, string>

These are the migrations that have already been attempted at least once, so they are not "new" any more.

Return values

array<string|int, string> —

List of recorded migration filenames

getRetryableMigrations()

Get migrations that failed and are still worth retrying.


    public
            static        getRetryableMigrations() : array<string|int, string>

A migration is retried until MAX_ATTEMPTS is reached; past that it stays on record as failed so an administrator can investigate.

Return values

array<string|int, string> —

List of failed migration filenames

prefixQuery()

Add a prefix to table in a SQL query string.


    public
            static        prefixQuery(string $sql_line, string $prefix) : string

Parameters

$sql_line : string: SQL string to prefix.
$prefix : string: Prefix to add

Return values

string —

Prefixed SQL query

reconcileForeignKeys()

Add back the declared constraints an install has lost along the way.


    public
            static        reconcileForeignKeys() : int

Restoring a snapshot only preserves what a database still has. Releases before 3.4.0 dropped the constraints for every migration run and put back only the ones owned by pending migrations, so long-lived installs are missing constraints no snapshot can recover — half of them on a 3.3.0 database (#273). SchemaConstraints::FOREIGN_KEYS says what should be there; whatever is absent is added.

Rows that a missing constraint would have prevented are already in the database. Adding under FOREIGN_KEY_CHECKS = 0 accepts them and gates writes from here on; a constraint InnoDB still refuses is logged and reported by getMissingForeignKeys() rather than failing the upgrade.

Return values

int —

How many were added

recordMigration()

Record the outcome of a migration run.


    public
            static        recordMigration(string $filename[, string $checksum = '' ][, string $status = self::STATUS_APPLIED ][, string|null $error = null ]) : void

Re-recording the same migration keeps a single row: the status and error are overwritten and the attempt counter is incremented, so a migration that failed once and succeeds on a later upgrade ends up as applied.

Parameters

$filename : string: The migration filename
$checksum : string = '': SHA-256 hash of the migration file
$status : string = self::STATUS_APPLIED: STATUS_APPLIED or STATUS_FAILED
$error : string|null = null: Error message when the migration failed

reparseAllTexts()

Reparse all texts in order.


    public
            static        reparseAllTexts() : void

restoreForeignKeys()

Recreate captured foreign keys that are no longer there.


    public
            static        restoreForeignKeys(array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}> $keys) : int

Anything the migration run already put back, under the same name, is left alone. A key whose table or column has since been renamed or dropped is skipped: the migration that renamed it owns the new shape.

Parameters

$keys : array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}>: Foreign keys from captureForeignKeys()

Return values

int —

How many were restored

update()

Update the database if it is using an outdate version.


    public
            static        update() : void

upgradeMigrationsTable()

Upgrade the _migrations table from old schema to new schema.


    public
            static        upgradeMigrationsTable() : void

Old schema stored migrations to be run; new schema tracks applied migrations. This method adds the applied_at and checksum columns.

validateMigrationIntegrity()

Validate that applied migrations haven't been modified.


    public
            static        validateMigrationIntegrity() : array{valid: bool, errors: string[]}

Checks the checksum of each applied migration against its stored value. This detects tampering or accidental modification of migration files.

Return values

array{valid: bool, errors: string[]} —

Validation result

addMissingForeignKeys()

Create each of the given foreign keys that is not already there.


    private
            static        addMissingForeignKeys(array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}> $keys) : int

A key whose table or column is missing is skipped: on an install that never had the table, or where a migration renamed it, the constraint is not this method's business.

Parameters

$keys : array<string|int, array{name: string, table: string, columns: string[], refTable: string, refColumns: string[], onUpdate: string, onDelete: string}>: Constraints to ensure

Return values

int —

How many were created

columnFamilyChanges()

Work out which columns of one family are out of step with their key.


    private
            static        columnFamilyChanges(string $suffix) : array<string, array<string|int, string>>

Parameters

$suffix : string: Column name suffix identifying the family (e.g. 'LgID')

Return values

array<string, array<string|int, string>> —

MODIFY clauses, keyed by table

columnsStillExist()

Check that a table still has all of the given columns.


    private
            static        columnsStillExist(string $table, array<string|int, string> $columns) : bool

Parameters

$table : string: Table name
$columns : array<string|int, string>: Column names

Return values

bool —

True when every column is present

fetchMigrationNames()

Run a query returning a `filename` column and collect the values.


    private
            static        fetchMigrationNames(string $sql) : array<string|int, string>

Parameters

$sql : string: Query selecting the filename column

Return values

array<string|int, string> —

List of migration filenames

isHarmlessFailure()

Tell an expected statement failure apart from a real one.


    private
            static        isHarmlessFailure(RuntimeException $e) : bool

Parameters

$e : RuntimeException: Exception raised while running a statement

Return values

bool —

True when the failure can be safely ignored

runMigrationFile()

Run every statement of one migration file.


    private
            static        runMigrationFile(string $filepath, string $filename) : string|null

Statements are independent: one failing statement does not stop the others, because a migration often mixes work that is still needed with work a fresh install already has.

Parameters

$filepath : string: Full path to the migration file
$filename : string: Migration filename, for logging

Return values

string|null —

The first real error, or null when nothing broke

sanitizeReferentialAction()

Keep a referential action to the values MySQL accepts.


    private
            static        sanitizeReferentialAction(string $action) : string

The value comes from INFORMATION_SCHEMA rather than user input, but it is interpolated into DDL, so it is checked against the allowed set anyway.

Parameters

$action : string: Action read back from the database

Return values

string —

A safe ON DELETE/ON UPDATE action

Migrations in package Lwt

Tags

Table of Contents

Constants

Methods

Constants

MAX_ATTEMPTS

STATUS_APPLIED

STATUS_FAILED

HARMLESS_SQL_ERRORS

Methods

alignReferenceColumnTypes()

calculateChecksum()

Parameters

Return values

captureForeignKeys()

Return values

checkAndUpdate()

dropAllForeignKeys()

getAppliedMigrations()

Return values

getFailedMigrations()

Return values

getMigrationFiles()

Return values

getMissingForeignKeys()

Return values

getRecordedMigrations()

Return values

getRetryableMigrations()

Return values

prefixQuery()

Parameters

Return values

reconcileForeignKeys()

Return values

recordMigration()

Parameters

reparseAllTexts()

restoreForeignKeys()

Parameters

Return values

update()

upgradeMigrationsTable()

validateMigrationIntegrity()

Return values

addMissingForeignKeys()

Parameters

Return values

columnFamilyChanges()

Parameters

Return values

columnsStillExist()

Parameters

Return values

fetchMigrationNames()

Parameters

Return values

isHarmlessFailure()

Parameters

Return values

runMigrationFile()

Parameters

Return values

sanitizeReferentialAction()

Parameters

Return values

Migrations
in package

Lwt