Passed
Pull Request — dev (#60)
by Def
15:02 queued 07:01
created

SchemaPDOOracle::findTableNames()   A

Complexity

Conditions 4
Paths 6

Size

Total Lines 38
Code Lines 28

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 11
CRAP Score 4.0582

Importance

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