1<?php 2 3namespace Doctrine\DBAL\Platforms; 4 5use Doctrine\DBAL\Schema\ForeignKeyConstraint; 6use Doctrine\DBAL\Schema\Identifier; 7use Doctrine\DBAL\Schema\Index; 8use Doctrine\DBAL\Schema\Table; 9use Doctrine\DBAL\Schema\TableDiff; 10use Doctrine\DBAL\TransactionIsolationLevel; 11use Doctrine\DBAL\Types\BlobType; 12use Doctrine\DBAL\Types\TextType; 13use InvalidArgumentException; 14 15use function array_diff_key; 16use function array_merge; 17use function array_unique; 18use function array_values; 19use function count; 20use function func_get_args; 21use function implode; 22use function in_array; 23use function is_numeric; 24use function is_string; 25use function sprintf; 26use function str_replace; 27use function strtoupper; 28use function trim; 29 30/** 31 * The MySqlPlatform provides the behavior, features and SQL dialect of the 32 * MySQL database platform. This platform represents a MySQL 5.0 or greater platform that 33 * uses the InnoDB storage engine. 34 * 35 * @todo Rename: MySQLPlatform 36 */ 37class MySqlPlatform extends AbstractPlatform 38{ 39 public const LENGTH_LIMIT_TINYTEXT = 255; 40 public const LENGTH_LIMIT_TEXT = 65535; 41 public const LENGTH_LIMIT_MEDIUMTEXT = 16777215; 42 43 public const LENGTH_LIMIT_TINYBLOB = 255; 44 public const LENGTH_LIMIT_BLOB = 65535; 45 public const LENGTH_LIMIT_MEDIUMBLOB = 16777215; 46 47 /** 48 * {@inheritDoc} 49 */ 50 protected function doModifyLimitQuery($query, $limit, $offset) 51 { 52 if ($limit !== null) { 53 $query .= ' LIMIT ' . $limit; 54 55 if ($offset > 0) { 56 $query .= ' OFFSET ' . $offset; 57 } 58 } elseif ($offset > 0) { 59 // 2^64-1 is the maximum of unsigned BIGINT, the biggest limit possible 60 $query .= ' LIMIT 18446744073709551615 OFFSET ' . $offset; 61 } 62 63 return $query; 64 } 65 66 /** 67 * {@inheritDoc} 68 */ 69 public function getIdentifierQuoteCharacter() 70 { 71 return '`'; 72 } 73 74 /** 75 * {@inheritDoc} 76 */ 77 public function getRegexpExpression() 78 { 79 return 'RLIKE'; 80 } 81 82 /** 83 * {@inheritDoc} 84 * 85 * @deprecated Use application-generated UUIDs instead 86 */ 87 public function getGuidExpression() 88 { 89 return 'UUID()'; 90 } 91 92 /** 93 * {@inheritDoc} 94 */ 95 public function getLocateExpression($str, $substr, $startPos = false) 96 { 97 if ($startPos === false) { 98 return 'LOCATE(' . $substr . ', ' . $str . ')'; 99 } 100 101 return 'LOCATE(' . $substr . ', ' . $str . ', ' . $startPos . ')'; 102 } 103 104 /** 105 * {@inheritDoc} 106 */ 107 public function getConcatExpression() 108 { 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 * {@inheritDoc} 124 */ 125 public function getDateDiffExpression($date1, $date2) 126 { 127 return 'DATEDIFF(' . $date1 . ', ' . $date2 . ')'; 128 } 129 130 /** 131 * {@inheritDoc} 132 */ 133 public function getListDatabasesSQL() 134 { 135 return 'SHOW DATABASES'; 136 } 137 138 /** 139 * {@inheritDoc} 140 */ 141 public function getListTableConstraintsSQL($table) 142 { 143 return 'SHOW INDEX FROM ' . $table; 144 } 145 146 /** 147 * {@inheritDoc} 148 * 149 * Two approaches to listing the table indexes. The information_schema is 150 * preferred, because it doesn't cause problems with SQL keywords such as "order" or "table". 151 */ 152 public function getListTableIndexesSQL($table, $database = null) 153 { 154 if ($database) { 155 $database = $this->quoteStringLiteral($database); 156 $table = $this->quoteStringLiteral($table); 157 158 return 'SELECT NON_UNIQUE AS Non_Unique, INDEX_NAME AS Key_name, COLUMN_NAME AS Column_Name,' . 159 ' SUB_PART AS Sub_Part, INDEX_TYPE AS Index_Type' . 160 ' FROM information_schema.STATISTICS WHERE TABLE_NAME = ' . $table . 161 ' AND TABLE_SCHEMA = ' . $database . 162 ' ORDER BY SEQ_IN_INDEX ASC'; 163 } 164 165 return 'SHOW INDEX FROM ' . $table; 166 } 167 168 /** 169 * {@inheritDoc} 170 */ 171 public function getListViewsSQL($database) 172 { 173 $database = $this->quoteStringLiteral($database); 174 175 return 'SELECT * FROM information_schema.VIEWS WHERE TABLE_SCHEMA = ' . $database; 176 } 177 178 /** 179 * @param string $table 180 * @param string|null $database 181 * 182 * @return string 183 */ 184 public function getListTableForeignKeysSQL($table, $database = null) 185 { 186 $table = $this->quoteStringLiteral($table); 187 188 if ($database !== null) { 189 $database = $this->quoteStringLiteral($database); 190 } 191 192 $sql = 'SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ' . 193 'k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ' . 194 'FROM information_schema.key_column_usage k /*!50116 ' . 195 'INNER JOIN information_schema.referential_constraints c ON ' . 196 ' c.constraint_name = k.constraint_name AND ' . 197 ' c.table_name = ' . $table . ' */ WHERE k.table_name = ' . $table; 198 199 $databaseNameSql = $database ?? 'DATABASE()'; 200 201 return $sql . ' AND k.table_schema = ' . $databaseNameSql 202 . ' /*!50116 AND c.constraint_schema = ' . $databaseNameSql . ' */' 203 . ' AND k.`REFERENCED_COLUMN_NAME` is not NULL'; 204 } 205 206 /** 207 * {@inheritDoc} 208 */ 209 public function getCreateViewSQL($name, $sql) 210 { 211 return 'CREATE VIEW ' . $name . ' AS ' . $sql; 212 } 213 214 /** 215 * {@inheritDoc} 216 */ 217 public function getDropViewSQL($name) 218 { 219 return 'DROP VIEW ' . $name; 220 } 221 222 /** 223 * {@inheritDoc} 224 */ 225 protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) 226 { 227 return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)') 228 : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)'); 229 } 230 231 /** 232 * {@inheritdoc} 233 */ 234 protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed) 235 { 236 return $fixed ? 'BINARY(' . ($length ?: 255) . ')' : 'VARBINARY(' . ($length ?: 255) . ')'; 237 } 238 239 /** 240 * Gets the SQL snippet used to declare a CLOB column type. 241 * TINYTEXT : 2 ^ 8 - 1 = 255 242 * TEXT : 2 ^ 16 - 1 = 65535 243 * MEDIUMTEXT : 2 ^ 24 - 1 = 16777215 244 * LONGTEXT : 2 ^ 32 - 1 = 4294967295 245 * 246 * {@inheritDoc} 247 */ 248 public function getClobTypeDeclarationSQL(array $column) 249 { 250 if (! empty($column['length']) && is_numeric($column['length'])) { 251 $length = $column['length']; 252 253 if ($length <= static::LENGTH_LIMIT_TINYTEXT) { 254 return 'TINYTEXT'; 255 } 256 257 if ($length <= static::LENGTH_LIMIT_TEXT) { 258 return 'TEXT'; 259 } 260 261 if ($length <= static::LENGTH_LIMIT_MEDIUMTEXT) { 262 return 'MEDIUMTEXT'; 263 } 264 } 265 266 return 'LONGTEXT'; 267 } 268 269 /** 270 * {@inheritDoc} 271 */ 272 public function getDateTimeTypeDeclarationSQL(array $column) 273 { 274 if (isset($column['version']) && $column['version'] === true) { 275 return 'TIMESTAMP'; 276 } 277 278 return 'DATETIME'; 279 } 280 281 /** 282 * {@inheritDoc} 283 */ 284 public function getDateTypeDeclarationSQL(array $column) 285 { 286 return 'DATE'; 287 } 288 289 /** 290 * {@inheritDoc} 291 */ 292 public function getTimeTypeDeclarationSQL(array $column) 293 { 294 return 'TIME'; 295 } 296 297 /** 298 * {@inheritDoc} 299 */ 300 public function getBooleanTypeDeclarationSQL(array $column) 301 { 302 return 'TINYINT(1)'; 303 } 304 305 /** 306 * Obtain DBMS specific SQL code portion needed to set the COLLATION 307 * of a column declaration to be used in statements like CREATE TABLE. 308 * 309 * @deprecated Deprecated since version 2.5, Use {@link self::getColumnCollationDeclarationSQL()} instead. 310 * 311 * @param string $collation name of the collation 312 * 313 * @return string DBMS specific SQL code portion needed to set the COLLATION 314 * of a column declaration. 315 */ 316 public function getCollationFieldDeclaration($collation) 317 { 318 return $this->getColumnCollationDeclarationSQL($collation); 319 } 320 321 /** 322 * {@inheritDoc} 323 * 324 * MySql prefers "autoincrement" identity columns since sequences can only 325 * be emulated with a table. 326 */ 327 public function prefersIdentityColumns() 328 { 329 return true; 330 } 331 332 /** 333 * {@inheritDoc} 334 * 335 * MySql supports this through AUTO_INCREMENT columns. 336 */ 337 public function supportsIdentityColumns() 338 { 339 return true; 340 } 341 342 /** 343 * {@inheritDoc} 344 */ 345 public function supportsInlineColumnComments() 346 { 347 return true; 348 } 349 350 /** 351 * {@inheritDoc} 352 */ 353 public function supportsColumnCollation() 354 { 355 return true; 356 } 357 358 /** 359 * {@inheritDoc} 360 */ 361 public function getListTablesSQL() 362 { 363 return "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'"; 364 } 365 366 /** 367 * {@inheritDoc} 368 */ 369 public function getListTableColumnsSQL($table, $database = null) 370 { 371 $table = $this->quoteStringLiteral($table); 372 373 if ($database) { 374 $database = $this->quoteStringLiteral($database); 375 } else { 376 $database = 'DATABASE()'; 377 } 378 379 return 'SELECT COLUMN_NAME AS Field, COLUMN_TYPE AS Type, IS_NULLABLE AS `Null`, ' . 380 'COLUMN_KEY AS `Key`, COLUMN_DEFAULT AS `Default`, EXTRA AS Extra, COLUMN_COMMENT AS Comment, ' . 381 'CHARACTER_SET_NAME AS CharacterSet, COLLATION_NAME AS Collation ' . 382 'FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = ' . $database . ' AND TABLE_NAME = ' . $table . 383 ' ORDER BY ORDINAL_POSITION ASC'; 384 } 385 386 public function getListTableMetadataSQL(string $table, ?string $database = null): string 387 { 388 return sprintf( 389 <<<'SQL' 390SELECT ENGINE, AUTO_INCREMENT, TABLE_COLLATION, TABLE_COMMENT, CREATE_OPTIONS 391FROM information_schema.TABLES 392WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = %s AND TABLE_NAME = %s 393SQL 394 , 395 $database ? $this->quoteStringLiteral($database) : 'DATABASE()', 396 $this->quoteStringLiteral($table) 397 ); 398 } 399 400 /** 401 * {@inheritDoc} 402 */ 403 public function getCreateDatabaseSQL($name) 404 { 405 return 'CREATE DATABASE ' . $name; 406 } 407 408 /** 409 * {@inheritDoc} 410 */ 411 public function getDropDatabaseSQL($name) 412 { 413 return 'DROP DATABASE ' . $name; 414 } 415 416 /** 417 * {@inheritDoc} 418 */ 419 protected function _getCreateTableSQL($name, array $columns, array $options = []) 420 { 421 $queryFields = $this->getColumnDeclarationListSQL($columns); 422 423 if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) { 424 foreach ($options['uniqueConstraints'] as $index => $definition) { 425 $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($index, $definition); 426 } 427 } 428 429 // add all indexes 430 if (isset($options['indexes']) && ! empty($options['indexes'])) { 431 foreach ($options['indexes'] as $index => $definition) { 432 $queryFields .= ', ' . $this->getIndexDeclarationSQL($index, $definition); 433 } 434 } 435 436 // attach all primary keys 437 if (isset($options['primary']) && ! empty($options['primary'])) { 438 $keyColumns = array_unique(array_values($options['primary'])); 439 $queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')'; 440 } 441 442 $query = 'CREATE '; 443 444 if (! empty($options['temporary'])) { 445 $query .= 'TEMPORARY '; 446 } 447 448 $query .= 'TABLE ' . $name . ' (' . $queryFields . ') '; 449 $query .= $this->buildTableOptions($options); 450 $query .= $this->buildPartitionOptions($options); 451 452 $sql = [$query]; 453 $engine = 'INNODB'; 454 455 if (isset($options['engine'])) { 456 $engine = strtoupper(trim($options['engine'])); 457 } 458 459 // Propagate foreign key constraints only for InnoDB. 460 if (isset($options['foreignKeys']) && $engine === 'INNODB') { 461 foreach ((array) $options['foreignKeys'] as $definition) { 462 $sql[] = $this->getCreateForeignKeySQL($definition, $name); 463 } 464 } 465 466 return $sql; 467 } 468 469 /** 470 * {@inheritdoc} 471 */ 472 public function getDefaultValueDeclarationSQL($column) 473 { 474 // Unset the default value if the given column definition does not allow default values. 475 if ($column['type'] instanceof TextType || $column['type'] instanceof BlobType) { 476 $column['default'] = null; 477 } 478 479 return parent::getDefaultValueDeclarationSQL($column); 480 } 481 482 /** 483 * Build SQL for table options 484 * 485 * @param mixed[] $options 486 * 487 * @return string 488 */ 489 private function buildTableOptions(array $options) 490 { 491 if (isset($options['table_options'])) { 492 return $options['table_options']; 493 } 494 495 $tableOptions = []; 496 497 // Charset 498 if (! isset($options['charset'])) { 499 $options['charset'] = 'utf8'; 500 } 501 502 $tableOptions[] = sprintf('DEFAULT CHARACTER SET %s', $options['charset']); 503 504 // Collate 505 if (! isset($options['collate'])) { 506 $options['collate'] = $options['charset'] . '_unicode_ci'; 507 } 508 509 $tableOptions[] = $this->getColumnCollationDeclarationSQL($options['collate']); 510 511 // Engine 512 if (! isset($options['engine'])) { 513 $options['engine'] = 'InnoDB'; 514 } 515 516 $tableOptions[] = sprintf('ENGINE = %s', $options['engine']); 517 518 // Auto increment 519 if (isset($options['auto_increment'])) { 520 $tableOptions[] = sprintf('AUTO_INCREMENT = %s', $options['auto_increment']); 521 } 522 523 // Comment 524 if (isset($options['comment'])) { 525 $tableOptions[] = sprintf('COMMENT = %s ', $this->quoteStringLiteral($options['comment'])); 526 } 527 528 // Row format 529 if (isset($options['row_format'])) { 530 $tableOptions[] = sprintf('ROW_FORMAT = %s', $options['row_format']); 531 } 532 533 return implode(' ', $tableOptions); 534 } 535 536 /** 537 * Build SQL for partition options. 538 * 539 * @param mixed[] $options 540 * 541 * @return string 542 */ 543 private function buildPartitionOptions(array $options) 544 { 545 return isset($options['partition_options']) 546 ? ' ' . $options['partition_options'] 547 : ''; 548 } 549 550 /** 551 * {@inheritDoc} 552 */ 553 public function getAlterTableSQL(TableDiff $diff) 554 { 555 $columnSql = []; 556 $queryParts = []; 557 $newName = $diff->getNewName(); 558 559 if ($newName !== false) { 560 $queryParts[] = 'RENAME TO ' . $newName->getQuotedName($this); 561 } 562 563 foreach ($diff->addedColumns as $column) { 564 if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { 565 continue; 566 } 567 568 $columnArray = array_merge($column->toArray(), [ 569 'comment' => $this->getColumnComment($column), 570 ]); 571 572 $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); 573 } 574 575 foreach ($diff->removedColumns as $column) { 576 if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { 577 continue; 578 } 579 580 $queryParts[] = 'DROP ' . $column->getQuotedName($this); 581 } 582 583 foreach ($diff->changedColumns as $columnDiff) { 584 if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { 585 continue; 586 } 587 588 $column = $columnDiff->column; 589 $columnArray = $column->toArray(); 590 591 // Don't propagate default value changes for unsupported column types. 592 if ( 593 $columnDiff->hasChanged('default') && 594 count($columnDiff->changedProperties) === 1 && 595 ($columnArray['type'] instanceof TextType || $columnArray['type'] instanceof BlobType) 596 ) { 597 continue; 598 } 599 600 $columnArray['comment'] = $this->getColumnComment($column); 601 $queryParts[] = 'CHANGE ' . ($columnDiff->getOldColumnName()->getQuotedName($this)) . ' ' 602 . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); 603 } 604 605 foreach ($diff->renamedColumns as $oldColumnName => $column) { 606 if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { 607 continue; 608 } 609 610 $oldColumnName = new Identifier($oldColumnName); 611 $columnArray = $column->toArray(); 612 $columnArray['comment'] = $this->getColumnComment($column); 613 $queryParts[] = 'CHANGE ' . $oldColumnName->getQuotedName($this) . ' ' 614 . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnArray); 615 } 616 617 if (isset($diff->addedIndexes['primary'])) { 618 $keyColumns = array_unique(array_values($diff->addedIndexes['primary']->getColumns())); 619 $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')'; 620 unset($diff->addedIndexes['primary']); 621 } elseif (isset($diff->changedIndexes['primary'])) { 622 // Necessary in case the new primary key includes a new auto_increment column 623 foreach ($diff->changedIndexes['primary']->getColumns() as $columnName) { 624 if (isset($diff->addedColumns[$columnName]) && $diff->addedColumns[$columnName]->getAutoincrement()) { 625 $keyColumns = array_unique(array_values($diff->changedIndexes['primary']->getColumns())); 626 $queryParts[] = 'DROP PRIMARY KEY'; 627 $queryParts[] = 'ADD PRIMARY KEY (' . implode(', ', $keyColumns) . ')'; 628 unset($diff->changedIndexes['primary']); 629 break; 630 } 631 } 632 } 633 634 $sql = []; 635 $tableSql = []; 636 637 if (! $this->onSchemaAlterTable($diff, $tableSql)) { 638 if (count($queryParts) > 0) { 639 $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' 640 . implode(', ', $queryParts); 641 } 642 643 $sql = array_merge( 644 $this->getPreAlterTableIndexForeignKeySQL($diff), 645 $sql, 646 $this->getPostAlterTableIndexForeignKeySQL($diff) 647 ); 648 } 649 650 return array_merge($sql, $tableSql, $columnSql); 651 } 652 653 /** 654 * {@inheritDoc} 655 */ 656 protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff) 657 { 658 $sql = []; 659 $table = $diff->getName($this)->getQuotedName($this); 660 661 foreach ($diff->changedIndexes as $changedIndex) { 662 $sql = array_merge($sql, $this->getPreAlterTableAlterPrimaryKeySQL($diff, $changedIndex)); 663 } 664 665 foreach ($diff->removedIndexes as $remKey => $remIndex) { 666 $sql = array_merge($sql, $this->getPreAlterTableAlterPrimaryKeySQL($diff, $remIndex)); 667 668 foreach ($diff->addedIndexes as $addKey => $addIndex) { 669 if ($remIndex->getColumns() !== $addIndex->getColumns()) { 670 continue; 671 } 672 673 $indexClause = 'INDEX ' . $addIndex->getName(); 674 675 if ($addIndex->isPrimary()) { 676 $indexClause = 'PRIMARY KEY'; 677 } elseif ($addIndex->isUnique()) { 678 $indexClause = 'UNIQUE INDEX ' . $addIndex->getName(); 679 } 680 681 $query = 'ALTER TABLE ' . $table . ' DROP INDEX ' . $remIndex->getName() . ', '; 682 $query .= 'ADD ' . $indexClause; 683 $query .= ' (' . $this->getIndexFieldDeclarationListSQL($addIndex) . ')'; 684 685 $sql[] = $query; 686 687 unset($diff->removedIndexes[$remKey], $diff->addedIndexes[$addKey]); 688 689 break; 690 } 691 } 692 693 $engine = 'INNODB'; 694 695 if ($diff->fromTable instanceof Table && $diff->fromTable->hasOption('engine')) { 696 $engine = strtoupper(trim($diff->fromTable->getOption('engine'))); 697 } 698 699 // Suppress foreign key constraint propagation on non-supporting engines. 700 if ($engine !== 'INNODB') { 701 $diff->addedForeignKeys = []; 702 $diff->changedForeignKeys = []; 703 $diff->removedForeignKeys = []; 704 } 705 706 $sql = array_merge( 707 $sql, 708 $this->getPreAlterTableAlterIndexForeignKeySQL($diff), 709 parent::getPreAlterTableIndexForeignKeySQL($diff), 710 $this->getPreAlterTableRenameIndexForeignKeySQL($diff) 711 ); 712 713 return $sql; 714 } 715 716 /** 717 * @return string[] 718 */ 719 private function getPreAlterTableAlterPrimaryKeySQL(TableDiff $diff, Index $index) 720 { 721 $sql = []; 722 723 if (! $index->isPrimary() || ! $diff->fromTable instanceof Table) { 724 return $sql; 725 } 726 727 $tableName = $diff->getName($this)->getQuotedName($this); 728 729 // Dropping primary keys requires to unset autoincrement attribute on the particular column first. 730 foreach ($index->getColumns() as $columnName) { 731 if (! $diff->fromTable->hasColumn($columnName)) { 732 continue; 733 } 734 735 $column = $diff->fromTable->getColumn($columnName); 736 737 if ($column->getAutoincrement() !== true) { 738 continue; 739 } 740 741 $column->setAutoincrement(false); 742 743 $sql[] = 'ALTER TABLE ' . $tableName . ' MODIFY ' . 744 $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); 745 746 // original autoincrement information might be needed later on by other parts of the table alteration 747 $column->setAutoincrement(true); 748 } 749 750 return $sql; 751 } 752 753 /** 754 * @param TableDiff $diff The table diff to gather the SQL for. 755 * 756 * @return string[] 757 */ 758 private function getPreAlterTableAlterIndexForeignKeySQL(TableDiff $diff) 759 { 760 $sql = []; 761 $table = $diff->getName($this)->getQuotedName($this); 762 763 foreach ($diff->changedIndexes as $changedIndex) { 764 // Changed primary key 765 if (! $changedIndex->isPrimary() || ! ($diff->fromTable instanceof Table)) { 766 continue; 767 } 768 769 foreach ($diff->fromTable->getPrimaryKeyColumns() as $columnName) { 770 $column = $diff->fromTable->getColumn($columnName); 771 772 // Check if an autoincrement column was dropped from the primary key. 773 if (! $column->getAutoincrement() || in_array($columnName, $changedIndex->getColumns())) { 774 continue; 775 } 776 777 // The autoincrement attribute needs to be removed from the dropped column 778 // before we can drop and recreate the primary key. 779 $column->setAutoincrement(false); 780 781 $sql[] = 'ALTER TABLE ' . $table . ' MODIFY ' . 782 $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); 783 784 // Restore the autoincrement attribute as it might be needed later on 785 // by other parts of the table alteration. 786 $column->setAutoincrement(true); 787 } 788 } 789 790 return $sql; 791 } 792 793 /** 794 * @param TableDiff $diff The table diff to gather the SQL for. 795 * 796 * @return string[] 797 */ 798 protected function getPreAlterTableRenameIndexForeignKeySQL(TableDiff $diff) 799 { 800 $sql = []; 801 $tableName = $diff->getName($this)->getQuotedName($this); 802 803 foreach ($this->getRemainingForeignKeyConstraintsRequiringRenamedIndexes($diff) as $foreignKey) { 804 if (in_array($foreignKey, $diff->changedForeignKeys, true)) { 805 continue; 806 } 807 808 $sql[] = $this->getDropForeignKeySQL($foreignKey, $tableName); 809 } 810 811 return $sql; 812 } 813 814 /** 815 * Returns the remaining foreign key constraints that require one of the renamed indexes. 816 * 817 * "Remaining" here refers to the diff between the foreign keys currently defined in the associated 818 * table and the foreign keys to be removed. 819 * 820 * @param TableDiff $diff The table diff to evaluate. 821 * 822 * @return ForeignKeyConstraint[] 823 */ 824 private function getRemainingForeignKeyConstraintsRequiringRenamedIndexes(TableDiff $diff) 825 { 826 if (empty($diff->renamedIndexes) || ! $diff->fromTable instanceof Table) { 827 return []; 828 } 829 830 $foreignKeys = []; 831 /** @var ForeignKeyConstraint[] $remainingForeignKeys */ 832 $remainingForeignKeys = array_diff_key( 833 $diff->fromTable->getForeignKeys(), 834 $diff->removedForeignKeys 835 ); 836 837 foreach ($remainingForeignKeys as $foreignKey) { 838 foreach ($diff->renamedIndexes as $index) { 839 if ($foreignKey->intersectsIndexColumns($index)) { 840 $foreignKeys[] = $foreignKey; 841 842 break; 843 } 844 } 845 } 846 847 return $foreignKeys; 848 } 849 850 /** 851 * {@inheritdoc} 852 */ 853 protected function getPostAlterTableIndexForeignKeySQL(TableDiff $diff) 854 { 855 return array_merge( 856 parent::getPostAlterTableIndexForeignKeySQL($diff), 857 $this->getPostAlterTableRenameIndexForeignKeySQL($diff) 858 ); 859 } 860 861 /** 862 * @param TableDiff $diff The table diff to gather the SQL for. 863 * 864 * @return string[] 865 */ 866 protected function getPostAlterTableRenameIndexForeignKeySQL(TableDiff $diff) 867 { 868 $sql = []; 869 $newName = $diff->getNewName(); 870 871 if ($newName !== false) { 872 $tableName = $newName->getQuotedName($this); 873 } else { 874 $tableName = $diff->getName($this)->getQuotedName($this); 875 } 876 877 foreach ($this->getRemainingForeignKeyConstraintsRequiringRenamedIndexes($diff) as $foreignKey) { 878 if (in_array($foreignKey, $diff->changedForeignKeys, true)) { 879 continue; 880 } 881 882 $sql[] = $this->getCreateForeignKeySQL($foreignKey, $tableName); 883 } 884 885 return $sql; 886 } 887 888 /** 889 * {@inheritDoc} 890 */ 891 protected function getCreateIndexSQLFlags(Index $index) 892 { 893 $type = ''; 894 if ($index->isUnique()) { 895 $type .= 'UNIQUE '; 896 } elseif ($index->hasFlag('fulltext')) { 897 $type .= 'FULLTEXT '; 898 } elseif ($index->hasFlag('spatial')) { 899 $type .= 'SPATIAL '; 900 } 901 902 return $type; 903 } 904 905 /** 906 * {@inheritDoc} 907 */ 908 public function getIntegerTypeDeclarationSQL(array $column) 909 { 910 return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 911 } 912 913 /** 914 * {@inheritDoc} 915 */ 916 public function getBigIntTypeDeclarationSQL(array $column) 917 { 918 return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 919 } 920 921 /** 922 * {@inheritDoc} 923 */ 924 public function getSmallIntTypeDeclarationSQL(array $column) 925 { 926 return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 927 } 928 929 /** 930 * {@inheritdoc} 931 */ 932 public function getFloatDeclarationSQL(array $column) 933 { 934 return 'DOUBLE PRECISION' . $this->getUnsignedDeclaration($column); 935 } 936 937 /** 938 * {@inheritdoc} 939 */ 940 public function getDecimalTypeDeclarationSQL(array $column) 941 { 942 return parent::getDecimalTypeDeclarationSQL($column) . $this->getUnsignedDeclaration($column); 943 } 944 945 /** 946 * Get unsigned declaration for a column. 947 * 948 * @param mixed[] $columnDef 949 * 950 * @return string 951 */ 952 private function getUnsignedDeclaration(array $columnDef) 953 { 954 return ! empty($columnDef['unsigned']) ? ' UNSIGNED' : ''; 955 } 956 957 /** 958 * {@inheritDoc} 959 */ 960 protected function _getCommonIntegerTypeDeclarationSQL(array $column) 961 { 962 $autoinc = ''; 963 if (! empty($column['autoincrement'])) { 964 $autoinc = ' AUTO_INCREMENT'; 965 } 966 967 return $this->getUnsignedDeclaration($column) . $autoinc; 968 } 969 970 /** 971 * {@inheritDoc} 972 */ 973 public function getColumnCharsetDeclarationSQL($charset) 974 { 975 return 'CHARACTER SET ' . $charset; 976 } 977 978 /** 979 * {@inheritDoc} 980 */ 981 public function getColumnCollationDeclarationSQL($collation) 982 { 983 return 'COLLATE ' . $this->quoteSingleIdentifier($collation); 984 } 985 986 /** 987 * {@inheritDoc} 988 */ 989 public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey) 990 { 991 $query = ''; 992 if ($foreignKey->hasOption('match')) { 993 $query .= ' MATCH ' . $foreignKey->getOption('match'); 994 } 995 996 $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey); 997 998 return $query; 999 } 1000 1001 /** 1002 * {@inheritDoc} 1003 */ 1004 public function getDropIndexSQL($index, $table = null) 1005 { 1006 if ($index instanceof Index) { 1007 $indexName = $index->getQuotedName($this); 1008 } elseif (is_string($index)) { 1009 $indexName = $index; 1010 } else { 1011 throw new InvalidArgumentException( 1012 __METHOD__ . '() expects $index parameter to be string or ' . Index::class . '.' 1013 ); 1014 } 1015 1016 if ($table instanceof Table) { 1017 $table = $table->getQuotedName($this); 1018 } elseif (! is_string($table)) { 1019 throw new InvalidArgumentException( 1020 __METHOD__ . '() expects $table parameter to be string or ' . Table::class . '.' 1021 ); 1022 } 1023 1024 if ($index instanceof Index && $index->isPrimary()) { 1025 // mysql primary keys are always named "PRIMARY", 1026 // so we cannot use them in statements because of them being keyword. 1027 return $this->getDropPrimaryKeySQL($table); 1028 } 1029 1030 return 'DROP INDEX ' . $indexName . ' ON ' . $table; 1031 } 1032 1033 /** 1034 * @param string $table 1035 * 1036 * @return string 1037 */ 1038 protected function getDropPrimaryKeySQL($table) 1039 { 1040 return 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY'; 1041 } 1042 1043 /** 1044 * {@inheritDoc} 1045 */ 1046 public function getSetTransactionIsolationSQL($level) 1047 { 1048 return 'SET SESSION TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level); 1049 } 1050 1051 /** 1052 * {@inheritDoc} 1053 */ 1054 public function getName() 1055 { 1056 return 'mysql'; 1057 } 1058 1059 /** 1060 * {@inheritDoc} 1061 */ 1062 public function getReadLockSQL() 1063 { 1064 return 'LOCK IN SHARE MODE'; 1065 } 1066 1067 /** 1068 * {@inheritDoc} 1069 */ 1070 protected function initializeDoctrineTypeMappings() 1071 { 1072 $this->doctrineTypeMapping = [ 1073 'tinyint' => 'boolean', 1074 'smallint' => 'smallint', 1075 'mediumint' => 'integer', 1076 'int' => 'integer', 1077 'integer' => 'integer', 1078 'bigint' => 'bigint', 1079 'tinytext' => 'text', 1080 'mediumtext' => 'text', 1081 'longtext' => 'text', 1082 'text' => 'text', 1083 'varchar' => 'string', 1084 'string' => 'string', 1085 'char' => 'string', 1086 'date' => 'date', 1087 'datetime' => 'datetime', 1088 'timestamp' => 'datetime', 1089 'time' => 'time', 1090 'float' => 'float', 1091 'double' => 'float', 1092 'real' => 'float', 1093 'decimal' => 'decimal', 1094 'numeric' => 'decimal', 1095 'year' => 'date', 1096 'longblob' => 'blob', 1097 'blob' => 'blob', 1098 'mediumblob' => 'blob', 1099 'tinyblob' => 'blob', 1100 'binary' => 'binary', 1101 'varbinary' => 'binary', 1102 'set' => 'simple_array', 1103 ]; 1104 } 1105 1106 /** 1107 * {@inheritDoc} 1108 */ 1109 public function getVarcharMaxLength() 1110 { 1111 return 65535; 1112 } 1113 1114 /** 1115 * {@inheritdoc} 1116 */ 1117 public function getBinaryMaxLength() 1118 { 1119 return 65535; 1120 } 1121 1122 /** 1123 * {@inheritDoc} 1124 */ 1125 protected function getReservedKeywordsClass() 1126 { 1127 return Keywords\MySQLKeywords::class; 1128 } 1129 1130 /** 1131 * {@inheritDoc} 1132 * 1133 * MySQL commits a transaction implicitly when DROP TABLE is executed, however not 1134 * if DROP TEMPORARY TABLE is executed. 1135 */ 1136 public function getDropTemporaryTableSQL($table) 1137 { 1138 if ($table instanceof Table) { 1139 $table = $table->getQuotedName($this); 1140 } elseif (! is_string($table)) { 1141 throw new InvalidArgumentException( 1142 __METHOD__ . '() expects $table parameter to be string or ' . Table::class . '.' 1143 ); 1144 } 1145 1146 return 'DROP TEMPORARY TABLE ' . $table; 1147 } 1148 1149 /** 1150 * Gets the SQL Snippet used to declare a BLOB column type. 1151 * TINYBLOB : 2 ^ 8 - 1 = 255 1152 * BLOB : 2 ^ 16 - 1 = 65535 1153 * MEDIUMBLOB : 2 ^ 24 - 1 = 16777215 1154 * LONGBLOB : 2 ^ 32 - 1 = 4294967295 1155 * 1156 * {@inheritDoc} 1157 */ 1158 public function getBlobTypeDeclarationSQL(array $column) 1159 { 1160 if (! empty($column['length']) && is_numeric($column['length'])) { 1161 $length = $column['length']; 1162 1163 if ($length <= static::LENGTH_LIMIT_TINYBLOB) { 1164 return 'TINYBLOB'; 1165 } 1166 1167 if ($length <= static::LENGTH_LIMIT_BLOB) { 1168 return 'BLOB'; 1169 } 1170 1171 if ($length <= static::LENGTH_LIMIT_MEDIUMBLOB) { 1172 return 'MEDIUMBLOB'; 1173 } 1174 } 1175 1176 return 'LONGBLOB'; 1177 } 1178 1179 /** 1180 * {@inheritdoc} 1181 */ 1182 public function quoteStringLiteral($str) 1183 { 1184 $str = str_replace('\\', '\\\\', $str); // MySQL requires backslashes to be escaped aswell. 1185 1186 return parent::quoteStringLiteral($str); 1187 } 1188 1189 /** 1190 * {@inheritdoc} 1191 */ 1192 public function getDefaultTransactionIsolationLevel() 1193 { 1194 return TransactionIsolationLevel::REPEATABLE_READ; 1195 } 1196 1197 public function supportsColumnLengthIndexes(): bool 1198 { 1199 return true; 1200 } 1201} 1202