📐 Plan

The Plan system is the schema migration module of Hector ORM. It provides a fluent, object-oriented API to describe DDL operations (CREATE TABLE, ALTER TABLE, DROP TABLE, …) and compile them into executable SQL statements for MySQL/MariaDB and SQLite.

A Plan does not execute SQL — it only produces statements. You decide when and how to execute them through your own Connection.

Quick start

use Hector\Connection\Connection;
use Hector\Schema\Index;
use Hector\Schema\Plan\Plan;
use Hector\Schema\Plan\Compiler\AutoCompiler;

$connection = new Connection('mysql:host=localhost;dbname=mydb', 'root', 'secret');
$compiler = new AutoCompiler($connection);

$plan = new Plan();

$plan->create('users', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('email', 'VARCHAR(255)');
    $table->addColumn('name', 'VARCHAR(100)', nullable: true);
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
    $table->addIndex('idx_email', ['email'], Index::UNIQUE);
});

foreach ($plan->getStatements($compiler) as $sql) {
    $connection->execute($sql);
}

Building a plan

A Plan is an ordered collection of entries. Each entry implements OperationInterface. Table operations ( CreateTable, AlterTable) group sub-operations (columns, indexes, foreign keys, triggers) for a single table; view operations (CreateView, AlterView, DropView), trigger operations (CreateTrigger, DropTrigger), atomic table operations (DropTable, PurgeTable, MigrateData), foreign key check operations (DisableForeignKeyChecks, EnableForeignKeyChecks) and raw statements (RawStatement) are standalone entries.

Plan implements Countable and IteratorAggregate.

When a callback is provided to create() or alter(), the method returns $this (the Plan) for chaining. Without a callback, it returns the CreateTable or AlterTable instance for direct manipulation.

Creating a table

Use create() with a callback to define columns, indexes and foreign keys in one go:

use Hector\Schema\ForeignKey;
use Hector\Schema\Index;
use Hector\Schema\Plan\Raw;

$plan->create('posts', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('title', 'VARCHAR(255)');
    $table->addColumn('body', 'TEXT', nullable: true);
    $table->addColumn('user_id', 'INT');
    $table->addColumn('created_at', 'DATETIME', default: new Raw('CURRENT_TIMESTAMP()'));

    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
    $table->addIndex('idx_user', ['user_id']);

    $table->addForeignKey(
        name: 'fk_posts_user',
        columns: ['user_id'],
        referencedTable: 'users',
        referencedColumns: ['id'],
        onDelete: ForeignKey::RULE_CASCADE,
    );
});

Without a callback, create() returns the CreateTable instance directly for chaining:

$table = $plan->create('tags');
$table->addColumn('id', 'INT', autoIncrement: true);
$table->addColumn('label', 'VARCHAR(50)');
$table->addIndex('PRIMARY', ['id'], Index::PRIMARY);

IF NOT EXISTS

$plan->create('users', callback: function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    // ...
}, ifNotExists: true);

Altering a table

Use alter() to add, modify, drop or rename columns, indexes and foreign keys on an existing table:

$plan->alter('users', function ($table) {
    $table->addColumn('avatar', 'VARCHAR(255)', nullable: true);
    $table->modifyColumn('name', 'VARCHAR(200)');
    $table->renameColumn('email', 'email_address');
    $table->dropColumn('legacy_field');

    $table->addIndex('idx_name', ['name']);
    $table->dropIndex('idx_old');

    $table->addForeignKey(
        name: 'fk_users_team',
        columns: ['team_id'],
        referencedTable: 'teams',
        referencedColumns: ['id'],
    );
    $table->dropForeignKey('fk_users_old');
});

Column placement (MySQL only)

addColumn() and modifyColumn() accept after and first parameters for column positioning:

$plan->alter('users', function ($table) {
    $table->addColumn('middle_name', 'VARCHAR(100)', nullable: true, after: 'first_name');
    $table->addColumn('row_id', 'INT', first: true);
});

Column defaults

A plain default value is emitted as a quoted string literal (e.g. DEFAULT 'active'). To emit a value verbatim — for SQL functions or keywords such as CURRENT_TIMESTAMP(), NOW() or UUID() — wrap it in Hector\Schema\Plan\Raw:

use Hector\Schema\Plan\Raw;

$plan->create('posts', function ($table) {
    // Literal string default: DEFAULT 'draft'
    $table->addColumn('status', 'VARCHAR(20)', default: 'draft');

    // Raw SQL expression: DEFAULT CURRENT_TIMESTAMP()
    $table->addColumn('created_at', 'DATETIME', default: new Raw('CURRENT_TIMESTAMP()'));
});

