git.gnu.io has moved to IP address 209.51.188.249 -- please double check where you are logging in.

mysqlschema.php 14.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60
<?php
/**
 * StatusNet, the distributed open-source microblogging tool
 *
 * Database schema utilities
 *
 * PHP version 5
 *
 * LICENCE: This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @category  Database
 * @package   StatusNet
 * @author    Evan Prodromou <evan@status.net>
 * @copyright 2009 StatusNet, Inc.
 * @license   http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0
 * @link      http://status.net/
 */

if (!defined('STATUSNET')) {
    exit(1);
}

/**
 * Class representing the database schema
 *
 * A class representing the database schema. Can be used to
 * manipulate the schema -- especially for plugins and upgrade
 * utilities.
 *
 * @category Database
 * @package  StatusNet
 * @author   Evan Prodromou <evan@status.net>
 * @license  http://www.fsf.org/licensing/licenses/agpl-3.0.html GNU Affero General Public License version 3.0
 * @link     http://status.net/
 */

class MysqlSchema extends Schema
{
    static $_single = null;
    protected $conn = null;


    /**
     * Main public entry point. Use this to get
     * the singleton object.
     *
     * @return Schema the (single) Schema object
     */

61
    static function get($conn = null)
62 63
    {
        if (empty(self::$_single)) {
64
            self::$_single = new Schema($conn);
65 66 67 68 69 70 71 72 73 74
        }
        return self::$_single;
    }

    /**
     * Returns a TableDef object for the table
     * in the schema with the given name.
     *
     * Throws an exception if the table is not found.
     *
75
     * @param string $table Name of the table to get
76 77
     *
     * @return TableDef tabledef for that table.
78
     * @throws SchemaTableMissingException
79 80
     */

81
    public function getTableDef($table)
82
    {
83
        $def = array();
84
        $hasKeys = false;
85

86 87 88 89 90
        // Pull column data from INFORMATION_SCHEMA
        $columns = $this->fetchMetaInfo($table, 'COLUMNS', 'ORDINAL_POSITION');
        if (count($columns) == 0) {
            throw new SchemaTableMissingException("No such table: $table");
        }
91

92
        foreach ($columns as $row) {
93

94 95
            $name = $row['COLUMN_NAME'];
            $field = array();
96

97 98
            // 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')
99
            $field['type'] = $type = $row['DATA_TYPE'];
100 101 102 103 104

            if ($type == 'char' || $type == 'varchar') {
                if ($row['CHARACTER_MAXIMUM_LENGTH'] !== null) {
                    $field['length'] = intval($row['CHARACTER_MAXIMUM_LENGTH']);
                }
105
            }
106
            if ($type == 'decimal') {
107 108 109 110 111 112 113 114
                // 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']);
                }
115 116 117 118 119
            }
            if ($row['IS_NULLABLE'] == 'NO') {
                $field['not null'] = true;
            }
            if ($row['COLUMN_DEFAULT'] !== null) {
120 121 122 123 124 125 126 127
                // 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']);
                    }
128
                }
129
            }
130 131 132
            if ($row['COLUMN_KEY'] !== null) {
                // We'll need to look up key info...
                $hasKeys = true;
133
            }
134
            if ($row['COLUMN_COMMENT'] !== null && $row['COLUMN_COMMENT'] != '') {
135
                $field['description'] = $row['COLUMN_COMMENT'];
136 137
            }

138 139 140
            $extra = $row['EXTRA'];
            if ($extra) {
                if (preg_match('/(^|\s)auto_increment(\s|$)/i', $extra)) {
141
                    $field['auto_increment'] = true;
142 143 144 145 146
                }
                // $row['EXTRA'] may contain 'on update CURRENT_TIMESTAMP'
                // ^ ...... how to specify?
            }

147 148 149 150 151
            if ($row['CHARACTER_SET_NAME'] !== null) {
                // @fixme check against defaults?
                //$def['charset'] = $row['CHARACTER_SET_NAME'];
                //$def['collate']  = $row['COLLATION_NAME'];
            }
152

153
            $def['fields'][$name] = $field;
154
        }
155

156 157 158 159 160 161 162 163 164 165 166 167
        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'];
168

169 170 171 172
                if (!isset($keys[$name])) {
                    $keys[$name] = array();
                }
                $keys[$name][] = $column;
173

174 175 176 177 178 179 180 181
                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';
182
                }
183 184 185 186 187 188 189 190 191
                $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];
192 193 194
                }
            }
        }
195
        return $def;
196 197
    }

198 199 200 201 202 203 204 205 206 207
    /**
     * Pull the given table properties from INFORMATION_SCHEMA.
     * Most of the good stuff is MySQL extensions.
     *
     * @return array
     * @throws Exception if table info can't be looked up
     */

    function getTableProperties($table, $props)
    {
208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224
        $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 " .
225 226
                 "WHERE TABLE_SCHEMA='%s' AND TABLE_NAME='%s'";
        $schema = $this->conn->dsn['database'];
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246
        $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);
    }

