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