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