The hasDefault parameter of addColumn() / modifyColumn() is now ?bool and defaults to null (auto): the DEFAULT clause is enabled automatically when a Raw expression or any non-null value is provided. Pass an explicit boolean to force the behaviour — in particular, use hasDefault: true together with a null value to emit DEFAULT NULL.

Automatic update timestamps (MySQL / MariaDB)

addColumn() and modifyColumn() accept useCurrentOnUpdate: bool = false:

use Hector\Schema\Plan\Raw;

$plan->create('events', function ($table) {
    $table->addColumn('id', 'INTEGER', autoIncrement: true);
    $table->addIndex('PRIMARY', ['id'], \Hector\Schema\Index::PRIMARY);
    $table->addColumn(
        name: 'updated_at',
        type: 'TIMESTAMP',
        default: new Raw('CURRENT_TIMESTAMP'),
        useCurrentOnUpdate: true,
    );
});

On MySQL / MariaDB, this emits DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. The database updates the timestamp when another column actually changes, including changes made outside Hector. An explicit assignment to the timestamp takes precedence. The option does not implicitly add a DEFAULT clause.

Only TIMESTAMP and DATETIME, with optional fractional seconds precision from 0 to 6, are supported. For example, DATETIME(6) emits ON UPDATE CURRENT_TIMESTAMP(6); use a matching default: new Raw('CURRENT_TIMESTAMP(6)') if a current-time default is also needed on MySQL/MariaDB. An incompatible type or autoIncrement: true together with this option raises a PlanException on MySQL/MariaDB. This is a current-time option, not a general-purpose update expression or a foreign-key ON UPDATE rule.

On SQLite, the option is ignored without an exception or an automatically created trigger. The first example can therefore be used with MySQL in production and SQLite in tests: the insertion default works on both, but SQLite does not automatically update the timestamp. Test the database-side update behaviour against MySQL/MariaDB. Other SQL expressions still need to be compatible with the target engine; for example, CURRENT_TIMESTAMP(6) is not a SQLite expression.

When modifying a column, repeat useCurrentOnUpdate: true to retain the behaviour. Omitting the option or setting it to false removes the clause on MySQL/MariaDB:

$plan->alter('events')->modifyColumn(
    'updated_at',
    'TIMESTAMP',
    default: new Raw('CURRENT_TIMESTAMP'),
    useCurrentOnUpdate: false,
);

Generated columns

addColumn() and modifyColumn() accept generated: Generated|string|null = null. Use a string for a VIRTUAL column, or a Hector\Schema\Plan\Generated object to choose its storage mode:

use Hector\Schema\Plan\Generated;

$plan->create('order_lines', function ($table) {
    $table->addColumn('quantity', 'INTEGER');
    $table->addColumn('unit_price', 'INTEGER');

    // VIRTUAL: evaluated when read.
    $table->addColumn('total', 'INTEGER', generated: 'quantity * unit_price');

    // STORED: calculated and stored when the row is written.
    $table->addColumn(
        name: 'stored_total',
        type: 'INTEGER',
        generated: new Generated('quantity * unit_price', stored: true),
    );
});

new Generated('quantity * unit_price') is equivalent to the string shorthand: stored defaults to false. Generated exposes getExpression(): string and isStored(): bool and has no setters. An empty or whitespace-only expression raises a PlanException. Column operations normalize strings once and expose getGenerated(): ?Generated and isGenerated(): bool.

In generated, a string is always SQL, whereas a string in default is a literal value. There is no need to wrap the expression in Raw. Expressions are emitted verbatim and must be compatible with the target engine: Hector does not translate SQL functions or validate expression dependencies. Write the source columns when inserting or updating data; let the database compute generated values.

MySQL and SQLite honour the requested nullability of generated columns. MariaDB’s generated-column syntax does not accept NULL / NOT NULL, so those clauses are omitted there. Use AutoCompiler or provide the appropriate driver capabilities when compiling for MariaDB. Indexes on generated columns are subject to engine restrictions; InnoDB supports secondary indexes on VIRTUAL columns as well as STORED columns.

Generated definitions cannot be combined with an explicit default, autoIncrement: true, or useCurrentOnUpdate: true. This includes hasDefault: true with a null default, and a non-null default supplied even with hasDefault: false. nullable: true alone is accepted and does not introduce an implicit DEFAULT NULL on a generated column.

When modifying a generated column, supply its definition again:

$plan->alter('order_lines')->modifyColumn(
    'stored_total',
    'INTEGER',
    generated: new Generated('quantity * unit_price + 1', stored: true),
);

