/**
* Parses definition of the column
*
* @param definition definition of the column
*/
public static function parse_definition(&$node_schema, &$node_table, &$node_column, $definition)
{
$type = $definition;
if (preg_match(self::PATTERN_NOT_NULL, $type, $matches) > 0) {
$type = trim($matches[1]);
dbx::set_attribute($node_column, 'null', 'false');
} else {
if (preg_match(self::PATTERN_NULL, $type, $matches) > 0 && preg_match(self::PATTERN_DEFAULT_NULL, $type) == 0) {
// PATTERN_NULL match only if it is not a trailing DEFAULT NULL
// as that is not a null designation just a default designation
$type = trim($matches[1]);
dbx::set_attribute($node_column, 'null', 'true');
}
}
if (preg_match(self::PATTERN_DEFAULT, $type, $matches) > 0) {
$type = trim($matches[1]);
dbx::set_attribute($node_column, 'default', trim($matches[2]));
}
// post-parsing sanity checks
if (preg_match('/[\\s]+/', $type) > 0) {
// type contains whitespace
// split the type and look for bad tokens
$bad_keywords = array('DEFAULT', 'UNIQUE');
$tokens = preg_split("/[\\s]+/", $type, -1, PREG_SPLIT_NO_EMPTY);
foreach ($tokens as $token) {
foreach ($bad_keywords as $bad_keyword) {
if (strcasecmp($token, $bad_keyword) == 0) {
var_dump($definition);
throw new exception($node_column['name'] . " column definition parse fail: type '" . $type . "' still contains '" . $bad_keyword . "' keyword -- look at callers for mis-handling of definition parameter");
}
}
}
}
dbx::set_attribute($node_column, 'type', $type);
// for serial and bigserials, create the accompanying sequence that powers the serial
if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $type) > 0) {
$sequence_name = pgsql8::identifier_name($node_schema['name'], $node_table['name'], $node_column['name'], '_seq');
$node_sequence =& dbx::get_sequence($node_schema, $sequence_name, TRUE);
dbx::set_attribute($node_sequence, 'owner', $node_table['owner']);
dbx::set_attribute($node_sequence, 'start', '1');
dbx::set_attribute($node_sequence, 'min', '1');
dbx::set_attribute($node_sequence, 'inc', '1');
dbx::set_attribute($node_sequence, 'cycle', 'false');
}
}
/**
* Parses all rows in ALTER TABLE command.
*
* @param table table being parsed
* @param commands commands
*
* @throws ParserException Thrown if problem occured while parsing DDL.
*/
private static function parse_rows(&$db_doc, &$node_schema, &$node_table, $commands)
{
$line = $commands;
$subCommand = null;
while (strlen($line) > 0) {
$commandEnd = sql_parser::get_command_end($line, 0);
$subCommand = trim(substr($line, 0, $commandEnd));
$line = $commandEnd >= strlen($line) ? "" : substr($line, $commandEnd + 1);
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_CONSTRAINT_FOREIGN_KEY, $subCommand, $matches) > 0) {
$column_name = trim($matches[3]);
$constraint_name = trim($matches[1]);
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[2]));
$subCommand = "";
}
}
if (preg_match(self::PATTERN_ADD_CONSTRAINT, $subCommand, $matches) > 0) {
$constraint_name = trim($matches[1]);
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[2]));
$subCommand = "";
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_PRIMARY_KEY, $subCommand, $matches) > 0) {
$definition = trim($matches[1]);
$column_name = trim($matches[2]);
$constraint_name = $node_table['name'] . '_pkey';
dbx::set_attribute($node_table, 'primaryKey', $column_name);
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_FOREIGN_KEY, $subCommand, $matches) > 0) {
$column_name = trim($matches[2]);
$constraint_name = pgsql8::identifier_name($node_schema['name'], $node_table['name'], $column_name, '_fkey');
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[1]));
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_SET_DEFAULT, $subCommand, $matches) > 0) {
$column_name = trim($matches[1]);
$default_value = trim($matches[2]);
if ($node_table->contains_column($column_name)) {
$node_column =& dbx::get_column($node_table, $column_name);
dbx::set_attribute($node_column, 'default', $default_value);
} else {
throw new exception("Cannot find column '" . $column_name . " 'in table '" . $node_table['name'] . "'");
}
$subCommand = "";
}
}
if (preg_match(self::PATTERN_ALTER_COLUMN_STATISTICS, $subCommand, $matches) > 0) {
$column_name = trim($matches[2]);
$value = trim($matches[3]);
$node_column =& dbx::get_column($node_table, $column_name);
dbx::set_attribute($node_column, 'statistics', $value);
$subCommand = "";
}
if (preg_match(self::PATTERN_CLUSTER_ON, $subCommand, $matches) > 0) {
$indexName = trim($matches[1]);
dbx::set_attribute($node_column, 'clusterIndexName', $indexName);
$subCommand = "";
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_TRIGGER, $subCommand, $matches) > 0) {
$triggerName = trim($matches[2]);
throw new exception("@TODO: do something with ALTER TABLE ... ENABLE / DISABLE trigger statements");
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
throw new exception("Don't know how to parse: " . $subCommand);
}
}
}
/**
* 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());
}
/**
* Adds commands for modification of 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_modify_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)) {
continue;
}
if (!dbsteward::$ignore_oldnames && pgsql8_diff_tables::is_renamed_column($old_table, $new_table, $new_column)) {
// oldColumnName renamed column ? skip definition diffing on it, it is being renamed
continue;
}
$old_column = pgsql8_table::get_column_by_name($old_table, $new_column['name'], $case_sensitive);
$new_column_name = pgsql8::get_quoted_column_name($new_column['name']);
$old_column_type = null;
if ($old_column) {
$old_column_type = pgsql8_column::column_type(dbsteward::$old_database, $new_schema, $old_table, $old_column, $foreign);
}
$new_column_type = pgsql8_column::column_type(dbsteward::$new_database, $new_schema, $new_table, $new_column, $foreign);
if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $new_column_type) > 0 && $old_column_type !== null && preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $old_column_type) == 0) {
throw new Exception("Table " . $new_schema['name'] . "." . $new_table['name'] . " column " . $new_column['name'] . " has linked type " . $new_column_type . " -- Column types cannot be altered to serial. If this column cannot be recreated as part of database change control, a user defined serial should be created, and corresponding nextval() defined as the default for the column.");
}
if (strcmp($old_column_type, $new_column_type) != 0) {
// ALTER TYPE .. USING support by looking up the new type in the xml definition
$type_using = '';
$type_using_comment = '';
if (isset($new_column['convertUsing'])) {
$type_using = ' USING ' . $new_column['convertUsing'] . ' ';
$type_using_comment = '- found XML convertUsing: ' . $new_column['convertUsing'] . ' ';
}
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " TYPE " . $new_column_type . $type_using . " /* TYPE change - table: " . $new_table['name'] . " original: " . $old_column_type . " new: " . $new_column_type . ' ' . $type_using_comment . '*/');
}
$old_default = isset($old_column['default']) ? $old_column['default'] : '';
$new_default = isset($new_column['default']) ? $new_column['default'] : '';
if (strcmp($old_default, $new_default) != 0) {
if (strlen($new_default) == 0) {
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " DROP DEFAULT");
} else {
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $new_default);
}
}
if (strcasecmp($old_column['null'], $new_column['null']) != 0) {
if (pgsql8_column::null_allowed($new_table, $new_column)) {
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " DROP NOT NULL");
} else {
if (pgsql8_diff::$add_defaults) {
$default_value = pgsql8_column::get_default_value($new_column_type);
if ($default_value != null) {
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " SET DEFAULT " . $default_value);
$drop_defaults_columns[] = $new_column;
}
}
// if the default value is defined in the dbsteward XML
// set the value of the column to the default in end of stage 1 so that NOT NULL can be applied in stage 3
// this way custom <sql> tags can be avoided for upgrade generation if defaults are specified
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 " . $new_column_name . " = " . $new_column['default'] . " WHERE " . $new_column_name . " IS NULL; -- has_default_now: make modified column that is null the default value before NOT NULL hits");
}
$commands[] = array('stage' => '3', 'command' => "\tALTER COLUMN " . $new_column_name . " SET NOT NULL");
}
}
// drop sequence and default if converting from *serial to *int
if (preg_match('/serial$/', $old_column['type']) > 0 && ($new_column['type'] == 'int' || $new_column['type'] == 'bigint')) {
$commands[] = array('stage' => 'BEFORE3', 'command' => "DROP SEQUENCE IF EXISTS " . pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_table_name(pgsql8::identifier_name($new_schema['name'], $new_table['name'], $new_column['name'], '_seq')) . ";");
$commands[] = array('stage' => '1', 'command' => "\tALTER COLUMN " . $new_column_name . " DROP DEFAULT");
}
}
}
public static function get_sql($db_doc, $node_schema, $node_object, $node_permission)
{
format::set_context_replica_set_id($node_object);
$perms = pgsql8_permission::get_permission_operations($node_permission);
$roles = preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY);
$object_type = strtoupper($node_object->getName());
switch ($object_type) {
case 'SCHEMA':
$object_name = pgsql8::get_quoted_schema_name($node_schema['name']);
break;
case 'SEQUENCE':
case 'TABLE':
case 'VIEW':
$object_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']);
break;
case 'FUNCTION':
$object_name = pgsql8_function::get_declaration($node_schema, $node_object);
break;
default:
throw new exception("unknown object type encountered: " . $object_type);
}
$sql = '';
for ($j = 0; $j < count($roles); $j++) {
$with = '';
if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
$with = "WITH " . $node_permission['with'] . " OPTION";
}
if (strcasecmp($object_type, 'VIEW') == 0) {
// postgresql doesn't want you to name the view keyword when you grant rights to views
$pg_object_type = '';
} else {
$pg_object_type = $object_type;
}
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement(strtoupper($node_permission->getName()), implode(', ', $perms), $pg_object_type, $object_name, xml_parser::role_enum($db_doc, $roles[$j]), $with);
// SCHEMA IMPLICIT GRANTS
if (strcasecmp($object_type, 'SCHEMA') == 0) {
// READYONLY USER PROVISION: grant usage on the schema for the readonly user
if (strlen($db_doc->database->role->readonly) > 0) {
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement('GRANT', 'USAGE', 'SCHEMA', pgsql8::get_quoted_schema_name($node_schema['name']), $db_doc->database->role->readonly);
}
}
// SEQUENCE IMPLICIT GRANTS
if (strcasecmp($object_type, 'SEQUENCE') == 0) {
// READYONLY USER PROVISION: generate a SELECT on the sequence for the readonly user
if (strlen($db_doc->database->role->readonly) > 0) {
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement('GRANT', 'SELECT', 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']), $db_doc->database->role->readonly);
}
}
// TABLE IMPLICIT GRANTS
if (strcasecmp($object_type, 'TABLE') == 0) {
// READYONLY USER PROVISION: grant select on the table for the readonly user
if (strlen($db_doc->database->role->readonly) > 0) {
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement('GRANT', 'SELECT', 'TABLE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']), $db_doc->database->role->readonly);
}
// don't need to grant cascaded serial permissions to the table owner
if (strcasecmp('ROLE_OWNER', $roles[$j]) == 0) {
continue;
}
// set serial columns permissions based on table permissions
foreach ($node_object->column as $column) {
if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $column['type']) > 0) {
$col_sequence = pgsql8::identifier_name($node_schema['name'], $node_object['name'], $column['name'], '_seq');
$seq_priv = array();
// if you can SELECT, INSERT or UPDATE the table, you can SELECT on the sequence
if (in_array('SELECT', $perms) || in_array('INSERT', $perms) || in_array('UPDATE', $perms)) {
$seq_priv[] = 'SELECT';
}
// if you can INSERT or UPDATE the table, you can UPDATE the sequence
if (in_array('INSERT', $perms) || in_array('UPDATE', $perms)) {
$seq_priv[] = 'UPDATE';
}
// if you only have USAGE or SELECT
// then seq_priv is empty, and no grant should be issued
if (count($seq_priv) > 0) {
$with = '';
if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
$with = "WITH " . $node_permission['with'] . " OPTION";
}
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement('GRANT', implode(',', $seq_priv), 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($col_sequence), xml_parser::role_enum($db_doc, $roles[$j]), $with);
}
// READYONLY USER PROVISION: grant implicit select on the sequence for the readonly user
if (strlen($db_doc->database->role->readonly) > 0) {
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement('GRANT', 'SELECT', 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($col_sequence), $db_doc->database->role->readonly);
}
}
}
}
}
return $sql;
}