/** * Parses CREATE TRIGGER command. * * @param database database * @param command CREATE TRIGGER command * * @throws ParserException Thrown if problem occured while parsing the * command. */ public static function parse($database, $command) { if (preg_match(self::PATTERN, trim($command), $matches) > 0) { $trigger_name = trim($matches[1]); $when = $matches[2]; $events = array(); if (strlen($matches[3]) > 0) { $events[] = $matches[3]; } if (strlen($matches[4]) > 0) { $events[] = $matches[4]; } if (strlen($matches[5]) > 0) { $events[] = $matches[5]; } $table_name = trim($matches[6]); $fireOn = $matches[7]; $procedure = $matches[8]; $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database)); $node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); if ($node_table == null) { throw new exception("Failed to find trigger table " . $trigger->get_table_name()); } $node_trigger =& dbx::get_table_trigger($node_schema, $node_table, $trigger_name, true); dbx::set_attribute($node_trigger, 'when', strcasecmp('BEFORE', $when) == 0 ? 'BEFORE' : 'AFTER'); dbx::set_attribute($node_trigger, 'forEach', strcasecmp('ROW', $when) == 0 ? 'ROW' : 'STATEMENT'); dbx::set_attribute($node_trigger, 'function', trim($procedure)); dbx::set_attribute($node_trigger, 'event', implode(', ', $events)); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Given a type name like `schema`.`enum_name`, find the type node in schema 'schema' with name 'enum_name' * @return SimpleXMLElement */ public static function get_type_node($db_doc, $node_schema, $name_ref) { if (preg_match('/(?:["`]?(\\w+)["`]?\\.)?["`]?(.+)["`]?/', $name_ref, $matches) > 0) { $schema_ref = $matches[1]; $type_name = $matches[2]; // if we found a schema name in the name reference, then attempt to override the given node_schema with the named one if (!$schema_ref) { if (!$node_schema) { throw new Exception("No schema node given and no schema name found in type name reference '{$name_ref}'"); } } else { $node_schema = dbx::get_schema($db_doc, $schema_ref); if (!$node_schema) { throw new Exception("Could not find schema '{$schema_ref}', given by type name reference '{$name_ref}'"); } } $node_type = dbx::get_type($node_schema, $type_name); if (!$node_type) { // we did not find the given type - this is not exceptional because we might just be testing to see if it exists return NULL; } // if we got this far, we found the referenced type node return $node_type; } throw new Exception("Unrecognizable type name reference: '{$name_ref}'"); }
/** * Creates new schemas (not the objects inside the schemas) * * @param object $ofs output file pointer * @return void */ protected static function create_new_schemas($ofs) { foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { if (dbx::get_schema(dbsteward::$old_database, $new_schema['name']) == null) { dbsteward::info("Create New Schema " . $new_schema['name']); pgsql8::set_context_replica_set_id($new_schema); $ofs->write(format_schema::get_creation_sql($new_schema)); } } }
/** * Parses ALTER SCHEMA command. * * @param database database * @param command ALTER SCHEMA command * */ public static function parse($database, $command) { if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) { $line = $command; $schema_name = trim($matches[1]); $owner_name = trim($matches[2]); $schema =& dbx::get_schema($database, $schema_name); if ($schema == null) { throw new exception("failed to find schema " . $schema_name . " for alter owner statement"); } dbx::set_attribute($schema, 'owner', $owner_name); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE SCHEMA command. * * @param database database * @param command CREATE SCHEMA command */ public static function parse($database, $command) { if (preg_match(self::PATTERN_CREATE_SCHEMA, $command, $matches) > 0) { $node_schema = dbx::get_schema($database, $matches[1], true); if (isset($matches[2])) { dbx::set_attribute($node_schema, 'authorization', $matches[2]); } } else { if (preg_match(self::PATTERN_CREATE_SCHEMA_AUTHORIZATION, $command, $matches) > 0) { $node_schema = dbx::get_schema($database, $matches[1], true); dbx::set_attribute($node_schema, 'authorization', $node_schema['name']); } else { throw new exception("Cannot parse command: " . $command); } } }
/** * Parses CREATE VIEW command. * * @param database database * @param command CREATE VIEW command */ public static function parse($database, $command) { if (preg_match(self::PATTERN, trim($command), $matches) > 0) { $view_name = $matches[1]; $column_names = $matches[2]; $query = $matches[3]; if (strlen($view_name) == 0 || strlen($query) == 0) { throw new exception("Cannot parse command: " . $command); } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($view_name, $database)); $node_view =& dbx::get_view($node_schema, sql_parser::get_object_name($view_name, $database), true); $node_view->addChild('viewQuery', $query); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE TABLE command. * * @param database database * @param command CREATE TABLE command * */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_TABLE_NAME, $line, $matches) > 0) { $table_name = trim($matches[1]); $line = preg_replace(self::PATTERN_TABLE_NAME, '', $line); } else { throw new exception("Cannot parse command: " . $line); } $table_name = sql_parser::get_schema_name($table_name, $database) . '.' . sql_parser::get_object_name($table_name); $schema_name = sql_parser::get_schema_name($table_name, $database); $node_schema = dbx::get_schema($database, $schema_name); if ($node_schema == null) { throw new exception("Cannot get schema '" . $schema_name . "'. Need to issue 'CREATE SCHEMA " . $schema_name . ";' before 'CREATE TABLE " . $table_name . "...;'?"); } $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name), true); self::parse_rows($node_schema, $node_table, sql_parser::remove_last_semicolon($line)); }
/** * Parses CREATE INDEX command. * * @param database database * @param command CREATE INDEX command */ public static function parse($database, $command) { if (preg_match(self::CREATE_PATTERN, trim($command), $matches) > 0) { $unique_value = strlen(trim($matches[1])) > 0 ? 'true' : 'false'; $index_name = $matches[2]; $table_name = $matches[3]; $using = trim($matches[4]); if ($index_name == null || $table_name == null || $using == null) { throw new exception("Cannot parse command: " . $command); } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name(trim($table_name), $database)); $node_table =& dbx::get_table($node_schema, sql_parser::get_object_name(trim($table_name))); if ($node_table == null) { throw new exception("Failed to find table: " . $table_name); } $node_index =& dbx::create_table_index($node_table, $index_name); dbx::set_attribute($node_index, 'using', $using); dbx::set_attribute($node_index, 'unique', $unique_value); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Iterates over all views in the given document, calling $callback for each one in dependency order * $callback takes ($node_schema, $node_view) */ public static function with_views_in_order($db_doc, $callback) { if ($db_doc != null) { $visited = array(); $dfs_from = function ($schema, $view) use($callback, &$dfs_from, $db_doc, &$visited) { $key = $schema['name'] . '.' . $view['name']; // echo "visiting $key\n"; if (array_key_exists($key, $visited)) { // echo " [visited]\n"; return; } // echo " remembering $key\n"; $visited[$key] = true; $deps = format_view::get_dependencies($schema, $view); foreach ($deps as $dep) { list($dep_schema_name, $dep_view_name) = $dep; // echo " depends on $dep_schema_name.$dep_view_name\n"; $dep_schema = dbx::get_schema($db_doc, $dep_schema_name); $dep_view = dbx::get_view($dep_schema, $dep_view_name); $dfs_from($dep_schema, $dep_view); } call_user_func($callback, $schema, $view); }; foreach (dbx::get_schemas($db_doc) as $root_schema) { $root_schema_name = (string) $root_schema['name']; foreach (dbx::get_views($root_schema) as $root_view) { $root_view_name = (string) $root_view['name']; $root_key = $root_schema_name . '.' . $root_view_name; // echo "starting at $root_key\n"; if (array_key_exists($root_key, $visited)) { // echo " [visited]\n"; continue; } $dfs_from($root_schema, $root_view); } } } }
/** * Parses ALTER FUNCTION command. * * @param database database * @param command ALTER FUNCTION command * */ public static function parse($database, $command) { if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) { $line = $command; $function_name = trim($matches[1]); // make all functionName's fully qualified // default_schema will make set search path induced schemas come through correctly $function_name = sql_parser::get_schema_name($function_name, $database) . '.' . sql_parser::get_object_name($function_name); $arguments = $matches[2]; $owner_name = trim($matches[3]); $node_schema = dbx::get_schema($database, sql_parser::get_schema_name($function_name, $database)); if ($node_schema == null) { throw new exception("Failed to find function schema for " . $function_name); } $node_function = dbx::get_function($node_schema, sql_parser::get_object_name($function_name)); if ($node_function == null) { throw new exception("Failed to find function " . $function_name . " in schema " . $node_schema['name']); } dbx::set_attribute($node_function, 'owner', $owner_name); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE SEQUENCE command. * * @param database database * @param command CREATE SEQUENCE command */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_SEQUENCE_NAME, $line, $matches) > 0) { $sequence_name = trim($matches[1]); $line = preg_replace(self::PATTERN_SEQUENCE_NAME, '', $line); } else { throw new exception("Cannot parse line: " . $line); } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($sequence_name, $database)); $node_sequence =& dbx::get_sequence($node_schema, sql_parser::get_object_name($sequence_name), true); $line = sql_parser::remove_last_semicolon($line); $line = self::processMaxValue($node_sequence, $line); $line = self::processMinValue($node_sequence, $line); $line = self::processCycle($node_sequence, $line); $line = self::processCache($node_sequence, $line); $line = self::processIncrement($node_sequence, $line); $line = self::processstart_with($node_sequence, $line); $line = trim($line); if (strlen($line) > 0) { throw new exception("Cannot parse commmand '" . $command . "', string '" . $line . "'"); } }
public static function parse($database, $command) { if (preg_match(self::PATTERN_INSERT_INTO, $command, $matches) > 0) { $line = $command; $table_name = $matches[1]; $columns_signature = md5($matches[2]); if (!isset(self::$columns_cache[$columns_signature])) { self::$columns_cache[$columns_signature] = $columns = self::column_split($matches[2], array('"')); } else { $columns = self::$columns_cache[$columns_signature]; } $data_row = self::column_split($matches[3], array("'")); if (count($columns) != count($data_row)) { throw new exception("column count " . count($columns) . " does not match data_row count " . count($data_row)); } // merge together as an alpha index row for ($i = 0; $i < count($columns); $i++) { $row[$columns[$i]] = $data_row[$i]; } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database)); if ($node_schema == null) { throw new exception("Failed to find schema for data append: " . sql_parser::get_schema_name($table_name, $database)); } $node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); if ($node_table == null) { throw new exception("Failed to find table for data append: " . $table_name); } try { pgsql8_table::add_data_row($node_table, $row); } catch (Exception $e) { var_dump($command); throw $e; } } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE FUNCTION and CREATE OR REPLACE FUNCTION command. * * @param database database * @param command CREATE FUNCTION command */ public static function parse($database, $command) { if (preg_match(self::PATTERN, trim($command), $matches) > 0) { $function_name = trim($matches[1]); // make all functionName's fully qualified // default_schema will make set search path induced schemas come through correctly $function_name = sql_parser::get_schema_name($function_name, $database) . '.' . sql_parser::get_object_name($function_name); $arguments = $matches[2]; $node_schema = dbx::get_schema($database, sql_parser::get_schema_name($function_name, $database)); if ($node_schema == null) { throw new exception("Failed to find function schema for " . $function_name); } $node_function = dbx::get_function($node_schema, sql_parser::get_object_name($function_name), null, true); //@TODO: this may be a problem when there is more than one prototype for a function $function_declaration = pgsql8_function::set_declaration($node_schema, $node_function, $arguments); // check remaining definition for function modifiers by chopping of function declaration $function_close_position = stripos($command, ')'); $function_modifiers = str_replace("\n", ' ', substr($command, $function_close_position + 1)); // kill extra whitespace by regex match $function_modifiers = preg_replace("/\\s+/", " ", $function_modifiers); // kill trailing semicolon $function_modifiers = trim($function_modifiers); if (substr($function_modifiers, -1) == ';') { $function_modifiers = trim(substr($function_modifiers, 0, -1)); } $function_modifiers = ' ' . $function_modifiers . ' '; // AS token (definition) token // AS $_$ BEGIN DO STUFF END $_$ if (($as_pos = stripos($function_modifiers, ' AS ')) !== false) { $end_as_token_pos = strpos($function_modifiers, ' ', $as_pos + 4); $as_token = substr($function_modifiers, $as_pos + 4, $end_as_token_pos - ($as_pos + 4)); $definition_start = strpos($function_modifiers, $as_token, $as_pos) + strlen($as_token); $definition_end = strpos($function_modifiers, $as_token, $definition_start + strlen($as_token)); $definition = substr($function_modifiers, $definition_start, $definition_end - $definition_start); $definition = trim($definition); pgsql8_function::set_definition($node_function, $definition); // cut out what we just found $function_modifiers = substr($function_modifiers, 0, $as_pos) . ' ' . substr($function_modifiers, $definition_end + strlen($as_token)); } // now that the AS <token> (definition) <token> section is gone, parsing is simpler: // RETURNS (type) if (preg_match(self::PATTERN_RETURNS, $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'returns', trim($matches[1])); } // LANGUAGE (languagename) if (preg_match(self::PATTERN_LANGUAGE, $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'language', trim($matches[1])); } // check for IMMUTABLE | STABLE | VOLATILE modifiers if (preg_match('/.*\\s+IMMUTABLE\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'cachePolicy', 'IMMUTABLE'); } if (preg_match('/.*\\s+STABLE\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'cachePolicy', 'STABLE'); } if (preg_match('/.*\\s+VOLATILE\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'cachePolicy', 'VOLATILE'); } // check for SECURITY DEFINER modifier if (preg_match('/.*\\s+SECURITY DEFINER\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'securityDefiner', 'true'); } } else { throw new exception("Cannot parse command: " . $command); } }
/** * If this table was renamed, get the old schema node for this table * as defined by oldSchemaName. oldSchemaName is optional in the DTD * so as to allow for table renaming when the RDBMS does not allow tables to change schema * * @param object $schema schema the table exists in * @param object $table table node * @return object old table node object */ public static function &get_old_table_schema($schema, $table) { if (!isset($table['oldSchemaName'])) { return $schema; } if (is_null(dbsteward::$old_database)) { $old_schema = NULL; } else { $old_schema = dbx::get_schema(dbsteward::$old_database, $table['oldSchemaName']); } return $old_schema; }
/** * Attepts to find a column on a foreign table. * Walks up table inheritance chains. * If the foreign column is itself a foreign key, resolves the type of that column before returning. */ private static function resolve_foreign_column($db_doc, $local_schema, $local_table, $local_colname, $foreign_schema, $foreign_table, $foreign_colname, $visited = array()) { // walk up the foreign table inheritance chain to find the foreign column definition $fschema = $foreign_schema; $ftable = $foreign_table; do { $foreign_column = dbx::get_table_column($ftable, $foreign_colname); if ($ftable['inheritsSchema']) { $fschema = dbx::get_schema($db_doc, (string) $ftable['inheritsSchema']); } if ($ftable['inheritsTable']) { $ftable = dbx::get_table($fschema, (string) $ftable['inheritsTable']); } else { $ftable = null; } } while (!$foreign_column && !!$fschema && !!$ftable); if (!$foreign_column) { // column wasn't found in any referenced tables throw new Exception("Local column {$local_schema['name']}.{$local_table['name']}.{$local_colname} references unknown column {$foreign_schema['name']}.{$foreign_table['name']}.{$foreign_colname}"); } // column type is missing, and resolved foreign is also a foreign key? // recurse and find the cascading foreign key if (empty($foreign_column['type']) && !empty($foreign_column['foreignTable'])) { // make sure we don't visit the same column twice $foreign_col = format::get_fully_qualified_column_name($foreign_schema['name'], $foreign_table['name'], $foreign_column['name']); if (in_array($foreign_col, $visited)) { $local = format::get_fully_qualified_column_name($local_schema['name'], $local_table['name'], $local_colname); throw new Exception("Foreign key cyclic dependency detected! Local column {$local} pointing to foreign column {$foreign_col}"); } $visited[] = $foreign_col; $nested_fkey = self::foreign_key_lookup($db_doc, $foreign_schema, $foreign_table, $foreign_column, $visited); // 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'] = (string) $nested_fkey['column']['type']; } return $foreign_column; }
/** * extract db schema from pg_catalog * based on http://www.postgresql.org/docs/8.3/static/catalogs.html documentation * * @return string pulled db schema from database, in dbsteward format */ public static function extract_schema($host, $port, $database, $user, $password) { // serials that are implicitly created as part of a table, no need to explicitly create these $table_serials = array(); dbsteward::notice("Connecting to pgsql8 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user); // if not supplied, ask for the password if ($password === FALSE) { // @TODO: mask the password somehow without requiring a PHP extension echo "Password: "******"host={$host} port={$port} dbname={$database} user={$user} password={$password}"); $doc = new SimpleXMLElement('<dbsteward></dbsteward>'); // set the document to contain the passed db host, name, etc to meet the DTD and for reference $node_database = $doc->addChild('database'); $node_database->addChild('sqlformat', 'pgsql8'); $node_role = $node_database->addChild('role'); $node_role->addChild('application', $user); $node_role->addChild('owner', $user); $node_role->addChild('replication', $user); $node_role->addChild('readonly', $user); // find all tables in the schema that aren't in the built-in schemas $sql = "SELECT t.schemaname, t.tablename, t.tableowner, t.tablespace,\n sd.description as schema_description, td.description as table_description,\n ( SELECT array_agg(cd.objsubid::text || ';' ||cd.description)\n FROM pg_catalog.pg_description cd\n WHERE cd.objoid = c.oid AND cd.classoid = c.tableoid AND cd.objsubid > 0 ) AS column_descriptions\n FROM pg_catalog.pg_tables t\n LEFT JOIN pg_catalog.pg_namespace n ON (n.nspname = t.schemaname)\n LEFT JOIN pg_catalog.pg_class c ON (c.relname = t.tablename AND c.relnamespace = n.oid)\n LEFT JOIN pg_catalog.pg_description td ON (td.objoid = c.oid AND td.classoid = c.tableoid AND td.objsubid = 0)\n LEFT JOIN pg_catalog.pg_description sd ON (sd.objoid = n.oid)\n WHERE schemaname NOT IN ('information_schema', 'pg_catalog')\n ORDER BY schemaname, tablename;"; $rs = pgsql8_db::query($sql); $sequence_cols = array(); while (($row = pg_fetch_assoc($rs)) !== FALSE) { dbsteward::info("Analyze table options " . $row['schemaname'] . "." . $row['tablename']); // schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers // create the schema if it is missing $nodes = $doc->xpath("schema[@name='" . $row['schemaname'] . "']"); if (count($nodes) == 0) { $node_schema = $doc->addChild('schema'); $node_schema['name'] = $row['schemaname']; $sql = "SELECT schema_owner FROM information_schema.schemata WHERE schema_name = '" . $row['schemaname'] . "'"; $schema_owner = pgsql8_db::query_str($sql); $node_schema['owner'] = self::translate_role_name($schema_owner); if ($row['schema_description']) { $node_schema['description'] = $row['schema_description']; } } else { $node_schema = $nodes[0]; } // create the table in the schema space $nodes = $node_schema->xpath("table[@name='" . $row['tablename'] . "']"); if (count($nodes) == 0) { $node_table = $node_schema->addChild('table'); $node_table['name'] = $row['tablename']; $node_table['owner'] = self::translate_role_name($row['tableowner']); $node_table['description'] = $row['table_description']; // extract tablespace as a tableOption if (!empty($row['tablespace'])) { $node_option = $node_table->addChild('tableOption'); $node_option->addAttribute('sqlFormat', 'pgsql8'); $node_option->addAttribute('name', 'tablespace'); $node_option->addAttribute('value', $row['tablespace']); } // extract storage parameters as a tableOption $sql = "SELECT reloptions, relhasoids\n FROM pg_catalog.pg_class\n WHERE relname = '" . $node_table['name'] . "' AND relnamespace = (\n SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = '" . $node_schema['name'] . "')"; $params_rs = pgsql8_db::query($sql); $params_row = pg_fetch_assoc($params_rs); $params = array(); if (!empty($params_row['reloptions'])) { // reloptions is formatted as {name=value,name=value} $params = explode(',', substr($params_row['reloptions'], 1, -1)); } $params[] = "oids=" . (strcasecmp('t', $params_row['relhasoids']) === 0 ? 'true' : 'false'); $node_option = $node_table->addChild('tableOption'); $node_option->addAttribute('sqlFormat', 'pgsql8'); $node_option->addAttribute('name', 'with'); $node_option->addAttribute('value', '(' . implode(',', $params) . ')'); dbsteward::info("Analyze table columns " . $row['schemaname'] . "." . $row['tablename']); $column_descriptions_raw = self::parse_sql_array($row['column_descriptions']); $column_descriptions = array(); foreach ($column_descriptions_raw as $desc) { list($idx, $description) = explode(';', $desc, 2); $column_descriptions[$idx] = $description; } //hasindexes | hasrules | hastriggers handled later // get columns for the table $sql = "SELECT\n column_name, data_type,\n column_default, is_nullable,\n ordinal_position, numeric_precision,\n format_type(atttypid, atttypmod) as attribute_data_type\n FROM information_schema.columns\n JOIN pg_class pgc ON (pgc.relname = table_name AND pgc.relkind='r')\n JOIN pg_namespace nsp ON (nsp.nspname = table_schema AND nsp.oid = pgc.relnamespace)\n JOIN pg_attribute pga ON (pga.attrelid = pgc.oid AND columns.column_name = pga.attname)\n WHERE table_schema='" . $node_schema['name'] . "' AND table_name='" . $node_table['name'] . "'\n AND attnum > 0\n AND NOT attisdropped"; $col_rs = pgsql8_db::query($sql); while (($col_row = pg_fetch_assoc($col_rs)) !== FALSE) { $node_column = $node_table->addChild('column'); $node_column->addAttribute('name', $col_row['column_name']); if (array_key_exists($col_row['ordinal_position'], $column_descriptions)) { $node_column['description'] = $column_descriptions[$col_row['ordinal_position']]; } // look for serial columns that are primary keys and collapse them down from integers with sequence defualts into serials // type int or bigint // is_nullable = NO // column_default starts with nextval and contains iq_seq if ((strcasecmp('integer', $col_row['attribute_data_type']) == 0 || strcasecmp('bigint', $col_row['attribute_data_type']) == 0) && strcasecmp($col_row['is_nullable'], 'NO') == 0 && (stripos($col_row['column_default'], 'nextval') === 0 && stripos($col_row['column_default'], '_seq') !== FALSE)) { $col_type = 'serial'; if (strcasecmp('bigint', $col_row['attribute_data_type']) == 0) { $col_type = 'bigserial'; } $node_column->addAttribute('type', $col_type); // store sequences that will be implicitly genreated during table create // could use pgsql8::identifier_name and fully qualify the table but it will just truncate "for us" anyhow, so manually prepend schema $identifier_name = $node_schema['name'] . '.' . pgsql8::identifier_name($node_schema['name'], $node_table['name'], $col_row['column_name'], '_seq'); $table_serials[] = $identifier_name; $seq_name = explode("'", $col_row['column_default']); $sequence_cols[] = $seq_name[1]; } else { $col_type = $col_row['attribute_data_type']; $node_column->addAttribute('type', $col_type); if (strcasecmp($col_row['is_nullable'], 'NO') == 0) { $node_column->addAttribute('null', 'false'); } if (strlen($col_row['column_default']) > 0) { $node_column->addAttribute('default', $col_row['column_default']); } } } dbsteward::info("Analyze table indexes " . $row['schemaname'] . "." . $row['tablename']); // get table INDEXs $sql = "SELECT ic.relname, i.indisunique, (\n -- get the n'th dimension's definition\n SELECT array_agg(pg_catalog.pg_get_indexdef(i.indexrelid, n, true))\n FROM generate_series(1, i.indnatts) AS n\n ) AS dimensions\n FROM pg_index i\n LEFT JOIN pg_class ic ON ic.oid = i.indexrelid\n LEFT JOIN pg_class tc ON tc.oid = i.indrelid\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = tc.relnamespace\n WHERE tc.relname = '{$node_table['name']}'\n AND n.nspname = '{$node_schema['name']}'\n AND i.indisprimary != 't'\n AND ic.relname NOT IN (\n SELECT constraint_name\n FROM information_schema.table_constraints\n WHERE table_schema = '{$node_schema['name']}'\n AND table_name = '{$node_table['name']}');"; $index_rs = pgsql8_db::query($sql); while (($index_row = pg_fetch_assoc($index_rs)) !== FALSE) { $dimensions = self::parse_sql_array($index_row['dimensions']); // only add a unique index if the column was $index_name = $index_row['relname']; $node_index = $node_table->addChild('index'); $node_index->addAttribute('name', $index_name); $node_index->addAttribute('using', 'btree'); $node_index->addAttribute('unique', $index_row['indisunique'] == 't' ? 'true' : 'false'); $dim_i = 1; foreach ($dimensions as $dim) { $node_index->addChild('indexDimension', $dim)->addAttribute('name', $index_name . '_' . $dim_i++); } } } else { // complain if it is found, it should have been throw new exception("table " . $row['schemaname'] . '.' . $row['tablename'] . " already defined in XML object -- unexpected"); } } $schemas =& dbx::get_schemas($doc); foreach ($sequence_cols as $idx => $seq_col) { $seq_col = "'" . $seq_col . "'"; $sequence_cols[$idx] = $seq_col; } $sequence_str = implode(',', $sequence_cols); foreach ($schemas as $schema) { dbsteward::info("Analyze isolated sequences in schema " . $schema['name']); // filter by sequences we've defined as part of a table already // and get the owner of each sequence $seq_list_sql = "\n SELECT s.relname, r.rolname\n FROM pg_statio_all_sequences s\n JOIN pg_class c ON (s.relname = c.relname)\n JOIN pg_roles r ON (c.relowner = r.oid)\n WHERE schemaname = '" . $schema['name'] . "'"; //. " AND s.relname NOT IN (" . $sequence_str. ");"; if (strlen($sequence_str) > 0) { $seq_list_sql .= " AND s.relname NOT IN (" . $sequence_str . ")"; } $seq_list_sql .= " GROUP BY s.relname, r.rolname;"; $seq_list_rs = pgsql8_db::query($seq_list_sql); while (($seq_list_row = pg_fetch_assoc($seq_list_rs)) !== FALSE) { $seq_sql = "SELECT cache_value, start_value, min_value, max_value,\n increment_by, is_cycled FROM \"" . $schema['name'] . "\"." . $seq_list_row['relname'] . ";"; $seq_rs = pgsql8_db::query($seq_sql); while (($seq_row = pg_fetch_assoc($seq_rs)) !== FALSE) { $nodes = $schema->xpath("sequence[@name='" . $seq_list_row['relname'] . "']"); if (count($nodes) == 0) { // is sequence being implictly generated? If so skip it if (in_array($schema['name'] . '.' . $seq_list_row['relname'], $table_serials)) { continue; } $node_sequence = $schema->addChild('sequence'); $node_sequence->addAttribute('name', $seq_list_row['relname']); $node_sequence->addAttribute('owner', $seq_list_row['rolname']); $node_sequence->addAttribute('cache', $seq_row['cache_value']); $node_sequence->addAttribute('start', $seq_row['start_value']); $node_sequence->addAttribute('min', $seq_row['min_value']); $node_sequence->addAttribute('max', $seq_row['max_value']); $node_sequence->addAttribute('inc', $seq_row['increment_by']); $node_sequence->addAttribute('cycle', $seq_row['is_cycled'] === 't' ? 'true' : 'false'); } } } } // extract views $sql = "SELECT *\n FROM pg_catalog.pg_views\n WHERE schemaname NOT IN ('information_schema', 'pg_catalog')\n ORDER BY schemaname, viewname;"; $rc_views = pgsql8_db::query($sql); while (($view_row = pg_fetch_assoc($rc_views)) !== FALSE) { dbsteward::info("Analyze view " . $view_row['schemaname'] . "." . $view_row['viewname']); // create the schema if it is missing $nodes = $doc->xpath("schema[@name='" . $view_row['schemaname'] . "']"); if (count($nodes) == 0) { $node_schema = $doc->addChild('schema'); $node_schema->addAttribute('name', $view_row['schemaname']); $sql = "SELECT schema_owner FROM information_schema.schemata WHERE schema_name = '" . $view_row['schemaname'] . "'"; $schema_owner = pgsql8_db::query_str($sql); $node_schema->addAttribute('owner', self::translate_role_name($schema_owner)); } else { $node_schema = $nodes[0]; } $nodes = $node_schema->xpath("view[@name='" . $view_row['viewname'] . "']"); if (count($nodes) !== 0) { throw new exception("view " . $view_row['schemaname'] . "." . $view_row['viewname'] . " already defined in XML object -- unexpected"); } $node_view = $node_schema->addChild('view'); $node_view->addAttribute('name', $view_row['viewname']); $node_view->addAttribute('owner', self::translate_role_name($view_row['viewowner'])); $node_query = $node_view->addChild('viewQuery', $view_row['definition']); $node_query->addAttribute('sqlFormat', 'pgsql8'); } // for all schemas, all tables - get table constraints that are not type 'FOREIGN KEY' dbsteward::info("Analyze table constraints " . $row['schemaname'] . "." . $row['tablename']); $sql = "SELECT constraint_name, constraint_type, table_schema, table_name, array_agg(columns) AS columns\n FROM (\n SELECT tc.constraint_name, tc.constraint_type, tc.table_schema, tc.table_name, kcu.column_name::text AS columns\n FROM information_schema.table_constraints tc\n LEFT JOIN information_schema.key_column_usage kcu ON tc.constraint_catalog = kcu.constraint_catalog AND tc.constraint_schema = kcu.constraint_schema AND tc.constraint_name = kcu.constraint_name\n WHERE tc.table_schema NOT IN ('information_schema', 'pg_catalog')\n AND tc.constraint_type != 'FOREIGN KEY'\n GROUP BY tc.constraint_name, tc.constraint_type, tc.table_schema, tc.table_name, kcu.column_name\n ORDER BY kcu.column_name, tc.table_schema, tc.table_name) AS results\n GROUP BY results.constraint_name, results.constraint_type, results.table_schema, results.table_name;"; $rc_constraint = pgsql8_db::query($sql); while (($constraint_row = pg_fetch_assoc($rc_constraint)) !== FALSE) { $nodes = $doc->xpath("schema[@name='" . $constraint_row['table_schema'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find constraint analysis schema '" . $constraint_row['table_schema'] . "'"); } else { $node_schema = $nodes[0]; } $nodes = $node_schema->xpath("table[@name='" . $constraint_row['table_name'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find constraint analysis table " . $constraint_row['table_schema'] . " table '" . $constraint_row['table_name'] . "'"); } else { $node_table = $nodes[0]; } $column_names = self::parse_sql_array($constraint_row['columns']); if (strcasecmp('PRIMARY KEY', $constraint_row['constraint_type']) == 0) { $node_table['primaryKey'] = implode(', ', $column_names); $node_table['primaryKeyName'] = $constraint_row['constraint_name']; } else { if (strcasecmp('UNIQUE', $constraint_row['constraint_type']) == 0) { $node_constraint = $node_table->addChild('constraint'); $node_constraint['name'] = $constraint_row['constraint_name']; $node_constraint['type'] = 'UNIQUE'; $node_constraint['definition'] = '("' . implode('", "', $column_names) . '")'; } else { if (strcasecmp('CHECK', $constraint_row['constraint_type']) == 0) { // @TODO: implement CHECK constraints } else { throw new exception("unknown constraint_type " . $constraint_row['constraint_type']); } } } } // We cannot accurately retrieve FOREIGN KEYs via information_schema // We must rely on getting them from pg_catalog instead // See http://stackoverflow.com/questions/1152260/postgres-sql-to-list-table-foreign-keys $sql = "SELECT con.constraint_name, con.update_rule, con.delete_rule,\n lns.nspname AS local_schema, lt_cl.relname AS local_table, array_to_string(array_agg(lc_att.attname), ' ') AS local_columns,\n fns.nspname AS foreign_schema, ft_cl.relname AS foreign_table, array_to_string(array_agg(fc_att.attname), ' ') AS foreign_columns\n FROM\n -- get column mappings\n (SELECT local_constraint.conrelid AS local_table, unnest(local_constraint.conkey) AS local_col,\n local_constraint.confrelid AS foreign_table, unnest(local_constraint.confkey) AS foreign_col,\n local_constraint.conname AS constraint_name, local_constraint.confupdtype AS update_rule, local_constraint.confdeltype as delete_rule\n FROM pg_class cl\n INNER JOIN pg_namespace ns ON cl.relnamespace = ns.oid\n INNER JOIN pg_constraint local_constraint ON local_constraint.conrelid = cl.oid\n WHERE ns.nspname NOT IN ('pg_catalog','information_schema')\n AND local_constraint.contype = 'f'\n ) con\n INNER JOIN pg_class lt_cl ON lt_cl.oid = con.local_table\n INNER JOIN pg_namespace lns ON lns.oid = lt_cl.relnamespace\n INNER JOIN pg_attribute lc_att ON lc_att.attrelid = con.local_table AND lc_att.attnum = con.local_col\n INNER JOIN pg_class ft_cl ON ft_cl.oid = con.foreign_table\n INNER JOIN pg_namespace fns ON fns.oid = ft_cl.relnamespace\n INNER JOIN pg_attribute fc_att ON fc_att.attrelid = con.foreign_table AND fc_att.attnum = con.foreign_col\n GROUP BY con.constraint_name, lns.nspname, lt_cl.relname, fns.nspname, ft_cl.relname, con.update_rule, con.delete_rule;"; $rc_fk = pgsql8_db::query($sql); $rules = array('a' => 'NO_ACTION', 'r' => 'RESTRICT', 'c' => 'CASCADE', 'n' => 'SET_NULL', 'd' => 'SET_DEFAULT'); while (($fk_row = pg_fetch_assoc($rc_fk)) !== FALSE) { $local_cols = explode(' ', $fk_row['local_columns']); $foreign_cols = explode(' ', $fk_row['foreign_columns']); if (count($local_cols) != count($foreign_cols)) { throw new Exception(sprintf("Unexpected: Foreign key columns (%s) on %s.%s are mismatched with columns (%s) on %s.%s", implode(', ', $local_cols), $fk_row['local_schema'], $fk_row['local_table'], implode(', ', $foreign_cols), $fk_row['foreign_schema'], $fk_row['foreign_table'])); } $nodes = $doc->xpath("schema[@name='" . $fk_row['local_schema'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find constraint analysis schema '" . $fk_row['local_schema'] . "'"); } else { $node_schema = $nodes[0]; } $nodes = $node_schema->xpath("table[@name='" . $fk_row['local_table'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find constraint analysis table " . $fk_row['local_schema'] . " table '" . $fk_row['local_table'] . "'"); } else { $node_table = $nodes[0]; } if (count($local_cols) === 1) { // inline on column $nodes = $node_table->xpath("column[@name='" . $local_cols[0] . "']"); if (strlen($local_cols[0]) > 0) { if (count($nodes) != 1) { throw new exception("failed to find constraint analysis column " . $fk_row['local_schema'] . " table '" . $fk_row['local_table'] . "' column '" . $local_cols[0]); } else { $node_column = $nodes[0]; } } $node_column['foreignSchema'] = $fk_row['foreign_schema']; $node_column['foreignTable'] = $fk_row['foreign_table']; $node_column['foreignColumn'] = $foreign_cols[0]; $node_column['foreignKeyName'] = $fk_row['constraint_name']; $node_column['foreignOnUpdate'] = $rules[$fk_row['update_rule']]; $node_column['foreignOnDelete'] = $rules[$fk_row['delete_rule']]; // dbsteward fkey columns aren't supposed to specify a type, they will determine it from the foreign reference unset($node_column['type']); } elseif (count($local_cols) > 1) { $node_fkey = $node_table->addChild('foreignKey'); $node_fkey['columns'] = implode(', ', $local_cols); $node_fkey['foreignSchema'] = $fk_row['foreign_schema']; $node_fkey['foreignTable'] = $fk_row['foreign_table']; $node_fkey['foreignColumns'] = implode(', ', $foreign_cols); $node_fkey['constraintName'] = $fk_row['constraint_name']; $node_fkey['onUpdate'] = $rules[$fk_row['update_rule']]; $node_fkey['onDelete'] = $rules[$fk_row['delete_rule']]; } } // get function info for all functions // this is based on psql 8.4's \df+ query // that are not language c // that are not triggers $sql = "SELECT p.oid, n.nspname as schema, p.proname as name,\n pg_catalog.pg_get_function_result(p.oid) as return_type,\n CASE\n WHEN p.proisagg THEN 'agg'\n WHEN p.proiswindow THEN 'window'\n WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'\n ELSE 'normal'\n END as type,\n CASE\n WHEN p.provolatile = 'i' THEN 'IMMUTABLE'\n WHEN p.provolatile = 's' THEN 'STABLE'\n WHEN p.provolatile = 'v' THEN 'VOLATILE'\n END as volatility,\n pg_catalog.pg_get_userbyid(p.proowner) as owner,\n l.lanname as language,\n p.prosrc as source,\n pg_catalog.obj_description(p.oid, 'pg_proc') as description\nFROM pg_catalog.pg_proc p\nLEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\nLEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang\nWHERE n.nspname NOT IN ('pg_catalog', 'information_schema')\n AND l.lanname NOT IN ( 'c' )\n AND pg_catalog.pg_get_function_result(p.oid) NOT IN ( 'trigger' );"; $rs_functions = pgsql8_db::query($sql); while (($row_fxn = pg_fetch_assoc($rs_functions)) !== FALSE) { dbsteward::info("Analyze function " . $row_fxn['schema'] . "." . $row_fxn['name']); $node_schema = dbx::get_schema($doc, $row_fxn['schema'], TRUE); if (!isset($node_schema['owner'])) { $sql = "SELECT schema_owner FROM information_schema.schemata WHERE schema_name = '" . $row_fxn['schema'] . "'"; $schema_owner = pgsql8_db::query_str($sql); $node_schema->addAttribute('owner', self::translate_role_name($schema_owner)); } if (!$node_schema) { throw new exception("failed to find function schema " . $row_fxn['schema']); } $node_function = $node_schema->addChild('function'); $node_function['name'] = $row_fxn['name']; // unnest the proargtypes (which are in ordinal order) and get the correct format for them. // information_schema.parameters does not contain enough information to get correct type (e.g. ARRAY) // Note: * proargnames can be empty (not null) if there are no parameters names // * proargnames will contain empty strings for unnamed parameters if there are other named // parameters, e.g. {"", parameter_name} // * proargtypes is an oidvector, enjoy the hackery to deal with NULL proargnames // * proallargtypes is NULL when all arguments are IN. $sql = "SELECT UNNEST(COALESCE(proargnames, ARRAY_FILL(''::text, ARRAY[(SELECT COUNT(*) FROM UNNEST(COALESCE(proallargtypes, proargtypes)))]::int[]))) as parameter_name,\n FORMAT_TYPE(UNNEST(COALESCE(proallargtypes, proargtypes)), NULL) AS data_type\n FROM pg_proc pr\n WHERE oid = {$row_fxn['oid']}"; $rs_args = pgsql8_db::query($sql); while (($row_arg = pg_fetch_assoc($rs_args)) !== FALSE) { $node_param = $node_function->addChild('functionParameter'); if (!empty($row_arg['parameter_name'])) { $node_param['name'] = $row_arg['parameter_name']; } $node_param['type'] = $row_arg['data_type']; } $node_function['returns'] = $row_fxn['return_type']; $node_function['cachePolicy'] = $row_fxn['volatility']; $node_function['owner'] = self::translate_role_name($row_fxn['owner']); // @TODO: how is / figure out how to express securityDefiner attribute in the functions query $node_function['description'] = $row_fxn['description']; $node_definition = $node_function->addChild('functionDefinition', xml_parser::ampersand_magic($row_fxn['source'])); $node_definition['language'] = $row_fxn['language']; $node_definition['sqlFormat'] = 'pgsql8'; } // specify any user triggers we can find in the information_schema.triggers view $sql = "SELECT *\n FROM information_schema.triggers\n WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema');"; $rc_trigger = pgsql8_db::query($sql); while (($row_trigger = pg_fetch_assoc($rc_trigger)) !== FALSE) { dbsteward::info("Analyze trigger " . $row_trigger['event_object_schema'] . "." . $row_trigger['trigger_name']); $nodes = $doc->xpath("schema[@name='" . $row_trigger['event_object_schema'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find trigger schema '" . $row_trigger['event_object_schema'] . "'"); } else { $node_schema = $nodes[0]; } $nodes = $node_schema->xpath("table[@name='" . $row_trigger['event_object_table'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find trigger schema " . $row_trigger['event_object_schema'] . " table '" . $row_trigger['event_object_table'] . "'"); } else { $node_table = $nodes[0]; } // there is a row for each event_manipulation, so we need to aggregate them, see if the trigger already exists $nodes = $node_schema->xpath("trigger[@name='{$row_trigger['trigger_name']}' and @table='{$row_trigger['event_object_table']}']"); if (count($nodes) == 0) { $node_trigger = $node_schema->addChild('trigger'); $node_trigger->addAttribute('name', dbsteward::string_cast($row_trigger['trigger_name'])); $node_trigger['event'] = dbsteward::string_cast($row_trigger['event_manipulation']); $node_trigger['sqlFormat'] = 'pgsql8'; } else { $node_trigger = $nodes[0]; // add to the event if the trigger already exists $node_trigger['event'] .= ', ' . dbsteward::string_cast($row_trigger['event_manipulation']); } if (isset($row_trigger['condition_timing'])) { $when = $row_trigger['condition_timing']; } else { $when = $row_trigger['action_timing']; } $node_trigger['when'] = dbsteward::string_cast($when); $node_trigger['table'] = dbsteward::string_cast($row_trigger['event_object_table']); $node_trigger['forEach'] = dbsteward::string_cast($row_trigger['action_orientation']); $trigger_function = trim(str_ireplace('EXECUTE PROCEDURE', '', $row_trigger['action_statement'])); $node_trigger['function'] = dbsteward::string_cast($trigger_function); } // find table grants and save them in the xml document dbsteward::info("Analyze table permissions "); $sql = "SELECT *\n FROM information_schema.table_privileges\n WHERE table_schema NOT IN ('pg_catalog', 'information_schema');"; $rc_grant = pgsql8_db::query($sql); while (($row_grant = pg_fetch_assoc($rc_grant)) !== FALSE) { $nodes = $doc->xpath("schema[@name='" . $row_grant['table_schema'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find grant schema '" . $row_grant['table_schema'] . "'"); } else { $node_schema = $nodes[0]; } $nodes = $node_schema->xpath("(table|view)[@name='" . $row_grant['table_name'] . "']"); if (count($nodes) != 1) { throw new exception("failed to find grant schema " . $row_grant['table_schema'] . " table '" . $row_grant['table_name'] . "'"); } else { $node_table = $nodes[0]; } // aggregate privileges by role $nodes = $node_table->xpath("grant[@role='" . self::translate_role_name(dbsteward::string_cast($row_grant['grantee'])) . "']"); if (count($nodes) == 0) { $node_grant = $node_table->addChild('grant'); $node_grant->addAttribute('role', self::translate_role_name(dbsteward::string_cast($row_grant['grantee']))); $node_grant->addAttribute('operation', dbsteward::string_cast($row_grant['privilege_type'])); } else { $node_grant = $nodes[0]; // add to the when if the trigger already exists $node_grant['operation'] .= ', ' . dbsteward::string_cast($row_grant['privilege_type']); } if (strcasecmp('YES', dbsteward::string_cast($row_grant['is_grantable'])) == 0) { if (!isset($node_grant['with'])) { $node_grant->addAttribute('with', 'GRANT'); } $node_grant['with'] = 'GRANT'; } } // analyze sequence grants and assign those to the xml document as well dbsteward::info("Analyze isolated sequence permissions "); foreach ($schemas as $schema) { $sequences =& dbx::get_sequences($schema); foreach ($sequences as $sequence) { $seq_name = $sequence['name']; $grant_sql = "SELECT relacl FROM pg_class WHERE relname = '" . $seq_name . "';"; $grant_rc = pgsql8_db::query($grant_sql); while (($grant_row = pg_fetch_assoc($grant_rc)) !== FALSE) { // privileges for unassociated sequences are not listed in // information_schema.sequences; i think this is probably the most // accurate way to get sequence-level grants if ($grant_row['relacl'] === NULL) { continue; } $grant_perm = self::parse_sequence_relacl($grant_row['relacl']); foreach ($grant_perm as $user => $perms) { foreach ($perms as $perm) { $nodes = $sequence->xpath("grant[@role='" . self::translate_role_name($user) . "']"); if (count($nodes) == 0) { $node_grant = $sequence->addChild('grant'); $node_grant->addAttribute('role', self::translate_role_name($user)); $node_grant->addAttribute('operation', $perm); } else { $node_grant = $nodes[0]; // add to the when if the trigger already exists $node_grant['operation'] .= ', ' . $perm; } } } } } } pgsql8_db::disconnect(); // scan all now defined tables $schemas =& dbx::get_schemas($doc); foreach ($schemas as $schema) { $tables =& dbx::get_tables($schema); foreach ($tables as $table) { // if table does not have a primary key defined // add a placeholder for DTD validity if (!isset($table['primaryKey'])) { $table->addAttribute('primaryKey', 'dbsteward_primary_key_not_found'); $table_notice_desc = 'DBSTEWARD_EXTRACTION_WARNING: primary key definition not found for ' . $table['name'] . ' - placeholder has been specified for DTD validity'; dbsteward::warning("WARNING: " . $table_notice_desc); if (!isset($table['description'])) { $table['description'] = $table_notice_desc; } else { $table['description'] .= '; ' . $table_notice_desc; } } // check owner and grant role definitions if (!self::is_custom_role_defined($doc, $table['owner'])) { self::add_custom_role($doc, $table['owner']); } if (isset($table->grant)) { foreach ($table->grant as $grant) { if (!self::is_custom_role_defined($doc, $grant['role'])) { self::add_custom_role($doc, $grant['role']); } } } } } xml_parser::validate_xml($doc->asXML()); return xml_parser::format_xml($doc->saveXML()); }
private function diff($old, $new, $expected1, $expected3, $message = '') { dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $old . '</dbsteward>'); dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $new . '</dbsteward>'); $ofs1 = new mock_output_file_segmenter(); $ofs3 = new mock_output_file_segmenter(); // same structure as mysql5_diff::update_structure foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', TRUE); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', TRUE); mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema); mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema); // mysql5_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', FALSE); } foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE); } $actual1 = trim($ofs1->_get_output()); $actual3 = trim($ofs3->_get_output()); $this->assertEquals($expected1, $actual1, "during stage 1: {$message}"); $this->assertEquals($expected3, $actual3, "during stage 3: {$message}"); }
/** * Parses GRANT and REVOKE commands * * @param database database * @param command REVOKE command * */ public static function parse($database, $command) { $command = sql_parser::remove_last_semicolon($command); if (preg_match(self::PATTERN_GRANT_REVOKE, $command, $matches) > 0) { if (count($matches) != 5) { throw new exception("GRANT/REVOKE definition preg exploded into " . count($matches) . ", panic!"); } $action = strtoupper($matches[1]); switch ($action) { case 'GRANT': case 'REVOKE': break; default: throw new exception("permission action " . $action . " is unknown, panic!"); break; } $operations = preg_split("/[\\,\\s]+/", $matches[2], -1, PREG_SPLIT_NO_EMPTY); if (!is_array($operations)) { $permission = array($operations); } for ($i = 0; $i < count($operations); $i++) { $operations[$i] = strtoupper($operations[$i]); switch ($operations[$i]) { case 'ALL': case 'SELECT': case 'INSERT': case 'UPDATE': case 'DELETE': case 'USAGE': case 'REFERENCES': case 'TRIGGER': break; default: var_dump($operations); throw new exception("the operation " . $operations[$i] . " is unknown, panic!"); break; } } $object = $matches[3]; $chunks = preg_split("/[\\s]+/", $object, -1, PREG_SPLIT_NO_EMPTY); if (count($chunks) == 1) { // if there is no white space separating this bit // then let postgresql decide what it is when the grant is run $object_type = ''; $object_name = $chunks[0]; } else { if (count($chunks) == 2) { // SEQUENCE schema.table_table_id_seq // TABLE schema.table $object_type = $chunks[0]; $object_name = $chunks[1]; // if it's a schema, don't try to explode / default the schema prefix if (strcasecmp($object_type, 'SCHEMA') == 0) { $schema =& dbx::get_schema($database, $object_name); } else { $object_name = sql_parser::get_schema_name($object_name, $database) . '.' . sql_parser::get_object_name($object_name); $schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database)); } if ($schema == null) { throw new exception("Failed to find schema for grant/revoke: " . sql_parser::get_schema_name($object_name, $database)); } } else { throw new exception("object definition exploded into " . count($chunks) . " chunks, panic!"); } } $role = $matches[4]; // find the node_object, swtich'd on $object_type // based on http://www.postgresql.org/docs/8.4/static/sql-grant.html // empty object_type should be considered a TABLE GRANT/REVOKE if (strlen($object_type) == 0) { $object_type = 'TABLE'; } /* var_dump($command); var_dump(sql_parser::get_schema_name($object_name, $database)); var_dump(sql_parser::get_object_name($object_name)); /**/ switch (strtoupper($object_type)) { case 'SCHEMA': $node_object =& dbx::get_schema($database, $object_name); break; case 'SEQUENCE': $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database)); $node_object =& dbx::get_sequence($node_schema, sql_parser::get_object_name($object_name)); break; case 'TABLE': $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database)); $node_object =& dbx::get_table($node_schema, sql_parser::get_object_name($object_name)); break; default: throw new exception("unknown object_type " . $object_type . " encountered, panic!"); break; } dbx::set_permission($node_object, $action, $operations, $role); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Updates objects in schemas. * * @param $ofs output file segmenter * @param $old_database original database * @param $new_database new database */ private static function update_data($ofs, $delete_mode = FALSE) { if (mysql5_diff::$new_table_dependency != NULL && count(mysql5_diff::$new_table_dependency) > 0) { for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) { // go in reverse when in delete mode if ($delete_mode) { $item = mysql5_diff::$new_table_dependency[count(mysql5_diff::$new_table_dependency) - 1 - $i]; } else { $item = mysql5_diff::$new_table_dependency[$i]; } if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']); $old_table = NULL; if ($old_schema != NULL) { $old_table = dbx::get_table($old_schema, $item['table']['name']); } $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); if ($new_schema == NULL) { throw new exception("schema " . $item['schema']['name'] . " not found in new database"); } $new_table = dbx::get_table($new_schema, $item['table']['name']); if ($new_table == NULL) { throw new exception("table " . $item['table']['name'] . " not found in new database schema " . $new_schema['name']); } $ofs->write(mysql5_diff_tables::get_data_sql($old_schema, $old_table, $new_schema, $new_table, $delete_mode)); } } else { // dependency order unknown, hit them in natural order foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); mysql5_diff_tables::diff_data($ofs, $old_schema, $new_schema); } } }
/** * Adds commands for creation of new columns to the list of * commands. * * @param commands list of commands * @param old_table original table * @param new_table new table * @param drop_defaults_columns list for storing columns for which default value should be dropped */ private static function add_create_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns) { foreach (dbx::get_table_columns($new_table) as $new_column) { if (!mssql10_table::contains_column($old_table, $new_column['name'])) { if (!dbsteward::$ignore_oldnames && mssql10_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // oldColumnName renamed column ? rename table column of create new one $renamed_column_schema_name = $new_schema['name']; $renamed_column_table_name = $new_table['name']; $old_column_name = $new_column['oldColumnName']; $new_column_name = $new_column['name']; $commands[] = array('stage' => 'BEFORE1', 'command' => "-- column rename from oldColumnName specification\n" . "sp_rename '" . $renamed_column_schema_name . "." . $renamed_column_table_name . "." . $old_column_name . "' , '{$new_column_name}', 'COLUMN' ;"); continue; } // notice $include_null_definition is false // this is because ADD <column definition>s with NOT NULL will fail when there are existing rows $new_column_type = mssql10_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign); /* @DIFFTOOL - look for columns of a certain type being added if ( preg_match('/time|date/i', $new_column_type) > 0 ) { echo $new_schema . "." . $new_table['name'] . "." . $new_column['name'] . " of type " . $new_column_type . " " . $new_column['default'] . " found\n"; } /**/ $commands[] = array('stage' => '1', 'command' => "\tADD " . mssql10_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, mssql10_diff::$add_defaults, FALSE)); // we put the NOT NULL as an alteration in STAGE3 as data will have been updated in STAGE2 if (!mssql10_column::null_allowed($new_table, $new_column)) { $commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . mssql10::get_quoted_column_name($new_column['name']) . " " . $new_column_type . " NOT NULL"); // also, if it's defined, default the column in stage 1 so the SET NULL will actually pass in stage 3 if (strlen($new_column['default']) > 0) { $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . mssql10::get_quoted_schema_name($new_schema['name']) . "." . mssql10::get_quoted_table_name($new_table['name']) . " SET " . mssql10::get_quoted_column_name($new_column['name']) . " = DEFAULT" . " WHERE " . mssql10::get_quoted_column_name($new_column['name']) . " IS NULL;"); } } // if the column type is a defined enum, add a check constraint to enforce the pseudo-enum if (mssql10_column::enum_type_check(dbsteward::$new_database, $new_schema, $new_table, $new_column, $drop_sql, $add_sql)) { $commands[] = array('stage' => 'AFTER1', 'command' => $add_sql); } if (mssql10_diff::$add_defaults && !mssql10_column::null_allowed($new_table, $new_column)) { $drop_defaults_columns[] = $new_column; } // some columns need filled with values before any new constraints can be applied // this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute $db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']); if ($db_doc_new_schema) { $db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']); if ($db_doc_new_table) { $db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']); if ($db_doc_new_column) { if (isset($db_doc_new_column['beforeAddStage1'])) { $commands[] = array('stage' => 'BEFORE1', 'command' => trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition"); } if (isset($db_doc_new_column['afterAddStage1'])) { $commands[] = array('stage' => 'AFTER1', 'command' => trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition"); } if (isset($db_doc_new_column['beforeAddStage3'])) { $commands[] = array('stage' => 'BEFORE3', 'command' => trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition"); } if (isset($db_doc_new_column['afterAddStage3'])) { $commands[] = array('stage' => 'AFTER3', 'command' => trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition"); } } else { throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found"); } } } }
public function testSlonyId() { // make sure if require_slony_set_id is TRUE then there are changes dbsteward::$require_slony_id = TRUE; dbsteward::$slonyid_start_value = 9001; $this->do_slony_numbering(); $out_schema = dbx::get_schema($this->slonyid_doc, 'someapp'); // test that users table column user_id with type serial got set to slonyId 9001 $out_table_users = dbx::get_table($out_schema, 'users'); $out_column_users_user_id = dbx::get_table_column($out_table_users, 'user_id'); $this->assertEquals('9001', $out_column_users_user_id['slonyId']); // test that log table now has slonyId 9002 $out_table_log = dbx::get_table($out_schema, 'log'); $this->assertEquals('9002', $out_table_log['slonyId']); // test that log table log_id serial now has slonyId 9003 $out_column_log_log_id = dbx::get_table_column($out_table_log, 'log_id'); $this->assertEquals('9003', $out_column_log_log_id['slonyId']); }
/** * Database system convertions for specific supported sqlFormats * * @return converted $doc */ public static function sql_format_convert($doc) { // legacy 1.0 column add directive attribute conversion foreach ($doc->schema as $schema) { foreach ($schema->table as $table) { foreach ($table->column as $column) { if (isset($column['afterAddPreStage1'])) { $column['beforeAddStage1'] = (string) $column['afterAddPreStage1']; unset($column['afterAddPreStage1']); } if (isset($column['afterAddPostStage1'])) { $column['afterAddStage1'] = (string) $column['afterAddPostStage1']; unset($column['afterAddPostStage1']); } if (isset($column['afterAddPreStage2'])) { $column['beforeAddStage3'] = (string) $column['afterAddPreStage2']; unset($column['afterAddPreStage2']); } if (isset($column['afterAddPostStage2'])) { $column['afterAddStage3'] = (string) $column['afterAddPostStage2']; unset($column['afterAddPostStage2']); } if (isset($column['afterAddPreStage3'])) { $column['beforeAddStage3'] = (string) $column['afterAddPreStage3']; unset($column['afterAddPreStage3']); } if (isset($column['afterAddPostStage3'])) { $column['afterAddStage3'] = (string) $column['afterAddPostStage3']; unset($column['afterAddPostStage3']); } } } } // mssql10 sql format conversions // @TODO: apply mssql10_type_convert to function parameters/returns as well. see below mysql5 impl if (strcasecmp(dbsteward::get_sql_format(), 'mssql10') == 0) { foreach ($doc->schema as $schema) { // if objects are being placed in the public schema, move the schema definition to dbo if (strcasecmp($schema['name'], 'public') == 0) { if (dbx::get_schema($doc, 'dbo')) { throw new exception("sql_format_convert() attempting to move public schema to dbo but dbo schema already exists"); } $schema['name'] = 'dbo'; } foreach ($schema->table as $table) { foreach ($table->column as $column) { if (isset($column['foreignSchema']) && strcasecmp($column['foreignSchema'], 'public') == 0) { $column['foreignSchema'] = 'dbo'; } // column type conversion if (isset($column['type'])) { self::mssql10_type_convert($column); } } } } } elseif (strcasecmp(dbsteward::get_sql_format(), 'mysql5') == 0) { foreach ($doc->schema as $schema) { foreach ($schema->table as $table) { foreach ($table->column as $column) { if (isset($column['type'])) { list($column['type'], $column['default']) = self::mysql5_type_convert($column['type'], $column['default']); } } } foreach ($schema->function as $function) { list($function['returns'], $_) = self::mysql5_type_convert($function['returns']); foreach ($function->functionParameter as $param) { list($param['type'], $_) = self::mysql5_type_convert($param['type']); } } } } return $doc; }
/** * Updates data in table definitions * * @param ofs output file segmenter * @param $old_database original database * @param $new_database new database */ private static function update_data($ofs, $delete_mode = false) { if (self::$new_table_dependency != null && count(self::$new_table_dependency) > 0) { for ($i = 0; $i < count(self::$new_table_dependency); $i++) { // go in reverse when in delete mode if ($delete_mode) { $item = self::$new_table_dependency[count(self::$new_table_dependency) - 1 - $i]; } else { $item = self::$new_table_dependency[$i]; } if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } $old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']); $old_table = null; if ($old_schema != null) { $old_table = dbx::get_table($old_schema, $item['table']['name']); } $new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']); if ($new_schema == null) { throw new exception("schema " . $item['schema']['name'] . " not found in new database"); } $new_table = dbx::get_table($new_schema, $item['table']['name']); if ($new_table == null) { throw new exception("table " . $item['table']['name'] . " not found in new database schema " . $new_schema['name']); } pgsql8::set_context_replica_set_id($new_schema); // if the table was renamed, get old definition pointers for comparison if (pgsql8_diff_tables::is_renamed_table($new_schema, $new_table)) { dbsteward::info("NOTICE: " . $new_schema['name'] . "." . $new_table['name'] . " used to be called " . $new_table['oldTableName'] . " -- will diff data against that definition"); $old_schema = pgsql8_table::get_old_table_schema($new_schema, $new_table); $old_table = pgsql8_table::get_old_table($new_schema, $new_table); } $ofs->write(pgsql8_diff_tables::get_data_sql($old_schema, $old_table, $new_schema, $new_table, $delete_mode)); } } else { // dependency order unknown, hit them in natural order foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) { pgsql8::set_context_replica_set_id($new_schema); $old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']); pgsql8_diff_tables::diff_data($ofs, $old_schema, $new_schema); } } }
/** * Parses ALTER TABLE command. * * @param database database * @param command ALTER TABLE command * */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) { $table_name = trim($matches[1]); $table_owner = sql_parser::remove_last_semicolon(trim($matches[2])); $schema_name = sql_parser::get_schema_name($table_name, $database); $node_schema = dbx::get_schema($database, $schema_name); if ($node_schema == null) { throw new exception("schema " . $schema_name . " not found in database object"); } // is it actually a sequence ownership reference? if (substr($table_name, -4) == '_seq') { //@TODO: figure out what sequences are not table linked and need to have ownership set on them } else { $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); if ($node_table == null) { throw new exception("table " . sql_parser::get_object_name($table_name) . " not found in " . $node_schema['name'] . " schema object"); } dbx::set_attribute($node_table, 'owner', $table_owner); } } else { if (preg_match(self::PATTERN_START, $line, $matches) > 0) { $table_name = trim($matches[1]); } else { throw new exception("Cannot parse command: " . $line); } $schema_name = sql_parser::get_schema_name($table_name, $database); $node_schema = dbx::get_schema($database, $schema_name); $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); $line = sql_parser::remove_last_semicolon($matches[2]); self::parse_rows($database, $node_schema, $node_table, $line); } }
/** * Adds commands for creation of new columns to the list of * commands. * * @param commands list of commands * @param old_table original table * @param new_table new table * @param drop_defaults_columns list for storing columns for which default value should be dropped */ private static function add_create_table_columns(&$commands, $old_table, $new_schema, $new_table, &$drop_defaults_columns) { $case_sensitive = dbsteward::$quote_all_names || dbsteward::$quote_column_names; foreach (dbx::get_table_columns($new_table) as $new_column) { if (!pgsql8_table::contains_column($old_table, $new_column['name'], $case_sensitive)) { if (!dbsteward::$ignore_oldnames && pgsql8_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // oldColumnName renamed column ? rename column instead of create new one $old_column_name = pgsql8::get_quoted_column_name($new_column['oldColumnName']); $new_column_name = pgsql8::get_quoted_column_name($new_column['name']); $commands[] = array('stage' => 'AFTER1', 'command' => "-- column rename from oldColumnName specification\n" . "ALTER TABLE " . pgsql8::get_quoted_schema_name($new_schema['name']) . "." . pgsql8::get_quoted_table_name($new_table['name']) . " RENAME COLUMN {$old_column_name} TO {$new_column_name};"); continue; } // notice $include_null_definition is false // this is because ADD COLUMNs with NOT NULL will fail when there are existing rows /* @DIFFTOOL - look for columns of a certain type being added if ( preg_match('/time|date/i', $new_column['type']) > 0 ) { echo $new_schema . "." . $new_table['name'] . "." . $new_column['name'] . " TYPE " . $new_column['type'] . " " . $new_column['default'] . "\n"; } /**/ $commands[] = array('stage' => '1', 'command' => "\tADD COLUMN " . pgsql8_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $new_column, pgsql8_diff::$add_defaults, false)); // instead we put the NOT NULL defintion in stage3 schema changes once data has been updated in stage2 data if (!pgsql8_column::null_allowed($new_table, $new_column)) { $commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . pgsql8::get_quoted_column_name($new_column['name']) . " SET NOT NULL"); // also, if it's defined, default the column in stage 1 so the SET NULL will actually pass in stage 3 if (strlen($new_column['default']) > 0) { $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . pgsql8::get_quoted_schema_name($new_schema['name']) . "." . pgsql8::get_quoted_table_name($new_table['name']) . " SET " . pgsql8::get_quoted_column_name($new_column['name']) . " = DEFAULT" . " WHERE " . pgsql8::get_quoted_column_name($new_column['name']) . " IS NULL;"); } } // FS#15997 - dbsteward - replica inconsistency on added new columns with default now() // slony replicas that add columns via DDL that have a default of NOW() will be out of sync // because the data in those columns is being placed in as a default by the local db server // to compensate, add UPDATE statements to make the these column's values NOW() from the master if (pgsql8_column::has_default_now($new_table, $new_column)) { $commands[] = array('stage' => 'AFTER1', 'command' => "UPDATE " . pgsql8::get_quoted_schema_name($new_schema['name']) . "." . pgsql8::get_quoted_table_name($new_table['name']) . " SET " . pgsql8::get_quoted_column_name($new_column['name']) . " = " . $new_column['default'] . " ; -- has_default_now: this statement is to make sure new columns are in sync on replicas"); } if (pgsql8_diff::$add_defaults && !pgsql8_column::null_allowed($new_table, $new_column)) { $drop_defaults_columns[] = $new_column; } // some columns need filled with values before any new constraints can be applied // this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute $db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']); if ($db_doc_new_schema) { $db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']); if ($db_doc_new_table) { $db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']); if ($db_doc_new_column) { if (isset($db_doc_new_column['beforeAddStage1'])) { $commands[] = array('stage' => 'BEFORE1', 'command' => trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition"); } if (isset($db_doc_new_column['afterAddStage1'])) { $commands[] = array('stage' => 'AFTER1', 'command' => trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition"); } if (isset($db_doc_new_column['beforeAddStage3'])) { $commands[] = array('stage' => 'BEFORE3', 'command' => trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition"); } if (isset($db_doc_new_column['afterAddStage3'])) { $commands[] = array('stage' => 'AFTER3', 'command' => trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition"); } } else { throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found"); } } } }
/** * Outputs commands for addition, removal and modifications of * table columns. * * @param $ofs1 stage1 output file segmenter * @param $ofs3 stage3 output file segmenter * @param $old_table original table * @param $new_table new table */ private static function update_table_columns($ofs1, $ofs3, $old_schema, $old_table, $new_schema, $new_table) { // README: This function differs substantially from its siblings in pgsql8 or mssql10 // The reason for this is two-fold. // // First, for every ALTER TABLE, mysql actually rebuilds the whole table. Thus, it is drastically // more efficient if we can pack as much into a single ALTER TABLE as possible. Secondly, unlike // other RDBMS's, MySQL requires that you completely redefine a column if you (a) rename it, // (b) change its NULL/NOT NULL attribute, (c) change its type, or (d) add or remove its AUTO_INCREMENT // attribute. This means that just about 75% of the changes that could happen between two versions // of column require a column redefine rather than granular alteration. Therefore, it doesn't make // much sense to have 3 redefines of a column in a single ALTER TABLE, which is what happens if you // port over the pgsql8 or mssql10 versions of this function. // // For these reasons, the mysql5 implementation of this function is optimized for making as few // alterations to each column as possible. // arbitrary sql $extra = array('BEFORE1' => array(), 'AFTER1' => array(), 'BEFORE3' => array(), 'AFTER3' => array()); // each entry is keyed by column name, and has a 'command' key, which may be one of // nothing: do nothing // drop: drop this column // change: rename & redefine // create: create this column // modify: redefine without rename // the 'defaults' key is whether to give the column a DEFAULT clause if it is NOT NULL // the 'nulls' key is whether to include NULL / NOT NULL in the column definition $commands = array('1' => array(), '3' => array()); $defaults = array('set' => array(), 'drop' => array()); foreach (dbx::get_table_columns($old_table) as $old_column) { if (!mysql5_table::contains_column($new_table, $old_column['name'])) { if (!dbsteward::$ignore_oldnames && ($renamed_column_name = mysql5_table::column_name_by_old_name($new_table, $old_column['name'])) !== false) { continue; } else { // echo "NOTICE: add_drop_table_columns() " . $new_table['name'] . " does not contain " . $old_column['name'] . "\n"; $commands['3'][(string) $old_column['name']] = array('command' => 'drop', 'column' => $old_column); } } } $new_columns = dbx::get_table_columns($new_table); foreach ($new_columns as $col_index => $new_column) { $cmd1 = array('command' => 'nothing', 'column' => $new_column, 'defaults' => mysql5_diff::$add_defaults, 'nulls' => TRUE, 'auto_increment' => FALSE); if (!mysql5_table::contains_column($old_table, $new_column['name'], TRUE)) { // column not present in old table, is either renamed or new if (!dbsteward::$ignore_oldnames && mysql5_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) { // renamed $cmd1['command'] = 'change'; $cmd1['old'] = $new_column['oldColumnName']; } else { // new $cmd1['command'] = 'create'; if ($col_index == 0) { $cmd1['first'] = TRUE; } else { $cmd1['after'] = $new_columns[$col_index - 1]['name']; } // some columns need filled with values before any new constraints can be applied // this is accomplished by defining arbitrary SQL in the column element afterAddPre/PostStageX attribute $db_doc_new_schema = dbx::get_schema(dbsteward::$new_database, $new_schema['name']); if ($db_doc_new_schema) { $db_doc_new_table = dbx::get_table($db_doc_new_schema, $new_table['name']); if ($db_doc_new_table) { $db_doc_new_column = dbx::get_table_column($db_doc_new_table, $new_column['name']); if ($db_doc_new_column) { if (isset($db_doc_new_column['beforeAddStage1'])) { $extras['BEFORE1'][] = trim($db_doc_new_column['beforeAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage1 definition"; } if (isset($db_doc_new_column['afterAddStage1'])) { $extras['AFTER1'][] = trim($db_doc_new_column['afterAddStage1']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage1 definition"; } if (isset($db_doc_new_column['beforeAddStage3'])) { $extras['BEFORE3'][] = trim($db_doc_new_column['beforeAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " beforeAddStage3 definition"; } if (isset($db_doc_new_column['afterAddStage3'])) { $extras['AFTER3'][] = trim($db_doc_new_column['afterAddStage3']) . " -- from " . $new_schema['name'] . "." . $new_table['name'] . "." . $new_column['name'] . " afterAddStage3 definition"; } } else { throw new exception("afterAddPre/PostStageX column " . $new_column['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX table " . $new_table['name'] . " not found"); } } else { throw new exception("afterAddPre/PostStageX schema " . $new_schema['name'] . " not found"); } } } else { if ($old_column = dbx::get_table_column($old_table, $new_column['name'])) { $old_column_type = mysql5_column::column_type(dbsteward::$old_database, $old_schema, $old_table, $old_column); $new_column_type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column); $old_default = isset($old_column['default']) ? (string) $old_column['default'] : ''; $new_default = isset($new_column['default']) ? (string) $new_column['default'] : ''; $auto_increment_added = !mysql5_column::is_auto_increment($old_column['type']) && mysql5_column::is_auto_increment($new_column['type']); $auto_increment_removed = mysql5_column::is_auto_increment($old_column['type']) && !mysql5_column::is_auto_increment($new_column['type']); $auto_increment_changed = $auto_increment_added || $auto_increment_removed; $type_changed = strcasecmp($old_column_type, $new_column_type) !== 0 || $auto_increment_changed; $default_changed = strcasecmp($old_default, $new_default) !== 0; $nullable_changed = strcasecmp($old_column['null'] ?: 'true', $new_column['null'] ?: 'true') !== 0; $cmd1['command'] = 'nothing'; if ($type_changed || $nullable_changed) { $cmd1['command'] = 'modify'; if ($default_changed && !$new_default) { $cmd1['defaults'] = FALSE; } if ($auto_increment_added) { $cmd1['auto_increment'] = TRUE; } } elseif ($default_changed) { if (strlen($new_default) > 0) { if (mysql5_column::is_timestamp($new_column)) { // timestamps get special treatment $cmd1['command'] = 'modify'; } else { $defaults['set'][] = $new_column; } } else { $defaults['drop'][] = $new_column; } } } } $commands['1'][(string) $new_column['name']] = $cmd1; } // end foreach column $table_name = mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']); $get_command_sql = function ($command) use(&$new_schema, &$new_table) { if ($command['command'] == 'nothing') { return NULL; } if ($command['command'] == 'drop') { $name = mysql5::get_quoted_column_name($command['column']['name']); return "DROP COLUMN {$name}"; } $defn = mysql5_column::get_full_definition(dbsteward::$new_database, $new_schema, $new_table, $command['column'], $command['defaults'], $command['nulls'], $command['auto_increment']); if ($command['command'] == 'change') { $old = mysql5::get_quoted_column_name($command['old']); return "CHANGE COLUMN {$old} {$defn}"; } if ($command['command'] == 'create') { if (array_key_exists('first', $command)) { return "ADD COLUMN {$defn} FIRST"; } elseif (array_key_exists('after', $command)) { $col = mysql5::get_quoted_column_name($command['after']); return "ADD COLUMN {$defn} AFTER {$col}"; } else { return "ADD COLUMN {$defn}"; } } if ($command['command'] == 'modify') { return "MODIFY COLUMN {$defn}"; } throw new Exception("Invalid column diff command '{$command['command']}'"); }; // end get_command_sql() // pre-stage SQL foreach ($extra['BEFORE1'] as $sql) { $ofs1->write($sql . "\n\n"); } foreach ($extra['BEFORE3'] as $sql) { $ofs3->write($sql . "\n\n"); } // output stage 1 sql $stage1_commands = array(); foreach ($commands['1'] as $column_name => $command) { $stage1_commands[] = $get_command_sql($command); } // we can also add SET DEFAULTs in here foreach ($defaults['set'] as $column) { $name = mysql5::get_quoted_column_name($column['name']); if (strlen($column['default']) > 0) { $default = (string) $column['default']; } else { $type = mysql5_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $column); $default = mysql5_column::get_default_value($type); } $stage1_commands[] = "ALTER COLUMN {$name} SET DEFAULT {$default}"; } foreach ($defaults['drop'] as $column) { $name = mysql5::get_quoted_column_name($column['name']); $stage1_commands[] = "ALTER COLUMN {$name} DROP DEFAULT"; } $stage1_commands = array_filter($stage1_commands); if (count($stage1_commands) > 0) { $sql = "ALTER TABLE {$table_name}\n "; $sql .= implode(",\n ", $stage1_commands); $sql .= ";\n\n"; $ofs1->write($sql); } // output stage 3 sql $stage3_commands = array(); foreach ($commands['3'] as $column_name => $command) { $stage3_commands[] = $get_command_sql($command); } $stage3_commands = array_filter($stage3_commands); if (count($stage3_commands) > 0) { $sql = "ALTER TABLE {$table_name}\n "; $sql .= implode(",\n ", $stage3_commands); $sql .= ";\n\n"; $ofs3->write($sql); } // post-stage SQL foreach ($extra['AFTER1'] as $sql) { $ofs1->write($sql . "\n\n"); } foreach ($extra['AFTER3'] as $sql) { $ofs3->write($sql . "\n\n"); } }
protected function compare_xml_definition() { $doc_a = simplexml_load_file($this->xml_file_a); $doc_b = simplexml_load_file($this->xml_file_b); // are all of the schemas defined in A in B? foreach ($doc_a->schema as $schema_a) { $schema_b = dbx::get_schema($doc_b, $schema_a['name']); $this->assertTrue(is_object($schema_b), $schema_a['name'] . ' schema_b object pointer not found'); $this->assertEquals($schema_a['name'], $schema_b['name']); // are all of the tables defined in A in B? foreach ($schema_a->table as $table_a) { $table_b = dbx::get_table($schema_b, $table_a['name']); $this->assertTrue(is_object($table_b), $table_a['name'] . ' table_b object pointer not found'); $this->assertEquals($table_a['name'], $table_b['name']); } } }
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})"; }
public static function enum_type_check($db_doc, $node_schema, $node_table, $node_column, &$drop_sql, &$add_sql) { // if the column type is a defined enum, (re)add a check constraint to enforce the pseudo-enum $foreign = array(); $column_type = mssql10_column::column_type($db_doc, $node_schema, $node_table, $node_column, $foreign, FALSE); if (preg_match('/' . dbx::enum_regex($db_doc) . '/i', $column_type) > 0) { $type_schema_name = sql_parser::get_schema_name($column_type, $db_doc); $type_schema = dbx::get_schema($db_doc, $type_schema_name); $node_type = dbx::get_type($type_schema, sql_parser::get_object_name($column_type, $db_doc)); if (!$node_type) { var_dump($node_type); throw new exception('failed to find column_type ' . $column_type . ' in type_schema_name ' . $type_schema_name); } $drop_sql = mssql10_type::get_drop_check_sql($node_schema, $node_table, $node_column, $node_type); $add_sql = mssql10_type::get_add_check_sql($node_schema, $node_table, $node_column, $node_type); return TRUE; } return FALSE; }