[RFC]: Documentation for Sql Ddl

[simon-mundy]

The documentation for sql-ddl.md (available at https://docs.laminas.dev/laminas-db/sql-ddl/ for reference) shows some detail but does not show the full scope of these classes and methods.

Looking to vastly increase the visibility of this class to assist in its uptake.

I have created a draft patch for review, open to comment please.

[simon-mundy]

DDL Abstraction

Table of Contents

1. Introduction & Overview
2. Creating Tables
3. Column Types Reference
4. Modifying Tables
5. Dropping Tables
6. Constraints In-Depth
7. Indexes
8. Advanced Features
9. Platform-Specific Features
10. Expression Objects
11. Column Options Reference
12. Error Handling
13. Best Practices
14. Complete Examples & Patterns

---

Introduction & Overview

PhpDb\Sql\Ddl is a sub-component of PhpDb\Sql that provides an object-oriented abstraction for DDL (Data Definition Language) SQL statements. It allows you to programmatically define database schemas in a platform-agnostic way.

What is DDL Abstraction?

DDL abstraction enables you to:

• Create, alter, and drop tables using PHP objects instead of raw SQL
• Define columns with platform-agnostic data types
• Add constraints (primary keys, foreign keys, unique constraints, checks)
• Create indexes for performance optimization
• Generate platform-specific SQL automatically for different databases

When to Use DDL Abstraction

Use PhpDb\Sql\Ddl when you need to:

• Create database migrations programmatically
• Build schema management tools
• Generate database schemas dynamically
• Ensure cross-platform compatibility for your database schema
• Maintain database structure alongside your application code

Platform Compatibility

When combined with a platform-specific PhpDb\Sql\Sql object, DDL objects produce optimized SQL statements for your target database platform. The system automatically handles:

• Platform-specific data type mappings
• Quoting and identifier formatting
• Constraint syntax differences
• Index creation syntax

Basic Workflow

The typical workflow for using DDL abstraction:

1. Create a DDL object (CreateTable, AlterTable, or DropTable)
2. Configure the object (add columns, constraints, etc.)
3. Generate SQL using Sql::buildSqlString()
4. Execute using Adapter::query() with QUERY_MODE_EXECUTE

use PhpDb\Sql\Sql;
use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;

// Assuming $adapter exists
$sql = new Sql($adapter);

// Create a DDL object
$table = new CreateTable('users');
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));

// Execute
$adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
);

---

Creating Tables

The CreateTable class represents a CREATE TABLE statement. You can build complex table definitions using a fluent, object-oriented interface.

Basic Table Creation

use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;

// Simple table
$table = new CreateTable('users');
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));

Generated SQL:

CREATE TABLE "users" (
    "id" INTEGER NOT NULL,
    "name" VARCHAR(255) NOT NULL
)

Setting the Table Name

You can set the table name during construction or after instantiation:

// During construction
$table = new CreateTable('products');

// After instantiation
$table = new CreateTable();
$table->setTable('products');

Schema-Qualified Tables

Use TableIdentifier to create tables in a specific schema:

use PhpDb\Sql\TableIdentifier;

// Create table in the "public" schema
$table = new CreateTable(new TableIdentifier('users', 'public'));

Generated SQL:

CREATE TABLE "public"."users" (...)

Temporary Tables

Create temporary tables by passing true as the second parameter:

$table = new CreateTable('temp_data', true);

// Or use the setter
$table = new CreateTable('temp_data');
$table->setTemporary(true);

Generated SQL:

CREATE TEMPORARY TABLE "temp_data" (...)

Adding Columns

Columns are added using the addColumn() method with column type objects:

use PhpDb\Sql\Ddl\Column;

$table = new CreateTable('products');

// Add various column types
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));
$table->addColumn(new Column\Text('description'));
$table->addColumn(new Column\Decimal('price', 10, 2));
$table->addColumn(new Column\Boolean('is_active'));
$table->addColumn(new Column\Timestamp('created_at'));

Adding Constraints

Table-level constraints are added using addConstraint():

use PhpDb\Sql\Ddl\Constraint;

// Primary key
$table->addConstraint(new Constraint\PrimaryKey('id'));

// Unique constraint
$table->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));

// Foreign key
$table->addConstraint(new Constraint\ForeignKey(
    'fk_user_role',    // Constraint name
    'role_id',         // Column in this table
    'roles',           // Referenced table
    'id',              // Referenced column
    'CASCADE',         // ON DELETE rule
    'CASCADE'          // ON UPDATE rule
));

// Check constraint
$table->addConstraint(new Constraint\Check('price > 0', 'check_positive_price'));

Column-Level Constraints

Columns can have constraints attached directly:

use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint;

// Create a primary key column
$id = new Column\Integer('id');
$id->addConstraint(new Constraint\PrimaryKey());
$table->addColumn($id);

Generated SQL:

"id" INTEGER NOT NULL PRIMARY KEY

Fluent Interface Pattern

All DDL objects support method chaining for cleaner code:

use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint;

$table = (new CreateTable('users'))
    ->addColumn(
        (new Column\Integer('id'))
            ->setNullable(false)
            ->addConstraint(new Constraint\PrimaryKey())
    )
    ->addColumn(
        (new Column\Varchar('email', 255))
            ->setNullable(false)
    )
    ->addConstraint(new Constraint\UniqueKey('email', 'unique_user_email'));

Complete Example: User Table

use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint;
use PhpDb\Sql\Ddl\Index\Index;

$table = new CreateTable('users');

// Auto-increment primary key
$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', true);
$id->addConstraint(new Constraint\PrimaryKey());
$table->addColumn($id);

// Basic columns
$table->addColumn(new Column\Varchar('username', 50));
$table->addColumn(new Column\Varchar('email', 255));
$table->addColumn(new Column\Varchar('password_hash', 255));

// Optional columns
$bio = new Column\Text('bio');
$bio->setNullable(true);
$table->addColumn($bio);

// Boolean (always NOT NULL)
$table->addColumn(new Column\Boolean('is_active'));

// Timestamps
$created = new Column\Timestamp('created_at');
$created->setDefault('CURRENT_TIMESTAMP');
$table->addColumn($created);

$updated = new Column\Timestamp('updated_at');
$updated->setDefault('CURRENT_TIMESTAMP');
$updated->setOption('on_update', true);
$table->addColumn($updated);

// Constraints
$table->addConstraint(new Constraint\UniqueKey('username', 'unique_username'));
$table->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));
$table->addConstraint(new Constraint\Check('email LIKE "%@%"', 'check_email_format'));

// Index for searches
$table->addConstraint(new Index(['username', 'email'], 'idx_user_search'));

// Execute
$sql = new Sql($adapter);
$adapter->query(
    $sql->buildSqlString($table),
    $adapter::QUERY_MODE_EXECUTE
);

---

Column Types Reference

All column types are in the PhpDb\Sql\Ddl\Column namespace and implement ColumnInterface.

Numeric Types

Integer

Standard integer column.

use PhpDb\Sql\Ddl\Column\Integer;

$column = new Integer('user_id');
$column = new Integer('count', false, 0); // NOT NULL with default 0

// With display length (platform-specific)
$column = new Integer('user_id');
$column->setOption('length', 11);

Constructor: __construct($name, $nullable = false, $default = null, array $options = [])

Methods:

• setNullable(bool $nullable): self
• isNullable(): bool
• setDefault(string|int|null $default): self
• getDefault(): string|int|null
• setOption(string $name, mixed $value): self
• setOptions(array $options): self

BigInteger

For larger integer values (typically 64-bit).

use PhpDb\Sql\Ddl\Column\BigInteger;

$column = new BigInteger('large_number');
$column = new BigInteger('id', false, null, ['length' => 20]);

Constructor: __construct($name, $nullable = false, $default = null, array $options = [])

Decimal

Fixed-point decimal numbers with precision and scale.

use PhpDb\Sql\Ddl\Column\Decimal;

$column = new Decimal('price', 10, 2); // DECIMAL(10,2)
$column = new Decimal('tax_rate', 5, 4); // DECIMAL(5,4)

// Can also be set after construction
$column = new Decimal('amount', 10);
$column->setDigits(12); // Change precision
$column->setDecimal(3); // Change scale

Constructor: __construct($name, $precision, $scale = null)

Methods:

• setDigits(int $digits): self - Set precision
• getDigits(): int - Get precision
• setDecimal(int $decimal): self - Set scale
• getDecimal(): int - Get scale

Floating

Floating-point numbers.

use PhpDb\Sql\Ddl\Column\Floating;

$column = new Floating('measurement', 10, 2);

// Adjustable after construction
$column->setDigits(12);
$column->setDecimal(4);

Constructor: __construct($name, $digits, $decimal)

