📐 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, 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;

$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: 'CURRENT_TIMESTAMP', hasDefault: true);

    $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);
});

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.

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:

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)

Without a schema, all operations are compiled optimistically — the compiler assumes the database supports them natively.

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, 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 or drop foreign keys. 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
7. PRAGMA foreign_keys = ON

This is fully transparent — you write the same Plan for both MySQL and SQLite.

// 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: Full table rebuild sequence (7 statements)
foreach ($plan->getStatements($compiler, $schema) as $sql) {
    $connection->execute($sql);
}

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