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