Note: The old Float class is deprecated; use Floating instead.

String Types

Varchar

Variable-length character string.

use PhpDb\Sql\Ddl\Column\Varchar;

$column = new Varchar('name', 255);
$column = new Varchar('email', 320); // Max email length

// Can be nullable
$column = new Varchar('middle_name', 100);
$column->setNullable(true);

Constructor: __construct($name, $length)

Methods:

• setLength(int $length): self
• getLength(): int

Char

Fixed-length character string.

use PhpDb\Sql\Ddl\Column\Char;

$column = new Char('country_code', 2); // ISO country codes
$column = new Char('status', 1); // Single character status

Constructor: __construct($name, $length)

Text

Variable-length text for large strings.

use PhpDb\Sql\Ddl\Column\Text;

$column = new Text('description');
$column = new Text('content', 65535); // With length limit

// Can be nullable and have defaults
$column = new Text('notes', null, true, 'No notes');

Constructor: __construct($name, $length = null, $nullable = false, $default = null, array $options = [])

Binary Types

Binary

Fixed-length binary data.

use PhpDb\Sql\Ddl\Column\Binary;

$column = new Binary('hash', 32); // 32-byte hash

Constructor: __construct($name, $length, $nullable = false, $default = null, array $options = [])

Varbinary

Variable-length binary data.

use PhpDb\Sql\Ddl\Column\Varbinary;

$column = new Varbinary('file_data', 65535);

Constructor: __construct($name, $length)

Blob

Binary large object for very large binary data.

use PhpDb\Sql\Ddl\Column\Blob;

$column = new Blob('image');
$column = new Blob('document', 16777215); // MEDIUMBLOB size

Constructor: __construct($name, $length = null, $nullable = false, $default = null, array $options = [])

Date and Time Types

Date

Date without time.

use PhpDb\Sql\Ddl\Column\Date;

$column = new Date('birth_date');
$column = new Date('hire_date');

Constructor: __construct($name)

Time

Time without date.

use PhpDb\Sql\Ddl\Column\Time;

$column = new Time('start_time');
$column = new Time('duration');

Constructor: __construct($name)

Datetime

Date and time combined.

use PhpDb\Sql\Ddl\Column\Datetime;

$column = new Datetime('last_login');
$column = new Datetime('event_time');

Constructor: __construct($name)

Timestamp

Timestamp with special capabilities.

use PhpDb\Sql\Ddl\Column\Timestamp;

// Basic timestamp
$column = new Timestamp('created_at');
$column->setDefault('CURRENT_TIMESTAMP');

// With automatic update on row modification
$column = new Timestamp('updated_at');
$column->setDefault('CURRENT_TIMESTAMP');
$column->setOption('on_update', true);
// Generates: TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Constructor: __construct($name)

Special Options:

• on_update - When true, adds ON UPDATE CURRENT_TIMESTAMP

Boolean Type

Boolean

Boolean/bit column. Note: Boolean columns are always NOT NULL and cannot be made nullable.

use PhpDb\Sql\Ddl\Column\Boolean;

$column = new Boolean('is_active');
$column = new Boolean('is_verified');

// Attempting to make nullable has no effect
$column->setNullable(true); // Does nothing - stays NOT NULL

Constructor: __construct($name)

Important: The setNullable() method is overridden to always enforce NOT NULL.

Generic Column Type

Column

Generic column type (defaults to INTEGER). Use specific types when possible.

use PhpDb\Sql\Ddl\Column\Column;

$column = new Column('custom_field');

Constructor: __construct($name = null)

Common Column Methods

All column types share these methods:

// Nullable setting
$column->setNullable(true);  // Allow NULL values
$column->setNullable(false); // NOT NULL (default for most types)
$isNullable = $column->isNullable();

// Default values
$column->setDefault('default_value');
$column->setDefault(0);
$column->setDefault(null);
$default = $column->getDefault();

// Options (platform-specific features)
$column->setOption('AUTO_INCREMENT', true);
$column->setOption('comment', 'User identifier');
$column->setOption('length', 11);
$column->setOptions(['AUTO_INCREMENT' => true, 'comment' => 'ID']);

// Constraints (column-level)
$column->addConstraint(new Constraint\PrimaryKey());

// Name
$name = $column->getName();

---

Modifying Tables

The AlterTable class represents an ALTER TABLE statement. It provides methods to modify existing table structures.

Basic AlterTable Creation

use PhpDb\Sql\Ddl\AlterTable;
use PhpDb\Sql\TableIdentifier;

// Simple
$alter = new AlterTable('users');

// With schema
$alter = new AlterTable(new TableIdentifier('users', 'public'));

// Set after construction
$alter = new AlterTable();
$alter->setTable('users');

Adding Columns

Add new columns to an existing table:

use PhpDb\Sql\Ddl\AlterTable;
use PhpDb\Sql\Ddl\Column;

$alter = new AlterTable('users');

// Add a single column
$alter->addColumn(new Column\Varchar('phone', 20));

// Add multiple columns
$alter->addColumn(new Column\Varchar('city', 100));
$alter->addColumn(new Column\Varchar('country', 2));

Generated SQL:

ALTER TABLE "users"
ADD COLUMN "phone" VARCHAR(20) NOT NULL,
ADD COLUMN "city" VARCHAR(100) NOT NULL,
ADD COLUMN "country" VARCHAR(2) NOT NULL

Changing Columns

Modify existing column definitions:

$alter = new AlterTable('users');

// Change column type or properties
$alter->changeColumn('name', new Column\Varchar('name', 500));
$alter->changeColumn('age', new Column\Integer('age'));

// Rename and change at the same time
$alter->changeColumn('name', new Column\Varchar('full_name', 200));

Generated SQL:

ALTER TABLE "users"
CHANGE COLUMN "name" "full_name" VARCHAR(200) NOT NULL

Dropping Columns

Remove columns from a table:

$alter = new AlterTable('users');

$alter->dropColumn('old_field');
$alter->dropColumn('deprecated_column');

Generated SQL:

ALTER TABLE "users"
DROP COLUMN "old_field",
DROP COLUMN "deprecated_column"

Adding Constraints

Add table constraints:

use PhpDb\Sql\Ddl\Constraint;

$alter = new AlterTable('users');

// Add primary key
$alter->addConstraint(new Constraint\PrimaryKey('id'));

// Add unique constraint
$alter->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));

// Add foreign key
$alter->addConstraint(new Constraint\ForeignKey(
    'fk_user_department',
    'department_id',
    'departments',
    'id',
    'SET NULL',  // ON DELETE
    'CASCADE'    // ON UPDATE
));

// Add check constraint
$alter->addConstraint(new Constraint\Check('age >= 18', 'check_adult'));

Dropping Constraints

Remove constraints from a table:

$alter = new AlterTable('users');

$alter->dropConstraint('old_unique_key');
$alter->dropConstraint('fk_old_relation');

Generated SQL:

ALTER TABLE "users"
DROP CONSTRAINT "old_unique_key",
DROP CONSTRAINT "fk_old_relation"

Adding Indexes

Add indexes to improve query performance:

use PhpDb\Sql\Ddl\Index\Index;

$alter = new AlterTable('products');

// Simple index
$alter->addConstraint(new Index('name', 'idx_product_name'));

// Composite index
$alter->addConstraint(new Index(['category', 'price'], 'idx_category_price'));

// Index with column length specifications
$alter->addConstraint(new Index(
    ['title', 'description'],
    'idx_search',
    [50, 100] // Index first 50 chars of title, 100 of description
));

Dropping Indexes

Remove indexes from a table:

$alter = new AlterTable('products');

$alter->dropIndex('idx_old_search');
$alter->dropIndex('idx_deprecated');

Generated SQL:

ALTER TABLE "products"
DROP INDEX "idx_old_search",
DROP INDEX "idx_deprecated"

Complex AlterTable Example

Combine multiple operations in a single statement:

use PhpDb\Sql\Ddl\AlterTable;
use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint;
use PhpDb\Sql\Ddl\Index\Index;

$alter = new AlterTable('users');

// Add new columns
$alter->addColumn(new Column\Varchar('email', 255));
$alter->addColumn(new Column\Varchar('phone', 20));

$updated = new Column\Timestamp('updated_at');
$updated->setDefault('CURRENT_TIMESTAMP');
$updated->setOption('on_update', true);
$alter->addColumn($updated);

// Modify existing columns
$alter->changeColumn('name', new Column\Varchar('full_name', 200));

// Drop old columns
$alter->dropColumn('old_field');
$alter->dropColumn('deprecated_field');

// Add constraints
$alter->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));
$alter->addConstraint(new Constraint\ForeignKey(
    'fk_user_role',
    'role_id',
    'roles',
    'id',
    'CASCADE',
    'CASCADE'
));

