MySqlPlatform.php 33.9 KB
Newer Older
1
<?php
romanb's avatar
romanb committed
2 3 4 5 6 7 8 9 10 11 12 13 14 15
/*
 * 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
Benjamin Eberlei's avatar
Benjamin Eberlei committed
16
 * and is licensed under the MIT license. 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\Schema\Identifier;
Benjamin Morel's avatar
Benjamin Morel committed
23 24
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Table;
25
use Doctrine\DBAL\Schema\TableDiff;
26
use Doctrine\DBAL\TransactionIsolationLevel;
27 28
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\TextType;
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
use function array_diff_key;
use function array_merge;
use function array_unique;
use function array_values;
use function count;
use function func_get_args;
use function implode;
use function in_array;
use function is_numeric;
use function is_string;
use function join;
use function sprintf;
use function str_replace;
use function strtoupper;
use function trim;
44

45 46
/**
 * The MySqlPlatform provides the behavior, features and SQL dialect of the
47 48
 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that
 * uses the InnoDB storage engine.
49
 *
Benjamin Morel's avatar
Benjamin Morel committed
50
 * @since  2.0
51
 * @author Roman Borschel <roman@code-factory.org>
52
 * @author Benjamin Eberlei <kontakt@beberlei.de>
Benjamin Morel's avatar
Benjamin Morel committed
53
 * @todo   Rename: MySQLPlatform
54
 */
