MySqlPlatform.php 34.4 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Platforms;
4

5
use Doctrine\DBAL\Schema\ForeignKeyConstraint;
6
use Doctrine\DBAL\Schema\Identifier;
Benjamin Morel's avatar
Benjamin Morel committed
7 8
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Table;
9
use Doctrine\DBAL\Schema\TableDiff;
10
use Doctrine\DBAL\TransactionIsolationLevel;
11 12
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\TextType;
13
use InvalidArgumentException;
14

15 16 17 18 19 20 21 22 23 24 25 26 27 28
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 sprintf;
use function str_replace;
use function strtoupper;
use function trim;
29

30 31
/**
 * The MySqlPlatform provides the behavior, features and SQL dialect of the
32 33
 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that
 * uses the InnoDB storage engine.
34
 *
Benjamin Morel's avatar
Benjamin Morel committed
35
 * @todo   Rename: MySQLPlatform
36
 */
37
class MySqlPlatform extends AbstractPlatform
38
{
39 40 41
    public const LENGTH_LIMIT_TINYTEXT   = 255;
    public const LENGTH_LIMIT_TEXT       = 65535;
    public const LENGTH_LIMIT_MEDIUMTEXT = 16777215;
42

43 44 45
    public const LENGTH_LIMIT_TINYBLOB   = 255;
    public const LENGTH_LIMIT_BLOB       = 65535;
    public const LENGTH_LIMIT_MEDIUMBLOB = 16777215;
46

47
    /**
48
     * {@inheritDoc}
49 50 51 52 53
     */
    protected function doModifyLimitQuery($query, $limit, $offset)
    {
        if ($limit !== null) {
            $query .= ' LIMIT ' . $limit;
54 55

            if ($offset > 0) {
56 57
                $query .= ' OFFSET ' . $offset;
            }
58 59
        } elseif ($offset > 0) {
            // 2^64-1 is the maximum of unsigned BIGINT, the biggest limit possible
60 61 62 63 64 65
            $query .= ' LIMIT 18446744073709551615 OFFSET ' . $offset;
        }

        return $query;
    }

romanb's avatar
romanb committed
66
    /**
67
     * {@inheritDoc}
romanb's avatar
romanb committed
68 69 70 71
     */
    public function getIdentifierQuoteCharacter()
    {
        return '`';
72
    }
73

74
    /**
75
     * {@inheritDoc}
76 77 78 79 80 81 82
     */
    public function getRegexpExpression()
    {
        return 'RLIKE';
    }

    /**
83
     * {@inheritDoc}
84 85
     *
     * @deprecated Use application-generated UUIDs instead
86 87 88 89 90 91
     */
    public function getGuidExpression()
    {
        return 'UUID()';
    }

92
    /**
93
     * {@inheritDoc}
94 95 96
     */
    public function getLocateExpression($str, $substr, $startPos = false)
    {
97
        if ($startPos === false) {
98 99
            return 'LOCATE(' . $substr . ', ' . $str . ')';
        }
100

101
        return 'LOCATE(' . $substr . ', ' . $str . ', ' . $startPos . ')';
102 103
    }

104
    /**
105
     * {@inheritDoc}
106 107 108
     */
    public function getConcatExpression()
    {
109
        return sprintf('CONCAT(%s)', implode(', ', func_get_args()));
110
    }
111

112
    /**
113
     * {@inheritdoc}
114
     */
115
    protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
116
    {
117
        $function = $operator === '+' ? 'DATE_ADD' : 'DATE_SUB';
118

119
        return $function . '(' . $date . ', INTERVAL ' . $interval . ' ' . $unit . ')';
120 121
    }

122 123 124
    /**
     * {@inheritDoc}
     */
125
    public function getDateDiffExpression($date1, $date2)
126
    {
127
        return 'DATEDIFF(' . $date1 . ', ' . $date2 . ')';
128
    }
129

Benjamin Morel's avatar
Benjamin Morel committed
130 131 132
    /**
     * {@inheritDoc}
     */
133
    public function getListDatabasesSQL()
134 135 136 137
    {
        return 'SHOW DATABASES';
    }

Benjamin Morel's avatar
Benjamin Morel committed
138 139 140
    /**
     * {@inheritDoc}
     */
141
    public function getListTableConstraintsSQL($table)
142
    {
143
        return 'SHOW INDEX FROM ' . $table;
144 145
    }

146
    /**
147 148
     * {@inheritDoc}
     *
149
     * Two approaches to listing the table indexes. The information_schema is
Pascal Borreli's avatar
Pascal Borreli committed
150
     * preferred, because it doesn't cause problems with SQL keywords such as "order" or "table".
151 152
     */
    public function getListTableIndexesSQL($table, $currentDatabase = null)
153
    {
154
        if ($currentDatabase) {
155
            $currentDatabase = $this->quoteStringLiteral($currentDatabase);
156
            $table           = $this->quoteStringLiteral($table);
157

158 159 160 161
            return 'SELECT NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, COLUMN_NAME AS Column_Name,' .
                   ' SUB_PART AS Sub_Part, INDEX_TYPE AS Index_Type' .
                   ' FROM information_schema.STATISTICS WHERE TABLE_NAME = ' . $table .
                   ' AND TABLE_SCHEMA = ' . $currentDatabase .
162
                   ' ORDER BY SEQ_IN_INDEX ASC';
163
        }
164 165

        return 'SHOW INDEX FROM ' . $table;
166 167
    }

Benjamin Morel's avatar
Benjamin Morel committed
168 169 170
    /**
     * {@inheritDoc}
     */
171
    public function getListViewsSQL($database)
172
    {
173 174
        $database = $this->quoteStringLiteral($database);

175
        return 'SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = ' . $database;
176 177
    }

Benjamin Morel's avatar
Benjamin Morel committed
178
    /**
179 180 181 182
     * @param string      $table
     * @param string|null $database
     *
     * @return string
Benjamin Morel's avatar
Benjamin Morel committed
183
     */
184
    public function getListTableForeignKeysSQL($table, $database = null)
185
    {
186 187
        $table = $this->quoteStringLiteral($table);

188
        if ($database !== null) {
189 190 191
            $database = $this->quoteStringLiteral($database);
        }

192 193 194 195 196
        $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 ' .
               'INNER JOIN information_schema.referential_constraints c ON ' .
               '  c.constraint_name = k.constraint_name AND ' .
197
               '  c.table_name = ' . $table . ' */ WHERE k.table_name = ' . $table;
198

199
        $databaseNameSql = $database ?? 'DATABASE()';
200

201
        $sql .= ' AND k.table_schema = ' . $databaseNameSql . ' /*!50116 AND c.constraint_schema = ' . $databaseNameSql . ' */';
202
        $sql .= ' AND k.`REFERENCED_COLUMN_NAME` is not NULL';
203 204 205 206

        return $sql;
    }

Benjamin Morel's avatar
Benjamin Morel committed
207 208 209
    /**
     * {@inheritDoc}
     */
210
    public function getCreateViewSQL($name, $sql)
211 212 213 214
    {
        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
    }

Benjamin Morel's avatar
Benjamin Morel committed
215 216 217
    /**
     * {@inheritDoc}
     */
218
    public function getDropViewSQL($name)
219
    {
220
        return 'DROP VIEW ' . $name;
221 222
    }

223
    /**
224
     * {@inheritDoc}
225
     */
226
    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
227 228
    {
        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)')
229
                : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
230
    }
