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