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