public function testDuplicateIndexNamesThrowException()
{
$xml = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table1" owner="ROLE_OWNER" primaryKey="col1">
<column name="col1" type="int"/>
<index name="index1">
<indexDimension name="index1_1">col1</indexDimension>
</index>
<index name="index1">
<indexDimension name="index1_1">col1</indexDimension>
</index>
</table>
</schema>
XML;
$schema = simplexml_load_string($xml);
$table = $schema->table;
try {
pgsql8_index::get_table_indexes($schema, $table);
} catch (Exception $ex) {
$this->assertContains('Duplicate index name', $ex->getMessage());
return;
}
$this->fail("Expected an exception because a table had duplicate index names");
}
/**
* @group pgsql8
*/
public function testPgsql8IndexNameLengthNoColumnLongTable()
{
$table = 'resource_device_type_locator_channel_type_list_id_holy_crap_dont_do_this';
$column = '';
$suffix = 'fkey';
$actual = pgsql8_index::index_name($table, $column, $suffix);
$this->assertEquals(pgsql8::MAX_IDENTIFIER_LENGTH, $l = strlen($actual), "Generated index name '{$actual}' has length {$l} but pgsql8 max is 63");
}
public function testNoQuoteIndexIdentifierDimensions()
{
$xml = <<<XML
<schema name="test">
<table name="test">
<index name="idx" using="btree" unique="false">
<indexDimension name="idx_1">col1</indexDimension>
<indexDimension name="idx_2" sql="true">x + 1</indexDimension>
<indexDimension name="idx_3">x + 1</indexDimension>
</index>
</table>
</schema>
XML;
$schema = simplexml_load_string($xml);
$sql = pgsql8_index::get_creation_sql($schema, $schema->table, $schema->table->index);
$this->assertEquals('CREATE INDEX "idx" ON "test"."test" USING btree ("col1", x + 1, "x + 1");', $sql);
}
public static function get_drop_check_sql($node_schema, $node_table, $column, $node_type)
{
if (!is_object($node_type)) {
var_dump($node_type);
throw new exception("node_type is not an object");
}
if (!isset($node_type->enum)) {
throw new exception("no enums defined for type " . $node_type['name']);
}
$enum_values = mssql10_type::get_enum_values($node_type);
$table_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']);
$column_name = mssql10::get_quoted_column_name($column['name']);
$constraint_name = pgsql8_index::index_name($node_table['name'], $column['name'], '_check_enum');
$enum_list = "'" . implode("','", $enum_values) . "'";
$ddl = "ALTER TABLE " . $table_name . "\n";
$ddl .= "\tDROP CONSTRAINT " . $constraint_name . ";\n";
return $ddl;
}
/**
* Parses all rows in CREATE TABLE command.
*
* @param $node_schema schema table belongs to
* @param $node_table table being parsed
* @param $command command without 'CREATE TABLE ... (' string
*/
private static function parse_rows(&$node_schema, &$node_table, $command)
{
$line = $command;
$post_columns = false;
while (strlen($line) > 0) {
$command_end = sql_parser::get_command_end($line, 0);
$subCommand = trim(substr($line, 0, $command_end));
if ($post_columns) {
$line = self::parse_post_columns($node_table, $subCommand);
break;
} else {
if (substr($line, $command_end, 1) == ')') {
$post_columns = true;
}
}
// look for modifier tokens and act accordingly
$tokens = preg_split("/[\\s]+/", $subCommand, -1, PREG_SPLIT_NO_EMPTY);
// start at 2, first is always name, second is always type
for ($i = 2; $i < count($tokens); $i++) {
if (strcasecmp($tokens[$i], 'UNIQUE') == 0) {
// CREATE TABLE test_table (
// test_table_id varchar(64) PRIMARY KEY,
// test_table_col_c varchar(100) UNIQUE NOT NULL
// );
// NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_table_pkey" for table "test_table"
// NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_table_test_table_col_c_key" for table "test_table"
dbsteward::debug("NOTICE: CREATE TABLE with UNIQUE column attribute -- creating implicit index \"" . pgsql8_index::index_name(sql_parser::get_object_name($node_table->get_name()), sql_parser::get_object_name($tokens[0]), 'key') . "\" for table \"" . $node_schema->get_name() . '.' . $node_table->get_name() . "\"");
$node_index =& dbx::create_table_index($node_table, pgsql8_index::index_name(sql_parser::get_object_name($node_table['name']), sql_parser::get_object_name($tokens[0]), 'key'));
dbx::set_attribute($node_index, 'unique', 'true');
dbx::set_attribute($node_index, 'using', 'btree');
$node_index->addChild('indexDimension', sql_parser::get_object_name($tokens[0]))->addAttribute('name', $tokens[0] . '_unq');
// make sure we don't process this token again
unset($tokens[$i]);
$tokens = array_merge($tokens);
$i--;
continue;
}
// @TODO: other cases?
// other cases is how you would fix pgsql8_column::parse_definition() throwing 'column definition parse fail' exceptions
}
$subCommand = implode(' ', $tokens);
self::parse_column_defs($node_schema, $node_table, $subCommand);
$line = $command_end >= strlen($line) ? "" : substr($line, $command_end + 1);
}
$line = trim($line);
if (strlen($line) > 0) {
throw new exception("Cannot parse CREATE TABLE '" . $node_table['name'] . "' - do not know how to parse '" . $line . "'");
}
}
public static function foreign_key($db_doc, $node_schema, $node_table, $column, &$foreign)
{
$fschema = strlen($column['foreignSchema']) == 0 ? (string) $node_schema['name'] : (string) $column['foreignSchema'];
$foreign['schema'] = dbx::get_schema($db_doc, $fschema);
if (!$foreign['schema']) {
throw new exception("Failed to find foreign schema '" . dbsteward::string_cast($column['foreignSchema']) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name']));
}
$foreign['table'] = dbx::get_table($foreign['schema'], $column['foreignTable']);
if (!$foreign['table']) {
throw new exception("Failed to find foreign table '" . $column['foreignTable'] . "' for " . $node_schema['name'] . "." . $node_table['name'] . "." . $column['name']);
}
// if foreignColumn is not set
// the column is assumed to be the same name as the referring column
if (isset($column['foreignColumn']) && strlen($column['foreignColumn'])) {
$foreignColumn = $column['foreignColumn'];
} else {
$foreignColumn = $column['name'];
}
$foreign['column'] = dbx::get_table_column($foreign['table'], $foreignColumn);
if (!$foreign['column']) {
var_dump($foreign['column']);
throw new exception("Failed to find foreign column '" . dbsteward::string_cast($foreignColumn) . "' for " . dbsteward::string_cast($node_schema['name']) . "." . dbsteward::string_cast($node_table['name']) . "." . dbsteward::string_cast($column['name']));
}
// column type is missing, and resolved foreign is also a foreign key?
// recurse and find the cascading foreign key
if (strlen($foreign['column']['type']) == 0 && isset($foreign['column']['foreignColumn'])) {
dbsteward::trace("Seeking nested foreign key for " . dbsteward::string_cast($foreign['schema']['name']) . "." . dbsteward::string_cast($foreign['table']['name']) . "." . $foreign['column']['name']);
$nested_fkey = array();
self::foreign_key($db_doc, $foreign['schema'], $foreign['table'], $foreign['column'], $nested_fkey);
//var_dump($nested_fkey['column']);
// make a separate clone of the column element because we are specifying the type only for foreign key type referencing
$foreign['column'] = new SimpleXMLElement($foreign['column']->asXML());
$foreign['column']['type'] = $nested_fkey['column']['type'];
}
$foreign['name'] = pgsql8_index::index_name($node_table['name'], $column['name'], 'fkey');
$table_name = format::get_fully_qualified_table_name($foreign['schema']['name'], $foreign['table']['name']);
$column_name = format::get_quoted_column_name($foreign['column']['name']);
$foreign['references'] = "{$table_name}({$column_name})";
}
/**
* Returns list of indexes that should be added.
*
* @param old_table original table
* @param new_table new table
*
* @return list of indexes that should be added
*/
public static function get_new_indexes($old_schema, $old_table, $new_schema, $new_table)
{
$list = array();
if ($new_table != null) {
if ($old_table == null) {
foreach (format_index::get_table_indexes($new_schema, $new_table) as $index) {
$list[] = $index;
}
} else {
foreach (format_index::get_table_indexes($new_schema, $new_table) as $index) {
$old_index = dbx::get_table_index($old_schema, $old_table, $index['name']);
if (!pgsql8_table::contains_index($old_schema, $old_table, $index['name'])) {
$list[] = $index;
} else {
if (!pgsql8_index::equals($old_index, $index)) {
$list[] = $index;
}
}
}
}
}
return $list;
}