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\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
|
|
|
* } |
71
|
|
|
*/ |
72
|
|
|
final class Schema extends AbstractPdoSchema |
73
|
|
|
{ |
74
|
|
|
/** |
75
|
|
|
* @var array Mapping from physical column types (keys) to abstract column types (values). |
76
|
|
|
* |
77
|
|
|
* @psalm-var array<array-key, string> $typeMap |
78
|
|
|
*/ |
79
|
|
|
private array $typeMap = [ |
80
|
|
|
'tinyint' => self::TYPE_TINYINT, |
81
|
|
|
'bit' => self::TYPE_SMALLINT, |
82
|
|
|
'boolean' => self::TYPE_BOOLEAN, |
83
|
|
|
'bool' => self::TYPE_BOOLEAN, |
84
|
|
|
'smallint' => self::TYPE_SMALLINT, |
85
|
|
|
'mediumint' => self::TYPE_INTEGER, |
86
|
|
|
'int' => self::TYPE_INTEGER, |
87
|
|
|
'integer' => self::TYPE_INTEGER, |
88
|
|
|
'bigint' => self::TYPE_BIGINT, |
89
|
|
|
'float' => self::TYPE_FLOAT, |
90
|
|
|
'double' => self::TYPE_DOUBLE, |
91
|
|
|
'real' => self::TYPE_FLOAT, |
92
|
|
|
'decimal' => self::TYPE_DECIMAL, |
93
|
|
|
'numeric' => self::TYPE_DECIMAL, |
94
|
|
|
'tinytext' => self::TYPE_TEXT, |
95
|
|
|
'mediumtext' => self::TYPE_TEXT, |
96
|
|
|
'longtext' => self::TYPE_TEXT, |
97
|
|
|
'text' => self::TYPE_TEXT, |
98
|
|
|
'varchar' => self::TYPE_STRING, |
99
|
|
|
'string' => self::TYPE_STRING, |
100
|
|
|
'char' => self::TYPE_CHAR, |
101
|
|
|
'blob' => self::TYPE_BINARY, |
102
|
|
|
'datetime' => self::TYPE_DATETIME, |
103
|
|
|
'year' => self::TYPE_DATE, |
104
|
|
|
'date' => self::TYPE_DATE, |
105
|
|
|
'time' => self::TYPE_TIME, |
106
|
|
|
'timestamp' => self::TYPE_TIMESTAMP, |
107
|
|
|
'enum' => self::TYPE_STRING, |
108
|
|
|
'json' => self::TYPE_JSON, |
109
|
|
|
]; |
110
|
|
|
|
111
|
15 |
|
public function createColumn(string $type, array|int|string $length = null): ColumnInterface |
112
|
|
|
{ |
113
|
15 |
|
return new Column($type, $length); |
114
|
|
|
} |
115
|
|
|
|
116
|
|
|
/** |
117
|
|
|
* Returns all table names in the database. |
118
|
|
|
* |
119
|
|
|
* This method should be overridden by child classes to support this feature because the default implementation |
120
|
|
|
* simply throws an exception. |
121
|
|
|
* |
122
|
|
|
* @param string $schema The schema of the tables. |
123
|
|
|
* Defaults to empty string, meaning the current or default schema. |
124
|
|
|
* |
125
|
|
|
* @throws Exception |
126
|
|
|
* @throws InvalidConfigException |
127
|
|
|
* @throws Throwable |
128
|
|
|
* |
129
|
|
|
* @return array All tables name in the database. The names have NO schema name prefix. |
130
|
|
|
*/ |
131
|
10 |
|
protected function findTableNames(string $schema = ''): array |
132
|
|
|
{ |
133
|
10 |
|
return $this->db |
134
|
10 |
|
->createCommand( |
135
|
10 |
|
"SELECT DISTINCT tbl_name FROM sqlite_master WHERE tbl_name<>'sqlite_sequence' ORDER BY tbl_name" |
136
|
10 |
|
) |
137
|
10 |
|
->queryColumn(); |
138
|
|
|
} |
139
|
|
|
|
140
|
|
|
/** |
141
|
|
|
* Loads the metadata for the specified table. |
142
|
|
|
* |
143
|
|
|
* @param string $name The table name. |
144
|
|
|
* |
145
|
|
|
* @throws Exception |
146
|
|
|
* @throws InvalidArgumentException |
147
|
|
|
* @throws InvalidConfigException |
148
|
|
|
* @throws Throwable |
149
|
|
|
* |
150
|
|
|
* @return TableSchemaInterface|null DBMS-dependent table metadata, `null` if the table doesn't exist. |
151
|
|
|
*/ |
152
|
160 |
|
protected function loadTableSchema(string $name): TableSchemaInterface|null |
153
|
|
|
{ |
154
|
160 |
|
$table = new TableSchema(); |
155
|
|
|
|
156
|
160 |
|
$table->name($name); |
157
|
160 |
|
$table->fullName($name); |
158
|
|
|
|
159
|
160 |
|
if ($this->findColumns($table)) { |
160
|
116 |
|
$this->findConstraints($table); |
161
|
|
|
|
162
|
116 |
|
return $table; |
163
|
|
|
} |
164
|
|
|
|
165
|
67 |
|
return null; |
166
|
|
|
} |
167
|
|
|
|
168
|
|
|
/** |
169
|
|
|
* Loads a primary key for the given table. |
170
|
|
|
* |
171
|
|
|
* @param string $tableName The table name. |
172
|
|
|
* |
173
|
|
|
* @throws Exception |
174
|
|
|
* @throws InvalidArgumentException |
175
|
|
|
* @throws InvalidConfigException |
176
|
|
|
* @throws Throwable |
177
|
|
|
* |
178
|
|
|
* @return Constraint|null Primary key for the given table, `null` if the table has no primary key. |
179
|
|
|
*/ |
180
|
54 |
|
protected function loadTablePrimaryKey(string $tableName): Constraint|null |
181
|
|
|
{ |
182
|
54 |
|
$tablePrimaryKey = $this->loadTableConstraints($tableName, self::PRIMARY_KEY); |
183
|
|
|
|
184
|
54 |
|
return $tablePrimaryKey instanceof Constraint ? $tablePrimaryKey : null; |
185
|
|
|
} |
186
|
|
|
|
187
|
|
|
/** |
188
|
|
|
* Loads all foreign keys for the given table. |
189
|
|
|
* |
190
|
|
|
* @param string $tableName The table name. |
191
|
|
|
* |
192
|
|
|
* @throws Exception |
193
|
|
|
* @throws InvalidConfigException |
194
|
|
|
* @throws Throwable |
195
|
|
|
* |
196
|
|
|
* @return ForeignKeyConstraint[] Foreign keys for the given table. |
197
|
|
|
*/ |
198
|
125 |
|
protected function loadTableForeignKeys(string $tableName): array |
199
|
|
|
{ |
200
|
125 |
|
$result = []; |
201
|
|
|
|
202
|
125 |
|
$foreignKeysList = $this->getPragmaForeignKeyList($tableName); |
203
|
|
|
/** @psalm-var ForeignKeyInfo[] $foreignKeysList */ |
204
|
125 |
|
$foreignKeysList = $this->normalizeRowKeyCase($foreignKeysList, true); |
205
|
125 |
|
$foreignKeysList = DbArrayHelper::index($foreignKeysList, null, ['table']); |
206
|
125 |
|
DbArrayHelper::multisort($foreignKeysList, 'seq'); |
207
|
|
|
|
208
|
|
|
/** @psalm-var GroupedForeignKeyInfo $foreignKeysList */ |
209
|
125 |
|
foreach ($foreignKeysList as $table => $foreignKeys) { |
210
|
11 |
|
$foreignKeysById = DbArrayHelper::index($foreignKeys, null, ['id']); |
211
|
|
|
|
212
|
|
|
/** |
213
|
|
|
* @psalm-var GroupedForeignKeyInfo $foreignKeysById |
214
|
|
|
* @psalm-var int $id |
215
|
|
|
*/ |
216
|
11 |
|
foreach ($foreignKeysById as $id => $foreignKey) { |
217
|
11 |
|
if ($foreignKey[0]['to'] === null) { |
218
|
5 |
|
$primaryKey = $this->getTablePrimaryKey($table); |
219
|
|
|
|
220
|
5 |
|
if ($primaryKey !== null) { |
221
|
|
|
/** @psalm-var string $primaryKeyColumnName */ |
222
|
5 |
|
foreach ((array) $primaryKey->getColumnNames() as $i => $primaryKeyColumnName) { |
223
|
5 |
|
$foreignKey[$i]['to'] = $primaryKeyColumnName; |
224
|
|
|
} |
225
|
|
|
} |
226
|
|
|
} |
227
|
|
|
|
228
|
11 |
|
$fk = (new ForeignKeyConstraint()) |
229
|
11 |
|
->name((string) $id) |
230
|
11 |
|
->columnNames(array_column($foreignKey, 'from')) |
231
|
11 |
|
->foreignTableName($table) |
232
|
11 |
|
->foreignColumnNames(array_column($foreignKey, 'to')) |
233
|
11 |
|
->onDelete($foreignKey[0]['on_delete']) |
234
|
11 |
|
->onUpdate($foreignKey[0]['on_update']); |
235
|
|
|
|
236
|
11 |
|
$result[] = $fk; |
237
|
|
|
} |
238
|
|
|
} |
239
|
|
|
|
240
|
125 |
|
return $result; |
241
|
|
|
} |
242
|
|
|
|
243
|
|
|
/** |
244
|
|
|
* Loads all indexes for the given table. |
245
|
|
|
* |
246
|
|
|
* @param string $tableName The table name. |
247
|
|
|
* |
248
|
|
|
* @throws Exception |
249
|
|
|
* @throws InvalidArgumentException |
250
|
|
|
* @throws InvalidConfigException |
251
|
|
|
* @throws Throwable |
252
|
|
|
* |
253
|
|
|
* @return array Indexes for the given table. |
254
|
|
|
* |
255
|
|
|
* @psalm-return array|IndexConstraint[] |
256
|
|
|
*/ |
257
|
14 |
|
protected function loadTableIndexes(string $tableName): array |
258
|
|
|
{ |
259
|
14 |
|
$tableIndexes = $this->loadTableConstraints($tableName, self::INDEXES); |
260
|
|
|
|
261
|
14 |
|
return is_array($tableIndexes) ? $tableIndexes : []; |
262
|
|
|
} |
263
|
|
|
|
264
|
|
|
/** |
265
|
|
|
* Loads all unique constraints for the given table. |
266
|
|
|
* |
267
|
|
|
* @param string $tableName The table name. |
268
|
|
|
* |
269
|
|
|
* @throws Exception |
270
|
|
|
* @throws InvalidArgumentException |
271
|
|
|
* @throws InvalidConfigException |
272
|
|
|
* @throws Throwable |
273
|
|
|
* |
274
|
|
|
* @return array Unique constraints for the given table. |
275
|
|
|
* |
276
|
|
|
* @psalm-return array|Constraint[] |
277
|
|
|
*/ |
278
|
15 |
|
protected function loadTableUniques(string $tableName): array |
279
|
|
|
{ |
280
|
15 |
|
$tableUniques = $this->loadTableConstraints($tableName, self::UNIQUES); |
281
|
|
|
|
282
|
15 |
|
return is_array($tableUniques) ? $tableUniques : []; |
283
|
|
|
} |
284
|
|
|
|
285
|
|
|
/** |
286
|
|
|
* Loads all check constraints for the given table. |
287
|
|
|
* |
288
|
|
|
* @param string $tableName The table name. |
289
|
|
|
* |
290
|
|
|
* @throws Exception |
291
|
|
|
* @throws InvalidArgumentException |
292
|
|
|
* @throws InvalidConfigException |
293
|
|
|
* @throws Throwable |
294
|
|
|
* |
295
|
|
|
* @return CheckConstraint[] Check constraints for the given table. |
296
|
|
|
*/ |
297
|
155 |
|
protected function loadTableChecks(string $tableName): array |
298
|
|
|
{ |
299
|
155 |
|
$sql = $this->db->createCommand( |
300
|
155 |
|
'SELECT `sql` FROM `sqlite_master` WHERE name = :tableName', |
301
|
155 |
|
[':tableName' => $tableName], |
302
|
155 |
|
)->queryScalar(); |
303
|
|
|
|
304
|
155 |
|
$sql = ($sql === false || $sql === null) ? '' : (string) $sql; |
305
|
|
|
|
306
|
|
|
/** @psalm-var SqlToken[]|SqlToken[][]|SqlToken[][][] $code */ |
307
|
155 |
|
$code = (new SqlTokenizer($sql))->tokenize(); |
308
|
155 |
|
$pattern = (new SqlTokenizer('any CREATE any TABLE any()'))->tokenize(); |
309
|
155 |
|
$result = []; |
310
|
|
|
|
311
|
155 |
|
if ($code[0] instanceof SqlToken && $code[0]->matches($pattern, 0, $firstMatchIndex, $lastMatchIndex)) { |
|
|
|
|
312
|
114 |
|
$offset = 0; |
313
|
114 |
|
$createTableToken = $code[0][(int) $lastMatchIndex - 1]; |
314
|
114 |
|
$sqlTokenizerAnyCheck = new SqlTokenizer('any CHECK()'); |
315
|
|
|
|
316
|
|
|
while ( |
317
|
114 |
|
$createTableToken instanceof SqlToken && |
318
|
114 |
|
$createTableToken->matches($sqlTokenizerAnyCheck->tokenize(), (int) $offset, $firstMatchIndex, $offset) |
319
|
|
|
) { |
320
|
23 |
|
$name = null; |
321
|
23 |
|
$checkSql = (string) $createTableToken[(int) $offset - 1]; |
322
|
23 |
|
$pattern = (new SqlTokenizer('CONSTRAINT any'))->tokenize(); |
323
|
|
|
|
324
|
|
|
if ( |
325
|
23 |
|
isset($createTableToken[(int) $firstMatchIndex - 2]) |
326
|
23 |
|
&& $createTableToken->matches($pattern, (int) $firstMatchIndex - 2) |
327
|
|
|
) { |
328
|
1 |
|
$sqlToken = $createTableToken[(int) $firstMatchIndex - 1]; |
329
|
1 |
|
$name = $sqlToken?->getContent(); |
330
|
|
|
} |
331
|
|
|
|
332
|
23 |
|
$result[] = (new CheckConstraint())->name($name)->expression($checkSql); |
333
|
|
|
} |
334
|
|
|
} |
335
|
|
|
|
336
|
155 |
|
return $result; |
337
|
|
|
} |
338
|
|
|
|
339
|
|
|
/** |
340
|
|
|
* Loads all default value constraints for the given table. |
341
|
|
|
* |
342
|
|
|
* @param string $tableName The table name. |
343
|
|
|
* |
344
|
|
|
* @throws NotSupportedException |
345
|
|
|
* |
346
|
|
|
* @return array Default value constraints for the given table. |
347
|
|
|
*/ |
348
|
13 |
|
protected function loadTableDefaultValues(string $tableName): array |
349
|
|
|
{ |
350
|
13 |
|
throw new NotSupportedException('SQLite does not support default value constraints.'); |
351
|
|
|
} |
352
|
|
|
|
353
|
|
|
/** |
354
|
|
|
* Collects the table column metadata. |
355
|
|
|
* |
356
|
|
|
* @param TableSchemaInterface $table The table metadata. |
357
|
|
|
* |
358
|
|
|
* @throws Exception |
359
|
|
|
* @throws InvalidConfigException |
360
|
|
|
* @throws Throwable |
361
|
|
|
* |
362
|
|
|
* @return bool Whether the table exists in the database. |
363
|
|
|
*/ |
364
|
160 |
|
protected function findColumns(TableSchemaInterface $table): bool |
365
|
|
|
{ |
366
|
|
|
/** @psalm-var ColumnInfo[] $columns */ |
367
|
160 |
|
$columns = $this->getPragmaTableInfo($table->getName()); |
368
|
160 |
|
$jsonColumns = $this->getJsonColumns($table); |
369
|
|
|
|
370
|
160 |
|
foreach ($columns as $info) { |
371
|
116 |
|
if (in_array($info['name'], $jsonColumns, true)) { |
372
|
18 |
|
$info['type'] = self::TYPE_JSON; |
373
|
|
|
} |
374
|
|
|
|
375
|
116 |
|
$column = $this->loadColumnSchema($info); |
376
|
116 |
|
$table->column($column->getName(), $column); |
377
|
|
|
|
378
|
116 |
|
if ($column->isPrimaryKey()) { |
379
|
76 |
|
$table->primaryKey($column->getName()); |
380
|
|
|
} |
381
|
|
|
} |
382
|
|
|
|
383
|
160 |
|
$column = count($table->getPrimaryKey()) === 1 ? $table->getColumn($table->getPrimaryKey()[0]) : null; |
384
|
|
|
|
385
|
160 |
|
if ($column !== null && !strncasecmp($column->getDbType() ?? '', 'int', 3)) { |
386
|
71 |
|
$table->sequenceName(''); |
387
|
71 |
|
$column->autoIncrement(true); |
388
|
|
|
} |
389
|
|
|
|
390
|
160 |
|
return !empty($columns); |
391
|
|
|
} |
392
|
|
|
|
393
|
|
|
/** |
394
|
|
|
* Collects the foreign key column details for the given table. |
395
|
|
|
* |
396
|
|
|
* @param TableSchemaInterface $table The table metadata. |
397
|
|
|
* |
398
|
|
|
* @throws Exception |
399
|
|
|
* @throws InvalidConfigException |
400
|
|
|
* @throws Throwable |
401
|
|
|
*/ |
402
|
116 |
|
protected function findConstraints(TableSchemaInterface $table): void |
403
|
|
|
{ |
404
|
|
|
/** @psalm-var ForeignKeyConstraint[] $foreignKeysList */ |
405
|
116 |
|
$foreignKeysList = $this->getTableForeignKeys($table->getName(), true); |
406
|
|
|
|
407
|
116 |
|
foreach ($foreignKeysList as $foreignKey) { |
408
|
|
|
/** @var array<string> $columnNames */ |
409
|
6 |
|
$columnNames = (array) $foreignKey->getColumnNames(); |
410
|
6 |
|
$columnNames = array_combine($columnNames, $foreignKey->getForeignColumnNames()); |
411
|
|
|
|
412
|
6 |
|
$foreignReference = array_merge([$foreignKey->getForeignTableName()], $columnNames); |
413
|
|
|
|
414
|
|
|
/** @psalm-suppress InvalidCast */ |
415
|
6 |
|
$table->foreignKey((string) $foreignKey->getName(), $foreignReference); |
416
|
|
|
} |
417
|
|
|
} |
418
|
|
|
|
419
|
|
|
/** |
420
|
|
|
* Returns all unique indexes for the given table. |
421
|
|
|
* |
422
|
|
|
* Each array element is of the following structure: |
423
|
|
|
* |
424
|
|
|
* ```php |
425
|
|
|
* [ |
426
|
|
|
* 'IndexName1' => ['col1' [, ...]], |
427
|
|
|
* 'IndexName2' => ['col2' [, ...]], |
428
|
|
|
* ] |
429
|
|
|
* ``` |
430
|
|
|
* |
431
|
|
|
* @param TableSchemaInterface $table The table metadata. |
432
|
|
|
* |
433
|
|
|
* @throws Exception |
434
|
|
|
* @throws InvalidConfigException |
435
|
|
|
* @throws Throwable |
436
|
|
|
* |
437
|
|
|
* @return array All unique indexes for the given table. |
438
|
|
|
*/ |
439
|
1 |
|
public function findUniqueIndexes(TableSchemaInterface $table): array |
440
|
|
|
{ |
441
|
|
|
/** @psalm-var IndexListInfo[] $indexList */ |
442
|
1 |
|
$indexList = $this->getPragmaIndexList($table->getName()); |
443
|
1 |
|
$uniqueIndexes = []; |
444
|
|
|
|
445
|
1 |
|
foreach ($indexList as $index) { |
446
|
1 |
|
$indexName = $index['name']; |
447
|
|
|
/** @psalm-var IndexInfo[] $indexInfo */ |
448
|
1 |
|
$indexInfo = $this->getPragmaIndexInfo($index['name']); |
449
|
|
|
|
450
|
1 |
|
if ($index['unique']) { |
451
|
1 |
|
$uniqueIndexes[$indexName] = []; |
452
|
1 |
|
foreach ($indexInfo as $row) { |
453
|
1 |
|
$uniqueIndexes[$indexName][] = $row['name']; |
454
|
|
|
} |
455
|
|
|
} |
456
|
|
|
} |
457
|
|
|
|
458
|
1 |
|
return $uniqueIndexes; |
459
|
|
|
} |
460
|
|
|
|
461
|
|
|
/** |
462
|
|
|
* @throws NotSupportedException |
463
|
|
|
*/ |
464
|
1 |
|
public function getSchemaDefaultValues(string $schema = '', bool $refresh = false): array |
465
|
|
|
{ |
466
|
1 |
|
throw new NotSupportedException(__METHOD__ . ' is not supported by SQLite.'); |
467
|
|
|
} |
468
|
|
|
|
469
|
|
|
/** |
470
|
|
|
* Loads the column information into a {@see ColumnSchemaInterface} object. |
471
|
|
|
* |
472
|
|
|
* @param array $info The column information. |
473
|
|
|
* |
474
|
|
|
* @return ColumnSchemaInterface The column schema object. |
475
|
|
|
* |
476
|
|
|
* @psalm-param array{cid:string, name:string, type:string, notnull:string, dflt_value:string|null, pk:string} $info |
477
|
|
|
*/ |
478
|
116 |
|
protected function loadColumnSchema(array $info): ColumnSchemaInterface |
479
|
|
|
{ |
480
|
116 |
|
$column = $this->createColumnSchema($info['name']); |
481
|
116 |
|
$column->allowNull(!$info['notnull']); |
482
|
116 |
|
$column->primaryKey($info['pk'] != '0'); |
483
|
116 |
|
$column->dbType(strtolower($info['type'])); |
484
|
116 |
|
$column->unsigned(str_contains($column->getDbType() ?? '', 'unsigned')); |
485
|
116 |
|
$column->type(self::TYPE_STRING); |
486
|
|
|
|
487
|
116 |
|
if (preg_match('/^(\w+)(?:\(([^)]+)\))?/', $column->getDbType() ?? '', $matches)) { |
488
|
116 |
|
$type = strtolower($matches[1]); |
489
|
|
|
|
490
|
116 |
|
if (isset($this->typeMap[$type])) { |
491
|
116 |
|
$column->type($this->typeMap[$type]); |
492
|
|
|
} |
493
|
|
|
|
494
|
116 |
|
if (!empty($matches[2])) { |
495
|
100 |
|
$values = explode(',', $matches[2]); |
496
|
100 |
|
$column->precision((int) $values[0]); |
497
|
100 |
|
$column->size((int) $values[0]); |
498
|
|
|
|
499
|
100 |
|
if (isset($values[1])) { |
500
|
32 |
|
$column->scale((int) $values[1]); |
501
|
|
|
} |
502
|
|
|
|
503
|
100 |
|
if (($type === 'tinyint' || $type === 'bit') && $column->getSize() === 1) { |
504
|
26 |
|
$column->type(self::TYPE_BOOLEAN); |
505
|
100 |
|
} elseif ($type === 'bit') { |
506
|
26 |
|
if ($column->getSize() > 32) { |
507
|
4 |
|
$column->type(self::TYPE_BIGINT); |
508
|
26 |
|
} elseif ($column->getSize() === 32) { |
509
|
4 |
|
$column->type(self::TYPE_INTEGER); |
510
|
|
|
} |
511
|
|
|
} |
512
|
|
|
} |
513
|
|
|
} |
514
|
|
|
|
515
|
116 |
|
$column->phpType($this->getColumnPhpType($column)); |
516
|
116 |
|
$column->defaultValue($this->normalizeDefaultValue($info['dflt_value'], $column)); |
517
|
|
|
|
518
|
116 |
|
return $column; |
519
|
|
|
} |
520
|
|
|
|
521
|
|
|
/** |
522
|
|
|
* Converts column's default value according to {@see ColumnSchema::phpType} after retrieval from the database. |
523
|
|
|
* |
524
|
|
|
* @param string|null $defaultValue The default value retrieved from the database. |
525
|
|
|
* @param ColumnSchemaInterface $column The column schema object. |
526
|
|
|
* |
527
|
|
|
* @return mixed The normalized default value. |
528
|
|
|
*/ |
529
|
116 |
|
private function normalizeDefaultValue(string|null $defaultValue, ColumnSchemaInterface $column): mixed |
530
|
|
|
{ |
531
|
116 |
|
if ($column->isPrimaryKey() || in_array($defaultValue, [null, '', 'null', 'NULL'], true)) { |
532
|
112 |
|
return null; |
533
|
|
|
} |
534
|
|
|
|
535
|
77 |
|
if (in_array($defaultValue, ['CURRENT_TIMESTAMP', 'CURRENT_DATE', 'CURRENT_TIME'], true)) { |
536
|
26 |
|
return new Expression($defaultValue); |
|
|
|
|
537
|
|
|
} |
538
|
|
|
|
539
|
77 |
|
$value = preg_replace('/^([\'"])(.*)\1$/s', '$2', $defaultValue); |
540
|
|
|
|
541
|
77 |
|
return $column->phpTypecast($value); |
542
|
|
|
} |
543
|
|
|
|
544
|
|
|
/** |
545
|
|
|
* Returns table columns info. |
546
|
|
|
* |
547
|
|
|
* @param string $tableName The table name. |
548
|
|
|
* |
549
|
|
|
* @throws Exception |
550
|
|
|
* @throws InvalidConfigException |
551
|
|
|
* @throws Throwable |
552
|
|
|
* |
553
|
|
|
* @return array The table columns info. |
554
|
|
|
*/ |
555
|
54 |
|
private function loadTableColumnsInfo(string $tableName): array |
556
|
|
|
{ |
557
|
54 |
|
$tableColumns = $this->getPragmaTableInfo($tableName); |
558
|
|
|
/** @psalm-var ColumnInfo[] $tableColumns */ |
559
|
54 |
|
$tableColumns = $this->normalizeRowKeyCase($tableColumns, true); |
560
|
|
|
|
561
|
54 |
|
return DbArrayHelper::index($tableColumns, 'cid'); |
562
|
|
|
} |
563
|
|
|
|
564
|
|
|
/** |
565
|
|
|
* Loads multiple types of constraints and returns the specified ones. |
566
|
|
|
* |
567
|
|
|
* @param string $tableName The table name. |
568
|
|
|
* @param string $returnType Return type: (primaryKey, indexes, uniques). |
569
|
|
|
* |
570
|
|
|
* @throws Exception |
571
|
|
|
* @throws InvalidConfigException |
572
|
|
|
* @throws Throwable |
573
|
|
|
* |
574
|
|
|
* @psalm-return Constraint[]|IndexConstraint[]|Constraint|null |
575
|
|
|
*/ |
576
|
83 |
|
private function loadTableConstraints(string $tableName, string $returnType): Constraint|array|null |
577
|
|
|
{ |
578
|
83 |
|
$indexList = $this->getPragmaIndexList($tableName); |
579
|
|
|
/** @psalm-var IndexListInfo[] $indexes */ |
580
|
83 |
|
$indexes = $this->normalizeRowKeyCase($indexList, true); |
581
|
83 |
|
$result = [ |
582
|
83 |
|
self::PRIMARY_KEY => null, |
583
|
83 |
|
self::INDEXES => [], |
584
|
83 |
|
self::UNIQUES => [], |
585
|
83 |
|
]; |
586
|
|
|
|
587
|
83 |
|
foreach ($indexes as $index) { |
588
|
|
|
/** @psalm-var IndexInfo[] $columns */ |
589
|
64 |
|
$columns = $this->getPragmaIndexInfo($index['name']); |
590
|
|
|
|
591
|
64 |
|
if ($index['origin'] === 'pk') { |
592
|
29 |
|
$result[self::PRIMARY_KEY] = (new Constraint()) |
593
|
29 |
|
->columnNames(DbArrayHelper::getColumn($columns, 'name')); |
594
|
|
|
} |
595
|
|
|
|
596
|
64 |
|
if ($index['origin'] === 'u') { |
597
|
59 |
|
$result[self::UNIQUES][] = (new Constraint()) |
598
|
59 |
|
->name($index['name']) |
599
|
59 |
|
->columnNames(DbArrayHelper::getColumn($columns, 'name')); |
600
|
|
|
} |
601
|
|
|
|
602
|
64 |
|
$result[self::INDEXES][] = (new IndexConstraint()) |
603
|
64 |
|
->primary($index['origin'] === 'pk') |
604
|
64 |
|
->unique((bool) $index['unique']) |
605
|
64 |
|
->name($index['name']) |
606
|
64 |
|
->columnNames(DbArrayHelper::getColumn($columns, 'name')); |
607
|
|
|
} |
608
|
|
|
|
609
|
83 |
|
if (!isset($result[self::PRIMARY_KEY])) { |
610
|
|
|
/** |
611
|
|
|
* Extra check for PK in case of `INTEGER PRIMARY KEY` with ROWID. |
612
|
|
|
* |
613
|
|
|
* @link https://www.sqlite.org/lang_createtable.html#primkeyconst |
614
|
|
|
* |
615
|
|
|
* @psalm-var ColumnInfo[] $tableColumns |
616
|
|
|
*/ |
617
|
54 |
|
$tableColumns = $this->loadTableColumnsInfo($tableName); |
618
|
|
|
|
619
|
54 |
|
foreach ($tableColumns as $tableColumn) { |
620
|
54 |
|
if ($tableColumn['pk'] > 0) { |
621
|
34 |
|
$result[self::PRIMARY_KEY] = (new Constraint())->columnNames([$tableColumn['name']]); |
622
|
34 |
|
break; |
623
|
|
|
} |
624
|
|
|
} |
625
|
|
|
} |
626
|
|
|
|
627
|
83 |
|
foreach ($result as $type => $data) { |
628
|
83 |
|
$this->setTableMetadata($tableName, $type, $data); |
629
|
|
|
} |
630
|
|
|
|
631
|
83 |
|
return $result[$returnType]; |
632
|
|
|
} |
633
|
|
|
|
634
|
|
|
/** |
635
|
|
|
* Creates a column schema for the database. |
636
|
|
|
* |
637
|
|
|
* This method may be overridden by child classes to create a DBMS-specific column schema. |
638
|
|
|
* |
639
|
|
|
* @param string $name Name of the column. |
640
|
|
|
*/ |
641
|
116 |
|
private function createColumnSchema(string $name): ColumnSchemaInterface |
642
|
|
|
{ |
643
|
116 |
|
return new ColumnSchema($name); |
644
|
|
|
} |
645
|
|
|
|
646
|
|
|
/** |
647
|
|
|
* @throws Exception |
648
|
|
|
* @throws InvalidConfigException |
649
|
|
|
* @throws Throwable |
650
|
|
|
*/ |
651
|
125 |
|
private function getPragmaForeignKeyList(string $tableName): array |
652
|
|
|
{ |
653
|
125 |
|
return $this->db->createCommand( |
654
|
125 |
|
'PRAGMA FOREIGN_KEY_LIST(' . $this->db->getQuoter()->quoteSimpleTableName($tableName) . ')' |
655
|
125 |
|
)->queryAll(); |
656
|
|
|
} |
657
|
|
|
|
658
|
|
|
/** |
659
|
|
|
* @throws Exception |
660
|
|
|
* @throws InvalidConfigException |
661
|
|
|
* @throws Throwable |
662
|
|
|
*/ |
663
|
65 |
|
private function getPragmaIndexInfo(string $name): array |
664
|
|
|
{ |
665
|
65 |
|
$column = $this->db |
666
|
65 |
|
->createCommand('PRAGMA INDEX_INFO(' . (string) $this->db->getQuoter()->quoteValue($name) . ')') |
667
|
65 |
|
->queryAll(); |
668
|
|
|
/** @psalm-var IndexInfo[] $column */ |
669
|
65 |
|
$column = $this->normalizeRowKeyCase($column, true); |
670
|
65 |
|
DbArrayHelper::multisort($column, 'seqno'); |
671
|
|
|
|
672
|
65 |
|
return $column; |
673
|
|
|
} |
674
|
|
|
|
675
|
|
|
/** |
676
|
|
|
* @throws Exception |
677
|
|
|
* @throws InvalidConfigException |
678
|
|
|
* @throws Throwable |
679
|
|
|
*/ |
680
|
84 |
|
private function getPragmaIndexList(string $tableName): array |
681
|
|
|
{ |
682
|
84 |
|
return $this->db |
683
|
84 |
|
->createCommand('PRAGMA INDEX_LIST(' . (string) $this->db->getQuoter()->quoteValue($tableName) . ')') |
684
|
84 |
|
->queryAll(); |
685
|
|
|
} |
686
|
|
|
|
687
|
|
|
/** |
688
|
|
|
* @throws Exception |
689
|
|
|
* @throws InvalidConfigException |
690
|
|
|
* @throws Throwable |
691
|
|
|
*/ |
692
|
169 |
|
private function getPragmaTableInfo(string $tableName): array |
693
|
|
|
{ |
694
|
169 |
|
return $this->db->createCommand( |
695
|
169 |
|
'PRAGMA TABLE_INFO(' . $this->db->getQuoter()->quoteSimpleTableName($tableName) . ')' |
696
|
169 |
|
)->queryAll(); |
697
|
|
|
} |
698
|
|
|
|
699
|
|
|
/** |
700
|
|
|
* @throws Exception |
701
|
|
|
* @throws InvalidConfigException |
702
|
|
|
* @throws Throwable |
703
|
|
|
*/ |
704
|
1 |
|
protected function findViewNames(string $schema = ''): array |
705
|
|
|
{ |
706
|
|
|
/** @psalm-var string[][] $views */ |
707
|
1 |
|
$views = $this->db->createCommand( |
708
|
1 |
|
<<<SQL |
709
|
|
|
SELECT name as view FROM sqlite_master WHERE type = 'view' AND name NOT LIKE 'sqlite_%' |
710
|
1 |
|
SQL, |
711
|
1 |
|
)->queryAll(); |
712
|
|
|
|
713
|
1 |
|
foreach ($views as $key => $view) { |
714
|
1 |
|
$views[$key] = $view['view']; |
715
|
|
|
} |
716
|
|
|
|
717
|
1 |
|
return $views; |
718
|
|
|
} |
719
|
|
|
|
720
|
|
|
/** |
721
|
|
|
* Returns the cache key for the specified table name. |
722
|
|
|
* |
723
|
|
|
* @param string $name the table name. |
724
|
|
|
* |
725
|
|
|
* @return array The cache key. |
726
|
|
|
*/ |
727
|
227 |
|
protected function getCacheKey(string $name): array |
728
|
|
|
{ |
729
|
227 |
|
return array_merge([self::class], $this->generateCacheKey(), [$this->getRawTableName($name)]); |
730
|
|
|
} |
731
|
|
|
|
732
|
|
|
/** |
733
|
|
|
* Returns the cache tag name. |
734
|
|
|
* |
735
|
|
|
* This allows {@see refresh()} to invalidate all cached table schemas. |
736
|
|
|
* |
737
|
|
|
* @return string The cache tag name. |
738
|
|
|
*/ |
739
|
211 |
|
protected function getCacheTag(): string |
740
|
|
|
{ |
741
|
211 |
|
return md5(serialize(array_merge([self::class], $this->generateCacheKey()))); |
742
|
|
|
} |
743
|
|
|
|
744
|
|
|
/** |
745
|
|
|
* @throws Throwable |
746
|
|
|
*/ |
747
|
160 |
|
private function getJsonColumns(TableSchemaInterface $table): array |
748
|
|
|
{ |
749
|
160 |
|
$result = []; |
750
|
|
|
/** @psalm-var CheckConstraint[] $checks */ |
751
|
160 |
|
$checks = $this->getTableChecks((string) $table->getFullName()); |
752
|
160 |
|
$regexp = '/\bjson_valid\(\s*["`\[]?(.+?)["`\]]?\s*\)/i'; |
753
|
|
|
|
754
|
160 |
|
foreach ($checks as $check) { |
755
|
19 |
|
if (preg_match_all($regexp, $check->getExpression(), $matches, PREG_SET_ORDER)) { |
756
|
18 |
|
foreach ($matches as $match) { |
757
|
18 |
|
$result[] = $match[1]; |
758
|
|
|
} |
759
|
|
|
} |
760
|
|
|
} |
761
|
|
|
|
762
|
160 |
|
return $result; |
763
|
|
|
} |
764
|
|
|
} |
765
|
|
|
|
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.