// Drop old constraints
$alter->dropConstraint('old_constraint');

// Add index
$alter->addConstraint(new Index(['full_name', 'email'], 'idx_user_search'));

// Drop old index
$alter->dropIndex('idx_old_search');

// Execute
$sql = new Sql($adapter);
$adapter->query(
    $sql->buildSqlString($alter),
    $adapter::QUERY_MODE_EXECUTE
);

---

Dropping Tables

The DropTable class represents a DROP TABLE statement.

Basic Drop Table

use PhpDb\Sql\Ddl\DropTable;

// Simple
$drop = new DropTable('old_table');

// Execute
$sql = new Sql($adapter);
$adapter->query(
    $sql->buildSqlString($drop),
    $adapter::QUERY_MODE_EXECUTE
);

Generated SQL:

DROP TABLE "old_table"

Schema-Qualified Drop

use PhpDb\Sql\Ddl\DropTable;
use PhpDb\Sql\TableIdentifier;

$drop = new DropTable(new TableIdentifier('users', 'archive'));

Generated SQL:

DROP TABLE "archive"."users"

Dropping Multiple Tables

Execute multiple drop statements:

$tables = ['temp_table1', 'temp_table2', 'old_cache'];

foreach ($tables as $tableName) {
    $drop = new DropTable($tableName);
    $adapter->query(
        $sql->buildSqlString($drop),
        $adapter::QUERY_MODE_EXECUTE
    );
}

---

Constraints In-Depth

Constraints enforce data integrity rules at the database level. All constraints are in the PhpDb\Sql\Ddl\Constraint namespace.

Primary Key Constraints

A primary key uniquely identifies each row in a table.

Single-Column Primary Key

use PhpDb\Sql\Ddl\Constraint\PrimaryKey;

// Simple - name is optional
$pk = new Constraint\PrimaryKey('id');

// With explicit name
$pk = new Constraint\PrimaryKey('id', 'pk_users');

Composite Primary Key

Multiple columns together form the primary key:

// Composite primary key
$pk = new Constraint\PrimaryKey(['user_id', 'role_id']);

// With explicit name
$pk = new Constraint\PrimaryKey(
    ['user_id', 'role_id'],
    'pk_user_roles'
);

Column-Level Primary Key

Attach primary key directly to a column:

use PhpDb\Sql\Ddl\Column\Integer;
use PhpDb\Sql\Ddl\Constraint\PrimaryKey;

$id = new Integer('id');
$id->setOption('AUTO_INCREMENT', true);
$id->addConstraint(new PrimaryKey());

$table->addColumn($id);

Generated SQL:

"id" INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT

Foreign Key Constraints

Foreign keys enforce referential integrity between tables.

Basic Foreign Key

use PhpDb\Sql\Ddl\Constraint\ForeignKey;

$fk = new ForeignKey(
    'fk_order_customer',  // Constraint name (required)
    'customer_id',         // Column in this table
    'customers',           // Referenced table
    'id'                   // Referenced column
);

$table->addConstraint($fk);

Generated SQL:

CONSTRAINT "fk_order_customer" FOREIGN KEY ("customer_id")
    REFERENCES "customers" ("id")

Foreign Key with Referential Actions

Control what happens when referenced rows are deleted or updated:

$fk = new ForeignKey(
    'fk_order_customer',
    'customer_id',
    'customers',
    'id',
    'CASCADE',    // ON DELETE CASCADE - delete orders when customer is deleted
    'RESTRICT'    // ON UPDATE RESTRICT - prevent customer ID changes if orders exist
);

Available Actions:

• CASCADE - Propagate the change to dependent rows
• SET NULL - Set foreign key column to NULL
• RESTRICT - Prevent the change if dependent rows exist
• NO ACTION - Similar to RESTRICT (default)

Common Patterns:

// Delete child records when parent is deleted
$fk = new ForeignKey('fk_name', 'parent_id', 'parents', 'id', 'CASCADE');

// Set to NULL when parent is deleted (requires nullable column)
$fk = new ForeignKey('fk_name', 'parent_id', 'parents', 'id', 'SET NULL');

// Prevent deletion if child records exist
$fk = new ForeignKey('fk_name', 'parent_id', 'parents', 'id', 'RESTRICT');

Composite Foreign Key

Multiple columns reference multiple columns in another table:

$fk = new ForeignKey(
    'fk_user_tenant',
    ['user_id', 'tenant_id'],      // Local columns (array)
    'user_tenants',                 // Referenced table
    ['user_id', 'tenant_id'],      // Referenced columns (array)
    'CASCADE',
    'CASCADE'
);

Generated SQL:

CONSTRAINT "fk_user_tenant" FOREIGN KEY ("user_id", "tenant_id")
    REFERENCES "user_tenants" ("user_id", "tenant_id")
    ON DELETE CASCADE ON UPDATE CASCADE

Unique Constraints

Unique constraints ensure column values are unique across all rows.

Single-Column Unique Constraint

use PhpDb\Sql\Ddl\Constraint\UniqueKey;

// Simple - name is optional
$unique = new UniqueKey('email');

// With explicit name
$unique = new UniqueKey('email', 'unique_user_email');

$table->addConstraint($unique);

Generated SQL:

CONSTRAINT "unique_user_email" UNIQUE ("email")

Composite Unique Constraint

Multiple columns together must be unique:

// Username + tenant must be unique together
$unique = new UniqueKey(
    ['username', 'tenant_id'],
    'unique_username_per_tenant'
);

Generated SQL:

CONSTRAINT "unique_username_per_tenant" UNIQUE ("username", "tenant_id")

Check Constraints

Check constraints enforce custom validation rules.

Simple Check Constraints

use PhpDb\Sql\Ddl\Constraint\Check;

// Age must be 18 or older
$check = new Check('age >= 18', 'check_adult_age');
$table->addConstraint($check);

// Price must be positive
$check = new Check('price > 0', 'check_positive_price');
$table->addConstraint($check);

// Email must contain @
$check = new Check('email LIKE "%@%"', 'check_email_format');
$table->addConstraint($check);

Complex Check Constraints

// Discount percentage must be between 0 and 100
$check = new Check(
    'discount_percent >= 0 AND discount_percent <= 100',
    'check_valid_discount'
);

// End date must be after start date
$check = new Check(
    'end_date > start_date',
    'check_date_range'
);

// Status must be one of specific values
$check = new Check(
    "status IN ('pending', 'active', 'completed', 'cancelled')",
    'check_valid_status'
);

Constraint Naming Conventions

While some constraints allow optional names, it's a best practice to always provide explicit names:

// Good - explicit names for all constraints
$table->addConstraint(new Constraint\PrimaryKey('id', 'pk_users'));
$table->addConstraint(new Constraint\UniqueKey('email', 'unique_user_email'));
$table->addConstraint(new Constraint\ForeignKey(
    'fk_user_role',
    'role_id',
    'roles',
    'id'
));

// This makes it easier to drop or modify constraints later
$alter->dropConstraint('unique_user_email');
$alter->dropConstraint('fk_user_role');

Recommended Naming Patterns:

• Primary keys: pk_<table_name>
• Foreign keys: fk_<table>_<referenced_table> or fk_<table>_<column>
• Unique constraints: unique_<table>_<column> or unique_<descriptive_name>
• Check constraints: check_<descriptive_name>

---

Indexes

Indexes improve query performance by creating fast lookup structures. The Index class is in the PhpDb\Sql\Ddl\Index namespace.

Basic Index Creation

use PhpDb\Sql\Ddl\Index\Index;

// Single column index
$index = new Index('username', 'idx_username');
$table->addConstraint($index);

// With explicit name
$index = new Index('email', 'idx_user_email');
$table->addConstraint($index);

Generated SQL:

INDEX "idx_username" ("username")

Composite Indexes

Index multiple columns together:

// Index on category and price (useful for filtered sorts)
$index = new Index(['category', 'price'], 'idx_category_price');
$table->addConstraint($index);

// Index on last_name, first_name (useful for name searches)
$index = new Index(['last_name', 'first_name'], 'idx_name_search');
$table->addConstraint($index);

Generated SQL:

INDEX "idx_category_price" ("category", "price")

Index with Column Length Specifications

For large text columns, you can index only a prefix:

// Index first 50 characters of title
$index = new Index('title', 'idx_title', [50]);
$table->addConstraint($index);

// Composite index with different lengths per column
$index = new Index(
    ['title', 'description'],
    'idx_search',
    [50, 100]  // Index 50 chars of title, 100 of description
);
$table->addConstraint($index);

Generated SQL (platform-specific):

INDEX "idx_search" ("title"(50), "description"(100))

Why use length specifications?

