Test Failed
Pull Request — dev (#53)
by Def
23:31 queued 23:31
created

SchemaPDOOracle   F

Complexity

Total Complexity 92

Size/Duplication

Total Lines 894
Duplicated Lines 0 %

Test Coverage

Coverage 95.33%

Importance

Changes 1
Bugs 0 Features 0
Metric Value
eloc 367
dl 0
loc 894
ccs 245
cts 257
cp 0.9533
rs 2
c 1
b 0
f 0
wmc 92

32 Methods

Rating   Name   Duplication   Size   Complexity  
A releaseSavepoint() 0 2 1
A findSchemaNames() 0 10 1
A findColumns() 0 51 5
A getCacheTag() 0 6 1
A loadTableForeignKeys() 0 5 2
A createColumnSchema() 0 3 1
A loadTableIndexes() 0 47 3
A normalizePdoRowKeyCase() 0 13 3
B extractColumnType() 0 27 10
B loadTableConstraints() 0 87 8
A createColumnSchemaBuilder() 0 3 1
A getCacheKey() 0 7 1
A setTransactionIsolationLevel() 0 3 1
A loadTableDefaultValues() 0 3 1
A supportsSavepoint() 0 3 1
B createColumn() 0 63 10
A loadTableChecks() 0 5 2
A __construct() 0 4 1
A extractColumnSize() 0 10 5
A createSavepoint() 0 3 1
A findTableNames() 0 38 4
A loadTableUniques() 0 5 2
A resolveTableNames() 0 14 3
A rollBackSavepoint() 0 3 1
B findConstraints() 0 81 8
A getTableSequenceName() 0 15 2
A resolveTableName() 0 20 3
A loadTableSchema() 0 12 2
A getRawTableName() 0 9 2
A findUniqueIndexes() 0 27 2
A loadTablePrimaryKey() 0 5 2
A getLastInsertID() 0 14 2

How to fix   Complexity   

Complex Class

Complex classes like SchemaPDOOracle often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.

Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.

While breaking up the class, it is a good idea to analyze how other classes use SchemaPDOOracle, and based on these observations, apply Extract Interface, too.

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\InvalidCallException;
18
use Yiisoft\Db\Exception\InvalidConfigException;
19
use Yiisoft\Db\Exception\NotSupportedException;
20
use Yiisoft\Db\Expression\Expression;
21
use Yiisoft\Db\Oracle\ColumnSchema;
22
use Yiisoft\Db\Oracle\ColumnSchemaBuilder;
23
use Yiisoft\Db\Oracle\TableSchema;
24
use Yiisoft\Db\Schema\Schema;
25
26
use function array_change_key_case;
27
use function array_map;
28
use function array_merge;
29
use function explode;
30
use function is_array;
31
use function md5;
32
use function preg_replace;
33
use function serialize;
34
use function str_contains;
35
use function str_replace;
36
use function stripos;
37
use function strlen;
38
use function substr;
39
use function trim;
40
41
/**
42
 * Schema is the class for retrieving metadata from an Oracle database.
43
 *
44
 * @property string $lastInsertID The row ID of the last row inserted, or the last value retrieved from the
45
 * sequence object. This property is read-only.
46
 *
47
 * @psalm-type ConstraintArray = array<
48
 *   array-key,
49 348
 *   array {
50
 *     name: string,
51 348
 *     column_name: string,
52 348
 *     type: string,
53
 *     foreign_table_schema: string|null,
54
 *     foreign_table_name: string|null,
55 70
 *     foreign_column_name: string|null,
56
 *     on_update: string,
57 70
 *     on_delete: string,
58
 *     check_expr: string
59 70
 *   }
60
 * >
61 70
 */
62
final class SchemaPDOOracle extends Schema
63
{
64
    public function __construct(private ConnectionPDOInterface $db, SchemaCache $schemaCache)
65 70
    {
66 70
        $this->defaultSchema = strtoupper($db->getDriver()->getUsername());
67
        parent::__construct($schemaCache);
68
    }
69 70
70 70
    protected function resolveTableName(string $name): TableSchema
71
    {
72 70
        $resolvedName = new TableSchema();
73
74 70
        $parts = explode('.', str_replace('"', '', $name));
75
76
        if (isset($parts[1])) {
77
            $resolvedName->schemaName($parts[0]);
78
            $resolvedName->name($parts[1]);
79
        } else {
80 1
            $resolvedName->schemaName($this->defaultSchema);
81
            $resolvedName->name($name);
82 1
        }
83
84
        $fullName = ($resolvedName->getSchemaName() !== $this->defaultSchema
85
            ? (string) $resolvedName->getSchemaName() . '.' : '') . $resolvedName->getName();
86
87
        $resolvedName->fullName($fullName);
88
89 1
        return $resolvedName;
90
    }
91
92
    /**
93
     * @see https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm
94
     */
95
    protected function findSchemaNames(): array
96
    {
97
        $sql = <<<SQL
98
        SELECT "u"."USERNAME"
99 5
        FROM "DBA_USERS" "u"
100
        WHERE "u"."DEFAULT_TABLESPACE" NOT IN ('SYSTEM', 'SYSAUX')
101 5
        ORDER BY "u"."USERNAME" ASC
102 5
        SQL;
103
104
        return $this->db->createCommand($sql)->queryColumn();
105
    }
106
107
    /**
108
     * @param string $schema
109
     *
110
     * @throws Exception|InvalidConfigException|Throwable
111
     *
112
     * @return array
113
     */
114 5
    protected function findTableNames(string $schema = ''): array
115
    {
116
        if ($schema === '') {
117
            $sql = <<<SQL
118
            SELECT TABLE_NAME
119
            FROM USER_TABLES
120
            UNION ALL
121
            SELECT VIEW_NAME AS TABLE_NAME
122
            FROM USER_VIEWS
123
            UNION ALL
124
            SELECT MVIEW_NAME AS TABLE_NAME
125 5
            FROM USER_MVIEWS
126 5
            ORDER BY TABLE_NAME
127
            SQL;
128 5
129 5
            $command = $this->db->createCommand($sql);
130 1
        } else {
131
            $sql = <<<SQL
132 5
            SELECT OBJECT_NAME AS TABLE_NAME
133
            FROM ALL_OBJECTS
134
            WHERE OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') AND OWNER = :schema
135 5
            ORDER BY OBJECT_NAME
136
            SQL;
137
            $command = $this->db->createCommand($sql, [':schema' => $schema]);
138
        }
139
140
        $rows = $command->queryAll();
141
        $names = [];
142
143
        /** @psalm-var string[][] $rows */
144
        foreach ($rows as $row) {
145 84
            if ($this->db->getSlavePdo()?->getAttribute(PDO::ATTR_CASE) === PDO::CASE_LOWER) {
146
                $row = array_change_key_case($row, CASE_UPPER);
147 84
            }
148
            $names[] = $row['TABLE_NAME'];
149 84
        }
150
151 84
        return $names;
152 71
    }
153 71
154
    /**
155
     * @param string $name
156 21
     *
157
     * @throws Exception|InvalidConfigException|Throwable
158
     *
159
     * @return TableSchema|null
160
     */
161
    protected function loadTableSchema(string $name): ?TableSchema
162
    {
163
        $table = new TableSchema();
164
165
        $this->resolveTableNames($table, $name);
166 30
167
        if ($this->findColumns($table)) {
168 30
            $this->findConstraints($table);
169
            return $table;
170
        }
171
172
        return null;
173
    }
174
175
    /**
176
     * @param string $tableName
177
     *
178 4
     * @throws Exception|InvalidConfigException|NotSupportedException|Throwable
179
     *
180 4
     * @return Constraint|null
181
     */
182
    protected function loadTablePrimaryKey(string $tableName): ?Constraint
183
    {
184
        /** @var mixed */
185
        $tablePrimaryKey = $this->loadTableConstraints($tableName, self::PRIMARY_KEY);
186
        return $tablePrimaryKey instanceof Constraint ? $tablePrimaryKey : null;
187
    }
188
189
    /**
190 27
     * @param string $tableName
191
     *
192 27
     * @throws Exception|InvalidConfigException|NotSupportedException|Throwable
193
     *
194
     * @return array
195
     */
196
    protected function loadTableForeignKeys(string $tableName): array
197
    {
198
        /** @var mixed */
199
        $tableForeingKeys = $this->loadTableConstraints($tableName, self::FOREIGN_KEYS);
200
        return is_array($tableForeingKeys) ? $tableForeingKeys : [];
201
    }
202
203
    /**
204
     * @param string $tableName
205 27
     *
206
     * @throws Exception|InvalidConfigException|NotSupportedException|Throwable
207 27
     *
208 27
     * @return array
209 27
     */
210 27
    protected function loadTableIndexes(string $tableName): array
211
    {
212 27
        $sql = <<<SQL
213
        SELECT "ui"."INDEX_NAME" AS "name", "uicol"."COLUMN_NAME" AS "column_name",
214 27
        CASE "ui"."UNIQUENESS" WHEN 'UNIQUE' THEN 1 ELSE 0 END AS "index_is_unique",
215
        CASE WHEN "uc"."CONSTRAINT_NAME" IS NOT NULL THEN 1 ELSE 0 END AS "index_is_primary"
216 27
        FROM "SYS"."USER_INDEXES" "ui"
217
        LEFT JOIN "SYS"."USER_IND_COLUMNS" "uicol"
218
        ON "uicol"."INDEX_NAME" = "ui"."INDEX_NAME"
219
        LEFT JOIN "SYS"."USER_CONSTRAINTS" "uc"
220
        ON "uc"."OWNER" = "ui"."TABLE_OWNER" AND "uc"."CONSTRAINT_NAME" = "ui"."INDEX_NAME" AND "uc"."CONSTRAINT_TYPE" = 'P'
221
        WHERE "ui"."TABLE_OWNER" = :schemaName AND "ui"."TABLE_NAME" = :tableName
222 27
        ORDER BY "uicol"."COLUMN_POSITION" ASC
223 24
        SQL;
224
225 24
        $resolvedName = $this->resolveTableName($tableName);
226 17
227
        $indexes = $this->db->createCommand($sql, [
228
            ':schemaName' => $resolvedName->getSchemaName(),
229 24
            ':tableName' => $resolvedName->getName(),
230 24
        ])->queryAll();
231 24
232 24
        /** @psalm-var array[] $indexes */
233 24
        $indexes = $this->normalizePdoRowKeyCase($indexes, true);
234
        $indexes = ArrayHelper::index($indexes, null, 'name');
235
236 27
        $result = [];
237
238
        /**
239
         * @psalm-var object|string|null $name
240
         * @psalm-var array[] $index
241
         */
242
        foreach ($indexes as $name => $index) {
243
            $columnNames = ArrayHelper::getColumn($index, 'column_name');
244
245
            if ($columnNames[0] === null) {
246 13
                $columnNames[0] = '';
247
            }
248 13
249
            $result[] = (new IndexConstraint())
250
                ->primary((bool) $index[0]['index_is_primary'])
251
                ->unique((bool) $index[0]['index_is_unique'])
252
                ->name($name)
253
                ->columnNames($columnNames);
254
        }
255
256
        return $result;
257
    }
258 13
259
    /**
260 13
     * @param string $tableName
261
     *
262
     * @throws Exception|InvalidConfigException|NotSupportedException|Throwable
263
     *
264
     * @return array
265
     */
266
    protected function loadTableUniques(string $tableName): array
267
    {
268
        /** @var mixed */
269
        $tableUniques = $this->loadTableConstraints($tableName, self::UNIQUES);
270 12
        return is_array($tableUniques) ? $tableUniques : [];
271
    }
272 12
273
    /**
274
     * @param string $tableName
275
     *
276
     * @throws Exception|InvalidConfigException|NotSupportedException|Throwable
277
     *
278
     * @return array
279
     */
280 3
    protected function loadTableChecks(string $tableName): array
281
    {
282 3
        /** @var mixed */
283
        $tableCheck = $this->loadTableConstraints($tableName, self::CHECKS);
284
        return is_array($tableCheck) ? $tableCheck : [];
285
    }
286
287
    /**
288
     * @param string $tableName
289
     *
290
     * @throws NotSupportedException if this method is called.
291
     *
292
     * @return array
293
     */
294
    protected function loadTableDefaultValues(string $tableName): array
295 3
    {
296
        throw new NotSupportedException('Oracle does not support default value constraints.');
297 3
    }
298
299
    public function releaseSavepoint(string $name): void
300
    {
301
        /* does nothing as Oracle does not support this */
302
    }
303
304
    /**
305
     * Create a column schema builder instance giving the type and value precision.
306 84
     *
307
     * This method may be overridden by child classes to create a DBMS-specific column schema builder.
308 84
     *
309
     * @param string $type type of the column. See {@see ColumnSchemaBuilder::$type}.
310 84
     * @param array|int|string|null $length length or precision of the column {@see ColumnSchemaBuilder::$length}.
311
     *
312
     * @return ColumnSchemaBuilder column schema builder instance
313
     *
314 84
     * @psalm-param string[]|int|string|null $length
315 84
     */
316
    public function createColumnSchemaBuilder(string $type, array|int|string $length = null): ColumnSchemaBuilder
317
    {
318 84
        return new ColumnSchemaBuilder($type, $length);
319 84
    }
320
321
    /**
322
     * Resolves the table name and schema name (if any).
323
     *
324
     * @param TableSchema $table the table metadata object
325
     * @param string $name the table name
326
     */
327
    protected function resolveTableNames(TableSchema $table, string $name): void
328
    {
329
        $parts = explode('.', str_replace('"', '', $name));
330
331 84
        if (isset($parts[1])) {
332
            $table->schemaName($parts[0]);
333 84
            $table->name($parts[1]);
334
        } else {
335
            $table->schemaName($this->defaultSchema);
336
            $table->name($name);
337
        }
338
339
        $table->fullName($table->getSchemaName() !== $this->defaultSchema
340
            ? (string) $table->getSchemaName() . '.' . $table->getName() : $table->getName());
341
    }
342
343
    /**
344
     * Collects the table column metadata.
345
     *
346
     * @param TableSchema $table the table schema.
347
     *
348
     * @throws Exception|Throwable
349
     *
350
     * @return bool whether the table exists.
351
     */
352
    protected function findColumns(TableSchema $table): bool
353
    {
354
        $sql = <<<SQL
355
        SELECT
356
            A.COLUMN_NAME,
357
            A.DATA_TYPE,
358 84
            A.DATA_PRECISION,
359 84
            A.DATA_SCALE,
360 84
            (
361 84
            CASE A.CHAR_USED WHEN 'C' THEN A.CHAR_LENGTH
362
                ELSE A.DATA_LENGTH
363
            END
364
            ) AS DATA_LENGTH,
365
            A.NULLABLE,
366 84
            A.DATA_DEFAULT,
367 21
            COM.COMMENTS AS COLUMN_COMMENT
368
        FROM ALL_TAB_COLUMNS A
369
            INNER JOIN ALL_OBJECTS B ON B.OWNER = A.OWNER AND LTRIM(B.OBJECT_NAME) = LTRIM(A.TABLE_NAME)
370 71
            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)
371 71
        WHERE
372 1
            A.OWNER = :schemaName
373
            AND B.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW')
374
            AND B.OBJECT_NAME = :tableName
375 71
        ORDER BY A.COLUMN_ID
376
        SQL;
377 71
378
        try {
379
            $columns = $this->db->createCommand($sql, [
380 71
                ':tableName' => $table->getName(),
381
                ':schemaName' => $table->getSchemaName(),
382
            ])->queryAll();
383
        } catch (Exception) {
384
            return false;
385
        }
386
387
        if (empty($columns)) {
388
            return false;
389
        }
390
391
        /** @psalm-var string[][] $columns */
392
        foreach ($columns as $column) {
393
            if ($this->db->getSlavePdo()?->getAttribute(PDO::ATTR_CASE) === PDO::CASE_LOWER) {
394 53
                $column = array_change_key_case($column, CASE_UPPER);
395
            }
396 53
397
            $c = $this->createColumn($column);
398
399
            $table->columns($c->getName(), $c);
400
        }
401
402
        return true;
403
    }
404
405
    /**
406 53
     * Sequence name of table.
407
     *
408 53
     * @param string $tableName
409
     *
410
     * @throws Exception|InvalidConfigException|Throwable
411
     *
412
     * @return bool|int|string|null whether the sequence exists.
413
     *
414
     * @internal TableSchema `$table->getName()` the table schema.
415
     */
416
    protected function getTableSequenceName(string $tableName): bool|string|int|null
417
    {
418
        $sequenceNameSql = <<<SQL
419
        SELECT
420
            UD.REFERENCED_NAME AS SEQUENCE_NAME
421
        FROM USER_DEPENDENCIES UD
422
            JOIN USER_TRIGGERS UT ON (UT.TRIGGER_NAME = UD.NAME)
423
        WHERE
424 3
            UT.TABLE_NAME = :tableName
425
            AND UD.TYPE = 'TRIGGER'
426 3
            AND UD.REFERENCED_TYPE = 'SEQUENCE'
427
        SQL;
428 3
        $sequenceName = $this->db->createCommand($sequenceNameSql, [':tableName' => $tableName])->queryScalar();
429
430 3
        return $sequenceName === false ? null : $sequenceName;
431 3
    }
432
433
    /**
434
     * @Overrides method in class 'Schema'
435
     *
436
     * {@see https://secure.php.net/manual/en/function.PDO-lastInsertId.php} -> Oracle does not support this.
437
     *
438
     * Returns the ID of the last inserted row or sequence value.
439
     *
440
     * @param string $sequenceName name of the sequence object (required by some DBMS)
441
     *
442
     * @throws Exception|InvalidCallException|InvalidConfigException|Throwable if the DB connection is not active.
443
     *
444
     * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object.
445 71
     */
446
    public function getLastInsertID(string $sequenceName = ''): string
447 71
    {
448
        if ($this->db->isActive()) {
449 71
            /* get the last insert id from the master connection */
450 71
            $sequenceName = $this->db->getQuoter()->quoteSimpleTableName($sequenceName);
451 71
452 71
            return (string) $this->db->useMaster(
453
                static function (ConnectionPDOInterface $db) use ($sequenceName): bool|int|null|string {
454 71
                    return $db->createCommand("SELECT $sequenceName.CURRVAL FROM DUAL")->queryScalar();
455
                }
456 71
            );
457 71
        }
458 71
459 71
        throw new InvalidCallException('DB Connection is not active.');
460
    }
461
462 71
    /**
463
     * Creates ColumnSchema instance.
464 71
     *
465 71
     * @param array|string $column
466 71
     *
467 71
     * @return ColumnSchema
468
     */
469
    protected function createColumn(array|string $column): ColumnSchema
470 71
    {
471
        $c = $this->createColumnSchema();
472 71
473 71
        /**
474 14
         * @psalm-var array{
475
         *   COLUMN_NAME: string,
476 71
         *   DATA_TYPE: string,
477
         *   DATA_PRECISION: string,
478 71
         *   DATA_SCALE: string,
479
         *   DATA_LENGTH: string,
480
         *   NULLABLE: string,
481 71
         *   DATA_DEFAULT: string|null,
482 52
         *   COLUMN_COMMENT: string|null
483 52
         * } $column
484
         */
485 14
        $c->name($column['COLUMN_NAME']);
486
        $c->allowNull($column['NULLABLE'] === 'Y');
487 52
        $c->comment($column['COLUMN_COMMENT'] ?? '');
488
        $c->primaryKey(false);
489
490 71
        $this->extractColumnType(
491
            $c,
492
            $column['DATA_TYPE'],
493
            $column['DATA_PRECISION'],
494
            $column['DATA_SCALE'],
495 71
            $column['DATA_LENGTH']
496
        );
497
498
        $this->extractColumnSize(
499
            $c,
500
            $column['DATA_TYPE'],
501
            $column['DATA_PRECISION'],
502
            $column['DATA_SCALE'],
503
            $column['DATA_LENGTH']
504
        );
505 71
506
        $c->phpType($this->getColumnPhpType($c));
507 71
508
        if (!$c->isPrimaryKey()) {
509
            if ($column['DATA_DEFAULT'] !== null && stripos($column['DATA_DEFAULT'], 'timestamp') !== false) {
510
                $c->defaultValue(null);
511
            } else {
512
                $defaultValue = $column['DATA_DEFAULT'];
513
514
                if ($c->getType() === 'timestamp' && $defaultValue === 'CURRENT_TIMESTAMP') {
515
                    $c->defaultValue(new Expression('CURRENT_TIMESTAMP'));
516
                } else {
517
                    if ($defaultValue !== null) {
518
                        if (($len = strlen($defaultValue)) > 2 && $defaultValue[0] === "'"
519
                            && $defaultValue[$len - 1] === "'"
520
                        ) {
521
                            $defaultValue = substr((string) $column['DATA_DEFAULT'], 1, -1);
522
                        } else {
523
                            $defaultValue = trim($defaultValue);
524
                        }
525
                    }
526
                    $c->defaultValue($c->phpTypecast($defaultValue));
527
                }
528 71
            }
529 71
        }
530 71
531
        return $c;
532
    }
533 71
534
    /**
535 71
     * Finds constraints and fills them into TableSchema object passed.
536 65
     *
537 1
     * @param TableSchema $table
538
     *
539
     * @throws Exception|InvalidConfigException|Throwable
540 65
     *
541 53
     * @psalm-suppress PossiblyNullArrayOffset
542 53
     */
543
    protected function findConstraints(TableSchema $table): void
544 53
    {
545 53
        $sql = <<<SQL
546
        SELECT
547
            /*+ PUSH_PRED(C) PUSH_PRED(D) PUSH_PRED(E) */
548
            D.CONSTRAINT_NAME,
549 65
            D.CONSTRAINT_TYPE,
550
            C.COLUMN_NAME,
551
            C.POSITION,
552
            D.R_CONSTRAINT_NAME,
553
            E.TABLE_NAME AS TABLE_REF,
554
            F.COLUMN_NAME AS COLUMN_REF,
555 65
            C.TABLE_NAME
556
        FROM ALL_CONS_COLUMNS C
557
            INNER JOIN ALL_CONSTRAINTS D ON D.OWNER = C.OWNER AND D.CONSTRAINT_NAME = C.CONSTRAINT_NAME
558 8
            LEFT JOIN ALL_CONSTRAINTS E ON E.OWNER = D.R_OWNER AND E.CONSTRAINT_NAME = D.R_CONSTRAINT_NAME
559
            LEFT JOIN ALL_CONS_COLUMNS F ON F.OWNER = E.OWNER AND F.CONSTRAINT_NAME = E.CONSTRAINT_NAME AND F.POSITION = C.POSITION
560 8
        WHERE
561 8
            C.OWNER = :schemaName
562 8
            AND C.TABLE_NAME = :tableName
563
            ORDER BY D.CONSTRAINT_NAME, C.POSITION
564
        SQL;
565
566
        /**
567 8
         * @psalm-var array{
568
         *   array{
569
         *     CONSTRAINT_NAME: string,
570 71
         *     CONSTRAINT_TYPE: string,
571 8
         *     COLUMN_NAME: string,
572 8
         *     POSITION: string|null,
573
         *     R_CONSTRAINT_NAME: string|null,
574
         *     TABLE_REF: string|null,
575
         *     COLUMN_REF: string|null,
576
         *     TABLE_NAME: string
577
         *   }
578
         * } $rows
579
         */
580
        $rows = $this->db->createCommand(
581
            $sql,
582
            [':tableName' => $table->getName(), ':schemaName' => $table->getSchemaName()]
583
        )->queryAll();
584
585
        $constraints = [];
586
587
        foreach ($rows as $row) {
588
            if ($this->db->getSlavePdo()?->getAttribute(PDO::ATTR_CASE) === PDO::CASE_LOWER) {
589
                $row = array_change_key_case($row, CASE_UPPER);
590
            }
591
592
            if ($row['CONSTRAINT_TYPE'] === 'P') {
593
                $table->getColumns()[(string) $row['COLUMN_NAME']]->primaryKey(true);
594 1
                $table->primaryKey((string) $row['COLUMN_NAME']);
595
596 1
                if (empty($table->getSequenceName())) {
597
                    $table->sequenceName((string) $this->getTableSequenceName($table->getName()));
598
                }
599
            }
600
601
            if ($row['CONSTRAINT_TYPE'] !== 'R') {
602
                /**
603
                 * This condition is not checked in SQL WHERE because of an Oracle Bug:
604
                 *
605
                 * {@see https://github.com/yiisoft/yii2/pull/8844}
606
                 */
607
                continue;
608 1
            }
609
610 1
            $name = (string) $row['CONSTRAINT_NAME'];
611 1
612 1
            if (!isset($constraints[$name])) {
613
                $constraints[$name] = [
614
                    'tableName' => $row['TABLE_REF'],
615 1
                    'columns' => [],
616 1
                ];
617
            }
618
619 1
            $constraints[$name]['columns'][$row['COLUMN_NAME']] = $row['COLUMN_REF'];
620
        }
621
622
        foreach ($constraints as $constraint) {
623
            $table->foreignKey(array_merge([$constraint['tableName']], $constraint['columns']));
624
        }
625
    }
626
627
    /**
628
     * Returns all unique indexes for the given table.
629
     *
630
     * Each array element is of the following structure:.
631 71
     *
632
     * ```php
633
     * [
634
     *     'IndexName1' => ['col1' [, ...]],
635
     *     'IndexName2' => ['col2' [, ...]],
636
     * ]
637
     * ```
638 71
     *
639
     * @param TableSchema $table the table metadata.
640 71
     *
641 16
     * @throws Exception|InvalidConfigException|Throwable
642 71
     *
643 68
     * @return array all unique indexes for the given table.
644 17
     */
645
    public function findUniqueIndexes(TableSchema $table): array
646 68
    {
647
        $query = <<<SQL
648 64
        SELECT
649
            DIC.INDEX_NAME,
650 64
            DIC.COLUMN_NAME
651 18
        FROM ALL_INDEXES DI
652 61
            INNER JOIN ALL_IND_COLUMNS DIC ON DI.TABLE_NAME = DIC.TABLE_NAME AND DI.INDEX_NAME = DIC.INDEX_NAME
653 22
        WHERE
654 60
            DI.UNIQUENESS = 'UNIQUE'
655 15
            AND DIC.TABLE_OWNER = :schemaName
656
            AND DIC.TABLE_NAME = :tableName
657 60
        ORDER BY DIC.TABLE_NAME, DIC.INDEX_NAME, DIC.COLUMN_POSITION
658
        SQL;
659
        $result = [];
660
661
        $rows = $this->db->createCommand(
662
            $query,
663
            [':tableName' => $table->getName(), ':schemaName' => $table->getschemaName()]
664
        )->queryAll();
665
666
        /** @psalm-var array<array{INDEX_NAME: string, COLUMN_NAME: string}> $rows */
667
        foreach ($rows as $row) {
668
            $result[$row['INDEX_NAME']][] = $row['COLUMN_NAME'];
669
        }
670 71
671
        return $result;
672
    }
673
674
    /**
675
     * Extracts the data types for the given column.
676
     *
677 71
     * @param ColumnSchema $column
678 71
     * @param string $dbType DB type.
679 71
     * @param string|null $precision total number of digits.
680
     * @param string|null $scale number of digits on the right of the decimal separator.
681
     * @param string $length length for character types.
682
     */
683
    protected function extractColumnType(
684
        ColumnSchema $column,
685
        string $dbType,
686
        ?string $precision,
0 ignored issues
show
Unused Code introduced by
The parameter $precision is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

686
        /** @scrutinizer ignore-unused */ ?string $precision,

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
687
        ?string $scale,
688
        string $length
0 ignored issues
show
Unused Code introduced by
The parameter $length is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

688
        /** @scrutinizer ignore-unused */ string $length

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
689
    ): void {
690
        $column->dbType($dbType);
691
692
        if (str_contains($dbType, 'FLOAT') || str_contains($dbType, 'DOUBLE')) {
693
            $column->type('double');
694
        } elseif (str_contains($dbType, 'NUMBER')) {
695
            if ($scale === null || $scale > 0) {
696 60
                $column->type('decimal');
697
            } else {
698 60
                $column->type('integer');
699
            }
700
        } elseif (str_contains($dbType, 'INTEGER')) {
701
            $column->type('integer');
702
        } elseif (str_contains($dbType, 'BLOB')) {
703
            $column->type('binary');
704
        } elseif (str_contains($dbType, 'CLOB')) {
705
            $column->type('text');
706
        } elseif (str_contains($dbType, 'TIMESTAMP')) {
707
            $column->type('timestamp');
708
        } else {
709
            $column->type('string');
710
        }
711
    }
712
713
    /**
714
     * Extracts size, precision and scale information from column's DB type.
715
     *
716
     * @param ColumnSchema $column
717
     * @param string $dbType the column's DB type.
718
     * @param string|null $precision total number of digits.
719 60
     * @param string|null $scale number of digits on the right of the decimal separator.
720
     * @param string $length length for character types.
721 60
     */
722 60
    protected function extractColumnSize(
723 60
        ColumnSchema $column,
724 60
        string $dbType,
0 ignored issues
show
Unused Code introduced by
The parameter $dbType is not used and could be removed. ( Ignorable by Annotation )

If this is a false-positive, you can also ignore this issue in your code via the ignore-unused  annotation

724
        /** @scrutinizer ignore-unused */ string $dbType,

This check looks for parameters that have been defined for a function or method, but which are not used in the method body.

Loading history...
725
        ?string $precision,
726 60
        ?string $scale,
727
        string $length
728 60
    ): void {
729
        $column->size(trim($length) === '' ? null : (int) $length);
730 60
        $column->precision(trim((string) $precision) === '' ? null : (int) $precision);
731
        $column->scale($scale === '' || $scale === null ? null : (int) $scale);
732
    }
733
734
    /**
735
     * Loads multiple types of constraints and returns the specified ones.
736
     *
737
     * @param string $tableName table name.
738
     * @param string $returnType return type:
739
     * - primaryKey
740
     * - foreignKeys
741 60
     * - uniques
742
     * - checks
743
     *
744
     * @throws Exception|InvalidConfigException|NotSupportedException|Throwable
745
     *
746 60
     * @return mixed constraints.
747 60
     */
748 60
    private function loadTableConstraints(string $tableName, string $returnType): mixed
749 45
    {
750 45
        $sql = <<<SQL
751 45
        SELECT
752 45
            "uc"."CONSTRAINT_NAME" AS "name",
753 60
            "uccol"."COLUMN_NAME" AS "column_name",
754 13
            "uc"."CONSTRAINT_TYPE" AS "type",
755 13
            "fuc"."OWNER" AS "foreign_table_schema",
756 13
            "fuc"."TABLE_NAME" AS "foreign_table_name",
757 13
            "fuccol"."COLUMN_NAME" AS "foreign_column_name",
758 13
            "uc"."DELETE_RULE" AS "on_delete",
759 13
            "uc"."SEARCH_CONDITION" AS "check_expr"
760 13
        FROM "USER_CONSTRAINTS" "uc"
761 13
        INNER JOIN "USER_CONS_COLUMNS" "uccol"
762 13
        ON "uccol"."OWNER" = "uc"."OWNER" AND "uccol"."CONSTRAINT_NAME" = "uc"."CONSTRAINT_NAME"
763 60
        LEFT JOIN "USER_CONSTRAINTS" "fuc"
764 46
        ON "fuc"."OWNER" = "uc"."R_OWNER" AND "fuc"."CONSTRAINT_NAME" = "uc"."R_CONSTRAINT_NAME"
765 46
        LEFT JOIN "USER_CONS_COLUMNS" "fuccol"
766 46
        ON "fuccol"."OWNER" = "fuc"."OWNER" AND "fuccol"."CONSTRAINT_NAME" = "fuc"."CONSTRAINT_NAME" AND "fuccol"."POSITION" = "uccol"."POSITION"
767 46
        WHERE "uc"."OWNER" = :schemaName AND "uc"."TABLE_NAME" = :tableName
768 60
        ORDER BY "uccol"."POSITION" ASC
769 60
        SQL;
770 60
771 60
        $resolvedName = $this->resolveTableName($tableName);
772 60
773 60
        $constraints = $this->db->createCommand($sql, [
774
            ':schemaName' => $resolvedName->getSchemaName(),
775
            ':tableName' => $resolvedName->getName(),
776
        ])->queryAll();
777
778 60
        /** @var Constraint[] $constraints */
779 60
        $constraints = $this->normalizePdoRowKeyCase($constraints, true);
780
        $constraints = ArrayHelper::index($constraints, null, ['type', 'name']);
781
782 60
        $result = [
783
            'primaryKey' => null,
784
            'foreignKeys' => [],
785
            'uniques' => [],
786
            'checks' => [],
787
        ];
788
789
        /**
790
         * @var string $type
791
         * @var array $names
792 71
         */
793
        foreach ($constraints as $type => $names) {
794 71
            /**
795
             * @psalm-var object|string|null $name
796
             * @psalm-var ConstraintArray $constraint
797 1
             */
798
            foreach ($names as $name => $constraint) {
799 1
                switch ($type) {
800
                    case 'P':
801
                        $result['primaryKey'] = (new Constraint())
802 2
                            ->name($name)
803
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'));
804 2
                        break;
805
                    case 'R':
806
                        $result['foreignKeys'][] = (new ForeignKeyConstraint())
807
                            ->name($name)
808
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'))
809
                            ->foreignSchemaName($constraint[0]['foreign_table_schema'])
810
                            ->foreignTableName($constraint[0]['foreign_table_name'])
811
                            ->foreignColumnNames(ArrayHelper::getColumn($constraint, 'foreign_column_name'))
812
                            ->onDelete($constraint[0]['on_delete'])
813
                            ->onUpdate(null);
814
                        break;
815
                    case 'U':
816
                        $result['uniques'][] = (new Constraint())
817 144
                            ->name($name)
818
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'));
819 144
                        break;
820 21
                    case 'C':
821
                        $result['checks'][] = (new CheckConstraint())
822 21
                            ->name($name)
823
                            ->columnNames(ArrayHelper::getColumn($constraint, 'column_name'))
824
                            ->expression($constraint[0]['check_expr']);
825 144
                        break;
826
                }
827
            }
828
        }
829
830
        foreach ($result as $type => $data) {
831
            $this->setTableMetadata($tableName, $type, $data);
832
        }
833
834
        return $result[$returnType];
835 144
    }
836
837
    /**
838 144
     * Creates a column schema for the database.
839 144
     *
840 144
     * This method may be overridden by child classes to create a DBMS-specific column schema.
841 144
     *
842
     * @return ColumnSchema column schema instance.
843
     */
844
    protected function createColumnSchema(): ColumnSchema
845
    {
846
        return new ColumnSchema();
847
    }
848
849
    public function rollBackSavepoint(string $name): void
850
    {
851
        $this->db->createCommand("ROLLBACK TO SAVEPOINT $name")->execute();
852 144
    }
853
854 144
    public function setTransactionIsolationLevel(string $level): void
855
    {
856 144
        $this->db->createCommand("SET TRANSACTION ISOLATION LEVEL $level")->execute();
857 144
    }
858
859
    /**
860
     * Returns the actual name of a given table name.
861
     *
862
     * This method will strip off curly brackets from the given table name and replace the percentage character '%' with
863
     * {@see ConnectionInterface::tablePrefix}.
864
     *
865
     * @param string $name the table name to be converted.
866
     *
867
     * @return string the real name of the given table name.
868
     */
869
    public function getRawTableName(string $name): string
870
    {
871 70
        if (str_contains($name, '{{')) {
872
            $name = preg_replace('/{{(.*?)}}/', '\1', $name);
873 70
874 54
            return str_replace('%', $this->db->getTablePrefix(), $name);
875
        }
876
877 16
        return $name;
878 16
    }
879 15
880
    /**
881
     * Returns the cache key for the specified table name.
882
     *
883
     * @param string $name the table name.
884
     *
885
     * @return array the cache key.
886
     */
887
    protected function getCacheKey(string $name): array
888
    {
889 2
        return [
890
            __CLASS__,
891 2
            $this->db->getDriver()->getDsn(),
892
            $this->db->getDriver()->getUsername(),
893
            $this->getRawTableName($name),
894
        ];
895
    }
896
897
    /**
898
     * Returns the cache tag name.
899
     *
900
     * This allows {@see refresh()} to invalidate all cached table schemas.
901 1
     *
902
     * @return string the cache tag name.
903 1
     */
904
    protected function getCacheTag(): string
905
    {
906
        return md5(serialize([
907
            __CLASS__,
908
            $this->db->getDriver()->getDsn(),
909
            $this->db->getDriver()->getUsername(),
910
        ]));
911
    }
912
913
    /**
914
     * Changes row's array key case to lower if PDO's one is set to uppercase.
915
     *
916
     * @param array $row row's array or an array of row's arrays.
917
     * @param bool $multiple whether multiple rows or a single row passed.
918
     *
919
     * @throws Exception
920
     *
921
     * @return array normalized row or rows.
922
     */
923
    protected function normalizePdoRowKeyCase(array $row, bool $multiple): array
924
    {
925
        if ($this->db->getSlavePdo()?->getAttribute(PDO::ATTR_CASE) !== PDO::CASE_UPPER) {
926
            return $row;
927
        }
928
929
        if ($multiple) {
930
            return array_map(static function (array $row) {
931
                return array_change_key_case($row, CASE_LOWER);
932
            }, $row);
933
        }
934
935
        return array_change_key_case($row, CASE_LOWER);
936
    }
937
938
    /**
939
     * @return bool whether this DBMS supports [savepoint](http://en.wikipedia.org/wiki/Savepoint).
940
     */
941
    public function supportsSavepoint(): bool
942
    {
943
        return $this->db->isSavepointEnabled();
944
    }
945
946
    /**
947
     * Creates a new savepoint.
948
     *
949
     * @param string $name the savepoint name
950
     *
951
     * @throws Exception|InvalidConfigException|Throwable
952
     */
953
    public function createSavepoint(string $name): void
954
    {
955
        $this->db->createCommand("SAVEPOINT $name")->execute();
956
    }
957
}
958