/**
* Creates SQL diff of two SQL dumps
*
* @return array output files list
*/
public static function diff_sql($old_sql_files, $new_sql_files, $upgrade_prefix)
{
dbsteward::$old_database = pgsql8_dump_loader::load_database($old_sql_files);
$old_sql_xml_file = $upgrade_prefix . '_old_sql.xml';
xml_parser::save_xml($old_sql_xml_file, dbsteward::$old_database->saveXML());
dbsteward::$new_database = pgsql8_dump_loader::load_database($new_sql_files);
$new_sql_xml_file = $upgrade_prefix . '_new_sql.xml';
xml_parser::save_xml($new_sql_xml_file, dbsteward::$new_database->saveXML());
self::diff_xml(array($old_sql_xml_file), array($new_sql_xml_file), $upgrade_prefix);
}
/**
* sort elements / levels of $file_name, save it as $sorted_file_name
*
* @param string $file_name dbsteward definition file to sort
* @param string $sorted_file_name sorted dbsteward definition save as
* @return boolean success
*/
public static function file_sort($file_name, $sorted_file_name)
{
$doc = simplexml_load_file($file_name);
// create a list of schemas and tables to get around simplexml iterator nodes - they can't be sorted and/or unset/set like PHP array()s
$table_map = array();
// sort the various levels of db definition
// sort schema order
self::file_sort_children($doc, 'schema', 'name');
foreach ($doc->schema as $schema) {
self::file_sort_children($schema, 'table', 'name');
foreach ($schema->table as $table) {
// on each table, sort columns in it
self::file_sort_children($table, 'column', 'name');
// resort the column children so that primary keys are first
$pkey_cols = preg_split("/[\\,\\s]+/", $table['primaryKey'], -1, PREG_SPLIT_NO_EMPTY);
self::file_sort_prepend_child($table, 'column', 'name', $pkey_cols);
// if there are data rows, sort them by primary key
if (isset($table->rows)) {
// the_pkey_indexes list is a list of row->col's that are primary key cols, in primaryKey order
if (!isset($table['primaryKey'])) {
// if the primaryKey isn't defined, specify col 0 and 1 as the pkey
$pkey_indexes = array(0, 1);
} else {
$rows_cols = preg_split("/[\\,\\s]+/", $table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$pkey_indexes = array();
foreach ($pkey_cols as $pkey_col) {
$pkey_indexes[] = array_search($pkey_col, $rows_cols);
}
}
self::file_sort_children($table->rows, 'row', 'col', $pkey_indexes);
}
// make sure table definition order still follows the DTD:
// <!ELEMENT table (column+, index*, constraint*, grant*, rows?)>
// if column defs were not all first, they will be after this!
self::file_sort_reappend_child($table, 'index');
self::file_sort_reappend_child($table, 'constraint');
self::file_sort_reappend_child($table, 'grant');
self::file_sort_reappend_child($table, 'rows');
}
// reappend miscellany tags in the schema, so that they are after tables, and in order
// note that they are not file_sort_childeren()'d as they have special compositing rules
// that we don't get into in file_sort() and it's helpers
self::file_sort_reappend_child($schema, 'sequence', 'name');
self::file_sort_reappend_child($schema, 'function', 'name');
self::file_sort_reappend_child($schema, 'trigger', 'name');
self::file_sort_reappend_child($schema, 'grant', 'role');
}
return xml_parser::save_xml($sorted_file_name, $doc->saveXML());
}
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());
}