231

Steve Müller's avatar
Steve Müller committed
232 233 234 235 236 237 238 239
    /**
     * {@inheritdoc}
     */
    protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
    {
        return $fixed ? 'BINARY(' . ($length ?: 255) . ')' : 'VARBINARY(' . ($length ?: 255) . ')';
    }

240
    /**
241 242 243 244 245 246
     * 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
     *
247
     * {@inheritDoc}
248
     */
249
    public function getClobTypeDeclarationSQL(array $field)
250
    {
251
        if (! empty($field['length']) && is_numeric($field['length'])) {
252
            $length = $field['length'];
253

254
            if ($length <= static::LENGTH_LIMIT_TINYTEXT) {
255
                return 'TINYTEXT';
256 257
            }

258
            if ($length <= static::LENGTH_LIMIT_TEXT) {
259
                return 'TEXT';
260 261
            }

262
            if ($length <= static::LENGTH_LIMIT_MEDIUMTEXT) {
263 264 265
                return 'MEDIUMTEXT';
            }
        }
266

267 268
        return 'LONGTEXT';
    }
269

270
    /**
271
     * {@inheritDoc}
272
     */
273
    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
274
    {
275
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] === true) {
276 277
            return 'TIMESTAMP';
        }
278 279

        return 'DATETIME';
