From 63c4cb3f36c5136a92f0f60ae10dad716b5f425b Mon Sep 17 00:00:00 2001 From: Brion Vibber Date: Thu, 7 Oct 2010 15:14:10 -0700 Subject: [PATCH] Finish patching up the index fetching in new schema stuff for mysql --- db/core.php | 5 +- lib/mysqlschema.php | 202 ++++++++++++++++++++++---------------------- 2 files changed, 105 insertions(+), 102 deletions(-) diff --git a/db/core.php b/db/core.php index 04c53a5853..1633187b4b 100644 --- a/db/core.php +++ b/db/core.php @@ -41,9 +41,10 @@ $schema['profile'] = array( 'primary key' => array('id'), 'indexes' => array( 'profile_nickname_idx' => array('nickname'), - 'FULLTEXT' => array('nickname', 'fullname', 'location', 'bio', 'homepage') // ?? ), -// Any way to specify that this table needs to be myisam if using the fulltext? + 'fulltext indexes' => array( + 'content' => array('nickname', 'fullname', 'location', 'bio', 'homepage') // ?? + ), ); $schema['avatar'] = array( diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index 60c432cd67..9ff7932364 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -72,38 +72,24 @@ class MysqlSchema extends Schema * * 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. * @throws SchemaTableMissingException */ - public function getTableDef($name) + public function getTableDef($table) { - $query = "SELECT * FROM INFORMATION_SCHEMA.COLUMNS " . - "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s' " . - "ORDER BY ORDINAL_POSITION"; - $schema = $this->conn->dsn['database']; - $sql = sprintf($query, $schema, $name); - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - if ($res->numRows() == 0) { - $res->free(); - throw new SchemaTableMissingException("No such table: $name"); - } - - $td = new TableDef(); - - $td->name = $name; - $td->columns = array(); $def = array(); + $hasKeys = false; - $row = array(); + // Pull column data from INFORMATION_SCHEMA + $columns = $this->fetchMetaInfo($table, 'COLUMNS', 'ORDINAL_POSITION'); + if (count($columns) == 0) { + throw new SchemaTableMissingException("No such table: $table"); + } - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + foreach ($columns as $row) { $name = $row['COLUMN_NAME']; $field = array(); @@ -126,19 +112,10 @@ class MysqlSchema extends Schema if ($row['COLUMN_DEFAULT'] !== null) { $field['default'] = $row['COLUMN_DEFAULT']; } - /* - if ($row['COLUMN_KEY'] == 'PRI') { - if (isset($def['primary keys'])) { - $def['primary keys'][] = $name; - } else { - $def['primary keys'][] = array($name); - } - } else if ($row['COLUMN_KEY'] == 'MUL') { - // @fixme - } else if ($row['COLUMN_KEY'] == 'UNI') { - // @fixme + if ($row['COLUMN_KEY'] !== null) { + // We'll need to look up key info... + $hasKeys = true; } - */ if ($row['COLUMN_COMMENT'] !== null) { $field['description'] = $row['COLUMN_COMMENT']; } @@ -157,67 +134,47 @@ class MysqlSchema extends Schema $def['fields'][$name] = $field; } - $res->free(); - - /* - BLURRRRRRF - we need to first pull the list/types of keys - - we could get those and the columns like this: - // this works but is insanely slow! - SELECT CONSTRAINT_NAME AS keyName, - (SELECT GROUP_CONCAT(COLUMN_NAME) - FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS keyColumns - WHERE keyColumns.TABLE_SCHEMA=keyDefs.TABLE_SCHEMA - AND keyColumns.TABLE_NAME=keyDefs.TABLE_NAME - AND keyColumns.CONSTRAINT_NAME=keyDefs.CONSTRAINT_NAME - ORDER BY ORDINAL_POSITION) - FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS keyDefs - WHERE TABLE_SCHEMA='mublog' - AND TABLE_NAME='subscription' - ORDER BY TABLE_NAME, CONSTRAINT_NAME; - - However MySQL's query optimizer is crap here and it does something - insane. ;) Using constants is faster, but won't let us do bulk - fetches. So... maybe go ahead and fetch each table separately. - */ - - // Now fetch the key info... - $query = "SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " . - "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s' " . - "ORDER BY CONSTRAINT_NAME,ORDINAL_POSITION"; - $schema = $this->conn->dsn['database']; - $sql = sprintf($query, $schema, $name); - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - if ($res->numRows() == 0) { - $res->free(); - throw new SchemaTableMissingException("No such table: $name"); - } - $row = array(); - - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { - $keyName = $row['CONSTRAINT_NAME']; - $colName = $row['COLUMN_NAME']; - // @fixme what about prefixes? - if ($keyName == 'PRIMARY') { - if (!isset($def['primary key'])) { - $def['primary key'] = array(); + if ($hasKeys) { + // INFORMATION_SCHEMA's CONSTRAINTS and KEY_COLUMN_USAGE tables give + // good info on primary and unique keys but don't list ANY info on + // multi-value keys, which is lame-o. Sigh. + // + // Let's go old school and use SHOW INDEX :D + // + $keyInfo = $this->fetchIndexInfo($table); + $keys = array(); + foreach ($keyInfo as $row) { + $name = $row['Key_name']; + $column = $row['Column_name']; + + if (!isset($keys[$name])) { + $keys[$name] = array(); + } + $keys[$name][] = $column; + + if ($name == 'PRIMARY') { + $type = 'primary key'; + } else if ($row['Non_unique'] == 0) { + $type = 'unique keys'; + } else if ($row['Index_type'] == 'FULLTEXT') { + $type = 'fulltext indexes'; + } else { + $type = 'indexes'; } - $def['primary key'][] = $colName; - } else { - if (!isset($def['indexes'][$keyName])) { - $def['indexes'][$keyName] = array(); + $keyTypes[$name] = $type; + } + + foreach ($keyTypes as $name => $type) { + if ($type == 'primary key') { + // there can be only one + $def[$type] = $keys[$name]; + } else { + $def[$type][$name] = $keys[$name]; } - $def['indexes'][$keyName][] = $colName; } } - $res->free(); - return $td; + return $def; } /** @@ -230,21 +187,66 @@ class MysqlSchema extends Schema function getTableProperties($table, $props) { - $query = "SELECT %s FROM INFORMATION_SCHEMA.TABLES " . + $data = $this->fetchMetaInfo($table, 'TABLES'); + if ($data) { + return $data[0]; + } else { + throw new SchemaTableMissingException("No such table: $table"); + } + } + + /** + * 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_SCHEMA='%s' AND TABLE_NAME='%s'"; $schema = $this->conn->dsn['database']; - $sql = sprintf($query, implode(',', $props), $schema, $table); + $sql = sprintf($query, $infoTable, $schema, $table); + if ($orderBy) { + $sql .= ' ORDER BY ' . $orderBy; + } + return $this->fetchQueryData($sql); + } + + /** + * Pull 'SHOW INDEX' data for the given table. + * + * @param string $table + * @return array of arrays + */ + function fetchIndexInfo($table) + { + $query = "SHOW INDEX FROM `%s`"; + $sql = sprintf($query, $table); + 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(); - $ok = $res->fetchInto($row, DB_FETCHMODE_ASSOC); - $res->free(); - - if ($ok) { - return $row; - } else { - throw new SchemaTableMissingException("No such table: $table"); + while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + $out[] = $row; } + $res->free(); + + return $out; } /** -- 2.39.5