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