/**
* create
*
* @param string $columns
* @param array $pks
* @param array $keys
* @param int $autoIncrement
* @param bool $ifNotExists
* @param array $options
*
* @return $this
*/
public function doCreate($columns, $pks = array(), $keys = array(), $autoIncrement = null, $ifNotExists = true, $options = array())
{
$inherits = isset($options['inherits']) ? $options['inherits'] : null;
$tablespace = isset($options['tablespace']) ? $options['tablespace'] : null;
$query = PostgresqlQueryBuilder::createTable($this->table, $columns, $pks, $keys, $inherits, $ifNotExists, $tablespace);
$comments = isset($options['comments']) ? $options['comments'] : array();
$keyComments = isset($options['key_comments']) ? $options['key_comments'] : array();
// Comments
foreach ($comments as $name => $comment) {
$query .= ";\n" . PostgresqlQueryBuilder::comment('COLUMN', $this->table, $name, $comment);
}
foreach ($keyComments as $name => $comment) {
$query .= ";\n" . PostgresqlQueryBuilder::comment('INDEX', 'public', $name, $comment);
}
DatabaseHelper::batchQuery($this->db, $query);
return $this;
}
/**
* Method to test createTable().
*
* @return void
*
* @covers Windwalker\Query\Postgresql\PostgresqlQueryBuilder::createTable
*/
public function testCreateTable()
{
$expected = <<<SQL
CREATE TABLE IF NOT EXISTS {$this->qn}foo{$this->qn} (
{$this->qn}id{$this->qn} int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
{$this->qn}name{$this->qn} varchar(255) NOT NULL COMMENT 'Member Name',
{$this->qn}email{$this->qn} varchar(255) NOT NULL COMMENT 'Member email',
PRIMARY KEY ({$this->qn}id{$this->qn}),
KEY {$this->qn}idx_alias{$this->qn} ({$this->qn}email{$this->qn})
) ENGINE=InnoDB AUTO_INCREMENT=415 DEFAULT CHARSET=utf8
SQL;
$columns = array('id' => 'int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'Primary Key\'', 'name' => array('varchar(255)', 'NOT NULL', 'COMMENT \'Member Name\''), 'email' => "varchar(255) NOT NULL COMMENT 'Member email'");
$keys = array(array('type' => 'KEY', 'name' => 'idx_alias', 'columns' => 'email'));
$actual = PostgresqlQueryBuilder::createTable('foo', $columns, 'id', $keys, 415, true, 'InnoDB');
$this->assertEquals(\SqlFormatter::compress($expected), \SqlFormatter::compress($actual));
$expected = <<<SQL
CREATE TABLE {$this->qn}foo{$this->qn} (
{$this->qn}id{$this->qn} int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key',
{$this->qn}name{$this->qn} varchar(255) NOT NULL COMMENT 'Member Name',
{$this->qn}email{$this->qn} varchar(255) NOT NULL COMMENT 'Member email',
PRIMARY KEY ({$this->qn}id{$this->qn}, {$this->qn}email{$this->qn}),
UNIQUE KEY {$this->qn}idx_alias{$this->qn} ({$this->qn}email{$this->qn}, {$this->qn}id{$this->qn})
) ENGINE=InnoDB AUTO_INCREMENT=415 DEFAULT CHARSET=utf8
SQL;
$columns = array('id' => 'int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT \'Primary Key\'', 'name' => array('varchar(255)', 'NOT NULL', 'COMMENT \'Member Name\''), 'email' => "varchar(255) NOT NULL COMMENT 'Member email'");
$keys = array(array('type' => 'UNIQUE KEY', 'name' => 'idx_alias', 'columns' => array('email', 'id')));
$actual = PostgresqlQueryBuilder::createTable('foo', $columns, array('id', 'email'), $keys, 415, false, 'InnoDB');
$this->assertEquals(\SqlFormatter::compress($expected), \SqlFormatter::compress($actual));
}
/**
* Method to test createTable().
*
* @return void
*
* @covers Windwalker\Query\Postgresql\PostgresqlQueryBuilder::createTable
*/
public function testCreateTable()
{
$expected = <<<SQL
CREATE TABLE IF NOT EXISTS {$this->qn('foo')} (
{$this->qn('id')} serial NOT NULL,
{$this->qn('name')} varchar(255) NOT NULL,
{$this->qn('email')} varchar(255) NOT NULL,
PRIMARY KEY ({$this->qn('id')})
) INHERITS ({$this->qn('bar')}) TABLESPACE {$this->qn('tablespace')};
CREATE INDEX {$this->qn('idx_alias')} ON {$this->qn('foo')} ({$this->qn('email')})
SQL;
$columns = array('id' => 'serial NOT NULL', 'name' => array('varchar(255)', 'NOT NULL'), 'email' => "varchar(255) NOT NULL");
$keys = array(array('type' => 'INDEX', 'name' => 'idx_alias', 'columns' => 'email'));
$actual = PostgresqlQueryBuilder::createTable('foo', $columns, 'id', $keys, 'bar', true, 'tablespace');
$this->assertEquals($this->format($expected), $this->format($actual));
$expected = <<<SQL
CREATE TABLE {$this->qn('foo')} (
{$this->qn('id')} int(11) NOT NULL,
{$this->qn('name')} varchar(255) NOT NULL,
{$this->qn('email')} varchar(255) NOT NULL,
PRIMARY KEY ({$this->qn('id')}, {$this->qn('email')})
);
CREATE UNIQUE INDEX {$this->qn('idx_alias')} ON {$this->qn('foo')} ({$this->qn('email')}, {$this->qn('id')})
SQL;
$columns = array('id' => 'int(11) NOT NULL', 'name' => array('varchar(255)', 'NOT NULL'), 'email' => "varchar(255) NOT NULL");
$keys = array(array('type' => 'UNIQUE INDEX', 'name' => 'idx_alias', 'columns' => array('email', 'id')));
$actual = PostgresqlQueryBuilder::createTable('foo', $columns, array('id', 'email'), $keys, null, false, null);
$this->assertEquals($this->format($expected), $this->format($actual));
}
