1<?php 2 3namespace Doctrine\DBAL\Platforms; 4 5use Doctrine\DBAL\Exception; 6use Doctrine\DBAL\Schema\Column; 7use Doctrine\DBAL\Schema\Constraint; 8use Doctrine\DBAL\Schema\ForeignKeyConstraint; 9use Doctrine\DBAL\Schema\Identifier; 10use Doctrine\DBAL\Schema\Index; 11use Doctrine\DBAL\Schema\Table; 12use Doctrine\DBAL\Schema\TableDiff; 13use Doctrine\DBAL\TransactionIsolationLevel; 14use Doctrine\DBAL\Types; 15 16use function array_merge; 17use function array_unique; 18use function array_values; 19use function implode; 20use function is_numeric; 21use function sprintf; 22use function sqrt; 23use function str_replace; 24use function strlen; 25use function strpos; 26use function strtolower; 27use function trim; 28 29/** 30 * The SqlitePlatform class describes the specifics and dialects of the SQLite 31 * database platform. 32 * 33 * @todo Rename: SQLitePlatform 34 */ 35class SqlitePlatform extends AbstractPlatform 36{ 37 /** 38 * {@inheritDoc} 39 */ 40 public function getRegexpExpression() 41 { 42 return 'REGEXP'; 43 } 44 45 /** 46 * {@inheritDoc} 47 * 48 * @deprecated Use application-generated UUIDs instead 49 */ 50 public function getGuidExpression() 51 { 52 return "HEX(RANDOMBLOB(4)) || '-' || HEX(RANDOMBLOB(2)) || '-4' || " 53 . "SUBSTR(HEX(RANDOMBLOB(2)), 2) || '-' || " 54 . "SUBSTR('89AB', 1 + (ABS(RANDOM()) % 4), 1) || " 55 . "SUBSTR(HEX(RANDOMBLOB(2)), 2) || '-' || HEX(RANDOMBLOB(6))"; 56 } 57 58 /** 59 * @param string $type 60 * 61 * @return string 62 */ 63 public function getNowExpression($type = 'timestamp') 64 { 65 switch ($type) { 66 case 'time': 67 return 'time(\'now\')'; 68 69 case 'date': 70 return 'date(\'now\')'; 71 72 case 'timestamp': 73 default: 74 return 'datetime(\'now\')'; 75 } 76 } 77 78 /** 79 * {@inheritDoc} 80 */ 81 public function getTrimExpression($str, $mode = TrimMode::UNSPECIFIED, $char = false) 82 { 83 $trimChar = $char !== false ? ', ' . $char : ''; 84 85 switch ($mode) { 86 case TrimMode::LEADING: 87 $trimFn = 'LTRIM'; 88 break; 89 90 case TrimMode::TRAILING: 91 $trimFn = 'RTRIM'; 92 break; 93 94 default: 95 $trimFn = 'TRIM'; 96 } 97 98 return $trimFn . '(' . $str . $trimChar . ')'; 99 } 100 101 /** 102 * {@inheritDoc} 103 * 104 * SQLite only supports the 2 parameter variant of this function 105 */ 106 public function getSubstringExpression($string, $start, $length = null) 107 { 108 if ($length !== null) { 109 return 'SUBSTR(' . $string . ', ' . $start . ', ' . $length . ')'; 110 } 111 112 return 'SUBSTR(' . $string . ', ' . $start . ', LENGTH(' . $string . '))'; 113 } 114 115 /** 116 * {@inheritDoc} 117 */ 118 public function getLocateExpression($str, $substr, $startPos = false) 119 { 120 if ($startPos === false) { 121 return 'LOCATE(' . $str . ', ' . $substr . ')'; 122 } 123 124 return 'LOCATE(' . $str . ', ' . $substr . ', ' . $startPos . ')'; 125 } 126 127 /** 128 * {@inheritdoc} 129 */ 130 protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit) 131 { 132 switch ($unit) { 133 case DateIntervalUnit::SECOND: 134 case DateIntervalUnit::MINUTE: 135 case DateIntervalUnit::HOUR: 136 return 'DATETIME(' . $date . ",'" . $operator . $interval . ' ' . $unit . "')"; 137 } 138 139 switch ($unit) { 140 case DateIntervalUnit::WEEK: 141 $interval *= 7; 142 $unit = DateIntervalUnit::DAY; 143 break; 144 145 case DateIntervalUnit::QUARTER: 146 $interval *= 3; 147 $unit = DateIntervalUnit::MONTH; 148 break; 149 } 150 151 if (! is_numeric($interval)) { 152 $interval = "' || " . $interval . " || '"; 153 } 154 155 return 'DATE(' . $date . ",'" . $operator . $interval . ' ' . $unit . "')"; 156 } 157 158 /** 159 * {@inheritDoc} 160 */ 161 public function getDateDiffExpression($date1, $date2) 162 { 163 return sprintf("JULIANDAY(%s, 'start of day') - JULIANDAY(%s, 'start of day')", $date1, $date2); 164 } 165 166 /** 167 * {@inheritDoc} 168 */ 169 protected function _getTransactionIsolationLevelSQL($level) 170 { 171 switch ($level) { 172 case TransactionIsolationLevel::READ_UNCOMMITTED: 173 return '0'; 174 175 case TransactionIsolationLevel::READ_COMMITTED: 176 case TransactionIsolationLevel::REPEATABLE_READ: 177 case TransactionIsolationLevel::SERIALIZABLE: 178 return '1'; 179 180 default: 181 return parent::_getTransactionIsolationLevelSQL($level); 182 } 183 } 184 185 /** 186 * {@inheritDoc} 187 */ 188 public function getSetTransactionIsolationSQL($level) 189 { 190 return 'PRAGMA read_uncommitted = ' . $this->_getTransactionIsolationLevelSQL($level); 191 } 192 193 /** 194 * {@inheritDoc} 195 */ 196 public function prefersIdentityColumns() 197 { 198 return true; 199 } 200 201 /** 202 * {@inheritDoc} 203 */ 204 public function getBooleanTypeDeclarationSQL(array $column) 205 { 206 return 'BOOLEAN'; 207 } 208 209 /** 210 * {@inheritDoc} 211 */ 212 public function getIntegerTypeDeclarationSQL(array $column) 213 { 214 return 'INTEGER' . $this->_getCommonIntegerTypeDeclarationSQL($column); 215 } 216 217 /** 218 * {@inheritDoc} 219 */ 220 public function getBigIntTypeDeclarationSQL(array $column) 221 { 222 // SQLite autoincrement is implicit for INTEGER PKs, but not for BIGINT columns 223 if (! empty($column['autoincrement'])) { 224 return $this->getIntegerTypeDeclarationSQL($column); 225 } 226 227 return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 228 } 229 230 /** 231 * @param array<string, mixed> $column 232 * 233 * @return string 234 */ 235 public function getTinyIntTypeDeclarationSql(array $column) 236 { 237 // SQLite autoincrement is implicit for INTEGER PKs, but not for TINYINT columns 238 if (! empty($column['autoincrement'])) { 239 return $this->getIntegerTypeDeclarationSQL($column); 240 } 241 242 return 'TINYINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 243 } 244 245 /** 246 * {@inheritDoc} 247 */ 248 public function getSmallIntTypeDeclarationSQL(array $column) 249 { 250 // SQLite autoincrement is implicit for INTEGER PKs, but not for SMALLINT columns 251 if (! empty($column['autoincrement'])) { 252 return $this->getIntegerTypeDeclarationSQL($column); 253 } 254 255 return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 256 } 257 258 /** 259 * @param array<string, mixed> $column 260 * 261 * @return string 262 */ 263 public function getMediumIntTypeDeclarationSql(array $column) 264 { 265 // SQLite autoincrement is implicit for INTEGER PKs, but not for MEDIUMINT columns 266 if (! empty($column['autoincrement'])) { 267 return $this->getIntegerTypeDeclarationSQL($column); 268 } 269 270 return 'MEDIUMINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 271 } 272 273 /** 274 * {@inheritDoc} 275 */ 276 public function getDateTimeTypeDeclarationSQL(array $column) 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 protected function _getCommonIntegerTypeDeclarationSQL(array $column) 301 { 302 // sqlite autoincrement is only possible for the primary key 303 if (! empty($column['autoincrement'])) { 304 return ' PRIMARY KEY AUTOINCREMENT'; 305 } 306 307 return ! empty($column['unsigned']) ? ' UNSIGNED' : ''; 308 } 309 310 /** 311 * {@inheritDoc} 312 */ 313 public function getForeignKeyDeclarationSQL(ForeignKeyConstraint $foreignKey) 314 { 315 return parent::getForeignKeyDeclarationSQL(new ForeignKeyConstraint( 316 $foreignKey->getQuotedLocalColumns($this), 317 str_replace('.', '__', $foreignKey->getQuotedForeignTableName($this)), 318 $foreignKey->getQuotedForeignColumns($this), 319 $foreignKey->getName(), 320 $foreignKey->getOptions() 321 )); 322 } 323 324 /** 325 * {@inheritDoc} 326 */ 327 protected function _getCreateTableSQL($name, array $columns, array $options = []) 328 { 329 $name = str_replace('.', '__', $name); 330 $queryFields = $this->getColumnDeclarationListSQL($columns); 331 332 if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) { 333 foreach ($options['uniqueConstraints'] as $name => $definition) { 334 $queryFields .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition); 335 } 336 } 337 338 $queryFields .= $this->getNonAutoincrementPrimaryKeyDefinition($columns, $options); 339 340 if (isset($options['foreignKeys'])) { 341 foreach ($options['foreignKeys'] as $foreignKey) { 342 $queryFields .= ', ' . $this->getForeignKeyDeclarationSQL($foreignKey); 343 } 344 } 345 346 $tableComment = ''; 347 if (isset($options['comment'])) { 348 $comment = trim($options['comment'], " '"); 349 350 $tableComment = $this->getInlineTableCommentSQL($comment); 351 } 352 353 $query = ['CREATE TABLE ' . $name . ' ' . $tableComment . '(' . $queryFields . ')']; 354 355 if (isset($options['alter']) && $options['alter'] === true) { 356 return $query; 357 } 358 359 if (isset($options['indexes']) && ! empty($options['indexes'])) { 360 foreach ($options['indexes'] as $indexDef) { 361 $query[] = $this->getCreateIndexSQL($indexDef, $name); 362 } 363 } 364 365 if (isset($options['unique']) && ! empty($options['unique'])) { 366 foreach ($options['unique'] as $indexDef) { 367 $query[] = $this->getCreateIndexSQL($indexDef, $name); 368 } 369 } 370 371 return $query; 372 } 373 374 /** 375 * Generate a PRIMARY KEY definition if no autoincrement value is used 376 * 377 * @param mixed[][] $columns 378 * @param mixed[] $options 379 */ 380 private function getNonAutoincrementPrimaryKeyDefinition(array $columns, array $options): string 381 { 382 if (empty($options['primary'])) { 383 return ''; 384 } 385 386 $keyColumns = array_unique(array_values($options['primary'])); 387 388 foreach ($keyColumns as $keyColumn) { 389 if (! empty($columns[$keyColumn]['autoincrement'])) { 390 return ''; 391 } 392 } 393 394 return ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')'; 395 } 396 397 /** 398 * {@inheritDoc} 399 */ 400 protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) 401 { 402 return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(255)') 403 : ($length ? 'VARCHAR(' . $length . ')' : 'TEXT'); 404 } 405 406 /** 407 * {@inheritdoc} 408 */ 409 protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed) 410 { 411 return 'BLOB'; 412 } 413 414 /** 415 * {@inheritdoc} 416 */ 417 public function getBinaryMaxLength() 418 { 419 return 0; 420 } 421 422 /** 423 * {@inheritdoc} 424 */ 425 public function getBinaryDefaultLength() 426 { 427 return 0; 428 } 429 430 /** 431 * {@inheritDoc} 432 */ 433 public function getClobTypeDeclarationSQL(array $column) 434 { 435 return 'CLOB'; 436 } 437 438 /** 439 * {@inheritDoc} 440 */ 441 public function getListTableConstraintsSQL($table) 442 { 443 $table = str_replace('.', '__', $table); 444 445 return sprintf( 446 "SELECT sql FROM sqlite_master WHERE type='index' AND tbl_name = %s AND sql NOT NULL ORDER BY name", 447 $this->quoteStringLiteral($table) 448 ); 449 } 450 451 /** 452 * {@inheritDoc} 453 */ 454 public function getListTableColumnsSQL($table, $database = null) 455 { 456 $table = str_replace('.', '__', $table); 457 458 return sprintf('PRAGMA table_info(%s)', $this->quoteStringLiteral($table)); 459 } 460 461 /** 462 * {@inheritDoc} 463 */ 464 public function getListTableIndexesSQL($table, $database = null) 465 { 466 $table = str_replace('.', '__', $table); 467 468 return sprintf('PRAGMA index_list(%s)', $this->quoteStringLiteral($table)); 469 } 470 471 /** 472 * {@inheritDoc} 473 */ 474 public function getListTablesSQL() 475 { 476 return 'SELECT name FROM sqlite_master' 477 . " WHERE type = 'table'" 478 . " AND name != 'sqlite_sequence'" 479 . " AND name != 'geometry_columns'" 480 . " AND name != 'spatial_ref_sys'" 481 . ' UNION ALL SELECT name FROM sqlite_temp_master' 482 . " WHERE type = 'table' ORDER BY name"; 483 } 484 485 /** 486 * {@inheritDoc} 487 */ 488 public function getListViewsSQL($database) 489 { 490 return "SELECT name, sql FROM sqlite_master WHERE type='view' AND sql NOT NULL"; 491 } 492 493 /** 494 * {@inheritDoc} 495 */ 496 public function getCreateViewSQL($name, $sql) 497 { 498 return 'CREATE VIEW ' . $name . ' AS ' . $sql; 499 } 500 501 /** 502 * {@inheritDoc} 503 */ 504 public function getDropViewSQL($name) 505 { 506 return 'DROP VIEW ' . $name; 507 } 508 509 /** 510 * {@inheritDoc} 511 */ 512 public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey) 513 { 514 $query = parent::getAdvancedForeignKeyOptionsSQL($foreignKey); 515 516 if (! $foreignKey->hasOption('deferrable') || $foreignKey->getOption('deferrable') === false) { 517 $query .= ' NOT'; 518 } 519 520 $query .= ' DEFERRABLE'; 521 $query .= ' INITIALLY'; 522 523 if ($foreignKey->hasOption('deferred') && $foreignKey->getOption('deferred') !== false) { 524 $query .= ' DEFERRED'; 525 } else { 526 $query .= ' IMMEDIATE'; 527 } 528 529 return $query; 530 } 531 532 /** 533 * {@inheritDoc} 534 */ 535 public function supportsIdentityColumns() 536 { 537 return true; 538 } 539 540 /** 541 * {@inheritDoc} 542 */ 543 public function supportsColumnCollation() 544 { 545 return true; 546 } 547 548 /** 549 * {@inheritDoc} 550 */ 551 public function supportsInlineColumnComments() 552 { 553 return true; 554 } 555 556 /** 557 * {@inheritDoc} 558 */ 559 public function getName() 560 { 561 return 'sqlite'; 562 } 563 564 /** 565 * {@inheritDoc} 566 */ 567 public function getTruncateTableSQL($tableName, $cascade = false) 568 { 569 $tableIdentifier = new Identifier($tableName); 570 $tableName = str_replace('.', '__', $tableIdentifier->getQuotedName($this)); 571 572 return 'DELETE FROM ' . $tableName; 573 } 574 575 /** 576 * User-defined function for Sqlite that is used with PDO::sqliteCreateFunction(). 577 * 578 * @param int|float $value 579 * 580 * @return float 581 */ 582 public static function udfSqrt($value) 583 { 584 return sqrt($value); 585 } 586 587 /** 588 * User-defined function for Sqlite that implements MOD(a, b). 589 * 590 * @param int $a 591 * @param int $b 592 * 593 * @return int 594 */ 595 public static function udfMod($a, $b) 596 { 597 return $a % $b; 598 } 599 600 /** 601 * @param string $str 602 * @param string $substr 603 * @param int $offset 604 * 605 * @return int 606 */ 607 public static function udfLocate($str, $substr, $offset = 0) 608 { 609 // SQL's LOCATE function works on 1-based positions, while PHP's strpos works on 0-based positions. 610 // So we have to make them compatible if an offset is given. 611 if ($offset > 0) { 612 $offset -= 1; 613 } 614 615 $pos = strpos($str, $substr, $offset); 616 617 if ($pos !== false) { 618 return $pos + 1; 619 } 620 621 return 0; 622 } 623 624 /** 625 * {@inheritDoc} 626 */ 627 public function getForUpdateSQL() 628 { 629 return ''; 630 } 631 632 /** 633 * {@inheritDoc} 634 */ 635 public function getInlineColumnCommentSQL($comment) 636 { 637 return '--' . str_replace("\n", "\n--", $comment) . "\n"; 638 } 639 640 private function getInlineTableCommentSQL(string $comment): string 641 { 642 return $this->getInlineColumnCommentSQL($comment); 643 } 644 645 /** 646 * {@inheritDoc} 647 */ 648 protected function initializeDoctrineTypeMappings() 649 { 650 $this->doctrineTypeMapping = [ 651 'boolean' => 'boolean', 652 'tinyint' => 'boolean', 653 'smallint' => 'smallint', 654 'mediumint' => 'integer', 655 'int' => 'integer', 656 'integer' => 'integer', 657 'serial' => 'integer', 658 'bigint' => 'bigint', 659 'bigserial' => 'bigint', 660 'clob' => 'text', 661 'tinytext' => 'text', 662 'mediumtext' => 'text', 663 'longtext' => 'text', 664 'text' => 'text', 665 'varchar' => 'string', 666 'longvarchar' => 'string', 667 'varchar2' => 'string', 668 'nvarchar' => 'string', 669 'image' => 'string', 670 'ntext' => 'string', 671 'char' => 'string', 672 'date' => 'date', 673 'datetime' => 'datetime', 674 'timestamp' => 'datetime', 675 'time' => 'time', 676 'float' => 'float', 677 'double' => 'float', 678 'double precision' => 'float', 679 'real' => 'float', 680 'decimal' => 'decimal', 681 'numeric' => 'decimal', 682 'blob' => 'blob', 683 ]; 684 } 685 686 /** 687 * {@inheritDoc} 688 */ 689 protected function getReservedKeywordsClass() 690 { 691 return Keywords\SQLiteKeywords::class; 692 } 693 694 /** 695 * {@inheritDoc} 696 */ 697 protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff) 698 { 699 if (! $diff->fromTable instanceof Table) { 700 throw new Exception( 701 'Sqlite platform requires for alter table the table diff with reference to original table schema' 702 ); 703 } 704 705 $sql = []; 706 foreach ($diff->fromTable->getIndexes() as $index) { 707 if ($index->isPrimary()) { 708 continue; 709 } 710 711 $sql[] = $this->getDropIndexSQL($index, $diff->name); 712 } 713 714 return $sql; 715 } 716 717 /** 718 * {@inheritDoc} 719 */ 720 protected function getPostAlterTableIndexForeignKeySQL(TableDiff $diff) 721 { 722 $fromTable = $diff->fromTable; 723 724 if (! $fromTable instanceof Table) { 725 throw new Exception( 726 'Sqlite platform requires for alter table the table diff with reference to original table schema' 727 ); 728 } 729 730 $sql = []; 731 $tableName = $diff->getNewName(); 732 733 if ($tableName === false) { 734 $tableName = $diff->getName($this); 735 } 736 737 foreach ($this->getIndexesInAlteredTable($diff, $fromTable) as $index) { 738 if ($index->isPrimary()) { 739 continue; 740 } 741 742 $sql[] = $this->getCreateIndexSQL($index, $tableName->getQuotedName($this)); 743 } 744 745 return $sql; 746 } 747 748 /** 749 * {@inheritDoc} 750 */ 751 protected function doModifyLimitQuery($query, $limit, $offset) 752 { 753 if ($limit === null && $offset > 0) { 754 return $query . ' LIMIT -1 OFFSET ' . $offset; 755 } 756 757 return parent::doModifyLimitQuery($query, $limit, $offset); 758 } 759 760 /** 761 * {@inheritDoc} 762 */ 763 public function getBlobTypeDeclarationSQL(array $column) 764 { 765 return 'BLOB'; 766 } 767 768 /** 769 * {@inheritDoc} 770 */ 771 public function getTemporaryTableName($tableName) 772 { 773 $tableName = str_replace('.', '__', $tableName); 774 775 return $tableName; 776 } 777 778 /** 779 * {@inheritDoc} 780 * 781 * Sqlite Platform emulates schema by underscoring each dot and generating tables 782 * into the default database. 783 * 784 * This hack is implemented to be able to use SQLite as testdriver when 785 * using schema supporting databases. 786 */ 787 public function canEmulateSchemas() 788 { 789 return true; 790 } 791 792 /** 793 * {@inheritDoc} 794 */ 795 public function supportsForeignKeyConstraints() 796 { 797 return false; 798 } 799 800 /** 801 * {@inheritDoc} 802 */ 803 public function getCreatePrimaryKeySQL(Index $index, $table) 804 { 805 throw new Exception('Sqlite platform does not support alter primary key.'); 806 } 807 808 /** 809 * {@inheritdoc} 810 */ 811 public function getCreateForeignKeySQL(ForeignKeyConstraint $foreignKey, $table) 812 { 813 throw new Exception('Sqlite platform does not support alter foreign key.'); 814 } 815 816 /** 817 * {@inheritdoc} 818 */ 819 public function getDropForeignKeySQL($foreignKey, $table) 820 { 821 throw new Exception('Sqlite platform does not support alter foreign key.'); 822 } 823 824 /** 825 * {@inheritDoc} 826 */ 827 public function getCreateConstraintSQL(Constraint $constraint, $table) 828 { 829 throw new Exception('Sqlite platform does not support alter constraint.'); 830 } 831 832 /** 833 * {@inheritDoc} 834 * 835 * @param int|null $createFlags 836 */ 837 public function getCreateTableSQL(Table $table, $createFlags = null) 838 { 839 $createFlags = $createFlags ?? self::CREATE_INDEXES | self::CREATE_FOREIGNKEYS; 840 841 return parent::getCreateTableSQL($table, $createFlags); 842 } 843 844 /** 845 * @param string $table 846 * @param string|null $database 847 * 848 * @return string 849 */ 850 public function getListTableForeignKeysSQL($table, $database = null) 851 { 852 $table = str_replace('.', '__', $table); 853 854 return sprintf('PRAGMA foreign_key_list(%s)', $this->quoteStringLiteral($table)); 855 } 856 857 /** 858 * {@inheritDoc} 859 */ 860 public function getAlterTableSQL(TableDiff $diff) 861 { 862 $sql = $this->getSimpleAlterTableSQL($diff); 863 if ($sql !== false) { 864 return $sql; 865 } 866 867 $fromTable = $diff->fromTable; 868 if (! $fromTable instanceof Table) { 869 throw new Exception( 870 'Sqlite platform requires for alter table the table diff with reference to original table schema' 871 ); 872 } 873 874 $table = clone $fromTable; 875 876 $columns = []; 877 $oldColumnNames = []; 878 $newColumnNames = []; 879 $columnSql = []; 880 881 foreach ($table->getColumns() as $columnName => $column) { 882 $columnName = strtolower($columnName); 883 $columns[$columnName] = $column; 884 $oldColumnNames[$columnName] = $newColumnNames[$columnName] = $column->getQuotedName($this); 885 } 886 887 foreach ($diff->removedColumns as $columnName => $column) { 888 if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { 889 continue; 890 } 891 892 $columnName = strtolower($columnName); 893 if (! isset($columns[$columnName])) { 894 continue; 895 } 896 897 unset( 898 $columns[$columnName], 899 $oldColumnNames[$columnName], 900 $newColumnNames[$columnName] 901 ); 902 } 903 904 foreach ($diff->renamedColumns as $oldColumnName => $column) { 905 if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { 906 continue; 907 } 908 909 $oldColumnName = strtolower($oldColumnName); 910 if (isset($columns[$oldColumnName])) { 911 unset($columns[$oldColumnName]); 912 } 913 914 $columns[strtolower($column->getName())] = $column; 915 916 if (! isset($newColumnNames[$oldColumnName])) { 917 continue; 918 } 919 920 $newColumnNames[$oldColumnName] = $column->getQuotedName($this); 921 } 922 923 foreach ($diff->changedColumns as $oldColumnName => $columnDiff) { 924 if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { 925 continue; 926 } 927 928 if (isset($columns[$oldColumnName])) { 929 unset($columns[$oldColumnName]); 930 } 931 932 $columns[strtolower($columnDiff->column->getName())] = $columnDiff->column; 933 934 if (! isset($newColumnNames[$oldColumnName])) { 935 continue; 936 } 937 938 $newColumnNames[$oldColumnName] = $columnDiff->column->getQuotedName($this); 939 } 940 941 foreach ($diff->addedColumns as $columnName => $column) { 942 if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { 943 continue; 944 } 945 946 $columns[strtolower($columnName)] = $column; 947 } 948 949 $sql = []; 950 $tableSql = []; 951 if (! $this->onSchemaAlterTable($diff, $tableSql)) { 952 $dataTable = new Table('__temp__' . $table->getName()); 953 954 $newTable = new Table( 955 $table->getQuotedName($this), 956 $columns, 957 $this->getPrimaryIndexInAlteredTable($diff, $fromTable), 958 $this->getForeignKeysInAlteredTable($diff, $fromTable), 959 0, 960 $table->getOptions() 961 ); 962 $newTable->addOption('alter', true); 963 964 $sql = $this->getPreAlterTableIndexForeignKeySQL($diff); 965 966 $sql[] = sprintf( 967 'CREATE TEMPORARY TABLE %s AS SELECT %s FROM %s', 968 $dataTable->getQuotedName($this), 969 implode(', ', $oldColumnNames), 970 $table->getQuotedName($this) 971 ); 972 $sql[] = $this->getDropTableSQL($fromTable); 973 974 $sql = array_merge($sql, $this->getCreateTableSQL($newTable)); 975 $sql[] = sprintf( 976 'INSERT INTO %s (%s) SELECT %s FROM %s', 977 $newTable->getQuotedName($this), 978 implode(', ', $newColumnNames), 979 implode(', ', $oldColumnNames), 980 $dataTable->getQuotedName($this) 981 ); 982 $sql[] = $this->getDropTableSQL($dataTable); 983 984 $newName = $diff->getNewName(); 985 986 if ($newName !== false) { 987 $sql[] = sprintf( 988 'ALTER TABLE %s RENAME TO %s', 989 $newTable->getQuotedName($this), 990 $newName->getQuotedName($this) 991 ); 992 } 993 994 $sql = array_merge($sql, $this->getPostAlterTableIndexForeignKeySQL($diff)); 995 } 996 997 return array_merge($sql, $tableSql, $columnSql); 998 } 999 1000 /** 1001 * @return string[]|false 1002 */ 1003 private function getSimpleAlterTableSQL(TableDiff $diff) 1004 { 1005 // Suppress changes on integer type autoincrement columns. 1006 foreach ($diff->changedColumns as $oldColumnName => $columnDiff) { 1007 if ( 1008 ! $columnDiff->fromColumn instanceof Column || 1009 ! $columnDiff->column instanceof Column || 1010 ! $columnDiff->column->getAutoincrement() || 1011 ! $columnDiff->column->getType() instanceof Types\IntegerType 1012 ) { 1013 continue; 1014 } 1015 1016 if (! $columnDiff->hasChanged('type') && $columnDiff->hasChanged('unsigned')) { 1017 unset($diff->changedColumns[$oldColumnName]); 1018 1019 continue; 1020 } 1021 1022 $fromColumnType = $columnDiff->fromColumn->getType(); 1023 1024 if (! ($fromColumnType instanceof Types\SmallIntType) && ! ($fromColumnType instanceof Types\BigIntType)) { 1025 continue; 1026 } 1027 1028 unset($diff->changedColumns[$oldColumnName]); 1029 } 1030 1031 if ( 1032 ! empty($diff->renamedColumns) 1033 || ! empty($diff->addedForeignKeys) 1034 || ! empty($diff->addedIndexes) 1035 || ! empty($diff->changedColumns) 1036 || ! empty($diff->changedForeignKeys) 1037 || ! empty($diff->changedIndexes) 1038 || ! empty($diff->removedColumns) 1039 || ! empty($diff->removedForeignKeys) 1040 || ! empty($diff->removedIndexes) 1041 || ! empty($diff->renamedIndexes) 1042 ) { 1043 return false; 1044 } 1045 1046 $table = new Table($diff->name); 1047 1048 $sql = []; 1049 $tableSql = []; 1050 $columnSql = []; 1051 1052 foreach ($diff->addedColumns as $column) { 1053 if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { 1054 continue; 1055 } 1056 1057 $definition = array_merge([ 1058 'unique' => null, 1059 'autoincrement' => null, 1060 'default' => null, 1061 ], $column->toArray()); 1062 1063 $type = $definition['type']; 1064 1065 switch (true) { 1066 case isset($definition['columnDefinition']) || $definition['autoincrement'] || $definition['unique']: 1067 case $type instanceof Types\DateTimeType && $definition['default'] === $this->getCurrentTimestampSQL(): 1068 case $type instanceof Types\DateType && $definition['default'] === $this->getCurrentDateSQL(): 1069 case $type instanceof Types\TimeType && $definition['default'] === $this->getCurrentTimeSQL(): 1070 return false; 1071 } 1072 1073 $definition['name'] = $column->getQuotedName($this); 1074 if ($type instanceof Types\StringType && $definition['length'] === null) { 1075 $definition['length'] = 255; 1076 } 1077 1078 $sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ADD COLUMN ' 1079 . $this->getColumnDeclarationSQL($definition['name'], $definition); 1080 } 1081 1082 if (! $this->onSchemaAlterTable($diff, $tableSql)) { 1083 if ($diff->newName !== false) { 1084 $newTable = new Identifier($diff->newName); 1085 1086 $sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' RENAME TO ' 1087 . $newTable->getQuotedName($this); 1088 } 1089 } 1090 1091 return array_merge($sql, $tableSql, $columnSql); 1092 } 1093 1094 /** 1095 * @return string[] 1096 */ 1097 private function getColumnNamesInAlteredTable(TableDiff $diff, Table $fromTable) 1098 { 1099 $columns = []; 1100 1101 foreach ($fromTable->getColumns() as $columnName => $column) { 1102 $columns[strtolower($columnName)] = $column->getName(); 1103 } 1104 1105 foreach ($diff->removedColumns as $columnName => $column) { 1106 $columnName = strtolower($columnName); 1107 if (! isset($columns[$columnName])) { 1108 continue; 1109 } 1110 1111 unset($columns[$columnName]); 1112 } 1113 1114 foreach ($diff->renamedColumns as $oldColumnName => $column) { 1115 $columnName = $column->getName(); 1116 $columns[strtolower($oldColumnName)] = $columnName; 1117 $columns[strtolower($columnName)] = $columnName; 1118 } 1119 1120 foreach ($diff->changedColumns as $oldColumnName => $columnDiff) { 1121 $columnName = $columnDiff->column->getName(); 1122 $columns[strtolower($oldColumnName)] = $columnName; 1123 $columns[strtolower($columnName)] = $columnName; 1124 } 1125 1126 foreach ($diff->addedColumns as $column) { 1127 $columnName = $column->getName(); 1128 $columns[strtolower($columnName)] = $columnName; 1129 } 1130 1131 return $columns; 1132 } 1133 1134 /** 1135 * @return Index[] 1136 */ 1137 private function getIndexesInAlteredTable(TableDiff $diff, Table $fromTable) 1138 { 1139 $indexes = $fromTable->getIndexes(); 1140 $columnNames = $this->getColumnNamesInAlteredTable($diff, $fromTable); 1141 1142 foreach ($indexes as $key => $index) { 1143 foreach ($diff->renamedIndexes as $oldIndexName => $renamedIndex) { 1144 if (strtolower($key) !== strtolower($oldIndexName)) { 1145 continue; 1146 } 1147 1148 unset($indexes[$key]); 1149 } 1150 1151 $changed = false; 1152 $indexColumns = []; 1153 foreach ($index->getColumns() as $columnName) { 1154 $normalizedColumnName = strtolower($columnName); 1155 if (! isset($columnNames[$normalizedColumnName])) { 1156 unset($indexes[$key]); 1157 continue 2; 1158 } 1159 1160 $indexColumns[] = $columnNames[$normalizedColumnName]; 1161 if ($columnName === $columnNames[$normalizedColumnName]) { 1162 continue; 1163 } 1164 1165 $changed = true; 1166 } 1167 1168 if (! $changed) { 1169 continue; 1170 } 1171 1172 $indexes[$key] = new Index( 1173 $index->getName(), 1174 $indexColumns, 1175 $index->isUnique(), 1176 $index->isPrimary(), 1177 $index->getFlags() 1178 ); 1179 } 1180 1181 foreach ($diff->removedIndexes as $index) { 1182 $indexName = strtolower($index->getName()); 1183 if (! strlen($indexName) || ! isset($indexes[$indexName])) { 1184 continue; 1185 } 1186 1187 unset($indexes[$indexName]); 1188 } 1189 1190 foreach (array_merge($diff->changedIndexes, $diff->addedIndexes, $diff->renamedIndexes) as $index) { 1191 $indexName = strtolower($index->getName()); 1192 if (strlen($indexName)) { 1193 $indexes[$indexName] = $index; 1194 } else { 1195 $indexes[] = $index; 1196 } 1197 } 1198 1199 return $indexes; 1200 } 1201 1202 /** 1203 * @return ForeignKeyConstraint[] 1204 */ 1205 private function getForeignKeysInAlteredTable(TableDiff $diff, Table $fromTable) 1206 { 1207 $foreignKeys = $fromTable->getForeignKeys(); 1208 $columnNames = $this->getColumnNamesInAlteredTable($diff, $fromTable); 1209 1210 foreach ($foreignKeys as $key => $constraint) { 1211 $changed = false; 1212 $localColumns = []; 1213 foreach ($constraint->getLocalColumns() as $columnName) { 1214 $normalizedColumnName = strtolower($columnName); 1215 if (! isset($columnNames[$normalizedColumnName])) { 1216 unset($foreignKeys[$key]); 1217 continue 2; 1218 } 1219 1220 $localColumns[] = $columnNames[$normalizedColumnName]; 1221 if ($columnName === $columnNames[$normalizedColumnName]) { 1222 continue; 1223 } 1224 1225 $changed = true; 1226 } 1227 1228 if (! $changed) { 1229 continue; 1230 } 1231 1232 $foreignKeys[$key] = new ForeignKeyConstraint( 1233 $localColumns, 1234 $constraint->getForeignTableName(), 1235 $constraint->getForeignColumns(), 1236 $constraint->getName(), 1237 $constraint->getOptions() 1238 ); 1239 } 1240 1241 foreach ($diff->removedForeignKeys as $constraint) { 1242 if (! $constraint instanceof ForeignKeyConstraint) { 1243 $constraint = new Identifier($constraint); 1244 } 1245 1246 $constraintName = strtolower($constraint->getName()); 1247 if (! strlen($constraintName) || ! isset($foreignKeys[$constraintName])) { 1248 continue; 1249 } 1250 1251 unset($foreignKeys[$constraintName]); 1252 } 1253 1254 foreach (array_merge($diff->changedForeignKeys, $diff->addedForeignKeys) as $constraint) { 1255 $constraintName = strtolower($constraint->getName()); 1256 if (strlen($constraintName)) { 1257 $foreignKeys[$constraintName] = $constraint; 1258 } else { 1259 $foreignKeys[] = $constraint; 1260 } 1261 } 1262 1263 return $foreignKeys; 1264 } 1265 1266 /** 1267 * @return Index[] 1268 */ 1269 private function getPrimaryIndexInAlteredTable(TableDiff $diff, Table $fromTable) 1270 { 1271 $primaryIndex = []; 1272 1273 foreach ($this->getIndexesInAlteredTable($diff, $fromTable) as $index) { 1274 if (! $index->isPrimary()) { 1275 continue; 1276 } 1277 1278 $primaryIndex = [$index->getName() => $index]; 1279 } 1280 1281 return $primaryIndex; 1282 } 1283} 1284