vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/DB2Platform.php

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