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) |
|
|
|
|
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
|
|
|
} |
1174
|
|
|
|
1175
|
|
|
/** |
1176
|
|
|
* {@inheritDoc} |
1177
|
|
|
*/ |
1178
|
81 |
|
public function getGuidTypeDeclarationSQL(array $field) |
1179
|
|
|
{ |
1180
|
81 |
|
return 'UNIQUEIDENTIFIER'; |
1181
|
|
|
} |
1182
|
|
|
|
1183
|
|
|
/** |
1184
|
|
|
* {@inheritDoc} |
1185
|
|
|
*/ |
1186
|
2099 |
|
protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) |
1187
|
|
|
{ |
1188
|
2099 |
|
return $fixed ? ($length ? 'NCHAR(' . $length . ')' : 'CHAR(255)') : ($length ? 'NVARCHAR(' . $length . ')' : 'NVARCHAR(255)'); |
1189
|
|
|
} |
1190
|
|
|
|
1191
|
|
|
/** |
1192
|
|
|
* {@inheritdoc} |
1193
|
|
|
*/ |
1194
|
85 |
|
protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed) |
1195
|
|
|
{ |
1196
|
85 |
|
return $fixed ? 'BINARY(' . ($length ?: 255) . ')' : 'VARBINARY(' . ($length ?: 255) . ')'; |
1197
|
|
|
} |
1198
|
|
|
|
1199
|
|
|
/** |
1200
|
|
|
* {@inheritdoc} |
1201
|
|
|
*/ |
1202
|
247 |
|
public function getBinaryMaxLength() |
1203
|
|
|
{ |
1204
|
247 |
|
return 8000; |
1205
|
|
|
} |
1206
|
|
|
|
1207
|
|
|
/** |
1208
|
|
|
* {@inheritDoc} |
1209
|
|
|
*/ |
1210
|
135 |
|
public function getClobTypeDeclarationSQL(array $field) |
1211
|
|
|
{ |
1212
|
135 |
|
return 'VARCHAR(MAX)'; |
1213
|
|
|
} |
1214
|
|
|
|
1215
|
|
|
/** |
1216
|
|
|
* {@inheritDoc} |
1217
|
|
|
*/ |
1218
|
1412 |
|
protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef) |
1219
|
|
|
{ |
1220
|
1412 |
|
return ! empty($columnDef['autoincrement']) ? ' IDENTITY' : ''; |
1221
|
|
|
} |
1222
|
|
|
|
1223
|
|
|
/** |
1224
|
|
|
* {@inheritDoc} |
1225
|
|
|
*/ |
1226
|
|
|
public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration) |
1227
|
|
|
{ |
1228
|
|
|
return 'DATETIME'; |
1229
|
|
|
} |
1230
|
|
|
|
1231
|
|
|
/** |
1232
|
|
|
* {@inheritDoc} |
1233
|
|
|
*/ |
1234
|
|
|
public function getDateTypeDeclarationSQL(array $fieldDeclaration) |
1235
|
|
|
{ |
1236
|
|
|
return 'DATETIME'; |
1237
|
|
|
} |
1238
|
|
|
|
1239
|
|
|
/** |
1240
|
|
|
* {@inheritDoc} |
1241
|
|
|
*/ |
1242
|
|
|
public function getTimeTypeDeclarationSQL(array $fieldDeclaration) |
1243
|
|
|
{ |
1244
|
|
|
return 'DATETIME'; |
1245
|
|
|
} |
1246
|
|
|
|
1247
|
|
|
/** |
1248
|
|
|
* {@inheritDoc} |
1249
|
|
|
*/ |
1250
|
115 |
|
public function getBooleanTypeDeclarationSQL(array $field) |
1251
|
|
|
{ |
1252
|
115 |
|
return 'BIT'; |
1253
|
|
|
} |
1254
|
|
|
|
1255
|
|
|
/** |
1256
|
|
|
* {@inheritDoc} |
1257
|
|
|
*/ |
1258
|
1134 |
|
protected function doModifyLimitQuery($query, $limit, $offset = null) |
1259
|
|
|
{ |
1260
|
1134 |
|
$where = []; |
1261
|
|
|
|
1262
|
1134 |
|
if ($offset > 0) { |
1263
|
108 |
|
$where[] = sprintf('doctrine_rownum >= %d', $offset + 1); |
1264
|
|
|
} |
1265
|
|
|
|
1266
|
1134 |
|
if ($limit !== null) { |
1267
|
1080 |
|
$where[] = sprintf('doctrine_rownum <= %d', $offset + $limit); |
1268
|
1080 |
|
$top = sprintf('TOP %d', $offset + $limit); |
1269
|
|
|
} else { |
1270
|
54 |
|
$top = 'TOP 9223372036854775807'; |
1271
|
|
|
} |
1272
|
|
|
|
1273
|
1134 |
|
if (empty($where)) { |
1274
|
54 |
|
return $query; |
1275
|
|
|
} |
1276
|
|
|
|
1277
|
|
|
// We'll find a SELECT or SELECT distinct and prepend TOP n to it |
1278
|
|
|
// Even if the TOP n is very large, the use of a CTE will |
1279
|
|
|
// allow the SQL Server query planner to optimize it so it doesn't |
1280
|
|
|
// actually scan the entire range covered by the TOP clause. |
1281
|
1080 |
|
if (! preg_match('/^(\s*SELECT\s+(?:DISTINCT\s+)?)(.*)$/im', $query, $matches)) { |
1282
|
|
|
return $query; |
1283
|
|
|
} |
1284
|
|
|
|
1285
|
1080 |
|
$query = $matches[1] . $top . ' ' . $matches[2]; |
1286
|
|
|
|
1287
|
1080 |
|
if (stristr($query, 'ORDER BY')) { |
1288
|
|
|
// Inner order by is not valid in SQL Server for our purposes |
1289
|
|
|
// unless it's in a TOP N subquery. |
1290
|
702 |
|
$query = $this->scrubInnerOrderBy($query); |
1291
|
|
|
} |
1292
|
|
|
|
1293
|
|
|
// Build a new limited query around the original, using a CTE |
1294
|
1080 |
|
return sprintf( |
1295
|
|
|
'WITH dctrn_cte AS (%s) ' |
1296
|
|
|
. 'SELECT * FROM (' |
1297
|
|
|
. 'SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM dctrn_cte' |
1298
|
|
|
. ') AS doctrine_tbl ' |
1299
|
1080 |
|
. 'WHERE %s ORDER BY doctrine_rownum ASC', |
1300
|
1080 |
|
$query, |
1301
|
1080 |
|
implode(' AND ', $where) |
1302
|
|
|
); |
1303
|
|
|
} |
1304
|
|
|
|
1305
|
|
|
/** |
1306
|
|
|
* Remove ORDER BY clauses in subqueries - they're not supported by SQL Server. |
1307
|
|
|
* Caveat: will leave ORDER BY in TOP N subqueries. |
1308
|
|
|
* |
1309
|
|
|
* @param string $query |
1310
|
|
|
* |
1311
|
|
|
* @return string |
1312
|
|
|
*/ |
1313
|
702 |
|
private function scrubInnerOrderBy($query) |
1314
|
|
|
{ |
1315
|
702 |
|
$count = substr_count(strtoupper($query), 'ORDER BY'); |
1316
|
702 |
|
$offset = 0; |
1317
|
|
|
|
1318
|
702 |
|
while ($count-- > 0) { |
1319
|
702 |
|
$orderByPos = stripos($query, ' ORDER BY', $offset); |
1320
|
702 |
|
if ($orderByPos === false) { |
1321
|
54 |
|
break; |
1322
|
|
|
} |
1323
|
|
|
|
1324
|
702 |
|
$qLen = strlen($query); |
1325
|
702 |
|
$parenCount = 0; |
1326
|
702 |
|
$currentPosition = $orderByPos; |
1327
|
|
|
|
1328
|
702 |
|
while ($parenCount >= 0 && $currentPosition < $qLen) { |
1329
|
702 |
|
if ($query[$currentPosition] === '(') { |
1330
|
54 |
|
$parenCount++; |
1331
|
702 |
|
} elseif ($query[$currentPosition] === ')') { |
1332
|
378 |
|
$parenCount--; |
1333
|
|
|
} |
1334
|
|
|
|
1335
|
702 |
|
$currentPosition++; |
1336
|
|
|
} |
1337
|
|
|
|
1338
|
702 |
|
if ($this->isOrderByInTopNSubquery($query, $orderByPos)) { |
1339
|
|
|
// If the order by clause is in a TOP N subquery, do not remove |
1340
|
|
|
// it and continue iteration from the current position. |
1341
|
621 |
|
$offset = $currentPosition; |
1342
|
621 |
|
continue; |
1343
|
|
|
} |
1344
|
|
|
|
1345
|
270 |
|
if ($currentPosition >= $qLen - 1) { |
1346
|
|
|
continue; |
1347
|
|
|
} |
1348
|
|
|
|
1349
|
270 |
|
$query = substr($query, 0, $orderByPos) . substr($query, $currentPosition - 1); |
1350
|
270 |
|
$offset = $orderByPos; |
1351
|
|
|
} |
1352
|
702 |
|
return $query; |
1353
|
|
|
} |
1354
|
|
|
|
1355
|
|
|
/** |
1356
|
|
|
* Check an ORDER BY clause to see if it is in a TOP N query or subquery. |
1357
|
|
|
* |
1358
|
|
|
* @param string $query The query |
1359
|
|
|
* @param int $currentPosition Start position of ORDER BY clause |
1360
|
|
|
* |
1361
|
|
|
* @return bool true if ORDER BY is in a TOP N query, false otherwise |
1362
|
|
|
*/ |
1363
|
702 |
|
private function isOrderByInTopNSubquery($query, $currentPosition) |
1364
|
|
|
{ |
1365
|
|
|
// Grab query text on the same nesting level as the ORDER BY clause we're examining. |
1366
|
702 |
|
$subQueryBuffer = ''; |
1367
|
702 |
|
$parenCount = 0; |
1368
|
|
|
|
1369
|
|
|
// If $parenCount goes negative, we've exited the subquery we're examining. |
1370
|
|
|
// If $currentPosition goes negative, we've reached the beginning of the query. |
1371
|
702 |
|
while ($parenCount >= 0 && $currentPosition >= 0) { |
1372
|
702 |
|
if ($query[$currentPosition] === '(') { |
1373
|
459 |
|
$parenCount--; |
1374
|
702 |
|
} elseif ($query[$currentPosition] === ')') { |
1375
|
378 |
|
$parenCount++; |
1376
|
|
|
} |
1377
|
|
|
|
1378
|
|
|
// Only yank query text on the same nesting level as the ORDER BY clause. |
1379
|
702 |
|
$subQueryBuffer = ($parenCount === 0 ? $query[$currentPosition] : ' ') . $subQueryBuffer; |
1380
|
|
|
|
1381
|
702 |
|
$currentPosition--; |
1382
|
|
|
} |
1383
|
|
|
|
1384
|
702 |
|
return (bool) preg_match('/SELECT\s+(DISTINCT\s+)?TOP\s/i', $subQueryBuffer); |
1385
|
|
|
} |
1386
|
|
|
|
1387
|
|
|
/** |
1388
|
|
|
* {@inheritDoc} |
1389
|
|
|
*/ |
1390
|
|
|
public function supportsLimitOffset() |
1391
|
|
|
{ |
1392
|
|
|
return false; |
1393
|
|
|
} |
1394
|
|
|
|
1395
|
|
|
/** |
1396
|
|
|
* {@inheritDoc} |
1397
|
|
|
*/ |
1398
|
89 |
|
public function convertBooleans($item) |
1399
|
|
|
{ |
1400
|
89 |
|
if (is_array($item)) { |
1401
|
|
|
foreach ($item as $key => $value) { |
1402
|
|
|
if (! is_bool($value) && ! is_numeric($item)) { |
1403
|
|
|
continue; |
1404
|
|
|
} |
1405
|
|
|
|
1406
|
|
|
$item[$key] = $value ? 1 : 0; |
1407
|
|
|
} |
1408
|
89 |
|
} elseif (is_bool($item) || is_numeric($item)) { |
1409
|
89 |
|
$item = $item ? 1 : 0; |
1410
|
|
|
} |
1411
|
|
|
|
1412
|
89 |
|
return $item; |
1413
|
|
|
} |
1414
|
|
|
|
1415
|
|
|
/** |
1416
|
|
|
* {@inheritDoc} |
1417
|
|
|
*/ |
1418
|
4 |
|
public function getCreateTemporaryTableSnippetSQL() |
1419
|
|
|
{ |
1420
|
4 |
|
return 'CREATE TABLE'; |
1421
|
|
|
} |
1422
|
|
|
|
1423
|
|
|
/** |
1424
|
|
|
* {@inheritDoc} |
1425
|
|
|
*/ |
1426
|
4 |
|
public function getTemporaryTableName($tableName) |
1427
|
|
|
{ |
1428
|
4 |
|
return '#' . $tableName; |
1429
|
|
|
} |
1430
|
|
|
|
1431
|
|
|
/** |
1432
|
|
|
* {@inheritDoc} |
1433
|
|
|
*/ |
1434
|
|
|
public function getDateTimeFormatString() |
1435
|
|
|
{ |
1436
|
|
|
return 'Y-m-d H:i:s.000'; |
1437
|
|
|
} |
1438
|
|
|
|
1439
|
|
|
/** |
1440
|
|
|
* {@inheritDoc} |
1441
|
|
|
*/ |
1442
|
|
|
public function getDateFormatString() |
1443
|
|
|
{ |
1444
|
|
|
return 'Y-m-d H:i:s.000'; |
1445
|
|
|
} |
1446
|
|
|
|
1447
|
|
|
/** |
1448
|
|
|
* {@inheritDoc} |
1449
|
|
|
*/ |
1450
|
|
|
public function getTimeFormatString() |
1451
|
|
|
{ |
1452
|
|
|
return 'Y-m-d H:i:s.000'; |
1453
|
|
|
} |
1454
|
|
|
|
1455
|
|
|
/** |
1456
|
|
|
* {@inheritDoc} |
1457
|
|
|
*/ |
1458
|
|
|
public function getDateTimeTzFormatString() |
1459
|
|
|
{ |
1460
|
|
|
return $this->getDateTimeFormatString(); |
1461
|
|
|
} |
1462
|
|
|
|
1463
|
|
|
/** |
1464
|
|
|
* {@inheritDoc} |
1465
|
|
|
*/ |
1466
|
140 |
|
public function getName() |
1467
|
|
|
{ |
1468
|
140 |
|
return 'mssql'; |
1469
|
|
|
} |
1470
|
|
|
|
1471
|
|
|
/** |
1472
|
|
|
* {@inheritDoc} |
1473
|
|
|
*/ |
1474
|
407 |
|
protected function initializeDoctrineTypeMappings() |
1475
|
|
|
{ |
1476
|
407 |
|
$this->doctrineTypeMapping = [ |
1477
|
|
|
'bigint' => 'bigint', |
1478
|
|
|
'numeric' => 'decimal', |
1479
|
|
|
'bit' => 'boolean', |
1480
|
|
|
'smallint' => 'smallint', |
1481
|
|
|
'decimal' => 'decimal', |
1482
|
|
|
'smallmoney' => 'integer', |
1483
|
|
|
'int' => 'integer', |
1484
|
|
|
'tinyint' => 'smallint', |
1485
|
|
|
'money' => 'integer', |
1486
|
|
|
'float' => 'float', |
1487
|
|
|
'real' => 'float', |
1488
|
|
|
'double' => 'float', |
1489
|
|
|
'double precision' => 'float', |
1490
|
|
|
'smalldatetime' => 'datetime', |
1491
|
|
|
'datetime' => 'datetime', |
1492
|
|
|
'char' => 'string', |
1493
|
|
|
'varchar' => 'string', |
1494
|
|
|
'text' => 'text', |
1495
|
|
|
'nchar' => 'string', |
1496
|
|
|
'nvarchar' => 'string', |
1497
|
|
|
'ntext' => 'text', |
1498
|
|
|
'binary' => 'binary', |
1499
|
|
|
'varbinary' => 'binary', |
1500
|
|
|
'image' => 'blob', |
1501
|
|
|
'uniqueidentifier' => 'guid', |
1502
|
|
|
]; |
1503
|
407 |
|
} |
1504
|
|
|
|
1505
|
|
|
/** |
1506
|
|
|
* {@inheritDoc} |
1507
|
|
|
*/ |
1508
|
2 |
|
public function createSavePoint($savepoint) |
1509
|
|
|
{ |
1510
|
2 |
|
return 'SAVE TRANSACTION ' . $savepoint; |
1511
|
|
|
} |
1512
|
|
|
|
1513
|
|
|
/** |
1514
|
|
|
* {@inheritDoc} |
1515
|
|
|
*/ |
1516
|
|
|
public function releaseSavePoint($savepoint) |
1517
|
|
|
{ |
1518
|
|
|
return ''; |
1519
|
|
|
} |
1520
|
|
|
|
1521
|
|
|
/** |
1522
|
|
|
* {@inheritDoc} |
1523
|
|
|
*/ |
1524
|
2 |
|
public function rollbackSavePoint($savepoint) |
1525
|
|
|
{ |
1526
|
2 |
|
return 'ROLLBACK TRANSACTION ' . $savepoint; |
1527
|
|
|
} |
1528
|
|
|
|
1529
|
|
|
/** |
1530
|
|
|
* {@inheritdoc} |
1531
|
|
|
*/ |
1532
|
569 |
|
public function getForeignKeyReferentialActionSQL($action) |
1533
|
|
|
{ |
1534
|
|
|
// RESTRICT is not supported, therefore falling back to NO ACTION. |
1535
|
569 |
|
if (strtoupper($action) === 'RESTRICT') { |
1536
|
81 |
|
return 'NO ACTION'; |
1537
|
|
|
} |
1538
|
|
|
|
1539
|
488 |
|
return parent::getForeignKeyReferentialActionSQL($action); |
1540
|
|
|
} |
1541
|
|
|
|
1542
|
|
|
/** |
1543
|
|
|
* {@inheritDoc} |
1544
|
|
|
*/ |
1545
|
193 |
|
public function appendLockHint($fromClause, $lockMode) |
1546
|
|
|
{ |
1547
|
175 |
|
switch (true) { |
1548
|
18 |
|
case $lockMode === LockMode::NONE: |
1549
|
27 |
|
return $fromClause . ' WITH (NOLOCK)'; |
1550
|
|
|
|
1551
|
166 |
|
case $lockMode === LockMode::PESSIMISTIC_READ: |
1552
|
27 |
|
return $fromClause . ' WITH (HOLDLOCK, ROWLOCK)'; |
1553
|
|
|
|
1554
|
139 |
|
case $lockMode === LockMode::PESSIMISTIC_WRITE: |
1555
|
31 |
|
return $fromClause . ' WITH (UPDLOCK, ROWLOCK)'; |
1556
|
|
|
|
1557
|
|
|
default: |
1558
|
108 |
|
return $fromClause; |
1559
|
|
|
} |
1560
|
|
|
} |
1561
|
|
|
|
1562
|
|
|
/** |
1563
|
|
|
* {@inheritDoc} |
1564
|
|
|
*/ |
1565
|
4 |
|
public function getForUpdateSQL() |
1566
|
|
|
{ |
1567
|
4 |
|
return ' '; |
1568
|
|
|
} |
1569
|
|
|
|
1570
|
|
|
/** |
1571
|
|
|
* {@inheritDoc} |
1572
|
|
|
*/ |
1573
|
1674 |
|
protected function getReservedKeywordsClass() |
1574
|
|
|
{ |
1575
|
1674 |
|
return Keywords\SQLServerKeywords::class; |
1576
|
|
|
} |
1577
|
|
|
|
1578
|
|
|
/** |
1579
|
|
|
* {@inheritDoc} |
1580
|
|
|
*/ |
1581
|
2465 |
|
public function quoteSingleIdentifier($str) |
1582
|
|
|
{ |
1583
|
2465 |
|
return '[' . str_replace(']', '][', $str) . ']'; |
1584
|
|
|
} |
1585
|
|
|
|
1586
|
|
|
/** |
1587
|
|
|
* {@inheritDoc} |
1588
|
|
|
*/ |
1589
|
97 |
|
public function getTruncateTableSQL($tableName, $cascade = false) |
1590
|
|
|
{ |
1591
|
97 |
|
$tableIdentifier = new Identifier($tableName); |
1592
|
|
|
|
1593
|
97 |
|
return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this); |
1594
|
|
|
} |
1595
|
|
|
|
1596
|
|
|
/** |
1597
|
|
|
* {@inheritDoc} |
1598
|
|
|
*/ |
1599
|
97 |
|
public function getBlobTypeDeclarationSQL(array $field) |
1600
|
|
|
{ |
1601
|
97 |
|
return 'VARBINARY(MAX)'; |
1602
|
|
|
} |
1603
|
|
|
|
1604
|
|
|
/** |
1605
|
|
|
* {@inheritDoc} |
1606
|
|
|
*/ |
1607
|
1561 |
|
public function getDefaultValueDeclarationSQL($field) |
1608
|
|
|
{ |
1609
|
1561 |
|
if (! isset($field['default'])) { |
1610
|
|
|
return empty($field['notnull']) ? ' NULL' : ''; |
1611
|
|
|
} |
1612
|
|
|
|
1613
|
1561 |
|
if (! isset($field['type'])) { |
1614
|
324 |
|
return " DEFAULT '" . $field['default'] . "'"; |
1615
|
|
|
} |
1616
|
|
|
|
1617
|
1237 |
|
$type = $field['type']; |
1618
|
|
|
|
1619
|
1237 |
|
if ($type instanceof Types\PhpIntegerMappingType) { |
1620
|
255 |
|
return ' DEFAULT ' . $field['default']; |
1621
|
|
|
} |
1622
|
|
|
|
1623
|
1069 |
|
if ($type instanceof Types\PhpDateTimeMappingType && $field['default'] === $this->getCurrentTimestampSQL()) { |
1624
|
83 |
|
return ' DEFAULT ' . $this->getCurrentTimestampSQL(); |
1625
|
|
|
} |
1626
|
|
|
|
1627
|
988 |
|
if ($type instanceof Types\BooleanType) { |
1628
|
85 |
|
return " DEFAULT '" . $this->convertBooleans($field['default']) . "'"; |
1629
|
|
|
} |
1630
|
|
|
|
1631
|
986 |
|
return " DEFAULT '" . $field['default'] . "'"; |
1632
|
|
|
} |
1633
|
|
|
|
1634
|
|
|
/** |
1635
|
|
|
* {@inheritdoc} |
1636
|
|
|
* |
1637
|
|
|
* Modifies column declaration order as it differs in Microsoft SQL Server. |
1638
|
|
|
*/ |
1639
|
2807 |
|
public function getColumnDeclarationSQL($name, array $field) |
1640
|
|
|
{ |
1641
|
2807 |
|
if (isset($field['columnDefinition'])) { |
1642
|
81 |
|
$columnDef = $this->getCustomTypeDeclarationSQL($field); |
1643
|
|
|
} else { |
1644
|
2726 |
|
$collation = isset($field['collation']) && $field['collation'] ? |
1645
|
2726 |
|
' ' . $this->getColumnCollationDeclarationSQL($field['collation']) : ''; |
1646
|
|
|
|
1647
|
2726 |
|
$notnull = isset($field['notnull']) && $field['notnull'] ? ' NOT NULL' : ''; |
1648
|
|
|
|
1649
|
2726 |
|
$unique = isset($field['unique']) && $field['unique'] ? |
1650
|
2726 |
|
' ' . $this->getUniqueFieldDeclarationSQL() : ''; |
1651
|
|
|
|
1652
|
2726 |
|
$check = isset($field['check']) && $field['check'] ? |
1653
|
2726 |
|
' ' . $field['check'] : ''; |
1654
|
|
|
|
1655
|
2726 |
|
$typeDecl = $field['type']->getSQLDeclaration($field, $this); |
1656
|
2726 |
|
$columnDef = $typeDecl . $collation . $notnull . $unique . $check; |
1657
|
|
|
} |
1658
|
|
|
|
1659
|
2807 |
|
return $name . ' ' . $columnDef; |
1660
|
|
|
} |
1661
|
|
|
|
1662
|
|
|
/** |
1663
|
|
|
* Returns a unique default constraint name for a table and column. |
1664
|
|
|
* |
1665
|
|
|
* @param string $table Name of the table to generate the unique default constraint name for. |
1666
|
|
|
* @param string $column Name of the column in the table to generate the unique default constraint name for. |
1667
|
|
|
* |
1668
|
|
|
* @return string |
1669
|
|
|
*/ |
1670
|
1237 |
|
private function generateDefaultConstraintName($table, $column) |
1671
|
|
|
{ |
1672
|
1237 |
|
return 'DF_' . $this->generateIdentifierName($table) . '_' . $this->generateIdentifierName($column); |
1673
|
|
|
} |
1674
|
|
|
|
1675
|
|
|
/** |
1676
|
|
|
* Returns a hash value for a given identifier. |
1677
|
|
|
* |
1678
|
|
|
* @param string $identifier Identifier to generate a hash value for. |
1679
|
|
|
* |
1680
|
|
|
* @return string |
1681
|
|
|
*/ |
1682
|
1318 |
|
private function generateIdentifierName($identifier) |
1683
|
|
|
{ |
1684
|
|
|
// Always generate name for unquoted identifiers to ensure consistency. |
1685
|
1318 |
|
$identifier = new Identifier($identifier); |
1686
|
|
|
|
1687
|
1318 |
|
return strtoupper(dechex(crc32($identifier->getName()))); |
1688
|
|
|
} |
1689
|
|
|
} |
1690
|
|
|
|
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.