function process($ddl, $dialect, $action, $allowedTableNames, $db = null, $dbmap = null, $ddlDir = '')
{
switch ($action) {
case 'outputSQL':
$serializer = new SQLDDLSerializer();
$sqlStatements = $serializer->serialize($ddl, $dialect, $dbmap);
if (!empty($sqlStatements)) {
if ($dialect == 'mysql') {
fputs(STDOUT, "set foreign_key_checks = 0;\n");
}
foreach ($sqlStatements as $stmt) {
fprintf(STDOUT, "%s;\n", $stmt);
}
if ($dialect == 'mysql') {
fputs(STDOUT, "set foreign_key_checks = 1;\n");
}
}
break;
case 'listTables':
foreach ($ddl->topLevelEntities as &$tle) {
switch (get_class($tle)) {
case 'DDLTable':
fprintf(STDOUT, "%s\n", $tle->tableName);
break;
}
}
unset($tle);
// release reference to last element
break;
case 'insertsWithKeyCols':
$serializer = new SQLDDLSerializer();
foreach ($ddl->topLevelEntities as &$tle) {
switch (get_class($tle)) {
case 'DDLInsert':
if (!empty($tle->keyColumnNames)) {
$sqlStatements = $serializer->serializeInsert($tle, $dialect, $db, null, $ddlDir);
if (!empty($sqlStatements)) {
if ($dialect == 'mysql') {
fputs(STDOUT, "set foreign_key_checks = 0;\n");
}
foreach ($sqlStatements as $stmt) {
fprintf(STDOUT, "%s;\n", $stmt);
}
if ($dialect == 'mysql') {
fputs(STDOUT, "set foreign_key_checks = 1;\n");
}
}
}
break;
}
}
unset($tle);
// release reference to last element
break;
}
}
public function generateSQLUpdates($oldDDL, $newDDL, $allowDropTable = false, $allowDropColumn = false, $allowDropIndex = false, $dialect = 'mysql', $dbmap = null, $localDBName = null, $basepath = '')
{
if (!in_array($dialect, DDL::$SUPPORTED_DIALECTS)) {
throw new Exception(sprintf("Requested SQL dialect \"%s\" is not in the list of supported dialects (%s).", $dialect, implode(', ', DDL::$SUPPORTED_DIALECTS)));
}
$ldbpfx = $localDBName != '' ? $localDBName . '.' : '';
$serializer = new SQLDDLSerializer();
$sqlStatements = array();
// Determine which table names are common among all tables.
$commonTableNames = $oldDDL->getCommonTableNames($newDDL);
$oldTableNames = $oldDDL->getAllTableNames();
$newTableNames = $newDDL->getAllTableNames();
$idxsDroppedByTableName = array();
$fksDroppedByTableName = array();
$droppedIndexOnTableNames = array();
if ($allowDropIndex) {
// For tables which exist in both old and new schemas, drop all indexes and
// foreign keys which exist in $oldDDL but don't exist in $newDDL,
// or which exist in both but are different between the two.
foreach ($commonTableNames as $tableName) {
$tmp = $oldDDL->getTableIndexesAndForeignKeys($tableName);
$oidxs = $tmp->idxs;
$ofks = $tmp->fks;
unset($tmp);
$tmp = $newDDL->getTableIndexesAndForeignKeys($tableName);
$nidxs = $tmp->idxs;
$nfks = $tmp->fks;
unset($tmp);
// Indexes
foreach (array_keys($oidxs) as $indexName) {
if (!isset($nidxs[$indexName]) || $oidxs[$indexName] != $nidxs[$indexName]) {
switch ($dialect) {
case 'mysql':
// MySQL also creates an index with the same name as the foreign key.
// This means we only want to drop the index if it's not part of
// a foreign key in the new schema, or if it's part of a foreign key
// which does not match between the old and new schemas.
if (!isset($nfks[$indexName]) || isset($ofks[$indexName]) && $ofks[$indexName] != $nfks[$indexName]) {
$itn = $indexName . ' on ' . $ldbpfx . $tableName;
if (!in_array($itn, $droppedIndexOnTableNames)) {
$sqlStatements[] = "drop index {$indexName} on {$ldbpfx}{$tableName}";
$droppedIndexOnTableNames[] = $itn;
}
unset($itn);
}
break;
case 'pgsql':
$itn = $indexName . ' on ' . $tableName;
if (!in_array($itn, $droppedIndexOnTableNames)) {
$sqlStatements[] = "drop index {$indexName}";
$droppedIndexOnTableNames[] = $itn;
}
unset($itn);
break;
}
if (!isset($idxsDroppedByTableName[$tableName])) {
$idxsDroppedByTableName[$tableName] = array();
}
if (!in_array($indexName, $idxsDroppedByTableName[$tableName])) {
$idxsDroppedByTableName[$tableName][] = $indexName;
}
}
}
// Foreign keys
foreach ($ofks as $foreignKeyName => $ofk) {
if (!isset($nfks[$foreignKeyName]) || $ofks[$foreignKeyName] != $nfks[$foreignKeyName]) {
switch ($dialect) {
case 'mysql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop foreign key {$foreignKeyName}";
$itn = $foreignKeyName . ' on ' . $tableName;
if (!in_array($itn, $droppedIndexOnTableNames)) {
$sqlStatements[] = "drop index {$foreignKeyName} on {$ldbpfx}{$tableName}";
$droppedIndexOnTableNames[] = $itn;
}
unset($itn);
break;
case 'pgsql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop constraint {$foreignKeyName}";
break;
}
if (!isset($fksDroppedByTableName[$tableName])) {
$fksDroppedByTableName[$tableName] = array();
}
if (!in_array($foreignKeyName, $fksDroppedByTableName[$tableName])) {
$fksDroppedByTableName[$tableName][] = $foreignKeyName;
}
}
}
}
}
// if ($allowDropIndex)
if ($allowDropTable) {
// Drop all tables which exist in $oldDDL but don't exist in $newDDL.
foreach ($oldTableNames as $tn) {
if (!in_array($tn, $newTableNames)) {
$sqlStatements[] = 'drop table if exists ' . $ldbpfx . $tn;
$sqlStatements[] = 'drop view if exists ' . $ldbpfx . $tn;
if ($dialect == 'pgsql') {
$sqlStatements[] = sprintf("drop sequence if exists %s_autoInc_seq", $ldbpfx . $tn);
}
}
}
}
// if ($allowDropTable)
// Create all tables and their indexes, which exist in $newDDL but don't exist in $oldDDL.
// Perform all inserts for newly created tables.
foreach ($newDDL->topLevelEntities as $ntle) {
if ($ntle instanceof DDLTable && !in_array($ntle->tableName, $commonTableNames)) {
$tles = array($ntle);
for ($i = 0, $ni = count($newDDL->topLevelEntities); $i < $ni; $i++) {
$tle = $newDDL->topLevelEntities[$i];
if (($tle instanceof DDLIndex || $tle instanceof DDLInsert) && $tle->tableName == $ntle->tableName) {
$tles[] = $tle;
continue;
}
}
$tddl = new DDL($tles);
$sqlStatements = array_merge($sqlStatements, $serializer->serialize($tddl, $dialect, $dbmap, $localDBName, $basepath));
}
}
// foreach ($newDDL->topLevelEntities as $ntle)
// Process each common table name.
foreach ($commonTableNames as $tableName) {
// Find the old table definition, and all indexes and foreign keys for it.
$tmp = $oldDDL->getTableIndexesAndForeignKeys($tableName);
$otbl = $tmp->tbl;
$oidxs = $tmp->idxs;
$ofks = $tmp->fks;
unset($tmp);
// Find the new table definition, and all indexes and foreign keys for it.
$tmp = $newDDL->getTableIndexesAndForeignKeys($tableName);
$ntbl = $tmp->tbl;
$nidxs = $tmp->idxs;
$nfks = $tmp->fks;
unset($tmp);
$dbname = $dbmap !== null && $ntbl !== false ? $dbmap->getDatabase($ntbl->group, $ntbl->tableName) : null;
$column_sqlStatements = array();
if ($allowDropColumn) {
// Drop columns which exist in the old table but not the new.
$ncols = array();
foreach ($ntbl->columns as $ncol) {
$ncols[$ncol->name] = $ncol;
}
for ($oci = 0, $onc = count($otbl->columns); $oci < $onc; $oci++) {
$ocol = $otbl->columns[$oci];
if (!isset($ncols[$ocol->name])) {
switch ($dialect) {
case 'mysql':
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop column " . $ocol->name;
break;
case 'pgsql':
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop column " . $ocol->name . " cascade";
break;
}
}
}
}
// Add columns which exist in the new table but not the old.
// Update columns which exist in both tables.
$ocols = array();
foreach ($otbl->columns as $ocol) {
$ocols[$ocol->name] = $ocol;
}
for ($nci = 0, $nnc = count($ntbl->columns); $nci < $nnc; $nci++) {
$ncol = $ntbl->columns[$nci];
if (!isset($ocols[$ncol->name])) {
// Column exists in new but not old. Add the column to the table.
switch ($dialect) {
case 'mysql':
case 'pgsql':
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} add column " . $serializer->serializeTableColumn($ncol, $tableName, $dialect, true, true, true, true, true, $localDBName) . ($nci > 0 ? ' after ' . $ntbl->columns[$nci - 1]->name : ' first');
break;
}
} else {
if ($ncol != $ocols[$ncol->name]) {
// If the table is a view, ignore differences in the auto_increment and default values.
if ($dbname !== null && $dbname != '' && $ncol->isEqualForView($ocols[$ncol->name])) {
continue;
}
// Column exists in both new and old, but is different. Alter the column.
$ocol = $ocols[$ncol->name];
switch ($dialect) {
case 'mysql':
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} change column {$ncol->name} " . $serializer->serializeTableColumn($ncol, $tableName, $dialect, true, true, true, true, true, $localDBName) . ($nci > 0 ? ' after ' . $ntbl->columns[$nci - 1]->name : ' first');
break;
case 'pgsql':
if ($ocol->type != $ncol->type || $ncol->type == 'datetime' && $ocol->useTimeZone != $ncol->useTimeZone || $ocol->size != $ncol->size || $ocol->scale != $ncol->scale) {
// The only binary type PostgreSQL supports is bytea, and it does
// not have a size parameter. Therefore, binary, varbinary and
// blob types all map to bytea in PostgreSQL. If both the new and
// old columns are any of these types, then we don't need to alter
// the column type.
if (($ocol->type == 'binary' || $ocol->type == 'varbinary' || $ocol->type == 'blob') && ($ncol->type == 'binary' || $ncol->type == 'varbinary' || $ncol->type == 'blob')) {
// Nothing to do here.
} else {
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} alter column {$ncol->name} type " . $serializer->serializeTableColumn($ncol, $tableName, $dialect, false, true, false, false, false, $localDBName);
}
}
if ($ocol->allowNull != $ncol->allowNull) {
if ($ncol->allowNull) {
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} alter column {$ncol->name} drop not null";
} else {
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} alter column {$ncol->name} set not null";
}
}
if ($ocol->defaultValue != $ncol->defaultValue || $ocol->sysVarDefault != $ncol->sysVarDefault || $ocol->autoIncrement != $ncol->autoIncrement) {
$s = $serializer->serializeTableColumn($ncol, $tableName, $dialect, false, false, false, true, true, $localDBName);
if ($s != '') {
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} alter column {$ncol->name} set " . ltrim($s, ' ');
// $s includes the "default" keyword.
} else {
$column_sqlStatements[] = "alter table {$ldbpfx}{$tableName} alter column {$ncol->name} drop default";
}
}
break;
}
}
}
}
// for ($nci = 0, $nnc = count($ntbl->columns); $nci < $nnc; $nci++)
$dbname = $dbmap !== null && $ntbl !== false ? $dbmap->getDatabase($ntbl->group, $ntbl->tableName) : null;
if ($dbname !== null && $dbname != '') {
// This is a mapped table, which is represented as a view.
if (!empty($column_sqlStatements)) {
// One or more columns likely changed in the target table. Update the view, then we're done with this table.
$sqlStatements[] = sprintf("drop table if exists %s", $ldbpfx . $ntbl->tableName);
$sqlStatements[] = sprintf("create or replace view %s as select * from %s.%s", $ldbpfx . $ntbl->tableName, $dbname, $ntbl->tableName);
}
continue;
}
// This is a normal table (not mapped to another database). Do the rest of the processing for it.
$sqlStatements = array_merge($sqlStatements, $column_sqlStatements);
// Add, drop or update the primary key.
if ($otbl->primaryKey === false && $ntbl->primaryKey !== false) {
// Add the primary key.
$cns = array();
foreach ($ntbl->primaryKey->columns as $cl) {
$cns[] = $cl->name;
}
switch ($dialect) {
case 'mysql':
case 'pgsql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} add primary key (" . implode(', ', $cns) . ")";
break;
}
} else {
if ($otbl->primaryKey !== false && $ntbl->primaryKey === false) {
// Drop the primary key.
switch ($dialect) {
case 'mysql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop primary key";
break;
case 'pgsql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop constraint {$tableName}_pkey";
break;
}
} else {
if ($otbl->primaryKey !== false && $ntbl->primaryKey !== false && $otbl->primaryKey != $ntbl->primaryKey) {
// Drop and re-add the primary key.
$cns = array();
foreach ($ntbl->primaryKey->columns as $cl) {
$cns[] = $cl->name;
}
switch ($dialect) {
case 'mysql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop primary key";
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} add primary key (" . implode(', ', $cns) . ")";
break;
case 'pgsql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop constraint {$tableName}_pkey";
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} add primary key (" . implode(', ', $cns) . ")";
break;
}
}
}
}
// Create any indexes which are missing or different between the old and new.
// Drop and re-create any indexes which don't match.
foreach (array_keys($nidxs) as $indexName) {
if (!isset($oidxs[$indexName]) || $oidxs[$indexName] != $nidxs[$indexName]) {
if (isset($oidxs[$indexName])) {
// Drop the index (will re-create it below).
$itn = $indexName . ' on ' . $ldbpfx . $tableName;
if (!in_array($itn, $droppedIndexOnTableNames)) {
switch ($dialect) {
case 'mysql':
$sqlStatements[] = "drop index {$indexName} on {$ldbpfx}{$tableName}";
break;
case 'pgsql':
$sqlStatements[] = "drop index {$indexName}";
break;
}
$droppedIndexOnTableNames[] = $itn;
}
unset($itn);
}
// (Re-)create the index.
$tddl = new DDL(array($nidxs[$indexName]));
$sqlStatements = array_merge($sqlStatements, $serializer->serialize($tddl, $dialect, $dbmap, $localDBName, $basepath));
}
}
}
// foreach ($commonTableNames as $tableName)
// For all tables which exist in $newDDL but don't exist in $oldDDL,
// create their foriegn keys.
foreach ($newDDL->topLevelEntities as $ntle) {
if ($ntle instanceof DDLTable && !in_array($ntle->tableName, $commonTableNames)) {
$dbname = $dbmap !== null ? $dbmap->getDatabase($ntle->group, $ntle->tableName) : null;
if ($dbname !== null && $dbname != '') {
// This is a mapped table, which is represented as a view. Don't do anything for this table here.
continue;
}
$tles = array();
for ($i = 0, $ni = count($newDDL->topLevelEntities); $i < $ni; $i++) {
$tle = $newDDL->topLevelEntities[$i];
if ($tle instanceof DDLForeignKey && $tle->localTableName == $ntle->tableName) {
$tles[] = $tle;
continue;
}
}
if (!empty($tles)) {
$tddl = new DDL($tles);
$sqlStatements = array_merge($sqlStatements, $serializer->serialize($tddl, $dialect, $dbmap, $localDBName, $basepath));
}
}
}
// Create any foreign keys which are missing or different between the old and new.
// Drop and re-create any foreign keys which don't match.
foreach ($commonTableNames as $tableName) {
// Find the old table definition, and all foreign keys for it.
$tmp = $oldDDL->getTableIndexesAndForeignKeys($tableName);
$ofks = $tmp->fks;
unset($tmp);
// Find the new table definition, and all indexes and foreign keys for it.
$tmp = $newDDL->getTableIndexesAndForeignKeys($tableName);
$nfks = $tmp->fks;
$ntbl = $tmp->tbl;
unset($tmp);
$dbname = $dbmap !== null && $ntbl !== false ? $dbmap->getDatabase($ntbl->group, $ntbl->tableName) : null;
if ($dbname !== null && $dbname != '') {
// This is a mapped table, which is represented as a view. Don't do anything for this table here.
continue;
}
foreach ($nfks as $foreignKeyName => $nfk) {
if (!isset($ofks[$foreignKeyName]) || $ofks[$foreignKeyName] != $nfks[$foreignKeyName]) {
if (isset($ofks[$foreignKeyName])) {
// Drop the foreign key (will re-create it below).
if (!in_array($foreignKeyName, $fksDroppedByTableName[$tableName])) {
switch ($dialect) {
case 'mysql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop foreign key {$foreignKeyName}";
$itn = $foreignKeyName . ' on ' . $ldbpfx . $tableName;
if (!in_array($itn, $droppedIndexOnTableNames)) {
$sqlStatements[] = "drop index {$foreignKeyName} on {$ldbpfx}{$tableName}";
$droppedIndexOnTableNames[] = $itn;
}
break;
case 'pgsql':
$sqlStatements[] = "alter table {$ldbpfx}{$tableName} drop constraint {$foreignKeyName}";
break;
}
}
}
$tmp = $newDDL->getTableIndexesAndForeignKeys($nfk->foreignTableName);
$tbl = $tmp->tbl;
if ($tbl !== false) {
$dbname = $dbmap !== null && $tbl !== false ? $dbmap->getDatabase($tbl->group, $tbl->tableName) : null;
if ($dbname === null || $dbname == '') {
// (Re-)create the foreign key.
$tddl = new DDL(array($nfks[$foreignKeyName]));
$sqlStatements = array_merge($sqlStatements, $serializer->serialize($tddl, $dialect, null, $localDBName, $basepath));
}
}
}
}
}
// Combine consecutive "alter table" statements for the same table name into a single "alter table" statement,
// limiting its length to 65000 characters.
$new_sqlStatements = array();
$combinedAlterStatement = null;
$combinedAlterPrefix = null;
$alterTableRegex = '/^alter table [^\\s]+ /';
foreach ($sqlStatements as $statement) {
// If the previous statement was an "alter table" statement, and the current statement is
// an "alter table" statement for the same table, and combining them won't exceed the maximum
// statement length we'd like to adhere to, append the new statement to the combined "alter table"
// statement.
// If we couldn't append (not an "alter table" statement, not the same table, or would exceed the
// maximum statement length), flush the existing combined "alter table" statement, and resume
// processing with the new statement.
if ($combinedAlterStatement !== null) {
if (preg_match($alterTableRegex, $statement, $matches) > 0) {
if ($matches[0] == $combinedAlterPrefix) {
$toAdd = ', ' . substr($statement, strlen($combinedAlterPrefix));
if (strlen($combinedAlterStatement) + strlen($toAdd) <= 650000) {
$combinedAlterStatement .= $toAdd;
continue;
}
}
}
$new_sqlStatements[] = $combinedAlterStatement;
$combinedAlterStatement = null;
$combinedAlterPrefixLen = 0;
}
// If we didn't append the current statment to a combined "alter table" statement, but it is
// an "alter table" statement, start a new combined "alter table" statement.
if (preg_match($alterTableRegex, $statement, $matches) > 0) {
$combinedAlterStatement = $statement;
$combinedAlterPrefix = $matches[0];
continue;
}
// This is not an "alter table" statement at all. Append it to the results.
$new_sqlStatements[] = $statement;
}
// Flush the last statment, if it was a (possibly combined) "alter table" statement.
if ($combinedAlterStatement !== null) {
$new_sqlStatements[] = $combinedAlterStatement;
}
return $new_sqlStatements;
}
