Ejemplo n.º 1
0
 public static function extract_schema($host, $port, $database, $user, $password)
 {
     $databases = explode(',', $database);
     dbsteward::notice("Connecting to mysql5 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user);
     // if not supplied, ask for the password
     if ($password === FALSE) {
         echo "Password: "******"Analyzing database {$database}");
         $db->use_database($database);
         $node_schema = $doc->addChild('schema');
         $node_schema['name'] = $database;
         $node_schema['owner'] = 'ROLE_OWNER';
         // extract global and schema permissions under the public schema
         foreach ($db->get_global_grants($user) as $db_grant) {
             $node_grant = $node_schema->addChild('grant');
             // There are 28 permissions encompassed by the GRANT ALL statement
             $node_grant['operation'] = $db_grant->num_ops == 28 ? 'ALL' : $db_grant->operations;
             $node_grant['role'] = self::translate_role_name($user, $doc);
             if ($db_grant->is_grantable) {
                 $node_grant['with'] = 'GRANT';
             }
         }
         $enum_types = array();
         $enum_type = function ($obj, $mem, $values) use(&$enum_types) {
             // if that set of values is defined by a previous enum, use that
             foreach ($enum_types as $name => $enum) {
                 if ($enum === $values) {
                     return $name;
                 }
             }
             // otherwise, make a new one
             $name = "enum_" . md5(implode('_', $values));
             $enum_types[$name] = $values;
             return $name;
         };
         foreach ($db->get_tables() as $db_table) {
             dbsteward::info("Analyze table options/partitions " . $db_table->table_name);
             $node_table = $node_schema->addChild('table');
             $node_table['name'] = $db_table->table_name;
             $node_table['owner'] = 'ROLE_OWNER';
             // because mysql doesn't have object owners
             $node_table['description'] = $db_table->table_comment;
             $node_table['primaryKey'] = '';
             if (stripos($db_table->create_options, 'partitioned') !== FALSE && ($partition_info = $db->get_partition_info($db_table))) {
                 $node_partition = $node_table->addChild('tablePartition');
                 $node_partition['sqlFormat'] = 'mysql5';
                 $node_partition['type'] = $partition_info->type;
                 switch ($partition_info->type) {
                     case 'HASH':
                     case 'LINEAR HASH':
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'expression');
                         $opt->addAttribute('value', $partition_info->expression);
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'number');
                         $opt->addAttribute('value', $partition_info->number);
                         break;
                     case 'KEY':
                     case 'LINEAR KEY':
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'columns');
                         $opt->addAttribute('value', $partition_info->columns);
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', 'number');
                         $opt->addAttribute('value', $partition_info->number);
                         break;
                     case 'LIST':
                     case 'RANGE':
                     case 'RANGE COLUMNS':
                         $opt = $node_partition->addChild('tablePartitionOption');
                         $opt->addAttribute('name', $partition_info->type == 'RANGE COLUMNS' ? 'columns' : 'expression');
                         $opt->addAttribute('value', $partition_info->expression);
                         foreach ($partition_info->segments as $segment) {
                             $node_seg = $node_partition->addChild('tablePartitionSegment');
                             $node_seg->addAttribute('name', $segment->name);
                             $node_seg->addAttribute('value', $segment->value);
                         }
                         break;
                 }
             }
             foreach ($db->get_table_options($db_table) as $name => $value) {
                 if (strcasecmp($name, 'auto_increment') === 0 && !static::$use_auto_increment_table_options) {
                     // don't extract auto_increment tableOptions if we're not using them
                     continue;
                 }
                 $node_option = $node_table->addChild('tableOption');
                 $node_option['sqlFormat'] = 'mysql5';
                 $node_option['name'] = $name;
                 $node_option['value'] = $value;
             }
             dbsteward::info("Analyze table columns " . $db_table->table_name);
             foreach ($db->get_columns($db_table) as $db_column) {
                 $node_column = $node_table->addChild('column');
                 $node_column['name'] = $db_column->column_name;
                 if (!empty($db_column->column_comment)) {
                     $node_column['description'] = $db_column->column_comment;
                 }
                 // returns FALSE if not serial, int/bigint if it is
                 $type = $db->is_serial_column($db_table, $db_column);
                 if (!$type) {
                     $type = $db_column->column_type;
                     if (stripos($type, 'enum') === 0) {
                         $values = $db->parse_enum_values($db_column->column_type);
                         $type = $enum_type($db_table->table_name, $db_column->column_name, $values);
                     }
                     if ($db_column->is_auto_increment) {
                         $type .= ' AUTO_INCREMENT';
                     }
                 }
                 if ($db_column->is_auto_update) {
                     $type .= ' ON UPDATE CURRENT_TIMESTAMP';
                 }
                 $node_column['type'] = $type;
                 // @TODO: if there are serial sequences/triggers for the column then convert to serial
                 if ($db_column->column_default !== NULL) {
                     $node_column['default'] = mysql5::escape_default_value($db_column->column_default);
                 } elseif (strcasecmp($db_column->is_nullable, 'YES') === 0) {
                     $node_column['default'] = 'NULL';
                 }
                 $node_column['null'] = strcasecmp($db_column->is_nullable, 'YES') === 0 ? 'true' : 'false';
             }
             // get all plain and unique indexes
             dbsteward::info("Analyze table indexes " . $db_table->table_name);
             foreach ($db->get_indices($db_table) as $db_index) {
                 // don't process primary key indexes here
                 if (strcasecmp($db_index->index_name, 'PRIMARY') === 0) {
                     continue;
                 }
                 // implement unique indexes on a single column as unique column, but only if the index name is the column name
                 if ($db_index->unique && count($db_index->columns) == 1 && strcasecmp($db_index->columns[0], $db_index->index_name) === 0) {
                     $column = $db_index->columns[0];
                     $node_column = dbx::get_table_column($node_table, $column);
                     if (!$node_column) {
                         throw new Exception("Unexpected: Could not find column node {$column} for unique index {$db_index->index_name}");
                     } else {
                         $node_column = $node_column[0];
                     }
                     $node_column['unique'] = 'true';
                 } else {
                     $node_index = $node_table->addChild('index');
                     $node_index['name'] = $db_index->index_name;
                     $node_index['using'] = strtolower($db_index->index_type);
                     $node_index['unique'] = $db_index->unique ? 'true' : 'false';
                     $i = 1;
                     foreach ($db_index->columns as $column_name) {
                         $node_index->addChild('indexDimension', $column_name)->addAttribute('name', $column_name . '_' . $i++);
                     }
                 }
             }
             // get all primary/foreign keys
             dbsteward::info("Analyze table constraints " . $db_table->table_name);
             foreach ($db->get_constraints($db_table) as $db_constraint) {
                 if (strcasecmp($db_constraint->constraint_type, 'primary key') === 0) {
                     $node_table['primaryKey'] = implode(',', $db_constraint->columns);
                 } elseif (strcasecmp($db_constraint->constraint_type, 'foreign key') === 0) {
                     // mysql sees foreign keys as indexes pointing at indexes.
                     // it's therefore possible for a compound index to point at a compound index
                     if (!$db_constraint->referenced_columns || !$db_constraint->referenced_table_name) {
                         throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} does not refer to any foreign columns");
                     }
                     if (count($db_constraint->referenced_columns) == 1 && count($db_constraint->columns) == 1) {
                         // not a compound index, define the FK inline in the column
                         $column = $db_constraint->columns[0];
                         $ref_column = $db_constraint->referenced_columns[0];
                         $node_column = dbx::get_table_column($node_table, $column);
                         if (!$node_column) {
                             throw new Exception("Unexpected: Could not find column node {$column} for foreign key constraint {$db_constraint->constraint_name}");
                         }
                         $node_column['foreignSchema'] = $db_constraint->referenced_table_schema;
                         $node_column['foreignTable'] = $db_constraint->referenced_table_name;
                         $node_column['foreignColumn'] = $ref_column;
                         unset($node_column['type']);
                         // inferred from referenced column
                         $node_column['foreignKeyName'] = $db_constraint->constraint_name;
                         // RESTRICT is the default, leave it implicit if possible
                         if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) {
                             $node_column['foreignOnDelete'] = str_replace(' ', '_', $db_constraint->delete_rule);
                         }
                         if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) {
                             $node_column['foreignOnUpdate'] = str_replace(' ', '_', $db_constraint->update_rule);
                         }
                     } elseif (count($db_constraint->referenced_columns) > 1 && count($db_constraint->referenced_columns) == count($db_constraint->columns)) {
                         $node_fkey = $node_table->addChild('foreignKey');
                         $node_fkey['columns'] = implode(', ', $db_constraint->columns);
                         $node_fkey['foreignSchema'] = $db_constraint->referenced_table_schema;
                         $node_fkey['foreignTable'] = $db_constraint->referenced_table_name;
                         $node_fkey['foreignColumns'] = implode(', ', $db_constraint->referenced_columns);
                         $node_fkey['constraintName'] = $db_constraint->constraint_name;
                         // RESTRICT is the default, leave it implicit if possible
                         if (strcasecmp($db_constraint->delete_rule, 'restrict') !== 0) {
                             $node_fkey['onDelete'] = str_replace(' ', '_', $db_constraint->delete_rule);
                         }
                         if (strcasecmp($db_constraint->update_rule, 'restrict') !== 0) {
                             $node_fkey['onUpdate'] = str_replace(' ', '_', $db_constraint->update_rule);
                         }
                     } else {
                         var_dump($db_constraint);
                         throw new Exception("Unexpected: Foreign key constraint {$db_constraint->constraint_name} has mismatched columns");
                     }
                 } elseif (strcasecmp($db_constraint->constraint_type, 'unique') === 0) {
                     dbsteward::warning("Ignoring UNIQUE constraint '{$db_constraint->constraint_name}' because they are implemented as indices");
                 } elseif (strcasecmp($db_constraint->constraint_type, 'check') === 0) {
                     // @TODO: implement CHECK constraints
                 } else {
                     throw new exception("unknown constraint_type {$db_constraint->constraint_type}");
                 }
             }
             foreach ($db->get_table_grants($db_table, $user) as $db_grant) {
                 dbsteward::info("Analyze table permissions " . $db_table->table_name);
                 $node_grant = $node_table->addChild('grant');
                 $node_grant['operation'] = $db_grant->operations;
                 $node_grant['role'] = self::translate_role_name($user, $doc);
                 if ($db_grant->is_grantable) {
                     $node_grant['with'] = 'GRANT';
                 }
             }
         }
         foreach ($db->get_sequences() as $db_seq) {
             $node_seq = $node_schema->addChild('sequence');
             $node_seq['name'] = $db_seq->name;
             $node_seq['owner'] = 'ROLE_OWNER';
             $node_seq['start'] = $db_seq->start_value;
             $node_seq['min'] = $db_seq->min_value;
             $node_seq['max'] = $db_seq->max_value;
             $node_seq['inc'] = $db_seq->increment;
             $node_seq['cycle'] = $db_seq->cycle ? 'true' : 'false';
             // the sequences table is a special case, since it's not picked up in the tables loop
             $seq_table = $db->get_table(mysql5_sequence::TABLE_NAME);
             foreach ($db->get_table_grants($seq_table, $user) as $db_grant) {
                 $node_grant = $node_seq->addChild('grant');
                 $node_grant['operation'] = $db_grant->operations;
                 $node_grant['role'] = self::translate_role_name($doc, $user);
                 if ($db_grant->is_grantable) {
                     $node_grant['with'] = 'GRANT';
                 }
             }
         }
         foreach ($db->get_functions() as $db_function) {
             dbsteward::info("Analyze function " . $db_function->routine_name);
             $node_fn = $node_schema->addChild('function');
             $node_fn['name'] = $db_function->routine_name;
             $node_fn['owner'] = 'ROLE_OWNER';
             $node_fn['returns'] = $type = $db_function->dtd_identifier;
             if (strcasecmp($type, 'enum') === 0) {
                 $node_fn['returns'] = $enum_type($db_function->routine_name, 'returns', $db->parse_enum_values($db_function->dtd_identifier));
             }
             $node_fn['description'] = $db_function->routine_comment;
             if (isset($db_function->procedure) && $db_function->procedure) {
                 $node_fn['procedure'] = 'true';
             }
             // $node_fn['procedure'] = 'false';
             $eval_type = $db_function->sql_data_access;
             // srsly mysql? is_deterministic varchar(3) not null default '', contains YES or NO
             $determinism = strcasecmp($db_function->is_deterministic, 'YES') === 0 ? 'DETERMINISTIC' : 'NOT DETERMINISTIC';
             $node_fn['cachePolicy'] = mysql5_function::get_cache_policy_from_characteristics($determinism, $eval_type);
             $node_fn['mysqlEvalType'] = str_replace(' ', '_', $eval_type);
             // INVOKER is the default, leave it implicit when possible
             if (strcasecmp($db_function->security_type, 'definer') === 0) {
                 $node_fn['securityDefiner'] = 'true';
             }
             foreach ($db_function->parameters as $param) {
                 $node_param = $node_fn->addChild('functionParameter');
                 // not supported in mysql functions, even though it's provided?
                 // $node_param['direction'] = strtoupper($param->parameter_mode);
                 $node_param['name'] = $param->parameter_name;
                 $node_param['type'] = $type = $param->dtd_identifier;
                 if (strcasecmp($type, 'enum') === 0) {
                     $node_param['type'] = $enum_type($db_function->routine_name, $param->parameter_name, $db->parse_enum_values($param->dtd_identifier));
                 }
                 if (isset($param->direction)) {
                     $node_param['direction'] = $param->direction;
                 }
             }
             $node_def = $node_fn->addChild('functionDefinition', $db_function->routine_definition);
             $node_def['language'] = 'sql';
             $node_def['sqlFormat'] = 'mysql5';
         }
         foreach ($db->get_triggers() as $db_trigger) {
             dbsteward::info("Analyze trigger " . $db_trigger->name);
             $node_trigger = $node_schema->addChild('trigger');
             foreach ((array) $db_trigger as $k => $v) {
                 $node_trigger->addAttribute($k, $v);
             }
             $node_trigger->addAttribute('sqlFormat', 'mysql5');
         }
         foreach ($db->get_views() as $db_view) {
             dbsteward::info("Analyze view " . $db_view->view_name);
             if (!empty($db_view->view_name) && empty($db_view->view_query)) {
                 throw new Exception("Found a view in the database with an empty query. User '{$user}' problaby doesn't have SELECT permissions on tables referenced by the view.");
             }
             $node_view = $node_schema->addChild('view');
             $node_view['name'] = $db_view->view_name;
             $node_view['owner'] = 'ROLE_OWNER';
             $node_view->addChild('viewQuery', $db_view->view_query)->addAttribute('sqlFormat', 'mysql5');
         }
         foreach ($enum_types as $name => $values) {
             $node_type = $node_schema->addChild('type');
             $node_type['type'] = 'enum';
             $node_type['name'] = $name;
             foreach ($values as $v) {
                 $node_type->addChild('enum')->addAttribute('name', $v);
             }
         }
     }
     xml_parser::validate_xml($doc->asXML());
     return xml_parser::format_xml($doc->saveXML());
 }
Ejemplo n.º 2
0
 /**
  * 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());
 }