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