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