DDL Abstraction

Laminas\Db\Sql\Ddl is a sub-component of Laminas\Db\Sql allowing creation of DDL (Data Definition Language) SQL statements. When combined with a platform specific Laminas\Db\Sql\Sql object, DDL objects are capable of producing platform-specific CREATE TABLE statements, with specialized data types, constraints, and indexes for a database/schema.

The following platforms have platform specializations for DDL:

• MySQL
• All databases compatible with ANSI SQL92

Creating Tables

Like Laminas\Db\Sql objects, each statement type is represented by a class. For example, CREATE TABLE is modeled by the CreateTable class; this is likewise the same for ALTER TABLE (as AlterTable), and DROP TABLE (as DropTable). You can create instances using a number of approaches:

use Laminas\Db\Sql\Ddl;
use Laminas\Db\Sql\TableIdentifier;

$table = new Ddl\CreateTable();

// With a table name:
$table = new Ddl\CreateTable('bar');

// With a schema name "foo":
$table = new Ddl\CreateTable(new TableIdentifier('bar', 'foo'));

// Optionally, as a temporary table:
$table = new Ddl\CreateTable('bar', true);

You can also set the table after instantiation:

$table->setTable('bar');

Currently, columns are added by creating a column object (described in the data type table below):

use Laminas\Db\Sql\Ddl\Column;

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

Beyond adding columns to a table, you may also add constraints:

use Laminas\Db\Sql\Ddl\Constraint;

$table->addConstraint(new Constraint\PrimaryKey('id'));
$table->addConstraint(
    new Constraint\UniqueKey(['name', 'foo'], 'my_unique_key')
);

You can also use the AUTO_INCREMENT attribute for MySQL:

use Laminas\Db\Sql\Ddl\Column;

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

Altering Tables

Similar to CreateTable, you may also use AlterTable instances:

use Laminas\Db\Sql\Ddl;
use Laminas\Db\Sql\TableIdentifier;

$table = new Ddl\AlterTable();

// With a table name:
$table = new Ddl\AlterTable('bar');

// With a schema name "foo":
$table = new Ddl\AlterTable(new TableIdentifier('bar', 'foo'));

// Optionally, as a temporary table:
$table = new Ddl\AlterTable('bar', true);

The primary difference between a CreateTable and AlterTable is that the AlterTable takes into account that the table and its assets already exist. Therefore, while you still have addColumn() and addConstraint(), you will also have the ability to alter existing columns:

use Laminas\Db\Sql\Ddl\Column;

$table->changeColumn('name', Column\Varchar('new_name', 50));

You may also drop existing columns or constraints:

$table->dropColumn('foo');
$table->dropConstraint('my_index');

Dropping Tables

To drop a table, create a DropTable instance:

use Laminas\Db\Sql\Ddl;
use Laminas\Db\Sql\TableIdentifier;

// With a table name:
$drop = new Ddl\DropTable('bar');

// With a schema name "foo":
$drop = new Ddl\DropTable(new TableIdentifier('bar', 'foo'));

Executing DDL Statements

After a DDL statement object has been created and configured, at some point you will need to execute the statement. This requires an Adapter instance and a properly seeded Sql instance.

The workflow looks something like this, with $ddl being a CreateTable, AlterTable, or DropTable instance:

use Laminas\Db\Sql\Sql;

// Existence of $adapter is assumed.
$sql = new Sql($adapter);

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

By passing the $ddl object through the $sql instance's getSqlStringForSqlObject() method, we ensure that any platform specific specializations/modifications are utilized to create a platform specific SQL statement.

Next, using the constant Laminas\Db\Adapter\Adapter::QUERY_MODE_EXECUTE ensures that the SQL statement is not prepared, as most DDL statements on most platforms cannot be prepared, only executed.

Currently Supported Data Types

These types exist in the Laminas\Db\Sql\Ddl\Column namespace. Data types must implement Laminas\Db\Sql\Ddl\Column\ColumnInterface.

In alphabetical order:

Each of the above types can be utilized in any place that accepts a Column\ColumnInterface instance. Currently, this is primarily in CreateTable::addColumn() and AlterTable's addColumn() and changeColumn() methods.

Currently Supported Constraint Types

These types exist in the Laminas\Db\Sql\Ddl\Constraint namespace. Data types must implement Laminas\Db\Sql\Ddl\Constraint\ConstraintInterface.

In alphabetical order:

Each of the above types can be utilized in any place that accepts a Column\ConstraintInterface instance. Currently, this is primarily in CreateTable::addConstraint() and AlterTable::addConstraint().