DDL support for Zend\Db

[ralphschindler]

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:

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

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`

[samsonasik]

add

/**
  * Zend Framework (http://framework.zend.com/)
  *
  * @link      http://github.com/zendframework/zf2 for the canonical source repository
  * @copyright Copyright (c) 2005-2013 Zend Technologies USA Inc. (http://www.zend.com)
  * @license   http://framework.zend.com/license/new-bsd New BSD License
  */

here ...

[tca3]

This needs a php-cs-fixer ! :)

Also for instance this:

const TABLE = 'table';
const ADD_COLUMNS = 'addColumns';
const CHANGE_COLUMNS = 'changeColumns';
const DROP_COLUMNS = 'dropColumns';
const ADD_CONSTRAINTS = 'addConstraints';
const DROP_CONSTRAINTS = 'dropConstraints';

Should look better like this:

const TABLE             = 'table';
const ADD_COLUMNS       = 'addColumns';
const CHANGE_COLUMNS    = 'changeColumns';
const DROP_COLUMNS      = 'dropColumns';
const ADD_CONSTRAINTS   = 'addConstraints';
const DROP_CONSTRAINTS  = 'dropConstraints';

I didn't go through all your code though. But there are also many unnecessary \n's and there's also a //var_dump() somewhere.

Looks like a nice feature though, looking forward to using it.

[ralphschindler]

It's a work in progress, more things are coming, including unit tests, doc blocks, etc.

[bakura10]

Don't forget to remove that :).

[weierophinney]

Merged to develop for 2.2.0.

[ramunasd]

What about simple non-unique column/s index?