280
    }
281

282
    /**
283
     * {@inheritDoc}
284
     */
285
    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
286 287 288
    {
        return 'DATE';
    }
289

290
    /**
291
     * {@inheritDoc}
292
     */
293
    public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
294 295
    {
        return 'TIME';
296
    }
297

298
    /**
299
     * {@inheritDoc}
300
     */
301
    public function getBooleanTypeDeclarationSQL(array $field)
302 303 304 305
    {
        return 'TINYINT(1)';
    }

306 307 308 309
    /**
     * Obtain DBMS specific SQL code portion needed to set the COLLATION
     * of a field declaration to be used in statements like CREATE TABLE.
     *
310 311
     * @deprecated Deprecated since version 2.5, Use {@link self::getColumnCollationDeclarationSQL()} instead.
     *
312
     * @param string $collation name of the collation
313
     *
314 315 316 317 318
     * @return string  DBMS specific SQL code portion needed to set the COLLATION
     *                 of a field declaration.
     */
    public function getCollationFieldDeclaration($collation)
    {
319
        return $this->getColumnCollationDeclarationSQL($collation);
320
    }
321

322
    /**
323 324
     * {@inheritDoc}
     *
325 326 327 328 329 330 331
     * MySql prefers "autoincrement" identity columns since sequences can only
     * be emulated with a table.
     */
    public function prefersIdentityColumns()
    {
        return true;
    }
332

romanb's avatar
romanb committed
333
    /**
334
     * {@inheritDoc}
romanb's avatar
romanb committed
335
     *
336
     * MySql supports this through AUTO_INCREMENT columns.
romanb's avatar
romanb committed
337 338 339 340
     */
    public function supportsIdentityColumns()
    {
        return true;
341 342
    }

343 344 345
    /**
     * {@inheritDoc}
     */
346 347 348
    public function supportsInlineColumnComments()
    {
        return true;
romanb's avatar
romanb committed
349
    }
350

Benjamin Morel's avatar
Benjamin Morel committed
351 352 353
    /**
     * {@inheritDoc}
     */
354 355 356 357 358
    public function supportsColumnCollation()
    {
        return true;
    }

359 360 361
    /**
     * {@inheritDoc}
     */
362
    public function getListTablesSQL()
363
    {
364
        return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'";
365
    }
366

Benjamin Morel's avatar
Benjamin Morel committed
367 368 369
    /**
     * {@inheritDoc}
     */
370
    public function getListTableColumnsSQL($table, $database = null)
371
    {
372 373
        $table = $this->quoteStringLiteral($table);

374
        if ($database) {
375
            $database = $this->quoteStringLiteral($database);
376 377
        } else {
            $database = 'DATABASE()';
378
        }
379

380 381 382
        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 ' .
383 384
               'FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ' . $database . ' AND TABLE_NAME = ' . $table .
               ' ORDER BY ORDINAL_POSITION ASC';
385 386
    }

387
    public function getListTableMetadataSQL(string $table, ?string $database = null): string
388 389 390 391 392 393 394 395 396 397 398 399 400
    {
        return sprintf(
            <<<'SQL'
SELECT ENGINE, AUTO_INCREMENT, TABLE_COLLATION, TABLE_COMMENT, CREATE_OPTIONS
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = %s AND TABLE_NAME = %s
SQL
            ,
            $database ? $this->quoteStringLiteral($database) : 'DATABASE()',
            $this->quoteStringLiteral($table)
        );
    }

401
    /**
402
     * {@inheritDoc}
403
     */
404
    public function getCreateDatabaseSQL($name)
405
    {
406
        return 'CREATE DATABASE ' . $name;
407
    }
408

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

417
    /**
418
     * {@inheritDoc}
419
     */
420
    protected function _getCreateTableSQL($tableName, array $columns, array $options = [])
