/** * Outputs commands for addition, removal and modifications of * table columns. * * @param $ofs1 stage1 output file segmenter * @param $ofs3 stage3 output file segmenter * @param $old_table original table * @param $new_table new table */ private static function update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table) { // README: This function differs substantially from its siblings in pgsql8 or mssql10 // The reason for this is two-fold. // // First, for every ALTER TABLE, mysql actually rebuilds the whole table. Thus, it is drastically // more efficient if we can pack as much into a single ALTER TABLE as possible. Secondly, unlike // other RDBMS's, MySQL requires that you completely redefine a column if you (a) rename it, // (b) change its NULL/NOT NULL attribute, (c) change its type, or (d) add or remove its AUTO_INCREMENT // attribute. This means that just about 75% of the changes that could happen between two versions // of column require a column redefine rather than granular alteration. Therefore, it doesn't make // much sense to have 3 redefines of a column in a single ALTER TABLE, which is what happens if you // port over the pgsql8 or mssql10 versions of this function. // // For these reasons, the mysql5 implementation of this function is optimized for making as few // alterations to each column as possible. // arbitrary sql $extra = array('BEFORE1' => array(), 'AFTER1' => array(), 'BEFORE3' => array(), 'AFTER3' => array()); // each entry is keyed by column name, and has a 'command' key, which may be one of // nothing: do nothing // drop: drop this column // change: rename & redefine // create: create this column // modify: redefine without rename // the 'defaults' key is whether to give the column a DEFAULT clause if it is NOT NULL // the 'nulls' key is whether to include NULL / NOT NULL in the column definition $commands = array('1' => array(), '3' => array()); $defaults = array('set' => array(), 'drop' => array()); foreach (dbx::get_table_columns($old_table) as $old_column) { if (!mysql5_table::contains_column($new_table, $old_column['name'])) { if (!dbsteward::$ignore_oldnames && ($renamed_column_name = mysql5_table::column_name_by_old_name($new_table, $old_column['name'])) !== false) { continue; } else { // echo "NOTICE: add_drop_table_columns() " . $new_table['name'] . " does not contain " . $old_column['name'] . "\n"; $commands['3'][(string) $old_column['name']] = array('command' => 'drop', 'column' => $old_column); } } } $new_columns = dbx::get_table_columns($new_table); foreach ($new_columns as $col_index => $new_column) { $cmd1 = array('command' => 'nothing', 'column' => $new_column, 'defaults' => mysql5_diff::$add_defaults, 'nulls' => TRUE, 'auto_increment' => FALSE); if (!mysql5_table::contains_column($old_table, $new_column['name'], TRUE)) { // column not present in old table, is either renamed or new if (!dbsteward::$ignore_oldnames && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // renamed $cmd1['command'] = 'change'; $cmd1['old'] = $new_column['oldColumnName']; } else { // new $cmd1['command'] = 'create'; if ($col_index == 0) { $cmd1['first'] = TRUE; } else { $cmd1['after'] = $new_columns[$col_index - 1]['name']; } // some columns need filled with values before any new constraints can be applied // this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute $db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']); if ($db_doc_new_schema) { $db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']); if ($db_doc_new_table) { $db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']); if ($db_doc_new_column) { if (isset($db_doc_new_column['beforeAddStage1'])) { $extras['BEFORE1'][] = trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition"; } if (isset($db_doc_new_column['afterAddStage1'])) { $extras['AFTER1'][] = trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition"; } if (isset($db_doc_new_column['beforeAddStage3'])) { $extras['BEFORE3'][] = trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition"; } if (isset($db_doc_new_column['afterAddStage3'])) { $extras['AFTER3'][] = trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition"; } } else { throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found"); } } } else { if ($old_column = dbx::get_table_column($old_table, $new_column['name'])) { $old_column_type = mysql5_column::column_type(dbsteward::$old_database, $old_schema, $old_table, $old_column); $new_column_type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column); $old_default = isset($old_column['default']) ? (string) $old_column['default'] : ''; $new_default = isset($new_column['default']) ? (string) $new_column['default'] : ''; $auto_increment_added = !mysql5_column::is_auto_increment($old_column['type']) && mysql5_column::is_auto_increment($new_column['type']); $auto_increment_removed = mysql5_column::is_auto_increment($old_column['type']) && !mysql5_column::is_auto_increment($new_column['type']); $auto_increment_changed = $auto_increment_added || $auto_increment_removed; $type_changed = strcasecmp($old_column_type, $new_column_type) !== 0 || $auto_increment_changed; $default_changed = strcasecmp($old_default, $new_default) !== 0; $nullable_changed = strcasecmp($old_column['null'] ?: 'true', $new_column['null'] ?: 'true') !== 0; $cmd1['command'] = 'nothing'; if ($type_changed || $nullable_changed) { $cmd1['command'] = 'modify'; if ($default_changed && !$new_default) { $cmd1['defaults'] = FALSE; } if ($auto_increment_added) { $cmd1['auto_increment'] = TRUE; } } elseif ($default_changed) { if (strlen($new_default) > 0) { if (mysql5_column::is_timestamp($new_column)) { // timestamps get special treatment $cmd1['command'] = 'modify'; } else { $defaults['set'][] = $new_column; } } else { $defaults['drop'][] = $new_column; } } } } $commands['1'][(string) $new_column['name']] = $cmd1; } // end foreach column $table_name = mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']); $get_command_sql = function ($command) use(&$new_schema, &$new_table) { if ($command['command'] == 'nothing') { return NULL; } if ($command['command'] == 'drop') { $name = mysql5::get_quoted_column_name($command['column']['name']); return "DROP COLUMN {$name}"; } $defn = mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $command['column'], $command['defaults'], $command['nulls'], $command['auto_increment']); if ($command['command'] == 'change') { $old = mysql5::get_quoted_column_name($command['old']); return "CHANGE COLUMN {$old} {$defn}"; } if ($command['command'] == 'create') { if (array_key_exists('first', $command)) { return "ADD COLUMN {$defn} FIRST"; } elseif (array_key_exists('after', $command)) { $col = mysql5::get_quoted_column_name($command['after']); return "ADD COLUMN {$defn} AFTER {$col}"; } else { return "ADD COLUMN {$defn}"; } } if ($command['command'] == 'modify') { return "MODIFY COLUMN {$defn}"; } throw new Exception("Invalid column diff command '{$command['command']}'"); }; // end get_command_sql() // pre-stage SQL foreach ($extra['BEFORE1'] as $sql) { $ofs1->write($sql . "\n\n"); } foreach ($extra['BEFORE3'] as $sql) { $ofs3->write($sql . "\n\n"); } // output stage 1 sql $stage1_commands = array(); foreach ($commands['1'] as $column_name => $command) { $stage1_commands[] = $get_command_sql($command); } // we can also add SET DEFAULTs in here foreach ($defaults['set'] as $column) { $name = mysql5::get_quoted_column_name($column['name']); if (strlen($column['default']) > 0) { $default = (string) $column['default']; } else { $type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $column); $default = mysql5_column::get_default_value($type); } $stage1_commands[] = "ALTER COLUMN {$name} SET DEFAULT {$default}"; } foreach ($defaults['drop'] as $column) { $name = mysql5::get_quoted_column_name($column['name']); $stage1_commands[] = "ALTER COLUMN {$name} DROP DEFAULT"; } $stage1_commands = array_filter($stage1_commands); if (count($stage1_commands) > 0) { $sql = "ALTER TABLE {$table_name}\n "; $sql .= implode(",\n ", $stage1_commands); $sql .= ";\n\n"; $ofs1->write($sql); } // output stage 3 sql $stage3_commands = array(); foreach ($commands['3'] as $column_name => $command) { $stage3_commands[] = $get_command_sql($command); } $stage3_commands = array_filter($stage3_commands); if (count($stage3_commands) > 0) { $sql = "ALTER TABLE {$table_name}\n "; $sql .= implode(",\n ", $stage3_commands); $sql .= ";\n\n"; $ofs3->write($sql); } // post-stage SQL foreach ($extra['AFTER1'] as $sql) { $ofs1->write($sql . "\n\n"); } foreach ($extra['AFTER3'] as $sql) { $ofs3->write($sql . "\n\n"); } }
/** * for type defintion changes, temporarily change all tables columns that reference the type * * @param $columns reference columns returned by reference * @param $node_schema * @param $node_type * @return string DDL */ public static function column_constraint_temporary_drop(&$columns, $node_schema, $node_type) { $ddl = ''; for ($i = 0; $i < count(mssql10_diff::$new_table_dependency); $i++) { // find the necessary pointers $table_item = mssql10_diff::$new_table_dependency[$i]; if ($table_item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } $new_schema = $table_item['schema']; $new_table = $table_item['table']; foreach (dbx::get_table_columns($new_table) as $new_column) { // is the column the passed type? $unquoted_type_name = $node_schema['name'] . '.' . $node_type['name']; if (strcasecmp($new_column['type'], $unquoted_type_name) == 0) { $ddl .= mssql10_type::get_drop_check_sql($new_schema, $new_table, $new_column, $node_type); // add column to the beginning of the list so it will be done before earlier changes (foreign key ordering) array_unshift($columns, array('alter_column_schema' => $new_schema, 'alter_column_table' => $new_table, 'alter_column_column' => $new_column)); } } } return $ddl; }
/** * Returns name of column that says it used to be called $old_name * * @param $old_name * * @return string */ public static function column_name_by_old_name($node_table, $old_column_name) { if (dbsteward::$ignore_oldnames) { throw new exception("dbsteward::ignore_oldname option is on, column_name_by_old_name() should not be getting called"); } $name = false; foreach (dbx::get_table_columns($node_table) as $column) { if (strcasecmp($column['oldColumnName'], $old_column_name) == 0) { $name = $column['name']; break; } } return $name; }
/** * Adds commands for modification of columns to the list of * commands. * * @param commands list of commands * @param old_table original table * @param new_table new table * @param drop_defaults_columns list for storing columns for which default value should be dropped */ private static function add_modify_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns) { $case_sensitive = dbsteward::$quote_all_names || dbsteward::$quote_column_names; foreach (dbx::get_table_columns($new_table) as $new_column) { if (!pgsql8_table::contains_column($old_table, $new_column['name'], $case_sensitive)) { continue; } if (!dbsteward::$ignore_oldnames && pgsql8_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // oldColumnName renamed column ? skip definition diffing on it, it is being renamed continue; } $old_column = pgsql8_table::get_column_by_name($old_table, $new_column['name'], $case_sensitive); $new_column_name = pgsql8::get_quoted_column_name($new_column['name']); $old_column_type = null; if ($old_column) { $old_column_type = pgsql8_column::column_type(dbsteward::$old_database, $new_schema, $old_table, $old_column, $foreign); } $new_column_type = pgsql8_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign); if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $new_column_type) > 0 && $old_column_type !== null && preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $old_column_type) == 0) { throw new Exception("Table " . $new_schema['name'] . "." . $new_table['name'] . " column " . $new_column['name'] . " has linked type " . $new_column_type . " -- Column types cannot be altered to serial. If this column cannot be recreated as part of database change control, a user defined serial should be created, and corresponding nextval() defined as the default for the column."); } if (strcmp($old_column_type, $new_column_type) != 0) { // ALTER TYPE .. USING support by looking up the new type in the xml definition $type_using = ''; $type_using_comment = ''; if (isset($new_column['convertUsing'])) { $type_using = ' USING ' . $new_column['convertUsing'] . ' '; $type_using_comment = '- found XML convertUsing: ' . $new_column['convertUsing'] . ' '; } $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " TYPE " . $new_column_type . $type_using . " /* TYPE change - table: " . $new_table['name'] . " original: " . $old_column_type . " new: " . $new_column_type . ' ' . $type_using_comment . '*/'); } $old_default = isset($old_column['default']) ? $old_column['default'] : ''; $new_default = isset($new_column['default']) ? $new_column['default'] : ''; if (strcmp($old_default, $new_default) != 0) { if (strlen($new_default) == 0) { $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " DROP DEFAULT"); } else { $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $new_default); } } if (strcasecmp($old_column['null'], $new_column['null']) != 0) { if (pgsql8_column::null_allowed($new_table, $new_column)) { $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " DROP NOT NULL"); } else { if (pgsql8_diff::$add_defaults) { $default_value = pgsql8_column::get_default_value($new_column_type); if ($default_value != null) { $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $default_value); $drop_defaults_columns[] = $new_column; } } // if the default value is defined in the dbsteward XML // set the value of the column to the default in end of stage 1 so that NOT NULL can be applied in stage 3 // this way custom <sql> tags can be avoided for upgrade generation if defaults are specified if (strlen($new_column['default']) > 0) { $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . pgsql8::get_quoted_schema_name($new_schema['name']) . "." . pgsql8::get_quoted_table_name($new_table['name']) . " SET " . $new_column_name . " = " . $new_column['default'] . " WHERE " . $new_column_name . " IS NULL; -- has_default_now: make modified column that is null the default value before NOT NULL hits"); } $commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . $new_column_name . " SET NOT NULL"); } } // drop sequence and default if converting from *serial to *int if (preg_match('/serial$/', $old_column['type']) > 0 && ($new_column['type'] == 'int' || $new_column['type'] == 'bigint')) { $commands[] = array('stage' => 'BEFORE3', 'command' => "DROP SEQUENCE IF EXISTS " . pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_table_name(pgsql8::identifier_name($new_schema['name'], $new_table['name'], $new_column['name'], '_seq')) . ";"); $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " DROP DEFAULT"); } } }
/** * Adds commands for modification of columns to the list of * commands. * * @param commands list of commands * @param old_table original table * @param new_table new table * @param drop_defaults_columns list for storing columns for which default value should be dropped */ private static function add_modify_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns) { foreach (dbx::get_table_columns($new_table) as $new_column) { if (!mssql10_table::contains_column($old_table, $new_column['name'])) { continue; } if (!dbsteward::$ignore_oldnames && mssql10_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // oldColumnName renamed column ? skip definition diffing on it, it is being renamed continue; } $quoted_table_name = mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']); $old_column = dbx::get_table_column($old_table, $new_column['name']); $new_column_name = mssql10::get_quoted_column_name($new_column['name']); $old_column_type = null; if ($old_column) { $old_column_type = mssql10_column::column_type(dbsteward::$old_database, $new_schema, $old_table, $old_column, $foreign); } $new_column_type = mssql10_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign); if (strcmp($old_column_type, $new_column_type) != 0) { // ALTER TYPE .. USING support by looking up the new type in the xml definition $type_using = ''; $type_using_comment = ''; if (isset($new_column['convertUsing'])) { $type_using = ' USING ' . $new_column['convertUsing'] . ' '; $type_using_comment = '- found XML convertUsing: ' . $new_column['convertUsing'] . ' '; } // if the column type is a defined enum, (re)add a check constraint to enforce the pseudo-enum if (mssql10_column::enum_type_check(dbsteward::$new_database, $new_schema, $new_table, $new_column, $drop_sql, $add_sql)) { // enum types rewritten as varchar(255) $new_column_type = 'varchar(255)'; $commands[] = array('stage' => 'AFTER1', 'command' => $drop_sql); $commands[] = array('stage' => 'AFTER1', 'command' => $add_sql); } $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . $type_using . " /* TYPE change - table: " . $new_table['name'] . " original: " . $old_column_type . " new: " . $new_column_type . ' ' . $type_using_comment . '*/'); } $old_default = isset($old_column['default']) ? $old_column['default'] : ''; $new_default = isset($new_column['default']) ? $new_column['default'] : ''; // has the default has changed? if (strcmp($old_default, $new_default) != 0) { // in MSSQL, violating the SQL standard, // inline column default definitions are translated to be table constraints // the constraint name is somewhat predictable, but not always. // some versions apped random numebrs when implicitly creating the constraint // was there a constraint before? if (strlen($old_default) > 0) { $commands[] = array('stage' => 'BEFORE1', 'command' => 'ALTER TABLE ' . $quoted_table_name . ' DROP CONSTRAINT ' . 'DF_' . $new_table['name'] . '_' . $old_column['name'] . ';'); } // is there now a default constraint? if (strlen($new_default) > 0) { $commands[] = array('stage' => 'AFTER1', 'command' => 'ALTER TABLE ' . $quoted_table_name . ' ADD CONSTRAINT ' . 'DF_' . $new_table['name'] . '_' . $new_column['name'] . ' DEFAULT ' . $new_default . ' FOR ' . $new_column_name . ';'); } } if (strcasecmp($old_column['null'], $new_column['null']) != 0) { if (mssql10_column::null_allowed($new_table, $new_column)) { $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . " NULL"); } else { if (mssql10_diff::$add_defaults) { $default_value = mssql10_column::get_default_value($new_column_type); if ($default_value != NULL) { $commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $default_value); $drop_defaults_columns[] = $new_column; } } // if the default value is defined in the dbsteward XML // set the value of the column to the default in end of stage 1 so that NOT NULL can be applied in stage 3 // this way custom <sql> tags can be avoided for upgrade generation if defaults are specified if (strlen($new_column['default']) > 0) { $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " SET " . $new_column_name . " = " . $new_column['default'] . " WHERE " . $new_column_name . " IS NULL; -- has_default_now: make modified column that is null the default value before NOT NULL hits"); } // before altering column, remove any constraint that would stop us from doing so foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, 'constraint') as $constraint) { if (preg_match('/' . $new_column['name'] . '[\\s,=)]/', $constraint['definition']) > 0) { $commands[] = array('stage' => '3', 'command' => mssql10_table::get_constraint_drop_sql_change_statement($constraint)); } } $commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . $new_column_name . " " . $new_column_type . " NOT NULL"); // add the constraint back on foreach (mssql10_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, 'constraint') as $constraint) { if (preg_match('/' . $new_column['name'] . '[\\s,=\\)]/', $constraint['definition']) > 0) { $commands[] = array('stage' => '3', 'command' => mssql10_table::get_constraint_sql_change_statement($constraint)); } } } } // for identity()'d columns (serial in dbsteward definition) in mssql that are to no longer be // we must recreate the table with out the identity attribute if (preg_match('/int\\sidentity.*$/', $old_column['type']) > 0 && ($new_column['type'] == 'int' || $new_column['type'] == 'bigint')) { dbsteward::warning("identity()d table " . $new_schema['name'] . "." . $new_table['name'] . " requires rebuild to drop identity property on " . $new_column['name']); // create a "deep copy" of the table so column and rows // references are not altered in the original old DOM $table_for_modifying_xml = $new_table->asXML(); $table_for_modifying = simplexml_load_string($table_for_modifying_xml); // @NOTICE: we do this because of by reference nature of get_table_column() // any subsequent references to old table's pkey column(s) would show the type as int/bigint and not the identity() definition // get the column then modify the type to remove the identity $old_id_pkey_col = dbx::get_table_column($table_for_modifying, $old_column['name']); $table_for_modifying['name'] = 'tmp_identity_drop_' . $table_for_modifying['name']; if (preg_match('/^int/', $old_column['type']) > 0) { $old_id_pkey_col['type'] = 'int'; } else { $old_id_pkey_col['type'] = 'bigint'; } // see FS#25730 - dbsteward not properly upgrading serial to int // http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/ // section start comment $identity_transition_commands = array('-- DBSteward: ' . $new_schema['name'] . '.' . $new_table['name'] . ' identity column ' . $new_column['name'] . ' was redefined to ' . $old_id_pkey_col['type'] . ' - table rebuild is necessary'); // get the creation sql for a temporary table $identity_transition_commands[] = mssql10_table::get_creation_sql($new_schema, $table_for_modifying); // copy over all the old data into new data, it's the only way $identity_transition_commands[] = "IF EXISTS(SELECT * FROM " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($new_table['name']) . ")\n EXEC('INSERT INTO " . mssql10::get_quoted_schema_name($new_schema['name']) . '.' . mssql10::get_quoted_table_name($table_for_modifying['name']) . " ( " . implode(",", mssql10_table::get_column_list($table_for_modifying)) . ")\n SELECT " . implode(",", mssql10_table::get_column_list($table_for_modifying)) . "\n FROM " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " WITH (HOLDLOCK TABLOCKX)');"; // drop FKEYs other tables have to the table $other_tables_foreign_keying_constraints = dbx::get_tables_foreign_keying_to_table(dbsteward::$new_database, mssql10_diff::$new_table_dependency, $new_schema, $new_table); dbsteward::info("identity()d table " . $new_schema['name'] . "." . $new_table['name'] . " rebuild has " . count($other_tables_foreign_keying_constraints) . " foreign key references to drop and reapply"); foreach ($other_tables_foreign_keying_constraints as $constraint) { $identity_transition_commands[] = mssql10_table::get_constraint_drop_sql($constraint); } // drop the old table $identity_transition_commands[] = "DROP TABLE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . ";"; // rename temporary table to original name // NOTE: sp_rename only takes an identifier for the new name, if you schema qualify the new name it will get doubled on the table name $identity_transition_commands[] = "EXECUTE sp_rename '" . $new_schema['name'] . "." . $table_for_modifying['name'] . "', '" . $new_table['name'] . "', 'OBJECT';"; // mssql10_table:::get_creation_sql() only creates the table // now that it has been renamed, recreate the table's indexes keys and triggers $tc_buffer = fopen("php://memory", "rw"); $tc_ofs = new output_file_segmenter('identity_transition_command', 1, $tc_buffer, 'identity_transition_command_buffer'); mssql10_diff_indexes::diff_indexes_table($tc_ofs, NULL, NULL, $new_schema, $new_table); mssql10_diff_tables::diff_constraints_table($tc_ofs, NULL, NULL, $new_schema, $new_table, 'primaryKey', FALSE); mssql10_diff_triggers::diff_triggers_table($tc_ofs, NULL, NULL, $new_schema, $new_table); mssql10_diff_tables::diff_constraints_table($tc_ofs, NULL, NULL, $new_schema, $new_table, 'constraint', FALSE); rewind($tc_buffer); while (($tc_line = fgets($tc_buffer, 4096)) !== false) { $identity_transition_commands[] = $tc_line; } unset($tc_ofs); // restore FKEYs other tables have to the table foreach ($other_tables_foreign_keying_constraints as $constraint) { $identity_transition_commands[] = mssql10_table::get_constraint_sql($constraint); } // section end comment $identity_transition_commands[] = '-- DBSteward: ' . $new_schema['name'] . '.' . $new_table['name'] . ' identity column ' . $new_column['name'] . ' was redefined to ' . $old_id_pkey_col['type'] . ' - table rebuild end' . "\n"; // put all of the identity_transition_commands into the command list as BEFORE3's // this will make the identity column changes occur at the beginning of stage 3 foreach ($identity_transition_commands as $itc) { $commands[] = array('stage' => 'BEFORE3', 'command' => $itc); } } } }
public static function has_identity($node_table) { foreach (dbx::get_table_columns($node_table) as $column) { // only check non-fkeyed columns for the identity types if (isset($column['type']) && stripos($column['type'], 'IDENTITY') !== FALSE) { return TRUE; } } return FALSE; }