public function build_schema($db_doc, $ofs, $table_depends)
{
// explicitly create the ROLE_APPLICATION
// webservers connect as a user granted this role
$ofs->write("CREATE ROLE " . $db_doc->database->role->application . ";\n");
// schema creation
foreach ($db_doc->schema as $schema) {
$ofs->write(mssql10_schema::get_creation_sql($schema));
// schema grants
if (isset($schema->grant)) {
foreach ($schema->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $schema, $grant) . "\n");
}
}
}
// types: enumerated list, etc
foreach ($db_doc->schema as $schema) {
foreach ($schema->type as $type) {
$ofs->write(mssql10_type::get_creation_sql($schema, $type) . "\n");
}
}
// function definitions
foreach ($db_doc->schema as $schema) {
foreach ($schema->function as $function) {
if (mssql10_function::has_definition($function)) {
$ofs->write(mssql10_function::get_creation_sql($schema, $function));
}
}
}
$ofs->write("\n");
// table structure creation
foreach ($db_doc->schema as $schema) {
// create defined tables
foreach ($schema->table as $table) {
// table definition
$ofs->write(mssql10_table::get_creation_sql($schema, $table) . "\n");
// table indexes
mssql10_diff_indexes::diff_indexes_table($ofs, NULL, NULL, $schema, $table);
// table grants
if (isset($table->grant)) {
foreach ($table->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $table, $grant) . "\n");
}
}
$ofs->write("\n");
}
// sequences contained in the schema
if (isset($schema->sequence)) {
foreach ($schema->sequence as $sequence) {
$ofs->write(mssql10_bit_table::get_creation_sql($schema, $sequence) . "\n");
// sequence permission grants
if (isset($sequence->grant)) {
foreach ($sequence->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $sequence, $grant) . "\n");
}
}
}
}
}
$ofs->write("\n");
// define table primary keys before foreign keys so unique requirements are always met for FOREIGN KEY constraints
foreach ($db_doc->schema as $schema) {
foreach ($schema->table as $table) {
mssql10_diff_tables::diff_constraints_table($ofs, NULL, NULL, $schema, $table, 'primaryKey', FALSE);
}
}
$ofs->write("\n");
// foreign key references
// use the dependency order to specify foreign keys in an order that will satisfy nested foreign keys and etc
for ($i = 0; $i < count($table_depends); $i++) {
$schema = $table_depends[$i]['schema'];
$table = $table_depends[$i]['table'];
if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
mssql10_diff_tables::diff_constraints_table($ofs, NULL, NULL, $schema, $table, 'constraint', FALSE);
}
$ofs->write("\n");
// trigger definitions
foreach ($db_doc->schema as $schema) {
foreach ($schema->trigger as $trigger) {
// only do triggers set to the current sql format
if (strcasecmp($trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) {
$ofs->write(mssql10_trigger::get_creation_sql($schema, $trigger));
}
}
}
$ofs->write("\n");
// view creation
foreach ($db_doc->schema as $schema) {
foreach ($schema->view as $view) {
$ofs->write(mssql10_view::get_creation_sql($schema, $view));
// view permission grants
if (isset($view->grant)) {
foreach ($view->grant as $grant) {
$ofs->write(mssql10_permission::get_sql($db_doc, $schema, $view, $grant) . "\n");
}
}
}
}
$ofs->write("\n");
// @TODO: database configurationParameter support needed ?
}
/**
* Updates objects in schemas.
*
* @param ofs1 stage1 output file segmenter
* @param ofs3 stage3 output file segmenter
* @param $old_database original database
* @param $new_database new database
*/
private static function update_structure($ofs1, $ofs3)
{
$type_modified_columns = array();
// drop all views in all schemas, regardless whether dependency order is known or not
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
$new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']);
mssql10_diff_views::drop_views($ofs1, $old_schema, $new_schema);
}
//@TODO: implement mssql10_language ? no relevant conversion exists see other TODO's stating this
//mssql10_diff_languages::diff_languages($ofs1);
// if the table dependency order is unknown, bang them in natural order
if (!is_array(mssql10_diff::$new_table_dependency)) {
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
//@NOTICE: @TODO: this does not honor old*Name attributes, does it matter?
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
mssql10_diff_types::apply_changes($ofs1, $old_schema, $new_schema, $type_modified_columns);
mssql10_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
mssql10_diff_sequences::diff_sequences($ofs1, $old_schema, $new_schema);
// remove old constraints before table contraints, so the SQL statements succeed
mssql10_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', TRUE);
mssql10_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', TRUE);
mssql10_diff_tables::drop_tables($ofs3, $old_schema, $new_schema);
mssql10_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema);
mssql10_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema);
mssql10_diff_tables::diff_clusters($ofs1, $old_schema, $new_schema);
mssql10_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', FALSE);
mssql10_diff_triggers::diff_triggers($ofs1, $old_schema, $new_schema);
}
// non-primary key constraints may be inter-schema dependant, and dependant on other's primary keys
// and therefore should be done after object creation sections
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
mssql10_diff_tables::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE);
}
} else {
$processed_schemas = array();
for ($i = 0; $i < count(mssql10_diff::$new_table_dependency); $i++) {
// find the necessary pointers
$item = mssql10_diff::$new_table_dependency[$i];
// @NOTICE: dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME is NOT checked here because these are schema operations
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
// do all types and functions on their own before table creation
// see next loop for other once per schema work
if (!in_array(trim($new_schema['name']), $processed_schemas)) {
mssql10_diff_types::apply_changes($ofs1, $old_schema, $new_schema, $type_modified_columns);
mssql10_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
$processed_schemas[] = trim($new_schema['name']);
}
}
// remove all old constraints before new contraints, in reverse dependency order
for ($i = count(mssql10_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
// find the necessary pointers
$item = mssql10_diff::$old_table_dependency[$i];
if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$new_table = NULL;
if ($new_schema != NULL) {
$new_table = dbx::get_table($new_schema, $item['table']['name']);
}
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
$old_table = NULL;
if ($old_schema != NULL) {
$old_table = dbx::get_table($old_schema, $item['table']['name']);
}
if ($old_table == NULL) {
throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!");
}
// @NOTICE: when dropping constraints, dbx::renamed_table_check_pointer() is not called for $old_table
// as mssql10_diff_tables::diff_constraints_table() will do rename checking when recreating constraints for renamed tables
mssql10_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', TRUE);
mssql10_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', TRUE);
}
$processed_schemas = array();
for ($i = 0; $i < count(mssql10_diff::$new_table_dependency); $i++) {
// find the necessary pointers
$item = mssql10_diff::$new_table_dependency[$i];
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$new_table = NULL;
if ($new_schema != NULL) {
$new_table = dbx::get_table($new_schema, $item['table']['name']);
}
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
// schema level stuff should only be done once, keep track of which ones we have done
// see above for pre table creation stuff
// see below for post table creation stuff
if (!in_array($new_schema['name'], $processed_schemas)) {
mssql10_diff_sequences::diff_sequences($ofs1, $old_schema, $new_schema);
$processed_schemas[] = $new_schema['name'];
}
if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
$old_table = NULL;
if ($old_schema != NULL) {
$old_table = dbx::get_table($old_schema, $item['table']['name']);
}
dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
mssql10_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table, $new_table);
mssql10_diff_indexes::diff_indexes_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
mssql10_diff_tables::diff_clusters_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
mssql10_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', FALSE);
mssql10_diff_triggers::diff_triggers_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
mssql10_diff_tables::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', FALSE);
}
// drop old tables in reverse dependency order
for ($i = count(mssql10_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
// find the necessary pointers
$item = mssql10_diff::$old_table_dependency[$i];
if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$new_table = NULL;
if ($new_schema != NULL) {
$new_table = dbx::get_table($new_schema, $item['table']['name']);
}
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
$old_table = NULL;
if ($old_schema != NULL) {
$old_table = dbx::get_table($old_schema, $item['table']['name']);
}
if ($old_table == NULL) {
throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!");
}
mssql10_diff_tables::drop_tables($ofs3, $old_schema, $new_schema, $old_table, $new_table);
}
}
// create all views in all schemas, regardless whether dependency order is known or not
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
$new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']);
mssql10_diff_views::create_views($ofs1, $old_schema, $new_schema);
}
}
/**
* 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);
}
}
}
}