421
    {
422
        $queryFields = $this->getColumnDeclarationListSQL($columns);
423

424
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
425
            foreach ($options['uniqueConstraints'] as $index => $definition) {
426
                $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($index, $definition);
427
            }
428 429 430 431
        }

        // add all indexes
        if (isset($options['indexes']) && ! empty($options['indexes'])) {
Steve Müller's avatar
Steve Müller committed
432
            foreach ($options['indexes'] as $index => $definition) {
433
                $queryFields .= ', ' . $this->getIndexDeclarationSQL($index, $definition);
434 435 436 437 438
            }
        }

        // attach all primary keys
        if (isset($options['primary']) && ! empty($options['primary'])) {
439
            $keyColumns   = array_unique(array_values($options['primary']));
440 441 442 443
            $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')';
        }

        $query = 'CREATE ';
444

445
        if (! empty($options['temporary'])) {
446 447
            $query .= 'TEMPORARY ';
        }
448

449
        $query .= 'TABLE ' . $tableName . ' (' . $queryFields . ') ';
450 451
        $query .= $this->buildTableOptions($options);
        $query .= $this->buildPartitionOptions($options);
452

453
        $sql    = [$query];
454
        $engine = 'INNODB';
455

456 457 458 459 460 461
        if (isset($options['engine'])) {
            $engine = strtoupper(trim($options['engine']));
        }

        // Propagate foreign key constraints only for InnoDB.
        if (isset($options['foreignKeys']) && $engine === 'INNODB') {
462 463 464 465
            foreach ((array) $options['foreignKeys'] as $definition) {
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
            }
        }
466

467 468 469
        return $sql;
    }

470 471 472 473 474 475 476 477 478 479 480 481 482
    /**
     * {@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);
    }

483 484 485
    /**
     * Build SQL for table options
     *
486
     * @param mixed[] $options
487 488 489 490 491 492 493
     *
     * @return string
     */
    private function buildTableOptions(array $options)
    {
        if (isset($options['table_options'])) {
            return $options['table_options'];
494
        }
495

496
        $tableOptions = [];
497 498

        // Charset
499
        if (! isset($options['charset'])) {
500
            $options['charset'] = 'utf8';
501 502
        }

503 504 505
        $tableOptions[] = sprintf('DEFAULT CHARACTER SET %s', $options['charset']);

        // Collate
506
        if (! isset($options['collate'])) {
507
            $options['collate'] = $options['charset'] . '_unicode_ci';
508
        }
509

510
        $tableOptions[] = $this->getColumnCollationDeclarationSQL($options['collate']);
511

512
        // Engine
513
        if (! isset($options['engine'])) {
514
            $options['engine'] = 'InnoDB';
515
        }
516

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

519 520 521
        // Auto increment
        if (isset($options['auto_increment'])) {
            $tableOptions[] = sprintf('AUTO_INCREMENT = %s', $options['auto_increment']);
522
        }
523

524 525
        // Comment
        if (isset($options['comment'])) {
526
            $tableOptions[] = sprintf('COMMENT = %s ', $this->quoteStringLiteral($options['comment']));
527 528 529 530 531 532 533 534 535 536 537 538 539
        }

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

        return implode(' ', $tableOptions);
    }

    /**
     * Build SQL for partition options.
     *
540
     * @param mixed[] $options
541 542 543 544 545
     *
     * @return string
     */
    private function buildPartitionOptions(array $options)
    {
546
        return isset($options['partition_options'])
547
            ? ' ' . $options['partition_options']
548
            : '';
549
    }
550

551
    /**
552
     * {@inheritDoc}
553
     */
554
    public function getAlterTableSQL(TableDiff $diff)
555
    {
556
        $columnSql  = [];
557
        $queryParts = [];
Sergei Morozov's avatar
Sergei Morozov committed
558 559 560 561
        $newName    = $diff->getNewName();

        if ($newName !== false) {
            $queryParts[] = 'RENAME TO ' . $newName->getQuotedName($this);
562 563
        }

564
        foreach ($diff->addedColumns as $column) {
565 566
            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
                continue;
567 568
            }

569
            $columnArray            = $column->toArray();
570
            $columnArray['comment'] = $this->getColumnComment($column);
571
            $queryParts[]           = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray);
572 573
        }

574
        foreach ($diff->removedColumns as $column) {
575 576
            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
                continue;
577 578
            }

579
            $queryParts[] =  'DROP ' . $column->getQuotedName($this);
580 581
        }

