SQLServer2012Platform.php 52.3 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Platforms;
4

5
use Doctrine\DBAL\LockMode;
6
use Doctrine\DBAL\Schema\Column;
7
use Doctrine\DBAL\Schema\ColumnDiff;
8
use Doctrine\DBAL\Schema\ForeignKeyConstraint;
9
use Doctrine\DBAL\Schema\Identifier;
10
use Doctrine\DBAL\Schema\Index;
11
use Doctrine\DBAL\Schema\Sequence;
12
use Doctrine\DBAL\Schema\Table;
13
use Doctrine\DBAL\Schema\TableDiff;
14
use InvalidArgumentException;
15

16 17 18 19 20 21
use function array_merge;
use function array_unique;
use function array_values;
use function count;
use function crc32;
use function dechex;
22
use function explode;
23
use function func_get_args;
24
use function implode;
25 26 27 28 29
use function is_array;
use function is_bool;
use function is_numeric;
use function is_string;
use function preg_match;
30
use function preg_match_all;
31
use function sprintf;
32
use function str_replace;
33
use function strpos;
34 35
use function strtoupper;
use function substr_count;
36

37
use const PREG_OFFSET_CAPTURE;
38 39

/**
40
 * Provides the behavior, features and SQL dialect of the Microsoft SQL Server 2012 database platform.
41
 */
42
class SQLServer2012Platform extends AbstractPlatform
43
{
44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
    /**
     * {@inheritdoc}
     */
    public function getCurrentDateSQL()
    {
        return $this->getConvertExpression('date', 'GETDATE()');
    }

    /**
     * {@inheritdoc}
     */
    public function getCurrentTimeSQL()
    {
        return $this->getConvertExpression('time', 'GETDATE()');
    }

    /**
     * Returns an expression that converts an expression of one data type to another.
     *
     * @param string $dataType   The target native data type. Alias data types cannot be used.
     * @param string $expression The SQL expression to convert.
     *
     * @return string
     */
    private function getConvertExpression($dataType, $expression)
    {
        return sprintf('CONVERT(%s, %s)', $dataType, $expression);
    }

73
    /**
74
     * {@inheritdoc}
75
     */
76
    protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
77
    {
78
        $factorClause = '';
79

80
        if ($operator === '-') {
81 82
            $factorClause = '-1 * ';
        }
83

84
        return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')';
85
    }
86

87 88 89
    /**
     * {@inheritDoc}
     */
90
    public function getDateDiffExpression($date1, $date2)
91
    {
92
        return 'DATEDIFF(day, ' . $date2 . ',' . $date1 . ')';
93
    }
94

95
    /**
96 97
     * {@inheritDoc}
     *
98 99
     * Microsoft SQL Server prefers "autoincrement" identity columns
     * since sequences can only be emulated with a table.
100
     */
101
    public function prefersIdentityColumns()
102
    {
103 104
        return true;
    }
105

106
    /**
107
     * {@inheritDoc}
108
     *
109
     * Microsoft SQL Server supports this through AUTO_INCREMENT columns.
110 111 112 113 114 115 116
     */
    public function supportsIdentityColumns()
    {
        return true;
    }

    /**
117
     * {@inheritDoc}
118
     */
119
    public function supportsReleaseSavepoints()
120 121 122
    {
        return false;
    }
123

124 125 126 127 128 129 130 131
    /**
     * {@inheritdoc}
     */
    public function supportsSchemas()
    {
        return true;
    }

132 133 134 135 136 137 138 139
    /**
     * {@inheritdoc}
     */
    public function getDefaultSchemaName()
    {
        return 'dbo';
    }

140 141 142 143 144 145 146 147
    /**
     * {@inheritDoc}
     */
    public function supportsColumnCollation()
    {
        return true;
    }

148
    public function supportsSequences(): bool
149 150 151 152
    {
        return true;
    }

153
    public function getAlterSequenceSQL(Sequence $sequence): string
154 155 156 157 158
    {
        return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) .
            ' INCREMENT BY ' . $sequence->getAllocationSize();
    }

159
    public function getCreateSequenceSQL(Sequence $sequence): string
160 161 162 163 164 165 166 167 168 169
    {
        return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) .
            ' START WITH ' . $sequence->getInitialValue() .
            ' INCREMENT BY ' . $sequence->getAllocationSize() .
            ' MINVALUE ' . $sequence->getInitialValue();
    }

    /**
     * {@inheritdoc}
     */
170
    public function getDropSequenceSQL($sequence): string
171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201
    {
        if ($sequence instanceof Sequence) {
            $sequence = $sequence->getQuotedName($this);
        }

        return 'DROP SEQUENCE ' . $sequence;
    }

    /**
     * {@inheritdoc}
     */
    public function getListSequencesSQL($database)
    {
        return 'SELECT seq.name,
                       CAST(
                           seq.increment AS VARCHAR(MAX)
                       ) AS increment, -- CAST avoids driver error for sql_variant type
                       CAST(
                           seq.start_value AS VARCHAR(MAX)
                       ) AS start_value -- CAST avoids driver error for sql_variant type
                FROM   sys.sequences AS seq';
    }

    /**
     * {@inheritdoc}
     */
    public function getSequenceNextValSQL($sequenceName)
    {
        return 'SELECT NEXT VALUE FOR ' . $sequenceName;
    }

202 203 204 205 206 207 208 209
    /**
     * {@inheritDoc}
     */
    public function hasNativeGuidType()
    {
        return true;
    }

210
    /**
211
     * {@inheritDoc}
212 213 214 215 216
     */
    public function getCreateDatabaseSQL($name)
    {
        return 'CREATE DATABASE ' . $name;
    }
217

218
    /**
219
     * {@inheritDoc}
220 221 222
     */
    public function getDropDatabaseSQL($name)
    {
223 224 225 226
        return 'DROP DATABASE ' . $name;
    }

    /**
227
     * {@inheritDoc}
228
     */
229
    public function supportsCreateDropDatabase()
230
    {
231
        return true;
232 233
    }

234 235 236 237 238 239 240 241
    /**
     * {@inheritDoc}
     */
    public function getCreateSchemaSQL($schemaName)
    {
        return 'CREATE SCHEMA ' . $schemaName;
    }

242
    /**
243
     * {@inheritDoc}
244 245 246
     */
    public function getDropForeignKeySQL($foreignKey, $table)
    {
247 248
        if (! $foreignKey instanceof ForeignKeyConstraint) {
            $foreignKey = new Identifier($foreignKey);
249 250
        }

251 252
        if (! $table instanceof Table) {
            $table = new Identifier($table);
253 254
        }

255
        $foreignKey = $foreignKey->getQuotedName($this);
256
        $table      = $table->getQuotedName($this);
257

258
        return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $foreignKey;
259
    }
260 261

    /**
262
     * {@inheritDoc}
263
     */
264
    public function getDropIndexSQL($index, $table = null)
