MySqlPlatform.php 20.6 KB
Newer Older
1
<?php
romanb's avatar
romanb committed
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
/*
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information, see
17
 * <http://www.doctrine-project.org>.
romanb's avatar
romanb committed
18
 */
19

20
namespace Doctrine\DBAL\Platforms;
21

22
use Doctrine\DBAL\DBALException,
23 24 25
    Doctrine\DBAL\Schema\TableDiff,
    Doctrine\DBAL\Schema\Index,
    Doctrine\DBAL\Schema\Table;
26

27 28
/**
 * The MySqlPlatform provides the behavior, features and SQL dialect of the
29 30
 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that
 * uses the InnoDB storage engine.
31 32 33
 *
 * @since 2.0
 * @author Roman Borschel <roman@code-factory.org>
34
 * @author Benjamin Eberlei <kontakt@beberlei.de>
35
 * @todo Rename: MySQLPlatform
36
 */
37
class MySqlPlatform extends AbstractPlatform
38
{
romanb's avatar
romanb committed
39 40 41 42 43 44 45 46 47
    /**
     * Gets the character used for identifier quoting.
     *
     * @return string
     * @override
     */
    public function getIdentifierQuoteCharacter()
    {
        return '`';
48 49 50
    }
    
    /**
romanb's avatar
romanb committed
51
     * Returns the regular expression operator.
52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
     *
     * @return string
     * @override
     */
    public function getRegexpExpression()
    {
        return 'RLIKE';
    }

    /**
     * Returns global unique identifier
     *
     * @return string to get global unique identifier
     * @override
     */
    public function getGuidExpression()
    {
        return 'UUID()';
    }

72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
    /**
     * returns the position of the first occurrence of substring $substr in string $str
     *
     * @param string $substr    literal string to find
     * @param string $str       literal string
     * @param int    $pos       position to start at, beginning of string by default
     * @return integer
     */
    public function getLocateExpression($str, $substr, $startPos = false)
    {
        if ($startPos == false) {
            return 'LOCATE(' . $substr . ', ' . $str . ')';
        } else {
            return 'LOCATE(' . $substr . ', ' . $str . ', '.$startPos.')';
        }
    }

89 90 91 92 93 94 95 96 97 98 99 100 101 102
    /**
     * Returns a series of strings concatinated
     *
     * concat() accepts an arbitrary number of parameters. Each parameter
     * must contain an expression or an array with expressions.
     *
     * @param string|array(string) strings that will be concatinated.
     * @override
     */
    public function getConcatExpression()
    {
        $args = func_get_args();
        return 'CONCAT(' . join(', ', (array) $args) . ')';
    }
103

104
    public function getListDatabasesSQL()
105 106 107 108
    {
        return 'SHOW DATABASES';
    }

109
    public function getListTableConstraintsSQL($table)
110
    {
111
        return 'SHOW INDEX FROM ' . $table;
112 113
    }

114 115 116 117 118 119 120 121 122
    /**
     * Two approaches to listing the table indexes. The information_schema is 
     * prefered, because it doesn't cause problems with SQL keywords such as "order" or "table".
     * 
     * @param string $table
     * @param string $currentDatabase
     * @return string
     */
    public function getListTableIndexesSQL($table, $currentDatabase = null)
123
    {
124 125 126 127 128 129 130 131 132
        if ($currentDatabase) {
            return "SELECT TABLE_NAME AS `Table`, NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, ".
                   "SEQ_IN_INDEX AS Seq_in_index, COLUMN_NAME AS Column_Name, COLLATION AS Collation, ".
                   "CARDINALITY AS Cardinality, SUB_PART AS Sub_Part, PACKED AS Packed, " .
                   "NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment " . 
                   "FROM information_schema.STATISTICS WHERE TABLE_NAME = '" . $table . "' AND TABLE_SCHEMA = '" . $currentDatabase . "'";
        } else {
            return 'SHOW INDEX FROM ' . $table;
        }
133 134
    }

135
    public function getListViewsSQL($database)
136
    {
137
        return "SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = '".$database."'";
138 139
    }

140
    public function getListTableForeignKeysSQL($table, $database = null)
141
    {
142 143 144
        $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".
               "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".
               "FROM information_schema.key_column_usage k /*!50116 ".
145
               "INNER JOIN information_schema.referential_constraints c ON ".
146
               "  c.constraint_name = k.constraint_name AND ".
147
               "  c.table_name = '$table' */ WHERE k.table_name = '$table'";
148

149
        if ($database) {
150
            $sql .= " AND k.table_schema = '$database' /*!50116 AND c.constraint_schema = '$database' */";
151 152
        }

153
        $sql .= " AND k.`REFERENCED_COLUMN_NAME` is not NULL";
154 155 156 157

        return $sql;
    }

158
    public function getCreateViewSQL($name, $sql)
159 160 161 162
    {
        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
    }

163
    public function getDropViewSQL($name)
164 165 166 167
    {
        return 'DROP VIEW '. $name;
    }

168
    /**
169 170 171
     * Gets the SQL snippet used to declare a VARCHAR column on the MySql platform.
     *
     * @params array $field
172
     */
173
    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
174 175
    {
        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')
176
                : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
177
    }
178

179
    /** @override */
180
    public function getClobTypeDeclarationSQL(array $field)
181
    {
182
        if ( ! empty($field['length']) && is_numeric($field['length'])) {
183 184 185 186 187 188 189 190 191 192 193
            $length = $field['length'];
            if ($length <= 255) {
                return 'TINYTEXT';
            } else if ($length <= 65532) {
                return 'TEXT';
            } else if ($length <= 16777215) {
                return 'MEDIUMTEXT';
            }
        }
        return 'LONGTEXT';
    }
194

195 196 197
    /**
     * @override
     */
198
    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
199
    {
200
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] == true) {
201 202 203 204
            return 'TIMESTAMP';
        } else {
            return 'DATETIME';
        }
