Failed Conditions
Push — master ( 30b923...92920e )
by Marco
19s queued 13s
created

SQLServerPlatform::doModifyLimitQuery()   B

Complexity

Conditions 6
Paths 16

Size

Total Lines 44
Code Lines 23

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 18
CRAP Score 6.0052

Importance

Changes 0
Metric Value
eloc 23
dl 0
loc 44
ccs 18
cts 19
cp 0.9474
rs 8.9297
c 0
b 0
f 0
cc 6
nc 16
nop 3
crap 6.0052
1
<?php
2
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\Table;
12
use Doctrine\DBAL\Schema\TableDiff;
13
use Doctrine\DBAL\Types;
14
use InvalidArgumentException;
15
use function array_merge;
16
use function array_unique;
17
use function array_values;
18
use function count;
19
use function crc32;
20
use function dechex;
21
use function explode;
22
use function func_get_args;
23
use function implode;
24
use function is_array;
25
use function is_bool;
26
use function is_numeric;
27
use function is_string;
28
use function preg_match;
29
use function sprintf;
30
use function str_replace;
31
use function stripos;
32
use function stristr;
33
use function strlen;
34
use function strpos;
35
use function strtoupper;
36
use function substr;
37
use function substr_count;
38
39
/**
40
 * The SQLServerPlatform provides the behavior, features and SQL dialect of the
41
 * Microsoft SQL Server database platform.
42
 */
43
class SQLServerPlatform extends AbstractPlatform
44
{
45
    /**
46
     * {@inheritdoc}
47
     */
48 164
    public function getCurrentDateSQL()
49
    {
50 164
        return $this->getConvertExpression('date', 'GETDATE()');
51
    }
52
53
    /**
54
     * {@inheritdoc}
55
     */
56 83
    public function getCurrentTimeSQL()
57
    {
58 83
        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 164
    private function getConvertExpression($dataType, $expression)
70
    {
71 164
        return sprintf('CONVERT(%s, %s)', $dataType, $expression);
72
    }
73
74
    /**
75
     * {@inheritdoc}
76
     */
77 2
    protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit)
78
    {
79 2
        $factorClause = '';
80
81 2
        if ($operator === '-') {
82 2
            $factorClause = '-1 * ';
83
        }
84
85 2
        return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')';
86
    }
87
88
    /**
89
     * {@inheritDoc}
90
     */
91 6
    public function getDateDiffExpression($date1, $date2)
92
    {
93 6
        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 83
    public function prefersIdentityColumns()
103
    {
104 83
        return true;
105
    }
106
107
    /**
108
     * {@inheritDoc}
109
     *
110
     * Microsoft SQL Server supports this through AUTO_INCREMENT columns.
111
     */
112 87
    public function supportsIdentityColumns()
113
    {
114 87
        return true;
115
    }
116
117
    /**
118
     * {@inheritDoc}
119
     */
120 2
    public function supportsReleaseSavepoints()
121
    {
122 2
        return false;
123
    }
124
125
    /**
126
     * {@inheritdoc}
127
     */
128 89
    public function supportsSchemas()
129
    {
130 89
        return true;
131
    }
132
133
    /**
134
     * {@inheritdoc}
135
     */
136 2
    public function getDefaultSchemaName()
137
    {
138 2
        return 'dbo';
139
    }
140
141
    /**
142
     * {@inheritDoc}
143
     */
144 245
    public function supportsColumnCollation()
145
    {
146 245
        return true;
147
    }
148
149
    /**
150
     * {@inheritDoc}
151
     */
152 4741
    public function hasNativeGuidType()
153
    {
154 4741
        return true;
155
    }
156
157
    /**
158
     * {@inheritDoc}
159
     */
160 85
    public function getCreateDatabaseSQL($name)
161
    {
162 85
        return 'CREATE DATABASE ' . $name;
163
    }
164
165
    /**
166
     * {@inheritDoc}
167
     */
168 85
    public function getDropDatabaseSQL($name)
169
    {
170 85
        return 'DROP DATABASE ' . $name;
171
    }
172
173
    /**
174
     * {@inheritDoc}
175
     */
176 85
    public function supportsCreateDropDatabase()
177
    {
178 85
        return true;
179
    }
180
181
    /**
182
     * {@inheritDoc}
183
     */
184 85
    public function getCreateSchemaSQL($schemaName)
185
    {
186 85
        return 'CREATE SCHEMA ' . $schemaName;
187
    }
188
189
    /**
190
     * {@inheritDoc}
191
     */
192 164
    public function getDropForeignKeySQL($foreignKey, $table)
193
    {
194 164
        if (! $foreignKey instanceof ForeignKeyConstraint) {
195 81
            $foreignKey = new Identifier($foreignKey);
196
        }
197
198 164
        if (! $table instanceof Table) {
199 164
            $table = new Identifier($table);
200
        }
201
202 164
        $foreignKey = $foreignKey->getQuotedName($this);
203 164
        $table      = $table->getQuotedName($this);
204
205 164
        return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $foreignKey;
206
    }
207
208
    /**
209
     * {@inheritDoc}
210
     */
211 6
    public function getDropIndexSQL($index, $table = null)
212
    {
213 6
        if ($index instanceof Index) {
214 4
            $index = $index->getQuotedName($this);
215 2
        } elseif (! is_string($index)) {
216
            throw new InvalidArgumentException('AbstractPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.');
217
        }
218
219 6
        if (! isset($table)) {
220
            return 'DROP INDEX ' . $index;
221
        }
222
223 6
        if ($table instanceof Table) {
224 2
            $table = $table->getQuotedName($this);
225
        }
226
227 6
        return sprintf(
228
            <<<SQL
229 6
IF EXISTS (SELECT * FROM sysobjects WHERE name = '%s')
230
    ALTER TABLE %s DROP CONSTRAINT %s
231
ELSE
232
    DROP INDEX %s ON %s
233
SQL
234
            ,
235 6
            $index,
236 6
            $table,
237 6
            $index,
238 6
            $index,
239 6
            $table
240
        );
241
    }
242
243
    /**
244
     * {@inheritDoc}
245
     */
246 1673
    protected function _getCreateTableSQL($tableName, array $columns, array $options = [])
247
    {
248 1673
        $defaultConstraintsSql = [];
249 1673
        $commentsSql           = [];
250
251
        // @todo does other code breaks because of this?
252
        // force primary keys to be not null
253 1673
        foreach ($columns as &$column) {
254 1673
            if (isset($column['primary']) && $column['primary']) {
255 668
                $column['notnull'] = true;
256
            }
257
258
            // Build default constraints SQL statements.
259 1673
            if (isset($column['default'])) {
260 346
                $defaultConstraintsSql[] = 'ALTER TABLE ' . $tableName .
261 346
                    ' ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $column);
262
            }
263
264 1673
            if (empty($column['comment']) && ! is_numeric($column['comment'])) {
265 1499
                continue;
266
            }
267
268 388
            $commentsSql[] = $this->getCreateColumnCommentSQL($tableName, $column['name'], $column['comment']);
269
        }
270
271 1673
        $columnListSql = $this->getColumnDeclarationListSQL($columns);
272
273 1673
        if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
274
            foreach ($options['uniqueConstraints'] as $name => $definition) {
275
                $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition);
276
            }
277
        }
278
279 1673
        if (isset($options['primary']) && ! empty($options['primary'])) {
280 749
            $flags = '';
281 749
            if (isset($options['primary_index']) && $options['primary_index']->hasFlag('nonclustered')) {
282 81
                $flags = ' NONCLUSTERED';
283
            }
284 749
            $columnListSql .= ', PRIMARY KEY' . $flags . ' (' . implode(', ', array_unique(array_values($options['primary']))) . ')';
285
        }
286
287 1673
        $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql;
288
289 1673
        $check = $this->getCheckDeclarationSQL($columns);
290 1673
        if (! empty($check)) {
291
            $query .= ', ' . $check;
292
        }
293 1673
        $query .= ')';
294
295 1673
        $sql = [$query];
296
297 1673
        if (isset($options['indexes']) && ! empty($options['indexes'])) {
298 259
            foreach ($options['indexes'] as $index) {
299 259
                $sql[] = $this->getCreateIndexSQL($index, $tableName);
300
            }
301
        }
302
303 1673
        if (isset($options['foreignKeys'])) {
304 346
            foreach ((array) $options['foreignKeys'] as $definition) {
305 91
                $sql[] = $this->getCreateForeignKeySQL($definition, $tableName);
306
            }
307
        }
308
309 1673
        return array_merge($sql, $commentsSql, $defaultConstraintsSql);
310
    }
