/**
* @param string $indexName
* @return AbstractMigration
* @throws IncorrectMethodUsageException
*/
protected final function dropIndexByName($indexName)
{
if ($this->table === null) {
throw new IncorrectMethodUsageException('Wrong use of method dropIndexByName(). Use method table() first.');
}
$this->table->dropIndex($indexName);
return $this;
}
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 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));
}
