1 | <?php |
||
2 | |||
3 | declare(strict_types=1); |
||
4 | |||
5 | namespace Doctrine\DBAL\Platforms; |
||
6 | |||
7 | use Doctrine\DBAL\LockMode; |
||
8 | use Doctrine\DBAL\Platforms\Exception\NotSupported; |
||
9 | use Doctrine\DBAL\Schema\Column; |
||
10 | use Doctrine\DBAL\Schema\ColumnDiff; |
||
11 | use Doctrine\DBAL\Schema\Constraint; |
||
12 | use Doctrine\DBAL\Schema\ForeignKeyConstraint; |
||
13 | use Doctrine\DBAL\Schema\Identifier; |
||
14 | use Doctrine\DBAL\Schema\Index; |
||
15 | use Doctrine\DBAL\Schema\Sequence; |
||
16 | use Doctrine\DBAL\Schema\Table; |
||
17 | use Doctrine\DBAL\Schema\TableDiff; |
||
18 | use Doctrine\DBAL\TransactionIsolationLevel; |
||
19 | use InvalidArgumentException; |
||
20 | use UnexpectedValueException; |
||
21 | use function array_merge; |
||
22 | use function array_unique; |
||
23 | use function array_values; |
||
24 | use function count; |
||
25 | use function explode; |
||
26 | use function get_class; |
||
27 | use function implode; |
||
28 | use function in_array; |
||
29 | use function is_string; |
||
30 | use function preg_match; |
||
31 | use function sprintf; |
||
32 | use function strlen; |
||
33 | use function strpos; |
||
34 | use function strtoupper; |
||
35 | use function substr; |
||
36 | |||
37 | /** |
||
38 | * The SQLAnywherePlatform provides the behavior, features and SQL dialect of the |
||
39 | * SAP Sybase SQL Anywhere 12 database platform. |
||
40 | */ |
||
41 | class SQLAnywherePlatform extends AbstractPlatform |
||
42 | { |
||
43 | public const FOREIGN_KEY_MATCH_SIMPLE = 1; |
||
44 | public const FOREIGN_KEY_MATCH_FULL = 2; |
||
45 | public const FOREIGN_KEY_MATCH_SIMPLE_UNIQUE = 129; |
||
46 | public const FOREIGN_KEY_MATCH_FULL_UNIQUE = 130; |
||
47 | |||
48 | /** |
||
49 | * {@inheritdoc} |
||
50 | */ |
||
51 | 135 | public function appendLockHint(string $fromClause, ?int $lockMode) : string |
|
52 | { |
||
53 | 135 | switch (true) { |
|
54 | case $lockMode === LockMode::NONE: |
||
55 | 27 | return $fromClause . ' WITH (NOLOCK)'; |
|
56 | |||
57 | 108 | case $lockMode === LockMode::PESSIMISTIC_READ: |
|
58 | 27 | return $fromClause . ' WITH (UPDLOCK)'; |
|
59 | |||
60 | 81 | case $lockMode === LockMode::PESSIMISTIC_WRITE: |
|
61 | 27 | return $fromClause . ' WITH (XLOCK)'; |
|
62 | |||
63 | default: |
||
64 | 54 | return $fromClause; |
|
65 | } |
||
66 | } |
||
67 | |||
68 | /** |
||
69 | * {@inheritdoc} |
||
70 | * |
||
71 | * SQL Anywhere supports a maximum length of 128 bytes for identifiers. |
||
72 | */ |
||
73 | 27 | public function fixSchemaElementName(string $schemaElementName) : string |
|
74 | { |
||
75 | 27 | $maxIdentifierLength = $this->getMaxIdentifierLength(); |
|
76 | |||
77 | 27 | if (strlen($schemaElementName) > $maxIdentifierLength) { |
|
78 | 27 | return substr($schemaElementName, 0, $maxIdentifierLength); |
|
79 | } |
||
80 | |||
81 | 27 | return $schemaElementName; |
|
82 | } |
||
83 | |||
84 | /** |
||
85 | * {@inheritdoc} |
||
86 | */ |
||
87 | 189 | public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey) : string |
|
88 | { |
||
89 | 189 | $query = ''; |
|
90 | |||
91 | 189 | if ($foreignKey->hasOption('match')) { |
|
92 | 27 | $query = ' MATCH ' . $this->getForeignKeyMatchClauseSQL($foreignKey->getOption('match')); |
|
93 | } |
||
94 | |||
95 | 189 | $query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey); |
|
96 | |||
97 | 189 | if ($foreignKey->hasOption('check_on_commit') && (bool) $foreignKey->getOption('check_on_commit')) { |
|
98 | 27 | $query .= ' CHECK ON COMMIT'; |
|
99 | } |
||
100 | |||
101 | 189 | if ($foreignKey->hasOption('clustered') && (bool) $foreignKey->getOption('clustered')) { |
|
102 | 27 | $query .= ' CLUSTERED'; |
|
103 | } |
||
104 | |||
105 | 189 | if ($foreignKey->hasOption('for_olap_workload') && (bool) $foreignKey->getOption('for_olap_workload')) { |
|
106 | 27 | $query .= ' FOR OLAP WORKLOAD'; |
|
107 | } |
||
108 | |||
109 | 189 | return $query; |
|
110 | } |
||
111 | |||
112 | /** |
||
113 | * {@inheritdoc} |
||
114 | */ |
||
115 | 405 | public function getAlterTableSQL(TableDiff $diff) : array |
|
116 | { |
||
117 | 405 | $sql = []; |
|
118 | 405 | $columnSql = []; |
|
119 | 405 | $commentsSQL = []; |
|
120 | 405 | $tableSql = []; |
|
121 | 405 | $alterClauses = []; |
|
122 | |||
123 | 405 | foreach ($diff->addedColumns as $column) { |
|
124 | 108 | if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { |
|
125 | continue; |
||
126 | } |
||
127 | |||
128 | 108 | $alterClauses[] = $this->getAlterTableAddColumnClause($column); |
|
129 | |||
130 | 108 | $comment = $this->getColumnComment($column); |
|
131 | |||
132 | 108 | if ($comment === null || $comment === '') { |
|
133 | 81 | continue; |
|
134 | } |
||
135 | |||
136 | 27 | $commentsSQL[] = $this->getCommentOnColumnSQL( |
|
137 | 27 | $diff->getName($this)->getQuotedName($this), |
|
138 | 27 | $column->getQuotedName($this), |
|
139 | $comment |
||
140 | ); |
||
141 | } |
||
142 | |||
143 | 405 | foreach ($diff->removedColumns as $column) { |
|
144 | 81 | if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { |
|
145 | continue; |
||
146 | } |
||
147 | |||
148 | 81 | $alterClauses[] = $this->getAlterTableRemoveColumnClause($column); |
|
149 | } |
||
150 | |||
151 | 405 | foreach ($diff->changedColumns as $columnDiff) { |
|
152 | 216 | if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { |
|
153 | continue; |
||
154 | } |
||
155 | |||
156 | 216 | $alterClause = $this->getAlterTableChangeColumnClause($columnDiff); |
|
157 | |||
158 | 216 | if ($alterClause !== null) { |
|
159 | 135 | $alterClauses[] = $alterClause; |
|
160 | } |
||
161 | |||
162 | 216 | if (! $columnDiff->hasChanged('comment')) { |
|
163 | 135 | continue; |
|
164 | } |
||
165 | |||
166 | 81 | $column = $columnDiff->column; |
|
167 | |||
168 | 81 | $commentsSQL[] = $this->getCommentOnColumnSQL( |
|
169 | 81 | $diff->getName($this)->getQuotedName($this), |
|
170 | 81 | $column->getQuotedName($this), |
|
171 | 81 | $this->getColumnComment($column) |
|
172 | ); |
||
173 | } |
||
174 | |||
175 | 405 | foreach ($diff->renamedColumns as $oldColumnName => $column) { |
|
176 | 108 | if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { |
|
177 | continue; |
||
178 | } |
||
179 | |||
180 | 108 | $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . |
|
181 | 108 | $this->getAlterTableRenameColumnClause($oldColumnName, $column); |
|
182 | } |
||
183 | |||
184 | 405 | if (! $this->onSchemaAlterTable($diff, $tableSql)) { |
|
185 | 405 | if (! empty($alterClauses)) { |
|
186 | 162 | $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . implode(', ', $alterClauses); |
|
187 | } |
||
188 | |||
189 | 405 | $sql = array_merge($sql, $commentsSQL); |
|
190 | |||
191 | 405 | $newName = $diff->getNewName(); |
|
192 | |||
193 | 405 | if ($newName !== null) { |
|
194 | 54 | $sql[] = $this->getAlterTableClause($diff->getName($this)) . ' ' . |
|
195 | 54 | $this->getAlterTableRenameTableClause($newName); |
|
196 | } |
||
197 | |||
198 | 405 | $sql = array_merge( |
|
199 | 405 | $this->getPreAlterTableIndexForeignKeySQL($diff), |
|
200 | 405 | $sql, |
|
201 | 405 | $this->getPostAlterTableIndexForeignKeySQL($diff) |
|
202 | ); |
||
203 | } |
||
204 | |||
205 | 405 | return array_merge($sql, $tableSql, $columnSql); |
|
206 | } |
||
207 | |||
208 | /** |
||
209 | * Returns the SQL clause for creating a column in a table alteration. |
||
210 | * |
||
211 | * @param Column $column The column to add. |
||
212 | */ |
||
213 | 108 | protected function getAlterTableAddColumnClause(Column $column) : string |
|
214 | { |
||
215 | 108 | return 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); |
|
216 | } |
||
217 | |||
218 | /** |
||
219 | * Returns the SQL clause for altering a table. |
||
220 | * |
||
221 | * @param Identifier $tableName The quoted name of the table to alter. |
||
222 | */ |
||
223 | 216 | protected function getAlterTableClause(Identifier $tableName) : string |
|
224 | { |
||
225 | 216 | return 'ALTER TABLE ' . $tableName->getQuotedName($this); |
|
226 | } |
||
227 | |||
228 | /** |
||
229 | * Returns the SQL clause for dropping a column in a table alteration. |
||
230 | * |
||
231 | * @param Column $column The column to drop. |
||
232 | */ |
||
233 | 81 | protected function getAlterTableRemoveColumnClause(Column $column) : string |
|
234 | { |
||
235 | 81 | return 'DROP ' . $column->getQuotedName($this); |
|
236 | } |
||
237 | |||
238 | /** |
||
239 | * Returns the SQL clause for renaming a column in a table alteration. |
||
240 | * |
||
241 | * @param string $oldColumnName The quoted name of the column to rename. |
||
242 | * @param Column $column The column to rename to. |
||
243 | */ |
||
244 | 108 | protected function getAlterTableRenameColumnClause(string $oldColumnName, Column $column) : string |
|
245 | { |
||
246 | 108 | $oldColumnName = new Identifier($oldColumnName); |
|
247 | |||
248 | 108 | return 'RENAME ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this); |
|
249 | } |
||
250 | |||
251 | /** |
||
252 | * Returns the SQL clause for renaming a table in a table alteration. |
||
253 | * |
||
254 | * @param Identifier $newTableName The quoted name of the table to rename to. |
||
255 | */ |
||
256 | 54 | protected function getAlterTableRenameTableClause(Identifier $newTableName) : string |
|
257 | { |
||
258 | 54 | return 'RENAME ' . $newTableName->getQuotedName($this); |
|
259 | } |
||
260 | |||
261 | /** |
||
262 | * Returns the SQL clause for altering a column in a table alteration. |
||
263 | * |
||
264 | * This method returns null in case that only the column comment has changed. |
||
265 | * Changes in column comments have to be handled differently. |
||
266 | * |
||
267 | * @param ColumnDiff $columnDiff The diff of the column to alter. |
||
268 | */ |
||
269 | 216 | protected function getAlterTableChangeColumnClause(ColumnDiff $columnDiff) : ?string |
|
270 | { |
||
271 | 216 | $column = $columnDiff->column; |
|
272 | |||
273 | // Do not return alter clause if only comment has changed. |
||
274 | 216 | if (! ($columnDiff->hasChanged('comment') && count($columnDiff->changedProperties) === 1)) { |
|
275 | $columnAlterationClause = 'ALTER ' . |
||
276 | 135 | $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); |
|
277 | |||
278 | 135 | if ($columnDiff->hasChanged('default') && $column->getDefault() === null) { |
|
279 | $columnAlterationClause .= ', ALTER ' . $column->getQuotedName($this) . ' DROP DEFAULT'; |
||
280 | } |
||
281 | |||
282 | 135 | return $columnAlterationClause; |
|
283 | } |
||
284 | |||
285 | 81 | return null; |
|
286 | } |
||
287 | |||
288 | /** |
||
289 | * {@inheritdoc} |
||
290 | */ |
||
291 | 27 | public function getBigIntTypeDeclarationSQL(array $columnDef) : string |
|
292 | { |
||
293 | 27 | $columnDef['integer_type'] = 'BIGINT'; |
|
294 | |||
295 | 27 | return $this->_getCommonIntegerTypeDeclarationSQL($columnDef); |
|
296 | } |
||
297 | |||
298 | /** |
||
299 | * {@inheritdoc} |
||
300 | */ |
||
301 | 27 | public function getBlobTypeDeclarationSQL(array $field) : string |
|
302 | { |
||
303 | 27 | return 'LONG BINARY'; |
|
304 | } |
||
305 | |||
306 | /** |
||
307 | * {@inheritdoc} |
||
308 | * |
||
309 | * BIT type columns require an explicit NULL declaration |
||
310 | * in SQL Anywhere if they shall be nullable. |
||
311 | * Otherwise by just omitting the NOT NULL clause, |
||
312 | * SQL Anywhere will declare them NOT NULL nonetheless. |
||
313 | */ |
||
314 | 54 | public function getBooleanTypeDeclarationSQL(array $columnDef) : string |
|
315 | { |
||
316 | 54 | $nullClause = isset($columnDef['notnull']) && (bool) $columnDef['notnull'] === false ? ' NULL' : ''; |
|
317 | |||
318 | 54 | return 'BIT' . $nullClause; |
|
319 | } |
||
320 | |||
321 | /** |
||
322 | * {@inheritdoc} |
||
323 | */ |
||
324 | 81 | public function getClobTypeDeclarationSQL(array $field) : string |
|
325 | { |
||
326 | 81 | return 'TEXT'; |
|
327 | } |
||
328 | |||
329 | /** |
||
330 | * {@inheritdoc} |
||
331 | */ |
||
332 | 216 | public function getCommentOnColumnSQL(string $tableName, string $columnName, ?string $comment) : string |
|
333 | { |
||
334 | 216 | $tableName = new Identifier($tableName); |
|
335 | 216 | $columnName = new Identifier($columnName); |
|
336 | 216 | $comment = $comment === null ? 'NULL' : $this->quoteStringLiteral($comment); |
|
337 | |||
338 | 216 | return sprintf( |
|
339 | 16 | 'COMMENT ON COLUMN %s.%s IS %s', |
|
340 | 216 | $tableName->getQuotedName($this), |
|
341 | 216 | $columnName->getQuotedName($this), |
|
342 | 216 | $comment |
|
343 | ); |
||
344 | } |
||
345 | |||
346 | /** |
||
347 | * {@inheritdoc} |
||
348 | */ |
||
349 | 27 | public function getConcatExpression(string ...$string) : string |
|
350 | { |
||
351 | 27 | return 'STRING(' . implode(', ', $string) . ')'; |
|
352 | } |
||
353 | |||
354 | /** |
||
355 | * {@inheritdoc} |
||
356 | */ |
||
357 | 81 | public function getCreateConstraintSQL(Constraint $constraint, $table) : string |
|
358 | { |
||
359 | 81 | if ($constraint instanceof ForeignKeyConstraint) { |
|
360 | 27 | return $this->getCreateForeignKeySQL($constraint, $table); |
|
361 | } |
||
362 | |||
363 | 81 | if ($table instanceof Table) { |
|
364 | 27 | $table = $table->getQuotedName($this); |
|
365 | } |
||
366 | |||
367 | 81 | return 'ALTER TABLE ' . $table . |
|
368 | 81 | ' ADD ' . $this->getTableConstraintDeclarationSQL($constraint, $constraint->getQuotedName($this)); |
|
369 | } |
||
370 | |||
371 | /** |
||
372 | * {@inheritdoc} |
||
373 | */ |
||
374 | 27 | public function getCreateDatabaseSQL(string $database) : string |
|
375 | { |
||
376 | 27 | $database = new Identifier($database); |
|
377 | |||
378 | 27 | return "CREATE DATABASE '" . $database->getName() . "'"; |
|
379 | } |
||
380 | |||
381 | /** |
||
382 | * {@inheritdoc} |
||
383 | * |
||
384 | * Appends SQL Anywhere specific flags if given. |
||
385 | */ |
||
386 | 243 | public function getCreateIndexSQL(Index $index, $table) : string |
|
387 | { |
||
388 | 243 | return parent::getCreateIndexSQL($index, $table) . $this->getAdvancedIndexOptionsSQL($index); |
|
389 | } |
||
390 | |||
391 | /** |
||
392 | * {@inheritdoc} |
||
393 | */ |
||
394 | 54 | public function getCreatePrimaryKeySQL(Index $index, $table) : string |
|
395 | { |
||
396 | 54 | if ($table instanceof Table) { |
|
397 | 27 | $table = $table->getQuotedName($this); |
|
398 | } |
||
399 | |||
400 | 54 | return 'ALTER TABLE ' . $table . ' ADD ' . $this->getPrimaryKeyDeclarationSQL($index); |
|
401 | } |
||
402 | |||
403 | /** |
||
404 | * {@inheritdoc} |
||
405 | */ |
||
406 | 27 | public function getCreateTemporaryTableSnippetSQL() : string |
|
407 | { |
||
408 | 27 | return 'CREATE ' . $this->getTemporaryTableSQL() . ' TABLE'; |
|
409 | } |
||
410 | |||
411 | /** |
||
412 | * {@inheritdoc} |
||
413 | */ |
||
414 | 27 | public function getCreateViewSQL(string $name, string $sql) : string |
|
415 | { |
||
416 | 27 | return 'CREATE VIEW ' . $name . ' AS ' . $sql; |
|
417 | } |
||
418 | |||
419 | /** |
||
420 | * {@inheritdoc} |
||
421 | */ |
||
422 | 54 | public function getCurrentDateSQL() : string |
|
423 | { |
||
424 | 54 | return 'CURRENT DATE'; |
|
425 | } |
||
426 | |||
427 | /** |
||
428 | * {@inheritdoc} |
||
429 | */ |
||
430 | 27 | public function getCurrentTimeSQL() : string |
|
431 | { |
||
432 | 27 | return 'CURRENT TIME'; |
|
433 | } |
||
434 | |||
435 | /** |
||
436 | * {@inheritdoc} |
||
437 | */ |
||
438 | 54 | public function getCurrentTimestampSQL() : string |
|
439 | { |
||
440 | 54 | return 'CURRENT TIMESTAMP'; |
|
441 | } |
||
442 | |||
443 | /** |
||
444 | * {@inheritdoc} |
||
445 | */ |
||
446 | 27 | protected function getDateArithmeticIntervalExpression(string $date, string $operator, string $interval, string $unit) : string |
|
447 | { |
||
448 | 27 | $factorClause = ''; |
|
449 | |||
450 | 27 | if ($operator === '-') { |
|
451 | 27 | $factorClause = '-1 * '; |
|
452 | } |
||
453 | |||
454 | 27 | return 'DATEADD(' . $unit . ', ' . $factorClause . $interval . ', ' . $date . ')'; |
|
455 | } |
||
456 | |||
457 | /** |
||
458 | * {@inheritdoc} |
||
459 | */ |
||
460 | 27 | public function getDateDiffExpression(string $date1, string $date2) : string |
|
461 | { |
||
462 | 27 | return 'DATEDIFF(day, ' . $date2 . ', ' . $date1 . ')'; |
|
463 | } |
||
464 | |||
465 | /** |
||
466 | * {@inheritdoc} |
||
467 | */ |
||
468 | 27 | public function getDateTimeFormatString() : string |
|
469 | { |
||
470 | 27 | return 'Y-m-d H:i:s.u'; |
|
471 | } |
||
472 | |||
473 | /** |
||
474 | * {@inheritdoc} |
||
475 | */ |
||
476 | 27 | public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration) : string |
|
477 | { |
||
478 | 27 | return 'DATETIME'; |
|
479 | } |
||
480 | |||
481 | /** |
||
482 | * {@inheritdoc} |
||
483 | */ |
||
484 | 27 | public function getDateTimeTzFormatString() : string |
|
485 | { |
||
486 | 27 | return 'Y-m-d H:i:s.uP'; |
|
487 | } |
||
488 | |||
489 | /** |
||
490 | * {@inheritdoc} |
||
491 | */ |
||
492 | 27 | public function getDateTypeDeclarationSQL(array $fieldDeclaration) : string |
|
493 | { |
||
494 | 27 | return 'DATE'; |
|
495 | } |
||
496 | |||
497 | /** |
||
498 | * {@inheritdoc} |
||
499 | */ |
||
500 | 27 | public function getDefaultTransactionIsolationLevel() : int |
|
501 | { |
||
502 | 27 | return TransactionIsolationLevel::READ_UNCOMMITTED; |
|
503 | } |
||
504 | |||
505 | /** |
||
506 | * {@inheritdoc} |
||
507 | */ |
||
508 | 27 | public function getDropDatabaseSQL(string $database) : string |
|
509 | { |
||
510 | 27 | $database = new Identifier($database); |
|
511 | |||
512 | 27 | return "DROP DATABASE '" . $database->getName() . "'"; |
|
513 | } |
||
514 | |||
515 | /** |
||
516 | * {@inheritdoc} |
||
517 | */ |
||
518 | 27 | public function getDropIndexSQL($index, $table = null) : string |
|
519 | { |
||
520 | 27 | if ($index instanceof Index) { |
|
521 | 27 | $index = $index->getQuotedName($this); |
|
522 | } |
||
523 | |||
524 | 27 | if (! is_string($index)) { |
|
525 | throw new InvalidArgumentException( |
||
526 | sprintf('SQLAnywherePlatform::getDropIndexSQL() expects $index parameter to be a string or an instance of %s.', Index::class) |
||
527 | ); |
||
528 | } |
||
529 | |||
530 | 27 | if (! isset($table)) { |
|
531 | 27 | return 'DROP INDEX ' . $index; |
|
532 | } |
||
533 | |||
534 | 27 | if ($table instanceof Table) { |
|
535 | 27 | $table = $table->getQuotedName($this); |
|
536 | } |
||
537 | |||
538 | 27 | if (! is_string($table)) { |
|
539 | throw new InvalidArgumentException( |
||
540 | sprintf('SQLAnywherePlatform::getDropIndexSQL() expects $table parameter to be a string or an instance of %s.', Index::class) |
||
541 | ); |
||
542 | } |
||
543 | |||
544 | 27 | return 'DROP INDEX ' . $table . '.' . $index; |
|
545 | } |
||
546 | |||
547 | /** |
||
548 | * {@inheritdoc} |
||
549 | */ |
||
550 | 27 | public function getDropViewSQL(string $name) : string |
|
551 | { |
||
552 | 27 | return 'DROP VIEW ' . $name; |
|
553 | } |
||
554 | |||
555 | /** |
||
556 | * {@inheritdoc} |
||
557 | */ |
||
558 | 270 | public function getForeignKeyBaseDeclarationSQL(ForeignKeyConstraint $foreignKey) : string |
|
559 | { |
||
560 | 270 | $sql = ''; |
|
561 | 270 | $foreignKeyName = $foreignKey->getName(); |
|
562 | 270 | $localColumns = $foreignKey->getQuotedLocalColumns($this); |
|
563 | 270 | $foreignColumns = $foreignKey->getQuotedForeignColumns($this); |
|
564 | 270 | $foreignTableName = $foreignKey->getQuotedForeignTableName($this); |
|
565 | |||
566 | 270 | if (! empty($foreignKeyName)) { |
|
567 | 162 | $sql .= 'CONSTRAINT ' . $foreignKey->getQuotedName($this) . ' '; |
|
568 | } |
||
569 | |||
570 | 270 | if (empty($localColumns)) { |
|
571 | 27 | throw new InvalidArgumentException('Incomplete definition. "local" required.'); |
|
572 | } |
||
573 | |||
574 | 243 | if (empty($foreignColumns)) { |
|
575 | 27 | throw new InvalidArgumentException('Incomplete definition. "foreign" required.'); |
|
576 | } |
||
577 | |||
578 | 216 | if (empty($foreignTableName)) { |
|
579 | 27 | throw new InvalidArgumentException('Incomplete definition. "foreignTable" required.'); |
|
580 | } |
||
581 | |||
582 | 189 | if ($foreignKey->hasOption('notnull') && (bool) $foreignKey->getOption('notnull')) { |
|
583 | 27 | $sql .= 'NOT NULL '; |
|
584 | } |
||
585 | |||
586 | return $sql . |
||
587 | 189 | 'FOREIGN KEY (' . $this->getColumnsFieldDeclarationListSQL($localColumns) . ') ' . |
|
588 | 189 | 'REFERENCES ' . $foreignKey->getQuotedForeignTableName($this) . |
|
589 | 189 | ' (' . $this->getColumnsFieldDeclarationListSQL($foreignColumns) . ')'; |
|
590 | } |
||
591 | |||
592 | /** |
||
593 | * Returns foreign key MATCH clause for given type. |
||
594 | * |
||
595 | * @param int $type The foreign key match type |
||
596 | * |
||
597 | * @throws InvalidArgumentException If unknown match type given. |
||
598 | */ |
||
599 | 81 | public function getForeignKeyMatchClauseSQL(int $type) : string |
|
600 | { |
||
601 | switch ($type) { |
||
602 | 81 | case self::FOREIGN_KEY_MATCH_SIMPLE: |
|
603 | 27 | return 'SIMPLE'; |
|
604 | |||
605 | break; |
||
0 ignored issues
–
show
|
|||
606 | 81 | case self::FOREIGN_KEY_MATCH_FULL: |
|
607 | 27 | return 'FULL'; |
|
608 | |||
609 | break; |
||
610 | 81 | case self::FOREIGN_KEY_MATCH_SIMPLE_UNIQUE: |
|
611 | 54 | return 'UNIQUE SIMPLE'; |
|
612 | |||
613 | break; |
||
614 | 54 | case self::FOREIGN_KEY_MATCH_FULL_UNIQUE: |
|
615 | 27 | return 'UNIQUE FULL'; |
|
616 | default: |
||
617 | 27 | throw new InvalidArgumentException(sprintf('Invalid foreign key match type "%s".', $type)); |
|
618 | } |
||
619 | } |
||
620 | |||
621 | /** |
||
622 | * {@inheritdoc} |
||
623 | */ |
||
624 | 216 | public function getForeignKeyReferentialActionSQL(string $action) : string |
|
625 | { |
||
626 | // NO ACTION is not supported, therefore falling back to RESTRICT. |
||
627 | 216 | if (strtoupper($action) === 'NO ACTION') { |
|
628 | 27 | return 'RESTRICT'; |
|
629 | } |
||
630 | |||
631 | 189 | return parent::getForeignKeyReferentialActionSQL($action); |
|
632 | } |
||
633 | |||
634 | /** |
||
635 | * {@inheritdoc} |
||
636 | */ |
||
637 | 27 | public function getForUpdateSQL() : string |
|
638 | { |
||
639 | 27 | return ''; |
|
640 | } |
||
641 | |||
642 | /** |
||
643 | * {@inheritdoc} |
||
644 | */ |
||
645 | 54 | public function getGuidTypeDeclarationSQL(array $column) : string |
|
646 | { |
||
647 | 54 | return 'UNIQUEIDENTIFIER'; |
|
648 | } |
||
649 | |||
650 | /** |
||
651 | * {@inheritdoc} |
||
652 | */ |
||
653 | 54 | public function getIndexDeclarationSQL(string $name, Index $index) : string |
|
654 | { |
||
655 | // Index declaration in statements like CREATE TABLE is not supported. |
||
656 | 54 | throw NotSupported::new(__METHOD__); |
|
657 | } |
||
658 | |||
659 | /** |
||
660 | * {@inheritdoc} |
||
661 | */ |
||
662 | 297 | public function getIntegerTypeDeclarationSQL(array $columnDef) : string |
|
663 | { |
||
664 | 297 | $columnDef['integer_type'] = 'INT'; |
|
665 | |||
666 | 297 | return $this->_getCommonIntegerTypeDeclarationSQL($columnDef); |
|
667 | } |
||
668 | |||
669 | /** |
||
670 | * {@inheritdoc} |
||
671 | */ |
||
672 | public function getListDatabasesSQL() : string |
||
673 | { |
||
674 | return 'SELECT db_name(number) AS name FROM sa_db_list()'; |
||
675 | } |
||
676 | |||
677 | /** |
||
678 | * {@inheritdoc} |
||
679 | */ |
||
680 | 27 | public function getListTableColumnsSQL(string $table, ?string $database = null) : string |
|
681 | { |
||
682 | 27 | $user = 'USER_NAME()'; |
|
683 | |||
684 | 27 | if (strpos($table, '.') !== false) { |
|
685 | 27 | [$user, $table] = explode('.', $table); |
|
686 | 27 | $user = $this->quoteStringLiteral($user); |
|
687 | } |
||
688 | |||
689 | 27 | return sprintf( |
|
690 | <<<'SQL' |
||
691 | 2 | SELECT col.column_name, |
|
692 | COALESCE(def.user_type_name, def.domain_name) AS 'type', |
||
693 | def.declared_width AS 'length', |
||
694 | def.scale, |
||
695 | CHARINDEX('unsigned', def.domain_name) AS 'unsigned', |
||
696 | IF col.nulls = 'Y' THEN 0 ELSE 1 ENDIF AS 'notnull', |
||
697 | col."default", |
||
698 | def.is_autoincrement AS 'autoincrement', |
||
699 | rem.remarks AS 'comment' |
||
700 | FROM sa_describe_query('SELECT * FROM "%s"') AS def |
||
701 | JOIN SYS.SYSTABCOL AS col |
||
702 | ON col.table_id = def.base_table_id AND col.column_id = def.base_column_id |
||
703 | LEFT JOIN SYS.SYSREMARK AS rem |
||
704 | ON col.object_id = rem.object_id |
||
705 | WHERE def.base_owner_name = %s |
||
706 | ORDER BY def.base_column_id ASC |
||
707 | SQL |
||
708 | , |
||
709 | 27 | $table, |
|
710 | 27 | $user |
|
711 | ); |
||
712 | } |
||
713 | |||
714 | /** |
||
715 | * {@inheritdoc} |
||
716 | * |
||
717 | * @todo Where is this used? Which information should be retrieved? |
||
718 | */ |
||
719 | 54 | public function getListTableConstraintsSQL(string $table) : string |
|
720 | { |
||
721 | 54 | $user = ''; |
|
722 | |||
723 | 54 | if (strpos($table, '.') !== false) { |
|
724 | 27 | [$user, $table] = explode('.', $table); |
|
725 | 27 | $user = $this->quoteStringLiteral($user); |
|
726 | 27 | $table = $this->quoteStringLiteral($table); |
|
727 | } else { |
||
728 | 27 | $table = $this->quoteStringLiteral($table); |
|
729 | } |
||
730 | |||
731 | 54 | return sprintf( |
|
732 | <<<'SQL' |
||
733 | 4 | SELECT con.* |
|
734 | FROM SYS.SYSCONSTRAINT AS con |
||
735 | JOIN SYS.SYSTAB AS tab ON con.table_object_id = tab.object_id |
||
736 | WHERE tab.table_name = %s |
||
737 | AND tab.creator = USER_ID(%s) |
||
738 | SQL |
||
739 | , |
||
740 | 54 | $table, |
|
741 | 54 | $user |
|
742 | ); |
||
743 | } |
||
744 | |||
745 | /** |
||
746 | * {@inheritdoc} |
||
747 | */ |
||
748 | 54 | public function getListTableForeignKeysSQL(string $table, ?string $database = null) : string |
|
749 | { |
||
750 | 54 | $user = ''; |
|
751 | |||
752 | 54 | if (strpos($table, '.') !== false) { |
|
753 | 27 | [$user, $table] = explode('.', $table); |
|
754 | 27 | $user = $this->quoteStringLiteral($user); |
|
755 | 27 | $table = $this->quoteStringLiteral($table); |
|
756 | } else { |
||
757 | 27 | $table = $this->quoteStringLiteral($table); |
|
758 | } |
||
759 | |||
760 | 54 | return sprintf( |
|
761 | <<<'SQL' |
||
762 | 4 | SELECT fcol.column_name AS local_column, |
|
763 | ptbl.table_name AS foreign_table, |
||
764 | pcol.column_name AS foreign_column, |
||
765 | idx.index_name, |
||
766 | IF fk.nulls = 'N' |
||
767 | THEN 1 |
||
768 | ELSE NULL |
||
769 | ENDIF AS notnull, |
||
770 | CASE ut.referential_action |
||
771 | WHEN 'C' THEN 'CASCADE' |
||
772 | WHEN 'D' THEN 'SET DEFAULT' |
||
773 | WHEN 'N' THEN 'SET NULL' |
||
774 | WHEN 'R' THEN 'RESTRICT' |
||
775 | ELSE NULL |
||
776 | END AS on_update, |
||
777 | CASE dt.referential_action |
||
778 | WHEN 'C' THEN 'CASCADE' |
||
779 | WHEN 'D' THEN 'SET DEFAULT' |
||
780 | WHEN 'N' THEN 'SET NULL' |
||
781 | WHEN 'R' THEN 'RESTRICT' |
||
782 | ELSE NULL |
||
783 | END AS on_delete, |
||
784 | IF fk.check_on_commit = 'Y' |
||
785 | THEN 1 |
||
786 | ELSE NULL |
||
787 | ENDIF AS check_on_commit, -- check_on_commit flag |
||
788 | IF ftbl.clustered_index_id = idx.index_id |
||
789 | THEN 1 |
||
790 | ELSE NULL |
||
791 | ENDIF AS 'clustered', -- clustered flag |
||
792 | IF fk.match_type = 0 |
||
793 | THEN NULL |
||
794 | ELSE fk.match_type |
||
795 | ENDIF AS 'match', -- match option |
||
796 | IF pidx.max_key_distance = 1 |
||
797 | THEN 1 |
||
798 | ELSE NULL |
||
799 | ENDIF AS for_olap_workload -- for_olap_workload flag |
||
800 | FROM SYS.SYSFKEY AS fk |
||
801 | JOIN SYS.SYSIDX AS idx |
||
802 | ON fk.foreign_table_id = idx.table_id |
||
803 | AND fk.foreign_index_id = idx.index_id |
||
804 | JOIN SYS.SYSPHYSIDX pidx |
||
805 | ON idx.table_id = pidx.table_id |
||
806 | AND idx.phys_index_id = pidx.phys_index_id |
||
807 | JOIN SYS.SYSTAB AS ptbl |
||
808 | ON fk.primary_table_id = ptbl.table_id |
||
809 | JOIN SYS.SYSTAB AS ftbl |
||
810 | ON fk.foreign_table_id = ftbl.table_id |
||
811 | JOIN SYS.SYSIDXCOL AS idxcol |
||
812 | ON idx.table_id = idxcol.table_id |
||
813 | AND idx.index_id = idxcol.index_id |
||
814 | JOIN SYS.SYSTABCOL AS pcol |
||
815 | ON ptbl.table_id = pcol.table_id |
||
816 | AND idxcol.primary_column_id = pcol.column_id |
||
817 | JOIN SYS.SYSTABCOL AS fcol |
||
818 | ON ftbl.table_id = fcol.table_id |
||
819 | AND idxcol.column_id = fcol.column_id |
||
820 | LEFT JOIN SYS.SYSTRIGGER ut |
||
821 | ON fk.foreign_table_id = ut.foreign_table_id |
||
822 | AND fk.foreign_index_id = ut.foreign_key_id |
||
823 | AND ut.event = 'C' |
||
824 | LEFT JOIN SYS.SYSTRIGGER dt |
||
825 | ON fk.foreign_table_id = dt.foreign_table_id |
||
826 | AND fk.foreign_index_id = dt.foreign_key_id |
||
827 | AND dt.event = 'D' |
||
828 | WHERE ftbl.table_name = %s |
||
829 | AND ftbl.creator = USER_ID(%s) |
||
830 | ORDER BY fk.foreign_index_id ASC, idxcol.sequence ASC |
||
831 | SQL |
||
832 | , |
||
833 | 54 | $table, |
|
834 | 54 | $user |
|
835 | ); |
||
836 | } |
||
837 | |||
838 | /** |
||
839 | * {@inheritdoc} |
||
840 | */ |
||
841 | 54 | public function getListTableIndexesSQL(string $table, ?string $currentDatabase = null) : string |
|
842 | { |
||
843 | 54 | $user = ''; |
|
844 | |||
845 | 54 | if (strpos($table, '.') !== false) { |
|
846 | 27 | [$user, $table] = explode('.', $table); |
|
847 | 27 | $user = $this->quoteStringLiteral($user); |
|
848 | 27 | $table = $this->quoteStringLiteral($table); |
|
849 | } else { |
||
850 | 27 | $table = $this->quoteStringLiteral($table); |
|
851 | } |
||
852 | |||
853 | 54 | return sprintf( |
|
854 | <<<'SQL' |
||
855 | 4 | SELECT idx.index_name AS key_name, |
|
856 | IF idx.index_category = 1 |
||
857 | THEN 1 |
||
858 | ELSE 0 |
||
859 | ENDIF AS 'primary', |
||
860 | col.column_name, |
||
861 | IF idx."unique" IN(1, 2, 5) |
||
862 | THEN 0 |
||
863 | ELSE 1 |
||
864 | ENDIF AS non_unique, |
||
865 | IF tbl.clustered_index_id = idx.index_id |
||
866 | THEN 1 |
||
867 | ELSE NULL |
||
868 | ENDIF AS 'clustered', -- clustered flag |
||
869 | IF idx."unique" = 5 |
||
870 | THEN 1 |
||
871 | ELSE NULL |
||
872 | ENDIF AS with_nulls_not_distinct, -- with_nulls_not_distinct flag |
||
873 | IF pidx.max_key_distance = 1 |
||
874 | THEN 1 |
||
875 | ELSE NULL |
||
876 | ENDIF AS for_olap_workload -- for_olap_workload flag |
||
877 | FROM SYS.SYSIDX AS idx |
||
878 | JOIN SYS.SYSPHYSIDX pidx |
||
879 | ON idx.table_id = pidx.table_id |
||
880 | AND idx.phys_index_id = pidx.phys_index_id |
||
881 | JOIN SYS.SYSIDXCOL AS idxcol |
||
882 | ON idx.table_id = idxcol.table_id AND idx.index_id = idxcol.index_id |
||
883 | JOIN SYS.SYSTABCOL AS col |
||
884 | ON idxcol.table_id = col.table_id AND idxcol.column_id = col.column_id |
||
885 | JOIN SYS.SYSTAB AS tbl |
||
886 | ON idx.table_id = tbl.table_id |
||
887 | WHERE tbl.table_name = %s |
||
888 | AND tbl.creator = USER_ID(%s) |
||
889 | AND idx.index_category != 2 -- exclude indexes implicitly created by foreign key constraints |
||
890 | ORDER BY idx.index_id ASC, idxcol.sequence ASC |
||
891 | SQL |
||
892 | , |
||
893 | 54 | $table, |
|
894 | 54 | $user |
|
895 | ); |
||
896 | } |
||
897 | |||
898 | /** |
||
899 | * {@inheritdoc} |
||
900 | */ |
||
901 | public function getListTablesSQL() : string |
||
902 | { |
||
903 | return "SELECT tbl.table_name |
||
904 | FROM SYS.SYSTAB AS tbl |
||
905 | JOIN SYS.SYSUSER AS usr ON tbl.creator = usr.user_id |
||
906 | JOIN dbo.SYSOBJECTS AS obj ON tbl.object_id = obj.id |
||
907 | WHERE tbl.table_type IN(1, 3) -- 'BASE', 'GBL TEMP' |
||
908 | AND usr.user_name NOT IN('SYS', 'dbo', 'rs_systabgroup') -- exclude system users |
||
909 | AND obj.type = 'U' -- user created tables only |
||
910 | ORDER BY tbl.table_name ASC"; |
||
911 | } |
||
912 | |||
913 | /** |
||
914 | * {@inheritdoc} |
||
915 | * |
||
916 | * @todo Where is this used? Which information should be retrieved? |
||
917 | */ |
||
918 | public function getListUsersSQL() : string |
||
919 | { |
||
920 | return 'SELECT * FROM SYS.SYSUSER ORDER BY user_name ASC'; |
||
921 | } |
||
922 | |||
923 | /** |
||
924 | * {@inheritdoc} |
||
925 | */ |
||
926 | public function getListViewsSQL(string $database) : string |
||
927 | { |
||
928 | return "SELECT tbl.table_name, v.view_def |
||
929 | FROM SYS.SYSVIEW v |
||
930 | JOIN SYS.SYSTAB tbl ON v.view_object_id = tbl.object_id |
||
931 | JOIN SYS.SYSUSER usr ON tbl.creator = usr.user_id |
||
932 | JOIN dbo.SYSOBJECTS obj ON tbl.object_id = obj.id |
||
933 | WHERE usr.user_name NOT IN('SYS', 'dbo', 'rs_systabgroup') -- exclude system users |
||
934 | ORDER BY tbl.table_name ASC"; |
||
935 | } |
||
936 | |||
937 | /** |
||
938 | * {@inheritdoc} |
||
939 | */ |
||
940 | 27 | public function getLocateExpression(string $string, string $substring, ?string $start = null) : string |
|
941 | { |
||
942 | 27 | if ($start === null) { |
|
943 | 27 | return sprintf('LOCATE(%s, %s)', $string, $substring); |
|
944 | } |
||
945 | |||
946 | 27 | return sprintf('LOCATE(%s, %s, %s)', $string, $substring, $start); |
|
947 | } |
||
948 | |||
949 | /** |
||
950 | * {@inheritdoc} |
||
951 | */ |
||
952 | 54 | public function getMaxIdentifierLength() : int |
|
953 | { |
||
954 | 54 | return 128; |
|
955 | } |
||
956 | |||
957 | /** |
||
958 | * {@inheritdoc} |
||
959 | */ |
||
960 | 27 | public function getMd5Expression(string $string) : string |
|
961 | { |
||
962 | 27 | return 'HASH(' . $string . ", 'MD5')"; |
|
963 | } |
||
964 | |||
965 | /** |
||
966 | * {@inheritdoc} |
||
967 | */ |
||
968 | public function getRegexpExpression() : string |
||
969 | { |
||
970 | return 'REGEXP'; |
||
971 | } |
||
972 | |||
973 | /** |
||
974 | * {@inheritdoc} |
||
975 | */ |
||
976 | 135 | public function getName() : string |
|
977 | { |
||
978 | 135 | return 'sqlanywhere'; |
|
979 | } |
||
980 | |||
981 | /** |
||
982 | * Obtain DBMS specific SQL code portion needed to set a primary key |
||
983 | * declaration to be used in statements like ALTER TABLE. |
||
984 | * |
||
985 | * @param Index $index Index definition |
||
986 | * @param string $name Name of the primary key |
||
987 | * |
||
988 | * @return string DBMS specific SQL code portion needed to set a primary key |
||
989 | * |
||
990 | * @throws InvalidArgumentException If the given index is not a primary key. |
||
991 | */ |
||
992 | 108 | public function getPrimaryKeyDeclarationSQL(Index $index, ?string $name = null) : string |
|
993 | { |
||
994 | 108 | if (! $index->isPrimary()) { |
|
995 | throw new InvalidArgumentException( |
||
996 | 'Can only create primary key declarations with getPrimaryKeyDeclarationSQL()' |
||
997 | ); |
||
998 | } |
||
999 | |||
1000 | 108 | return $this->getTableConstraintDeclarationSQL($index, $name); |
|
1001 | } |
||
1002 | |||
1003 | /** |
||
1004 | * {@inheritdoc} |
||
1005 | */ |
||
1006 | 27 | public function getSetTransactionIsolationSQL(int $level) : string |
|
1007 | { |
||
1008 | 27 | return 'SET TEMPORARY OPTION isolation_level = ' . $this->_getTransactionIsolationLevelSQL($level); |
|
1009 | } |
||
1010 | |||
1011 | /** |
||
1012 | * {@inheritdoc} |
||
1013 | */ |
||
1014 | 27 | public function getSmallIntTypeDeclarationSQL(array $columnDef) : string |
|
1015 | { |
||
1016 | 27 | $columnDef['integer_type'] = 'SMALLINT'; |
|
1017 | |||
1018 | 27 | return $this->_getCommonIntegerTypeDeclarationSQL($columnDef); |
|
1019 | } |
||
1020 | |||
1021 | /** |
||
1022 | * Returns the SQL statement for starting an existing database. |
||
1023 | * |
||
1024 | * In SQL Anywhere you can start and stop databases on a |
||
1025 | * database server instance. |
||
1026 | * This is a required statement after having created a new database |
||
1027 | * as it has to be explicitly started to be usable. |
||
1028 | * SQL Anywhere does not automatically start a database after creation! |
||
1029 | * |
||
1030 | * @param string $database Name of the database to start. |
||
1031 | */ |
||
1032 | 27 | public function getStartDatabaseSQL(string $database) : string |
|
1033 | { |
||
1034 | 27 | $database = new Identifier($database); |
|
1035 | |||
1036 | 27 | return "START DATABASE '" . $database->getName() . "' AUTOSTOP OFF"; |
|
1037 | } |
||
1038 | |||
1039 | /** |
||
1040 | * Returns the SQL statement for stopping a running database. |
||
1041 | * |
||
1042 | * In SQL Anywhere you can start and stop databases on a |
||
1043 | * database server instance. |
||
1044 | * This is a required statement before dropping an existing database |
||
1045 | * as it has to be explicitly stopped before it can be dropped. |
||
1046 | * |
||
1047 | * @param string $database Name of the database to stop. |
||
1048 | */ |
||
1049 | 27 | public function getStopDatabaseSQL(string $database) : string |
|
1050 | { |
||
1051 | 27 | $database = new Identifier($database); |
|
1052 | |||
1053 | 27 | return 'STOP DATABASE "' . $database->getName() . '" UNCONDITIONALLY'; |
|
1054 | } |
||
1055 | |||
1056 | /** |
||
1057 | * {@inheritdoc} |
||
1058 | */ |
||
1059 | 27 | public function getSubstringExpression(string $string, string $start, ?string $length = null) : string |
|
1060 | { |
||
1061 | 27 | if ($length === null) { |
|
1062 | 27 | return sprintf('SUBSTRING(%s, %s)', $string, $start); |
|
1063 | } |
||
1064 | |||
1065 | 27 | return sprintf('SUBSTRING(%s, %s, %s)', $string, $start, $length); |
|
1066 | } |
||
1067 | |||
1068 | /** |
||
1069 | * {@inheritdoc} |
||
1070 | */ |
||
1071 | 54 | public function getTemporaryTableSQL() : string |
|
1072 | { |
||
1073 | 54 | return 'GLOBAL TEMPORARY'; |
|
1074 | } |
||
1075 | |||
1076 | /** |
||
1077 | * {@inheritdoc} |
||
1078 | */ |
||
1079 | 27 | public function getTimeFormatString() : string |
|
1080 | { |
||
1081 | 27 | return 'H:i:s.u'; |
|
1082 | } |
||
1083 | |||
1084 | /** |
||
1085 | * {@inheritdoc} |
||
1086 | */ |
||
1087 | 27 | public function getTimeTypeDeclarationSQL(array $fieldDeclaration) : string |
|
1088 | { |
||
1089 | 27 | return 'TIME'; |
|
1090 | } |
||
1091 | |||
1092 | /** |
||
1093 | * {@inheritdoc} |
||
1094 | */ |
||
1095 | 27 | public function getTrimExpression(string $str, int $mode = TrimMode::UNSPECIFIED, ?string $char = null) : string |
|
1096 | { |
||
1097 | 27 | if (! in_array($mode, [TrimMode::UNSPECIFIED, TrimMode::LEADING, TrimMode::TRAILING, TrimMode::BOTH], true)) { |
|
1098 | throw new InvalidArgumentException( |
||
1099 | sprintf('The value of $mode is expected to be one of the TrimMode constants, %d given', $mode) |
||
1100 | ); |
||
1101 | } |
||
1102 | |||
1103 | 27 | if ($char === null) { |
|
1104 | 27 | switch ($mode) { |
|
1105 | case TrimMode::LEADING: |
||
1106 | 27 | return $this->getLtrimExpression($str); |
|
1107 | case TrimMode::TRAILING: |
||
1108 | 27 | return $this->getRtrimExpression($str); |
|
1109 | default: |
||
1110 | 27 | return 'TRIM(' . $str . ')'; |
|
1111 | } |
||
1112 | } |
||
1113 | |||
1114 | 27 | $pattern = "'%[^' + " . $char . " + ']%'"; |
|
1115 | |||
1116 | 27 | switch ($mode) { |
|
1117 | case TrimMode::LEADING: |
||
1118 | 27 | return 'SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))'; |
|
1119 | case TrimMode::TRAILING: |
||
1120 | 27 | return 'REVERSE(SUBSTR(REVERSE(' . $str . '), PATINDEX(' . $pattern . ', REVERSE(' . $str . '))))'; |
|
1121 | default: |
||
1122 | 27 | return 'REVERSE(SUBSTR(REVERSE(SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))), ' . |
|
1123 | 27 | 'PATINDEX(' . $pattern . ', REVERSE(SUBSTR(' . $str . ', PATINDEX(' . $pattern . ', ' . $str . '))))))'; |
|
1124 | } |
||
1125 | } |
||
1126 | |||
1127 | /** |
||
1128 | * {@inheritDoc} |
||
1129 | */ |
||
1130 | public function getCurrentDatabaseExpression() : string |
||
1131 | { |
||
1132 | return 'DB_NAME()'; |
||
1133 | } |
||
1134 | |||
1135 | /** |
||
1136 | * {@inheritdoc} |
||
1137 | */ |
||
1138 | 54 | public function getTruncateTableSQL(string $tableName, bool $cascade = false) : string |
|
1139 | { |
||
1140 | 54 | $tableIdentifier = new Identifier($tableName); |
|
1141 | |||
1142 | 54 | return 'TRUNCATE TABLE ' . $tableIdentifier->getQuotedName($this); |
|
1143 | } |
||
1144 | |||
1145 | /** |
||
1146 | * {@inheritdoc} |
||
1147 | */ |
||
1148 | 27 | public function getCreateSequenceSQL(Sequence $sequence) : string |
|
1149 | { |
||
1150 | 27 | return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) . |
|
1151 | 27 | ' INCREMENT BY ' . $sequence->getAllocationSize() . |
|
1152 | 27 | ' START WITH ' . $sequence->getInitialValue() . |
|
1153 | 27 | ' MINVALUE ' . $sequence->getInitialValue(); |
|
1154 | } |
||
1155 | |||
1156 | /** |
||
1157 | * {@inheritdoc} |
||
1158 | */ |
||
1159 | 27 | public function getAlterSequenceSQL(Sequence $sequence) : string |
|
1160 | { |
||
1161 | 27 | return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) . |
|
1162 | 27 | ' INCREMENT BY ' . $sequence->getAllocationSize(); |
|
1163 | } |
||
1164 | |||
1165 | /** |
||
1166 | * {@inheritdoc} |
||
1167 | */ |
||
1168 | 27 | public function getDropSequenceSQL($sequence) : string |
|
1169 | { |
||
1170 | 27 | if ($sequence instanceof Sequence) { |
|
1171 | 27 | $sequence = $sequence->getQuotedName($this); |
|
1172 | } |
||
1173 | |||
1174 | 27 | return 'DROP SEQUENCE ' . $sequence; |
|
1175 | } |
||
1176 | |||
1177 | /** |
||
1178 | * {@inheritdoc} |
||
1179 | */ |
||
1180 | public function getListSequencesSQL(string $database) : string |
||
1181 | { |
||
1182 | return 'SELECT sequence_name, increment_by, start_with, min_value FROM SYS.SYSSEQUENCE'; |
||
1183 | } |
||
1184 | |||
1185 | /** |
||
1186 | * {@inheritdoc} |
||
1187 | */ |
||
1188 | 27 | public function getSequenceNextValSQL(string $sequenceName) : string |
|
1189 | { |
||
1190 | 27 | return 'SELECT ' . $sequenceName . '.NEXTVAL'; |
|
1191 | } |
||
1192 | |||
1193 | /** |
||
1194 | * {@inheritdoc} |
||
1195 | */ |
||
1196 | 27 | public function supportsSequences() : bool |
|
1197 | { |
||
1198 | 27 | return true; |
|
1199 | } |
||
1200 | |||
1201 | /** |
||
1202 | * {@inheritdoc} |
||
1203 | */ |
||
1204 | 27 | public function getDateTimeTzTypeDeclarationSQL(array $fieldDeclaration) : string |
|
1205 | { |
||
1206 | 27 | return 'TIMESTAMP WITH TIME ZONE'; |
|
1207 | } |
||
1208 | |||
1209 | /** |
||
1210 | * {@inheritdoc} |
||
1211 | */ |
||
1212 | 1188 | public function hasNativeGuidType() : bool |
|
1213 | { |
||
1214 | 1188 | return true; |
|
1215 | } |
||
1216 | |||
1217 | /** |
||
1218 | * {@inheritdoc} |
||
1219 | */ |
||
1220 | 27 | public function prefersIdentityColumns() : bool |
|
1221 | { |
||
1222 | 27 | return true; |
|
1223 | } |
||
1224 | |||
1225 | /** |
||
1226 | * {@inheritdoc} |
||
1227 | */ |
||
1228 | 351 | public function supportsCommentOnStatement() : bool |
|
1229 | { |
||
1230 | 351 | return true; |
|
1231 | } |
||
1232 | |||
1233 | /** |
||
1234 | * {@inheritdoc} |
||
1235 | */ |
||
1236 | 27 | public function supportsIdentityColumns() : bool |
|
1237 | { |
||
1238 | 27 | return true; |
|
1239 | } |
||
1240 | |||
1241 | /** |
||
1242 | * {@inheritdoc} |
||
1243 | */ |
||
1244 | 297 | protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef) : string |
|
1245 | { |
||
1246 | 297 | $unsigned = ! empty($columnDef['unsigned']) ? 'UNSIGNED ' : ''; |
|
1247 | 297 | $autoincrement = ! empty($columnDef['autoincrement']) ? ' IDENTITY' : ''; |
|
1248 | |||
1249 | 297 | return $unsigned . $columnDef['integer_type'] . $autoincrement; |
|
1250 | } |
||
1251 | |||
1252 | /** |
||
1253 | * {@inheritdoc} |
||
1254 | */ |
||
1255 | 324 | protected function _getCreateTableSQL(string $tableName, array $columns, array $options = []) : array |
|
1256 | { |
||
1257 | 324 | $columnListSql = $this->getColumnDeclarationListSQL($columns); |
|
1258 | 324 | $indexSql = []; |
|
1259 | |||
1260 | 324 | if (! empty($options['uniqueConstraints'])) { |
|
1261 | foreach ((array) $options['uniqueConstraints'] as $name => $definition) { |
||
1262 | $columnListSql .= ', ' . $this->getUniqueConstraintDeclarationSQL($name, $definition); |
||
1263 | } |
||
1264 | } |
||
1265 | |||
1266 | 324 | if (! empty($options['indexes'])) { |
|
1267 | /** @var Index $index */ |
||
1268 | 108 | foreach ((array) $options['indexes'] as $index) { |
|
1269 | 108 | $indexSql[] = $this->getCreateIndexSQL($index, $tableName); |
|
1270 | } |
||
1271 | } |
||
1272 | |||
1273 | 324 | if (! empty($options['primary'])) { |
|
1274 | 162 | $flags = ''; |
|
1275 | |||
1276 | 162 | if (isset($options['primary_index']) && $options['primary_index']->hasFlag('clustered')) { |
|
1277 | $flags = ' CLUSTERED '; |
||
1278 | } |
||
1279 | |||
1280 | 162 | $columnListSql .= ', PRIMARY KEY' . $flags . ' (' . implode(', ', array_unique(array_values((array) $options['primary']))) . ')'; |
|
1281 | } |
||
1282 | |||
1283 | 324 | if (! empty($options['foreignKeys'])) { |
|
1284 | 54 | foreach ((array) $options['foreignKeys'] as $definition) { |
|
1285 | 54 | $columnListSql .= ', ' . $this->getForeignKeyDeclarationSQL($definition); |
|
1286 | } |
||
1287 | } |
||
1288 | |||
1289 | 324 | $query = 'CREATE TABLE ' . $tableName . ' (' . $columnListSql; |
|
1290 | 324 | $check = $this->getCheckDeclarationSQL($columns); |
|
1291 | |||
1292 | 324 | if (! empty($check)) { |
|
1293 | 27 | $query .= ', ' . $check; |
|
1294 | } |
||
1295 | |||
1296 | 324 | $query .= ')'; |
|
1297 | |||
1298 | 324 | return array_merge([$query], $indexSql); |
|
1299 | } |
||
1300 | |||
1301 | /** |
||
1302 | * {@inheritdoc} |
||
1303 | */ |
||
1304 | 27 | protected function _getTransactionIsolationLevelSQL(int $level) : string |
|
1305 | { |
||
1306 | switch ($level) { |
||
1307 | 27 | case TransactionIsolationLevel::READ_UNCOMMITTED: |
|
1308 | 27 | return '0'; |
|
1309 | 27 | case TransactionIsolationLevel::READ_COMMITTED: |
|
1310 | 27 | return '1'; |
|
1311 | 27 | case TransactionIsolationLevel::REPEATABLE_READ: |
|
1312 | 27 | return '2'; |
|
1313 | 27 | case TransactionIsolationLevel::SERIALIZABLE: |
|
1314 | 27 | return '3'; |
|
1315 | default: |
||
1316 | throw new InvalidArgumentException(sprintf('Invalid isolation level %d.', $level)); |
||
1317 | } |
||
1318 | } |
||
1319 | |||
1320 | /** |
||
1321 | * {@inheritdoc} |
||
1322 | */ |
||
1323 | 162 | protected function doModifyLimitQuery(string $query, ?int $limit, int $offset) : string |
|
1324 | { |
||
1325 | 162 | $limitOffsetClause = $this->getTopClauseSQL($limit, $offset); |
|
1326 | |||
1327 | 162 | if ($limitOffsetClause === '') { |
|
1328 | 27 | return $query; |
|
1329 | } |
||
1330 | |||
1331 | 135 | if (! preg_match('/^\s*(SELECT\s+(DISTINCT\s+)?)(.*)/i', $query, $matches)) { |
|
1332 | return $query; |
||
1333 | } |
||
1334 | |||
1335 | 135 | return $matches[1] . $limitOffsetClause . ' ' . $matches[3]; |
|
1336 | } |
||
1337 | |||
1338 | 162 | private function getTopClauseSQL(?int $limit, ?int $offset) : string |
|
1339 | { |
||
1340 | 162 | if ($offset > 0) { |
|
1341 | 54 | return sprintf('TOP %s START AT %d', $limit ?? 'ALL', $offset + 1); |
|
1342 | } |
||
1343 | |||
1344 | 108 | return $limit === null ? '' : 'TOP ' . $limit; |
|
1345 | } |
||
1346 | |||
1347 | /** |
||
1348 | * Return the INDEX query section dealing with non-standard |
||
1349 | * SQL Anywhere options. |
||
1350 | * |
||
1351 | * @param Index $index Index definition |
||
1352 | */ |
||
1353 | 243 | protected function getAdvancedIndexOptionsSQL(Index $index) : string |
|
1354 | { |
||
1355 | 243 | if ($index->hasFlag('with_nulls_distinct') && $index->hasFlag('with_nulls_not_distinct')) { |
|
1356 | 27 | throw new UnexpectedValueException( |
|
1357 | 27 | 'An Index can either have a "with_nulls_distinct" or "with_nulls_not_distinct" flag but not both.' |
|
1358 | ); |
||
1359 | } |
||
1360 | |||
1361 | 216 | $sql = ''; |
|
1362 | |||
1363 | 216 | if (! $index->isPrimary() && $index->hasFlag('for_olap_workload')) { |
|
1364 | 27 | $sql .= ' FOR OLAP WORKLOAD'; |
|
1365 | } |
||
1366 | |||
1367 | 216 | if (! $index->isPrimary() && $index->isUnique() && $index->hasFlag('with_nulls_not_distinct')) { |
|
1368 | 27 | return ' WITH NULLS NOT DISTINCT' . $sql; |
|
1369 | } |
||
1370 | |||
1371 | 216 | if (! $index->isPrimary() && $index->isUnique() && $index->hasFlag('with_nulls_distinct')) { |
|
1372 | 27 | return ' WITH NULLS DISTINCT' . $sql; |
|
1373 | } |
||
1374 | |||
1375 | 216 | return $sql; |
|
1376 | } |
||
1377 | |||
1378 | /** |
||
1379 | * Returns the SQL snippet for creating a table constraint. |
||
1380 | * |
||
1381 | * @param Constraint $constraint The table constraint to create the SQL snippet for. |
||
1382 | * @param string|null $name The table constraint name to use if any. |
||
1383 | * |
||
1384 | * @throws InvalidArgumentException If the given table constraint type is not supported by this method. |
||
1385 | */ |
||
1386 | 189 | protected function getTableConstraintDeclarationSQL(Constraint $constraint, ?string $name = null) : string |
|
1387 | { |
||
1388 | 189 | if ($constraint instanceof ForeignKeyConstraint) { |
|
1389 | return $this->getForeignKeyDeclarationSQL($constraint); |
||
1390 | } |
||
1391 | |||
1392 | 189 | if (! $constraint instanceof Index) { |
|
1393 | 27 | throw new InvalidArgumentException(sprintf('Unsupported constraint type %s.', get_class($constraint))); |
|
1394 | } |
||
1395 | |||
1396 | 162 | if (! $constraint->isPrimary() && ! $constraint->isUnique()) { |
|
1397 | 27 | throw new InvalidArgumentException( |
|
1398 | 'Can only create primary, unique or foreign key constraint declarations, no common index declarations ' . |
||
1399 | 27 | 'with getTableConstraintDeclarationSQL().' |
|
1400 | ); |
||
1401 | } |
||
1402 | |||
1403 | 135 | $constraintColumns = $constraint->getQuotedColumns($this); |
|
1404 | |||
1405 | 135 | if (empty($constraintColumns)) { |
|
1406 | 27 | throw new InvalidArgumentException('Incomplete definition. "columns" required.'); |
|
1407 | } |
||
1408 | |||
1409 | 108 | $sql = ''; |
|
1410 | 108 | $flags = ''; |
|
1411 | |||
1412 | 108 | if (! empty($name)) { |
|
1413 | 54 | $name = new Identifier($name); |
|
1414 | 54 | $sql .= 'CONSTRAINT ' . $name->getQuotedName($this) . ' '; |
|
1415 | } |
||
1416 | |||
1417 | 108 | if ($constraint->hasFlag('clustered')) { |
|
1418 | 54 | $flags = 'CLUSTERED '; |
|
1419 | } |
||
1420 | |||
1421 | 108 | if ($constraint->isPrimary()) { |
|
1422 | 108 | return $sql . 'PRIMARY KEY ' . $flags . '(' . $this->getColumnsFieldDeclarationListSQL($constraintColumns) . ')'; |
|
1423 | } |
||
1424 | |||
1425 | 27 | return $sql . 'UNIQUE ' . $flags . '(' . $this->getColumnsFieldDeclarationListSQL($constraintColumns) . ')'; |
|
1426 | } |
||
1427 | |||
1428 | /** |
||
1429 | * {@inheritdoc} |
||
1430 | */ |
||
1431 | 243 | protected function getCreateIndexSQLFlags(Index $index) : string |
|
1432 | { |
||
1433 | 243 | $type = ''; |
|
1434 | 243 | if ($index->hasFlag('virtual')) { |
|
1435 | 27 | $type .= 'VIRTUAL '; |
|
1436 | } |
||
1437 | |||
1438 | 243 | if ($index->isUnique()) { |
|
1439 | 81 | $type .= 'UNIQUE '; |
|
1440 | } |
||
1441 | |||
1442 | 243 | if ($index->hasFlag('clustered')) { |
|
1443 | 27 | $type .= 'CLUSTERED '; |
|
1444 | } |
||
1445 | |||
1446 | 243 | return $type; |
|
1447 | } |
||
1448 | |||
1449 | /** |
||
1450 | * {@inheritdoc} |
||
1451 | */ |
||
1452 | 135 | protected function getRenameIndexSQL(string $oldIndexName, Index $index, string $tableName) : array |
|
1453 | { |
||
1454 | 135 | return ['ALTER INDEX ' . $oldIndexName . ' ON ' . $tableName . ' RENAME TO ' . $index->getQuotedName($this)]; |
|
1455 | } |
||
1456 | |||
1457 | /** |
||
1458 | * {@inheritdoc} |
||
1459 | */ |
||
1460 | 1458 | protected function getReservedKeywordsClass() : string |
|
1461 | { |
||
1462 | 1458 | return Keywords\SQLAnywhereKeywords::class; |
|
1463 | } |
||
1464 | |||
1465 | /** |
||
1466 | * {@inheritdoc} |
||
1467 | */ |
||
1468 | 162 | protected function initializeDoctrineTypeMappings() : void |
|
1469 | { |
||
1470 | 162 | $this->doctrineTypeMapping = [ |
|
1471 | 'bigint' => 'bigint', |
||
1472 | 'binary' => 'binary', |
||
1473 | 'bit' => 'boolean', |
||
1474 | 'char' => 'string', |
||
1475 | 'decimal' => 'decimal', |
||
1476 | 'date' => 'date', |
||
1477 | 'datetime' => 'datetime', |
||
1478 | 'double' => 'float', |
||
1479 | 'float' => 'float', |
||
1480 | 'image' => 'blob', |
||
1481 | 'int' => 'integer', |
||
1482 | 'integer' => 'integer', |
||
1483 | 'long binary' => 'blob', |
||
1484 | 'long nvarchar' => 'text', |
||
1485 | 'long varbit' => 'text', |
||
1486 | 'long varchar' => 'text', |
||
1487 | 'money' => 'decimal', |
||
1488 | 'nchar' => 'string', |
||
1489 | 'ntext' => 'text', |
||
1490 | 'numeric' => 'decimal', |
||
1491 | 'nvarchar' => 'string', |
||
1492 | 'smalldatetime' => 'datetime', |
||
1493 | 'smallint' => 'smallint', |
||
1494 | 'smallmoney' => 'decimal', |
||
1495 | 'text' => 'text', |
||
1496 | 'time' => 'time', |
||
1497 | 'timestamp' => 'datetime', |
||
1498 | 'timestamp with time zone' => 'datetime', |
||
1499 | 'tinyint' => 'smallint', |
||
1500 | 'uniqueidentifier' => 'guid', |
||
1501 | 'uniqueidentifierstr' => 'guid', |
||
1502 | 'unsigned bigint' => 'bigint', |
||
1503 | 'unsigned int' => 'integer', |
||
1504 | 'unsigned smallint' => 'smallint', |
||
1505 | 'unsigned tinyint' => 'smallint', |
||
1506 | 'varbinary' => 'binary', |
||
1507 | 'varbit' => 'string', |
||
1508 | 'varchar' => 'string', |
||
1509 | 'xml' => 'text', |
||
1510 | ]; |
||
1511 | 162 | } |
|
1512 | } |
||
1513 |
The
break
statement is not necessary if it is preceded for example by areturn
statement:If you would like to keep this construct to be consistent with other
case
statements, you can safely mark this issue as a false-positive.