582
        foreach ($diff->changedColumns as $columnDiff) {
583 584
            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
                continue;
585 586
            }

587
            $column      = $columnDiff->column;
588
            $columnArray = $column->toArray();
589 590

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

599
            $columnArray['comment'] = $this->getColumnComment($column);
600
            $queryParts[]           =  'CHANGE ' . ($columnDiff->getOldColumnName()->getQuotedName($this)) . ' '
601
                    . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray);
602 603
        }

604
        foreach ($diff->renamedColumns as $oldColumnName => $column) {
605 606
            if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
                continue;
607 608
            }

609 610
            $oldColumnName          = new Identifier($oldColumnName);
            $columnArray            = $column->toArray();
611
            $columnArray['comment'] = $this->getColumnComment($column);
612
            $queryParts[]           =  'CHANGE ' . $oldColumnName->getQuotedName($this) . ' '
613
                    . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray);
614 615
        }

616
        if (isset($diff->addedIndexes['primary'])) {
617
            $keyColumns   = array_unique(array_values($diff->addedIndexes['primary']->getColumns()));
618 619
            $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')';
            unset($diff->addedIndexes['primary']);
620 621 622 623 624 625 626 627 628 629 630
        } elseif (isset($diff->changedIndexes['primary'])) {
            // Necessary in case the new primary key includes a new auto_increment column
            foreach ($diff->changedIndexes['primary']->getColumns() as $columnName) {
                if (isset($diff->addedColumns[$columnName]) && $diff->addedColumns[$columnName]->getAutoincrement()) {
                    $keyColumns   = array_unique(array_values($diff->changedIndexes['primary']->getColumns()));
                    $queryParts[] = 'DROP PRIMARY KEY';
                    $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')';
                    unset($diff->changedIndexes['primary']);
                    break;
                }
            }
631 632
        }

633
        $sql      = [];
634
        $tableSql = [];
635

636
        if (! $this->onSchemaAlterTable($diff, $tableSql)) {
637
            if (count($queryParts) > 0) {
638
                $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . implode(', ', $queryParts);
639
            }
Grégoire Paris's avatar
Grégoire Paris committed
640

641 642 643 644 645
            $sql = array_merge(
                $this->getPreAlterTableIndexForeignKeySQL($diff),
                $sql,
                $this->getPostAlterTableIndexForeignKeySQL($diff)
            );
646
        }
647 648

        return array_merge($sql, $tableSql, $columnSql);
649
    }
650

651
    /**
652
     * {@inheritDoc}
653 654 655
     */
    protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff)
    {
656
        $sql   = [];
657
        $table = $diff->getName($this)->getQuotedName($this);
658

659 660 661
        foreach ($diff->changedIndexes as $changedIndex) {
            $sql = array_merge($sql, $this->getPreAlterTableAlterPrimaryKeySQL($diff, $changedIndex));
        }
andig's avatar
andig committed
662

663 664
        foreach ($diff->removedIndexes as $remKey => $remIndex) {
            $sql = array_merge($sql, $this->getPreAlterTableAlterPrimaryKeySQL($diff, $remIndex));
665 666

            foreach ($diff->addedIndexes as $addKey => $addIndex) {
Grégoire Paris's avatar
Grégoire Paris committed
667 668 669
                if ($remIndex->getColumns() !== $addIndex->getColumns()) {
                    continue;
                }
670

Grégoire Paris's avatar
Grégoire Paris committed
671
                $indexClause = 'INDEX ' . $addIndex->getName();
672

Grégoire Paris's avatar
Grégoire Paris committed
673 674 675 676 677
                if ($addIndex->isPrimary()) {
                    $indexClause = 'PRIMARY KEY';
                } elseif ($addIndex->isUnique()) {
                    $indexClause = 'UNIQUE INDEX ' . $addIndex->getName();
                }
678

Grégoire Paris's avatar
Grégoire Paris committed
679 680 681
                $query  = 'ALTER TABLE ' . $table . ' DROP INDEX ' . $remIndex->getName() . ', ';
                $query .= 'ADD ' . $indexClause;
                $query .= ' (' . $this->getIndexFieldDeclarationListSQL($addIndex) . ')';
682

Grégoire Paris's avatar
Grégoire Paris committed
683
                $sql[] = $query;
684

Grégoire Paris's avatar
Grégoire Paris committed
685 686 687
                unset($diff->removedIndexes[$remKey], $diff->addedIndexes[$addKey]);

                break;
688 689 690
            }
        }

691 692 693 694 695 696 697
        $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.
698
        if ($engine !== 'INNODB') {
699 700 701
            $diff->addedForeignKeys   = [];
            $diff->changedForeignKeys = [];
            $diff->removedForeignKeys = [];
702 703
        }

704 705
        $sql = array_merge(
            $sql,
706
            $this->getPreAlterTableAlterIndexForeignKeySQL($diff),
707 708 709 710 711 712 713
            parent::getPreAlterTableIndexForeignKeySQL($diff),
            $this->getPreAlterTableRenameIndexForeignKeySQL($diff)
        );

        return $sql;
    }

