X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=lib%2Fmysqlschema.php;h=c3d3501c74cff52f6018cc73ac8120be65970a75;hb=31b29fde50e9664e1b70064c043879ce87553883;hp=485096ac425d4dea1dc48f7ab3d5cfb3040d1a83;hpb=dc09453a77f33c4dfdff306321ce93cf5fbd2d57;p=quix0rs-gnu-social.git diff --git a/lib/mysqlschema.php b/lib/mysqlschema.php index 485096ac42..c3d3501c74 100644 --- a/lib/mysqlschema.php +++ b/lib/mysqlschema.php @@ -50,21 +50,6 @@ class MysqlSchema extends Schema static $_single = null; protected $conn = null; - /** - * Constructor. Only run once for singleton object. - */ - - protected function __construct() - { - // XXX: there should be an easier way to do this. - $user = new User(); - - $this->conn = $user->getDatabaseConnection(); - - $user->free(); - - unset($user); - } /** * Main public entry point. Use this to get @@ -87,414 +72,272 @@ 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) { - $res = $this->conn->query('DESCRIBE ' . $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(); - - $td->name = $name; - $td->columns = array(); - - $row = array(); + foreach ($columns as $row) { - while ($res->fetchInto($row, DB_FETCHMODE_ASSOC)) { + $name = $row['COLUMN_NAME']; + $field = array(); - $cd = new ColumnDef(); + // 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']; - $cd->name = $row['Field']; - - $packed = $row['Type']; + if ($type == 'char' || $type == 'varchar') { + if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) { + $field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']); + } + } + 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) { + // Hack for timestamp cols + if ($type == 'timestamp' && $row['COLUMN_DEFAULT'] == 'CURRENT_TIMESTAMP') { + // skip + } else { + $field['default'] = $row['COLUMN_DEFAULT']; + if ($this->isNumericType($type)) { + $field['default'] = intval($field['default']); + } + } + } + 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']; + } - if (preg_match('/^(\w+)\((\d+)\)$/', $packed, $match)) { - $cd->type = $match[1]; - $cd->size = $match[2]; - } else { - $cd->type = $packed; + $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? } - $cd->nullable = ($row['Null'] == 'YES') ? true : false; - $cd->key = $row['Key']; - $cd->default = $row['Default']; - $cd->extra = $row['Extra']; + if ($row['CHARACTER_SET_NAME'] !== null) { + // @fixme check against defaults? + //$def['charset'] = $row['CHARACTER_SET_NAME']; + //$def['collate'] = $row['COLLATION_NAME']; + } - $td->columns[] = $cd; + $def['fields'][$name] = $field; } - return $td; - } - - /** - * Gets a ColumnDef object for a single column. - * - * Throws an exception if the table is not found. - * - * @param string $table name of the table - * @param string $column name of the column - * - * @return ColumnDef definition of the column or null - * if not found. - */ - - public function getColumnDef($table, $column) - { - $td = $this->getTableDef($table); + 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'; + } + $keyTypes[$name] = $type; + } - foreach ($td->columns as $cd) { - if ($cd->name == $column) { - return $cd; + foreach ($keyTypes as $name => $type) { + if ($type == 'primary key') { + // there can be only one + $def[$type] = $keys[$name]; + } else { + $def[$type][$name] = $keys[$name]; + } } } - - return null; + return $def; } /** - * Creates a table with the given names and columns. + * Pull the given table properties from INFORMATION_SCHEMA. + * Most of the good stuff is MySQL extensions. * - * @param string $name Name of the table - * @param array $columns Array of ColumnDef objects - * for new table. - * - * @return boolean success flag + * @return array + * @throws Exception if table info can't be looked up */ - public function createTable($name, $columns) + function getTableProperties($table, $props) { - $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); - - switch ($cd->key) { - case 'UNI': - $uniques[] = $cd->name; - break; - case 'PRI': - $primary[] = $cd->name; - break; - case 'MUL': - $indices[] = $cd->name; - break; - } - } - - if (count($primary) > 0) { // it really should be... - $sql .= ",\nconstraint primary key (" . implode(',', $primary) . ")"; - } - - foreach ($uniques as $u) { - $sql .= ",\nunique index {$name}_{$u}_idx ($u)"; - } - - foreach ($indices as $i) { - $sql .= ",\nindex {$name}_{$i}_idx ($i)"; - } - - $sql .= "); "; - - common_log(LOG_INFO, $sql); - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $data = $this->fetchMetaInfo($table, 'TABLES'); + if ($data) { + return $data[0]; + } else { + throw new SchemaTableMissingException("No such table: $table"); } - - return true; } /** - * Drops a table from the schema + * Pull some INFORMATION.SCHEMA data for the given table. * - * Throws an exception if the table is not found. - * - * @param string $name Name of the table to drop - * - * @return boolean success flag + * @param string $table + * @return array of arrays */ - - public function dropTable($name) + function fetchMetaInfo($table, $infoTable, $orderBy=null) { - $res = $this->conn->query("DROP TABLE $name"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $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 true; + return $this->fetchQueryData($sql); } /** - * Adds an index to a table. - * - * If no name is provided, a name will be made up based - * on the table name and column names. - * - * Throws an exception on database error, esp. if the table - * does not exist. + * Pull 'SHOW INDEX' data for the given table. * - * @param string $table Name of the table - * @param array $columnNames Name of columns to index - * @param string $name (Optional) name of the index - * - * @return boolean success flag + * @param string $table + * @return array of arrays */ - - public function createIndex($table, $columnNames, $name=null) + function fetchIndexInfo($table) { - if (!is_array($columnNames)) { - $columnNames = array($columnNames); - } - - if (empty($name)) { - $name = "$table_".implode("_", $columnNames)."_idx"; - } - - $res = $this->conn->query("ALTER TABLE $table ". - "ADD INDEX $name (". - implode(",", $columnNames).")"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + $query = "SHOW INDEX FROM `%s`"; + $sql = sprintf($query, $table); + return $this->fetchQueryData($sql); } /** - * Drops a named index from a table. - * - * @param string $table name of the table the index is on. - * @param string $name name of the index + * Append an SQL statement with an index definition for a full-text search + * index over one or more columns on a table. * - * @return boolean success flag + * @param array $statements + * @param string $table + * @param string $name + * @param array $def */ - - public function dropIndex($table, $name) + function appendCreateFulltextIndex(array &$statements, $table, $name, array $def) { - $res = $this->conn->query("ALTER TABLE $table DROP INDEX $name"); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + $statements[] = "CREATE FULLTEXT INDEX $name ON $table " . $this->buildIndexList($def); } /** - * Adds a column to a table + * Close out a 'create table' SQL statement. * - * @param string $table name of the table - * @param ColumnDef $columndef Definition of the new - * column. + * @param string $name + * @param array $def + * @return string; * - * @return boolean success flag + * @fixme ENGINE may need to be set differently in some cases, + * such as to support fulltext index. */ - - public function addColumn($table, $columndef) + function endCreateTable($name, array $def) { - $sql = "ALTER TABLE $table ADD COLUMN " . $this->_columnSql($columndef); - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); + $engine = $this->preferredEngine($def); + return ") ENGINE=$engine CHARACTER SET utf8 COLLATE utf8_bin"; + } + + function preferredEngine($def) + { + if (!empty($def['fulltext indexes'])) { + return 'MyISAM'; } - - return true; + return 'InnoDB'; } /** - * 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 + * Get the unique index key name for a given column on this table */ - - public function modifyColumn($table, $columndef) + function _uniqueKey($tableName, $columnName) { - $sql = "ALTER TABLE $table MODIFY COLUMN " . - $this->_columnSql($columndef); - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + return $this->_key($tableName, $columnName); } /** - * Drops a column from a table - * - * The name must match an existing column. - * - * @param string $table name of the table - * @param string $columnName name of the column to drop - * - * @return boolean success flag + * Get the index key name for a given column on this table */ - - public function dropColumn($table, $columnName) + function _key($tableName, $columnName) { - $sql = "ALTER TABLE $table DROP COLUMN $columnName"; - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + return "{$tableName}_{$columnName}_idx"; } + /** - * Ensures that a table exists with the given - * name and the given column definitions. + * MySQL doesn't take 'DROP CONSTRAINT', need to treat unique keys as + * if they were indexes here. * - * 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. - * - * @param string $tableName name of the table - * @param array $columns array of ColumnDef - * objects for the table - * - * @return boolean success flag + * @param array $phrase + * @param $keyName MySQL */ - - public function ensureTable($tableName, $columns) + function appendAlterDropUnique(array &$phrase, $keyName) { - // XXX: DB engine portability -> toilet - - try { - $td = $this->getTableDef($tableName); - } catch (Exception $e) { - if (preg_match('/no such table/', $e->getMessage())) { - return $this->createTable($tableName, $columns); - } else { - throw $e; - } - } - - $cur = $this->_names($td->columns); - $new = $this->_names($columns); - - $toadd = array_diff($new, $cur); - $todrop = array_diff($cur, $new); - $same = array_intersect($new, $cur); - $tomod = array(); - - foreach ($same as $m) { - $curCol = $this->_byName($td->columns, $m); - $newCol = $this->_byName($columns, $m); - - if (!$newCol->equals($curCol)) { - $tomod[] = $newCol->name; - } - } - - if (count($toadd) + count($todrop) + count($tomod) == 0) { - // nothing to do - return true; - } - - // For efficiency, we want this all in one - // query, instead of using our methods. - - $phrase = array(); - - 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); - } - - $sql = 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $phrase); - - $res = $this->conn->query($sql); - - if (PEAR::isError($res)) { - throw new Exception($res->getMessage()); - } - - return true; + $phrase[] = 'DROP INDEX ' . $keyName; } /** - * Returns the array of names from an array of - * ColumnDef objects. - * - * @param array $cds array of ColumnDef objects - * - * @return array strings for name values + * Throw some table metadata onto the ALTER TABLE if we have a mismatch + * in expected type, collation. */ - - private function _names($cds) + function appendAlterExtras(array &$phrase, $tableName, array $def) { - $names = array(); - - foreach ($cds as $cd) { - $names[] = $cd->name; + // 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')); + $engine = $this->preferredEngine($def); + if (strtolower($oldProps['ENGINE']) != strtolower($engine)) { + $phrase[] = "ENGINE=$engine"; + } + if (strtolower($oldProps['TABLE_COLLATION']) != 'utf8_bin') { + $phrase[] = 'DEFAULT CHARSET=utf8'; + $phrase[] = 'COLLATE=utf8_bin'; } - - return $names; } /** - * Get a ColumnDef from an array matching - * name. - * - * @param array $cds Array of ColumnDef objects - * @param string $name Name of the column - * - * @return ColumnDef matching item or null if no match. + * Is this column a string type? */ - - private function _byName($cds, $name) + private function _isString(array $cd) { - foreach ($cds as $cd) { - if ($cd->name == $name) { - return $cd; - } - } - - return null; + $strings = array('char', 'varchar', 'text'); + return in_array(strtolower($cd['type']), $strings); } /** @@ -509,30 +352,93 @@ class MysqlSchema extends Schema * @return string correct SQL for that column */ - private function _columnSql($cd) + function columnSql(array $cd) { - $sql = "{$cd->name} "; + $line = array(); + $line[] = parent::columnSql($cd); - if (!empty($cd->size)) { - $sql .= "{$cd->type}({$cd->size}) "; - } else { - $sql .= "{$cd->type} "; + // This'll have been added from our transform of 'serial' type + if (!empty($cd['auto_increment'])) { + $line[] = 'auto_increment'; } - if (!empty($cd->default)) { - $sql .= "default {$cd->default} "; - } else { - $sql .= ($cd->nullable) ? "null " : "not null "; + if (!empty($cd['description'])) { + $line[] = 'comment'; + $line[] = $this->quoteValue($cd['description']); } + + return implode(' ', $line); + } + + function mapType($column) + { + $map = array('serial' => 'int', + 'integer' => 'int', + 'numeric' => 'decimal'); - if (!empty($cd->auto_increment)) { - $sql .= " auto_increment "; + $type = $column['type']; + if (isset($map[$type])) { + $type = $map[$type]; } - if (!empty($cd->extra)) { - $sql .= "{$cd->extra} "; + if (!empty($column['size'])) { + $size = $column['size']; + if ($type == 'int' && + in_array($size, array('tiny', 'small', 'medium', 'big'))) { + $type = $size . $type; + } else if (in_array($type, array('blob', 'text')) && + in_array($size, array('tiny', 'medium', 'long'))) { + $type = $size . $type; + } + } + + return $type; + } + + function typeAndSize($column) + { + if ($column['type'] == 'enum') { + $vals = array_map(array($this, 'quote'), $column['enum']); + return 'enum(' . implode(',', $vals) . ')'; + } else if ($this->_isString($column)) { + $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); } + } - return $sql; + /** + * 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; } }