/** * generate create table queries * @throws IncorrectMethodUsageException if table() was not called first */ protected final function create() { if ($this->table === null) { throw new IncorrectMethodUsageException('Wrong use of method create(). Use method table() first.'); } $this->table->addPrimary($this->primaryKey); $this->tables[count($this->queries)] = $this->table; $queryBuilder = $this->adapter->getQueryBuilder(); $queries = $queryBuilder->createTable($this->table); $this->queries = array_merge($this->queries, $queries); $this->table = null; }
private function createNewTable(Table $table, $tmpTableName) { if (is_null($this->adapter)) { throw new PhoenixException('Missing adapter'); } $oldColumns = $this->adapter->tableInfo($table->getName()); $columns = array_merge($oldColumns, $table->getColumnsToChange()); $newTable = new Table($table->getName()); $columnNames = []; foreach ($columns as $column) { $columnNames[] = $column->getName(); if ($column->isAutoincrement()) { $newTable->addPrimary($column); continue; } $newTable->addColumn($column); } $queries = $this->createTable($newTable); $queries[] = 'INSERT INTO ' . $this->escapeString($newTable->getName()) . ' (' . implode(',', $this->escapeArray($columnNames)) . ') SELECT ' . implode(',', $this->escapeArray(array_keys($oldColumns))) . ' FROM ' . $this->escapeString($tmpTableName); return $queries; }
public function testIndexes() { $table = new Table('test'); $this->assertCount(0, $table->getIndexes()); $table->addPrimary(true); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('title', 'title', 'unique'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index(['title', 'alias'], 'title_alias'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('bodytext', 'bodytext', 'fulltext'))); $this->assertCount(3, $table->getIndexes()); $this->assertCount(0, $table->getIndexesToDrop()); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropIndex('title_alias')); $this->assertCount(1, $table->getIndexesToDrop()); $this->assertEquals('title_alias', $table->getIndexesToDrop()[0]); }
public function testIndexesAndForeignKeys() { $table = new Table('table_with_indexes_and_foreign_keys'); $table->addPrimary(true); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('title', 'string'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('alias', 'string'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('bodytext', 'text'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_table_id', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_table_id', 'second_table', 'foreign_id', 'set null', 'set null'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'table_with_indexes_and_foreign_keys_sorting', '', 'btree'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index(['title', 'alias'], 'table_with_indexes_and_foreign_keys_title_alias', 'unique'))); $pdo = new FakePdo(); $adapter = new SqliteAdapter($pdo); $queryBuilder = new SqliteQueryBuilder($adapter); $expectedQueries = ['CREATE TABLE "table_with_indexes_and_foreign_keys" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,"title" varchar(255) NOT NULL,"alias" varchar(255) NOT NULL,"sorting" integer NOT NULL,"bodytext" text NOT NULL,"foreign_table_id" integer NOT NULL,CONSTRAINT "table_with_indexes_and_foreign_keys_foreign_table_id" FOREIGN KEY ("foreign_table_id") REFERENCES "second_table" ("foreign_id") ON DELETE SET NULL ON UPDATE SET NULL);', 'CREATE INDEX "table_with_indexes_and_foreign_keys_sorting" ON "table_with_indexes_and_foreign_keys" ("sorting");', 'CREATE UNIQUE INDEX "table_with_indexes_and_foreign_keys_title_alias" ON "table_with_indexes_and_foreign_keys" ("title","alias");']; $this->assertEquals($expectedQueries, $queryBuilder->createTable($table)); }
public function testAlterTable() { // add columns $table = new Table('add_columns'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('title', 'string'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('alias', 'string'))); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `add_columns` ADD COLUMN `title` varchar(255) NOT NULL,ADD COLUMN `alias` varchar(255) NOT NULL;']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add and remove primary key $table = new Table('change_primary_key'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropPrimaryKey()); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addPrimary('new_primary')); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `change_primary_key` DROP PRIMARY KEY;', 'ALTER TABLE `change_primary_key` ADD PRIMARY KEY (`new_primary`);']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add index $table = new Table('add_index'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('alias', 'alias', 'unique'))); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `add_index` ADD UNIQUE INDEX `alias` (`alias`);']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add column and index $table = new Table('add_column_and_index'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('alias', 'string'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('alias', 'alias', 'unique'))); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `add_column_and_index` ADD COLUMN `alias` varchar(255) NOT NULL;', 'ALTER TABLE `add_column_and_index` ADD UNIQUE INDEX `alias` (`alias`);']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add foreign key, index, columns $table = new Table('add_columns_index_foreign_key'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_key_id', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'sorting'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_key_id', 'referenced_table'))); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `add_columns_index_foreign_key` ADD COLUMN `foreign_key_id` int(11) NOT NULL,ADD COLUMN `sorting` int(11) NOT NULL;', 'ALTER TABLE `add_columns_index_foreign_key` ADD INDEX `sorting` (`sorting`);', 'ALTER TABLE `add_columns_index_foreign_key` ADD CONSTRAINT `add_columns_index_foreign_key_foreign_key_id` FOREIGN KEY (`foreign_key_id`) REFERENCES `referenced_table` (`id`);']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // remove columns // remove index // remove foreign key // combination of add / remove column, add / remove index, add / remove foreign key $table = new Table('all_in_one'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_key_id', 'integer', ['after' => 'column_before']))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropColumn('title')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'sorting'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropIndex('alias')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_key_id', 'referenced_table'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropForeignKey('foreign_key_to_drop_id')); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `all_in_one` DROP INDEX `alias`;', 'ALTER TABLE `all_in_one` DROP FOREIGN KEY `all_in_one_foreign_key_to_drop_id`;', 'ALTER TABLE `all_in_one` DROP COLUMN `title`;', 'ALTER TABLE `all_in_one` ADD COLUMN `foreign_key_id` int(11) NOT NULL AFTER `column_before`,ADD COLUMN `sorting` int(11) NOT NULL;', 'ALTER TABLE `all_in_one` ADD INDEX `sorting` (`sorting`);', 'ALTER TABLE `all_in_one` ADD CONSTRAINT `all_in_one_foreign_key_id` FOREIGN KEY (`foreign_key_id`) REFERENCES `referenced_table` (`id`);']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // mixed order of calls add / remove column, add / remove index, add / remove foreign key - output is the same $table = new Table('all_in_one_mixed'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'sorting'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropForeignKey('foreign_key_to_drop_id')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_key_id', 'integer', ['first' => true]))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropColumn('title')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropIndex('alias')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_key_id', 'referenced_table'))); $queryBuilder = new MysqlQueryBuilder(); $expectedQueries = ['ALTER TABLE `all_in_one_mixed` DROP INDEX `alias`;', 'ALTER TABLE `all_in_one_mixed` DROP FOREIGN KEY `all_in_one_mixed_foreign_key_to_drop_id`;', 'ALTER TABLE `all_in_one_mixed` DROP COLUMN `title`;', 'ALTER TABLE `all_in_one_mixed` ADD COLUMN `foreign_key_id` int(11) NOT NULL FIRST,ADD COLUMN `sorting` int(11) NOT NULL;', 'ALTER TABLE `all_in_one_mixed` ADD INDEX `sorting` (`sorting`);', 'ALTER TABLE `all_in_one_mixed` ADD CONSTRAINT `all_in_one_mixed_foreign_key_id` FOREIGN KEY (`foreign_key_id`) REFERENCES `referenced_table` (`id`);']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); }
public function testAlterTable() { // add columns $table = new Table('add_columns'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('title', 'string'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('alias', 'string'))); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['ALTER TABLE "add_columns" ADD COLUMN "title" varchar(255) NOT NULL,ADD COLUMN "alias" varchar(255) NOT NULL;']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add and remove primary key $table = new Table('change_primary_key'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropPrimaryKey()); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addPrimary('new_primary')); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['ALTER TABLE "change_primary_key" DROP CONSTRAINT "change_primary_key_pkey";', 'ALTER TABLE "change_primary_key" ADD CONSTRAINT "change_primary_key_pkey" PRIMARY KEY ("new_primary");']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add index $table = new Table('add_index'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('alias', 'add_index_alias', 'unique'))); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['CREATE UNIQUE INDEX "add_index_alias" ON "add_index" ("alias");']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add column and index $table = new Table('add_column_and_index'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('alias', 'string'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('alias', 'add_column_and_index_alias', 'unique'))); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['ALTER TABLE "add_column_and_index" ADD COLUMN "alias" varchar(255) NOT NULL;', 'CREATE UNIQUE INDEX "add_column_and_index_alias" ON "add_column_and_index" ("alias");']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // add foreign key, index, columns $table = new Table('add_columns_index_foreign_key'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_key_id', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'add_columns_index_foreign_key_sorting'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_key_id', 'referenced_table'))); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['ALTER TABLE "add_columns_index_foreign_key" ADD COLUMN "foreign_key_id" int4 NOT NULL,ADD COLUMN "sorting" int4 NOT NULL;', 'CREATE INDEX "add_columns_index_foreign_key_sorting" ON "add_columns_index_foreign_key" ("sorting");', 'ALTER TABLE "add_columns_index_foreign_key" ADD CONSTRAINT "add_columns_index_foreign_key_foreign_key_id" FOREIGN KEY ("foreign_key_id") REFERENCES "referenced_table" ("id");']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // remove columns // remove index // remove foreign key // combination of add / remove column, add / remove index, add / remove foreign key $table = new Table('all_in_one'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_key_id', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropColumn('title')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'all_in_one_sorting'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropIndex('all_in_one_alias')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_key_id', 'referenced_table'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropForeignKey('foreign_key_to_drop_id')); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['DROP INDEX "all_in_one_alias";', 'ALTER TABLE "all_in_one" DROP CONSTRAINT "all_in_one_foreign_key_to_drop_id";', 'ALTER TABLE "all_in_one" DROP COLUMN "title";', 'ALTER TABLE "all_in_one" ADD COLUMN "foreign_key_id" int4 NOT NULL,ADD COLUMN "sorting" int4 NOT NULL;', 'CREATE INDEX "all_in_one_sorting" ON "all_in_one" ("sorting");', 'ALTER TABLE "all_in_one" ADD CONSTRAINT "all_in_one_foreign_key_id" FOREIGN KEY ("foreign_key_id") REFERENCES "referenced_table" ("id");']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); // mixed order of calls add / remove column, add / remove index, add / remove foreign key - output is the same $table = new Table('all_in_one_mixed'); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addIndex(new Index('sorting', 'all_in_one_mixed_sorting'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropForeignKey('foreign_key_to_drop_id')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('foreign_key_id', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropColumn('title')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addColumn(new Column('sorting', 'integer'))); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->dropIndex('all_in_one_mixed_alias')); $this->assertInstanceOf('\\Phoenix\\Database\\Element\\Table', $table->addForeignKey(new ForeignKey('foreign_key_id', 'referenced_table'))); $queryBuilder = new PgsqlQueryBuilder(); $expectedQueries = ['DROP INDEX "all_in_one_mixed_alias";', 'ALTER TABLE "all_in_one_mixed" DROP CONSTRAINT "all_in_one_mixed_foreign_key_to_drop_id";', 'ALTER TABLE "all_in_one_mixed" DROP COLUMN "title";', 'ALTER TABLE "all_in_one_mixed" ADD COLUMN "foreign_key_id" int4 NOT NULL,ADD COLUMN "sorting" int4 NOT NULL;', 'CREATE INDEX "all_in_one_mixed_sorting" ON "all_in_one_mixed" ("sorting");', 'ALTER TABLE "all_in_one_mixed" ADD CONSTRAINT "all_in_one_mixed_foreign_key_id" FOREIGN KEY ("foreign_key_id") REFERENCES "referenced_table" ("id");']; $this->assertEquals($expectedQueries, $queryBuilder->alterTable($table)); }