1 | <?php |
||
2 | /* |
||
3 | * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
||
4 | * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
||
5 | * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR |
||
6 | * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT |
||
7 | * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, |
||
8 | * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT |
||
9 | * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, |
||
10 | * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY |
||
11 | * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
||
12 | * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
||
13 | * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
||
14 | * |
||
15 | * This software consists of voluntary contributions made by many individuals |
||
16 | * and is licensed under the MIT license. For more information, see |
||
17 | * <http://www.doctrine-project.org>. |
||
18 | */ |
||
19 | |||
20 | namespace Doctrine\DBAL\Platforms; |
||
21 | |||
22 | use Doctrine\DBAL\LockMode; |
||
23 | use Doctrine\DBAL\Schema\Column; |
||
24 | use Doctrine\DBAL\Schema\ColumnDiff; |
||
25 | use Doctrine\DBAL\Schema\ForeignKeyConstraint; |
||
26 | use Doctrine\DBAL\Schema\Identifier; |
||
27 | use Doctrine\DBAL\Schema\Index; |
||
28 | use Doctrine\DBAL\Schema\Table; |
||
29 | use Doctrine\DBAL\Schema\TableDiff; |
||
30 | use Doctrine\DBAL\Types; |
||
31 | |||
32 | /** |
||
33 | * The SQLServerPlatform provides the behavior, features and SQL dialect of the |
||
34 | * Microsoft SQL Server database platform. |
||
35 | * |
||
36 | * @since 2.0 |
||
37 | * @author Roman Borschel <[email protected]> |
||
38 | * @author Jonathan H. Wage <[email protected]> |
||
39 | * @author Benjamin Eberlei <[email protected]> |
||
40 | * @author Steve Müller <[email protected]> |
||
41 | */ |
||
42 | class SQLServerPlatform extends AbstractPlatform |
||
43 | { |
||
44 | /** |
||
45 | * {@inheritdoc} |
||
46 | */ |
||
47 | 6 | public function getCurrentDateSQL() |
|
48 | { |
||
49 | 6 | return $this->getConvertExpression('date', 'GETDATE()'); |
|
50 | } |
||
51 | |||
52 | /** |
||
53 | * {@inheritdoc} |
||
54 | */ |
||
55 | 3 | public function getCurrentTimeSQL() |
|
56 | { |
||
57 | 3 | return $this->getConvertExpression('time', 'GETDATE()'); |
|
58 | } |
||
59 | |||
60 | /** |
||
61 | * Returns an expression that converts an expression of one data type to another. |
||
62 | * |
||
63 | * @param string $dataType The target native data type. Alias data types cannot be used. |
||
64 | * @param string $expression The SQL expression to convert. |
||
65 | * |
||
66 | * @return string |
||
67 | */ |
||
68 | 6 | private function getConvertExpression($dataType, $expression) |
|
69 | { |
||
70 | 6 | return sprintf('CONVERT(%s, %s)', $dataType, $expression); |
|
71 | } |
||
72 | |||
73 | /** |
||
74 | * {@inheritdoc} |
||
75 | */ |
||
76 | View Code Duplication | protected function getDateArithmeticIntervalExpression($date, $operator, $interval, $unit) |
|
77 | { |
||
78 | $factorClause = ''; |
||
79 | |||
80 | if ('-' === $operator) { |
||
81 | $factorClause = '-1 * '; |
||
82 | } |
||
83 | |||
84 | return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')'; |
||
85 | } |
||
86 | |||
87 | /** |
||
88 | * {@inheritDoc} |
||
89 | */ |
||
90 | public function getDateDiffExpression($date1, $date2) |
||
91 | { |
||
92 | return 'DATEDIFF(day, ' . $date2 . ',' . $date1 . ')'; |
||
93 | } |
||
94 | |||
95 | /** |
||
96 | * {@inheritDoc} |
||
97 | * |
||
98 | * Microsoft SQL Server prefers "autoincrement" identity columns |
||
99 | * since sequences can only be emulated with a table. |
||
100 | */ |
||
101 | 3 | public function prefersIdentityColumns() |
|
102 | { |
||
103 | 3 | return true; |
|
104 | } |
||
105 | |||
106 | /** |
||
107 | * {@inheritDoc} |
||
108 | * |
||
109 | * Microsoft SQL Server supports this through AUTO_INCREMENT columns. |
||
110 | */ |
||
111 | 3 | public function supportsIdentityColumns() |
|
112 | { |
||
113 | 3 | return true; |
|
114 | } |
||
115 | |||
116 | /** |
||
117 | * {@inheritDoc} |
||
118 | */ |
||
119 | public function supportsReleaseSavepoints() |
||
120 | { |
||
121 | return false; |
||
122 | } |
||
123 | |||
124 | /** |
||
125 | * {@inheritdoc} |
||
126 | */ |
||
127 | 3 | public function supportsSchemas() |
|
128 | { |
||
129 | 3 | return true; |
|
130 | } |
||
131 | |||
132 | /** |
||
133 | * {@inheritdoc} |
||
134 | */ |
||
135 | public function getDefaultSchemaName() |
||
136 | { |
||
137 | return 'dbo'; |
||
138 | } |
||
139 | |||
140 | /** |
||
141 | * {@inheritDoc} |
||
142 | */ |
||
143 | public function supportsColumnCollation() |
||
144 | { |
||
145 | return true; |
||
146 | } |
||
147 | |||
148 | /** |
||
149 | * {@inheritDoc} |
||
150 | */ |
||
151 | 160 | public function hasNativeGuidType() |
|
152 | { |
||
153 | 160 | return true; |
|
154 | } |
||
155 | |||
156 | /** |
||
157 | * {@inheritDoc} |
||
158 | */ |
||
159 | 3 | public function getCreateDatabaseSQL($name) |
|
160 | { |
||
161 | 3 | return 'CREATE DATABASE ' . $name; |
|
162 | } |
||
163 | |||
164 | /** |
||
165 | * {@inheritDoc} |
||
166 | */ |
||
167 | 3 | public function getDropDatabaseSQL($name) |
|
168 | { |
||
169 | 3 | return 'DROP DATABASE ' . $name; |
|
170 | } |
||
171 | |||
172 | /** |
||
173 | * {@inheritDoc} |
||
174 | */ |
||
175 | 3 | public function supportsCreateDropDatabase() |
|
176 | { |
||
177 | 3 | return true; |
|
178 | } |
||
179 | |||
180 | /** |
||
181 | * {@inheritDoc} |
||
182 | */ |
||
183 | 3 | public function getCreateSchemaSQL($schemaName) |
|
184 | { |
||
185 | 3 | return 'CREATE SCHEMA ' . $schemaName; |
|
186 | } |
||
187 | |||
188 | /** |
||
189 | * {@inheritDoc} |
||
190 | */ |
||
191 | 6 | View Code Duplication | public function getDropForeignKeySQL($foreignKey, $table) |
192 | { |
||
193 | 6 | if (! $foreignKey instanceof ForeignKeyConstraint) { |
|
194 | 3 | $foreignKey = new Identifier($foreignKey); |
|
195 | } |
||
196 | |||
197 | 6 | if (! $table instanceof Table) { |
|
198 | 6 | $table = new Identifier($table); |
|
199 | } |
||
200 | |||
201 | 6 | $foreignKey = $foreignKey->getQuotedName($this); |
|
202 | 6 | $table = $table->getQuotedName($this); |
|
203 | |||
204 | 6 | return 'ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $foreignKey; |
|
205 | } |
||
206 | |||
207 | /** |
||
208 | * {@inheritDoc} |
||
209 | */ |
||
210 | public function getDropIndexSQL($index, $table = null) |
||
211 | { |
||
212 | if ($index instanceof Index) { |
||
213 | $index = $index->getQuotedName($this); |
||
214 | } elseif (!is_string($index)) { |
||
215 | throw new \InvalidArgumentException('AbstractPlatform::getDropIndexSQL() expects $index parameter to be string or \Doctrine\DBAL\Schema\Index.'); |
||
216 | } |
||
217 | |||
218 | if (!isset($table)) { |
||
219 | return 'DROP INDEX ' . $index; |
||
220 | } |
||
221 | |||
222 | if ($table instanceof Table) { |
||
223 | $table = $table->getQuotedName($this); |
||
224 | } |
||
225 | |||
226 | return "IF EXISTS (SELECT * FROM sysobjects WHERE name = '$index') |
||
227 | ALTER TABLE " . $table . " DROP CONSTRAINT " . $index . " |
||
228 | ELSE |
||
229 | DROP INDEX " . $index . " ON " . $table; |
||
230 | } |
||
231 | |||
232 | /** |
||
233 | * {@inheritDoc} |
||
234 | */ |
||
235 | 46 | protected function _getCreateTableSQL($tableName, array $columns, array $options = []) |
|
236 | { |
||
237 | 46 | $defaultConstraintsSql = []; |
|
238 | 46 | $commentsSql = []; |
|
239 | |||
240 | // @todo does other code breaks because of this? |
||
241 | // force primary keys to be not null |
||
242 | 46 | foreach ($columns as &$column) { |
|
243 | 46 | if (isset($column['primary']) && $column['primary']) { |
|
244 | 15 | $column['notnull'] = true; |
|
245 | } |
||
246 | |||
247 | // Build default constraints SQL statements. |
||
248 | 46 | if (isset($column['default'])) { |
|
249 | 12 | $defaultConstraintsSql[] = 'ALTER TABLE ' . $tableName . |
|
250 | 12 | ' ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $column); |
|
251 | } |
||
252 | |||
253 | 46 | if ( ! empty($column['comment']) || is_numeric($column['comment'])) { |
|
254 | 46 | $commentsSql[] = $this->getCreateColumnCommentSQL($tableName, $column['name'], $column['comment']); |
|
255 | } |
||
256 | } |
||
257 | |||
258 | 46 | $columnListSql = $this->getColumnDeclarationListSQL($columns); |
|
259 | |||
260 | 46 | if (isset($options['uniqueConstraints']) && !empty($options['uniqueConstraints'])) { |
|
261 | foreach ($options['uniqueConstraints'] as $name => $definition) { |
||
262 | $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition); |
||
263 | } |
||
264 | } |
||
265 | |||
266 | 46 | View Code Duplication | if (isset($options['primary']) && !empty($options['primary'])) { |
267 | 18 | $flags = ''; |
|
268 | 18 | if (isset($options['primary_index']) && $options['primary_index']->hasFlag('nonclustered')) { |
|
269 | 3 | $flags = ' NONCLUSTERED'; |
|
270 | } |
||
271 | 18 | $columnListSql .= ', PRIMARY KEY' . $flags . ' (' . implode(', ', array_unique(array_values($options['primary']))) . ')'; |
|
272 | } |
||
273 | |||
274 | 46 | $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql; |
|
275 | |||
276 | 46 | $check = $this->getCheckDeclarationSQL($columns); |
|
277 | 46 | if (!empty($check)) { |
|
278 | $query .= ', ' . $check; |
||
279 | } |
||
280 | 46 | $query .= ')'; |
|
281 | |||
282 | 46 | $sql[] = $query; |
|
283 | |||
284 | 46 | View Code Duplication | if (isset($options['indexes']) && !empty($options['indexes'])) { |
285 | 9 | foreach ($options['indexes'] as $index) { |
|
286 | 9 | $sql[] = $this->getCreateIndexSQL($index, $tableName); |
|
287 | } |
||
288 | } |
||
289 | |||
290 | 46 | View Code Duplication | if (isset($options['foreignKeys'])) { |
291 | 3 | foreach ((array) $options['foreignKeys'] as $definition) { |
|
292 | 3 | $sql[] = $this->getCreateForeignKeySQL($definition, $tableName); |
|
293 | } |
||
294 | } |
||
295 | |||
296 | 46 | return array_merge($sql, $commentsSql, $defaultConstraintsSql); |
|
297 | } |
||
298 | |||
299 | /** |
||
300 | * {@inheritDoc} |
||
301 | */ |
||
302 | 6 | public function getCreatePrimaryKeySQL(Index $index, $table) |
|
303 | { |
||
304 | 6 | $flags = ''; |
|
305 | 6 | if ($index->hasFlag('nonclustered')) { |
|
306 | 3 | $flags = ' NONCLUSTERED'; |
|
307 | } |
||
308 | |||
309 | 6 | return 'ALTER TABLE ' . $table . ' ADD PRIMARY KEY' . $flags . ' (' . $this->getIndexFieldDeclarationListSQL($index->getQuotedColumns($this)) . ')'; |
|
310 | } |
||
311 | |||
312 | /** |
||
313 | * Returns the SQL statement for creating a column comment. |
||
314 | * |
||
315 | * SQL Server does not support native column comments, |
||
316 | * therefore the extended properties functionality is used |
||
317 | * as a workaround to store them. |
||
318 | * The property name used to store column comments is "MS_Description" |
||
319 | * which provides compatibility with SQL Server Management Studio, |
||
320 | * as column comments are stored in the same property there when |
||
321 | * specifying a column's "Description" attribute. |
||
322 | * |
||
323 | * @param string $tableName The quoted table name to which the column belongs. |
||
324 | * @param string $columnName The quoted column name to create the comment for. |
||
325 | * @param string $comment The column's comment. |
||
326 | * |
||
327 | * @return string |
||
328 | */ |
||
329 | 15 | protected function getCreateColumnCommentSQL($tableName, $columnName, $comment) |
|
330 | { |
||
331 | 15 | return $this->getAddExtendedPropertySQL( |
|
332 | 15 | 'MS_Description', |
|
333 | 15 | $comment, |
|
334 | 15 | 'SCHEMA', |
|
335 | 15 | 'dbo', |
|
336 | 15 | 'TABLE', |
|
337 | 15 | $tableName, |
|
338 | 15 | 'COLUMN', |
|
339 | 15 | $columnName |
|
340 | ); |
||
341 | } |
||
342 | |||
343 | /** |
||
344 | * Returns the SQL snippet for declaring a default constraint. |
||
345 | * |
||
346 | * @param string $table Name of the table to return the default constraint declaration for. |
||
347 | * @param array $column Column definition. |
||
348 | * |
||
349 | * @return string |
||
350 | * |
||
351 | * @throws \InvalidArgumentException |
||
352 | */ |
||
353 | 45 | public function getDefaultConstraintDeclarationSQL($table, array $column) |
|
354 | { |
||
355 | 45 | if ( ! isset($column['default'])) { |
|
356 | throw new \InvalidArgumentException("Incomplete column definition. 'default' required."); |
||
357 | } |
||
358 | |||
359 | 45 | $columnName = new Identifier($column['name']); |
|
360 | |||
361 | return |
||
362 | ' CONSTRAINT ' . |
||
363 | 45 | $this->generateDefaultConstraintName($table, $column['name']) . |
|
364 | 45 | $this->getDefaultValueDeclarationSQL($column) . |
|
365 | 45 | ' FOR ' . $columnName->getQuotedName($this); |
|
366 | } |
||
367 | |||
368 | /** |
||
369 | * {@inheritDoc} |
||
370 | */ |
||
371 | 6 | public function getUniqueConstraintDeclarationSQL($name, Index $index) |
|
372 | { |
||
373 | 6 | $constraint = parent::getUniqueConstraintDeclarationSQL($name, $index); |
|
374 | |||
375 | 6 | $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index); |
|
376 | |||
377 | 6 | return $constraint; |
|
378 | } |
||
379 | |||
380 | /** |
||
381 | * {@inheritDoc} |
||
382 | */ |
||
383 | 24 | public function getCreateIndexSQL(Index $index, $table) |
|
384 | { |
||
385 | 24 | $constraint = parent::getCreateIndexSQL($index, $table); |
|
386 | |||
387 | 24 | if ($index->isUnique() && !$index->isPrimary()) { |
|
388 | 6 | $constraint = $this->_appendUniqueConstraintDefinition($constraint, $index); |
|
389 | } |
||
390 | |||
391 | 24 | return $constraint; |
|
392 | } |
||
393 | |||
394 | /** |
||
395 | * {@inheritDoc} |
||
396 | */ |
||
397 | 24 | View Code Duplication | protected function getCreateIndexSQLFlags(Index $index) |
398 | { |
||
399 | 24 | $type = ''; |
|
400 | 24 | if ($index->isUnique()) { |
|
401 | 6 | $type .= 'UNIQUE '; |
|
402 | } |
||
403 | |||
404 | 24 | if ($index->hasFlag('clustered')) { |
|
405 | 3 | $type .= 'CLUSTERED '; |
|
406 | 21 | } elseif ($index->hasFlag('nonclustered')) { |
|
407 | $type .= 'NONCLUSTERED '; |
||
408 | } |
||
409 | |||
410 | 24 | return $type; |
|
411 | } |
||
412 | |||
413 | /** |
||
414 | * Extend unique key constraint with required filters |
||
415 | * |
||
416 | * @param string $sql |
||
417 | * @param \Doctrine\DBAL\Schema\Index $index |
||
418 | * |
||
419 | * @return string |
||
420 | */ |
||
421 | 12 | private function _appendUniqueConstraintDefinition($sql, Index $index) |
|
422 | { |
||
423 | 12 | $fields = []; |
|
424 | |||
425 | 12 | foreach ($index->getQuotedColumns($this) as $field) { |
|
426 | 12 | $fields[] = $field . ' IS NOT NULL'; |
|
427 | } |
||
428 | |||
429 | 12 | return $sql . ' WHERE ' . implode(' AND ', $fields); |
|
430 | } |
||
431 | |||
432 | /** |
||
433 | * {@inheritDoc} |
||
434 | */ |
||
435 | 57 | public function getAlterTableSQL(TableDiff $diff) |
|
436 | { |
||
437 | 57 | $queryParts = []; |
|
438 | 57 | $sql = []; |
|
439 | 57 | $columnSql = []; |
|
440 | 57 | $commentsSql = []; |
|
441 | |||
442 | /** @var \Doctrine\DBAL\Schema\Column $column */ |
||
443 | 57 | foreach ($diff->addedColumns as $column) { |
|
444 | 27 | if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { |
|
445 | continue; |
||
446 | } |
||
447 | |||
448 | 27 | $columnDef = $column->toArray(); |
|
449 | 27 | $queryParts[] = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); |
|
450 | |||
451 | 27 | if (isset($columnDef['default'])) { |
|
452 | 12 | $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); |
|
453 | } |
||
454 | |||
455 | 27 | $comment = $this->getColumnComment($column); |
|
456 | |||
457 | 27 | if ( ! empty($comment) || is_numeric($comment)) { |
|
458 | 6 | $commentsSql[] = $this->getCreateColumnCommentSQL( |
|
459 | 6 | $diff->name, |
|
460 | 6 | $column->getQuotedName($this), |
|
461 | 27 | $comment |
|
462 | ); |
||
463 | } |
||
464 | } |
||
465 | |||
466 | 57 | View Code Duplication | foreach ($diff->removedColumns as $column) { |
467 | 24 | if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { |
|
468 | continue; |
||
469 | } |
||
470 | |||
471 | 24 | $queryParts[] = 'DROP COLUMN ' . $column->getQuotedName($this); |
|
472 | } |
||
473 | |||
474 | /* @var $columnDiff \Doctrine\DBAL\Schema\ColumnDiff */ |
||
475 | 57 | foreach ($diff->changedColumns as $columnDiff) { |
|
476 | 36 | if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { |
|
477 | continue; |
||
478 | } |
||
479 | |||
480 | 36 | $column = $columnDiff->column; |
|
481 | 36 | $comment = $this->getColumnComment($column); |
|
482 | 36 | $hasComment = ! empty ($comment) || is_numeric($comment); |
|
483 | |||
484 | 36 | if ($columnDiff->fromColumn instanceof Column) { |
|
485 | 21 | $fromComment = $this->getColumnComment($columnDiff->fromColumn); |
|
486 | 21 | $hasFromComment = ! empty ($fromComment) || is_numeric($fromComment); |
|
487 | |||
488 | 21 | if ($hasFromComment && $hasComment && $fromComment != $comment) { |
|
489 | 3 | $commentsSql[] = $this->getAlterColumnCommentSQL( |
|
490 | 3 | $diff->name, |
|
491 | 3 | $column->getQuotedName($this), |
|
492 | 3 | $comment |
|
493 | ); |
||
494 | 21 | } elseif ($hasFromComment && ! $hasComment) { |
|
495 | 3 | $commentsSql[] = $this->getDropColumnCommentSQL($diff->name, $column->getQuotedName($this)); |
|
496 | 21 | } elseif ($hasComment) { |
|
497 | 3 | $commentsSql[] = $this->getCreateColumnCommentSQL( |
|
498 | 3 | $diff->name, |
|
499 | 3 | $column->getQuotedName($this), |
|
500 | 21 | $comment |
|
501 | ); |
||
502 | } |
||
503 | } else { |
||
504 | // todo: Original comment cannot be determined. What to do? Add, update, drop or skip? |
||
505 | } |
||
506 | |||
507 | // Do not add query part if only comment has changed. |
||
508 | 36 | if ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1) { |
|
509 | 6 | continue; |
|
510 | } |
||
511 | |||
512 | 33 | $requireDropDefaultConstraint = $this->alterColumnRequiresDropDefaultConstraint($columnDiff); |
|
513 | |||
514 | 33 | if ($requireDropDefaultConstraint) { |
|
515 | 15 | $queryParts[] = $this->getAlterTableDropDefaultConstraintClause( |
|
516 | 15 | $diff->name, |
|
517 | 15 | $columnDiff->oldColumnName |
|
518 | ); |
||
519 | } |
||
520 | |||
521 | 33 | $columnDef = $column->toArray(); |
|
522 | |||
523 | 33 | $queryParts[] = 'ALTER COLUMN ' . |
|
524 | 33 | $this->getColumnDeclarationSQL($column->getQuotedName($this), $columnDef); |
|
525 | |||
526 | 33 | if (isset($columnDef['default']) && ($requireDropDefaultConstraint || $columnDiff->hasChanged('default'))) { |
|
527 | 33 | $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); |
|
528 | } |
||
529 | } |
||
530 | |||
531 | 57 | foreach ($diff->renamedColumns as $oldColumnName => $column) { |
|
532 | 15 | if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { |
|
533 | continue; |
||
534 | } |
||
535 | |||
536 | 15 | $oldColumnName = new Identifier($oldColumnName); |
|
537 | |||
538 | 15 | $sql[] = "sp_RENAME '" . |
|
539 | 15 | $diff->getName($this)->getQuotedName($this) . "." . $oldColumnName->getQuotedName($this) . |
|
540 | 15 | "', '" . $column->getQuotedName($this) . "', 'COLUMN'"; |
|
541 | |||
542 | // Recreate default constraint with new column name if necessary (for future reference). |
||
543 | 15 | if ($column->getDefault() !== null) { |
|
544 | 3 | $queryParts[] = $this->getAlterTableDropDefaultConstraintClause( |
|
545 | 3 | $diff->name, |
|
546 | 3 | $oldColumnName->getQuotedName($this) |
|
547 | ); |
||
548 | 15 | $queryParts[] = $this->getAlterTableAddDefaultConstraintClause($diff->name, $column); |
|
549 | } |
||
550 | } |
||
551 | |||
552 | 57 | $tableSql = []; |
|
553 | |||
554 | 57 | if ($this->onSchemaAlterTable($diff, $tableSql)) { |
|
555 | return array_merge($tableSql, $columnSql); |
||
556 | } |
||
557 | |||
558 | 57 | foreach ($queryParts as $query) { |
|
559 | 39 | $sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
|
560 | } |
||
561 | |||
562 | 57 | $sql = array_merge($sql, $commentsSql); |
|
563 | |||
564 | 57 | if ($diff->newName !== false) { |
|
565 | 6 | $sql[] = "sp_RENAME '" . $diff->getName($this)->getQuotedName($this) . "', '" . $diff->getNewName()->getName() . "'"; |
|
566 | |||
567 | /** |
||
568 | * Rename table's default constraints names |
||
569 | * to match the new table name. |
||
570 | * This is necessary to ensure that the default |
||
571 | * constraints can be referenced in future table |
||
572 | * alterations as the table name is encoded in |
||
573 | * default constraints' names. |
||
574 | */ |
||
575 | 6 | $sql[] = "DECLARE @sql NVARCHAR(MAX) = N''; " . |
|
576 | "SELECT @sql += N'EXEC sp_rename N''' + dc.name + ''', N''' " . |
||
577 | 6 | "+ REPLACE(dc.name, '" . $this->generateIdentifierName($diff->name) . "', " . |
|
578 | 6 | "'" . $this->generateIdentifierName($diff->newName) . "') + ''', ''OBJECT'';' " . |
|
579 | 6 | "FROM sys.default_constraints dc " . |
|
580 | 6 | "JOIN sys.tables tbl ON dc.parent_object_id = tbl.object_id " . |
|
581 | 6 | "WHERE tbl.name = '" . $diff->getNewName()->getName() . "';" . |
|
582 | 6 | "EXEC sp_executesql @sql"; |
|
583 | } |
||
584 | |||
585 | 57 | $sql = array_merge( |
|
586 | 57 | $this->getPreAlterTableIndexForeignKeySQL($diff), |
|
587 | 57 | $sql, |
|
588 | 57 | $this->getPostAlterTableIndexForeignKeySQL($diff) |
|
589 | ); |
||
590 | |||
591 | 57 | return array_merge($sql, $tableSql, $columnSql); |
|
592 | } |
||
593 | |||
594 | /** |
||
595 | * Returns the SQL clause for adding a default constraint in an ALTER TABLE statement. |
||
596 | * |
||
597 | * @param string $tableName The name of the table to generate the clause for. |
||
598 | * @param Column $column The column to generate the clause for. |
||
599 | * |
||
600 | * @return string |
||
601 | */ |
||
602 | 21 | private function getAlterTableAddDefaultConstraintClause($tableName, Column $column) |
|
603 | { |
||
604 | 21 | $columnDef = $column->toArray(); |
|
605 | 21 | $columnDef['name'] = $column->getQuotedName($this); |
|
606 | |||
607 | 21 | return 'ADD' . $this->getDefaultConstraintDeclarationSQL($tableName, $columnDef); |
|
608 | } |
||
609 | |||
610 | /** |
||
611 | * Returns the SQL clause for dropping an existing default constraint in an ALTER TABLE statement. |
||
612 | * |
||
613 | * @param string $tableName The name of the table to generate the clause for. |
||
614 | * @param string $columnName The name of the column to generate the clause for. |
||
615 | * |
||
616 | * @return string |
||
617 | */ |
||
618 | 18 | private function getAlterTableDropDefaultConstraintClause($tableName, $columnName) |
|
619 | { |
||
620 | 18 | return 'DROP CONSTRAINT ' . $this->generateDefaultConstraintName($tableName, $columnName); |
|
621 | } |
||
622 | |||
623 | /** |
||
624 | * Checks whether a column alteration requires dropping its default constraint first. |
||
625 | * |
||
626 | * Different to other database vendors SQL Server implements column default values |
||
627 | * as constraints and therefore changes in a column's default value as well as changes |
||
628 | * in a column's type require dropping the default constraint first before being to |
||
629 | * alter the particular column to the new definition. |
||
630 | * |
||
631 | * @param ColumnDiff $columnDiff The column diff to evaluate. |
||
632 | * |
||
633 | * @return boolean True if the column alteration requires dropping its default constraint first, false otherwise. |
||
634 | */ |
||
635 | 33 | private function alterColumnRequiresDropDefaultConstraint(ColumnDiff $columnDiff) |
|
636 | { |
||
637 | // We can only decide whether to drop an existing default constraint |
||
638 | // if we know the original default value. |
||
639 | 33 | if ( ! $columnDiff->fromColumn instanceof Column) { |
|
640 | 12 | return false; |
|
641 | } |
||
642 | |||
643 | // We only need to drop an existing default constraint if we know the |
||
644 | // column was defined with a default value before. |
||
645 | 21 | if ($columnDiff->fromColumn->getDefault() === null) { |
|
646 | 6 | return false; |
|
647 | } |
||
648 | |||
649 | // We need to drop an existing default constraint if the column was |
||
650 | // defined with a default value before and it has changed. |
||
651 | 15 | if ($columnDiff->hasChanged('default')) { |
|
652 | 12 | return true; |
|
653 | } |
||
654 | |||
655 | // We need to drop an existing default constraint if the column was |
||
656 | // defined with a default value before and the native column type has changed. |
||
657 | 3 | if ($columnDiff->hasChanged('type') || $columnDiff->hasChanged('fixed')) { |
|
658 | 3 | return true; |
|
659 | } |
||
660 | |||
661 | return false; |
||
662 | } |
||
663 | |||
664 | /** |
||
665 | * Returns the SQL statement for altering a column comment. |
||
666 | * |
||
667 | * SQL Server does not support native column comments, |
||
668 | * therefore the extended properties functionality is used |
||
669 | * as a workaround to store them. |
||
670 | * The property name used to store column comments is "MS_Description" |
||
671 | * which provides compatibility with SQL Server Management Studio, |
||
672 | * as column comments are stored in the same property there when |
||
673 | * specifying a column's "Description" attribute. |
||
674 | * |
||
675 | * @param string $tableName The quoted table name to which the column belongs. |
||
676 | * @param string $columnName The quoted column name to alter the comment for. |
||
677 | * @param string $comment The column's comment. |
||
678 | * |
||
679 | * @return string |
||
680 | */ |
||
681 | 3 | protected function getAlterColumnCommentSQL($tableName, $columnName, $comment) |
|
682 | { |
||
683 | 3 | return $this->getUpdateExtendedPropertySQL( |
|
684 | 3 | 'MS_Description', |
|
685 | 3 | $comment, |
|
686 | 3 | 'SCHEMA', |
|
687 | 3 | 'dbo', |
|
688 | 3 | 'TABLE', |
|
689 | 3 | $tableName, |
|
690 | 3 | 'COLUMN', |
|
691 | 3 | $columnName |
|
692 | ); |
||
693 | } |
||
694 | |||
695 | /** |
||
696 | * Returns the SQL statement for dropping a column comment. |
||
697 | * |
||
698 | * SQL Server does not support native column comments, |
||
699 | * therefore the extended properties functionality is used |
||
700 | * as a workaround to store them. |
||
701 | * The property name used to store column comments is "MS_Description" |
||
702 | * which provides compatibility with SQL Server Management Studio, |
||
703 | * as column comments are stored in the same property there when |
||
704 | * specifying a column's "Description" attribute. |
||
705 | * |
||
706 | * @param string $tableName The quoted table name to which the column belongs. |
||
707 | * @param string $columnName The quoted column name to drop the comment for. |
||
708 | * |
||
709 | * @return string |
||
710 | */ |
||
711 | 3 | protected function getDropColumnCommentSQL($tableName, $columnName) |
|
712 | { |
||
713 | 3 | return $this->getDropExtendedPropertySQL( |
|
714 | 3 | 'MS_Description', |
|
715 | 3 | 'SCHEMA', |
|
716 | 3 | 'dbo', |
|
717 | 3 | 'TABLE', |
|
718 | 3 | $tableName, |
|
719 | 3 | 'COLUMN', |
|
720 | 3 | $columnName |
|
721 | ); |
||
722 | } |
||
723 | |||
724 | /** |
||
725 | * {@inheritdoc} |
||
726 | */ |
||
727 | 15 | protected function getRenameIndexSQL($oldIndexName, Index $index, $tableName) |
|
728 | { |
||
729 | return [ |
||
730 | 15 | sprintf( |
|
731 | 15 | "EXEC sp_RENAME N'%s.%s', N'%s', N'INDEX'", |
|
732 | 15 | $tableName, |
|
733 | 15 | $oldIndexName, |
|
734 | 15 | $index->getQuotedName($this) |
|
735 | ) |
||
736 | ]; |
||
737 | } |
||
738 | |||
739 | /** |
||
740 | * Returns the SQL statement for adding an extended property to a database object. |
||
741 | * |
||
742 | * @param string $name The name of the property to add. |
||
743 | * @param string|null $value The value of the property to add. |
||
744 | * @param string|null $level0Type The type of the object at level 0 the property belongs to. |
||
745 | * @param string|null $level0Name The name of the object at level 0 the property belongs to. |
||
746 | * @param string|null $level1Type The type of the object at level 1 the property belongs to. |
||
747 | * @param string|null $level1Name The name of the object at level 1 the property belongs to. |
||
748 | * @param string|null $level2Type The type of the object at level 2 the property belongs to. |
||
749 | * @param string|null $level2Name The name of the object at level 2 the property belongs to. |
||
750 | * |
||
751 | * @return string |
||
752 | * |
||
753 | * @link http://msdn.microsoft.com/en-us/library/ms180047%28v=sql.90%29.aspx |
||
754 | */ |
||
755 | 15 | View Code Duplication | public function getAddExtendedPropertySQL( |
756 | $name, |
||
757 | $value = null, |
||
758 | $level0Type = null, |
||
759 | $level0Name = null, |
||
760 | $level1Type = null, |
||
761 | $level1Name = null, |
||
762 | $level2Type = null, |
||
763 | $level2Name = null |
||
764 | ) { |
||
765 | return "EXEC sp_addextendedproperty " . |
||
766 | 15 | "N" . $this->quoteStringLiteral($name) . ", N" . $this->quoteStringLiteral($value) . ", " . |
|
767 | 15 | "N" . $this->quoteStringLiteral($level0Type) . ", " . $level0Name . ', ' . |
|
768 | 15 | "N" . $this->quoteStringLiteral($level1Type) . ", " . $level1Name . ', ' . |
|
769 | 15 | "N" . $this->quoteStringLiteral($level2Type) . ", " . $level2Name; |
|
770 | } |
||
771 | |||
772 | /** |
||
773 | * Returns the SQL statement for dropping an extended property from a database object. |
||
774 | * |
||
775 | * @param string $name The name of the property to drop. |
||
776 | * @param string|null $level0Type The type of the object at level 0 the property belongs to. |
||
777 | * @param string|null $level0Name The name of the object at level 0 the property belongs to. |
||
778 | * @param string|null $level1Type The type of the object at level 1 the property belongs to. |
||
779 | * @param string|null $level1Name The name of the object at level 1 the property belongs to. |
||
780 | * @param string|null $level2Type The type of the object at level 2 the property belongs to. |
||
781 | * @param string|null $level2Name The name of the object at level 2 the property belongs to. |
||
782 | * |
||
783 | * @return string |
||
784 | * |
||
785 | * @link http://technet.microsoft.com/en-gb/library/ms178595%28v=sql.90%29.aspx |
||
786 | */ |
||
787 | 3 | public function getDropExtendedPropertySQL( |
|
788 | $name, |
||
789 | $level0Type = null, |
||
790 | $level0Name = null, |
||
791 | $level1Type = null, |
||
792 | $level1Name = null, |
||
793 | $level2Type = null, |
||
794 | $level2Name = null |
||
795 | ) { |
||
796 | return "EXEC sp_dropextendedproperty " . |
||
797 | 3 | "N" . $this->quoteStringLiteral($name) . ", " . |
|
798 | 3 | "N" . $this->quoteStringLiteral($level0Type) . ", " . $level0Name . ', ' . |
|
799 | 3 | "N" . $this->quoteStringLiteral($level1Type) . ", " . $level1Name . ', ' . |
|
800 | 3 | "N" . $this->quoteStringLiteral($level2Type) . ", " . $level2Name; |
|
801 | } |
||
802 | |||
803 | /** |
||
804 | * Returns the SQL statement for updating an extended property of a database object. |
||
805 | * |
||
806 | * @param string $name The name of the property to update. |
||
807 | * @param string|null $value The value of the property to update. |
||
808 | * @param string|null $level0Type The type of the object at level 0 the property belongs to. |
||
809 | * @param string|null $level0Name The name of the object at level 0 the property belongs to. |
||
810 | * @param string|null $level1Type The type of the object at level 1 the property belongs to. |
||
811 | * @param string|null $level1Name The name of the object at level 1 the property belongs to. |
||
812 | * @param string|null $level2Type The type of the object at level 2 the property belongs to. |
||
813 | * @param string|null $level2Name The name of the object at level 2 the property belongs to. |
||
814 | * |
||
815 | * @return string |
||
816 | * |
||
817 | * @link http://msdn.microsoft.com/en-us/library/ms186885%28v=sql.90%29.aspx |
||
818 | */ |
||
819 | 3 | View Code Duplication | public function getUpdateExtendedPropertySQL( |
820 | $name, |
||
821 | $value = null, |
||
822 | $level0Type = null, |
||
823 | $level0Name = null, |
||
824 | $level1Type = null, |
||
825 | $level1Name = null, |
||
826 | $level2Type = null, |
||
827 | $level2Name = null |
||
828 | ) { |
||
829 | return "EXEC sp_updateextendedproperty " . |
||
830 | 3 | "N" . $this->quoteStringLiteral($name) . ", N" . $this->quoteStringLiteral($value) . ", " . |
|
831 | 3 | "N" . $this->quoteStringLiteral($level0Type) . ", " . $level0Name . ', ' . |
|
832 | 3 | "N" . $this->quoteStringLiteral($level1Type) . ", " . $level1Name . ', ' . |
|
833 | 3 | "N" . $this->quoteStringLiteral($level2Type) . ", " . $level2Name; |
|
834 | } |
||
835 | |||
836 | /** |
||
837 | * {@inheritDoc} |
||
838 | */ |
||
839 | public function getEmptyIdentityInsertSQL($quotedTableName, $quotedIdentifierColumnName) |
||
840 | { |
||
841 | return 'INSERT INTO ' . $quotedTableName . ' DEFAULT VALUES'; |
||
842 | } |
||
843 | |||
844 | /** |
||
845 | * {@inheritDoc} |
||
846 | */ |
||
847 | public function getListTablesSQL() |
||
848 | { |
||
849 | // "sysdiagrams" table must be ignored as it's internal SQL Server table for Database Diagrams |
||
850 | // Category 2 must be ignored as it is "MS SQL Server 'pseudo-system' object[s]" for replication |
||
851 | return "SELECT name FROM sysobjects WHERE type = 'U' AND name != 'sysdiagrams' AND category != 2 ORDER BY name"; |
||
852 | } |
||
853 | |||
854 | /** |
||
855 | * {@inheritDoc} |
||
856 | */ |
||
857 | 6 | public function getListTableColumnsSQL($table, $database = null) |
|
858 | { |
||
859 | return "SELECT col.name, |
||
860 | type.name AS type, |
||
861 | col.max_length AS length, |
||
862 | ~col.is_nullable AS notnull, |
||
863 | def.definition AS [default], |
||
864 | col.scale, |
||
865 | col.precision, |
||
866 | col.is_identity AS autoincrement, |
||
867 | col.collation_name AS collation, |
||
868 | CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type |
||
869 | FROM sys.columns AS col |
||
870 | JOIN sys.types AS type |
||
871 | ON col.user_type_id = type.user_type_id |
||
872 | JOIN sys.objects AS obj |
||
873 | ON col.object_id = obj.object_id |
||
874 | JOIN sys.schemas AS scm |
||
875 | ON obj.schema_id = scm.schema_id |
||
876 | LEFT JOIN sys.default_constraints def |
||
877 | ON col.default_object_id = def.object_id |
||
878 | AND col.object_id = def.parent_object_id |
||
879 | LEFT JOIN sys.extended_properties AS prop |
||
880 | ON obj.object_id = prop.major_id |
||
881 | AND col.column_id = prop.minor_id |
||
882 | AND prop.name = 'MS_Description' |
||
883 | WHERE obj.type = 'U' |
||
884 | 6 | AND " . $this->getTableWhereClause($table, 'scm.name', 'obj.name'); |
|
885 | } |
||
886 | |||
887 | /** |
||
888 | * {@inheritDoc} |
||
889 | */ |
||
890 | 6 | public function getListTableForeignKeysSQL($table, $database = null) |
|
891 | { |
||
892 | return "SELECT f.name AS ForeignKey, |
||
893 | SCHEMA_NAME (f.SCHEMA_ID) AS SchemaName, |
||
894 | OBJECT_NAME (f.parent_object_id) AS TableName, |
||
895 | COL_NAME (fc.parent_object_id,fc.parent_column_id) AS ColumnName, |
||
896 | SCHEMA_NAME (o.SCHEMA_ID) ReferenceSchemaName, |
||
897 | OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName, |
||
898 | COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName, |
||
899 | f.delete_referential_action_desc, |
||
900 | f.update_referential_action_desc |
||
901 | FROM sys.foreign_keys AS f |
||
902 | INNER JOIN sys.foreign_key_columns AS fc |
||
903 | INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id |
||
904 | ON f.OBJECT_ID = fc.constraint_object_id |
||
905 | WHERE " . |
||
906 | 6 | $this->getTableWhereClause($table, 'SCHEMA_NAME (f.schema_id)', 'OBJECT_NAME (f.parent_object_id)'); |
|
907 | } |
||
908 | |||
909 | /** |
||
910 | * {@inheritDoc} |
||
911 | */ |
||
912 | 6 | public function getListTableIndexesSQL($table, $currentDatabase = null) |
|
913 | { |
||
914 | return "SELECT idx.name AS key_name, |
||
915 | col.name AS column_name, |
||
916 | ~idx.is_unique AS non_unique, |
||
917 | idx.is_primary_key AS [primary], |
||
918 | CASE idx.type |
||
919 | WHEN '1' THEN 'clustered' |
||
920 | WHEN '2' THEN 'nonclustered' |
||
921 | ELSE NULL |
||
922 | END AS flags |
||
923 | FROM sys.tables AS tbl |
||
924 | JOIN sys.schemas AS scm ON tbl.schema_id = scm.schema_id |
||
925 | JOIN sys.indexes AS idx ON tbl.object_id = idx.object_id |
||
926 | JOIN sys.index_columns AS idxcol ON idx.object_id = idxcol.object_id AND idx.index_id = idxcol.index_id |
||
927 | JOIN sys.columns AS col ON idxcol.object_id = col.object_id AND idxcol.column_id = col.column_id |
||
928 | 6 | WHERE " . $this->getTableWhereClause($table, 'scm.name', 'tbl.name') . " |
|
929 | ORDER BY idx.index_id ASC, idxcol.key_ordinal ASC"; |
||
930 | } |
||
931 | |||
932 | /** |
||
933 | * {@inheritDoc} |
||
934 | */ |
||
935 | public function getCreateViewSQL($name, $sql) |
||
936 | { |
||
937 | return 'CREATE VIEW ' . $name . ' AS ' . $sql; |
||
938 | } |
||
939 | |||
940 | /** |
||
941 | * {@inheritDoc} |
||
942 | */ |
||
943 | public function getListViewsSQL($database) |
||
944 | { |
||
945 | return "SELECT name FROM sysobjects WHERE type = 'V' ORDER BY name"; |
||
946 | } |
||
947 | |||
948 | /** |
||
949 | * Returns the where clause to filter schema and table name in a query. |
||
950 | * |
||
951 | * @param string $table The full qualified name of the table. |
||
952 | * @param string $schemaColumn The name of the column to compare the schema to in the where clause. |
||
953 | * @param string $tableColumn The name of the column to compare the table to in the where clause. |
||
954 | * |
||
955 | * @return string |
||
956 | */ |
||
957 | 18 | View Code Duplication | private function getTableWhereClause($table, $schemaColumn, $tableColumn) |
958 | { |
||
959 | 18 | if (strpos($table, ".") !== false) { |
|
960 | 9 | list($schema, $table) = explode(".", $table); |
|
961 | 9 | $schema = $this->quoteStringLiteral($schema); |
|
962 | 9 | $table = $this->quoteStringLiteral($table); |
|
963 | } else { |
||
964 | 9 | $schema = "SCHEMA_NAME()"; |
|
965 | 9 | $table = $this->quoteStringLiteral($table); |
|
966 | } |
||
967 | |||
968 | 18 | return "({$tableColumn} = {$table} AND {$schemaColumn} = {$schema})"; |
|
969 | } |
||
970 | |||
971 | /** |
||
972 | * {@inheritDoc} |
||
973 | */ |
||
974 | public function getDropViewSQL($name) |
||
975 | { |
||
976 | return 'DROP VIEW ' . $name; |
||
977 | } |
||
978 | |||
979 | /** |
||
980 | * {@inheritDoc} |
||
981 | */ |
||
982 | public function getGuidExpression() |
||
983 | { |
||
984 | return 'NEWID()'; |
||
985 | } |
||
986 | |||
987 | /** |
||
988 | * {@inheritDoc} |
||
989 | */ |
||
990 | View Code Duplication | public function getLocateExpression($str, $substr, $startPos = false) |
|
991 | { |
||
992 | if ($startPos == false) { |
||
993 | return 'CHARINDEX(' . $substr . ', ' . $str . ')'; |
||
994 | } |
||
995 | |||
996 | return 'CHARINDEX(' . $substr . ', ' . $str . ', ' . $startPos . ')'; |
||
997 | } |
||
998 | |||
999 | /** |
||
1000 | * {@inheritDoc} |
||
1001 | */ |
||
1002 | public function getModExpression($expression1, $expression2) |
||
1003 | { |
||
1004 | return $expression1 . ' % ' . $expression2; |
||
1005 | } |
||
1006 | |||
1007 | /** |
||
1008 | * {@inheritDoc} |
||
1009 | */ |
||
1010 | public function getTrimExpression($str, $pos = self::TRIM_UNSPECIFIED, $char = false) |
||
1011 | { |
||
1012 | if ( ! $char) { |
||
1013 | View Code Duplication | switch ($pos) { |
|
1014 | case self::TRIM_LEADING: |
||
1015 | $trimFn = 'LTRIM'; |
||
1016 | break; |
||
1017 | |||
1018 | case self::TRIM_TRAILING: |
||
1019 | $trimFn = 'RTRIM'; |
||
1020 | break; |
||
1021 | |||
1022 | default: |
||
1023 | return 'LTRIM(RTRIM(' . $str . '))'; |
||
1024 | } |
||
1025 | |||
1026 | return $trimFn . '(' . $str . ')'; |
||
1027 | } |
||
1028 | |||
1029 | /** Original query used to get those expressions |
||
1030 | declare @c varchar(100) = 'xxxBarxxx', @trim_char char(1) = 'x'; |
||
1031 | declare @pat varchar(10) = '%[^' + @trim_char + ']%'; |
||
1032 | select @c as string |
||
1033 | , @trim_char as trim_char |
||
1034 | , stuff(@c, 1, patindex(@pat, @c) - 1, null) as trim_leading |
||
1035 | , reverse(stuff(reverse(@c), 1, patindex(@pat, reverse(@c)) - 1, null)) as trim_trailing |
||
1036 | , 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; |
||
1037 | */ |
||
1038 | $pattern = "'%[^' + $char + ']%'"; |
||
1039 | |||
1040 | View Code Duplication | if ($pos == self::TRIM_LEADING) { |
|
1041 | return 'stuff(' . $str . ', 1, patindex(' . $pattern . ', ' . $str . ') - 1, null)'; |
||
1042 | } |
||
1043 | |||
1044 | View Code Duplication | if ($pos == self::TRIM_TRAILING) { |
|
1045 | return 'reverse(stuff(reverse(' . $str . '), 1, patindex(' . $pattern . ', reverse(' . $str . ')) - 1, null))'; |
||
1046 | } |
||
1047 | |||
1048 | 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))'; |
||
1049 | } |
||
1050 | |||
1051 | /** |
||
1052 | * {@inheritDoc} |
||
1053 | */ |
||
1054 | 3 | public function getConcatExpression() |
|
1055 | { |
||
1056 | 3 | $args = func_get_args(); |
|
1057 | |||
1058 | 3 | return '(' . implode(' + ', $args) . ')'; |
|
1059 | } |
||
1060 | |||
1061 | /** |
||
1062 | * {@inheritDoc} |
||
1063 | */ |
||
1064 | 3 | public function getListDatabasesSQL() |
|
1065 | { |
||
1066 | 3 | return 'SELECT * FROM sys.databases'; |
|
1067 | } |
||
1068 | |||
1069 | /** |
||
1070 | * {@inheritDoc} |
||
1071 | */ |
||
1072 | public function getListNamespacesSQL() |
||
1073 | { |
||
1074 | return "SELECT name FROM sys.schemas WHERE name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')"; |
||
1075 | } |
||
1076 | |||
1077 | /** |
||
1078 | * {@inheritDoc} |
||
1079 | */ |
||
1080 | public function getSubstringExpression($value, $from, $length = null) |
||
1081 | { |
||
1082 | if (!is_null($length)) { |
||
1083 | return 'SUBSTRING(' . $value . ', ' . $from . ', ' . $length . ')'; |
||
1084 | } |
||
1085 | |||
1086 | return 'SUBSTRING(' . $value . ', ' . $from . ', LEN(' . $value . ') - ' . $from . ' + 1)'; |
||
1087 | } |
||
1088 | |||
1089 | /** |
||
1090 | * {@inheritDoc} |
||
1091 | */ |
||
1092 | public function getLengthExpression($column) |
||
1093 | { |
||
1094 | return 'LEN(' . $column . ')'; |
||
1095 | } |
||
1096 | |||
1097 | /** |
||
1098 | * {@inheritDoc} |
||
1099 | */ |
||
1100 | 3 | public function getSetTransactionIsolationSQL($level) |
|
1101 | { |
||
1102 | 3 | return 'SET TRANSACTION ISOLATION LEVEL ' . $this->_getTransactionIsolationLevelSQL($level); |
|
1103 | } |
||
1104 | |||
1105 | /** |
||
1106 | * {@inheritDoc} |
||
1107 | */ |
||
1108 | 37 | public function getIntegerTypeDeclarationSQL(array $field) |
|
1109 | { |
||
1110 | 37 | return 'INT' . $this->_getCommonIntegerTypeDeclarationSQL($field); |
|
1111 | } |
||
1112 | |||
1113 | /** |
||
1114 | * {@inheritDoc} |
||
1115 | */ |
||
1116 | public function getBigIntTypeDeclarationSQL(array $field) |
||
1117 | { |
||
1118 | return 'BIGINT' . $this->_getCommonIntegerTypeDeclarationSQL($field); |
||
1119 | } |
||
1120 | |||
1121 | /** |
||
1122 | * {@inheritDoc} |
||
1123 | */ |
||
1124 | public function getSmallIntTypeDeclarationSQL(array $field) |
||
1125 | { |
||
1126 | return 'SMALLINT' . $this->_getCommonIntegerTypeDeclarationSQL($field); |
||
1127 | } |
||
1128 | |||
1129 | /** |
||
1130 | * {@inheritDoc} |
||
1131 | */ |
||
1132 | 3 | public function getGuidTypeDeclarationSQL(array $field) |
|
1133 | { |
||
1134 | 3 | return 'UNIQUEIDENTIFIER'; |
|
1135 | } |
||
1136 | |||
1137 | /** |
||
1138 | * {@inheritDoc} |
||
1139 | */ |
||
1140 | 69 | protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed) |
|
1141 | { |
||
1142 | 69 | return $fixed ? ($length ? 'NCHAR(' . $length . ')' : 'CHAR(255)') : ($length ? 'NVARCHAR(' . $length . ')' : 'NVARCHAR(255)'); |
|
1143 | } |
||
1144 | |||
1145 | /** |
||
1146 | * {@inheritdoc} |
||
1147 | */ |
||
1148 | 3 | protected function getBinaryTypeDeclarationSQLSnippet($length, $fixed) |
|
1149 | { |
||
1150 | 3 | return $fixed ? 'BINARY(' . ($length ?: 255) . ')' : 'VARBINARY(' . ($length ?: 255) . ')'; |
|
1151 | } |
||
1152 | |||
1153 | /** |
||
1154 | * {@inheritdoc} |
||
1155 | */ |
||
1156 | 6 | public function getBinaryMaxLength() |
|
1157 | { |
||
1158 | 6 | return 8000; |
|
1159 | } |
||
1160 | |||
1161 | /** |
||
1162 | * {@inheritDoc} |
||
1163 | */ |
||
1164 | 5 | public function getClobTypeDeclarationSQL(array $field) |
|
1165 | { |
||
1166 | 5 | return 'VARCHAR(MAX)'; |
|
1167 | } |
||
1168 | |||
1169 | /** |
||
1170 | * {@inheritDoc} |
||
1171 | */ |
||
1172 | 37 | protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef) |
|
1173 | { |
||
1174 | 37 | return (!empty($columnDef['autoincrement'])) ? ' IDENTITY' : ''; |
|
1175 | } |
||
1176 | |||
1177 | /** |
||
1178 | * {@inheritDoc} |
||
1179 | */ |
||
1180 | public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration) |
||
1181 | { |
||
1182 | return 'DATETIME'; |
||
1183 | } |
||
1184 | |||
1185 | /** |
||
1186 | * {@inheritDoc} |
||
1187 | */ |
||
1188 | public function getDateTypeDeclarationSQL(array $fieldDeclaration) |
||
1189 | { |
||
1190 | return 'DATETIME'; |
||
1191 | } |
||
1192 | |||
1193 | /** |
||
1194 | * {@inheritDoc} |
||
1195 | */ |
||
1196 | public function getTimeTypeDeclarationSQL(array $fieldDeclaration) |
||
1197 | { |
||
1198 | return 'DATETIME'; |
||
1199 | } |
||
1200 | |||
1201 | /** |
||
1202 | * {@inheritDoc} |
||
1203 | */ |
||
1204 | 3 | public function getBooleanTypeDeclarationSQL(array $field) |
|
1205 | { |
||
1206 | 3 | return 'BIT'; |
|
1207 | } |
||
1208 | |||
1209 | /** |
||
1210 | * {@inheritDoc} |
||
1211 | */ |
||
1212 | 40 | protected function doModifyLimitQuery($query, $limit, $offset = null) |
|
1213 | { |
||
1214 | 40 | if ($limit === null) { |
|
1215 | return $query; |
||
1216 | } |
||
1217 | |||
1218 | 40 | $start = $offset + 1; |
|
1219 | 40 | $end = $offset + $limit; |
|
1220 | |||
1221 | // We'll find a SELECT or SELECT distinct and prepend TOP n to it |
||
1222 | // Even if the TOP n is very large, the use of a CTE will |
||
1223 | // allow the SQL Server query planner to optimize it so it doesn't |
||
1224 | // actually scan the entire range covered by the TOP clause. |
||
1225 | 40 | $selectPattern = '/^(\s*SELECT\s+(?:DISTINCT\s+)?)(.*)$/im'; |
|
1226 | 40 | $replacePattern = sprintf('$1%s $2', "TOP $end"); |
|
1227 | 40 | $query = preg_replace($selectPattern, $replacePattern, $query); |
|
1228 | |||
1229 | 40 | if (stristr($query, "ORDER BY")) { |
|
1230 | // Inner order by is not valid in SQL Server for our purposes |
||
1231 | // unless it's in a TOP N subquery. |
||
1232 | 26 | $query = $this->scrubInnerOrderBy($query); |
|
1233 | } |
||
1234 | |||
1235 | // Build a new limited query around the original, using a CTE |
||
1236 | 40 | return sprintf( |
|
1237 | "WITH dctrn_cte AS (%s) " |
||
1238 | . "SELECT * FROM (" |
||
1239 | . "SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS doctrine_rownum FROM dctrn_cte" |
||
1240 | . ") AS doctrine_tbl " |
||
1241 | 40 | . "WHERE doctrine_rownum BETWEEN %d AND %d ORDER BY doctrine_rownum ASC", |
|
1242 | 40 | $query, |
|
1243 | 40 | $start, |
|
1244 | 40 | $end |
|
1245 | ); |
||
1246 | } |
||
1247 | |||
1248 | /** |
||
1249 | * Remove ORDER BY clauses in subqueries - they're not supported by SQL Server. |
||
1250 | * Caveat: will leave ORDER BY in TOP N subqueries. |
||
1251 | * |
||
1252 | * @param $query |
||
1253 | * @return string |
||
1254 | */ |
||
1255 | 26 | private function scrubInnerOrderBy($query) |
|
1256 | { |
||
1257 | 26 | $count = substr_count(strtoupper($query), 'ORDER BY'); |
|
1258 | 26 | $offset = 0; |
|
1259 | |||
1260 | 26 | while ($count-- > 0) { |
|
1261 | 26 | $orderByPos = stripos($query, ' ORDER BY', $offset); |
|
1262 | 26 | if ($orderByPos === false) { |
|
1263 | 2 | break; |
|
1264 | } |
||
1265 | |||
1266 | 26 | $qLen = strlen($query); |
|
1267 | 26 | $parenCount = 0; |
|
1268 | 26 | $currentPosition = $orderByPos; |
|
1269 | |||
1270 | 26 | while ($parenCount >= 0 && $currentPosition < $qLen) { |
|
1271 | 26 | View Code Duplication | if ($query[$currentPosition] === '(') { |
1272 | 2 | $parenCount++; |
|
1273 | 26 | } elseif ($query[$currentPosition] === ')') { |
|
1274 | 14 | $parenCount--; |
|
1275 | } |
||
1276 | |||
1277 | 26 | $currentPosition++; |
|
1278 | } |
||
1279 | |||
1280 | 26 | if ($this->isOrderByInTopNSubquery($query, $orderByPos)) { |
|
1281 | // If the order by clause is in a TOP N subquery, do not remove |
||
1282 | // it and continue iteration from the current position. |
||
1283 | 23 | $offset = $currentPosition; |
|
1284 | 23 | continue; |
|
1285 | } |
||
1286 | |||
1287 | 10 | if ($currentPosition < $qLen - 1) { |
|
1288 | 10 | $query = substr($query, 0, $orderByPos) . substr($query, $currentPosition - 1); |
|
1289 | 10 | $offset = $orderByPos; |
|
1290 | } |
||
1291 | } |
||
1292 | 26 | return $query; |
|
1293 | } |
||
1294 | |||
1295 | /** |
||
1296 | * Check an ORDER BY clause to see if it is in a TOP N query or subquery. |
||
1297 | * |
||
1298 | * @param string $query The query |
||
1299 | * @param int $currentPosition Start position of ORDER BY clause |
||
1300 | * @return bool true if ORDER BY is in a TOP N query, false otherwise |
||
1301 | */ |
||
1302 | 26 | private function isOrderByInTopNSubquery($query, $currentPosition) |
|
1303 | { |
||
1304 | // Grab query text on the same nesting level as the ORDER BY clause we're examining. |
||
1305 | 26 | $subQueryBuffer = ''; |
|
1306 | 26 | $parenCount = 0; |
|
1307 | |||
1308 | // If $parenCount goes negative, we've exited the subquery we're examining. |
||
1309 | // If $currentPosition goes negative, we've reached the beginning of the query. |
||
1310 | 26 | while ($parenCount >= 0 && $currentPosition >= 0) { |
|
1311 | 26 | View Code Duplication | if ($query[$currentPosition] === '(') { |
1312 | 17 | $parenCount--; |
|
1313 | 26 | } elseif ($query[$currentPosition] === ')') { |
|
1314 | 14 | $parenCount++; |
|
1315 | } |
||
1316 | |||
1317 | // Only yank query text on the same nesting level as the ORDER BY clause. |
||
1318 | 26 | $subQueryBuffer = ($parenCount === 0 ? $query[$currentPosition] : ' ') . $subQueryBuffer; |
|
1319 | |||
1320 | 26 | $currentPosition--; |
|
1321 | } |
||
1322 | |||
1323 | 26 | if (preg_match('/SELECT\s+(DISTINCT\s+)?TOP\s/i', $subQueryBuffer)) { |
|
1324 | 23 | return true; |
|
1325 | } |
||
1326 | |||
1327 | 10 | return false; |
|
1328 | } |
||
1329 | |||
1330 | /** |
||
1331 | * {@inheritDoc} |
||
1332 | */ |
||
1333 | public function supportsLimitOffset() |
||
1334 | { |
||
1335 | return false; |
||
1336 | } |
||
1337 | |||
1338 | /** |
||
1339 | * {@inheritDoc} |
||
1340 | */ |
||
1341 | 3 | View Code Duplication | public function convertBooleans($item) |
1342 | { |
||
1343 | 3 | if (is_array($item)) { |
|
1344 | foreach ($item as $key => $value) { |
||
1345 | if (is_bool($value) || is_numeric($item)) { |
||
1346 | $item[$key] = ($value) ? 1 : 0; |
||
1347 | } |
||
1348 | } |
||
1349 | 3 | } elseif (is_bool($item) || is_numeric($item)) { |
|
1350 | 3 | $item = ($item) ? 1 : 0; |
|
1351 | } |
||
1352 | |||
1353 | 3 | return $item; |
|
1354 | } |
||
1355 | |||
1356 | /** |
||
1357 | * {@inheritDoc} |
||
1358 | */ |
||
1359 | public function getCreateTemporaryTableSnippetSQL() |
||
1360 | { |
||
1361 | return "CREATE TABLE"; |
||
1362 | } |
||
1363 | |||
1364 | /** |
||
1365 | * {@inheritDoc} |
||
1366 | */ |
||
1367 | public function getTemporaryTableName($tableName) |
||
1368 | { |
||
1369 | return '#' . $tableName; |
||
1370 | } |
||
1371 | |||
1372 | /** |
||
1373 | * {@inheritDoc} |
||
1374 | */ |
||
1375 | public function getDateTimeFormatString() |
||
1376 | { |
||
1377 | return 'Y-m-d H:i:s.000'; |
||
1378 | } |
||
1379 | |||
1380 | /** |
||
1381 | * {@inheritDoc} |
||
1382 | */ |
||
1383 | public function getDateFormatString() |
||
1384 | { |
||
1385 | return 'Y-m-d H:i:s.000'; |
||
1386 | } |
||
1387 | |||
1388 | /** |
||
1389 | * {@inheritDoc} |
||
1390 | */ |
||
1391 | public function getTimeFormatString() |
||
1392 | { |
||
1393 | return 'Y-m-d H:i:s.000'; |
||
1394 | } |
||
1395 | |||
1396 | /** |
||
1397 | * {@inheritDoc} |
||
1398 | */ |
||
1399 | public function getDateTimeTzFormatString() |
||
1400 | { |
||
1401 | return $this->getDateTimeFormatString(); |
||
1402 | } |
||
1403 | |||
1404 | /** |
||
1405 | * {@inheritDoc} |
||
1406 | */ |
||
1407 | 1 | public function getName() |
|
1408 | { |
||
1409 | 1 | return 'mssql'; |
|
1410 | } |
||
1411 | |||
1412 | /** |
||
1413 | * {@inheritDoc} |
||
1414 | */ |
||
1415 | 15 | protected function initializeDoctrineTypeMappings() |
|
1416 | { |
||
1417 | 15 | $this->doctrineTypeMapping = [ |
|
1418 | 'bigint' => 'bigint', |
||
1419 | 'numeric' => 'decimal', |
||
1420 | 'bit' => 'boolean', |
||
1421 | 'smallint' => 'smallint', |
||
1422 | 'decimal' => 'decimal', |
||
1423 | 'smallmoney' => 'integer', |
||
1424 | 'int' => 'integer', |
||
1425 | 'tinyint' => 'smallint', |
||
1426 | 'money' => 'integer', |
||
1427 | 'float' => 'float', |
||
1428 | 'real' => 'float', |
||
1429 | 'double' => 'float', |
||
1430 | 'double precision' => 'float', |
||
1431 | 'smalldatetime' => 'datetime', |
||
1432 | 'datetime' => 'datetime', |
||
1433 | 'char' => 'string', |
||
1434 | 'varchar' => 'string', |
||
1435 | 'text' => 'text', |
||
1436 | 'nchar' => 'string', |
||
1437 | 'nvarchar' => 'string', |
||
1438 | 'ntext' => 'text', |
||
1439 | 'binary' => 'binary', |
||
1440 | 'varbinary' => 'binary', |
||
1441 | 'image' => 'blob', |
||
1442 | 'uniqueidentifier' => 'guid', |
||
1443 | ]; |
||
1444 | 15 | } |
|
1445 | |||
1446 | /** |
||
1447 | * {@inheritDoc} |
||
1448 | */ |
||
1449 | public function createSavePoint($savepoint) |
||
1450 | { |
||
1451 | return 'SAVE TRANSACTION ' . $savepoint; |
||
1452 | } |
||
1453 | |||
1454 | /** |
||
1455 | * {@inheritDoc} |
||
1456 | */ |
||
1457 | public function releaseSavePoint($savepoint) |
||
1458 | { |
||
1459 | return ''; |
||
1460 | } |
||
1461 | |||
1462 | /** |
||
1463 | * {@inheritDoc} |
||
1464 | */ |
||
1465 | public function rollbackSavePoint($savepoint) |
||
1466 | { |
||
1467 | return 'ROLLBACK TRANSACTION ' . $savepoint; |
||
1468 | } |
||
1469 | |||
1470 | /** |
||
1471 | * {@inheritdoc} |
||
1472 | */ |
||
1473 | 21 | public function getForeignKeyReferentialActionSQL($action) |
|
1474 | { |
||
1475 | // RESTRICT is not supported, therefore falling back to NO ACTION. |
||
1476 | 21 | if (strtoupper($action) === 'RESTRICT') { |
|
1477 | 3 | return 'NO ACTION'; |
|
1478 | } |
||
1479 | |||
1480 | 18 | return parent::getForeignKeyReferentialActionSQL($action); |
|
1481 | } |
||
1482 | |||
1483 | /** |
||
1484 | * {@inheritDoc} |
||
1485 | */ |
||
1486 | 7 | View Code Duplication | public function appendLockHint($fromClause, $lockMode) |
1487 | { |
||
1488 | switch (true) { |
||
1489 | 7 | case LockMode::NONE === $lockMode: |
|
1490 | 1 | return $fromClause . ' WITH (NOLOCK)'; |
|
1491 | |||
1492 | 6 | case LockMode::PESSIMISTIC_READ === $lockMode: |
|
1493 | 1 | return $fromClause . ' WITH (HOLDLOCK, ROWLOCK)'; |
|
1494 | |||
1495 | 5 | case LockMode::PESSIMISTIC_WRITE === $lockMode: |
|
1496 | 1 | return $fromClause . ' WITH (UPDLOCK, ROWLOCK)'; |
|
1497 | |||
1498 | default: |
||
1499 | 4 | return $fromClause; |
|
1500 | } |
||
1501 | } |
||
1502 | |||
1503 | /** |
||
1504 | * {@inheritDoc} |
||
1505 | */ |
||
1506 | public function getForUpdateSQL() |
||
1507 | { |
||
1508 | return ' '; |
||
1509 | } |
||
1510 | |||
1511 | /** |
||
1512 | * {@inheritDoc} |
||
1513 | */ |
||
1514 | 57 | protected function getReservedKeywordsClass() |
|
1515 | { |
||
1516 | 57 | return Keywords\SQLServerKeywords::class; |
|
1517 | } |
||
1518 | |||
1519 | /** |
||
1520 | * {@inheritDoc} |
||
1521 | */ |
||
1522 | 91 | public function quoteSingleIdentifier($str) |
|
1523 | { |
||
1524 | 91 | return "[" . str_replace("]", "][", $str) . "]"; |
|
1525 | } |
||
1526 | |||
1527 | /** |
||
1528 | * {@inheritDoc} |
||
1529 | */ |
||
1530 | 3 | public function getTruncateTableSQL($tableName, $cascade = false) |
|
1531 | { |
||
1532 | 3 | $tableIdentifier = new Identifier($tableName); |
|
1533 | |||
1534 | 3 | return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this); |
|
1535 | } |
||
1536 | |||
1537 | /** |
||
1538 | * {@inheritDoc} |
||
1539 | */ |
||
1540 | 3 | public function getBlobTypeDeclarationSQL(array $field) |
|
1541 | { |
||
1542 | 3 | return 'VARBINARY(MAX)'; |
|
1543 | } |
||
1544 | |||
1545 | /** |
||
1546 | * {@inheritDoc} |
||
1547 | */ |
||
1548 | 57 | public function getDefaultValueDeclarationSQL($field) |
|
1549 | { |
||
1550 | 57 | if ( ! isset($field['default'])) { |
|
1551 | return empty($field['notnull']) ? ' NULL' : ''; |
||
1552 | } |
||
1553 | |||
1554 | 57 | if ( ! isset($field['type'])) { |
|
1555 | 12 | return " DEFAULT '" . $field['default'] . "'"; |
|
1556 | } |
||
1557 | |||
1558 | 45 | $type = $field['type']; |
|
1559 | |||
1560 | 45 | if ($type instanceof Types\PhpIntegerMappingType) { |
|
1561 | 9 | return " DEFAULT " . $field['default']; |
|
1562 | } |
||
1563 | |||
1564 | 39 | View Code Duplication | if ($type instanceof Types\PhpDateTimeMappingType |
1565 | 39 | && $field['default'] == $this->getCurrentTimestampSQL()) { |
|
1566 | 3 | return " DEFAULT " . $this->getCurrentTimestampSQL(); |
|
1567 | } |
||
1568 | |||
1569 | 36 | if ($type instanceof Types\BooleanType) { |
|
1570 | 3 | return " DEFAULT '" . $this->convertBooleans($field['default']) . "'"; |
|
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||
1571 | } |
||
1572 | |||
1573 | 36 | return " DEFAULT '" . $field['default'] . "'"; |
|
1574 | } |
||
1575 | |||
1576 | /** |
||
1577 | * {@inheritdoc} |
||
1578 | * |
||
1579 | * Modifies column declaration order as it differs in Microsoft SQL Server. |
||
1580 | */ |
||
1581 | 85 | public function getColumnDeclarationSQL($name, array $field) |
|
1582 | { |
||
1583 | 85 | if (isset($field['columnDefinition'])) { |
|
1584 | 3 | $columnDef = $this->getCustomTypeDeclarationSQL($field); |
|
1585 | } else { |
||
1586 | 82 | $collation = (isset($field['collation']) && $field['collation']) ? |
|
1587 | 82 | ' ' . $this->getColumnCollationDeclarationSQL($field['collation']) : ''; |
|
1588 | |||
1589 | 82 | $notnull = (isset($field['notnull']) && $field['notnull']) ? ' NOT NULL' : ''; |
|
1590 | |||
1591 | 82 | $unique = (isset($field['unique']) && $field['unique']) ? |
|
1592 | 82 | ' ' . $this->getUniqueFieldDeclarationSQL() : ''; |
|
1593 | |||
1594 | 82 | $check = (isset($field['check']) && $field['check']) ? |
|
1595 | 82 | ' ' . $field['check'] : ''; |
|
1596 | |||
1597 | 82 | $typeDecl = $field['type']->getSQLDeclaration($field, $this); |
|
1598 | 82 | $columnDef = $typeDecl . $collation . $notnull . $unique . $check; |
|
1599 | } |
||
1600 | |||
1601 | 85 | return $name . ' ' . $columnDef; |
|
1602 | } |
||
1603 | |||
1604 | /** |
||
1605 | * Returns a unique default constraint name for a table and column. |
||
1606 | * |
||
1607 | * @param string $table Name of the table to generate the unique default constraint name for. |
||
1608 | * @param string $column Name of the column in the table to generate the unique default constraint name for. |
||
1609 | * |
||
1610 | * @return string |
||
1611 | */ |
||
1612 | 45 | private function generateDefaultConstraintName($table, $column) |
|
1613 | { |
||
1614 | 45 | return 'DF_' . $this->generateIdentifierName($table) . '_' . $this->generateIdentifierName($column); |
|
1615 | } |
||
1616 | |||
1617 | /** |
||
1618 | * Returns a hash value for a given identifier. |
||
1619 | * |
||
1620 | * @param string $identifier Identifier to generate a hash value for. |
||
1621 | * |
||
1622 | * @return string |
||
1623 | */ |
||
1624 | 48 | private function generateIdentifierName($identifier) |
|
1625 | { |
||
1626 | // Always generate name for unquoted identifiers to ensure consistency. |
||
1627 | 48 | $identifier = new Identifier($identifier); |
|
1628 | |||
1629 | 48 | return strtoupper(dechex(crc32($identifier->getName()))); |
|
1630 | } |
||
1631 | } |
||
1632 |