55
class MySqlPlatform extends AbstractPlatform
56
{
57 58 59 60 61 62 63 64
    const LENGTH_LIMIT_TINYTEXT   = 255;
    const LENGTH_LIMIT_TEXT       = 65535;
    const LENGTH_LIMIT_MEDIUMTEXT = 16777215;

    const LENGTH_LIMIT_TINYBLOB   = 255;
    const LENGTH_LIMIT_BLOB       = 65535;
    const LENGTH_LIMIT_MEDIUMBLOB = 16777215;

65 66 67
    /**
     * Adds MySQL-specific LIMIT clause to the query
     * 18446744073709551615 is 2^64-1 maximum of unsigned BIGINT the biggest limit possible
68
     *
69 70 71
     * @param string $query
     * @param int    $limit
     * @param int    $offset
72 73
     *
     * @return string
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
     */
    protected function doModifyLimitQuery($query, $limit, $offset)
    {
        if ($limit !== null) {
            $query .= ' LIMIT ' . $limit;
            if ($offset !== null) {
                $query .= ' OFFSET ' . $offset;
            }
        } elseif ($offset !== null) {
            $query .= ' LIMIT 18446744073709551615 OFFSET ' . $offset;
        }

        return $query;
    }

romanb's avatar
romanb committed
89
    /**
90
     * {@inheritDoc}
romanb's avatar
romanb committed
91 92 93 94
     */
    public function getIdentifierQuoteCharacter()
    {
        return '`';
95
    }
96

97
    /**
98
     * {@inheritDoc}
99 100 101 102 103 104 105
     */
    public function getRegexpExpression()
    {
        return 'RLIKE';
    }

    /**
106
     * {@inheritDoc}
107 108 109 110 111 112
     */
    public function getGuidExpression()
    {
        return 'UUID()';
    }

113
    /**
114
     * {@inheritDoc}
115 116 117 118 119 120
     */
    public function getLocateExpression($str, $substr, $startPos = false)
    {
        if ($startPos == false) {
            return 'LOCATE(' . $substr . ', ' . $str . ')';
        }
121 122

        return 'LOCATE(' . $substr . ', ' . $str . ', '.$startPos.')';
123 124
    }

125
    /**
126
     * {@inheritDoc}
127 128 129 130
     */
    public function getConcatExpression()
    {
        $args = func_get_args();
131

132 133
        return 'CONCAT(' . join(', ', (array) $args) . ')';
    }
134

135
    /**
136
     * {@inheritdoc}
137
     */
138
    protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
139
    {
140
        $function = '+' === $operator ? 'DATE_ADD' : 'DATE_SUB';
141

142
        return $function . '(' . $date . ', INTERVAL ' . $interval . ' ' . $unit . ')';
143 144
    }

145 146 147
    /**
     * {@inheritDoc}
     */
148
    public function getDateDiffExpression($date1, $date2)
149
    {
150
        return 'DATEDIFF(' . $date1 . ', ' . $date2 . ')';
151
    }
152

Benjamin Morel's avatar
Benjamin Morel committed
153 154 155
    /**
     * {@inheritDoc}
     */
156
    public function getListDatabasesSQL()
157 158 159 160
    {
        return 'SHOW DATABASES';
    }

Benjamin Morel's avatar
Benjamin Morel committed
161 162 163
    /**
     * {@inheritDoc}
     */
164
    public function getListTableConstraintsSQL($table)
165
    {
166
        return 'SHOW INDEX FROM ' . $table;
167 168
    }

169
    /**
170 171
     * {@inheritDoc}
     *
172
     * Two approaches to listing the table indexes. The information_schema is
Pascal Borreli's avatar
Pascal Borreli committed
173
     * preferred, because it doesn't cause problems with SQL keywords such as "order" or "table".
174 175
     */
    public function getListTableIndexesSQL($table, $currentDatabase = null)
176
    {
177
        if ($currentDatabase) {
178 179 180
            $currentDatabase = $this->quoteStringLiteral($currentDatabase);
            $table = $this->quoteStringLiteral($table);

181 182 183
            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, " .
184
                   "NULLABLE AS `Null`, INDEX_TYPE AS Index_Type, COMMENT AS Comment " .
185
                   "FROM information_schema.STATISTICS WHERE TABLE_NAME = " . $table . " AND TABLE_SCHEMA = " . $currentDatabase;
186
        }
187 188

        return 'SHOW INDEX FROM ' . $table;
189 190
    }

Benjamin Morel's avatar
Benjamin Morel committed
191 192 193
    /**
     * {@inheritDoc}
     */
194
    public function getListViewsSQL($database)
195
    {
196 197 198
        $database = $this->quoteStringLiteral($database);

        return "SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = " . $database;
199 200
    }

Benjamin Morel's avatar
Benjamin Morel committed
201 202 203
    /**
     * {@inheritDoc}
     */
204
    public function getListTableForeignKeysSQL($table, $database = null)
205
    {
206 207 208 209 210 211
        $table = $this->quoteStringLiteral($table);

        if (null !== $database) {
            $database = $this->quoteStringLiteral($database);
        }

212 213 214
        $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 ".
215
               "INNER JOIN information_schema.referential_constraints c ON ".
216
               "  c.constraint_name = k.constraint_name AND ".
217
               "  c.table_name = $table */ WHERE k.table_name = $table";
218

Steve Müller's avatar
Steve Müller committed
219
        $databaseNameSql = null === $database ? 'DATABASE()' : $database;
220

221
        $sql .= " AND k.table_schema = $databaseNameSql /*!50116 AND c.constraint_schema = $databaseNameSql */";
222
        $sql .= " AND k.`REFERENCED_COLUMN_NAME` is not NULL";
223 224 225 226

        return $sql;
    }

Benjamin Morel's avatar
Benjamin Morel committed
227 228 229
    /**
     * {@inheritDoc}
     */
230
    public function getCreateViewSQL($name, $sql)
231 232 233 234
    {
        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
    }

Benjamin Morel's avatar
Benjamin Morel committed
235 236 237
    /**
     * {@inheritDoc}
     */
238
    public function getDropViewSQL($name)
239 240 241 242
    {
        return 'DROP VIEW '. $name;
    }

243
    /**
244
     * {@inheritDoc}
245
     */
246
    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
247 248
    {
        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')
249
                : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
250
    }
251

Steve Müller's avatar
Steve Müller committed
252 253 254 255 256 257 258 259
    /**
     * {@inheritdoc}
     */
    protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
    {
        return $fixed ? 'BINARY(' . ($length ?: 255) . ')' : 'VARBINARY(' . ($length ?: 255) . ')';
    }

260
    /**
261 262 263 264 265 266 267 268 269
     * Gets the SQL snippet used to declare a CLOB column type.
     *     TINYTEXT   : 2 ^  8 - 1 = 255
     *     TEXT       : 2 ^ 16 - 1 = 65535
     *     MEDIUMTEXT : 2 ^ 24 - 1 = 16777215
     *     LONGTEXT   : 2 ^ 32 - 1 = 4294967295
     *
     * @param array $field
     *
     * @return string
270
     */
271
    public function getClobTypeDeclarationSQL(array $field)
272
    {
273
        if ( ! empty($field['length']) && is_numeric($field['length'])) {
274
            $length = $field['length'];
275

276
            if ($length <= static::LENGTH_LIMIT_TINYTEXT) {
277
                return 'TINYTEXT';
278 279
            }

280
            if ($length <= static::LENGTH_LIMIT_TEXT) {
281
                return 'TEXT';
282 283
            }

284
            if ($length <= static::LENGTH_LIMIT_MEDIUMTEXT) {
285 286 287
                return 'MEDIUMTEXT';
            }
        }
288

289 290
        return 'LONGTEXT';
    }
291

292
    /**
293
     * {@inheritDoc}
294
     */
295
    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
296
    {
297
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] == true) {
298 299
            return 'TIMESTAMP';
        }