311
312
    /**
313
     * {@inheritDoc}
314
     */
315 162
    public function getCreatePrimaryKeySQL(Index $index, $table)
316
    {
317 162
        if ($table instanceof Table) {
318
            $identifier = $table->getQuotedName($this);
319
        } else {
320 162
            $identifier = $table;
321
        }
322
323 162
        $sql = 'ALTER TABLE ' . $identifier . ' ADD PRIMARY KEY';
324
325 162
        if ($index->hasFlag('nonclustered')) {
326 81
            $sql .= ' NONCLUSTERED';
327
        }
328
329 162
        return $sql . ' (' . $this->getIndexFieldDeclarationListSQL($index) . ')';
330
    }
331
332
    /**
333
     * Returns the SQL statement for creating a column comment.
334
     *
335
     * SQL Server does not support native column comments,
336
     * therefore the extended properties functionality is used
337
     * as a workaround to store them.
338
     * The property name used to store column comments is "MS_Description"
339
     * which provides compatibility with SQL Server Management Studio,
340
     * as column comments are stored in the same property there when
341
     * specifying a column's "Description" attribute.
342
     *
343
     * @param string      $tableName  The quoted table name to which the column belongs.
344
     * @param string      $columnName The quoted column name to create the comment for.
345
     * @param string|null $comment    The column's comment.
346
     *
347
     * @return string
348
     */
349 631
    protected function getCreateColumnCommentSQL($tableName, $columnName, $comment)
