zendframework/zf2 — pull request #4311 by ralphschindler (2013-04-24 21:21:54+00:00)
Title: [WIP] DDL support for Zend\Db

This is initial support for DDL commands in Zend\Db\Sql via an object-oriented abstraction interface. There are many things that would be supported long term.

At current, this component is capable of creating, altering and dropping tables, with columns, constraints and indexes. It has MySQL specialization support. Below is a sample script and its output:

```php

use Zend\Db\Sql\Sql;
use Zend\Db\Sql\Ddl;

$ct = new Ddl\CreateTable();
$ct->setTable('bar');
$ct->addColumn(new Ddl\Column\Integer('id', 12, true, null, ['auto_increment' => true, 'comment' => 'Some comment']));
$ct->addColumn(new Ddl\Column\Varchar('name', 255));
$ct->addColumn(new Ddl\Column\Char('foo', 20));
$ct->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
$ct->addConstraint(new Ddl\Constraint\UniqueKey(['name', 'foo'], 'my_unique_key'));

$sql = new Sql($adapter);
echo $sql->getSqlStringForSqlObject($ct);

echo PHP_EOL . PHP_EOL;

$at = new Ddl\AlterTable('bar');
$at->changeColumn('name', new Ddl\Column\Varchar('new_name', 50));
$at->addColumn(new Ddl\Column\Varchar('another', 255));
$at->addColumn(new Ddl\Column\Varchar('other_id', 255));
$at->dropColumn('foo');
$at->addConstraint(new Ddl\Constraint\ForeignKey('my_fk', 'other_id', 'other_table', 'id', 'CASCADE', 'CASCADE'));
$at->dropConstraint('my_index');
echo $sql->getSqlStringForSqlObject($at);

echo PHP_EOL . PHP_EOL;

$dt = new Ddl\DropTable('bar');
echo $sql->getSqlStringForSqlObject($dt);

echo PHP_EOL . PHP_EOL;
```

Which results in

```sql
CREATE TABLE `bar` (
`id` INTEGER AUTO_INCREMENT COMMENT 'Some comment',
`name` VARCHAR(255) NOT NULL,
`foo` CHAR(20) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT UNIQUE KEY `my_unique_key` (`name`, `foo`)
)

ALTER TABLE `bar`
ADD COLUMN `another` VARCHAR(255) NOT NULL,
ADD COLUMN `other_id` VARCHAR(255) NOT NULL,
CHANGE COLUMN `name` `new_name` VARCHAR(50) NOT NULL,
DROP COLUMN `foo`,
ADD CONSTRAINT `my_fk` FOREIGN KEY (`other_id`) REFERENCES `other_table` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
DROP CONSTRAINT `my_index`

DROP TABLE `bar`
```