From 2d0807bc1c72c1351f57d7f65386ad7be9d19e83 Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Fri, 8 Oct 2010 16:36:32 -0700 Subject: [PATCH] Starting on adapting postgresql schema class to look stuff up in the new drupalish format... Fetching basic column data and unique indexes. Still needs detail work, multi-value indexes, foreign keys, and distinguishing the primary key. Since we don't get comments and such, for cleaner comparisons we should probably do a filtering on supported features. --- lib/mysqlschema.php | 23 -------- lib/pgsqlschema.php | 131 +++++++++++++++++++++++++++++++++----------- lib/schema.php | 35 ++++++++++++ 3 files changed, 135 insertions(+), 54 deletions(-) diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index 696b2b8d2a..98e276a408 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -243,29 +243,6 @@ class MysqlSchema extends Schema return $this->fetchQueryData($sql); } - /** - * Pull info from the query into a fun-fun array of dooooom - * - * @param string $sql - * @return array of arrays - */ - protected function fetchQueryData($sql) - { - $res = $this->conn->query($sql); - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - $out = array(); - $row = array(); - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { - $out[] = $row; - } - $res->free(); - - return $out; - } - /** * Creates a table with the given names and columns. * diff --git a/lib/pgsqlschema.php b/lib/pgsqlschema.php index 2d0f609836..1c6d12b944 100644 --- a/lib/pgsqlschema.php +++ b/lib/pgsqlschema.php @@ -42,6 +42,7 @@ if (!defined('STATUSNET')) { * @package StatusNet * @author Evan Prodromou * @author Brenda Wallace + * @author Brion Vibber * @license http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0 * @link http://status.net/ */ @@ -50,57 +51,104 @@ class PgsqlSchema extends Schema { /** - * Returns a TableDef object for the table + * Returns a table definition array for the table * in the schema with the given name. * * Throws an exception if the table is not found. * - * @param string $name Name of the table to get + * @param string $table Name of the table to get * - * @return TableDef tabledef for that table. + * @return array tabledef for that table. */ - public function getTableDef($name) + public function getTableDef($table) { - $res = $this->conn->query("SELECT *, column_default as default, is_nullable as Null, - udt_name as Type, column_name AS Field from INFORMATION_SCHEMA.COLUMNS where table_name = '$name'"); + $def = array(); + $hasKeys = false; - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + // Pull column data from INFORMATION_SCHEMA + $columns = $this->fetchMetaInfo($table, 'columns', 'ordinal_position'); + if (count($columns) == 0) { + throw new SchemaTableMissingException("No such table: $table"); } - $td = new TableDef(); + foreach ($columns as $row) { - $td->name = $name; - $td->columns = array(); - - if ($res->numRows() == 0 ) { - throw new Exception('no such table'); //pretend to be the msyql error. yeah, this sucks. - } - $row = array(); + $name = $row['column_name']; + $field = array(); - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { - $cd = new ColumnDef(); + // ?? + list($type, $size) = $this->reverseMapType($row['udt_name']); + $field['type'] = $type; + if ($size !== null) { + $field['size'] = $size; + } - $cd->name = $row['field']; + if ($type == 'char' || $type == 'varchar') { + if ($row['character_maximum_length'] !== null) { + $field['length'] = intval($row['character_maximum_length']); + } + } + if ($type == 'numeric') { + // Other int types may report these values, but they're irrelevant. + // Just ignore them! + if ($row['numeric_precision'] !== null) { + $field['precision'] = intval($row['numeric_precision']); + } + if ($row['numeric_scale'] !== null) { + $field['scale'] = intval($row['numeric_scale']); + } + } + if ($row['is_nullable'] == 'NO') { + $field['not null'] = true; + } + if ($row['column_default'] !== null) { + $field['default'] = $row['column_default']; + if ($this->isNumericType($type)) { + $field['default'] = intval($field['default']); + } + } - $packed = $row['type']; + $def['fields'][$name] = $field; + } - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; + // Pull constraint data from INFORMATION_SCHEMA + // @fixme also find multi-val indexes + // @fixme distinguish the primary key + // @fixme pull foreign key references + $keyColumns = $this->fetchMetaInfo($table, 'key_column_usage', 'constraint_name,ordinal_position'); + $keys = array(); + + foreach ($keyColumns as $row) { + $keyName = $row['constraint_name']; + $keyCol = $row['column_name']; + if (!isset($keys[$keyName])) { + $keys[$keyName] = array(); } + $keys[$keyName][] = $keyCol; + } - $cd->nullable = ($row['null'] == 'YES') ? true : false; - $cd->key = $row['Key']; - $cd->default = $row['default']; - $cd->extra = $row['Extra']; + foreach ($keys as $keyName => $cols) { + $def['unique indexes'][$keyName] = $cols; + } + return $def; + } - $td->columns[] = $cd; + /** + * Pull some INFORMATION.SCHEMA data for the given table. + * + * @param string $table + * @return array of arrays + */ + function fetchMetaInfo($table, $infoTable, $orderBy=null) + { + $query = "SELECT * FROM information_schema.%s " . + "WHERE table_name='%s'"; + $sql = sprintf($query, $infoTable, $table); + if ($orderBy) { + $sql .= ' ORDER BY ' . $orderBy; } - return $td; + return $this->fetchQueryData($sql); } /** @@ -360,4 +408,25 @@ class PgsqlSchema extends Schema } } + /** + * Map a native type back to an independent type + size + * + * @param string $type + * @return array ($type, $size) -- $size may be null + */ + protected function reverseMapType($type) + { + $type = strtolower($type); + $map = array( + 'int4' => array('int', null), + 'int8' => array('int', 'big'), + 'bytea' => array('blob', null), + ); + if (isset($map[$type])) { + return $map[$type]; + } else { + return array($type, null); + } + } + } diff --git a/lib/schema.php b/lib/schema.php index 5868627ed7..5085ab6fe5 100644 --- a/lib/schema.php +++ b/lib/schema.php @@ -515,6 +515,17 @@ class Schema } } + /** + * Map a native type back to an independent type + size + * + * @param string $type + * @return array ($type, $size) -- $size may be null + */ + protected function reverseMapType($type) + { + return array($type, null); + } + /** * Convert an old-style set of ColumnDef objects into the current * Drupal-style schema definition array, for backwards compatibility @@ -590,6 +601,30 @@ class Schema $known = array('int', 'serial', 'numeric'); return in_array($type, $known); } + + /** + * Pull info from the query into a fun-fun array of dooooom + * + * @param string $sql + * @return array of arrays + */ + protected function fetchQueryData($sql) + { + $res = $this->conn->query($sql); + if (PEAR::isError($res)) { + throw new Exception($res->getMessage()); + } + + $out = array(); + $row = array(); + while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + $out[] = $row; + } + $res->free(); + + return $out; + } + } class SchemaTableMissingException extends Exception -- 2.39.5