350
    {
351 631
        if (strpos($tableName, '.') !== false) {
352 162
            [$schemaSQL, $tableSQL] = explode('.', $tableName);
353 162
            $schemaSQL              = $this->quoteStringLiteral($schemaSQL);
354 162
            $tableSQL               = $this->quoteStringLiteral($tableSQL);
355
        } else {
356 469
            $schemaSQL = "'dbo'";
357 469
            $tableSQL  = $this->quoteStringLiteral($tableName);
358
        }
359
360 631
        return $this->getAddExtendedPropertySQL(
361 631
            'MS_Description',
362 631
            $comment,
363 631
            'SCHEMA',
364 631
            $schemaSQL,
365 631
            'TABLE',
366 631
            $tableSQL,
367 631
            'COLUMN',
368 631
            $columnName
369
        );
370
    }
371
372
    /**
373
     * Returns the SQL snippet for declaring a default constraint.
374
     *
375
     * @param string  $table  Name of the table to return the default constraint declaration for.
376
     * @param mixed[] $column Column definition.
377
     *
378
     * @return string
379
     *
380
     * @throws InvalidArgumentException
381
     */
382 1237
    public function getDefaultConstraintDeclarationSQL($table, array $column)
383
    {
384 1237
        if (! isset($column['default'])) {
385
            throw new InvalidArgumentException("Incomplete column definition. 'default' required.");
386
        }
387
388 1237
        $columnName = new Identifier($column['name']);
389
390
        return ' CONSTRAINT ' .
391 1237
            $this->generateDefaultConstraintName($table, $column['name']) .
392 1237
            $this->getDefaultValueDeclarationSQL($column) .
393 1237
            ' FOR ' . $columnName->getQuotedName($this);
394
    }
395
396
    /**
397
     * {@inheritDoc}
398
     */
399 162
    public function getUniqueConstraintDeclarationSQL($name, Index $index)
400
    {
401 162
        $constraint = parent::getUniqueConstraintDeclarationSQL($name, $index);
402
403 162
        $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index);
404
405 162
        return $constraint;
406
    }
407
408
    /**
409
     * {@inheritDoc}
410
     */
411 666
    public function getCreateIndexSQL(Index $index, $table)
412
    {
413 666
        $constraint = parent::getCreateIndexSQL($index, $table);
414
415 666
        if ($index->isUnique() && ! $index->isPrimary()) {
416 168
            $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index);
417
        }
418
419 666
        return $constraint;
420
    }
421
422
    /**
423
     * {@inheritDoc}
424
     */
425 666
    protected function getCreateIndexSQLFlags(Index $index)
426
    {
427 666
        $type = '';
428 666
        if ($index->isUnique()) {
429 168
            $type .= 'UNIQUE ';
430
        }
431
432 666
        if ($index->hasFlag('clustered')) {
433 81
            $type .= 'CLUSTERED ';
434 585
        } elseif ($index->hasFlag('nonclustered')) {
435
            $type .= 'NONCLUSTERED ';
436
        }
437
438 666
        return $type;
439
    }
440
441
    /**
442
     * Extend unique key constraint with required filters
443
     *
444
     * @param string $sql
445
     *
446
     * @return string
447
     */
448 330
    private function _appendUniqueConstraintDefinition($sql, Index $index)
449
    {
450 330
        $fields = [];
451
452 330
        foreach ($index->getQuotedColumns($this) as $field) {
453 330
            $fields[] = $field . ' IS NOT NULL';
454
        }
455
456 330
        return $sql . ' WHERE ' . implode(' AND ', $fields);
457
    }
458
459
    /**
460
     * {@inheritDoc}
461
     */
462 1818
    public function getAlterTableSQL(TableDiff $diff)
463
    {
464 1818
        $queryParts  = [];
465 1818
        $sql         = [];
466 1818
        $columnSql   = [];
467 1818
        $commentsSql = [];
468
469 1818
        foreach ($diff->addedColumns as $column) {
470 816
            if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) {
471
                continue;
472
            }
473
474 816
            $columnDef    = $column->toArray();
475 816
            $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);
476
477 816
            if (isset($columnDef['default'])) {
478 326
                $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
479
            }
480
481 816
            $comment = $this->getColumnComment($column);
482
483 816
            if (empty($comment) && ! is_numeric($comment)) {
484 654
                continue;
485
            }
486
487 245
            $commentsSql[] = $this->getCreateColumnCommentSQL(
488 245
                $diff->name,
489 245
                $column->getQuotedName($this),
490 245
                $comment
491
            );
492
        }
493
494 1818
        foreach ($diff->removedColumns as $column) {
495 656
            if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) {
496
                continue;
497
            }
498
499 656
            $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this);
500
        }
501
502 1818
        foreach ($diff->changedColumns as $columnDiff) {
503 1162
            if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) {
504
                continue;
505
            }
506
507 1162
            $column     = $columnDiff->column;
508 1162
            $comment    = $this->getColumnComment($column);
509 1162
            $hasComment = ! empty($comment) || is_numeric($comment);