265
    {
266
        if ($index instanceof Index) {
267
            $index = $index->getQuotedName($this);
268 269
        } elseif (! is_string($index)) {
            throw new InvalidArgumentException('AbstractPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
270 271
        }

272
        if (! isset($table)) {
273
            return 'DROP INDEX ' . $index;
274
        }
275

276 277
        if ($table instanceof Table) {
            $table = $table->getQuotedName($this);
278
        }
279

280 281 282 283 284 285 286 287 288 289 290 291 292 293
        return sprintf(
            <<<SQL
IF EXISTS (SELECT * FROM sysobjects WHERE name = '%s')
    ALTER TABLE %s DROP CONSTRAINT %s
ELSE
    DROP INDEX %s ON %s
SQL
            ,
            $index,
            $table,
            $index,
            $index,
            $table
        );
294
    }
295 296

    /**
297
     * {@inheritDoc}
298
     */
299
    protected function _getCreateTableSQL($tableName, array $columns, array $options = [])
300
    {
301 302
        $defaultConstraintsSql = [];
        $commentsSql           = [];
303

304 305 306 307 308
        $tableComment = $options['comment'] ?? null;
        if ($tableComment !== null) {
            $commentsSql[] = $this->getCommentOnTableSQL($tableName, $tableComment);
        }

309
        // @todo does other code breaks because of this?
310
        // force primary keys to be not null
311
        foreach ($columns as &$column) {
312
            if (! empty($column['primary'])) {
313 314
                $column['notnull'] = true;
            }
315

316
            // Build default constraints SQL statements.
317
            if (isset($column['default'])) {
318 319 320
                $defaultConstraintsSql[] = 'ALTER TABLE ' . $tableName .
                    ' ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $column);
            }
321

322 323
            if (empty($column['comment']) && ! is_numeric($column['comment'])) {
                continue;
324
            }
325 326

            $commentsSql[] = $this->getCreateColumnCommentSQL($tableName, $column['name'], $column['comment']);
327 328
        }

329
        $columnListSql = $this->getColumnDeclarationListSQL($columns);
330

331
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
332 333 334 335
            foreach ($options['uniqueConstraints'] as $name => $definition) {
                $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition);
            }
        }
336

337
        if (isset($options['primary']) && ! empty($options['primary'])) {
338 339 340 341
            $flags = '';
            if (isset($options['primary_index']) && $options['primary_index']->hasFlag('nonclustered')) {
                $flags = ' NONCLUSTERED';
            }
Grégoire Paris's avatar
Grégoire Paris committed
342

343
            $columnListSql .= ', PRIMARY KEY' . $flags . ' (' . implode(', ', array_unique(array_values($options['primary']))) . ')';
344 345 346 347 348
        }

        $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql;

        $check = $this->getCheckDeclarationSQL($columns);
349
        if (! empty($check)) {
350 351
            $query .= ', ' . $check;
        }
Grégoire Paris's avatar
Grégoire Paris committed
352

353 354
        $query .= ')';

355
        $sql = [$query];
356

357
        if (isset($options['indexes']) && ! empty($options['indexes'])) {
358
            foreach ($options['indexes'] as $index) {
359 360 361 362 363
                $sql[] = $this->getCreateIndexSQL($index, $tableName);
            }
        }

        if (isset($options['foreignKeys'])) {
364
            foreach ((array) $options['foreignKeys'] as $definition) {
365 366 367 368
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
            }
        }

369
        return array_merge($sql, $commentsSql, $defaultConstraintsSql);
370
    }
371

372
    /**
373
     * {@inheritDoc}
374 375 376
     */
    public function getCreatePrimaryKeySQL(Index $index, $table)
    {
Sergei Morozov's avatar
Sergei Morozov committed
377 378 379 380 381 382 383 384
        if ($table instanceof Table) {
            $identifier = $table->getQuotedName($this);
        } else {
            $identifier = $table;
        }

        $sql = 'ALTER TABLE ' . $identifier . ' ADD PRIMARY KEY';

385
        if ($index->hasFlag('nonclustered')) {
Sergei Morozov's avatar
Sergei Morozov committed
386
            $sql .= ' NONCLUSTERED';
387
        }
388

Sergei Morozov's avatar
Sergei Morozov committed
389
        return $sql . ' (' . $this->getIndexFieldDeclarationListSQL($index) . ')';
390 391
    }

392 393 394 395 396 397 398 399 400 401 402
    /**
     * Returns the SQL statement for creating a column comment.
     *
     * SQL Server does not support native column comments,
     * therefore the extended properties functionality is used
     * as a workaround to store them.
     * The property name used to store column comments is "MS_Description"
     * which provides compatibility with SQL Server Management Studio,
     * as column comments are stored in the same property there when
     * specifying a column's "Description" attribute.
     *
Sergei Morozov's avatar
Sergei Morozov committed
403 404 405
     * @param string      $tableName  The quoted table name to which the column belongs.
     * @param string      $columnName The quoted column name to create the comment for.
     * @param string|null $comment    The column's comment.
406 407 408 409 410
     *
     * @return string
     */
    protected function getCreateColumnCommentSQL($tableName, $columnName, $comment)
    {
411 412 413 414 415 416 417 418 419
        if (strpos($tableName, '.') !== false) {
            [$schemaSQL, $tableSQL] = explode('.', $tableName);
            $schemaSQL              = $this->quoteStringLiteral($schemaSQL);
            $tableSQL               = $this->quoteStringLiteral($tableSQL);
        } else {
            $schemaSQL = "'dbo'";
            $tableSQL  = $this->quoteStringLiteral($tableName);
        }

420 421 422 423
        return $this->getAddExtendedPropertySQL(
            'MS_Description',
            $comment,
            'SCHEMA',
424
            $schemaSQL,
425
            'TABLE',
426
            $tableSQL,
427 428 429 430 431
            'COLUMN',
            $columnName
        );
    }

432 433 434
    /**
     * Returns the SQL snippet for declaring a default constraint.
     *
435 436
     * @param string  $table  Name of the table to return the default constraint declaration for.
     * @param mixed[] $column Column definition.
437 438 439
     *
     * @return string
     *
440
     * @throws InvalidArgumentException
441 442 443
     */
    public function getDefaultConstraintDeclarationSQL($table, array $column)
    {
444 445
        if (! isset($column['default'])) {
            throw new InvalidArgumentException("Incomplete column definition. 'default' required.");
446 447
        }

448 449
        $columnName = new Identifier($column['name']);

450
        return ' CONSTRAINT ' .
451 452
            $this->generateDefaultConstraintName($table, $column['name']) .
            $this->getDefaultValueDeclarationSQL($column) .
453
            ' FOR ' . $columnName->getQuotedName($this);
454 455
    }

456
    /**
457
     * {@inheritDoc}
458
     */
459
    public function getUniqueConstraintDeclarationSQL($name, Index $index)