300 301

        return 'DATETIME';
302
    }
303

304
    /**
305
     * {@inheritDoc}
306
     */
307
    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
308 309 310
    {
        return 'DATE';
    }
311

312
    /**
313
     * {@inheritDoc}
314
     */
315
    public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
316 317
    {
        return 'TIME';
318
    }
319

320
    /**
321
     * {@inheritDoc}
322
     */
323
    public function getBooleanTypeDeclarationSQL(array $field)
324 325 326 327
    {
        return 'TINYINT(1)';
    }

328 329 330 331
    /**
     * Obtain DBMS specific SQL code portion needed to set the COLLATION
     * of a field declaration to be used in statements like CREATE TABLE.
     *
332 333
     * @deprecated Deprecated since version 2.5, Use {@link self::getColumnCollationDeclarationSQL()} instead.
     *
334
     * @param string $collation name of the collation
335
     *
336 337 338 339 340
     * @return string  DBMS specific SQL code portion needed to set the COLLATION
     *                 of a field declaration.
     */
    public function getCollationFieldDeclaration($collation)
    {
341
        return $this->getColumnCollationDeclarationSQL($collation);
342
    }
343

344
    /**
345 346
     * {@inheritDoc}
     *
347 348 349 350 351 352 353
     * MySql prefers "autoincrement" identity columns since sequences can only
     * be emulated with a table.
     */
    public function prefersIdentityColumns()
    {
        return true;
    }
354

romanb's avatar
romanb committed
355
    /**
356
     * {@inheritDoc}
romanb's avatar
romanb committed
357
     *
358
     * MySql supports this through AUTO_INCREMENT columns.
romanb's avatar
romanb committed
359 360 361 362
     */
    public function supportsIdentityColumns()
    {
        return true;
363 364
    }

365 366 367
    /**
     * {@inheritDoc}
     */
368 369 370
    public function supportsInlineColumnComments()
    {
        return true;
romanb's avatar
romanb committed
371
    }
372

Benjamin Morel's avatar
Benjamin Morel committed
373 374 375
    /**
     * {@inheritDoc}
     */
376 377 378 379 380
    public function supportsColumnCollation()
    {
        return true;
    }

381 382 383
    /**
     * {@inheritDoc}
     */
384
    public function getListTablesSQL()
385
    {
386
        return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'";
387
    }
388

Benjamin Morel's avatar
Benjamin Morel committed
389 390 391
    /**
     * {@inheritDoc}
     */
392
    public function getListTableColumnsSQL($table, $database = null)
393
    {
394 395
        $table = $this->quoteStringLiteral($table);

396
        if ($database) {
397
            $database = $this->quoteStringLiteral($database);
398 399
        } else {
            $database = 'DATABASE()';
400
        }
401

402 403 404
        return "SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, ".
               "COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, " .
               "CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation ".
405
               "FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = " . $database . " AND TABLE_NAME = " . $table;
406 407
    }

408
    /**
409
     * {@inheritDoc}
410
     */
411
    public function getCreateDatabaseSQL($name)
412
    {
413
        return 'CREATE DATABASE ' . $name;
414
    }
415

416
    /**
417
     * {@inheritDoc}
418
     */
419
    public function getDropDatabaseSQL($name)
420
    {
421
        return 'DROP DATABASE ' . $name;
422
    }
423

424
    /**
425
     * {@inheritDoc}
426
     */
427
    protected function _getCreateTableSQL($tableName, array $columns, array $options = [])
428
    {
429
        $queryFields = $this->getColumnDeclarationListSQL($columns);
430

431
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
432
            foreach ($options['uniqueConstraints'] as $index => $definition) {
433
                $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($index, $definition);
434
            }
435 436 437 438
        }

        // add all indexes
        if (isset($options['indexes']) && ! empty($options['indexes'])) {
Steve Müller's avatar
Steve Müller committed
439
            foreach ($options['indexes'] as $index => $definition) {
440
                $queryFields .= ', ' . $this->getIndexDeclarationSQL($index, $definition);
441 442 443 444 445
            }
        }

        // attach all primary keys
        if (isset($options['primary']) && ! empty($options['primary'])) {
446
            $keyColumns = array_unique(array_values($options['primary']));
447 448 449 450
            $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')';
        }

        $query = 'CREATE ';
