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; |
|
|
|
|
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)) { |
|
|
|
|
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); |
|
|
|
|
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)); |
|
|
|
|
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); |
|
|
|
|
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
|
|
|
|
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:For further information see https://scrutinizer-ci.com/docs/tools/php/php-scrutinizer/#list-dependency-paths