packages/sql-fixture/tests/Unit/Platform/PostgreSql/PostgreSqlSchemaParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Platform\PostgreSql;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\Test;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use SqlFixture\Platform\PostgreSql\PostgreSqlSchemaParser;
12use SqlFixture\Schema\ColumnDefinition;
13use SqlFixture\Schema\SchemaParseException;
14use SqlFixture\Schema\TableSchema;
15
16#[CoversClass(PostgreSqlSchemaParser::class)]
17#[UsesClass(ColumnDefinition::class)]
18#[UsesClass(TableSchema::class)]
19#[UsesClass(SchemaParseException::class)]
20#[CoversClass(\SqlFixture\Platform\PostgreSql\Schema\ColumnParser::class)]
21#[CoversClass(\SqlFixture\Platform\PostgreSql\Schema\DefaultExpression::class)]
22#[CoversClass(\SqlFixture\Platform\PostgreSql\Schema\DefinitionList::class)]
23#[CoversClass(\SqlFixture\Platform\PostgreSql\Schema\TableSyntax::class)]
24#[CoversClass(\SqlFixture\Platform\PostgreSql\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 PostgreSqlSchemaParserTest 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 PostgreSqlSchemaParser())->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_bigint BIGINT,
52 col_smallint SMALLINT,
53 col_text TEXT,
54 col_boolean BOOLEAN,
55 col_real REAL,
56 col_double DOUBLE PRECISION,
57 col_date DATE,
58 col_timestamp TIMESTAMP,
59 col_timestamptz TIMESTAMPTZ,
60 col_uuid UUID,
61 col_jsonb JSONB,
62 col_bytea BYTEA,
63 col_inet INET
64 )
65 SQL;
66
67 $schema = (new PostgreSqlSchemaParser())->parse($sql);
68
69 self::assertSame('INTEGER', $schema->columns['col_int']->type);
70 self::assertSame('BIGINT', $schema->columns['col_bigint']->type);
71 self::assertSame('SMALLINT', $schema->columns['col_smallint']->type);
72 self::assertSame('TEXT', $schema->columns['col_text']->type);
73 self::assertSame('BOOLEAN', $schema->columns['col_boolean']->type);
74 self::assertSame('REAL', $schema->columns['col_real']->type);
75 self::assertSame('DOUBLE PRECISION', $schema->columns['col_double']->type);
76 self::assertSame('DATE', $schema->columns['col_date']->type);
77 self::assertSame('TIMESTAMP', $schema->columns['col_timestamp']->type);
78 self::assertSame('TIMESTAMPTZ', $schema->columns['col_timestamptz']->type);
79 self::assertSame('UUID', $schema->columns['col_uuid']->type);
80 self::assertSame('JSONB', $schema->columns['col_jsonb']->type);
81 self::assertSame('BYTEA', $schema->columns['col_bytea']->type);
82 self::assertSame('INET', $schema->columns['col_inet']->type);
83 }
84
85 #[Test]
86 public function testParseSerialType(): void
87 {
88 $sql = 'CREATE TABLE test (id SERIAL PRIMARY KEY, name TEXT)';
89 $schema = (new PostgreSqlSchemaParser())->parse($sql);
90
91 self::assertSame('INTEGER', $schema->columns['id']->type);
92 self::assertTrue($schema->columns['id']->autoIncrement);
93 }
94
95 #[Test]
96 public function testParseBigSerialType(): void
97 {
98 $sql = 'CREATE TABLE test (id BIGSERIAL PRIMARY KEY)';
99 $schema = (new PostgreSqlSchemaParser())->parse($sql);
100
101 self::assertSame('BIGINT', $schema->columns['id']->type);
102 self::assertTrue($schema->columns['id']->autoIncrement);
103 }
104
105 #[Test]
106 public function testParseSmallSerialType(): void
107 {
108 $sql = 'CREATE TABLE test (id SMALLSERIAL PRIMARY KEY)';
109 $schema = (new PostgreSqlSchemaParser())->parse($sql);
110
111 self::assertSame('SMALLINT', $schema->columns['id']->type);
112 self::assertTrue($schema->columns['id']->autoIncrement);
113 }
114
115 #[Test]
116 public function testParseVarcharWithLength(): void
117 {
118 $sql = 'CREATE TABLE test (name VARCHAR(100))';
119 $schema = (new PostgreSqlSchemaParser())->parse($sql);
120
121 self::assertSame('VARCHAR', $schema->columns['name']->type);
122 self::assertSame(100, $schema->columns['name']->length);
123 }
124
125 #[Test]
126 public function testParseNumericWithPrecisionAndScale(): void
127 {
128 $sql = 'CREATE TABLE test (price NUMERIC(10, 2))';
129 $schema = (new PostgreSqlSchemaParser())->parse($sql);
130
131 self::assertSame('NUMERIC', $schema->columns['price']->type);
132 self::assertSame(10, $schema->columns['price']->precision);
133 self::assertSame(2, $schema->columns['price']->scale);
134 }
135
136 #[Test]
137 public function testParseNotNullConstraint(): void
138 {
139 $sql = 'CREATE TABLE test (id INTEGER NOT NULL, name TEXT)';
140 $schema = (new PostgreSqlSchemaParser())->parse($sql);
141
142 self::assertFalse($schema->columns['id']->nullable);
143 self::assertTrue($schema->columns['name']->nullable);
144 }
145
146 #[Test]
147 public function testParsePrimaryKey(): void
148 {
149 $sql = 'CREATE TABLE test (id INTEGER PRIMARY KEY)';
150 $schema = (new PostgreSqlSchemaParser())->parse($sql);
151
152 self::assertFalse($schema->columns['id']->nullable);
153 }
154
155 #[Test]
156 public function testParseTableLevelPrimaryKey(): void
157 {
158 $sql = 'CREATE TABLE test (a INTEGER, b TEXT, PRIMARY KEY (a, b))';
159 $schema = (new PostgreSqlSchemaParser())->parse($sql);
160
161 self::assertSame(['a', 'b'], $schema->primaryKeys);
162 self::assertFalse($schema->columns['a']->nullable);
163 self::assertFalse($schema->columns['b']->nullable);
164 }
165
166 #[Test]
167 public function testParseDefaultString(): void
168 {
169 $sql = "CREATE TABLE test (name TEXT DEFAULT 'default_value')";
170 $schema = (new PostgreSqlSchemaParser())->parse($sql);
171
172 self::assertSame('default_value', $schema->columns['name']->default);
173 }
174
175 #[Test]
176 public function testParseDefaultNull(): void
177 {
178 $sql = 'CREATE TABLE test (name TEXT DEFAULT NULL)';
179 $schema = (new PostgreSqlSchemaParser())->parse($sql);
180
181 self::assertNull($schema->columns['name']->default);
182 }
183
184 #[Test]
185 public function testParseDefaultInteger(): void
186 {
187 $sql = 'CREATE TABLE test (count INTEGER DEFAULT 42)';
188 $schema = (new PostgreSqlSchemaParser())->parse($sql);
189
190 self::assertSame(42, $schema->columns['count']->default);
191 }
192
193 #[Test]
194 public function testParseDefaultBoolean(): void
195 {
196 $sql = 'CREATE TABLE test (active BOOLEAN DEFAULT TRUE)';
197 $schema = (new PostgreSqlSchemaParser())->parse($sql);
198
199 self::assertTrue($schema->columns['active']->default);
200 }
201
202 #[Test]
203 public function testParseDefaultFunctionCall(): void
204 {
205 $sql = 'CREATE TABLE test (id UUID DEFAULT gen_random_uuid())';
206 $schema = (new PostgreSqlSchemaParser())->parse($sql);
207
208 self::assertSame('gen_random_uuid()', $schema->columns['id']->default);
209 }
210
211 #[Test]
212 public function testParseDefaultTypeCast(): void
213 {
214 $sql = "CREATE TABLE test (data JSONB DEFAULT '{}'::jsonb)";
215 $schema = (new PostgreSqlSchemaParser())->parse($sql);
216
217 self::assertSame("'{}'::jsonb", $schema->columns['data']->default);
218 }
219
220 #[Test]
221 public function testParseIfNotExists(): void
222 {
223 $sql = 'CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY)';
224 $schema = (new PostgreSqlSchemaParser())->parse($sql);
225
226 self::assertSame('users', $schema->tableName);
227 }
228
229 #[Test]
230 public function testParseQuotedTableName(): void
231 {
232 $sql = 'CREATE TABLE "my_table" ("my_column" INTEGER)';
233 $schema = (new PostgreSqlSchemaParser())->parse($sql);
234
235 self::assertSame('my_table', $schema->tableName);
236 self::assertArrayHasKey('my_column', $schema->columns);
237 }
238
239 #[Test]
240 public function testParseSchemaQualifiedTableName(): void
241 {
242 $sql = 'CREATE TABLE public.users (id INTEGER PRIMARY KEY)';
243 $schema = (new PostgreSqlSchemaParser())->parse($sql);
244
245 self::assertSame('users', $schema->tableName);
246 }
247
248 #[Test]
249 public function testParseSkipsTableConstraints(): void
250 {
251 $sql = <<<'SQL'
252 CREATE TABLE test (
253 id INTEGER,
254 name TEXT,
255 FOREIGN KEY (id) REFERENCES other(id),
256 UNIQUE (name),
257 CHECK (id > 0),
258 CONSTRAINT my_constraint UNIQUE (id, name)
259 )
260 SQL;
261
262 $schema = (new PostgreSqlSchemaParser())->parse($sql);
263
264 self::assertCount(2, $schema->columns);
265 self::assertArrayHasKey('id', $schema->columns);
266 self::assertArrayHasKey('name', $schema->columns);
267 }
268
269 #[Test]
270 public function testParseComments(): void
271 {
272 $sql = <<<'SQL'
273 -- This is a comment
274 CREATE TABLE test (
275 id INTEGER -- inline comment
276 )
277 SQL;
278
279 $schema = (new PostgreSqlSchemaParser())->parse($sql);
280
281 self::assertSame('test', $schema->tableName);
282 }
283
284 #[Test]
285 public function testThrowsExceptionForInvalidSql(): void
286 {
287 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
288 (new PostgreSqlSchemaParser())->parse('NOT A VALID SQL STATEMENT');
289 }
290
291 #[Test]
292 public function testThrowsExceptionForEmptyTable(): void
293 {
294 $this->expectException(\SqlFixture\Schema\Exception\MissingColumnDefinitionsException::class);
295 (new PostgreSqlSchemaParser())->parse('CREATE TABLE test ()');
296 }
297
298 #[Test]
299 public function testParseUnsignedAlwaysFalse(): void
300 {
301 $sql = 'CREATE TABLE test (id INTEGER, price NUMERIC(10, 2))';
302 $schema = (new PostgreSqlSchemaParser())->parse($sql);
303
304 self::assertFalse($schema->columns['id']->unsigned);
305 self::assertFalse($schema->columns['price']->unsigned);
306 }
307
308 #[Test]
309 public function testParseGeneratedColumn(): void
310 {
311 $sql = 'CREATE TABLE test (a INTEGER, b INTEGER, c INTEGER GENERATED ALWAYS AS (a + b) STORED)';
312 $schema = (new PostgreSqlSchemaParser())->parse($sql);
313
314 self::assertTrue($schema->columns['c']->generated);
315 }
316
317 #[Test]
318 public function testParseArrayType(): void
319 {
320 $sql = 'CREATE TABLE test (tags TEXT[])';
321 $schema = (new PostgreSqlSchemaParser())->parse($sql);
322
323 self::assertSame('TEXT_ARRAY', $schema->columns['tags']->type);
324 }
325
326 #[Test]
327 public function testParseIntegerArrayType(): void
328 {
329 $sql = 'CREATE TABLE test (ids INTEGER[])';
330 $schema = (new PostgreSqlSchemaParser())->parse($sql);
331
332 self::assertSame('INTEGER_ARRAY', $schema->columns['ids']->type);
333 }
334
335 #[Test]
336 public function testParseTimestampWithTimeZone(): void
337 {
338 $sql = 'CREATE TABLE test (created_at TIMESTAMP WITH TIME ZONE)';
339 $schema = (new PostgreSqlSchemaParser())->parse($sql);
340
341 self::assertSame('TIMESTAMP WITH TIME ZONE', $schema->columns['created_at']->type);
342 }
343
344 #[Test]
345 public function testParseTimestampWithoutTimeZone(): void
346 {
347 $sql = 'CREATE TABLE test (created_at TIMESTAMP WITHOUT TIME ZONE)';
348 $schema = (new PostgreSqlSchemaParser())->parse($sql);
349
350 self::assertSame('TIMESTAMP WITHOUT TIME ZONE', $schema->columns['created_at']->type);
351 }
352
353 #[Test]
354 public function testParseTimeWithTimeZone(): void
355 {
356 $sql = 'CREATE TABLE test (t TIME WITH TIME ZONE)';
357 $schema = (new PostgreSqlSchemaParser())->parse($sql);
358
359 self::assertSame('TIME WITH TIME ZONE', $schema->columns['t']->type);
360 }
361
362 #[Test]
363 public function testParseDoublePrecision(): void
364 {
365 $sql = 'CREATE TABLE test (val DOUBLE PRECISION)';
366 $schema = (new PostgreSqlSchemaParser())->parse($sql);
367
368 self::assertSame('DOUBLE PRECISION', $schema->columns['val']->type);
369 }
370
371 #[Test]
372 public function testParseCharacterVarying(): void
373 {
374 $sql = 'CREATE TABLE test (name CHARACTER VARYING(100))';
375 $schema = (new PostgreSqlSchemaParser())->parse($sql);
376
377 self::assertSame('CHARACTER VARYING', $schema->columns['name']->type);
378 self::assertSame(100, $schema->columns['name']->length);
379 }
380
381 #[Test]
382 public function testParseNumericWithPrecisionOnly(): void
383 {
384 $sql = 'CREATE TABLE test (val NUMERIC(8))';
385 $schema = (new PostgreSqlSchemaParser())->parse($sql);
386
387 self::assertSame('NUMERIC', $schema->columns['val']->type);
388 self::assertSame(8, $schema->columns['val']->precision);
389 self::assertSame(0, $schema->columns['val']->scale);
390 }
391
392 #[Test]
393 public function testParseUuidType(): void
394 {
395 $sql = 'CREATE TABLE test (id UUID NOT NULL)';
396 $schema = (new PostgreSqlSchemaParser())->parse($sql);
397
398 self::assertSame('UUID', $schema->columns['id']->type);
399 self::assertFalse($schema->columns['id']->nullable);
400 }
401
402 #[Test]
403 public function testParseJsonbType(): void
404 {
405 $sql = 'CREATE TABLE test (data JSONB)';
406 $schema = (new PostgreSqlSchemaParser())->parse($sql);
407
408 self::assertSame('JSONB', $schema->columns['data']->type);
409 }
410
411 #[Test]
412 public function testParseByteaType(): void
413 {
414 $sql = 'CREATE TABLE test (content BYTEA)';
415 $schema = (new PostgreSqlSchemaParser())->parse($sql);
416
417 self::assertSame('BYTEA', $schema->columns['content']->type);
418 }
419
420 #[Test]
421 public function testParseInetType(): void
422 {
423 $sql = 'CREATE TABLE test (ip INET)';
424 $schema = (new PostgreSqlSchemaParser())->parse($sql);
425
426 self::assertSame('INET', $schema->columns['ip']->type);
427 }
428
429 #[Test]
430 public function testParseCidrType(): void
431 {
432 $sql = 'CREATE TABLE test (network CIDR)';
433 $schema = (new PostgreSqlSchemaParser())->parse($sql);
434
435 self::assertSame('CIDR', $schema->columns['network']->type);
436 }
437
438 #[Test]
439 public function testParseMacaddrType(): void
440 {
441 $sql = 'CREATE TABLE test (mac MACADDR)';
442 $schema = (new PostgreSqlSchemaParser())->parse($sql);
443
444 self::assertSame('MACADDR', $schema->columns['mac']->type);
445 }
446
447 #[Test]
448 public function testParseMoneyType(): void
449 {
450 $sql = 'CREATE TABLE test (price MONEY)';
451 $schema = (new PostgreSqlSchemaParser())->parse($sql);
452
453 self::assertSame('MONEY', $schema->columns['price']->type);
454 }
455
456 #[Test]
457 public function testParseIntervalType(): void
458 {
459 $sql = 'CREATE TABLE test (duration INTERVAL)';
460 $schema = (new PostgreSqlSchemaParser())->parse($sql);
461
462 self::assertSame('INTERVAL', $schema->columns['duration']->type);
463 }
464
465 #[Test]
466 public function testParseXmlType(): void
467 {
468 $sql = 'CREATE TABLE test (data XML)';
469 $schema = (new PostgreSqlSchemaParser())->parse($sql);
470
471 self::assertSame('XML', $schema->columns['data']->type);
472 }
473
474 #[Test]
475 public function testParseExcludeConstraintSkipped(): void
476 {
477 $sql = <<<'SQL'
478 CREATE TABLE test (
479 id INTEGER,
480 range_col INTEGER,
481 EXCLUDE USING gist (range_col WITH &&)
482 )
483 SQL;
484
485 $schema = (new PostgreSqlSchemaParser())->parse($sql);
486
487 self::assertCount(2, $schema->columns);
488 self::assertArrayHasKey('id', $schema->columns);
489 self::assertArrayHasKey('range_col', $schema->columns);
490 }
491
492 #[Test]
493 public function testParseDefaultCurrentTimestamp(): void
494 {
495 $sql = 'CREATE TABLE test (created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)';
496 $schema = (new PostgreSqlSchemaParser())->parse($sql);
497
498 self::assertSame('CURRENT_TIMESTAMP', $schema->columns['created_at']->default);
499 }
500
501 #[Test]
502 public function testParseMultipleSerialColumns(): void
503 {
504 $sql = <<<'SQL'
505 CREATE TABLE test (
506 id SERIAL PRIMARY KEY,
507 seq BIGSERIAL NOT NULL,
508 name TEXT
509 )
510 SQL;
511
512 $schema = (new PostgreSqlSchemaParser())->parse($sql);
513
514 self::assertSame('INTEGER', $schema->columns['id']->type);
515 self::assertTrue($schema->columns['id']->autoIncrement);
516 self::assertSame('BIGINT', $schema->columns['seq']->type);
517 self::assertTrue($schema->columns['seq']->autoIncrement);
518 self::assertSame('TEXT', $schema->columns['name']->type);
519 self::assertFalse($schema->columns['name']->autoIncrement);
520 }
521
522 #[Test]
523 public function testParseDefaultFalseValue(): void
524 {
525 $sql = 'CREATE TABLE test (active BOOLEAN DEFAULT FALSE)';
526 $schema = (new PostgreSqlSchemaParser())->parse($sql);
527
528 self::assertFalse($schema->columns['active']->default);
529 }
530
531 #[Test]
532 public function testParseDefaultFloatValue(): void
533 {
534 $sql = 'CREATE TABLE test (price NUMERIC(10,2) DEFAULT 9.99)';
535 $schema = (new PostgreSqlSchemaParser())->parse($sql);
536
537 self::assertSame(9.99, $schema->columns['price']->default);
538 }
539
540 #[Test]
541 public function testParseDefaultIntegerValue(): void
542 {
543 $sql = 'CREATE TABLE test (count INTEGER DEFAULT 0)';
544 $schema = (new PostgreSqlSchemaParser())->parse($sql);
545
546 self::assertSame(0, $schema->columns['count']->default);
547 }
548
549 #[Test]
550 public function testParseDefaultNow(): void
551 {
552 $sql = 'CREATE TABLE test (created_at TIMESTAMP DEFAULT NOW())';
553 $schema = (new PostgreSqlSchemaParser())->parse($sql);
554
555 self::assertSame('NOW()', $schema->columns['created_at']->default);
556 }
557
558 #[Test]
559 public function testParseNonGeneratedColumn(): void
560 {
561 $sql = 'CREATE TABLE test (id INTEGER, name TEXT)';
562 $schema = (new PostgreSqlSchemaParser())->parse($sql);
563
564 self::assertFalse($schema->columns['id']->generated);
565 self::assertFalse($schema->columns['name']->generated);
566 }
567
568 #[Test]
569 public function testParseNonAutoIncrementColumn(): void
570 {
571 $sql = 'CREATE TABLE test (id INTEGER, name TEXT)';
572 $schema = (new PostgreSqlSchemaParser())->parse($sql);
573
574 self::assertFalse($schema->columns['id']->autoIncrement);
575 }
576
577 #[Test]
578 public function testParseEnumValuesAlwaysNull(): void
579 {
580 $sql = 'CREATE TABLE test (id INTEGER)';
581 $schema = (new PostgreSqlSchemaParser())->parse($sql);
582
583 self::assertNull($schema->columns['id']->enumValues);
584 }
585
586 #[Test]
587 public function testParseColumnWithEmptyType(): void
588 {
589 $sql = 'CREATE TABLE test (id)';
590 $schema = (new PostgreSqlSchemaParser())->parse($sql);
591
592 self::assertSame('TEXT', $schema->columns['id']->type);
593 }
594
595 #[Test]
596 public function testParseBlockComment(): void
597 {
598 $sql = '/* comment */ CREATE TABLE test (id INTEGER)';
599 $schema = (new PostgreSqlSchemaParser())->parse($sql);
600
601 self::assertSame('test', $schema->tableName);
602 }
603
604 #[Test]
605 public function testParseNoParentheses(): void
606 {
607 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
608 (new PostgreSqlSchemaParser())->parse('CREATE TABLE test');
609 }
610
611 #[Test]
612 public function testParseDefaultExpressionParenthesized(): void
613 {
614 $sql = 'CREATE TABLE test (val INTEGER DEFAULT (1+2))';
615 $schema = (new PostgreSqlSchemaParser())->parse($sql);
616
617 self::assertSame('(1+2)', $schema->columns['val']->default);
618 }
619
620 #[Test]
621 public function testParseDecimalWithPrecisionOnly(): void
622 {
623 $sql = 'CREATE TABLE test (val DECIMAL(8))';
624 $schema = (new PostgreSqlSchemaParser())->parse($sql);
625
626 self::assertSame('DECIMAL', $schema->columns['val']->type);
627 self::assertSame(8, $schema->columns['val']->precision);
628 self::assertSame(0, $schema->columns['val']->scale);
629 }
630
631 #[Test]
632 public function testParseTimeWithoutTimeZone(): void
633 {
634 $sql = 'CREATE TABLE test (t TIME WITHOUT TIME ZONE)';
635 $schema = (new PostgreSqlSchemaParser())->parse($sql);
636
637 self::assertSame('TIME WITHOUT TIME ZONE', $schema->columns['t']->type);
638 }
639
640 #[Test]
641 public function testParseDefaultNullValueExplicit(): void
642 {
643 $sql = 'CREATE TABLE test (val VARCHAR(100) DEFAULT NULL)';
644 $schema = (new PostgreSqlSchemaParser())->parse($sql);
645
646 self::assertNull($schema->columns['val']->default);
647 }
648
649 #[Test]
650 public function testParseQuotedPrimaryKeyColumns(): void
651 {
652 $sql = 'CREATE TABLE test ("a" INTEGER, "b" TEXT, PRIMARY KEY ("a", "b"))';
653 $schema = (new PostgreSqlSchemaParser())->parse($sql);
654
655 self::assertSame(['a', 'b'], $schema->primaryKeys);
656 }
657
658 #[Test]
659 public function testParseMultilineWithWhitespace(): void
660 {
661 $sql = ' CREATE TABLE test ( id INTEGER NOT NULL ) ';
662 $schema = (new PostgreSqlSchemaParser())->parse($sql);
663 self::assertSame('test', $schema->tableName);
664 self::assertFalse($schema->columns['id']->nullable);
665 }
666
667 #[Test]
668 public function testParseDecPrecisionOnly(): void
669 {
670 $sql = 'CREATE TABLE test (val DEC(6))';
671 $schema = (new PostgreSqlSchemaParser())->parse($sql);
672 self::assertSame(6, $schema->columns['val']->precision);
673 self::assertSame(0, $schema->columns['val']->scale);
674 }
675
676 #[Test]
677 public function testParseDefaultStringDoubleQuotes(): void
678 {
679 $sql = 'CREATE TABLE test (name TEXT DEFAULT "hello")';
680 $schema = (new PostgreSqlSchemaParser())->parse($sql);
681 self::assertSame('hello', $schema->columns['name']->default);
682 }
683
684 #[Test]
685 public function testParseColumnWithNoTypeGetsTextDefault(): void
686 {
687 $sql = 'CREATE TABLE test (col)';
688 $schema = (new PostgreSqlSchemaParser())->parse($sql);
689 self::assertSame('TEXT', $schema->columns['col']->type);
690 }
691
692 #[Test]
693 public function testParseColumnNotAutoIncrementByDefault(): void
694 {
695 $sql = 'CREATE TABLE test (id INTEGER NOT NULL, name TEXT)';
696 $schema = (new PostgreSqlSchemaParser())->parse($sql);
697 self::assertFalse($schema->columns['id']->autoIncrement);
698 }
699
700 #[Test]
701 public function testParseDefaultLocaltimeValue(): void
702 {
703 $sql = 'CREATE TABLE test (t TIME DEFAULT LOCALTIME)';
704 $schema = (new PostgreSqlSchemaParser())->parse($sql);
705 self::assertSame('LOCALTIME', $schema->columns['t']->default);
706 }
707
708 #[Test]
709 public function testParseDefaultLocaltimestampValue(): void
710 {
711 $sql = 'CREATE TABLE test (ts TIMESTAMP DEFAULT LOCALTIMESTAMP)';
712 $schema = (new PostgreSqlSchemaParser())->parse($sql);
713 self::assertSame('LOCALTIMESTAMP', $schema->columns['ts']->default);
714 }
715
716 #[Test]
717 public function testParseDefaultCurrentDateValue(): void
718 {
719 $sql = 'CREATE TABLE test (d DATE DEFAULT CURRENT_DATE)';
720 $schema = (new PostgreSqlSchemaParser())->parse($sql);
721 self::assertSame('CURRENT_DATE', $schema->columns['d']->default);
722 }
723
724 #[Test]
725 public function testParseDefaultCurrentTimeValue(): void
726 {
727 $sql = 'CREATE TABLE test (t TIME DEFAULT CURRENT_TIME)';
728 $schema = (new PostgreSqlSchemaParser())->parse($sql);
729 self::assertSame('CURRENT_TIME', $schema->columns['t']->default);
730 }
731
732 #[Test]
733 public function testParseSerialWithPrecision(): void
734 {
735 $sql = 'CREATE TABLE test (id SERIAL(10) PRIMARY KEY, name TEXT)';
736 $schema = (new PostgreSqlSchemaParser())->parse($sql);
737 self::assertSame('INTEGER', $schema->columns['id']->type);
738 self::assertTrue($schema->columns['id']->autoIncrement);
739 }
740
741 #[Test]
742 public function testParseDefaultStringWithTrailingConstraints(): void
743 {
744 $sql = "CREATE TABLE test (name TEXT DEFAULT 'test' NOT NULL UNIQUE)";
745 $schema = (new PostgreSqlSchemaParser())->parse($sql);
746 self::assertSame('test', $schema->columns['name']->default);
747 }
748
749 #[Test]
750 public function testParseCharWithLength(): void
751 {
752 $sql = 'CREATE TABLE test (code CHAR(3))';
753 $schema = (new PostgreSqlSchemaParser())->parse($sql);
754 self::assertSame('CHAR', $schema->columns['code']->type);
755 self::assertSame(3, $schema->columns['code']->length);
756 }
757
758 #[Test]
759 public function testParseInlineComment(): void
760 {
761 $sql = "CREATE TABLE test (id INTEGER -- this is pk\n)";
762 $schema = (new PostgreSqlSchemaParser())->parse($sql);
763 self::assertSame('test', $schema->tableName);
764 }
765
766 #[Test]
767 public function testParseLowercaseCreateTable(): void
768 {
769 $sql = 'create table users (id integer primary key, name text not null)';
770 $schema = (new PostgreSqlSchemaParser())->parse($sql);
771
772 self::assertSame('users', $schema->tableName);
773 self::assertSame('INTEGER', $schema->columns['id']->type);
774 self::assertSame('TEXT', $schema->columns['name']->type);
775 self::assertFalse($schema->columns['id']->nullable);
776 self::assertFalse($schema->columns['name']->nullable);
777 }
778
779 #[Test]
780 public function testParseLowercaseSerialType(): void
781 {
782 $sql = 'create table test (id serial primary key)';
783 $schema = (new PostgreSqlSchemaParser())->parse($sql);
784
785 self::assertSame('INTEGER', $schema->columns['id']->type);
786 self::assertTrue($schema->columns['id']->autoIncrement);
787 }
788
789 #[Test]
790 public function testParseLowercaseBigserialType(): void
791 {
792 $sql = 'create table test (id bigserial primary key)';
793 $schema = (new PostgreSqlSchemaParser())->parse($sql);
794
795 self::assertSame('BIGINT', $schema->columns['id']->type);
796 self::assertTrue($schema->columns['id']->autoIncrement);
797 }
798
799 #[Test]
800 public function testParseLowercaseSmallserialType(): void
801 {
802 $sql = 'create table test (id smallserial primary key)';
803 $schema = (new PostgreSqlSchemaParser())->parse($sql);
804
805 self::assertSame('SMALLINT', $schema->columns['id']->type);
806 self::assertTrue($schema->columns['id']->autoIncrement);
807 }
808
809 #[Test]
810 public function testParseLowercaseIfNotExists(): void
811 {
812 $sql = 'create table if not exists users (id integer primary key)';
813 $schema = (new PostgreSqlSchemaParser())->parse($sql);
814
815 self::assertSame('users', $schema->tableName);
816 }
817
818 #[Test]
819 public function testParseLowercaseConstraints(): void
820 {
821 $sql = <<<'SQL'
822 create table test (
823 id integer,
824 name text,
825 foreign key (id) references other(id),
826 unique (name),
827 check (id > 0),
828 constraint my_constraint unique (id, name),
829 exclude using gist (id with &&)
830 )
831 SQL;
832
833 $schema = (new PostgreSqlSchemaParser())->parse($sql);
834
835 self::assertCount(2, $schema->columns);
836 }
837
838 #[Test]
839 public function testParseLowercaseTablePrimaryKey(): void
840 {
841 $sql = 'create table test (a integer, b text, primary key (a, b))';
842 $schema = (new PostgreSqlSchemaParser())->parse($sql);
843
844 self::assertSame(['a', 'b'], $schema->primaryKeys);
845 self::assertFalse($schema->columns['a']->nullable);
846 }
847
848 #[Test]
849 public function testParseLowercaseMultiWordTypes(): void
850 {
851 $sql = <<<'SQL'
852 create table test (
853 col_dp double precision,
854 col_tstz timestamp with time zone,
855 col_ts timestamp without time zone,
856 col_ttz time with time zone,
857 col_t time without time zone,
858 col_cv character varying(100)
859 )
860 SQL;
861
862 $schema = (new PostgreSqlSchemaParser())->parse($sql);
863
864 self::assertSame('DOUBLE PRECISION', $schema->columns['col_dp']->type);
865 self::assertSame('TIMESTAMP WITH TIME ZONE', $schema->columns['col_tstz']->type);
866 self::assertSame('TIMESTAMP WITHOUT TIME ZONE', $schema->columns['col_ts']->type);
867 self::assertSame('TIME WITH TIME ZONE', $schema->columns['col_ttz']->type);
868 self::assertSame('TIME WITHOUT TIME ZONE', $schema->columns['col_t']->type);
869 self::assertSame('CHARACTER VARYING', $schema->columns['col_cv']->type);
870 self::assertSame(100, $schema->columns['col_cv']->length);
871 }
872
873 #[Test]
874 public function testParseLowercaseDecimalType(): void
875 {
876 $sql = 'create table test (val decimal(10, 2))';
877 $schema = (new PostgreSqlSchemaParser())->parse($sql);
878
879 self::assertSame('DECIMAL', $schema->columns['val']->type);
880 self::assertSame(10, $schema->columns['val']->precision);
881 self::assertSame(2, $schema->columns['val']->scale);
882 }
883
884 #[Test]
885 public function testParseLowercaseNumericType(): void
886 {
887 $sql = 'create table test (val numeric(8))';
888 $schema = (new PostgreSqlSchemaParser())->parse($sql);
889
890 self::assertSame('NUMERIC', $schema->columns['val']->type);
891 self::assertSame(8, $schema->columns['val']->precision);
892 }
893
894 #[Test]
895 public function testParseLowercaseDecType(): void
896 {
897 $sql = 'create table test (val dec(6))';
898 $schema = (new PostgreSqlSchemaParser())->parse($sql);
899
900 self::assertSame(6, $schema->columns['val']->precision);
901 }
902
903 #[Test]
904 public function testParseLowercaseGeneratedColumn(): void
905 {
906 $sql = 'create table test (a integer, b integer, c integer generated always as (a + b) stored)';
907 $schema = (new PostgreSqlSchemaParser())->parse($sql);
908
909 self::assertTrue($schema->columns['c']->generated);
910 }
911
912 #[Test]
913 public function testParseLowercaseDefaultValues(): void
914 {
915 $sql = <<<'SQL'
916 create table test (
917 col_null text default null,
918 col_true boolean default true,
919 col_false boolean default false,
920 col_str text default 'hello',
921 col_int integer default 42,
922 col_float numeric(10,2) default 9.99,
923 col_func uuid default gen_random_uuid(),
924 col_cast jsonb default '{}'::jsonb,
925 col_expr integer default (1+2),
926 col_ts timestamp default current_timestamp,
927 col_now timestamp default now(),
928 col_date date default current_date,
929 col_time time default current_time,
930 col_lt time default localtime,
931 col_lts timestamp default localtimestamp
932 )
933 SQL;
934
935 $schema = (new PostgreSqlSchemaParser())->parse($sql);
936
937 self::assertNull($schema->columns['col_null']->default);
938 self::assertTrue($schema->columns['col_true']->default);
939 self::assertFalse($schema->columns['col_false']->default);
940 self::assertSame('hello', $schema->columns['col_str']->default);
941 self::assertSame(42, $schema->columns['col_int']->default);
942 self::assertSame(9.99, $schema->columns['col_float']->default);
943 self::assertSame('gen_random_uuid()', $schema->columns['col_func']->default);
944 self::assertSame("'{}'::jsonb", $schema->columns['col_cast']->default);
945 self::assertSame('(1+2)', $schema->columns['col_expr']->default);
946 self::assertSame('current_timestamp', $schema->columns['col_ts']->default);
947 self::assertSame('now()', $schema->columns['col_now']->default);
948 self::assertSame('current_date', $schema->columns['col_date']->default);
949 self::assertSame('current_time', $schema->columns['col_time']->default);
950 self::assertSame('localtime', $schema->columns['col_lt']->default);
951 self::assertSame('localtimestamp', $schema->columns['col_lts']->default);
952 }
953
954 #[Test]
955 public function testParseLowercaseArrayTypes(): void
956 {
957 $sql = 'create table test (tags text[], ids integer[])';
958 $schema = (new PostgreSqlSchemaParser())->parse($sql);
959
960 self::assertSame('TEXT_ARRAY', $schema->columns['tags']->type);
961 self::assertSame('INTEGER_ARRAY', $schema->columns['ids']->type);
962 }
963
964 #[Test]
965 public function testParseLowercaseDefaultWithTrailingConstraints(): void
966 {
967 $sql = "create table test (name text default 'test' not null unique)";
968 $schema = (new PostgreSqlSchemaParser())->parse($sql);
969
970 self::assertSame('test', $schema->columns['name']->default);
971 self::assertFalse($schema->columns['name']->nullable);
972 }
973
974 #[Test]
975 public function testParseWithLeadingTrailingWhitespace(): void
976 {
977 $sql = " \n create table test ( \n id integer , \n name text \n ) \n ";
978 $schema = (new PostgreSqlSchemaParser())->parse($sql);
979
980 self::assertSame('test', $schema->tableName);
981 self::assertCount(2, $schema->columns);
982 }
983
984 #[Test]
985 public function testParseMixedCaseKeywords(): void
986 {
987 $sql = 'Create Table test (id Integer Primary Key, name Varchar(100) Not Null)';
988 $schema = (new PostgreSqlSchemaParser())->parse($sql);
989
990 self::assertSame('test', $schema->tableName);
991 self::assertSame('INTEGER', $schema->columns['id']->type);
992 self::assertSame('VARCHAR', $schema->columns['name']->type);
993 self::assertFalse($schema->columns['id']->nullable);
994 self::assertFalse($schema->columns['name']->nullable);
995 }
996
997 #[Test]
998 public function testParseDefaultStringWithConstraintsAfter(): void
999 {
1000 $sql = "CREATE TABLE test (val TEXT DEFAULT 'hello' NOT NULL CHECK (val <> ''))";
1001 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1002
1003 self::assertSame('hello', $schema->columns['val']->default);
1004 }
1005
1006 #[Test]
1007 public function testParseConstraintBeforeColumn(): void
1008 {
1009 $sql = <<<'SQL'
1010 CREATE TABLE test (
1011 id INTEGER,
1012 PRIMARY KEY (id),
1013 name TEXT NOT NULL
1014 )
1015 SQL;
1016
1017 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1018
1019 self::assertCount(2, $schema->columns);
1020 self::assertArrayHasKey('id', $schema->columns);
1021 self::assertArrayHasKey('name', $schema->columns);
1022 self::assertSame('TEXT', $schema->columns['name']->type);
1023 self::assertFalse($schema->columns['name']->nullable);
1024 }
1025
1026 #[Test]
1027 public function testParseMultipleConstraintsBetweenColumns(): void
1028 {
1029 $sql = <<<'SQL'
1030 CREATE TABLE test (
1031 id INTEGER,
1032 CONSTRAINT pk PRIMARY KEY (id),
1033 UNIQUE (id),
1034 name TEXT NOT NULL,
1035 email TEXT,
1036 CHECK (name <> ''),
1037 age INTEGER
1038 )
1039 SQL;
1040
1041 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1042
1043 self::assertCount(4, $schema->columns);
1044 self::assertArrayHasKey('name', $schema->columns);
1045 self::assertArrayHasKey('email', $schema->columns);
1046 self::assertArrayHasKey('age', $schema->columns);
1047 self::assertSame('INTEGER', $schema->columns['age']->type);
1048 }
1049
1050 #[Test]
1051 public function testParseDefaultExpressionWithOnlyOpeningParen(): void
1052 {
1053 $sql = "CREATE TABLE test (val TEXT DEFAULT 'value(test')";
1054 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1055
1056 self::assertSame('value(test', $schema->columns['val']->default);
1057 }
1058
1059 #[Test]
1060 public function testParseColumnWithNoTypeReturnsTextViaExtractType(): void
1061 {
1062 $sql = 'CREATE TABLE test (col1, col2 INTEGER)';
1063 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1064
1065 self::assertSame('TEXT', $schema->columns['col1']->type);
1066 self::assertSame('INTEGER', $schema->columns['col2']->type);
1067 }
1068
1069 #[Test]
1070 public function testParseDefaultExpressionWrappedInParens(): void
1071 {
1072 $sql = 'CREATE TABLE test (val INTEGER DEFAULT (10 * 2))';
1073 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1074
1075 self::assertSame('(10 * 2)', $schema->columns['val']->default);
1076 }
1077
1078 #[Test]
1079 public function testParseDefaultExpressionOnlyClosingParen(): void
1080 {
1081 $sql = "CREATE TABLE test (val TEXT DEFAULT 'test)')";
1082 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1083
1084 self::assertSame('test)', $schema->columns['val']->default);
1085 }
1086
1087 #[Test]
1088 public function testParseDefaultTypeCastIsPreserved(): void
1089 {
1090 $sql = "CREATE TABLE test (val TEXT DEFAULT 'hello'::text)";
1091 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1092
1093 self::assertSame("'hello'::text", $schema->columns['val']->default);
1094 }
1095
1096 #[Test]
1097 public function testParseDefaultFunctionCallPreserved(): void
1098 {
1099 $sql = 'CREATE TABLE test (id UUID DEFAULT uuid_generate_v4())';
1100 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1101
1102 self::assertSame('uuid_generate_v4()', $schema->columns['id']->default);
1103 }
1104
1105 #[Test]
1106 public function testParseTrimsDefinitionsInSplitColumnDefinitions(): void
1107 {
1108 $sql = 'CREATE TABLE test ( id INTEGER , name TEXT )';
1109 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1110
1111 self::assertCount(2, $schema->columns);
1112 self::assertArrayHasKey('id', $schema->columns);
1113 self::assertArrayHasKey('name', $schema->columns);
1114 }
1115
1116 #[Test]
1117 public function testParseColumnDefinitionTrimsRest(): void
1118 {
1119 $sql = 'CREATE TABLE test (id INTEGER NOT NULL)';
1120 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1121
1122 self::assertSame('INTEGER', $schema->columns['id']->type);
1123 self::assertFalse($schema->columns['id']->nullable);
1124 }
1125
1126 #[Test]
1127 public function testParseDefaultTrimsValue(): void
1128 {
1129 $sql = "CREATE TABLE test (val TEXT DEFAULT 'trimmed' NOT NULL)";
1130 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1131
1132 self::assertSame('trimmed', $schema->columns['val']->default);
1133 self::assertFalse($schema->columns['val']->nullable);
1134 }
1135
1136 #[Test]
1137 public function testParseIsTableConstraintWithLeadingWhitespace(): void
1138 {
1139 $sql = "CREATE TABLE test (id INTEGER, \n PRIMARY KEY (id))";
1140 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1141
1142 self::assertCount(1, $schema->columns);
1143 }
1144
1145 #[Test]
1146 public function testParseIsDecimalTypeCaseInsensitive(): void
1147 {
1148 $sql = 'CREATE TABLE test (val decimal(5))';
1149 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1150
1151 self::assertSame('DECIMAL', $schema->columns['val']->type);
1152 self::assertSame(5, $schema->columns['val']->precision);
1153 self::assertSame(0, $schema->columns['val']->scale);
1154 }
1155
1156 #[Test]
1157 public function testParseDefaultCurrentTimestampNotPartOfLargerWord(): void
1158 {
1159 $sql = "CREATE TABLE test (ts TEXT DEFAULT 'NOT_CURRENT_TIMESTAMP_EXTRA')";
1160 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1161
1162 self::assertSame('NOT_CURRENT_TIMESTAMP_EXTRA', $schema->columns['ts']->default);
1163 }
1164
1165 #[Test]
1166 public function testParseNormalizeSqlRemovesBlockComments(): void
1167 {
1168 $sql = 'CREATE TABLE /* block comment */ test (id /* another */ INTEGER)';
1169 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1170
1171 self::assertSame('test', $schema->tableName);
1172 self::assertSame('INTEGER', $schema->columns['id']->type);
1173 }
1174
1175 #[Test]
1176 public function testParseNormalizeSqlTrimsWhitespace(): void
1177 {
1178 $sql = "\n\n CREATE TABLE test (id INTEGER) \n\n";
1179 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1180
1181 self::assertSame('test', $schema->tableName);
1182 }
1183
1184 #[Test]
1185 public function testParseDefaultStringWithDoubleQuotes(): void
1186 {
1187 $sql = 'CREATE TABLE test (name TEXT DEFAULT "value_here")';
1188 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1189
1190 self::assertSame('value_here', $schema->columns['name']->default);
1191 }
1192
1193 #[Test]
1194 public function testParseDefaultReferencesConstraint(): void
1195 {
1196 $sql = 'CREATE TABLE test (user_id INTEGER DEFAULT 1 REFERENCES users(id))';
1197 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1198
1199 self::assertSame(1, $schema->columns['user_id']->default);
1200 }
1201
1202 #[Test]
1203 public function testParseDefaultWithGeneratedKeyword(): void
1204 {
1205 $sql = 'CREATE TABLE test (val INTEGER DEFAULT 5, gen INTEGER GENERATED ALWAYS AS (val * 2) STORED)';
1206 $schema = (new PostgreSqlSchemaParser())->parse($sql);
1207
1208 self::assertSame(5, $schema->columns['val']->default);
1209 self::assertTrue($schema->columns['gen']->generated);
1210 }
1211
1212 #[Test]
1213 public function testParseParensEqualStartReturnsNull(): void
1214 {
1215 $this->expectException(\SqlFixture\Schema\Exception\InvalidSqlException::class);
1216 (new PostgreSqlSchemaParser())->parse('CREATE TABLE test )( ');
1217 }
1218}
1219