X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=lib%2Fmysqlschema.php;h=435ba4e3a95f57f3ca0ba314db4fb483d0ca0b83;hb=d81b25729005973e6daa27fc787a042f05a12c94;hp=60c432cd67acb4a53730c2f85954483cd6d8da4d;hpb=a680e1798c356a7be8183153cf5058be1c6dcd8a;p=quix0rs-gnu-social.git diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index 60c432cd67..435ba4e3a9 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -72,83 +72,78 @@ 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(); - if ($row['DATA_TYPE'] !== null) { - $field['type'] = $row['DATA_TYPE']; - } - if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) { - $field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']); - } - if ($row['NUMERIC_PRECISION'] !== null) { - $field['precision'] = intval($row['NUMERIC_PRECISION']); + // warning -- 'unsigned' attr on numbers isn't given in DATA_TYPE and friends. + // It is stuck in on COLUMN_TYPE though (eg 'bigint(20) unsigned') + $field['type'] = $type = $row['DATA_TYPE']; + + if ($type == 'char' || $type == 'varchar') { + if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) { + $field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']); + } } - if ($row['NUMERIC_SCALE'] !== null) { - $field['scale'] = intval($row['NUMERIC_SCALE']); + if ($type == 'decimal') { + // 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 ($row['COLUMN_KEY'] == 'PRI') { - if (isset($def['primary keys'])) { - $def['primary keys'][] = $name; + // Hack for timestamp cols + if ($type == 'timestamp' && $row['COLUMN_DEFAULT'] == 'CURRENT_TIMESTAMP') { + // skip } else { - $def['primary keys'][] = array($name); + $field['default'] = $row['COLUMN_DEFAULT']; + if ($this->isNumericType($type)) { + $field['default'] = intval($field['default']); + } } - } else if ($row['COLUMN_KEY'] == 'MUL') { - // @fixme - } else if ($row['COLUMN_KEY'] == 'UNI') { - // @fixme } - */ - if ($row['COLUMN_COMMENT'] !== null) { + if ($row['COLUMN_KEY'] !== null) { + // We'll need to look up key info... + $hasKeys = true; + } + if ($row['COLUMN_COMMENT'] !== null && $row['COLUMN_COMMENT'] != '') { $field['description'] = $row['COLUMN_COMMENT']; } - // $row['EXTRA'] may contain 'autoincrement' - // ^ type=serial? - // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP' - // ^ ...... how to specify? - // these seem to be the only values in curent use - + $extra = $row['EXTRA']; + if ($extra) { + if (preg_match('/(^|\s)auto_increment(\s|$)/i', $extra)) { + $field['auto_increment'] = true; + } + // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP' + // ^ ...... how to specify? + } + if ($row['CHARACTER_SET_NAME'] !== null) { // @fixme check against defaults? //$def['charset'] = $row['CHARACTER_SET_NAME']; @@ -157,67 +152,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(); } - $def['primary key'][] = $colName; - } else { - if (!isset($def['indexes'][$keyName])) { - $def['indexes'][$keyName] = 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'; + } + $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,102 +205,81 @@ class MysqlSchema extends Schema function getTableProperties($table, $props) { - $query = "SELECT %s FROM INFORMATION_SCHEMA.TABLES " . - "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'"; - $schema = $this->conn->dsn['database']; - $sql = sprintf($query, implode(',', $props), $schema, $table); - $res = $this->conn->query($sql); - - $row = array(); - $ok = $res->fetchInto($row, DB_FETCHMODE_ASSOC); - $res->free(); - - if ($ok) { - return $row; + $data = $this->fetchMetaInfo($table, 'TABLES'); + if ($data) { + return $data[0]; } else { throw new SchemaTableMissingException("No such table: $table"); } } /** - * Creates a table with the given names and columns. + * Pull some INFORMATION.SCHEMA data for the given table. * - * @param string $name Name of the table - * @param array $columns Array of ColumnDef objects - * for new table. - * - * @return boolean success flag + * @param string $table + * @return array of arrays */ - - public function createTable($name, $columns) + function fetchMetaInfo($table, $infoTable, $orderBy=null) { - $uniques = array(); - $primary = array(); - $indices = 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); - } - - $idx = $this->_indexList($columns); - - if ($idx['primary']) { - $sql .= ",\nconstraint primary key (" . implode(',', $idx['primary']) . ")"; - } - - foreach ($idx['uniques'] as $u) { - $key = $this->_uniqueKey($name, $u); - $sql .= ",\nunique index $key ($u)"; - } - - foreach ($idx['indices'] as $i) { - $key = $this->_key($name, $i); - $sql .= ",\nindex $key ($i)"; + $query = "SELECT * FROM INFORMATION_SCHEMA.%s " . + "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'"; + $schema = $this->conn->dsn['database']; + $sql = sprintf($query, $infoTable, $schema, $table); + if ($orderBy) { + $sql .= ' ORDER BY ' . $orderBy; } + return $this->fetchQueryData($sql); + } - $sql .= ") ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin; "; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } + /** + * 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); + } - return true; + /** + * Append an SQL statement with an index definition for a full-text search + * index over one or more columns on a table. + * + * @param array $statements + * @param string $table + * @param string $name + * @param array $def + */ + function appendCreateFulltextIndex(array &$statements, $table, $name, array $def) + { + $statements[] = "CREATE FULLTEXT INDEX $name ON $table " . $this->buildIndexList($def); } /** - * Look over a list of column definitions and list up which - * indices will be present + * Close out a 'create table' SQL statement. + * + * @param string $name + * @param array $def + * @return string; + * + * @fixme ENGINE may need to be set differently in some cases, + * such as to support fulltext index. */ - private function _indexList(array $columns) + function endCreateTable($name, array $def) { - $list = array('uniques' => array(), - 'primary' => array(), - 'indices' => array()); - foreach ($columns as $cd) { - switch ($cd->key) { - case 'UNI': - $list['uniques'][] = $cd->name; - break; - case 'PRI': - $list['primary'][] = $cd->name; - break; - case 'MUL': - $list['indices'][] = $cd->name; - break; - } + $engine = $this->preferredEngine($def); + return ") ENGINE=$engine CHARACTER SET utf8 COLLATE utf8_bin"; + } + + function preferredEngine($def) + { + if (!empty($def['fulltext indexes'])) { + return 'MyISAM'; } - return $list; + return 'InnoDB'; } /** @@ -345,152 +299,46 @@ class MysqlSchema extends Schema } /** - * Ensures that a table exists with the given - * name and the given column definitions. - * - * If the table does not yet exist, it will - * create the table. If it does exist, it will - * alter the table to match the column definitions. + * MySQL doesn't take 'DROP CONSTRAINT', need to treat primary keys as + * if they were indexes here, but can use 'PRIMARY KEY' special name. * - * @param string $tableName name of the table - * @param array $columns array of ColumnDef - * objects for the table - * - * @return boolean success flag + * @param array $phrase */ - - public function ensureTable($tableName, $columns) + function appendAlterDropPrimary(array &$phrase) { - // XXX: DB engine portability -> toilet - - try { - $td = $this->getTableDef($tableName); - } catch (SchemaTableMissingException $e) { - return $this->createTable($tableName, $columns); - } - - $cur = $this->_names($td->columns); - $new = $this->_names($columns); - - $dropIndex = array(); - $toadd = array_diff($new, $cur); - $todrop = array_diff($cur, $new); - $same = array_intersect($new, $cur); - $tomod = array(); - $addIndex = array(); - $tableProps = array(); - - foreach ($same as $m) { - $curCol = $this->_byName($td->columns, $m); - $newCol = $this->_byName($columns, $m); - - if (!$newCol->equals($curCol)) { - $tomod[] = $newCol->name; - continue; - } - - // Earlier versions may have accidentally left tables at default - // charsets which might be latin1 or other freakish things. - if ($this->_isString($curCol)) { - if ($curCol->charset != 'utf8') { - $tomod[] = $newCol->name; - continue; - } - } - } - - // Find any indices we have to change... - $curIdx = $this->_indexList($td->columns); - $newIdx = $this->_indexList($columns); - - if ($curIdx['primary'] != $newIdx['primary']) { - if ($curIdx['primary']) { - $dropIndex[] = 'drop primary key'; - } - if ($newIdx['primary']) { - $keys = implode(',', $newIdx['primary']); - $addIndex[] = "add constraint primary key ($keys)"; - } - } - - $dropUnique = array_diff($curIdx['uniques'], $newIdx['uniques']); - $addUnique = array_diff($newIdx['uniques'], $curIdx['uniques']); - foreach ($dropUnique as $columnName) { - $dropIndex[] = 'drop key ' . $this->_uniqueKey($tableName, $columnName); - } - foreach ($addUnique as $columnName) { - $addIndex[] = 'add constraint unique key ' . $this->_uniqueKey($tableName, $columnName) . " ($columnName)";; - } + $phrase[] = 'DROP PRIMARY KEY'; + } - $dropMultiple = array_diff($curIdx['indices'], $newIdx['indices']); - $addMultiple = array_diff($newIdx['indices'], $curIdx['indices']); - foreach ($dropMultiple as $columnName) { - $dropIndex[] = 'drop key ' . $this->_key($tableName, $columnName); - } - foreach ($addMultiple as $columnName) { - $addIndex[] = 'add key ' . $this->_key($tableName, $columnName) . " ($columnName)"; - } + /** + * MySQL doesn't take 'DROP CONSTRAINT', need to treat unique keys as + * if they were indexes here. + * + * @param array $phrase + * @param $keyName MySQL + */ + function appendAlterDropUnique(array &$phrase, $keyName) + { + $phrase[] = 'DROP INDEX ' . $keyName; + } + /** + * Throw some table metadata onto the ALTER TABLE if we have a mismatch + * in expected type, collation. + */ + function appendAlterExtras(array &$phrase, $tableName, array $def) + { // Check for table properties: make sure we're using a sane // engine type and charset/collation. // @fixme make the default engine configurable? $oldProps = $this->getTableProperties($tableName, array('ENGINE', 'TABLE_COLLATION')); - if (strtolower($oldProps['ENGINE']) != 'innodb') { - $tableProps['ENGINE'] = 'InnoDB'; + $engine = $this->preferredEngine($def); + if (strtolower($oldProps['ENGINE']) != strtolower($engine)) { + $phrase[] = "ENGINE=$engine"; } if (strtolower($oldProps['TABLE_COLLATION']) != 'utf8_bin') { - $tableProps['DEFAULT CHARSET'] = 'utf8'; - $tableProps['COLLATE'] = 'utf8_bin'; - } - - if (count($dropIndex) + count($toadd) + count($todrop) + count($tomod) + count($addIndex) + count($tableProps) == 0) { - // nothing to do - return true; - } - - // For efficiency, we want this all in one - // query, instead of using our methods. - - $phrase = array(); - - foreach ($dropIndex as $indexSql) { - $phrase[] = $indexSql; - } - - foreach ($toadd as $columnName) { - $cd = $this->_byName($columns, $columnName); - - $phrase[] = 'ADD COLUMN ' . $this->_columnSql($cd); - } - - foreach ($todrop as $columnName) { - $phrase[] = 'DROP COLUMN ' . $columnName; - } - - foreach ($tomod as $columnName) { - $cd = $this->_byName($columns, $columnName); - - $phrase[] = 'MODIFY COLUMN ' . $this->_columnSql($cd); - } - - foreach ($addIndex as $indexSql) { - $phrase[] = $indexSql; - } - - foreach ($tableProps as $key => $val) { - $phrase[] = "$key=$val"; - } - - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); - - common_log(LOG_DEBUG, __METHOD__ . ': ' . $sql); - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $phrase[] = 'DEFAULT CHARSET=utf8'; + $phrase[] = 'COLLATE=utf8_bin'; } - - return true; } /** @@ -517,19 +365,16 @@ class MysqlSchema extends Schema function columnSql(array $cd) { $line = array(); - $line[] = parent::_columnSql($cd); + $line[] = parent::columnSql($cd); - if ($cd['type'] == 'serial') { + // This'll have been added from our transform of 'serial' type + if (!empty($cd['auto_increment'])) { $line[] = 'auto_increment'; } - if (!empty($cd['extra'])) { - $line[] = $cd['extra']; // hisss boooo - } - if (!empty($cd['description'])) { $line[] = 'comment'; - $line[] = $this->quote($cd['description']); + $line[] = $this->quoteValue($cd['description']); } return implode(' ', $line); @@ -566,9 +411,44 @@ class MysqlSchema extends Schema $vals = array_map(array($this, 'quote'), $column['enum']); return 'enum(' . implode(',', $vals) . ')'; } else if ($this->_isString($column)) { - return parent::typeAndSize($column) . ' CHARSET utf8'; + $col = parent::typeAndSize($column); + if (!empty($column['charset'])) { + $col .= ' CHARSET ' . $column['charset']; + } + if (!empty($column['collate'])) { + $col .= ' COLLATE ' . $column['collate']; + } + return $col; } else { return parent::typeAndSize($column); } } + + /** + * 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) { + if ($col['type'] == 'serial') { + $col['type'] = 'int'; + $col['auto_increment'] = true; + } + if ($col['type'] == 'datetime' && isset($col['default']) && $col['default'] == 'CURRENT_TIMESTAMP') { + $col['type'] = 'timestamp'; + } + $col['type'] = $this->mapType($col); + unset($col['size']); + } + if (!common_config('db', 'mysql_foreign_keys')) { + unset($tableDef['foreign keys']); + } + return $tableDef; + } }