• Reduces index size for large text columns
• Improves index creation and maintenance performance
• Particularly useful for VARCHAR/TEXT columns that store long content

Adding Indexes to Existing Tables

Use AlterTable to add indexes:

use PhpDb\Sql\Ddl\AlterTable;
use PhpDb\Sql\Ddl\Index\Index;

$alter = new AlterTable('products');

// Add single-column index
$alter->addConstraint(new Index('sku', 'idx_product_sku'));

// Add composite index
$alter->addConstraint(new Index(
    ['category_id', 'created_at'],
    'idx_category_date'
));

// Add index with length limit
$alter->addConstraint(new Index('description', 'idx_description', [200]));

Dropping Indexes

$alter = new AlterTable('products');
$alter->dropIndex('idx_old_search');
$alter->dropIndex('idx_deprecated_field');

Index Naming Conventions

Use descriptive names that indicate the purpose:

// Good index names
$index = new Index('email', 'idx_user_email');
$index = new Index(['last_name', 'first_name'], 'idx_name_search');
$index = new Index(['category', 'price'], 'idx_category_price');
$index = new Index('created_at', 'idx_created_date');

// Common patterns:
// - idx_<table>_<column>
// - idx_<purpose>
// - idx_<column1>_<column2> (for composite)

Complete Index Example

use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint;
use PhpDb\Sql\Ddl\Index\Index;

$table = new CreateTable('articles');

// Columns
$table->addColumn((new Column\Integer('id'))->addConstraint(new Constraint\PrimaryKey()));
$table->addColumn(new Column\Varchar('title', 255));
$table->addColumn(new Column\Text('content'));
$table->addColumn(new Column\Integer('category_id'));
$table->addColumn(new Column\Integer('author_id'));
$table->addColumn(new Column\Timestamp('published_at'));
$table->addColumn(new Column\Boolean('is_published'));

// Indexes for performance
$table->addConstraint(new Index('category_id', 'idx_category'));
$table->addConstraint(new Index('author_id', 'idx_author'));
$table->addConstraint(new Index('published_at', 'idx_published_date'));

// Composite indexes
$table->addConstraint(new Index(
    ['is_published', 'published_at'],
    'idx_published_articles'
));

$table->addConstraint(new Index(
    ['category_id', 'published_at'],
    'idx_category_date'
));

// Text search index with length limit
$table->addConstraint(new Index('title', 'idx_title_search', [100]));

---

Advanced Features

Column Options System

All columns support a flexible options system for platform-specific features and additional metadata.

Setting Options

use PhpDb\Sql\Ddl\Column;

$column = new Column\Integer('id');

// Set single option
$column->setOption('AUTO_INCREMENT', true);
$column->setOption('comment', 'Primary key identifier');

// Set multiple options at once
$column->setOptions([
    'AUTO_INCREMENT' => true,
    'comment' => 'User ID',
    'unsigned' => true,
]);

Common Options

AUTO_INCREMENT (MySQL, MariaDB):

$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', true);
// Generates: "id" INTEGER NOT NULL AUTO_INCREMENT

comment (Most platforms):

$column = new Column\Varchar('email', 255);
$column->setOption('comment', 'User email address');
// Generates: "email" VARCHAR(255) NOT NULL COMMENT 'User email address'

on_update (Timestamp columns):

$updated = new Column\Timestamp('updated_at');
$updated->setOption('on_update', true);
// Generates: "updated_at" TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP

length (Integer/BigInteger):

$column = new Column\Integer('user_id');
$column->setOption('length', 11);
// Generates: "user_id" INTEGER(11) NOT NULL

identity (SQL Server, PostgreSQL):

$id = new Column\Integer('id');
$id->setOption('identity', true);
// Platform-specific identity/serial generation

Nullable and Default Values

Setting Nullable

// NOT NULL (default for most types)
$column = new Column\Varchar('email', 255);
$column->setNullable(false);

// Allow NULL
$column = new Column\Varchar('middle_name', 100);
$column->setNullable(true);

// Check if nullable
if ($column->isNullable()) {
    // ...
}

Note: Boolean columns cannot be made nullable:

$column = new Column\Boolean('is_active');
$column->setNullable(true); // Has no effect - still NOT NULL

Setting Default Values

// String default
$column = new Column\Varchar('status', 20);
$column->setDefault('pending');

// Numeric default
$column = new Column\Integer('count');
$column->setDefault(0);

// SQL expression default
$column = new Column\Timestamp('created_at');
$column->setDefault('CURRENT_TIMESTAMP');

// NULL default (requires nullable column)
$column = new Column\Varchar('notes', 255);
$column->setNullable(true);
$column->setDefault(null);

// Get default value
$default = $column->getDefault();

Fluent Interface Patterns

All DDL objects support method chaining for cleaner, more readable code.

Chaining Column Configuration

$column = (new Column\Varchar('email', 255))
    ->setNullable(false)
    ->setDefault('user@example.com')
    ->setOption('comment', 'User email address')
    ->addConstraint(new Constraint\UniqueKey());

$table->addColumn($column);

Chaining Table Construction

$table = (new CreateTable('users'))
    ->addColumn(
        (new Column\Integer('id'))
            ->setOption('AUTO_INCREMENT', true)
            ->addConstraint(new Constraint\PrimaryKey())
    )
    ->addColumn(
        (new Column\Varchar('username', 50))
            ->setNullable(false)
    )
    ->addColumn(
        (new Column\Varchar('email', 255))
            ->setNullable(false)
    )
    ->addConstraint(new Constraint\UniqueKey('username', 'unique_username'))
    ->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));

Chaining Constraint Configuration

$fk = (new ForeignKey('fk_order_customer', 'customer_id', 'customers', 'id'))
    ->setName('fk_order_customer_id'); // Can rename if needed

// Primary key with multiple columns
$pk = (new PrimaryKey())
    ->addColumn('user_id')
    ->addColumn('role_id')
    ->setName('pk_user_roles');

Inspecting DDL Objects

Use getRawState() to inspect the internal configuration of DDL objects:

$table = new CreateTable('users');
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));
$table->addConstraint(new Constraint\PrimaryKey('id'));

// Get the internal state
$state = $table->getRawState();
print_r($state);

/* Output:
Array(
    [table] => users
    [temporary] => false
    [columns] => Array(...)
    [constraints] => Array(...)
)
*/

This is useful for:

• Debugging DDL object configuration
• Testing DDL generation
• Introspection and analysis tools

Working with Table Identifiers

Use TableIdentifier for schema-qualified table references:

use PhpDb\Sql\TableIdentifier;

// Table in default schema
$identifier = new TableIdentifier('users');

// Table in specific schema
$identifier = new TableIdentifier('users', 'public');
$identifier = new TableIdentifier('audit_log', 'audit');

// Use in DDL objects
$table = new CreateTable(new TableIdentifier('users', 'auth'));
$alter = new AlterTable(new TableIdentifier('products', 'inventory'));
$drop = new DropTable(new TableIdentifier('temp', 'scratch'));

// In foreign keys (schema.table syntax)
$fk = new ForeignKey(
    'fk_user_role',
    'role_id',
    new TableIdentifier('roles', 'auth'), // Referenced table with schema
    'id'
);

---

Platform-Specific Features

Current Status

Important: Platform-specific DDL decorators have been removed during refactoring. The decorator infrastructure exists in the codebase but specific platform implementations (MySQL, SQL Server, Oracle, SQLite) have been deprecated and removed.

What This Means

1. Platform specialization is handled at the Adapter Platform level, not the SQL DDL level
2. DDL objects are platform-agnostic - they define the structure, and the platform renders it appropriately
3. The decorator system can be used manually if needed via setTypeDecorator(), but this is advanced usage

Manual Decorator Usage (Advanced)

If you need custom platform-specific behavior, you can register decorators manually:

use PhpDb\Sql\Platform\Platform;
use PhpDb\Sql\Platform\PlatformDecoratorInterface;

$sqlPlatform = new Platform($adapter);

// Create a custom decorator
$customDecorator = new class implements PlatformDecoratorInterface {
    private $subject;

    public function setSubject($subject): void {
        $this->subject = $subject;
    }

    // Implement platform-specific logic here
};

// Register it for a specific type
$sqlPlatform->setTypeDecorator(CreateTable::class, $customDecorator);

Recommendation: Most users will not need to use decorators. Rely on the built-in platform rendering instead.

Platform-Agnostic Approach

The DDL abstraction is designed to work across platforms without modification:

// This code works on MySQL, PostgreSQL, SQL Server, SQLite, etc.
$table = new CreateTable('users');
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));