Omitting generated, or passing null, requests an ordinary column. Whether a conversion between ordinary, VIRTUAL and STORED columns is legal depends on the engine. SQLite rebuilds support these conversions; MySQL/MariaDB impose additional restrictions and some MariaDB virtual-column alterations require ALGORITHM=COPY. Use explicit SQL for operations that require engine-specific ALTER options.

See SQLite table rebuild for copying data and combined-rename behaviour, and column introspection for reading generated definitions back from the database.

Column character set and collation

addColumn() and modifyColumn() accept charset: ?string = null and collation: ?string = null. Names are native to the target database, not portable presets. Existing calls and table-level options keep their behavior.

For example, store canonical textual UUIDs in an ASCII column on MySQL/MariaDB, while keeping the same migration executable on SQLite:

use Hector\Schema\Plan\Plan;

$plan = new Plan();
$plan->create('documents', charset: 'utf8mb4', collation: 'utf8mb4_unicode_ci')
    ->addColumn('id', 'CHAR(36)', charset: 'ascii')
    ->addColumn('title', 'VARCHAR(255)')
    ->addIndex('uq_documents_id', ['id'], type: 'unique');

$alter = new Plan();
$alter->alter('documents')->modifyColumn('id', 'CHAR(36)', charset: 'ascii');

ASCII is a storage choice, not a portable input validator: SQLite still accepts non-ASCII text. ASCII characters already use one byte each in UTF-8; declaring ASCII does not imply a universal fourfold space saving. Binary UUID storage is a separate representation.

For explicit comparison behavior, choose a collation supported by your database:

// MySQL / MariaDB:
$table->addColumn('reference', 'VARCHAR(64)', charset: 'ascii', collation: 'ascii_bin');

// SQLite:
$table->addColumn('reference', 'VARCHAR(64)', collation: 'BINARY');

These are backend-specific alternatives, not equivalent portable presets. MySQL’s ascii_bin and SQLite’s BINARY differ on trailing spaces; SQLite’s NOCASE only folds ASCII case and is not equivalent to a Unicode case-/accent-insensitive MySQL collation. Unknown or incompatible native names are rejected by the database, without an automatic fallback.

Defaults and modifications:

• On MySQL/MariaDB, when neither option is supplied, text columns inherit the table defaults. A charset without a collation uses that charset’s default collation; a collation without a charset selects its associated charset. When both are supplied, they must be compatible.
• SQLite continues to ignore table-level charset/collation options. Column comparisons use BINARY unless an explicit column collation is supplied; there is no table-default inheritance.
• modifyColumn() describes the complete new column definition. Repeat options you want to retain. Omitting both returns to the table defaults on MySQL/MariaDB and the implicit BINARY collation on SQLite; it does not preserve the modified column’s previous options automatically.
• The same options work on generated text columns. Renames and unrelated table rebuilds preserve existing column options.
• SQLite column modification requires a table rebuild with a current schema. Pass the schema to getStatements($compiler, $schema) or use the migration runner with a supplied schema for transaction and foreign-key management. Re-read metadata after structural changes.

Rename column compatibility

renameColumn() uses the modern RENAME COLUMN syntax by default. When the compiler detects an older server (MySQL < 8.0, MariaDB < 10.5.2) via AutoCompiler, it automatically falls back to CHANGE COLUMN using the current column definition from the schema.

CHECK constraints

Use TableOperation::addCheck(string $name, Raw|string $expression): static on CREATE or ALTER, and AlterTable::dropCheck(string $name): static to remove a named constraint:

use Hector\Schema\Plan\Plan;
use Hector\Schema\Plan\Raw;

$create = new Plan();
$create->create('products')
    ->addColumn('price', 'INTEGER')
    ->addCheck('chk_price', 'price >= 0');

// A separate plan for an existing table:
$alter = new Plan();
$alter->alter('products')
    ->dropCheck('chk_price')
    ->addCheck('chk_price', new Raw('price >= 1'));

Names and expressions must be non-empty after trimming; invalid input raises PlanException immediately. Names are quoted as identifiers. Both strings and Raw contain SQL expressions emitted verbatim, without value binding, function translation or automatic sanitization. Expressions must be valid for the target engine. Do not include the enclosing CHECK (...). An expression evaluating to SQL NULL normally satisfies a CHECK; use a non-nullable column when NULL itself must be rejected.

Older MySQL/MariaDB, Vitess and unknown capabilities are rejected rather than emitting an unenforced constraint. PostgreSQL has native CHECK support, but Hector does not currently provide a PostgreSQL plan compiler. The fluent API adds enforced constraints; it does not provide a NOT ENFORCED option.

