/** * @param Table $localTable * @param ForeignKeyConstraint $fkConstraint */ public function acceptForeignKey(Table $localTable, ForeignKeyConstraint $fkConstraint) { $namespace = $this->getNamespace($localTable); if ($this->_platform->supportsForeignKeyConstraints()) { $this->_createFkConstraintQueries[$namespace] = array_merge($this->_createFkConstraintQueries[$namespace], (array) $this->_platform->getCreateForeignKeySQL($fkConstraint, $localTable)); } }
/** * @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())); } }
/** * ->processQueueCallback(function (\Dja\Db\Model\Metadata $metadata, \Doctrine\DBAL\Schema\Table $table, array $sql, \Doctrine\DBAL\Connection $db) {}) * @param callable|\Closure $callBack */ public function processQueueCallback(\Closure $callBack) { $callbackQueue = []; while (count($this->generateQueue)) { $modelName = array_shift($this->generateQueue); try { /** @var Metadata $metadata */ $metadata = $modelName::metadata(); $tblName = $metadata->getDbTableName(); if ($this->db->getSchemaManager()->tablesExist($tblName)) { continue; } if (isset($this->generated[$tblName])) { continue; } $table = $this->metadataToTable($metadata); $this->generated[$tblName] = 1; $sql = $this->dp->getCreateTableSQL($table, AbstractPlatform::CREATE_INDEXES); array_unshift($callbackQueue, [$metadata, $table, $sql]); $fks = $table->getForeignKeys(); if (count($fks)) { $sql = []; foreach ($fks as $fk) { $sql[] = $this->dp->getCreateForeignKeySQL($fk, $table); } array_push($callbackQueue, [$metadata, $table, $sql]); } } catch (\Exception $e) { pr($e->__toString()); } } foreach ($callbackQueue as $args) { $callBack($args[0], $args[1], $args[2], $this->db); } }
/** * @param Table $localTable * @param ForeignKeyConstraint $fkConstraint */ public function acceptForeignKey(Table $localTable, ForeignKeyConstraint $fkConstraint) { // Append the foreign key constraints SQL if ($this->_platform->supportsForeignKeyConstraints()) { $this->_createFkConstraintQueries = array_merge($this->_createFkConstraintQueries, (array) $this->_platform->getCreateForeignKeySQL($fkConstraint, $localTable->getName())); } }
/** * @group DBAL-132 */ public function testGenerateForeignKeySQL() { $tableOld = new Table("test"); $tableOld->addColumn('foo_id', 'integer'); $tableOld->addUnnamedForeignKeyConstraint('test_foreign', array('foo_id'), array('foo_id')); $sqls = array(); foreach ($tableOld->getForeignKeys() as $fk) { $sqls[] = $this->platform->getCreateForeignKeySQL($fk, $tableOld); } $this->assertEquals(array("ALTER TABLE test ADD CONSTRAINT FK_D87F7E0C8E48560F FOREIGN KEY (foo_id) REFERENCES test_foreign (foo_id)"), $sqls); }
public function testGeneratesForeignKeySqlOnlyWhenSupportingForeignKeys() { $fk = new \Doctrine\DBAL\Schema\ForeignKeyConstraint(array('fk_name'), 'foreign', array('id'), 'constraint_fk'); if ($this->_platform->supportsForeignKeyConstraints()) { $this->assertInternalType('string', $this->_platform->getCreateForeignKeySQL($fk, 'test')); } else { $this->setExpectedException('Doctrine\\DBAL\\DBALException'); $this->_platform->getCreateForeignKeySQL($fk, 'test'); } }
/** * @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; }
/** * Create a new foreign key * * @param ForeignKeyConstraint $foreignKey ForeignKey instance * @param string|Table $table name of the table on which the foreign key is to be created */ public function createForeignKey(ForeignKeyConstraint $foreignKey, $table) { $this->_execSql($this->_platform->getCreateForeignKeySQL($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; }
/** * {@inheritdoc} */ public function acceptForeignKey(Table $localTable, ForeignKeyConstraint $fkConstraint) { if ($this->platform->supportsForeignKeyConstraints()) { $this->createFkConstraintQueries = array_merge($this->createFkConstraintQueries, (array) $this->platform->getCreateForeignKeySQL($fkConstraint, $localTable)); } }