// The platform adapter handles rendering differences:
// - MySQL: CREATE TABLE `users` (`id` INT NOT NULL, `name` VARCHAR(255) NOT NULL)
// - PostgreSQL: CREATE TABLE "users" ("id" INTEGER NOT NULL, "name" VARCHAR(255) NOT NULL)
// - SQL Server: CREATE TABLE [users] ([id] INT NOT NULL, [name] VARCHAR(255) NOT NULL)

Platform-Specific Options

Use column options for platform-specific features:

// MySQL AUTO_INCREMENT
$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', true);

// PostgreSQL/SQL Server IDENTITY
$id = new Column\Integer('id');
$id->setOption('identity', true);

// MySQL UNSIGNED
$count = new Column\Integer('count');
$count->setOption('unsigned', true);

Note: Not all options work on all platforms. Test your DDL against your target database.

Best Practice: Platform Detection

// Check platform before using platform-specific options
$platformName = $adapter->getPlatform()->getName();

if ($platformName === 'MySQL') {
    $id->setOption('AUTO_INCREMENT', true);
} elseif (in_array($platformName, ['PostgreSQL', 'SqlServer'])) {
    $id->setOption('identity', true);
}

---

Expression Objects

Using Expressions in Check Constraints

Check constraints can accept either string expressions or Expression objects.

String Expressions (Simple)

For simple constraints, use strings:

use PhpDb\Sql\Ddl\Constraint\Check;

// Simple string expression
$check = new Check('age >= 18', 'check_adult');
$check = new Check('price > 0', 'check_positive_price');
$check = new Check("status IN ('active', 'pending', 'completed')", 'check_valid_status');

Expression Objects (Advanced)

For complex or parameterized constraints, use Expression objects:

use PhpDb\Sql\Expression;
use PhpDb\Sql\Ddl\Constraint\Check;

// Expression with parameters
$expr = new Expression(
    'age >= ? AND age <= ?',
    [18, 120]
);
$check = new Check($expr, 'check_valid_age_range');

// Complex expression
$expr = new Expression(
    'discount_percent BETWEEN ? AND ?',
    [0, 100]
);
$check = new Check($expr, 'check_discount_range');

Expression Constructor

The Expression class accepts:

// Simple expression without parameters
$expr = new Expression('column_name IS NOT NULL');

// Expression with single parameter
$expr = new Expression('age >= ?', 18);

// Expression with multiple parameters (array)
$expr = new Expression('value BETWEEN ? AND ?', [0, 100]);

// Expression with typed parameters (advanced)
use PhpDb\Sql\Argument;
use PhpDb\Sql\ArgumentType;

$expr = new Expression(
    'user_id = ?',
    new Argument(123, ArgumentType::Integer)
);

When to Use Expression Objects

Use strings when:

• The constraint is simple and static
• No parameters are needed
• The expression is platform-agnostic

Use Expression objects when:

• You need parameterized values
• Building constraints programmatically
• Reusing expressions across multiple constraints
• Working with complex conditional logic

Expression Methods

$expr = new Expression();

// Set the expression string
$expr->setExpression('age >= ? AND status = ?');

// Set parameters
$expr->setParameters([18, 'active']);

// Get the expression
$expression = $expr->getExpression(); // Returns: 'age >= ? AND status = ?'

Complete Example: Dynamic Check Constraints

use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint\Check;
use PhpDb\Sql\Expression;

$table = new CreateTable('products');

// Columns
$table->addColumn(new Column\Varchar('name', 255));
$table->addColumn(new Column\Decimal('price', 10, 2));
$table->addColumn(new Column\Decimal('discount_percent', 5, 2));

// Simple string check
$table->addConstraint(new Check(
    'price > 0',
    'check_positive_price'
));

// Expression-based check with parameters
$minDiscount = 0;
$maxDiscount = 50;

$discountExpr = new Expression(
    'discount_percent >= ? AND discount_percent <= ?',
    [$minDiscount, $maxDiscount]
);
$table->addConstraint(new Check($discountExpr, 'check_discount_range'));

// Complex conditional check
$complexExpr = new Expression(
    '(discount_percent = 0) OR (discount_percent >= 5 AND discount_percent <= 50)'
);
$table->addConstraint(new Check($complexExpr, 'check_meaningful_discount'));

---

Column Options Reference

Column options provide a flexible way to specify platform-specific features and metadata.

How Options Work

// Set single option
$column->setOption('option_name', 'option_value');

// Set multiple options
$column->setOptions([
    'option1' => 'value1',
    'option2' => 'value2',
]);

// Get all options
$options = $column->getOptions();

Documented Options

These options are tested and documented:

Option	Type	Platforms	Description	Example
AUTO_INCREMENT	bool	MySQL, MariaDB	Auto-incrementing integer	$col->setOption('AUTO_INCREMENT', true)
identity	bool	PostgreSQL, SQL Server	Identity/Serial column	$col->setOption('identity', true)
comment	string	MySQL, PostgreSQL	Column comment/description	$col->setOption('comment', 'User ID')
on_update	bool	MySQL (Timestamp)	ON UPDATE CURRENT_TIMESTAMP	$col->setOption('on_update', true)
length	int	MySQL (Integer)	Display width	$col->setOption('length', 11)

Platform-Specific Options

These options may work on specific platforms but are not guaranteed across all databases:

MySQL/MariaDB Specific

// UNSIGNED modifier
$column = new Column\Integer('count');
$column->setOption('unsigned', true);
// Generates: `count` INT UNSIGNED NOT NULL

// ZEROFILL modifier
$column = new Column\Integer('code');
$column->setOption('zerofill', true);
// Generates: `code` INT ZEROFILL NOT NULL

// Character set
$column = new Column\Varchar('name', 255);
$column->setOption('charset', 'utf8mb4');
// Generates: `name` VARCHAR(255) CHARACTER SET utf8mb4 NOT NULL

// Collation
$column = new Column\Varchar('name', 255);
$column->setOption('collation', 'utf8mb4_unicode_ci');
// Generates: `name` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL

PostgreSQL Specific

// SERIAL type (via identity option)
$id = new Column\Integer('id');
$id->setOption('identity', true);
// Generates: "id" SERIAL NOT NULL

// Note: PostgreSQL-specific types like ARRAY, JSONB, etc. may require
// custom column classes or platform-specific handling

SQL Server Specific

// IDENTITY column
$id = new Column\Integer('id');
$id->setOption('identity', true);
// Generates: [id] INT IDENTITY NOT NULL

Common Option Patterns

Auto-Incrementing Primary Key

// MySQL
$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', true);
$id->addConstraint(new Constraint\PrimaryKey());
$table->addColumn($id);

// PostgreSQL/SQL Server
$id = new Column\Integer('id');
$id->setOption('identity', true);
$id->addConstraint(new Constraint\PrimaryKey());
$table->addColumn($id);

Timestamp with Auto-Update

$updated = new Column\Timestamp('updated_at');
$updated->setDefault('CURRENT_TIMESTAMP');
$updated->setOption('on_update', true);
$table->addColumn($updated);
// MySQL: updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Documented Column with Comment

$column = new Column\Varchar('email', 255);
$column->setOption('comment', 'User email address for authentication');
$table->addColumn($column);

Option Compatibility Notes

Important Considerations:

1. Not all options work on all platforms - Test your DDL against your target database
2. Some options are silently ignored on unsupported platforms
3. Platform rendering varies - the same option may produce different SQL on different platforms
4. Options are not validated by DDL objects - invalid options may cause SQL errors during execution

---

Error Handling

DDL Error Behavior

Important: DDL objects themselves do not throw exceptions during construction or configuration. They are designed to build up state without validation.

Errors typically occur during:

1. SQL Generation - When buildSqlString() is called
2. Execution - When the adapter executes the DDL statement

Exception Types

DDL-related operations can throw:

use PhpDb\Sql\Exception\InvalidArgumentException;
use PhpDb\Sql\Exception\RuntimeException;
use PhpDb\Adapter\Exception\InvalidQueryException;

Common Error Scenarios

1. Empty Expression

use PhpDb\Sql\Expression;

try {
    $expr = new Expression(''); // Throws InvalidArgumentException
} catch (\PhpDb\Sql\Exception\InvalidArgumentException $e) {
    echo "Error: " . $e->getMessage();
    // Error: Supplied expression must not be an empty string.
}

2. SQL Execution Errors

use PhpDb\Sql\Sql;
use PhpDb\Sql\Ddl\CreateTable;

$table = new CreateTable('users');
// ... configure table ...

$sql = new Sql($adapter);

try {
    $adapter->query(
        $sql->buildSqlString($table),
        $adapter::QUERY_MODE_EXECUTE
    );
} catch (\Exception $e) {
    // Catch execution errors (syntax errors, constraint violations, etc.)
    echo "DDL execution failed: " . $e->getMessage();
}

3. Platform-Specific Errors

Different platforms may reject different DDL constructs:

// SQLite doesn't support DROP CONSTRAINT
$alter = new AlterTable('users');
$alter->dropConstraint('unique_email');

try {
    $adapter->query($sql->buildSqlString($alter), $adapter::QUERY_MODE_EXECUTE);
} catch (\Exception $e) {
    // SQLite will throw an error: ALTER TABLE syntax does not support DROP CONSTRAINT
    echo "Platform error: " . $e->getMessage();
}

Error Handling Best Practices

1. Wrap DDL Execution in Try-Catch

function createTable($adapter, $table) {
    $sql = new Sql($adapter);

    try {
        $adapter->query(
            $sql->buildSqlString($table),
            $adapter::QUERY_MODE_EXECUTE
        );
        return true;
    } catch (\PhpDb\Adapter\Exception\InvalidQueryException $e) {
        // SQL syntax or execution error
        error_log("DDL execution failed: " . $e->getMessage());
        return false;
    } catch (\Exception $e) {
        // General error
        error_log("Unexpected error: " . $e->getMessage());
        return false;
    }
}

2. Validate Platform Capabilities

function alterTable($adapter, $alterTable) {
    $platformName = $adapter->getPlatform()->getName();

    // Check if platform supports ALTER TABLE ... DROP CONSTRAINT
    if ($platformName === 'SQLite' && hasDropConstraint($alterTable)) {
        throw new \RuntimeException(
            'SQLite does not support DROP CONSTRAINT in ALTER TABLE'
        );
    }

    // Proceed with execution
    $sql = new Sql($adapter);
    $adapter->query($sql->buildSqlString($alterTable), $adapter::QUERY_MODE_EXECUTE);
}

3. Transaction Wrapping

use PhpDb\Adapter\Adapter;

function executeMigration($adapter, array $ddlObjects) {
    $connection = $adapter->getDriver()->getConnection();

    try {
        $connection->beginTransaction();

        $sql = new Sql($adapter);
        foreach ($ddlObjects as $ddl) {
            $adapter->query(
                $sql->buildSqlString($ddl),
                Adapter::QUERY_MODE_EXECUTE
            );
        }

        $connection->commit();
        return true;

    } catch (\Exception $e) {
        $connection->rollback();
        error_log("Migration failed: " . $e->getMessage());
        return false;
    }
}

Debugging DDL Issues

Use getRawState() for Inspection

$table = new CreateTable('users');
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));

// Inspect the DDL object state
$state = $table->getRawState();
print_r($state);

/*
Array(
    [table] => users
    [isTemporary] => false
    [columns] => Array(
        [0] => PhpDb\Sql\Ddl\Column\Integer Object
        [1] => PhpDb\Sql\Ddl\Column\Varchar Object
    )
    [constraints] => Array()
)
*/

Generate SQL Without Execution

$sql = new Sql($adapter);

// Generate the SQL string to see what will be executed
$sqlString = $sql->buildSqlString($table);
echo $sqlString . "\n";

// Review before executing
if (confirmExecution($sqlString)) {
    $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
}

Log DDL Statements

use PhpDb\Adapter\Adapter;

function executeDdl($adapter, $ddl, $logger) {
    $sql = new Sql($adapter);
    $sqlString = $sql->buildSqlString($ddl);

    // Log before execution
    $logger->info("Executing DDL: " . $sqlString);

    try {
        $adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
        $logger->info("DDL executed successfully");
    } catch (\Exception $e) {
        $logger->error("DDL execution failed: " . $e->getMessage());
        throw $e;
    }
}

---

Best Practices

Column Type Selection

Numeric Types

// Use Integer for most numeric IDs and counters
$id = new Column\Integer('id');           // -2,147,483,648 to 2,147,483,647
$count = new Column\Integer('view_count');

// Use BigInteger for very large numbers
$bigId = new Column\BigInteger('user_id'); // -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

// Use Decimal for money and precise calculations
$price = new Column\Decimal('price', 10, 2);     // DECIMAL(10,2) - $99,999,999.99
$tax = new Column\Decimal('tax_rate', 5, 4);     // DECIMAL(5,4) - 0.9999 (99.99%)

// Use Floating for scientific/approximate calculations (avoid for money!)
$latitude = new Column\Floating('lat', 10, 6);   // GPS coordinates
$measurement = new Column\Floating('temp', 5, 2); // Temperature readings

String Types

// Use Varchar for bounded strings with known max length
$email = new Column\Varchar('email', 320);       // Max email length (RFC 5321)
$username = new Column\Varchar('username', 50);
$countryCode = new Column\Varchar('country', 2); // ISO 3166-1 alpha-2

// Use Char for fixed-length strings
$statusCode = new Column\Char('status', 1);      // Single character: 'A', 'P', 'C'
$currencyCode = new Column\Char('currency', 3);  // ISO 4217: 'USD', 'EUR', 'GBP'

// Use Text for unbounded or very large strings
$description = new Column\Text('description');    // Product descriptions
$content = new Column\Text('article_content');    // Article content
$notes = new Column\Text('notes');                // User notes

Rule of Thumb:

• String <= 255 chars with known max → Varchar
• Fixed length → Char
• No length limit or very large → Text

Date/Time Types

// Use Date for dates without time
$birthDate = new Column\Date('birth_date');
$eventDate = new Column\Date('event_date');

// Use Time for times without date
$openTime = new Column\Time('opening_time');
$duration = new Column\Time('duration');

// Use Datetime for specific moments in time (platform-agnostic)
$appointmentTime = new Column\Datetime('appointment_at');
$publishedAt = new Column\Datetime('published_at');

// Use Timestamp for automatic tracking (created/updated)
$created = new Column\Timestamp('created_at');
$created->setDefault('CURRENT_TIMESTAMP');

$updated = new Column\Timestamp('updated_at');
$updated->setDefault('CURRENT_TIMESTAMP');
$updated->setOption('on_update', true);

Naming Conventions

Table Names

// Use plural, lowercase, snake_case
new CreateTable('users');           // Good
new CreateTable('user_roles');      // Good
new CreateTable('order_items');     // Good

new CreateTable('User');            // Avoid - capitalization issues
new CreateTable('userRole');        // Avoid - camelCase
new CreateTable('user');            // Avoid - singular (debatable)

Column Names

// Use singular, lowercase, snake_case
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('first_name', 100));
$table->addColumn(new Column\Integer('user_id')); // Foreign key

// Avoid
// 'firstName' - camelCase
// 'FirstName' - PascalCase
// 'FIRST_NAME' - all caps

Constraint Names

// Primary keys: pk_{table}
new Constraint\PrimaryKey('id', 'pk_users');

// Foreign keys: fk_{table}_{referenced_table} OR fk_{table}_{column}
new Constraint\ForeignKey('fk_order_customer', 'customer_id', 'customers', 'id');
new Constraint\ForeignKey('fk_order_user', 'user_id', 'users', 'id');

// Unique constraints: unique_{table}_{column} OR unique_{descriptive_name}
new Constraint\UniqueKey('email', 'unique_user_email');
new Constraint\UniqueKey(['tenant_id', 'username'], 'unique_tenant_username');

// Check constraints: check_{descriptive_name}
new Constraint\Check('age >= 18', 'check_adult_age');
new Constraint\Check('price > 0', 'check_positive_price');

Index Names

// idx_{table}_{column(s)} OR idx_{purpose}
new Index('email', 'idx_user_email');
new Index(['last_name', 'first_name'], 'idx_user_name');
new Index(['created_at', 'status'], 'idx_recent_active');

Index Strategy

When to Add Indexes

DO index:

• Primary keys (automatic in most platforms)
• Foreign key columns
• Columns frequently used in WHERE clauses
• Columns used in JOIN conditions
• Columns used in ORDER BY clauses
• Columns used in GROUP BY clauses

DON'T index:

• Very small tables (< 1000 rows)
• Columns with low cardinality (few unique values) like boolean
• Columns rarely used in queries
• Columns that change frequently in write-heavy tables

Index Best Practices

// 1. Index foreign keys
$table->addColumn(new Column\Integer('user_id'));
$table->addConstraint(new Constraint\ForeignKey(
    'fk_order_user',
    'user_id',
    'users',
    'id'
));
$table->addConstraint(new Index('user_id', 'idx_user'));

// 2. Composite indexes for common query patterns
// If you often query: WHERE category_id = ? ORDER BY created_at DESC
$table->addConstraint(new Index(['category_id', 'created_at'], 'idx_category_date'));

// 3. Covering indexes (columns used together in WHERE/ORDER)
// Query: WHERE status = 'active' AND priority = 'high' ORDER BY created_at
$table->addConstraint(new Index(['status', 'priority', 'created_at'], 'idx_active_priority'));