CHECK changes belong to the structure phase, retaining their order relative to other ALTER operations and surrounding raw statements. In particular, drop a CHECK before dropping a column it references, or before adding a replacement with the same name. They are not moved into the foreign-key pre/post phases.

Online and offline capabilities

With a connection, AutoCompiler resolves the engine and version automatically. Offline MySQL/MariaDB callers must explicitly provide capabilities:

use Hector\Connection\Driver\DriverInfo;
use Hector\Schema\Plan\Compiler\MySQLCompiler;

$capabilities = (new DriverInfo('mysql', '8.0.16'))->getCapabilities();
$compiler = new MySQLCompiler($capabilities);
$statements = [...$create->getStatements($compiler)];

Use DriverInfo('mariadb', '10.5.0') for MariaDB. new MySQLCompiler() remains usable for ordinary operations, but CHECK compilation without capabilities raises PlanException. Custom DriverCapabilities implementations must implement hasCheckConstraints() and hasDropCheck().

new SqliteCompiler() can compile CHECKs in CREATE TABLE offline. SQLite ALTER requires a current Schema, including complete CHECK metadata, to rebuild the existing table. Named and anonymous CHECKs survive unrelated rebuilds; anonymous constraints have no synthetic name for dropCheck(). Missing or ambiguous CHECK names are rejected during SQLite rebuild compilation.

Purging a table

Plan::purge(string|Table $table, bool $resetIncrement = false): static removes all rows while preserving the table structure. It accepts a table name or a schema Table object and returns the plan for chaining:

// Remove all rows without explicitly resetting the identifier counter
$plan->purge('articles');

// Remove all rows and explicitly reset the automatic identifier counter
$plan->purge('articles', resetIncrement: true);

The dialect selects the SQL according to the reset option:

No schema metadata is required to compile a purge. The PurgeTable operation runs at its declaration position among structure operations, after global pre-operations and before post-operations. Declare it before an alteration that requires an empty table:

$plan->purge('articles', resetIncrement: true);
$plan->alter('articles')
    ->modifyColumn('reference', 'VARCHAR(255)', nullable: false);

See Clearing data before a schema change for a complete migration.

SQLite counter reset

An explicit reset requires sqlite_sequence to exist. SQLite creates this system table when an AUTOINCREMENT table is created. If it exists but has no entry for the target table, deleting the sequence entry does nothing. If it does not exist, the reset fails with a normal SQL error. Within the migration runner’s SQLite transaction, that failure also rolls back the preceding deletion. Executing the SQL manually outside a transaction does not provide that guarantee.

For INTEGER PRIMARY KEY without AUTOINCREMENT, there is no persistent counter to reset and identifiers may be reused after a purge even with resetIncrement: false. That option means no explicit reset, not guaranteed identifier continuity. Reset values follow the database’s native counter rules.

Foreign keys and triggers

Purges are explicit: Hector does not infer them from schema changes, inspect incoming foreign keys, disable foreign-key checks, add CASCADE, or fall back from TRUNCATE to DELETE on failure. Native errors stop the migration. On MySQL/InnoDB, a foreign key from another table can prevent truncation even if that table is empty.

DELETE follows the database’s ON DELETE actions and fires deletion triggers; MySQL/MariaDB TRUNCATE does not fire those triggers. Changing resetIncrement therefore also changes these native effects on MySQL/MariaDB.

Transaction boundaries

MySQL/MariaDB TRUNCATE implicitly commits and cannot be rolled back. A later failure can leave a table purged while its migration remains pending; retrying runs the purge again. DELETE followed by ALTER TABLE is not globally rollback-safe either, because the alteration implicitly commits preceding changes before execution. An enclosing transaction cannot prevent that commit. A down() method does not automatically restore deleted data.

Custom dialects

Starting with version 1.5, custom implementations of DialectInterface must implement compilePurgeTable(PurgeTable $purgeTable): iterable, returning the SQL statements for their database.

Dropping a table

$plan->drop('old_table');
$plan->drop('maybe_exists', ifExists: true);

Renaming a table

$plan->rename('old_name', 'new_name');

You can also rename a table as part of an alter() call, combined with other operations:

$plan->alter('users', function ($table) {
    $table->addColumn('avatar', 'VARCHAR(255)', nullable: true);
    $table->renameTable('members');
});

Migrating data

Copy data from one table to another, optionally mapping columns:

// Copy all columns (SELECT *)
$plan->migrate('source_table', 'target_table');

// Map specific columns
$plan->migrate('source_table', 'target_table', [
    'old_name' => 'new_name',
    'old_email' => 'email_address',
]);

Views

Plans can also manage views with createView(), dropView() and alterView():

// Create a view
$plan->createView('active_users', 'SELECT * FROM users WHERE active = 1');

// Create or replace
$plan->createView('active_users', 'SELECT * FROM users WHERE active = 1', orReplace: true);

// With MySQL-specific algorithm
$plan->createView('active_users', 'SELECT * FROM users WHERE active = 1', algorithm: 'MERGE');

// Alter a view (replace its SELECT statement)
$plan->alterView('active_users', 'SELECT * FROM users WHERE active = 1 AND verified = 1');

// Drop a view
$plan->dropView('old_view', ifExists: true);

Triggers

Plans can create and drop triggers with createTrigger() and dropTrigger():

use Hector\Schema\Plan\CreateTrigger;

// Create a trigger (standalone)
$plan->createTrigger(
    name: 'trg_users_audit',
    table: 'users',
    timing: CreateTrigger::AFTER,
    event: CreateTrigger::INSERT,
    body: 'INSERT INTO audit_log (table_name, action) VALUES (\'users\', \'insert\')',
);

// Drop a trigger
$plan->dropTrigger('trg_users_audit', 'users');

Triggers can also be created inside create() or alter() calls:

$plan->create('users', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('name', 'VARCHAR(255)');
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);

    $table->createTrigger(
        'trg_users_audit',
        CreateTrigger::AFTER,
        CreateTrigger::INSERT,
        'INSERT INTO audit_log (table_name, action) VALUES (\'users\', \'insert\')',
    );
});

$plan->alter('users', function ($table) {
    $table->dropTrigger('trg_users_audit');
});

Available constants on CreateTrigger:

WHEN condition (SQLite only):

SQLite supports an optional WHEN condition on triggers. On MySQL, this parameter is ignored:

$plan->createTrigger(
    name: 'trg_status_change',
    table: 'users',
    timing: CreateTrigger::BEFORE,
    event: CreateTrigger::UPDATE,
    body: 'INSERT INTO audit_log (action) VALUES (\'status_changed\')',
    when: 'NEW.status != OLD.status',
);

Trigger ordering:

• CREATE TRIGGER implements PostOperationInterface — it is emitted after structure operations (columns, indexes, etc.), just like ADD FOREIGN KEY.
• DROP TRIGGER implements PreOperationInterface — it is emitted before structure operations, just like DROP FOREIGN KEY.

Foreign key checks

When performing operations that would violate foreign key constraints — such as dropping a table referenced by another, handling circular foreign keys, or performing large data migrations — you can explicitly disable and re-enable foreign key checks:

use Hector\Schema\Plan\DisableForeignKeyChecks;
use Hector\Schema\Plan\EnableForeignKeyChecks;

$plan = new Plan();

$plan->add(new DisableForeignKeyChecks());

$plan->drop('users');          // Would fail if referenced by other tables
$plan->drop('orders');
$plan->migrate('old_data', 'new_data', ['col_a' => 'col_b']);

$plan->add(new EnableForeignKeyChecks());

These operations leverage the compiler’s automatic ordering:

• DisableForeignKeyChecks implements PreOperationInterface — it is emitted first, before any other operation (including DROP FOREIGN KEY and DROP TRIGGER).
• EnableForeignKeyChecks implements PostOperationInterface — it is emitted last, after all other operations (including ADD FOREIGN KEY and CREATE TRIGGER).

This guarantees that FK checks are disabled for the entire plan execution, regardless of where you place the add() calls in your code.

Generated SQL per driver:

Raw SQL statements

For SQL features not covered by the Plan API (fulltext indexes, engine changes, spatial indexes, partitions, stored procedures, etc.), you can inject raw SQL statements that will be executed as-is:

use Hector\Schema\Index;

$plan->create('articles', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('title', 'VARCHAR(255)');
    $table->addColumn('body', 'TEXT');
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
});

// Fulltext index — not supported by the Plan API
$plan->raw('CREATE FULLTEXT INDEX ft_search ON articles (title, body)');

// Engine change
$plan->raw('ALTER TABLE articles ENGINE = InnoDB');

Raw statements bypass the compiler and are emitted verbatim during the structure pass, preserving their position relative to other plan entries.

Driver filter

Raw statements can be scoped to specific database drivers using the drivers parameter. When the compiler does not match any of the specified drivers, the statement is silently skipped:

// Only executed on MySQL / MariaDB / Vitess
$plan->raw('ALTER TABLE articles ENGINE = InnoDB', drivers: ['mysql']);

// Only executed on SQLite
$plan->raw('PRAGMA journal_mode = WAL', drivers: ['sqlite']);

// Executed on all drivers (default behavior)
$plan->raw('INSERT INTO migrations (name) VALUES (\'v1.3\')');