460 461
    {
        $constraint = parent::getUniqueConstraintDeclarationSQL($name, $index);
462 463 464 465 466 467 468

        $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index);

        return $constraint;
    }

    /**
469
     * {@inheritDoc}
470 471 472 473 474
     */
    public function getCreateIndexSQL(Index $index, $table)
    {
        $constraint = parent::getCreateIndexSQL($index, $table);

475
        if ($index->isUnique() && ! $index->isPrimary()) {
476 477 478 479 480 481
            $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index);
        }

        return $constraint;
    }

482
    /**
483
     * {@inheritDoc}
484 485 486 487 488 489 490 491 492 493
     */
    protected function getCreateIndexSQLFlags(Index $index)
    {
        $type = '';
        if ($index->isUnique()) {
            $type .= 'UNIQUE ';
        }

        if ($index->hasFlag('clustered')) {
            $type .= 'CLUSTERED ';
Steve Müller's avatar
Steve Müller committed
494
        } elseif ($index->hasFlag('nonclustered')) {
495 496 497 498 499 500
            $type .= 'NONCLUSTERED ';
        }

        return $type;
    }

501
    /**
502
     * Extend unique key constraint with required filters
503
     *
504
     * @param string $sql
505
     *
506 507 508 509
     * @return string
     */
    private function _appendUniqueConstraintDefinition($sql, Index $index)
    {
510
        $fields = [];
511

512
        foreach ($index->getQuotedColumns($this) as $field) {
513
            $fields[] = $field . ' IS NOT NULL';
514
        }
515 516 517

        return $sql . ' WHERE ' . implode(' AND ', $fields);
    }
518

519
    /**
520
     * {@inheritDoc}
521
     */
522
    public function getAlterTableSQL(TableDiff $diff)
523
    {
524 525 526 527
        $queryParts  = [];
        $sql         = [];
        $columnSql   = [];
        $commentsSql = [];
528

529
        foreach ($diff->addedColumns as $column) {
530 531
            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
                continue;
532 533
            }

534
            $columnDef    = $column->toArray();
535 536
            $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);

537
            if (isset($columnDef['default'])) {
538
                $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
539
            }
540 541 542

            $comment = $this->getColumnComment($column);

543 544
            if (empty($comment) && ! is_numeric($comment)) {
                continue;
545
            }
546 547 548 549 550 551

            $commentsSql[] = $this->getCreateColumnCommentSQL(
                $diff->name,
                $column->getQuotedName($this),
                $comment
            );
552 553
        }

554
        foreach ($diff->removedColumns as $column) {
555 556
            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
                continue;
557 558
            }

559
            $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this);
560 561
        }

562
        foreach ($diff->changedColumns as $columnDiff) {
563 564
            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
                continue;
565 566
            }

567 568
            $column     = $columnDiff->column;
            $comment    = $this->getColumnComment($column);
569
            $hasComment = ! empty($comment) || is_numeric($comment);
570 571 572

            if ($columnDiff->fromColumn instanceof Column) {
                $fromComment    = $this->getColumnComment($columnDiff->fromColumn);
573
                $hasFromComment = ! empty($fromComment) || is_numeric($fromComment);
574

575
                if ($hasFromComment && $hasComment && $fromComment !== $comment) {
576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596
                    $commentsSql[] = $this->getAlterColumnCommentSQL(
                        $diff->name,
                        $column->getQuotedName($this),
                        $comment
                    );
                } elseif ($hasFromComment && ! $hasComment) {
                    $commentsSql[] = $this->getDropColumnCommentSQL($diff->name, $column->getQuotedName($this));
                } elseif ($hasComment) {
                    $commentsSql[] = $this->getCreateColumnCommentSQL(
                        $diff->name,
                        $column->getQuotedName($this),
                        $comment
                    );
                }
            }

            // Do not add query part if only comment has changed.
            if ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1) {
                continue;
            }

597
            $requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff);
598

599 600 601 602 603
            if ($requireDropDefaultConstraint) {
                $queryParts[] = $this->getAlterTableDropDefaultConstraintClause(
                    $diff->name,
                    $columnDiff->oldColumnName
                );
604 605
            }

606 607
            $columnDef = $column->toArray();

608
            $queryParts[] = 'ALTER COLUMN ' .
609 610
                    $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);

611 612
            if (! isset($columnDef['default']) || (! $requireDropDefaultConstraint && ! $columnDiff->hasChanged('default'))) {
                continue;
613
            }
614 615

            $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
616 617
        }

618
        foreach ($diff->renamedColumns as $oldColumnName => $column) {
619 620
            if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
                continue;
621 622
            }

623 624
            $oldColumnName = new Identifier($oldColumnName);

625
            $sql[] = "sp_RENAME '" .
626
                $diff->getName($this)->getQuotedName($this) . '.' . $oldColumnName->getQuotedName($this) .
627
                "', '" . $column->getQuotedName($this) . "', 'COLUMN'";
628

629
            // Recreate default constraint with new column name if necessary (for future reference).
630 631
            if ($column->getDefault() === null) {
                continue;
632
            }
633 634 635 636 637 638

            $queryParts[] = $this->getAlterTableDropDefaultConstraintClause(
                $diff->name,
                $oldColumnName->getQuotedName($this)
            );
            $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
639
        }
640

641
        $tableSql = [];
642 643

        if ($this->onSchemaAlterTable($diff, $tableSql)) {
644
            return array_merge($tableSql, $columnSql);
645
        }
646

647
        foreach ($queryParts as $query) {
648
            $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
649
        }
650

651
        $sql = array_merge($sql, $commentsSql);
Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
652

Sergei Morozov's avatar
Sergei Morozov committed
653 654 655 656
        $newName = $diff->getNewName();

        if ($newName !== false) {
            $sql[] = "sp_RENAME '" . $diff->getName($this)->getQuotedName($this) . "', '" . $newName->getName() . "'";
657 658 659 660 661 662 663 664 665 666 667 668

            /**
             * Rename table's default constraints names
             * to match the new table name.
             * This is necessary to ensure that the default
             * constraints can be referenced in future table
             * alterations as the table name is encoded in
             * default constraints' names.
             */
            $sql[] = "DECLARE @sql NVARCHAR(MAX) = N''; " .
                "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " .
                "+ REPLACE(dc.name, '" . $this->generateIdentifierName($diff->name) . "', " .
Sergei Morozov's avatar
Sergei Morozov committed
669
                "'" . $this->generateIdentifierName($newName->getName()) . "') + ''', ''OBJECT'';' " .
670 671
                'FROM sys.default_constraints dc ' .
                'JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id ' .
Sergei Morozov's avatar
Sergei Morozov committed
672
                "WHERE tbl.name = '" . $newName->getName() . "';" .
673
                'EXEC sp_executesql @sql';
674 675
        }

676 677 678 679 680 681
        $sql = array_merge(
            $this->getPreAlterTableIndexForeignKeySQL($diff),
            $sql,
            $this->getPostAlterTableIndexForeignKeySQL($diff)
        );

682
        return array_merge($sql, $tableSql, $columnSql);
683
    }
684

