Test Failed
Pull Request — master (#273)
by Sergei
41:41 queued 26:59
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
            }
525
526
            if ($dbType === 'bit') {
527
                return match (true) {
528
                    $info['size'] === 32 => self::TYPE_INTEGER,
529 116
                    $info['size'] > 32 => self::TYPE_BIGINT,
530
                    default => self::TYPE_SMALLINT,
531 116
                };
532 112
            }
533
        }
534
535 77
        return $this->typeMap[$dbType] ?? self::TYPE_STRING;
536 26
    }
537
538
    /**
539 77
     * Converts column's default value according to {@see ColumnSchema::phpType} after retrieval from the database.
540
     *
541 77
     * @param string|null $defaultValue The default value retrieved from the database.
542
     * @param ColumnSchemaInterface $column The column schema object.
543
     *
544
     * @return mixed The normalized default value.
545
     */
546
    private function normalizeDefaultValue(string|null $defaultValue, ColumnSchemaInterface $column): mixed
547
    {
548
        if ($column->isPrimaryKey() || in_array($defaultValue, [null, '', 'null', 'NULL'], true)) {
549
            return null;
550
        }
551
552
        if (in_array($defaultValue, ['CURRENT_TIMESTAMP', 'CURRENT_DATE', 'CURRENT_TIME'], true)) {
553
            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

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