packages/sql-fixture/tests/Unit/Platform/Sqlite/SqliteSchemaParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Platform\Sqlite;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\Test;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use SqlFixture\Platform\Sqlite\SqliteSchemaParser;
12use SqlFixture\Schema\ColumnDefinition;
13use SqlFixture\Schema\SchemaParseException;
14use SqlFixture\Schema\TableSchema;
15
16#[CoversClass(SqliteSchemaParser::class)]
17#[UsesClass(ColumnDefinition::class)]
18#[UsesClass(TableSchema::class)]
19#[UsesClass(SchemaParseException::class)]
20#[CoversClass(\SqlFixture\Platform\Sqlite\Schema\ColumnParser::class)]
21#[CoversClass(\SqlFixture\Platform\Sqlite\Schema\DefaultExpression::class)]
22#[CoversClass(\SqlFixture\Platform\Sqlite\Schema\DefinitionList::class)]
23#[CoversClass(\SqlFixture\Platform\Sqlite\Schema\TableSyntax::class)]
24#[CoversClass(\SqlFixture\Platform\Sqlite\Schema\TypeDeclaration::class)]
25#[CoversClass(\SqlFixture\Schema\DefinitionSegments::class)]
26#[UsesClass(\SqlFixture\Schema\SchemaParserInterface::class)]
27#[UsesClass(\SqlFixture\Schema\TypeShape::class)]
28#[UsesClass(\SqlFixture\Schema\Exception\InvalidSqlException::class)]
29#[UsesClass(\SqlFixture\Schema\Exception\ExpectedCreateTableException::class)]
30#[UsesClass(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class)]
31final class SqliteSchemaParserTest extends TestCase
32{
33 #[Test]
34 public function testParseSimpleTable(): void
35 {
36 $sql = 'CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)';
37 $schema = (new SqliteSchemaParser())->parse($sql);
38
39 self::assertSame('users', $schema->tableName);
40 self::assertCount(2, $schema->columns);
41 self::assertArrayHasKey('id', $schema->columns);
42 self::assertArrayHasKey('name', $schema->columns);
43 }
44
45 #[Test]
46 public function testParseColumnTypes(): void
47 {
48 $sql = <<<'SQL'
49 CREATE TABLE test (
50 col_int INTEGER,
51 col_text TEXT,
52 col_real REAL,
53 col_blob BLOB
54 )
55 SQL;
56
57 $schema = (new SqliteSchemaParser())->parse($sql);
58
59 self::assertSame('INTEGER', $schema->columns['col_int']->type);
60 self::assertSame('TEXT', $schema->columns['col_text']->type);
61 self::assertSame('REAL', $schema->columns['col_real']->type);
62 self::assertSame('BLOB', $schema->columns['col_blob']->type);
63 }
64
65 #[Test]
66 public function testParseVarcharWithLength(): void
67 {
68 $sql = 'CREATE TABLE test (name VARCHAR(100))';
69 $schema = (new SqliteSchemaParser())->parse($sql);
70
71 self::assertSame('VARCHAR', $schema->columns['name']->type);
72 self::assertSame(100, $schema->columns['name']->length);
73 }
74
75 #[Test]
76 public function testParseDecimalWithPrecisionAndScale(): void
77 {
78 $sql = 'CREATE TABLE test (price DECIMAL(10, 2))';
79 $schema = (new SqliteSchemaParser())->parse($sql);
80
81 self::assertSame('DECIMAL', $schema->columns['price']->type);
82 self::assertSame(10, $schema->columns['price']->precision);
83 self::assertSame(2, $schema->columns['price']->scale);
84 }
85
86 #[Test]
87 public function testParseNotNullConstraint(): void
88 {
89 $sql = 'CREATE TABLE test (id INTEGER NOT NULL, name TEXT)';
90 $schema = (new SqliteSchemaParser())->parse($sql);
91
92 self::assertFalse($schema->columns['id']->nullable);
93 self::assertTrue($schema->columns['name']->nullable);
94 }
95
96 #[Test]
97 public function testParsePrimaryKey(): void
98 {
99 $sql = 'CREATE TABLE test (id INTEGER PRIMARY KEY)';
100 $schema = (new SqliteSchemaParser())->parse($sql);
101
102 self::assertFalse($schema->columns['id']->nullable);
103 }
104
105 #[Test]
106 public function testParsePrimaryKeyAutoincrement(): void
107 {
108 $sql = 'CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT)';
109 $schema = (new SqliteSchemaParser())->parse($sql);
110
111 self::assertTrue($schema->columns['id']->autoIncrement);
112 }
113
114 #[Test]
115 public function testParseTableLevelPrimaryKey(): void
116 {
117 $sql = 'CREATE TABLE test (a INTEGER, b TEXT, PRIMARY KEY (a, b))';
118 $schema = (new SqliteSchemaParser())->parse($sql);
119
120 self::assertSame(['a', 'b'], $schema->primaryKeys);
121 self::assertFalse($schema->columns['a']->nullable);
122 self::assertFalse($schema->columns['b']->nullable);
123 }
124
125 #[Test]
126 public function testParseDefaultString(): void
127 {
128 $sql = "CREATE TABLE test (name TEXT DEFAULT 'default_value')";
129 $schema = (new SqliteSchemaParser())->parse($sql);
130
131 self::assertSame('default_value', $schema->columns['name']->default);
132 }
133
134 #[Test]
135 public function testParseDefaultNull(): void
136 {
137 $sql = 'CREATE TABLE test (name TEXT DEFAULT NULL)';
138 $schema = (new SqliteSchemaParser())->parse($sql);
139
140 self::assertNull($schema->columns['name']->default);
141 }
142
143 #[Test]
144 public function testParseDefaultInteger(): void
145 {
146 $sql = 'CREATE TABLE test (count INTEGER DEFAULT 42)';
147 $schema = (new SqliteSchemaParser())->parse($sql);
148
149 self::assertSame(42, $schema->columns['count']->default);
150 }
151
152 #[Test]
153 public function testParseIfNotExists(): void
154 {
155 $sql = 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY)';
156 $schema = (new SqliteSchemaParser())->parse($sql);
157
158 self::assertSame('users', $schema->tableName);
159 }
160
161 #[Test]
162 public function testParseQuotedTableName(): void
163 {
164 $sql = 'CREATE TABLE "my_table" ("my_column" INTEGER)';
165 $schema = (new SqliteSchemaParser())->parse($sql);
166
167 self::assertSame('my_table', $schema->tableName);
168 self::assertArrayHasKey('my_column', $schema->columns);
169 }
170
171 #[Test]
172 public function testParseMultipleConstraints(): void
173 {
174 $sql = 'CREATE TABLE test (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT)';
175 $schema = (new SqliteSchemaParser())->parse($sql);
176
177 self::assertFalse($schema->columns['id']->nullable);
178 self::assertTrue($schema->columns['id']->autoIncrement);
179 }
180
181 #[Test]
182 public function testParseTypeAffinity(): void
183 {
184 $sql = <<<'SQL'
185 CREATE TABLE test (
186 col_int INT,
187 col_integer INTEGER,
188 col_tinyint TINYINT,
189 col_smallint SMALLINT,
190 col_mediumint MEDIUMINT,
191 col_bigint BIGINT,
192 col_char CHAR(10),
193 col_varchar VARCHAR(255),
194 col_float FLOAT,
195 col_double DOUBLE,
196 col_boolean BOOLEAN,
197 col_date DATE,
198 col_datetime DATETIME
199 )
200 SQL;
201
202 $schema = (new SqliteSchemaParser())->parse($sql);
203
204 self::assertSame('INT', $schema->columns['col_int']->type);
205 self::assertSame('INTEGER', $schema->columns['col_integer']->type);
206 self::assertSame('TINYINT', $schema->columns['col_tinyint']->type);
207 self::assertSame('SMALLINT', $schema->columns['col_smallint']->type);
208 self::assertSame('MEDIUMINT', $schema->columns['col_mediumint']->type);
209 self::assertSame('BIGINT', $schema->columns['col_bigint']->type);
210
211 self::assertSame('CHAR', $schema->columns['col_char']->type);
212 self::assertSame('VARCHAR', $schema->columns['col_varchar']->type);
213
214 self::assertSame('FLOAT', $schema->columns['col_float']->type);
215 self::assertSame('DOUBLE', $schema->columns['col_double']->type);
216
217 self::assertSame('BOOLEAN', $schema->columns['col_boolean']->type);
218 self::assertSame('DATE', $schema->columns['col_date']->type);
219 self::assertSame('DATETIME', $schema->columns['col_datetime']->type);
220 }
221
222 #[Test]
223 public function testParseSkipsTableConstraints(): void
224 {
225 $sql = <<<'SQL'
226 CREATE TABLE test (
227 id INTEGER,
228 name TEXT,
229 FOREIGN KEY (id) REFERENCES other(id),
230 UNIQUE (name),
231 CHECK (id > 0)
232 )
233 SQL;
234
235 $schema = (new SqliteSchemaParser())->parse($sql);
236
237 self::assertCount(2, $schema->columns);
238 self::assertArrayHasKey('id', $schema->columns);
239 self::assertArrayHasKey('name', $schema->columns);
240 }
241
242 #[Test]
243 public function testParseComments(): void
244 {
245 $sql = <<<'SQL'
246 -- This is a comment
247 CREATE TABLE test (
248 id INTEGER -- inline comment
249 )
250 SQL;
251
252 $schema = (new SqliteSchemaParser())->parse($sql);
253
254 self::assertSame('test', $schema->tableName);
255 }
256
257 #[Test]
258 public function testThrowsExceptionForInvalidSql(): void
259 {
260 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
261 (new SqliteSchemaParser())->parse('NOT A VALID SQL STATEMENT');
262 }
263
264 #[Test]
265 public function testThrowsExceptionForEmptyTable(): void
266 {
267 $this->expectException(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class);
268 (new SqliteSchemaParser())->parse('CREATE TABLE test ()');
269 }
270
271 #[Test]
272 public function testParseDefaultExpression(): void
273 {
274 $sql = 'CREATE TABLE test (created_at TEXT DEFAULT CURRENT_TIMESTAMP)';
275 $schema = (new SqliteSchemaParser())->parse($sql);
276
277 self::assertSame('CURRENT_TIMESTAMP', $schema->columns['created_at']->default);
278 }
279
280 #[Test]
281 public function testParseGeneratedColumn(): void
282 {
283 $sql = 'CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER AS (a + b))';
284 $schema = (new SqliteSchemaParser())->parse($sql);
285
286 self::assertTrue($schema->columns['c']->generated);
287 self::assertFalse($schema->columns['a']->generated);
288 }
289
290 #[Test]
291 public function testParseDefaultBooleanTrue(): void
292 {
293 $sql = 'CREATE TABLE test (active INTEGER DEFAULT TRUE)';
294 $schema = (new SqliteSchemaParser())->parse($sql);
295
296 self::assertTrue($schema->columns['active']->default);
297 }
298
299 #[Test]
300 public function testParseDefaultBooleanFalse(): void
301 {
302 $sql = 'CREATE TABLE test (active INTEGER DEFAULT FALSE)';
303 $schema = (new SqliteSchemaParser())->parse($sql);
304
305 self::assertFalse($schema->columns['active']->default);
306 }
307
308 #[Test]
309 public function testParseDefaultNumericOne(): void
310 {
311 $sql = 'CREATE TABLE test (flag INTEGER DEFAULT 1)';
312 $schema = (new SqliteSchemaParser())->parse($sql);
313
314 self::assertTrue($schema->columns['flag']->default);
315 }
316
317 #[Test]
318 public function testParseDefaultNumericZero(): void
319 {
320 $sql = 'CREATE TABLE test (flag INTEGER DEFAULT 0)';
321 $schema = (new SqliteSchemaParser())->parse($sql);
322
323 self::assertFalse($schema->columns['flag']->default);
324 }
325
326 #[Test]
327 public function testParseDefaultFloat(): void
328 {
329 $sql = 'CREATE TABLE test (price REAL DEFAULT 9.99)';
330 $schema = (new SqliteSchemaParser())->parse($sql);
331
332 self::assertSame(9.99, $schema->columns['price']->default);
333 }
334
335 #[Test]
336 public function testParseDefaultCurrentDate(): void
337 {
338 $sql = 'CREATE TABLE test (d TEXT DEFAULT CURRENT_DATE)';
339 $schema = (new SqliteSchemaParser())->parse($sql);
340
341 self::assertSame('CURRENT_DATE', $schema->columns['d']->default);
342 }
343
344 #[Test]
345 public function testParseDefaultCurrentTime(): void
346 {
347 $sql = 'CREATE TABLE test (t TEXT DEFAULT CURRENT_TIME)';
348 $schema = (new SqliteSchemaParser())->parse($sql);
349
350 self::assertSame('CURRENT_TIME', $schema->columns['t']->default);
351 }
352
353 #[Test]
354 public function testParseDefaultExpression2(): void
355 {
356 $sql = 'CREATE TABLE test (val INTEGER DEFAULT (1+2))';
357 $schema = (new SqliteSchemaParser())->parse($sql);
358
359 self::assertSame('(1+2)', $schema->columns['val']->default);
360 }
361
362 #[Test]
363 public function testParseIntegerPrimaryKeyNotAutoincrement(): void
364 {
365 $sql = 'CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT)';
366 $schema = (new SqliteSchemaParser())->parse($sql);
367
368 self::assertFalse($schema->columns['id']->autoIncrement);
369 self::assertFalse($schema->columns['id']->nullable);
370 }
371
372 #[Test]
373 public function testParseIntegerPrimaryKeyAutoincrement(): void
374 {
375 $sql = 'CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)';
376 $schema = (new SqliteSchemaParser())->parse($sql);
377
378 self::assertTrue($schema->columns['id']->autoIncrement);
379 }
380
381 #[Test]
382 public function testParseNonIntegerPrimaryKeyAutoincrement(): void
383 {
384 $sql = 'CREATE TABLE test (code TEXT PRIMARY KEY, name TEXT)';
385 $schema = (new SqliteSchemaParser())->parse($sql);
386
387 self::assertFalse($schema->columns['code']->autoIncrement);
388 self::assertFalse($schema->columns['code']->nullable);
389 }
390
391 #[Test]
392 public function testParseColumnWithNoType(): void
393 {
394 $sql = 'CREATE TABLE test (val)';
395 $schema = (new SqliteSchemaParser())->parse($sql);
396
397 self::assertSame('BLOB', $schema->columns['val']->type);
398 }
399
400 #[Test]
401 public function testParseBlockComment(): void
402 {
403 $sql = '/* comment */ CREATE TABLE test (id INTEGER)';
404 $schema = (new SqliteSchemaParser())->parse($sql);
405
406 self::assertSame('test', $schema->tableName);
407 }
408
409 #[Test]
410 public function testParseNonGeneratedColumn(): void
411 {
412 $sql = 'CREATE TABLE test (id INTEGER, name TEXT)';
413 $schema = (new SqliteSchemaParser())->parse($sql);
414
415 self::assertFalse($schema->columns['id']->generated);
416 self::assertFalse($schema->columns['name']->generated);
417 }
418
419 #[Test]
420 public function testParseUnsignedAlwaysFalse(): void
421 {
422 $sql = 'CREATE TABLE test (id INTEGER)';
423 $schema = (new SqliteSchemaParser())->parse($sql);
424
425 self::assertFalse($schema->columns['id']->unsigned);
426 }
427
428 #[Test]
429 public function testParseEnumValuesAlwaysNull(): void
430 {
431 $sql = 'CREATE TABLE test (id INTEGER)';
432 $schema = (new SqliteSchemaParser())->parse($sql);
433
434 self::assertNull($schema->columns['id']->enumValues);
435 }
436
437 #[Test]
438 public function testParseConstraintKeyword(): void
439 {
440 $sql = 'CREATE TABLE test (id INTEGER, CONSTRAINT pk PRIMARY KEY (id))';
441 $schema = (new SqliteSchemaParser())->parse($sql);
442
443 self::assertCount(1, $schema->columns);
444 self::assertArrayHasKey('id', $schema->columns);
445 }
446
447 #[Test]
448 public function testParseDefaultWithNotNull(): void
449 {
450 $sql = "CREATE TABLE test (name TEXT NOT NULL DEFAULT 'test_val')";
451 $schema = (new SqliteSchemaParser())->parse($sql);
452
453 self::assertSame('test_val', $schema->columns['name']->default);
454 self::assertFalse($schema->columns['name']->nullable);
455 }
456
457 #[Test]
458 public function testParseSchemaQualifiedTableName(): void
459 {
460 $sql = 'CREATE TABLE main.users (id INTEGER PRIMARY KEY)';
461 $schema = (new SqliteSchemaParser())->parse($sql);
462
463 self::assertSame('users', $schema->tableName);
464 }
465
466 #[Test]
467 public function testParseNoParentheses(): void
468 {
469 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
470 (new SqliteSchemaParser())->parse('CREATE TABLE test');
471 }
472
473 #[Test]
474 public function testParseIsTableConstraintCaseInsensitive(): void
475 {
476 $sql = 'CREATE TABLE test (id INTEGER, foreign key (id) REFERENCES other(id))';
477 $schema = (new SqliteSchemaParser())->parse($sql);
478 self::assertCount(1, $schema->columns);
479 }
480
481 #[Test]
482 public function testParseBacktickedColumn(): void
483 {
484 $sql = 'CREATE TABLE test (`col` INTEGER NOT NULL)';
485 $schema = (new SqliteSchemaParser())->parse($sql);
486 self::assertArrayHasKey('col', $schema->columns);
487 }
488
489 #[Test]
490 public function testParseQuotedPrimaryKeyColumns(): void
491 {
492 $sql = 'CREATE TABLE test ("a" INTEGER, "b" TEXT, PRIMARY KEY ("a", "b"))';
493 $schema = (new SqliteSchemaParser())->parse($sql);
494 self::assertSame(['a', 'b'], $schema->primaryKeys);
495 }
496
497 #[Test]
498 public function testParseDefaultStringWithNotNull(): void
499 {
500 $sql = "CREATE TABLE test (col TEXT DEFAULT 'hello' NOT NULL)";
501 $schema = (new SqliteSchemaParser())->parse($sql);
502 self::assertSame('hello', $schema->columns['col']->default);
503 self::assertFalse($schema->columns['col']->nullable);
504 }
505
506 #[Test]
507 public function testParseDefaultWithCollate(): void
508 {
509 $sql = "CREATE TABLE test (col TEXT DEFAULT 'val' COLLATE NOCASE)";
510 $schema = (new SqliteSchemaParser())->parse($sql);
511 self::assertSame('val', $schema->columns['col']->default);
512 }
513
514 #[Test]
515 public function testParseMultilineWithWhitespace(): void
516 {
517 $sql = ' CREATE TABLE test ( id INTEGER NOT NULL ) ';
518 $schema = (new SqliteSchemaParser())->parse($sql);
519 self::assertSame('test', $schema->tableName);
520 self::assertFalse($schema->columns['id']->nullable);
521 }
522
523 #[Test]
524 public function testParseLengthOnlyColumn(): void
525 {
526 $sql = 'CREATE TABLE test (col VARCHAR(50) NOT NULL)';
527 $schema = (new SqliteSchemaParser())->parse($sql);
528 self::assertSame(50, $schema->columns['col']->length);
529 self::assertNull($schema->columns['col']->precision);
530 }
531
532 #[Test]
533 public function testParseIntegerPrimaryKeyWithoutAutoincrementIsNotAutoIncrement(): void
534 {
535 $sql = 'CREATE TABLE test (id INTEGER PRIMARY KEY)';
536 $schema = (new SqliteSchemaParser())->parse($sql);
537 self::assertFalse($schema->columns['id']->autoIncrement);
538 }
539
540 #[Test]
541 public function testParseTextPrimaryKeyNoAutoIncrement(): void
542 {
543 $sql = 'CREATE TABLE test (code TEXT PRIMARY KEY, name TEXT)';
544 $schema = (new SqliteSchemaParser())->parse($sql);
545 self::assertFalse($schema->columns['code']->autoIncrement);
546 self::assertFalse($schema->columns['code']->nullable);
547 }
548
549 #[Test]
550 public function testParseDefaultInteger42(): void
551 {
552 $sql = 'CREATE TABLE test (count INTEGER DEFAULT 42)';
553 $schema = (new SqliteSchemaParser())->parse($sql);
554 self::assertSame(42, $schema->columns['count']->default);
555 }
556
557 #[Test]
558 public function testParseLowercaseCreateTable(): void
559 {
560 $sql = 'create table users (id integer primary key, name text not null)';
561 $schema = (new SqliteSchemaParser())->parse($sql);
562
563 self::assertSame('users', $schema->tableName);
564 self::assertSame('INTEGER', $schema->columns['id']->type);
565 self::assertSame('TEXT', $schema->columns['name']->type);
566 self::assertFalse($schema->columns['id']->nullable);
567 self::assertFalse($schema->columns['name']->nullable);
568 }
569
570 #[Test]
571 public function testParseLowercaseIfNotExists(): void
572 {
573 $sql = 'create table if not exists users (id integer primary key)';
574 $schema = (new SqliteSchemaParser())->parse($sql);
575
576 self::assertSame('users', $schema->tableName);
577 }
578
579 #[Test]
580 public function testParseLowercaseAutoincrement(): void
581 {
582 $sql = 'create table test (id integer primary key autoincrement)';
583 $schema = (new SqliteSchemaParser())->parse($sql);
584
585 self::assertTrue($schema->columns['id']->autoIncrement);
586 }
587
588 #[Test]
589 public function testParseLowercaseConstraints(): void
590 {
591 $sql = <<<'SQL'
592 create table test (
593 id integer,
594 name text,
595 foreign key (id) references other(id),
596 unique (name),
597 check (id > 0),
598 constraint my_constraint unique (id, name)
599 )
600 SQL;
601
602 $schema = (new SqliteSchemaParser())->parse($sql);
603
604 self::assertCount(2, $schema->columns);
605 }
606
607 #[Test]
608 public function testParseLowercaseTablePrimaryKey(): void
609 {
610 $sql = 'create table test (a integer, b text, primary key (a, b))';
611 $schema = (new SqliteSchemaParser())->parse($sql);
612
613 self::assertSame(['a', 'b'], $schema->primaryKeys);
614 self::assertFalse($schema->columns['a']->nullable);
615 }
616
617 #[Test]
618 public function testParseLowercaseDecimalType(): void
619 {
620 $sql = 'create table test (val decimal(10, 2))';
621 $schema = (new SqliteSchemaParser())->parse($sql);
622
623 self::assertSame('DECIMAL', $schema->columns['val']->type);
624 self::assertSame(10, $schema->columns['val']->precision);
625 self::assertSame(2, $schema->columns['val']->scale);
626 }
627
628 #[Test]
629 public function testParseLowercaseVarcharWithLength(): void
630 {
631 $sql = 'create table test (name varchar(100))';
632 $schema = (new SqliteSchemaParser())->parse($sql);
633
634 self::assertSame('VARCHAR', $schema->columns['name']->type);
635 self::assertSame(100, $schema->columns['name']->length);
636 }
637
638 #[Test]
639 public function testParseLowercaseGeneratedColumn(): void
640 {
641 $sql = 'create table test (a integer, b integer, c integer as (a + b))';
642 $schema = (new SqliteSchemaParser())->parse($sql);
643
644 self::assertTrue($schema->columns['c']->generated);
645 }
646
647 #[Test]
648 public function testParseLowercaseDefaultValues(): void
649 {
650 $sql = <<<'SQL'
651 create table test (
652 col_null text default null,
653 col_true integer default true,
654 col_false integer default false,
655 col_str text default 'hello',
656 col_int integer default 42,
657 col_float real default 9.99,
658 col_ts text default current_timestamp,
659 col_date text default current_date,
660 col_time text default current_time,
661 col_expr integer default (1+2)
662 )
663 SQL;
664
665 $schema = (new SqliteSchemaParser())->parse($sql);
666
667 self::assertNull($schema->columns['col_null']->default);
668 self::assertTrue($schema->columns['col_true']->default);
669 self::assertFalse($schema->columns['col_false']->default);
670 self::assertSame('hello', $schema->columns['col_str']->default);
671 self::assertSame(42, $schema->columns['col_int']->default);
672 self::assertSame(9.99, $schema->columns['col_float']->default);
673 self::assertSame('current_timestamp', $schema->columns['col_ts']->default);
674 self::assertSame('current_date', $schema->columns['col_date']->default);
675 self::assertSame('current_time', $schema->columns['col_time']->default);
676 self::assertSame('(1+2)', $schema->columns['col_expr']->default);
677 }
678
679 #[Test]
680 public function testParseLowercaseDefaultWithTrailingConstraints(): void
681 {
682 $sql = "create table test (name text default 'test' not null collate nocase)";
683 $schema = (new SqliteSchemaParser())->parse($sql);
684
685 self::assertSame('test', $schema->columns['name']->default);
686 self::assertFalse($schema->columns['name']->nullable);
687 }
688
689 #[Test]
690 public function testParseLowercaseTypeAffinity(): void
691 {
692 $sql = <<<'SQL'
693 create table test (
694 col_int int,
695 col_tinyint tinyint,
696 col_smallint smallint,
697 col_bigint bigint,
698 col_float float,
699 col_double double,
700 col_boolean boolean,
701 col_date date,
702 col_datetime datetime,
703 col_blob blob
704 )
705 SQL;
706
707 $schema = (new SqliteSchemaParser())->parse($sql);
708
709 self::assertSame('INT', $schema->columns['col_int']->type);
710 self::assertSame('TINYINT', $schema->columns['col_tinyint']->type);
711 self::assertSame('SMALLINT', $schema->columns['col_smallint']->type);
712 self::assertSame('BIGINT', $schema->columns['col_bigint']->type);
713 self::assertSame('FLOAT', $schema->columns['col_float']->type);
714 self::assertSame('DOUBLE', $schema->columns['col_double']->type);
715 self::assertSame('BOOLEAN', $schema->columns['col_boolean']->type);
716 self::assertSame('DATE', $schema->columns['col_date']->type);
717 self::assertSame('DATETIME', $schema->columns['col_datetime']->type);
718 self::assertSame('BLOB', $schema->columns['col_blob']->type);
719 }
720
721 #[Test]
722 public function testParseWithLeadingTrailingWhitespace(): void
723 {
724 $sql = " \n create table test ( \n id integer , \n name text \n ) \n ";
725 $schema = (new SqliteSchemaParser())->parse($sql);
726
727 self::assertSame('test', $schema->tableName);
728 self::assertCount(2, $schema->columns);
729 }
730
731 #[Test]
732 public function testParseMixedCaseKeywords(): void
733 {
734 $sql = 'Create Table test (id Integer Primary Key, name Varchar(100) Not Null)';
735 $schema = (new SqliteSchemaParser())->parse($sql);
736
737 self::assertSame('test', $schema->tableName);
738 self::assertSame('INTEGER', $schema->columns['id']->type);
739 self::assertSame('VARCHAR', $schema->columns['name']->type);
740 self::assertFalse($schema->columns['id']->nullable);
741 self::assertFalse($schema->columns['name']->nullable);
742 }
743
744 #[Test]
745 public function testParseLowercaseSchemaQualified(): void
746 {
747 $sql = 'create table main.users (id integer primary key)';
748 $schema = (new SqliteSchemaParser())->parse($sql);
749
750 self::assertSame('users', $schema->tableName);
751 }
752
753 #[Test]
754 public function testParseDefaultStringWithCheckConstraint(): void
755 {
756 $sql = "CREATE TABLE test (val TEXT DEFAULT 'hello' CHECK (val <> ''))";
757 $schema = (new SqliteSchemaParser())->parse($sql);
758
759 self::assertSame('hello', $schema->columns['val']->default);
760 }
761
762 #[Test]
763 public function testParseConstraintBeforeColumn(): void
764 {
765 $sql = <<<'SQL'
766 CREATE TABLE test (
767 id INTEGER,
768 PRIMARY KEY (id),
769 name TEXT NOT NULL
770 )
771 SQL;
772
773 $schema = (new SqliteSchemaParser())->parse($sql);
774
775 self::assertCount(2, $schema->columns);
776 self::assertArrayHasKey('id', $schema->columns);
777 self::assertArrayHasKey('name', $schema->columns);
778 self::assertSame('TEXT', $schema->columns['name']->type);
779 self::assertFalse($schema->columns['name']->nullable);
780 }
781
782 #[Test]
783 public function testParseMultipleConstraintsBetweenColumns(): void
784 {
785 $sql = <<<'SQL'
786 CREATE TABLE test (
787 id INTEGER,
788 CONSTRAINT pk PRIMARY KEY (id),
789 UNIQUE (id),
790 name TEXT NOT NULL,
791 email TEXT,
792 CHECK (name <> ''),
793 age INTEGER
794 )
795 SQL;
796
797 $schema = (new SqliteSchemaParser())->parse($sql);
798
799 self::assertCount(4, $schema->columns);
800 self::assertArrayHasKey('name', $schema->columns);
801 self::assertArrayHasKey('email', $schema->columns);
802 self::assertArrayHasKey('age', $schema->columns);
803 self::assertSame('INTEGER', $schema->columns['age']->type);
804 }
805
806 #[Test]
807 public function testParseNonIntegerPrimaryKeyIsNotAutoIncrement(): void
808 {
809 $sql = 'CREATE TABLE test (code TEXT PRIMARY KEY, name TEXT)';
810 $schema = (new SqliteSchemaParser())->parse($sql);
811
812 self::assertFalse($schema->columns['code']->autoIncrement);
813 self::assertSame('TEXT', $schema->columns['code']->type);
814 self::assertFalse($schema->columns['code']->nullable);
815 }
816
817 #[Test]
818 public function testParseDefaultExpressionWithOnlyOpeningParen(): void
819 {
820 $sql = "CREATE TABLE test (val TEXT DEFAULT 'value(test')";
821 $schema = (new SqliteSchemaParser())->parse($sql);
822
823 self::assertSame('value(test', $schema->columns['val']->default);
824 }
825
826 #[Test]
827 public function testParseColumnWithNoTypeReturnsBlobViaExtractType(): void
828 {
829 $sql = 'CREATE TABLE test (col1, col2 INTEGER)';
830 $schema = (new SqliteSchemaParser())->parse($sql);
831
832 self::assertSame('BLOB', $schema->columns['col1']->type);
833 self::assertSame('INTEGER', $schema->columns['col2']->type);
834 }
835
836 #[Test]
837 public function testParseDefaultExpressionWrappedInParens(): void
838 {
839 $sql = 'CREATE TABLE test (val INTEGER DEFAULT (10 * 2))';
840 $schema = (new SqliteSchemaParser())->parse($sql);
841
842 self::assertSame('(10 * 2)', $schema->columns['val']->default);
843 }
844
845 #[Test]
846 public function testParseDefaultExpressionOnlyClosingParen(): void
847 {
848 $sql = "CREATE TABLE test (val TEXT DEFAULT 'test)')";
849 $schema = (new SqliteSchemaParser())->parse($sql);
850
851 self::assertSame('test)', $schema->columns['val']->default);
852 }
853
854 #[Test]
855 public function testParseDefaultCurrentTimestampUpperCase(): void
856 {
857 $sql = 'CREATE TABLE test (ts TEXT DEFAULT CURRENT_TIMESTAMP)';
858 $schema = (new SqliteSchemaParser())->parse($sql);
859
860 self::assertSame('CURRENT_TIMESTAMP', $schema->columns['ts']->default);
861 }
862
863 #[Test]
864 public function testParseDefaultCurrentTimestampNotPartOfLargerWord(): void
865 {
866 $sql = "CREATE TABLE test (ts TEXT DEFAULT 'NOT_CURRENT_TIMESTAMP_EXTRA')";
867 $schema = (new SqliteSchemaParser())->parse($sql);
868
869 self::assertSame('NOT_CURRENT_TIMESTAMP_EXTRA', $schema->columns['ts']->default);
870 }
871
872 #[Test]
873 public function testParseTrimsDefinitionsInSplitColumnDefinitions(): void
874 {
875 $sql = 'CREATE TABLE test ( id INTEGER , name TEXT )';
876 $schema = (new SqliteSchemaParser())->parse($sql);
877
878 self::assertCount(2, $schema->columns);
879 self::assertArrayHasKey('id', $schema->columns);
880 self::assertArrayHasKey('name', $schema->columns);
881 }
882
883 #[Test]
884 public function testParseColumnDefinitionTrimsRest(): void
885 {
886 $sql = 'CREATE TABLE test (id INTEGER NOT NULL)';
887 $schema = (new SqliteSchemaParser())->parse($sql);
888
889 self::assertSame('INTEGER', $schema->columns['id']->type);
890 self::assertFalse($schema->columns['id']->nullable);
891 }
892
893 #[Test]
894 public function testParseDefaultTrimsValue(): void
895 {
896 $sql = "CREATE TABLE test (val TEXT DEFAULT 'trimmed' NOT NULL)";
897 $schema = (new SqliteSchemaParser())->parse($sql);
898
899 self::assertSame('trimmed', $schema->columns['val']->default);
900 self::assertFalse($schema->columns['val']->nullable);
901 }
902
903 #[Test]
904 public function testParseIsTableConstraintWithLeadingWhitespace(): void
905 {
906 $sql = "CREATE TABLE test (id INTEGER, \n PRIMARY KEY (id))";
907 $schema = (new SqliteSchemaParser())->parse($sql);
908
909 self::assertCount(1, $schema->columns);
910 }
911
912 #[Test]
913 public function testParseColumnParensEqualStartReturnsNull(): void
914 {
915 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
916 (new SqliteSchemaParser())->parse('CREATE TABLE test )( ');
917 }
918
919 #[Test]
920 public function testParseIntegerPrimaryKeyWithTextPrimaryKeyElseBranch(): void
921 {
922 $sql = <<<'SQL'
923 CREATE TABLE test (
924 id INTEGER PRIMARY KEY AUTOINCREMENT,
925 code TEXT PRIMARY KEY,
926 name VARCHAR(100)
927 )
928 SQL;
929
930 $schema = (new SqliteSchemaParser())->parse($sql);
931
932 self::assertTrue($schema->columns['id']->autoIncrement);
933 self::assertFalse($schema->columns['code']->autoIncrement);
934 }
935
936 #[Test]
937 public function testParseDefaultStringWithDoubleQuotes(): void
938 {
939 $sql = 'CREATE TABLE test (name TEXT DEFAULT "hello_world")';
940 $schema = (new SqliteSchemaParser())->parse($sql);
941
942 self::assertSame('hello_world', $schema->columns['name']->default);
943 }
944
945 #[Test]
946 public function testParseDefaultReferencesConstraint(): void
947 {
948 $sql = 'CREATE TABLE test (user_id INTEGER DEFAULT 1 REFERENCES users(id))';
949 $schema = (new SqliteSchemaParser())->parse($sql);
950
951 self::assertTrue($schema->columns['user_id']->default);
952 }
953
954 #[Test]
955 public function testParseDefaultWithGeneratedKeyword(): void
956 {
957 $sql = 'CREATE TABLE test (val INTEGER DEFAULT 5, gen INTEGER AS (val * 2))';
958 $schema = (new SqliteSchemaParser())->parse($sql);
959
960 self::assertSame(5, $schema->columns['val']->default);
961 self::assertTrue($schema->columns['gen']->generated);
962 }
963
964 #[Test]
965 public function testParseNormalizeSqlRemovesBlockComments(): void
966 {
967 $sql = 'CREATE TABLE /* block comment */ test (id /* another */ INTEGER)';
968 $schema = (new SqliteSchemaParser())->parse($sql);
969
970 self::assertSame('test', $schema->tableName);
971 self::assertSame('INTEGER', $schema->columns['id']->type);
972 }
973
974 #[Test]
975 public function testParseNormalizeSqlTrimsWhitespace(): void
976 {
977 $sql = "\n\n CREATE TABLE test (id INTEGER) \n\n";
978 $schema = (new SqliteSchemaParser())->parse($sql);
979
980 self::assertSame('test', $schema->tableName);
981 }
982}
983