510
511 1162
            if ($columnDiff->fromColumn instanceof Column) {
512 757
                $fromComment    = $this->getColumnComment($columnDiff->fromColumn);
513 757
                $hasFromComment = ! empty($fromComment) || is_numeric($fromComment);
514
515 757
                if ($hasFromComment && $hasComment && $fromComment !== $comment) {
516 170
                    $commentsSql[] = $this->getAlterColumnCommentSQL(
517 170
                        $diff->name,
518 170
                        $column->getQuotedName($this),
519 170
                        $comment
520
                    );
521 676
                } elseif ($hasFromComment && ! $hasComment) {
522 178
                    $commentsSql[] = $this->getDropColumnCommentSQL($diff->name, $column->getQuotedName($this));
523 593
                } elseif ($hasComment) {
524 101
                    $commentsSql[] = $this->getCreateColumnCommentSQL(
525 101
                        $diff->name,
526 101
                        $column->getQuotedName($this),
527 101
                        $comment
528
                    );
529
                }
530
            }
531
532
            // Do not add query part if only comment has changed.
533 1162
            if ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1) {
534 346
                continue;
535
            }
536
537 899
            $requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff);
538
539 899
            if ($requireDropDefaultConstraint) {
540 411
                $queryParts[] = $this->getAlterTableDropDefaultConstraintClause(
541 411
                    $diff->name,
542 411
                    $columnDiff->oldColumnName
543
                );
544
            }
545
546 899
            $columnDef = $column->toArray();
547
548 899
            $queryParts[] = 'ALTER COLUMN ' .
549 899
                    $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef);
550
551 899
            if (! isset($columnDef['default']) || (! $requireDropDefaultConstraint && ! $columnDiff->hasChanged('default'))) {
552 411
                continue;
553
            }
554
555 492
            $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
556
        }
557
558 1818
        foreach ($diff->renamedColumns as $oldColumnName => $column) {
559 411
            if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) {
560
                continue;
561
            }
562
563 411
            $oldColumnName = new Identifier($oldColumnName);
564
565 411
            $sql[] = "sp_RENAME '" .
566 411
                $diff->getName($this)->getQuotedName($this) . '.' . $oldColumnName->getQuotedName($this) .
567 411
                "', '" . $column->getQuotedName($this) . "', 'COLUMN'";
568
569
            // Recreate default constraint with new column name if necessary (for future reference).
570 411
            if ($column->getDefault() === null) {
571 328
                continue;
572
            }
573
574 83
            $queryParts[] = $this->getAlterTableDropDefaultConstraintClause(
575 83
                $diff->name,
576 83
                $oldColumnName->getQuotedName($this)
577
            );
578 83
            $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column);
579
        }
580
581 1818
        $tableSql = [];
582
583 1818
        if ($this->onSchemaAlterTable($diff, $tableSql)) {
584
            return array_merge($tableSql, $columnSql);
585
        }
586
587 1818
        foreach ($queryParts as $query) {
588 1146
            $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query;
589
        }
590
591 1818
        $sql = array_merge($sql, $commentsSql);
592
593 1818
        $newName = $diff->getNewName();
594
595 1818
        if ($newName !== false) {
596 164
            $sql[] = "sp_RENAME '" . $diff->getName($this)->getQuotedName($this) . "', '" . $newName->getName() . "'";
597
598
            /**
599
             * Rename table's default constraints names
600
             * to match the new table name.
601
             * This is necessary to ensure that the default
602
             * constraints can be referenced in future table
603
             * alterations as the table name is encoded in
604
             * default constraints' names.
605
             */
606 164
            $sql[] = "DECLARE @sql NVARCHAR(MAX) = N''; " .
607
                "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " .
608 164
                "+ REPLACE(dc.name, '" . $this->generateIdentifierName($diff->name) . "', " .
609 164
                "'" . $this->generateIdentifierName($newName->getName()) . "') + ''', ''OBJECT'';' " .
610 164
                'FROM sys.default_constraints dc ' .
611 164
                'JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id ' .
612 164
                "WHERE tbl.name = '" . $newName->getName() . "';" .
613 164
                'EXEC sp_executesql @sql';
614
        }
615
616 1818
        $sql = array_merge(
617 1818
            $this->getPreAlterTableIndexForeignKeySQL($diff),
618 1818
            $sql,
619 1818
            $this->getPostAlterTableIndexForeignKeySQL($diff)
620
        );
621
622 1818
        return array_merge($sql, $tableSql, $columnSql);
623
    }
624
625
    /**
626
     * Returns the SQL clause for adding a default constraint in an ALTER TABLE statement.
627
     *
628
     * @param string $tableName The name of the table to generate the clause for.
629
     * @param Column $column    The column to generate the clause for.
630
     *
631
     * @return string
632
     */
633 573
    private function getAlterTableAddDefaultConstraintClause($tableName, Column $column)
634
    {
635 573
        $columnDef         = $column->toArray();
636 573
        $columnDef['name'] = $column->getQuotedName($this);
637
638 573
        return 'ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $columnDef);
639
    }
640
641
    /**
642
     * Returns the SQL clause for dropping an existing default constraint in an ALTER TABLE statement.
643
     *
644
     * @param string $tableName  The name of the table to generate the clause for.
645
     * @param string $columnName The name of the column to generate the clause for.
646
     *
647
     * @return string
648
     */
649 492
    private function getAlterTableDropDefaultConstraintClause($tableName, $columnName)
