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 function __call($m, $a) { $ignore_ofs_methods = array('__destruct'); if (in_array($m, $ignore_ofs_methods)) { return 'IGNORE_OFS_COMMAND_COMPLETE'; } // if the command is in the list of commands to run on all ofs objects, do so $all_ofs_methods = array('append_header', 'append_footer'); if (in_array($m, $all_ofs_methods)) { foreach ($this->ofs as $set_id => $ofs) { call_user_func_array(array(&$ofs, $m), $a); } return 'ALL_OFS_COMMAND_COMPLETE'; } $use_replica_set_id = format::get_context_replica_set_id(); if ($use_replica_set_id == -10) { // context_replica_set_id -10 means object does not have slonySetId defined // use the natural first replica set as the replica context $first_replica_set = pgsql8::get_slony_replica_set_natural_first(dbsteward::$new_database); $use_replica_set_id = (int) $first_replica_set['id']; } // make sure replica set id to use is known if (!isset($this->ofs[$use_replica_set_id])) { if ($this->skip_unknown_set_ids) { dbsteward::notice("[OFS RSR] context replica set ID is " . $use_replica_set_id . ", but no replica set by that ID, skipping output"); return FALSE; } throw new exception("context replica set ID " . $use_replica_set_id . " not defined"); } $active_set_ofs = $this->ofs[$use_replica_set_id]; dbsteward::debug("[OFS RSR] __call calling " . $use_replica_set_id . " ofs::" . $m); return call_user_func_array(array(&$active_set_ofs, $m), $a); }
/** * determine the next file segment and open that file and file pointer for all future writes * * @return void */ protected function next_file_segment() { if (!$this->segmenting_enabled) { throw new exception("next_file_segment called while segmenting_enabled is false. base_file_name = " . $this->base_file_name); } if ($this->file_pointer !== NULL) { $this->write_footer(); fclose($this->file_pointer); $this->file_segment += 1; } $this->current_output_file = $this->base_file_name . $this->file_segment . '.sql'; dbsteward::notice("[File Segment] Opening output file segement " . $this->current_output_file); $this->file_pointer = fopen($this->current_output_file, 'w'); $this->write_header(); $this->statement_count = 0; }
/** * composite postgresql schema_to_xml() database_to_xml() data outputs onto a dbsteward database definition * * @param SimpleXMLElement $base full definition element to add data to * @param SimpleXMLElement $pgdatafiles postgres table data XML from database_to_xml() to overlay in * * @return void */ public static function xml_composite_pgdata(&$base, $pgdatafiles) { // psql -U deployment megatrain_nkiraly -c "select database_to_xml(true, false, 'http://dbsteward.org/pgdataxml');" /* <megatrain_nkiraly xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://dbsteward.org/pgdataxml"> <public> <system_status_list> <row> <system_status_list_id>1</system_status_list_id> <system_status>Active</system_status> </row> </system_status_list> </public> <search_results> </search_results> </megatrain_nkiraly> /**/ foreach ($pgdatafiles as $file) { $file_name = realpath($file); dbsteward::notice("Loading postgres data XML " . $file_name); $xml_contents = @file_get_contents($file_name); if ($xml_contents === FALSE) { throw new exception("Failed to load postgres data XML from disk: " . $file_name); } $doc = simplexml_load_string($xml_contents); if ($doc === FALSE) { throw new Exception("failed to simplexml_load_string() contents of " . $file_name); } dbsteward::info("Compositing postgres data (size=" . strlen($xml_contents) . ")"); foreach ($doc as $schema) { foreach ($schema as $table) { $table_xpath = "schema[@name='" . $schema->getName() . "']/table[@name='" . $table->getName() . "']"; $nodes = $base->xpath($table_xpath); if (count($nodes) != 1 || $nodes === FALSE) { var_dump($nodes); throw new exception("xpath did not yield one table match: " . $table_xpath . " - do the schema and table exist in your DBSteward XML?"); } $node = $nodes[0]; // check for actual row children $rows = $table->children(); if (count($rows) == 0) { //throw new exception("table " . $table->getName() . " has no row children"); } else { // first row members used to designate columns attribute $row = $rows[0]; $columns = ''; $i = 0; foreach ($row as $column) { $columns .= $column->getName(); if ($i < count($row) - 1) { $columns .= ', '; } $i++; } // switch rows pointer back to the document $rows = $node->addChild('rows'); $rows->addAttribute('columns', $columns); // pump all of the row cols foreach ($table as $row) { if (strcasecmp($row->getName(), 'row') != 0) { throw new exception("schema->table->row iterator expected row tag but found " . $row->getName()); } $node_row = $rows->addChild('row'); foreach ($row as $column) { $node_row->addChild('col', self::ampersand_magic($column)); } } } } } // revalidate composited xml self::validate_xml($base->asXML()); } }
public function xml_data_insert($def_file, $data_file) { dbsteward::info("Automatic insert data into " . $def_file . " from " . $data_file); $def_doc = simplexml_load_file($def_file); if (!$def_doc) { throw new exception("Failed to load " . $def_file); } $data_doc = simplexml_load_file($data_file); if (!$data_doc) { throw new exception("Failed to load " . $data_file); } // for each of the tables defined, act on rows addColumns definitions foreach ($data_doc->schema as $data_schema) { $xpath = "schema[@name='" . $data_schema['name'] . "']"; $def_schema = $def_doc->xpath($xpath); if (count($def_schema) == 0) { throw new exception("definition " . $xpath . " not found"); } if (count($def_schema) > 1) { throw new exception("more than one " . $xpath . " found"); } $def_schema = $def_schema[0]; foreach ($data_schema->table as $data_table) { $xpath = "table[@name='" . $data_table['name'] . "']"; $def_table = $def_schema->xpath($xpath); if (count($def_table) == 0) { throw new exception("definition " . $xpath . " not found"); } if (count($def_table) > 1) { throw new exception("more than one " . $xpath . " found"); } $def_table = $def_table[0]; if (!isset($data_table->rows) || !isset($data_table->rows->row)) { throw new exception($xpath . " rows->row definition is incomplete"); } if (count($data_table->rows->row) > 1) { throw new exception("Unexpected: more than one rows->row found in " . $xpath . " definition"); } $definition_columns = preg_split("/[\\,\\s]+/", $def_table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY); $new_columns = preg_split("/[\\,\\s]+/", $data_table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY); for ($i = 0; $i < count($new_columns); $i++) { $new_column = $new_columns[$i]; dbsteward::info("Adding rows column " . $new_column . " to definition table " . $def_table['name']); if (in_array($new_column, $definition_columns)) { throw new exception("new column " . $new_column . " is already defined in dbsteward definition file"); } $def_table->rows['columns'] = $def_table->rows['columns'] . ", " . $new_column; $col_value = $data_table->rows->row->col[$i]; // add the value to each row col set in def_table->rows foreach ($def_table->rows->row as $row) { $row->addChild('col', $col_value); } } } } $def_file_modified = $def_file . '.xmldatainserted'; dbsteward::notice("Saving modified dbsteward definition as " . $def_file_modified); return xml_parser::save_xml($def_file_modified, $def_doc->saveXML()); }
public static function extract_schema($host, $port, $database, $user, $password) { $databases = explode(',', $database); dbsteward::notice("Connecting to mysql5 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user); // if not supplied, ask for the password if ($password === FALSE) { echo "Password: "******"Analyzing database {$database}"); $db->use_database($database); $node_schema = $doc->addChild('schema'); $node_schema['name'] = $database; $node_schema['owner'] = 'ROLE_OWNER'; // extract global and schema permissions under the public schema foreach ($db->get_global_grants($user) as $db_grant) { $node_grant = $node_schema->addChild('grant'); // There are 28 permissions encompassed by the GRANT ALL statement $node_grant['operation'] = $db_grant->num_ops == 28 ? 'ALL' : $db_grant->operations; $node_grant['role'] = self::translate_role_name($user, $doc); if ($db_grant->is_grantable) { $node_grant['with'] = 'GRANT'; } } $enum_types = array(); $enum_type = function ($obj, $mem, $values) use(&$enum_types) { // if that set of values is defined by a previous enum, use that foreach ($enum_types as $name => $enum) { if ($enum === $values) { return $name; } } // otherwise, make a new one $name = "enum_" . md5(implode('_', $values)); $enum_types[$name] = $values; return $name; }; foreach ($db->get_tables() as $db_table) { dbsteward::info("Analyze table options/partitions " . $db_table->table_name); $node_table = $node_schema->addChild('table'); $node_table['name'] = $db_table->table_name; $node_table['owner'] = 'ROLE_OWNER'; // because mysql doesn't have object owners $node_table['description'] = $db_table->table_comment; $node_table['primaryKey'] = ''; if (stripos($db_table->create_options, 'partitioned') !== FALSE && ($partition_info = $db->get_partition_info($db_table))) { $node_partition = $node_table->addChild('tablePartition'); $node_partition['sqlFormat'] = 'mysql5'; $node_partition['type'] = $partition_info->type; switch ($partition_info->type) { case 'HASH': case 'LINEAR HASH': $opt = $node_partition->addChild('tablePartitionOption'); $opt->addAttribute('name', 'expression'); $opt->addAttribute('value', $partition_info->expression); $opt = $node_partition->addChild('tablePartitionOption'); $opt->addAttribute('name', 'number'); $opt->addAttribute('value', $partition_info->number); break; case 'KEY': case 'LINEAR KEY': $opt = $node_partition->addChild('tablePartitionOption'); $opt->addAttribute('name', 'columns'); $opt->addAttribute('value', $partition_info->columns); $opt = $node_partition->addChild('tablePartitionOption'); $opt->addAttribute('name', 'number'); $opt->addAttribute('value', $partition_info->number); break; case 'LIST': case 'RANGE': case 'RANGE COLUMNS': $opt = $node_partition->addChild('tablePartitionOption'); $opt->addAttribute('name', $partition_info->type == 'RANGE COLUMNS' ? 'columns' : 'expression'); $opt->addAttribute('value', $partition_info->expression); foreach ($partition_info->segments as $segment) { $node_seg = $node_partition->addChild('tablePartitionSegment'); $node_seg->addAttribute('name', $segment->name); $node_seg->addAttribute('value', $segment->value); } break; } } foreach ($db->get_table_options($db_table) as $name => $value) { if (strcasecmp($name, 'auto_increment') === 0 && !static::$use_auto_increment_table_options) { // don't extract auto_increment tableOptions if we're not using them continue; } $node_option = $node_table->addChild('tableOption'); $node_option['sqlFormat'] = 'mysql5'; $node_option['name'] = $name; $node_option['value'] = $value; } dbsteward::info("Analyze table columns " . $db_table->table_name); foreach ($db->get_columns($db_table) as $db_column) { $node_column = $node_table->addChild('column'); $node_column['name'] = $db_column->column_name; if (!empty($db_column->column_comment)) { $node_column['description'] = $db_column->column_comment; } // returns FALSE if not serial, int/bigint if it is $type = $db->is_serial_column($db_table, $db_column); if (!$type) { $type = $db_column->column_type; if (stripos($type, 'enum') === 0) { $values = $db->parse_enum_values($db_column->column_type); $type = $enum_type($db_table->table_name, $db_column->column_name, $values); } if ($db_column->is_auto_increment) { $type .= ' AUTO_INCREMENT'; } } if ($db_column->is_auto_update) { $type .= ' ON UPDATE CURRENT_TIMESTAMP'; } $node_column['type'] = $type; // @TODO: if there are serial sequences/triggers for the column then convert to serial if ($db_column->column_default !== NULL) { $node_column['default'] = mysql5::escape_default_value($db_column->column_default); } elseif (strcasecmp($db_column->is_nullable, 'YES') === 0) { $node_column['default'] = 'NULL'; } $node_column['null'] = strcasecmp($db_column->is_nullable, 'YES') === 0 ? 'true' : 'false'; } // get all plain and unique indexes dbsteward::info("Analyze table indexes " . $db_table->table_name); foreach ($db->get_indices($db_table) as $db_index) { // don't process primary key indexes here if (strcasecmp($db_index->index_name, 'PRIMARY') === 0) { continue; } // implement unique indexes on a single column as unique column, but only if the index name is the column name if ($db_index->unique && count($db_index->columns) == 1 && strcasecmp($db_index->columns[0], $db_index->index_name) === 0) { $column = $db_index->columns[0]; $node_column = dbx::get_table_column($node_table, $column); if (!$node_column) { throw new Exception("Unexpected: Could not find column node {$column} for unique index {$db_index->index_name}"); } else { $node_column = $node_column[0]; } $node_column['unique'] = 'true'; } else { $node_index = $node_table->addChild('index'); $node_index['name'] = $db_index->index_name; $node_index['using'] = strtolower($db_index->index_type); $node_index['unique'] = $db_index->unique ? 'true' : 'false'; $i = 1; foreach ($db_index->columns as $column_name) { $node_index->addChild('indexDimension', $column_name)->addAttribute('name', $column_name . '_' . $i++); } } } // get all primary/foreign keys dbsteward::info("Analyze table constraints " . $db_table->table_name); foreach ($db->get_constraints($db_table) as $db_constraint) { if (strcasecmp($db_constraint->constraint_type, 'primary key') === 0) { $node_table['primaryKey'] = implode(',', $db_constraint->columns); } elseif (strcasecmp($db_constraint->constraint_type, 'foreign key') === 0) { // mysql sees foreign keys as indexes pointing at indexes. // it's therefore possible for a compound index to point at a compound index if (!$db_constraint->referenced_columns || !$db_constraint->referenced_table_name) { throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} does not refer to any foreign columns"); } if (count($db_constraint->referenced_columns) == 1 && count($db_constraint->columns) == 1) { // not a compound index, define the FK inline in the column $column = $db_constraint->columns[0]; $ref_column = $db_constraint->referenced_columns[0]; $node_column = dbx::get_table_column($node_table, $column); if (!$node_column) { throw new Exception("Unexpected: Could not find column node {$column} for foreign key constraint {$db_constraint->constraint_name}"); } $node_column['foreignSchema'] = $db_constraint->referenced_table_schema; $node_column['foreignTable'] = $db_constraint->referenced_table_name; $node_column['foreignColumn'] = $ref_column; unset($node_column['type']); // inferred from referenced column $node_column['foreignKeyName'] = $db_constraint->constraint_name; // RESTRICT is the default, leave it implicit if possible if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) { $node_column['foreignOnDelete'] = str_replace(' ', '_', $db_constraint->delete_rule); } if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) { $node_column['foreignOnUpdate'] = str_replace(' ', '_', $db_constraint->update_rule); } } elseif (count($db_constraint->referenced_columns) > 1 && count($db_constraint->referenced_columns) == count($db_constraint->columns)) { $node_fkey = $node_table->addChild('foreignKey'); $node_fkey['columns'] = implode(', ', $db_constraint->columns); $node_fkey['foreignSchema'] = $db_constraint->referenced_table_schema; $node_fkey['foreignTable'] = $db_constraint->referenced_table_name; $node_fkey['foreignColumns'] = implode(', ', $db_constraint->referenced_columns); $node_fkey['constraintName'] = $db_constraint->constraint_name; // RESTRICT is the default, leave it implicit if possible if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) { $node_fkey['onDelete'] = str_replace(' ', '_', $db_constraint->delete_rule); } if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) { $node_fkey['onUpdate'] = str_replace(' ', '_', $db_constraint->update_rule); } } else { var_dump($db_constraint); throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} has mismatched columns"); } } elseif (strcasecmp($db_constraint->constraint_type, 'unique') === 0) { dbsteward::warning("Ignoring UNIQUE constraint '{$db_constraint->constraint_name}' because they are implemented as indices"); } elseif (strcasecmp($db_constraint->constraint_type, 'check') === 0) { // @TODO: implement CHECK constraints } else { throw new exception("unknown constraint_type {$db_constraint->constraint_type}"); } } foreach ($db->get_table_grants($db_table, $user) as $db_grant) { dbsteward::info("Analyze table permissions " . $db_table->table_name); $node_grant = $node_table->addChild('grant'); $node_grant['operation'] = $db_grant->operations; $node_grant['role'] = self::translate_role_name($user, $doc); if ($db_grant->is_grantable) { $node_grant['with'] = 'GRANT'; } } } foreach ($db->get_sequences() as $db_seq) { $node_seq = $node_schema->addChild('sequence'); $node_seq['name'] = $db_seq->name; $node_seq['owner'] = 'ROLE_OWNER'; $node_seq['start'] = $db_seq->start_value; $node_seq['min'] = $db_seq->min_value; $node_seq['max'] = $db_seq->max_value; $node_seq['inc'] = $db_seq->increment; $node_seq['cycle'] = $db_seq->cycle ? 'true' : 'false'; // the sequences table is a special case, since it's not picked up in the tables loop $seq_table = $db->get_table(mysql5_sequence::TABLE_NAME); foreach ($db->get_table_grants($seq_table, $user) as $db_grant) { $node_grant = $node_seq->addChild('grant'); $node_grant['operation'] = $db_grant->operations; $node_grant['role'] = self::translate_role_name($doc, $user); if ($db_grant->is_grantable) { $node_grant['with'] = 'GRANT'; } } } foreach ($db->get_functions() as $db_function) { dbsteward::info("Analyze function " . $db_function->routine_name); $node_fn = $node_schema->addChild('function'); $node_fn['name'] = $db_function->routine_name; $node_fn['owner'] = 'ROLE_OWNER'; $node_fn['returns'] = $type = $db_function->dtd_identifier; if (strcasecmp($type, 'enum') === 0) { $node_fn['returns'] = $enum_type($db_function->routine_name, 'returns', $db->parse_enum_values($db_function->dtd_identifier)); } $node_fn['description'] = $db_function->routine_comment; if (isset($db_function->procedure) && $db_function->procedure) { $node_fn['procedure'] = 'true'; } // $node_fn['procedure'] = 'false'; $eval_type = $db_function->sql_data_access; // srsly mysql? is_deterministic varchar(3) not null default '', contains YES or NO $determinism = strcasecmp($db_function->is_deterministic, 'YES') === 0 ? 'DETERMINISTIC' : 'NOT DETERMINISTIC'; $node_fn['cachePolicy'] = mysql5_function::get_cache_policy_from_characteristics($determinism, $eval_type); $node_fn['mysqlEvalType'] = str_replace(' ', '_', $eval_type); // INVOKER is the default, leave it implicit when possible if (strcasecmp($db_function->security_type, 'definer') === 0) { $node_fn['securityDefiner'] = 'true'; } foreach ($db_function->parameters as $param) { $node_param = $node_fn->addChild('functionParameter'); // not supported in mysql functions, even though it's provided? // $node_param['direction'] = strtoupper($param->parameter_mode); $node_param['name'] = $param->parameter_name; $node_param['type'] = $type = $param->dtd_identifier; if (strcasecmp($type, 'enum') === 0) { $node_param['type'] = $enum_type($db_function->routine_name, $param->parameter_name, $db->parse_enum_values($param->dtd_identifier)); } if (isset($param->direction)) { $node_param['direction'] = $param->direction; } } $node_def = $node_fn->addChild('functionDefinition', $db_function->routine_definition); $node_def['language'] = 'sql'; $node_def['sqlFormat'] = 'mysql5'; } foreach ($db->get_triggers() as $db_trigger) { dbsteward::info("Analyze trigger " . $db_trigger->name); $node_trigger = $node_schema->addChild('trigger'); foreach ((array) $db_trigger as $k => $v) { $node_trigger->addAttribute($k, $v); } $node_trigger->addAttribute('sqlFormat', 'mysql5'); } foreach ($db->get_views() as $db_view) { dbsteward::info("Analyze view " . $db_view->view_name); if (!empty($db_view->view_name) && empty($db_view->view_query)) { throw new Exception("Found a view in the database with an empty query. User '{$user}' problaby doesn't have SELECT permissions on tables referenced by the view."); } $node_view = $node_schema->addChild('view'); $node_view['name'] = $db_view->view_name; $node_view['owner'] = 'ROLE_OWNER'; $node_view->addChild('viewQuery', $db_view->view_query)->addAttribute('sqlFormat', 'mysql5'); } foreach ($enum_types as $name => $values) { $node_type = $node_schema->addChild('type'); $node_type['type'] = 'enum'; $node_type['name'] = $name; foreach ($values as $v) { $node_type->addChild('enum')->addAttribute('name', $v); } } } xml_parser::validate_xml($doc->asXML()); return xml_parser::format_xml($doc->saveXML()); }
/** * compare composite db doc to specified database * * @return string XML */ public static function compare_db_data($db_doc, $host, $port, $database, $user, $password) { dbsteward::notice("Connecting to pgsql8 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user); // if not supplied, ask for the password if ($password === FALSE) { // @TODO: mask the password somehow without requiring a PHP extension echo "Password: "******"host={$host} port={$port} dbname={$database} user={$user} password={$password}"); dbsteward::info("Comparing composited dbsteward definition data rows to postgresql database connection table contents"); // compare the composited dbsteward document to the established database connection // effectively looking to see if rows are found that match primary keys, and if their contents are the same foreach ($db_doc->schema as $schema) { foreach ($schema->table as $table) { if (isset($table->rows)) { $table_name = dbsteward::string_cast($schema['name']) . '.' . dbsteward::string_cast($table['name']); $primary_key_cols = self::primary_key_split($table['primaryKey']); $cols = preg_split("/[\\,\\s]+/", $table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY); $col_types = array(); foreach ($table->column as $table_column) { $type = ''; // foreign keyed columns inherit their foreign reference type if (isset($table_column['foreignTable']) && isset($table_column['foreignColumn'])) { if (strlen($type) > 0) { throw new exception("type of " . $type . " was found for " . dbsteward::string_cast($cols[$j]) . " in table " . dbsteward::string_cast($table['name']) . " but it is foreign keyed!"); } $foreign = array(); dbx::foreign_key($db_doc, $schema, $table, $table_column, $foreign); // don't need to error-check, foreign_key() is self-checking if it doesnt find the fkey col it will complain $type = $foreign['column']['type']; } else { $type = dbsteward::string_cast($table_column['type']); } if (strlen($type) == 0) { throw new exception($table_name . " column " . $table_column['name'] . " type not found!"); } $col_types[dbsteward::string_cast($table_column['name'])] = $type; } foreach ($table->rows->row as $row) { // glue the primary key expression together for the where $primary_key_expression = ''; for ($k = 0; $k < count($primary_key_cols); $k++) { $column_name = pgsql8::get_quoted_column_name($primary_key_cols[$k]); $pk_index = array_search($primary_key_cols[$k], $cols); if ($pk_index === FALSE) { throw new exception("failed to find " . $schema['name'] . "." . $table['name'] . " primary key column " . $primary_key_cols[$k] . " in cols list (" . implode(", ", $cols) . ")"); } $primary_key_expression .= $column_name . " = " . pgsql8::value_escape($col_types[$primary_key_cols[$k]], $row->col[$pk_index], $db_doc); if ($k < count($primary_key_cols) - 1) { $primary_key_expression .= ' AND '; } } $sql = "SELECT *\n FROM " . $table_name . "\n WHERE " . $primary_key_expression; $rs = pgsql8_db::query($sql); // is the row supposed to be deleted? if (strcasecmp('true', $row['delete']) == 0) { if (pg_num_rows($rs) > 0) { dbsteward::notice($table_name . " row marked for DELETE found WHERE " . $primary_key_expression); } } else { if (pg_num_rows($rs) == 0) { dbsteward::notice($table_name . " does not contain row WHERE " . $primary_key_expression); } else { if (pg_num_rows($rs) > 1) { dbsteward::notice($table_name . " contains more than one row WHERE " . $primary_key_expression); while (($db_row = pg_fetch($rs)) !== FALSE) { dbsteward::notice("\t" . implode(', ', $db_row)); } } else { $db_row = pg_fetch_assoc($rs); // make sure any aspects of the $row are present in the $db_row for ($i = 0; $i < count($cols); $i++) { $xml_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($row->col[$i])); $db_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($db_row[$cols[$i]])); $values_match = FALSE; // evaluate if they are equal $values_match = $xml_value == $db_value; // if they are not PHP equal, and are alternate expressionable, ask the database if (!$values_match && preg_match('/^time.*|^date.*|^interval/i', $col_types[$cols[$i]]) > 0) { // do both describe atleast some value (greater than zero len?) if (strlen($xml_value) > 0 && strlen($db_value) > 0) { $sql = "SELECT '{$xml_value}'::" . $col_types[$cols[$i]] . " = '{$db_value}'::" . $col_types[$cols[$i]] . " AS equal_eval"; $values_match = pgsql8_db::query_str($sql) == 't'; } } if (!$values_match) { dbsteward::warning($table_name . " row column WHERE (" . $primary_key_expression . ") " . $cols[$i] . " data does not match database row column: '" . $xml_value . "' VS '" . $db_value . "'"); } } } } } } } } } //xml_parser::validate_xml($db_doc->asXML()); return xml_parser::format_xml($db_doc->saveXML()); }
/** * 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; }