205
    }
206 207 208 209
    
    /**
     * @override
     */
210
    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
211 212 213
    {
        return 'DATE';
    }
214

215 216 217
    /**
     * @override
     */
218
    public function getTimeTypeDeclarationSQL(array $fieldDeclaration) 
219 220 221 222
    {
        return 'TIME';
    }	

223 224 225
    /**
     * @override
     */
226
    public function getBooleanTypeDeclarationSQL(array $field)
227 228 229 230
    {
        return 'TINYINT(1)';
    }

231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255
    /**
     * Obtain DBMS specific SQL code portion needed to set the COLLATION
     * of a field declaration to be used in statements like CREATE TABLE.
     *
     * @param string $collation   name of the collation
     * @return string  DBMS specific SQL code portion needed to set the COLLATION
     *                 of a field declaration.
     */
    public function getCollationFieldDeclaration($collation)
    {
        return 'COLLATE ' . $collation;
    }
    
    /**
     * Whether the platform prefers identity columns for ID generation.
     * MySql prefers "autoincrement" identity columns since sequences can only
     * be emulated with a table.
     *
     * @return boolean
     * @override
     */
    public function prefersIdentityColumns()
    {
        return true;
    }
romanb's avatar
romanb committed
256 257 258 259 260 261 262 263 264 265 266 267
    
    /**
     * Whether the platform supports identity columns.
     * MySql supports this through AUTO_INCREMENT columns.
     *
     * @return boolean
     * @override
     */
    public function supportsIdentityColumns()
    {
        return true;
    }
268

269
    public function getShowDatabasesSQL()
270 271 272 273
    {
        return 'SHOW DATABASES';
    }
    
274
    public function getListTablesSQL()
275
    {
276
        return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'";
277
    }
278

279
    public function getListTableColumnsSQL($table)
280
    {
281
        return 'DESCRIBE ' . $table;
282 283
    }

284 285 286 287 288 289 290
    /**
     * create a new database
     *
     * @param string $name name of the database that should be created
     * @return string
     * @override
     */
291
    public function getCreateDatabaseSQL($name)
292
    {
293
        return 'CREATE DATABASE ' . $name;
294 295 296 297 298 299 300 301 302
    }
    
    /**
     * drop an existing database
     *
     * @param string $name name of the database that should be dropped
     * @return string
     * @override
     */
303
    public function getDropDatabaseSQL($name)
304
    {
305
        return 'DROP DATABASE ' . $name;
306 307 308 309 310
    }
    
    /**
     * create a new table
     *
311 312
     * @param string $tableName   Name of the database that should be created
     * @param array $columns  Associative array that contains the definition of each field of the new table
313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342
     *                       The indexes of the array entries are the names of the fields of the table an
     *                       the array entry values are associative arrays like those that are meant to be
     *                       passed with the field definitions to get[Type]Declaration() functions.
     *                          array(
     *                              'id' => array(
     *                                  'type' => 'integer',
     *                                  'unsigned' => 1
     *                                  'notnull' => 1
     *                                  'default' => 0
     *                              ),
     *                              'name' => array(
     *                                  'type' => 'text',
     *                                  'length' => 12
     *                              ),
     *                              'password' => array(
     *                                  'type' => 'text',
     *                                  'length' => 12
     *                              )
     *                          );
     * @param array $options  An associative array of table options:
     *                          array(
     *                              'comment' => 'Foo',
     *                              'charset' => 'utf8',
     *                              'collate' => 'utf8_unicode_ci',
     *                              'type'    => 'innodb',
     *                          );
     *
     * @return void
     * @override
     */