714 715 716 717 718
    /**
     * @return string[]
     */
    private function getPreAlterTableAlterPrimaryKeySQL(TableDiff $diff, Index $index)
    {
719
        $sql = [];
720 721 722 723 724 725 726 727 728

        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) {
729 730 731
            if (! $diff->fromTable->hasColumn($columnName)) {
                continue;
            }
732

733 734
            $column = $diff->fromTable->getColumn($columnName);

735 736 737
            if ($column->getAutoincrement() !== true) {
                continue;
            }
738

739
            $column->setAutoincrement(false);
740

741 742 743 744 745
            $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);
746 747 748 749 750
        }

        return $sql;
    }

751 752 753
    /**
     * @param TableDiff $diff The table diff to gather the SQL for.
     *
754
     * @return string[]
755 756 757
     */
    private function getPreAlterTableAlterIndexForeignKeySQL(TableDiff $diff)
    {
758
        $sql   = [];
759 760 761 762
        $table = $diff->getName($this)->getQuotedName($this);

        foreach ($diff->changedIndexes as $changedIndex) {
            // Changed primary key
763 764 765 766 767 768 769 770 771 772
            if (! $changedIndex->isPrimary() || ! ($diff->fromTable instanceof Table)) {
                continue;
            }

            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())) {
                    continue;
773
                }
774 775 776 777 778 779 780 781 782 783 784

                // 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);
785 786 787 788 789 790
            }
        }

        return $sql;
    }

791 792 793
    /**
     * @param TableDiff $diff The table diff to gather the SQL for.
     *
794
     * @return string[]
795 796 797
     */
    protected function getPreAlterTableRenameIndexForeignKeySQL(TableDiff $diff)
    {
798
        $sql       = [];
799 800 801
        $tableName = $diff->getName($this)->getQuotedName($this);

        foreach ($this->getRemainingForeignKeyConstraintsRequiringRenamedIndexes($diff) as $foreignKey) {
802 803
            if (in_array($foreignKey, $diff->changedForeignKeys, true)) {
                continue;
804
            }
805 806

            $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName);
807 808 809 810 811 812 813 814 815 816 817 818 819
        }

        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.
     *
820
     * @return ForeignKeyConstraint[]
821 822 823 824
     */
    private function getRemainingForeignKeyConstraintsRequiringRenamedIndexes(TableDiff $diff)
    {
        if (empty($diff->renamedIndexes) || ! $diff->fromTable instanceof Table) {
825
            return [];
826 827
        }

828
        $foreignKeys = [];
829
        /** @var ForeignKeyConstraint[] $remainingForeignKeys */
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 855 856 857 858 859 860 861
        $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.
     *
862
     * @return string[]
863 864 865
     */
    protected function getPostAlterTableRenameIndexForeignKeySQL(TableDiff $diff)
    {
Sergei Morozov's avatar
Sergei Morozov committed
866 867 868 869 870 871 872 873
        $sql     = [];
        $newName = $diff->getNewName();

        if ($newName !== false) {
            $tableName = $newName->getQuotedName($this);
        } else {
            $tableName = $diff->getName($this)->getQuotedName($this);
        }
874 875

        foreach ($this->getRemainingForeignKeyConstraintsRequiringRenamedIndexes($diff) as $foreignKey) {
876 877
            if (in_array($foreignKey, $diff->changedForeignKeys, true)) {
                continue;
878
            }
879 880

            $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName);
881
        }
882 883 884 885

        return $sql;
    }