685 686 687
    /**
     * Returns the SQL clause for adding a default constraint in an ALTER TABLE statement.
     *
688 689
     * @param string $tableName The name of the table to generate the clause for.
     * @param Column $column    The column to generate the clause for.
690 691 692 693 694
     *
     * @return string
     */
    private function getAlterTableAddDefaultConstraintClause($tableName, Column $column)
    {
695
        $columnDef         = $column->toArray();
696 697 698 699 700 701 702 703
        $columnDef['name'] = $column->getQuotedName($this);

        return 'ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $columnDef);
    }

    /**
     * Returns the SQL clause for dropping an existing default constraint in an ALTER TABLE statement.
     *
704 705
     * @param string $tableName  The name of the table to generate the clause for.
     * @param string $columnName The name of the column to generate the clause for.
706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721
     *
     * @return string
     */
    private function getAlterTableDropDefaultConstraintClause($tableName, $columnName)
    {
        return 'DROP CONSTRAINT ' . $this->generateDefaultConstraintName($tableName, $columnName);
    }

    /**
     * Checks whether a column alteration requires dropping its default constraint first.
     *
     * Different to other database vendors SQL Server implements column default values
     * as constraints and therefore changes in a column's default value as well as changes
     * in a column's type require dropping the default constraint first before being to
     * alter the particular column to the new definition.
     *
722
     * @param ColumnDiff $columnDiff The column diff to evaluate.
723
     *
724
     * @return bool True if the column alteration requires dropping its default constraint first, false otherwise.
725 726 727 728 729
     */
    private function alterColumnRequiresDropDefaultConstraint(ColumnDiff $columnDiff)
    {
        // We can only decide whether to drop an existing default constraint
        // if we know the original default value.
730
        if (! $columnDiff->fromColumn instanceof Column) {
731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747
            return false;
        }

        // We only need to drop an existing default constraint if we know the
        // column was defined with a default value before.
        if ($columnDiff->fromColumn->getDefault() === null) {
            return false;
        }

        // We need to drop an existing default constraint if the column was
        // defined with a default value before and it has changed.
        if ($columnDiff->hasChanged('default')) {
            return true;
        }

        // We need to drop an existing default constraint if the column was
        // defined with a default value before and the native column type has changed.
Gabriel Caruso's avatar
Gabriel Caruso committed
748
        return $columnDiff->hasChanged('type') || $columnDiff->hasChanged('fixed');
749 750
    }

751 752 753 754 755 756 757 758 759 760 761
    /**
     * Returns the SQL statement for altering a column comment.
     *
     * SQL Server does not support native column comments,
     * therefore the extended properties functionality is used
     * as a workaround to store them.
     * The property name used to store column comments is "MS_Description"
     * which provides compatibility with SQL Server Management Studio,
     * as column comments are stored in the same property there when
     * specifying a column's "Description" attribute.
     *
Sergei Morozov's avatar
Sergei Morozov committed
762 763 764
     * @param string      $tableName  The quoted table name to which the column belongs.
     * @param string      $columnName The quoted column name to alter the comment for.
     * @param string|null $comment    The column's comment.
765 766 767 768 769
     *
     * @return string
     */
    protected function getAlterColumnCommentSQL($tableName, $columnName, $comment)
    {
770 771 772 773 774 775 776 777 778
        if (strpos($tableName, '.') !== false) {
            [$schemaSQL, $tableSQL] = explode('.', $tableName);
            $schemaSQL              = $this->quoteStringLiteral($schemaSQL);
            $tableSQL               = $this->quoteStringLiteral($tableSQL);
        } else {
            $schemaSQL = "'dbo'";
            $tableSQL  = $this->quoteStringLiteral($tableName);
        }

779 780 781 782
        return $this->getUpdateExtendedPropertySQL(
            'MS_Description',
            $comment,
            'SCHEMA',
783
            $schemaSQL,
784
            'TABLE',
785
            $tableSQL,
786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808
            'COLUMN',
            $columnName
        );
    }

    /**
     * Returns the SQL statement for dropping a column comment.
     *
     * SQL Server does not support native column comments,
     * therefore the extended properties functionality is used
     * as a workaround to store them.
     * The property name used to store column comments is "MS_Description"
     * which provides compatibility with SQL Server Management Studio,
     * as column comments are stored in the same property there when
     * specifying a column's "Description" attribute.
     *
     * @param string $tableName  The quoted table name to which the column belongs.
     * @param string $columnName The quoted column name to drop the comment for.
     *
     * @return string
     */
    protected function getDropColumnCommentSQL($tableName, $columnName)
    {
809 810 811 812 813 814 815 816 817
        if (strpos($tableName, '.') !== false) {
            [$schemaSQL, $tableSQL] = explode('.', $tableName);
            $schemaSQL              = $this->quoteStringLiteral($schemaSQL);
            $tableSQL               = $this->quoteStringLiteral($tableSQL);
        } else {
            $schemaSQL = "'dbo'";
            $tableSQL  = $this->quoteStringLiteral($tableName);
        }

818 819 820
        return $this->getDropExtendedPropertySQL(
            'MS_Description',
            'SCHEMA',
821
            $schemaSQL,
822
            'TABLE',
823
            $tableSQL,
824 825 826 827 828
            'COLUMN',
            $columnName
        );
    }

829 830 831 832 833
    /**
     * {@inheritdoc}
     */
    protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName)
    {
834 835 836 837 838 839
        return [sprintf(
            "EXEC sp_RENAME N'%s.%s', N'%s', N'INDEX'",
            $tableName,
            $oldIndexName,
            $index->getQuotedName($this)
        ),
840
        ];
841 842
    }

