1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace Doctrine\DBAL\Platforms; |
6
|
|
|
|
7
|
|
|
use Doctrine\DBAL\Schema\ColumnDiff; |
8
|
|
|
use Doctrine\DBAL\Schema\ForeignKeyConstraint; |
9
|
|
|
use Doctrine\DBAL\Schema\Identifier; |
10
|
|
|
use Doctrine\DBAL\Schema\Index; |
11
|
|
|
use Doctrine\DBAL\Schema\Sequence; |
12
|
|
|
use Doctrine\DBAL\Schema\TableDiff; |
13
|
|
|
use Doctrine\DBAL\Types\BigIntType; |
14
|
|
|
use Doctrine\DBAL\Types\BinaryType; |
15
|
|
|
use Doctrine\DBAL\Types\BlobType; |
16
|
|
|
use Doctrine\DBAL\Types\IntegerType; |
17
|
|
|
use Doctrine\DBAL\Types\Type; |
18
|
|
|
use UnexpectedValueException; |
19
|
|
|
use function array_diff; |
20
|
|
|
use function array_merge; |
21
|
|
|
use function array_unique; |
22
|
|
|
use function array_values; |
23
|
|
|
use function assert; |
24
|
|
|
use function count; |
25
|
|
|
use function explode; |
26
|
|
|
use function implode; |
27
|
|
|
use function in_array; |
28
|
|
|
use function is_array; |
29
|
|
|
use function is_bool; |
30
|
|
|
use function is_numeric; |
31
|
|
|
use function is_string; |
32
|
|
|
use function sprintf; |
33
|
|
|
use function strpos; |
34
|
|
|
use function strtolower; |
35
|
|
|
use function trim; |
36
|
|
|
|
37
|
|
|
/** |
38
|
|
|
* Provides the behavior, features and SQL dialect of the PostgreSQL 9.4+ database platform. |
39
|
|
|
*/ |
40
|
|
|
class PostgreSQL94Platform extends AbstractPlatform |
41
|
|
|
{ |
42
|
|
|
/** @var bool */ |
43
|
|
|
private $useBooleanTrueFalseStrings = true; |
44
|
|
|
|
45
|
|
|
/** @var string[][] PostgreSQL booleans literals */ |
46
|
|
|
private $booleanLiterals = [ |
47
|
|
|
'true' => [ |
48
|
|
|
't', |
49
|
|
|
'true', |
50
|
|
|
'y', |
51
|
|
|
'yes', |
52
|
|
|
'on', |
53
|
|
|
'1', |
54
|
|
|
], |
55
|
|
|
'false' => [ |
56
|
|
|
'f', |
57
|
|
|
'false', |
58
|
|
|
'n', |
59
|
|
|
'no', |
60
|
|
|
'off', |
61
|
|
|
'0', |
62
|
|
|
], |
63
|
|
|
]; |
64
|
|
|
|
65
|
|
|
/** |
66
|
|
|
* PostgreSQL has different behavior with some drivers |
67
|
|
|
* with regard to how booleans have to be handled. |
68
|
|
|
* |
69
|
|
|
* Enables use of 'true'/'false' or otherwise 1 and 0 instead. |
70
|
|
|
*/ |
71
|
|
|
public function setUseBooleanTrueFalseStrings(bool $flag) : void |
72
|
|
|
{ |
73
|
|
|
$this->useBooleanTrueFalseStrings = $flag; |
74
|
|
|
} |
75
|
|
|
|
76
|
|
|
public function getNowExpression() : string |
77
|
|
|
{ |
78
|
|
|
return 'LOCALTIMESTAMP(0)'; |
79
|
|
|
} |
80
|
|
|
|
81
|
|
|
public function getRegexpExpression() : string |
82
|
|
|
{ |
83
|
|
|
return 'SIMILAR TO'; |
84
|
|
|
} |
85
|
|
|
|
86
|
|
|
public function getLocateExpression(string $string, string $substring, ?string $start = null) : string |
87
|
|
|
{ |
88
|
|
|
if ($start !== null) { |
89
|
|
|
$string = $this->getSubstringExpression($string, $start); |
90
|
|
|
|
91
|
|
|
return 'CASE WHEN (POSITION(' . $substring . ' IN ' . $string . ') = 0) THEN 0 ELSE (POSITION(' . $substring . ' IN ' . $string . ') + ' . $start . ' - 1) END'; |
92
|
|
|
} |
93
|
|
|
|
94
|
|
|
return sprintf('POSITION(%s IN %s)', $substring, $string); |
95
|
|
|
} |
96
|
|
|
|
97
|
|
|
protected function getDateArithmeticIntervalExpression(string $date, string $operator, string $interval, string $unit) : string |
98
|
|
|
{ |
99
|
|
|
if ($unit === DateIntervalUnit::QUARTER) { |
100
|
|
|
$interval = $this->multiplyInterval($interval, 3); |
101
|
|
|
$unit = DateIntervalUnit::MONTH; |
102
|
|
|
} |
103
|
|
|
|
104
|
|
|
return '(' . $date . ' ' . $operator . ' (' . $interval . " || ' " . $unit . "')::interval)"; |
105
|
|
|
} |
106
|
|
|
|
107
|
|
|
public function getDateDiffExpression(string $date1, string $date2) : string |
108
|
|
|
{ |
109
|
|
|
return '(DATE(' . $date1 . ')-DATE(' . $date2 . '))'; |
110
|
|
|
} |
111
|
|
|
|
112
|
|
|
public function getCurrentDatabaseExpression() : string |
113
|
|
|
{ |
114
|
|
|
return 'CURRENT_DATABASE()'; |
115
|
|
|
} |
116
|
|
|
|
117
|
|
|
public function supportsSequences() : bool |
118
|
|
|
{ |
119
|
|
|
return true; |
120
|
|
|
} |
121
|
|
|
|
122
|
|
|
public function supportsSchemas() : bool |
123
|
|
|
{ |
124
|
|
|
return true; |
125
|
|
|
} |
126
|
|
|
|
127
|
|
|
public function getDefaultSchemaName() : string |
128
|
|
|
{ |
129
|
|
|
return 'public'; |
130
|
|
|
} |
131
|
|
|
|
132
|
|
|
public function supportsIdentityColumns() : bool |
133
|
|
|
{ |
134
|
|
|
return true; |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
public function supportsPartialIndexes() : bool |
138
|
|
|
{ |
139
|
|
|
return true; |
140
|
|
|
} |
141
|
|
|
|
142
|
|
|
public function usesSequenceEmulatedIdentityColumns() : bool |
143
|
|
|
{ |
144
|
|
|
return true; |
145
|
|
|
} |
146
|
|
|
|
147
|
|
|
public function getIdentitySequenceName(string $tableName, string $columnName) : string |
148
|
|
|
{ |
149
|
|
|
return $tableName . '_' . $columnName . '_seq'; |
150
|
|
|
} |
151
|
|
|
|
152
|
|
|
public function supportsCommentOnStatement() : bool |
153
|
|
|
{ |
154
|
|
|
return true; |
155
|
|
|
} |
156
|
|
|
|
157
|
|
|
public function prefersSequences() : bool |
158
|
|
|
{ |
159
|
|
|
return true; |
160
|
|
|
} |
161
|
|
|
|
162
|
|
|
public function hasNativeGuidType() : bool |
163
|
|
|
{ |
164
|
|
|
return true; |
165
|
|
|
} |
166
|
|
|
|
167
|
|
|
public function getListDatabasesSQL() : string |
168
|
|
|
{ |
169
|
|
|
return 'SELECT datname FROM pg_database'; |
170
|
|
|
} |
171
|
|
|
|
172
|
|
|
public function getListNamespacesSQL() : string |
173
|
|
|
{ |
174
|
|
|
return "SELECT schema_name AS nspname |
175
|
|
|
FROM information_schema.schemata |
176
|
|
|
WHERE schema_name NOT LIKE 'pg\_%' |
177
|
|
|
AND schema_name != 'information_schema'"; |
178
|
|
|
} |
179
|
|
|
|
180
|
|
|
public function getListSequencesSQL(string $database) : string |
181
|
|
|
{ |
182
|
|
|
return "SELECT sequence_name AS relname, |
183
|
|
|
sequence_schema AS schemaname |
184
|
|
|
FROM information_schema.sequences |
185
|
|
|
WHERE sequence_schema NOT LIKE 'pg\_%' |
186
|
|
|
AND sequence_schema != 'information_schema'"; |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
public function getListTablesSQL() : string |
190
|
|
|
{ |
191
|
|
|
return "SELECT quote_ident(table_name) AS table_name, |
192
|
|
|
table_schema AS schema_name |
193
|
|
|
FROM information_schema.tables |
194
|
|
|
WHERE table_schema NOT LIKE 'pg\_%' |
195
|
|
|
AND table_schema != 'information_schema' |
196
|
|
|
AND table_name != 'geometry_columns' |
197
|
|
|
AND table_name != 'spatial_ref_sys' |
198
|
|
|
AND table_type != 'VIEW'"; |
199
|
|
|
} |
200
|
|
|
|
201
|
|
|
public function getListViewsSQL(string $database) : string |
202
|
|
|
{ |
203
|
|
|
return 'SELECT quote_ident(table_name) AS viewname, |
204
|
|
|
table_schema AS schemaname, |
205
|
|
|
view_definition AS definition |
206
|
|
|
FROM information_schema.views |
207
|
|
|
WHERE view_definition IS NOT NULL'; |
208
|
|
|
} |
209
|
|
|
|
210
|
|
|
public function getListTableForeignKeysSQL(string $table, ?string $database = null) : string |
211
|
|
|
{ |
212
|
|
|
return 'SELECT quote_ident(r.conname) as conname, pg_catalog.pg_get_constraintdef(r.oid, true) as condef |
213
|
|
|
FROM pg_catalog.pg_constraint r |
214
|
|
|
WHERE r.conrelid = |
215
|
|
|
( |
216
|
|
|
SELECT c.oid |
217
|
|
|
FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n |
218
|
|
|
WHERE ' . $this->getTableWhereClause($table) . " AND n.oid = c.relnamespace |
219
|
|
|
) |
220
|
|
|
AND r.contype = 'f'"; |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
public function getCreateViewSQL(string $name, string $sql) : string |
224
|
|
|
{ |
225
|
|
|
return 'CREATE VIEW ' . $name . ' AS ' . $sql; |
226
|
|
|
} |
227
|
|
|
|
228
|
|
|
public function getDropViewSQL(string $name) : string |
229
|
|
|
{ |
230
|
|
|
return 'DROP VIEW ' . $name; |
231
|
|
|
} |
232
|
|
|
|
233
|
|
|
public function getListTableConstraintsSQL(string $table) : string |
234
|
|
|
{ |
235
|
|
|
$table = new Identifier($table); |
236
|
|
|
$table = $this->quoteStringLiteral($table->getName()); |
237
|
|
|
|
238
|
|
|
return sprintf( |
239
|
|
|
<<<'SQL' |
240
|
|
|
SELECT |
241
|
|
|
quote_ident(relname) as relname |
242
|
|
|
FROM |
243
|
|
|
pg_class |
244
|
|
|
WHERE oid IN ( |
245
|
|
|
SELECT indexrelid |
246
|
|
|
FROM pg_index, pg_class |
247
|
|
|
WHERE pg_class.relname = %s |
248
|
|
|
AND pg_class.oid = pg_index.indrelid |
249
|
|
|
AND (indisunique = 't' OR indisprimary = 't') |
250
|
|
|
) |
251
|
|
|
SQL |
252
|
|
|
, |
253
|
|
|
$table |
254
|
|
|
); |
255
|
|
|
} |
256
|
|
|
|
257
|
|
|
/** |
258
|
|
|
* {@inheritDoc} |
259
|
|
|
* |
260
|
|
|
* @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html |
261
|
|
|
*/ |
262
|
|
|
public function getListTableIndexesSQL(string $table, ?string $currentDatabase = null) : string |
263
|
|
|
{ |
264
|
|
|
return 'SELECT quote_ident(relname) as relname, pg_index.indisunique, pg_index.indisprimary, |
265
|
|
|
pg_index.indkey, pg_index.indrelid, |
266
|
|
|
pg_get_expr(indpred, indrelid) AS where |
267
|
|
|
FROM pg_class, pg_index |
268
|
|
|
WHERE oid IN ( |
269
|
|
|
SELECT indexrelid |
270
|
|
|
FROM pg_index si, pg_class sc, pg_namespace sn |
271
|
|
|
WHERE ' . $this->getTableWhereClause($table, 'sc', 'sn') . ' AND sc.oid=si.indrelid AND sc.relnamespace = sn.oid |
272
|
|
|
) AND pg_index.indexrelid = oid'; |
273
|
|
|
} |
274
|
|
|
|
275
|
|
|
private function getTableWhereClause(string $table, string $classAlias = 'c', string $namespaceAlias = 'n') : string |
276
|
|
|
{ |
277
|
|
|
$whereClause = $namespaceAlias . ".nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast') AND "; |
278
|
|
|
if (strpos($table, '.') !== false) { |
279
|
|
|
[$schema, $table] = explode('.', $table); |
280
|
|
|
$schema = $this->quoteStringLiteral($schema); |
281
|
|
|
} else { |
282
|
|
|
$schema = 'ANY(current_schemas(false))'; |
283
|
|
|
} |
284
|
|
|
|
285
|
|
|
$table = new Identifier($table); |
286
|
|
|
$table = $this->quoteStringLiteral($table->getName()); |
287
|
|
|
|
288
|
|
|
return $whereClause . sprintf( |
289
|
|
|
'%s.relname = %s AND %s.nspname = %s', |
290
|
|
|
$classAlias, |
291
|
|
|
$table, |
292
|
|
|
$namespaceAlias, |
293
|
|
|
$schema |
294
|
|
|
); |
295
|
|
|
} |
296
|
|
|
|
297
|
|
|
public function getListTableColumnsSQL(string $table, ?string $database = null) : string |
298
|
|
|
{ |
299
|
|
|
return "SELECT |
300
|
|
|
a.attnum, |
301
|
|
|
quote_ident(a.attname) AS field, |
302
|
|
|
t.typname AS type, |
303
|
|
|
format_type(a.atttypid, a.atttypmod) AS complete_type, |
304
|
|
|
(SELECT tc.collcollate FROM pg_catalog.pg_collation tc WHERE tc.oid = a.attcollation) AS collation, |
305
|
|
|
(SELECT t1.typname FROM pg_catalog.pg_type t1 WHERE t1.oid = t.typbasetype) AS domain_type, |
306
|
|
|
(SELECT format_type(t2.typbasetype, t2.typtypmod) FROM |
307
|
|
|
pg_catalog.pg_type t2 WHERE t2.typtype = 'd' AND t2.oid = a.atttypid) AS domain_complete_type, |
308
|
|
|
a.attnotnull AS isnotnull, |
309
|
|
|
(SELECT 't' |
310
|
|
|
FROM pg_index |
311
|
|
|
WHERE c.oid = pg_index.indrelid |
312
|
|
|
AND pg_index.indkey[0] = a.attnum |
313
|
|
|
AND pg_index.indisprimary = 't' |
314
|
|
|
) AS pri, |
315
|
|
|
(SELECT pg_get_expr(adbin, adrelid) |
316
|
|
|
FROM pg_attrdef |
317
|
|
|
WHERE c.oid = pg_attrdef.adrelid |
318
|
|
|
AND pg_attrdef.adnum=a.attnum |
319
|
|
|
) AS default, |
320
|
|
|
(SELECT pg_description.description |
321
|
|
|
FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid |
322
|
|
|
) AS comment |
323
|
|
|
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n |
324
|
|
|
WHERE " . $this->getTableWhereClause($table, 'c', 'n') . ' |
325
|
|
|
AND a.attnum > 0 |
326
|
|
|
AND a.attrelid = c.oid |
327
|
|
|
AND a.atttypid = t.oid |
328
|
|
|
AND n.oid = c.relnamespace |
329
|
|
|
ORDER BY a.attnum'; |
330
|
|
|
} |
331
|
|
|
|
332
|
|
|
public function getCreateDatabaseSQL(string $database) : string |
333
|
|
|
{ |
334
|
|
|
return 'CREATE DATABASE ' . $database; |
335
|
|
|
} |
336
|
|
|
|
337
|
|
|
/** |
338
|
|
|
* Returns the SQL statement for disallowing new connections on the given database. |
339
|
|
|
* |
340
|
|
|
* This is useful to force DROP DATABASE operations which could fail because of active connections. |
341
|
|
|
* |
342
|
|
|
* @param string $database The name of the database to disallow new connections for. |
343
|
|
|
*/ |
344
|
|
|
public function getDisallowDatabaseConnectionsSQL(string $database) : string |
345
|
|
|
{ |
346
|
|
|
return "UPDATE pg_database SET datallowconn = 'false' WHERE datname = " . $this->quoteStringLiteral($database); |
347
|
|
|
} |
348
|
|
|
|
349
|
|
|
/** |
350
|
|
|
* Returns the SQL statement for closing currently active connections on the given database. |
351
|
|
|
* |
352
|
|
|
* This is useful to force DROP DATABASE operations which could fail because of active connections. |
353
|
|
|
* |
354
|
|
|
* @param string $database The name of the database to close currently active connections for. |
355
|
|
|
*/ |
356
|
|
|
public function getCloseActiveDatabaseConnectionsSQL(string $database) : string |
357
|
|
|
{ |
358
|
|
|
return 'SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = ' |
359
|
|
|
. $this->quoteStringLiteral($database); |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
public function getAdvancedForeignKeyOptionsSQL(ForeignKeyConstraint $foreignKey) : string |
363
|
|
|
{ |
364
|
|
|
$query = ''; |
365
|
|
|
|
366
|
|
|
if ($foreignKey->hasOption('match')) { |
367
|
|
|
$query .= ' MATCH ' . $foreignKey->getOption('match'); |
368
|
|
|
} |
369
|
|
|
|
370
|
|
|
$query .= parent::getAdvancedForeignKeyOptionsSQL($foreignKey); |
371
|
|
|
|
372
|
|
|
if ($foreignKey->hasOption('deferrable') && $foreignKey->getOption('deferrable') !== false) { |
373
|
|
|
$query .= ' DEFERRABLE'; |
374
|
|
|
} else { |
375
|
|
|
$query .= ' NOT DEFERRABLE'; |
376
|
|
|
} |
377
|
|
|
|
378
|
|
|
if ($foreignKey->hasOption('deferred') && $foreignKey->getOption('deferred') !== false) { |
379
|
|
|
$query .= ' INITIALLY DEFERRED'; |
380
|
|
|
} else { |
381
|
|
|
$query .= ' INITIALLY IMMEDIATE'; |
382
|
|
|
} |
383
|
|
|
|
384
|
|
|
return $query; |
385
|
|
|
} |
386
|
|
|
|
387
|
|
|
/** |
388
|
|
|
* {@inheritDoc} |
389
|
|
|
*/ |
390
|
|
|
public function getAlterTableSQL(TableDiff $diff) : array |
391
|
|
|
{ |
392
|
|
|
$sql = []; |
393
|
|
|
$commentsSQL = []; |
394
|
|
|
$columnSql = []; |
395
|
|
|
|
396
|
|
|
foreach ($diff->addedColumns as $column) { |
397
|
|
|
if ($this->onSchemaAlterTableAddColumn($column, $diff, $columnSql)) { |
398
|
|
|
continue; |
399
|
|
|
} |
400
|
|
|
|
401
|
|
|
$query = 'ADD ' . $this->getColumnDeclarationSQL($column->getQuotedName($this), $column->toArray()); |
402
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
403
|
|
|
|
404
|
|
|
$comment = $this->getColumnComment($column); |
405
|
|
|
|
406
|
|
|
if ($comment === null || $comment === '') { |
407
|
|
|
continue; |
408
|
|
|
} |
409
|
|
|
|
410
|
|
|
$commentsSQL[] = $this->getCommentOnColumnSQL( |
411
|
|
|
$diff->getName($this)->getQuotedName($this), |
412
|
|
|
$column->getQuotedName($this), |
413
|
|
|
$comment |
414
|
|
|
); |
415
|
|
|
} |
416
|
|
|
|
417
|
|
|
foreach ($diff->removedColumns as $column) { |
418
|
|
|
if ($this->onSchemaAlterTableRemoveColumn($column, $diff, $columnSql)) { |
419
|
|
|
continue; |
420
|
|
|
} |
421
|
|
|
|
422
|
|
|
$query = 'DROP ' . $column->getQuotedName($this); |
423
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
424
|
|
|
} |
425
|
|
|
|
426
|
|
|
foreach ($diff->changedColumns as $columnDiff) { |
427
|
|
|
assert($columnDiff instanceof ColumnDiff); |
428
|
|
|
if ($this->onSchemaAlterTableChangeColumn($columnDiff, $diff, $columnSql)) { |
429
|
|
|
continue; |
430
|
|
|
} |
431
|
|
|
|
432
|
|
|
if ($this->isUnchangedBinaryColumn($columnDiff)) { |
433
|
|
|
continue; |
434
|
|
|
} |
435
|
|
|
|
436
|
|
|
$oldColumnName = $columnDiff->getOldColumnName()->getQuotedName($this); |
437
|
|
|
$column = $columnDiff->column; |
438
|
|
|
|
439
|
|
|
if ($columnDiff->hasChanged('type') || $columnDiff->hasChanged('precision') || $columnDiff->hasChanged('scale') || $columnDiff->hasChanged('fixed')) { |
440
|
|
|
$type = $column->getType(); |
441
|
|
|
|
442
|
|
|
// SERIAL/BIGSERIAL are not "real" types and we can't alter a column to that type |
443
|
|
|
$columnDefinition = $column->toArray(); |
444
|
|
|
$columnDefinition['autoincrement'] = false; |
445
|
|
|
|
446
|
|
|
// here was a server version check before, but DBAL API does not support this anymore. |
447
|
|
|
$query = 'ALTER ' . $oldColumnName . ' TYPE ' . $type->getSQLDeclaration($columnDefinition, $this); |
448
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
449
|
|
|
} |
450
|
|
|
|
451
|
|
|
if ($columnDiff->hasChanged('default') || $this->typeChangeBreaksDefaultValue($columnDiff)) { |
452
|
|
|
$defaultClause = $column->getDefault() === null |
453
|
|
|
? ' DROP DEFAULT' |
454
|
|
|
: ' SET' . $this->getDefaultValueDeclarationSQL($column->toArray()); |
455
|
|
|
$query = 'ALTER ' . $oldColumnName . $defaultClause; |
456
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
457
|
|
|
} |
458
|
|
|
|
459
|
|
|
if ($columnDiff->hasChanged('notnull')) { |
460
|
|
|
$query = 'ALTER ' . $oldColumnName . ' ' . ($column->getNotnull() ? 'SET' : 'DROP') . ' NOT NULL'; |
461
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
462
|
|
|
} |
463
|
|
|
|
464
|
|
|
if ($columnDiff->hasChanged('autoincrement')) { |
465
|
|
|
if ($column->getAutoincrement()) { |
466
|
|
|
// add autoincrement |
467
|
|
|
$seqName = $this->getIdentitySequenceName($diff->name, $oldColumnName); |
468
|
|
|
|
469
|
|
|
$sql[] = 'CREATE SEQUENCE ' . $seqName; |
470
|
|
|
$sql[] = "SELECT setval('" . $seqName . "', (SELECT MAX(" . $oldColumnName . ') FROM ' . $diff->getName($this)->getQuotedName($this) . '))'; |
471
|
|
|
$query = 'ALTER ' . $oldColumnName . " SET DEFAULT nextval('" . $seqName . "')"; |
472
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
473
|
|
|
} else { |
474
|
|
|
// Drop autoincrement, but do NOT drop the sequence. It might be re-used by other tables or have |
475
|
|
|
$query = 'ALTER ' . $oldColumnName . ' DROP DEFAULT'; |
476
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
477
|
|
|
} |
478
|
|
|
} |
479
|
|
|
|
480
|
|
|
$newComment = $this->getColumnComment($column); |
481
|
|
|
$oldComment = $this->getOldColumnComment($columnDiff); |
482
|
|
|
|
483
|
|
|
if ($columnDiff->hasChanged('comment') || ($columnDiff->fromColumn !== null && $oldComment !== $newComment)) { |
484
|
|
|
$commentsSQL[] = $this->getCommentOnColumnSQL( |
485
|
|
|
$diff->getName($this)->getQuotedName($this), |
486
|
|
|
$column->getQuotedName($this), |
487
|
|
|
$newComment |
488
|
|
|
); |
489
|
|
|
} |
490
|
|
|
|
491
|
|
|
if (! $columnDiff->hasChanged('length')) { |
492
|
|
|
continue; |
493
|
|
|
} |
494
|
|
|
|
495
|
|
|
$query = 'ALTER ' . $oldColumnName . ' TYPE ' . $column->getType()->getSQLDeclaration($column->toArray(), $this); |
496
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . ' ' . $query; |
497
|
|
|
} |
498
|
|
|
|
499
|
|
|
foreach ($diff->renamedColumns as $oldColumnName => $column) { |
500
|
|
|
if ($this->onSchemaAlterTableRenameColumn($oldColumnName, $column, $diff, $columnSql)) { |
501
|
|
|
continue; |
502
|
|
|
} |
503
|
|
|
|
504
|
|
|
$oldColumnName = new Identifier($oldColumnName); |
505
|
|
|
|
506
|
|
|
$sql[] = 'ALTER TABLE ' . $diff->getName($this)->getQuotedName($this) . |
507
|
|
|
' RENAME COLUMN ' . $oldColumnName->getQuotedName($this) . ' TO ' . $column->getQuotedName($this); |
508
|
|
|
} |
509
|
|
|
|
510
|
|
|
$tableSql = []; |
511
|
|
|
|
512
|
|
|
if (! $this->onSchemaAlterTable($diff, $tableSql)) { |
513
|
|
|
$sql = array_merge($sql, $commentsSQL); |
514
|
|
|
|
515
|
|
|
$newName = $diff->getNewName(); |
516
|
|
|
|
517
|
|
|
if ($newName !== null) { |
518
|
|
|
$sql[] = sprintf( |
519
|
|
|
'ALTER TABLE %s RENAME TO %s', |
520
|
|
|
$diff->getName($this)->getQuotedName($this), |
521
|
|
|
$newName->getQuotedName($this) |
522
|
|
|
); |
523
|
|
|
} |
524
|
|
|
|
525
|
|
|
$sql = array_merge( |
526
|
|
|
$this->getPreAlterTableIndexForeignKeySQL($diff), |
527
|
|
|
$sql, |
528
|
|
|
$this->getPostAlterTableIndexForeignKeySQL($diff) |
529
|
|
|
); |
530
|
|
|
} |
531
|
|
|
|
532
|
|
|
return array_merge($sql, $tableSql, $columnSql); |
533
|
|
|
} |
534
|
|
|
|
535
|
|
|
/** |
536
|
|
|
* Checks whether a given column diff is a logically unchanged binary type column. |
537
|
|
|
* |
538
|
|
|
* Used to determine whether a column alteration for a binary type column can be skipped. |
539
|
|
|
* Doctrine's {@link \Doctrine\DBAL\Types\BinaryType} and {@link \Doctrine\DBAL\Types\BlobType} |
540
|
|
|
* are mapped to the same database column type on this platform as this platform |
541
|
|
|
* does not have a native VARBINARY/BINARY column type. Therefore the {@link \Doctrine\DBAL\Schema\Comparator} |
542
|
|
|
* might detect differences for binary type columns which do not have to be propagated |
543
|
|
|
* to database as there actually is no difference at database level. |
544
|
|
|
* |
545
|
|
|
* @param ColumnDiff $columnDiff The column diff to check against. |
546
|
|
|
* |
547
|
|
|
* @return bool True if the given column diff is an unchanged binary type column, false otherwise. |
548
|
|
|
*/ |
549
|
|
|
private function isUnchangedBinaryColumn(ColumnDiff $columnDiff) : bool |
550
|
|
|
{ |
551
|
|
|
$columnType = $columnDiff->column->getType(); |
552
|
|
|
|
553
|
|
|
if (! $columnType instanceof BinaryType && ! $columnType instanceof BlobType) { |
554
|
|
|
return false; |
555
|
|
|
} |
556
|
|
|
|
557
|
|
|
$fromColumn = $columnDiff->fromColumn; |
558
|
|
|
|
559
|
|
|
if ($fromColumn !== null) { |
560
|
|
|
$fromColumnType = $fromColumn->getType(); |
561
|
|
|
|
562
|
|
|
if (! $fromColumnType instanceof BinaryType && ! $fromColumnType instanceof BlobType) { |
563
|
|
|
return false; |
564
|
|
|
} |
565
|
|
|
|
566
|
|
|
return count(array_diff($columnDiff->changedProperties, ['type', 'length', 'fixed'])) === 0; |
567
|
|
|
} |
568
|
|
|
|
569
|
|
|
if ($columnDiff->hasChanged('type')) { |
570
|
|
|
return false; |
571
|
|
|
} |
572
|
|
|
|
573
|
|
|
return count(array_diff($columnDiff->changedProperties, ['length', 'fixed'])) === 0; |
574
|
|
|
} |
575
|
|
|
|
576
|
|
|
/** |
577
|
|
|
* {@inheritdoc} |
578
|
|
|
*/ |
579
|
|
|
protected function getRenameIndexSQL(string $oldIndexName, Index $index, string $tableName) : array |
580
|
|
|
{ |
581
|
|
|
if (strpos($tableName, '.') !== false) { |
582
|
|
|
[$schema] = explode('.', $tableName); |
583
|
|
|
$oldIndexName = $schema . '.' . $oldIndexName; |
584
|
|
|
} |
585
|
|
|
|
586
|
|
|
return ['ALTER INDEX ' . $oldIndexName . ' RENAME TO ' . $index->getQuotedName($this)]; |
587
|
|
|
} |
588
|
|
|
|
589
|
|
|
public function getCommentOnColumnSQL(string $tableName, string $columnName, ?string $comment) : string |
590
|
|
|
{ |
591
|
|
|
$tableName = new Identifier($tableName); |
592
|
|
|
$columnName = new Identifier($columnName); |
593
|
|
|
$comment = $comment === null ? 'NULL' : $this->quoteStringLiteral($comment); |
594
|
|
|
|
595
|
|
|
return sprintf( |
596
|
|
|
'COMMENT ON COLUMN %s.%s IS %s', |
597
|
|
|
$tableName->getQuotedName($this), |
598
|
|
|
$columnName->getQuotedName($this), |
599
|
|
|
$comment |
600
|
|
|
); |
601
|
|
|
} |
602
|
|
|
|
603
|
|
|
public function getCreateSequenceSQL(Sequence $sequence) : string |
604
|
|
|
{ |
605
|
|
|
return 'CREATE SEQUENCE ' . $sequence->getQuotedName($this) . |
606
|
|
|
' INCREMENT BY ' . $sequence->getAllocationSize() . |
607
|
|
|
' MINVALUE ' . $sequence->getInitialValue() . |
608
|
|
|
' START ' . $sequence->getInitialValue() . |
609
|
|
|
$this->getSequenceCacheSQL($sequence); |
610
|
|
|
} |
611
|
|
|
|
612
|
|
|
public function getAlterSequenceSQL(Sequence $sequence) : string |
613
|
|
|
{ |
614
|
|
|
return 'ALTER SEQUENCE ' . $sequence->getQuotedName($this) . |
615
|
|
|
' INCREMENT BY ' . $sequence->getAllocationSize() . |
616
|
|
|
$this->getSequenceCacheSQL($sequence); |
617
|
|
|
} |
618
|
|
|
|
619
|
|
|
/** |
620
|
|
|
* Cache definition for sequences |
621
|
|
|
*/ |
622
|
|
|
private function getSequenceCacheSQL(Sequence $sequence) : string |
623
|
|
|
{ |
624
|
|
|
if ($sequence->getCache() > 1) { |
625
|
|
|
return ' CACHE ' . $sequence->getCache(); |
626
|
|
|
} |
627
|
|
|
|
628
|
|
|
return ''; |
629
|
|
|
} |
630
|
|
|
|
631
|
|
|
/** |
632
|
|
|
* {@inheritDoc} |
633
|
|
|
*/ |
634
|
|
|
public function getDropSequenceSQL($sequence) : string |
635
|
|
|
{ |
636
|
|
|
if ($sequence instanceof Sequence) { |
637
|
|
|
$sequence = $sequence->getQuotedName($this); |
638
|
|
|
} |
639
|
|
|
|
640
|
|
|
return 'DROP SEQUENCE ' . $sequence . ' CASCADE'; |
641
|
|
|
} |
642
|
|
|
|
643
|
|
|
public function getCreateSchemaSQL(string $schemaName) : string |
644
|
|
|
{ |
645
|
|
|
return 'CREATE SCHEMA ' . $schemaName; |
646
|
|
|
} |
647
|
|
|
|
648
|
|
|
/** |
649
|
|
|
* {@inheritDoc} |
650
|
|
|
*/ |
651
|
|
|
public function getDropForeignKeySQL($foreignKey, $table) : string |
652
|
|
|
{ |
653
|
|
|
return $this->getDropConstraintSQL($foreignKey, $table); |
654
|
|
|
} |
655
|
|
|
|
656
|
|
|
/** |
657
|
|
|
* {@inheritDoc} |
658
|
|
|
*/ |
659
|
|
|
protected function _getCreateTableSQL(string $tableName, array $columns, array $options = []) : array |
660
|
|
|
{ |
661
|
|
|
$queryFields = $this->getColumnDeclarationListSQL($columns); |
662
|
|
|
|
663
|
|
|
if (isset($options['primary']) && ! empty($options['primary'])) { |
664
|
|
|
$keyColumns = array_unique(array_values($options['primary'])); |
665
|
|
|
$queryFields .= ', PRIMARY KEY(' . implode(', ', $keyColumns) . ')'; |
666
|
|
|
} |
667
|
|
|
|
668
|
|
|
$query = 'CREATE TABLE ' . $tableName . ' (' . $queryFields . ')'; |
669
|
|
|
|
670
|
|
|
$sql = [$query]; |
671
|
|
|
|
672
|
|
|
if (isset($options['indexes']) && ! empty($options['indexes'])) { |
673
|
|
|
foreach ($options['indexes'] as $index) { |
674
|
|
|
$sql[] = $this->getCreateIndexSQL($index, $tableName); |
675
|
|
|
} |
676
|
|
|
} |
677
|
|
|
|
678
|
|
|
if (isset($options['foreignKeys'])) { |
679
|
|
|
foreach ((array) $options['foreignKeys'] as $definition) { |
680
|
|
|
$sql[] = $this->getCreateForeignKeySQL($definition, $tableName); |
681
|
|
|
} |
682
|
|
|
} |
683
|
|
|
|
684
|
|
|
return $sql; |
685
|
|
|
} |
686
|
|
|
|
687
|
|
|
/** |
688
|
|
|
* Converts a single boolean value. |
689
|
|
|
* |
690
|
|
|
* First converts the value to its native PHP boolean type |
691
|
|
|
* and passes it to the given callback function to be reconverted |
692
|
|
|
* into any custom representation. |
693
|
|
|
* |
694
|
|
|
* @param mixed $value The value to convert. |
695
|
|
|
* @param callable $callback The callback function to use for converting the real boolean value. |
696
|
|
|
* |
697
|
|
|
* @return mixed |
698
|
|
|
* |
699
|
|
|
* @throws UnexpectedValueException |
700
|
|
|
*/ |
701
|
|
|
private function convertSingleBooleanValue($value, callable $callback) |
702
|
|
|
{ |
703
|
|
|
if ($value === null) { |
704
|
|
|
return $callback(null); |
705
|
|
|
} |
706
|
|
|
|
707
|
|
|
if (is_bool($value) || is_numeric($value)) { |
708
|
|
|
return $callback((bool) $value); |
709
|
|
|
} |
710
|
|
|
|
711
|
|
|
if (! is_string($value)) { |
712
|
|
|
return $callback(true); |
713
|
|
|
} |
714
|
|
|
|
715
|
|
|
/** |
716
|
|
|
* Better safe than sorry: http://php.net/in_array#106319 |
717
|
|
|
*/ |
718
|
|
|
if (in_array(strtolower(trim($value)), $this->booleanLiterals['false'], true)) { |
719
|
|
|
return $callback(false); |
720
|
|
|
} |
721
|
|
|
|
722
|
|
|
if (in_array(strtolower(trim($value)), $this->booleanLiterals['true'], true)) { |
723
|
|
|
return $callback(true); |
724
|
|
|
} |
725
|
|
|
|
726
|
|
|
throw new UnexpectedValueException(sprintf( |
727
|
|
|
'Unrecognized boolean literal, %s given.', |
728
|
|
|
$value |
729
|
|
|
)); |
730
|
|
|
} |
731
|
|
|
|
732
|
|
|
/** |
733
|
|
|
* Converts one or multiple boolean values. |
734
|
|
|
* |
735
|
|
|
* First converts the value(s) to their native PHP boolean type |
736
|
|
|
* and passes them to the given callback function to be reconverted |
737
|
|
|
* into any custom representation. |
738
|
|
|
* |
739
|
|
|
* @param mixed $item The value(s) to convert. |
740
|
|
|
* @param callable $callback The callback function to use for converting the real boolean value(s). |
741
|
|
|
* |
742
|
|
|
* @return mixed |
743
|
|
|
*/ |
744
|
|
|
private function doConvertBooleans($item, callable $callback) |
745
|
|
|
{ |
746
|
|
|
if (is_array($item)) { |
747
|
|
|
foreach ($item as $key => $value) { |
748
|
|
|
$item[$key] = $this->convertSingleBooleanValue($value, $callback); |
749
|
|
|
} |
750
|
|
|
|
751
|
|
|
return $item; |
752
|
|
|
} |
753
|
|
|
|
754
|
|
|
return $this->convertSingleBooleanValue($item, $callback); |
755
|
|
|
} |
756
|
|
|
|
757
|
|
|
/** |
758
|
|
|
* {@inheritDoc} |
759
|
|
|
* |
760
|
|
|
* Postgres wants boolean values converted to the strings 'true'/'false'. |
761
|
|
|
*/ |
762
|
|
|
public function convertBooleans($item) |
763
|
|
|
{ |
764
|
|
|
if (! $this->useBooleanTrueFalseStrings) { |
765
|
|
|
return parent::convertBooleans($item); |
766
|
|
|
} |
767
|
|
|
|
768
|
|
|
return $this->doConvertBooleans( |
769
|
|
|
$item, |
770
|
|
|
static function ($boolean) : string { |
771
|
|
|
if ($boolean === null) { |
772
|
|
|
return 'NULL'; |
773
|
|
|
} |
774
|
|
|
|
775
|
|
|
return $boolean === true ? 'true' : 'false'; |
776
|
|
|
} |
777
|
|
|
); |
778
|
|
|
} |
779
|
|
|
|
780
|
|
|
/** |
781
|
|
|
* {@inheritDoc} |
782
|
|
|
*/ |
783
|
|
|
public function convertBooleansToDatabaseValue($item) |
784
|
|
|
{ |
785
|
|
|
if (! $this->useBooleanTrueFalseStrings) { |
786
|
|
|
return parent::convertBooleansToDatabaseValue($item); |
787
|
|
|
} |
788
|
|
|
|
789
|
|
|
return $this->doConvertBooleans( |
790
|
|
|
$item, |
791
|
|
|
static function ($boolean) : ?int { |
792
|
|
|
return $boolean === null ? null : (int) $boolean; |
793
|
|
|
} |
794
|
|
|
); |
795
|
|
|
} |
796
|
|
|
|
797
|
|
|
/** |
798
|
|
|
* {@inheritDoc} |
799
|
|
|
*/ |
800
|
|
|
public function convertFromBoolean($item) : ?bool |
801
|
|
|
{ |
802
|
|
|
if (in_array($item, $this->booleanLiterals['false'], true)) { |
803
|
|
|
return false; |
804
|
|
|
} |
805
|
|
|
|
806
|
|
|
return parent::convertFromBoolean($item); |
807
|
|
|
} |
808
|
|
|
|
809
|
|
|
public function getSequenceNextValSQL(string $sequenceName) : string |
810
|
|
|
{ |
811
|
|
|
return "SELECT NEXTVAL('" . $sequenceName . "')"; |
812
|
|
|
} |
813
|
|
|
|
814
|
|
|
public function getSetTransactionIsolationSQL(int $level) : string |
815
|
|
|
{ |
816
|
|
|
return 'SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ' |
817
|
|
|
. $this->_getTransactionIsolationLevelSQL($level); |
818
|
|
|
} |
819
|
|
|
|
820
|
|
|
/** |
821
|
|
|
* {@inheritDoc} |
822
|
|
|
*/ |
823
|
|
|
public function getBooleanTypeDeclarationSQL(array $columnDef) : string |
824
|
|
|
{ |
825
|
|
|
return 'BOOLEAN'; |
826
|
|
|
} |
827
|
|
|
|
828
|
|
|
/** |
829
|
|
|
* {@inheritDoc} |
830
|
|
|
*/ |
831
|
|
|
public function getIntegerTypeDeclarationSQL(array $columnDef) : string |
832
|
|
|
{ |
833
|
|
|
if (! empty($columnDef['autoincrement'])) { |
834
|
|
|
return 'SERIAL'; |
835
|
|
|
} |
836
|
|
|
|
837
|
|
|
return 'INT'; |
838
|
|
|
} |
839
|
|
|
|
840
|
|
|
/** |
841
|
|
|
* {@inheritDoc} |
842
|
|
|
*/ |
843
|
|
|
public function getBigIntTypeDeclarationSQL(array $columnDef) : string |
844
|
|
|
{ |
845
|
|
|
if (! empty($columnDef['autoincrement'])) { |
846
|
|
|
return 'BIGSERIAL'; |
847
|
|
|
} |
848
|
|
|
|
849
|
|
|
return 'BIGINT'; |
850
|
|
|
} |
851
|
|
|
|
852
|
|
|
/** |
853
|
|
|
* {@inheritDoc} |
854
|
|
|
*/ |
855
|
|
|
public function getSmallIntTypeDeclarationSQL(array $columnDef) : string |
856
|
|
|
{ |
857
|
|
|
if (! empty($columnDef['autoincrement'])) { |
858
|
|
|
return 'SMALLSERIAL'; |
859
|
|
|
} |
860
|
|
|
|
861
|
|
|
return 'SMALLINT'; |
862
|
|
|
} |
863
|
|
|
|
864
|
|
|
/** |
865
|
|
|
* {@inheritDoc} |
866
|
|
|
*/ |
867
|
|
|
public function getGuidTypeDeclarationSQL(array $column) : string |
868
|
|
|
{ |
869
|
|
|
return 'UUID'; |
870
|
|
|
} |
871
|
|
|
|
872
|
|
|
/** |
873
|
|
|
* {@inheritDoc} |
874
|
|
|
*/ |
875
|
|
|
public function getDateTimeTypeDeclarationSQL(array $fieldDeclaration) : string |
876
|
|
|
{ |
877
|
|
|
return 'TIMESTAMP(0) WITHOUT TIME ZONE'; |
878
|
|
|
} |
879
|
|
|
|
880
|
|
|
/** |
881
|
|
|
* {@inheritDoc} |
882
|
|
|
*/ |
883
|
|
|
public function getDateTimeTzTypeDeclarationSQL(array $fieldDeclaration) : string |
884
|
|
|
{ |
885
|
|
|
return 'TIMESTAMP(0) WITH TIME ZONE'; |
886
|
|
|
} |
887
|
|
|
|
888
|
|
|
/** |
889
|
|
|
* {@inheritDoc} |
890
|
|
|
*/ |
891
|
|
|
public function getDateTypeDeclarationSQL(array $fieldDeclaration) : string |
892
|
|
|
{ |
893
|
|
|
return 'DATE'; |
894
|
|
|
} |
895
|
|
|
|
896
|
|
|
/** |
897
|
|
|
* {@inheritDoc} |
898
|
|
|
*/ |
899
|
|
|
public function getTimeTypeDeclarationSQL(array $fieldDeclaration) : string |
900
|
|
|
{ |
901
|
|
|
return 'TIME(0) WITHOUT TIME ZONE'; |
902
|
|
|
} |
903
|
|
|
|
904
|
|
|
/** |
905
|
|
|
* {@inheritDoc} |
906
|
|
|
*/ |
907
|
|
|
protected function _getCommonIntegerTypeDeclarationSQL(array $columnDef) : string |
908
|
|
|
{ |
909
|
|
|
return ''; |
910
|
|
|
} |
911
|
|
|
|
912
|
|
|
protected function getVarcharTypeDeclarationSQLSnippet(?int $length) : string |
913
|
|
|
{ |
914
|
|
|
$sql = 'VARCHAR'; |
915
|
|
|
|
916
|
|
|
if ($length !== null) { |
917
|
|
|
$sql .= sprintf('(%d)', $length); |
918
|
|
|
} |
919
|
|
|
|
920
|
|
|
return $sql; |
921
|
|
|
} |
922
|
|
|
|
923
|
|
|
protected function getBinaryTypeDeclarationSQLSnippet(?int $length) : string |
924
|
|
|
{ |
925
|
|
|
return 'BYTEA'; |
926
|
|
|
} |
927
|
|
|
|
928
|
|
|
protected function getVarbinaryTypeDeclarationSQLSnippet(?int $length) : string |
929
|
|
|
{ |
930
|
|
|
return 'BYTEA'; |
931
|
|
|
} |
932
|
|
|
|
933
|
|
|
/** |
934
|
|
|
* {@inheritDoc} |
935
|
|
|
*/ |
936
|
|
|
public function getClobTypeDeclarationSQL(array $field) : string |
937
|
|
|
{ |
938
|
|
|
return 'TEXT'; |
939
|
|
|
} |
940
|
|
|
|
941
|
|
|
public function getName() : string |
942
|
|
|
{ |
943
|
|
|
return 'postgresql'; |
944
|
|
|
} |
945
|
|
|
|
946
|
|
|
/** |
947
|
|
|
* {@inheritDoc} |
948
|
|
|
* |
949
|
|
|
* PostgreSQL returns all column names in SQL result sets in lowercase. |
950
|
|
|
*/ |
951
|
|
|
public function getSQLResultCasing(string $column) : string |
952
|
|
|
{ |
953
|
|
|
return strtolower($column); |
954
|
|
|
} |
955
|
|
|
|
956
|
|
|
public function getDateTimeTzFormatString() : string |
957
|
|
|
{ |
958
|
|
|
return 'Y-m-d H:i:sO'; |
959
|
|
|
} |
960
|
|
|
|
961
|
|
|
public function getEmptyIdentityInsertSQL(string $tableName, string $identifierColumnName) : string |
962
|
|
|
{ |
963
|
|
|
return 'INSERT INTO ' . $tableName . ' (' . $identifierColumnName . ') VALUES (DEFAULT)'; |
964
|
|
|
} |
965
|
|
|
|
966
|
|
|
public function getTruncateTableSQL(string $tableName, bool $cascade = false) : string |
967
|
|
|
{ |
968
|
|
|
$tableIdentifier = new Identifier($tableName); |
969
|
|
|
$sql = 'TRUNCATE ' . $tableIdentifier->getQuotedName($this); |
970
|
|
|
|
971
|
|
|
if ($cascade) { |
972
|
|
|
$sql .= ' CASCADE'; |
973
|
|
|
} |
974
|
|
|
|
975
|
|
|
return $sql; |
976
|
|
|
} |
977
|
|
|
|
978
|
|
|
public function getReadLockSQL() : string |
979
|
|
|
{ |
980
|
|
|
return 'FOR SHARE'; |
981
|
|
|
} |
982
|
|
|
|
983
|
|
|
protected function initializeDoctrineTypeMappings() : void |
984
|
|
|
{ |
985
|
|
|
$this->doctrineTypeMapping = [ |
986
|
|
|
'bigint' => 'bigint', |
987
|
|
|
'bigserial' => 'bigint', |
988
|
|
|
'bool' => 'boolean', |
989
|
|
|
'boolean' => 'boolean', |
990
|
|
|
'bpchar' => 'string', |
991
|
|
|
'bytea' => 'blob', |
992
|
|
|
'char' => 'string', |
993
|
|
|
'date' => 'date', |
994
|
|
|
'datetime' => 'datetime', |
995
|
|
|
'decimal' => 'decimal', |
996
|
|
|
'double' => 'float', |
997
|
|
|
'double precision' => 'float', |
998
|
|
|
'float' => 'float', |
999
|
|
|
'float4' => 'float', |
1000
|
|
|
'float8' => 'float', |
1001
|
|
|
'inet' => 'string', |
1002
|
|
|
'int' => 'integer', |
1003
|
|
|
'int2' => 'smallint', |
1004
|
|
|
'int4' => 'integer', |
1005
|
|
|
'int8' => 'bigint', |
1006
|
|
|
'integer' => 'integer', |
1007
|
|
|
'interval' => 'string', |
1008
|
|
|
'json' => 'json', |
1009
|
|
|
'jsonb' => 'json', |
1010
|
|
|
'money' => 'decimal', |
1011
|
|
|
'numeric' => 'decimal', |
1012
|
|
|
'serial' => 'integer', |
1013
|
|
|
'serial4' => 'integer', |
1014
|
|
|
'serial8' => 'bigint', |
1015
|
|
|
'real' => 'float', |
1016
|
|
|
'smallint' => 'smallint', |
1017
|
|
|
'text' => 'text', |
1018
|
|
|
'time' => 'time', |
1019
|
|
|
'timestamp' => 'datetime', |
1020
|
|
|
'timestamptz' => 'datetimetz', |
1021
|
|
|
'timetz' => 'time', |
1022
|
|
|
'tsvector' => 'text', |
1023
|
|
|
'uuid' => 'guid', |
1024
|
|
|
'varchar' => 'string', |
1025
|
|
|
'year' => 'date', |
1026
|
|
|
'_varchar' => 'string', |
1027
|
|
|
]; |
1028
|
|
|
} |
1029
|
|
|
|
1030
|
|
|
public function hasNativeJsonType() : bool |
1031
|
|
|
{ |
1032
|
|
|
return true; |
1033
|
|
|
} |
1034
|
|
|
|
1035
|
|
|
protected function getReservedKeywordsClass() : string |
1036
|
|
|
{ |
1037
|
|
|
return Keywords\PostgreSQL94Keywords::class; |
1038
|
|
|
} |
1039
|
|
|
|
1040
|
|
|
/** |
1041
|
|
|
* {@inheritDoc} |
1042
|
|
|
*/ |
1043
|
|
|
public function getBlobTypeDeclarationSQL(array $field) : string |
1044
|
|
|
{ |
1045
|
|
|
return 'BYTEA'; |
1046
|
|
|
} |
1047
|
|
|
|
1048
|
|
|
/** |
1049
|
|
|
* {@inheritdoc} |
1050
|
|
|
*/ |
1051
|
|
|
public function getDefaultValueDeclarationSQL(array $field) : string |
1052
|
|
|
{ |
1053
|
|
|
if ($this->isSerialField($field)) { |
1054
|
|
|
return ''; |
1055
|
|
|
} |
1056
|
|
|
|
1057
|
|
|
return parent::getDefaultValueDeclarationSQL($field); |
1058
|
|
|
} |
1059
|
|
|
|
1060
|
|
|
public function supportsColumnCollation() : bool |
1061
|
|
|
{ |
1062
|
|
|
return true; |
1063
|
|
|
} |
1064
|
|
|
|
1065
|
|
|
public function getColumnCollationDeclarationSQL(string $collation) : string |
1066
|
|
|
{ |
1067
|
|
|
return 'COLLATE ' . $this->quoteSingleIdentifier($collation); |
1068
|
|
|
} |
1069
|
|
|
|
1070
|
|
|
/** |
1071
|
|
|
* {@inheritdoc} |
1072
|
|
|
*/ |
1073
|
|
|
public function getJsonTypeDeclarationSQL(array $field) : string |
1074
|
|
|
{ |
1075
|
|
|
if (! empty($field['jsonb'])) { |
1076
|
|
|
return 'JSONB'; |
1077
|
|
|
} |
1078
|
|
|
|
1079
|
|
|
return 'JSON'; |
1080
|
|
|
} |
1081
|
|
|
|
1082
|
|
|
/** |
1083
|
|
|
* @param mixed[] $field |
1084
|
|
|
*/ |
1085
|
|
|
private function isSerialField(array $field) : bool |
1086
|
|
|
{ |
1087
|
|
|
return isset($field['type'], $field['autoincrement']) |
1088
|
|
|
&& $field['autoincrement'] === true |
1089
|
|
|
&& $this->isNumericType($field['type']); |
1090
|
|
|
} |
1091
|
|
|
|
1092
|
|
|
/** |
1093
|
|
|
* Check whether the type of a column is changed in a way that invalidates the default value for the column |
1094
|
|
|
*/ |
1095
|
|
|
private function typeChangeBreaksDefaultValue(ColumnDiff $columnDiff) : bool |
1096
|
|
|
{ |
1097
|
|
|
if ($columnDiff->fromColumn === null) { |
1098
|
|
|
return $columnDiff->hasChanged('type'); |
1099
|
|
|
} |
1100
|
|
|
|
1101
|
|
|
$oldTypeIsNumeric = $this->isNumericType($columnDiff->fromColumn->getType()); |
1102
|
|
|
$newTypeIsNumeric = $this->isNumericType($columnDiff->column->getType()); |
1103
|
|
|
|
1104
|
|
|
// default should not be changed when switching between numeric types and the default comes from a sequence |
1105
|
|
|
return $columnDiff->hasChanged('type') |
1106
|
|
|
&& ! ($oldTypeIsNumeric && $newTypeIsNumeric && $columnDiff->column->getAutoincrement()); |
1107
|
|
|
} |
1108
|
|
|
|
1109
|
|
|
private function isNumericType(Type $type) : bool |
1110
|
|
|
{ |
1111
|
|
|
return $type instanceof IntegerType || $type instanceof BigIntType; |
1112
|
|
|
} |
1113
|
|
|
|
1114
|
|
|
private function getOldColumnComment(ColumnDiff $columnDiff) : ?string |
1115
|
|
|
{ |
1116
|
|
|
if ($columnDiff->fromColumn === null) { |
1117
|
|
|
return null; |
1118
|
|
|
} |
1119
|
|
|
|
1120
|
|
|
return $this->getColumnComment($columnDiff->fromColumn); |
1121
|
|
|
} |
1122
|
|
|
|
1123
|
|
|
public function getListTableMetadataSQL(string $table, ?string $schema = null) : string |
1124
|
|
|
{ |
1125
|
|
|
if ($schema !== null) { |
1126
|
|
|
$table = $schema . '.' . $table; |
1127
|
|
|
} |
1128
|
|
|
|
1129
|
|
|
return sprintf( |
1130
|
|
|
<<<'SQL' |
1131
|
|
|
SELECT obj_description(%s::regclass) AS table_comment; |
1132
|
|
|
SQL |
1133
|
|
|
, |
1134
|
|
|
$this->quoteStringLiteral($table) |
1135
|
|
|
); |
1136
|
|
|
} |
1137
|
|
|
} |
1138
|
|
|
|