/**
* 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");
}
}
}
/**
* Generate column defaults from column definitions, returns FALSE if
* no defaults were defined, otherwise return the
* ALTER TABLE ALTER COLUMN SET statements needed.
*
* Don't know if this would work with functions referenced as function(argument1 ... argumentN)
*
* @param type $node_schema
* @param type $node_table
* @param type $node_column
* @param type $add_defaults
* @param type $include_null_definition
* @param type $include_default_nextval
* @return boolean|string
*/
public static function set_column_defaults($node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_default_nextval = TRUE)
{
$fq_table_name = pgsql8::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
$base_sql = "ALTER TABLE " . $fq_table_name . " ALTER COLUMN " . pgsql8::get_quoted_column_name($node_column['name']) . " SET";
$sql = $base_sql;
$changes = FALSE;
if (strlen($node_column['default']) > 0) {
if (!$include_default_nextval && static::has_default_nextval($node_table, $node_column)) {
// if the default is a nextval expression, don't specify it in the regular full definition
// because if the sequence has not been defined yet,
// the nextval expression will be evaluated inline and fail
dbsteward::info("Skipping " . $node_column['name'] . " default expression \"" . $node_column['default'] . "\" - this default expression will be applied after all sequences have been created");
return $changes;
} else {
$sql .= " DEFAULT " . $node_column['default'];
$changes = TRUE;
}
} else {
if (!pgsql8_column::null_allowed($node_table, $node_column) && $add_defaults) {
$default_col_value = pgsql8_column::get_default_value($node_column['type']);
if ($default_col_value != null) {
$sql .= " DEFAULT " . $default_col_value;
$changes = TRUE;
}
}
}
if ($include_null_definition && !pgsql8_column::null_allowed($node_table, $node_column)) {
if ($changes) {
$sql .= ";\n";
$sql .= $base_sql . " NOT NULL";
} else {
$sql .= " NOT NULL";
$changes = TRUE;
}
}
// no changes? we don't have a default for this column... keep going pls
if (!$changes) {
return $changes;
}
$sql .= ";\n";
return $sql;
}
public static function null_allowed($node_table, $node_column)
{
$null_allowed = parent::null_allowed($node_table, $node_column);
if ($null_allowed) {
// if the column is in the primary_key list, make it NOT NULL anyway
// mssql will not implicitly make the column NOT NULL like postgresql
$primary_keys = pgsql8_table::primary_key_columns($node_table);
if (in_array((string) $node_column['name'], $primary_keys)) {
$null_allowed = FALSE;
}
}
return $null_allowed;
}