843 844 845
    /**
     * Returns the SQL statement for adding an extended property to a database object.
     *
846 847
     * @link http://msdn.microsoft.com/en-us/library/ms180047%28v=sql.90%29.aspx
     *
848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868
     * @param string      $name       The name of the property to add.
     * @param string|null $value      The value of the property to add.
     * @param string|null $level0Type The type of the object at level 0 the property belongs to.
     * @param string|null $level0Name The name of the object at level 0 the property belongs to.
     * @param string|null $level1Type The type of the object at level 1 the property belongs to.
     * @param string|null $level1Name The name of the object at level 1 the property belongs to.
     * @param string|null $level2Type The type of the object at level 2 the property belongs to.
     * @param string|null $level2Name The name of the object at level 2 the property belongs to.
     *
     * @return string
     */
    public function getAddExtendedPropertySQL(
        $name,
        $value = null,
        $level0Type = null,
        $level0Name = null,
        $level1Type = null,
        $level1Name = null,
        $level2Type = null,
        $level2Name = null
    ) {
869
        return 'EXEC sp_addextendedproperty ' .
Sergei Morozov's avatar
Sergei Morozov committed
870 871 872 873
            'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value) . ', ' .
            'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' .
            'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' .
            'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name;
874 875 876 877 878
    }

    /**
     * Returns the SQL statement for dropping an extended property from a database object.
     *
879 880
     * @link http://technet.microsoft.com/en-gb/library/ms178595%28v=sql.90%29.aspx
     *
881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899
     * @param string      $name       The name of the property to drop.
     * @param string|null $level0Type The type of the object at level 0 the property belongs to.
     * @param string|null $level0Name The name of the object at level 0 the property belongs to.
     * @param string|null $level1Type The type of the object at level 1 the property belongs to.
     * @param string|null $level1Name The name of the object at level 1 the property belongs to.
     * @param string|null $level2Type The type of the object at level 2 the property belongs to.
     * @param string|null $level2Name The name of the object at level 2 the property belongs to.
     *
     * @return string
     */
    public function getDropExtendedPropertySQL(
        $name,
        $level0Type = null,
        $level0Name = null,
        $level1Type = null,
        $level1Name = null,
        $level2Type = null,
        $level2Name = null
    ) {
900 901
        return 'EXEC sp_dropextendedproperty ' .
            'N' . $this->quoteStringLiteral($name) . ', ' .
Sergei Morozov's avatar
Sergei Morozov committed
902 903 904
            'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' .
            'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' .
            'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name;
905 906 907 908 909
    }

    /**
     * Returns the SQL statement for updating an extended property of a database object.
     *
910 911
     * @link http://msdn.microsoft.com/en-us/library/ms186885%28v=sql.90%29.aspx
     *
912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932
     * @param string      $name       The name of the property to update.
     * @param string|null $value      The value of the property to update.
     * @param string|null $level0Type The type of the object at level 0 the property belongs to.
     * @param string|null $level0Name The name of the object at level 0 the property belongs to.
     * @param string|null $level1Type The type of the object at level 1 the property belongs to.
     * @param string|null $level1Name The name of the object at level 1 the property belongs to.
     * @param string|null $level2Type The type of the object at level 2 the property belongs to.
     * @param string|null $level2Name The name of the object at level 2 the property belongs to.
     *
     * @return string
     */
    public function getUpdateExtendedPropertySQL(
        $name,
        $value = null,
        $level0Type = null,
        $level0Name = null,
        $level1Type = null,
        $level1Name = null,
        $level2Type = null,
        $level2Name = null
    ) {
933
        return 'EXEC sp_updateextendedproperty ' .
Sergei Morozov's avatar
Sergei Morozov committed
934 935 936 937
            'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value) . ', ' .
            'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' .
            'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' .
            'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name;
938 939
    }

940
    /**
941
     * {@inheritDoc}
942
     */
943
    public function getEmptyIdentityInsertSQL($quotedTableName, $quotedIdentifierColumnName)
944
    {
945
        return 'INSERT INTO ' . $quotedTableName . ' DEFAULT VALUES';
946 947
    }

948
    /**
949
     * {@inheritDoc}
950
     */
951
    public function getListTablesSQL()
952
    {
953
        // "sysdiagrams" table must be ignored as it's internal SQL Server table for Database Diagrams
954
        // Category 2 must be ignored as it is "MS SQL Server 'pseudo-system' object[s]" for replication
955
        return "SELECT name, SCHEMA_NAME (uid) AS schema_name FROM sysobjects WHERE type = 'U' AND name != 'sysdiagrams' AND category != 2 ORDER BY name";
956 957 958
    }

    /**
959
     * {@inheritDoc}
960
     */
961
    public function getListTableColumnsSQL($table, $database = null)
962
    {
963 964 965 966 967 968 969 970
        return "SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
971 972
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
973 974 975 976 977
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
978 979
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
980 981 982
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
983 984 985 986
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
987
                WHERE     obj.type = 'U'
988
                AND       " . $this->getTableWhereClause($table, 'scm.name', 'obj.name');
989 990 991
    }

    /**
992 993 994 995
     * @param string      $table
     * @param string|null $database
     *
     * @return string
996 997 998
     */
    public function getListTableForeignKeysSQL($table, $database = null)
    {
999
        return 'SELECT f.name AS ForeignKey,
1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011
                SCHEMA_NAME (f.SCHEMA_ID) AS SchemaName,
                OBJECT_NAME (f.parent_object_id) AS TableName,
                COL_NAME (fc.parent_object_id,fc.parent_column_id) AS ColumnName,
                SCHEMA_NAME (o.SCHEMA_ID) ReferenceSchemaName,
                OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
                COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
                f.delete_referential_action_desc,
                f.update_referential_action_desc
                FROM sys.foreign_keys AS f
                INNER JOIN sys.foreign_key_columns AS fc
                INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
                ON f.OBJECT_ID = fc.constraint_object_id
1012
                WHERE ' .
1013
                $this->getTableWhereClause($table, 'SCHEMA_NAME (f.schema_id)', 'OBJECT_NAME (f.parent_object_id)');
1014 1015 1016
    }

    /**
1017
     * {@inheritDoc}
1018
     */
1019
    public function getListTableIndexesSQL($table, $currentDatabase = null)
1020
    {
1021 1022
        return "SELECT idx.name AS key_name,
                       col.name AS column_name,
Steve Müller's avatar
Steve Müller committed
1023 1024
                       ~idx.is_unique AS non_unique,
                       idx.is_primary_key AS [primary],
1025 1026 1027 1028 1029 1030
                       CASE idx.type
                           WHEN '1' THEN 'clustered'
                           WHEN '2' THEN 'nonclustered'
                           ELSE NULL
                       END AS flags
                FROM sys.tables AS tbl
1031
                JOIN sys.schemas AS scm ON tbl.schema_id = scm.schema_id
1032 1033 1034
                JOIN sys.indexes AS idx ON tbl.object_id = idx.object_id
                JOIN sys.index_columns AS idxcol ON idx.object_id = idxcol.object_id AND idx.index_id = idxcol.index_id
                JOIN sys.columns AS col ON idxcol.object_id = col.object_id AND idxcol.column_id = col.column_id
1035 1036
                WHERE " . $this->getTableWhereClause($table, 'scm.name', 'tbl.name') . '
                ORDER BY idx.index_id ASC, idxcol.key_ordinal ASC';
1037
    }
1038 1039

    /**
1040
     * {@inheritDoc}
1041
     */
1042
    public function getCreateViewSQL($name, $sql)
1043 1044 1045
    {
        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
    }
1046 1047

    /**
1048
     * {@inheritDoc}
1049
     */
1050
    public function getListViewsSQL($database)
1051 1052 1053 1054
    {
        return "SELECT name FROM sysobjects WHERE type = 'V' ORDER BY name";
    }

