1<?php
2
3namespace Doctrine\DBAL\Platforms;
4
5use Doctrine\DBAL\DBALException;
6use Doctrine\DBAL\Schema\ForeignKeyConstraint;
7use Doctrine\DBAL\Schema\Identifier;
8use Doctrine\DBAL\Schema\Index;
9use Doctrine\DBAL\Schema\Sequence;
10use Doctrine\DBAL\Schema\Table;
11use Doctrine\DBAL\Schema\TableDiff;
12use Doctrine\DBAL\TransactionIsolationLevel;
13use Doctrine\DBAL\Types\BinaryType;
14use InvalidArgumentException;
15use function array_merge;
16use function count;
17use function explode;
18use function func_get_arg;
19use function func_num_args;
20use function implode;
21use function preg_match;
22use function sprintf;
23use function strlen;
24use function strpos;
25use function strtoupper;
26use function substr;
27
28/**
29 * OraclePlatform.
30 */
31class OraclePlatform extends AbstractPlatform
32{
33    /**
34     * Assertion for Oracle identifiers.
35     *
36     * @link http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm
37     *
38     * @param string $identifier
39     *
40     * @throws DBALException
41     */
42    public static function assertValidIdentifier($identifier)
43    {
44        if (! preg_match('(^(([a-zA-Z]{1}[a-zA-Z0-9_$#]{0,})|("[^"]+"))$)', $identifier)) {
45            throw new DBALException('Invalid Oracle identifier');
46        }
47    }
48
49    /**
50     * {@inheritDoc}
51     */
52    public function getSubstringExpression($value, $position, $length = null)
53    {
54        if ($length !== null) {
55            return sprintf('SUBSTR(%s, %d, %d)', $value, $position, $length);
56        }
57
58        return sprintf('SUBSTR(%s, %d)', $value, $position);
59    }
60
61    /**
62     * {@inheritDoc}
63     */
64    public function getNowExpression($type = 'timestamp')
65    {
66        switch ($type) {
67            case 'date':
68            case 'time':
69            case 'timestamp':
70            default:
71                return 'TO_CHAR(CURRENT_TIMESTAMP, \'YYYY-MM-DD HH24:MI:SS\')';
72        }
73    }
74
75    /**
76     * {@inheritDoc}
77     */
78    public function getLocateExpression($str, $substr, $startPos = false)
79    {
80        if ($startPos === false) {
81            return 'INSTR(' . $str . ', ' . $substr . ')';
82        }
83
84        return 'INSTR(' . $str . ', ' . $substr . ', ' . $startPos . ')';
85    }
86
87    /**
88     * {@inheritDoc}
89     *
90     * @deprecated Use application-generated UUIDs instead
91     */
92    public function getGuidExpression()
93    {
94        return 'SYS_GUID()';
95    }
96
97    /**
98     * {@inheritdoc}
99     */
100    protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
101    {
102        switch ($unit) {
103            case DateIntervalUnit::MONTH:
104            case DateIntervalUnit::QUARTER:
105            case DateIntervalUnit::YEAR:
106                switch ($unit) {
107                    case DateIntervalUnit::QUARTER:
108                        $interval *= 3;
109                        break;
110
111                    case DateIntervalUnit::YEAR:
112                        $interval *= 12;
113                        break;
114                }
115
116                return 'ADD_MONTHS(' . $date . ', ' . $operator . $interval . ')';
117
118            default:
119                $calculationClause = '';
120
121                switch ($unit) {
122                    case DateIntervalUnit::SECOND:
123                        $calculationClause = '/24/60/60';
124                        break;
125
126                    case DateIntervalUnit::MINUTE:
127                        $calculationClause = '/24/60';
128                        break;
129
130                    case DateIntervalUnit::HOUR:
131                        $calculationClause = '/24';
132                        break;
133
134                    case DateIntervalUnit::WEEK:
135                        $calculationClause = '*7';
136                        break;
137                }
138
139                return '(' . $date . $operator . $interval . $calculationClause . ')';
140        }
141    }
142
143    /**
144     * {@inheritDoc}
145     */
146    public function getDateDiffExpression($date1, $date2)
147    {
148        return sprintf('TRUNC(%s) - TRUNC(%s)', $date1, $date2);
149    }
150
151    /**
152     * {@inheritDoc}
153     */
154    public function getBitAndComparisonExpression($value1, $value2)
155    {
156        return 'BITAND(' . $value1 . ', ' . $value2 . ')';
157    }
158
159    /**
160     * {@inheritDoc}
161     */
162    public function getBitOrComparisonExpression($value1, $value2)
163    {
164        return '(' . $value1 . '-' .
165                $this->getBitAndComparisonExpression($value1, $value2)
166                . '+' . $value2 . ')';
167    }
168
169    /**
170     * {@inheritDoc}
171     *
172     * Need to specifiy minvalue, since start with is hidden in the system and MINVALUE <= START WITH.
173     * Therefore we can use MINVALUE to be able to get a hint what START WITH was for later introspection
174     * in {@see listSequences()}
175     */
176    public function getCreateSequenceSQL(Sequence $sequence)
177    {
178        return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) .
179               ' START WITH ' . $sequence->getInitialValue() .
180               ' MINVALUE ' . $sequence->getInitialValue() .
181               ' INCREMENT BY ' . $sequence->getAllocationSize() .
182               $this->getSequenceCacheSQL($sequence);
183    }
184
185    /**
186     * {@inheritDoc}
187     */
188    public function getAlterSequenceSQL(Sequence $sequence)
189    {
190        return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) .
191               ' INCREMENT BY ' . $sequence->getAllocationSize()
192               . $this->getSequenceCacheSQL($sequence);
193    }
194
195    /**
196     * Cache definition for sequences
197     *
198     * @return string
199     */
200    private function getSequenceCacheSQL(Sequence $sequence)
201    {
202        if ($sequence->getCache() === 0) {
203            return ' NOCACHE';
204        } elseif ($sequence->getCache() === 1) {
205            return ' NOCACHE';
206        } elseif ($sequence->getCache() > 1) {
207            return ' CACHE ' . $sequence->getCache();
208        }
209
210        return '';
211    }
212
213    /**
214     * {@inheritDoc}
215     */
216    public function getSequenceNextValSQL($sequenceName)
217    {
218        return 'SELECT ' . $sequenceName . '.nextval FROM DUAL';
219    }
220
221    /**
222     * {@inheritDoc}
223     */
224    public function getSetTransactionIsolationSQL($level)
225    {
226        return 'SET TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
227    }
228
229    /**
230     * {@inheritDoc}
231     */
232    protected function _getTransactionIsolationLevelSQL($level)
233    {
234        switch ($level) {
235            case TransactionIsolationLevel::READ_UNCOMMITTED:
236                return 'READ UNCOMMITTED';
237            case TransactionIsolationLevel::READ_COMMITTED:
238                return 'READ COMMITTED';
239            case TransactionIsolationLevel::REPEATABLE_READ:
240            case TransactionIsolationLevel::SERIALIZABLE:
241                return 'SERIALIZABLE';
242            default:
243                return parent::_getTransactionIsolationLevelSQL($level);
244        }
245    }
246
247    /**
248     * {@inheritDoc}
249     */
250    public function getBooleanTypeDeclarationSQL(array $field)
251    {
252        return 'NUMBER(1)';
253    }
254
255    /**
256     * {@inheritDoc}
257     */
258    public function getIntegerTypeDeclarationSQL(array $field)
259    {
260        return 'NUMBER(10)';
261    }
262
263    /**
264     * {@inheritDoc}
265     */
266    public function getBigIntTypeDeclarationSQL(array $field)
267    {
268        return 'NUMBER(20)';
269    }
270
271    /**
272     * {@inheritDoc}
273     */
274    public function getSmallIntTypeDeclarationSQL(array $field)
275    {
276        return 'NUMBER(5)';
277    }
278
279    /**
280     * {@inheritDoc}
281     */
282    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
283    {
284        return 'TIMESTAMP(0)';
285    }
286
287    /**
288     * {@inheritDoc}
289     */
290    public function getDateTimeTzTypeDeclarationSQL(array $fieldDeclaration)
291    {
292        return 'TIMESTAMP(0) WITH TIME ZONE';
293    }
294
295    /**
296     * {@inheritDoc}
297     */
298    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
299    {
300        return 'DATE';
301    }
302
303    /**
304     * {@inheritDoc}
305     */
306    public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
307    {
308        return 'DATE';
309    }
310
311    /**
312     * {@inheritDoc}
313     */
314    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
315    {
316        return '';
317    }
318
319    /**
320     * {@inheritDoc}
321     */
322    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
323    {
324        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(2000)')
325                : ($length ? 'VARCHAR2(' . $length . ')' : 'VARCHAR2(4000)');
326    }
327
328    /**
329     * {@inheritdoc}
330     */
331    protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
332    {
333        return 'RAW(' . ($length ?: $this->getBinaryMaxLength()) . ')';
334    }
335
336    /**
337     * {@inheritdoc}
338     */
339    public function getBinaryMaxLength()
340    {
341        return 2000;
342    }
343
344    /**
345     * {@inheritDoc}
346     */
347    public function getClobTypeDeclarationSQL(array $field)
348    {
349        return 'CLOB';
350    }
351
352    /**
353     * {@inheritDoc}
354     */
355    public function getListDatabasesSQL()
356    {
357        return 'SELECT username FROM all_users';
358    }
359
360    /**
361     * {@inheritDoc}
362     */
363    public function getListSequencesSQL($database)
364    {
365        $database = $this->normalizeIdentifier($database);
366        $database = $this->quoteStringLiteral($database->getName());
367
368        return 'SELECT sequence_name, min_value, increment_by FROM sys.all_sequences ' .
369               'WHERE SEQUENCE_OWNER = ' . $database;
370    }
371
372    /**
373     * {@inheritDoc}
374     */
375    protected function _getCreateTableSQL($table, array $columns, array $options = [])
376    {
377        $indexes            = $options['indexes'] ?? [];
378        $options['indexes'] = [];
379        $sql                = parent::_getCreateTableSQL($table, $columns, $options);
380
381        foreach ($columns as $name => $column) {
382            if (isset($column['sequence'])) {
383                $sql[] = $this->getCreateSequenceSQL($column['sequence']);
384            }
385
386            if (! isset($column['autoincrement']) || ! $column['autoincrement'] &&
387               (! isset($column['autoinc']) || ! $column['autoinc'])) {
388                continue;
389            }
390
391            $sql = array_merge($sql, $this->getCreateAutoincrementSql($name, $table));
392        }
393
394        if (isset($indexes) && ! empty($indexes)) {
395            foreach ($indexes as $index) {
396                $sql[] = $this->getCreateIndexSQL($index, $table);
397            }
398        }
399
400        return $sql;
401    }
402
403    /**
404     * {@inheritDoc}
405     *
406     * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaOracleReader.html
407     */
408    public function getListTableIndexesSQL($table, $currentDatabase = null)
409    {
410        $table = $this->normalizeIdentifier($table);
411        $table = $this->quoteStringLiteral($table->getName());
412
413        return "SELECT uind_col.index_name AS name,
414                       (
415                           SELECT uind.index_type
416                           FROM   user_indexes uind
417                           WHERE  uind.index_name = uind_col.index_name
418                       ) AS type,
419                       decode(
420                           (
421                               SELECT uind.uniqueness
422                               FROM   user_indexes uind
423                               WHERE  uind.index_name = uind_col.index_name
424                           ),
425                           'NONUNIQUE',
426                           0,
427                           'UNIQUE',
428                           1
429                       ) AS is_unique,
430                       uind_col.column_name AS column_name,
431                       uind_col.column_position AS column_pos,
432                       (
433                           SELECT ucon.constraint_type
434                           FROM   user_constraints ucon
435                           WHERE  ucon.index_name = uind_col.index_name
436                       ) AS is_primary
437             FROM      user_ind_columns uind_col
438             WHERE     uind_col.table_name = " . $table . '
439             ORDER BY  uind_col.column_position ASC';
440    }
441
442    /**
443     * {@inheritDoc}
444     */
445    public function getListTablesSQL()
446    {
447        return 'SELECT * FROM sys.user_tables';
448    }
449
450    /**
451     * {@inheritDoc}
452     */
453    public function getListViewsSQL($database)
454    {
455        return 'SELECT view_name, text FROM sys.user_views';
456    }
457
458    /**
459     * {@inheritDoc}
460     */
461    public function getCreateViewSQL($name, $sql)
462    {
463        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
464    }
465
466    /**
467     * {@inheritDoc}
468     */
469    public function getDropViewSQL($name)
470    {
471        return 'DROP VIEW ' . $name;
472    }
473
474    /**
475     * @param string $name
476     * @param string $table
477     * @param int    $start
478     *
479     * @return string[]
480     */
481    public function getCreateAutoincrementSql($name, $table, $start = 1)
482    {
483        $tableIdentifier   = $this->normalizeIdentifier($table);
484        $quotedTableName   = $tableIdentifier->getQuotedName($this);
485        $unquotedTableName = $tableIdentifier->getName();
486
487        $nameIdentifier = $this->normalizeIdentifier($name);
488        $quotedName     = $nameIdentifier->getQuotedName($this);
489        $unquotedName   = $nameIdentifier->getName();
490
491        $sql = [];
492
493        $autoincrementIdentifierName = $this->getAutoincrementIdentifierName($tableIdentifier);
494
495        $idx = new Index($autoincrementIdentifierName, [$quotedName], true, true);
496
497        $sql[] = 'DECLARE
498  constraints_Count NUMBER;
499BEGIN
500  SELECT COUNT(CONSTRAINT_NAME) INTO constraints_Count FROM USER_CONSTRAINTS WHERE TABLE_NAME = \'' . $unquotedTableName . '\' AND CONSTRAINT_TYPE = \'P\';
501  IF constraints_Count = 0 OR constraints_Count = \'\' THEN
502    EXECUTE IMMEDIATE \'' . $this->getCreateConstraintSQL($idx, $quotedTableName) . '\';
503  END IF;
504END;';
505
506        $sequenceName = $this->getIdentitySequenceName(
507            $tableIdentifier->isQuoted() ? $quotedTableName : $unquotedTableName,
508            $nameIdentifier->isQuoted() ? $quotedName : $unquotedName
509        );
510        $sequence     = new Sequence($sequenceName, $start);
511        $sql[]        = $this->getCreateSequenceSQL($sequence);
512
513        $sql[] = 'CREATE TRIGGER ' . $autoincrementIdentifierName . '
514   BEFORE INSERT
515   ON ' . $quotedTableName . '
516   FOR EACH ROW
517DECLARE
518   last_Sequence NUMBER;
519   last_InsertID NUMBER;
520BEGIN
521   SELECT ' . $sequenceName . '.NEXTVAL INTO :NEW.' . $quotedName . ' FROM DUAL;
522   IF (:NEW.' . $quotedName . ' IS NULL OR :NEW.' . $quotedName . ' = 0) THEN
523      SELECT ' . $sequenceName . '.NEXTVAL INTO :NEW.' . $quotedName . ' FROM DUAL;
524   ELSE
525      SELECT NVL(Last_Number, 0) INTO last_Sequence
526        FROM User_Sequences
527       WHERE Sequence_Name = \'' . $sequence->getName() . '\';
528      SELECT :NEW.' . $quotedName . ' INTO last_InsertID FROM DUAL;
529      WHILE (last_InsertID > last_Sequence) LOOP
530         SELECT ' . $sequenceName . '.NEXTVAL INTO last_Sequence FROM DUAL;
531      END LOOP;
532   END IF;
533END;';
534
535        return $sql;
536    }
537
538    /**
539     * Returns the SQL statements to drop the autoincrement for the given table name.
540     *
541     * @param string $table The table name to drop the autoincrement for.
542     *
543     * @return string[]
544     */
545    public function getDropAutoincrementSql($table)
546    {
547        $table                       = $this->normalizeIdentifier($table);
548        $autoincrementIdentifierName = $this->getAutoincrementIdentifierName($table);
549        $identitySequenceName        = $this->getIdentitySequenceName(
550            $table->isQuoted() ? $table->getQuotedName($this) : $table->getName(),
551            ''
552        );
553
554        return [
555            'DROP TRIGGER ' . $autoincrementIdentifierName,
556            $this->getDropSequenceSQL($identitySequenceName),
557            $this->getDropConstraintSQL($autoincrementIdentifierName, $table->getQuotedName($this)),
558        ];
559    }
560
561    /**
562     * Normalizes the given identifier.
563     *
564     * Uppercases the given identifier if it is not quoted by intention
565     * to reflect Oracle's internal auto uppercasing strategy of unquoted identifiers.
566     *
567     * @param string $name The identifier to normalize.
568     *
569     * @return Identifier The normalized identifier.
570     */
571    private function normalizeIdentifier($name)
572    {
573        $identifier = new Identifier($name);
574
575        return $identifier->isQuoted() ? $identifier : new Identifier(strtoupper($name));
576    }
577
578    /**
579     * Returns the autoincrement primary key identifier name for the given table identifier.
580     *
581     * Quotes the autoincrement primary key identifier name
582     * if the given table name is quoted by intention.
583     *
584     * @param Identifier $table The table identifier to return the autoincrement primary key identifier name for.
585     *
586     * @return string
587     */
588    private function getAutoincrementIdentifierName(Identifier $table)
589    {
590        $identifierName = $table->getName() . '_AI_PK';
591
592        return $table->isQuoted()
593            ? $this->quoteSingleIdentifier($identifierName)
594            : $identifierName;
595    }
596
597    /**
598     * {@inheritDoc}
599     */
600    public function getListTableForeignKeysSQL($table)
601    {
602        $table = $this->normalizeIdentifier($table);
603        $table = $this->quoteStringLiteral($table->getName());
604
605        return "SELECT alc.constraint_name,
606          alc.DELETE_RULE,
607          cols.column_name \"local_column\",
608          cols.position,
609          (
610              SELECT r_cols.table_name
611              FROM   user_cons_columns r_cols
612              WHERE  alc.r_constraint_name = r_cols.constraint_name
613              AND    r_cols.position = cols.position
614          ) AS \"references_table\",
615          (
616              SELECT r_cols.column_name
617              FROM   user_cons_columns r_cols
618              WHERE  alc.r_constraint_name = r_cols.constraint_name
619              AND    r_cols.position = cols.position
620          ) AS \"foreign_column\"
621     FROM user_cons_columns cols
622     JOIN user_constraints alc
623       ON alc.constraint_name = cols.constraint_name
624      AND alc.constraint_type = 'R'
625      AND alc.table_name = " . $table . '
626    ORDER BY cols.constraint_name ASC, cols.position ASC';
627    }
628
629    /**
630     * {@inheritDoc}
631     */
632    public function getListTableConstraintsSQL($table)
633    {
634        $table = $this->normalizeIdentifier($table);
635        $table = $this->quoteStringLiteral($table->getName());
636
637        return 'SELECT * FROM user_constraints WHERE table_name = ' . $table;
638    }
639
640    /**
641     * {@inheritDoc}
642     */
643    public function getListTableColumnsSQL($table, $database = null)
644    {
645        $table = $this->normalizeIdentifier($table);
646        $table = $this->quoteStringLiteral($table->getName());
647
648        $tabColumnsTableName       = 'user_tab_columns';
649        $colCommentsTableName      = 'user_col_comments';
650        $tabColumnsOwnerCondition  = '';
651        $colCommentsOwnerCondition = '';
652
653        if ($database !== null && $database !== '/') {
654            $database                  = $this->normalizeIdentifier($database);
655            $database                  = $this->quoteStringLiteral($database->getName());
656            $tabColumnsTableName       = 'all_tab_columns';
657            $colCommentsTableName      = 'all_col_comments';
658            $tabColumnsOwnerCondition  = ' AND c.owner = ' . $database;
659            $colCommentsOwnerCondition = ' AND d.OWNER = c.OWNER';
660        }
661
662        return sprintf(
663            <<<'SQL'
664SELECT   c.*,
665         (
666             SELECT d.comments
667             FROM   %s d
668             WHERE  d.TABLE_NAME = c.TABLE_NAME%s
669             AND    d.COLUMN_NAME = c.COLUMN_NAME
670         ) AS comments
671FROM     %s c
672WHERE    c.table_name = %s%s
673ORDER BY c.column_id
674SQL
675            ,
676            $colCommentsTableName,
677            $colCommentsOwnerCondition,
678            $tabColumnsTableName,
679            $table,
680            $tabColumnsOwnerCondition
681        );
682    }
683
684    /**
685     * {@inheritDoc}
686     */
687    public function getDropSequenceSQL($sequence)
688    {
689        if ($sequence instanceof Sequence) {
690            $sequence = $sequence->getQuotedName($this);
691        }
692
693        return 'DROP SEQUENCE ' . $sequence;
694    }
695
696    /**
697     * {@inheritDoc}
698     */
699    public function getDropForeignKeySQL($foreignKey, $table)
700    {
701        if (! $foreignKey instanceof ForeignKeyConstraint) {
702            $foreignKey = new Identifier($foreignKey);
703        }
704
705        if (! $table instanceof Table) {
706            $table = new Identifier($table);
707        }
708
709        $foreignKey = $foreignKey->getQuotedName($this);
710        $table      = $table->getQuotedName($this);
711
712        return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $foreignKey;
713    }
714
715    /**
716     * {@inheritdoc}
717     */
718    public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey)
719    {
720        $referentialAction = null;
721
722        if ($foreignKey->hasOption('onDelete')) {
723            $referentialAction = $this->getForeignKeyReferentialActionSQL($foreignKey->getOption('onDelete'));
724        }
725
726        return $referentialAction ? ' ON DELETE ' . $referentialAction : '';
727    }
728
729    /**
730     * {@inheritdoc}
731     */
732    public function getForeignKeyReferentialActionSQL($action)
733    {
734        $action = strtoupper($action);
735
736        switch ($action) {
737            case 'RESTRICT': // RESTRICT is not supported, therefore falling back to NO ACTION.
738            case 'NO ACTION':
739                // NO ACTION cannot be declared explicitly,
740                // therefore returning empty string to indicate to OMIT the referential clause.
741                return '';
742
743            case 'CASCADE':
744            case 'SET NULL':
745                return $action;
746
747            default:
748                // SET DEFAULT is not supported, throw exception instead.
749                throw new InvalidArgumentException('Invalid foreign key action: ' . $action);
750        }
751    }
752
753    /**
754     * {@inheritDoc}
755     */
756    public function getDropDatabaseSQL($database)
757    {
758        return 'DROP USER ' . $database . ' CASCADE';
759    }
760
761    /**
762     * {@inheritDoc}
763     */
764    public function getAlterTableSQL(TableDiff $diff)
765    {
766        $sql         = [];
767        $commentsSQL = [];
768        $columnSql   = [];
769
770        $fields = [];
771
772        foreach ($diff->addedColumns as $column) {
773            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
774                continue;
775            }
776
777            $fields[] = $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray());
778            $comment  = $this->getColumnComment($column);
779
780            if (! $comment) {
781                continue;
782            }
783
784            $commentsSQL[] = $this->getCommentOnColumnSQL(
785                $diff->getName($this)->getQuotedName($this),
786                $column->getQuotedName($this),
787                $comment
788            );
789        }
790
791        if (count($fields)) {
792            $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ADD (' . implode(', ', $fields) . ')';
793        }
794
795        $fields = [];
796        foreach ($diff->changedColumns as $columnDiff) {
797            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
798                continue;
799            }
800
801            $column = $columnDiff->column;
802
803            // Do not generate column alteration clause if type is binary and only fixed property has changed.
804            // Oracle only supports binary type columns with variable length.
805            // Avoids unnecessary table alteration statements.
806            if ($column->getType() instanceof BinaryType &&
807                $columnDiff->hasChanged('fixed') &&
808                count($columnDiff->changedProperties) === 1
809            ) {
810                continue;
811            }
812
813            $columnHasChangedComment = $columnDiff->hasChanged('comment');
814
815            /**
816             * Do not add query part if only comment has changed
817             */
818            if (! ($columnHasChangedComment && count($columnDiff->changedProperties) === 1)) {
819                $columnInfo = $column->toArray();
820
821                if (! $columnDiff->hasChanged('notnull')) {
822                    unset($columnInfo['notnull']);
823                }
824
825                $fields[] = $column->getQuotedName($this) . $this->getColumnDeclarationSQL('', $columnInfo);
826            }
827
828            if (! $columnHasChangedComment) {
829                continue;
830            }
831
832            $commentsSQL[] = $this->getCommentOnColumnSQL(
833                $diff->getName($this)->getQuotedName($this),
834                $column->getQuotedName($this),
835                $this->getColumnComment($column)
836            );
837        }
838
839        if (count($fields)) {
840            $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' MODIFY (' . implode(', ', $fields) . ')';
841        }
842
843        foreach ($diff->renamedColumns as $oldColumnName => $column) {
844            if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
845                continue;
846            }
847
848            $oldColumnName = new Identifier($oldColumnName);
849
850            $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) .
851                ' RENAME COLUMN ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this);
852        }
853
854        $fields = [];
855        foreach ($diff->removedColumns as $column) {
856            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
857                continue;
858            }
859
860            $fields[] = $column->getQuotedName($this);
861        }
862
863        if (count($fields)) {
864            $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' DROP (' . implode(', ', $fields) . ')';
865        }
866
867        $tableSql = [];
868
869        if (! $this->onSchemaAlterTable($diff, $tableSql)) {
870            $sql = array_merge($sql, $commentsSQL);
871
872            if ($diff->newName !== false) {
873                $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' RENAME TO ' . $diff->getNewName()->getQuotedName($this);
874            }
875
876            $sql = array_merge(
877                $this->getPreAlterTableIndexForeignKeySQL($diff),
878                $sql,
879                $this->getPostAlterTableIndexForeignKeySQL($diff)
880            );
881        }
882
883        return array_merge($sql, $tableSql, $columnSql);
884    }
885
886    /**
887     * {@inheritdoc}
888     */
889    public function getColumnDeclarationSQL($name, array $field)
890    {
891        if (isset($field['columnDefinition'])) {
892            $columnDef = $this->getCustomTypeDeclarationSQL($field);
893        } else {
894            $default = $this->getDefaultValueDeclarationSQL($field);
895
896            $notnull = '';
897
898            if (isset($field['notnull'])) {
899                $notnull = $field['notnull'] ? ' NOT NULL' : ' NULL';
900            }
901
902            $unique = isset($field['unique']) && $field['unique'] ?
903                ' ' . $this->getUniqueFieldDeclarationSQL() : '';
904
905            $check = isset($field['check']) && $field['check'] ?
906                ' ' . $field['check'] : '';
907
908            $typeDecl  = $field['type']->getSQLDeclaration($field, $this);
909            $columnDef = $typeDecl . $default . $notnull . $unique . $check;
910        }
911
912        return $name . ' ' . $columnDef;
913    }
914
915    /**
916     * {@inheritdoc}
917     */
918    protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName)
919    {
920        if (strpos($tableName, '.') !== false) {
921            [$schema]     = explode('.', $tableName);
922            $oldIndexName = $schema . '.' . $oldIndexName;
923        }
924
925        return ['ALTER INDEX ' . $oldIndexName . ' RENAME TO ' . $index->getQuotedName($this)];
926    }
927
928    /**
929     * {@inheritDoc}
930     */
931    public function prefersSequences()
932    {
933        return true;
934    }
935
936    /**
937     * {@inheritdoc}
938     */
939    public function usesSequenceEmulatedIdentityColumns()
940    {
941        return true;
942    }
943
944    /**
945     * {@inheritdoc}
946     */
947    public function getIdentitySequenceName($tableName, $columnName)
948    {
949        $table = new Identifier($tableName);
950
951        // No usage of column name to preserve BC compatibility with <2.5
952        $identitySequenceName = $table->getName() . '_SEQ';
953
954        if ($table->isQuoted()) {
955            $identitySequenceName = '"' . $identitySequenceName . '"';
956        }
957
958        $identitySequenceIdentifier = $this->normalizeIdentifier($identitySequenceName);
959
960        return $identitySequenceIdentifier->getQuotedName($this);
961    }
962
963    /**
964     * {@inheritDoc}
965     */
966    public function supportsCommentOnStatement()
967    {
968        return true;
969    }
970
971    /**
972     * {@inheritDoc}
973     */
974    public function getName()
975    {
976        return 'oracle';
977    }
978
979    /**
980     * {@inheritDoc}
981     */
982    protected function doModifyLimitQuery($query, $limit, $offset = null)
983    {
984        if ($limit === null && $offset <= 0) {
985            return $query;
986        }
987
988        if (preg_match('/^\s*SELECT/i', $query)) {
989            if (! preg_match('/\sFROM\s/i', $query)) {
990                $query .= ' FROM dual';
991            }
992
993            $columns = ['a.*'];
994
995            if ($offset > 0) {
996                $columns[] = 'ROWNUM AS doctrine_rownum';
997            }
998
999            $query = sprintf('SELECT %s FROM (%s) a', implode(', ', $columns), $query);
1000
1001            if ($limit !== null) {
1002                $query .= sprintf(' WHERE ROWNUM <= %d', $offset + $limit);
1003            }
1004
1005            if ($offset > 0) {
1006                $query = sprintf('SELECT * FROM (%s) WHERE doctrine_rownum >= %d', $query, $offset + 1);
1007            }
1008        }
1009
1010        return $query;
1011    }
1012
1013    /**
1014     * {@inheritDoc}
1015     *
1016     * Oracle returns all column names in SQL result sets in uppercase.
1017     */
1018    public function getSQLResultCasing($column)
1019    {
1020        return strtoupper($column);
1021    }
1022
1023    /**
1024     * {@inheritDoc}
1025     */
1026    public function getCreateTemporaryTableSnippetSQL()
1027    {
1028        return 'CREATE GLOBAL TEMPORARY TABLE';
1029    }
1030
1031    /**
1032     * {@inheritDoc}
1033     */
1034    public function getDateTimeTzFormatString()
1035    {
1036        return 'Y-m-d H:i:sP';
1037    }
1038
1039    /**
1040     * {@inheritDoc}
1041     */
1042    public function getDateFormatString()
1043    {
1044        return 'Y-m-d 00:00:00';
1045    }
1046
1047    /**
1048     * {@inheritDoc}
1049     */
1050    public function getTimeFormatString()
1051    {
1052        return '1900-01-01 H:i:s';
1053    }
1054
1055    /**
1056     * {@inheritDoc}
1057     */
1058    public function fixSchemaElementName($schemaElementName)
1059    {
1060        if (strlen($schemaElementName) > 30) {
1061            // Trim it
1062            return substr($schemaElementName, 0, 30);
1063        }
1064
1065        return $schemaElementName;
1066    }
1067
1068    /**
1069     * {@inheritDoc}
1070     */
1071    public function getMaxIdentifierLength()
1072    {
1073        return 30;
1074    }
1075
1076    /**
1077     * {@inheritDoc}
1078     */
1079    public function supportsSequences()
1080    {
1081        return true;
1082    }
1083
1084    /**
1085     * {@inheritDoc}
1086     */
1087    public function supportsForeignKeyOnUpdate()
1088    {
1089        return false;
1090    }
1091
1092    /**
1093     * {@inheritDoc}
1094     */
1095    public function supportsReleaseSavepoints()
1096    {
1097        return false;
1098    }
1099
1100    /**
1101     * {@inheritDoc}
1102     */
1103    public function getTruncateTableSQL($tableName, $cascade = false)
1104    {
1105        $tableIdentifier = new Identifier($tableName);
1106
1107        return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this);
1108    }
1109
1110    /**
1111     * {@inheritDoc}
1112     */
1113    public function getDummySelectSQL()
1114    {
1115        $expression = func_num_args() > 0 ? func_get_arg(0) : '1';
1116
1117        return sprintf('SELECT %s FROM DUAL', $expression);
1118    }
1119
1120    /**
1121     * {@inheritDoc}
1122     */
1123    protected function initializeDoctrineTypeMappings()
1124    {
1125        $this->doctrineTypeMapping = [
1126            'integer'           => 'integer',
1127            'number'            => 'integer',
1128            'pls_integer'       => 'boolean',
1129            'binary_integer'    => 'boolean',
1130            'varchar'           => 'string',
1131            'varchar2'          => 'string',
1132            'nvarchar2'         => 'string',
1133            'char'              => 'string',
1134            'nchar'             => 'string',
1135            'date'              => 'date',
1136            'timestamp'         => 'datetime',
1137            'timestamptz'       => 'datetimetz',
1138            'float'             => 'float',
1139            'binary_float'      => 'float',
1140            'binary_double'     => 'float',
1141            'long'              => 'string',
1142            'clob'              => 'text',
1143            'nclob'             => 'text',
1144            'raw'               => 'binary',
1145            'long raw'          => 'blob',
1146            'rowid'             => 'string',
1147            'urowid'            => 'string',
1148            'blob'              => 'blob',
1149        ];
1150    }
1151
1152    /**
1153     * {@inheritDoc}
1154     */
1155    public function releaseSavePoint($savepoint)
1156    {
1157        return '';
1158    }
1159
1160    /**
1161     * {@inheritDoc}
1162     */
1163    protected function getReservedKeywordsClass()
1164    {
1165        return Keywords\OracleKeywords::class;
1166    }
1167
1168    /**
1169     * {@inheritDoc}
1170     */
1171    public function getBlobTypeDeclarationSQL(array $field)
1172    {
1173        return 'BLOB';
1174    }
1175}
1176