/**
* @group DDC-1737
*/
public function testClobNoAlterTable()
{
$tableOld = new Table("test");
$tableOld->addColumn('id', 'integer');
$tableOld->addColumn('description', 'string', array('length' => 65536));
$tableNew = clone $tableOld;
$tableNew->setPrimaryKey(array('id'));
$diff = $this->comparator->diffTable($tableOld, $tableNew);
$sql = $this->platform->getAlterTableSQL($diff);
$this->assertEquals(array('ALTER TABLE test ADD PRIMARY KEY (id)'), $sql);
}
public function testSwitchPrimaryKeyOrder()
{
$tableOld = new Table("test");
$tableOld->addColumn('foo_id', 'integer');
$tableOld->addColumn('bar_id', 'integer');
$tableNew = clone $tableOld;
$tableOld->setPrimaryKey(array('foo_id', 'bar_id'));
$tableNew->setPrimaryKey(array('bar_id', 'foo_id'));
$diff = $this->comparator->diffTable($tableOld, $tableNew);
$sql = $this->platform->getAlterTableSQL($diff);
$this->assertEquals(array('ALTER TABLE test DROP PRIMARY KEY', 'ALTER TABLE test ADD PRIMARY KEY (bar_id, foo_id)'), $sql);
}
/**
* @group DBAL-585
*/
public function testAlterTableChangeQuotedColumn()
{
$tableDiff = new \Doctrine\DBAL\Schema\TableDiff('mytable');
$tableDiff->fromTable = new \Doctrine\DBAL\Schema\Table('mytable');
$tableDiff->changedColumns['foo'] = new \Doctrine\DBAL\Schema\ColumnDiff('select', new \Doctrine\DBAL\Schema\Column('select', \Doctrine\DBAL\Types\Type::getType('string')), array('type'));
$this->assertContains($this->_platform->quoteIdentifier('select'), implode(';', $this->_platform->getAlterTableSQL($tableDiff)));
}
/**
* Alter an existing tables schema
*
* @param TableDiff $tableDiff
*/
public function alterTable(TableDiff $tableDiff)
{
$queries = $this->_platform->getAlterTableSQL($tableDiff);
if (is_array($queries) && count($queries)) {
foreach ($queries as $ddlQuery) {
$this->_execSql($ddlQuery);
}
}
}
/**
* Create a foreign key constraint without check of table and columns existence.
* This method can be helpful when you need to create a constraint for renamed table or column
*
* @param Schema $schema
* @param QueryBag $queries
* @param string $tableName
* @param string $foreignTable
* @param string[] $localColumnNames
* @param string[] $foreignColumnNames
* @param array $options
* @param string|null $constraintName
*/
public function addForeignKeyConstraint(Schema $schema, QueryBag $queries, $tableName, $foreignTable, array $localColumnNames, array $foreignColumnNames, array $options = [], $constraintName = null)
{
if (!$constraintName) {
$constraintName = $this->nameGenerator->generateForeignKeyConstraintName($tableName, $localColumnNames);
}
$constraint = new ForeignKeyConstraint($localColumnNames, $foreignTable, $foreignColumnNames, $constraintName, $options);
$diff = new TableDiff($tableName);
$diff->addedForeignKeys = [$constraintName => $constraint];
$renameQuery = new SqlMigrationQuery($this->platform->getAlterTableSQL($diff));
$queries->addQuery($renameQuery);
}
/**
* @group DBAL-1062
*/
public function testGeneratesAlterTableRenameIndexUsedByForeignKeySQL()
{
$foreignTable = new Table('foreign_table');
$foreignTable->addColumn('id', 'integer');
$foreignTable->setPrimaryKey(array('id'));
$primaryTable = new Table('mytable');
$primaryTable->addColumn('foo', 'integer');
$primaryTable->addColumn('bar', 'integer');
$primaryTable->addColumn('baz', 'integer');
$primaryTable->addIndex(array('foo'), 'idx_foo');
$primaryTable->addIndex(array('bar'), 'idx_bar');
$primaryTable->addForeignKeyConstraint($foreignTable, array('foo'), array('id'), array(), 'fk_foo');
$primaryTable->addForeignKeyConstraint($foreignTable, array('bar'), array('id'), array(), 'fk_bar');
$tableDiff = new TableDiff('mytable');
$tableDiff->fromTable = $primaryTable;
$tableDiff->renamedIndexes['idx_foo'] = new Index('idx_foo_renamed', array('foo'));
$this->assertSame($this->getGeneratesAlterTableRenameIndexUsedByForeignKeySQL(), $this->_platform->getAlterTableSQL($tableDiff));
}
/**
* @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform
* @param boolean $saveMode
*
* @return array
*/
protected function _toSql(AbstractPlatform $platform, $saveMode = false)
{
$sql = array();
if ($platform->supportsSchemas()) {
foreach ($this->newNamespaces as $newNamespace) {
$sql[] = $platform->getCreateSchemaSQL($newNamespace);
}
}
if ($platform->supportsForeignKeyConstraints() && $saveMode == false) {
foreach ($this->orphanedForeignKeys as $orphanedForeignKey) {
$sql[] = $platform->getDropForeignKeySQL($orphanedForeignKey, $orphanedForeignKey->getLocalTableName());
}
}
if ($platform->supportsSequences() == true) {
foreach ($this->changedSequences as $sequence) {
$sql[] = $platform->getAlterSequenceSQL($sequence);
}
if ($saveMode === false) {
foreach ($this->removedSequences as $sequence) {
$sql[] = $platform->getDropSequenceSQL($sequence);
}
}
foreach ($this->newSequences as $sequence) {
$sql[] = $platform->getCreateSequenceSQL($sequence);
}
}
$foreignKeySql = array();
foreach ($this->newTables as $table) {
$sql = array_merge($sql, $platform->getCreateTableSQL($table, AbstractPlatform::CREATE_INDEXES));
if ($platform->supportsForeignKeyConstraints()) {
foreach ($table->getForeignKeys() as $foreignKey) {
$foreignKeySql[] = $platform->getCreateForeignKeySQL($foreignKey, $table);
}
}
}
$sql = array_merge($sql, $foreignKeySql);
if ($saveMode === false) {
foreach ($this->removedTables as $table) {
$sql[] = $platform->getDropTableSQL($table);
}
}
foreach ($this->changedTables as $tableDiff) {
$sql = array_merge($sql, $platform->getAlterTableSQL($tableDiff));
}
return $sql;
}
/**
* @param $tables
* @param $backupPrefix
*
* @return array
*
* @throws \Doctrine\DBAL\DBALException
*/
protected function backupExistingSchema($tables, $mauticTables, $backupPrefix)
{
$sql = [];
$sm = $this->db->getSchemaManager();
//backup existing tables
$backupRestraints = $backupSequences = $backupIndexes = $backupTables = $dropSequences = $dropTables = [];
//cycle through the first time to drop all the foreign keys
foreach ($tables as $t) {
if (!isset($mauticTables[$t]) && !in_array($t, $mauticTables)) {
// Not an applicable table
continue;
}
$restraints = $sm->listTableForeignKeys($t);
if (isset($mauticTables[$t])) {
//to be backed up
$backupRestraints[$mauticTables[$t]] = $restraints;
$backupTables[$t] = $mauticTables[$t];
$backupIndexes[$t] = $sm->listTableIndexes($t);
} else {
//existing backup to be dropped
$dropTables[] = $t;
}
foreach ($restraints as $restraint) {
$sql[] = $this->platform->getDropForeignKeySQL($restraint, $t);
}
}
//now drop all the backup tables
foreach ($dropTables as $t) {
$sql[] = $this->platform->getDropTableSQL($t);
}
//now backup tables
foreach ($backupTables as $t => $backup) {
//drop old indexes
/** @var \Doctrine\DBAL\Schema\Index $oldIndex */
foreach ($backupIndexes[$t] as $indexName => $oldIndex) {
if ($indexName == 'primary') {
continue;
}
$oldName = $oldIndex->getName();
$newName = $this->generateBackupName($this->dbParams['table_prefix'], $backupPrefix, $oldName);
$newIndex = new Index($newName, $oldIndex->getColumns(), $oldIndex->isUnique(), $oldIndex->isPrimary(), $oldIndex->getFlags());
$newIndexes[] = $newIndex;
$sql[] = $this->platform->getDropIndexSQL($oldIndex, $t);
}
//rename table
$tableDiff = new TableDiff($t);
$tableDiff->newName = $backup;
$queries = $this->platform->getAlterTableSQL($tableDiff);
$sql = array_merge($sql, $queries);
//create new index
if (!empty($newIndexes)) {
foreach ($newIndexes as $newIndex) {
$sql[] = $this->platform->getCreateIndexSQL($newIndex, $backup);
}
unset($newIndexes);
}
}
//apply foreign keys to backup tables
foreach ($backupRestraints as $table => $oldRestraints) {
foreach ($oldRestraints as $or) {
$foreignTable = $or->getForeignTableName();
$foreignTableName = $this->generateBackupName($this->dbParams['table_prefix'], $backupPrefix, $foreignTable);
$r = new ForeignKeyConstraint($or->getLocalColumns(), $foreignTableName, $or->getForeignColumns(), $backupPrefix . $or->getName(), $or->getOptions());
$sql[] = $this->platform->getCreateForeignKeySQL($r, $table);
}
}
return $sql;
}
