/**
* 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");
}
}
protected static function get_dimension_list($node_schema, $node_table, $node_index)
{
$dimensions = array();
foreach ($node_index->indexDimension as $dimension) {
// mysql only supports indexed columns, not indexed expressions like in pgsql or mssql
if (!mysql5_table::contains_column($node_table, $dimension)) {
throw new Exception("Table " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']) . " does not contain column '{$dimension}'");
}
$dimensions[] = mysql5::get_quoted_column_name($dimension);
}
return $dimensions;
}
