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