451

452 453 454
        if (!empty($options['temporary'])) {
            $query .= 'TEMPORARY ';
        }
455

456
        $query .= 'TABLE ' . $tableName . ' (' . $queryFields . ') ';
457 458
        $query .= $this->buildTableOptions($options);
        $query .= $this->buildPartitionOptions($options);
459

460 461
        $sql[]  = $query;
        $engine = 'INNODB';
462

463 464 465 466 467 468
        if (isset($options['engine'])) {
            $engine = strtoupper(trim($options['engine']));
        }

        // Propagate foreign key constraints only for InnoDB.
        if (isset($options['foreignKeys']) && $engine === 'INNODB') {
469 470 471 472
            foreach ((array) $options['foreignKeys'] as $definition) {
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
            }
        }
473

474 475 476
        return $sql;
    }

477 478 479 480 481 482 483 484 485 486 487 488 489
    /**
     * {@inheritdoc}
     */
    public function getDefaultValueDeclarationSQL($field)
    {
        // Unset the default value if the given field definition does not allow default values.
        if ($field['type'] instanceof TextType || $field['type'] instanceof BlobType) {
            $field['default'] = null;
        }

        return parent::getDefaultValueDeclarationSQL($field);
    }

490 491 492 493 494 495 496 497 498 499 500
    /**
     * Build SQL for table options
     *
     * @param array $options
     *
     * @return string
     */
    private function buildTableOptions(array $options)
    {
        if (isset($options['table_options'])) {
            return $options['table_options'];
501
        }
502

503
        $tableOptions = [];
504 505

        // Charset
506 507
        if ( ! isset($options['charset'])) {
            $options['charset'] = 'utf8';
508 509
        }

510 511 512
        $tableOptions[] = sprintf('DEFAULT CHARACTER SET %s', $options['charset']);

        // Collate
513
        if ( ! isset($options['collate'])) {
514
            $options['collate'] = $options['charset'] . '_unicode_ci';
515
        }
516

517
        $tableOptions[] = sprintf('COLLATE %s', $options['collate']);
518

519
        // Engine
520 521
        if ( ! isset($options['engine'])) {
            $options['engine'] = 'InnoDB';
522
        }
523

524
        $tableOptions[] = sprintf('ENGINE = %s', $options['engine']);
525

526 527 528
        // Auto increment
        if (isset($options['auto_increment'])) {
            $tableOptions[] = sprintf('AUTO_INCREMENT = %s', $options['auto_increment']);
529
        }
530

531 532 533 534
        // Comment
        if (isset($options['comment'])) {
            $comment = trim($options['comment'], " '");

535
            $tableOptions[] = sprintf("COMMENT = %s ", $this->quoteStringLiteral($comment));
536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555
        }

        // Row format
        if (isset($options['row_format'])) {
            $tableOptions[] = sprintf('ROW_FORMAT = %s', $options['row_format']);
        }

        return implode(' ', $tableOptions);
    }

    /**
     * Build SQL for partition options.
     *
     * @param array $options
     *
     * @return string
     */
    private function buildPartitionOptions(array $options)
    {
        return (isset($options['partition_options']))
556
            ? ' ' . $options['partition_options']
557
            : '';
558
    }
559

560
    /**
561
     * {@inheritDoc}
562
     */
563
    public function getAlterTableSQL(TableDiff $diff)
564
    {
565 566
        $columnSql = [];
        $queryParts = [];
567
        if ($diff->newName !== false) {
568
            $queryParts[] = 'RENAME TO ' . $diff->getNewName()->getQuotedName($this);
569 570
        }

571
        foreach ($diff->addedColumns as $column) {
572 573
            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
                continue;
574 575
            }

576 577 578
            $columnArray = $column->toArray();
            $columnArray['comment'] = $this->getColumnComment($column);
            $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray);
579 580
        }

581
        foreach ($diff->removedColumns as $column) {
582 583
            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
                continue;
584 585
            }

586
            $queryParts[] =  'DROP ' . $column->getQuotedName($this);
587 588
        }

589
        foreach ($diff->changedColumns as $columnDiff) {
590 591
            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
                continue;
592 593
            }

594
            /* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */
595
            $column = $columnDiff->column;
596
            $columnArray = $column->toArray();
597 598 599 600 601 602 603 604 605

            // Don't propagate default value changes for unsupported column types.
            if ($columnDiff->hasChanged('default') &&
                count($columnDiff->changedProperties) === 1 &&
                ($columnArray['type'] instanceof TextType || $columnArray['type'] instanceof BlobType)
            ) {
                continue;
            }