650
    {
651 492
        return 'DROP CONSTRAINT ' . $this->generateDefaultConstraintName($tableName, $columnName);
652
    }
653
654
    /**
655
     * Checks whether a column alteration requires dropping its default constraint first.
656
     *
657
     * Different to other database vendors SQL Server implements column default values
658
     * as constraints and therefore changes in a column's default value as well as changes
659
     * in a column's type require dropping the default constraint first before being to
660
     * alter the particular column to the new definition.
661
     *
662
     * @param ColumnDiff $columnDiff The column diff to evaluate.
663
     *
664
     * @return bool True if the column alteration requires dropping its default constraint first, false otherwise.
665
     */
666 899
    private function alterColumnRequiresDropDefaultConstraint(ColumnDiff $columnDiff)
667
    {
668
        // We can only decide whether to drop an existing default constraint
669
        // if we know the original default value.
670 899
        if (! $columnDiff->fromColumn instanceof Column) {
671 324
            return false;
672
        }
673
674
        // We only need to drop an existing default constraint if we know the
675
        // column was defined with a default value before.
676 575
        if ($columnDiff->fromColumn->getDefault() === null) {
677 166
            return false;
678
        }
679
680
        // We need to drop an existing default constraint if the column was
681
        // defined with a default value before and it has changed.
682 411
        if ($columnDiff->hasChanged('default')) {
683 328
            return true;
684
        }
685
686
        // We need to drop an existing default constraint if the column was
687
        // defined with a default value before and the native column type has changed.
688 85
        return $columnDiff->hasChanged('type') || $columnDiff->hasChanged('fixed');
689
    }
690
691
    /**
692
     * Returns the SQL statement for altering a column comment.
693
     *
694
     * SQL Server does not support native column comments,
695
     * therefore the extended properties functionality is used
696
     * as a workaround to store them.
697
     * The property name used to store column comments is "MS_Description"
698
     * which provides compatibility with SQL Server Management Studio,
699
     * as column comments are stored in the same property there when
700
     * specifying a column's "Description" attribute.
701
     *
702
     * @param string      $tableName  The quoted table name to which the column belongs.
703
     * @param string      $columnName The quoted column name to alter the comment for.
704
     * @param string|null $comment    The column's comment.
705
     *
706
     * @return string
707
     */
708 170
    protected function getAlterColumnCommentSQL($tableName, $columnName, $comment)
709
    {
710 170
        if (strpos($tableName, '.') !== false) {
711 81
            [$schemaSQL, $tableSQL] = explode('.', $tableName);
712 81
            $schemaSQL              = $this->quoteStringLiteral($schemaSQL);
713 81
            $tableSQL               = $this->quoteStringLiteral($tableSQL);
714
        } else {
715 89
            $schemaSQL = "'dbo'";
716 89
            $tableSQL  = $this->quoteStringLiteral($tableName);
717
        }
718
719 170
        return $this->getUpdateExtendedPropertySQL(
720 170
            'MS_Description',
721 170
            $comment,
722 170
            'SCHEMA',
723 170
            $schemaSQL,
724 170
            'TABLE',
725 170
            $tableSQL,
726 170
            'COLUMN',
727 170
            $columnName
728
        );
729
    }
730
731
    /**
732
     * Returns the SQL statement for dropping a column comment.
733
     *
734
     * SQL Server does not support native column comments,
735
     * therefore the extended properties functionality is used
736
     * as a workaround to store them.
737
     * The property name used to store column comments is "MS_Description"
738
     * which provides compatibility with SQL Server Management Studio,
739
     * as column comments are stored in the same property there when
740
     * specifying a column's "Description" attribute.
741
     *
742
     * @param string $tableName  The quoted table name to which the column belongs.
743
     * @param string $columnName The quoted column name to drop the comment for.
744
     *
745
     * @return string
746
     */
747 178
    protected function getDropColumnCommentSQL($tableName, $columnName)
748
    {
749 178
        if (strpos($tableName, '.') !== false) {
750 81
            [$schemaSQL, $tableSQL] = explode('.', $tableName);
751 81
            $schemaSQL              = $this->quoteStringLiteral($schemaSQL);
752 81
            $tableSQL               = $this->quoteStringLiteral($tableSQL);
753
        } else {
754 97
            $schemaSQL = "'dbo'";
755 97
            $tableSQL  = $this->quoteStringLiteral($tableName);
756
        }
757
758 178
        return $this->getDropExtendedPropertySQL(
759 178
            'MS_Description',
760 178
            'SCHEMA',
761 178
            $schemaSQL,
762 178
            'TABLE',
763 178
            $tableSQL,
764 178
            'COLUMN',
765 178
            $columnName
766
        );
767
    }
768
769
    /**
770
     * {@inheritdoc}
771
     */
772 409
    protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName)
773
    {
774 409
        return [sprintf(
775 409
            "EXEC sp_RENAME N'%s.%s', N'%s', N'INDEX'",
776 409
            $tableName,
777 409
            $oldIndexName,
778 409
            $index->getQuotedName($this)
779
        ),
780
        ];
781
    }