// 4. Prefix indexes for large text columns
$table->addConstraint(new Index('title', 'idx_title', [100])); // Index first 100 chars

Index Order Matters

// For query: WHERE category_id = ? ORDER BY created_at DESC
new Index(['category_id', 'created_at'], 'idx_category_date'); // Good

// Less effective for the same query:
new Index(['created_at', 'category_id'], 'idx_date_category'); // Not optimal

Rule: Most selective (filters most rows) columns should come first.

Schema Migration Patterns

Pattern 1: Versioned Migrations

class Migration_001_CreateUsersTable {
    public function up($adapter) {
        $sql = new Sql($adapter);
        $table = new CreateTable('users');

        $id = new Column\Integer('id');
        $id->setOption('AUTO_INCREMENT', true);
        $id->addConstraint(new Constraint\PrimaryKey());
        $table->addColumn($id);

        $table->addColumn(new Column\Varchar('email', 255));
        $table->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));

        $adapter->query($sql->buildSqlString($table), $adapter::QUERY_MODE_EXECUTE);
    }

    public function down($adapter) {
        $sql = new Sql($adapter);
        $drop = new DropTable('users');
        $adapter->query($sql->buildSqlString($drop), $adapter::QUERY_MODE_EXECUTE);
    }
}

Pattern 2: Safe Migrations

// Check if table exists before creating
function safeCreateTable($adapter, $tableName, $ddlObject) {
    $sql = new Sql($adapter);

    // Check existence (platform-specific)
    $platformName = $adapter->getPlatform()->getName();

    $exists = false;
    if ($platformName === 'MySQL') {
        $result = $adapter->query(
            "SHOW TABLES LIKE '$tableName'",
            $adapter::QUERY_MODE_EXECUTE
        );
        $exists = $result->count() > 0;
    }

    if (!$exists) {
        $adapter->query(
            $sql->buildSqlString($ddlObject),
            $adapter::QUERY_MODE_EXECUTE
        );
    }
}

Pattern 3: Idempotent Migrations

// Use IF NOT EXISTS (platform-specific)
// Note: PhpDb DDL doesn't support IF NOT EXISTS directly
// You'll need to handle this at the SQL level or check existence first

function createTableIfNotExists($adapter, $tableName, CreateTable $table) {
    $sql = new Sql($adapter);
    $platformName = $adapter->getPlatform()->getName();

    if ($platformName === 'MySQL') {
        // Manually construct IF NOT EXISTS
        $sqlString = $sql->buildSqlString($table);
        $sqlString = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $sqlString);
        $adapter->query($sqlString, $adapter::QUERY_MODE_EXECUTE);
    } else {
        // Fallback: check and create
        safeCreateTable($adapter, $tableName, $table);
    }
}

Performance Considerations

1. Batch Multiple DDL Operations

// Bad: Multiple ALTER TABLE statements
$alter1 = new AlterTable('users');
$alter1->addColumn(new Column\Varchar('phone', 20));
$adapter->query($sql->buildSqlString($alter1), $adapter::QUERY_MODE_EXECUTE);

$alter2 = new AlterTable('users');
$alter2->addColumn(new Column\Varchar('city', 100));
$adapter->query($sql->buildSqlString($alter2), $adapter::QUERY_MODE_EXECUTE);

// Good: Single ALTER TABLE with multiple operations
$alter = new AlterTable('users');
$alter->addColumn(new Column\Varchar('phone', 20));
$alter->addColumn(new Column\Varchar('city', 100));
$adapter->query($sql->buildSqlString($alter), $adapter::QUERY_MODE_EXECUTE);

2. Add Indexes After Bulk Insert

// For large initial data loads:

// 1. Create table without indexes
$table = new CreateTable('products');
$table->addColumn(new Column\Integer('id'));
$table->addColumn(new Column\Varchar('name', 255));
// ... more columns ...
$adapter->query($sql->buildSqlString($table), $adapter::QUERY_MODE_EXECUTE);

// 2. Load data
// ... insert thousands of rows ...

// 3. Add indexes after data is loaded
$alter = new AlterTable('products');
$alter->addConstraint(new Index('name', 'idx_name'));
$alter->addConstraint(new Index(['category_id', 'price'], 'idx_category_price'));
$adapter->query($sql->buildSqlString($alter), $adapter::QUERY_MODE_EXECUTE);

3. Foreign Key Impact

Foreign keys add overhead to INSERT/UPDATE/DELETE operations:

// If you need to bulk load data, consider:
// 1. Disable foreign key checks (platform-specific)
// 2. Load data
// 3. Re-enable foreign key checks

// MySQL example (outside DDL abstraction):
$adapter->query('SET FOREIGN_KEY_CHECKS = 0', $adapter::QUERY_MODE_EXECUTE);
// ... bulk operations ...
$adapter->query('SET FOREIGN_KEY_CHECKS = 1', $adapter::QUERY_MODE_EXECUTE);

Testing DDL Changes

1. Test on Development Copy

// Always test DDL on a copy of production data
$devAdapter = new Adapter($devConfig);
$prodAdapter = new Adapter($prodConfig);

// Test migration on dev first
try {
    executeMigration($devAdapter, $ddlObjects);
    echo "Dev migration successful\n";

    // If successful, run on production
    executeMigration($prodAdapter, $ddlObjects);
} catch (\Exception $e) {
    echo "Migration failed on dev: " . $e->getMessage() . "\n";
    // Don't touch production
}

2. Generate and Review SQL

// Generate DDL SQL and review before executing
$sql = new Sql($adapter);

foreach ($ddlObjects as $ddl) {
    $sqlString = $sql->buildSqlString($ddl);
    echo $sqlString . ";\n\n";
}

// Review output, then execute if satisfied

3. Backup Before DDL

function executeSafeDdl($adapter, $ddl) {
    // 1. Backup (implementation depends on platform)
    backupDatabase($adapter);

    // 2. Execute DDL
    try {
        $sql = new Sql($adapter);
        $adapter->query(
            $sql->buildSqlString($ddl),
            $adapter::QUERY_MODE_EXECUTE
        );
        return true;
    } catch (\Exception $e) {
        // 3. Restore on failure
        restoreDatabase($adapter);
        throw $e;
    }
}

---

Complete Examples & Patterns

Example 1: E-Commerce Product Table

use PhpDb\Sql\Ddl\CreateTable;
use PhpDb\Sql\Ddl\Column;
use PhpDb\Sql\Ddl\Constraint;
use PhpDb\Sql\Ddl\Index\Index;

$table = new CreateTable('products');

// Primary key with auto-increment
$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', true);
$id->addConstraint(new Constraint\PrimaryKey());
$table->addColumn($id);

// Basic product info
$table->addColumn(new Column\Varchar('sku', 50));
$table->addColumn(new Column\Varchar('name', 255));
$table->addColumn(new Column\Text('description'));

// Pricing
$table->addColumn(new Column\Decimal('price', 10, 2));
$table->addColumn(new Column\Decimal('cost', 10, 2));

// Inventory
$table->addColumn(new Column\Integer('stock_quantity'));

// Foreign key to category
$table->addColumn(new Column\Integer('category_id'));
$table->addConstraint(new Constraint\ForeignKey(
    'fk_product_category',
    'category_id',
    'categories',
    'id',
    'RESTRICT',  // Don't allow category deletion if products exist
    'CASCADE'    // Update category_id if category.id changes
));

// Status and flags
$table->addColumn(new Column\Boolean('is_active'));
$table->addColumn(new Column\Boolean('is_featured'));

// Timestamps
$created = new Column\Timestamp('created_at');
$created->setDefault('CURRENT_TIMESTAMP');
$table->addColumn($created);

$updated = new Column\Timestamp('updated_at');
$updated->setDefault('CURRENT_TIMESTAMP');
$updated->setOption('on_update', true);
$table->addColumn($updated);

// Constraints
$table->addConstraint(new Constraint\UniqueKey('sku', 'unique_product_sku'));
$table->addConstraint(new Constraint\Check('price >= cost', 'check_profitable_price'));
$table->addConstraint(new Constraint\Check('stock_quantity >= 0', 'check_non_negative_stock'));

// Indexes for performance
$table->addConstraint(new Index('category_id', 'idx_category'));
$table->addConstraint(new Index('sku', 'idx_sku'));
$table->addConstraint(new Index(['is_active', 'is_featured'], 'idx_active_featured'));
$table->addConstraint(new Index('name', 'idx_name_search', [100]));

// Execute
$sql = new Sql($adapter);
$adapter->query($sql->buildSqlString($table), $adapter::QUERY_MODE_EXECUTE);

Example 2: User Authentication System

// Users table
$users = new CreateTable('users');