1055 1056 1057 1058
    /**
     * Returns the where clause to filter schema and table name in a query.
     *
     * @param string $table        The full qualified name of the table.
1059 1060
     * @param string $schemaColumn The name of the column to compare the schema to in the where clause.
     * @param string $tableColumn  The name of the column to compare the table to in the where clause.
1061 1062 1063 1064 1065
     *
     * @return string
     */
    private function getTableWhereClause($table, $schemaColumn, $tableColumn)
    {
1066 1067 1068 1069
        if (strpos($table, '.') !== false) {
            [$schema, $table] = explode('.', $table);
            $schema           = $this->quoteStringLiteral($schema);
            $table            = $this->quoteStringLiteral($table);
1070
        } else {
1071 1072
            $schema = 'SCHEMA_NAME()';
            $table  = $this->quoteStringLiteral($table);
1073 1074
        }

1075
        return sprintf('(%s = %s AND %s = %s)', $tableColumn, $table, $schemaColumn, $schema);
1076 1077
    }

1078
    /**
1079
     * {@inheritDoc}
1080 1081 1082
     */
    public function getDropViewSQL($name)
    {
1083
        return 'DROP VIEW ' . $name;
1084
    }
1085

1086
    /**
1087
     * {@inheritDoc}
1088
     */
1089
    public function getLocateExpression($str, $substr, $startPos = false)
1090
    {
1091
        if ($startPos === false) {
1092 1093
            return 'CHARINDEX(' . $substr . ', ' . $str . ')';
        }
1094 1095

        return 'CHARINDEX(' . $substr . ', ' . $str . ', ' . $startPos . ')';
1096
    }
1097

1098
    /**
1099
     * {@inheritDoc}
1100
     */
1101
    public function getModExpression($expression1, $expression2)
1102
    {
1103
        return $expression1 . ' % ' . $expression2;
1104
    }
1105

1106
    /**
1107
     * {@inheritDoc}
1108
     */
1109
    public function getTrimExpression($str, $pos = TrimMode::UNSPECIFIED, $char = false)
1110
    {
1111
        if ($char === false) {
1112
            switch ($pos) {
1113
                case TrimMode::LEADING:
1114 1115 1116
                    $trimFn = 'LTRIM';
                    break;

1117
                case TrimMode::TRAILING:
1118 1119 1120 1121 1122
                    $trimFn = 'RTRIM';
                    break;

                default:
                    return 'LTRIM(RTRIM(' . $str . '))';
1123 1124 1125 1126
            }

            return $trimFn . '(' . $str . ')';
        }
1127 1128 1129 1130 1131 1132 1133 1134 1135 1136

        /** Original query used to get those expressions
          declare @c varchar(100) = 'xxxBarxxx', @trim_char char(1) = 'x';
          declare @pat varchar(10) = '%[^' + @trim_char + ']%';
          select @c as string
          , @trim_char as trim_char
          , stuff(@c, 1, patindex(@pat, @c) - 1, null) as trim_leading
          , reverse(stuff(reverse(@c), 1, patindex(@pat, reverse(@c)) - 1, null)) as trim_trailing
          , reverse(stuff(reverse(stuff(@c, 1, patindex(@pat, @c) - 1, null)), 1, patindex(@pat, reverse(stuff(@c, 1, patindex(@pat, @c) - 1, null))) - 1, null)) as trim_both;
         */
1137
        $pattern = "'%[^' + " . $char . " + ']%'";
1138

1139
        if ($pos === TrimMode::LEADING) {
1140 1141 1142
            return 'stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)';
        }

1143
        if ($pos === TrimMode::TRAILING) {
1144 1145 1146 1147
            return 'reverse(stuff(reverse(' . $str . '), 1, patindex(' . $pattern . ', reverse(' . $str . ')) - 1, null))';
        }

        return 'reverse(stuff(reverse(stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)), 1, patindex(' . $pattern . ', reverse(stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null))) - 1, null))';
1148
    }
1149

1150
    /**
1151
     * {@inheritDoc}
1152 1153
     */
    public function getConcatExpression()
1154
    {
1155
        $args = func_get_args();
1156

1157
        return '(' . implode(' + ', $args) . ')';
1158
    }
1159

Benjamin Morel's avatar
Benjamin Morel committed
1160 1161 1162
    /**
     * {@inheritDoc}
     */
1163
    public function getListDatabasesSQL()
1164
    {
1165
        return 'SELECT * FROM sys.databases';
1166
    }
1167

1168 1169 1170 1171 1172
    /**
     * {@inheritDoc}
     */
    public function getListNamespacesSQL()
    {
1173
        return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
1174 1175
    }

1176
    /**
1177
     * {@inheritDoc}
1178
     */
1179
    public function getSubstringExpression($value, $from, $length = null)
1180
    {
1181
        if ($length !== null) {
1182
            return 'SUBSTRING(' . $value . ', ' . $from . ', ' . $length . ')';
1183
        }
1184

1185
        return 'SUBSTRING(' . $value . ', ' . $from . ', LEN(' . $value . ') - ' . $from . ' + 1)';
1186
    }
1187

1188
    /**
1189
     * {@inheritDoc}
1190
     */
1191
    public function getLengthExpression($column)
1192
    {
1193
        return 'LEN(' . $column . ')';
1194 1195
    }

1196 1197 1198 1199 1200
    public function getCurrentDatabaseExpression(): string
    {
        return 'DB_NAME()';
    }

1201
    /**
1202
     * {@inheritDoc}
1203
     */
1204
    public function getSetTransactionIsolationSQL($level)
romanb's avatar
romanb committed
1205
    {
1206
        return 'SET TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
romanb's avatar
romanb committed
1207
    }
1208

1209
    /**
1210
     * {@inheritDoc}
1211
     */
1212
    public function getIntegerTypeDeclarationSQL(array $field)
1213
    {
1214
        return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
1215 1216
    }

1217
    /**
1218
     * {@inheritDoc}
1219
     */
1220
    public function getBigIntTypeDeclarationSQL(array $field)
1221
    {
1222
        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
1223 1224
    }

1225
    /**
1226
     * {@inheritDoc}
1227
     */
1228
    public function getSmallIntTypeDeclarationSQL(array $field)
1229
    {
1230
        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
1231 1232
    }

1233
    /**
1234
     * {@inheritDoc}
1235
     */
1236
    public function getGuidTypeDeclarationSQL(array $field)
1237 1238 1239 1240
    {
        return 'UNIQUEIDENTIFIER';
    }

1241 1242 1243 1244 1245 1246 1247 1248
    /**
     * {@inheritDoc}
     */
    public function getDateTimeTzTypeDeclarationSQL(array $fieldDeclaration)
    {
        return 'DATETIMEOFFSET(6)';
    }

1249 1250 1251
    /**
     * {@inheritDoc}
     */
1252
    protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
1253
    {
1254 1255 1256
        return $fixed
            ? ($length > 0 ? 'NCHAR(' . $length . ')' : 'CHAR(255)')
            : ($length > 0 ? 'NVARCHAR(' . $length . ')' : 'NVARCHAR(255)');
1257
    }
1258