606
            $columnArray['comment'] = $this->getColumnComment($column);
607
            $queryParts[] =  'CHANGE ' . ($columnDiff->getOldColumnName()->getQuotedName($this)) . ' '
608
                    . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray);
609 610
        }

611
        foreach ($diff->renamedColumns as $oldColumnName => $column) {
612 613
            if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
                continue;
614 615
            }

616
            $oldColumnName = new Identifier($oldColumnName);
617 618
            $columnArray = $column->toArray();
            $columnArray['comment'] = $this->getColumnComment($column);
619
            $queryParts[] =  'CHANGE ' . $oldColumnName->getQuotedName($this) . ' '
620
                    . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray);
621 622
        }

623 624 625 626 627 628
        if (isset($diff->addedIndexes['primary'])) {
            $keyColumns = array_unique(array_values($diff->addedIndexes['primary']->getColumns()));
            $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')';
            unset($diff->addedIndexes['primary']);
        }

629 630
        $sql = [];
        $tableSql = [];
631

632
        if ( ! $this->onSchemaAlterTable($diff, $tableSql)) {
633
            if (count($queryParts) > 0) {
634
                $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . implode(", ", $queryParts);
635 636 637 638 639 640
            }
            $sql = array_merge(
                $this->getPreAlterTableIndexForeignKeySQL($diff),
                $sql,
                $this->getPostAlterTableIndexForeignKeySQL($diff)
            );
641
        }
642 643

        return array_merge($sql, $tableSql, $columnSql);
644
    }
645

646
    /**
647
     * {@inheritDoc}
648 649 650
     */
    protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff)
    {
651
        $sql = [];
652
        $table = $diff->getName($this)->getQuotedName($this);
653

654 655 656
        foreach ($diff->changedIndexes as $changedIndex) {
            $sql = array_merge($sql, $this->getPreAlterTableAlterPrimaryKeySQL($diff, $changedIndex));
        }
andig's avatar
andig committed
657

658 659
        foreach ($diff->removedIndexes as $remKey => $remIndex) {
            $sql = array_merge($sql, $this->getPreAlterTableAlterPrimaryKeySQL($diff, $remIndex));
660 661 662 663

            foreach ($diff->addedIndexes as $addKey => $addIndex) {
                if ($remIndex->getColumns() == $addIndex->getColumns()) {

664 665 666 667 668 669
                    $indexClause = 'INDEX ' . $addIndex->getName();

                    if ($addIndex->isPrimary()) {
                        $indexClause = 'PRIMARY KEY';
                    } elseif ($addIndex->isUnique()) {
                        $indexClause = 'UNIQUE INDEX ' . $addIndex->getName();
670 671 672
                    }

                    $query = 'ALTER TABLE ' . $table . ' DROP INDEX ' . $remIndex->getName() . ', ';
673
                    $query .= 'ADD ' . $indexClause;
674
                    $query .= ' (' . $this->getIndexFieldDeclarationListSQL($addIndex->getQuotedColumns($this)) . ')';
675 676 677

                    $sql[] = $query;

678
                    unset($diff->removedIndexes[$remKey], $diff->addedIndexes[$addKey]);
679 680 681 682 683 684

                    break;
                }
            }
        }

685 686 687 688 689 690 691 692
        $engine = 'INNODB';

        if ($diff->fromTable instanceof Table && $diff->fromTable->hasOption('engine')) {
            $engine = strtoupper(trim($diff->fromTable->getOption('engine')));
        }

        // Suppress foreign key constraint propagation on non-supporting engines.
        if ('INNODB' !== $engine) {
693 694 695
            $diff->addedForeignKeys   = [];
            $diff->changedForeignKeys = [];
            $diff->removedForeignKeys = [];
696 697
        }

698 699
        $sql = array_merge(
            $sql,
700
            $this->getPreAlterTableAlterIndexForeignKeySQL($diff),
701 702 703 704 705 706 707
            parent::getPreAlterTableIndexForeignKeySQL($diff),
            $this->getPreAlterTableRenameIndexForeignKeySQL($diff)
        );

        return $sql;
    }

