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