1<?php 2 3namespace Doctrine\DBAL\Platforms; 4 5use Doctrine\DBAL\Exception; 6use Doctrine\DBAL\LockMode; 7use Doctrine\DBAL\Schema\Column; 8use Doctrine\DBAL\Schema\ColumnDiff; 9use Doctrine\DBAL\Schema\Constraint; 10use Doctrine\DBAL\Schema\ForeignKeyConstraint; 11use Doctrine\DBAL\Schema\Identifier; 12use Doctrine\DBAL\Schema\Index; 13use Doctrine\DBAL\Schema\Table; 14use Doctrine\DBAL\Schema\TableDiff; 15use Doctrine\DBAL\TransactionIsolationLevel; 16use Doctrine\Deprecations\Deprecation; 17use InvalidArgumentException; 18 19use function array_merge; 20use function array_unique; 21use function array_values; 22use function assert; 23use function count; 24use function explode; 25use function func_get_args; 26use function get_class; 27use function implode; 28use function is_string; 29use function preg_match; 30use function sprintf; 31use function strlen; 32use function strpos; 33use function strtoupper; 34use function substr; 35 36/** 37 * The SQLAnywherePlatform provides the behavior, features and SQL dialect of the 38 * SAP Sybase SQL Anywhere 10 database platform. 39 * 40 * @deprecated Support for SQLAnywhere will be removed in 3.0. 41 */ 42class SQLAnywherePlatform extends AbstractPlatform 43{ 44 public const FOREIGN_KEY_MATCH_SIMPLE = 1; 45 public const FOREIGN_KEY_MATCH_FULL = 2; 46 public const FOREIGN_KEY_MATCH_SIMPLE_UNIQUE = 129; 47 public const FOREIGN_KEY_MATCH_FULL_UNIQUE = 130; 48 49 /** 50 * {@inheritdoc} 51 */ 52 public function appendLockHint($fromClause, $lockMode) 53 { 54 switch (true) { 55 case $lockMode === LockMode::NONE: 56 return $fromClause; 57 58 case $lockMode === LockMode::PESSIMISTIC_READ: 59 return $fromClause . ' WITH (UPDLOCK)'; 60 61 case $lockMode === LockMode::PESSIMISTIC_WRITE: 62 return $fromClause . ' WITH (XLOCK)'; 63 64 default: 65 return $fromClause; 66 } 67 } 68 69 /** 70 * {@inheritdoc} 71 * 72 * SQL Anywhere supports a maximum length of 128 bytes for identifiers. 73 */ 74 public function fixSchemaElementName($schemaElementName) 75 { 76 Deprecation::trigger( 77 'doctrine/dbal', 78 'https://github.com/doctrine/dbal/pull/4132', 79 'AbstractPlatform::fixSchemaElementName is deprecated with no replacement and removed in DBAL 3.0' 80 ); 81 82 $maxIdentifierLength = $this->getMaxIdentifierLength(); 83 84 if (strlen($schemaElementName) > $maxIdentifierLength) { 85 return substr($schemaElementName, 0, $maxIdentifierLength); 86 } 87 88 return $schemaElementName; 89 } 90 91 /** 92 * {@inheritdoc} 93 */ 94 public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey) 95 { 96 $query = ''; 97 98 if ($foreignKey->hasOption('match')) { 99 $query = ' MATCH ' . $this->getForeignKeyMatchClauseSQL($foreignKey->getOption('match')); 100 } 101 102 $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey); 103 104 if ($foreignKey->hasOption('check_on_commit') && (bool) $foreignKey->getOption('check_on_commit')) { 105 $query .= ' CHECK ON COMMIT'; 106 } 107 108 if ($foreignKey->hasOption('clustered') && (bool) $foreignKey->getOption('clustered')) { 109 $query .= ' CLUSTERED'; 110 } 111 112 if ($foreignKey->hasOption('for_olap_workload') && (bool) $foreignKey->getOption('for_olap_workload')) { 113 $query .= ' FOR OLAP WORKLOAD'; 114 } 115 116 return $query; 117 } 118 119 /** 120 * {@inheritdoc} 121 */ 122 public function getAlterTableSQL(TableDiff $diff) 123 { 124 $sql = []; 125 $columnSql = []; 126 $commentsSQL = []; 127 $tableSql = []; 128 $alterClauses = []; 129 130 foreach ($diff->addedColumns as $column) { 131 if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { 132 continue; 133 } 134 135 $alterClauses[] = $this->getAlterTableAddColumnClause($column); 136 137 $comment = $this->getColumnComment($column); 138 139 if ($comment === null || $comment === '') { 140 continue; 141 } 142 143 $commentsSQL[] = $this->getCommentOnColumnSQL( 144 $diff->getName($this)->getQuotedName($this), 145 $column->getQuotedName($this), 146 $comment 147 ); 148 } 149 150 foreach ($diff->removedColumns as $column) { 151 if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { 152 continue; 153 } 154 155 $alterClauses[] = $this->getAlterTableRemoveColumnClause($column); 156 } 157 158 foreach ($diff->changedColumns as $columnDiff) { 159 if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { 160 continue; 161 } 162 163 $alterClause = $this->getAlterTableChangeColumnClause($columnDiff); 164 165 if ($alterClause !== null) { 166 $alterClauses[] = $alterClause; 167 } 168 169 if (! $columnDiff->hasChanged('comment')) { 170 continue; 171 } 172 173 $column = $columnDiff->column; 174 175 $commentsSQL[] = $this->getCommentOnColumnSQL( 176 $diff->getName($this)->getQuotedName($this), 177 $column->getQuotedName($this), 178 $this->getColumnComment($column) 179 ); 180 } 181 182 foreach ($diff->renamedColumns as $oldColumnName => $column) { 183 if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { 184 continue; 185 } 186 187 $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . 188 $this->getAlterTableRenameColumnClause($oldColumnName, $column); 189 } 190 191 if (! $this->onSchemaAlterTable($diff, $tableSql)) { 192 if (! empty($alterClauses)) { 193 $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . implode(', ', $alterClauses); 194 } 195 196 $sql = array_merge($sql, $commentsSQL); 197 198 $newName = $diff->getNewName(); 199 200 if ($newName !== false) { 201 $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . 202 $this->getAlterTableRenameTableClause($newName); 203 } 204 205 $sql = array_merge( 206 $this->getPreAlterTableIndexForeignKeySQL($diff), 207 $sql, 208 $this->getPostAlterTableIndexForeignKeySQL($diff) 209 ); 210 } 211 212 return array_merge($sql, $tableSql, $columnSql); 213 } 214 215 /** 216 * Returns the SQL clause for creating a column in a table alteration. 217 * 218 * @param Column $column The column to add. 219 * 220 * @return string 221 */ 222 protected function getAlterTableAddColumnClause(Column $column) 223 { 224 return 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); 225 } 226 227 /** 228 * Returns the SQL clause for altering a table. 229 * 230 * @param Identifier $tableName The quoted name of the table to alter. 231 * 232 * @return string 233 */ 234 protected function getAlterTableClause(Identifier $tableName) 235 { 236 return 'ALTER TABLE ' . $tableName->getQuotedName($this); 237 } 238 239 /** 240 * Returns the SQL clause for dropping a column in a table alteration. 241 * 242 * @param Column $column The column to drop. 243 * 244 * @return string 245 */ 246 protected function getAlterTableRemoveColumnClause(Column $column) 247 { 248 return 'DROP ' . $column->getQuotedName($this); 249 } 250 251 /** 252 * Returns the SQL clause for renaming a column in a table alteration. 253 * 254 * @param string $oldColumnName The quoted name of the column to rename. 255 * @param Column $column The column to rename to. 256 * 257 * @return string 258 */ 259 protected function getAlterTableRenameColumnClause($oldColumnName, Column $column) 260 { 261 $oldColumnName = new Identifier($oldColumnName); 262 263 return 'RENAME ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this); 264 } 265 266 /** 267 * Returns the SQL clause for renaming a table in a table alteration. 268 * 269 * @param Identifier $newTableName The quoted name of the table to rename to. 270 * 271 * @return string 272 */ 273 protected function getAlterTableRenameTableClause(Identifier $newTableName) 274 { 275 return 'RENAME ' . $newTableName->getQuotedName($this); 276 } 277 278 /** 279 * Returns the SQL clause for altering a column in a table alteration. 280 * 281 * This method returns null in case that only the column comment has changed. 282 * Changes in column comments have to be handled differently. 283 * 284 * @param ColumnDiff $columnDiff The diff of the column to alter. 285 * 286 * @return string|null 287 */ 288 protected function getAlterTableChangeColumnClause(ColumnDiff $columnDiff) 289 { 290 $column = $columnDiff->column; 291 292 // Do not return alter clause if only comment has changed. 293 if (! ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1)) { 294 $columnAlterationClause = 'ALTER ' . 295 $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); 296 297 if ($columnDiff->hasChanged('default') && $column->getDefault() === null) { 298 $columnAlterationClause .= ', ALTER ' . $column->getQuotedName($this) . ' DROP DEFAULT'; 299 } 300 301 return $columnAlterationClause; 302 } 303 304 return null; 305 } 306 307 /** 308 * {@inheritdoc} 309 */ 310 public function getBigIntTypeDeclarationSQL(array $column) 311 { 312 $column['integer_type'] = 'BIGINT'; 313 314 return $this->_getCommonIntegerTypeDeclarationSQL($column); 315 } 316 317 /** 318 * {@inheritdoc} 319 */ 320 public function getBinaryDefaultLength() 321 { 322 return 1; 323 } 324 325 /** 326 * {@inheritdoc} 327 */ 328 public function getBinaryMaxLength() 329 { 330 return 32767; 331 } 332 333 /** 334 * {@inheritdoc} 335 */ 336 public function getBlobTypeDeclarationSQL(array $column) 337 { 338 return 'LONG BINARY'; 339 } 340 341 /** 342 * {@inheritdoc} 343 * 344 * BIT type columns require an explicit NULL declaration 345 * in SQL Anywhere if they shall be nullable. 346 * Otherwise by just omitting the NOT NULL clause, 347 * SQL Anywhere will declare them NOT NULL nonetheless. 348 */ 349 public function getBooleanTypeDeclarationSQL(array $column) 350 { 351 $nullClause = isset($column['notnull']) && (bool) $column['notnull'] === false ? ' NULL' : ''; 352 353 return 'BIT' . $nullClause; 354 } 355 356 /** 357 * {@inheritdoc} 358 */ 359 public function getClobTypeDeclarationSQL(array $column) 360 { 361 return 'TEXT'; 362 } 363 364 /** 365 * {@inheritdoc} 366 */ 367 public function getCommentOnColumnSQL($tableName, $columnName, $comment) 368 { 369 $tableName = new Identifier($tableName); 370 $columnName = new Identifier($columnName); 371 $comment = $comment === null ? 'NULL' : $this->quoteStringLiteral($comment); 372 373 return sprintf( 374 'COMMENT ON COLUMN %s.%s IS %s', 375 $tableName->getQuotedName($this), 376 $columnName->getQuotedName($this), 377 $comment 378 ); 379 } 380 381 /** 382 * {@inheritdoc} 383 */ 384 public function getConcatExpression() 385 { 386 return 'STRING(' . implode(', ', func_get_args()) . ')'; 387 } 388 389 /** 390 * {@inheritdoc} 391 */ 392 public function getCreateConstraintSQL(Constraint $constraint, $table) 393 { 394 if ($constraint instanceof ForeignKeyConstraint) { 395 return $this->getCreateForeignKeySQL($constraint, $table); 396 } 397 398 if ($table instanceof Table) { 399 $table = $table->getQuotedName($this); 400 } 401 402 return 'ALTER TABLE ' . $table . 403 ' ADD ' . $this->getTableConstraintDeclarationSQL($constraint, $constraint->getQuotedName($this)); 404 } 405 406 /** 407 * {@inheritdoc} 408 */ 409 public function getCreateDatabaseSQL($name) 410 { 411 $name = new Identifier($name); 412 413 return "CREATE DATABASE '" . $name->getName() . "'"; 414 } 415 416 /** 417 * {@inheritdoc} 418 * 419 * Appends SQL Anywhere specific flags if given. 420 */ 421 public function getCreateIndexSQL(Index $index, $table) 422 { 423 return parent::getCreateIndexSQL($index, $table) . $this->getAdvancedIndexOptionsSQL($index); 424 } 425 426 /** 427 * {@inheritdoc} 428 */ 429 public function getCreatePrimaryKeySQL(Index $index, $table) 430 { 431 if ($table instanceof Table) { 432 $table = $table->getQuotedName($this); 433 } 434 435 return 'ALTER TABLE ' . $table . ' ADD ' . $this->getPrimaryKeyDeclarationSQL($index); 436 } 437 438 /** 439 * {@inheritdoc} 440 */ 441 public function getCreateTemporaryTableSnippetSQL() 442 { 443 return 'CREATE ' . $this->getTemporaryTableSQL() . ' TABLE'; 444 } 445 446 /** 447 * {@inheritdoc} 448 */ 449 public function getCreateViewSQL($name, $sql) 450 { 451 return 'CREATE VIEW ' . $name . ' AS ' . $sql; 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 protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit) 482 { 483 $factorClause = ''; 484 485 if ($operator === '-') { 486 $factorClause = '-1 * '; 487 } 488 489 return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')'; 490 } 491 492 /** 493 * {@inheritdoc} 494 */ 495 public function getDateDiffExpression($date1, $date2) 496 { 497 return 'DATEDIFF(day, ' . $date2 . ', ' . $date1 . ')'; 498 } 499 500 /** 501 * {@inheritdoc} 502 */ 503 public function getDateTimeFormatString() 504 { 505 return 'Y-m-d H:i:s.u'; 506 } 507 508 /** 509 * {@inheritdoc} 510 */ 511 public function getDateTimeTypeDeclarationSQL(array $column) 512 { 513 return 'DATETIME'; 514 } 515 516 /** 517 * {@inheritdoc} 518 */ 519 public function getDateTimeTzFormatString() 520 { 521 return $this->getDateTimeFormatString(); 522 } 523 524 /** 525 * {@inheritdoc} 526 */ 527 public function getDateTypeDeclarationSQL(array $column) 528 { 529 return 'DATE'; 530 } 531 532 /** 533 * {@inheritdoc} 534 */ 535 public function getDefaultTransactionIsolationLevel() 536 { 537 return TransactionIsolationLevel::READ_UNCOMMITTED; 538 } 539 540 /** 541 * {@inheritdoc} 542 */ 543 public function getDropDatabaseSQL($name) 544 { 545 $name = new Identifier($name); 546 547 return "DROP DATABASE '" . $name->getName() . "'"; 548 } 549 550 /** 551 * {@inheritdoc} 552 */ 553 public function getDropIndexSQL($index, $table = null) 554 { 555 if ($index instanceof Index) { 556 $index = $index->getQuotedName($this); 557 } 558 559 if (! is_string($index)) { 560 throw new InvalidArgumentException( 561 __METHOD__ . '() expects $index parameter to be string or ' . Index::class . '.' 562 ); 563 } 564 565 if (! isset($table)) { 566 return 'DROP INDEX ' . $index; 567 } 568 569 if ($table instanceof Table) { 570 $table = $table->getQuotedName($this); 571 } 572 573 if (! is_string($table)) { 574 throw new InvalidArgumentException( 575 __METHOD__ . '() expects $table parameter to be string or ' . Index::class . '.' 576 ); 577 } 578 579 return 'DROP INDEX ' . $table . '.' . $index; 580 } 581 582 /** 583 * {@inheritdoc} 584 */ 585 public function getDropViewSQL($name) 586 { 587 return 'DROP VIEW ' . $name; 588 } 589 590 /** 591 * {@inheritdoc} 592 */ 593 public function getForeignKeyBaseDeclarationSQL(ForeignKeyConstraint $foreignKey) 594 { 595 $sql = ''; 596 $foreignKeyName = $foreignKey->getName(); 597 $localColumns = $foreignKey->getQuotedLocalColumns($this); 598 $foreignColumns = $foreignKey->getQuotedForeignColumns($this); 599 $foreignTableName = $foreignKey->getQuotedForeignTableName($this); 600 601 if (! empty($foreignKeyName)) { 602 $sql .= 'CONSTRAINT ' . $foreignKey->getQuotedName($this) . ' '; 603 } 604 605 if (empty($localColumns)) { 606 throw new InvalidArgumentException("Incomplete definition. 'local' required."); 607 } 608 609 if (empty($foreignColumns)) { 610 throw new InvalidArgumentException("Incomplete definition. 'foreign' required."); 611 } 612 613 if (empty($foreignTableName)) { 614 throw new InvalidArgumentException("Incomplete definition. 'foreignTable' required."); 615 } 616 617 if ($foreignKey->hasOption('notnull') && (bool) $foreignKey->getOption('notnull')) { 618 $sql .= 'NOT NULL '; 619 } 620 621 return $sql . 622 'FOREIGN KEY (' . $this->getIndexFieldDeclarationListSQL($localColumns) . ') ' . 623 'REFERENCES ' . $foreignKey->getQuotedForeignTableName($this) . 624 ' (' . $this->getIndexFieldDeclarationListSQL($foreignColumns) . ')'; 625 } 626 627 /** 628 * Returns foreign key MATCH clause for given type. 629 * 630 * @param int $type The foreign key match type 631 * 632 * @return string 633 * 634 * @throws InvalidArgumentException If unknown match type given. 635 */ 636 public function getForeignKeyMatchClauseSQL($type) 637 { 638 switch ((int) $type) { 639 case self::FOREIGN_KEY_MATCH_SIMPLE: 640 return 'SIMPLE'; 641 642 case self::FOREIGN_KEY_MATCH_FULL: 643 return 'FULL'; 644 645 case self::FOREIGN_KEY_MATCH_SIMPLE_UNIQUE: 646 return 'UNIQUE SIMPLE'; 647 648 case self::FOREIGN_KEY_MATCH_FULL_UNIQUE: 649 return 'UNIQUE FULL'; 650 651 default: 652 throw new InvalidArgumentException('Invalid foreign key match type: ' . $type); 653 } 654 } 655 656 /** 657 * {@inheritdoc} 658 */ 659 public function getForeignKeyReferentialActionSQL($action) 660 { 661 // NO ACTION is not supported, therefore falling back to RESTRICT. 662 if (strtoupper($action) === 'NO ACTION') { 663 return 'RESTRICT'; 664 } 665 666 return parent::getForeignKeyReferentialActionSQL($action); 667 } 668 669 /** 670 * {@inheritdoc} 671 */ 672 public function getForUpdateSQL() 673 { 674 return ''; 675 } 676 677 /** 678 * {@inheritdoc} 679 * 680 * @deprecated Use application-generated UUIDs instead 681 */ 682 public function getGuidExpression() 683 { 684 return 'NEWID()'; 685 } 686 687 /** 688 * {@inheritdoc} 689 */ 690 public function getGuidTypeDeclarationSQL(array $column) 691 { 692 return 'UNIQUEIDENTIFIER'; 693 } 694 695 /** 696 * {@inheritdoc} 697 */ 698 public function getIndexDeclarationSQL($name, Index $index) 699 { 700 // Index declaration in statements like CREATE TABLE is not supported. 701 throw Exception::notSupported(__METHOD__); 702 } 703 704 /** 705 * {@inheritdoc} 706 */ 707 public function getIntegerTypeDeclarationSQL(array $column) 708 { 709 $column['integer_type'] = 'INT'; 710 711 return $this->_getCommonIntegerTypeDeclarationSQL($column); 712 } 713 714 /** 715 * {@inheritdoc} 716 */ 717 public function getListDatabasesSQL() 718 { 719 return 'SELECT db_name(number) AS name FROM sa_db_list()'; 720 } 721 722 /** 723 * {@inheritdoc} 724 */ 725 public function getListTableColumnsSQL($table, $database = null) 726 { 727 $user = 'USER_NAME()'; 728 729 if (strpos($table, '.') !== false) { 730 [$user, $table] = explode('.', $table); 731 $user = $this->quoteStringLiteral($user); 732 } 733 734 return sprintf( 735 <<<'SQL' 736SELECT col.column_name, 737 COALESCE(def.user_type_name, def.domain_name) AS 'type', 738 def.declared_width AS 'length', 739 def.scale, 740 CHARINDEX('unsigned', def.domain_name) AS 'unsigned', 741 IF col.nulls = 'Y' THEN 0 ELSE 1 ENDIF AS 'notnull', 742 col."default", 743 def.is_autoincrement AS 'autoincrement', 744 rem.remarks AS 'comment' 745FROM sa_describe_query('SELECT * FROM "%s"') AS def 746JOIN SYS.SYSTABCOL AS col 747ON col.table_id = def.base_table_id AND col.column_id = def.base_column_id 748LEFT JOIN SYS.SYSREMARK AS rem 749ON col.object_id = rem.object_id 750WHERE def.base_owner_name = %s 751ORDER BY def.base_column_id ASC 752SQL 753 , 754 $table, 755 $user 756 ); 757 } 758 759 /** 760 * {@inheritdoc} 761 * 762 * @todo Where is this used? Which information should be retrieved? 763 */ 764 public function getListTableConstraintsSQL($table) 765 { 766 $user = ''; 767 768 if (strpos($table, '.') !== false) { 769 [$user, $table] = explode('.', $table); 770 $user = $this->quoteStringLiteral($user); 771 $table = $this->quoteStringLiteral($table); 772 } else { 773 $table = $this->quoteStringLiteral($table); 774 } 775 776 return sprintf( 777 <<<'SQL' 778SELECT con.* 779FROM SYS.SYSCONSTRAINT AS con 780JOIN SYS.SYSTAB AS tab ON con.table_object_id = tab.object_id 781WHERE tab.table_name = %s 782AND tab.creator = USER_ID(%s) 783SQL 784 , 785 $table, 786 $user 787 ); 788 } 789 790 /** 791 * {@inheritdoc} 792 */ 793 public function getListTableForeignKeysSQL($table) 794 { 795 $user = ''; 796 797 if (strpos($table, '.') !== false) { 798 [$user, $table] = explode('.', $table); 799 $user = $this->quoteStringLiteral($user); 800 $table = $this->quoteStringLiteral($table); 801 } else { 802 $table = $this->quoteStringLiteral($table); 803 } 804 805 return sprintf( 806 <<<'SQL' 807SELECT fcol.column_name AS local_column, 808 ptbl.table_name AS foreign_table, 809 pcol.column_name AS foreign_column, 810 idx.index_name, 811 IF fk.nulls = 'N' 812 THEN 1 813 ELSE NULL 814 ENDIF AS notnull, 815 CASE ut.referential_action 816 WHEN 'C' THEN 'CASCADE' 817 WHEN 'D' THEN 'SET DEFAULT' 818 WHEN 'N' THEN 'SET NULL' 819 WHEN 'R' THEN 'RESTRICT' 820 ELSE NULL 821 END AS on_update, 822 CASE dt.referential_action 823 WHEN 'C' THEN 'CASCADE' 824 WHEN 'D' THEN 'SET DEFAULT' 825 WHEN 'N' THEN 'SET NULL' 826 WHEN 'R' THEN 'RESTRICT' 827 ELSE NULL 828 END AS on_delete, 829 IF fk.check_on_commit = 'Y' 830 THEN 1 831 ELSE NULL 832 ENDIF AS check_on_commit, -- check_on_commit flag 833 IF ftbl.clustered_index_id = idx.index_id 834 THEN 1 835 ELSE NULL 836 ENDIF AS 'clustered', -- clustered flag 837 IF fk.match_type = 0 838 THEN NULL 839 ELSE fk.match_type 840 ENDIF AS 'match', -- match option 841 IF pidx.max_key_distance = 1 842 THEN 1 843 ELSE NULL 844 ENDIF AS for_olap_workload -- for_olap_workload flag 845FROM SYS.SYSFKEY AS fk 846JOIN SYS.SYSIDX AS idx 847ON fk.foreign_table_id = idx.table_id 848AND fk.foreign_index_id = idx.index_id 849JOIN SYS.SYSPHYSIDX pidx 850ON idx.table_id = pidx.table_id 851AND idx.phys_index_id = pidx.phys_index_id 852JOIN SYS.SYSTAB AS ptbl 853ON fk.primary_table_id = ptbl.table_id 854JOIN SYS.SYSTAB AS ftbl 855ON fk.foreign_table_id = ftbl.table_id 856JOIN SYS.SYSIDXCOL AS idxcol 857ON idx.table_id = idxcol.table_id 858AND idx.index_id = idxcol.index_id 859JOIN SYS.SYSTABCOL AS pcol 860ON ptbl.table_id = pcol.table_id 861AND idxcol.primary_column_id = pcol.column_id 862JOIN SYS.SYSTABCOL AS fcol 863ON ftbl.table_id = fcol.table_id 864AND idxcol.column_id = fcol.column_id 865LEFT JOIN SYS.SYSTRIGGER ut 866ON fk.foreign_table_id = ut.foreign_table_id 867AND fk.foreign_index_id = ut.foreign_key_id 868AND ut.event = 'C' 869LEFT JOIN SYS.SYSTRIGGER dt 870ON fk.foreign_table_id = dt.foreign_table_id 871AND fk.foreign_index_id = dt.foreign_key_id 872AND dt.event = 'D' 873WHERE ftbl.table_name = %s 874AND ftbl.creator = USER_ID(%s) 875ORDER BY fk.foreign_index_id ASC, idxcol.sequence ASC 876SQL 877 , 878 $table, 879 $user 880 ); 881 } 882 883 /** 884 * {@inheritdoc} 885 */ 886 public function getListTableIndexesSQL($table, $database = null) 887 { 888 $user = ''; 889 890 if (strpos($table, '.') !== false) { 891 [$user, $table] = explode('.', $table); 892 $user = $this->quoteStringLiteral($user); 893 $table = $this->quoteStringLiteral($table); 894 } else { 895 $table = $this->quoteStringLiteral($table); 896 } 897 898 return sprintf( 899 <<<'SQL' 900SELECT idx.index_name AS key_name, 901 IF idx.index_category = 1 902 THEN 1 903 ELSE 0 904 ENDIF AS 'primary', 905 col.column_name, 906 IF idx."unique" IN(1, 2, 5) 907 THEN 0 908 ELSE 1 909 ENDIF AS non_unique, 910 IF tbl.clustered_index_id = idx.index_id 911 THEN 1 912 ELSE NULL 913 ENDIF AS 'clustered', -- clustered flag 914 IF idx."unique" = 5 915 THEN 1 916 ELSE NULL 917 ENDIF AS with_nulls_not_distinct, -- with_nulls_not_distinct flag 918 IF pidx.max_key_distance = 1 919 THEN 1 920 ELSE NULL 921 ENDIF AS for_olap_workload -- for_olap_workload flag 922FROM SYS.SYSIDX AS idx 923JOIN SYS.SYSPHYSIDX pidx 924ON idx.table_id = pidx.table_id 925AND idx.phys_index_id = pidx.phys_index_id 926JOIN SYS.SYSIDXCOL AS idxcol 927ON idx.table_id = idxcol.table_id AND idx.index_id = idxcol.index_id 928JOIN SYS.SYSTABCOL AS col 929ON idxcol.table_id = col.table_id AND idxcol.column_id = col.column_id 930JOIN SYS.SYSTAB AS tbl 931ON idx.table_id = tbl.table_id 932WHERE tbl.table_name = %s 933AND tbl.creator = USER_ID(%s) 934AND idx.index_category != 2 -- exclude indexes implicitly created by foreign key constraints 935ORDER BY idx.index_id ASC, idxcol.sequence ASC 936SQL 937 , 938 $table, 939 $user 940 ); 941 } 942 943 /** 944 * {@inheritdoc} 945 */ 946 public function getListTablesSQL() 947 { 948 return "SELECT tbl.table_name 949 FROM SYS.SYSTAB AS tbl 950 JOIN SYS.SYSUSER AS usr ON tbl.creator = usr.user_id 951 JOIN dbo.SYSOBJECTS AS obj ON tbl.object_id = obj.id 952 WHERE tbl.table_type IN(1, 3) -- 'BASE', 'GBL TEMP' 953 AND usr.user_name NOT IN('SYS', 'dbo', 'rs_systabgroup') -- exclude system users 954 AND obj.type = 'U' -- user created tables only 955 ORDER BY tbl.table_name ASC"; 956 } 957 958 /** 959 * {@inheritdoc} 960 * 961 * @todo Where is this used? Which information should be retrieved? 962 */ 963 public function getListUsersSQL() 964 { 965 return 'SELECT * FROM SYS.SYSUSER ORDER BY user_name ASC'; 966 } 967 968 /** 969 * {@inheritdoc} 970 */ 971 public function getListViewsSQL($database) 972 { 973 return "SELECT tbl.table_name, v.view_def 974 FROM SYS.SYSVIEW v 975 JOIN SYS.SYSTAB tbl ON v.view_object_id = tbl.object_id 976 JOIN SYS.SYSUSER usr ON tbl.creator = usr.user_id 977 JOIN dbo.SYSOBJECTS obj ON tbl.object_id = obj.id 978 WHERE usr.user_name NOT IN('SYS', 'dbo', 'rs_systabgroup') -- exclude system users 979 ORDER BY tbl.table_name ASC"; 980 } 981 982 /** 983 * {@inheritdoc} 984 */ 985 public function getLocateExpression($str, $substr, $startPos = false) 986 { 987 if ($startPos === false) { 988 return 'LOCATE(' . $str . ', ' . $substr . ')'; 989 } 990 991 return 'LOCATE(' . $str . ', ' . $substr . ', ' . $startPos . ')'; 992 } 993 994 /** 995 * {@inheritdoc} 996 */ 997 public function getMaxIdentifierLength() 998 { 999 return 128; 1000 } 1001 1002 /** 1003 * {@inheritdoc} 1004 */ 1005 public function getMd5Expression($column) 1006 { 1007 return 'HASH(' . $column . ", 'MD5')"; 1008 } 1009 1010 /** 1011 * {@inheritdoc} 1012 */ 1013 public function getName() 1014 { 1015 return 'sqlanywhere'; 1016 } 1017 1018 /** 1019 * Obtain DBMS specific SQL code portion needed to set a primary key 1020 * declaration to be used in statements like ALTER TABLE. 1021 * 1022 * @param Index $index Index definition 1023 * @param string $name Name of the primary key 1024 * 1025 * @return string DBMS specific SQL code portion needed to set a primary key 1026 * 1027 * @throws InvalidArgumentException If the given index is not a primary key. 1028 */ 1029 public function getPrimaryKeyDeclarationSQL(Index $index, $name = null) 1030 { 1031 if (! $index->isPrimary()) { 1032 throw new InvalidArgumentException( 1033 'Can only create primary key declarations with getPrimaryKeyDeclarationSQL()' 1034 ); 1035 } 1036 1037 return $this->getTableConstraintDeclarationSQL($index, $name); 1038 } 1039 1040 /** 1041 * {@inheritdoc} 1042 */ 1043 public function getSetTransactionIsolationSQL($level) 1044 { 1045 return 'SET TEMPORARY OPTION isolation_level = ' . $this->_getTransactionIsolationLevelSQL($level); 1046 } 1047 1048 /** 1049 * {@inheritdoc} 1050 */ 1051 public function getSmallIntTypeDeclarationSQL(array $column) 1052 { 1053 $column['integer_type'] = 'SMALLINT'; 1054 1055 return $this->_getCommonIntegerTypeDeclarationSQL($column); 1056 } 1057 1058 /** 1059 * Returns the SQL statement for starting an existing database. 1060 * 1061 * In SQL Anywhere you can start and stop databases on a 1062 * database server instance. 1063 * This is a required statement after having created a new database 1064 * as it has to be explicitly started to be usable. 1065 * SQL Anywhere does not automatically start a database after creation! 1066 * 1067 * @param string $database Name of the database to start. 1068 * 1069 * @return string 1070 */ 1071 public function getStartDatabaseSQL($database) 1072 { 1073 $database = new Identifier($database); 1074 1075 return "START DATABASE '" . $database->getName() . "' AUTOSTOP OFF"; 1076 } 1077 1078 /** 1079 * Returns the SQL statement for stopping a running database. 1080 * 1081 * In SQL Anywhere you can start and stop databases on a 1082 * database server instance. 1083 * This is a required statement before dropping an existing database 1084 * as it has to be explicitly stopped before it can be dropped. 1085 * 1086 * @param string $database Name of the database to stop. 1087 * 1088 * @return string 1089 */ 1090 public function getStopDatabaseSQL($database) 1091 { 1092 $database = new Identifier($database); 1093 1094 return 'STOP DATABASE "' . $database->getName() . '" UNCONDITIONALLY'; 1095 } 1096 1097 /** 1098 * {@inheritdoc} 1099 */ 1100 public function getSubstringExpression($string, $start, $length = null) 1101 { 1102 if ($length === null) { 1103 return 'SUBSTRING(' . $string . ', ' . $start . ')'; 1104 } 1105 1106 return 'SUBSTRING(' . $string . ', ' . $start . ', ' . $length . ')'; 1107 } 1108 1109 /** 1110 * {@inheritdoc} 1111 */ 1112 public function getTemporaryTableSQL() 1113 { 1114 return 'GLOBAL TEMPORARY'; 1115 } 1116 1117 /** 1118 * {@inheritdoc} 1119 */ 1120 public function getTimeFormatString() 1121 { 1122 return 'H:i:s.u'; 1123 } 1124 1125 /** 1126 * {@inheritdoc} 1127 */ 1128 public function getTimeTypeDeclarationSQL(array $column) 1129 { 1130 return 'TIME'; 1131 } 1132 1133 /** 1134 * {@inheritdoc} 1135 */ 1136 public function getTrimExpression($str, $mode = TrimMode::UNSPECIFIED, $char = false) 1137 { 1138 if (! $char) { 1139 switch ($mode) { 1140 case TrimMode::LEADING: 1141 return $this->getLtrimExpression($str); 1142 1143 case TrimMode::TRAILING: 1144 return $this->getRtrimExpression($str); 1145 1146 default: 1147 return 'TRIM(' . $str . ')'; 1148 } 1149 } 1150 1151 $pattern = "'%[^' + " . $char . " + ']%'"; 1152 1153 switch ($mode) { 1154 case TrimMode::LEADING: 1155 return 'SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))'; 1156 1157 case TrimMode::TRAILING: 1158 return 'REVERSE(SUBSTR(REVERSE(' . $str . '), PATINDEX(' . $pattern . ', REVERSE(' . $str . '))))'; 1159 1160 default: 1161 return 'REVERSE(SUBSTR(REVERSE(SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))), ' . 1162 'PATINDEX(' . $pattern . ', ' . 1163 'REVERSE(SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))))))'; 1164 } 1165 } 1166 1167 /** 1168 * {@inheritdoc} 1169 */ 1170 public function getTruncateTableSQL($tableName, $cascade = false) 1171 { 1172 $tableIdentifier = new Identifier($tableName); 1173 1174 return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this); 1175 } 1176 1177 /** 1178 * {@inheritdoc} 1179 */ 1180 public function getUniqueConstraintDeclarationSQL($name, Index $index) 1181 { 1182 if ($index->isPrimary()) { 1183 throw new InvalidArgumentException( 1184 'Cannot create primary key constraint declarations with getUniqueConstraintDeclarationSQL().' 1185 ); 1186 } 1187 1188 if (! $index->isUnique()) { 1189 throw new InvalidArgumentException( 1190 'Can only create unique constraint declarations, no common index declarations with ' . 1191 'getUniqueConstraintDeclarationSQL().' 1192 ); 1193 } 1194 1195 return $this->getTableConstraintDeclarationSQL($index, $name); 1196 } 1197 1198 /** 1199 * {@inheritdoc} 1200 */ 1201 public function getVarcharDefaultLength() 1202 { 1203 return 1; 1204 } 1205 1206 /** 1207 * {@inheritdoc} 1208 */ 1209 public function getVarcharMaxLength() 1210 { 1211 return 32767; 1212 } 1213 1214 /** 1215 * {@inheritdoc} 1216 */ 1217 public function hasNativeGuidType() 1218 { 1219 return true; 1220 } 1221 1222 /** 1223 * {@inheritdoc} 1224 */ 1225 public function prefersIdentityColumns() 1226 { 1227 return true; 1228 } 1229 1230 /** 1231 * {@inheritdoc} 1232 */ 1233 public function supportsCommentOnStatement() 1234 { 1235 return true; 1236 } 1237 1238 /** 1239 * {@inheritdoc} 1240 */ 1241 public function supportsIdentityColumns() 1242 { 1243 return true; 1244 } 1245 1246 /** 1247 * {@inheritdoc} 1248 */ 1249 protected function _getCommonIntegerTypeDeclarationSQL(array $column) 1250 { 1251 $unsigned = ! empty($column['unsigned']) ? 'UNSIGNED ' : ''; 1252 $autoincrement = ! empty($column['autoincrement']) ? ' IDENTITY' : ''; 1253 1254 return $unsigned . $column['integer_type'] . $autoincrement; 1255 } 1256 1257 /** 1258 * {@inheritdoc} 1259 */ 1260 protected function _getCreateTableSQL($name, array $columns, array $options = []) 1261 { 1262 $columnListSql = $this->getColumnDeclarationListSQL($columns); 1263 $indexSql = []; 1264 1265 if (! empty($options['uniqueConstraints'])) { 1266 foreach ((array) $options['uniqueConstraints'] as $name => $definition) { 1267 $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition); 1268 } 1269 } 1270 1271 if (! empty($options['indexes'])) { 1272 foreach ((array) $options['indexes'] as $index) { 1273 assert($index instanceof Index); 1274 $indexSql[] = $this->getCreateIndexSQL($index, $name); 1275 } 1276 } 1277 1278 if (! empty($options['primary'])) { 1279 $flags = ''; 1280 1281 if (isset($options['primary_index']) && $options['primary_index']->hasFlag('clustered')) { 1282 $flags = ' CLUSTERED '; 1283 } 1284 1285 $columnListSql .= ', PRIMARY KEY' . $flags 1286 . ' (' . implode(', ', array_unique(array_values((array) $options['primary']))) . ')'; 1287 } 1288 1289 if (! empty($options['foreignKeys'])) { 1290 foreach ((array) $options['foreignKeys'] as $definition) { 1291 $columnListSql .= ', ' . $this->getForeignKeyDeclarationSQL($definition); 1292 } 1293 } 1294 1295 $query = 'CREATE TABLE ' . $name . ' (' . $columnListSql; 1296 $check = $this->getCheckDeclarationSQL($columns); 1297 1298 if (! empty($check)) { 1299 $query .= ', ' . $check; 1300 } 1301 1302 $query .= ')'; 1303 1304 return array_merge([$query], $indexSql); 1305 } 1306 1307 /** 1308 * {@inheritdoc} 1309 */ 1310 protected function _getTransactionIsolationLevelSQL($level) 1311 { 1312 switch ($level) { 1313 case TransactionIsolationLevel::READ_UNCOMMITTED: 1314 return '0'; 1315 1316 case TransactionIsolationLevel::READ_COMMITTED: 1317 return '1'; 1318 1319 case TransactionIsolationLevel::REPEATABLE_READ: 1320 return '2'; 1321 1322 case TransactionIsolationLevel::SERIALIZABLE: 1323 return '3'; 1324 1325 default: 1326 throw new InvalidArgumentException('Invalid isolation level:' . $level); 1327 } 1328 } 1329 1330 /** 1331 * {@inheritdoc} 1332 */ 1333 protected function doModifyLimitQuery($query, $limit, $offset) 1334 { 1335 $limitOffsetClause = $this->getTopClauseSQL($limit, $offset); 1336 1337 if ($limitOffsetClause === '') { 1338 return $query; 1339 } 1340 1341 if (! preg_match('/^\s*(SELECT\s+(DISTINCT\s+)?)(.*)/i', $query, $matches)) { 1342 return $query; 1343 } 1344 1345 return $matches[1] . $limitOffsetClause . ' ' . $matches[3]; 1346 } 1347 1348 private function getTopClauseSQL(?int $limit, ?int $offset): string 1349 { 1350 if ($offset > 0) { 1351 return sprintf('TOP %s START AT %d', $limit ?? 'ALL', $offset + 1); 1352 } 1353 1354 return $limit === null ? '' : 'TOP ' . $limit; 1355 } 1356 1357 /** 1358 * Return the INDEX query section dealing with non-standard 1359 * SQL Anywhere options. 1360 * 1361 * @param Index $index Index definition 1362 * 1363 * @return string 1364 */ 1365 protected function getAdvancedIndexOptionsSQL(Index $index) 1366 { 1367 $sql = ''; 1368 1369 if (! $index->isPrimary() && $index->hasFlag('for_olap_workload')) { 1370 $sql .= ' FOR OLAP WORKLOAD'; 1371 } 1372 1373 return $sql; 1374 } 1375 1376 /** 1377 * {@inheritdoc} 1378 */ 1379 protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed) 1380 { 1381 return $fixed 1382 ? 'BINARY(' . ($length ?: $this->getBinaryDefaultLength()) . ')' 1383 : 'VARBINARY(' . ($length ?: $this->getBinaryDefaultLength()) . ')'; 1384 } 1385 1386 /** 1387 * Returns the SQL snippet for creating a table constraint. 1388 * 1389 * @param Constraint $constraint The table constraint to create the SQL snippet for. 1390 * @param string|null $name The table constraint name to use if any. 1391 * 1392 * @return string 1393 * 1394 * @throws InvalidArgumentException If the given table constraint type is not supported by this method. 1395 */ 1396 protected function getTableConstraintDeclarationSQL(Constraint $constraint, $name = null) 1397 { 1398 if ($constraint instanceof ForeignKeyConstraint) { 1399 return $this->getForeignKeyDeclarationSQL($constraint); 1400 } 1401 1402 if (! $constraint instanceof Index) { 1403 throw new InvalidArgumentException('Unsupported constraint type: ' . get_class($constraint)); 1404 } 1405 1406 if (! $constraint->isPrimary() && ! $constraint->isUnique()) { 1407 throw new InvalidArgumentException( 1408 'Can only create primary, unique or foreign key constraint declarations, no common index declarations' 1409 . ' with getTableConstraintDeclarationSQL().' 1410 ); 1411 } 1412 1413 $constraintColumns = $constraint->getQuotedColumns($this); 1414 1415 if (empty($constraintColumns)) { 1416 throw new InvalidArgumentException("Incomplete definition. 'columns' required."); 1417 } 1418 1419 $sql = ''; 1420 $flags = ''; 1421 1422 if (! empty($name)) { 1423 $name = new Identifier($name); 1424 $sql .= 'CONSTRAINT ' . $name->getQuotedName($this) . ' '; 1425 } 1426 1427 if ($constraint->hasFlag('clustered')) { 1428 $flags = 'CLUSTERED '; 1429 } 1430 1431 if ($constraint->isPrimary()) { 1432 return $sql . 'PRIMARY KEY ' . $flags 1433 . '(' . $this->getIndexFieldDeclarationListSQL($constraintColumns) . ')'; 1434 } 1435 1436 return $sql . 'UNIQUE ' . $flags . '(' . $this->getIndexFieldDeclarationListSQL($constraintColumns) . ')'; 1437 } 1438 1439 /** 1440 * {@inheritdoc} 1441 */ 1442 protected function getCreateIndexSQLFlags(Index $index) 1443 { 1444 $type = ''; 1445 if ($index->hasFlag('virtual')) { 1446 $type .= 'VIRTUAL '; 1447 } 1448 1449 if ($index->isUnique()) { 1450 $type .= 'UNIQUE '; 1451 } 1452 1453 if ($index->hasFlag('clustered')) { 1454 $type .= 'CLUSTERED '; 1455 } 1456 1457 return $type; 1458 } 1459 1460 /** 1461 * {@inheritdoc} 1462 */ 1463 protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName) 1464 { 1465 return ['ALTER INDEX ' . $oldIndexName . ' ON ' . $tableName . ' RENAME TO ' . $index->getQuotedName($this)]; 1466 } 1467 1468 /** 1469 * {@inheritdoc} 1470 */ 1471 protected function getReservedKeywordsClass() 1472 { 1473 return Keywords\SQLAnywhereKeywords::class; 1474 } 1475 1476 /** 1477 * {@inheritdoc} 1478 */ 1479 protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) 1480 { 1481 return $fixed 1482 ? ($length ? 'CHAR(' . $length . ')' : 'CHAR(' . $this->getVarcharDefaultLength() . ')') 1483 : ($length ? 'VARCHAR(' . $length . ')' : 'VARCHAR(' . $this->getVarcharDefaultLength() . ')'); 1484 } 1485 1486 /** 1487 * {@inheritdoc} 1488 */ 1489 protected function initializeDoctrineTypeMappings() 1490 { 1491 $this->doctrineTypeMapping = [ 1492 'char' => 'string', 1493 'long nvarchar' => 'text', 1494 'long varchar' => 'text', 1495 'nchar' => 'string', 1496 'ntext' => 'text', 1497 'nvarchar' => 'string', 1498 'text' => 'text', 1499 'uniqueidentifierstr' => 'guid', 1500 'varchar' => 'string', 1501 'xml' => 'text', 1502 'bigint' => 'bigint', 1503 'unsigned bigint' => 'bigint', 1504 'bit' => 'boolean', 1505 'decimal' => 'decimal', 1506 'double' => 'float', 1507 'float' => 'float', 1508 'int' => 'integer', 1509 'integer' => 'integer', 1510 'unsigned int' => 'integer', 1511 'numeric' => 'decimal', 1512 'smallint' => 'smallint', 1513 'unsigned smallint' => 'smallint', 1514 'tinyint' => 'smallint', 1515 'unsigned tinyint' => 'smallint', 1516 'money' => 'decimal', 1517 'smallmoney' => 'decimal', 1518 'long varbit' => 'text', 1519 'varbit' => 'string', 1520 'date' => 'date', 1521 'datetime' => 'datetime', 1522 'smalldatetime' => 'datetime', 1523 'time' => 'time', 1524 'timestamp' => 'datetime', 1525 'binary' => 'binary', 1526 'image' => 'blob', 1527 'long binary' => 'blob', 1528 'uniqueidentifier' => 'guid', 1529 'varbinary' => 'binary', 1530 ]; 1531 } 1532} 1533