708 709 710 711 712 713 714 715
    /**
     * @param TableDiff $diff
     * @param Index     $index
     *
     * @return string[]
     */
    private function getPreAlterTableAlterPrimaryKeySQL(TableDiff $diff, Index $index)
    {
716
        $sql = [];
717 718 719 720 721 722 723 724 725

        if (! $index->isPrimary() || ! $diff->fromTable instanceof Table) {
            return $sql;
        }

        $tableName = $diff->getName($this)->getQuotedName($this);

        // Dropping primary keys requires to unset autoincrement attribute on the particular column first.
        foreach ($index->getColumns() as $columnName) {
726 727 728
            if (! $diff->fromTable->hasColumn($columnName)) {
                continue;
            }
729

730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745
            $column = $diff->fromTable->getColumn($columnName);

            if ($column->getAutoincrement() === true) {
                $column->setAutoincrement(false);

                $sql[] = 'ALTER TABLE ' . $tableName . ' MODIFY ' .
                    $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());

                // original autoincrement information might be needed later on by other parts of the table alteration
                $column->setAutoincrement(true);
            }
        }

        return $sql;
    }

746 747 748 749 750 751 752
    /**
     * @param TableDiff $diff The table diff to gather the SQL for.
     *
     * @return array
     */
    private function getPreAlterTableAlterIndexForeignKeySQL(TableDiff $diff)
    {
753
        $sql = [];
754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781
        $table = $diff->getName($this)->getQuotedName($this);

        foreach ($diff->changedIndexes as $changedIndex) {
            // Changed primary key
            if ($changedIndex->isPrimary() && $diff->fromTable instanceof Table) {
                foreach ($diff->fromTable->getPrimaryKeyColumns() as $columnName) {
                    $column = $diff->fromTable->getColumn($columnName);

                    // Check if an autoincrement column was dropped from the primary key.
                    if ($column->getAutoincrement() && ! in_array($columnName, $changedIndex->getColumns())) {
                        // The autoincrement attribute needs to be removed from the dropped column
                        // before we can drop and recreate the primary key.
                        $column->setAutoincrement(false);

                        $sql[] = 'ALTER TABLE ' . $table . ' MODIFY ' .
                            $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());

                        // Restore the autoincrement attribute as it might be needed later on
                        // by other parts of the table alteration.
                        $column->setAutoincrement(true);
                    }
                }
            }
        }

        return $sql;
    }

782 783 784 785 786 787 788
    /**
     * @param TableDiff $diff The table diff to gather the SQL for.
     *
     * @return array
     */
    protected function getPreAlterTableRenameIndexForeignKeySQL(TableDiff $diff)
    {
789
        $sql = [];
790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813
        $tableName = $diff->getName($this)->getQuotedName($this);

        foreach ($this->getRemainingForeignKeyConstraintsRequiringRenamedIndexes($diff) as $foreignKey) {
            if (! in_array($foreignKey, $diff->changedForeignKeys, true)) {
                $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName);
            }
        }

        return $sql;
    }

    /**
     * Returns the remaining foreign key constraints that require one of the renamed indexes.
     *
     * "Remaining" here refers to the diff between the foreign keys currently defined in the associated
     * table and the foreign keys to be removed.
     *
     * @param TableDiff $diff The table diff to evaluate.
     *
     * @return array
     */
    private function getRemainingForeignKeyConstraintsRequiringRenamedIndexes(TableDiff $diff)
    {
        if (empty($diff->renamedIndexes) || ! $diff->fromTable instanceof Table) {
814
            return [];
815 816
        }

817
        $foreignKeys = [];
818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854
        /** @var \Doctrine\DBAL\Schema\ForeignKeyConstraint[] $remainingForeignKeys */
        $remainingForeignKeys = array_diff_key(
            $diff->fromTable->getForeignKeys(),
            $diff->removedForeignKeys
        );

        foreach ($remainingForeignKeys as $foreignKey) {
            foreach ($diff->renamedIndexes as $index) {
                if ($foreignKey->intersectsIndexColumns($index)) {
                    $foreignKeys[] = $foreignKey;

                    break;
                }
            }
        }

        return $foreignKeys;
    }

    /**
     * {@inheritdoc}
     */
    protected function getPostAlterTableIndexForeignKeySQL(TableDiff $diff)
    {
        return array_merge(
            parent::getPostAlterTableIndexForeignKeySQL($diff),
            $this->getPostAlterTableRenameIndexForeignKeySQL($diff)
        );
    }

    /**
     * @param TableDiff $diff The table diff to gather the SQL for.
     *
     * @return array
     */
    protected function getPostAlterTableRenameIndexForeignKeySQL(TableDiff $diff)
    {
855
        $sql = [];
856 857 858 859 860 861 862 863 864
        $tableName = (false !== $diff->newName)
            ? $diff->getNewName()->getQuotedName($this)
            : $diff->getName($this)->getQuotedName($this);

        foreach ($this->getRemainingForeignKeyConstraintsRequiringRenamedIndexes($diff) as $foreignKey) {
            if (! in_array($foreignKey, $diff->changedForeignKeys, true)) {
                $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName);
            }
        }
