public function testQuoteFKeyTable()
{
$xml = <<<XML
<dbsteward>
<schema name="test">
<table name="test1">
<column name="col1" type="int" />
</table>
<table name="test2">
<column name="col1" foreignSchema="test" foreignTable="test1" foreignColumn="col1"/>
</table>
</schema>
</dbsteward>
XML;
$db_doc = simplexml_load_string($xml);
$constraints = pgsql8_constraint::get_table_constraints($db_doc, $db_doc->schema, $db_doc->schema->table[1], 'foreignKey');
$sql = trim(pgsql8_table::get_constraint_sql_change_statement($constraints[0]));
$expected = 'ADD CONSTRAINT "test2_col1_fkey" FOREIGN KEY ("col1") REFERENCES "test"."test1" ("col1")';
$this->assertEquals($expected, $sql);
}
public function testFkeyToInheritedColumn()
{
$xml = <<<XML
<dbsteward>
<database>
<role>
<application>test</application>
<owner>deployment</owner>
<replication/>
<readonly/>
</role>
</database>
<schema name="test" owner="ROLE_OWNER">
<table name="parent" primaryKey="foo" owner="ROLE_OWNER">
<column name="foo" type="varchar(255)"/>
</table>
<table name="child" inheritsSchema="test" inheritsTable="parent" primaryKey="foo" owner="ROLE_OWNER">
<column name="bar" type="varchar(255)"/>
</table>
</schema>
<schema name="other" owner="ROLE_OWNER">
<table name="baz" primaryKey="footoo" owner="ROLE_OWNER">
<column name="footoo" foreignSchema="test" foreignTable="child" foreignColumn="foo"/>
</table>
</schema>
</dbsteward>
XML;
$doc = simplexml_load_string($xml);
$schema = $doc->schema[1];
$table = $schema->table;
$column = $table->column;
$fkey = pgsql8_constraint::foreign_key_lookup($doc, $schema, $table, $column);
$this->assertEquals($doc->schema[0], $fkey['schema']);
$this->assertEquals($doc->schema[0]->table[1], $fkey['table']);
$this->assertEquals($doc->schema[0]->table[0]->column, $fkey['column']);
}
/**
* Parses all rows in ALTER TABLE command.
*
* @param table table being parsed
* @param commands commands
*
* @throws ParserException Thrown if problem occured while parsing DDL.
*/
private static function parse_rows(&$db_doc, &$node_schema, &$node_table, $commands)
{
$line = $commands;
$subCommand = null;
while (strlen($line) > 0) {
$commandEnd = sql_parser::get_command_end($line, 0);
$subCommand = trim(substr($line, 0, $commandEnd));
$line = $commandEnd >= strlen($line) ? "" : substr($line, $commandEnd + 1);
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_CONSTRAINT_FOREIGN_KEY, $subCommand, $matches) > 0) {
$column_name = trim($matches[3]);
$constraint_name = trim($matches[1]);
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[2]));
$subCommand = "";
}
}
if (preg_match(self::PATTERN_ADD_CONSTRAINT, $subCommand, $matches) > 0) {
$constraint_name = trim($matches[1]);
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[2]));
$subCommand = "";
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_PRIMARY_KEY, $subCommand, $matches) > 0) {
$definition = trim($matches[1]);
$column_name = trim($matches[2]);
$constraint_name = $node_table['name'] . '_pkey';
dbx::set_attribute($node_table, 'primaryKey', $column_name);
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_FOREIGN_KEY, $subCommand, $matches) > 0) {
$column_name = trim($matches[2]);
$constraint_name = pgsql8::identifier_name($node_schema['name'], $node_table['name'], $column_name, '_fkey');
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[1]));
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_SET_DEFAULT, $subCommand, $matches) > 0) {
$column_name = trim($matches[1]);
$default_value = trim($matches[2]);
if ($node_table->contains_column($column_name)) {
$node_column =& dbx::get_column($node_table, $column_name);
dbx::set_attribute($node_column, 'default', $default_value);
} else {
throw new exception("Cannot find column '" . $column_name . " 'in table '" . $node_table['name'] . "'");
}
$subCommand = "";
}
}
if (preg_match(self::PATTERN_ALTER_COLUMN_STATISTICS, $subCommand, $matches) > 0) {
$column_name = trim($matches[2]);
$value = trim($matches[3]);
$node_column =& dbx::get_column($node_table, $column_name);
dbx::set_attribute($node_column, 'statistics', $value);
$subCommand = "";
}
if (preg_match(self::PATTERN_CLUSTER_ON, $subCommand, $matches) > 0) {
$indexName = trim($matches[1]);
dbx::set_attribute($node_column, 'clusterIndexName', $indexName);
$subCommand = "";
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_TRIGGER, $subCommand, $matches) > 0) {
$triggerName = trim($matches[2]);
throw new exception("@TODO: do something with ALTER TABLE ... ENABLE / DISABLE trigger statements");
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
throw new exception("Don't know how to parse: " . $subCommand);
}
}
}
/**
* Returns list of constraints that should be added.
*
* @param old_table original table
* @param new_table new table
* @param type whether primary keys should be processed or other constraints should be processed
*
* @return list of constraints that should be added
*/
private static function get_new_constraints($old_schema, $old_table, $new_schema, $new_table, $type)
{
$list = array();
if ($new_table != null) {
if ($old_table == null) {
foreach (pgsql8_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, $type) as $constraint) {
$list[] = $constraint;
}
} else {
foreach (pgsql8_constraint::get_table_constraints(dbsteward::$new_database, $new_schema, $new_table, $type) as $constraint) {
$old_constraint = pgsql8_constraint::get_table_constraint(dbsteward::$old_database, $old_schema, $old_table, $constraint['name']);
if (!pgsql8_table::contains_constraint(dbsteward::$old_database, $old_schema, $old_table, $constraint['name']) || !pgsql8_table::constraint_equals($old_constraint, $constraint) || pgsql8_table::constraint_depends_on_renamed_table(dbsteward::$new_database, $constraint)) {
$list[] = $constraint;
}
}
}
}
return $list;
}
