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