/**
* Creates SQL diff of two DBSteward XML definition documents
*
* @return array output files list
*/
public static function diff_xml($old_xml_file, $new_xml_file, $upgrade_prefix)
{
$old_database = simplexml_load_file($old_xml_file);
if ($old_database === false) {
throw new Exception("failed to simplexml_load_file() " . $old_xml_file);
}
$old_database = xml_parser::sql_format_convert($old_database);
$new_database = simplexml_load_file($new_xml_file);
if ($new_database === false) {
throw new Exception("failed to simplexml_load_file() " . $new_xml_file);
}
$new_database = xml_parser::sql_format_convert($new_database);
pgsql8::diff_doc($old_xml_file, $new_xml_file, $old_database, $new_database, $upgrade_prefix);
}
public static function xml_composite_children(&$base, &$overlay, $file_name, &$addendum = NULL)
{
// do overlay includes first, to allow definer to overwrite included file values at the same level
while (isset($overlay->includeFile)) {
foreach ($overlay->includeFile as $includeFile) {
$include_file_name = (string) $includeFile['name'];
// if include_file_name does not appear to be absolute, make it relative to its parent
if (substr($include_file_name, 0, 1) != '/') {
$include_file_name = dirname($file_name) . '/' . $include_file_name;
}
dbsteward::notice("Compositing XML includeFile " . $include_file_name);
$include_doc = simplexml_load_file($include_file_name);
if ($include_doc === FALSE) {
throw new Exception("failed to simplexml_load_file() includeFile " . $include_file_name);
}
$include_doc = xml_parser::expand_tabrow_data($include_doc);
$include_doc = xml_parser::sql_format_convert($include_doc);
self::xml_composite_children($base, $include_doc, $include_file_name);
}
unset($overlay->includeFile);
unset($base->includeFile);
}
// overlay elements found in the overlay node
foreach ($overlay->children() as $child) {
// always reset the base relative node to null to prevent loop carry-over
$node = NULL;
$tag_name = $child->getName();
if (strcasecmp('function', $tag_name) == 0) {
$nodes = $base->xpath($tag_name . "[@name='" . $child['name'] . "']");
// doesn't exist
if (count($nodes) == 0) {
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
$node->addAttribute('name', $child['name']);
} else {
$node = NULL;
// find the function that has the same parameters
for ($i = 0; $i < count($nodes); $i++) {
$base_node = $nodes[$i];
// match them in cardinal order
$base_parameters = $base_node->xpath("functionParameter");
$overlay_parameters = $child->xpath("functionParameter");
// only analyze further if they have the same number of parameters
if (count($base_parameters) != count($overlay_parameters)) {
continue;
}
for ($j = 0; $j < count($overlay_parameters); $j++) {
$base_param = $base_parameters[$j];
$overlay_param = $overlay_parameters[$j];
// is the parameter the same type, at the same index?
if (strcasecmp($base_param['type'], $overlay_param['type']) != 0) {
// no they aren't, this is not the function we are looking for
// continue 2 to go to next node in $nodes
continue 2;
}
}
// check to make sure there aren't duplicate sqlFormats
$f = function ($n) {
return strtolower($n['sqlFormat']);
};
$base_formats = array_map($f, $base_node->xpath("functionDefinition"));
$overlay_formats = array_map($f, $child->xpath("functionDefinition"));
// if there isn't a functionDefinition with the same sqlFormat
if (count(array_intersect($base_formats, $overlay_formats)) == 0) {
continue;
}
// made it through the whole parameter list without breaking out
// this is the function we have been looking for all the days of our lives
$node = $nodes[$i];
break;
}
// it was not found, create it
if ($node === NULL) {
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
$node->addAttribute('name', $child['name']);
} else {
if (!dbsteward::$allow_function_redefinition) {
throw new Exception("function " . $child['name'] . " with identical parameters is being redefined");
}
}
}
// the base function being replaced element's children will all be added unconditionally (see next else if)
unset($node->functionParameter);
// kill functionDefinition and grant tags as well, soas to keep the sane element order the DTD enforces
unset($node->functionDefinition, $node->grant);
} else {
if (strcasecmp('functionParameter', $tag_name) == 0 || strcasecmp('functionDefinition', $tag_name) == 0) {
$node = $base->addChild($tag_name, self::ampersand_magic($child));
} else {
if (strcasecmp('viewQuery', $tag_name) == 0) {
$nodes = $base->xpath($tag_name . "[@sqlFormat='" . $child['sqlFormat'] . "']");
// doesn't exist
if (count($nodes) == 0) {
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
// sqlFormat for viewQuery is optional
if ($child['sqlFormat']) {
$node->addAttribute('sqlFormat', $child['sqlFormat']);
}
} else {
$node = $nodes[0];
}
} else {
if (strcasecmp('trigger', $tag_name) == 0) {
$trigger_attributes = array('name', 'table');
$attributes_xpath = "";
foreach ($trigger_attributes as $trigger_attribute) {
$attributes_xpath .= "@" . $trigger_attribute . "='" . $child[$trigger_attribute] . "' and ";
}
$attributes_xpath = substr($attributes_xpath, 0, -5);
$xpath = "trigger[" . $attributes_xpath . "]";
$nodes = $base->xpath($xpath);
if (count($nodes) > 1) {
throw new exception("more than one match for " . $xpath);
}
// doesn't exist
if (count($nodes) == 0) {
dbsteward::debug("DEBUG: Add missing trigger: " . $child->asXML());
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
$node->addAttribute('name', $child['name']);
} else {
$node = $nodes[0];
}
} else {
if (strcasecmp('index', $tag_name) == 0) {
$xpath = "index[name='" . $child['name'] . "']";
$nodes = $base->xpath($xpath);
if (count($nodes) > 1) {
throw new exception("dupliate index name, more than one match for {$xpath}");
}
if (count($nodes) == 0) {
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
$node->addAttribute('name', $child['name']);
} else {
$node = $nodes[0];
}
} else {
if (isset($child['name'])) {
$xpath = $tag_name . "[@name='" . $child['name'] . "']";
$nodes = $base->xpath($xpath);
if (count($nodes) > 1) {
throw new exception("more than one match for " . $xpath);
}
// doesn't exist
if (count($nodes) == 0) {
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
$node->addAttribute('name', $child['name']);
} else {
$node = $nodes[0];
}
} else {
if (strcasecmp($tag_name, 'rows') == 0) {
self::data_rows_overlay($base, $child);
if ($addendum !== NULL) {
$rows = simplexml_load_string($child->asXML());
self::xml_join($addendum, $rows);
}
// continue the loop so the rows element isn't recursed into below
continue;
} else {
if (strcasecmp($tag_name, 'grant') == 0) {
$node = $base->addChild($tag_name, self::ampersand_magic($child));
} else {
if (strcasecmp($tag_name, 'sql') == 0) {
// just replace the " with "e; since we're embedding it, DOMDocument text nodes won't escape for
// this specific purpose
$nodes = $base->xpath('sql[. ="' . str_replace('"', '"e;', $child) . '"]');
if ($nodes === FALSE) {
throw new exception("xpath to lookup sql match for sql element inner text '" . $child . "' returned error");
}
if (count($nodes) == 0) {
$node = $base->addChild($tag_name, self::ampersand_magic($child));
} else {
$node = $nodes[0];
}
} else {
if (strcasecmp($tag_name, 'slonyNode') == 0 || strcasecmp($tag_name, 'slonyReplicaSet') == 0 || strcasecmp($tag_name, 'slonyReplicaSetNode') == 0) {
$xpath = $tag_name . "[@id='" . $child['id'] . "']";
$nodes = $base->xpath($xpath);
if (count($nodes) > 1) {
throw new exception("more than one match for " . $xpath);
}
// doesn't exist
if (count($nodes) == 0) {
$node = $base->addChild($tag_name);
$node->addAttribute('id', $child['id']);
} else {
$node = $nodes[0];
}
} else {
$nodes = $base->xpath($tag_name);
if (count($nodes) > 1) {
throw new exception("more than one match for " . $tag_name);
}
// doesn't exist
if (count($nodes) == 0) {
$node = $base->addChild($tag_name, dbsteward::string_cast($child));
} else {
$node = $nodes[0];
// we have determined that we are only matching on tag_name, so it's pretty safe to
// abuse -> accessor to change the value of the tag with the base->node accessor to transfer the value of child tag to node
// @NOTICE: we also assume this is a data definition tag with no children
if (strlen((string) $child) > 0 && count($child->children()) == 0) {
$base->{$tag_name} = (string) $child;
}
}
}
}
}
}
}
}
}
}
}
}
if (!is_object($node)) {
var_dump($child);
var_dump($node);
throw new exception("node is not an object, panic!");
}
// set attributes for the element, either found or created
foreach ($child->attributes() as $attribute => $value) {
if (!isset($node[$attribute])) {
$node->addAttribute($attribute, $value);
} else {
$node[$attribute] = $value;
}
}
// if we're collecting addendums, make sure we add any schemas and tables to the addendum doc
$node2 = NULL;
if ($addendum !== NULL) {
$tag = $node->getName();
if (strcasecmp($node->getName(), 'schema') == 0 || strcasecmp($node->getName(), 'table') == 0) {
$name = (string) $node['name'];
$nodes = $addendum->xpath("{$tag}[@name='{$name}']");
if (count($nodes) == 0) {
$node2 = $addendum->addChild($node->getName());
// for the addendum, we only need the name attribute
$node2->addAttribute('name', $node['name']);
} else {
if (count($nodes) == 1) {
$node2 = $nodes[0];
} else {
throw new exception("More than one {$tag} by name {$name}!? Panic!");
}
}
}
}
// recurse if child has children
if (count($child->children()) > 0) {
self::xml_composite_children($node, $child, $file_name, $node2);
}
}
// when compositing table definitions
// columns, constraints, grants, etc added after initial table definition will be out of order and therefore not DTD valid
// if the base was a table element, rebuild it's children in DTD-valid order
if (strcasecmp($base->getName(), 'table') == 0) {
static::file_sort_reappend_child($base, 'tablePartition');
static::file_sort_reappend_child($base, 'tableOption');
static::file_sort_reappend_child($base, 'column');
static::file_sort_reappend_child($base, 'index');
static::file_sort_reappend_child($base, 'constraint');
static::file_sort_reappend_child($base, 'grant');
static::file_sort_reappend_child($base, 'rows');
} else {
if (strcasecmp($base->getName(), 'database') == 0) {
static::file_sort_reappend_child($base, 'role');
static::file_sort_reappend_child($base, 'slony');
static::file_sort_reappend_child($base, 'configurationParameter');
} else {
if (strcasecmp($base->getName(), 'slony') == 0) {
static::file_sort_reappend_child($base, 'slony');
static::file_sort_reappend_child($base, 'slonyNode');
static::file_sort_reappend_child($base, 'slonyReplicaSet');
static::file_sort_reappend_child($base, 'slonyReplicaSetNode');
}
}
}
return TRUE;
}
public function xml_convert($files)
{
if (!is_array($files)) {
$files = array($files);
}
for ($i = 0; $i < count($files); $i++) {
$file_name = $files[$i];
$converted_file_name = $file_name . '.xmlconverted';
dbsteward::info("Upconverting XML definition file: " . $file_name);
dbsteward::info("Upconvert XML output file: " . $converted_file_name);
$doc = simplexml_load_file($file_name);
xml_parser::sql_format_convert($doc);
$converted_xml = xml_parser::format_xml($doc->asXML());
$converted_xml = str_replace('pgdbxml>', 'dbsteward>', $converted_xml);
file_put_contents($converted_file_name, $converted_xml);
}
}
public function testTypesAndDefaults()
{
$xml = <<<XML
<dbsteward>
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="id" owner="NOBODY">
<column name="integer" type="integer" null="false"/>
<column name="int" type="int" null="false"/>
<column name="decimal" type="decimal" null="false"/>
<column name="float" type="float" null="false"/>
<column name="double" type="double" null="false"/>
<column name="text" type="text" null="false"/>
<column name="varchar80" type="varchar(80)" null="false"/>
<column name="char" type="char" null="false"/>
<column name="boolean" type="boolean" null="false"/>
<column name="boolean" type="boolean" null="false" default="false"/>
<column name="boolean" type="boolean" null="false" default="true"/>
</table>
</schema>
</dbsteward>
XML;
$doc = new SimpleXMLElement($xml);
$schema = xml_parser::sql_format_convert($doc)->schema;
$def = function ($col, $add_default, $include_null_def) use(&$schema) {
return mysql5_column::get_full_definition($schema, $schema, $schema->table, $col, $add_default, $include_null_def);
};
for ($i = 0; $i <= 4; $i++) {
$col = $schema->table->column[$i];
$this->assertEquals("`{$col['name']}` {$col['type']} NOT NULL DEFAULT 0", $def($col, true, true));
}
for ($i = 5; $i <= 7; $i++) {
$col = $schema->table->column[$i];
$this->assertEquals("`{$col['name']}` {$col['type']} NOT NULL DEFAULT ''", $def($col, true, true));
}
$col = $schema->table->column[8];
$this->assertEquals("`{$col['name']}` tinyint(1) NOT NULL DEFAULT 0", $def($col, true, true));
$col = $schema->table->column[9];
$this->assertEquals("`{$col['name']}` tinyint(1) NOT NULL DEFAULT 0", $def($col, true, true));
$col = $schema->table->column[10];
$this->assertEquals("`{$col['name']}` tinyint(1) NOT NULL DEFAULT 1", $def($col, true, true));
}