This is especially useful for mixed-environment migrations where development uses SQLite and production uses MySQL:

$plan->create('articles', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('title', 'VARCHAR(255)');
    $table->addColumn('body', 'TEXT');
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
});

// MySQL-only: fulltext index and engine
$plan->raw('CREATE FULLTEXT INDEX ft_search ON articles (title, body)', drivers: ['mysql']);
$plan->raw('ALTER TABLE articles ENGINE = InnoDB', drivers: ['mysql']);

// SQLite-only: WAL mode for better concurrency
$plan->raw('PRAGMA journal_mode = WAL', drivers: ['sqlite']);

Available driver names (matching Connection\Driver\DriverInfo::getDriver()):

Execution order with raw statements:

1. Pre-operations (global): DisableForeignKeyChecks, all DROP FOREIGN KEY, DROP TRIGGER from all entries
2. Structure + Raw (in declaration order): compiled structure operations interleaved with raw statements
3. Post-operations (global): all ADD FOREIGN KEY, CREATE TRIGGER, EnableForeignKeyChecks from all entries

This means raw statements are always executed after foreign key drops and before foreign key additions, which is the safest position for structural changes.

TableOperation operations

TableOperation is the abstract base class for CreateTable and AlterTable. It groups sub-operations (columns, indexes, foreign keys) for a single table. It implements Countable and IteratorAggregate.

Column operations (shared — CreateTable and AlterTable):

Column operations (ALTER only):

Index operations:

Foreign key operations:

CHECK operations (since 1.5):

Trigger operations:

Table-level operations (ALTER only):

Utility methods:

Compilers

A Plan is compiled into SQL statements by a CompilerInterface. Three implementations are provided:

All compilers implement CompilerInterface:

AutoCompiler

The simplest approach — pass your Connection and the correct compiler is resolved automatically:

use Hector\Schema\Plan\Compiler\AutoCompiler;

$compiler = new AutoCompiler($connection);

foreach ($plan->getStatements($compiler) as $sql) {
    $connection->execute($sql);
}

Supported drivers: mysql, mariadb, vitess, sqlite.

Using a specific compiler

use Hector\Schema\Plan\Compiler\MySQLCompiler;
use Hector\Schema\Plan\Compiler\SqliteCompiler;

$compiler = new MySQLCompiler();
// or
$compiler = new SqliteCompiler();

foreach ($plan->getStatements($compiler) as $sql) {
    echo $sql . ";\n";
}

Schema introspection

When a Schema object is provided to getStatements(), the compiler can introspect the current database state and adapt its strategy:

use Hector\Schema\Generator\MySQL as MySQLGenerator;

$generator = new MySQLGenerator($connection);
$schema = $generator->generateSchema('mydb');

foreach ($plan->getStatements($compiler, $schema) as $sql) {
    $connection->execute($sql);
}

With a schema, the compiler will:

• Check index existence before adding — if an index already exists, it emits a DROP INDEX + CREATE INDEX pair instead of failing
• Use table rebuild for SQLite when the operation requires it (e.g., MODIFY COLUMN, ADD/DROP FOREIGN KEY)

Many operations can compile without a schema. SQLite CHECK alterations and generated-column rebuilds require one and raise PlanException when it is missing. MySQL/MariaDB CHECK compilation requires explicit enforcement capabilities.

Operation ordering

Plan automatically reorders operations to avoid constraint violations and dependency issues:

1. Pre-operations (global): DisableForeignKeyChecks, DROP FOREIGN KEY, DROP TRIGGER — from all entries
2. Structure + Raw (in declaration order): CREATE/ALTER/DROP TABLE, columns, indexes, CHECKs, data migrations, views, and raw SQL statements
3. Post-operations (global): ADD FOREIGN KEY, CREATE TRIGGER, EnableForeignKeyChecks — from all entries

This happens transparently — you don’t need to worry about the order in which you add operations to the plan. Raw statements are interleaved with structure operations in the order you declared them.

use Hector\Schema\Index;

$plan = new Plan();

// These can be added in any order — FK ordering is automatic
$plan->create('orders', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('user_id', 'INT');
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
    $table->addForeignKey('fk_order_user', ['user_id'], 'users', ['id']);
});

$plan->raw('CREATE FULLTEXT INDEX ft_order_ref ON orders (reference)');

$plan->create('users', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('name', 'VARCHAR(100)');
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
});

// Execution order:
// 1. CREATE TABLE orders (structure)
// 2. CREATE FULLTEXT INDEX ft_order_ref ON orders (raw — in position)
// 3. CREATE TABLE users (structure)
// 4. ALTER TABLE orders ADD CONSTRAINT fk_order_user ... (post — last)
foreach ($plan->getStatements($compiler) as $sql) {
    $connection->execute($sql);
}

