/** * 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; }
/** * Creates and returns SQL for creation of the table. * * @return created SQL command */ public static function get_creation_sql($node_schema, $node_table) { if ($node_schema->getName() != 'schema') { throw new exception("node_schema object element name is not schema. check stack for offending caller"); } if ($node_table->getName() != 'table') { throw new exception("node_table object element name is not table. check stack for offending caller"); } $table_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']); $sql = "CREATE TABLE " . $table_name . " (\n"; foreach (dbx::get_table_columns($node_table) as $column) { $sql .= "\t" . mssql10_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $column, FALSE) . ",\n"; } $sql = substr($sql, 0, strlen($sql) - 2); $sql .= "\n)"; if (isset($node_table['inherits']) && strlen($node_table['inherits']) > 0) { //@TODO: this does not look like it is supported } $sql .= ";"; // @IMPLEMENT: $table['description'] specifier ? foreach (dbx::get_table_columns($node_table) as $column) { if (isset($column['statistics'])) { $sql .= "\nALTER TABLE ONLY " . $table_name . " ALTER COLUMN " . mssql10::get_quoted_column_name($column['name']) . " SET STATISTICS " . $column['statistics'] . ";\n"; } // @IMPLEMENT: $column['description'] specifier ? // if the column type is a defined enum, add a check constraint to enforce the pseudo-enum if (mssql10_column::enum_type_check(dbsteward::$new_database, $node_schema, $node_table, $column, $drop_sql, $add_sql)) { $sql .= $add_sql; } } // @IMPLMENT table ownership with $node_table['owner'] ? return $sql; }
/** * 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 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); } } } }
public static function enum_type_check($db_doc, $node_schema, $node_table, $node_column, &$drop_sql, &$add_sql) { // if the column type is a defined enum, (re)add a check constraint to enforce the pseudo-enum $foreign = array(); $column_type = mssql10_column::column_type($db_doc, $node_schema, $node_table, $node_column, $foreign, FALSE); if (preg_match('/' . dbx::enum_regex($db_doc) . '/i', $column_type) > 0) { $type_schema_name = sql_parser::get_schema_name($column_type, $db_doc); $type_schema = dbx::get_schema($db_doc, $type_schema_name); $node_type = dbx::get_type($type_schema, sql_parser::get_object_name($column_type, $db_doc)); if (!$node_type) { var_dump($node_type); throw new exception('failed to find column_type ' . $column_type . ' in type_schema_name ' . $type_schema_name); } $drop_sql = mssql10_type::get_drop_check_sql($node_schema, $node_table, $node_column, $node_type); $add_sql = mssql10_type::get_add_check_sql($node_schema, $node_table, $node_column, $node_type); return TRUE; } return FALSE; }