1<?php 2 3namespace Doctrine\DBAL\Platforms; 4 5use Doctrine\DBAL\LockMode; 6use Doctrine\DBAL\Schema\Column; 7use Doctrine\DBAL\Schema\ColumnDiff; 8use Doctrine\DBAL\Schema\ForeignKeyConstraint; 9use Doctrine\DBAL\Schema\Identifier; 10use Doctrine\DBAL\Schema\Index; 11use Doctrine\DBAL\Schema\Table; 12use Doctrine\DBAL\Schema\TableDiff; 13use InvalidArgumentException; 14 15use function array_merge; 16use function array_unique; 17use function array_values; 18use function count; 19use function crc32; 20use function dechex; 21use function explode; 22use function func_get_args; 23use function implode; 24use function is_array; 25use function is_bool; 26use function is_numeric; 27use function is_string; 28use function preg_match; 29use function sprintf; 30use function str_replace; 31use function stripos; 32use function stristr; 33use function strlen; 34use function strpos; 35use function strtoupper; 36use function substr; 37use function substr_count; 38 39/** 40 * The SQLServerPlatform provides the behavior, features and SQL dialect of the 41 * Microsoft SQL Server database platform. 42 * 43 * @deprecated Use SQL Server 2012 or newer 44 */ 45class SQLServerPlatform extends AbstractPlatform 46{ 47 /** 48 * {@inheritdoc} 49 */ 50 public function getCurrentDateSQL() 51 { 52 return $this->getConvertExpression('date', 'GETDATE()'); 53 } 54 55 /** 56 * {@inheritdoc} 57 */ 58 public function getCurrentTimeSQL() 59 { 60 return $this->getConvertExpression('time', 'GETDATE()'); 61 } 62 63 /** 64 * Returns an expression that converts an expression of one data type to another. 65 * 66 * @param string $dataType The target native data type. Alias data types cannot be used. 67 * @param string $expression The SQL expression to convert. 68 * 69 * @return string 70 */ 71 private function getConvertExpression($dataType, $expression) 72 { 73 return sprintf('CONVERT(%s, %s)', $dataType, $expression); 74 } 75 76 /** 77 * {@inheritdoc} 78 */ 79 protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit) 80 { 81 $factorClause = ''; 82 83 if ($operator === '-') { 84 $factorClause = '-1 * '; 85 } 86 87 return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')'; 88 } 89 90 /** 91 * {@inheritDoc} 92 */ 93 public function getDateDiffExpression($date1, $date2) 94 { 95 return 'DATEDIFF(day, ' . $date2 . ',' . $date1 . ')'; 96 } 97 98 /** 99 * {@inheritDoc} 100 * 101 * Microsoft SQL Server prefers "autoincrement" identity columns 102 * since sequences can only be emulated with a table. 103 */ 104 public function prefersIdentityColumns() 105 { 106 return true; 107 } 108 109 /** 110 * {@inheritDoc} 111 * 112 * Microsoft SQL Server supports this through AUTO_INCREMENT columns. 113 */ 114 public function supportsIdentityColumns() 115 { 116 return true; 117 } 118 119 /** 120 * {@inheritDoc} 121 */ 122 public function supportsReleaseSavepoints() 123 { 124 return false; 125 } 126 127 /** 128 * {@inheritdoc} 129 */ 130 public function supportsSchemas() 131 { 132 return true; 133 } 134 135 /** 136 * {@inheritdoc} 137 */ 138 public function getDefaultSchemaName() 139 { 140 return 'dbo'; 141 } 142 143 /** 144 * {@inheritDoc} 145 */ 146 public function supportsColumnCollation() 147 { 148 return true; 149 } 150 151 /** 152 * {@inheritDoc} 153 */ 154 public function hasNativeGuidType() 155 { 156 return true; 157 } 158 159 /** 160 * {@inheritDoc} 161 */ 162 public function getCreateDatabaseSQL($name) 163 { 164 return 'CREATE DATABASE ' . $name; 165 } 166 167 /** 168 * {@inheritDoc} 169 */ 170 public function getDropDatabaseSQL($name) 171 { 172 return 'DROP DATABASE ' . $name; 173 } 174 175 /** 176 * {@inheritDoc} 177 */ 178 public function supportsCreateDropDatabase() 179 { 180 return true; 181 } 182 183 /** 184 * {@inheritDoc} 185 */ 186 public function getCreateSchemaSQL($schemaName) 187 { 188 return 'CREATE SCHEMA ' . $schemaName; 189 } 190 191 /** 192 * {@inheritDoc} 193 */ 194 public function getDropForeignKeySQL($foreignKey, $table) 195 { 196 if (! $foreignKey instanceof ForeignKeyConstraint) { 197 $foreignKey = new Identifier($foreignKey); 198 } 199 200 if (! $table instanceof Table) { 201 $table = new Identifier($table); 202 } 203 204 $foreignKey = $foreignKey->getQuotedName($this); 205 $table = $table->getQuotedName($this); 206 207 return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $foreignKey; 208 } 209 210 /** 211 * {@inheritDoc} 212 */ 213 public function getDropIndexSQL($index, $table = null) 214 { 215 if ($index instanceof Index) { 216 $index = $index->getQuotedName($this); 217 } elseif (! is_string($index)) { 218 throw new InvalidArgumentException( 219 __METHOD__ . '() expects $index parameter to be string or ' . Index::class . '.' 220 ); 221 } 222 223 if (! isset($table)) { 224 return 'DROP INDEX ' . $index; 225 } 226 227 if ($table instanceof Table) { 228 $table = $table->getQuotedName($this); 229 } 230 231 return sprintf( 232 " 233 IF EXISTS (SELECT * FROM sysobjects WHERE name = '%s') 234 ALTER TABLE %s DROP CONSTRAINT %s 235 ELSE 236 DROP INDEX %s ON %s 237 ", 238 $index, 239 $table, 240 $index, 241 $index, 242 $table 243 ); 244 } 245 246 /** 247 * {@inheritDoc} 248 */ 249 protected function _getCreateTableSQL($name, array $columns, array $options = []) 250 { 251 $defaultConstraintsSql = []; 252 $commentsSql = []; 253 254 $tableComment = $options['comment'] ?? null; 255 if ($tableComment !== null) { 256 $commentsSql[] = $this->getCommentOnTableSQL($name, $tableComment); 257 } 258 259 // @todo does other code breaks because of this? 260 // force primary keys to be not null 261 foreach ($columns as &$column) { 262 if (isset($column['primary']) && $column['primary']) { 263 $column['notnull'] = true; 264 } 265 266 // Build default constraints SQL statements. 267 if (isset($column['default'])) { 268 $defaultConstraintsSql[] = 'ALTER TABLE ' . $name . 269 ' ADD' . $this->getDefaultConstraintDeclarationSQL($name, $column); 270 } 271 272 if (empty($column['comment']) && ! is_numeric($column['comment'])) { 273 continue; 274 } 275 276 $commentsSql[] = $this->getCreateColumnCommentSQL($name, $column['name'], $column['comment']); 277 } 278 279 $columnListSql = $this->getColumnDeclarationListSQL($columns); 280 281 if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) { 282 foreach ($options['uniqueConstraints'] as $name => $definition) { 283 $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition); 284 } 285 } 286 287 if (isset($options['primary']) && ! empty($options['primary'])) { 288 $flags = ''; 289 if (isset($options['primary_index']) && $options['primary_index']->hasFlag('nonclustered')) { 290 $flags = ' NONCLUSTERED'; 291 } 292 293 $columnListSql .= ', PRIMARY KEY' . $flags 294 . ' (' . implode(', ', array_unique(array_values($options['primary']))) . ')'; 295 } 296 297 $query = 'CREATE TABLE ' . $name . ' (' . $columnListSql; 298 299 $check = $this->getCheckDeclarationSQL($columns); 300 if (! empty($check)) { 301 $query .= ', ' . $check; 302 } 303 304 $query .= ')'; 305 306 $sql = [$query]; 307 308 if (isset($options['indexes']) && ! empty($options['indexes'])) { 309 foreach ($options['indexes'] as $index) { 310 $sql[] = $this->getCreateIndexSQL($index, $name); 311 } 312 } 313 314 if (isset($options['foreignKeys'])) { 315 foreach ((array) $options['foreignKeys'] as $definition) { 316 $sql[] = $this->getCreateForeignKeySQL($definition, $name); 317 } 318 } 319 320 return array_merge($sql, $commentsSql, $defaultConstraintsSql); 321 } 322 323 /** 324 * {@inheritDoc} 325 */ 326 public function getCreatePrimaryKeySQL(Index $index, $table) 327 { 328 if ($table instanceof Table) { 329 $identifier = $table->getQuotedName($this); 330 } else { 331 $identifier = $table; 332 } 333 334 $sql = 'ALTER TABLE ' . $identifier . ' ADD PRIMARY KEY'; 335 336 if ($index->hasFlag('nonclustered')) { 337 $sql .= ' NONCLUSTERED'; 338 } 339 340 return $sql . ' (' . $this->getIndexFieldDeclarationListSQL($index) . ')'; 341 } 342 343 /** 344 * Returns the SQL statement for creating a column comment. 345 * 346 * SQL Server does not support native column comments, 347 * therefore the extended properties functionality is used 348 * as a workaround to store them. 349 * The property name used to store column comments is "MS_Description" 350 * which provides compatibility with SQL Server Management Studio, 351 * as column comments are stored in the same property there when 352 * specifying a column's "Description" attribute. 353 * 354 * @param string $tableName The quoted table name to which the column belongs. 355 * @param string $columnName The quoted column name to create the comment for. 356 * @param string|null $comment The column's comment. 357 * 358 * @return string 359 */ 360 protected function getCreateColumnCommentSQL($tableName, $columnName, $comment) 361 { 362 if (strpos($tableName, '.') !== false) { 363 [$schemaSQL, $tableSQL] = explode('.', $tableName); 364 $schemaSQL = $this->quoteStringLiteral($schemaSQL); 365 $tableSQL = $this->quoteStringLiteral($tableSQL); 366 } else { 367 $schemaSQL = "'dbo'"; 368 $tableSQL = $this->quoteStringLiteral($tableName); 369 } 370 371 return $this->getAddExtendedPropertySQL( 372 'MS_Description', 373 $comment, 374 'SCHEMA', 375 $schemaSQL, 376 'TABLE', 377 $tableSQL, 378 'COLUMN', 379 $columnName 380 ); 381 } 382 383 /** 384 * Returns the SQL snippet for declaring a default constraint. 385 * 386 * @param string $table Name of the table to return the default constraint declaration for. 387 * @param mixed[] $column Column definition. 388 * 389 * @return string 390 * 391 * @throws InvalidArgumentException 392 */ 393 public function getDefaultConstraintDeclarationSQL($table, array $column) 394 { 395 if (! isset($column['default'])) { 396 throw new InvalidArgumentException("Incomplete column definition. 'default' required."); 397 } 398 399 $columnName = new Identifier($column['name']); 400 401 return ' CONSTRAINT ' . 402 $this->generateDefaultConstraintName($table, $column['name']) . 403 $this->getDefaultValueDeclarationSQL($column) . 404 ' FOR ' . $columnName->getQuotedName($this); 405 } 406 407 /** 408 * {@inheritDoc} 409 */ 410 public function getUniqueConstraintDeclarationSQL($name, Index $index) 411 { 412 $constraint = parent::getUniqueConstraintDeclarationSQL($name, $index); 413 414 $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index); 415 416 return $constraint; 417 } 418 419 /** 420 * {@inheritDoc} 421 */ 422 public function getCreateIndexSQL(Index $index, $table) 423 { 424 $constraint = parent::getCreateIndexSQL($index, $table); 425 426 if ($index->isUnique() && ! $index->isPrimary()) { 427 $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index); 428 } 429 430 return $constraint; 431 } 432 433 /** 434 * {@inheritDoc} 435 */ 436 protected function getCreateIndexSQLFlags(Index $index) 437 { 438 $type = ''; 439 if ($index->isUnique()) { 440 $type .= 'UNIQUE '; 441 } 442 443 if ($index->hasFlag('clustered')) { 444 $type .= 'CLUSTERED '; 445 } elseif ($index->hasFlag('nonclustered')) { 446 $type .= 'NONCLUSTERED '; 447 } 448 449 return $type; 450 } 451 452 /** 453 * Extend unique key constraint with required filters 454 * 455 * @param string $sql 456 * 457 * @return string 458 */ 459 private function _appendUniqueConstraintDefinition($sql, Index $index) 460 { 461 $fields = []; 462 463 foreach ($index->getQuotedColumns($this) as $field) { 464 $fields[] = $field . ' IS NOT NULL'; 465 } 466 467 return $sql . ' WHERE ' . implode(' AND ', $fields); 468 } 469 470 /** 471 * {@inheritDoc} 472 */ 473 public function getAlterTableSQL(TableDiff $diff) 474 { 475 $queryParts = []; 476 $sql = []; 477 $columnSql = []; 478 $commentsSql = []; 479 480 foreach ($diff->addedColumns as $column) { 481 if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { 482 continue; 483 } 484 485 $columnDef = $column->toArray(); 486 $addColumnSql = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); 487 if (isset($columnDef['default'])) { 488 $addColumnSql .= ' CONSTRAINT ' . 489 $this->generateDefaultConstraintName($diff->name, $column->getQuotedName($this)) . 490 $this->getDefaultValueDeclarationSQL($columnDef); 491 } 492 493 $queryParts[] = $addColumnSql; 494 495 $comment = $this->getColumnComment($column); 496 497 if (empty($comment) && ! is_numeric($comment)) { 498 continue; 499 } 500 501 $commentsSql[] = $this->getCreateColumnCommentSQL( 502 $diff->name, 503 $column->getQuotedName($this), 504 $comment 505 ); 506 } 507 508 foreach ($diff->removedColumns as $column) { 509 if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { 510 continue; 511 } 512 513 $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this); 514 } 515 516 foreach ($diff->changedColumns as $columnDiff) { 517 if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { 518 continue; 519 } 520 521 $column = $columnDiff->column; 522 $comment = $this->getColumnComment($column); 523 $hasComment = ! empty($comment) || is_numeric($comment); 524 525 if ($columnDiff->fromColumn instanceof Column) { 526 $fromComment = $this->getColumnComment($columnDiff->fromColumn); 527 $hasFromComment = ! empty($fromComment) || is_numeric($fromComment); 528 529 if ($hasFromComment && $hasComment && $fromComment !== $comment) { 530 $commentsSql[] = $this->getAlterColumnCommentSQL( 531 $diff->name, 532 $column->getQuotedName($this), 533 $comment 534 ); 535 } elseif ($hasFromComment && ! $hasComment) { 536 $commentsSql[] = $this->getDropColumnCommentSQL($diff->name, $column->getQuotedName($this)); 537 } elseif (! $hasFromComment && $hasComment) { 538 $commentsSql[] = $this->getCreateColumnCommentSQL( 539 $diff->name, 540 $column->getQuotedName($this), 541 $comment 542 ); 543 } 544 } 545 546 // Do not add query part if only comment has changed. 547 if ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1) { 548 continue; 549 } 550 551 $requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff); 552 553 if ($requireDropDefaultConstraint) { 554 $queryParts[] = $this->getAlterTableDropDefaultConstraintClause( 555 $diff->name, 556 $columnDiff->oldColumnName 557 ); 558 } 559 560 $columnDef = $column->toArray(); 561 562 $queryParts[] = 'ALTER COLUMN ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); 563 564 if ( 565 ! isset($columnDef['default']) 566 || (! $requireDropDefaultConstraint && ! $columnDiff->hasChanged('default')) 567 ) { 568 continue; 569 } 570 571 $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); 572 } 573 574 foreach ($diff->renamedColumns as $oldColumnName => $column) { 575 if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { 576 continue; 577 } 578 579 $oldColumnName = new Identifier($oldColumnName); 580 581 $sql[] = "sp_rename '" . 582 $diff->getName($this)->getQuotedName($this) . '.' . $oldColumnName->getQuotedName($this) . 583 "', '" . $column->getQuotedName($this) . "', 'COLUMN'"; 584 585 // Recreate default constraint with new column name if necessary (for future reference). 586 if ($column->getDefault() === null) { 587 continue; 588 } 589 590 $queryParts[] = $this->getAlterTableDropDefaultConstraintClause( 591 $diff->name, 592 $oldColumnName->getQuotedName($this) 593 ); 594 $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); 595 } 596 597 $tableSql = []; 598 599 if ($this->onSchemaAlterTable($diff, $tableSql)) { 600 return array_merge($tableSql, $columnSql); 601 } 602 603 foreach ($queryParts as $query) { 604 $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; 605 } 606 607 $sql = array_merge($sql, $commentsSql); 608 609 $newName = $diff->getNewName(); 610 611 if ($newName !== false) { 612 $sql[] = "sp_rename '" . $diff->getName($this)->getQuotedName($this) . "', '" . $newName->getName() . "'"; 613 614 /** 615 * Rename table's default constraints names 616 * to match the new table name. 617 * This is necessary to ensure that the default 618 * constraints can be referenced in future table 619 * alterations as the table name is encoded in 620 * default constraints' names. 621 */ 622 $sql[] = "DECLARE @sql NVARCHAR(MAX) = N''; " . 623 "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " . 624 "+ REPLACE(dc.name, '" . $this->generateIdentifierName($diff->name) . "', " . 625 "'" . $this->generateIdentifierName($newName->getName()) . "') + ''', ''OBJECT'';' " . 626 'FROM sys.default_constraints dc ' . 627 'JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id ' . 628 "WHERE tbl.name = '" . $newName->getName() . "';" . 629 'EXEC sp_executesql @sql'; 630 } 631 632 $sql = array_merge( 633 $this->getPreAlterTableIndexForeignKeySQL($diff), 634 $sql, 635 $this->getPostAlterTableIndexForeignKeySQL($diff) 636 ); 637 638 return array_merge($sql, $tableSql, $columnSql); 639 } 640 641 /** 642 * Returns the SQL clause for adding a default constraint in an ALTER TABLE statement. 643 * 644 * @param string $tableName The name of the table to generate the clause for. 645 * @param Column $column The column to generate the clause for. 646 * 647 * @return string 648 */ 649 private function getAlterTableAddDefaultConstraintClause($tableName, Column $column) 650 { 651 $columnDef = $column->toArray(); 652 $columnDef['name'] = $column->getQuotedName($this); 653 654 return 'ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $columnDef); 655 } 656 657 /** 658 * Returns the SQL clause for dropping an existing default constraint in an ALTER TABLE statement. 659 * 660 * @param string $tableName The name of the table to generate the clause for. 661 * @param string $columnName The name of the column to generate the clause for. 662 * 663 * @return string 664 */ 665 private function getAlterTableDropDefaultConstraintClause($tableName, $columnName) 666 { 667 return 'DROP CONSTRAINT ' . $this->generateDefaultConstraintName($tableName, $columnName); 668 } 669 670 /** 671 * Checks whether a column alteration requires dropping its default constraint first. 672 * 673 * Different to other database vendors SQL Server implements column default values 674 * as constraints and therefore changes in a column's default value as well as changes 675 * in a column's type require dropping the default constraint first before being to 676 * alter the particular column to the new definition. 677 * 678 * @param ColumnDiff $columnDiff The column diff to evaluate. 679 * 680 * @return bool True if the column alteration requires dropping its default constraint first, false otherwise. 681 */ 682 private function alterColumnRequiresDropDefaultConstraint(ColumnDiff $columnDiff) 683 { 684 // We can only decide whether to drop an existing default constraint 685 // if we know the original default value. 686 if (! $columnDiff->fromColumn instanceof Column) { 687 return false; 688 } 689 690 // We only need to drop an existing default constraint if we know the 691 // column was defined with a default value before. 692 if ($columnDiff->fromColumn->getDefault() === null) { 693 return false; 694 } 695 696 // We need to drop an existing default constraint if the column was 697 // defined with a default value before and it has changed. 698 if ($columnDiff->hasChanged('default')) { 699 return true; 700 } 701 702 // We need to drop an existing default constraint if the column was 703 // defined with a default value before and the native column type has changed. 704 return $columnDiff->hasChanged('type') || $columnDiff->hasChanged('fixed'); 705 } 706 707 /** 708 * Returns the SQL statement for altering a column comment. 709 * 710 * SQL Server does not support native column comments, 711 * therefore the extended properties functionality is used 712 * as a workaround to store them. 713 * The property name used to store column comments is "MS_Description" 714 * which provides compatibility with SQL Server Management Studio, 715 * as column comments are stored in the same property there when 716 * specifying a column's "Description" attribute. 717 * 718 * @param string $tableName The quoted table name to which the column belongs. 719 * @param string $columnName The quoted column name to alter the comment for. 720 * @param string|null $comment The column's comment. 721 * 722 * @return string 723 */ 724 protected function getAlterColumnCommentSQL($tableName, $columnName, $comment) 725 { 726 if (strpos($tableName, '.') !== false) { 727 [$schemaSQL, $tableSQL] = explode('.', $tableName); 728 $schemaSQL = $this->quoteStringLiteral($schemaSQL); 729 $tableSQL = $this->quoteStringLiteral($tableSQL); 730 } else { 731 $schemaSQL = "'dbo'"; 732 $tableSQL = $this->quoteStringLiteral($tableName); 733 } 734 735 return $this->getUpdateExtendedPropertySQL( 736 'MS_Description', 737 $comment, 738 'SCHEMA', 739 $schemaSQL, 740 'TABLE', 741 $tableSQL, 742 'COLUMN', 743 $columnName 744 ); 745 } 746 747 /** 748 * Returns the SQL statement for dropping a column comment. 749 * 750 * SQL Server does not support native column comments, 751 * therefore the extended properties functionality is used 752 * as a workaround to store them. 753 * The property name used to store column comments is "MS_Description" 754 * which provides compatibility with SQL Server Management Studio, 755 * as column comments are stored in the same property there when 756 * specifying a column's "Description" attribute. 757 * 758 * @param string $tableName The quoted table name to which the column belongs. 759 * @param string $columnName The quoted column name to drop the comment for. 760 * 761 * @return string 762 */ 763 protected function getDropColumnCommentSQL($tableName, $columnName) 764 { 765 if (strpos($tableName, '.') !== false) { 766 [$schemaSQL, $tableSQL] = explode('.', $tableName); 767 $schemaSQL = $this->quoteStringLiteral($schemaSQL); 768 $tableSQL = $this->quoteStringLiteral($tableSQL); 769 } else { 770 $schemaSQL = "'dbo'"; 771 $tableSQL = $this->quoteStringLiteral($tableName); 772 } 773 774 return $this->getDropExtendedPropertySQL( 775 'MS_Description', 776 'SCHEMA', 777 $schemaSQL, 778 'TABLE', 779 $tableSQL, 780 'COLUMN', 781 $columnName 782 ); 783 } 784 785 /** 786 * {@inheritdoc} 787 */ 788 protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName) 789 { 790 return [sprintf( 791 "EXEC sp_rename N'%s.%s', N'%s', N'INDEX'", 792 $tableName, 793 $oldIndexName, 794 $index->getQuotedName($this) 795 ), 796 ]; 797 } 798 799 /** 800 * Returns the SQL statement for adding an extended property to a database object. 801 * 802 * @link http://msdn.microsoft.com/en-us/library/ms180047%28v=sql.90%29.aspx 803 * 804 * @param string $name The name of the property to add. 805 * @param string|null $value The value of the property to add. 806 * @param string|null $level0Type The type of the object at level 0 the property belongs to. 807 * @param string|null $level0Name The name of the object at level 0 the property belongs to. 808 * @param string|null $level1Type The type of the object at level 1 the property belongs to. 809 * @param string|null $level1Name The name of the object at level 1 the property belongs to. 810 * @param string|null $level2Type The type of the object at level 2 the property belongs to. 811 * @param string|null $level2Name The name of the object at level 2 the property belongs to. 812 * 813 * @return string 814 */ 815 public function getAddExtendedPropertySQL( 816 $name, 817 $value = null, 818 $level0Type = null, 819 $level0Name = null, 820 $level1Type = null, 821 $level1Name = null, 822 $level2Type = null, 823 $level2Name = null 824 ) { 825 return 'EXEC sp_addextendedproperty ' . 826 'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value) . ', ' . 827 'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' . 828 'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' . 829 'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name; 830 } 831 832 /** 833 * Returns the SQL statement for dropping an extended property from a database object. 834 * 835 * @link http://technet.microsoft.com/en-gb/library/ms178595%28v=sql.90%29.aspx 836 * 837 * @param string $name The name of the property to drop. 838 * @param string|null $level0Type The type of the object at level 0 the property belongs to. 839 * @param string|null $level0Name The name of the object at level 0 the property belongs to. 840 * @param string|null $level1Type The type of the object at level 1 the property belongs to. 841 * @param string|null $level1Name The name of the object at level 1 the property belongs to. 842 * @param string|null $level2Type The type of the object at level 2 the property belongs to. 843 * @param string|null $level2Name The name of the object at level 2 the property belongs to. 844 * 845 * @return string 846 */ 847 public function getDropExtendedPropertySQL( 848 $name, 849 $level0Type = null, 850 $level0Name = null, 851 $level1Type = null, 852 $level1Name = null, 853 $level2Type = null, 854 $level2Name = null 855 ) { 856 return 'EXEC sp_dropextendedproperty ' . 857 'N' . $this->quoteStringLiteral($name) . ', ' . 858 'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' . 859 'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' . 860 'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name; 861 } 862 863 /** 864 * Returns the SQL statement for updating an extended property of a database object. 865 * 866 * @link http://msdn.microsoft.com/en-us/library/ms186885%28v=sql.90%29.aspx 867 * 868 * @param string $name The name of the property to update. 869 * @param string|null $value The value of the property to update. 870 * @param string|null $level0Type The type of the object at level 0 the property belongs to. 871 * @param string|null $level0Name The name of the object at level 0 the property belongs to. 872 * @param string|null $level1Type The type of the object at level 1 the property belongs to. 873 * @param string|null $level1Name The name of the object at level 1 the property belongs to. 874 * @param string|null $level2Type The type of the object at level 2 the property belongs to. 875 * @param string|null $level2Name The name of the object at level 2 the property belongs to. 876 * 877 * @return string 878 */ 879 public function getUpdateExtendedPropertySQL( 880 $name, 881 $value = null, 882 $level0Type = null, 883 $level0Name = null, 884 $level1Type = null, 885 $level1Name = null, 886 $level2Type = null, 887 $level2Name = null 888 ) { 889 return 'EXEC sp_updateextendedproperty ' . 890 'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value) . ', ' . 891 'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' . 892 'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' . 893 'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name; 894 } 895 896 /** 897 * {@inheritDoc} 898 */ 899 public function getEmptyIdentityInsertSQL($quotedTableName, $quotedIdentifierColumnName) 900 { 901 return 'INSERT INTO ' . $quotedTableName . ' DEFAULT VALUES'; 902 } 903 904 /** 905 * {@inheritDoc} 906 */ 907 public function getListTablesSQL() 908 { 909 // "sysdiagrams" table must be ignored as it's internal SQL Server table for Database Diagrams 910 // Category 2 must be ignored as it is "MS SQL Server 'pseudo-system' object[s]" for replication 911 return "SELECT name FROM sysobjects WHERE type = 'U' AND name != 'sysdiagrams' AND category != 2 ORDER BY name"; 912 } 913 914 /** 915 * {@inheritDoc} 916 */ 917 public function getListTableColumnsSQL($table, $database = null) 918 { 919 return "SELECT col.name, 920 type.name AS type, 921 col.max_length AS length, 922 ~col.is_nullable AS notnull, 923 def.definition AS [default], 924 col.scale, 925 col.precision, 926 col.is_identity AS autoincrement, 927 col.collation_name AS collation, 928 CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type 929 FROM sys.columns AS col 930 JOIN sys.types AS type 931 ON col.user_type_id = type.user_type_id 932 JOIN sys.objects AS obj 933 ON col.object_id = obj.object_id 934 JOIN sys.schemas AS scm 935 ON obj.schema_id = scm.schema_id 936 LEFT JOIN sys.default_constraints def 937 ON col.default_object_id = def.object_id 938 AND col.object_id = def.parent_object_id 939 LEFT JOIN sys.extended_properties AS prop 940 ON obj.object_id = prop.major_id 941 AND col.column_id = prop.minor_id 942 AND prop.name = 'MS_Description' 943 WHERE obj.type = 'U' 944 AND " . $this->getTableWhereClause($table, 'scm.name', 'obj.name'); 945 } 946 947 /** 948 * @param string $table 949 * @param string|null $database 950 * 951 * @return string 952 */ 953 public function getListTableForeignKeysSQL($table, $database = null) 954 { 955 return 'SELECT f.name AS ForeignKey, 956 SCHEMA_NAME (f.SCHEMA_ID) AS SchemaName, 957 OBJECT_NAME (f.parent_object_id) AS TableName, 958 COL_NAME (fc.parent_object_id,fc.parent_column_id) AS ColumnName, 959 SCHEMA_NAME (o.SCHEMA_ID) ReferenceSchemaName, 960 OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, 961 COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName, 962 f.delete_referential_action_desc, 963 f.update_referential_action_desc 964 FROM sys.foreign_keys AS f 965 INNER JOIN sys.foreign_key_columns AS fc 966 INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id 967 ON f.OBJECT_ID = fc.constraint_object_id 968 WHERE ' . 969 $this->getTableWhereClause($table, 'SCHEMA_NAME (f.schema_id)', 'OBJECT_NAME (f.parent_object_id)'); 970 } 971 972 /** 973 * {@inheritDoc} 974 */ 975 public function getListTableIndexesSQL($table, $database = null) 976 { 977 return "SELECT idx.name AS key_name, 978 col.name AS column_name, 979 ~idx.is_unique AS non_unique, 980 idx.is_primary_key AS [primary], 981 CASE idx.type 982 WHEN '1' THEN 'clustered' 983 WHEN '2' THEN 'nonclustered' 984 ELSE NULL 985 END AS flags 986 FROM sys.tables AS tbl 987 JOIN sys.schemas AS scm ON tbl.schema_id = scm.schema_id 988 JOIN sys.indexes AS idx ON tbl.object_id = idx.object_id 989 JOIN sys.index_columns AS idxcol ON idx.object_id = idxcol.object_id AND idx.index_id = idxcol.index_id 990 JOIN sys.columns AS col ON idxcol.object_id = col.object_id AND idxcol.column_id = col.column_id 991 WHERE " . $this->getTableWhereClause($table, 'scm.name', 'tbl.name') . ' 992 ORDER BY idx.index_id ASC, idxcol.key_ordinal ASC'; 993 } 994 995 /** 996 * {@inheritDoc} 997 */ 998 public function getCreateViewSQL($name, $sql) 999 { 1000 return 'CREATE VIEW ' . $name . ' AS ' . $sql; 1001 } 1002 1003 /** 1004 * {@inheritDoc} 1005 */ 1006 public function getListViewsSQL($database) 1007 { 1008 return "SELECT name FROM sysobjects WHERE type = 'V' ORDER BY name"; 1009 } 1010 1011 /** 1012 * Returns the where clause to filter schema and table name in a query. 1013 * 1014 * @param string $table The full qualified name of the table. 1015 * @param string $schemaColumn The name of the column to compare the schema to in the where clause. 1016 * @param string $tableColumn The name of the column to compare the table to in the where clause. 1017 * 1018 * @return string 1019 */ 1020 private function getTableWhereClause($table, $schemaColumn, $tableColumn) 1021 { 1022 if (strpos($table, '.') !== false) { 1023 [$schema, $table] = explode('.', $table); 1024 $schema = $this->quoteStringLiteral($schema); 1025 $table = $this->quoteStringLiteral($table); 1026 } else { 1027 $schema = 'SCHEMA_NAME()'; 1028 $table = $this->quoteStringLiteral($table); 1029 } 1030 1031 return sprintf('(%s = %s AND %s = %s)', $tableColumn, $table, $schemaColumn, $schema); 1032 } 1033 1034 /** 1035 * {@inheritDoc} 1036 */ 1037 public function getDropViewSQL($name) 1038 { 1039 return 'DROP VIEW ' . $name; 1040 } 1041 1042 /** 1043 * {@inheritDoc} 1044 * 1045 * @deprecated Use application-generated UUIDs instead 1046 */ 1047 public function getGuidExpression() 1048 { 1049 return 'NEWID()'; 1050 } 1051 1052 /** 1053 * {@inheritDoc} 1054 */ 1055 public function getLocateExpression($str, $substr, $startPos = false) 1056 { 1057 if ($startPos === false) { 1058 return 'CHARINDEX(' . $substr . ', ' . $str . ')'; 1059 } 1060 1061 return 'CHARINDEX(' . $substr . ', ' . $str . ', ' . $startPos . ')'; 1062 } 1063 1064 /** 1065 * {@inheritDoc} 1066 */ 1067 public function getModExpression($expression1, $expression2) 1068 { 1069 return $expression1 . ' % ' . $expression2; 1070 } 1071 1072 /** 1073 * {@inheritDoc} 1074 */ 1075 public function getTrimExpression($str, $mode = TrimMode::UNSPECIFIED, $char = false) 1076 { 1077 if (! $char) { 1078 switch ($mode) { 1079 case TrimMode::LEADING: 1080 $trimFn = 'LTRIM'; 1081 break; 1082 1083 case TrimMode::TRAILING: 1084 $trimFn = 'RTRIM'; 1085 break; 1086 1087 default: 1088 return 'LTRIM(RTRIM(' . $str . '))'; 1089 } 1090 1091 return $trimFn . '(' . $str . ')'; 1092 } 1093 1094 $pattern = "'%[^' + " . $char . " + ']%'"; 1095 1096 if ($mode === TrimMode::LEADING) { 1097 return 'stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)'; 1098 } 1099 1100 if ($mode === TrimMode::TRAILING) { 1101 return 'reverse(stuff(reverse(' . $str . '), 1, ' 1102 . 'patindex(' . $pattern . ', reverse(' . $str . ')) - 1, null))'; 1103 } 1104 1105 return 'reverse(stuff(reverse(stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)), 1, ' 1106 . 'patindex(' . $pattern . ', reverse(stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str 1107 . ') - 1, null))) - 1, null))'; 1108 } 1109 1110 /** 1111 * {@inheritDoc} 1112 */ 1113 public function getConcatExpression() 1114 { 1115 $args = func_get_args(); 1116 1117 return '(' . implode(' + ', $args) . ')'; 1118 } 1119 1120 /** 1121 * {@inheritDoc} 1122 */ 1123 public function getListDatabasesSQL() 1124 { 1125 return 'SELECT * FROM sys.databases'; 1126 } 1127 1128 /** 1129 * {@inheritDoc} 1130 */ 1131 public function getListNamespacesSQL() 1132 { 1133 return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')"; 1134 } 1135 1136 /** 1137 * {@inheritDoc} 1138 */ 1139 public function getSubstringExpression($string, $start, $length = null) 1140 { 1141 if ($length !== null) { 1142 return 'SUBSTRING(' . $string . ', ' . $start . ', ' . $length . ')'; 1143 } 1144 1145 return 'SUBSTRING(' . $string . ', ' . $start . ', LEN(' . $string . ') - ' . $start . ' + 1)'; 1146 } 1147 1148 /** 1149 * {@inheritDoc} 1150 */ 1151 public function getLengthExpression($column) 1152 { 1153 return 'LEN(' . $column . ')'; 1154 } 1155 1156 /** 1157 * {@inheritDoc} 1158 */ 1159 public function getSetTransactionIsolationSQL($level) 1160 { 1161 return 'SET TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level); 1162 } 1163 1164 /** 1165 * {@inheritDoc} 1166 */ 1167 public function getIntegerTypeDeclarationSQL(array $column) 1168 { 1169 return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 1170 } 1171 1172 /** 1173 * {@inheritDoc} 1174 */ 1175 public function getBigIntTypeDeclarationSQL(array $column) 1176 { 1177 return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 1178 } 1179 1180 /** 1181 * {@inheritDoc} 1182 */ 1183 public function getSmallIntTypeDeclarationSQL(array $column) 1184 { 1185 return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($column); 1186 } 1187 1188 /** 1189 * {@inheritDoc} 1190 */ 1191 public function getGuidTypeDeclarationSQL(array $column) 1192 { 1193 return 'UNIQUEIDENTIFIER'; 1194 } 1195 1196 /** 1197 * {@inheritDoc} 1198 */ 1199 public function getAsciiStringTypeDeclarationSQL(array $column): string 1200 { 1201 $length = $column['length'] ?? null; 1202 1203 if (! isset($column['fixed'])) { 1204 return sprintf('VARCHAR(%d)', $length ?? 255); 1205 } 1206 1207 return sprintf('CHAR(%d)', $length ?? 255); 1208 } 1209 1210 /** 1211 * {@inheritDoc} 1212 */ 1213 protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) 1214 { 1215 return $fixed 1216 ? ($length ? 'NCHAR(' . $length . ')' : 'CHAR(255)') 1217 : ($length ? 'NVARCHAR(' . $length . ')' : 'NVARCHAR(255)'); 1218 } 1219 1220 /** 1221 * {@inheritdoc} 1222 */ 1223 protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed) 1224 { 1225 return $fixed ? 'BINARY(' . ($length ?: 255) . ')' : 'VARBINARY(' . ($length ?: 255) . ')'; 1226 } 1227 1228 /** 1229 * {@inheritdoc} 1230 */ 1231 public function getBinaryMaxLength() 1232 { 1233 return 8000; 1234 } 1235 1236 /** 1237 * {@inheritDoc} 1238 */ 1239 public function getClobTypeDeclarationSQL(array $column) 1240 { 1241 return 'VARCHAR(MAX)'; 1242 } 1243 1244 /** 1245 * {@inheritDoc} 1246 */ 1247 protected function _getCommonIntegerTypeDeclarationSQL(array $column) 1248 { 1249 return ! empty($column['autoincrement']) ? ' IDENTITY' : ''; 1250 } 1251 1252 /** 1253 * {@inheritDoc} 1254 */ 1255 public function getDateTimeTypeDeclarationSQL(array $column) 1256 { 1257 return 'DATETIME'; 1258 } 1259 1260 /** 1261 * {@inheritDoc} 1262 */ 1263 public function getDateTypeDeclarationSQL(array $column) 1264 { 1265 return 'DATETIME'; 1266 } 1267 1268 /** 1269 * {@inheritDoc} 1270 */ 1271 public function getTimeTypeDeclarationSQL(array $column) 1272 { 1273 return 'DATETIME'; 1274 } 1275 1276 /** 1277 * {@inheritDoc} 1278 */ 1279 public function getBooleanTypeDeclarationSQL(array $column) 1280 { 1281 return 'BIT'; 1282 } 1283 1284 /** 1285 * {@inheritDoc} 1286 */ 1287 protected function doModifyLimitQuery($query, $limit, $offset = null) 1288 { 1289 $where = []; 1290 1291 if ($offset > 0) { 1292 $where[] = sprintf('doctrine_rownum >= %d', $offset + 1); 1293 } 1294 1295 if ($limit !== null) { 1296 $where[] = sprintf('doctrine_rownum <= %d', $offset + $limit); 1297 $top = sprintf('TOP %d', $offset + $limit); 1298 } else { 1299 $top = 'TOP 9223372036854775807'; 1300 } 1301 1302 if (empty($where)) { 1303 return $query; 1304 } 1305 1306 // We'll find a SELECT or SELECT distinct and prepend TOP n to it 1307 // Even if the TOP n is very large, the use of a CTE will 1308 // allow the SQL Server query planner to optimize it so it doesn't 1309 // actually scan the entire range covered by the TOP clause. 1310 if (! preg_match('/^(\s*SELECT\s+(?:DISTINCT\s+)?)(.*)$/is', $query, $matches)) { 1311 return $query; 1312 } 1313 1314 $query = $matches[1] . $top . ' ' . $matches[2]; 1315 1316 if (stristr($query, 'ORDER BY')) { 1317 // Inner order by is not valid in SQL Server for our purposes 1318 // unless it's in a TOP N subquery. 1319 $query = $this->scrubInnerOrderBy($query); 1320 } 1321 1322 // Build a new limited query around the original, using a CTE 1323 return sprintf( 1324 'WITH dctrn_cte AS (%s) ' 1325 . 'SELECT * FROM (' 1326 . 'SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM dctrn_cte' 1327 . ') AS doctrine_tbl ' 1328 . 'WHERE %s ORDER BY doctrine_rownum ASC', 1329 $query, 1330 implode(' AND ', $where) 1331 ); 1332 } 1333 1334 /** 1335 * Remove ORDER BY clauses in subqueries - they're not supported by SQL Server. 1336 * Caveat: will leave ORDER BY in TOP N subqueries. 1337 * 1338 * @param string $query 1339 * 1340 * @return string 1341 */ 1342 private function scrubInnerOrderBy($query) 1343 { 1344 $count = substr_count(strtoupper($query), 'ORDER BY'); 1345 $offset = 0; 1346 1347 while ($count-- > 0) { 1348 $orderByPos = stripos($query, ' ORDER BY', $offset); 1349 if ($orderByPos === false) { 1350 break; 1351 } 1352 1353 $qLen = strlen($query); 1354 $parenCount = 0; 1355 $currentPosition = $orderByPos; 1356 1357 while ($parenCount >= 0 && $currentPosition < $qLen) { 1358 if ($query[$currentPosition] === '(') { 1359 $parenCount++; 1360 } elseif ($query[$currentPosition] === ')') { 1361 $parenCount--; 1362 } 1363 1364 $currentPosition++; 1365 } 1366 1367 if ($this->isOrderByInTopNSubquery($query, $orderByPos)) { 1368 // If the order by clause is in a TOP N subquery, do not remove 1369 // it and continue iteration from the current position. 1370 $offset = $currentPosition; 1371 continue; 1372 } 1373 1374 if ($currentPosition >= $qLen - 1) { 1375 continue; 1376 } 1377 1378 $query = substr($query, 0, $orderByPos) . substr($query, $currentPosition - 1); 1379 $offset = $orderByPos; 1380 } 1381 1382 return $query; 1383 } 1384 1385 /** 1386 * Check an ORDER BY clause to see if it is in a TOP N query or subquery. 1387 * 1388 * @param string $query The query 1389 * @param int $currentPosition Start position of ORDER BY clause 1390 * 1391 * @return bool true if ORDER BY is in a TOP N query, false otherwise 1392 */ 1393 private function isOrderByInTopNSubquery($query, $currentPosition) 1394 { 1395 // Grab query text on the same nesting level as the ORDER BY clause we're examining. 1396 $subQueryBuffer = ''; 1397 $parenCount = 0; 1398 1399 // If $parenCount goes negative, we've exited the subquery we're examining. 1400 // If $currentPosition goes negative, we've reached the beginning of the query. 1401 while ($parenCount >= 0 && $currentPosition >= 0) { 1402 if ($query[$currentPosition] === '(') { 1403 $parenCount--; 1404 } elseif ($query[$currentPosition] === ')') { 1405 $parenCount++; 1406 } 1407 1408 // Only yank query text on the same nesting level as the ORDER BY clause. 1409 $subQueryBuffer = ($parenCount === 0 ? $query[$currentPosition] : ' ') . $subQueryBuffer; 1410 1411 $currentPosition--; 1412 } 1413 1414 return (bool) preg_match('/SELECT\s+(DISTINCT\s+)?TOP\s/i', $subQueryBuffer); 1415 } 1416 1417 /** 1418 * {@inheritDoc} 1419 */ 1420 public function supportsLimitOffset() 1421 { 1422 return false; 1423 } 1424 1425 /** 1426 * {@inheritDoc} 1427 */ 1428 public function convertBooleans($item) 1429 { 1430 if (is_array($item)) { 1431 foreach ($item as $key => $value) { 1432 if (! is_bool($value) && ! is_numeric($value)) { 1433 continue; 1434 } 1435 1436 $item[$key] = $value ? 1 : 0; 1437 } 1438 } elseif (is_bool($item) || is_numeric($item)) { 1439 $item = $item ? 1 : 0; 1440 } 1441 1442 return $item; 1443 } 1444 1445 /** 1446 * {@inheritDoc} 1447 */ 1448 public function getCreateTemporaryTableSnippetSQL() 1449 { 1450 return 'CREATE TABLE'; 1451 } 1452 1453 /** 1454 * {@inheritDoc} 1455 */ 1456 public function getTemporaryTableName($tableName) 1457 { 1458 return '#' . $tableName; 1459 } 1460 1461 /** 1462 * {@inheritDoc} 1463 */ 1464 public function getDateTimeFormatString() 1465 { 1466 return 'Y-m-d H:i:s.000'; 1467 } 1468 1469 /** 1470 * {@inheritDoc} 1471 */ 1472 public function getDateFormatString() 1473 { 1474 return 'Y-m-d H:i:s.000'; 1475 } 1476 1477 /** 1478 * {@inheritDoc} 1479 */ 1480 public function getTimeFormatString() 1481 { 1482 return 'Y-m-d H:i:s.000'; 1483 } 1484 1485 /** 1486 * {@inheritDoc} 1487 */ 1488 public function getDateTimeTzFormatString() 1489 { 1490 return $this->getDateTimeFormatString(); 1491 } 1492 1493 /** 1494 * {@inheritDoc} 1495 */ 1496 public function getName() 1497 { 1498 return 'mssql'; 1499 } 1500 1501 /** 1502 * {@inheritDoc} 1503 */ 1504 protected function initializeDoctrineTypeMappings() 1505 { 1506 $this->doctrineTypeMapping = [ 1507 'bigint' => 'bigint', 1508 'numeric' => 'decimal', 1509 'bit' => 'boolean', 1510 'smallint' => 'smallint', 1511 'decimal' => 'decimal', 1512 'smallmoney' => 'integer', 1513 'int' => 'integer', 1514 'tinyint' => 'smallint', 1515 'money' => 'integer', 1516 'float' => 'float', 1517 'real' => 'float', 1518 'double' => 'float', 1519 'double precision' => 'float', 1520 'smalldatetime' => 'datetime', 1521 'datetime' => 'datetime', 1522 'char' => 'string', 1523 'varchar' => 'string', 1524 'text' => 'text', 1525 'nchar' => 'string', 1526 'nvarchar' => 'string', 1527 'ntext' => 'text', 1528 'binary' => 'binary', 1529 'varbinary' => 'binary', 1530 'image' => 'blob', 1531 'uniqueidentifier' => 'guid', 1532 ]; 1533 } 1534 1535 /** 1536 * {@inheritDoc} 1537 */ 1538 public function createSavePoint($savepoint) 1539 { 1540 return 'SAVE TRANSACTION ' . $savepoint; 1541 } 1542 1543 /** 1544 * {@inheritDoc} 1545 */ 1546 public function releaseSavePoint($savepoint) 1547 { 1548 return ''; 1549 } 1550 1551 /** 1552 * {@inheritDoc} 1553 */ 1554 public function rollbackSavePoint($savepoint) 1555 { 1556 return 'ROLLBACK TRANSACTION ' . $savepoint; 1557 } 1558 1559 /** 1560 * {@inheritdoc} 1561 */ 1562 public function getForeignKeyReferentialActionSQL($action) 1563 { 1564 // RESTRICT is not supported, therefore falling back to NO ACTION. 1565 if (strtoupper($action) === 'RESTRICT') { 1566 return 'NO ACTION'; 1567 } 1568 1569 return parent::getForeignKeyReferentialActionSQL($action); 1570 } 1571 1572 /** 1573 * {@inheritDoc} 1574 */ 1575 public function appendLockHint($fromClause, $lockMode) 1576 { 1577 switch (true) { 1578 case $lockMode === LockMode::NONE: 1579 return $fromClause; 1580 1581 case $lockMode === LockMode::PESSIMISTIC_READ: 1582 return $fromClause . ' WITH (HOLDLOCK, ROWLOCK)'; 1583 1584 case $lockMode === LockMode::PESSIMISTIC_WRITE: 1585 return $fromClause . ' WITH (UPDLOCK, ROWLOCK)'; 1586 1587 default: 1588 return $fromClause; 1589 } 1590 } 1591 1592 /** 1593 * {@inheritDoc} 1594 */ 1595 public function getForUpdateSQL() 1596 { 1597 return ' '; 1598 } 1599 1600 /** 1601 * {@inheritDoc} 1602 */ 1603 protected function getReservedKeywordsClass() 1604 { 1605 return Keywords\SQLServerKeywords::class; 1606 } 1607 1608 /** 1609 * {@inheritDoc} 1610 */ 1611 public function quoteSingleIdentifier($str) 1612 { 1613 return '[' . str_replace(']', ']]', $str) . ']'; 1614 } 1615 1616 /** 1617 * {@inheritDoc} 1618 */ 1619 public function getTruncateTableSQL($tableName, $cascade = false) 1620 { 1621 $tableIdentifier = new Identifier($tableName); 1622 1623 return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this); 1624 } 1625 1626 /** 1627 * {@inheritDoc} 1628 */ 1629 public function getBlobTypeDeclarationSQL(array $column) 1630 { 1631 return 'VARBINARY(MAX)'; 1632 } 1633 1634 /** 1635 * {@inheritdoc} 1636 * 1637 * Modifies column declaration order as it differs in Microsoft SQL Server. 1638 */ 1639 public function getColumnDeclarationSQL($name, array $column) 1640 { 1641 if (isset($column['columnDefinition'])) { 1642 $columnDef = $this->getCustomTypeDeclarationSQL($column); 1643 } else { 1644 $collation = isset($column['collation']) && $column['collation'] ? 1645 ' ' . $this->getColumnCollationDeclarationSQL($column['collation']) : ''; 1646 1647 $notnull = isset($column['notnull']) && $column['notnull'] ? ' NOT NULL' : ''; 1648 1649 $unique = isset($column['unique']) && $column['unique'] ? 1650 ' ' . $this->getUniqueFieldDeclarationSQL() : ''; 1651 1652 $check = isset($column['check']) && $column['check'] ? 1653 ' ' . $column['check'] : ''; 1654 1655 $typeDecl = $column['type']->getSQLDeclaration($column, $this); 1656 $columnDef = $typeDecl . $collation . $notnull . $unique . $check; 1657 } 1658 1659 return $name . ' ' . $columnDef; 1660 } 1661 1662 /** 1663 * Returns a unique default constraint name for a table and column. 1664 * 1665 * @param string $table Name of the table to generate the unique default constraint name for. 1666 * @param string $column Name of the column in the table to generate the unique default constraint name for. 1667 * 1668 * @return string 1669 */ 1670 private function generateDefaultConstraintName($table, $column) 1671 { 1672 return 'DF_' . $this->generateIdentifierName($table) . '_' . $this->generateIdentifierName($column); 1673 } 1674 1675 /** 1676 * Returns a hash value for a given identifier. 1677 * 1678 * @param string $identifier Identifier to generate a hash value for. 1679 * 1680 * @return string 1681 */ 1682 private function generateIdentifierName($identifier) 1683 { 1684 // Always generate name for unquoted identifiers to ensure consistency. 1685 $identifier = new Identifier($identifier); 1686 1687 return strtoupper(dechex(crc32($identifier->getName()))); 1688 } 1689 1690 protected function getCommentOnTableSQL(string $tableName, ?string $comment): string 1691 { 1692 return sprintf( 1693 " 1694 EXEC sys.sp_addextendedproperty @name=N'MS_Description', 1695 @value=N%s, @level0type=N'SCHEMA', @level0name=N'dbo', 1696 @level1type=N'TABLE', @level1name=N%s 1697 ", 1698 $this->quoteStringLiteral((string) $comment), 1699 $this->quoteStringLiteral($tableName) 1700 ); 1701 } 1702 1703 public function getListTableMetadataSQL(string $table): string 1704 { 1705 return sprintf( 1706 " 1707 SELECT 1708 p.value AS [table_comment] 1709 FROM 1710 sys.tables AS tbl 1711 INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1 1712 WHERE 1713 (tbl.name=N%s and SCHEMA_NAME(tbl.schema_id)=N'dbo' and p.name=N'MS_Description') 1714 ", 1715 $this->quoteStringLiteral($table) 1716 ); 1717 } 1718} 1719