1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
declare(strict_types=1); |
4
|
|
|
|
5
|
|
|
namespace Yiisoft\Db\Oracle; |
6
|
|
|
|
7
|
|
|
use Throwable; |
8
|
|
|
use Yiisoft\Db\Cache\SchemaCache; |
9
|
|
|
use Yiisoft\Db\Connection\ConnectionInterface; |
10
|
|
|
use Yiisoft\Db\Constraint\CheckConstraint; |
11
|
|
|
use Yiisoft\Db\Constraint\Constraint; |
12
|
|
|
use Yiisoft\Db\Constraint\ForeignKeyConstraint; |
13
|
|
|
use Yiisoft\Db\Constraint\IndexConstraint; |
14
|
|
|
use Yiisoft\Db\Exception\Exception; |
15
|
|
|
use Yiisoft\Db\Exception\InvalidConfigException; |
16
|
|
|
use Yiisoft\Db\Exception\NotSupportedException; |
17
|
|
|
use Yiisoft\Db\Expression\Expression; |
18
|
|
|
use Yiisoft\Db\Helper\ArrayHelper; |
19
|
|
|
use Yiisoft\Db\Schema\AbstractSchema; |
20
|
|
|
use Yiisoft\Db\Schema\Builder\ColumnInterface; |
|
|
|
|
21
|
|
|
use Yiisoft\Db\Schema\ColumnSchemaInterface; |
22
|
|
|
use Yiisoft\Db\Schema\TableSchemaInterface; |
23
|
|
|
|
24
|
|
|
use function array_merge; |
25
|
|
|
use function is_array; |
26
|
|
|
use function md5; |
27
|
|
|
use function serialize; |
28
|
|
|
use function str_contains; |
29
|
|
|
use function strlen; |
30
|
|
|
use function substr; |
31
|
|
|
use function trim; |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* Schema is the class for retrieving metadata from an Oracle database. |
35
|
|
|
* |
36
|
|
|
* @property string $lastInsertID The row ID of the last row inserted, or the last value retrieved from the |
37
|
|
|
* sequence object. This property is read-only. |
38
|
|
|
* |
39
|
|
|
* @psalm-type ConstraintArray = array< |
40
|
|
|
* array-key, |
41
|
|
|
* array { |
42
|
|
|
* name: string, |
43
|
|
|
* column_name: string, |
44
|
|
|
* type: string, |
45
|
|
|
* foreign_table_schema: string|null, |
46
|
|
|
* foreign_table_name: string|null, |
47
|
|
|
* foreign_column_name: string|null, |
48
|
|
|
* on_update: string, |
49
|
|
|
* on_delete: string, |
50
|
|
|
* check_expr: string |
51
|
|
|
* } |
52
|
|
|
* > |
53
|
|
|
*/ |
54
|
|
|
final class Schema extends AbstractSchema |
55
|
533 |
|
{ |
56
|
|
|
public function __construct(protected ConnectionInterface $db, SchemaCache $schemaCache, string $defaultSchema) |
57
|
533 |
|
{ |
58
|
533 |
|
$this->defaultSchema = $defaultSchema; |
59
|
|
|
parent::__construct($db, $schemaCache); |
60
|
|
|
} |
61
|
197 |
|
|
62
|
|
|
public function createColumn(string $type, array|int|string $length = null): ColumnInterface |
63
|
197 |
|
{ |
64
|
|
|
return new Column($type, $length); |
65
|
197 |
|
} |
66
|
197 |
|
|
67
|
197 |
|
|
68
|
|
|
protected function resolveTableName(string $name): TableSchemaInterface |
69
|
197 |
|
{ |
70
|
197 |
|
$resolvedName = new TableSchema(); |
71
|
|
|
|
72
|
197 |
|
$parts = array_reverse( |
73
|
197 |
|
$this->db->getQuoter()->getTableNameParts($name) |
74
|
197 |
|
); |
75
|
197 |
|
|
76
|
|
|
$resolvedName->name($parts[0] ?? ''); |
77
|
197 |
|
$resolvedName->schemaName($parts[1] ?? $this->defaultSchema); |
78
|
|
|
|
79
|
|
|
$resolvedName->fullName( |
80
|
|
|
$resolvedName->getSchemaName() !== $this->defaultSchema ? |
81
|
|
|
implode('.', array_reverse($parts)) : $resolvedName->getName() |
82
|
|
|
); |
83
|
|
|
|
84
|
|
|
return $resolvedName; |
85
|
|
|
} |
86
|
|
|
|
87
|
|
|
/** |
88
|
1 |
|
* @link https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm |
89
|
|
|
* |
90
|
1 |
|
* @throws Exception |
91
|
|
|
* @throws InvalidConfigException |
92
|
|
|
* @throws NotSupportedException |
93
|
|
|
* @throws Throwable |
94
|
|
|
*/ |
95
|
1 |
|
protected function findSchemaNames(): array |
96
|
|
|
{ |
97
|
1 |
|
$sql = <<<SQL |
98
|
|
|
SELECT "u"."USERNAME" |
99
|
|
|
FROM "DBA_USERS" "u" |
100
|
|
|
WHERE "u"."DEFAULT_TABLESPACE" NOT IN ('SYSTEM', 'SYSAUX') |
101
|
|
|
ORDER BY "u"."USERNAME" ASC |
102
|
|
|
SQL; |
103
|
|
|
|
104
|
|
|
return $this->db->createCommand($sql)->queryColumn(); |
105
|
138 |
|
} |
106
|
|
|
|
107
|
138 |
|
/** |
108
|
|
|
* @throws Exception |
109
|
|
|
* @throws InvalidConfigException |
110
|
|
|
* @throws Throwable |
111
|
|
|
*/ |
112
|
|
|
protected function findTableComment(TableSchemaInterface $tableSchema): void |
113
|
138 |
|
{ |
114
|
|
|
$sql = <<<SQL |
115
|
138 |
|
SELECT "COMMENTS" |
116
|
138 |
|
FROM ALL_TAB_COMMENTS |
117
|
138 |
|
WHERE |
118
|
138 |
|
"OWNER" = :schemaName AND |
119
|
|
|
"TABLE_NAME" = :tableName |
120
|
138 |
|
SQL; |
121
|
|
|
|
122
|
|
|
$comment = $this->db->createCommand($sql, [ |
123
|
|
|
':schemaName' => $tableSchema->getSchemaName(), |
124
|
|
|
':tableName' => $tableSchema->getName(), |
125
|
|
|
])->queryScalar(); |
126
|
|
|
|
127
|
|
|
$tableSchema->comment(is_string($comment) ? $comment : null); |
128
|
12 |
|
} |
129
|
|
|
|
130
|
12 |
|
/** |
131
|
11 |
|
* @throws Exception |
132
|
|
|
* @throws InvalidConfigException |
133
|
|
|
* @throws Throwable |
134
|
|
|
*/ |
135
|
|
|
protected function findTableNames(string $schema = ''): array |
136
|
|
|
{ |
137
|
|
|
if ($schema === '') { |
138
|
|
|
$sql = <<<SQL |
139
|
|
|
SELECT TABLE_NAME |
140
|
|
|
FROM USER_TABLES |
141
|
11 |
|
UNION ALL |
142
|
|
|
SELECT VIEW_NAME AS TABLE_NAME |
143
|
11 |
|
FROM USER_VIEWS |
144
|
|
|
UNION ALL |
145
|
1 |
|
SELECT MVIEW_NAME AS TABLE_NAME |
146
|
|
|
FROM USER_MVIEWS |
147
|
|
|
ORDER BY TABLE_NAME |
148
|
|
|
SQL; |
149
|
|
|
|
150
|
1 |
|
$command = $this->db->createCommand($sql); |
151
|
1 |
|
} else { |
152
|
|
|
$sql = <<<SQL |
153
|
|
|
SELECT OBJECT_NAME AS TABLE_NAME |
154
|
12 |
|
FROM ALL_OBJECTS |
155
|
12 |
|
WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') AND OWNER = :schema |
156
|
|
|
ORDER BY OBJECT_NAME |
157
|
|
|
SQL; |
158
|
12 |
|
$command = $this->db->createCommand($sql, [':schema' => $schema]); |
159
|
|
|
} |
160
|
12 |
|
|
161
|
12 |
|
$rows = $command->queryAll(); |
162
|
|
|
$names = []; |
163
|
|
|
|
164
|
12 |
|
/** @psalm-var string[][] $rows */ |
165
|
|
|
foreach ($rows as $row) { |
166
|
|
|
/** @psalm-var string[] $row */ |
167
|
|
|
$row = $this->normalizeRowKeyCase($row, false); |
168
|
|
|
$names[] = $row['table_name']; |
169
|
|
|
} |
170
|
|
|
|
171
|
|
|
return $names; |
172
|
138 |
|
} |
173
|
|
|
|
174
|
138 |
|
/** |
175
|
138 |
|
* @throws Exception |
176
|
|
|
* @throws InvalidConfigException |
177
|
138 |
|
* @throws Throwable |
178
|
121 |
|
*/ |
179
|
121 |
|
protected function loadTableSchema(string $name): TableSchemaInterface|null |
180
|
|
|
{ |
181
|
|
|
$table = $this->resolveTableName($name); |
182
|
32 |
|
$this->findTableComment($table); |
183
|
|
|
|
184
|
|
|
if ($this->findColumns($table)) { |
185
|
|
|
$this->findConstraints($table); |
186
|
|
|
return $table; |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
return null; |
190
|
|
|
} |
191
|
47 |
|
|
192
|
|
|
/** |
193
|
|
|
* @throws Exception |
194
|
47 |
|
* @throws InvalidConfigException |
195
|
47 |
|
* @throws NotSupportedException |
196
|
|
|
* @throws Throwable |
197
|
|
|
*/ |
198
|
|
|
protected function loadTablePrimaryKey(string $tableName): Constraint|null |
199
|
|
|
{ |
200
|
|
|
/** @psalm-var mixed $tablePrimaryKey */ |
201
|
|
|
$tablePrimaryKey = $this->loadTableConstraints($tableName, self::PRIMARY_KEY); |
202
|
|
|
return $tablePrimaryKey instanceof Constraint ? $tablePrimaryKey : null; |
203
|
|
|
} |
204
|
8 |
|
|
205
|
|
|
/** |
206
|
|
|
* @throws Exception |
207
|
8 |
|
* @throws InvalidConfigException |
208
|
8 |
|
* @throws NotSupportedException |
209
|
|
|
* @throws Throwable |
210
|
|
|
*/ |
211
|
|
|
protected function loadTableForeignKeys(string $tableName): array |
212
|
|
|
{ |
213
|
|
|
/** @psalm-var mixed $tableForeingKeys */ |
214
|
|
|
$tableForeingKeys = $this->loadTableConstraints($tableName, self::FOREIGN_KEYS); |
215
|
|
|
return is_array($tableForeingKeys) ? $tableForeingKeys : []; |
216
|
|
|
} |
217
|
38 |
|
|
218
|
|
|
/** |
219
|
38 |
|
* @throws Exception |
220
|
|
|
* @throws InvalidConfigException |
221
|
|
|
* @throws NotSupportedException |
222
|
|
|
* @throws Throwable |
223
|
|
|
*/ |
224
|
|
|
protected function loadTableIndexes(string $tableName): array |
225
|
|
|
{ |
226
|
|
|
$sql = <<<SQL |
227
|
|
|
SELECT "ui"."INDEX_NAME" AS "name", "uicol"."COLUMN_NAME" AS "column_name", |
228
|
|
|
CASE "ui"."UNIQUENESS" WHEN 'UNIQUE' THEN 1 ELSE 0 END AS "index_is_unique", |
229
|
|
|
CASE WHEN "uc"."CONSTRAINT_NAME" IS NOT NULL THEN 1 ELSE 0 END AS "index_is_primary" |
230
|
38 |
|
FROM "SYS"."USER_INDEXES" "ui" |
231
|
|
|
LEFT JOIN "SYS"."USER_IND_COLUMNS" "uicol" |
232
|
38 |
|
ON "uicol"."INDEX_NAME" = "ui"."INDEX_NAME" |
233
|
|
|
LEFT JOIN "SYS"."USER_CONSTRAINTS" "uc" |
234
|
38 |
|
ON "uc"."OWNER" = "ui"."TABLE_OWNER" AND "uc"."CONSTRAINT_NAME" = "ui"."INDEX_NAME" AND "uc"."CONSTRAINT_TYPE" = 'P' |
235
|
38 |
|
WHERE "ui"."TABLE_OWNER" = :schemaName AND "ui"."TABLE_NAME" = :tableName |
236
|
38 |
|
ORDER BY "uicol"."COLUMN_POSITION" ASC |
237
|
38 |
|
SQL; |
238
|
|
|
|
239
|
|
|
$resolvedName = $this->resolveTableName($tableName); |
240
|
38 |
|
|
241
|
38 |
|
$indexes = $this->db->createCommand($sql, [ |
242
|
|
|
':schemaName' => $resolvedName->getSchemaName(), |
243
|
38 |
|
':tableName' => $resolvedName->getName(), |
244
|
|
|
])->queryAll(); |
245
|
|
|
|
246
|
|
|
/** @psalm-var array[] $indexes */ |
247
|
|
|
$indexes = $this->normalizeRowKeyCase($indexes, true); |
248
|
|
|
$indexes = ArrayHelper::index($indexes, null, ['name']); |
249
|
38 |
|
|
250
|
35 |
|
$result = []; |
251
|
|
|
|
252
|
35 |
|
/** |
253
|
20 |
|
* @psalm-var object|string|null $name |
254
|
|
|
* @psalm-var array[] $index |
255
|
|
|
*/ |
256
|
35 |
|
foreach ($indexes as $name => $index) { |
257
|
35 |
|
$columnNames = ArrayHelper::getColumn($index, 'column_name'); |
258
|
35 |
|
|
259
|
35 |
|
if ($columnNames[0] === null) { |
260
|
35 |
|
$columnNames[0] = ''; |
261
|
|
|
} |
262
|
|
|
|
263
|
38 |
|
$result[] = (new IndexConstraint()) |
264
|
|
|
->primary((bool) $index[0]['index_is_primary']) |
265
|
|
|
->unique((bool) $index[0]['index_is_unique']) |
266
|
|
|
->name($name) |
267
|
|
|
->columnNames($columnNames); |
268
|
|
|
} |
269
|
|
|
|
270
|
|
|
return $result; |
271
|
|
|
} |
272
|
17 |
|
|
273
|
|
|
/** |
274
|
|
|
* @throws Exception |
275
|
17 |
|
* @throws InvalidConfigException |
276
|
17 |
|
* @throws NotSupportedException |
277
|
|
|
* @throws Throwable |
278
|
|
|
*/ |
279
|
|
|
protected function loadTableUniques(string $tableName): array |
280
|
|
|
{ |
281
|
|
|
/** @psalm-var mixed $tableUniques */ |
282
|
|
|
$tableUniques = $this->loadTableConstraints($tableName, self::UNIQUES); |
283
|
|
|
return is_array($tableUniques) ? $tableUniques : []; |
284
|
|
|
} |
285
|
17 |
|
|
286
|
|
|
/** |
287
|
|
|
* @throws Exception |
288
|
17 |
|
* @throws InvalidConfigException |
289
|
17 |
|
* @throws NotSupportedException |
290
|
|
|
* @throws Throwable |
291
|
|
|
*/ |
292
|
|
|
protected function loadTableChecks(string $tableName): array |
293
|
|
|
{ |
294
|
|
|
/** @psalm-var mixed $tableCheck */ |
295
|
13 |
|
$tableCheck = $this->loadTableConstraints($tableName, self::CHECKS); |
296
|
|
|
return is_array($tableCheck) ? $tableCheck : []; |
297
|
13 |
|
} |
298
|
|
|
|
299
|
|
|
/** |
300
|
7 |
|
* @throws NotSupportedException if this method is called. |
301
|
|
|
*/ |
302
|
|
|
protected function loadTableDefaultValues(string $tableName): array |
303
|
|
|
{ |
304
|
7 |
|
throw new NotSupportedException(__METHOD__ . ' is not supported by Oracle.'); |
305
|
|
|
} |
306
|
|
|
|
307
|
|
|
/** |
308
|
|
|
* Collects the table column metadata. |
309
|
|
|
* |
310
|
|
|
* @param TableSchemaInterface $table the table schema. |
311
|
|
|
* |
312
|
|
|
* @throws Exception |
313
|
|
|
* @throws Throwable |
314
|
|
|
* |
315
|
|
|
* @return bool whether the table exists. |
316
|
|
|
*/ |
317
|
138 |
|
protected function findColumns(TableSchemaInterface $table): bool |
318
|
|
|
{ |
319
|
138 |
|
$sql = <<<SQL |
320
|
|
|
SELECT |
321
|
|
|
A.COLUMN_NAME, |
322
|
|
|
A.DATA_TYPE, |
323
|
|
|
A.DATA_PRECISION, |
324
|
|
|
A.DATA_SCALE, |
325
|
|
|
( |
326
|
|
|
CASE A.CHAR_USED WHEN 'C' THEN A.CHAR_LENGTH |
327
|
|
|
ELSE A.DATA_LENGTH |
328
|
|
|
END |
329
|
|
|
) AS DATA_LENGTH, |
330
|
|
|
A.NULLABLE, |
331
|
|
|
A.DATA_DEFAULT, |
332
|
|
|
( |
333
|
|
|
SELECT COUNT(*) |
334
|
|
|
FROM ALL_CONSTRAINTS AC |
335
|
|
|
INNER JOIN ALL_CONS_COLUMNS ACC ON ACC.CONSTRAINT_NAME=AC.CONSTRAINT_NAME |
336
|
|
|
WHERE |
337
|
|
|
AC.OWNER = A.OWNER |
338
|
|
|
AND AC.TABLE_NAME = B.OBJECT_NAME |
339
|
|
|
AND ACC.COLUMN_NAME = A.COLUMN_NAME |
340
|
|
|
AND AC.CONSTRAINT_TYPE = 'P' |
341
|
|
|
) AS IS_PK, |
342
|
|
|
COM.COMMENTS AS COLUMN_COMMENT |
343
|
|
|
FROM ALL_TAB_COLUMNS A |
344
|
|
|
INNER JOIN ALL_OBJECTS B ON B.OWNER = A.OWNER AND LTRIM(B.OBJECT_NAME) = LTRIM(A.TABLE_NAME) |
345
|
|
|
LEFT JOIN ALL_COL_COMMENTS COM ON (A.OWNER = COM.OWNER AND A.TABLE_NAME = COM.TABLE_NAME AND A.COLUMN_NAME = COM.COLUMN_NAME) |
346
|
|
|
WHERE |
347
|
|
|
A.OWNER = :schemaName |
348
|
|
|
AND B.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') |
349
|
|
|
AND B.OBJECT_NAME = :tableName |
350
|
|
|
ORDER BY A.COLUMN_ID |
351
|
138 |
|
SQL; |
352
|
|
|
|
353
|
138 |
|
$columns = $this->db->createCommand($sql, [ |
354
|
138 |
|
':tableName' => $table->getName(), |
355
|
138 |
|
':schemaName' => $table->getSchemaName(), |
356
|
138 |
|
])->queryAll(); |
357
|
|
|
|
358
|
138 |
|
if ($columns === []) { |
359
|
32 |
|
return false; |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
/** @psalm-var string[][] $columns */ |
363
|
121 |
|
foreach ($columns as $column) { |
364
|
121 |
|
$column = $this->normalizeRowKeyCase($column, false); |
365
|
|
|
|
366
|
121 |
|
$c = $this->createColumnSchema($column); |
367
|
|
|
|
368
|
121 |
|
$table->columns($c->getName(), $c); |
369
|
|
|
} |
370
|
|
|
|
371
|
121 |
|
return true; |
372
|
|
|
} |
373
|
|
|
|
374
|
|
|
/** |
375
|
|
|
* Sequence name of table. |
376
|
|
|
* |
377
|
|
|
* @throws Exception |
378
|
|
|
* @throws InvalidConfigException |
379
|
|
|
* @throws Throwable |
380
|
|
|
* |
381
|
|
|
* @return bool|float|int|string|null whether the sequence exists. |
382
|
|
|
* |
383
|
|
|
* @internal TableSchemaInterface `$table->getName()` the table schema. |
384
|
|
|
*/ |
385
|
75 |
|
protected function getTableSequenceName(string $tableName): bool|float|int|string|null |
386
|
|
|
{ |
387
|
75 |
|
$sequenceNameSql = <<<SQL |
388
|
|
|
SELECT |
389
|
|
|
UD.REFERENCED_NAME AS SEQUENCE_NAME |
390
|
|
|
FROM USER_DEPENDENCIES UD |
391
|
|
|
JOIN USER_TRIGGERS UT ON (UT.TRIGGER_NAME = UD.NAME) |
392
|
|
|
WHERE |
393
|
|
|
UT.TABLE_NAME = :tableName |
394
|
|
|
AND UD.TYPE = 'TRIGGER' |
395
|
|
|
AND UD.REFERENCED_TYPE = 'SEQUENCE' |
396
|
75 |
|
SQL; |
397
|
75 |
|
$sequenceName = $this->db->createCommand($sequenceNameSql, [':tableName' => $tableName])->queryScalar(); |
398
|
|
|
|
399
|
75 |
|
return $sequenceName === false ? null : $sequenceName; |
400
|
|
|
} |
401
|
|
|
|
402
|
|
|
/** |
403
|
|
|
* Creates ColumnSchema instance. |
404
|
|
|
*/ |
405
|
121 |
|
protected function createColumnSchema(array|string $column): ColumnSchemaInterface |
406
|
|
|
{ |
407
|
121 |
|
$c = new ColumnSchema(); |
408
|
|
|
|
409
|
|
|
/** |
410
|
|
|
* @psalm-var array{ |
411
|
|
|
* column_name: string, |
412
|
|
|
* data_type: string, |
413
|
|
|
* data_precision: string, |
414
|
|
|
* data_scale: string, |
415
|
|
|
* data_length: string, |
416
|
|
|
* nullable: string, |
417
|
|
|
* data_default: string|null, |
418
|
|
|
* is_pk: string|null, |
419
|
|
|
* column_comment: string|null |
420
|
|
|
* } $column |
421
|
|
|
*/ |
422
|
121 |
|
$c->name($column['column_name']); |
423
|
121 |
|
$c->allowNull($column['nullable'] === 'Y'); |
424
|
121 |
|
$c->comment($column['column_comment'] ?? ''); |
425
|
121 |
|
$c->primaryKey((int) ($column['is_pk'] ?? 0) > 0); |
426
|
|
|
|
427
|
121 |
|
$this->extractColumnType( |
428
|
121 |
|
$c, |
429
|
121 |
|
$column['data_type'], |
430
|
121 |
|
$column['data_precision'], |
431
|
121 |
|
$column['data_scale'], |
432
|
121 |
|
$column['data_length'] |
433
|
121 |
|
); |
434
|
|
|
|
435
|
121 |
|
$this->extractColumnSize( |
436
|
121 |
|
$c, |
437
|
121 |
|
$column['data_type'], |
438
|
121 |
|
$column['data_precision'], |
439
|
121 |
|
$column['data_scale'], |
440
|
121 |
|
$column['data_length'] |
441
|
121 |
|
); |
442
|
|
|
|
443
|
121 |
|
$c->phpType($this->getColumnPhpType($c)); |
444
|
|
|
|
445
|
121 |
|
if (!$c->isPrimaryKey()) { |
446
|
119 |
|
if ($column['data_default'] === null) { |
447
|
116 |
|
$c->defaultValue(null); |
448
|
|
|
} else { |
449
|
69 |
|
$defaultValue = $column['data_default']; |
450
|
|
|
|
451
|
69 |
|
if ($c->getType() === 'timestamp' && $defaultValue === 'CURRENT_TIMESTAMP') { |
452
|
|
|
$c->defaultValue(new Expression('CURRENT_TIMESTAMP')); |
453
|
|
|
} else { |
454
|
|
|
if ( |
455
|
69 |
|
strlen($defaultValue) > 2 |
456
|
69 |
|
&& str_starts_with($defaultValue, "'") |
457
|
69 |
|
&& str_ends_with($defaultValue, "'") |
458
|
|
|
) { |
459
|
25 |
|
$defaultValue = substr($defaultValue, 1, -1); |
460
|
|
|
} else { |
461
|
69 |
|
$defaultValue = trim($defaultValue); |
462
|
|
|
} |
463
|
|
|
|
464
|
69 |
|
$c->defaultValue($c->phpTypecast($defaultValue)); |
465
|
|
|
} |
466
|
|
|
} |
467
|
|
|
} |
468
|
|
|
|
469
|
121 |
|
return $c; |
470
|
|
|
} |
471
|
|
|
|
472
|
|
|
/** |
473
|
|
|
* Finds constraints and fills them into TableSchemaInterface object passed. |
474
|
|
|
* |
475
|
|
|
* @throws Exception |
476
|
|
|
* @throws InvalidConfigException |
477
|
|
|
* @throws Throwable |
478
|
|
|
* |
479
|
|
|
* @psalm-suppress PossiblyNullArrayOffset |
480
|
|
|
*/ |
481
|
121 |
|
protected function findConstraints(TableSchemaInterface $table): void |
482
|
|
|
{ |
483
|
121 |
|
$sql = <<<SQL |
484
|
|
|
SELECT |
485
|
|
|
/*+ PUSH_PRED(C) PUSH_PRED(D) PUSH_PRED(E) */ |
486
|
|
|
D.CONSTRAINT_NAME, |
487
|
|
|
D.CONSTRAINT_TYPE, |
488
|
|
|
C.COLUMN_NAME, |
489
|
|
|
C.POSITION, |
490
|
|
|
D.R_CONSTRAINT_NAME, |
491
|
|
|
E.TABLE_NAME AS TABLE_REF, |
492
|
|
|
F.COLUMN_NAME AS COLUMN_REF, |
493
|
|
|
C.TABLE_NAME |
494
|
|
|
FROM ALL_CONS_COLUMNS C |
495
|
|
|
INNER JOIN ALL_CONSTRAINTS D ON D.OWNER = C.OWNER AND D.CONSTRAINT_NAME = C.CONSTRAINT_NAME |
496
|
|
|
LEFT JOIN ALL_CONSTRAINTS E ON E.OWNER = D.R_OWNER AND E.CONSTRAINT_NAME = D.R_CONSTRAINT_NAME |
497
|
|
|
LEFT JOIN ALL_CONS_COLUMNS F ON F.OWNER = E.OWNER AND F.CONSTRAINT_NAME = E.CONSTRAINT_NAME AND F.POSITION = C.POSITION |
498
|
|
|
WHERE |
499
|
|
|
C.OWNER = :schemaName |
500
|
|
|
AND C.TABLE_NAME = :tableName |
501
|
|
|
ORDER BY D.CONSTRAINT_NAME, C.POSITION |
502
|
121 |
|
SQL; |
503
|
|
|
|
504
|
|
|
/** |
505
|
|
|
* @psalm-var array{ |
506
|
|
|
* array{ |
507
|
|
|
* constraint_name: string, |
508
|
|
|
* constraint_type: string, |
509
|
|
|
* column_name: string, |
510
|
|
|
* position: string|null, |
511
|
|
|
* r_constraint_name: string|null, |
512
|
|
|
* table_ref: string|null, |
513
|
|
|
* column_ref: string|null, |
514
|
|
|
* table_name: string |
515
|
|
|
* } |
516
|
|
|
* } $rows |
517
|
|
|
*/ |
518
|
121 |
|
$rows = $this->db->createCommand( |
519
|
121 |
|
$sql, |
520
|
121 |
|
[':tableName' => $table->getName(), ':schemaName' => $table->getSchemaName()] |
521
|
121 |
|
)->queryAll(); |
522
|
|
|
|
523
|
121 |
|
$constraints = []; |
524
|
|
|
|
525
|
121 |
|
foreach ($rows as $row) { |
526
|
|
|
/** @psalm-var string[] $row */ |
527
|
112 |
|
$row = $this->normalizeRowKeyCase($row, false); |
528
|
|
|
|
529
|
112 |
|
if ($row['constraint_type'] === 'P') { |
530
|
75 |
|
$table->getColumns()[$row['column_name']]->primaryKey(true); |
531
|
75 |
|
$table->primaryKey($row['column_name']); |
532
|
|
|
|
533
|
75 |
|
if (empty($table->getSequenceName())) { |
534
|
75 |
|
$table->sequenceName((string) $this->getTableSequenceName($table->getName())); |
535
|
|
|
} |
536
|
|
|
} |
537
|
|
|
|
538
|
112 |
|
if ($row['constraint_type'] !== 'R') { |
539
|
|
|
/** |
540
|
|
|
* This condition is not checked in SQL WHERE because of an Oracle Bug: |
541
|
|
|
* |
542
|
|
|
* {@see https://github.com/yiisoft/yii2/pull/8844} |
543
|
|
|
*/ |
544
|
112 |
|
continue; |
545
|
|
|
} |
546
|
|
|
|
547
|
14 |
|
$name = $row['constraint_name']; |
548
|
|
|
|
549
|
14 |
|
if (!isset($constraints[$name])) { |
550
|
14 |
|
$constraints[$name] = [ |
551
|
14 |
|
'tableName' => $row['table_ref'], |
552
|
14 |
|
'columns' => [], |
553
|
14 |
|
]; |
554
|
|
|
} |
555
|
|
|
|
556
|
14 |
|
$constraints[$name]['columns'][$row['column_name']] = $row['column_ref']; |
557
|
|
|
} |
558
|
|
|
|
559
|
121 |
|
foreach ($constraints as $index => $constraint) { |
560
|
14 |
|
$table->foreignKey($index, array_merge([$constraint['tableName']], $constraint['columns'])); |
561
|
|
|
} |
562
|
|
|
} |
563
|
|
|
|
564
|
|
|
/** |
565
|
|
|
* Returns all unique indexes for the given table. |
566
|
|
|
* |
567
|
|
|
* Each array element is of the following structure:. |
568
|
|
|
* |
569
|
|
|
* ```php |
570
|
|
|
* [ |
571
|
|
|
* 'IndexName1' => ['col1' [, ...]], |
572
|
|
|
* 'IndexName2' => ['col2' [, ...]], |
573
|
|
|
* ] |
574
|
|
|
* ``` |
575
|
|
|
* |
576
|
|
|
* @param TableSchemaInterface $table the table metadata. |
577
|
|
|
* |
578
|
|
|
* @throws Exception |
579
|
|
|
* @throws InvalidConfigException |
580
|
|
|
* @throws Throwable |
581
|
|
|
* |
582
|
|
|
* @return array all unique indexes for the given table. |
583
|
|
|
*/ |
584
|
1 |
|
public function findUniqueIndexes(TableSchemaInterface $table): array |
585
|
|
|
{ |
586
|
1 |
|
$query = <<<SQL |
587
|
|
|
SELECT |
588
|
|
|
DIC.INDEX_NAME, |
589
|
|
|
DIC.COLUMN_NAME |
590
|
|
|
FROM ALL_INDEXES DI |
591
|
|
|
INNER JOIN ALL_IND_COLUMNS DIC ON DI.TABLE_NAME = DIC.TABLE_NAME AND DI.INDEX_NAME = DIC.INDEX_NAME |
592
|
|
|
WHERE |
593
|
|
|
DI.UNIQUENESS = 'UNIQUE' |
594
|
|
|
AND DIC.TABLE_OWNER = :schemaName |
595
|
|
|
AND DIC.TABLE_NAME = :tableName |
596
|
|
|
ORDER BY DIC.TABLE_NAME, DIC.INDEX_NAME, DIC.COLUMN_POSITION |
597
|
1 |
|
SQL; |
598
|
1 |
|
$result = []; |
599
|
|
|
|
600
|
1 |
|
$rows = $this->db->createCommand( |
601
|
1 |
|
$query, |
602
|
1 |
|
[':tableName' => $table->getName(), ':schemaName' => $table->getschemaName()] |
603
|
1 |
|
)->queryAll(); |
604
|
|
|
|
605
|
|
|
/** @psalm-var array<array{INDEX_NAME: string, COLUMN_NAME: string}> $rows */ |
606
|
1 |
|
foreach ($rows as $row) { |
607
|
1 |
|
$result[$row['INDEX_NAME']][] = $row['COLUMN_NAME']; |
608
|
|
|
} |
609
|
|
|
|
610
|
1 |
|
return $result; |
611
|
|
|
} |
612
|
|
|
|
613
|
|
|
/** |
614
|
|
|
* Extracts the data types for the given column. |
615
|
|
|
* |
616
|
|
|
* @param string $dbType DB type. |
617
|
|
|
* @param string|null $precision total number of digits. |
618
|
|
|
* @param string|null $scale number of digits on the right of the decimal separator. |
619
|
|
|
* @param string $length length for character types. |
620
|
|
|
*/ |
621
|
121 |
|
protected function extractColumnType( |
622
|
|
|
ColumnSchema $column, |
623
|
|
|
string $dbType, |
624
|
|
|
string|null $precision, |
|
|
|
|
625
|
|
|
string|null $scale, |
626
|
|
|
string $length |
|
|
|
|
627
|
|
|
): void { |
628
|
121 |
|
$column->dbType($dbType); |
629
|
|
|
|
630
|
121 |
|
if (str_contains($dbType, 'FLOAT') || str_contains($dbType, 'DOUBLE')) { |
631
|
28 |
|
$column->type(self::TYPE_DOUBLE); |
632
|
121 |
|
} elseif (str_contains($dbType, 'NUMBER')) { |
633
|
118 |
|
if ($scale === null || $scale > 0) { |
634
|
31 |
|
$column->type(self::TYPE_DECIMAL); |
635
|
|
|
} else { |
636
|
118 |
|
$column->type(self::TYPE_INTEGER); |
637
|
|
|
} |
638
|
90 |
|
} elseif (str_contains($dbType, 'BLOB')) { |
639
|
30 |
|
$column->type(self::TYPE_BINARY); |
640
|
86 |
|
} elseif (str_contains($dbType, 'CLOB')) { |
641
|
24 |
|
$column->type(self::TYPE_TEXT); |
642
|
85 |
|
} elseif (str_contains($dbType, 'TIMESTAMP')) { |
643
|
26 |
|
$column->type(self::TYPE_TIMESTAMP); |
644
|
|
|
} else { |
645
|
85 |
|
$column->type(self::TYPE_STRING); |
646
|
|
|
} |
647
|
|
|
} |
648
|
|
|
|
649
|
|
|
/** |
650
|
|
|
* Extracts size, precision and scale information from column's DB type. |
651
|
|
|
* |
652
|
|
|
* @param string $dbType the column's DB type. |
653
|
|
|
* @param string|null $precision total number of digits. |
654
|
|
|
* @param string|null $scale number of digits on the right of the decimal separator. |
655
|
|
|
* @param string $length length for character types. |
656
|
|
|
*/ |
657
|
121 |
|
protected function extractColumnSize( |
658
|
|
|
ColumnSchema $column, |
659
|
|
|
string $dbType, |
|
|
|
|
660
|
|
|
string|null $precision, |
661
|
|
|
string|null $scale, |
662
|
|
|
string $length |
663
|
|
|
): void { |
664
|
121 |
|
$column->size(trim($length) === '' ? null : (int) $length); |
665
|
121 |
|
$column->precision(trim((string) $precision) === '' ? null : (int) $precision); |
666
|
121 |
|
$column->scale($scale === '' || $scale === null ? null : (int) $scale); |
667
|
|
|
} |
668
|
|
|
|
669
|
|
|
/** |
670
|
|
|
* Loads multiple types of constraints and returns the specified ones. |
671
|
|
|
* |
672
|
|
|
* @param string $tableName table name. |
673
|
|
|
* @param string $returnType return type: |
674
|
|
|
* - primaryKey |
675
|
|
|
* - foreignKeys |
676
|
|
|
* - uniques |
677
|
|
|
* - checks |
678
|
|
|
* |
679
|
|
|
* @throws Exception |
680
|
|
|
* @throws InvalidConfigException |
681
|
|
|
* @throws NotSupportedException |
682
|
|
|
* @throws Throwable |
683
|
|
|
* |
684
|
|
|
* @return mixed constraints. |
685
|
|
|
*/ |
686
|
89 |
|
private function loadTableConstraints(string $tableName, string $returnType): mixed |
687
|
|
|
{ |
688
|
89 |
|
$sql = <<<SQL |
689
|
|
|
SELECT |
690
|
|
|
"uc"."CONSTRAINT_NAME" AS "name", |
691
|
|
|
"uccol"."COLUMN_NAME" AS "column_name", |
692
|
|
|
"uc"."CONSTRAINT_TYPE" AS "type", |
693
|
|
|
"fuc"."OWNER" AS "foreign_table_schema", |
694
|
|
|
"fuc"."TABLE_NAME" AS "foreign_table_name", |
695
|
|
|
"fuccol"."COLUMN_NAME" AS "foreign_column_name", |
696
|
|
|
"uc"."DELETE_RULE" AS "on_delete", |
697
|
|
|
"uc"."SEARCH_CONDITION" AS "check_expr" |
698
|
|
|
FROM "USER_CONSTRAINTS" "uc" |
699
|
|
|
INNER JOIN "USER_CONS_COLUMNS" "uccol" |
700
|
|
|
ON "uccol"."OWNER" = "uc"."OWNER" AND "uccol"."CONSTRAINT_NAME" = "uc"."CONSTRAINT_NAME" |
701
|
|
|
LEFT JOIN "USER_CONSTRAINTS" "fuc" |
702
|
|
|
ON "fuc"."OWNER" = "uc"."R_OWNER" AND "fuc"."CONSTRAINT_NAME" = "uc"."R_CONSTRAINT_NAME" |
703
|
|
|
LEFT JOIN "USER_CONS_COLUMNS" "fuccol" |
704
|
|
|
ON "fuccol"."OWNER" = "fuc"."OWNER" AND "fuccol"."CONSTRAINT_NAME" = "fuc"."CONSTRAINT_NAME" AND "fuccol"."POSITION" = "uccol"."POSITION" |
705
|
|
|
WHERE "uc"."OWNER" = :schemaName AND "uc"."TABLE_NAME" = :tableName |
706
|
|
|
ORDER BY "uccol"."POSITION" ASC |
707
|
89 |
|
SQL; |
708
|
|
|
|
709
|
89 |
|
$resolvedName = $this->resolveTableName($tableName); |
710
|
|
|
|
711
|
89 |
|
$constraints = $this->db->createCommand($sql, [ |
712
|
89 |
|
':schemaName' => $resolvedName->getSchemaName(), |
713
|
89 |
|
':tableName' => $resolvedName->getName(), |
714
|
89 |
|
])->queryAll(); |
715
|
|
|
|
716
|
|
|
/** @var array[] $constraints */ |
717
|
89 |
|
$constraints = $this->normalizeRowKeyCase($constraints, true); |
718
|
89 |
|
$constraints = ArrayHelper::index($constraints, null, ['type', 'name']); |
719
|
|
|
|
720
|
89 |
|
$result = [ |
721
|
89 |
|
self::PRIMARY_KEY => null, |
722
|
89 |
|
self::FOREIGN_KEYS => [], |
723
|
89 |
|
self::UNIQUES => [], |
724
|
89 |
|
self::CHECKS => [], |
725
|
89 |
|
]; |
726
|
|
|
|
727
|
|
|
/** |
728
|
|
|
* @var string $type |
729
|
|
|
* @var array $names |
730
|
|
|
*/ |
731
|
89 |
|
foreach ($constraints as $type => $names) { |
732
|
|
|
/** |
733
|
|
|
* @psalm-var object|string|null $name |
734
|
|
|
* @psalm-var ConstraintArray $constraint |
735
|
|
|
*/ |
736
|
82 |
|
foreach ($names as $name => $constraint) { |
737
|
|
|
switch ($type) { |
738
|
82 |
|
case 'P': |
739
|
57 |
|
$result[self::PRIMARY_KEY] = (new Constraint()) |
740
|
57 |
|
->name($name) |
741
|
57 |
|
->columnNames(ArrayHelper::getColumn($constraint, 'column_name')); |
742
|
57 |
|
break; |
743
|
82 |
|
case 'R': |
744
|
19 |
|
$result[self::FOREIGN_KEYS][] = (new ForeignKeyConstraint()) |
745
|
19 |
|
->name($name) |
746
|
19 |
|
->columnNames(ArrayHelper::getColumn($constraint, 'column_name')) |
747
|
19 |
|
->foreignSchemaName($constraint[0]['foreign_table_schema']) |
748
|
19 |
|
->foreignTableName($constraint[0]['foreign_table_name']) |
749
|
19 |
|
->foreignColumnNames(ArrayHelper::getColumn($constraint, 'foreign_column_name')) |
750
|
19 |
|
->onDelete($constraint[0]['on_delete']) |
751
|
19 |
|
->onUpdate(null); |
752
|
19 |
|
break; |
753
|
82 |
|
case 'U': |
754
|
58 |
|
$result[self::UNIQUES][] = (new Constraint()) |
755
|
58 |
|
->name($name) |
756
|
58 |
|
->columnNames(ArrayHelper::getColumn($constraint, 'column_name')); |
757
|
58 |
|
break; |
758
|
82 |
|
case 'C': |
759
|
82 |
|
$result[self::CHECKS][] = (new CheckConstraint()) |
760
|
82 |
|
->name($name) |
761
|
82 |
|
->columnNames(ArrayHelper::getColumn($constraint, 'column_name')) |
762
|
82 |
|
->expression($constraint[0]['check_expr']); |
763
|
82 |
|
break; |
764
|
|
|
} |
765
|
|
|
} |
766
|
|
|
} |
767
|
|
|
|
768
|
89 |
|
foreach ($result as $type => $data) { |
769
|
89 |
|
$this->setTableMetadata($tableName, $type, $data); |
770
|
|
|
} |
771
|
|
|
|
772
|
89 |
|
return $result[$returnType]; |
773
|
|
|
} |
774
|
|
|
|
775
|
|
|
/** |
776
|
|
|
* @throws Exception |
777
|
|
|
* @throws InvalidConfigException |
778
|
|
|
* @throws Throwable |
779
|
|
|
*/ |
780
|
|
|
protected function findViewNames(string $schema = ''): array |
781
|
|
|
{ |
782
|
121 |
|
$sql = match ($schema) { |
783
|
|
|
'' => <<<SQL |
784
|
121 |
|
SELECT VIEW_NAME FROM USER_VIEWS |
785
|
|
|
SQL, |
786
|
|
|
default => <<<SQL |
787
|
|
|
SELECT VIEW_NAME FROM ALL_VIEWS WHERE OWNER = '$schema' |
788
|
|
|
SQL, |
789
|
|
|
}; |
790
|
|
|
|
791
|
|
|
/** @psalm-var string[][] $views */ |
792
|
2 |
|
$views = $this->db->createCommand($sql)->queryAll(); |
793
|
|
|
|
794
|
2 |
|
foreach ($views as $key => $view) { |
795
|
2 |
|
$views[$key] = $view['VIEW_NAME']; |
796
|
|
|
} |
797
|
2 |
|
|
798
|
2 |
|
return $views; |
799
|
2 |
|
} |
800
|
2 |
|
|
801
|
2 |
|
/** |
802
|
|
|
* Returns the cache key for the specified table name. |
803
|
|
|
* |
804
|
2 |
|
* @param string $name the table name. |
805
|
|
|
* |
806
|
2 |
|
* @return array the cache key. |
807
|
2 |
|
*/ |
808
|
|
|
protected function getCacheKey(string $name): array |
809
|
|
|
{ |
810
|
2 |
|
return array_merge([self::class], $this->db->getCacheKey(), [$this->getRawTableName($name)]); |
811
|
|
|
} |
812
|
|
|
|
813
|
|
|
/** |
814
|
|
|
* Returns the cache tag name. |
815
|
|
|
* |
816
|
|
|
* This allows {@see refresh()} to invalidate all cached table schemas. |
817
|
|
|
* |
818
|
|
|
* @return string the cache tag name. |
819
|
|
|
*/ |
820
|
235 |
|
protected function getCacheTag(): string |
821
|
|
|
{ |
822
|
235 |
|
return md5(serialize(array_merge([self::class], $this->db->getCacheKey()))); |
823
|
|
|
} |
824
|
|
|
} |
825
|
|
|
|
The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g.
excluded_paths: ["lib/*"]
, you can move it to the dependency path list as follows:For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths