/** * @NOTICE: sql_format specific! * Compare dbsteward::$old_database to dbsteward::$new_database * Generate DDL / DML / DCL statements to upgrade old to new * * Changes are outputted to output_file_segementer members of this class * * @param object $stage1_ofs stage 1 output file segmentor * @param object $stage2_ofs stage 2 output file segmentor * @param object $stage3_ofs stage 3 output file segmentor * @param object $stage4_ofs stage 4 output file segmentor * @return void */ protected static function diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs) { if (mssql10_diff::$as_transaction) { $stage1_ofs->append_header("BEGIN TRANSACTION;\n\n"); $stage1_ofs->append_footer("\nCOMMIT TRANSACTION;\n"); if (!dbsteward::$single_stage_upgrade) { $stage2_ofs->append_header("BEGIN TRANSACTION;\n\n"); $stage3_ofs->append_header("BEGIN TRANSACTION;\n\n"); $stage4_ofs->append_header("BEGIN TRANSACTION;\n\n"); $stage2_ofs->append_footer("\nCOMMIT TRANSACTION;\n"); $stage3_ofs->append_footer("\nCOMMIT TRANSACTION;\n"); $stage4_ofs->append_footer("\nCOMMIT TRANSACTION;\n"); } } // start with pre-upgrade sql statements that prepare the database to take on its changes dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1BEFORE'); dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2BEFORE'); dbsteward::inf("Drop Old Schemas"); mssql10_diff::drop_old_schemas($stage3_ofs); dbsteward::info("Create New Schemas"); mssql10_diff::create_new_schemas($stage1_ofs); dbsteward::info("Update Structure"); mssql10_diff::update_structure($stage1_ofs, $stage3_ofs, mssql10_diff::$new_table_dependency); dbsteward::info("Update Permissions"); mssql10_diff::update_permissions($stage1_ofs, $stage3_ofs); mssql10_diff::update_database_config_parameters($stage1_ofs); dbsteward::info("Update Data"); mssql10_diff::update_data($stage2_ofs, TRUE); mssql10_diff::update_data($stage2_ofs, FALSE); // append any literal SQL in new not in old at the end of data stage 1 $old_sql = dbx::get_sql(dbsteward::$old_database); $new_sql = dbx::get_sql(dbsteward::$new_database); for ($n = 0; $n < count($new_sql); $n++) { if (isset($new_sql[$n]['stage'])) { // ignore upgrade staged sql elements continue; } // is this new statement in the old database? $found = FALSE; for ($o = 0; $o < count($old_sql); $o++) { if (isset($old_sql[$o]['stage'])) { // ignore upgrade staged sql elements continue; } if (strcmp($new_sql[$n], $old_sql[$o]) == 0) { $found = TRUE; } } if (!$found) { $stage2_ofs->write($new_sql[$n] . "\n"); } } // append stage defined sql statements to appropriate stage file dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1'); dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2'); dbx::build_staged_sql(dbsteward::$new_database, $stage3_ofs, 'STAGE3'); dbx::build_staged_sql(dbsteward::$new_database, $stage4_ofs, 'STAGE4'); }
/** * @NOTICE: sql_format specific! * Compare dbsteward::$old_database to dbsteward::$new_database * Generate DDL / DML / DCL statements to upgrade old to new * * Changes are outputted to output_file_segementer members of this class * * @param object $stage1_ofs stage 1 output file segmentor * @param object $stage2_ofs stage 2 output file segmentor * @param object $stage3_ofs stage 3 output file segmentor * @param object $stage4_ofs stage 4 output file segmentor * @return void */ public static function diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs) { if (mysql5_diff::$as_transaction) { dbsteward::warning("Most MySQL DDL implicitly commits transactions, so using them is pointless."); } // start with pre-upgrade sql statements that prepare the database to take on its changes dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1BEFORE'); dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2BEFORE'); dbsteward::info("Revoke Permissions"); self::revoke_permissions($stage1_ofs, $stage3_ofs); dbsteward::info("Update Structure"); self::update_structure($stage1_ofs, $stage3_ofs, self::$new_table_dependency); dbsteward::info("Update Permissions"); self::update_permissions($stage1_ofs, $stage3_ofs); // self::update_database_config_parameters($stage1_ofs); dbsteward::info("Update Data"); self::update_data($stage2_ofs, TRUE); self::update_data($stage4_ofs, FALSE); // append any literal SQL in new not in old at the end of data stage 1 $old_sql = dbx::get_sql(dbsteward::$old_database); $new_sql = dbx::get_sql(dbsteward::$new_database); for ($n = 0; $n < count($new_sql); $n++) { if (isset($new_sql[$n]['stage'])) { // ignore upgrade staged sql elements continue; } // is this new statement in the old database? $found = FALSE; for ($o = 0; $o < count($old_sql); $o++) { if (isset($old_sql[$o]['stage'])) { // ignore upgrade staged sql elements continue; } if (strcmp($new_sql[$n], $old_sql[$o]) == 0) { $found = TRUE; } } if (!$found) { $stage2_ofs->write($new_sql[$n] . "\n"); } } // append stage defined sql statements to appropriate stage file dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1'); dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2'); dbx::build_staged_sql(dbsteward::$new_database, $stage3_ofs, 'STAGE3'); dbx::build_staged_sql(dbsteward::$new_database, $stage4_ofs, 'STAGE4'); }
public static function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $limit_to_tables_count = count(dbsteward::$limit_to_tables); foreach ($tables as $dep_table) { $schema = $dep_table['schema']; $table = $dep_table['table']; if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(mysql5_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); $table_primary_keys = mysql5_table::primary_key_columns($table); $table_column_names = dbx::to_array($table->column, 'name'); $node_rows =& dbx::get_table_rows($table); // the <rows> element $data_column_names = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY); // set serial primary keys to the max value after inserts have been performed // only if the PRIMARY KEY is not a multi column if (count($table_primary_keys) == 1 && in_array($table_primary_keys[0], $data_column_names)) { $pk_column_name = $table_primary_keys[0]; $node_pk_column = dbx::get_table_column($table, $pk_column_name); if ($node_pk_column == NULL) { throw new exception("Failed to find primary key column '" . $pk_column_name . "' for " . $schema['name'] . "." . $table['name']); } // only set the pkey to MAX() if the primary key column is also a serial/bigserial and if serialStart is not defined if (mysql5_column::is_serial($node_pk_column['type']) && !isset($node_pk_column['serialStart'])) { $fqtn = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']); $qcol = mysql5::get_quoted_column_name($pk_column_name); $setval = mysql5_sequence::get_setval_call(mysql5_column::get_serial_sequence_name($schema, $table, $node_pk_column), "MAX({$qcol})", "TRUE"); $sql = "SELECT {$setval} FROM {$fqtn};\n"; $ofs->write($sql); } } // unlike the pg class, we cannot just set identity column start values here with setval without inserting a row // check if primary key is a column of this table - FS#17481 if (count(array_diff($table_primary_keys, $table_column_names)) != 0) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }
public static function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $tables_count = count($tables); $limit_to_tables_count = count(dbsteward::$limit_to_tables); for ($i = 0; $i < $tables_count; $i++) { $schema = $tables[$i]['schema']; $table = $tables[$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; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); // set serial primary keys to the max value after inserts have been performed // only if the PRIMARY KEY is not a multi column $node_rows =& dbx::get_table_rows($table); $columns = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY); if (isset($table['primaryKey']) && strlen($table['primaryKey']) > 0 && in_array(dbsteward::string_cast($table['primaryKey']), $columns)) { $pk_column = dbsteward::string_cast($table['primaryKey']); // only do it if the primary key column is also a serial/bigserial $nodes = xml_parser::inheritance_get_column($table, $pk_column); if (count($nodes) != 1) { var_dump($nodes); throw new exception("Failed to find primary key column '" . $pk_column . "' for " . $schema['name'] . "." . $table['name']); } $pk = $nodes[0]; $pk_column_type = strtolower(dbsteward::string_cast($pk['type'])); if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $pk_column_type) > 0) { // only set the pkey to MAX() if serialStart is not defined if (!isset($pk['serialStart'])) { $sql = "SELECT setval(pg_get_serial_sequence('" . $schema['name'] . "." . $table['name'] . "', '" . $pk_column . "'), MAX({$pk_column}), TRUE) FROM " . $schema['name'] . "." . $table['name'] . ";\n"; $ofs->write($sql); } } } // check if primary key is a column of this table - FS#17481 $primary_keys_exist = self::primary_key_split($table['primaryKey']); foreach ($table->column as $column) { // while looping through columns, check to see if primary key is one of them // if it is remove it from the primary keys array, at the end of loop array should be empty $key = array_search($column['name'], $primary_keys_exist); if (is_numeric($key)) { unset($primary_keys_exist[$key]); } } // throw an error if the table is using a primaryKey column that does not actually exist if (!empty($primary_keys_exist)) { if (empty($table['inheritsTable'])) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } else { dbsteward::info('Primary key ' . $table['primaryKey'] . ' does not exist as a column in child table ' . $table['name'] . ', but may exist in parent table'); } } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }
/** * @NOTICE: sql_format specific! * Compare dbsteward::$old_database to dbsteward::$new_database * Generate DDL / DML / DCL statements to upgrade old to new * * Changes are outputted to output_file_segementer members of this class * * @param object $stage1_ofs stage 1 output file segmentor * @param object $stage2_ofs stage 2 output file segmentor * @param object $stage3_ofs stage 3 output file segmentor * @param object $stage4_ofs stage 4 output file segmentor * @return void */ public static function diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs) { // this shouldn't be called if we're not generating slonik, it looks for // a slony element in <database> which most likely won't be there if // we're not interested in slony replication if (dbsteward::$generate_slonik) { format::set_context_replica_set_to_natural_first(dbsteward::$new_database); } if (self::$as_transaction) { // stage 1 and 3 should not be in a transaction // as they will be submitted via slonik EXECUTE SCRIPT if (!dbsteward::$generate_slonik) { $stage1_ofs->append_header("\nBEGIN;\n"); $stage1_ofs->append_footer("\nCOMMIT;\n"); } else { $stage1_ofs->append_header("\n-- generateslonik specified: pgsql8 STAGE1 upgrade omitting BEGIN. slonik EXECUTE SCRIPT will wrap stage 1 DDL and DCL in a transaction\n"); } if (!dbsteward::$single_stage_upgrade) { $stage2_ofs->append_header("\nBEGIN;\n\n"); $stage2_ofs->append_footer("\nCOMMIT;\n"); // if generating slonik, stage 1 and 3 should not be in a transaction // as they will be submitted via slonik EXECUTE SCRIPT if (!dbsteward::$generate_slonik) { $stage3_ofs->append_header("\nBEGIN;\n\n"); $stage3_ofs->append_footer("\nCOMMIT;\n"); } else { $stage3_ofs->append_header("\n-- generateslonik specified: pgsql8 STAGE1 upgrade omitting BEGIN. slonik EXECUTE SCRIPT will wrap stage 3 DDL and DCL in a transaction\n"); } $stage4_ofs->append_header("\nBEGIN;\n\n"); $stage4_ofs->append_footer("\nCOMMIT;\n"); } } // start with pre-upgrade sql statements that prepare the database to take on its changes dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1BEFORE'); dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2BEFORE'); dbsteward::info("Drop Old Schemas"); self::drop_old_schemas($stage3_ofs); dbsteward::info("Create New Schemas"); self::create_new_schemas($stage1_ofs); dbsteward::info("Update Structure"); self::update_structure($stage1_ofs, $stage3_ofs, self::$new_table_dependency); dbsteward::info("Update Permissions"); self::update_permissions($stage1_ofs, $stage3_ofs); self::update_database_config_parameters($stage1_ofs, dbsteward::$new_database, dbsteward::$old_database); dbsteward::info("Update Data"); if (dbsteward::$generate_slonik) { format::set_context_replica_set_to_natural_first(dbsteward::$new_database); } self::update_data($stage2_ofs, true); self::update_data($stage4_ofs, false); // append any literal SQL in new not in old at the end of data stage 1 $old_sql = dbx::get_sql(dbsteward::$old_database); $new_sql = dbx::get_sql(dbsteward::$new_database); for ($n = 0; $n < count($new_sql); $n++) { if (isset($new_sql[$n]['stage'])) { // ignore upgrade staged sql elements continue; } // is this new statement in the old database? $found = false; for ($o = 0; $o < count($old_sql); $o++) { if (isset($old_sql[$o]['stage'])) { // ignore upgrade staged sql elements continue; } if (strcmp($new_sql[$n], $old_sql[$o]) == 0) { $found = true; } } if (!$found) { $stage2_ofs->write($new_sql[$n] . "\n"); } } // append stage defined sql statements to appropriate stage file if (dbsteward::$generate_slonik) { format::set_context_replica_set_to_natural_first(dbsteward::$new_database); } dbx::build_staged_sql(dbsteward::$new_database, $stage1_ofs, 'STAGE1'); dbx::build_staged_sql(dbsteward::$new_database, $stage2_ofs, 'STAGE2'); dbx::build_staged_sql(dbsteward::$new_database, $stage3_ofs, 'STAGE3'); dbx::build_staged_sql(dbsteward::$new_database, $stage4_ofs, 'STAGE4'); }
public function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $tables_count = count($tables); $limit_to_tables_count = count(dbsteward::$limit_to_tables); for ($i = 0; $i < $tables_count; $i++) { $schema = $tables[$i]['schema']; $table = $tables[$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; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(mssql10_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); // unlike the pg class, we cannot just set identity column start values here with setval without inserting a row // check if primary key is a column of this table - FS#17481 $primary_keys_exist = self::primary_key_split($table['primaryKey']); foreach ($table->column as $column) { // while looping through columns, check to see if primary key is one of them // if it is remove it from the primary keys array, at the end of loop array should be empty $key = array_search($column['name'], $primary_keys_exist); if (is_numeric($key)) { unset($primary_keys_exist[$key]); } } // throw an error if the table is using a primaryKey column that does not actually exist if (!empty($primary_keys_exist)) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }