1<?php
2
3namespace Doctrine\DBAL\Platforms;
4
5use Doctrine\DBAL\Exception;
6use Doctrine\DBAL\Schema\ColumnDiff;
7use Doctrine\DBAL\Schema\Identifier;
8use Doctrine\DBAL\Schema\Index;
9use Doctrine\DBAL\Schema\TableDiff;
10use Doctrine\DBAL\Types\Type;
11use Doctrine\DBAL\Types\Types;
12
13use function array_merge;
14use function count;
15use function current;
16use function explode;
17use function func_get_arg;
18use function func_num_args;
19use function implode;
20use function sprintf;
21use function strpos;
22use function strtoupper;
23
24class DB2Platform extends AbstractPlatform
25{
26    public function getCharMaxLength(): int
27    {
28        return 254;
29    }
30
31    /**
32     * {@inheritdoc}
33     */
34    public function getBinaryMaxLength()
35    {
36        return 32704;
37    }
38
39    /**
40     * {@inheritdoc}
41     */
42    public function getBinaryDefaultLength()
43    {
44        return 1;
45    }
46
47    /**
48     * {@inheritDoc}
49     */
50    public function getVarcharTypeDeclarationSQL(array $column)
51    {
52        // for IBM DB2, the CHAR max length is less than VARCHAR default length
53        if (! isset($column['length']) && ! empty($column['fixed'])) {
54            $column['length'] = $this->getCharMaxLength();
55        }
56
57        return parent::getVarcharTypeDeclarationSQL($column);
58    }
59
60    /**
61     * {@inheritDoc}
62     */
63    public function getBlobTypeDeclarationSQL(array $column)
64    {
65        // todo blob(n) with $column['length'];
66        return 'BLOB(1M)';
67    }
68
69    /**
70     * {@inheritDoc}
71     */
72    public function initializeDoctrineTypeMappings()
73    {
74        $this->doctrineTypeMapping = [
75            'smallint'      => 'smallint',
76            'bigint'        => 'bigint',
77            'integer'       => 'integer',
78            'time'          => 'time',
79            'date'          => 'date',
80            'varchar'       => 'string',
81            'character'     => 'string',
82            'varbinary'     => 'binary',
83            'binary'        => 'binary',
84            'clob'          => 'text',
85            'blob'          => 'blob',
86            'decimal'       => 'decimal',
87            'double'        => 'float',
88            'real'          => 'float',
89            'timestamp'     => 'datetime',
90        ];
91    }
92
93    /**
94     * {@inheritdoc}
95     */
96    public function isCommentedDoctrineType(Type $doctrineType)
97    {
98        if ($doctrineType->getName() === Types::BOOLEAN) {
99            // We require a commented boolean type in order to distinguish between boolean and smallint
100            // as both (have to) map to the same native type.
101            return true;
102        }
103
104        return parent::isCommentedDoctrineType($doctrineType);
105    }
106
107    /**
108     * {@inheritDoc}
109     */
110    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
111    {
112        return $fixed ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(254)')
113                : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
114    }
115
116    /**
117     * {@inheritdoc}
118     */
119    protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
120    {
121        return $this->getVarcharTypeDeclarationSQLSnippet($length, $fixed) . ' FOR BIT DATA';
122    }
123
124    /**
125     * {@inheritDoc}
126     */
127    public function getClobTypeDeclarationSQL(array $column)
128    {
129        // todo clob(n) with $column['length'];
130        return 'CLOB(1M)';
131    }
132
133    /**
134     * {@inheritDoc}
135     */
136    public function getName()
137    {
138        return 'db2';
139    }
140
141    /**
142     * {@inheritDoc}
143     */
144    public function getBooleanTypeDeclarationSQL(array $column)
145    {
146        return 'SMALLINT';
147    }
148
149    /**
150     * {@inheritDoc}
151     */
152    public function getIntegerTypeDeclarationSQL(array $column)
153    {
154        return 'INTEGER' . $this->_getCommonIntegerTypeDeclarationSQL($column);
155    }
156
157    /**
158     * {@inheritDoc}
159     */
160    public function getBigIntTypeDeclarationSQL(array $column)
161    {
162        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($column);
163    }
164
165    /**
166     * {@inheritDoc}
167     */
168    public function getSmallIntTypeDeclarationSQL(array $column)
169    {
170        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($column);
171    }
172
173    /**
174     * {@inheritDoc}
175     */
176    protected function _getCommonIntegerTypeDeclarationSQL(array $column)
177    {
178        $autoinc = '';
179        if (! empty($column['autoincrement'])) {
180            $autoinc = ' GENERATED BY DEFAULT AS IDENTITY';
181        }
182
183        return $autoinc;
184    }
185
186    /**
187     * {@inheritdoc}
188     */
189    public function getBitAndComparisonExpression($value1, $value2)
190    {
191        return 'BITAND(' . $value1 . ', ' . $value2 . ')';
192    }
193
194    /**
195     * {@inheritdoc}
196     */
197    public function getBitOrComparisonExpression($value1, $value2)
198    {
199        return 'BITOR(' . $value1 . ', ' . $value2 . ')';
200    }
201
202    /**
203     * {@inheritdoc}
204     */
205    protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
206    {
207        switch ($unit) {
208            case DateIntervalUnit::WEEK:
209                $interval *= 7;
210                $unit      = DateIntervalUnit::DAY;
211                break;
212
213            case DateIntervalUnit::QUARTER:
214                $interval *= 3;
215                $unit      = DateIntervalUnit::MONTH;
216                break;
217        }
218
219        return $date . ' ' . $operator . ' ' . $interval . ' ' . $unit;
220    }
221
222    /**
223     * {@inheritdoc}
224     */
225    public function getDateDiffExpression($date1, $date2)
226    {
227        return 'DAYS(' . $date1 . ') - DAYS(' . $date2 . ')';
228    }
229
230    /**
231     * {@inheritDoc}
232     */
233    public function getDateTimeTypeDeclarationSQL(array $column)
234    {
235        if (isset($column['version']) && $column['version'] === true) {
236            return 'TIMESTAMP(0) WITH DEFAULT';
237        }
238
239        return 'TIMESTAMP(0)';
240    }
241
242    /**
243     * {@inheritDoc}
244     */
245    public function getDateTypeDeclarationSQL(array $column)
246    {
247        return 'DATE';
248    }
249
250    /**
251     * {@inheritDoc}
252     */
253    public function getTimeTypeDeclarationSQL(array $column)
254    {
255        return 'TIME';
256    }
257
258    /**
259     * {@inheritdoc}
260     */
261    public function getTruncateTableSQL($tableName, $cascade = false)
262    {
263        $tableIdentifier = new Identifier($tableName);
264
265        return 'TRUNCATE ' . $tableIdentifier->getQuotedName($this) . ' IMMEDIATE';
266    }
267
268    /**
269     * This code fragment is originally from the Zend_Db_Adapter_Db2 class, but has been edited.
270     *
271     * @param string $table
272     * @param string $database
273     *
274     * @return string
275     */
276    public function getListTableColumnsSQL($table, $database = null)
277    {
278        $table = $this->quoteStringLiteral($table);
279
280        // We do the funky subquery and join syscat.columns.default this crazy way because
281        // as of db2 v10, the column is CLOB(64k) and the distinct operator won't allow a CLOB,
282        // it wants shorter stuff like a varchar.
283        return "
284        SELECT
285          cols.default,
286          subq.*
287        FROM (
288               SELECT DISTINCT
289                 c.tabschema,
290                 c.tabname,
291                 c.colname,
292                 c.colno,
293                 c.typename,
294                 c.nulls,
295                 c.length,
296                 c.scale,
297                 c.identity,
298                 tc.type AS tabconsttype,
299                 c.remarks AS comment,
300                 k.colseq,
301                 CASE
302                 WHEN c.generated = 'D' THEN 1
303                 ELSE 0
304                 END     AS autoincrement
305               FROM syscat.columns c
306                 LEFT JOIN (syscat.keycoluse k JOIN syscat.tabconst tc
307                     ON (k.tabschema = tc.tabschema
308                         AND k.tabname = tc.tabname
309                         AND tc.type = 'P'))
310                   ON (c.tabschema = k.tabschema
311                       AND c.tabname = k.tabname
312                       AND c.colname = k.colname)
313               WHERE UPPER(c.tabname) = UPPER(" . $table . ')
314               ORDER BY c.colno
315             ) subq
316          JOIN syscat.columns cols
317            ON subq.tabschema = cols.tabschema
318               AND subq.tabname = cols.tabname
319               AND subq.colno = cols.colno
320        ORDER BY subq.colno
321        ';
322    }
323
324    /**
325     * {@inheritDoc}
326     */
327    public function getListTablesSQL()
328    {
329        return "SELECT NAME FROM SYSIBM.SYSTABLES WHERE TYPE = 'T'";
330    }
331
332    /**
333     * {@inheritDoc}
334     */
335    public function getListViewsSQL($database)
336    {
337        return 'SELECT NAME, TEXT FROM SYSIBM.SYSVIEWS';
338    }
339
340    /**
341     * {@inheritDoc}
342     */
343    public function getListTableIndexesSQL($table, $database = null)
344    {
345        $table = $this->quoteStringLiteral($table);
346
347        return "SELECT   idx.INDNAME AS key_name,
348                         idxcol.COLNAME AS column_name,
349                         CASE
350                             WHEN idx.UNIQUERULE = 'P' THEN 1
351                             ELSE 0
352                         END AS primary,
353                         CASE
354                             WHEN idx.UNIQUERULE = 'D' THEN 1
355                             ELSE 0
356                         END AS non_unique
357                FROM     SYSCAT.INDEXES AS idx
358                JOIN     SYSCAT.INDEXCOLUSE AS idxcol
359                ON       idx.INDSCHEMA = idxcol.INDSCHEMA AND idx.INDNAME = idxcol.INDNAME
360                WHERE    idx.TABNAME = UPPER(" . $table . ')
361                ORDER BY idxcol.COLSEQ ASC';
362    }
363
364    /**
365     * {@inheritDoc}
366     */
367    public function getListTableForeignKeysSQL($table)
368    {
369        $table = $this->quoteStringLiteral($table);
370
371        return "SELECT   fkcol.COLNAME AS local_column,
372                         fk.REFTABNAME AS foreign_table,
373                         pkcol.COLNAME AS foreign_column,
374                         fk.CONSTNAME AS index_name,
375                         CASE
376                             WHEN fk.UPDATERULE = 'R' THEN 'RESTRICT'
377                             ELSE NULL
378                         END AS on_update,
379                         CASE
380                             WHEN fk.DELETERULE = 'C' THEN 'CASCADE'
381                             WHEN fk.DELETERULE = 'N' THEN 'SET NULL'
382                             WHEN fk.DELETERULE = 'R' THEN 'RESTRICT'
383                             ELSE NULL
384                         END AS on_delete
385                FROM     SYSCAT.REFERENCES AS fk
386                JOIN     SYSCAT.KEYCOLUSE AS fkcol
387                ON       fk.CONSTNAME = fkcol.CONSTNAME
388                AND      fk.TABSCHEMA = fkcol.TABSCHEMA
389                AND      fk.TABNAME = fkcol.TABNAME
390                JOIN     SYSCAT.KEYCOLUSE AS pkcol
391                ON       fk.REFKEYNAME = pkcol.CONSTNAME
392                AND      fk.REFTABSCHEMA = pkcol.TABSCHEMA
393                AND      fk.REFTABNAME = pkcol.TABNAME
394                WHERE    fk.TABNAME = UPPER(" . $table . ')
395                ORDER BY fkcol.COLSEQ ASC';
396    }
397
398    /**
399     * {@inheritDoc}
400     */
401    public function getCreateViewSQL($name, $sql)
402    {
403        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
404    }
405
406    /**
407     * {@inheritDoc}
408     */
409    public function getDropViewSQL($name)
410    {
411        return 'DROP VIEW ' . $name;
412    }
413
414    /**
415     * {@inheritDoc}
416     */
417    public function getCreateDatabaseSQL($name)
418    {
419        return 'CREATE DATABASE ' . $name;
420    }
421
422    /**
423     * {@inheritDoc}
424     */
425    public function getDropDatabaseSQL($name)
426    {
427        return 'DROP DATABASE ' . $name;
428    }
429
430    /**
431     * {@inheritDoc}
432     */
433    public function supportsCreateDropDatabase()
434    {
435        return false;
436    }
437
438    /**
439     * {@inheritDoc}
440     */
441    public function supportsReleaseSavepoints()
442    {
443        return false;
444    }
445
446    /**
447     * {@inheritdoc}
448     */
449    public function supportsCommentOnStatement()
450    {
451        return true;
452    }
453
454    /**
455     * {@inheritDoc}
456     */
457    public function getCurrentDateSQL()
458    {
459        return 'CURRENT DATE';
460    }
461
462    /**
463     * {@inheritDoc}
464     */
465    public function getCurrentTimeSQL()
466    {
467        return 'CURRENT TIME';
468    }
469
470    /**
471     * {@inheritDoc}
472     */
473    public function getCurrentTimestampSQL()
474    {
475        return 'CURRENT TIMESTAMP';
476    }
477
478    /**
479     * {@inheritDoc}
480     */
481    public function getIndexDeclarationSQL($name, Index $index)
482    {
483        // Index declaration in statements like CREATE TABLE is not supported.
484        throw Exception::notSupported(__METHOD__);
485    }
486
487    /**
488     * {@inheritDoc}
489     */
490    protected function _getCreateTableSQL($name, array $columns, array $options = [])
491    {
492        $indexes = [];
493        if (isset($options['indexes'])) {
494            $indexes = $options['indexes'];
495        }
496
497        $options['indexes'] = [];
498
499        $sqls = parent::_getCreateTableSQL($name, $columns, $options);
500
501        foreach ($indexes as $definition) {
502            $sqls[] = $this->getCreateIndexSQL($definition, $name);
503        }
504
505        return $sqls;
506    }
507
508    /**
509     * {@inheritDoc}
510     */
511    public function getAlterTableSQL(TableDiff $diff)
512    {
513        $sql         = [];
514        $columnSql   = [];
515        $commentsSQL = [];
516
517        $queryParts = [];
518        foreach ($diff->addedColumns as $column) {
519            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
520                continue;
521            }
522
523            $columnDef = $column->toArray();
524            $queryPart = 'ADD COLUMN ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);
525
526            // Adding non-nullable columns to a table requires a default value to be specified.
527            if (
528                ! empty($columnDef['notnull']) &&
529                ! isset($columnDef['default']) &&
530                empty($columnDef['autoincrement'])
531            ) {
532                $queryPart .= ' WITH DEFAULT';
533            }
534
535            $queryParts[] = $queryPart;
536
537            $comment = $this->getColumnComment($column);
538
539            if ($comment === null || $comment === '') {
540                continue;
541            }
542
543            $commentsSQL[] = $this->getCommentOnColumnSQL(
544                $diff->getName($this)->getQuotedName($this),
545                $column->getQuotedName($this),
546                $comment
547            );
548        }
549
550        foreach ($diff->removedColumns as $column) {
551            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
552                continue;
553            }
554
555            $queryParts[] =  'DROP COLUMN ' . $column->getQuotedName($this);
556        }
557
558        foreach ($diff->changedColumns as $columnDiff) {
559            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
560                continue;
561            }
562
563            if ($columnDiff->hasChanged('comment')) {
564                $commentsSQL[] = $this->getCommentOnColumnSQL(
565                    $diff->getName($this)->getQuotedName($this),
566                    $columnDiff->column->getQuotedName($this),
567                    $this->getColumnComment($columnDiff->column)
568                );
569
570                if (count($columnDiff->changedProperties) === 1) {
571                    continue;
572                }
573            }
574
575            $this->gatherAlterColumnSQL($diff->getName($this), $columnDiff, $sql, $queryParts);
576        }
577
578        foreach ($diff->renamedColumns as $oldColumnName => $column) {
579            if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
580                continue;
581            }
582
583            $oldColumnName = new Identifier($oldColumnName);
584
585            $queryParts[] =  'RENAME COLUMN ' . $oldColumnName->getQuotedName($this) .
586                ' TO ' . $column->getQuotedName($this);
587        }
588
589        $tableSql = [];
590
591        if (! $this->onSchemaAlterTable($diff, $tableSql)) {
592            if (count($queryParts) > 0) {
593                $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . implode(' ', $queryParts);
594            }
595
596            // Some table alteration operations require a table reorganization.
597            if (! empty($diff->removedColumns) || ! empty($diff->changedColumns)) {
598                $sql[] = "CALL SYSPROC.ADMIN_CMD ('REORG TABLE " . $diff->getName($this)->getQuotedName($this) . "')";
599            }
600
601            $sql = array_merge($sql, $commentsSQL);
602
603            $newName = $diff->getNewName();
604
605            if ($newName !== false) {
606                $sql[] = sprintf(
607                    'RENAME TABLE %s TO %s',
608                    $diff->getName($this)->getQuotedName($this),
609                    $newName->getQuotedName($this)
610                );
611            }
612
613            $sql = array_merge(
614                $this->getPreAlterTableIndexForeignKeySQL($diff),
615                $sql,
616                $this->getPostAlterTableIndexForeignKeySQL($diff)
617            );
618        }
619
620        return array_merge($sql, $tableSql, $columnSql);
621    }
622
623    /**
624     * Gathers the table alteration SQL for a given column diff.
625     *
626     * @param Identifier $table      The table to gather the SQL for.
627     * @param ColumnDiff $columnDiff The column diff to evaluate.
628     * @param string[]   $sql        The sequence of table alteration statements to fill.
629     * @param mixed[]    $queryParts The sequence of column alteration clauses to fill.
630     */
631    private function gatherAlterColumnSQL(
632        Identifier $table,
633        ColumnDiff $columnDiff,
634        array &$sql,
635        array &$queryParts
636    ): void {
637        $alterColumnClauses = $this->getAlterColumnClausesSQL($columnDiff);
638
639        if (empty($alterColumnClauses)) {
640            return;
641        }
642
643        // If we have a single column alteration, we can append the clause to the main query.
644        if (count($alterColumnClauses) === 1) {
645            $queryParts[] = current($alterColumnClauses);
646
647            return;
648        }
649
650        // We have multiple alterations for the same column,
651        // so we need to trigger a complete ALTER TABLE statement
652        // for each ALTER COLUMN clause.
653        foreach ($alterColumnClauses as $alterColumnClause) {
654            $sql[] = 'ALTER TABLE ' . $table->getQuotedName($this) . ' ' . $alterColumnClause;
655        }
656    }
657
658    /**
659     * Returns the ALTER COLUMN SQL clauses for altering a column described by the given column diff.
660     *
661     * @param ColumnDiff $columnDiff The column diff to evaluate.
662     *
663     * @return string[]
664     */
665    private function getAlterColumnClausesSQL(ColumnDiff $columnDiff)
666    {
667        $column = $columnDiff->column->toArray();
668
669        $alterClause = 'ALTER COLUMN ' . $columnDiff->column->getQuotedName($this);
670
671        if ($column['columnDefinition']) {
672            return [$alterClause . ' ' . $column['columnDefinition']];
673        }
674
675        $clauses = [];
676
677        if (
678            $columnDiff->hasChanged('type') ||
679            $columnDiff->hasChanged('length') ||
680            $columnDiff->hasChanged('precision') ||
681            $columnDiff->hasChanged('scale') ||
682            $columnDiff->hasChanged('fixed')
683        ) {
684            $clauses[] = $alterClause . ' SET DATA TYPE ' . $column['type']->getSQLDeclaration($column, $this);
685        }
686
687        if ($columnDiff->hasChanged('notnull')) {
688            $clauses[] = $column['notnull'] ? $alterClause . ' SET NOT NULL' : $alterClause . ' DROP NOT NULL';
689        }
690
691        if ($columnDiff->hasChanged('default')) {
692            if (isset($column['default'])) {
693                $defaultClause = $this->getDefaultValueDeclarationSQL($column);
694
695                if ($defaultClause) {
696                    $clauses[] = $alterClause . ' SET' . $defaultClause;
697                }
698            } else {
699                $clauses[] = $alterClause . ' DROP DEFAULT';
700            }
701        }
702
703        return $clauses;
704    }
705
706    /**
707     * {@inheritDoc}
708     */
709    protected function getPreAlterTableIndexForeignKeySQL(TableDiff $diff)
710    {
711        $sql   = [];
712        $table = $diff->getName($this)->getQuotedName($this);
713
714        foreach ($diff->removedIndexes as $remKey => $remIndex) {
715            foreach ($diff->addedIndexes as $addKey => $addIndex) {
716                if ($remIndex->getColumns() !== $addIndex->getColumns()) {
717                    continue;
718                }
719
720                if ($remIndex->isPrimary()) {
721                    $sql[] = 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY';
722                } elseif ($remIndex->isUnique()) {
723                    $sql[] = 'ALTER TABLE ' . $table . ' DROP UNIQUE ' . $remIndex->getQuotedName($this);
724                } else {
725                    $sql[] = $this->getDropIndexSQL($remIndex, $table);
726                }
727
728                $sql[] = $this->getCreateIndexSQL($addIndex, $table);
729
730                unset($diff->removedIndexes[$remKey], $diff->addedIndexes[$addKey]);
731
732                break;
733            }
734        }
735
736        $sql = array_merge($sql, parent::getPreAlterTableIndexForeignKeySQL($diff));
737
738        return $sql;
739    }
740
741    /**
742     * {@inheritdoc}
743     */
744    protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName)
745    {
746        if (strpos($tableName, '.') !== false) {
747            [$schema]     = explode('.', $tableName);
748            $oldIndexName = $schema . '.' . $oldIndexName;
749        }
750
751        return ['RENAME INDEX ' . $oldIndexName . ' TO ' . $index->getQuotedName($this)];
752    }
753
754    /**
755     * {@inheritDoc}
756     */
757    public function getDefaultValueDeclarationSQL($column)
758    {
759        if (! empty($column['autoincrement'])) {
760            return '';
761        }
762
763        if (isset($column['version']) && $column['version']) {
764            if ((string) $column['type'] !== 'DateTime') {
765                $column['default'] = '1';
766            }
767        }
768
769        return parent::getDefaultValueDeclarationSQL($column);
770    }
771
772    /**
773     * {@inheritDoc}
774     */
775    public function getEmptyIdentityInsertSQL($quotedTableName, $quotedIdentifierColumnName)
776    {
777        return 'INSERT INTO ' . $quotedTableName . ' (' . $quotedIdentifierColumnName . ') VALUES (DEFAULT)';
778    }
779
780    /**
781     * {@inheritDoc}
782     */
783    public function getCreateTemporaryTableSnippetSQL()
784    {
785        return 'DECLARE GLOBAL TEMPORARY TABLE';
786    }
787
788    /**
789     * {@inheritDoc}
790     */
791    public function getTemporaryTableName($tableName)
792    {
793        return 'SESSION.' . $tableName;
794    }
795
796    /**
797     * {@inheritDoc}
798     */
799    protected function doModifyLimitQuery($query, $limit, $offset = null)
800    {
801        $where = [];
802
803        if ($offset > 0) {
804            $where[] = sprintf('db22.DC_ROWNUM >= %d', $offset + 1);
805        }
806
807        if ($limit !== null) {
808            $where[] = sprintf('db22.DC_ROWNUM <= %d', $offset + $limit);
809        }
810
811        if (empty($where)) {
812            return $query;
813        }
814
815        // Todo OVER() needs ORDER BY data!
816        return sprintf(
817            'SELECT db22.* FROM (SELECT db21.*, ROW_NUMBER() OVER() AS DC_ROWNUM FROM (%s) db21) db22 WHERE %s',
818            $query,
819            implode(' AND ', $where)
820        );
821    }
822
823    /**
824     * {@inheritDoc}
825     */
826    public function getLocateExpression($str, $substr, $startPos = false)
827    {
828        if ($startPos === false) {
829            return 'LOCATE(' . $substr . ', ' . $str . ')';
830        }
831
832        return 'LOCATE(' . $substr . ', ' . $str . ', ' . $startPos . ')';
833    }
834
835    /**
836     * {@inheritDoc}
837     */
838    public function getSubstringExpression($string, $start, $length = null)
839    {
840        if ($length === null) {
841            return 'SUBSTR(' . $string . ', ' . $start . ')';
842        }
843
844        return 'SUBSTR(' . $string . ', ' . $start . ', ' . $length . ')';
845    }
846
847    /**
848     * {@inheritDoc}
849     */
850    public function supportsIdentityColumns()
851    {
852        return true;
853    }
854
855    /**
856     * {@inheritDoc}
857     */
858    public function prefersIdentityColumns()
859    {
860        return true;
861    }
862
863    /**
864     * {@inheritDoc}
865     *
866     * DB2 returns all column names in SQL result sets in uppercase.
867     *
868     * @deprecated
869     */
870    public function getSQLResultCasing($column)
871    {
872        return strtoupper($column);
873    }
874
875    /**
876     * {@inheritDoc}
877     */
878    public function getForUpdateSQL()
879    {
880        return ' WITH RR USE AND KEEP UPDATE LOCKS';
881    }
882
883    /**
884     * {@inheritDoc}
885     */
886    public function getDummySelectSQL()
887    {
888        $expression = func_num_args() > 0 ? func_get_arg(0) : '1';
889
890        return sprintf('SELECT %s FROM sysibm.sysdummy1', $expression);
891    }
892
893    /**
894     * {@inheritDoc}
895     *
896     * DB2 supports savepoints, but they work semantically different than on other vendor platforms.
897     *
898     * TODO: We have to investigate how to get DB2 up and running with savepoints.
899     */
900    public function supportsSavepoints()
901    {
902        return false;
903    }
904
905    /**
906     * {@inheritDoc}
907     */
908    protected function getReservedKeywordsClass()
909    {
910        return Keywords\DB2Keywords::class;
911    }
912
913    public function getListTableCommentsSQL(string $table): string
914    {
915        return sprintf(
916            <<<'SQL'
917SELECT REMARKS
918  FROM SYSIBM.SYSTABLES
919  WHERE NAME = UPPER( %s )
920SQL
921            ,
922            $this->quoteStringLiteral($table)
923        );
924    }
925}
926