782
783
    /**
784
     * Returns the SQL statement for adding an extended property to a database object.
785
     *
786
     * @link http://msdn.microsoft.com/en-us/library/ms180047%28v=sql.90%29.aspx
787
     *
788
     * @param string      $name       The name of the property to add.
789
     * @param string|null $value      The value of the property to add.
790
     * @param string|null $level0Type The type of the object at level 0 the property belongs to.
791
     * @param string|null $level0Name The name of the object at level 0 the property belongs to.
792
     * @param string|null $level1Type The type of the object at level 1 the property belongs to.
793
     * @param string|null $level1Name The name of the object at level 1 the property belongs to.
794
     * @param string|null $level2Type The type of the object at level 2 the property belongs to.
795
     * @param string|null $level2Name The name of the object at level 2 the property belongs to.
796
     *
797
     * @return string
798
     */
799 631
    public function getAddExtendedPropertySQL(
800
        $name,
801
        $value = null,
802
        $level0Type = null,
803
        $level0Name = null,
804
        $level1Type = null,
805
        $level1Name = null,
806
        $level2Type = null,
807
        $level2Name = null
808
    ) {
809
        return 'EXEC sp_addextendedproperty ' .
810 631
            'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value) . ', ' .
811 631
            'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' .
812 631
            'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' .
813 631
            'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name;
814
    }
815
816
    /**
817
     * Returns the SQL statement for dropping an extended property from a database object.
818
     *
819
     * @link http://technet.microsoft.com/en-gb/library/ms178595%28v=sql.90%29.aspx
820
     *
821
     * @param string      $name       The name of the property to drop.
822
     * @param string|null $level0Type The type of the object at level 0 the property belongs to.
823
     * @param string|null $level0Name The name of the object at level 0 the property belongs to.
824
     * @param string|null $level1Type The type of the object at level 1 the property belongs to.
825
     * @param string|null $level1Name The name of the object at level 1 the property belongs to.
826
     * @param string|null $level2Type The type of the object at level 2 the property belongs to.
827
     * @param string|null $level2Name The name of the object at level 2 the property belongs to.
828
     *
829
     * @return string
830
     */
831 178
    public function getDropExtendedPropertySQL(
832
        $name,
833
        $level0Type = null,
834
        $level0Name = null,
835
        $level1Type = null,
836
        $level1Name = null,
837
        $level2Type = null,
838
        $level2Name = null
839
    ) {
840
        return 'EXEC sp_dropextendedproperty ' .
841 178
            'N' . $this->quoteStringLiteral($name) . ', ' .
842 178
            'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' .
843 178
            'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' .
844 178
            'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name;
845
    }
846
847
    /**
848
     * Returns the SQL statement for updating an extended property of a database object.
849
     *
850
     * @link http://msdn.microsoft.com/en-us/library/ms186885%28v=sql.90%29.aspx
851
     *
852
     * @param string      $name       The name of the property to update.
853
     * @param string|null $value      The value of the property to update.
854
     * @param string|null $level0Type The type of the object at level 0 the property belongs to.
855
     * @param string|null $level0Name The name of the object at level 0 the property belongs to.
856
     * @param string|null $level1Type The type of the object at level 1 the property belongs to.
857
     * @param string|null $level1Name The name of the object at level 1 the property belongs to.
858
     * @param string|null $level2Type The type of the object at level 2 the property belongs to.
859
     * @param string|null $level2Name The name of the object at level 2 the property belongs to.
860
     *
861
     * @return string
862
     */
863 170
    public function getUpdateExtendedPropertySQL(
864
        $name,
865
        $value = null,
866
        $level0Type = null,
867
        $level0Name = null,
868
        $level1Type = null,
869
        $level1Name = null,
870
        $level2Type = null,
871
        $level2Name = null
872
    ) {
873
        return 'EXEC sp_updateextendedproperty ' .
874 170
            'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral((string) $value) . ', ' .
875 170
            'N' . $this->quoteStringLiteral((string) $level0Type) . ', ' . $level0Name . ', ' .
876 170
            'N' . $this->quoteStringLiteral((string) $level1Type) . ', ' . $level1Name . ', ' .
877 170
            'N' . $this->quoteStringLiteral((string) $level2Type) . ', ' . $level2Name;
878
    }
879
880
    /**
881
     * {@inheritDoc}
882
     */
883 2
    public function getEmptyIdentityInsertSQL($quotedTableName, $quotedIdentifierColumnName)
884
    {
885 2
        return 'INSERT INTO ' . $quotedTableName . ' DEFAULT VALUES';
886
    }
887
888
    /**
889
     * {@inheritDoc}
890
     */
891
    public function getListTablesSQL()
892
    {
893
        // "sysdiagrams" table must be ignored as it's internal SQL Server table for Database Diagrams
894
        // Category 2 must be ignored as it is "MS SQL Server 'pseudo-system' object[s]" for replication
895
        return "SELECT name FROM sysobjects WHERE type = 'U' AND name != 'sysdiagrams' AND category != 2 ORDER BY name";
896
    }
897
898
    /**
899
     * {@inheritDoc}
900
     */
901 238
    public function getListTableColumnsSQL($table, $database = null)