Steve Müller's avatar
Steve Müller committed
1259 1260 1261 1262 1263
    /**
     * {@inheritdoc}
     */
    protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed)
    {
1264 1265 1266
        return $fixed
            ? 'BINARY(' . ($length > 0 ? $length : 255) . ')'
            : 'VARBINARY(' . ($length > 0 ? $length : 255) . ')';
Steve Müller's avatar
Steve Müller committed
1267 1268 1269 1270 1271 1272 1273 1274 1275 1276
    }

    /**
     * {@inheritdoc}
     */
    public function getBinaryMaxLength()
    {
        return 8000;
    }

1277 1278 1279
    /**
     * {@inheritDoc}
     */
1280
    public function getClobTypeDeclarationSQL(array $field)
1281
    {
1282
        return 'VARCHAR(MAX)';
1283
    }
1284

1285
    /**
1286
     * {@inheritDoc}
1287
     */
1288
    protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef)
1289
    {
1290
        return ! empty($columnDef['autoincrement']) ? ' IDENTITY' : '';
1291
    }
1292

1293
    /**
1294
     * {@inheritDoc}
1295
     */
1296
    public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration)
1297
    {
1298 1299 1300
        // 3 - microseconds precision length
        // http://msdn.microsoft.com/en-us/library/ms187819.aspx
        return 'DATETIME2(6)';
1301 1302
    }

1303
    /**
1304
     * {@inheritDoc}
1305
     */
1306
    public function getDateTypeDeclarationSQL(array $fieldDeclaration)
1307
    {
1308
        return 'DATE';
1309
    }
1310 1311

    /**
1312
     * {@inheritDoc}
1313
     */
1314
    public function getTimeTypeDeclarationSQL(array $fieldDeclaration)
1315
    {
1316
        return 'TIME(0)';
1317
    }
1318

1319
    /**
1320
     * {@inheritDoc}
1321
     */
1322
    public function getBooleanTypeDeclarationSQL(array $field)
1323 1324 1325 1326
    {
        return 'BIT';
    }

1327
    /**
1328
     * {@inheritDoc}
1329
     */
1330
    protected function doModifyLimitQuery($query, $limit, $offset = null)
1331
    {
1332
        if ($limit === null && $offset <= 0) {
1333 1334
            return $query;
        }
1335

1336 1337 1338 1339 1340 1341 1342
        // Queries using OFFSET... FETCH MUST have an ORDER BY clause
        // Find the position of the last instance of ORDER BY and ensure it is not within a parenthetical statement
        // but can be in a newline
        $matches      = [];
        $matchesCount = preg_match_all('/[\\s]+order\\s+by\\s/im', $query, $matches, PREG_OFFSET_CAPTURE);
        $orderByPos   = false;
        if ($matchesCount > 0) {
1343
            $orderByPos = $matches[0][$matchesCount - 1][1];
Sergei Morozov's avatar
Sergei Morozov committed
1344 1345
        }

1346 1347
        if (
            $orderByPos === false
1348
            || substr_count($query, '(', $orderByPos) !== substr_count($query, ')', $orderByPos)
1349
        ) {
1350
            if (preg_match('/^SELECT\s+DISTINCT/im', $query) > 0) {
1351 1352 1353 1354 1355 1356 1357 1358 1359 1360
                // SQL Server won't let us order by a non-selected column in a DISTINCT query,
                // so we have to do this madness. This says, order by the first column in the
                // result. SQL Server's docs say that a nonordered query's result order is non-
                // deterministic anyway, so this won't do anything that a bunch of update and
                // deletes to the table wouldn't do anyway.
                $query .= ' ORDER BY 1';
            } else {
                // In another DBMS, we could do ORDER BY 0, but SQL Server gets angry if you
                // use constant expressions in the order by list.
                $query .= ' ORDER BY (SELECT 0)';
1361
            }
1362
        }
1363

1364 1365 1366 1367
        // This looks somewhat like MYSQL, but limit/offset are in inverse positions
        // Supposedly SQL:2008 core standard.
        // Per TSQL spec, FETCH NEXT n ROWS ONLY is not valid without OFFSET n ROWS.
        $query .= sprintf(' OFFSET %d ROWS', $offset);
1368

1369 1370
        if ($limit !== null) {
            $query .= sprintf(' FETCH NEXT %d ROWS ONLY', $limit);
1371 1372
        }

1373
        return $query;
1374 1375
    }

1376
    /**
1377
     * {@inheritDoc}
1378 1379 1380
     */
    public function supportsLimitOffset()
    {
1381
        return true;
1382 1383
    }

1384
    /**
1385
     * {@inheritDoc}
1386
     */
1387
    public function convertBooleans($item)
1388
    {
1389 1390
        if (is_array($item)) {
            foreach ($item as $key => $value) {
1391
                if (! is_bool($value) && ! is_numeric($value)) {
1392
                    continue;
1393
                }
1394

1395
                $item[$key] = (int) (bool) $value;
1396
            }
Steve Müller's avatar
Steve Müller committed
1397
        } elseif (is_bool($item) || is_numeric($item)) {
1398
            $item = (int) (bool) $item;
1399
        }
1400

1401
        return $item;
1402
    }
1403 1404

    /**
1405
     * {@inheritDoc}
1406
     */
1407
    public function getCreateTemporaryTableSnippetSQL()
1408
    {
1409
        return 'CREATE TABLE';
1410
    }
1411

1412
    /**
1413
     * {@inheritDoc}
1414 1415 1416 1417 1418 1419
     */
    public function getTemporaryTableName($tableName)
    {
        return '#' . $tableName;
    }

1420
    /**
1421
     * {@inheritDoc}
1422 1423 1424
     */
    public function getDateTimeFormatString()
    {
1425
        return 'Y-m-d H:i:s.u';
1426
    }
1427

1428
    /**
1429
     * {@inheritDoc}
1430
     */
1431 1432
    public function getDateFormatString()
    {
1433
        return 'Y-m-d';
1434 1435 1436
    }

    /**
1437
     * {@inheritDoc}
1438
     */
1439 1440
    public function getTimeFormatString()
    {
1441
        return 'H:i:s';
1442
    }
1443

1444
    /**
1445
     * {@inheritDoc}
1446 1447 1448
     */
    public function getDateTimeTzFormatString()
    {
1449
        return 'Y-m-d H:i:s.u P';
1450
    }
1451

1452
    /**
1453
     * {@inheritDoc}
1454
     */
1455
    public function getName()
1456
    {
1457
        return 'mssql';
1458
    }
1459

Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
1460
    /**
1461
     * {@inheritDoc}
Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
1462
     */