886
    /**
887
     * {@inheritDoc}
888 889 890 891 892 893
     */
    protected function getCreateIndexSQLFlags(Index $index)
    {
        $type = '';
        if ($index->isUnique()) {
            $type .= 'UNIQUE ';
Steve Müller's avatar
Steve Müller committed
894
        } elseif ($index->hasFlag('fulltext')) {
895
            $type .= 'FULLTEXT ';
896 897
        } elseif ($index->hasFlag('spatial')) {
            $type .= 'SPATIAL ';
898 899 900 901 902
        }

        return $type;
    }

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

911 912 913
    /**
     * {@inheritDoc}
     */
914
    public function getBigIntTypeDeclarationSQL(array $field)
915
    {
916
        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
917 918
    }

919 920 921
    /**
     * {@inheritDoc}
     */
922
    public function getSmallIntTypeDeclarationSQL(array $field)
923
    {
924
        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
925 926
    }

927 928 929 930 931
    /**
     * {@inheritdoc}
     */
    public function getFloatDeclarationSQL(array $field)
    {
932
        return 'DOUBLE PRECISION' . $this->getUnsignedDeclaration($field);
933 934
    }

935 936 937 938 939
    /**
     * {@inheritdoc}
     */
    public function getDecimalTypeDeclarationSQL(array $columnDef)
    {
Steve Müller's avatar
Steve Müller committed
940
        return parent::getDecimalTypeDeclarationSQL($columnDef) . $this->getUnsignedDeclaration($columnDef);
941 942 943 944 945
    }

    /**
     * Get unsigned declaration for a column.
     *
946
     * @param mixed[] $columnDef
947 948 949 950 951
     *
     * @return string
     */
    private function getUnsignedDeclaration(array $columnDef)
    {
952
        return ! empty($columnDef['unsigned']) ? ' UNSIGNED' : '';
953 954
    }

955 956 957
    /**
     * {@inheritDoc}
     */
958
    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
959
    {
960
        $autoinc = '';
961
        if (! empty($columnDef['autoincrement'])) {
962 963 964
            $autoinc = ' AUTO_INCREMENT';
        }

965
        return $this->getUnsignedDeclaration($columnDef) . $autoinc;
966
    }
967

968 969 970 971 972 973 974 975
    /**
     * {@inheritDoc}
     */
    public function getColumnCharsetDeclarationSQL($charset)
    {
        return 'CHARACTER SET ' . $charset;
    }

976 977 978 979 980 981 982 983
    /**
     * {@inheritDoc}
     */
    public function getColumnCollationDeclarationSQL($collation)
    {
        return 'COLLATE ' . $this->quoteSingleIdentifier($collation);
    }

984
    /**
985
     * {@inheritDoc}
986
     */
987
    public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey)
988 989
    {
        $query = '';
990 991
        if ($foreignKey->hasOption('match')) {
            $query .= ' MATCH ' . $foreignKey->getOption('match');
992
        }
Grégoire Paris's avatar
Grégoire Paris committed
993

994
        $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
995

996 997
        return $query;
    }
998

999
    /**
1000
     * {@inheritDoc}
1001
     */
1002
    public function getDropIndexSQL($index, $table = null)