$id = new Column\Integer('id');
$id->setOption('AUTO_INCREMENT', true);
$id->addConstraint(new Constraint\PrimaryKey());
$users->addColumn($id);

$users->addColumn(new Column\Varchar('username', 50));
$users->addColumn(new Column\Varchar('email', 255));
$users->addColumn(new Column\Varchar('password_hash', 255));

$lastLogin = new Column\Timestamp('last_login');
$lastLogin->setNullable(true);
$users->addColumn($lastLogin);

$users->addColumn(new Column\Boolean('is_active'));
$users->addColumn(new Column\Boolean('is_verified'));

$users->addConstraint(new Constraint\UniqueKey('username', 'unique_username'));
$users->addConstraint(new Constraint\UniqueKey('email', 'unique_email'));
$users->addConstraint(new Index(['username', 'email'], 'idx_user_search'));

// Execute
$adapter->query($sql->buildSqlString($users), $adapter::QUERY_MODE_EXECUTE);

// Roles table
$roles = new CreateTable('roles');

$roleId = new Column\Integer('id');
$roleId->setOption('AUTO_INCREMENT', true);
$roleId->addConstraint(new Constraint\PrimaryKey());
$roles->addColumn($roleId);

$roles->addColumn(new Column\Varchar('name', 50));
$roles->addColumn(new Column\Text('description'));
$roles->addConstraint(new Constraint\UniqueKey('name', 'unique_role_name'));

$adapter->query($sql->buildSqlString($roles), $adapter::QUERY_MODE_EXECUTE);

// User-Role junction table
$userRoles = new CreateTable('user_roles');

$userRoles->addColumn(new Column\Integer('user_id'));
$userRoles->addColumn(new Column\Integer('role_id'));

// Composite primary key
$userRoles->addConstraint(new Constraint\PrimaryKey(['user_id', 'role_id']));

// Foreign keys
$userRoles->addConstraint(new Constraint\ForeignKey(
    'fk_user_role_user',
    'user_id',
    'users',
    'id',
    'CASCADE',  // Delete role assignments when user is deleted
    'CASCADE'
));

$userRoles->addConstraint(new Constraint\ForeignKey(
    'fk_user_role_role',
    'role_id',
    'roles',
    'id',
    'CASCADE',  // Delete role assignments when role is deleted
    'CASCADE'
));

// Indexes
$userRoles->addConstraint(new Index('user_id', 'idx_user'));
$userRoles->addConstraint(new Index('role_id', 'idx_role'));

$adapter->query($sql->buildSqlString($userRoles), $adapter::QUERY_MODE_EXECUTE);

Example 3: Multi-Tenant Schema

use PhpDb\Sql\TableIdentifier;

// Tenants table (in public schema)
$tenants = new CreateTable(new TableIdentifier('tenants', 'public'));

$tenantId = new Column\Integer('id');
$tenantId->setOption('AUTO_INCREMENT', true);
$tenantId->addConstraint(new Constraint\PrimaryKey());
$tenants->addColumn($tenantId);

$tenants->addColumn(new Column\Varchar('name', 255));
$tenants->addColumn(new Column\Varchar('subdomain', 100));
$tenants->addColumn(new Column\Boolean('is_active'));

$tenants->addConstraint(new Constraint\UniqueKey('subdomain', 'unique_subdomain'));

$adapter->query($sql->buildSqlString($tenants), $adapter::QUERY_MODE_EXECUTE);

// Tenant-specific users table (in tenant schema)
$tenantUsers = new CreateTable(new TableIdentifier('users', 'tenant_schema'));

$userId = new Column\Integer('id');
$userId->setOption('AUTO_INCREMENT', true);
$userId->addConstraint(new Constraint\PrimaryKey());
$tenantUsers->addColumn($userId);

$tenantUsers->addColumn(new Column\Integer('tenant_id'));
$tenantUsers->addColumn(new Column\Varchar('username', 50));
$tenantUsers->addColumn(new Column\Varchar('email', 255));

// Composite unique constraint (username unique per tenant)
$tenantUsers->addConstraint(new Constraint\UniqueKey(
    ['tenant_id', 'username'],
    'unique_tenant_username'
));

// Foreign key to public.tenants
$tenantUsers->addConstraint(new Constraint\ForeignKey(
    'fk_user_tenant',
    'tenant_id',
    new TableIdentifier('tenants', 'public'),
    'id',
    'CASCADE',
    'CASCADE'
));

$adapter->query($sql->buildSqlString($tenantUsers), $adapter::QUERY_MODE_EXECUTE);

Example 4: Database Migration Pattern

use PhpDb\Sql\Sql;
use PhpDb\Sql\Ddl;

class Migration_001_CreateUsersTable
{
    public function up($adapter)
    {
        $sql = new Sql($adapter);

        $table = new Ddl\CreateTable('users');

        $id = new Ddl\Column\Integer('id');
        $id->setOption('AUTO_INCREMENT', true);
        $id->addConstraint(new Ddl\Constraint\PrimaryKey());
        $table->addColumn($id);

        $table->addColumn(new Ddl\Column\Varchar('email', 255));
        $table->addColumn(new Ddl\Column\Varchar('password_hash', 255));
        $table->addColumn(new Ddl\Column\Boolean('is_active'));

        $table->addConstraint(new Ddl\Constraint\UniqueKey('email', 'unique_email'));

        $adapter->query(
            $sql->buildSqlString($table),
            $adapter::QUERY_MODE_EXECUTE
        );
    }

    public function down($adapter)
    {
        $sql = new Sql($adapter);
        $drop = new Ddl\DropTable('users');

        $adapter->query(
            $sql->buildSqlString($drop),
            $adapter::QUERY_MODE_EXECUTE
        );
    }
}

class Migration_002_AddUserProfiles
{
    public function up($adapter)
    {
        $sql = new Sql($adapter);

        $alter = new Ddl\AlterTable('users');

        $alter->addColumn(new Ddl\Column\Varchar('first_name', 100));
        $alter->addColumn(new Ddl\Column\Varchar('last_name', 100));

        $bio = new Ddl\Column\Text('bio');
        $bio->setNullable(true);
        $alter->addColumn($bio);

        $adapter->query(
            $sql->buildSqlString($alter),
            $adapter::QUERY_MODE_EXECUTE
        );
    }

    public function down($adapter)
    {
        $sql = new Sql($adapter);

        $alter = new Ddl\AlterTable('users');
        $alter->dropColumn('first_name');
        $alter->dropColumn('last_name');
        $alter->dropColumn('bio');

        $adapter->query(
            $sql->buildSqlString($alter),
            $adapter::QUERY_MODE_EXECUTE
        );
    }
}

Example 5: Audit Log Table

$auditLog = new CreateTable('audit_log');

// Auto-increment ID
$id = new Column\BigInteger('id');
$id->setOption('AUTO_INCREMENT', true);
$id->addConstraint(new Constraint\PrimaryKey());
$auditLog->addColumn($id);

// What was changed
$auditLog->addColumn(new Column\Varchar('table_name', 100));
$auditLog->addColumn(new Column\Varchar('action', 20)); // INSERT, UPDATE, DELETE
$auditLog->addColumn(new Column\BigInteger('record_id'));

// Who changed it
$userId = new Column\Integer('user_id');
$userId->setNullable(true); // System actions might not have a user
$auditLog->addColumn($userId);

// When it changed
$timestamp = new Column\Timestamp('created_at');
$timestamp->setDefault('CURRENT_TIMESTAMP');
$auditLog->addColumn($timestamp);

// What changed (JSON or TEXT)
$auditLog->addColumn(new Column\Text('old_values'));
$auditLog->addColumn(new Column\Text('new_values'));

// Additional context
$ipAddress = new Column\Varchar('ip_address', 45); // IPv6 compatible
$ipAddress->setNullable(true);
$auditLog->addColumn($ipAddress);

// Constraints
$auditLog->addConstraint(new Constraint\Check(
    "action IN ('INSERT', 'UPDATE', 'DELETE')",
    'check_valid_action'
));

// Indexes for querying
$auditLog->addConstraint(new Index('table_name', 'idx_table'));
$auditLog->addConstraint(new Index('record_id', 'idx_record'));
$auditLog->addConstraint(new Index('user_id', 'idx_user'));
$auditLog->addConstraint(new Index('created_at', 'idx_created'));
$auditLog->addConstraint(new Index(['table_name', 'record_id'], 'idx_table_record'));

$adapter->query($sql->buildSqlString($auditLog), $adapter::QUERY_MODE_EXECUTE);

[simon-mundy]

@tyrsson @settermjd This is subject to the BC changes for Argument classes changed in PR#87

[tyrsson]

Looks good. We need to decide how we are going to organize and aggregate the documentation around the adapter packages and their components such as the Sql\Ddl decorators...