packages/ztd-query-sqlite/tests/Unit/Schema/SqliteSchemaParserTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Schema;
6
7use Generator;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\DataProvider;
10use PHPUnit\Framework\Attributes\UsesClass;
11use ZtdQuery\Platform\Sqlite\Schema\SqliteSchemaParser;
12use ZtdQuery\Platform\Sqlite\Sql\SqliteLexicalMasker;
13use ZtdQuery\Platform\Sqlite\Sql\SqliteParser;
14use ZtdQuery\Schema\ColumnTypeFamily;
15use ZtdQuery\Schema\Key\IdentityGenerationStrategy;
16
17#[CoversClass(SqliteSchemaParser::class)]
18#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentColumnParser::class)]
19#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentParser::class)]
20#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\ValueListParser::class)]
21#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Insert\InsertClauseParser::class)]
22#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\ExpressionSpan::class)]
23#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\IdentifierDecoder::class)]
24#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\LiteralMasker::class)]
25#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\OpaqueSqlSpan::class)]
26#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\QuotedSpan::class)]
27#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\TopLevelKeywordScanner::class)]
28#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\RelationSourceParser::class)]
29#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\StatementClassifier::class)]
30#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\StatementStructure::class)]
31#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Statement\TargetTableParser::class)]
32#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Update\UpdateClauseParser::class)]
33#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Create\ColumnDefinitionParser::class)]
34#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Create\TableBodyParser::class)]
35#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Create\TableDefinitionBuilder::class)]
36#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Create\VirtualTableParser::class)]
37#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Key\ForeignKeyEntryParser::class)]
38#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Key\ForeignKeyTokens::class)]
39#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\SqliteColumnTypeMapper::class)]
40#[UsesClass(\ZtdQuery\Platform\Sqlite\Schema\Key\SqliteForeignKeyDefinitionParser::class)]
41#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
42#[UsesClass(SqliteLexicalMasker::class)]
43#[UsesClass(SqliteParser::class)]
44#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Relation\SqliteSelectRelationParser::class)]
45final class SqliteSchemaParserTest extends \PHPUnit\Framework\TestCase
46{
47    public function testValidCreateTableReturnsNonNull(): void
48    {
49        $parser = new SqliteSchemaParser();
50        $result = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
51        self::assertNotNull($result);
52    }
53
54    public function testNonCreateTableReturnsNull(): void
55    {
56        $parser = new SqliteSchemaParser();
57        $result = $parser->parse('SELECT 1');
58        self::assertNull($result);
59    }
60
61    public function testPrimaryKeysSubsetOfColumns(): void
62    {
63        $parser = new SqliteSchemaParser();
64        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT, c REAL)';
65        $result = $parser->parse($sql);
66        self::assertNotNull($result);
67        self::assertSame([], array_diff($result->primaryKeys, $result->columns));
68    }
69
70    public function testNotNullSubsetOfColumns(): void
71    {
72        $parser = new SqliteSchemaParser();
73        $sql = 'CREATE TABLE t (a INTEGER NOT NULL, b TEXT, c REAL NOT NULL)';
74        $result = $parser->parse($sql);
75        self::assertNotNull($result);
76        self::assertSame([], array_diff($result->notNullColumns, $result->columns));
77    }
78
79    public function testColumnTypesKeysSubsetOfColumns(): void
80    {
81        $parser = new SqliteSchemaParser();
82        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
83        self::assertNotNull($definition);
84        self::assertSame([], array_diff(array_keys($definition->columnTypes), $definition->columns));
85    }
86
87    public function testUniqueConstraintColumnsSubsetOfColumns(): void
88    {
89        $parser = new SqliteSchemaParser();
90        $sql = 'CREATE TABLE t (a INTEGER, b TEXT UNIQUE, c REAL, UNIQUE(a, c))';
91        $result = $parser->parse($sql);
92        self::assertNotNull($result);
93        $allConstraintCols = array_unique(array_merge(...array_values($result->uniqueConstraints)));
94        self::assertSame([], array_diff($allConstraintCols, $result->columns));
95    }
96
97    public function testParsedDefinitionHasNonEmptyColumns(): void
98    {
99        $parser = new SqliteSchemaParser();
100        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
101        self::assertNotNull($definition);
102        self::assertNotEmpty($definition->columns);
103    }
104
105    public function testParsedColumnsMatchExpected(): void
106    {
107        $parser = new SqliteSchemaParser();
108        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
109        self::assertNotNull($definition);
110        self::assertSame(['id', 'name', 'email'], $definition->columns, 'Parsed column names must match expected columns in order');
111    }
112
113    public function testParsedPrimaryKeysMatchExpected(): void
114    {
115        $parser = new SqliteSchemaParser();
116        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
117        self::assertNotNull($definition);
118        self::assertSame(['id'], $definition->primaryKeys, 'Parsed primary keys must match expected primary keys');
119    }
120
121    public function testParsedNotNullColumnsMatchExpected(): void
122    {
123        $parser = new SqliteSchemaParser();
124        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
125        self::assertNotNull($definition);
126        self::assertSame([], array_diff(['id', 'name'], $definition->notNullColumns));
127    }
128
129    public function testColumnCountMatchesExpected(): void
130    {
131        $parser = new SqliteSchemaParser();
132        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
133        self::assertNotNull($definition);
134        self::assertCount(count(['id', 'name', 'email']), $definition->columns, 'Column count must match expected');
135    }
136
137    public function testMalformedInputReturnsNull(): void
138    {
139        $parser = new SqliteSchemaParser();
140        $result = $parser->parse('NOT VALID SQL AT ALL %%%');
141        self::assertNull($result);
142    }
143
144    public function testTypedColumnsKeysSubsetOfColumns(): void
145    {
146        $parser = new SqliteSchemaParser();
147        $definition = $parser->parse('CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, name TEXT NOT NULL, email TEXT, UNIQUE (email))');
148        self::assertNotNull($definition);
149        self::assertSame([], array_diff(array_keys($definition->typedColumns), $definition->columns));
150    }
151
152    public function testParseSimpleCreateTable(): void
153    {
154        $parser = new SqliteSchemaParser();
155        $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT)';
156        $result = $parser->parse($sql);
157        self::assertNotNull($result);
158        self::assertSame(['id', 'name', 'email'], $result->columns);
159        self::assertSame(['id'], $result->primaryKeys);
160        self::assertContains('id', $result->notNullColumns);
161        self::assertContains('name', $result->notNullColumns);
162        self::assertSame('INTEGER', $result->columnTypes['id']);
163        self::assertSame('TEXT', $result->columnTypes['name']);
164        self::assertSame('TEXT', $result->columnTypes['email']);
165    }
166
167    public function testParseWithCompositePrimaryKey(): void
168    {
169        $parser = new SqliteSchemaParser();
170        $sql = 'CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER, PRIMARY KEY (order_id, product_id))';
171        $result = $parser->parse($sql);
172        self::assertNotNull($result);
173        self::assertSame(['order_id', 'product_id', 'quantity'], $result->columns);
174        self::assertSame(['order_id', 'product_id'], $result->primaryKeys);
175    }
176
177    public function testParseWithUniqueConstraint(): void
178    {
179        $parser = new SqliteSchemaParser();
180        $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT UNIQUE, name TEXT)';
181        $result = $parser->parse($sql);
182        self::assertNotNull($result);
183        self::assertArrayHasKey('email_UNIQUE', $result->uniqueConstraints);
184        self::assertSame(['email'], $result->uniqueConstraints['email_UNIQUE']);
185    }
186
187    public function testParseWithTableLevelUniqueConstraint(): void
188    {
189        $parser = new SqliteSchemaParser();
190        $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT, UNIQUE (first_name, last_name))';
191        $result = $parser->parse($sql);
192        self::assertNotNull($result);
193        self::assertNotEmpty($result->uniqueConstraints);
194        $uniqueKeys = array_values($result->uniqueConstraints);
195        self::assertSame(['first_name', 'last_name'], $uniqueKeys[0]);
196    }
197
198    public function testParseWithIfNotExists(): void
199    {
200        $parser = new SqliteSchemaParser();
201        $sql = 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)';
202        $result = $parser->parse($sql);
203        self::assertNotNull($result);
204        self::assertSame(['id', 'name'], $result->columns);
205    }
206
207    public function testParseFts5VirtualTableColumnsAndOptions(): void
208    {
209        $parser = new SqliteSchemaParser();
210        $result = $parser->parse('CREATE VIRTUAL TABLE IF NOT EXISTS main.fts_articles USING fts5(' . 'title, "article body" UNINDEXED, ' . "tokenize='porter unicode61', prefix='2 3')");
211        self::assertNotNull($result);
212        self::assertSame(['title', 'article body'], $result->columns);
213        self::assertSame(['title' => 'TEXT', 'article body' => 'TEXT'], $result->columnTypes);
214        self::assertSame(ColumnTypeFamily::TEXT, $result->typedColumns['title']->family);
215    }
216
217    public function testParseFts5OptionsBeforeColumns(): void
218    {
219        $result = (new SqliteSchemaParser())->parse("CREATE VIRTUAL TABLE articles USING fts5(tokenize='porter', content='source', " . 'title, body UNINDEXED);');
220        self::assertNotNull($result);
221        self::assertSame(['title', 'body'], $result->columns);
222    }
223
224    public function testParseFts5WithWhitespaceBeforeTheTerminator(): void
225    {
226        $result = (new SqliteSchemaParser())->parse('CREATE VIRTUAL TABLE articles USING fts5(title) ;');
227        self::assertNotNull($result);
228        self::assertSame(['title'], $result->columns);
229    }
230
231    #[DataProvider('providerInvalidFts5Declaration')]
232    public function testRejectsInvalidFts5Declaration(string $sql): void
233    {
234        self::assertNull((new SqliteSchemaParser())->parse($sql));
235    }
236    /**
237     * @return iterable<string, array{string}>
238     */
239    public static function providerInvalidFts5Declaration(): iterable
240    {
241        yield 'wrong create keyword' => ['WRONG VIRTUAL TABLE articles USING fts5(title)'];
242        yield 'create only' => ['CREATE'];
243        yield 'missing table keyword' => ['CREATE VIRTUAL'];
244        yield 'missing virtual' => ['CREATE TABLE articles USING fts5(title)'];
245        yield 'wrong virtual keyword' => ['CREATE WRONG TABLE articles USING fts5(title)'];
246        yield 'missing table' => ['CREATE VIRTUAL INDEX articles USING fts5(title)'];
247        yield 'missing using' => ['CREATE VIRTUAL TABLE articles'];
248        yield 'missing module' => ['CREATE VIRTUAL TABLE articles USING'];
249        yield 'wrong module' => ['CREATE VIRTUAL TABLE articles USING fts4(title)'];
250        yield 'missing opening parenthesis' => ['CREATE VIRTUAL TABLE articles USING fts5 title'];
251        yield 'missing closing parenthesis' => ['CREATE VIRTUAL TABLE articles USING fts5(title'];
252        yield 'empty body' => ['CREATE VIRTUAL TABLE articles USING fts5()'];
253        yield 'options only' => ["CREATE VIRTUAL TABLE articles USING fts5(tokenize='porter')"];
254        yield 'typed column' => ['CREATE VIRTUAL TABLE articles USING fts5(title TEXT)'];
255        yield 'extra column modifier' => ['CREATE VIRTUAL TABLE articles USING fts5(title UNINDEXED extra)'];
256        yield 'non-identifier column' => ['CREATE VIRTUAL TABLE articles USING fts5(42)'];
257        yield 'unexpected suffix' => ['CREATE VIRTUAL TABLE articles USING fts5(title) WITHOUT ROWID'];
258    }
259
260    public function testParseWithQuotedIdentifiers(): void
261    {
262        $parser = new SqliteSchemaParser();
263        $sql = 'CREATE TABLE "my table" ("my id" INTEGER PRIMARY KEY, "my name" TEXT)';
264        $result = $parser->parse($sql);
265        self::assertNotNull($result);
266        self::assertSame(['my id', 'my name'], $result->columns);
267    }
268
269    public function testParseWithTypes(): void
270    {
271        $parser = new SqliteSchemaParser();
272        $sql = 'CREATE TABLE t (a INTEGER, b REAL, c TEXT, d BLOB, e NUMERIC, f VARCHAR(255), g DECIMAL(10,2))';
273        $result = $parser->parse($sql);
274        self::assertNotNull($result);
275        self::assertSame('INTEGER', $result->columnTypes['a']);
276        self::assertSame('REAL', $result->columnTypes['b']);
277        self::assertSame('TEXT', $result->columnTypes['c']);
278        self::assertSame('BLOB', $result->columnTypes['d']);
279        self::assertSame('NUMERIC', $result->columnTypes['e']);
280        self::assertSame('VARCHAR(255)', $result->columnTypes['f']);
281        self::assertSame('DECIMAL(10,2)', $result->columnTypes['g']);
282    }
283
284    public function testParseWithDefaultValues(): void
285    {
286        $parser = new SqliteSchemaParser();
287        $sql = "CREATE TABLE t (id INTEGER PRIMARY KEY, status TEXT DEFAULT 'active', created_at TEXT DEFAULT CURRENT_TIMESTAMP)";
288        $result = $parser->parse($sql);
289        self::assertNotNull($result);
290        self::assertSame(['id', 'status', 'created_at'], $result->columns);
291        self::assertSame(['status' => "'active'", 'created_at' => 'CURRENT_TIMESTAMP'], $result->columnDefaults);
292    }
293
294    public function testParsePreservesParenthesizedDefaultExpressionBeforeConstraint(): void
295    {
296        $result = (new SqliteSchemaParser())->parse("CREATE TABLE t (label TEXT DEFAULT (printf('%s,%s', 'a', 'b')) NOT NULL, enabled INTEGER DEFAULT 1)");
297        self::assertNotNull($result);
298        self::assertSame(['label' => "(printf('%s,%s', 'a', 'b'))", 'enabled' => '1'], $result->columnDefaults);
299    }
300
301    public function testParseStopsDefaultAtInlinePrimaryKeyConstraint(): void
302    {
303        $result = (new SqliteSchemaParser())->parse('CREATE TABLE t (id INTEGER DEFAULT 7 PRIMARY KEY)');
304        self::assertNotNull($result);
305        self::assertSame(['id' => '7'], $result->columnDefaults);
306        self::assertSame(['id'], $result->primaryKeys);
307    }
308
309    public function testParseIntegerPrimaryKeyAsMaxValueIdentity(): void
310    {
311        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');
312        self::assertNotNull($definition);
313        self::assertSame(['id' => IdentityGenerationStrategy::MaxValue], $definition->identityStrategies);
314    }
315
316    public function testWithoutRowidPrimaryKeyIsNotIdentity(): void
317    {
318        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT) WITHOUT ROWID');
319        self::assertNotNull($definition);
320        self::assertSame([], $definition->identityStrategies);
321    }
322
323    public function testStrictTableRetainsColumnsTypesAndRowidIdentity(): void
324    {
325        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE measurements (id INTEGER PRIMARY KEY, sensor TEXT NOT NULL, reading REAL NOT NULL) STRICT');
326        self::assertNotNull($definition);
327        self::assertSame(['id', 'sensor', 'reading'], $definition->columns);
328        self::assertSame(['id' => 'INTEGER', 'sensor' => 'TEXT', 'reading' => 'REAL'], $definition->columnTypes);
329        self::assertSame(['id' => IdentityGenerationStrategy::MaxValue], $definition->identityStrategies);
330    }
331
332    public function testStrictAndWithoutRowidOptionsAreAcceptedInEitherOrder(): void
333    {
334        $strictLast = (new SqliteSchemaParser())->parse('CREATE TABLE events (id INTEGER PRIMARY KEY, payload BLOB) WITHOUT ROWID, STRICT;');
335        $withoutLast = (new SqliteSchemaParser())->parse('CREATE TABLE events (id INTEGER PRIMARY KEY, payload BLOB) STRICT, WITHOUT ROWID');
336        self::assertNotNull($strictLast);
337        self::assertNotNull($withoutLast);
338        self::assertSame([], $strictLast->identityStrategies);
339        self::assertSame([], $withoutLast->identityStrategies);
340    }
341
342    public function testTempStrictTableIsAcceptedWithTriviaBeforeOption(): void
343    {
344        $definition = (new SqliteSchemaParser())->parse("CREATE TEMP TABLE events (id INTEGER PRIMARY KEY, payload BLOB) /* option */ STRICT\n");
345        self::assertNotNull($definition);
346        self::assertSame(['id', 'payload'], $definition->columns);
347    }
348
349    #[DataProvider('providerInvalidTableOptionSuffixes')]
350    public function testRejectsInvalidTableOptionSuffix(string $suffix): void
351    {
352        self::assertNull((new SqliteSchemaParser())->parse('CREATE TABLE events (id INTEGER PRIMARY KEY) ' . $suffix));
353    }
354    /**
355     * @return Generator<string, array{string}>
356     */
357    public static function providerInvalidTableOptionSuffixes(): Generator
358    {
359        yield 'unknown option' => ['COMPRESS'];
360        yield 'missing rowid' => ['WITHOUT'];
361        yield 'missing comma' => ['STRICT WITHOUT ROWID'];
362        yield 'symbol instead of comma' => ['STRICT + WITHOUT ROWID'];
363        yield 'trailing comma' => ['STRICT,'];
364        yield 'duplicate strict' => ['STRICT, STRICT'];
365        yield 'duplicate without rowid' => ['WITHOUT ROWID, WITHOUT ROWID'];
366        yield 'extra closing parenthesis' => [') STRICT'];
367        yield 'statement after semicolon' => ['STRICT; SELECT 1'];
368    }
369
370    public function testLowercaseWithoutRowidIsNotIdentity(): void
371    {
372        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT) without rowid');
373        self::assertNotNull($definition);
374        self::assertSame([], $definition->identityStrategies);
375    }
376
377    public function testWithoutRowidTextInsideDefaultDoesNotDisableIdentity(): void
378    {
379        $definition = (new SqliteSchemaParser())->parse("CREATE TABLE users (id INTEGER PRIMARY KEY, note TEXT DEFAULT 'WITHOUT ROWID')");
380        self::assertNotNull($definition);
381        self::assertSame(['id' => IdentityGenerationStrategy::MaxValue], $definition->identityStrategies);
382    }
383
384    public function testNestedWithoutRowidTokensDoNotDisableIdentity(): void
385    {
386        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id INTEGER PRIMARY KEY, value TEXT CHECK (WITHOUT ROWID))');
387        self::assertNotNull($definition);
388        self::assertSame(['id' => IdentityGenerationStrategy::MaxValue], $definition->identityStrategies);
389    }
390
391    public function testNestedWithoutRowidDoesNotHideRealWithoutRowidClause(): void
392    {
393        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id INTEGER PRIMARY KEY, value TEXT CHECK (WITHOUT ROWID)) WITHOUT ROWID');
394        self::assertNotNull($definition);
395        self::assertSame([], $definition->identityStrategies);
396    }
397
398    public function testNonIntegerPrimaryKeyIsNotIdentity(): void
399    {
400        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id TEXT PRIMARY KEY)');
401        self::assertNotNull($definition);
402        self::assertSame([], $definition->identityStrategies);
403    }
404
405    public function testDuplicatePrimaryKeySourcesStillProduceOneIdentity(): void
406    {
407        $definition = (new SqliteSchemaParser())->parse('CREATE TABLE users (id INTEGER PRIMARY KEY, PRIMARY KEY (id))');
408        self::assertNotNull($definition);
409        self::assertSame(['id'], $definition->primaryKeys);
410        self::assertSame(['id' => IdentityGenerationStrategy::MaxValue], $definition->identityStrategies);
411    }
412
413    public function testParseWithForeignKey(): void
414    {
415        $parser = new SqliteSchemaParser();
416        $sql = 'CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), total REAL)';
417        $result = $parser->parse($sql);
418        self::assertNotNull($result);
419        self::assertSame(['id', 'user_id', 'total'], $result->columns);
420    }
421
422    public function testParseWithTableLevelForeignKey(): void
423    {
424        $parser = new SqliteSchemaParser();
425        $sql = 'CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users(id))';
426        $result = $parser->parse($sql);
427        self::assertNotNull($result);
428        self::assertSame(['id', 'user_id'], $result->columns);
429    }
430
431    public function testParseNonCreateTableReturnsNull(): void
432    {
433        $parser = new SqliteSchemaParser();
434        self::assertNull($parser->parse('SELECT * FROM users'));
435        self::assertNull($parser->parse('INSERT INTO users (name) VALUES ("Alice")'));
436        self::assertNull($parser->parse('DROP TABLE users'));
437    }
438
439    public function testParseMalformedReturnsNull(): void
440    {
441        $parser = new SqliteSchemaParser();
442        self::assertNull($parser->parse('CREATE TABLE'));
443        self::assertNull($parser->parse(''));
444        self::assertNull($parser->parse('garbage text'));
445    }
446
447    public function testParseWithAutoincrement(): void
448    {
449        $parser = new SqliteSchemaParser();
450        $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL)';
451        $result = $parser->parse($sql);
452        self::assertNotNull($result);
453        self::assertSame(['id', 'name'], $result->columns);
454        self::assertSame(['id'], $result->primaryKeys);
455    }
456
457    public function testParseWithCheckConstraint(): void
458    {
459        $parser = new SqliteSchemaParser();
460        $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, age INTEGER CHECK(age > 0), name TEXT)';
461        $result = $parser->parse($sql);
462        self::assertNotNull($result);
463        self::assertSame(['id', 'age', 'name'], $result->columns);
464    }
465
466    public function testColumnTypeKeysSubsetOfColumns(): void
467    {
468        $parser = new SqliteSchemaParser();
469        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, c REAL)';
470        $result = $parser->parse($sql);
471        self::assertNotNull($result);
472        self::assertSame([], array_diff(array_keys($result->columnTypes), $result->columns));
473    }
474
475    public function testParseTypedColumnsMapping(): void
476    {
477        $parser = new SqliteSchemaParser();
478        $sql = 'CREATE TABLE t (a INT, b TINYINT, c SMALLINT, d MEDIUMINT, e BIGINT, f INT2, g INT8)';
479        $result = $parser->parse($sql);
480        self::assertNotNull($result);
481        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['a']->family);
482        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['b']->family);
483        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['c']->family);
484        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['d']->family);
485        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['e']->family);
486        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['f']->family);
487        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['g']->family);
488    }
489
490    public function testParseTypedColumnsReal(): void
491    {
492        $parser = new SqliteSchemaParser();
493        $sql = 'CREATE TABLE t (a REAL, b DOUBLE, c FLOAT)';
494        $result = $parser->parse($sql);
495        self::assertNotNull($result);
496        self::assertSame(ColumnTypeFamily::FLOAT, $result->typedColumns['a']->family);
497        self::assertSame(ColumnTypeFamily::FLOAT, $result->typedColumns['b']->family);
498        self::assertSame(ColumnTypeFamily::FLOAT, $result->typedColumns['c']->family);
499    }
500
501    public function testParseTypedColumnsDoublePrecision(): void
502    {
503        $parser = new SqliteSchemaParser();
504        $sql = 'CREATE TABLE t (a DOUBLE PRECISION)';
505        $result = $parser->parse($sql);
506        self::assertNotNull($result);
507        self::assertSame(ColumnTypeFamily::FLOAT, $result->typedColumns['a']->family);
508    }
509
510    public function testParseTypedColumnsDecimal(): void
511    {
512        $parser = new SqliteSchemaParser();
513        $sql = 'CREATE TABLE t (a DECIMAL, b NUMERIC)';
514        $result = $parser->parse($sql);
515        self::assertNotNull($result);
516        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['a']->family);
517        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['b']->family);
518    }
519
520    public function testParseTypedColumnsBoolean(): void
521    {
522        $parser = new SqliteSchemaParser();
523        $sql = 'CREATE TABLE t (a BOOLEAN, b BOOL)';
524        $result = $parser->parse($sql);
525        self::assertNotNull($result);
526        self::assertSame(ColumnTypeFamily::BOOLEAN, $result->typedColumns['a']->family);
527        self::assertSame(ColumnTypeFamily::BOOLEAN, $result->typedColumns['b']->family);
528    }
529
530    public function testParseTypedColumnsText(): void
531    {
532        $parser = new SqliteSchemaParser();
533        $sql = 'CREATE TABLE t (a TEXT, b CLOB)';
534        $result = $parser->parse($sql);
535        self::assertNotNull($result);
536        self::assertSame(ColumnTypeFamily::TEXT, $result->typedColumns['a']->family);
537        self::assertSame(ColumnTypeFamily::TEXT, $result->typedColumns['b']->family);
538    }
539
540    public function testParseTypedColumnsString(): void
541    {
542        $parser = new SqliteSchemaParser();
543        $sql = 'CREATE TABLE t (a CHAR, b CHARACTER, c VARCHAR, d NCHAR, e NVARCHAR)';
544        $result = $parser->parse($sql);
545        self::assertNotNull($result);
546        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['a']->family);
547        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['b']->family);
548        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['c']->family);
549        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['d']->family);
550        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['e']->family);
551    }
552
553    public function testParseTypedColumnsVaryingCharacter(): void
554    {
555        $parser = new SqliteSchemaParser();
556        $sql = 'CREATE TABLE t (a VARYING CHARACTER)';
557        $result = $parser->parse($sql);
558        self::assertNotNull($result);
559        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['a']->family);
560    }
561
562    public function testParseTypedColumnsNativeCharacter(): void
563    {
564        $parser = new SqliteSchemaParser();
565        $sql = 'CREATE TABLE t (a NATIVE CHARACTER)';
566        $result = $parser->parse($sql);
567        self::assertNotNull($result);
568        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['a']->family);
569    }
570
571    public function testParseTypedColumnsBlob(): void
572    {
573        $parser = new SqliteSchemaParser();
574        $sql = 'CREATE TABLE t (a BLOB)';
575        $result = $parser->parse($sql);
576        self::assertNotNull($result);
577        self::assertSame(ColumnTypeFamily::BINARY, $result->typedColumns['a']->family);
578    }
579
580    public function testParseTypedColumnsDateTypes(): void
581    {
582        $parser = new SqliteSchemaParser();
583        $sql = 'CREATE TABLE t (a DATE, b TIME, c DATETIME, d TIMESTAMP)';
584        $result = $parser->parse($sql);
585        self::assertNotNull($result);
586        self::assertSame(ColumnTypeFamily::DATE, $result->typedColumns['a']->family);
587        self::assertSame(ColumnTypeFamily::TIME, $result->typedColumns['b']->family);
588        self::assertSame(ColumnTypeFamily::DATETIME, $result->typedColumns['c']->family);
589        self::assertSame(ColumnTypeFamily::TIMESTAMP, $result->typedColumns['d']->family);
590    }
591
592    public function testParseTypedColumnsJson(): void
593    {
594        $parser = new SqliteSchemaParser();
595        $sql = 'CREATE TABLE t (a JSON)';
596        $result = $parser->parse($sql);
597        self::assertNotNull($result);
598        self::assertSame(ColumnTypeFamily::JSON, $result->typedColumns['a']->family);
599    }
600
601    public function testParseTypedColumnsWithNumericAffinity(): void
602    {
603        $parser = new SqliteSchemaParser();
604        $sql = 'CREATE TABLE t (a CUSTOM_TYPE)';
605        $result = $parser->parse($sql);
606        self::assertNotNull($result);
607        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['a']->family);
608    }
609
610    public function testParseTypedColumnsWithParenthesizedSize(): void
611    {
612        $parser = new SqliteSchemaParser();
613        $sql = 'CREATE TABLE t (a VARCHAR(255))';
614        $result = $parser->parse($sql);
615        self::assertNotNull($result);
616        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['a']->family);
617        self::assertSame('VARCHAR(255)', $result->typedColumns['a']->nativeType);
618    }
619
620    public function testParseWithConstraintPrimaryKey(): void
621    {
622        $parser = new SqliteSchemaParser();
623        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, CONSTRAINT pk_t PRIMARY KEY (a))';
624        $result = $parser->parse($sql);
625        self::assertNotNull($result);
626        self::assertSame(['a'], $result->primaryKeys);
627    }
628
629    public function testParseWithConstraintUnique(): void
630    {
631        $parser = new SqliteSchemaParser();
632        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, CONSTRAINT uq_b UNIQUE (b))';
633        $result = $parser->parse($sql);
634        self::assertNotNull($result);
635        self::assertNotEmpty($result->uniqueConstraints);
636        $uniqueValues = array_values($result->uniqueConstraints);
637        self::assertSame(['b'], $uniqueValues[0]);
638    }
639
640    public function testParseWithCheckConstraintAtTableLevel(): void
641    {
642        $parser = new SqliteSchemaParser();
643        $sql = 'CREATE TABLE t (a INTEGER, b INTEGER, CHECK (a > b))';
644        $result = $parser->parse($sql);
645        self::assertNotNull($result);
646        self::assertSame(['a', 'b'], $result->columns);
647    }
648
649    public function testParseNoColumnsReturnsNull(): void
650    {
651        $parser = new SqliteSchemaParser();
652        self::assertNull($parser->parse('CREATE TABLE t ()'));
653    }
654
655    public function testParseWithoutRowid(): void
656    {
657        $parser = new SqliteSchemaParser();
658        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT) WITHOUT ROWID';
659        $result = $parser->parse($sql);
660        self::assertNotNull($result);
661        self::assertSame(['a', 'b'], $result->columns);
662    }
663
664    public function testParseColumnNoTypeWithNotNull(): void
665    {
666        $parser = new SqliteSchemaParser();
667        $sql = 'CREATE TABLE t (a NOT NULL)';
668        $result = $parser->parse($sql);
669        self::assertNotNull($result);
670        self::assertSame(['a'], $result->columns);
671        self::assertContains('a', $result->notNullColumns);
672        self::assertArrayNotHasKey('a', $result->columnTypes);
673    }
674
675    public function testParseColumnNoTypeWithPrimaryKey(): void
676    {
677        $parser = new SqliteSchemaParser();
678        $sql = 'CREATE TABLE t (a PRIMARY KEY)';
679        $result = $parser->parse($sql);
680        self::assertNotNull($result);
681        self::assertSame(['a'], $result->primaryKeys);
682    }
683
684    public function testParseColumnWithDefault(): void
685    {
686        $parser = new SqliteSchemaParser();
687        $sql = "CREATE TABLE t (a TEXT DEFAULT 'hello', b INTEGER DEFAULT 0)";
688        $result = $parser->parse($sql);
689        self::assertNotNull($result);
690        self::assertSame(['a', 'b'], $result->columns);
691        self::assertSame('TEXT', $result->columnTypes['a']);
692        self::assertSame('INTEGER', $result->columnTypes['b']);
693    }
694
695    public function testParseWithQuotedCommaInString(): void
696    {
697        $parser = new SqliteSchemaParser();
698        $sql = "CREATE TABLE t (a TEXT DEFAULT 'a,b', b INTEGER)";
699        $result = $parser->parse($sql);
700        self::assertNotNull($result);
701        self::assertSame(['a', 'b'], $result->columns);
702    }
703
704    public function testParseColumnWithCollate(): void
705    {
706        $parser = new SqliteSchemaParser();
707        $sql = 'CREATE TABLE t (a COLLATE NOCASE)';
708        $result = $parser->parse($sql);
709        self::assertNotNull($result);
710        self::assertSame(['a'], $result->columns);
711    }
712
713    public function testParseColumnWithGeneratedAs(): void
714    {
715        $parser = new SqliteSchemaParser();
716        $sql = 'CREATE TABLE t (a INTEGER, b GENERATED ALWAYS AS (a * 2))';
717        $result = $parser->parse($sql);
718        self::assertNotNull($result);
719        self::assertContains('b', $result->columns);
720        self::assertSame(['b' => '(a * 2)'], $result->generatedExpressions);
721    }
722
723    public function testGeneratedExpressionExcludesStoredAndVirtualStorageKeywords(): void
724    {
725        $result = (new SqliteSchemaParser())->parse('CREATE TABLE t (a INTEGER, stored_value INTEGER GENERATED ALWAYS AS (a * 2) STORED, ' . 'virtual_value INTEGER AS (a * 3) VIRTUAL)');
726        self::assertNotNull($result);
727        self::assertSame(['stored_value' => '(a * 2)', 'virtual_value' => '(a * 3)'], $result->generatedExpressions);
728    }
729
730    public function testParseColumnWithReferences(): void
731    {
732        $parser = new SqliteSchemaParser();
733        $sql = 'CREATE TABLE t (a INTEGER REFERENCES other(id))';
734        $result = $parser->parse($sql);
735        self::assertNotNull($result);
736        self::assertSame(['a'], $result->columns);
737    }
738
739    public function testParsePrimaryKeyColumnIsNotNull(): void
740    {
741        $parser = new SqliteSchemaParser();
742        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT)';
743        $result = $parser->parse($sql);
744        self::assertNotNull($result);
745        self::assertContains('a', $result->notNullColumns);
746    }
747
748    public function testParseUniqueColumnConstraint(): void
749    {
750        $parser = new SqliteSchemaParser();
751        $sql = 'CREATE TABLE t (a INTEGER, b TEXT UNIQUE)';
752        $result = $parser->parse($sql);
753        self::assertNotNull($result);
754        self::assertArrayHasKey('b_UNIQUE', $result->uniqueConstraints);
755        self::assertSame(['b'], $result->uniqueConstraints['b_UNIQUE']);
756    }
757
758    public function testParseIntegerTypeFamilyMapping(): void
759    {
760        $parser = new SqliteSchemaParser();
761        $sql = 'CREATE TABLE t (a INTEGER)';
762        $result = $parser->parse($sql);
763        self::assertNotNull($result);
764        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['a']->family);
765    }
766
767    public function testParseColumnWithBackticks(): void
768    {
769        $parser = new SqliteSchemaParser();
770        $sql = 'CREATE TABLE t (`my col` INTEGER)';
771        $result = $parser->parse($sql);
772        self::assertNotNull($result);
773        self::assertSame(['my col'], $result->columns);
774    }
775
776    public function testParseColumnWithBrackets(): void
777    {
778        $parser = new SqliteSchemaParser();
779        $sql = 'CREATE TABLE t ([my col] INTEGER)';
780        $result = $parser->parse($sql);
781        self::assertNotNull($result);
782        self::assertSame(['my col'], $result->columns);
783    }
784
785    public function testParseUniqueConstraintOnNonExistentColumnReturnsNull(): void
786    {
787        $parser = new SqliteSchemaParser();
788        $sql = 'CREATE TABLE t (a INTEGER, UNIQUE(nonexistent))';
789        $result = $parser->parse($sql);
790        self::assertNull($result);
791    }
792
793    public function testParseTemporaryTable(): void
794    {
795        $parser = new SqliteSchemaParser();
796        $sql = 'CREATE TEMPORARY TABLE tmp (id INTEGER PRIMARY KEY, val TEXT)';
797        $result = $parser->parse($sql);
798        self::assertNotNull($result);
799        self::assertSame(['id', 'val'], $result->columns);
800    }
801
802    public function testParseDuplicatePrimaryKeysDeduped(): void
803    {
804        $parser = new SqliteSchemaParser();
805        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, PRIMARY KEY (a))';
806        $result = $parser->parse($sql);
807        self::assertNotNull($result);
808        self::assertSame(['a'], $result->primaryKeys);
809    }
810
811    public function testParseDuplicateNotNullDeduped(): void
812    {
813        $parser = new SqliteSchemaParser();
814        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY NOT NULL)';
815        $result = $parser->parse($sql);
816        self::assertNotNull($result);
817        $count = array_count_values($result->notNullColumns);
818        self::assertSame(1, $count['a']);
819    }
820
821    public function testParseLowercase(): void
822    {
823        $parser = new SqliteSchemaParser();
824        $sql = 'create table users (id integer primary key, name text not null)';
825        $result = $parser->parse($sql);
826        self::assertNotNull($result);
827        self::assertSame(['id', 'name'], $result->columns);
828        self::assertSame(['id'], $result->primaryKeys);
829        self::assertContains('name', $result->notNullColumns);
830    }
831
832    public function testParseLowercaseWithUnique(): void
833    {
834        $parser = new SqliteSchemaParser();
835        $sql = 'create table t (id integer primary key, email text unique)';
836        $result = $parser->parse($sql);
837        self::assertNotNull($result);
838        self::assertNotEmpty($result->uniqueConstraints);
839    }
840
841    public function testParseLowercaseTableLevelPrimaryKey(): void
842    {
843        $parser = new SqliteSchemaParser();
844        $sql = 'create table t (a integer, b integer, primary key (a, b))';
845        $result = $parser->parse($sql);
846        self::assertNotNull($result);
847        self::assertSame(['a', 'b'], $result->primaryKeys);
848    }
849
850    public function testParseLowercaseTableLevelUnique(): void
851    {
852        $parser = new SqliteSchemaParser();
853        $sql = 'create table t (a integer, b integer, unique (a, b))';
854        $result = $parser->parse($sql);
855        self::assertNotNull($result);
856        self::assertNotEmpty($result->uniqueConstraints);
857    }
858
859    public function testParseLowercaseConstraintPrimaryKey(): void
860    {
861        $parser = new SqliteSchemaParser();
862        $sql = 'create table t (a integer, constraint pk primary key (a))';
863        $result = $parser->parse($sql);
864        self::assertNotNull($result);
865        self::assertSame(['a'], $result->primaryKeys);
866    }
867
868    public function testParseLowercaseConstraintUnique(): void
869    {
870        $parser = new SqliteSchemaParser();
871        $sql = 'create table t (a integer, constraint uq unique (a))';
872        $result = $parser->parse($sql);
873        self::assertNotNull($result);
874        self::assertNotEmpty($result->uniqueConstraints);
875    }
876
877    public function testParseLowercaseForeignKey(): void
878    {
879        $parser = new SqliteSchemaParser();
880        $sql = 'create table t (id integer, uid integer, foreign key (uid) references users(id))';
881        $result = $parser->parse($sql);
882        self::assertNotNull($result);
883        self::assertSame(['id', 'uid'], $result->columns);
884        self::assertCount(1, $result->foreignKeys);
885        self::assertSame('users', array_values($result->foreignKeys)[0]->referencedTable);
886        self::assertSame(['uid'], array_values($result->foreignKeys)[0]->columns);
887    }
888
889    public function testParseLowercaseCheck(): void
890    {
891        $parser = new SqliteSchemaParser();
892        $sql = 'create table t (id integer, age integer, check (age > 0))';
893        $result = $parser->parse($sql);
894        self::assertNotNull($result);
895        self::assertSame(['id', 'age'], $result->columns);
896    }
897
898    public function testParseLowercaseIfNotExists(): void
899    {
900        $parser = new SqliteSchemaParser();
901        $sql = 'create table if not exists t (id integer)';
902        $result = $parser->parse($sql);
903        self::assertNotNull($result);
904        self::assertSame(['id'], $result->columns);
905    }
906
907    public function testParseLowercaseTemporary(): void
908    {
909        $parser = new SqliteSchemaParser();
910        $sql = 'create temporary table tmp (id integer)';
911        $result = $parser->parse($sql);
912        self::assertNotNull($result);
913        self::assertSame(['id'], $result->columns);
914    }
915
916    public function testParseWithTrailingSemicolon(): void
917    {
918        $parser = new SqliteSchemaParser();
919        $sql = 'CREATE TABLE t (id INTEGER);';
920        $result = $parser->parse($sql);
921        self::assertNotNull($result);
922        self::assertSame(['id'], $result->columns);
923    }
924
925    public function testParseColumnNotNullAndUnique(): void
926    {
927        $parser = new SqliteSchemaParser();
928        $sql = 'CREATE TABLE t (a INTEGER, b TEXT NOT NULL UNIQUE)';
929        $result = $parser->parse($sql);
930        self::assertNotNull($result);
931        self::assertContains('b', $result->notNullColumns);
932        self::assertArrayHasKey('b_UNIQUE', $result->uniqueConstraints);
933    }
934
935    public function testParseColumnPrimaryKeyIsNotUnique(): void
936    {
937        $parser = new SqliteSchemaParser();
938        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY UNIQUE)';
939        $result = $parser->parse($sql);
940        self::assertNotNull($result);
941        self::assertSame(['a'], $result->primaryKeys);
942        self::assertEmpty($result->uniqueConstraints);
943    }
944
945    public function testParseLowercaseWithoutRowid(): void
946    {
947        $parser = new SqliteSchemaParser();
948        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT) without rowid';
949        $result = $parser->parse($sql);
950        self::assertNotNull($result);
951        self::assertSame(['a', 'b'], $result->columns);
952    }
953
954    public function testParseTypedColumnsNativeTypePreserved(): void
955    {
956        $parser = new SqliteSchemaParser();
957        $sql = 'CREATE TABLE t (a TINYINT, b SMALLINT, c MEDIUMINT, d BIGINT)';
958        $result = $parser->parse($sql);
959        self::assertNotNull($result);
960        self::assertSame('TINYINT', $result->typedColumns['a']->nativeType);
961        self::assertSame('SMALLINT', $result->typedColumns['b']->nativeType);
962        self::assertSame('MEDIUMINT', $result->typedColumns['c']->nativeType);
963        self::assertSame('BIGINT', $result->typedColumns['d']->nativeType);
964    }
965
966    public function testParseColumnWithOnKeyword(): void
967    {
968        $parser = new SqliteSchemaParser();
969        $sql = 'CREATE TABLE t (a INTEGER, b TEXT ON CONFLICT REPLACE)';
970        $result = $parser->parse($sql);
971        self::assertNotNull($result);
972        self::assertContains('b', $result->columns);
973    }
974
975    public function testParseColumnTypeNotNullExcluded(): void
976    {
977        $parser = new SqliteSchemaParser();
978        $sql = 'CREATE TABLE t (a NOT NULL, b INTEGER NOT NULL)';
979        $result = $parser->parse($sql);
980        self::assertNotNull($result);
981        self::assertArrayNotHasKey('a', $result->columnTypes);
982        self::assertSame('INTEGER', $result->columnTypes['b']);
983    }
984
985    public function testParseColumnTypeUniqueExcluded(): void
986    {
987        $parser = new SqliteSchemaParser();
988        $sql = 'CREATE TABLE t (a UNIQUE)';
989        $result = $parser->parse($sql);
990        self::assertNotNull($result);
991        self::assertArrayNotHasKey('a', $result->columnTypes);
992    }
993
994    public function testParseColumnTypeCheckExcluded(): void
995    {
996        $parser = new SqliteSchemaParser();
997        $sql = 'CREATE TABLE t (a CHECK(a > 0))';
998        $result = $parser->parse($sql);
999        self::assertNotNull($result);
1000        self::assertArrayNotHasKey('a', $result->columnTypes);
1001    }
1002
1003    public function testParseColumnTypeDefaultExcluded(): void
1004    {
1005        $parser = new SqliteSchemaParser();
1006        $sql = "CREATE TABLE t (a DEFAULT 'x')";
1007        $result = $parser->parse($sql);
1008        self::assertNotNull($result);
1009        self::assertArrayNotHasKey('a', $result->columnTypes);
1010    }
1011
1012    public function testParseColumnTypeConstraintExcluded(): void
1013    {
1014        $parser = new SqliteSchemaParser();
1015        $sql = 'CREATE TABLE t (a CONSTRAINT ck CHECK(a > 0))';
1016        $result = $parser->parse($sql);
1017        self::assertNotNull($result);
1018        self::assertArrayNotHasKey('a', $result->columnTypes);
1019    }
1020
1021    public function testParseColumnTypeAsExcluded(): void
1022    {
1023        $parser = new SqliteSchemaParser();
1024        $sql = 'CREATE TABLE t (a INTEGER, b AS (a * 2))';
1025        $result = $parser->parse($sql);
1026        self::assertNotNull($result);
1027        self::assertArrayNotHasKey('b', $result->columnTypes);
1028    }
1029
1030    public function testParseWithWhitespace(): void
1031    {
1032        $parser = new SqliteSchemaParser();
1033        $sql = '  CREATE TABLE t (a INTEGER)  ';
1034        $result = $parser->parse($sql);
1035        self::assertNotNull($result);
1036        self::assertSame(['a'], $result->columns);
1037    }
1038
1039    public function testParsePrimaryKeyMergedFromMultipleSources(): void
1040    {
1041        $parser = new SqliteSchemaParser();
1042        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b INTEGER, PRIMARY KEY (b))';
1043        $result = $parser->parse($sql);
1044        self::assertNotNull($result);
1045        self::assertContains('a', $result->primaryKeys);
1046        self::assertContains('b', $result->primaryKeys);
1047    }
1048
1049    public function testParseConstraintQuotedName(): void
1050    {
1051        $parser = new SqliteSchemaParser();
1052        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, CONSTRAINT "pk_t" PRIMARY KEY (a))';
1053        $result = $parser->parse($sql);
1054        self::assertNotNull($result);
1055        self::assertSame(['a'], $result->primaryKeys);
1056    }
1057
1058    public function testParseConstraintBacktickName(): void
1059    {
1060        $parser = new SqliteSchemaParser();
1061        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, CONSTRAINT `uq` UNIQUE (b))';
1062        $result = $parser->parse($sql);
1063        self::assertNotNull($result);
1064        self::assertNotEmpty($result->uniqueConstraints);
1065    }
1066
1067    public function testParseMultipleTableLevelUniqueConstraints(): void
1068    {
1069        $parser = new SqliteSchemaParser();
1070        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, c TEXT, UNIQUE(a, b), UNIQUE(b, c))';
1071        $result = $parser->parse($sql);
1072        self::assertNotNull($result);
1073        self::assertCount(2, $result->uniqueConstraints);
1074    }
1075
1076    public function testParseColumnTypeWithForeign(): void
1077    {
1078        $parser = new SqliteSchemaParser();
1079        $sql = 'CREATE TABLE t (a FOREIGN)';
1080        $result = $parser->parse($sql);
1081        self::assertNotNull($result);
1082        self::assertArrayNotHasKey('a', $result->columnTypes);
1083    }
1084
1085    public function testParseColumnTypeFamilyInteger(): void
1086    {
1087        $parser = new SqliteSchemaParser();
1088        $sql = 'CREATE TABLE t (a INTEGER)';
1089        $result = $parser->parse($sql);
1090        self::assertNotNull($result);
1091        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['a']->family);
1092        self::assertSame('INTEGER', $result->typedColumns['a']->nativeType);
1093    }
1094
1095    public function testParseColumnTypeFamilyNumericAffinityWithParens(): void
1096    {
1097        $parser = new SqliteSchemaParser();
1098        $sql = 'CREATE TABLE t (a CUSTOM_TYPE(10))';
1099        $result = $parser->parse($sql);
1100        self::assertNotNull($result);
1101        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['a']->family);
1102        self::assertSame('CUSTOM_TYPE(10)', $result->typedColumns['a']->nativeType);
1103    }
1104
1105    public function testParseColumnWithSingleQuoteInDefault(): void
1106    {
1107        $parser = new SqliteSchemaParser();
1108        $sql = "CREATE TABLE t (a TEXT DEFAULT 'it''s', b INTEGER)";
1109        $result = $parser->parse($sql);
1110        self::assertNotNull($result);
1111        self::assertSame(['a', 'b'], $result->columns);
1112    }
1113
1114    public function testParseUniqueConstraintIncrementIndex(): void
1115    {
1116        $parser = new SqliteSchemaParser();
1117        $sql = 'CREATE TABLE t (a INTEGER, b TEXT UNIQUE, c TEXT, UNIQUE(a), UNIQUE(c))';
1118        $result = $parser->parse($sql);
1119        self::assertNotNull($result);
1120        self::assertArrayHasKey('b_UNIQUE', $result->uniqueConstraints);
1121        self::assertArrayHasKey('unique_0', $result->uniqueConstraints);
1122        self::assertArrayHasKey('unique_1', $result->uniqueConstraints);
1123    }
1124
1125    public function testParseEmptyDefinitionSkipped(): void
1126    {
1127        $parser = new SqliteSchemaParser();
1128        $sql = 'CREATE TABLE t (a INTEGER, , b TEXT)';
1129        $result = $parser->parse($sql);
1130        self::assertNotNull($result);
1131        self::assertSame(['a', 'b'], $result->columns);
1132    }
1133
1134    public function testParseSplitColumnDefinitionsWithQuotedComma(): void
1135    {
1136        $parser = new SqliteSchemaParser();
1137        $sql = "CREATE TABLE t (a TEXT DEFAULT 'a,b', c INTEGER)";
1138        $result = $parser->parse($sql);
1139        self::assertNotNull($result);
1140        self::assertSame(['a', 'c'], $result->columns);
1141    }
1142
1143    public function testParseSplitColumnDefinitionsWithParenComma(): void
1144    {
1145        $parser = new SqliteSchemaParser();
1146        $sql = 'CREATE TABLE t (a DECIMAL(10,2), b INTEGER)';
1147        $result = $parser->parse($sql);
1148        self::assertNotNull($result);
1149        self::assertSame(['a', 'b'], $result->columns);
1150        self::assertSame('DECIMAL(10,2)', $result->columnTypes['a']);
1151    }
1152
1153    public function testParseColumnNameEmpty(): void
1154    {
1155        $parser = new SqliteSchemaParser();
1156        $sql = 'CREATE TABLE t ("" INTEGER)';
1157        $result = $parser->parse($sql);
1158        self::assertNull($result);
1159    }
1160
1161    public function testParseSplitColumnDefinitionsDoubleQuoteInDefault(): void
1162    {
1163        $parser = new SqliteSchemaParser();
1164        $sql = "CREATE TABLE t (a TEXT DEFAULT 'ab\"cd', b INTEGER)";
1165        $result = $parser->parse($sql);
1166        self::assertNotNull($result);
1167        self::assertSame(['a', 'b'], $result->columns);
1168    }
1169
1170    public function testParseSplitColumnDefinitionsWithEscapedSingleQuote(): void
1171    {
1172        $parser = new SqliteSchemaParser();
1173        $sql = "CREATE TABLE t (a TEXT DEFAULT 'a''b', c INTEGER)";
1174        $result = $parser->parse($sql);
1175        self::assertNotNull($result);
1176        self::assertSame(['a', 'c'], $result->columns);
1177    }
1178
1179    public function testParseSplitColumnDefinitionsWithEscapedDoubleQuote(): void
1180    {
1181        $parser = new SqliteSchemaParser();
1182        $sql = 'CREATE TABLE t ("a""b" INTEGER, c TEXT)';
1183        $result = $parser->parse($sql);
1184        self::assertNotNull($result);
1185        self::assertContains('a"b', $result->columns);
1186        self::assertContains('c', $result->columns);
1187    }
1188
1189    public function testParseSplitColumnDefinitionsCloseParenReducesDepth(): void
1190    {
1191        $parser = new SqliteSchemaParser();
1192        $sql = 'CREATE TABLE t (a DECIMAL(10), b INTEGER)';
1193        $result = $parser->parse($sql);
1194        self::assertNotNull($result);
1195        self::assertSame(['a', 'b'], $result->columns);
1196    }
1197
1198    public function testParseArrayValuesAndUniqueOnPrimaryKeys(): void
1199    {
1200        $parser = new SqliteSchemaParser();
1201        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b INTEGER NOT NULL, PRIMARY KEY (a))';
1202        $result = $parser->parse($sql);
1203        self::assertNotNull($result);
1204        self::assertSame(['a'], $result->primaryKeys);
1205        self::assertSame(['a', 'b'], $result->notNullColumns);
1206    }
1207
1208    public function testParseExtractColumnTypeWithParenthesized(): void
1209    {
1210        $parser = new SqliteSchemaParser();
1211        $sql = 'CREATE TABLE t (a VARCHAR(100) NOT NULL)';
1212        $result = $parser->parse($sql);
1213        self::assertNotNull($result);
1214        self::assertSame('VARCHAR(100)', $result->columnTypes['a']);
1215    }
1216
1217    public function testParseExtractColumnTypeWithTwoWordType(): void
1218    {
1219        $parser = new SqliteSchemaParser();
1220        $sql = 'CREATE TABLE t (a DOUBLE PRECISION NOT NULL)';
1221        $result = $parser->parse($sql);
1222        self::assertNotNull($result);
1223        self::assertSame('DOUBLE PRECISION', $result->columnTypes['a']);
1224    }
1225
1226    public function testParseColumnDefinitionEmptyRestNoType(): void
1227    {
1228        $parser = new SqliteSchemaParser();
1229        $sql = 'CREATE TABLE t (x)';
1230        $result = $parser->parse($sql);
1231        self::assertNotNull($result);
1232        self::assertSame(['x'], $result->columns);
1233        self::assertArrayNotHasKey('x', $result->columnTypes);
1234    }
1235
1236    public function testParseColumnTypePrimaryExcluded(): void
1237    {
1238        $parser = new SqliteSchemaParser();
1239        $sql = 'CREATE TABLE t (a PRIMARY KEY)';
1240        $result = $parser->parse($sql);
1241        self::assertNotNull($result);
1242        self::assertArrayNotHasKey('a', $result->columnTypes);
1243        self::assertSame(['a'], $result->primaryKeys);
1244    }
1245
1246    public function testParseExtractColumnTypeNullForEmptyType(): void
1247    {
1248        $parser = new SqliteSchemaParser();
1249        $sql = 'CREATE TABLE t (a)';
1250        $result = $parser->parse($sql);
1251        self::assertNotNull($result);
1252        self::assertSame([], $result->columnTypes);
1253    }
1254
1255    public function testParseColumnNameList(): void
1256    {
1257        $parser = new SqliteSchemaParser();
1258        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, PRIMARY KEY ("a", `b`))';
1259        $result = $parser->parse($sql);
1260        self::assertNotNull($result);
1261        self::assertContains('a', $result->primaryKeys);
1262        self::assertContains('b', $result->primaryKeys);
1263    }
1264
1265    public function testParseTypeFamilyMappingPreservesParenthesizedType(): void
1266    {
1267        $parser = new SqliteSchemaParser();
1268        $sql = 'CREATE TABLE t (a INT(11))';
1269        $result = $parser->parse($sql);
1270        self::assertNotNull($result);
1271        self::assertSame(ColumnTypeFamily::INTEGER, $result->typedColumns['a']->family);
1272        self::assertSame('INT(11)', $result->typedColumns['a']->nativeType);
1273    }
1274
1275    public function testPrimaryKeyConstraintDoesNotCreateColumn(): void
1276    {
1277        $parser = new SqliteSchemaParser();
1278        $sql = 'CREATE TABLE t (id INTEGER, name TEXT, PRIMARY KEY (id))';
1279        $result = $parser->parse($sql);
1280        self::assertNotNull($result);
1281        self::assertSame(['id', 'name'], $result->columns);
1282        self::assertContains('id', $result->primaryKeys);
1283    }
1284
1285    public function testUniqueConstraintDoesNotCreateColumn(): void
1286    {
1287        $parser = new SqliteSchemaParser();
1288        $sql = 'CREATE TABLE t (id INTEGER, email TEXT, UNIQUE (email))';
1289        $result = $parser->parse($sql);
1290        self::assertNotNull($result);
1291        self::assertSame(['id', 'email'], $result->columns);
1292        self::assertArrayHasKey('unique_0', $result->uniqueConstraints);
1293        self::assertContains('email', $result->uniqueConstraints['unique_0']);
1294    }
1295
1296    public function testConstraintPrimaryKeyDoesNotCreateColumn(): void
1297    {
1298        $parser = new SqliteSchemaParser();
1299        $sql = 'CREATE TABLE t (id INTEGER, CONSTRAINT pk PRIMARY KEY (id))';
1300        $result = $parser->parse($sql);
1301        self::assertNotNull($result);
1302        self::assertSame(['id'], $result->columns);
1303        self::assertContains('id', $result->primaryKeys);
1304    }
1305
1306    public function testConstraintUniqueDoesNotCreateColumn(): void
1307    {
1308        $parser = new SqliteSchemaParser();
1309        $sql = 'CREATE TABLE t (id INTEGER, email TEXT, CONSTRAINT uq UNIQUE (email))';
1310        $result = $parser->parse($sql);
1311        self::assertNotNull($result);
1312        self::assertSame(['id', 'email'], $result->columns);
1313    }
1314
1315    public function testForeignKeyDoesNotCreateColumn(): void
1316    {
1317        $parser = new SqliteSchemaParser();
1318        $sql = 'CREATE TABLE t (id INTEGER, user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users (id))';
1319        $result = $parser->parse($sql);
1320        self::assertNotNull($result);
1321        self::assertSame(['id', 'user_id'], $result->columns);
1322    }
1323
1324    public function testCheckConstraintDoesNotCreateColumn(): void
1325    {
1326        $parser = new SqliteSchemaParser();
1327        $sql = 'CREATE TABLE t (id INTEGER, age INTEGER, CHECK (age > 0))';
1328        $result = $parser->parse($sql);
1329        self::assertNotNull($result);
1330        self::assertSame(['id', 'age'], $result->columns);
1331    }
1332
1333    public function testColumnWithPrimaryKeyKeywordHasNullType(): void
1334    {
1335        $parser = new SqliteSchemaParser();
1336        $sql = 'CREATE TABLE t (id PRIMARY KEY)';
1337        $result = $parser->parse($sql);
1338        self::assertNotNull($result);
1339        self::assertContains('id', $result->columns);
1340        self::assertArrayNotHasKey('id', $result->columnTypes);
1341    }
1342
1343    public function testColumnWithNotNullKeywordHasNullType(): void
1344    {
1345        $parser = new SqliteSchemaParser();
1346        $sql = 'CREATE TABLE t (id NOT NULL)';
1347        $result = $parser->parse($sql);
1348        self::assertNotNull($result);
1349        self::assertContains('id', $result->columns);
1350        self::assertArrayNotHasKey('id', $result->columnTypes);
1351    }
1352
1353    public function testColumnWithUniqueKeywordHasNullType(): void
1354    {
1355        $parser = new SqliteSchemaParser();
1356        $sql = 'CREATE TABLE t (id UNIQUE)';
1357        $result = $parser->parse($sql);
1358        self::assertNotNull($result);
1359        self::assertContains('id', $result->columns);
1360        self::assertArrayNotHasKey('id', $result->columnTypes);
1361    }
1362
1363    public function testColumnWithCheckKeywordHasNullType(): void
1364    {
1365        $parser = new SqliteSchemaParser();
1366        $sql = 'CREATE TABLE t (id CHECK (id > 0))';
1367        $result = $parser->parse($sql);
1368        self::assertNotNull($result);
1369        self::assertContains('id', $result->columns);
1370        self::assertArrayNotHasKey('id', $result->columnTypes);
1371    }
1372
1373    public function testColumnWithDefaultKeywordHasNullType(): void
1374    {
1375        $parser = new SqliteSchemaParser();
1376        $sql = 'CREATE TABLE t (id DEFAULT 0)';
1377        $result = $parser->parse($sql);
1378        self::assertNotNull($result);
1379        self::assertContains('id', $result->columns);
1380        self::assertArrayNotHasKey('id', $result->columnTypes);
1381    }
1382
1383    public function testColumnWithReferencesKeywordHasNullType(): void
1384    {
1385        $parser = new SqliteSchemaParser();
1386        $sql = 'CREATE TABLE t (id REFERENCES other(id))';
1387        $result = $parser->parse($sql);
1388        self::assertNotNull($result);
1389        self::assertContains('id', $result->columns);
1390        self::assertArrayNotHasKey('id', $result->columnTypes);
1391    }
1392
1393    public function testColumnWithConstraintKeywordHasNullType(): void
1394    {
1395        $parser = new SqliteSchemaParser();
1396        $sql = 'CREATE TABLE t (id CONSTRAINT ck CHECK (id > 0))';
1397        $result = $parser->parse($sql);
1398        self::assertNotNull($result);
1399        self::assertContains('id', $result->columns);
1400        self::assertArrayNotHasKey('id', $result->columnTypes);
1401    }
1402
1403    public function testColumnWithCollateKeywordHasNullType(): void
1404    {
1405        $parser = new SqliteSchemaParser();
1406        $sql = 'CREATE TABLE t (id COLLATE NOCASE)';
1407        $result = $parser->parse($sql);
1408        self::assertNotNull($result);
1409        self::assertContains('id', $result->columns);
1410        self::assertArrayNotHasKey('id', $result->columnTypes);
1411    }
1412
1413    public function testColumnWithGeneratedKeywordHasNullType(): void
1414    {
1415        $parser = new SqliteSchemaParser();
1416        $sql = 'CREATE TABLE t (id GENERATED ALWAYS AS (1))';
1417        $result = $parser->parse($sql);
1418        self::assertNotNull($result);
1419        self::assertContains('id', $result->columns);
1420        self::assertArrayNotHasKey('id', $result->columnTypes);
1421        self::assertSame(['id' => '(1)'], $result->generatedExpressions);
1422    }
1423
1424    public function testColumnWithAsKeywordHasNullType(): void
1425    {
1426        $parser = new SqliteSchemaParser();
1427        $sql = 'CREATE TABLE t (id AS (1 + 1))';
1428        $result = $parser->parse($sql);
1429        self::assertNotNull($result);
1430        self::assertContains('id', $result->columns);
1431        self::assertArrayNotHasKey('id', $result->columnTypes);
1432        self::assertSame(['id' => '(1 + 1)'], $result->generatedExpressions);
1433    }
1434
1435    public function testDuplicatePrimaryKeysDeduped(): void
1436    {
1437        $parser = new SqliteSchemaParser();
1438        $sql = 'CREATE TABLE t (id INTEGER PRIMARY KEY, PRIMARY KEY (id))';
1439        $result = $parser->parse($sql);
1440        self::assertNotNull($result);
1441        self::assertSame(['id'], $result->primaryKeys);
1442    }
1443
1444    public function testDuplicateNotNullDeduped(): void
1445    {
1446        $parser = new SqliteSchemaParser();
1447        $sql = 'CREATE TABLE t (id INTEGER NOT NULL PRIMARY KEY)';
1448        $result = $parser->parse($sql);
1449        self::assertNotNull($result);
1450        $count = count(array_filter($result->notNullColumns, static fn (string $c): bool => $c === 'id'));
1451        self::assertSame(1, $count);
1452    }
1453
1454    public function testColumnTypeTextMapsToTextFamily(): void
1455    {
1456        $parser = new SqliteSchemaParser();
1457        $sql = 'CREATE TABLE t (name TEXT)';
1458        $result = $parser->parse($sql);
1459        self::assertNotNull($result);
1460        self::assertSame(ColumnTypeFamily::TEXT, $result->typedColumns['name']->family);
1461    }
1462
1463    public function testColumnTypeRealMapsToFloatFamily(): void
1464    {
1465        $parser = new SqliteSchemaParser();
1466        $sql = 'CREATE TABLE t (val REAL)';
1467        $result = $parser->parse($sql);
1468        self::assertNotNull($result);
1469        self::assertSame(ColumnTypeFamily::FLOAT, $result->typedColumns['val']->family);
1470    }
1471
1472    public function testColumnTypeBlobMapsToBinaryFamily(): void
1473    {
1474        $parser = new SqliteSchemaParser();
1475        $sql = 'CREATE TABLE t (data BLOB)';
1476        $result = $parser->parse($sql);
1477        self::assertNotNull($result);
1478        self::assertSame(ColumnTypeFamily::BINARY, $result->typedColumns['data']->family);
1479    }
1480
1481    public function testColumnTypeDateMapsToDateFamily(): void
1482    {
1483        $parser = new SqliteSchemaParser();
1484        $sql = 'CREATE TABLE t (d DATE)';
1485        $result = $parser->parse($sql);
1486        self::assertNotNull($result);
1487        self::assertSame(ColumnTypeFamily::DATE, $result->typedColumns['d']->family);
1488    }
1489
1490    public function testColumnTypeDatetimeMapsToDatetimeFamily(): void
1491    {
1492        $parser = new SqliteSchemaParser();
1493        $sql = 'CREATE TABLE t (dt DATETIME)';
1494        $result = $parser->parse($sql);
1495        self::assertNotNull($result);
1496        self::assertSame(ColumnTypeFamily::DATETIME, $result->typedColumns['dt']->family);
1497    }
1498
1499    public function testColumnTypeTimestampMapsToTimestampFamily(): void
1500    {
1501        $parser = new SqliteSchemaParser();
1502        $sql = 'CREATE TABLE t (ts TIMESTAMP)';
1503        $result = $parser->parse($sql);
1504        self::assertNotNull($result);
1505        self::assertSame(ColumnTypeFamily::TIMESTAMP, $result->typedColumns['ts']->family);
1506    }
1507
1508    public function testColumnTypeTimeMapsToTimeFamily(): void
1509    {
1510        $parser = new SqliteSchemaParser();
1511        $sql = 'CREATE TABLE t (t TIME)';
1512        $result = $parser->parse($sql);
1513        self::assertNotNull($result);
1514        self::assertSame(ColumnTypeFamily::TIME, $result->typedColumns['t']->family);
1515    }
1516
1517    public function testColumnTypeJsonMapsToJsonFamily(): void
1518    {
1519        $parser = new SqliteSchemaParser();
1520        $sql = 'CREATE TABLE t (j JSON)';
1521        $result = $parser->parse($sql);
1522        self::assertNotNull($result);
1523        self::assertSame(ColumnTypeFamily::JSON, $result->typedColumns['j']->family);
1524    }
1525
1526    public function testColumnTypeBooleanMapsToBooleanFamily(): void
1527    {
1528        $parser = new SqliteSchemaParser();
1529        $sql = 'CREATE TABLE t (b BOOLEAN)';
1530        $result = $parser->parse($sql);
1531        self::assertNotNull($result);
1532        self::assertSame(ColumnTypeFamily::BOOLEAN, $result->typedColumns['b']->family);
1533    }
1534
1535    public function testColumnTypeDecimalMapsToDecimalFamily(): void
1536    {
1537        $parser = new SqliteSchemaParser();
1538        $sql = 'CREATE TABLE t (d DECIMAL(10,2))';
1539        $result = $parser->parse($sql);
1540        self::assertNotNull($result);
1541        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['d']->family);
1542    }
1543
1544    public function testColumnTypeVarcharMapsToStringFamily(): void
1545    {
1546        $parser = new SqliteSchemaParser();
1547        $sql = 'CREATE TABLE t (s VARCHAR(255))';
1548        $result = $parser->parse($sql);
1549        self::assertNotNull($result);
1550        self::assertSame(ColumnTypeFamily::STRING, $result->typedColumns['s']->family);
1551    }
1552
1553    public function testColumnTypeWithoutAffinityMarkerMapsToDecimalFamily(): void
1554    {
1555        $parser = new SqliteSchemaParser();
1556        $sql = 'CREATE TABLE t (x CUSTOM_TYPE)';
1557        $result = $parser->parse($sql);
1558        self::assertNotNull($result);
1559        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['x']->family);
1560    }
1561
1562    public function testColumnUniqueNotPrimaryKey(): void
1563    {
1564        $parser = new SqliteSchemaParser();
1565        $sql = 'CREATE TABLE t (email TEXT UNIQUE)';
1566        $result = $parser->parse($sql);
1567        self::assertNotNull($result);
1568        self::assertArrayHasKey('email_UNIQUE', $result->uniqueConstraints);
1569        self::assertNotContains('email', $result->primaryKeys);
1570    }
1571
1572    public function testColumnPrimaryKeyAndUniqueOnlyCountsAsPrimary(): void
1573    {
1574        $parser = new SqliteSchemaParser();
1575        $sql = 'CREATE TABLE t (id INTEGER PRIMARY KEY UNIQUE)';
1576        $result = $parser->parse($sql);
1577        self::assertNotNull($result);
1578        self::assertContains('id', $result->primaryKeys);
1579        self::assertArrayNotHasKey('id_UNIQUE', $result->uniqueConstraints);
1580    }
1581
1582    public function testMultipleUniqueConstraintIndexes(): void
1583    {
1584        $parser = new SqliteSchemaParser();
1585        $sql = 'CREATE TABLE t (id INTEGER, a TEXT, b TEXT, UNIQUE (a), UNIQUE (b))';
1586        $result = $parser->parse($sql);
1587        self::assertNotNull($result);
1588        self::assertArrayHasKey('unique_0', $result->uniqueConstraints);
1589        self::assertArrayHasKey('unique_1', $result->uniqueConstraints);
1590        self::assertContains('a', $result->uniqueConstraints['unique_0']);
1591        self::assertContains('b', $result->uniqueConstraints['unique_1']);
1592    }
1593
1594    public function testUniqueConstraintReferringToNonExistentColumnReturnsNull(): void
1595    {
1596        $parser = new SqliteSchemaParser();
1597        $sql = 'CREATE TABLE t (id INTEGER, UNIQUE (nonexistent))';
1598        $result = $parser->parse($sql);
1599        self::assertNull($result);
1600    }
1601
1602    public function testExtractColumnTypeWithOnKeyword(): void
1603    {
1604        $parser = new SqliteSchemaParser();
1605        $sql = 'CREATE TABLE t (id INTEGER ON CONFLICT ABORT)';
1606        $result = $parser->parse($sql);
1607        self::assertNotNull($result);
1608        self::assertSame('INTEGER ON CONFLICT ABORT', $result->columnTypes['id']);
1609    }
1610
1611    public function testExtractColumnTypeReturnsNullForEmptyMatch(): void
1612    {
1613        $parser = new SqliteSchemaParser();
1614        $sql = 'CREATE TABLE t (id)';
1615        $result = $parser->parse($sql);
1616        self::assertNotNull($result);
1617        self::assertContains('id', $result->columns);
1618        self::assertArrayNotHasKey('id', $result->columnTypes);
1619    }
1620
1621    public function testWithoutRowidTable(): void
1622    {
1623        $parser = new SqliteSchemaParser();
1624        $sql = 'CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT) WITHOUT ROWID';
1625        $result = $parser->parse($sql);
1626        self::assertNotNull($result);
1627        self::assertSame(['id', 'name'], $result->columns);
1628    }
1629
1630    public function testTemporaryTable(): void
1631    {
1632        $parser = new SqliteSchemaParser();
1633        $sql = 'CREATE TEMPORARY TABLE t (id INTEGER PRIMARY KEY)';
1634        $result = $parser->parse($sql);
1635        self::assertNotNull($result);
1636        self::assertSame(['id'], $result->columns);
1637    }
1638
1639    public function testIfNotExistsTable(): void
1640    {
1641        $parser = new SqliteSchemaParser();
1642        $sql = 'CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY)';
1643        $result = $parser->parse($sql);
1644        self::assertNotNull($result);
1645        self::assertSame(['id'], $result->columns);
1646    }
1647
1648    public function testColumnNotNullFlagSet(): void
1649    {
1650        $parser = new SqliteSchemaParser();
1651        $sql = 'CREATE TABLE t (id INTEGER, name TEXT NOT NULL)';
1652        $result = $parser->parse($sql);
1653        self::assertNotNull($result);
1654        self::assertContains('name', $result->notNullColumns);
1655        self::assertNotContains('id', $result->notNullColumns);
1656    }
1657
1658    public function testPrimaryKeyColumnIsNotNull(): void
1659    {
1660        $parser = new SqliteSchemaParser();
1661        $sql = 'CREATE TABLE t (id INTEGER PRIMARY KEY)';
1662        $result = $parser->parse($sql);
1663        self::assertNotNull($result);
1664        self::assertContains('id', $result->notNullColumns);
1665    }
1666
1667    public function testMapToColumnTypeFamilyParenthesizedType(): void
1668    {
1669        $parser = new SqliteSchemaParser();
1670        $sql = 'CREATE TABLE t (v NUMERIC(10))';
1671        $result = $parser->parse($sql);
1672        self::assertNotNull($result);
1673        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['v']->family);
1674    }
1675
1676    public function testPrimaryKeyConstraintBeforeColumnDefinitions(): void
1677    {
1678        $parser = new SqliteSchemaParser();
1679        $sql = 'CREATE TABLE t (a INTEGER, PRIMARY KEY (a), b TEXT)';
1680        $result = $parser->parse($sql);
1681        self::assertNotNull($result);
1682        self::assertSame(['a', 'b'], $result->columns);
1683        self::assertContains('a', $result->primaryKeys);
1684        self::assertSame('TEXT', $result->columnTypes['b']);
1685    }
1686
1687    public function testConstraintPrimaryKeyBeforeColumnDefinitions(): void
1688    {
1689        $parser = new SqliteSchemaParser();
1690        $sql = 'CREATE TABLE t (a INTEGER, CONSTRAINT pk PRIMARY KEY (a), b TEXT)';
1691        $result = $parser->parse($sql);
1692        self::assertNotNull($result);
1693        self::assertSame(['a', 'b'], $result->columns);
1694        self::assertContains('a', $result->primaryKeys);
1695    }
1696
1697    public function testConstraintUniqueBeforeColumnDefinitions(): void
1698    {
1699        $parser = new SqliteSchemaParser();
1700        $sql = 'CREATE TABLE t (a INTEGER, CONSTRAINT uq UNIQUE (a), b TEXT)';
1701        $result = $parser->parse($sql);
1702        self::assertNotNull($result);
1703        self::assertSame(['a', 'b'], $result->columns);
1704        self::assertArrayHasKey('unique_0', $result->uniqueConstraints);
1705    }
1706
1707    public function testUniqueConstraintBeforeColumnDefinitions(): void
1708    {
1709        $parser = new SqliteSchemaParser();
1710        $sql = 'CREATE TABLE t (a INTEGER, UNIQUE (a), b TEXT)';
1711        $result = $parser->parse($sql);
1712        self::assertNotNull($result);
1713        self::assertSame(['a', 'b'], $result->columns);
1714    }
1715
1716    public function testForeignKeyConstraintBeforeColumnDefinitions(): void
1717    {
1718        $parser = new SqliteSchemaParser();
1719        $sql = 'CREATE TABLE t (a INTEGER, FOREIGN KEY (a) REFERENCES other(id), b TEXT)';
1720        $result = $parser->parse($sql);
1721        self::assertNotNull($result);
1722        self::assertSame(['a', 'b'], $result->columns);
1723    }
1724
1725    public function testCheckConstraintBeforeColumnDefinitions(): void
1726    {
1727        $parser = new SqliteSchemaParser();
1728        $sql = 'CREATE TABLE t (a INTEGER, CHECK (a > 0), b TEXT)';
1729        $result = $parser->parse($sql);
1730        self::assertNotNull($result);
1731        self::assertSame(['a', 'b'], $result->columns);
1732    }
1733
1734    public function testParseColumnDefinitionReturnsNullForEmptyName(): void
1735    {
1736        $parser = new SqliteSchemaParser();
1737        $sql = 'CREATE TABLE t ("" INTEGER)';
1738        $result = $parser->parse($sql);
1739        self::assertNull($result);
1740    }
1741
1742    public function testSplitColumnDefinitionsWithQuotedComma(): void
1743    {
1744        $parser = new SqliteSchemaParser();
1745        $sql = 'CREATE TABLE t ("a,b" INTEGER, c TEXT)';
1746        $result = $parser->parse($sql);
1747        self::assertNotNull($result);
1748        self::assertSame(['a,b', 'c'], $result->columns);
1749    }
1750
1751    public function testSplitColumnDefinitionsWithSingleQuotedDefault(): void
1752    {
1753        $parser = new SqliteSchemaParser();
1754        $sql = "CREATE TABLE t (a TEXT DEFAULT 'x,y', b INTEGER)";
1755        $result = $parser->parse($sql);
1756        self::assertNotNull($result);
1757        self::assertSame(['a', 'b'], $result->columns);
1758    }
1759
1760    public function testSplitColumnDefinitionsWithNestedParens(): void
1761    {
1762        $parser = new SqliteSchemaParser();
1763        $sql = 'CREATE TABLE t (a INTEGER CHECK(a IN (1,2,3)), b TEXT)';
1764        $result = $parser->parse($sql);
1765        self::assertNotNull($result);
1766        self::assertSame(['a', 'b'], $result->columns);
1767    }
1768
1769    public function testExtractColumnTypeCaseInsensitive(): void
1770    {
1771        $parser = new SqliteSchemaParser();
1772        $sql = 'CREATE TABLE t (a integer)';
1773        $result = $parser->parse($sql);
1774        self::assertNotNull($result);
1775        self::assertSame('INTEGER', $result->columnTypes['a']);
1776    }
1777
1778    public function testExtractColumnTypeMultiWordType(): void
1779    {
1780        $parser = new SqliteSchemaParser();
1781        $sql = 'CREATE TABLE t (a DOUBLE PRECISION)';
1782        $result = $parser->parse($sql);
1783        self::assertNotNull($result);
1784        self::assertSame(ColumnTypeFamily::FLOAT, $result->typedColumns['a']->family);
1785    }
1786
1787    public function testExtractColumnTypeWithParensAndKeyword(): void
1788    {
1789        $parser = new SqliteSchemaParser();
1790        $sql = 'CREATE TABLE t (a VARCHAR(255) NOT NULL)';
1791        $result = $parser->parse($sql);
1792        self::assertNotNull($result);
1793        self::assertSame('VARCHAR(255)', $result->columnTypes['a']);
1794    }
1795
1796    public function testParseLowercaseCreateTable(): void
1797    {
1798        $parser = new SqliteSchemaParser();
1799        $sql = 'create table t (a integer primary key)';
1800        $result = $parser->parse($sql);
1801        self::assertNotNull($result);
1802        self::assertContains('a', $result->primaryKeys);
1803    }
1804
1805    public function testParseCaseInsensitivePrimaryKeyConstraint(): void
1806    {
1807        $parser = new SqliteSchemaParser();
1808        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, primary key (a))';
1809        $result = $parser->parse($sql);
1810        self::assertNotNull($result);
1811        self::assertContains('a', $result->primaryKeys);
1812    }
1813
1814    public function testParseCaseInsensitiveUniqueConstraint(): void
1815    {
1816        $parser = new SqliteSchemaParser();
1817        $sql = 'CREATE TABLE t (a INTEGER, unique (a))';
1818        $result = $parser->parse($sql);
1819        self::assertNotNull($result);
1820        self::assertCount(1, $result->uniqueConstraints);
1821    }
1822
1823    public function testParseCaseInsensitiveForeignKey(): void
1824    {
1825        $parser = new SqliteSchemaParser();
1826        $sql = 'CREATE TABLE t (a INTEGER, foreign key (a) references other(id))';
1827        $result = $parser->parse($sql);
1828        self::assertNotNull($result);
1829        self::assertSame(['a'], $result->columns);
1830    }
1831
1832    public function testParseCaseInsensitiveCheck(): void
1833    {
1834        $parser = new SqliteSchemaParser();
1835        $sql = 'CREATE TABLE t (a INTEGER, check (a > 0))';
1836        $result = $parser->parse($sql);
1837        self::assertNotNull($result);
1838        self::assertSame(['a'], $result->columns);
1839    }
1840
1841    public function testColumnWithNotNullHasFlag(): void
1842    {
1843        $parser = new SqliteSchemaParser();
1844        $sql = 'CREATE TABLE t (a TEXT NOT NULL)';
1845        $result = $parser->parse($sql);
1846        self::assertNotNull($result);
1847        self::assertContains('a', $result->notNullColumns);
1848    }
1849
1850    public function testColumnWithBoolCastPrimaryKey(): void
1851    {
1852        $parser = new SqliteSchemaParser();
1853        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT UNIQUE)';
1854        $result = $parser->parse($sql);
1855        self::assertNotNull($result);
1856        self::assertContains('a', $result->primaryKeys);
1857        self::assertCount(1, $result->uniqueConstraints);
1858    }
1859
1860    public function testParseColumnNameListWithQuotedNames(): void
1861    {
1862        $parser = new SqliteSchemaParser();
1863        $sql = 'CREATE TABLE t ("a" INTEGER, "b" TEXT, PRIMARY KEY ("a", "b"))';
1864        $result = $parser->parse($sql);
1865        self::assertNotNull($result);
1866        self::assertSame(['a', 'b'], $result->primaryKeys);
1867    }
1868
1869    public function testParseColumnNameListTrimsWhitespace(): void
1870    {
1871        $parser = new SqliteSchemaParser();
1872        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, PRIMARY KEY ( a , b ))';
1873        $result = $parser->parse($sql);
1874        self::assertNotNull($result);
1875        self::assertSame(['a', 'b'], $result->primaryKeys);
1876    }
1877
1878    public function testExtractColumnTypeOnKeywordInNonTypeList(): void
1879    {
1880        $parser = new SqliteSchemaParser();
1881        $sql = 'CREATE TABLE t (a ON DELETE CASCADE)';
1882        $result = $parser->parse($sql);
1883        self::assertNotNull($result);
1884        self::assertArrayNotHasKey('a', $result->columnTypes);
1885    }
1886
1887    public function testExtractColumnTypeForeignKeywordExcluded(): void
1888    {
1889        $parser = new SqliteSchemaParser();
1890        $sql = 'CREATE TABLE t (a FOREIGN INTEGER)';
1891        $result = $parser->parse($sql);
1892        self::assertNotNull($result);
1893        self::assertArrayNotHasKey('a', $result->columnTypes);
1894    }
1895
1896    public function testSplitColumnDefinitionsDoubleQuoteEscaped(): void
1897    {
1898        $parser = new SqliteSchemaParser();
1899        $sql = 'CREATE TABLE t ("col""name" INTEGER, b TEXT)';
1900        $result = $parser->parse($sql);
1901        self::assertNotNull($result);
1902        self::assertSame(['col"name', 'b'], $result->columns);
1903    }
1904
1905    public function testParseColumnNameListBacktickQuoted(): void
1906    {
1907        $parser = new SqliteSchemaParser();
1908        $sql = 'CREATE TABLE t (`a` INTEGER, `b` TEXT, PRIMARY KEY (`a`))';
1909        $result = $parser->parse($sql);
1910        self::assertNotNull($result);
1911        self::assertSame(['a'], $result->primaryKeys);
1912    }
1913
1914    public function testParseColumnNameListSkipsEmpty(): void
1915    {
1916        $parser = new SqliteSchemaParser();
1917        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, UNIQUE (a, , b))';
1918        $result = $parser->parse($sql);
1919        self::assertNotNull($result);
1920    }
1921
1922    public function testMultipleConstraintsProcessedInOrder(): void
1923    {
1924        $parser = new SqliteSchemaParser();
1925        $sql = 'CREATE TABLE t (a INTEGER, b TEXT, c REAL, PRIMARY KEY (a), UNIQUE (b), CHECK (c > 0), FOREIGN KEY (a) REFERENCES other(id))';
1926        $result = $parser->parse($sql);
1927        self::assertNotNull($result);
1928        self::assertSame(['a', 'b', 'c'], $result->columns);
1929        self::assertContains('a', $result->primaryKeys);
1930        self::assertCount(1, $result->uniqueConstraints);
1931    }
1932
1933    public function testMapToColumnTypeFamilyAllIntegerTypes(): void
1934    {
1935        $parser = new SqliteSchemaParser();
1936        $result0 = $parser->parse('CREATE TABLE t0 (a INT)');
1937        self::assertNotNull($result0);
1938        self::assertSame(ColumnTypeFamily::INTEGER, $result0->typedColumns['a']->family);
1939        $result1 = $parser->parse('CREATE TABLE t1 (a TINYINT)');
1940        self::assertNotNull($result1);
1941        self::assertSame(ColumnTypeFamily::INTEGER, $result1->typedColumns['a']->family);
1942        $result2 = $parser->parse('CREATE TABLE t2 (a SMALLINT)');
1943        self::assertNotNull($result2);
1944        self::assertSame(ColumnTypeFamily::INTEGER, $result2->typedColumns['a']->family);
1945        $result3 = $parser->parse('CREATE TABLE t3 (a MEDIUMINT)');
1946        self::assertNotNull($result3);
1947        self::assertSame(ColumnTypeFamily::INTEGER, $result3->typedColumns['a']->family);
1948        $result4 = $parser->parse('CREATE TABLE t4 (a BIGINT)');
1949        self::assertNotNull($result4);
1950        self::assertSame(ColumnTypeFamily::INTEGER, $result4->typedColumns['a']->family);
1951        $result5 = $parser->parse('CREATE TABLE t5 (a INT2)');
1952        self::assertNotNull($result5);
1953        self::assertSame(ColumnTypeFamily::INTEGER, $result5->typedColumns['a']->family);
1954        $result6 = $parser->parse('CREATE TABLE t6 (a INT8)');
1955        self::assertNotNull($result6);
1956        self::assertSame(ColumnTypeFamily::INTEGER, $result6->typedColumns['a']->family);
1957    }
1958
1959    public function testMapToColumnTypeFamilyAllStringTypes(): void
1960    {
1961        $parser = new SqliteSchemaParser();
1962        $result0 = $parser->parse('CREATE TABLE t0 (a CHAR)');
1963        self::assertNotNull($result0);
1964        self::assertSame(ColumnTypeFamily::STRING, $result0->typedColumns['a']->family);
1965        $result1 = $parser->parse('CREATE TABLE t1 (a CHARACTER)');
1966        self::assertNotNull($result1);
1967        self::assertSame(ColumnTypeFamily::STRING, $result1->typedColumns['a']->family);
1968        $result2 = $parser->parse('CREATE TABLE t2 (a VARCHAR)');
1969        self::assertNotNull($result2);
1970        self::assertSame(ColumnTypeFamily::STRING, $result2->typedColumns['a']->family);
1971        $result3 = $parser->parse('CREATE TABLE t3 (a NCHAR)');
1972        self::assertNotNull($result3);
1973        self::assertSame(ColumnTypeFamily::STRING, $result3->typedColumns['a']->family);
1974        $result4 = $parser->parse('CREATE TABLE t4 (a NVARCHAR)');
1975        self::assertNotNull($result4);
1976        self::assertSame(ColumnTypeFamily::STRING, $result4->typedColumns['a']->family);
1977    }
1978
1979    public function testMapToColumnTypeFamilyClobIsText(): void
1980    {
1981        $parser = new SqliteSchemaParser();
1982        $sql = 'CREATE TABLE t (a CLOB)';
1983        $result = $parser->parse($sql);
1984        self::assertNotNull($result);
1985        self::assertSame(ColumnTypeFamily::TEXT, $result->typedColumns['a']->family);
1986    }
1987
1988    public function testMapToColumnTypeFamilyBlobIsBinary(): void
1989    {
1990        $parser = new SqliteSchemaParser();
1991        $sql = 'CREATE TABLE t (a BLOB)';
1992        $result = $parser->parse($sql);
1993        self::assertNotNull($result);
1994        self::assertSame(ColumnTypeFamily::BINARY, $result->typedColumns['a']->family);
1995    }
1996
1997    public function testMapToColumnTypeFamilyBooleanTypes(): void
1998    {
1999        $parser = new SqliteSchemaParser();
2000        $sql = 'CREATE TABLE t (a BOOL)';
2001        $result = $parser->parse($sql);
2002        self::assertNotNull($result);
2003        self::assertSame(ColumnTypeFamily::BOOLEAN, $result->typedColumns['a']->family);
2004    }
2005
2006    public function testMapToColumnTypeFamilyDateTimeTypes(): void
2007    {
2008        $parser = new SqliteSchemaParser();
2009        $result0 = $parser->parse('CREATE TABLE t0 (a DATE)');
2010        self::assertNotNull($result0);
2011        self::assertSame(ColumnTypeFamily::DATE, $result0->typedColumns['a']->family);
2012        $result1 = $parser->parse('CREATE TABLE t1 (a TIME)');
2013        self::assertNotNull($result1);
2014        self::assertSame(ColumnTypeFamily::TIME, $result1->typedColumns['a']->family);
2015        $result2 = $parser->parse('CREATE TABLE t2 (a DATETIME)');
2016        self::assertNotNull($result2);
2017        self::assertSame(ColumnTypeFamily::DATETIME, $result2->typedColumns['a']->family);
2018        $result3 = $parser->parse('CREATE TABLE t3 (a TIMESTAMP)');
2019        self::assertNotNull($result3);
2020        self::assertSame(ColumnTypeFamily::TIMESTAMP, $result3->typedColumns['a']->family);
2021    }
2022
2023    public function testMapToColumnTypeFamilyJsonType(): void
2024    {
2025        $parser = new SqliteSchemaParser();
2026        $sql = 'CREATE TABLE t (a JSON)';
2027        $result = $parser->parse($sql);
2028        self::assertNotNull($result);
2029        self::assertSame(ColumnTypeFamily::JSON, $result->typedColumns['a']->family);
2030    }
2031
2032    public function testMapToColumnTypeFamilyDefaultsToNumericAffinity(): void
2033    {
2034        $parser = new SqliteSchemaParser();
2035        $sql = 'CREATE TABLE t (a FOOBAR)';
2036        $result = $parser->parse($sql);
2037        self::assertNotNull($result);
2038        self::assertSame(ColumnTypeFamily::DECIMAL, $result->typedColumns['a']->family);
2039    }
2040
2041    public function testUniqueConstraintWithEmptyColumnsNotStored(): void
2042    {
2043        $parser = new SqliteSchemaParser();
2044        $sql = 'CREATE TABLE t (a INTEGER, UNIQUE ())';
2045        $result = $parser->parse($sql);
2046        self::assertNotNull($result);
2047        self::assertSame([], $result->uniqueConstraints);
2048    }
2049
2050    public function testColumnTypeRestIsTrimmed(): void
2051    {
2052        $parser = new SqliteSchemaParser();
2053        $sql = 'CREATE TABLE t (a   INTEGER  )';
2054        $result = $parser->parse($sql);
2055        self::assertNotNull($result);
2056        self::assertSame('INTEGER', $result->columnTypes['a']);
2057    }
2058
2059    public function testPrimaryKeysArrayIsNumericallyIndexed(): void
2060    {
2061        $parser = new SqliteSchemaParser();
2062        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT, c TEXT, PRIMARY KEY (a))';
2063        $result = $parser->parse($sql);
2064        self::assertNotNull($result);
2065        self::assertSame([0], array_keys($result->primaryKeys));
2066    }
2067
2068    public function testNotNullColumnsArrayIsNumericallyIndexed(): void
2069    {
2070        $parser = new SqliteSchemaParser();
2071        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT NOT NULL)';
2072        $result = $parser->parse($sql);
2073        self::assertNotNull($result);
2074        self::assertSame([0, 1], array_keys($result->notNullColumns));
2075    }
2076
2077    public function testNotNullColumnsDeduplicated(): void
2078    {
2079        $parser = new SqliteSchemaParser();
2080        $sql = 'CREATE TABLE t (a INTEGER PRIMARY KEY NOT NULL, b TEXT)';
2081        $result = $parser->parse($sql);
2082        self::assertNotNull($result);
2083        self::assertSame(['a'], $result->notNullColumns);
2084    }
2085
2086    public function testConstraintFollowedByColumnParsesAll(): void
2087    {
2088        $parser = new SqliteSchemaParser();
2089        $sql = 'CREATE TABLE t (a INTEGER, FOREIGN KEY (a) REFERENCES other(id), b TEXT NOT NULL)';
2090        $result = $parser->parse($sql);
2091        self::assertNotNull($result);
2092        self::assertSame(['a', 'b'], $result->columns);
2093        self::assertContains('b', $result->notNullColumns);
2094    }
2095
2096    public function testConstraintNamedPrimaryKeyFollowedByColumn(): void
2097    {
2098        $parser = new SqliteSchemaParser();
2099        $sql = 'CREATE TABLE t (a INTEGER, CONSTRAINT pk PRIMARY KEY (a), b TEXT)';
2100        $result = $parser->parse($sql);
2101        self::assertNotNull($result);
2102        self::assertSame(['a', 'b'], $result->columns);
2103        self::assertSame(['a'], $result->primaryKeys);
2104    }
2105
2106    public function testConstraintNamedUniqueFollowedByColumn(): void
2107    {
2108        $parser = new SqliteSchemaParser();
2109        $sql = 'CREATE TABLE t (a INTEGER, CONSTRAINT uq UNIQUE (a), b TEXT)';
2110        $result = $parser->parse($sql);
2111        self::assertNotNull($result);
2112        self::assertSame(['a', 'b'], $result->columns);
2113        self::assertNotEmpty($result->uniqueConstraints);
2114    }
2115
2116    public function testMultilineCreateTableParsed(): void
2117    {
2118        $parser = new SqliteSchemaParser();
2119        $sql = "CREATE TABLE t (\n  id INTEGER PRIMARY KEY,\n  name TEXT NOT NULL\n)";
2120        $result = $parser->parse($sql);
2121        self::assertNotNull($result);
2122        self::assertSame(['id', 'name'], $result->columns);
2123    }
2124
2125    public function testParseReturnsNullForTrailingContent(): void
2126    {
2127        $parser = new SqliteSchemaParser();
2128        $result = $parser->parse('CREATE TABLE t (id INTEGER) EXTRA STUFF HERE');
2129        self::assertNull($result);
2130    }
2131
2132    public function testParseColumnDefinitionWithQuotedColumnName(): void
2133    {
2134        $parser = new SqliteSchemaParser();
2135        $sql = 'CREATE TABLE t ("col one" TEXT, "col two" INTEGER)';
2136        $result = $parser->parse($sql);
2137        self::assertNotNull($result);
2138        self::assertSame(['col one', 'col two'], $result->columns);
2139        self::assertSame('TEXT', $result->columnTypes['col one']);
2140        self::assertSame('INTEGER', $result->columnTypes['col two']);
2141    }
2142
2143    public function testConstraintKeywordPrefixesRemainColumnNames(): void
2144    {
2145        $parser = new SqliteSchemaParser();
2146        $sql = 'CREATE TABLE bookings (id INT, check_in TEXT, checkout_date TEXT, unique_value TEXT, constraint_name TEXT, foreign_key_id INT)';
2147        $result = $parser->parse($sql);
2148        self::assertNotNull($result);
2149        self::assertSame(['id', 'check_in', 'checkout_date', 'unique_value', 'constraint_name', 'foreign_key_id'], $result->columns);
2150    }
2151}
2152