343
    protected function _getCreateTableSQL($tableName, array $columns, array $options = array())
344
    {
345
        $queryFields = $this->getColumnDeclarationListSQL($columns);
346

347
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
348
            foreach ($options['uniqueConstraints'] as $index => $definition) {
349
                $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($index, $definition);
350
            }
351 352 353 354 355
        }

        // add all indexes
        if (isset($options['indexes']) && ! empty($options['indexes'])) {
            foreach($options['indexes'] as $index => $definition) {
356
                $queryFields .= ', ' . $this->getIndexDeclarationSQL($index, $definition);
357 358 359 360 361
            }
        }

        // attach all primary keys
        if (isset($options['primary']) && ! empty($options['primary'])) {
362
            $keyColumns = array_unique(array_values($options['primary']));
363 364 365 366 367 368 369
            $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')';
        }

        $query = 'CREATE ';
        if (!empty($options['temporary'])) {
            $query .= 'TEMPORARY ';
        }
370
        $query.= 'TABLE ' . $tableName . ' (' . $queryFields . ')';
371 372 373 374

        $optionStrings = array();

        if (isset($options['comment'])) {
375
            $optionStrings['comment'] = 'COMMENT = ' . $options['comment'];
376 377 378 379 380 381 382 383 384
        }
        if (isset($options['charset'])) {
            $optionStrings['charset'] = 'DEFAULT CHARACTER SET ' . $options['charset'];
            if (isset($options['collate'])) {
                $optionStrings['charset'] .= ' COLLATE ' . $options['collate'];
            }
        }

        // get the type of the table
385
        if (isset($options['engine'])) {
386
            $optionStrings[] = 'ENGINE = ' . $options['engine'];
387 388 389
        } else {
            // default to innodb
            $optionStrings[] = 'ENGINE = InnoDB';
390
        }
391
        
392 393 394 395 396 397
        if ( ! empty($optionStrings)) {
            $query.= ' '.implode(' ', $optionStrings);
        }
        $sql[] = $query;

        if (isset($options['foreignKeys'])) {
398
            foreach ((array) $options['foreignKeys'] as $definition) {
399
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
400 401 402 403 404 405 406
            }
        }
        
        return $sql;
    }
    
    /**
407
     * Gets the SQL to alter an existing table.
408
     *
409 410
     * @param TableDiff $diff
     * @return array
411
     */
412
    public function getAlterTableSQL(TableDiff $diff)
413
    {
414 415 416
        $queryParts = array();
        if ($diff->newName !== false) {
            $queryParts[] =  'RENAME TO ' . $diff->newName;
417 418
        }

419
        foreach ($diff->addedColumns AS $fieldName => $column) {
420
            $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
421 422
        }

423
        foreach ($diff->removedColumns AS $column) {
424
            $queryParts[] =  'DROP ' . $column->getQuotedName($this);
425 426
        }

427 428 429 430
        foreach ($diff->changedColumns AS $columnDiff) {
            /* @var $columnDiff Doctrine\DBAL\Schema\ColumnDiff */
            $column = $columnDiff->column;
            $queryParts[] =  'CHANGE ' . ($columnDiff->oldColumnName) . ' '
431
                    . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
432 433
        }

434 435
        foreach ($diff->renamedColumns AS $oldColumnName => $column) {
            $queryParts[] =  'CHANGE ' . $oldColumnName . ' '
436
                    . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
437 438
        }

439
        $sql = array();
440 441
        if (count($queryParts) > 0) {
            $sql[] = 'ALTER TABLE ' . $diff->name . ' ' . implode(", ", $queryParts);
442
        }
443
        $sql = array_merge($sql, $this->_getAlterTableIndexForeignKeySQL($diff));
444
        return $sql;
445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472
    }
    
    /**
     * Obtain DBMS specific SQL code portion needed to declare an integer type
     * field to be used in statements like CREATE TABLE.
     *
     * @param string  $name   name the field to be declared.
     * @param string  $field  associative array with the name of the properties
     *                        of the field being declared as array indexes.
     *                        Currently, the types of supported field
     *                        properties are as follows:
     *
     *                       unsigned
     *                        Boolean flag that indicates whether the field
     *                        should be declared as unsigned integer if
     *                        possible.
     *
     *                       default
     *                        Integer value to be used as default for this
     *                        field.
     *
     *                       notnull
     *                        Boolean flag that indicates whether this field is
     *                        constrained to not be set to null.
     * @return string  DBMS specific SQL code portion that should be used to
     *                 declare the specified field.
     * @override
     */
473
    public function getIntegerTypeDeclarationSQL(array $field)
