/** * @param Schema $schema * @param QueryBag $queries * @param string $tableName * @param string $columnName * @param string $type * * @throws \Exception */ public function changePrimaryKeyType(Schema $schema, QueryBag $queries, $tableName, $columnName, $type) { $targetColumn = $schema->getTable($tableName)->getColumn($columnName); $type = Type::getType($type); if ($targetColumn->getType() === $type) { return; } /** @var ForeignKeyConstraint[] $foreignKeys */ $foreignKeys = []; foreach ($schema->getTables() as $table) { /** @var ForeignKeyConstraint[] $tableForeignKeys */ $tableForeignKeys = array_filter($table->getForeignKeys(), function (ForeignKeyConstraint $tableForeignKey) use($tableName, $columnName) { if ($tableForeignKey->getForeignTableName() !== $tableName) { return false; } return $tableForeignKey->getForeignColumns() === [$columnName]; }); foreach ($tableForeignKeys as $tableForeignKey) { $foreignKeys[$tableForeignKey->getName()] = $tableForeignKey; $foreignKeyTableName = $tableForeignKey->getLocalTable()->getName(); $foreignKeyColumnNames = $tableForeignKey->getLocalColumns(); $queries->addPreQuery($this->platform->getDropForeignKeySQL($tableForeignKey, $foreignKeyTableName)); $column = $schema->getTable($foreignKeyTableName)->getColumn(reset($foreignKeyColumnNames)); if ($column instanceof ExtendColumn) { $column->disableExtendOptions()->setType($type)->enableExtendOptions(); } else { $column->setType($type); } } } $targetColumn->setType($type); foreach ($foreignKeys as $foreignKey) { $queries->addPostQuery($this->platform->getCreateForeignKeySQL($foreignKey, $foreignKey->getLocalTable())); } }
/** * @param Table $localTable * @param ForeignKeyConstraint $fkConstraint */ public function acceptForeignKey(Table $localTable, ForeignKeyConstraint $fkConstraint) { if (strlen($fkConstraint->getName()) == 0) { throw SchemaException::namedForeignKeyRequired($localTable, $fkConstraint); } $this->_constraints[] = $this->_platform->getDropForeignKeySQL($fkConstraint->getQuotedName($this->_platform), $localTable->getQuotedName($this->_platform)); }
/** * * @param array $classes * @return array */ public function getDropSchemaSQL(array $classes) { $sm = $this->_em->getConnection()->getSchemaManager(); $sql = array(); $orderedTables = array(); foreach ($classes AS $class) { if ($class->isIdGeneratorSequence() && $class->name == $class->rootEntityName && $this->_platform->supportsSequences()) { $sql[] = $this->_platform->getDropSequenceSQL($class->sequenceGeneratorDefinition['sequenceName']); } } $commitOrder = $this->_getCommitOrder($classes); $associationTables = $this->_getAssociationTables($commitOrder); // Drop association tables first foreach ($associationTables as $associationTable) { if (!in_array($associationTable, $orderedTables)) { $orderedTables[] = $associationTable; } } // Drop tables in reverse commit order for ($i = count($commitOrder) - 1; $i >= 0; --$i) { $class = $commitOrder[$i]; if (($class->isInheritanceTypeSingleTable() && $class->name != $class->rootEntityName) || $class->isMappedSuperclass) { continue; } if (!in_array($class->getTableName(), $orderedTables)) { $orderedTables[] = $class->getTableName(); } } $dropTablesSql = array(); foreach ($orderedTables AS $tableName) { /* @var $sm \Doctrine\DBAL\Schema\AbstractSchemaManager */ $foreignKeys = $sm->listTableForeignKeys($tableName); foreach ($foreignKeys AS $foreignKey) { $sql[] = $this->_platform->getDropForeignKeySQL($foreignKey, $tableName); } $dropTablesSql[] = $this->_platform->getDropTableSQL($tableName); } return array_merge($sql, $dropTablesSql); }
/** * @return array */ public function getQueries() { $sql = array(); foreach ($this->constraints as $fkConstraint) { $localTable = $this->constraints[$fkConstraint]; $sql[] = $this->platform->getDropForeignKeySQL($fkConstraint, $localTable); } foreach ($this->sequences as $sequence) { $sql[] = $this->platform->getDropSequenceSQL($sequence); } foreach ($this->tables as $table) { $sql[] = $this->platform->getDropTableSQL($table); } return $sql; }
/** * @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; }
/** * This serves a rather strange use case: renaming columns used in FK constraints. * * For a column that is used in a FK constraint to be renamed, the FK constraint has to be * dropped first, then the column can be renamed and last the FK constraint needs to be * added back (using the new name, of course). * * This method helps with the task of handling the FK constraints during this. Given a list * of tables that contain columns to be renamed and a search/replace pair for the column name, * it will return an array with arrays with drop and add SQL statements. * * Use them like this before and after renaming the affected fields: * * // collect foreign keys pointing to "our" tables * $tableNames = array(...); * $foreignKeyHandlingSql = $this->getForeignKeyHandlingSql($schema, $tableNames, 'old_name', 'new_name'); * * // drop FK constraints * foreach ($foreignKeyHandlingSql['drop'] as $sql) { * $this->addSql($sql); * } * * // rename columns now * * // add back FK constraints * foreach ($foreignKeyHandlingSql['add'] as $sql) { * $this->addSql($sql); * } * * @param \Doctrine\DBAL\Schema\Schema $schema * @param \Doctrine\DBAL\Platforms\AbstractPlatform $platform * @param array $tableNames * @param string $search * @param string $replace * @return array */ public static function getForeignKeyHandlingSql(\Doctrine\DBAL\Schema\Schema $schema, \Doctrine\DBAL\Platforms\AbstractPlatform $platform, $tableNames, $search, $replace) { $foreignKeyHandlingSql = array('drop' => array(), 'add' => array()); $tables = $schema->getTables(); foreach ($tables as $table) { $foreignKeys = $table->getForeignKeys(); foreach ($foreignKeys as $foreignKey) { if (!in_array($table->getName(), $tableNames) && !in_array($foreignKey->getForeignTableName(), $tableNames)) { continue; } $localColumns = $foreignKey->getLocalColumns(); $foreignColumns = $foreignKey->getForeignColumns(); if (in_array($search, $foreignColumns) || in_array($search, $localColumns)) { if (in_array($foreignKey->getLocalTableName(), $tableNames)) { array_walk($localColumns, function (&$value) use($search, $replace) { if ($value === $search) { $value = $replace; } }); } if (in_array($foreignKey->getForeignTableName(), $tableNames)) { array_walk($foreignColumns, function (&$value) use($search, $replace) { if ($value === $search) { $value = $replace; } }); } $identifierConstructorCallback = function ($columnName) { return new Identifier($columnName); }; $localColumns = array_map($identifierConstructorCallback, $localColumns); $foreignColumns = array_map($identifierConstructorCallback, $foreignColumns); $newForeignKey = clone $foreignKey; \TYPO3\Flow\Reflection\ObjectAccess::setProperty($newForeignKey, '_localColumnNames', $localColumns, true); \TYPO3\Flow\Reflection\ObjectAccess::setProperty($newForeignKey, '_foreignColumnNames', $foreignColumns, true); $foreignKeyHandlingSql['drop'][] = $platform->getDropForeignKeySQL($foreignKey, $table); $foreignKeyHandlingSql['add'][] = $platform->getCreateForeignKeySQL($newForeignKey, $table); } } } return $foreignKeyHandlingSql; }
/** * Drops a foreign key from a table. * * @param ForeignKeyConstraint|string $table The name of the table with the foreign key. * @param Table|string $name The name of the foreign key. * @return boolean $result */ public function dropForeignKey($foreignKey, $table) { $this->_execSql($this->_platform->getDropForeignKeySQL($foreignKey, $table)); }
/** * @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; }