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