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