/** * Returns the number of differences. * * Compare the foreign keys of the fromTable and the toTable, * and modifies the inner tableDiff if necessary. * * @param boolean $caseInsensitive * @return integer */ public function compareForeignKeys($caseInsensitive = false) { $fkDifferences = 0; $fromTableFks = $this->getFromTable()->getForeignKeys(); $toTableFks = $this->getToTable()->getForeignKeys(); foreach ($fromTableFks as $fromTableFkPos => $fromTableFk) { foreach ($toTableFks as $toTableFkPos => $toTableFk) { $sameName = $caseInsensitive ? strtolower($fromTableFk->getName()) == strtolower($toTableFk->getName()) : $fromTableFk->getName() == $toTableFk->getName(); if ($sameName && !$toTableFk->isPolymorphic()) { if (false === ForeignKeyComparator::computeDiff($fromTableFk, $toTableFk, $caseInsensitive)) { unset($fromTableFks[$fromTableFkPos]); unset($toTableFks[$toTableFkPos]); } else { // same name, but different columns $this->tableDiff->addModifiedFk($fromTableFk->getName(), $fromTableFk, $toTableFk); unset($fromTableFks[$fromTableFkPos]); unset($toTableFks[$toTableFkPos]); $fkDifferences++; } } } } foreach ($fromTableFks as $fromTableFk) { if (!$fromTableFk->isSkipSql() && !$fromTableFk->isPolymorphic() && !in_array($fromTableFk, $toTableFks)) { $this->tableDiff->addRemovedFk($fromTableFk->getName(), $fromTableFk); $fkDifferences++; } } foreach ($toTableFks as $toTableFk) { if (!$toTableFk->isSkipSql() && !$toTableFk->isPolymorphic() && !in_array($toTableFk, $fromTableFks)) { $this->tableDiff->addAddedFk($toTableFk->getName(), $toTableFk); $fkDifferences++; } } return $fkDifferences; }
/** * Creates a temporarily created table with the new schema, * moves all items into it and drops the origin as well as renames the temp table to the origin then. * * @param TableDiff $tableDiff * @return string */ public function getMigrationTableDDL(TableDiff $tableDiff) { $pattern = "\nCREATE TEMPORARY TABLE %s AS SELECT %s FROM %s;\nDROP TABLE %s;\n%s\nINSERT INTO %s (%s) SELECT %s FROM %s;\nDROP TABLE %s;\n"; $originTable = clone $tableDiff->getFromTable(); $newTable = clone $tableDiff->getToTable(); $originTableName = $originTable->getName(); $tempTableName = $newTable->getCommonName() . '__temp__' . uniqid(); $originTableFields = $this->getColumnListDDL($originTable->getColumns()); $fieldMap = []; /** struct: [<oldCol> => <newCol>] */ //start with modified columns foreach ($tableDiff->getModifiedColumns() as $diff) { $fieldMap[$diff->getFromColumn()->getName()] = $diff->getToColumn()->getName(); } foreach ($tableDiff->getRenamedColumns() as $col) { list($from, $to) = $col; $fieldMap[$from->getName()] = $to->getName(); } foreach ($newTable->getColumns() as $col) { if ($originTable->hasColumn($col)) { if (!isset($fieldMap[$col->getName()])) { $fieldMap[$col->getName()] = $col->getName(); } } } $createTable = $this->getAddTableDDL($newTable); $createTable .= $this->getAddIndicesDDL($newTable); $sql = sprintf($pattern, $this->quoteIdentifier($tempTableName), $originTableFields, $this->quoteIdentifier($originTableName), $this->quoteIdentifier($originTableName), $createTable, $this->quoteIdentifier($originTableName), implode(', ', $fieldMap), implode(', ', array_keys($fieldMap)), $this->quoteIdentifier($tempTableName), $this->quoteIdentifier($tempTableName)); return $sql; }
public function getModifyTableDDL(TableDiff $tableDiff) { $alterTableStatements = ''; $toTable = $tableDiff->getToTable(); // drop indices, foreign keys foreach ($tableDiff->getRemovedFks() as $fk) { $alterTableStatements .= $this->getDropForeignKeyDDL($fk); } foreach ($tableDiff->getModifiedFks() as $fkModification) { list($fromFk) = $fkModification; $alterTableStatements .= $this->getDropForeignKeyDDL($fromFk); } foreach ($tableDiff->getRemovedIndices() as $index) { $alterTableStatements .= $this->getDropIndexDDL($index); } foreach ($tableDiff->getModifiedIndices() as $indexModification) { list($fromIndex) = $indexModification; $alterTableStatements .= $this->getDropIndexDDL($fromIndex); } // alter table structure if ($tableDiff->hasModifiedPk()) { $alterTableStatements .= $this->getDropPrimaryKeyDDL($tableDiff->getFromTable()); } foreach ($tableDiff->getRenamedColumns() as $columnRenaming) { $alterTableStatements .= $this->getRenameColumnDDL($columnRenaming[0], $columnRenaming[1]); } if ($modifiedColumns = $tableDiff->getModifiedColumns()) { $alterTableStatements .= $this->getModifyColumnsDDL($modifiedColumns); } if ($addedColumns = $tableDiff->getAddedColumns()) { $alterTableStatements .= $this->getAddColumnsDDL($addedColumns); } foreach ($tableDiff->getRemovedColumns() as $column) { $alterTableStatements .= $this->getRemoveColumnDDL($column); } // add new indices and foreign keys if ($tableDiff->hasModifiedPk()) { $alterTableStatements .= $this->getAddPrimaryKeyDDL($tableDiff->getToTable()); } // create indices, foreign keys foreach ($tableDiff->getModifiedIndices() as $indexModification) { list($oldIndex, $toIndex) = $indexModification; $alterTableStatements .= $this->getAddIndexDDL($toIndex); } foreach ($tableDiff->getAddedIndices() as $index) { $alterTableStatements .= $this->getAddIndexDDL($index); } foreach ($tableDiff->getModifiedFks() as $fkModification) { list(, $toFk) = $fkModification; $alterTableStatements .= $this->getAddForeignKeyDDL($toFk); } foreach ($tableDiff->getAddedFks() as $fk) { $alterTableStatements .= $this->getAddForeignKeyDDL($fk); } $ret = ''; if (trim($alterTableStatements)) { //merge all changes into one command. This prevents https://github.com/propelorm/Propel2/issues/1115 $changes = explode(';', $alterTableStatements); $changeFragments = []; foreach ($changes as $change) { if (trim($change)) { $changeFragments[] = preg_replace(sprintf('/ALTER TABLE %s /', $this->quoteIdentifier($toTable->getName())), "\n\n ", trim($change)); } } $ret .= sprintf("\nALTER TABLE %s%s;\n", $this->quoteIdentifier($toTable->getName()), implode(',', $changeFragments)); } return $ret; }
/** * Builds the DDL SQL to alter a table's foreign keys * based on a TableDiff instance * * @return string */ public function getModifyTableForeignKeysDDL(TableDiff $tableDiff) { $ret = ''; foreach ($tableDiff->getRemovedFks() as $fk) { $ret .= $this->getDropForeignKeyDDL($fk); } foreach ($tableDiff->getAddedFks() as $fk) { $ret .= $this->getAddForeignKeyDDL($fk); } foreach ($tableDiff->getModifiedFks() as $fkModification) { list($fromFk, $toFk) = $fkModification; $ret .= $this->getDropForeignKeyDDL($fromFk); $ret .= $this->getAddForeignKeyDDL($toFk); } return $ret; }
public function testToString() { $tableA = new Table('A'); $tableB = new Table('B'); $diff = new TableDiff($tableA, $tableB); $diff->addAddedColumn('id', new Column('id', 'integer')); $diff->addRemovedColumn('category_id', new Column('category_id', 'integer')); $colFoo = new Column('foo', 'integer'); $colBar = new Column('bar', 'integer'); $tableA->addColumn($colFoo); $tableA->addColumn($colBar); $diff->addRenamedColumn($colFoo, $colBar); $columnDiff = new ColumnDiff($colFoo, $colBar); $diff->addModifiedColumn('foo', $columnDiff); $fk = new ForeignKey('category'); $fk->setTable($tableA); $fk->setForeignTableCommonName('B'); $fk->addReference('category_id', 'id'); $fkChanged = clone $fk; $fkChanged->setForeignTableCommonName('C'); $fkChanged->addReference('bla', 'id2'); $fkChanged->setOnDelete('cascade'); $fkChanged->setOnUpdate('cascade'); $diff->addAddedFk('category', $fk); $diff->addModifiedFk('category', $fk, $fkChanged); $diff->addRemovedFk('category', $fk); $index = new Index('test_index'); $index->setTable($tableA); $index->setColumns([$colFoo]); $indexChanged = clone $index; $indexChanged->setColumns([$colBar]); $diff->addAddedIndex('test_index', $index); $diff->addModifiedIndex('test_index', $index, $indexChanged); $diff->addRemovedIndex('test_index', $index); $string = (string) $diff; $expected = ' A: addedColumns: - id removedColumns: - category_id modifiedColumns: A.FOO: modifiedProperties: renamedColumns: foo: bar addedIndices: - test_index removedIndices: - test_index modifiedIndices: - test_index addedFks: - category removedFks: - category modifiedFks: category: localColumns: from ["category_id"] to ["category_id","bla"] foreignColumns: from ["id"] to ["id","id2"] onUpdate: from to CASCADE onDelete: from to CASCADE '; $this->assertEquals($expected, $string); }