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