/** * in MSSQL indexes must contain column references, value expressions are not allowed * */ public static function index_dimension_scan($node_schema, $node_table, $node_index, &$add_column_sql) { $dimension_list = ''; $add_column_sql = ''; // in MSSQL, index dimensions that are not explicit columns must be converted to computed columns to make the index work like it does in postgresql $i = 0; foreach ($node_index->indexDimension as $dimension) { $i++; $dimension_name = (string) $dimension; if (mssql10_table::contains_column($node_table, $dimension_name)) { // dimension is an explicit column // check unique index indexDimensions for nulled columns // mssql index constraint engine will not ignore null values for nullable columns if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) { $node_column = dbx::get_table_column($node_table, $dimension_name); if (mssql10_column::null_allowed($node_table, $node_column)) { dbsteward::error("dimension_name = " . $dimension_name); //var_dump($node_column); throw new exception("nulled column index found"); } } } else { // not an explicit column, so create one $dimension_name = $node_index['name'] . '_' . $i; $add_column_sql .= "ALTER TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']) . "\n" . " ADD " . $dimension_name . " AS " . (string) $dimension . ";\n"; } $dimension_list .= $dimension_name . ', '; } $dimension_list = substr($dimension_list, 0, -2); return $dimension_list; }
public static function get_multiple_create_bits($node_schema, $node_table, $constraints) { $bits = array(); foreach ($constraints as $constraint) { $bits[] = mysql5_constraint::get_constraint_sql($constraint, FALSE); if (strcasecmp($constraint['type'], 'PRIMARY KEY') == 0) { // we're adding the PK constraint, so we need to add AUTO_INCREMENT on any affected columns immediately after! $columns = mysql5_table::primary_key_columns($node_table); foreach ($columns as $col) { $node_column = dbx::get_table_column($node_table, $col); if (mysql5_column::is_auto_increment($node_column['type'])) { $bits[] = "MODIFY " . mysql5_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $node_column, FALSE, TRUE, TRUE); break; // there can only be one AI column per table } } } } return $bits; }
/** * Parses COLUMN and other DDL within '(' and ')' in CREATE TABLE * definition. * * @param table table being parsed * @param line line being processed */ private static function parse_column_defs(&$node_schema, &$node_table, $line) { if (strlen($line) > 0) { $matched = false; if (preg_match(self::PATTERN_CONSTRAINT, trim($line), $matches) > 0) { $node_constraint =& dbx::create_table_constraint($node_table, trim($matches[1])); dbx::set_attribute($node_constraint, 'definition', trim($matches[2])); //@TODO: type? $matched = true; } if (!$matched) { if (preg_match(self::PATTERN_COLUMN, $line, $matches) > 0) { $node_column =& dbx::get_table_column($node_table, trim($matches[1]), true); pgsql8_column::parse_definition($node_schema, $node_table, $node_column, trim($matches[2])); $matched = true; } } if (!$matched) { throw new exception("Cannot parse command: " . $line); } } }
/** * 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"); } }
/** * escape a column's value, or return the default value if none specified * * @NOTE: it is critical to note that colmn values should always be escaped with this function * as it also converts MSSQL specific values from postgresql ones * * @return string */ public static function column_value_default($node_schema, $node_table, $data_column_name, $node_col) { // if marked, make it null or default, depending on column options if (isset($node_col['null']) && strcasecmp('true', $node_col['null']) == 0) { return 'NULL'; } // columns that specify empty attribute are made empty strings if (isset($node_col['empty']) && strcasecmp('true', $node_col['empty']) == 0) { return "''"; } // don't esacape columns marked literal sql values if (isset($node_col['sql']) && strcasecmp($node_col['sql'], 'true') == 0) { return '(' . $node_col . ')'; } $node_column = dbx::get_table_column($node_table, $data_column_name); if ($node_column === NULL) { throw new exception("Failed to find table " . $node_table['name'] . " column " . $data_column_name . " for default value check"); } $value_type = mysql5_column::column_type(dbsteward::$new_database, $node_schema, $node_table, $node_column); // else if col is zero length, make it default, or DB NULL if (strlen($node_col) == 0) { // is there a default defined for the column? $dummy_data_column = new stdClass(); $column_default_value = xml_parser::column_default_value($node_table, $data_column_name, $dummy_data_column); if ($column_default_value != NULL) { // run default value through value_escape to allow data value conversions to happen $value = mysql5::value_escape($value_type, $column_default_value); } else { $value = 'NULL'; } } else { $value = mysql5::value_escape($value_type, dbsteward::string_cast($node_col)); } return $value; }
/** * determine SQL clause expression to match for data_row primary keys * * @return string */ public static function primary_key_expression($db_doc, $node_schema, $node_table, $data_row_columns, $data_row) { $primary_keys = format_table::primary_key_columns($node_table); $primary_key_index = xml_parser::data_row_overlay_primary_key_index($primary_keys, $data_row_columns, $data_row_columns); $primary_key_index = $primary_key_index['base']; // figure out the primary key expression $primary_key_expression = array(); foreach ($primary_keys as $primary_column_name) { if (!isset($primary_key_index[$primary_column_name])) { throw new exception("primary key column named {$primary_column_name} not found in primary_key_index"); } $column_index = $primary_key_index[$primary_column_name]; // get the type of the column, chasing foreign keys if necessary $node_column = dbx::get_table_column($node_table, $primary_column_name); $value_type = format_column::column_type($db_doc, $node_schema, $node_table, $node_column, $foreign); $primary_key_expression[] = format::get_quoted_column_name($primary_column_name) . ' = ' . format::value_escape($value_type, $data_row->col[$column_index]); } if (count($primary_key_expression) == 0) { throw new exception($node_table['name'] . " primary_key_expression is empty, determinate loop failed"); } return implode(' AND ', $primary_key_expression); }
public function testSlonyId() { // make sure if require_slony_set_id is TRUE then there are changes dbsteward::$require_slony_id = TRUE; dbsteward::$slonyid_start_value = 9001; $this->do_slony_numbering(); $out_schema = dbx::get_schema($this->slonyid_doc, 'someapp'); // test that users table column user_id with type serial got set to slonyId 9001 $out_table_users = dbx::get_table($out_schema, 'users'); $out_column_users_user_id = dbx::get_table_column($out_table_users, 'user_id'); $this->assertEquals('9001', $out_column_users_user_id['slonyId']); // test that log table now has slonyId 9002 $out_table_log = dbx::get_table($out_schema, 'log'); $this->assertEquals('9002', $out_table_log['slonyId']); // test that log table log_id serial now has slonyId 9003 $out_column_log_log_id = dbx::get_table_column($out_table_log, 'log_id'); $this->assertEquals('9003', $out_column_log_log_id['slonyId']); }
/** * escape a column's value, or return the default value if none specified * * @NOTE: it is critical to note that colmn values should always be escaped with this function * as it also converts MSSQL specific values from postgresql ones * * @return string */ public static function column_value_default($node_schema, $node_table, $data_column_name, $node_col) { // if marked, make it null or default, depending on column options if (isset($node_col['null']) && strcasecmp('true', $node_col['null']) == 0) { $value = 'NULL'; } else { if (isset($node_col['empty']) && strcasecmp('true', $node_col['empty']) == 0) { // string escape prefix needed? -- see pgsql8::E_ESCAPE usage $value = "''"; } else { if (isset($node_col['sql']) && strcasecmp($node_col['sql'], 'true') == 0) { $value = '(' . $node_col . ')'; } else { $node_column = dbx::get_table_column($node_table, $data_column_name); if ($node_column === NULL) { throw new exception("Failed to find table " . $node_table['name'] . " column " . $data_column_name . " for default value check"); } $value_type = mssql10_column::column_type(dbsteward::$new_database, $node_schema, $node_table, $node_column, $foreign); // else if col is zero length, make it default, or DB NULL if (strlen($node_col) == 0) { // is there a default defined for the column? $dummy_data_column = new stdClass(); $column_default_value = xml_parser::column_default_value($node_table, $data_column_name, $dummy_data_column); if ($column_default_value != NULL) { // run default value through value_escape to allow data value conversions to happen $value = mssql10::value_escape($value_type, $column_default_value); } else { $value = 'NULL'; } } else { $value = mssql10::value_escape($value_type, dbsteward::string_cast($node_col)); } } } } return $value; }
/** * Adds commands for creation of new 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_create_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)) { if (!dbsteward::$ignore_oldnames && pgsql8_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // oldColumnName renamed column ? rename column instead of create new one $old_column_name = pgsql8::get_quoted_column_name($new_column['oldColumnName']); $new_column_name = pgsql8::get_quoted_column_name($new_column['name']); $commands[] = array('stage' => 'AFTER1', 'command' => "-- column rename from oldColumnName specification\n" . "ALTER TABLE " . pgsql8::get_quoted_schema_name($new_schema['name']) . "." . pgsql8::get_quoted_table_name($new_table['name']) . " RENAME COLUMN {$old_column_name} TO {$new_column_name};"); continue; } // notice $include_null_definition is false // this is because ADD COLUMNs with NOT NULL will fail when there are existing rows /* @DIFFTOOL - look for columns of a certain type being added if ( preg_match('/time|date/i', $new_column['type']) > 0 ) { echo $new_schema . "." . $new_table['name'] . "." . $new_column['name'] . " TYPE " . $new_column['type'] . " " . $new_column['default'] . "\n"; } /**/ $commands[] = array('stage' => '1', 'command' => "\tADD COLUMN " . pgsql8_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, pgsql8_diff::$add_defaults, false)); // instead we put the NOT NULL defintion in stage3 schema changes once data has been updated in stage2 data if (!pgsql8_column::null_allowed($new_table, $new_column)) { $commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . pgsql8::get_quoted_column_name($new_column['name']) . " SET NOT NULL"); // also, if it's defined, default the column in stage 1 so the SET NULL will actually pass in stage 3 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 " . pgsql8::get_quoted_column_name($new_column['name']) . " = DEFAULT" . " WHERE " . pgsql8::get_quoted_column_name($new_column['name']) . " IS NULL;"); } } // FS#15997 - dbsteward - replica inconsistency on added new columns with default now() // slony replicas that add columns via DDL that have a default of NOW() will be out of sync // because the data in those columns is being placed in as a default by the local db server // to compensate, add UPDATE statements to make the these column's values NOW() from the master if (pgsql8_column::has_default_now($new_table, $new_column)) { $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . pgsql8::get_quoted_schema_name($new_schema['name']) . "." . pgsql8::get_quoted_table_name($new_table['name']) . " SET " . pgsql8::get_quoted_column_name($new_column['name']) . " = " . $new_column['default'] . " ; -- has_default_now: this statement is to make sure new columns are in sync on replicas"); } if (pgsql8_diff::$add_defaults && !pgsql8_column::null_allowed($new_table, $new_column)) { $drop_defaults_columns[] = $new_column; } // 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'])) { $commands[] = array('stage' => 'BEFORE1', 'command' => 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'])) { $commands[] = array('stage' => 'AFTER1', 'command' => 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'])) { $commands[] = array('stage' => 'BEFORE3', 'command' => 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'])) { $commands[] = array('stage' => 'AFTER3', 'command' => 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"); } } } }
/** * 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); } } } }
/** * Attepts to find a column on a foreign table. * Walks up table inheritance chains. * If the foreign column is itself a foreign key, resolves the type of that column before returning. */ private static function resolve_foreign_column($db_doc, $local_schema, $local_table, $local_colname, $foreign_schema, $foreign_table, $foreign_colname, $visited = array()) { // walk up the foreign table inheritance chain to find the foreign column definition $fschema = $foreign_schema; $ftable = $foreign_table; do { $foreign_column = dbx::get_table_column($ftable, $foreign_colname); if ($ftable['inheritsSchema']) { $fschema = dbx::get_schema($db_doc, (string) $ftable['inheritsSchema']); } if ($ftable['inheritsTable']) { $ftable = dbx::get_table($fschema, (string) $ftable['inheritsTable']); } else { $ftable = null; } } while (!$foreign_column && !!$fschema && !!$ftable); if (!$foreign_column) { // column wasn't found in any referenced tables throw new Exception("Local column {$local_schema['name']}.{$local_table['name']}.{$local_colname} references unknown column {$foreign_schema['name']}.{$foreign_table['name']}.{$foreign_colname}"); } // column type is missing, and resolved foreign is also a foreign key? // recurse and find the cascading foreign key if (empty($foreign_column['type']) && !empty($foreign_column['foreignTable'])) { // make sure we don't visit the same column twice $foreign_col = format::get_fully_qualified_column_name($foreign_schema['name'], $foreign_table['name'], $foreign_column['name']); if (in_array($foreign_col, $visited)) { $local = format::get_fully_qualified_column_name($local_schema['name'], $local_table['name'], $local_colname); throw new Exception("Foreign key cyclic dependency detected! Local column {$local} pointing to foreign column {$foreign_col}"); } $visited[] = $foreign_col; $nested_fkey = self::foreign_key_lookup($db_doc, $foreign_schema, $foreign_table, $foreign_column, $visited); // make a separate clone of the column element because we are specifying the type only for foreign key type referencing $foreign_column = new SimpleXMLElement($foreign_column->asXML()); $foreign_column['type'] = (string) $nested_fkey['column']['type']; } return $foreign_column; }