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;
}
public static function build($output_prefix, $db_doc)
{
if (strlen($output_prefix) == 0) {
throw new exception("pgsql8::build() sanity failure: output_prefix is blank");
}
// build full db creation script
$build_file = $output_prefix . '_build.sql';
dbsteward::info("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");
}
if (!dbsteward::$generate_slonik) {
$build_file_ofs->write("BEGIN;\n\n");
}
dbsteward::info("Calculating table foreign key dependency order..");
$table_dependency = xml_parser::table_dependency_order($db_doc);
// database-specific implementation code refers to dbsteward::$new_database when looking up roles/values/conflicts etc
dbsteward::$new_database = $db_doc;
dbx::set_default_schema($db_doc, 'public');
// language defintions
if (dbsteward::$create_languages) {
foreach ($db_doc->language as $language) {
$build_file_ofs->write(pgsql8_language::get_creation_sql($language));
}
}
// by default, postgresql will validate the contents of LANGUAGE SQL functions during creation
// because we are creating all functions before tables, this doesn't work when LANGUAGE SQL functions
// refer to tables yet to be created.
// scan language="sql" functions for <functionDefiniton>s that contain FROM (<TABLE>) statements
$set_check_function_bodies = TRUE;
// on in default postgresql configs
dbx::set_default_schema($db_doc, 'public');
foreach ($db_doc->schema as $schema) {
foreach ($schema->function as $function) {
if (pgsql8_function::has_definition($function)) {
$definition = pgsql8_function::get_definition($function);
if (strcasecmp($definition['language'], 'sql') == 0 && $definition['sqlFormat'] == 'pgsql8' && !is_null($referenced_table_name = static::function_definition_references_table($definition))) {
$table_schema_name = sql_parser::get_schema_name($referenced_table_name, $db_doc);
$node_schema = dbx::get_schema($db_doc, $table_schema_name);
$node_table = dbx::get_table($node_schema, sql_parser::get_object_name($referenced_table_name));
if ($node_table) {
// the referenced table is in the definition
// turn off check_function_bodies
$set_check_function_bodies = FALSE;
$set_check_function_bodies_info = "Detected LANGUAGE SQL function " . $schema['name'] . '.' . $function['name'] . " referring to table " . $table_schema_name . '.' . $node_table['name'] . " in the database definition";
dbsteward::info($set_check_function_bodies_info);
break 2;
}
}
}
}
}
if (!$set_check_function_bodies) {
$build_file_ofs->write("\n");
$build_file_ofs->write("SET check_function_bodies = FALSE; -- DBSteward " . $set_check_function_bodies_info . "\n\n");
}
if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) {
dbsteward::info("Defining structure");
pgsql8::build_schema($db_doc, $build_file_ofs, $table_dependency);
}
if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) {
dbsteward::info("Defining data inserts");
pgsql8::build_data($db_doc, $build_file_ofs, $table_dependency);
}
dbsteward::$new_database = NULL;
if (!dbsteward::$generate_slonik) {
$build_file_ofs->write("COMMIT;\n\n");
}
if (dbsteward::$generate_slonik) {
$replica_sets = static::get_slony_replica_sets($db_doc);
foreach ($replica_sets as $replica_set) {
// output preamble file standalone for tool chains that use the preamble to do additional slonik commands
pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_preamble.slonik");
// output paths specificity standalone for tool chains that use the store path slonik statements separately
pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_paths.slonik");
// output create set file standalone for tool chains that use the create_set slonik separately
$create_set_filename = $output_prefix . '_slony_replica_set_' . $replica_set['id'] . '_create_set.slonik';
pgsql8::build_slonik_create_set($db_doc, $replica_set, $create_set_filename);
pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik");
pgsql8::build_slonik_store_nodes($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik");
pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik");
// build full subscribe steps that creates sets and subscribes nodes
$subscribe_filename = $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik";
pgsql8::build_slonik_preamble($db_doc, $replica_set, $subscribe_filename);
// create_set does one time slony configuration comparison.
// so append the content of _create_set into _subscribe built earlier
file_put_contents($subscribe_filename, file_get_contents($create_set_filename), FILE_APPEND);
foreach ($replica_set->slonyReplicaSetNode as $replica_set_node) {
pgsql8::build_slonik_subscribe_set_node($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik", $replica_set_node);
}
static::slony_ids_required_during_build($replica_set, $db_doc);
}
$count = 0;
foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) {
$count += count(self::$sequence_slony_ids[$slony_set_id]);
}
dbsteward::notice("[slony] ID summary: " . count(self::$table_slony_ids) . " tables " . $count . " sequences");
dbsteward::notice("[slony] table ID segments: " . static::slony_id_segment_summary(self::$table_slony_ids));
// keep this from bombing on there being no ids in $sequence_slony_ids
// if there were none returned (i.e. either there weren't any defined
// or they were all set to IGNORE_REQUIRED which hopefully doesn't happen
// because why would you do that for all of them)
if (!empty(self::$sequence_slony_ids)) {
foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) {
$console_line = "[slony] sequence ID segments";
if ($slony_set_id != 'NoSlonySet') {
$console_line .= " for slonySetId {$slony_set_id}";
}
$console_line .= ": ";
dbsteward::notice($console_line . static::slony_id_segment_summary(self::$sequence_slony_ids[$slony_set_id]));
}
}
}
return $db_doc;
}
/**
* Loads database schema from dump file.
*
* @param file input file to be read
*
* @return database schema from dump fle
*/
public static function load_database($files)
{
// one or more files to load as database
if (!is_array($files)) {
$files = array($files);
}
pgsql8::$track_pg_identifiers = true;
pgsql8::$known_pg_identifiers = array();
$database = new SimpleXMLElement('<dbsteward></dbsteward>');
dbx::set_default_schema($database, 'public');
foreach ($files as $file) {
dbsteward::notice("Loading " . $file);
$fp = fopen($file, 'r');
if ($fp === false) {
throw new exception("failed to open database dump file " . $file);
}
$line = fgets($fp);
while ($line != null) {
// blindly include LITERAL_SQL_INCLUDE lines in the database literal_sql collection
if (preg_match(self::PATTERN_LITERAL_SQL, $line, $matches) > 0) {
dbx::add_sql($database, trim($line));
// clear the line that was literally obsorbed
$line = ' ';
}
$line = trim(self::strip_comment(trim($line)));
if (strlen($line) == 0) {
$line = fgets($fp);
continue;
} else {
if (preg_match(self::PATTERN_INSERT_INTO, $line, $matches) > 0) {
pgsql8_parser_insert_into::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_DELETE_FROM, $line, $matches) > 0) {
pgsql8_parser_delete_from::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_LANGUAGE, $line, $matches) > 0) {
pgsql8_parser_create_language::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_ALTER_LANGUAGE, $line, $matches) > 0) {
pgsql8_parser_alter_language::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_TYPE, $line, $matches) > 0) {
pgsql8_parser_create_type::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_SCHEMA, $line, $matches) > 0) {
pgsql8_parser_create_schema::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_DEFAULT_SCHEMA, $line, $matches) > 0) {
dbx::set_default_schema($database, $matches[1]);
} else {
if (preg_match(self::PATTERN_ALTER_SCHEMA, $line, $matches) > 0) {
pgsql8_parser_alter_schema::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_TABLE, $line, $matches) > 0) {
pgsql8_parser_create_table::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_ALTER_TABLE, $line, $matches) > 0) {
pgsql8_parser_alter_table::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_SEQUENCE, $line, $matches) > 0) {
pgsql8_parser_create_sequence::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_ALTER_SEQUENCE, $line, $matches) > 0) {
pgsql8_parser_alter_sequence::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_INDEX, $line, $matches) > 0) {
pgsql8_parser_create_index::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_VIEW, $line, $matches) > 0) {
pgsql8_parser_create_view::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_ALTER_VIEW, $line, $matches) > 0) {
pgsql8_parser_alter_view::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_TRIGGER, $line, $matches) > 0) {
pgsql8_parser_create_trigger::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CREATE_FUNCTION, $line, $matches) > 0) {
pgsql8_parser_create_function::parse($database, self::get_whole_function($fp, $line));
} else {
if (preg_match(self::PATTERN_ALTER_FUNCTION, $line, $matches) > 0) {
pgsql8_parser_alter_function::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_GRANT_REVOKE, $line, $matches) > 0) {
pgsql8_parser_grant_revoke::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_CONFIG_PARAMETER, $line, $matches) > 0) {
pgsql8_parser_config_parameter::parse($database, self::get_whole_command($fp, $line));
} else {
if (preg_match(self::PATTERN_SET, $line, $matches) > 0 || preg_match(self::PATTERN_COMMENT, $line, $matches) > 0 || preg_match(self::PATTERN_SELECT, $line, $matches) > 0 || preg_match(self::PATTERN_BEGIN_END, $line, $matches) > 0) {
// @TODO: implement these pg_dump modifiers?
self::get_whole_command($fp, $line);
} else {
throw new exception("Line did not match to any patterns: " . $line);
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
}
$line = fgets($fp);
/* Development debug: every line, save our current rendition of $database to disk
xml_parser::save_xml(dirname(__FILE__) . '/../../../../dbsteward_monitor.xml', $database->asXML());
/**/
//echo $line . "\n";
}
fclose($fp);
}
pgsql8::$track_pg_identifiers = false;
return $database;
}
