/**
* 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);
}