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