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 !== null) {
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 229
        return $fixed ? ($length > 0 ? 'CHAR(' . $length . ')' : 'CHAR(255)')
                : ($length > 0 ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
230
    }
231

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

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

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

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

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

269 270
        return 'LONGTEXT';
    }
271

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

        return 'DATETIME';
282
    }
283

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

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

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

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

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

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

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

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

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

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

376
        if ($database !== null) {
377
            $database = $this->quoteStringLiteral($database);
378 379
        } else {
            $database = 'DATABASE()';
380
        }
381

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

389
    public function getListTableMetadataSQL(string $table, ?string $database = null): string
390 391 392 393 394 395 396 397
    {
        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
            ,
398
            $database !== null ? $this->quoteStringLiteral($database) : 'DATABASE()',
399 400 401 402
            $this->quoteStringLiteral($table)
        );
    }

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

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

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

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

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

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

        $query = 'CREATE ';
446

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

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

455
        $sql    = [$query];
456
        $engine = 'INNODB';
457

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

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

469 470 471
        return $sql;
    }

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

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

498
        $tableOptions = [];
499 500

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

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

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

512
        $tableOptions[] = $this->getColumnCollationDeclarationSQL($options['collate']);
513

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

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

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

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

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

        return implode(' ', $tableOptions);
    }

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

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

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

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

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

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

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

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

589
            $column      = $columnDiff->column;
590
            $columnArray = $column->toArray();
591 592

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

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

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

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

618
        if (isset($diff->addedIndexes['primary'])) {
619
            $keyColumns   = array_unique(array_values($diff->addedIndexes['primary']->getColumns()));
620 621
            $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')';
            unset($diff->addedIndexes['primary']);
622 623 624 625 626 627 628 629 630 631 632
        } 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;
                }
            }
633 634
        }

635
        $sql      = [];
636
        $tableSql = [];
637

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

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

        return array_merge($sql, $tableSql, $columnSql);
651
    }
652

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

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

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

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

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

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

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

Grégoire Paris's avatar
Grégoire Paris committed
685
                $sql[] = $query;
686

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

                break;
690 691 692
            }
        }

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

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

        return $sql;
    }

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

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

735 736
            $column = $diff->fromTable->getColumn($columnName);

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

741
            $column->setAutoincrement(false);
742

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

        return $sql;
    }

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

        foreach ($diff->changedIndexes as $changedIndex) {
            // Changed primary key
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.
773
                if (! $column->getAutoincrement() || in_array($columnName, $changedIndex->getColumns(), true)) {
774
                    continue;
775
                }
776 777 778 779 780 781 782 783 784 785 786

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

        return $sql;
    }

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

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

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

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

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

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

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

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

        return $sql;
    }

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

        return $type;
    }

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

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

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

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

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

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

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

967
        return $this->getUnsignedDeclaration($columnDef) . $autoinc;
968
    }
969

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

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

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

996
        $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey);
997

998 999
        return $query;
    }
1000

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

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

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

1026 1027
        return 'DROP INDEX ' . $indexName . ' ON ' . $table;
    }
1028

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

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

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

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

1063 1064 1065
    /**
     * {@inheritDoc}
     */
1066 1067
    protected function initializeDoctrineTypeMappings()
    {
1068
        $this->doctrineTypeMapping = [
1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098
            'bigint'     => 'bigint',
            'binary'     => 'binary',
            'blob'       => 'blob',
            'char'       => 'string',
            'date'       => 'date',
            'datetime'   => 'datetime',
            'decimal'    => 'decimal',
            'double'     => 'float',
            'float'      => 'float',
            'int'        => 'integer',
            'integer'    => 'integer',
            'longblob'   => 'blob',
            'longtext'   => 'text',
            'mediumblob' => 'blob',
            'mediumint'  => 'integer',
            'mediumtext' => 'text',
            'numeric'    => 'decimal',
            'real'       => 'float',
            'set'        => 'simple_array',
            'smallint'   => 'smallint',
            'string'     => 'string',
            'text'       => 'text',
            'time'       => 'time',
            'timestamp'  => 'datetime',
            'tinyblob'   => 'blob',
            'tinyint'    => 'boolean',
            'tinytext'   => 'text',
            'varbinary'  => 'binary',
            'varchar'    => 'string',
            'year'       => 'date',
1099
        ];
1100
    }
1101

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

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

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

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

        return 'DROP TEMPORARY TABLE ' . $table;
    }
1142 1143

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

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

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

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

1170 1171
        return 'LONGBLOB';
    }
1172 1173 1174 1175 1176 1177 1178 1179 1180 1181

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

        return parent::quoteStringLiteral($str);
    }
1182 1183 1184 1185 1186 1187

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

1191
    public function supportsColumnLengthIndexes(): bool
1192 1193 1194
    {
        return true;
    }
1195
}