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