902
    {
903
        return "SELECT    col.name,
904
                          type.name AS type,
905
                          col.max_length AS length,
906
                          ~col.is_nullable AS notnull,
907
                          def.definition AS [default],
908
                          col.scale,
909
                          col.precision,
910
                          col.is_identity AS autoincrement,
911
                          col.collation_name AS collation,
912
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
913
                FROM      sys.columns AS col
914
                JOIN      sys.types AS type
915
                ON        col.user_type_id = type.user_type_id
916
                JOIN      sys.objects AS obj
917
                ON        col.object_id = obj.object_id
918
                JOIN      sys.schemas AS scm
919
                ON        obj.schema_id = scm.schema_id
920
                LEFT JOIN sys.default_constraints def
921
                ON        col.default_object_id = def.object_id
922
                AND       col.object_id = def.parent_object_id
923
                LEFT JOIN sys.extended_properties AS prop
924
                ON        obj.object_id = prop.major_id
925
                AND       col.column_id = prop.minor_id
926
                AND       prop.name = 'MS_Description'
927
                WHERE     obj.type = 'U'
928 238
                AND       " . $this->getTableWhereClause($table, 'scm.name', 'obj.name');
929
    }
930
931
    /**
932
     * {@inheritDoc}
933
     */
934 216
    public function getListTableForeignKeysSQL($table, $database = null)
0 ignored issues
show
Unused Code introduced by Juozas Kaziukenas
The parameter $database is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

934
    public function getListTableForeignKeysSQL($table, /** @scrutinizer ignore-unused */ $database = null)

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
935
    {
936
        return 'SELECT f.name AS ForeignKey,
937
                SCHEMA_NAME (f.SCHEMA_ID) AS SchemaName,
938
                OBJECT_NAME (f.parent_object_id) AS TableName,
939
                COL_NAME (fc.parent_object_id,fc.parent_column_id) AS ColumnName,
940
                SCHEMA_NAME (o.SCHEMA_ID) ReferenceSchemaName,
941
                OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
942
                COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName,
943
                f.delete_referential_action_desc,
944
                f.update_referential_action_desc
945
                FROM sys.foreign_keys AS f
946
                INNER JOIN sys.foreign_key_columns AS fc
947
                INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
948
                ON f.OBJECT_ID = fc.constraint_object_id
949
                WHERE ' .
950 216
                $this->getTableWhereClause($table, 'SCHEMA_NAME (f.schema_id)', 'OBJECT_NAME (f.parent_object_id)');
951
    }
952
953
    /**
954
     * {@inheritDoc}
955
     */
956 222
    public function getListTableIndexesSQL($table, $currentDatabase = null)
957
    {
958
        return "SELECT idx.name AS key_name,
959
                       col.name AS column_name,
960
                       ~idx.is_unique AS non_unique,
961
                       idx.is_primary_key AS [primary],
962
                       CASE idx.type
963
                           WHEN '1' THEN 'clustered'
964
                           WHEN '2' THEN 'nonclustered'
965
                           ELSE NULL
966
                       END AS flags
967
                FROM sys.tables AS tbl
968
                JOIN sys.schemas AS scm ON tbl.schema_id = scm.schema_id
969
                JOIN sys.indexes AS idx ON tbl.object_id = idx.object_id
970
                JOIN sys.index_columns AS idxcol ON idx.object_id = idxcol.object_id AND idx.index_id = idxcol.index_id
971
                JOIN sys.columns AS col ON idxcol.object_id = col.object_id AND idxcol.column_id = col.column_id
972 222
                WHERE " . $this->getTableWhereClause($table, 'scm.name', 'tbl.name') . '
973
                ORDER BY idx.index_id ASC, idxcol.key_ordinal ASC';
974
    }
975
976
    /**
977
     * {@inheritDoc}
978
     */
979 2
    public function getCreateViewSQL($name, $sql)
980
    {
981 2
        return 'CREATE VIEW ' . $name . ' AS ' . $sql;
982
    }
983
984
    /**
985
     * {@inheritDoc}
986
     */
987 2
    public function getListViewsSQL($database)
988
    {
989 2
        return "SELECT name FROM sysobjects WHERE type = 'V' ORDER BY name";
990
    }
991
992
    /**
993
     * Returns the where clause to filter schema and table name in a query.
994
     *
995
     * @param string $table        The full qualified name of the table.
996
     * @param string $schemaColumn The name of the column to compare the schema to in the where clause.
997
     * @param string $tableColumn  The name of the column to compare the table to in the where clause.
998
     *
999
     * @return string
1000
     */
1001 576
    private function getTableWhereClause($table, $schemaColumn, $tableColumn)
1002
    {
1003 576
        if (strpos($table, '.') !== false) {
1004 245
            [$schema, $table] = explode('.', $table);
1005 245
            $schema           = $this->quoteStringLiteral($schema);
1006 245
            $table            = $this->quoteStringLiteral($table);
1007
        } else {
1008 333
            $schema = 'SCHEMA_NAME()';
1009 333
            $table  = $this->quoteStringLiteral($table);
1010
        }
1011
1012 576
        return sprintf('(%s = %s AND %s = %s)', $tableColumn, $table, $schemaColumn, $schema);
1013
    }