SQLite: table rebuild

SQLite has limited ALTER TABLE support — it cannot modify columns, add STORED generated columns, or add or drop foreign keys or CHECK constraints. When these operations are detected and a Schema is available, the SqliteCompiler automatically generates a table rebuild sequence:

1. PRAGMA foreign_keys = OFF
2. CREATE TABLE a temporary table with the new schema
3. INSERT INTO ... SELECT ... to migrate data from the original table
4. DROP TABLE the original table
5. ALTER TABLE ... RENAME TO ... the temporary table to the original name
6. Recreate non-primary indexes and attached triggers
7. PRAGMA foreign_keys = ON

The compiler handles this sequence from the supplied Schema and the requested operations.

// Works on both MySQL and SQLite
$plan = new Plan();
$plan->alter('users', function ($table) {
    $table->modifyColumn('name', 'VARCHAR(200)');
    $table->addForeignKey('fk_team', ['team_id'], 'teams', ['id']);
});

// On MySQL: ALTER TABLE users MODIFY COLUMN ..., ADD CONSTRAINT ...
// On SQLite: A table rebuild sequence
foreach ($plan->getStatements($compiler, $schema) as $sql) {
    $connection->execute($sql);
}

Table rebuild requires a current Schema with known CHECK metadata, including a confirmed-empty list when there are no constraints. Regenerate old serialized schemas before rebuilding. Manually constructed tables must supply their complete CHECK list via the checks: constructor argument.

For CHECK alterations, adding STORED columns or modifying generated definitions without the required schema raises a PlanException rather than emitting an unsupported native ALTER statement. Supply a current schema snapshot for the existing table, including the runner’s schema argument when using MigrationRunner.

Generated definitions and their storage modes survive rebuilds. The copy statement writes only surviving ordinary destination columns, leaving SQLite to calculate generated values. Converting a generated column to an ordinary column copies its former computed value. SQL defaults and secondary indexes are also preserved.

When a rename is combined with a rebuild, the compiler follows rename chains and updates references in generated expressions, indexes and local foreign keys (including self-references). It preserves SQL strings, comments, function names, CAST types and collation names. An ambiguous unquoted keyword reference raises a PlanException; use a separate native rename and refresh the schema before rebuilding in that case. Rebuilds with no surviving writable destination column also raise a PlanException instead of falling back to an incorrect SELECT * copy.

CHECK preservation and fidelity

CHECK-only changes use the native CREATE TABLE definition retained by SQLite introspection. This path also allows index changes and a table rename. It preserves named/anonymous CHECKs, column collations, conflict clauses, foreign-key clauses, generated definitions, STRICT and WITHOUT ROWID options. Explicit indexes (including expression/partial indexes) and attached triggers are recreated from their own definitions. Hidden rowids and AUTOINCREMENT sequence history are retained; generated destination values are recalculated during the copy.

Column/foreign-key changes use a structured rebuild. Column renames update retained CHECK expressions; dropping a column still referenced by a CHECK raises PlanException. Drop its named CHECK first in the same alter(). The compiler refuses transformations it cannot reproduce faithfully, including unsupported table clauses, column rename/drop with attached triggers, and unsafe expression/partial-index rewrites. Dependent views also prevent rebuilding; drop those dependencies, refresh the schema, rebuild, then recreate them in separate migrations. An implicit index cannot be altered independently of its constraint.

Combine changes to a table in one alter() group. A later rebuild of a table already structurally changed in the same plan is rejected because the supplied snapshot is stale. Raw DDL is not analyzed for schema changes: execute it separately and refresh the schema before a rebuild.

Execution and transactions

Use MigrationRunner to execute rebuilds atomically. It disables foreign keys before starting its transaction, verifies integrity before committing, and restores the original setting. It also refreshes supplied SQLite metadata between executed migrations.

If executing compiler output yourself, fully compile first, capture the original foreign_keys setting, disable it outside the transaction, and execute the rebuild statements inside a transaction. Run PRAGMA foreign_key_check before committing, roll back on any error, and restore the original setting after the transaction ends. The compiler’s OFF/ON statements alone do not provide rollback or integrity validation, and an unconditional ON does not preserve an initially disabled setting. SQLite cannot change foreign_keys inside an active transaction.

Complete example: migration script

This example demonstrates how operations can be declared in any order — the compiler automatically reorders them to avoid constraint violations:

