示例#1
0
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;
    }
}
示例#2
0
 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;
 }