summaryrefslogtreecommitdiff
path: root/vendor/doctrine/dbal/src/Platforms/SQLServerPlatform.php
diff options
context:
space:
mode:
Diffstat (limited to 'vendor/doctrine/dbal/src/Platforms/SQLServerPlatform.php')
-rw-r--r--vendor/doctrine/dbal/src/Platforms/SQLServerPlatform.php1223
1 files changed, 1223 insertions, 0 deletions
diff --git a/vendor/doctrine/dbal/src/Platforms/SQLServerPlatform.php b/vendor/doctrine/dbal/src/Platforms/SQLServerPlatform.php
new file mode 100644
index 0000000..7a10a32
--- /dev/null
+++ b/vendor/doctrine/dbal/src/Platforms/SQLServerPlatform.php
@@ -0,0 +1,1223 @@
1<?php
2
3declare(strict_types=1);
4
5namespace Doctrine\DBAL\Platforms;
6
7use Doctrine\DBAL\Connection;
8use Doctrine\DBAL\Exception\InvalidColumnType\ColumnLengthRequired;
9use Doctrine\DBAL\LockMode;
10use Doctrine\DBAL\Platforms\Keywords\KeywordList;
11use Doctrine\DBAL\Platforms\Keywords\SQLServerKeywords;
12use Doctrine\DBAL\Platforms\SQLServer\SQL\Builder\SQLServerSelectSQLBuilder;
13use Doctrine\DBAL\Schema\Column;
14use Doctrine\DBAL\Schema\ColumnDiff;
15use Doctrine\DBAL\Schema\Identifier;
16use Doctrine\DBAL\Schema\Index;
17use Doctrine\DBAL\Schema\Sequence;
18use Doctrine\DBAL\Schema\SQLServerSchemaManager;
19use Doctrine\DBAL\Schema\TableDiff;
20use Doctrine\DBAL\SQL\Builder\SelectSQLBuilder;
21use Doctrine\DBAL\TransactionIsolationLevel;
22use Doctrine\DBAL\Types\Types;
23use InvalidArgumentException;
24
25use function array_merge;
26use function array_unique;
27use function array_values;
28use function explode;
29use function implode;
30use function is_array;
31use function is_bool;
32use function is_numeric;
33use function preg_match;
34use function preg_match_all;
35use function sprintf;
36use function str_contains;
37use function str_ends_with;
38use function str_replace;
39use function str_starts_with;
40use function strtoupper;
41use function substr;
42use function substr_count;
43
44use const PREG_OFFSET_CAPTURE;
45
46/**
47 * Provides the behavior, features and SQL dialect of the Microsoft SQL Server database platform
48 * of the oldest supported version.
49 */
50class SQLServerPlatform extends AbstractPlatform
51{
52 /** @internal Should be used only from within the {@see AbstractSchemaManager} class hierarchy. */
53 public const OPTION_DEFAULT_CONSTRAINT_NAME = 'default_constraint_name';
54
55 public function createSelectSQLBuilder(): SelectSQLBuilder
56 {
57 return new SQLServerSelectSQLBuilder($this);
58 }
59
60 public function getCurrentDateSQL(): string
61 {
62 return $this->getConvertExpression('date', 'GETDATE()');
63 }
64
65 public function getCurrentTimeSQL(): string
66 {
67 return $this->getConvertExpression('time', 'GETDATE()');
68 }
69
70 /**
71 * Returns an expression that converts an expression of one data type to another.
72 *
73 * @param string $dataType The target native data type. Alias data types cannot be used.
74 * @param string $expression The SQL expression to convert.
75 */
76 private function getConvertExpression(string $dataType, string $expression): string
77 {
78 return sprintf('CONVERT(%s, %s)', $dataType, $expression);
79 }
80
81 protected function getDateArithmeticIntervalExpression(
82 string $date,
83 string $operator,
84 string $interval,
85 DateIntervalUnit $unit,
86 ): string {
87 $factorClause = '';
88
89 if ($operator === '-') {
90 $factorClause = '-1 * ';
91 }
92
93 return 'DATEADD(' . $unit->value . ', ' . $factorClause . $interval . ', ' . $date . ')';
94 }
95
96 public function getDateDiffExpression(string $date1, string $date2): string
97 {
98 return 'DATEDIFF(day, ' . $date2 . ',' . $date1 . ')';
99 }
100
101 /**
102 * {@inheritDoc}
103 *
104 * Microsoft SQL Server supports this through AUTO_INCREMENT columns.
105 */
106 public function supportsIdentityColumns(): bool
107 {
108 return true;
109 }
110
111 public function supportsReleaseSavepoints(): bool
112 {
113 return false;
114 }
115
116 public function supportsSchemas(): bool
117 {
118 return true;
119 }
120
121 /** @internal The method should be only used from within the {@see AbstractPlatform} class hierarchy. */
122 public function supportsColumnCollation(): bool
123 {
124 return true;
125 }
126
127 public function supportsSequences(): bool
128 {
129 return true;
130 }
131
132 public function getAlterSequenceSQL(Sequence $sequence): string
133 {
134 return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) .
135 ' INCREMENT BY ' . $sequence->getAllocationSize();
136 }
137
138 public function getCreateSequenceSQL(Sequence $sequence): string
139 {
140 return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) .
141 ' START WITH ' . $sequence->getInitialValue() .
142 ' INCREMENT BY ' . $sequence->getAllocationSize() .
143 ' MINVALUE ' . $sequence->getInitialValue();
144 }
145
146 /** @internal The method should be only used from within the {@see AbstractSchemaManager} class hierarchy. */
147 public function getListSequencesSQL(string $database): string
148 {
149 return 'SELECT seq.name,
150 CAST(
151 seq.increment AS VARCHAR(MAX)
152 ) AS increment, -- CAST avoids driver error for sql_variant type
153 CAST(
154 seq.start_value AS VARCHAR(MAX)
155 ) AS start_value -- CAST avoids driver error for sql_variant type
156 FROM sys.sequences AS seq';
157 }
158
159 public function getSequenceNextValSQL(string $sequence): string
160 {
161 return 'SELECT NEXT VALUE FOR ' . $sequence;
162 }
163
164 public function getDropForeignKeySQL(string $foreignKey, string $table): string
165 {
166 return $this->getDropConstraintSQL($foreignKey, $table);
167 }
168
169 public function getDropIndexSQL(string $name, string $table): string
170 {
171 return 'DROP INDEX ' . $name . ' ON ' . $table;
172 }
173
174 /**
175 * {@inheritDoc}
176 */
177 protected function _getCreateTableSQL(string $name, array $columns, array $options = []): array
178 {
179 $defaultConstraintsSql = [];
180 $commentsSql = [];
181
182 $tableComment = $options['comment'] ?? null;
183 if ($tableComment !== null) {
184 $commentsSql[] = $this->getCommentOnTableSQL($name, $tableComment);
185 }
186
187 // @todo does other code breaks because of this?
188 // force primary keys to be not null
189 foreach ($columns as &$column) {
190 if (! empty($column['primary'])) {
191 $column['notnull'] = true;
192 }
193
194 // Build default constraints SQL statements.
195 if (isset($column['default'])) {
196 $defaultConstraintsSql[] = 'ALTER TABLE ' . $name .
197 ' ADD' . $this->getDefaultConstraintDeclarationSQL($column);
198 }
199
200 if (empty($column['comment']) && ! is_numeric($column['comment'])) {
201 continue;
202 }
203
204 $commentsSql[] = $this->getCreateColumnCommentSQL($name, $column['name'], $column['comment']);
205 }
206
207 $columnListSql = $this->getColumnDeclarationListSQL($columns);
208
209 if (isset($options['uniqueConstraints']) && ! empty($options['uniqueConstraints'])) {
210 foreach ($options['uniqueConstraints'] as $definition) {
211 $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($definition);
212 }
213 }
214
215 if (isset($options['primary']) && ! empty($options['primary'])) {
216 $flags = '';
217 if (isset($options['primary_index']) && $options['primary_index']->hasFlag('nonclustered')) {
218 $flags = ' NONCLUSTERED';
219 }
220
221 $columnListSql .= ', PRIMARY KEY' . $flags
222 . ' (' . implode(', ', array_unique(array_values($options['primary']))) . ')';
223 }
224
225 $query = 'CREATE TABLE ' . $name . ' (' . $columnListSql;
226
227 $check = $this->getCheckDeclarationSQL($columns);
228 if (! empty($check)) {
229 $query .= ', ' . $check;
230 }
231
232 $query .= ')';
233
234 $sql = [$query];
235
236 if (isset($options['indexes']) && ! empty($options['indexes'])) {
237 foreach ($options['indexes'] as $index) {
238 $sql[] = $this->getCreateIndexSQL($index, $name);
239 }
240 }
241
242 if (isset($options['foreignKeys'])) {
243 foreach ($options['foreignKeys'] as $definition) {
244 $sql[] = $this->getCreateForeignKeySQL($definition, $name);
245 }
246 }
247
248 return array_merge($sql, $commentsSql, $defaultConstraintsSql);
249 }
250
251 public function getCreatePrimaryKeySQL(Index $index, string $table): string
252 {
253 $sql = 'ALTER TABLE ' . $table . ' ADD PRIMARY KEY';
254
255 if ($index->hasFlag('nonclustered')) {
256 $sql .= ' NONCLUSTERED';
257 }
258
259 return $sql . ' (' . implode(', ', $index->getQuotedColumns($this)) . ')';
260 }
261
262 private function unquoteSingleIdentifier(string $possiblyQuotedName): string
263 {
264 return str_starts_with($possiblyQuotedName, '[') && str_ends_with($possiblyQuotedName, ']')
265 ? substr($possiblyQuotedName, 1, -1)
266 : $possiblyQuotedName;
267 }
268
269 /**
270 * Returns the SQL statement for creating a column comment.
271 *
272 * SQL Server does not support native column comments,
273 * therefore the extended properties functionality is used
274 * as a workaround to store them.
275 * The property name used to store column comments is "MS_Description"
276 * which provides compatibility with SQL Server Management Studio,
277 * as column comments are stored in the same property there when
278 * specifying a column's "Description" attribute.
279 *
280 * @param string $tableName The quoted table name to which the column belongs.
281 * @param string $columnName The quoted column name to create the comment for.
282 * @param string $comment The column's comment.
283 */
284 protected function getCreateColumnCommentSQL(string $tableName, string $columnName, string $comment): string
285 {
286 if (str_contains($tableName, '.')) {
287 [$schemaName, $tableName] = explode('.', $tableName);
288 } else {
289 $schemaName = 'dbo';
290 }
291
292 return $this->getAddExtendedPropertySQL(
293 'MS_Description',
294 $comment,
295 'SCHEMA',
296 $this->quoteStringLiteral($this->unquoteSingleIdentifier($schemaName)),
297 'TABLE',
298 $this->quoteStringLiteral($this->unquoteSingleIdentifier($tableName)),
299 'COLUMN',
300 $this->quoteStringLiteral($this->unquoteSingleIdentifier($columnName)),
301 );
302 }
303
304 /**
305 * Returns the SQL snippet for declaring a default constraint.
306 *
307 * @param mixed[] $column Column definition.
308 */
309 protected function getDefaultConstraintDeclarationSQL(array $column): string
310 {
311 if (! isset($column['default'])) {
312 throw new InvalidArgumentException('Incomplete column definition. "default" required.');
313 }
314
315 $columnName = new Identifier($column['name']);
316
317 return $this->getDefaultValueDeclarationSQL($column) . ' FOR ' . $columnName->getQuotedName($this);
318 }
319
320 public function getCreateIndexSQL(Index $index, string $table): string
321 {
322 $constraint = parent::getCreateIndexSQL($index, $table);
323
324 if ($index->isUnique() && ! $index->isPrimary()) {
325 $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index);
326 }
327
328 return $constraint;
329 }
330
331 protected function getCreateIndexSQLFlags(Index $index): string
332 {
333 $type = '';
334 if ($index->isUnique()) {
335 $type .= 'UNIQUE ';
336 }
337
338 if ($index->hasFlag('clustered')) {
339 $type .= 'CLUSTERED ';
340 } elseif ($index->hasFlag('nonclustered')) {
341 $type .= 'NONCLUSTERED ';
342 }
343
344 return $type;
345 }
346
347 /**
348 * Extend unique key constraint with required filters
349 */
350 private function _appendUniqueConstraintDefinition(string $sql, Index $index): string
351 {
352 $fields = [];
353
354 foreach ($index->getQuotedColumns($this) as $field) {
355 $fields[] = $field . ' IS NOT NULL';
356 }
357
358 return $sql . ' WHERE ' . implode(' AND ', $fields);
359 }
360
361 /**
362 * {@inheritDoc}
363 */
364 public function getAlterTableSQL(TableDiff $diff): array
365 {
366 $queryParts = [];
367 $sql = [];
368 $columnSql = [];
369 $commentsSql = [];
370
371 $table = $diff->getOldTable();
372
373 $tableName = $table->getName();
374
375 foreach ($diff->getAddedColumns() as $column) {
376 $columnProperties = $column->toArray();
377
378 $addColumnSql = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnProperties);
379
380 if (isset($columnProperties['default'])) {
381 $addColumnSql .= $this->getDefaultValueDeclarationSQL($columnProperties);
382 }
383
384 $queryParts[] = $addColumnSql;
385
386 $comment = $column->getComment();
387
388 if ($comment === '') {
389 continue;
390 }
391
392 $commentsSql[] = $this->getCreateColumnCommentSQL(
393 $tableName,
394 $column->getQuotedName($this),
395 $comment,
396 );
397 }
398
399 foreach ($diff->getDroppedColumns() as $column) {
400 if ($column->getDefault() !== null) {
401 $queryParts[] = $this->getAlterTableDropDefaultConstraintClause($column);
402 }
403
404 $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this);
405 }
406
407 foreach ($diff->getModifiedColumns() as $columnDiff) {
408 $newColumn = $columnDiff->getNewColumn();
409 $newComment = $newColumn->getComment();
410 $hasNewComment = $newComment !== '';
411
412 $oldColumn = $columnDiff->getOldColumn();
413 $oldComment = $oldColumn->getComment();
414 $hasOldComment = $oldComment !== '';
415
416 if ($hasOldComment && $hasNewComment && $newComment !== $oldComment) {
417 $commentsSql[] = $this->getAlterColumnCommentSQL(
418 $tableName,
419 $newColumn->getQuotedName($this),
420 $newComment,
421 );
422 } elseif ($hasOldComment && ! $hasNewComment) {
423 $commentsSql[] = $this->getDropColumnCommentSQL(
424 $tableName,
425 $newColumn->getQuotedName($this),
426 );
427 } elseif (! $hasOldComment && $hasNewComment) {
428 $commentsSql[] = $this->getCreateColumnCommentSQL(
429 $tableName,
430 $newColumn->getQuotedName($this),
431 $newComment,
432 );
433 }
434
435 $columnNameSQL = $newColumn->getQuotedName($this);
436
437 $oldDeclarationSQL = $this->getColumnDeclarationSQL($columnNameSQL, $oldColumn->toArray());
438 $newDeclarationSQL = $this->getColumnDeclarationSQL($columnNameSQL, $newColumn->toArray());
439
440 $declarationSQLChanged = $newDeclarationSQL !== $oldDeclarationSQL;
441 $defaultChanged = $columnDiff->hasDefaultChanged();
442
443 if (! $declarationSQLChanged && ! $defaultChanged) {
444 continue;
445 }
446
447 $requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff);
448
449 if ($requireDropDefaultConstraint) {
450 $queryParts[] = $this->getAlterTableDropDefaultConstraintClause($oldColumn);
451 }
452
453 if ($declarationSQLChanged) {
454 $queryParts[] = 'ALTER COLUMN ' . $newDeclarationSQL;
455 }
456
457 if (
458 $newColumn->getDefault() === null
459 || (! $requireDropDefaultConstraint && ! $defaultChanged)
460 ) {
461 continue;
462 }
463
464 $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($tableName, $newColumn);
465 }
466
467 $tableNameSQL = $table->getQuotedName($this);
468
469 foreach ($diff->getRenamedColumns() as $oldColumnName => $newColumn) {
470 $oldColumnName = new Identifier($oldColumnName);
471
472 $sql[] = sprintf(
473 "sp_rename '%s.%s', '%s', 'COLUMN'",
474 $tableNameSQL,
475 $oldColumnName->getQuotedName($this),
476 $newColumn->getQuotedName($this),
477 );
478 }
479
480 foreach ($queryParts as $query) {
481 $sql[] = 'ALTER TABLE ' . $tableNameSQL . ' ' . $query;
482 }
483
484 return array_merge(
485 $this->getPreAlterTableIndexForeignKeySQL($diff),
486 $sql,
487 $commentsSql,
488 $this->getPostAlterTableIndexForeignKeySQL($diff),
489 $columnSql,
490 );
491 }
492
493 public function getRenameTableSQL(string $oldName, string $newName): string
494 {
495 return sprintf(
496 'sp_rename %s, %s',
497 $this->quoteStringLiteral($oldName),
498 $this->quoteStringLiteral($newName),
499 );
500 }
501
502 /**
503 * Returns the SQL clause for adding a default constraint in an ALTER TABLE statement.
504 *
505 * @param string $tableName The name of the table to generate the clause for.
506 * @param Column $column The column to generate the clause for.
507 */
508 private function getAlterTableAddDefaultConstraintClause(string $tableName, Column $column): string
509 {
510 $columnDef = $column->toArray();
511 $columnDef['name'] = $column->getQuotedName($this);
512
513 return 'ADD' . $this->getDefaultConstraintDeclarationSQL($columnDef);
514 }
515
516 /**
517 * Returns the SQL clause for dropping an existing default constraint in an ALTER TABLE statement.
518 */
519 private function getAlterTableDropDefaultConstraintClause(Column $column): string
520 {
521 if (! $column->hasPlatformOption(self::OPTION_DEFAULT_CONSTRAINT_NAME)) {
522 throw new InvalidArgumentException(
523 'Column ' . $column->getName() . ' was not properly introspected as it has a default value'
524 . ' but does not have the default constraint name.',
525 );
526 }
527
528 return 'DROP CONSTRAINT ' . $this->quoteIdentifier(
529 $column->getPlatformOption(self::OPTION_DEFAULT_CONSTRAINT_NAME),
530 );
531 }
532
533 /**
534 * Checks whether a column alteration requires dropping its default constraint first.
535 *
536 * Different to other database vendors SQL Server implements column default values
537 * as constraints and therefore changes in a column's default value as well as changes
538 * in a column's type require dropping the default constraint first before being to
539 * alter the particular column to the new definition.
540 */
541 private function alterColumnRequiresDropDefaultConstraint(ColumnDiff $columnDiff): bool
542 {
543 // We only need to drop an existing default constraint if we know the
544 // column was defined with a default value before.
545 if ($columnDiff->getOldColumn()->getDefault() === null) {
546 return false;
547 }
548
549 // We need to drop an existing default constraint if the column was
550 // defined with a default value before and it has changed.
551 if ($columnDiff->hasDefaultChanged()) {
552 return true;
553 }
554
555 // We need to drop an existing default constraint if the column was
556 // defined with a default value before and the native column type has changed.
557 return $columnDiff->hasTypeChanged() || $columnDiff->hasFixedChanged();
558 }
559
560 /**
561 * Returns the SQL statement for altering a column comment.
562 *
563 * SQL Server does not support native column comments,
564 * therefore the extended properties functionality is used
565 * as a workaround to store them.
566 * The property name used to store column comments is "MS_Description"
567 * which provides compatibility with SQL Server Management Studio,
568 * as column comments are stored in the same property there when
569 * specifying a column's "Description" attribute.
570 *
571 * @param string $tableName The quoted table name to which the column belongs.
572 * @param string $columnName The quoted column name to alter the comment for.
573 * @param string $comment The column's comment.
574 */
575 protected function getAlterColumnCommentSQL(string $tableName, string $columnName, string $comment): string
576 {
577 if (str_contains($tableName, '.')) {
578 [$schemaName, $tableName] = explode('.', $tableName);
579 } else {
580 $schemaName = 'dbo';
581 }
582
583 return $this->getUpdateExtendedPropertySQL(
584 'MS_Description',
585 $comment,
586 'SCHEMA',
587 $this->quoteStringLiteral($this->unquoteSingleIdentifier($schemaName)),
588 'TABLE',
589 $this->quoteStringLiteral($this->unquoteSingleIdentifier($tableName)),
590 'COLUMN',
591 $this->quoteStringLiteral($this->unquoteSingleIdentifier($columnName)),
592 );
593 }
594
595 /**
596 * Returns the SQL statement for dropping a column comment.
597 *
598 * SQL Server does not support native column comments,
599 * therefore the extended properties functionality is used
600 * as a workaround to store them.
601 * The property name used to store column comments is "MS_Description"
602 * which provides compatibility with SQL Server Management Studio,
603 * as column comments are stored in the same property there when
604 * specifying a column's "Description" attribute.
605 *
606 * @param string $tableName The quoted table name to which the column belongs.
607 * @param string $columnName The quoted column name to drop the comment for.
608 */
609 protected function getDropColumnCommentSQL(string $tableName, string $columnName): string
610 {
611 if (str_contains($tableName, '.')) {
612 [$schemaName, $tableName] = explode('.', $tableName);
613 } else {
614 $schemaName = 'dbo';
615 }
616
617 return $this->getDropExtendedPropertySQL(
618 'MS_Description',
619 'SCHEMA',
620 $this->quoteStringLiteral($this->unquoteSingleIdentifier($schemaName)),
621 'TABLE',
622 $this->quoteStringLiteral($this->unquoteSingleIdentifier($tableName)),
623 'COLUMN',
624 $this->quoteStringLiteral($this->unquoteSingleIdentifier($columnName)),
625 );
626 }
627
628 /**
629 * {@inheritDoc}
630 */
631 protected function getRenameIndexSQL(string $oldIndexName, Index $index, string $tableName): array
632 {
633 return [sprintf(
634 "EXEC sp_rename N'%s.%s', N'%s', N'INDEX'",
635 $tableName,
636 $oldIndexName,
637 $index->getQuotedName($this),
638 ),
639 ];
640 }
641
642 /**
643 * Returns the SQL statement for adding an extended property to a database object.
644 *
645 * @link http://msdn.microsoft.com/en-us/library/ms180047%28v=sql.90%29.aspx
646 *
647 * @param string $name The name of the property to add.
648 * @param string|null $value The value of the property to add.
649 * @param string|null $level0Type The type of the object at level 0 the property belongs to.
650 * @param string|null $level0Name The name of the object at level 0 the property belongs to.
651 * @param string|null $level1Type The type of the object at level 1 the property belongs to.
652 * @param string|null $level1Name The name of the object at level 1 the property belongs to.
653 * @param string|null $level2Type The type of the object at level 2 the property belongs to.
654 * @param string|null $level2Name The name of the object at level 2 the property belongs to.
655 */
656 protected function getAddExtendedPropertySQL(
657 string $name,
658 ?string $value = null,
659 ?string $level0Type = null,
660 ?string $level0Name = null,
661 ?string $level1Type = null,
662 ?string $level1Name = null,
663 ?string $level2Type = null,
664 ?string $level2Name = null,
665 ): string {
666 return 'EXEC sp_addextendedproperty ' .
667 'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral($value ?? '') . ', ' .
668 'N' . $this->quoteStringLiteral($level0Type ?? '') . ', ' . $level0Name . ', ' .
669 'N' . $this->quoteStringLiteral($level1Type ?? '') . ', ' . $level1Name .
670 ($level2Type !== null || $level2Name !== null
671 ? ', N' . $this->quoteStringLiteral($level2Type ?? '') . ', ' . $level2Name
672 : ''
673 );
674 }
675
676 /**
677 * Returns the SQL statement for dropping an extended property from a database object.
678 *
679 * @link http://technet.microsoft.com/en-gb/library/ms178595%28v=sql.90%29.aspx
680 *
681 * @param string $name The name of the property to drop.
682 * @param string|null $level0Type The type of the object at level 0 the property belongs to.
683 * @param string|null $level0Name The name of the object at level 0 the property belongs to.
684 * @param string|null $level1Type The type of the object at level 1 the property belongs to.
685 * @param string|null $level1Name The name of the object at level 1 the property belongs to.
686 * @param string|null $level2Type The type of the object at level 2 the property belongs to.
687 * @param string|null $level2Name The name of the object at level 2 the property belongs to.
688 */
689 protected function getDropExtendedPropertySQL(
690 string $name,
691 ?string $level0Type = null,
692 ?string $level0Name = null,
693 ?string $level1Type = null,
694 ?string $level1Name = null,
695 ?string $level2Type = null,
696 ?string $level2Name = null,
697 ): string {
698 return 'EXEC sp_dropextendedproperty ' .
699 'N' . $this->quoteStringLiteral($name) . ', ' .
700 'N' . $this->quoteStringLiteral($level0Type ?? '') . ', ' . $level0Name . ', ' .
701 'N' . $this->quoteStringLiteral($level1Type ?? '') . ', ' . $level1Name .
702 ($level2Type !== null || $level2Name !== null
703 ? ', N' . $this->quoteStringLiteral($level2Type ?? '') . ', ' . $level2Name
704 : ''
705 );
706 }
707
708 /**
709 * Returns the SQL statement for updating an extended property of a database object.
710 *
711 * @link http://msdn.microsoft.com/en-us/library/ms186885%28v=sql.90%29.aspx
712 *
713 * @param string $name The name of the property to update.
714 * @param string|null $value The value of the property to update.
715 * @param string|null $level0Type The type of the object at level 0 the property belongs to.
716 * @param string|null $level0Name The name of the object at level 0 the property belongs to.
717 * @param string|null $level1Type The type of the object at level 1 the property belongs to.
718 * @param string|null $level1Name The name of the object at level 1 the property belongs to.
719 * @param string|null $level2Type The type of the object at level 2 the property belongs to.
720 * @param string|null $level2Name The name of the object at level 2 the property belongs to.
721 */
722 protected function getUpdateExtendedPropertySQL(
723 string $name,
724 ?string $value = null,
725 ?string $level0Type = null,
726 ?string $level0Name = null,
727 ?string $level1Type = null,
728 ?string $level1Name = null,
729 ?string $level2Type = null,
730 ?string $level2Name = null,
731 ): string {
732 return 'EXEC sp_updateextendedproperty ' .
733 'N' . $this->quoteStringLiteral($name) . ', N' . $this->quoteStringLiteral($value ?? '') . ', ' .
734 'N' . $this->quoteStringLiteral($level0Type ?? '') . ', ' . $level0Name . ', ' .
735 'N' . $this->quoteStringLiteral($level1Type ?? '') . ', ' . $level1Name .
736 ($level2Type !== null || $level2Name !== null
737 ? ', N' . $this->quoteStringLiteral($level2Type ?? '') . ', ' . $level2Name
738 : ''
739 );
740 }
741
742 public function getEmptyIdentityInsertSQL(string $quotedTableName, string $quotedIdentifierColumnName): string
743 {
744 return 'INSERT INTO ' . $quotedTableName . ' DEFAULT VALUES';
745 }
746
747 /** @internal The method should be only used from within the {@see AbstractSchemaManager} class hierarchy. */
748 public function getListViewsSQL(string $database): string
749 {
750 return "SELECT name, definition FROM sysobjects
751 INNER JOIN sys.sql_modules ON sysobjects.id = sys.sql_modules.object_id
752 WHERE type = 'V' ORDER BY name";
753 }
754
755 public function getLocateExpression(string $string, string $substring, ?string $start = null): string
756 {
757 if ($start === null) {
758 return sprintf('CHARINDEX(%s, %s)', $substring, $string);
759 }
760
761 return sprintf('CHARINDEX(%s, %s, %s)', $substring, $string, $start);
762 }
763
764 public function getModExpression(string $dividend, string $divisor): string
765 {
766 return $dividend . ' % ' . $divisor;
767 }
768
769 public function getTrimExpression(
770 string $str,
771 TrimMode $mode = TrimMode::UNSPECIFIED,
772 ?string $char = null,
773 ): string {
774 if ($char === null) {
775 return match ($mode) {
776 TrimMode::LEADING => 'LTRIM(' . $str . ')',
777 TrimMode::TRAILING => 'RTRIM(' . $str . ')',
778 default => 'LTRIM(RTRIM(' . $str . '))',
779 };
780 }
781
782 $pattern = "'%[^' + " . $char . " + ']%'";
783
784 if ($mode === TrimMode::LEADING) {
785 return 'stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)';
786 }
787
788 if ($mode === TrimMode::TRAILING) {
789 return 'reverse(stuff(reverse(' . $str . '), 1, '
790 . 'patindex(' . $pattern . ', reverse(' . $str . ')) - 1, null))';
791 }
792
793 return 'reverse(stuff(reverse(stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)), 1, '
794 . 'patindex(' . $pattern . ', reverse(stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str
795 . ') - 1, null))) - 1, null))';
796 }
797
798 public function getConcatExpression(string ...$string): string
799 {
800 return sprintf('CONCAT(%s)', implode(', ', $string));
801 }
802
803 /** @internal The method should be only used from within the {@see AbstractSchemaManager} class hierarchy. */
804 public function getListDatabasesSQL(): string
805 {
806 return 'SELECT * FROM sys.databases';
807 }
808
809 public function getSubstringExpression(string $string, string $start, ?string $length = null): string
810 {
811 if ($length === null) {
812 return sprintf('SUBSTRING(%s, %s, LEN(%s) - %s + 1)', $string, $start, $string, $start);
813 }
814
815 return sprintf('SUBSTRING(%s, %s, %s)', $string, $start, $length);
816 }
817
818 public function getLengthExpression(string $string): string
819 {
820 return 'LEN(' . $string . ')';
821 }
822
823 public function getCurrentDatabaseExpression(): string
824 {
825 return 'DB_NAME()';
826 }
827
828 public function getSetTransactionIsolationSQL(TransactionIsolationLevel $level): string
829 {
830 return 'SET TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level);
831 }
832
833 /**
834 * {@inheritDoc}
835 */
836 public function getIntegerTypeDeclarationSQL(array $column): string
837 {
838 return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($column);
839 }
840
841 /**
842 * {@inheritDoc}
843 */
844 public function getBigIntTypeDeclarationSQL(array $column): string
845 {
846 return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($column);
847 }
848
849 /**
850 * {@inheritDoc}
851 */
852 public function getSmallIntTypeDeclarationSQL(array $column): string
853 {
854 return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($column);
855 }
856
857 /**
858 * {@inheritDoc}
859 */
860 public function getGuidTypeDeclarationSQL(array $column): string
861 {
862 return 'UNIQUEIDENTIFIER';
863 }
864
865 /**
866 * {@inheritDoc}
867 */
868 public function getDateTimeTzTypeDeclarationSQL(array $column): string
869 {
870 return 'DATETIMEOFFSET(6)';
871 }
872
873 protected function getCharTypeDeclarationSQLSnippet(?int $length): string
874 {
875 $sql = 'NCHAR';
876
877 if ($length !== null) {
878 $sql .= sprintf('(%d)', $length);
879 }
880
881 return $sql;
882 }
883
884 protected function getVarcharTypeDeclarationSQLSnippet(?int $length): string
885 {
886 if ($length === null) {
887 throw ColumnLengthRequired::new($this, 'NVARCHAR');
888 }
889
890 return sprintf('NVARCHAR(%d)', $length);
891 }
892
893 /**
894 * {@inheritDoc}
895 */
896 public function getAsciiStringTypeDeclarationSQL(array $column): string
897 {
898 $length = $column['length'] ?? null;
899
900 if (empty($column['fixed'])) {
901 return parent::getVarcharTypeDeclarationSQLSnippet($length);
902 }
903
904 return parent::getCharTypeDeclarationSQLSnippet($length);
905 }
906
907 /**
908 * {@inheritDoc}
909 */
910 public function getClobTypeDeclarationSQL(array $column): string
911 {
912 return 'VARCHAR(MAX)';
913 }
914
915 /**
916 * {@inheritDoc}
917 */
918 protected function _getCommonIntegerTypeDeclarationSQL(array $column): string
919 {
920 return ! empty($column['autoincrement']) ? ' IDENTITY' : '';
921 }
922
923 /**
924 * {@inheritDoc}
925 */
926 public function getDateTimeTypeDeclarationSQL(array $column): string
927 {
928 // 3 - microseconds precision length
929 // http://msdn.microsoft.com/en-us/library/ms187819.aspx
930 return 'DATETIME2(6)';
931 }
932
933 /**
934 * {@inheritDoc}
935 */
936 public function getDateTypeDeclarationSQL(array $column): string
937 {
938 return 'DATE';
939 }
940
941 /**
942 * {@inheritDoc}
943 */
944 public function getTimeTypeDeclarationSQL(array $column): string
945 {
946 return 'TIME(0)';
947 }
948
949 /**
950 * {@inheritDoc}
951 */
952 public function getBooleanTypeDeclarationSQL(array $column): string
953 {
954 return 'BIT';
955 }
956
957 protected function doModifyLimitQuery(string $query, ?int $limit, int $offset): string
958 {
959 if ($limit === null && $offset <= 0) {
960 return $query;
961 }
962
963 if ($this->shouldAddOrderBy($query)) {
964 if (preg_match('/^SELECT\s+DISTINCT/im', $query) > 0) {
965 // SQL Server won't let us order by a non-selected column in a DISTINCT query,
966 // so we have to do this madness. This says, order by the first column in the
967 // result. SQL Server's docs say that a nonordered query's result order is non-
968 // deterministic anyway, so this won't do anything that a bunch of update and
969 // deletes to the table wouldn't do anyway.
970 $query .= ' ORDER BY 1';
971 } else {
972 // In another DBMS, we could do ORDER BY 0, but SQL Server gets angry if you
973 // use constant expressions in the order by list.
974 $query .= ' ORDER BY (SELECT 0)';
975 }
976 }
977
978 // This looks somewhat like MYSQL, but limit/offset are in inverse positions
979 // Supposedly SQL:2008 core standard.
980 // Per TSQL spec, FETCH NEXT n ROWS ONLY is not valid without OFFSET n ROWS.
981 $query .= sprintf(' OFFSET %d ROWS', $offset);
982
983 if ($limit !== null) {
984 $query .= sprintf(' FETCH NEXT %d ROWS ONLY', $limit);
985 }
986
987 return $query;
988 }
989
990 public function convertBooleans(mixed $item): mixed
991 {
992 if (is_array($item)) {
993 foreach ($item as $key => $value) {
994 if (! is_bool($value) && ! is_numeric($value)) {
995 continue;
996 }
997
998 $item[$key] = (int) (bool) $value;
999 }
1000 } elseif (is_bool($item) || is_numeric($item)) {
1001 $item = (int) (bool) $item;
1002 }
1003
1004 return $item;
1005 }
1006
1007 public function getCreateTemporaryTableSnippetSQL(): string
1008 {
1009 return 'CREATE TABLE';
1010 }
1011
1012 public function getTemporaryTableName(string $tableName): string
1013 {
1014 return '#' . $tableName;
1015 }
1016
1017 public function getDateTimeFormatString(): string
1018 {
1019 return 'Y-m-d H:i:s.u';
1020 }
1021
1022 public function getDateFormatString(): string
1023 {
1024 return 'Y-m-d';
1025 }
1026
1027 public function getTimeFormatString(): string
1028 {
1029 return 'H:i:s';
1030 }
1031
1032 public function getDateTimeTzFormatString(): string
1033 {
1034 return 'Y-m-d H:i:s.u P';
1035 }
1036
1037 protected function initializeDoctrineTypeMappings(): void
1038 {
1039 $this->doctrineTypeMapping = [
1040 'bigint' => Types::BIGINT,
1041 'binary' => Types::BINARY,
1042 'bit' => Types::BOOLEAN,
1043 'blob' => Types::BLOB,
1044 'char' => Types::STRING,
1045 'date' => Types::DATE_MUTABLE,
1046 'datetime' => Types::DATETIME_MUTABLE,
1047 'datetime2' => Types::DATETIME_MUTABLE,
1048 'datetimeoffset' => Types::DATETIMETZ_MUTABLE,
1049 'decimal' => Types::DECIMAL,
1050 'double' => Types::FLOAT,
1051 'double precision' => Types::FLOAT,
1052 'float' => Types::FLOAT,
1053 'image' => Types::BLOB,
1054 'int' => Types::INTEGER,
1055 'money' => Types::INTEGER,
1056 'nchar' => Types::STRING,
1057 'ntext' => Types::TEXT,
1058 'numeric' => Types::DECIMAL,
1059 'nvarchar' => Types::STRING,
1060 'real' => Types::FLOAT,
1061 'smalldatetime' => Types::DATETIME_MUTABLE,
1062 'smallint' => Types::SMALLINT,
1063 'smallmoney' => Types::INTEGER,
1064 'text' => Types::TEXT,
1065 'time' => Types::TIME_MUTABLE,
1066 'tinyint' => Types::SMALLINT,
1067 'uniqueidentifier' => Types::GUID,
1068 'varbinary' => Types::BINARY,
1069 'varchar' => Types::STRING,
1070 ];
1071 }
1072
1073 public function createSavePoint(string $savepoint): string
1074 {
1075 return 'SAVE TRANSACTION ' . $savepoint;
1076 }
1077
1078 public function releaseSavePoint(string $savepoint): string
1079 {
1080 return '';
1081 }
1082
1083 public function rollbackSavePoint(string $savepoint): string
1084 {
1085 return 'ROLLBACK TRANSACTION ' . $savepoint;
1086 }
1087
1088 /** @internal The method should be only used from within the {@see AbstractPlatform} class hierarchy. */
1089 public function getForeignKeyReferentialActionSQL(string $action): string
1090 {
1091 // RESTRICT is not supported, therefore falling back to NO ACTION.
1092 if (strtoupper($action) === 'RESTRICT') {
1093 return 'NO ACTION';
1094 }
1095
1096 return parent::getForeignKeyReferentialActionSQL($action);
1097 }
1098
1099 public function appendLockHint(string $fromClause, LockMode $lockMode): string
1100 {
1101 return match ($lockMode) {
1102 LockMode::NONE,
1103 LockMode::OPTIMISTIC => $fromClause,
1104 LockMode::PESSIMISTIC_READ => $fromClause . ' WITH (HOLDLOCK, ROWLOCK)',
1105 LockMode::PESSIMISTIC_WRITE => $fromClause . ' WITH (UPDLOCK, ROWLOCK)',
1106 };
1107 }
1108
1109 protected function createReservedKeywordsList(): KeywordList
1110 {
1111 return new SQLServerKeywords();
1112 }
1113
1114 public function quoteSingleIdentifier(string $str): string
1115 {
1116 return '[' . str_replace(']', ']]', $str) . ']';
1117 }
1118
1119 public function getTruncateTableSQL(string $tableName, bool $cascade = false): string
1120 {
1121 $tableIdentifier = new Identifier($tableName);
1122
1123 return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this);
1124 }
1125
1126 /**
1127 * {@inheritDoc}
1128 */
1129 public function getBlobTypeDeclarationSQL(array $column): string
1130 {
1131 return 'VARBINARY(MAX)';
1132 }
1133
1134 /**
1135 * {@inheritDoc}
1136 *
1137 * @internal The method should be only used from within the {@see AbstractPlatform} class hierarchy.
1138 */
1139 public function getColumnDeclarationSQL(string $name, array $column): string
1140 {
1141 if (isset($column['columnDefinition'])) {
1142 $declaration = $column['columnDefinition'];
1143 } else {
1144 $collation = ! empty($column['collation']) ?
1145 ' ' . $this->getColumnCollationDeclarationSQL($column['collation']) : '';
1146
1147 $notnull = ! empty($column['notnull']) ? ' NOT NULL' : '';
1148
1149 $typeDecl = $column['type']->getSQLDeclaration($column, $this);
1150 $declaration = $typeDecl . $collation . $notnull;
1151 }
1152
1153 return $name . ' ' . $declaration;
1154 }
1155
1156 /**
1157 * SQL Server does not support quoting collation identifiers.
1158 */
1159 public function getColumnCollationDeclarationSQL(string $collation): string
1160 {
1161 return 'COLLATE ' . $collation;
1162 }
1163
1164 public function columnsEqual(Column $column1, Column $column2): bool
1165 {
1166 if (! parent::columnsEqual($column1, $column2)) {
1167 return false;
1168 }
1169
1170 return $this->getDefaultValueDeclarationSQL($column1->toArray())
1171 === $this->getDefaultValueDeclarationSQL($column2->toArray());
1172 }
1173
1174 protected function getLikeWildcardCharacters(): string
1175 {
1176 return parent::getLikeWildcardCharacters() . '[]^';
1177 }
1178
1179 protected function getCommentOnTableSQL(string $tableName, string $comment): string
1180 {
1181 return $this->getAddExtendedPropertySQL(
1182 'MS_Description',
1183 $comment,
1184 'SCHEMA',
1185 $this->quoteStringLiteral('dbo'),
1186 'TABLE',
1187 $this->quoteStringLiteral($this->unquoteSingleIdentifier($tableName)),
1188 );
1189 }
1190
1191 private function shouldAddOrderBy(string $query): bool
1192 {
1193 // Find the position of the last instance of ORDER BY and ensure it is not within a parenthetical statement
1194 // but can be in a newline
1195 $matches = [];
1196 $matchesCount = preg_match_all('/[\\s]+order\\s+by\\s/im', $query, $matches, PREG_OFFSET_CAPTURE);
1197 if ($matchesCount === 0) {
1198 return true;
1199 }
1200
1201 // ORDER BY instance may be in a subquery after ORDER BY
1202 // e.g. SELECT col1 FROM test ORDER BY (SELECT col2 from test ORDER BY col2)
1203 // if in the searched query ORDER BY clause was found where
1204 // number of open parentheses after the occurrence of the clause is equal to
1205 // number of closed brackets after the occurrence of the clause,
1206 // it means that ORDER BY is included in the query being checked
1207 while ($matchesCount > 0) {
1208 $orderByPos = $matches[0][--$matchesCount][1];
1209 $openBracketsCount = substr_count($query, '(', $orderByPos);
1210 $closedBracketsCount = substr_count($query, ')', $orderByPos);
1211 if ($openBracketsCount === $closedBracketsCount) {
1212 return false;
1213 }
1214 }
1215
1216 return true;
1217 }
1218
1219 public function createSchemaManager(Connection $connection): SQLServerSchemaManager
1220 {
1221 return new SQLServerSchemaManager($connection, $this);
1222 }
1223}
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-12 14:18:13 +0000