474
    {
475
        return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
476 477 478
    }

    /** @override */
479
    public function getBigIntTypeDeclarationSQL(array $field)
480
    {
481
        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
482 483 484
    }

    /** @override */
485
    public function getSmallIntTypeDeclarationSQL(array $field)
486
    {
487
        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
488 489 490
    }

    /** @override */
491
    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
492
    {
493
        $autoinc = '';
494
        if ( ! empty($columnDef['autoincrement'])) {
495 496
            $autoinc = ' AUTO_INCREMENT';
        }
497
        $unsigned = (isset($columnDef['unsigned']) && $columnDef['unsigned']) ? ' UNSIGNED' : '';
498

499
        return $unsigned . $autoinc;
500 501 502 503 504 505
    }
    
    /**
     * Return the FOREIGN KEY query section dealing with non-standard options
     * as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
     *
506
     * @param ForeignKeyConstraint $foreignKey
507 508 509
     * @return string
     * @override
     */
510
    public function getAdvancedForeignKeyOptionsSQL(\Doctrine\DBAL\Schema\ForeignKeyConstraint $foreignKey)
511 512
    {
        $query = '';
513 514
        if ($foreignKey->hasOption('match')) {
            $query .= ' MATCH ' . $foreignKey->getOption('match');
515
        }
516
        $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
517 518 519 520
        return $query;
    }
    
    /**
521
     * Gets the SQL to drop an index of a table.
522
     *
523 524
     * @param Index $index           name of the index to be dropped
     * @param string|Table $table          name of table that should be used in method
525 526
     * @override
     */
527
    public function getDropIndexSQL($index, $table=null)
528 529 530 531 532 533
    {        
        if($index instanceof Index) {
            $indexName = $index->getQuotedName($this);
        } else if(is_string($index)) {
            $indexName = $index;
        } else {
534
            throw new \InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
535 536
        }
        
537
        if($table instanceof Table) {
538
            $table = $table->getQuotedName($this);
539
        } else if(!is_string($table)) {
540
            throw new \InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
541
        }
542 543 544 545 546 547
        
        if ($index instanceof Index && $index->isPrimary()) {
            // mysql primary keys are always named "PRIMARY", 
            // so we cannot use them in statements because of them being keyword.
            return $this->getDropPrimaryKeySQL($table);
        }
548

549 550 551 552 553 554 555 556 557 558
        return 'DROP INDEX ' . $indexName . ' ON ' . $table;
    }
    
    /**
     * @param Index $index
     * @param Table $table 
     */
    protected function getDropPrimaryKeySQL($table)
    {
        return 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY';
559 560 561
    }
    
    /**
562
     * Gets the SQL to drop a table.
563
     *
564
     * @param string $table The name of table to drop.
565 566
     * @override
     */
567
    public function getDropTableSQL($table)
568
    {
569
        if ($table instanceof \Doctrine\DBAL\Schema\Table) {
570
            $table = $table->getQuotedName($this);
571
        } else if(!is_string($table)) {
572
            throw new \InvalidArgumentException('MysqlPlatform::getDropTableSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
573 574
        }

575
        return 'DROP TABLE ' . $table;
576
    }
577

578
    public function getSetTransactionIsolationSQL($level)
romanb's avatar
romanb committed
579
    {
580
        return 'SET SESSION TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
romanb's avatar
romanb committed
581
    }
582 583

    /**
584
     * Get the platform name for this instance.
585 586 587 588 589 590 591
     *
     * @return string
     */
    public function getName()
    {
        return 'mysql';
    }
592

593 594 595 596
    public function getReadLockSQL()
    {
        return 'LOCK IN SHARE MODE';
    }
597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617

    protected function initializeDoctrineTypeMappings()
    {
        $this->doctrineTypeMapping = array(
            'tinyint'       => 'boolean',
            'smallint'      => 'smallint',
            'mediumint'     => 'integer',
            'int'           => 'integer',
            'integer'       => 'integer',
            'bigint'        => 'bigint',
            'tinytext'      => 'text',
            'mediumtext'    => 'text',
            'longtext'      => 'text',
            'text'          => 'text',
            'varchar'       => 'string',
            'string'        => 'string',
            'char'          => 'string',
            'date'          => 'date',
            'datetime'      => 'datetime',
            'timestamp'     => 'datetime',
            'time'          => 'time',
618 619 620
            'float'         => 'float',
            'double'        => 'float',
            'real'          => 'float',
621 622 623 624 625
            'decimal'       => 'decimal',
            'numeric'       => 'decimal',
            'year'          => 'date',
        );
    }
626 627 628 629 630

    public function getVarcharMaxLength()
    {
        return 65535;
    }
631
}