865 866 867 868

        return $sql;
    }

869
    /**
870
     * {@inheritDoc}
871 872 873 874 875 876
     */
    protected function getCreateIndexSQLFlags(Index $index)
    {
        $type = '';
        if ($index->isUnique()) {
            $type .= 'UNIQUE ';
Steve Müller's avatar
Steve Müller committed
877
        } elseif ($index->hasFlag('fulltext')) {
878
            $type .= 'FULLTEXT ';
879 880
        } elseif ($index->hasFlag('spatial')) {
            $type .= 'SPATIAL ';
881 882 883 884 885
        }

        return $type;
    }

886
    /**
887
     * {@inheritDoc}
888
     */
889
    public function getIntegerTypeDeclarationSQL(array $field)
890
    {
891
        return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
892 893
    }

894 895 896
    /**
     * {@inheritDoc}
     */
897
    public function getBigIntTypeDeclarationSQL(array $field)
898
    {
899
        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
900 901
    }

902 903 904
    /**
     * {@inheritDoc}
     */
905
    public function getSmallIntTypeDeclarationSQL(array $field)
906
    {
907
        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
908 909
    }

910 911 912 913 914
    /**
     * {@inheritdoc}
     */
    public function getFloatDeclarationSQL(array $field)
    {
915
        return 'DOUBLE PRECISION' . $this->getUnsignedDeclaration($field);
916 917
    }

918 919 920 921 922
    /**
     * {@inheritdoc}
     */
    public function getDecimalTypeDeclarationSQL(array $columnDef)
    {
Steve Müller's avatar
Steve Müller committed
923
        return parent::getDecimalTypeDeclarationSQL($columnDef) . $this->getUnsignedDeclaration($columnDef);
924 925 926 927 928 929 930 931 932 933 934
    }

    /**
     * Get unsigned declaration for a column.
     *
     * @param array $columnDef
     *
     * @return string
     */
    private function getUnsignedDeclaration(array $columnDef)
    {
935
        return ! empty($columnDef['unsigned']) ? ' UNSIGNED' : '';
936 937
    }

938 939 940
    /**
     * {@inheritDoc}
     */
941
    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
942
    {
943
        $autoinc = '';
944
        if ( ! empty($columnDef['autoincrement'])) {
945 946 947
            $autoinc = ' AUTO_INCREMENT';
        }

948
        return $this->getUnsignedDeclaration($columnDef) . $autoinc;
949
    }
950

951
    /**
952
     * {@inheritDoc}
953
     */
954
    public function getAdvancedForeignKeyOptionsSQL(\Doctrine\DBAL\Schema\ForeignKeyConstraint $foreignKey)
955 956
    {
        $query = '';
957 958
        if ($foreignKey->hasOption('match')) {
            $query .= ' MATCH ' . $foreignKey->getOption('match');
959
        }
960
        $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
961

962 963
        return $query;
    }
964

965
    /**
966
     * {@inheritDoc}
967
     */
968
    public function getDropIndexSQL($index, $table=null)
969
    {
970
        if ($index instanceof Index) {
971
            $indexName = $index->getQuotedName($this);
Steve Müller's avatar
Steve Müller committed
972
        } elseif (is_string($index)) {
973 974
            $indexName = $index;
        } else {
975
            throw new \InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
976
        }
977

978
        if ($table instanceof Table) {
979
            $table = $table->getQuotedName($this);
Steve Müller's avatar
Steve Müller committed
980
        } elseif (!is_string($table)) {
981
            throw new \InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
982
        }
983

984
        if ($index instanceof Index && $index->isPrimary()) {
985
            // mysql primary keys are always named "PRIMARY",
986 987 988
            // so we cannot use them in statements because of them being keyword.
            return $this->getDropPrimaryKeySQL($table);
        }
989

990 991
        return 'DROP INDEX ' . $indexName . ' ON ' . $table;
    }
992

993
    /**
994 995 996
     * @param string $table
     *
     * @return string
997 998 999 1000
     */
    protected function getDropPrimaryKeySQL($table)
    {
        return 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY';
1001
    }
1002

1003 1004 1005
    /**
     * {@inheritDoc}
     */
1006
    public function getSetTransactionIsolationSQL($level)