247 248 249 250 251 252 253 254 255 256 257 258 259 260
    /**
     * 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);
    }

261
    /**
262
     * Close out a 'create table' SQL statement.
263
     *
264 265
     * @param string $name
     * @param array $def
266
     * @return string;
267 268 269
     *
     * @fixme ENGINE may need to be set differently in some cases,
     * such as to support fulltext index.
270
     */
271
    function endCreateTable($name, array $def)
272
    {
273
        $engine = $this->preferredEngine($def);
274
        return ") ENGINE=$engine CHARACTER SET utf8mb4 COLLATE utf8mb4_bin";
275 276 277 278 279 280 281 282
    }
    
    function preferredEngine($def)
    {
        if (!empty($def['fulltext indexes'])) {
            return 'MyISAM';
        }
        return 'InnoDB';
283 284
    }

285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300
    /**
     * Get the unique index key name for a given column on this table
     */
    function _uniqueKey($tableName, $columnName)
    {
        return $this->_key($tableName, $columnName);
    }

    /**
     * Get the index key name for a given column on this table
     */
    function _key($tableName, $columnName)
    {
        return "{$tableName}_{$columnName}_idx";
    }

301 302 303 304 305 306 307 308 309 310
    /**
     * 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 array $phrase
     */
    function appendAlterDropPrimary(array &$phrase)
    {
        $phrase[] = 'DROP PRIMARY KEY';
    }
311

312
    /**
313 314
     * MySQL doesn't take 'DROP CONSTRAINT', need to treat unique keys as
     * if they were indexes here.
315
     *
316 317
     * @param array $phrase
     * @param <type> $keyName MySQL
318
     */
319
    function appendAlterDropUnique(array &$phrase, $keyName)
320
    {
321 322
        $phrase[] = 'DROP INDEX ' . $keyName;
    }
323

324 325 326 327
    /**
     * Throw some table metadata onto the ALTER TABLE if we have a mismatch
     * in expected type, collation.
     */
328
    function appendAlterExtras(array &$phrase, $tableName, array $def)
329
    {
330 331 332 333
        // 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'));
334 335 336
        $engine = $this->preferredEngine($def);
        if (strtolower($oldProps['ENGINE']) != strtolower($engine)) {
            $phrase[] = "ENGINE=$engine";
337
        }
338 339 340 341
        if (strtolower($oldProps['TABLE_COLLATION']) != 'utf8mb4_bin') {
            $phrase[] = 'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin';
            $phrase[] = 'DEFAULT CHARACTER SET = utf8mb4';
            $phrase[] = 'DEFAULT COLLATE = utf8mb4_bin';
342
        }
343 344 345
    }

    /**
346
     * Is this column a string type?
347
     */
348
    private function _isString(array $cd)
349
    {
350 351
        $strings = array('char', 'varchar', 'text');
        return in_array(strtolower($cd['type']), $strings);
352 353 354 355 356 357 358 359 360 361 362 363 364 365
    }

    /**
     * Return the proper SQL for creating or
     * altering a column.
     *
     * Appropriate for use in CREATE TABLE or
     * ALTER TABLE statements.
     *
     * @param ColumnDef $cd column to create
     *
     * @return string correct SQL for that column
     */

366
    function columnSql(array $cd)
367
    {
368
        $line = array();
Brion Vibber's avatar
Brion Vibber committed
369
        $line[] = parent::columnSql($cd);
370

371 372
        // This'll have been added from our transform of 'serial' type
        if (!empty($cd['auto_increment'])) {
373
            $line[] = 'auto_increment';
374 375
        }

376 377
        if (!empty($cd['description'])) {
            $line[] = 'comment';
Brion Vibber's avatar
Brion Vibber committed
378
            $line[] = $this->quoteValue($cd['description']);
379 380
        }

381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403
        return implode(' ', $line);
    }

    function mapType($column)
    {
        $map = array('serial' => 'int',
                     'integer' => 'int',
                     'numeric' => 'decimal');
        
        $type = $column['type'];
        if (isset($map[$type])) {
            $type = $map[$type];
        }

        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;
            }
404 405
        }

406
        return $type;
407
    }
408

409
    function typeAndSize($column)
410
    {
411 412 413 414
        if ($column['type'] == 'enum') {
            $vals = array_map(array($this, 'quote'), $column['enum']);
            return 'enum(' . implode(',', $vals) . ')';
        } else if ($this->_isString($column)) {
415 416 417 418 419 420 421 422
            $col = parent::typeAndSize($column);
            if (!empty($column['charset'])) {
                $col .= ' CHARSET ' . $column['charset'];
            }
            if (!empty($column['collate'])) {
                $col .= ' COLLATE ' . $column['collate'];
            }
            return $col;
423 424 425
        } else {
            return parent::typeAndSize($column);
        }
426
    }
427 428 429 430 431 432 433 434 435 436 437 438

    /**
     * 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)
    {
439 440 441 442 443 444 445 446 447 448 449
        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']);
        }
450 451 452
        if (!common_config('db', 'mysql_foreign_keys')) {
            unset($tableDef['foreign keys']);
        }
453 454
        return $tableDef;
    }
455
}