throw new SchemaTableMissingException("No such table: $table");
}
+ // We'll need to match up fields by ordinal reference
+ $orderedFields = array();
+
foreach ($columns as $row) {
$name = $row['column_name'];
+ $orderedFields[$row['ordinal_position']] = $name;
+
$field = array();
// ??
$def['fields'][$name] = $field;
}
- // Pull constraint data from INFORMATION_SCHEMA
- // @fixme also find multi-val indexes
- // @fixme distinguish the primary key
- // @fixme pull foreign key references
+ // Pulling index info from pg_class & pg_index
+ // This can give us primary & unique key info, but not foreign key constraints
+ // so we exclude them and pick them up later.
+ $indexInfo = $this->getIndexInfo($table);
+ foreach ($indexInfo as $row) {
+ $keyName = $row['key_name'];
+
+ // Dig the column references out!
+ //
+ // These are inconvenient arrays with partial references to the
+ // pg_att table, but since we've already fetched up the column
+ // info on the current table, we can look those up locally.
+ $cols = array();
+ $colPositions = explode(' ', $row['indkey']);
+ foreach ($colPositions as $ord) {
+ if ($ord == 0) {
+ $cols[] = 'FUNCTION'; // @fixme
+ } else {
+ $cols[] = $orderedFields[$ord];
+ }
+ }
+
+ $def['indexes'][$keyName] = $cols;
+ }
+
+ // Pull constraint data from INFORMATION_SCHEMA:
+ // Primary key, unique keys, foreign keys
$keyColumns = $this->fetchMetaInfo($table, 'key_column_usage', 'constraint_name,ordinal_position');
$keys = array();
}
foreach ($keys as $keyName => $cols) {
- $def['unique indexes'][$keyName] = $cols;
+ // name hack -- is this reliable?
+ if ($keyName == "{$table}_pkey") {
+ $def['primary key'] = $cols;
+ } else if (preg_match("/^{$table}_(.*)_fkey$/", $keyName, $matches)) {
+ $fkey = $this->getForeignKeyInfo($table, $keyName);
+ $colMap = array_combine($cols, $fkey['col_names']);
+ $def['foreign keys'][$keyName] = array($fkey['table_name'], $colMap);
+ } else {
+ $def['unique keys'][$keyName] = $cols;
+ }
}
return $def;
}
}
/**
- * Creates a table with the given names and columns.
- *
- * @param string $name Name of the table
- * @param array $columns Array of ColumnDef objects
- * for new table.
- *
- * @return boolean success flag
+ * Pull some PG-specific index info
+ * @param string $table
+ * @return array of arrays
*/
-
- public function createTable($name, $columns)
+ function getIndexInfo($table)
{
- $uniques = array();
- $primary = array();
- $indices = array();
- $onupdate = array();
-
- $sql = "CREATE TABLE $name (\n";
-
- for ($i = 0; $i < count($columns); $i++) {
-
- $cd =& $columns[$i];
-
- if ($i > 0) {
- $sql .= ",\n";
- }
-
- $sql .= $this->_columnSql($cd);
- switch ($cd->key) {
- case 'UNI':
- $uniques[] = $cd->name;
- break;
- case 'PRI':
- $primary[] = $cd->name;
- break;
- case 'MUL':
- $indices[] = $cd->name;
- break;
- }
- }
+ $query = 'SELECT ' .
+ '(SELECT relname FROM pg_class WHERE oid=indexrelid) AS key_name, ' .
+ '* FROM pg_index ' .
+ 'WHERE indrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' .
+ 'AND indisprimary=\'f\' AND indisunique=\'f\' ' .
+ 'ORDER BY indrelid, indexrelid';
+ $sql = sprintf($query, $table);
+ return $this->fetchQueryData($sql);
+ }
- if (count($primary) > 0) { // it really should be...
- $sql .= ",\n PRIMARY KEY (" . implode(',', $primary) . ")";
+ /**
+ * Column names from the foreign table can be resolved with a call to getTableColumnNames()
+ * @param <type> $table
+ * @return array array of rows with keys: fkey_name, table_name, table_id, col_names (array of strings)
+ */
+ function getForeignKeyInfo($table, $constraint_name)
+ {
+ // In a sane world, it'd be easier to query the column names directly.
+ // But it's pretty hard to work with arrays such as col_indexes in direct SQL here.
+ $query = 'SELECT ' .
+ '(SELECT relname FROM pg_class WHERE oid=confrelid) AS table_name, ' .
+ 'confrelid AS table_id, ' .
+ '(SELECT indkey FROM pg_index WHERE indexrelid=conindid) AS col_indexes ' .
+ 'FROM pg_constraint ' .
+ 'WHERE conrelid=(SELECT oid FROM pg_class WHERE relname=\'%s\') ' .
+ 'AND conname=\'%s\' ' .
+ 'AND contype=\'f\'';
+ $sql = sprintf($query, $table, $constraint_name);
+ $data = $this->fetchQueryData($sql);
+ if (count($data) < 1) {
+ throw new Exception("Could not find foreign key " . $constraint_name . " on table " . $table);
}
- $sql .= "); ";
-
-
- foreach ($uniques as $u) {
- $sql .= "\n CREATE index {$name}_{$u}_idx ON {$name} ($u); ";
- }
+ $row = $data[0];
+ return array(
+ 'table_name' => $row['table_name'],
+ 'col_names' => $this->getTableColumnNames($row['table_id'], $row['col_indexes'])
+ );
+ }
- foreach ($indices as $i) {
- $sql .= "CREATE index {$name}_{$i}_idx ON {$name} ($i)";
+ /**
+ *
+ * @param int $table_id
+ * @param array $col_indexes
+ * @return array of strings
+ */
+ function getTableColumnNames($table_id, $col_indexes)
+ {
+ $indexes = array_map('intval', explode(' ', $col_indexes));
+ $query = 'SELECT attnum AS col_index, attname AS col_name ' .
+ 'FROM pg_attribute where attrelid=%d ' .
+ 'AND attnum IN (%s)';
+ $sql = sprintf($query, $table_id, implode(',', $indexes));
+ $data = $this->fetchQueryData($sql);
+
+ $byId = array();
+ foreach ($data as $row) {
+ $byId[$row['col_index']] = $row['col_name'];
}
- $res = $this->conn->query($sql);
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage(). ' SQL was '. $sql);
+ $out = array();
+ foreach ($indexes as $id) {
+ $out[] = $byId[$id];
}
-
- return true;
+ return $out;
}
/**
return $type;
}
- /**
- * Modifies a column in the schema.
- *
- * The name must match an existing column and table.
- *
- * @param string $table name of the table
- * @param ColumnDef $columndef new definition of the column.
- *
- * @return boolean success flag
- */
-
- public function modifyColumn($table, $columndef)
- {
- $sql = "ALTER TABLE $table ALTER COLUMN TYPE " .
- $this->_columnSql($columndef);
-
- $res = $this->conn->query($sql);
-
- if (PEAR::isError($res)) {
- throw new Exception($res->getMessage());
- }
-
- return true;
- }
-
/**
* Return the proper SQL for creating or
* altering a column.
* Appropriate for use in CREATE TABLE or
* ALTER TABLE statements.
*
- * @param string $tableName
- * @param array $tableDef
- * @param string $columnName
* @param array $cd column to create
*
* @return string correct SQL for that column
*/
- function columnSql($name, array $cd)
+ function columnSql(array $cd)
{
$line = array();
- $line[] = parent::_columnSql($cd);
+ $line[] = parent::columnSql($cd);
+ /*
if ($table['foreign keys'][$name]) {
foreach ($table['foreign keys'][$name] as $foreignTable => $foreignColumn) {
$line[] = 'references';
- $line[] = $this->quoteId($foreignTable);
- $line[] = '(' . $this->quoteId($foreignColumn) . ')';
+ $line[] = $this->quoteIdentifier($foreignTable);
+ $line[] = '(' . $this->quoteIdentifier($foreignColumn) . ')';
}
}
+ */
return implode(' ', $line);
}
$oldType = $this->mapType($old);
$newType = $this->mapType($cd);
if ($oldType != $newType) {
- $phrase[] .= $prefix . 'TYPE ' . $newType;
+ $phrase[] = $prefix . 'TYPE ' . $newType;
}
if (!empty($old['not null']) && empty($cd['not null'])) {
- $phrase[] .= $prefix . 'DROP NOT NULL';
+ $phrase[] = $prefix . 'DROP NOT NULL';
} else if (empty($old['not null']) && !empty($cd['not null'])) {
- $phrase[] .= $prefix . 'SET NOT NULL';
+ $phrase[] = $prefix . 'SET NOT NULL';
}
if (isset($old['default']) && !isset($cd['default'])) {
- $phrase[] . $prefix . 'DROP DEFAULT';
+ $phrase[] = $prefix . 'DROP DEFAULT';
} else if (!isset($old['default']) && isset($cd['default'])) {
- $phrase[] . $prefix . 'SET DEFAULT ' . $this->quoteDefaultValue($cd);
+ $phrase[] = $prefix . 'SET DEFAULT ' . $this->quoteDefaultValue($cd);
}
}
}
}
+ /**
+ * Filter the given table definition array to match features available
+ * in this database.
+ *
+ * This lets us strip out unsupported things like comments, foreign keys,
+ * or type variants that we wouldn't get back from getTableDef().
+ *
+ * @param array $tableDef
+ */
+ function filterDef(array $tableDef)
+ {
+ foreach ($tableDef['fields'] as $name => &$col) {
+ // No convenient support for field descriptions
+ unset($col['description']);
+
+ if (isset($col['size'])) {
+ // Don't distinguish between tinyint and int.
+ if ($col['size'] == 'tiny' && $col['type'] == 'int') {
+ unset($col['size']);
+ }
+ }
+ }
+ return $tableDef;
+ }
+
}