1463 1464
    protected function initializeDoctrineTypeMappings()
    {
1465
        $this->doctrineTypeMapping = [
1466 1467 1468 1469 1470 1471 1472 1473 1474 1475
            'bigint'           => 'bigint',
            'binary'           => 'binary',
            'bit'              => 'boolean',
            'char'             => 'string',
            'date'             => 'date',
            'datetime'         => 'datetime',
            'datetime2'        => 'datetime',
            'datetimeoffset'   => 'datetimetz',
            'decimal'          => 'decimal',
            'double'           => 'float',
1476
            'double precision' => 'float',
1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491
            'float'            => 'float',
            'image'            => 'blob',
            'int'              => 'integer',
            'money'            => 'integer',
            'nchar'            => 'string',
            'ntext'            => 'text',
            'numeric'          => 'decimal',
            'nvarchar'         => 'string',
            'real'             => 'float',
            'smalldatetime'    => 'datetime',
            'smallint'         => 'smallint',
            'smallmoney'       => 'integer',
            'text'             => 'text',
            'time'             => 'time',
            'tinyint'          => 'smallint',
1492
            'uniqueidentifier' => 'guid',
1493 1494
            'varbinary'        => 'binary',
            'varchar'          => 'string',
1495
        ];
1496
    }
1497 1498

    /**
1499
     * {@inheritDoc}
1500 1501 1502 1503 1504 1505 1506
     */
    public function createSavePoint($savepoint)
    {
        return 'SAVE TRANSACTION ' . $savepoint;
    }

    /**
1507
     * {@inheritDoc}
1508 1509 1510 1511 1512 1513 1514
     */
    public function releaseSavePoint($savepoint)
    {
        return '';
    }

    /**
1515
     * {@inheritDoc}
1516 1517 1518 1519
     */
    public function rollbackSavePoint($savepoint)
    {
        return 'ROLLBACK TRANSACTION ' . $savepoint;
1520
    }
1521

1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534
    /**
     * {@inheritdoc}
     */
    public function getForeignKeyReferentialActionSQL($action)
    {
        // RESTRICT is not supported, therefore falling back to NO ACTION.
        if (strtoupper($action) === 'RESTRICT') {
            return 'NO ACTION';
        }

        return parent::getForeignKeyReferentialActionSQL($action);
    }

1535
    /**
1536
     * {@inheritDoc}
Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
1537
     */
1538
    public function appendLockHint($fromClause, $lockMode)
Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
1539
    {
1540
        switch (true) {
1541
            case $lockMode === LockMode::NONE:
1542
                return $fromClause . ' WITH (NOLOCK)';
1543

1544
            case $lockMode === LockMode::PESSIMISTIC_READ:
1545
                return $fromClause . ' WITH (HOLDLOCK, ROWLOCK)';
1546

1547
            case $lockMode === LockMode::PESSIMISTIC_WRITE:
1548
                return $fromClause . ' WITH (UPDLOCK, ROWLOCK)';
1549

Steve Müller's avatar
Steve Müller committed
1550
            default:
1551
                return $fromClause;
Steve Müller's avatar
Steve Müller committed
1552
        }
Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
1553 1554 1555
    }

    /**
1556
     * {@inheritDoc}
Juozas Kaziukenas's avatar
Juozas Kaziukenas committed
1557 1558 1559 1560 1561
     */
    public function getForUpdateSQL()
    {
        return ' ';
    }
1562

1563 1564 1565
    /**
     * {@inheritDoc}
     */
1566 1567
    protected function getReservedKeywordsClass()
    {
1568
        return Keywords\SQLServer2012Keywords::class;
1569
    }
1570 1571

    /**
1572
     * {@inheritDoc}
1573
     */
1574
    public function quoteSingleIdentifier($str)
1575
    {
1576
        return '[' . str_replace(']', '][', $str) . ']';
1577
    }
1578

1579 1580 1581
    /**
     * {@inheritDoc}
     */
1582 1583
    public function getTruncateTableSQL($tableName, $cascade = false)
    {
1584 1585 1586
        $tableIdentifier = new Identifier($tableName);

        return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this);
1587
    }
1588 1589

    /**
1590
     * {@inheritDoc}
1591 1592 1593 1594 1595
     */
    public function getBlobTypeDeclarationSQL(array $field)
    {
        return 'VARBINARY(MAX)';
    }
1596

1597 1598 1599 1600 1601 1602 1603 1604 1605 1606
    /**
     * {@inheritdoc}
     *
     * Modifies column declaration order as it differs in Microsoft SQL Server.
     */
    public function getColumnDeclarationSQL($name, array $field)
    {
        if (isset($field['columnDefinition'])) {
            $columnDef = $this->getCustomTypeDeclarationSQL($field);
        } else {
1607
            $collation = ! empty($field['collation']) ?
1608
                ' ' . $this->getColumnCollationDeclarationSQL($field['collation']) : '';
1609

1610
            $notnull = ! empty($field['notnull']) ? ' NOT NULL' : '';
1611

1612
            $unique = ! empty($field['unique']) ?
1613 1614
                ' ' . $this->getUniqueFieldDeclarationSQL() : '';

1615
            $check = ! empty($field['check']) ?
1616 1617
                ' ' . $field['check'] : '';

1618
            $typeDecl  = $field['type']->getSQLDeclaration($field, $this);
1619
            $columnDef = $typeDecl . $collation . $notnull . $unique . $check;
1620 1621 1622 1623
        }

        return $name . ' ' . $columnDef;
    }
1624

1625
    protected function getLikeWildcardCharacters(): string
1626 1627 1628 1629
    {
        return parent::getLikeWildcardCharacters() . '[]^';
    }

1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651
    /**
     * Returns a unique default constraint name for a table and column.
     *
     * @param string $table  Name of the table to generate the unique default constraint name for.
     * @param string $column Name of the column in the table to generate the unique default constraint name for.
     *
     * @return string
     */
    private function generateDefaultConstraintName($table, $column)
    {
        return 'DF_' . $this->generateIdentifierName($table) . '_' . $this->generateIdentifierName($column);
    }

    /**
     * Returns a hash value for a given identifier.
     *
     * @param string $identifier Identifier to generate a hash value for.
     *
     * @return string
     */
    private function generateIdentifierName($identifier)
    {
1652 1653 1654 1655
        // Always generate name for unquoted identifiers to ensure consistency.
        $identifier = new Identifier($identifier);

        return strtoupper(dechex(crc32($identifier->getName())));
1656
    }
1657

1658
    protected function getCommentOnTableSQL(string $tableName, ?string $comment): string
1659 1660 1661
    {
        return sprintf(
            <<<'SQL'
Sergei Morozov's avatar
Sergei Morozov committed
1662 1663
EXEC sys.sp_addextendedproperty @name=N'MS_Description',
  @value=N%s, @level0type=N'SCHEMA', @level0name=N'dbo',
1664 1665 1666 1667 1668 1669 1670 1671
  @level1type=N'TABLE', @level1name=N%s
SQL
            ,
            $this->quoteStringLiteral((string) $comment),
            $this->quoteStringLiteral($tableName)
        );
    }

1672
    public function getListTableMetadataSQL(string $table): string
1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687
    {
        return sprintf(
            <<<'SQL'
SELECT
  p.value AS [table_comment]
FROM
  sys.tables AS tbl
  INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1
WHERE
  (tbl.name=N%s and SCHEMA_NAME(tbl.schema_id)=N'dbo' and p.name=N'MS_Description')
SQL
            ,
            $this->quoteStringLiteral($table)
        );
    }
1688
}