public static function build($output_prefix, $db_doc) { if (strlen($output_prefix) == 0) { throw new exception("mssql10::build() sanity failure: output_prefix is blank"); } // build full db creation script $build_file = $output_prefix . '_build.sql'; dbsteward::notice("Building complete file " . $build_file); $build_file_fp = fopen($build_file, 'w'); if ($build_file_fp === FALSE) { throw new exception("failed to open full file " . $build_file . ' for output'); } $build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file); if (count(dbsteward::$limit_to_tables) == 0) { $build_file_ofs->write("-- full database definition file generated " . date('r') . "\n"); } // error encountered: ALTER DATABASE statement not allowed within multi-statement transaction. // so do the assembly configuration before the db structure / data creation transaction // see BEGIN TRANSACTION below if (isset($db_doc->inlineAssembly)) { foreach ($db_doc->inlineAssembly as $assembly) { $assembly_file_name = dirname($files[0]) . '/' . $assembly['name']; if (!is_readable($assembly_file_name)) { throw new exception("assembly file " . $assembly_file_name . " not readable"); } $assembly_name = substr(basename($assembly_file_name), 0, -4); dbsteward::info("Including " . $assembly_name . " assembly inline from " . $assembly_file_name); $afh = fopen($assembly_file_name, "rb"); $assembly_contents = fread($afh, filesize($assembly_file_name)); fclose($afh); $assembly_binary_hex = '0x' . bin2hex($assembly_contents); $ddl = "CREATE ASSEMBLY " . $assembly_name . "\n" . "FROM " . $assembly_binary_hex . "\n" . "WITH PERMISSION_SET = SAFE;\n\n"; $build_file_ofs->write($ddl); } unset($db_doc->inlineAssembly); } $build_file_ofs->write("BEGIN TRANSACTION;\n\n"); dbsteward::info("Calculating table foreign key dependency order.."); $table_dependency = xml_parser::table_dependency_order($db_doc); // database-specific implementation refers to dbsteward::$new_database when looking up roles/values/conflicts etc dbsteward::$new_database = $db_doc; dbx::set_default_schema($db_doc, 'dbo'); // language defintions if (dbsteward::$create_languages) { foreach ($db_doc->language as $language) { //@TODO: implement mssql10_language ? no relevant conversion exists see other TODO's stating this } } if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) { dbsteward::notice("Defining structure"); mssql10::build_schema($db_doc, $build_file_ofs, $table_dependency); } if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) { dbsteward::notice("Defining data inserts"); mssql10::build_data($db_doc, $build_file_ofs, $table_dependency); } dbsteward::$new_database = NULL; $build_file_ofs->write("COMMIT TRANSACTION;\n\n"); return $db_doc; }
/** * Setup file pointers then call diff_doc_work() to do the actual diffing * @param string $old_xml_file * @param string $new_xml_file * @param SimpleXMLElement $old_database * @param SimpleXMLElement $new_database * @param string $upgrade_prefix */ public static function diff_doc($old_xml_file, $new_xml_file, $old_database, $new_database, $upgrade_prefix) { $timestamp = date('r'); $old_set_new_set = "-- Old definition: " . $old_xml_file . "\n" . "-- New definition: " . $new_xml_file . "\n"; // setup file pointers, depending on stage file mode -- single (all the same) or multiple if (dbsteward::$single_stage_upgrade) { $single_stage_upgrade_file = $upgrade_prefix . '_single_stage.sql'; $single_stage_fp = fopen($single_stage_upgrade_file, 'w'); if ($single_stage_fp === false) { throw new exception("failed to open upgrade single stage output file " . $single_stage_upgrade_file . ' for write'); } $stage1_ofs = new output_file_segmenter($single_stage_upgrade_file, 1, $single_stage_fp, $single_stage_upgrade_file); $stage1_ofs->set_header("-- DBSteward single stage upgrade changes - generated " . $timestamp . "\n" . $old_set_new_set); $stage2_ofs =& $stage1_ofs; $stage3_ofs =& $stage1_ofs; $stage4_ofs =& $stage1_ofs; } else { $stage1_ofs = new output_file_segmenter($upgrade_prefix . '_stage1_schema', 1); $stage1_ofs->set_header("-- DBSteward stage 1 structure additions and modifications - generated " . $timestamp . "\n" . $old_set_new_set); $stage2_ofs = new output_file_segmenter($upgrade_prefix . '_stage2_data', 1); $stage2_ofs->set_header("-- DBSteward stage 2 data definitions removed - generated " . $timestamp . "\n" . $old_set_new_set); $stage3_ofs = new output_file_segmenter($upgrade_prefix . '_stage3_schema', 1); $stage3_ofs->set_header("-- DBSteward stage 3 structure changes, constraints and removals - generated " . $timestamp . "\n" . $old_set_new_set); $stage4_ofs = new output_file_segmenter($upgrade_prefix . '_stage4_data', 1); $stage4_ofs->set_header("-- DBSteward stage 4 data definition changes and additions - generated " . $timestamp . "\n" . $old_set_new_set); } dbsteward::$old_database = $old_database; dbsteward::$new_database = $new_database; static::diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs); }
/** * Creates views in dependency order * @param output_file_segmenter $ofs Output file segmenter to write to * @param SimpleXMLElement $db_doc_old Old database document * @param SimpleXMLElement $db_doc_new New database document */ public static function create_views_ordered($ofs, $db_doc_old, $db_doc_new) { static::with_views_in_order($db_doc_new, function ($new_schema, $new_view) use($db_doc_new, $db_doc_old, $ofs) { $old_schema = dbx::get_schema($db_doc_old, $new_schema['name']); $old_view = dbx::get_view($old_schema, $new_view['name']); if (format_diff_views::should_create_view($old_schema, $old_view, $new_schema, $new_view)) { // set replica set context for view if (pgsql8::set_context_replica_set_id($new_view) === -10) { // view doesn't specify one, set from for schema object pgsql8::set_context_replica_set_id($new_schema); } $ofs->write(format_view::get_creation_sql($db_doc_new, $new_schema, $new_view) . "\n"); } }); }
public static function build($output_prefix, $db_doc) { if (strlen($output_prefix) == 0) { throw new exception("mysql5::build() sanity failure: output_prefix is blank"); } // build full db creation script $build_file = $output_prefix . '_build.sql'; dbsteward::notice("Building complete file " . $build_file); $build_file_fp = fopen($build_file, 'w'); if ($build_file_fp === FALSE) { throw new exception("failed to open full file " . $build_file . ' for output'); } $build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file); if (count(dbsteward::$limit_to_tables) == 0) { $build_file_ofs->write("-- full database definition file generated " . date('r') . "\n"); } // $build_file_ofs->write("START TRANSACTION;\n\n"); dbsteward::info("Calculating table foreign key dependency order.."); $table_dependency = xml_parser::table_dependency_order($db_doc); // database-specific implementation refers to dbsteward::$new_database when looking up roles/values/conflicts etc dbsteward::$new_database = $db_doc; // language defintions if (dbsteward::$create_languages) { foreach ($db_doc->language as $language) { dbsteward::warning("Ignoring language {$language['name']} declaration because MySQL does not support languages other than 'sql'"); } } if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) { dbsteward::info("Defining structure"); mysql5::build_schema($db_doc, $build_file_ofs, $table_dependency); } if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) { dbsteward::info("Defining data inserts"); mysql5::build_data($db_doc, $build_file_ofs, $table_dependency); } dbsteward::$new_database = NULL; // $build_file_ofs->write("COMMIT TRANSACTION;\n\n"); return $db_doc; }
public static function slony_compare($files) { if (!is_array($files)) { $files = array($files); } $output_prefix = dirname($files[0]) . '/' . substr(basename($files[0]), 0, -4); $db_doc = xml_parser::xml_composite($output_prefix, $files, $slony_composite_file); $slony_compare_file = $output_prefix . '_slonycompare.sql'; dbsteward::notice("Building slony comparison script " . $slony_compare_file); $slony_compare_file_fp = fopen($slony_compare_file, 'w'); if ($slony_compare_file_fp === FALSE) { throw new exception("failed to open slony comparison script " . $slony_compare_file . ' for output'); } $slony_compare_ofs = new output_file_segmenter($slony_compare_file, 1, $slony_compare_file_fp, $slony_compare_file); $slony_compare_ofs->write("-- slony comparison script generated " . date('r') . "\n"); $slony_compare_ofs->write("-- source files: " . implode(', ', $files) . "\n\n"); $slony_compare_ofs->write("-- Uniformly compare dates and timezones in UTC\n"); $slony_compare_ofs->write("SET timezone='UTC';\n\n"); foreach ($db_doc->schema as $schema) { // select all table column data, in predictable order (via primary key sort) foreach ($schema->table as $table) { $table_ident = $schema['name'] . '.' . $table['name']; if (isset($table['primaryKey']) && strlen($table['primaryKey']) > 0) { $order_by = "ORDER BY " . dbsteward::string_cast($table['primaryKey']); } else { throw new exception($table_ident . ' has no primary key, cannot create slony comparison script without it'); } // analyze table columns $table_columns = ''; foreach ($table->column as $column) { // select any table column sequence values for comparison if (preg_match(pgsql8::PATTERN_SERIAL_COLUMN, $column['type']) > 0) { $sequence_name = pgsql8::identifier_name($schema['name'], $table['name'], $column['name'], '_seq'); $sql = 'SELECT last_value FROM ' . $schema['name'] . '.' . $sequence_name . ';'; $slony_compare_ofs->write($sql . "\n"); } // explicitly name columns, so that column order is homogenized between replicas of any age/source $table_columns .= pgsql8::get_quoted_column_name($column['name']) . ', '; } $table_columns = substr($table_columns, 0, -2); $sql = 'SELECT ' . $table_columns . ' FROM ' . $table_ident . ' ' . $order_by . ';'; $slony_compare_ofs->write($sql . "\n"); } // select any standalone sequences' value for comparison foreach ($schema->sequence as $sequence) { $sql = 'SELECT last_value FROM ' . $schema['name'] . '.' . $sequence['name'] . ';'; $slony_compare_ofs->write($sql . "\n"); } } return $db_doc; }