Test Failed
Pull Request — master (#273)
by Sergei
17:10 queued 13:09
created

Schema::loadColumnSchema()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 17
Code Lines 13

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 13
CRAP Score 1

Importance

Changes 5
Bugs 0 Features 0
Metric Value
eloc 13
c 5
b 0
f 0
dl 0
loc 17
ccs 13
cts 13
cp 1
rs 9.8333
cc 1
nc 1
nop 1
crap 1
1
<?php
2
3
declare(strict_types=1);
4
5
namespace Yiisoft\Db\Sqlite;
6
7
use Throwable;
8
use Yiisoft\Db\Constraint\CheckConstraint;
9
use Yiisoft\Db\Constraint\Constraint;
10
use Yiisoft\Db\Constraint\ForeignKeyConstraint;
11
use Yiisoft\Db\Constraint\IndexConstraint;
12
use Yiisoft\Db\Driver\Pdo\AbstractPdoSchema;
13
use Yiisoft\Db\Exception\Exception;
14
use Yiisoft\Db\Exception\InvalidArgumentException;
15
use Yiisoft\Db\Exception\InvalidConfigException;
16
use Yiisoft\Db\Exception\NotSupportedException;
17
use Yiisoft\Db\Expression\Expression;
18
use Yiisoft\Db\Helper\DbArrayHelper;
19
use Yiisoft\Db\Schema\Builder\ColumnInterface;
20
use Yiisoft\Db\Schema\Column\ColumnSchemaInterface;
0 ignored issues
show
Bug introduced by
The type Yiisoft\Db\Schema\Column\ColumnSchemaInterface was not found. Maybe you did not declare it correctly or list all dependencies?

The issue could also be caused by a filter entry in the build configuration. If the path has been excluded in your configuration, e.g. excluded_paths: ["lib/*"], you can move it to the dependency path list as follows:

filter:
    dependency_paths: ["lib/*"]

For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths

Loading history...
21
use Yiisoft\Db\Schema\TableSchemaInterface;
22
23
use function array_column;
24
use function array_merge;
25
use function count;
26
use function explode;
27
use function md5;
28
use function preg_match;
29
use function preg_replace;
30
use function serialize;
31
use function strncasecmp;
32
use function strtolower;
33
34
/**
35
 * Implements the SQLite Server specific schema, supporting SQLite 3.3.0 or higher.
36
 *
37
 * @psalm-type ForeignKeyInfo = array{
38
 *     id:string,
39
 *     cid:string,
40
 *     seq:string,
41
 *     table:string,
42
 *     from:string,
43
 *     to:string|null,
44
 *     on_update:string,
45
 *     on_delete:string
46
 * }
47
 * @psalm-type GroupedForeignKeyInfo = array<
48
 *     string,
49
 *     ForeignKeyInfo[]
50
 * >
51
 * @psalm-type IndexInfo = array{
52
 *     seqno:string,
53
 *     cid:string,
54
 *     name:string
55
 * }
56
 * @psalm-type IndexListInfo = array{
57
 *     seq:string,
58
 *     name:string,
59
 *     unique:string,
60
 *     origin:string,
61
 *     partial:string
62
 * }
63
 * @psalm-type ColumnInfo = array{
64
 *     cid:string,
65
 *     name:string,
66
 *     type:string,
67
 *     notnull:string,
68
 *     dflt_value:string|null,
69
 *     pk:string,
70
 *     size?: int,
71
 *     precision?: int,
72
 *     scale?: int,
73
 * }
74
 */
75
final class Schema extends AbstractPdoSchema
76
{
77
    /**
78
     * @var array Mapping from physical column types (keys) to abstract column types (values).
79
     *
80
     * @psalm-var array<array-key, string> $typeMap
81
     */
82
    private array $typeMap = [
83
        'tinyint' => self::TYPE_TINYINT,
84
        'bit' => self::TYPE_SMALLINT,
85
        'boolean' => self::TYPE_BOOLEAN,
86
        'bool' => self::TYPE_BOOLEAN,
87
        'smallint' => self::TYPE_SMALLINT,
88
        'mediumint' => self::TYPE_INTEGER,
89
        'int' => self::TYPE_INTEGER,
90
        'integer' => self::TYPE_INTEGER,
91
        'bigint' => self::TYPE_BIGINT,
92
        'float' => self::TYPE_FLOAT,
93
        'double' => self::TYPE_DOUBLE,
94
        'real' => self::TYPE_FLOAT,
95
        'decimal' => self::TYPE_DECIMAL,
96
        'numeric' => self::TYPE_DECIMAL,
97
        'tinytext' => self::TYPE_TEXT,
98
        'mediumtext' => self::TYPE_TEXT,
99
        'longtext' => self::TYPE_TEXT,
100
        'text' => self::TYPE_TEXT,
101
        'varchar' => self::TYPE_STRING,
102
        'string' => self::TYPE_STRING,
103
        'char' => self::TYPE_CHAR,
104
        'blob' => self::TYPE_BINARY,
105
        'datetime' => self::TYPE_DATETIME,
106
        'year' => self::TYPE_DATE,
107
        'date' => self::TYPE_DATE,
108
        'time' => self::TYPE_TIME,
109
        'timestamp' => self::TYPE_TIMESTAMP,
110
        'enum' => self::TYPE_STRING,
111 15
        'json' => self::TYPE_JSON,
112
    ];
113 15
114
    public function createColumn(string $type, array|int|string $length = null): ColumnInterface
115
    {
116
        return new Column($type, $length);
117
    }
118
119
    /**
120
     * Returns all table names in the database.
121
     *
122
     * This method should be overridden by child classes to support this feature because the default implementation
123
     * simply throws an exception.
124
     *
125
     * @param string $schema The schema of the tables.
126
     * Defaults to empty string, meaning the current or default schema.
127
     *
128
     * @throws Exception
129
     * @throws InvalidConfigException
130
     * @throws Throwable
131 10
     *
132
     * @return array All tables name in the database. The names have NO schema name prefix.
133 10
     */
134 10
    protected function findTableNames(string $schema = ''): array
135 10
    {
136 10
        return $this->db
137 10
           ->createCommand(
138
               "SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence' ORDER BY tbl_name"
139
           )
140
           ->queryColumn();
141
    }
142
143
    /**
144
     * Loads the metadata for the specified table.
145
     *
146
     * @param string $name The table name.
147
     *
148
     * @throws Exception
149
     * @throws InvalidArgumentException
150
     * @throws InvalidConfigException
151
     * @throws Throwable
152 160
     *
153
     * @return TableSchemaInterface|null DBMS-dependent table metadata, `null` if the table doesn't exist.
154 160
     */
155
    protected function loadTableSchema(string $name): TableSchemaInterface|null
156 160
    {
157 160
        $table = new TableSchema();
158
159 160
        $table->name($name);
160 116
        $table->fullName($name);
161
162 116
        if ($this->findColumns($table)) {
163
            $this->findConstraints($table);
164
165 67
            return $table;
166
        }
167
168
        return null;
169
    }
170
171
    /**
172
     * Loads a primary key for the given table.
173
     *
174
     * @param string $tableName The table name.
175
     *
176
     * @throws Exception
177
     * @throws InvalidArgumentException
178
     * @throws InvalidConfigException
179
     * @throws Throwable
180 54
     *
181
     * @return Constraint|null Primary key for the given table, `null` if the table has no primary key.
182 54
     */
183
    protected function loadTablePrimaryKey(string $tableName): Constraint|null
184 54
    {
185
        $tablePrimaryKey = $this->loadTableConstraints($tableName, self::PRIMARY_KEY);
186
187
        return $tablePrimaryKey instanceof Constraint ? $tablePrimaryKey : null;
188
    }
189
190
    /**
191
     * Loads all foreign keys for the given table.
192
     *
193
     * @param string $tableName The table name.
194
     *
195
     * @throws Exception
196
     * @throws InvalidConfigException
197
     * @throws Throwable
198 125
     *
199
     * @return ForeignKeyConstraint[] Foreign keys for the given table.
200 125
     */
201
    protected function loadTableForeignKeys(string $tableName): array
202 125
    {
203
        $result = [];
204 125
205 125
        $foreignKeysList = $this->getPragmaForeignKeyList($tableName);
206 125
        /** @psalm-var ForeignKeyInfo[] $foreignKeysList */
207
        $foreignKeysList = $this->normalizeRowKeyCase($foreignKeysList, true);
208
        $foreignKeysList = DbArrayHelper::index($foreignKeysList, null, ['table']);
209 125
        DbArrayHelper::multisort($foreignKeysList, 'seq');
210 11
211
        /** @psalm-var GroupedForeignKeyInfo $foreignKeysList */
212
        foreach ($foreignKeysList as $table => $foreignKeys) {
213
            $foreignKeysById = DbArrayHelper::index($foreignKeys, null, ['id']);
214
215
            /**
216 11
             * @psalm-var GroupedForeignKeyInfo $foreignKeysById
217 11
             * @psalm-var int $id
218 5
             */
219
            foreach ($foreignKeysById as $id => $foreignKey) {
220 5
                if ($foreignKey[0]['to'] === null) {
221
                    $primaryKey = $this->getTablePrimaryKey($table);
222 5
223 5
                    if ($primaryKey !== null) {
224
                        /** @psalm-var string $primaryKeyColumnName */
225
                        foreach ((array) $primaryKey->getColumnNames() as $i => $primaryKeyColumnName) {
226
                            $foreignKey[$i]['to'] = $primaryKeyColumnName;
227
                        }
228 11
                    }
229 11
                }
230 11
231 11
                $fk = (new ForeignKeyConstraint())
232 11
                    ->name((string) $id)
233 11
                    ->columnNames(array_column($foreignKey, 'from'))
234 11
                    ->foreignTableName($table)
235
                    ->foreignColumnNames(array_column($foreignKey, 'to'))
236 11
                    ->onDelete($foreignKey[0]['on_delete'])
237
                    ->onUpdate($foreignKey[0]['on_update']);
238
239
                $result[] = $fk;
240 125
            }
241
        }
242
243
        return $result;
244
    }
245
246
    /**
247
     * Loads all indexes for the given table.
248
     *
249
     * @param string $tableName The table name.
250
     *
251
     * @throws Exception
252
     * @throws InvalidArgumentException
253
     * @throws InvalidConfigException
254
     * @throws Throwable
255
     *
256
     * @return array Indexes for the given table.
257 14
     *
258
     * @psalm-return array|IndexConstraint[]
259 14
     */
260
    protected function loadTableIndexes(string $tableName): array
261 14
    {
262
        $tableIndexes = $this->loadTableConstraints($tableName, self::INDEXES);
263
264
        return is_array($tableIndexes) ? $tableIndexes : [];
265
    }
266
267
    /**
268
     * Loads all unique constraints for the given table.
269
     *
270
     * @param string $tableName The table name.
271
     *
272
     * @throws Exception
273
     * @throws InvalidArgumentException
274
     * @throws InvalidConfigException
275
     * @throws Throwable
276
     *
277
     * @return array Unique constraints for the given table.
278 15
     *
279
     * @psalm-return array|Constraint[]
280 15
     */
281
    protected function loadTableUniques(string $tableName): array
282 15
    {
283
        $tableUniques = $this->loadTableConstraints($tableName, self::UNIQUES);
284
285
        return is_array($tableUniques) ? $tableUniques : [];
286
    }
287
288
    /**
289
     * Loads all check constraints for the given table.
290
     *
291
     * @param string $tableName The table name.
292
     *
293
     * @throws Exception
294
     * @throws InvalidArgumentException
295
     * @throws InvalidConfigException
296
     * @throws Throwable
297 155
     *
298
     * @return CheckConstraint[] Check constraints for the given table.
299 155
     */
300 155
    protected function loadTableChecks(string $tableName): array
301 155
    {
302 155
        $sql = $this->db->createCommand(
303
            'SELECT `sql` FROM `sqlite_master` WHERE name = :tableName',
304 155
            [':tableName' => $tableName],
305
        )->queryScalar();
306
307 155
        $sql = ($sql === false || $sql === null) ? '' : (string) $sql;
308 155
309 155
        /** @psalm-var SqlToken[]|SqlToken[][]|SqlToken[][][] $code */
310
        $code = (new SqlTokenizer($sql))->tokenize();
311 155
        $pattern = (new SqlTokenizer('any CREATE any TABLE any()'))->tokenize();
312 114
        $result = [];
313 114
314 114
        if ($code[0] instanceof SqlToken && $code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) {
0 ignored issues
show
Bug introduced by
The method matches() does not exist on null. ( Ignorable by Annotation )

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

314
        if ($code[0] instanceof SqlToken && $code[0]->/** @scrutinizer ignore-call */ matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) {

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
315
            $offset = 0;
316
            $createTableToken = $code[0][(int) $lastMatchIndex - 1];
317 114
            $sqlTokenizerAnyCheck = new SqlTokenizer('any CHECK()');
318 114
319
            while (
320 23
                $createTableToken instanceof SqlToken &&
321 23
                $createTableToken->matches($sqlTokenizerAnyCheck->tokenize(), (int) $offset, $firstMatchIndex, $offset)
322 23
            ) {
323
                $name = null;
324
                $checkSql = (string) $createTableToken[(int) $offset - 1];
325 23
                $pattern = (new SqlTokenizer('CONSTRAINT any'))->tokenize();
326 23
327
                if (
328 1
                    isset($createTableToken[(int) $firstMatchIndex - 2])
329 1
                    && $createTableToken->matches($pattern, (int) $firstMatchIndex - 2)
330
                ) {
331
                    $sqlToken = $createTableToken[(int) $firstMatchIndex - 1];
332 23
                    $name = $sqlToken?->getContent();
333
                }
334
335
                $result[] = (new CheckConstraint())->name($name)->expression($checkSql);
336 155
            }
337
        }
338
339
        return $result;
340
    }
341
342
    /**
343
     * Loads all default value constraints for the given table.
344
     *
345
     * @param string $tableName The table name.
346
     *
347
     * @throws NotSupportedException
348 13
     *
349
     * @return array Default value constraints for the given table.
350 13
     */
351
    protected function loadTableDefaultValues(string $tableName): array
352
    {
353
        throw new NotSupportedException('SQLite does not support default value constraints.');
354
    }
355
356
    /**
357
     * Collects the table column metadata.
358
     *
359
     * @param TableSchemaInterface $table The table metadata.
360
     *
361
     * @throws Exception
362
     * @throws InvalidConfigException
363
     * @throws Throwable
364 160
     *
365
     * @return bool Whether the table exists in the database.
366
     */
367 160
    protected function findColumns(TableSchemaInterface $table): bool
368 160
    {
369
        /** @psalm-var ColumnInfo[] $columns */
370 160
        $columns = $this->getPragmaTableInfo($table->getName());
371 116
        $jsonColumns = $this->getJsonColumns($table);
372 18
373
        foreach ($columns as $info) {
374
            if (in_array($info['name'], $jsonColumns, true)) {
375 116
                $info['type'] = self::TYPE_JSON;
376 116
            }
377
378 116
            $column = $this->loadColumnSchema($info);
379 76
            $table->column($column->getName(), $column);
380
381
            if ($column->isPrimaryKey()) {
382
                $table->primaryKey($column->getName());
383 160
            }
384
        }
385 160
386 71
        $column = count($table->getPrimaryKey()) === 1 ? $table->getColumn($table->getPrimaryKey()[0]) : null;
387 71
388
        if ($column !== null && !strncasecmp($column->getDbType() ?? '', 'int', 3)) {
389
            $table->sequenceName('');
390 160
            $column->autoIncrement(true);
391
        }
392
393
        return !empty($columns);
394
    }
395
396
    /**
397
     * Collects the foreign key column details for the given table.
398
     *
399
     * @param TableSchemaInterface $table The table metadata.
400
     *
401
     * @throws Exception
402 116
     * @throws InvalidConfigException
403
     * @throws Throwable
404
     */
405 116
    protected function findConstraints(TableSchemaInterface $table): void
406
    {
407 116
        /** @psalm-var ForeignKeyConstraint[] $foreignKeysList */
408
        $foreignKeysList = $this->getTableForeignKeys($table->getName(), true);
409 6
410 6
        foreach ($foreignKeysList as $foreignKey) {
411
            /** @var array<string> $columnNames */
412 6
            $columnNames = (array) $foreignKey->getColumnNames();
413
            $columnNames = array_combine($columnNames, $foreignKey->getForeignColumnNames());
414
415 6
            $foreignReference = array_merge([$foreignKey->getForeignTableName()], $columnNames);
416
417
            /** @psalm-suppress InvalidCast */
418
            $table->foreignKey((string) $foreignKey->getName(), $foreignReference);
419
        }
420
    }
421
422
    /**
423
     * Returns all unique indexes for the given table.
424
     *
425
     * Each array element is of the following structure:
426
     *
427
     * ```php
428
     * [
429
     *     'IndexName1' => ['col1' [, ...]],
430
     *     'IndexName2' => ['col2' [, ...]],
431
     * ]
432
     * ```
433
     *
434
     * @param TableSchemaInterface $table The table metadata.
435
     *
436
     * @throws Exception
437
     * @throws InvalidConfigException
438
     * @throws Throwable
439 1
     *
440
     * @return array All unique indexes for the given table.
441
     */
442 1
    public function findUniqueIndexes(TableSchemaInterface $table): array
443 1
    {
444
        /** @psalm-var IndexListInfo[] $indexList */
445 1
        $indexList = $this->getPragmaIndexList($table->getName());
446 1
        $uniqueIndexes = [];
447
448 1
        foreach ($indexList as $index) {
449
            $indexName = $index['name'];
450 1
            /** @psalm-var IndexInfo[] $indexInfo */
451 1
            $indexInfo = $this->getPragmaIndexInfo($index['name']);
452 1
453 1
            if ($index['unique']) {
454
                $uniqueIndexes[$indexName] = [];
455
                foreach ($indexInfo as $row) {
456
                    $uniqueIndexes[$indexName][] = $row['name'];
457
                }
458 1
            }
459
        }
460
461
        return $uniqueIndexes;
462
    }
463
464 1
    /**
465
     * @throws NotSupportedException
466 1
     */
467
    public function getSchemaDefaultValues(string $schema = '', bool $refresh = false): array
468
    {
469
        throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.');
470
    }
471
472
    /**
473
     * Loads the column information into a {@see ColumnSchemaInterface} object.
474
     *
475
     * @param array $info The column information.
476
     *
477
     * @return ColumnSchemaInterface The column schema object.
478 116
     *
479
     * @psalm-param ColumnInfo $info
480 116
     */
481 116
    private function loadColumnSchema(array $info): ColumnSchemaInterface
482 116
    {
483 116
        $dbType = strtolower($info['type']);
484 116
        $type = $this->getColumnType($dbType, $info);
485 116
        $isUnsigned = str_contains($dbType, 'unsigned');
486
        /** @psalm-var ColumnInfo $info */
487 116
        $column = $this->createColumnSchema($type, $info['name'], unsigned: $isUnsigned);
0 ignored issues
show
Bug introduced by
The method createColumnSchema() does not exist on Yiisoft\Db\Sqlite\Schema. Did you maybe mean createColumn()? ( Ignorable by Annotation )

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

487
        /** @scrutinizer ignore-call */ 
488
        $column = $this->createColumnSchema($type, $info['name'], unsigned: $isUnsigned);

This check looks for calls to methods that do not seem to exist on a given type. It looks for the method on the type itself as well as in inherited classes or implemented interfaces.

This is most likely a typographical error or the method has been renamed.

Loading history...
488 116
        $column->size($info['size'] ?? null);
489
        $column->precision($info['precision'] ?? null);
490 116
        $column->scale($info['scale'] ?? null);
491 116
        $column->allowNull(!$info['notnull']);
492
        $column->primaryKey((bool) $info['pk']);
493
        $column->dbType($dbType);
494 116
        $column->phpType($this->getColumnPhpType($type));
0 ignored issues
show
Bug introduced by
$type of type string is incompatible with the type Yiisoft\Db\Schema\ColumnSchemaInterface expected by parameter $column of Yiisoft\Db\Schema\Abstra...ema::getColumnPhpType(). ( Ignorable by Annotation )

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

494
        $column->phpType($this->getColumnPhpType(/** @scrutinizer ignore-type */ $type));
Loading history...
495 100
        $column->defaultValue($this->normalizeDefaultValue($info['dflt_value'], $column));
496 100
497 100
        return $column;
498
    }
499 100
500 32
    /**
501
     * Get the abstract data type for the database data type.
502
     *
503 100
     * @param string $dbType The database data type
504 26
     * @param array $info Column information.
505 100
     *
506 26
     * @return string The abstract data type.
507 4
     */
508 26
    private function getColumnType(string $dbType, array &$info): string
509 4
    {
510
        preg_match('/^(\w*)(?:\(([^)]+)\))?/', $dbType, $matches);
511
        $dbType = strtolower($matches[1]);
512
513
        if (!empty($matches[2])) {
514
            $values = explode(',', $matches[2], 2);
515 116
            $info['size'] = (int) $values[0];
516 116
            $info['precision'] = (int) $values[0];
517
518 116
            if (isset($values[1])) {
519
                $info['scale'] = (int) $values[1];
520
            }
521
522
            if (($dbType === 'tinyint' || $dbType === 'bit') && $info['size'] === 1) {
523
                return self::TYPE_BOOLEAN;
524
            } elseif ($dbType === 'bit') {
525
                return match (true) {
526
                    $info['size'] === 32 => self::TYPE_INTEGER,
527
                    $info['size'] > 32 => self::TYPE_BIGINT,
528
                    default => self::TYPE_SMALLINT,
529 116
                };
530
            }
531 116
        }
532 112
533
        return $this->typeMap[$dbType] ?? self::TYPE_STRING;
534
    }
535 77
536 26
    /**
537
     * Converts column's default value according to {@see ColumnSchema::phpType} after retrieval from the database.
538
     *
539 77
     * @param string|null $defaultValue The default value retrieved from the database.
540
     * @param ColumnSchemaInterface $column The column schema object.
541 77
     *
542
     * @return mixed The normalized default value.
543
     */
544
    private function normalizeDefaultValue(string|null $defaultValue, ColumnSchemaInterface $column): mixed
545
    {
546
        if ($column->isPrimaryKey() || in_array($defaultValue, [null, '', 'null', 'NULL'], true)) {
547
            return null;
548
        }
549
550
        if (in_array($defaultValue, ['CURRENT_TIMESTAMP', 'CURRENT_DATE', 'CURRENT_TIME'], true)) {
551
            return new Expression($defaultValue);
0 ignored issues
show
Bug introduced by
It seems like $defaultValue can also be of type null; however, parameter $expression of Yiisoft\Db\Expression\Expression::__construct() does only seem to accept string, maybe add an additional type check? ( Ignorable by Annotation )

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

551
            return new Expression(/** @scrutinizer ignore-type */ $defaultValue);
Loading history...
552
        }
553
554
        $value = preg_replace('/^([\'"])(.*)\1$/s', '$2', $defaultValue);
555 54
556
        return $column->phpTypecast($value);
557 54
    }
558
559 54
    /**
560
     * Returns table columns info.
561 54
     *
562
     * @param string $tableName The table name.
563
     *
564
     * @throws Exception
565
     * @throws InvalidConfigException
566
     * @throws Throwable
567
     *
568
     * @return array The table columns info.
569
     */
570
    private function loadTableColumnsInfo(string $tableName): array
571
    {
572
        $tableColumns = $this->getPragmaTableInfo($tableName);
573
        /** @psalm-var ColumnInfo[] $tableColumns */
574
        $tableColumns = $this->normalizeRowKeyCase($tableColumns, true);
575
576 83
        return DbArrayHelper::index($tableColumns, 'cid');
577
    }
578 83
579
    /**
580 83
     * Loads multiple types of constraints and returns the specified ones.
581 83
     *
582 83
     * @param string $tableName The table name.
583 83
     * @param string $returnType Return type: (primaryKey, indexes, uniques).
584 83
     *
585 83
     * @throws Exception
586
     * @throws InvalidConfigException
587 83
     * @throws Throwable
588
     *
589 64
     * @psalm-return Constraint[]|IndexConstraint[]|Constraint|null
590
     */
591 64
    private function loadTableConstraints(string $tableName, string $returnType): Constraint|array|null
592 29
    {
593 29
        $indexList = $this->getPragmaIndexList($tableName);
594
        /** @psalm-var IndexListInfo[] $indexes */
595
        $indexes = $this->normalizeRowKeyCase($indexList, true);
596 64
        $result = [
597 59
            self::PRIMARY_KEY => null,
598 59
            self::INDEXES => [],
599 59
            self::UNIQUES => [],
600
        ];
601
602 64
        foreach ($indexes as $index) {
603 64
            /** @psalm-var IndexInfo[] $columns */
604 64
            $columns = $this->getPragmaIndexInfo($index['name']);
605 64
606 64
            if ($index['origin'] === 'pk') {
607
                $result[self::PRIMARY_KEY] = (new Constraint())
608
                    ->columnNames(DbArrayHelper::getColumn($columns, 'name'));
609 83
            }
610
611
            if ($index['origin'] === 'u') {
612
                $result[self::UNIQUES][] = (new Constraint())
613
                    ->name($index['name'])
614
                    ->columnNames(DbArrayHelper::getColumn($columns, 'name'));
615
            }
616
617 54
            $result[self::INDEXES][] = (new IndexConstraint())
618
                ->primary($index['origin'] === 'pk')
619 54
                ->unique((bool) $index['unique'])
620 54
                ->name($index['name'])
621 34
                ->columnNames(DbArrayHelper::getColumn($columns, 'name'));
622 34
        }
623
624
        if (!isset($result[self::PRIMARY_KEY])) {
625
            /**
626
             * Extra check for PK in case of `INTEGER PRIMARY KEY` with ROWID.
627 83
             *
628 83
             * @link https://www.sqlite.org/lang_createtable.html#primkeyconst
629
             *
630
             * @psalm-var ColumnInfo[] $tableColumns
631 83
             */
632
            $tableColumns = $this->loadTableColumnsInfo($tableName);
633
634
            foreach ($tableColumns as $tableColumn) {
635
                if ($tableColumn['pk'] > 0) {
636
                    $result[self::PRIMARY_KEY] = (new Constraint())->columnNames([$tableColumn['name']]);
637
                    break;
638
                }
639
            }
640
        }
641 116
642
        foreach ($result as $type => $data) {
643 116
            $this->setTableMetadata($tableName, $type, $data);
644
        }
645
646
        return $result[$returnType];
647
    }
648
649
    /**
650
     * @throws Exception
651 125
     * @throws InvalidConfigException
652
     * @throws Throwable
653 125
     */
654 125
    private function getPragmaForeignKeyList(string $tableName): array
655 125
    {
656
        return $this->db->createCommand(
657
            'PRAGMA FOREIGN_KEY_LIST(' . $this->db->getQuoter()->quoteSimpleTableName($tableName) . ')'
658
        )->queryAll();
659
    }
660
661
    /**
662
     * @throws Exception
663 65
     * @throws InvalidConfigException
664
     * @throws Throwable
665 65
     */
666 65
    private function getPragmaIndexInfo(string $name): array
667 65
    {
668
        $column = $this->db
669 65
            ->createCommand('PRAGMA INDEX_INFO(' . (string) $this->db->getQuoter()->quoteValue($name) . ')')
670 65
            ->queryAll();
671
        /** @psalm-var IndexInfo[] $column */
672 65
        $column = $this->normalizeRowKeyCase($column, true);
673
        DbArrayHelper::multisort($column, 'seqno');
674
675
        return $column;
676
    }
677
678
    /**
679
     * @throws Exception
680 84
     * @throws InvalidConfigException
681
     * @throws Throwable
682 84
     */
683 84
    private function getPragmaIndexList(string $tableName): array
684 84
    {
685
        return $this->db
686
            ->createCommand('PRAGMA INDEX_LIST(' . (string) $this->db->getQuoter()->quoteValue($tableName) . ')')
687
            ->queryAll();
688
    }
689
690
    /**
691
     * @throws Exception
692 169
     * @throws InvalidConfigException
693
     * @throws Throwable
694 169
     */
695 169
    private function getPragmaTableInfo(string $tableName): array
696 169
    {
697
        return $this->db->createCommand(
698
            'PRAGMA TABLE_INFO(' . $this->db->getQuoter()->quoteSimpleTableName($tableName) . ')'
699
        )->queryAll();
700
    }
701
702
    /**
703
     * @throws Exception
704 1
     * @throws InvalidConfigException
705
     * @throws Throwable
706
     */
707 1
    protected function findViewNames(string $schema = ''): array
708 1
    {
709
        /** @psalm-var string[][] $views */
710 1
        $views = $this->db->createCommand(
711 1
            <<<SQL
712
            SELECT name as view FROM sqlite_master WHERE type = 'view' AND name NOT LIKE 'sqlite_%'
713 1
            SQL,
714 1
        )->queryAll();
715
716
        foreach ($views as $key => $view) {
717 1
            $views[$key] = $view['view'];
718
        }
719
720
        return $views;
721
    }
722
723
    /**
724
     * Returns the cache key for the specified table name.
725
     *
726
     * @param string $name the table name.
727 227
     *
728
     * @return array The cache key.
729 227
     */
730
    protected function getCacheKey(string $name): array
731
    {
732
        return array_merge([self::class], $this->generateCacheKey(), [$this->getRawTableName($name)]);
733
    }
734
735
    /**
736
     * Returns the cache tag name.
737
     *
738
     * This allows {@see refresh()} to invalidate all cached table schemas.
739 211
     *
740
     * @return string The cache tag name.
741 211
     */
742
    protected function getCacheTag(): string
743
    {
744
        return md5(serialize(array_merge([self::class], $this->generateCacheKey())));
745
    }
746
747 160
    /**
748
     * @throws Throwable
749 160
     */
750
    private function getJsonColumns(TableSchemaInterface $table): array
751 160
    {
752 160
        $result = [];
753
        /** @psalm-var CheckConstraint[] $checks */
754 160
        $checks = $this->getTableChecks((string) $table->getFullName());
755 19
        $regexp = '/\bjson_valid\(\s*["`\[]?(.+?)["`\]]?\s*\)/i';
756 18
757 18
        foreach ($checks as $check) {
758
            if (preg_match_all($regexp, $check->getExpression(), $matches, PREG_SET_ORDER)) {
759
                foreach ($matches as $match) {
760
                    $result[] = $match[1];
761
                }
762 160
            }
763
        }
764
765
        return $result;
766
    }
767
}
768