1014
1015
    /**
1016
     * {@inheritDoc}
1017
     */
1018 2
    public function getDropViewSQL($name)
1019
    {
1020 2
        return 'DROP VIEW ' . $name;
1021
    }
1022
1023
    /**
1024
     * {@inheritDoc}
1025
     *
1026
     * @deprecated Use application-generated UUIDs instead
1027
     */
1028
    public function getGuidExpression()
1029
    {
1030
        return 'NEWID()';
1031
    }
1032
1033
    /**
1034
     * {@inheritDoc}
1035
     */
1036 2
    public function getLocateExpression($str, $substr, $startPos = false)
1037
    {
1038 2
        if ($startPos === false) {
1039 2
            return 'CHARINDEX(' . $substr . ', ' . $str . ')';
1040
        }
1041
1042 2
        return 'CHARINDEX(' . $substr . ', ' . $str . ', ' . $startPos . ')';
1043
    }
1044
1045
    /**
1046
     * {@inheritDoc}
1047
     */
1048
    public function getModExpression($expression1, $expression2)
1049
    {
1050
        return $expression1 . ' % ' . $expression2;
1051
    }
1052
1053
    /**
1054
     * {@inheritDoc}
1055
     */
1056 72
    public function getTrimExpression($str, $pos = TrimMode::UNSPECIFIED, $char = false)
1057
    {
1058 72
        if (! $char) {
1059 16
            switch ($pos) {
1060
                case TrimMode::LEADING:
1061 4
                    $trimFn = 'LTRIM';
1062 4
                    break;
1063
1064
                case TrimMode::TRAILING:
1065 4
                    $trimFn = 'RTRIM';
1066 4
                    break;
1067
1068
                default:
1069 8
                    return 'LTRIM(RTRIM(' . $str . '))';
1070
            }
1071
1072 8
            return $trimFn . '(' . $str . ')';
1073
        }
1074
1075
        /** Original query used to get those expressions
1076
          declare @c varchar(100) = 'xxxBarxxx', @trim_char char(1) = 'x';
1077
          declare @pat varchar(10) = '%[^' + @trim_char + ']%';
1078
          select @c as string
1079
          , @trim_char as trim_char
1080
          , stuff(@c, 1, patindex(@pat, @c) - 1, null) as trim_leading
1081
          , reverse(stuff(reverse(@c), 1, patindex(@pat, reverse(@c)) - 1, null)) as trim_trailing
1082
          , 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;
1083
         */
1084 56
        $pattern = "'%[^' + " . $char . " + ']%'";
1085
1086 56
        if ($pos === TrimMode::LEADING) {
1087 14
            return 'stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)';
1088
        }
1089
1090 42
        if ($pos === TrimMode::TRAILING) {
1091 14
            return 'reverse(stuff(reverse(' . $str . '), 1, patindex(' . $pattern . ', reverse(' . $str . ')) - 1, null))';
1092
        }
1093
1094 28
        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))';
1095
    }
1096
1097
    /**
1098
     * {@inheritDoc}
1099
     */
1100 81
    public function getConcatExpression()
1101
    {
1102 81
        $args = func_get_args();
1103
1104 81
        return '(' . implode(' + ', $args) . ')';
1105
    }
1106
1107
    /**
1108
     * {@inheritDoc}
1109
     */
1110 85
    public function getListDatabasesSQL()
1111
    {
1112 85
        return 'SELECT * FROM sys.databases';
1113
    }
1114
1115
    /**
1116
     * {@inheritDoc}
1117
     */
1118 4
    public function getListNamespacesSQL()
1119
    {
1120 4
        return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')";
1121
    }
1122
1123
    /**
1124
     * {@inheritDoc}
1125
     */
1126
    public function getSubstringExpression($value, $from, $length = null)
1127
    {
1128
        if ($length !== null) {
1129
            return 'SUBSTRING(' . $value . ', ' . $from . ', ' . $length . ')';
1130
        }
1131
1132
        return 'SUBSTRING(' . $value . ', ' . $from . ', LEN(' . $value . ') - ' . $from . ' + 1)';
1133
    }
1134
1135
    /**
1136
     * {@inheritDoc}
1137
     */
1138
    public function getLengthExpression($column)
1139
    {
1140
        return 'LEN(' . $column . ')';
1141
    }
1142
1143
    /**
1144
     * {@inheritDoc}
1145
     */
1146 81
    public function getSetTransactionIsolationSQL($level)
1147
    {
1148 81
        return 'SET TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
1149
    }
1150
1151
    /**
1152
     * {@inheritDoc}
1153
     */
1154 1412
    public function getIntegerTypeDeclarationSQL(array $field)
1155
    {
1156 1412
        return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
1157
    }
1158
1159
    /**
1160
     * {@inheritDoc}
1161
     */
1162 30
    public function getBigIntTypeDeclarationSQL(array $field)
1163
    {
1164 30
        return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
1165
    }
1166
1167
    /**
1168
     * {@inheritDoc}
1169
     */
1170 2
    public function getSmallIntTypeDeclarationSQL(array $field)
1171
    {
1172 2
        return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field);
1173
    }