1003
    {
1004
        if ($index instanceof Index) {
1005
            $indexName = $index->getQuotedName($this);
Steve Müller's avatar
Steve Müller committed
1006
        } elseif (is_string($index)) {
1007 1008
            $indexName = $index;
        } else {
1009
            throw new InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
1010
        }
1011

1012
        if ($table instanceof Table) {
1013
            $table = $table->getQuotedName($this);
1014 1015
        } elseif (! is_string($table)) {
            throw new InvalidArgumentException('MysqlPlatform::getDropIndexSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
1016
        }
1017

1018
        if ($index instanceof Index && $index->isPrimary()) {
1019
            // mysql primary keys are always named "PRIMARY",
1020 1021 1022
            // so we cannot use them in statements because of them being keyword.
            return $this->getDropPrimaryKeySQL($table);
        }
1023

1024 1025
        return 'DROP INDEX ' . $indexName . ' ON ' . $table;
    }
1026

1027
    /**
1028 1029 1030
     * @param string $table
     *
     * @return string
1031 1032 1033 1034
     */
    protected function getDropPrimaryKeySQL($table)
    {
        return 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY';
1035
    }
1036

1037 1038 1039
    /**
     * {@inheritDoc}
     */
1040
    public function getSetTransactionIsolationSQL($level)
romanb's avatar
romanb committed
1041
    {
1042
        return 'SET SESSION TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
romanb's avatar
romanb committed
1043
    }
1044 1045

    /**
1046
     * {@inheritDoc}
1047 1048 1049 1050 1051
     */
    public function getName()
    {
        return 'mysql';
    }
1052

1053 1054 1055
    /**
     * {@inheritDoc}
     */
1056 1057 1058 1059
    public function getReadLockSQL()
    {
        return 'LOCK IN SHARE MODE';
    }
1060

1061 1062 1063
    /**
     * {@inheritDoc}
     */
1064 1065
    protected function initializeDoctrineTypeMappings()
    {
1066
        $this->doctrineTypeMapping = [
1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083
            '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',
1084 1085 1086
            'float'         => 'float',
            'double'        => 'float',
            'real'          => 'float',
1087 1088 1089
            'decimal'       => 'decimal',
            'numeric'       => 'decimal',
            'year'          => 'date',
1090 1091 1092 1093
            'longblob'      => 'blob',
            'blob'          => 'blob',
            'mediumblob'    => 'blob',
            'tinyblob'      => 'blob',
Steve Müller's avatar
Steve Müller committed
1094 1095
            'binary'        => 'binary',
            'varbinary'     => 'binary',
1096
            'set'           => 'simple_array',
1097
        ];
1098
    }
1099

1100 1101 1102
    /**
     * {@inheritDoc}
     */
1103 1104 1105 1106
    public function getVarcharMaxLength()
    {
        return 65535;
    }
1107

Steve Müller's avatar
Steve Müller committed
1108 1109 1110 1111 1112 1113 1114 1115
    /**
     * {@inheritdoc}
     */
    public function getBinaryMaxLength()
    {
        return 65535;
    }

1116 1117 1118
    /**
     * {@inheritDoc}
     */
1119 1120
    protected function getReservedKeywordsClass()
    {
1121
        return Keywords\MySQLKeywords::class;
1122
    }
1123 1124

    /**
1125
     * {@inheritDoc}
1126 1127 1128 1129 1130 1131
     *
     * MySQL commits a transaction implicitly when DROP TABLE is executed, however not
     * if DROP TEMPORARY TABLE is executed.
     */
    public function getDropTemporaryTableSQL($table)
    {
1132
        if ($table instanceof Table) {
1133
            $table = $table->getQuotedName($this);
1134 1135
        } elseif (! is_string($table)) {
            throw new InvalidArgumentException('getDropTemporaryTableSQL() expects $table parameter to be string or \Doctrine\DBAL\Schema\Table.');
1136 1137 1138 1139
        }

        return 'DROP TEMPORARY TABLE ' . $table;
    }
1140 1141

    /**
1142 1143 1144 1145 1146 1147
     * 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
     *
1148
     * {@inheritDoc}
1149 1150 1151
     */
    public function getBlobTypeDeclarationSQL(array $field)
    {
1152
        if (! empty($field['length']) && is_numeric($field['length'])) {
1153 1154
            $length = $field['length'];

1155
            if ($length <= static::LENGTH_LIMIT_TINYBLOB) {
1156 1157 1158
                return 'TINYBLOB';
            }

1159
            if ($length <= static::LENGTH_LIMIT_BLOB) {
1160 1161 1162
                return 'BLOB';
            }

1163
            if ($length <= static::LENGTH_LIMIT_MEDIUMBLOB) {
1164 1165 1166 1167
                return 'MEDIUMBLOB';
            }
        }

1168 1169
        return 'LONGBLOB';
    }
1170 1171 1172 1173 1174 1175 1176 1177 1178 1179

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

        return parent::quoteStringLiteral($str);
    }
1180 1181 1182 1183 1184 1185

    /**
     * {@inheritdoc}
     */
    public function getDefaultTransactionIsolationLevel()
    {
1186
        return TransactionIsolationLevel::REPEATABLE_READ;
1187
    }
1188

1189
    public function supportsColumnLengthIndexes(): bool
1190 1191 1192
    {
        return true;
    }
1193
}