romanb's avatar
romanb committed
1007
    {
1008
        return 'SET SESSION TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
romanb's avatar
romanb committed
1009
    }
1010 1011

    /**
1012
     * {@inheritDoc}
1013 1014 1015 1016 1017
     */
    public function getName()
    {
        return 'mysql';
    }
1018

1019 1020 1021
    /**
     * {@inheritDoc}
     */
1022 1023 1024 1025
    public function getReadLockSQL()
    {
        return 'LOCK IN SHARE MODE';
    }
1026

1027 1028 1029
    /**
     * {@inheritDoc}
     */
1030 1031
    protected function initializeDoctrineTypeMappings()
    {
1032
        $this->doctrineTypeMapping = [
1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049
            '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',
1050 1051 1052
            'float'         => 'float',
            'double'        => 'float',
            'real'          => 'float',
1053 1054 1055
            'decimal'       => 'decimal',
            'numeric'       => 'decimal',
            'year'          => 'date',
1056 1057 1058 1059
            'longblob'      => 'blob',
            'blob'          => 'blob',
            'mediumblob'    => 'blob',
            'tinyblob'      => 'blob',
Steve Müller's avatar
Steve Müller committed
1060 1061
            'binary'        => 'binary',
            'varbinary'     => 'binary',
1062
            'set'           => 'simple_array',
1063
        ];
1064
    }
1065

1066 1067 1068
    /**
     * {@inheritDoc}
     */
1069 1070 1071 1072
    public function getVarcharMaxLength()
    {
        return 65535;
    }
1073

Steve Müller's avatar
Steve Müller committed
1074 1075 1076 1077 1078 1079 1080 1081
    /**
     * {@inheritdoc}
     */
    public function getBinaryMaxLength()
    {
        return 65535;
    }

1082 1083 1084
    /**
     * {@inheritDoc}
     */
1085 1086
    protected function getReservedKeywordsClass()
    {
1087
        return Keywords\MySQLKeywords::class;
1088
    }
1089 1090

    /**
1091
     * {@inheritDoc}
1092 1093 1094 1095 1096 1097
     *
     * MySQL commits a transaction implicitly when DROP TABLE is executed, however not
     * if DROP TEMPORARY TABLE is executed.
     */
    public function getDropTemporaryTableSQL($table)
    {
1098
        if ($table instanceof Table) {
1099
            $table = $table->getQuotedName($this);
Steve Müller's avatar
Steve Müller committed
1100
        } elseif (!is_string($table)) {
Leo's avatar
Leo committed
1101
            throw new \InvalidArgumentException('getDropTemporaryTableSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
1102 1103 1104 1105
        }

        return 'DROP TEMPORARY TABLE ' . $table;
    }
1106 1107

    /**
1108 1109 1110 1111 1112 1113 1114 1115 1116
     * Gets the SQL Snippet used to declare a BLOB column type.
     *     TINYBLOB   : 2 ^  8 - 1 = 255
     *     BLOB       : 2 ^ 16 - 1 = 65535
     *     MEDIUMBLOB : 2 ^ 24 - 1 = 16777215
     *     LONGBLOB   : 2 ^ 32 - 1 = 4294967295
     *
     * @param array $field
     *
     * @return string
1117 1118 1119
     */
    public function getBlobTypeDeclarationSQL(array $field)
    {
1120 1121 1122
        if ( ! empty($field['length']) && is_numeric($field['length'])) {
            $length = $field['length'];

1123
            if ($length <= static::LENGTH_LIMIT_TINYBLOB) {
1124 1125 1126
                return 'TINYBLOB';
            }

1127
            if ($length <= static::LENGTH_LIMIT_BLOB) {
1128 1129 1130
                return 'BLOB';
            }

1131
            if ($length <= static::LENGTH_LIMIT_MEDIUMBLOB) {
1132 1133 1134 1135
                return 'MEDIUMBLOB';
            }
        }

1136 1137
        return 'LONGBLOB';
    }
1138 1139 1140 1141 1142 1143 1144 1145 1146 1147

    /**
     * {@inheritdoc}
     */
    public function quoteStringLiteral($str)
    {
        $str = str_replace('\\', '\\\\', $str); // MySQL requires backslashes to be escaped aswell.

        return parent::quoteStringLiteral($str);
    }
1148 1149 1150 1151 1152 1153

    /**
     * {@inheritdoc}
     */
    public function getDefaultTransactionIsolationLevel()
    {
1154
        return TransactionIsolationLevel::REPEATABLE_READ;
1155
    }
1156
}