use Hector\Connection\Connection;
use Hector\Schema\ForeignKey;
use Hector\Schema\Index;
use Hector\Schema\Generator\MySQL as MySQLGenerator;
use Hector\Schema\Plan\Compiler\AutoCompiler;
use Hector\Schema\Plan\CreateTrigger;
use Hector\Schema\Plan\Plan;

$connection = new Connection('mysql:host=localhost;dbname=mydb', 'root', 'secret');
$generator = new MySQLGenerator($connection);
$schema = $generator->generateSchema('mydb');
$compiler = new AutoCompiler($connection);

$plan = new Plan();

// 1. Alter posts — drop old FK, add new FK and a trigger
$plan->alter('posts', function ($table) {
    $table->dropForeignKey('fk_posts_author');
    $table->addColumn('category_id', 'INT', nullable: true);
    $table->addForeignKey(
        name: 'fk_posts_category',
        columns: ['category_id'],
        referencedTable: 'categories',
        referencedColumns: ['id'],
        onDelete: ForeignKey::RULE_SET_NULL,
    );
    $table->createTrigger(
        'trg_posts_audit',
        CreateTrigger::AFTER,
        CreateTrigger::INSERT,
        'INSERT INTO audit_log (table_name, action) VALUES (\'posts\', \'insert\')',
    );
});

// 2. Create the categories table (referenced by posts FK above)
$plan->create('categories', function ($table) {
    $table->addColumn('id', 'INT', autoIncrement: true);
    $table->addColumn('name', 'VARCHAR(100)');
    $table->addIndex('PRIMARY', ['id'], Index::PRIMARY);
    $table->addIndex('idx_name', ['name'], Index::UNIQUE);
});

// 3. MySQL-only: fulltext index on posts
$plan->raw('CREATE FULLTEXT INDEX ft_search ON posts (title, body)', drivers: ['mysql']);

// 4. Drop an old table
$plan->drop('legacy_posts', ifExists: true);

// Execute
foreach ($plan->getStatements($compiler, $schema) as $sql) {
    $connection->execute($sql);
}

The compiler produces SQL in this order:

-- Pass 1 — Pre-operations: FK drops first
ALTER TABLE `posts` DROP FOREIGN KEY `fk_posts_author`;

-- Pass 2 — Structure (in declaration order)
ALTER TABLE `posts` ADD COLUMN `category_id` INT NULL DEFAULT NULL;

CREATE TABLE `categories` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `idx_name` (`name`)
);

CREATE FULLTEXT INDEX ft_search ON posts (title, body);

DROP TABLE IF EXISTS `legacy_posts`;

-- Pass 3 — Post-operations: FK additions and trigger creations last
ALTER TABLE `posts` ADD CONSTRAINT `fk_posts_category` FOREIGN KEY (`category_id`)
  REFERENCES `categories` (`id`) ON DELETE SET NULL;

CREATE TRIGGER `trg_posts_audit` AFTER INSERT ON `posts`
  FOR EACH ROW INSERT INTO audit_log (table_name, action) VALUES ('posts', 'insert');

-- Pass 2 — Structure (in declaration order)
-- The ALTER on posts triggers a full table rebuild (drop FK + add FK):
PRAGMA foreign_keys = OFF;

CREATE TABLE "__htemp_xxx_posts" (
  "id" INT NOT NULL PRIMARY KEY AUTOINCREMENT,
  "title" VARCHAR(255) NOT NULL,
  "body" TEXT NULL,
  "author_id" INT NOT NULL,
  "category_id" INT NULL DEFAULT NULL,
  CONSTRAINT "fk_posts_category" FOREIGN KEY ("category_id")
    REFERENCES "categories" ("id") ON DELETE SET NULL
);

INSERT INTO "__htemp_xxx_posts" ("id", "title", "body", "author_id")
  SELECT "id", "title", "body", "author_id" FROM "posts";

DROP TABLE "posts";

ALTER TABLE "__htemp_xxx_posts" RENAME TO "posts";

PRAGMA foreign_keys = ON;

CREATE TABLE "categories" (
  "id" INT NOT NULL PRIMARY KEY AUTOINCREMENT,
  "name" VARCHAR(100) NOT NULL
);

CREATE UNIQUE INDEX "idx_name" ON "categories" ("name");

-- raw('CREATE FULLTEXT INDEX ...', drivers: ['mysql']) → skipped

DROP TABLE IF EXISTS "legacy_posts";

-- Pass 3 — Post-operations: trigger creation last
CREATE TRIGGER IF NOT EXISTS "trg_posts_audit" AFTER INSERT ON "posts"
  FOR EACH ROW BEGIN
    INSERT INTO audit_log (table_name, action) VALUES ('posts', 'insert');
  END