packages/ztd-query-postgres/tests/Unit/Schema/PgSqlSchemaParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Schema;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use ZtdQuery\Platform\Postgres\Schema\PgSqlSchemaParser;
10use ZtdQuery\Platform\Postgres\Sql\Partition\PgSqlPartitionParser;
11use ZtdQuery\Schema\ColumnTypeFamily;
12use ZtdQuery\Schema\Key\IdentityGenerationStrategy;
13use ZtdQuery\Schema\Partition\TablePartitionStrategy;
14
15#[CoversClass(PgSqlSchemaParser::class)]
16#[UsesClass(\ZtdQuery\Platform\Postgres\Schema\PgSqlColumnTypeMapper::class)]
17#[UsesClass(\ZtdQuery\Platform\Postgres\Schema\Key\PgSqlForeignKeyDefinitionParser::class)]
18#[UsesClass(PgSqlPartitionParser::class)]
19#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
20#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Definition\ColumnDefinition::class)]
21#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Definition\ColumnTypeDeclaration::class)]
22#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Definition\TableBody::class)]
23#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Definition\TableConstraint::class)]
24#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Definition\TableFields::class)]
25#[UsesClass(\ZtdQuery\Platform\Postgres\Schema\Key\DefinitionEntry::class)]
26#[UsesClass(\ZtdQuery\Platform\Postgres\Schema\Key\DefinitionTokens::class)]
27#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Partition\BoundPredicate::class)]
28#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Partition\ClauseTokens::class)]
29#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Lexing\QuotedSpan::class)]
30final class PgSqlSchemaParserTest extends \PHPUnit\Framework\TestCase
31{
32 public function testValidCreateTableReturnsNonNull(): void
33 {
34 $parser = new PgSqlSchemaParser();
35 $result = $parser->parse(<<<'SQL'
36 CREATE TABLE users (
37 id INTEGER NOT NULL,
38 name VARCHAR(255) NOT NULL,
39 email TEXT,
40 PRIMARY KEY (id),
41 UNIQUE (email)
42 )
43 SQL);
44 self::assertNotNull($result);
45 }
46
47 public function testNonCreateTableReturnsNull(): void
48 {
49 $parser = new PgSqlSchemaParser();
50 self::assertNull($parser->parse('SELECT * FROM users'));
51 self::assertNull($parser->parse('INSERT INTO users (id) VALUES (1)'));
52 self::assertNull($parser->parse('DROP TABLE users'));
53 }
54
55 public function testPrimaryKeysSubsetOfColumns(): void
56 {
57 $parser = new PgSqlSchemaParser();
58 $sql = 'CREATE TABLE test (
59 id INTEGER,
60 name TEXT,
61 PRIMARY KEY (id)
62 )';
63 $def = $parser->parse($sql);
64 self::assertNotNull($def);
65 self::assertSame(['id'], $def->primaryKeys);
66 self::assertContains('id', $def->columns);
67 }
68
69 public function testNotNullSubsetOfColumns(): void
70 {
71 $parser = new PgSqlSchemaParser();
72 $sql = 'CREATE TABLE test (
73 id INTEGER NOT NULL,
74 name TEXT
75 )';
76 $def = $parser->parse($sql);
77 self::assertNotNull($def);
78 self::assertContains('id', $def->notNullColumns);
79 self::assertContains('id', $def->columns);
80 }
81
82 public function testColumnTypesKeysSubsetOfColumns(): void
83 {
84 $parser = new PgSqlSchemaParser();
85 $sql = 'CREATE TABLE test (
86 id INTEGER,
87 name TEXT
88 )';
89 $def = $parser->parse($sql);
90 self::assertNotNull($def);
91 self::assertArrayHasKey('id', $def->columnTypes);
92 self::assertContains('id', $def->columns);
93 self::assertArrayHasKey('name', $def->columnTypes);
94 self::assertContains('name', $def->columns);
95 }
96
97 public function testUniqueConstraintColumnsSubsetOfColumns(): void
98 {
99 $parser = new PgSqlSchemaParser();
100 $definition = $parser->parse(<<<'SQL'
101 CREATE TABLE users (
102 id INTEGER NOT NULL,
103 name VARCHAR(255) NOT NULL,
104 email TEXT,
105 PRIMARY KEY (id),
106 UNIQUE (email)
107 )
108 SQL);
109 self::assertNotNull($definition);
110 self::assertSame([['email']], array_values($definition->uniqueConstraints));
111 self::assertContains('email', $definition->columns);
112 }
113
114 public function testParsedDefinitionHasNonEmptyColumns(): void
115 {
116 $parser = new PgSqlSchemaParser();
117 $definition = $parser->parse(<<<'SQL'
118 CREATE TABLE users (
119 id INTEGER NOT NULL,
120 name VARCHAR(255) NOT NULL,
121 email TEXT,
122 PRIMARY KEY (id),
123 UNIQUE (email)
124 )
125 SQL);
126 self::assertNotNull($definition);
127 self::assertNotEmpty($definition->columns);
128 }
129
130 public function testParsedColumnsMatchExpected(): void
131 {
132 $parser = new PgSqlSchemaParser();
133 $definition = $parser->parse(<<<'SQL'
134 CREATE TABLE users (
135 id INTEGER NOT NULL,
136 name VARCHAR(255) NOT NULL,
137 email TEXT,
138 PRIMARY KEY (id),
139 UNIQUE (email)
140 )
141 SQL);
142 self::assertNotNull($definition);
143 self::assertSame(['id', 'name', 'email'], $definition->columns, 'Parsed column names must match expected columns in order');
144 }
145
146 public function testParsedPrimaryKeysMatchExpected(): void
147 {
148 $parser = new PgSqlSchemaParser();
149 $definition = $parser->parse(<<<'SQL'
150 CREATE TABLE users (
151 id INTEGER NOT NULL,
152 name VARCHAR(255) NOT NULL,
153 email TEXT,
154 PRIMARY KEY (id),
155 UNIQUE (email)
156 )
157 SQL);
158 self::assertNotNull($definition);
159 self::assertSame(['id'], $definition->primaryKeys, 'Parsed primary keys must match expected primary keys');
160 }
161
162 public function testParsedNotNullColumnsMatchExpected(): void
163 {
164 $parser = new PgSqlSchemaParser();
165 $definition = $parser->parse(<<<'SQL'
166 CREATE TABLE users (
167 id INTEGER NOT NULL,
168 name VARCHAR(255) NOT NULL,
169 email TEXT,
170 PRIMARY KEY (id),
171 UNIQUE (email)
172 )
173 SQL);
174 self::assertNotNull($definition);
175 self::assertSame(['id', 'name'], $definition->notNullColumns);
176 }
177
178 public function testColumnCountMatchesExpected(): void
179 {
180 $parser = new PgSqlSchemaParser();
181 $definition = $parser->parse(<<<'SQL'
182 CREATE TABLE users (
183 id INTEGER NOT NULL,
184 name VARCHAR(255) NOT NULL,
185 email TEXT,
186 PRIMARY KEY (id),
187 UNIQUE (email)
188 )
189 SQL);
190 self::assertNotNull($definition);
191 self::assertCount(count(['id', 'name', 'email']), $definition->columns, 'Column count must match expected');
192 }
193
194 public function testMalformedInputReturnsNull(): void
195 {
196 $parser = new PgSqlSchemaParser();
197 $result = $parser->parse('NOT VALID SQL AT ALL %%%');
198 self::assertNull($result);
199 }
200
201 public function testTypedColumnsKeysSubsetOfColumns(): void
202 {
203 $parser = new PgSqlSchemaParser();
204 $definition = $parser->parse(<<<'SQL'
205 CREATE TABLE users (
206 id INTEGER NOT NULL,
207 name VARCHAR(255) NOT NULL,
208 email TEXT,
209 PRIMARY KEY (id),
210 UNIQUE (email)
211 )
212 SQL);
213 self::assertNotNull($definition);
214 self::assertSame($definition->columns, array_keys($definition->typedColumns));
215 }
216 public function testParsesSchemaQualifiedQuotedDomainTypeWithoutChangingItsCase(): void
217 {
218 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE contacts (age "tenant"."PositiveValue" NOT NULL)');
219 self::assertNotNull($definition);
220 self::assertSame('"tenant"."PositiveValue"', $definition->columnTypes['age']);
221 self::assertSame(ColumnTypeFamily::UNKNOWN, $definition->typedColumns['age']->family);
222 self::assertSame('"tenant"."PositiveValue"', $definition->typedColumns['age']->nativeType);
223 }
224 public function testParsesQuotedDomainAndDomainArrayTypes(): void
225 {
226 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE contacts (age "PositiveValue", history "tenant"."PositiveValue"[])');
227 self::assertNotNull($definition);
228 self::assertSame('"PositiveValue"', $definition->columnTypes['age']);
229 self::assertSame('"tenant"."PositiveValue"[]', $definition->columnTypes['history']);
230 }
231 public function testRejectsIncompleteQualifiedDomainType(): void
232 {
233 self::assertNull((new PgSqlSchemaParser())->parse('CREATE TABLE contacts (age "tenant". NOT NULL)'));
234 self::assertNull((new PgSqlSchemaParser())->parse('CREATE TABLE contacts (age "tenant". not null)'));
235 }
236 public function testQuotedConstraintWordsRemainValidDomainNames(): void
237 {
238 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE contacts (flag "tenant"."NOT", note "NOT NULL")');
239 self::assertNotNull($definition);
240 self::assertSame('"tenant"."NOT"', $definition->columnTypes['flag']);
241 self::assertSame('"NOT NULL"', $definition->columnTypes['note']);
242 self::assertSame([], $definition->notNullColumns);
243 }
244 public function testParseBasicCreateTable(): void
245 {
246 $parser = new PgSqlSchemaParser();
247 $sql = 'CREATE TABLE users (
248 id INTEGER PRIMARY KEY,
249 name VARCHAR(255) NOT NULL,
250 email TEXT
251 )';
252 $def = $parser->parse($sql);
253 self::assertNotNull($def);
254 self::assertSame(['id', 'name', 'email'], $def->columns);
255 self::assertSame(['id'], $def->primaryKeys);
256 self::assertContains('id', $def->notNullColumns);
257 self::assertContains('name', $def->notNullColumns);
258 }
259 public function testPartitionClauseDoesNotBecomePartOfTableBody(): void
260 {
261 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE logs (id INTEGER, log_date DATE, PRIMARY KEY (id, log_date)) ' . 'PARTITION BY RANGE (log_date)');
262 self::assertNotNull($definition);
263 self::assertSame(['id', 'log_date'], $definition->columns);
264 self::assertSame(['id', 'log_date'], $definition->primaryKeys);
265 $partitionKey = $definition->partitionKey;
266 self::assertNotNull($partitionKey);
267 self::assertSame(TablePartitionStrategy::Range, $partitionKey->strategy);
268 self::assertSame(['log_date'], $partitionKey->expressions);
269 }
270 public function testCreateKeywordInsideAnotherStatementDoesNotParse(): void
271 {
272 self::assertNull((new PgSqlSchemaParser())->parse('SELECT 1 FROM source_table; CREATE TABLE hidden (id INTEGER)'));
273 }
274 public function testIncompleteIfNotExistsAndQualifiedNamesDoNotParse(): void
275 {
276 $parser = new PgSqlSchemaParser();
277 self::assertNull($parser->parse('CREATE TABLE IF users (id INTEGER)'));
278 self::assertNull($parser->parse('CREATE TABLE IF NOT users (id INTEGER)'));
279 self::assertNull($parser->parse('CREATE TABLE IF EXISTS users (id INTEGER)'));
280 self::assertNull($parser->parse('CREATE TABLE public. (id INTEGER)'));
281 }
282 public function testParseColumnTypes(): void
283 {
284 $parser = new PgSqlSchemaParser();
285 $sql = 'CREATE TABLE test (
286 id INTEGER,
287 price NUMERIC(10,2),
288 name VARCHAR(100),
289 active BOOLEAN,
290 created_at TIMESTAMP,
291 data JSONB
292 )';
293 $def = $parser->parse($sql);
294 self::assertNotNull($def);
295 self::assertSame('INTEGER', $def->columnTypes['id']);
296 self::assertSame('NUMERIC(10,2)', $def->columnTypes['price']);
297 self::assertSame('VARCHAR(100)', $def->columnTypes['name']);
298 self::assertSame('BOOLEAN', $def->columnTypes['active']);
299 self::assertSame('TIMESTAMP', $def->columnTypes['created_at']);
300 self::assertSame('JSONB', $def->columnTypes['data']);
301 }
302 public function testParseTypedColumns(): void
303 {
304 $parser = new PgSqlSchemaParser();
305 $sql = 'CREATE TABLE test (
306 id INTEGER,
307 name TEXT,
308 active BOOLEAN,
309 data JSONB
310 )';
311 $def = $parser->parse($sql);
312 self::assertNotNull($def);
313 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['id']->family);
314 self::assertSame(ColumnTypeFamily::TEXT, $def->typedColumns['name']->family);
315 self::assertSame(ColumnTypeFamily::BOOLEAN, $def->typedColumns['active']->family);
316 self::assertSame(ColumnTypeFamily::JSON, $def->typedColumns['data']->family);
317 }
318 public function testParsePrimaryKeyConstraint(): void
319 {
320 $parser = new PgSqlSchemaParser();
321 $sql = 'CREATE TABLE test (
322 id INTEGER,
323 name TEXT,
324 PRIMARY KEY (id)
325 )';
326 $def = $parser->parse($sql);
327 self::assertNotNull($def);
328 self::assertSame(['id'], $def->primaryKeys);
329 }
330 public function testParseCompositePrimaryKey(): void
331 {
332 $parser = new PgSqlSchemaParser();
333 $sql = 'CREATE TABLE test (
334 user_id INTEGER,
335 role_id INTEGER,
336 PRIMARY KEY (user_id, role_id)
337 )';
338 $def = $parser->parse($sql);
339 self::assertNotNull($def);
340 self::assertSame(['user_id', 'role_id'], $def->primaryKeys);
341 }
342 public function testParseUniqueConstraint(): void
343 {
344 $parser = new PgSqlSchemaParser();
345 $sql = 'CREATE TABLE test (
346 id INTEGER PRIMARY KEY,
347 email TEXT,
348 CONSTRAINT email_unique UNIQUE (email)
349 )';
350 $def = $parser->parse($sql);
351 self::assertNotNull($def);
352 self::assertArrayHasKey('email_unique', $def->uniqueConstraints);
353 self::assertSame(['email'], $def->uniqueConstraints['email_unique']);
354 }
355 public function testParseUniqueOnColumn(): void
356 {
357 $parser = new PgSqlSchemaParser();
358 $sql = 'CREATE TABLE test (
359 id INTEGER PRIMARY KEY,
360 email TEXT UNIQUE
361 )';
362 $def = $parser->parse($sql);
363 self::assertNotNull($def);
364 self::assertArrayHasKey('email_UNIQUE', $def->uniqueConstraints);
365 }
366 public function testParseQuotedIdentifiers(): void
367 {
368 $parser = new PgSqlSchemaParser();
369 $sql = 'CREATE TABLE "Users" (
370 "Id" INTEGER PRIMARY KEY,
371 "Name" VARCHAR(255) NOT NULL
372 )';
373 $def = $parser->parse($sql);
374 self::assertNotNull($def);
375 self::assertSame(['Id', 'Name'], $def->columns);
376 }
377 public function testParseIfNotExists(): void
378 {
379 $parser = new PgSqlSchemaParser();
380 $sql = 'CREATE TABLE IF NOT EXISTS users (
381 id INTEGER PRIMARY KEY
382 )';
383 $def = $parser->parse($sql);
384 self::assertNotNull($def);
385 }
386 public function testParseTemporaryTable(): void
387 {
388 $parser = new PgSqlSchemaParser();
389 $sql = 'CREATE TEMPORARY TABLE tmp (
390 id INTEGER PRIMARY KEY
391 )';
392 $def = $parser->parse($sql);
393 self::assertNotNull($def);
394 }
395 public function testMalformedSqlReturnsNull(): void
396 {
397 $parser = new PgSqlSchemaParser();
398 self::assertNull($parser->parse('GIBBERISH'));
399 }
400 public function testParseSerialType(): void
401 {
402 $parser = new PgSqlSchemaParser();
403 $sql = 'CREATE TABLE test (
404 id SERIAL PRIMARY KEY,
405 name TEXT
406 )';
407 $def = $parser->parse($sql);
408 self::assertNotNull($def);
409 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['id']->family);
410 }
411 public function testParseBigserialType(): void
412 {
413 $parser = new PgSqlSchemaParser();
414 $sql = 'CREATE TABLE test (
415 id BIGSERIAL PRIMARY KEY
416 )';
417 $def = $parser->parse($sql);
418 self::assertNotNull($def);
419 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['id']->family);
420 }
421 public function testParseTimestamptzType(): void
422 {
423 $parser = new PgSqlSchemaParser();
424 $sql = 'CREATE TABLE test (
425 created_at TIMESTAMPTZ
426 )';
427 $def = $parser->parse($sql);
428 self::assertNotNull($def);
429 self::assertSame(ColumnTypeFamily::TIMESTAMP, $def->typedColumns['created_at']->family);
430 }
431 public function testParseByteaType(): void
432 {
433 $parser = new PgSqlSchemaParser();
434 $sql = 'CREATE TABLE test (
435 data BYTEA
436 )';
437 $def = $parser->parse($sql);
438 self::assertNotNull($def);
439 self::assertSame(ColumnTypeFamily::BINARY, $def->typedColumns['data']->family);
440 }
441 public function testParseSchemaQualifiedTable(): void
442 {
443 $parser = new PgSqlSchemaParser();
444 $sql = 'CREATE TABLE public.users (
445 id INTEGER PRIMARY KEY
446 )';
447 $def = $parser->parse($sql);
448 self::assertNotNull($def);
449 self::assertSame(['id'], $def->columns);
450 }
451 public function testParseReservedWordColumnNames(): void
452 {
453 $parser = new PgSqlSchemaParser();
454 $sql = 'CREATE TABLE test (
455 "select" INTEGER,
456 "table" TEXT,
457 "order" INTEGER
458 )';
459 $def = $parser->parse($sql);
460 self::assertNotNull($def);
461 self::assertSame(['select', 'table', 'order'], $def->columns);
462 }
463 public function testParseEmptyInputReturnsNull(): void
464 {
465 $parser = new PgSqlSchemaParser();
466 self::assertNull($parser->parse(''));
467 }
468 public function testParseDefaultValues(): void
469 {
470 $parser = new PgSqlSchemaParser();
471 $sql = 'CREATE TABLE test (
472 id INTEGER PRIMARY KEY,
473 status TEXT DEFAULT \'active\' NOT NULL,
474 created_at TIMESTAMP DEFAULT NOW()
475 )';
476 $def = $parser->parse($sql);
477 self::assertNotNull($def);
478 self::assertSame(['id', 'status', 'created_at'], $def->columns);
479 self::assertContains('status', $def->notNullColumns);
480 self::assertSame(['status' => "'active'", 'created_at' => 'NOW()'], $def->columnDefaults);
481 }
482 public function testParseKeepsConstraintWordsInsideDefaultExpressions(): void
483 {
484 $definition = (new PgSqlSchemaParser())->parse("CREATE TABLE t (label TEXT DEFAULT ('not null, still default') NOT NULL, enabled BOOLEAN DEFAULT TRUE)");
485 self::assertNotNull($definition);
486 self::assertSame(['label' => "('not null, still default')", 'enabled' => 'TRUE'], $definition->columnDefaults);
487 }
488 public function testParseDoesNotTreatSequenceAsOrdinaryDefault(): void
489 {
490 $definition = (new PgSqlSchemaParser())->parse("CREATE TABLE t (id BIGINT DEFAULT nextval('t_id_seq'::regclass), name TEXT DEFAULT 'new')");
491 self::assertNotNull($definition);
492 self::assertSame(['name' => "'new'"], $definition->columnDefaults);
493 self::assertSame(['id' => IdentityGenerationStrategy::Sequence], $definition->identityStrategies);
494 }
495 public function testParseSerialAndGeneratedIdentityStrategies(): void
496 {
497 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE t (id serial PRIMARY KEY, generated BIGINT generated by default as identity, name TEXT)');
498 self::assertNotNull($definition);
499 self::assertSame(['id' => IdentityGenerationStrategy::Sequence, 'generated' => IdentityGenerationStrategy::Sequence], $definition->identityStrategies);
500 }
501 public function testParseDetectsParenthesizedUppercaseSequenceDefault(): void
502 {
503 $definition = (new PgSqlSchemaParser())->parse("CREATE TABLE t (id BIGINT DEFAULT ((NEXTVAL('t_id_seq'::regclass))), fallback BIGINT DEFAULT coalesce(nextval('fallback_seq'), 1), offset_id BIGINT DEFAULT +nextval('offset_seq'))");
504 self::assertNotNull($definition);
505 self::assertSame(['fallback' => "coalesce(nextval('fallback_seq'), 1)", 'offset_id' => "+nextval('offset_seq')"], $definition->columnDefaults);
506 self::assertSame(['id' => IdentityGenerationStrategy::Sequence], $definition->identityStrategies);
507 }
508 public function testGeneratedStoredColumnIsNotAnIdentity(): void
509 {
510 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE t (source INTEGER, computed INTEGER GENERATED ALWAYS AS (source + 1) STORED)');
511 self::assertNotNull($definition);
512 self::assertSame([], $definition->identityStrategies);
513 self::assertSame(['computed' => '(source + 1)'], $definition->generatedExpressions);
514 }
515 public function testUnprefixedAndIncompleteIdentityClausesAreNotIdentities(): void
516 {
517 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE t (always_value BIGINT ALWAYS AS IDENTITY, default_value BIGINT BY DEFAULT AS IDENTITY, ' . 'incomplete BIGINT GENERATED, unprefixed_computed INTEGER ALWAYS AS (1) STORED)');
518 self::assertNotNull($definition);
519 self::assertSame([], $definition->identityStrategies);
520 self::assertSame([], $definition->generatedExpressions);
521 }
522 public function testParseStopsDefaultAtInlinePrimaryKeyConstraint(): void
523 {
524 $definition = (new PgSqlSchemaParser())->parse('CREATE TABLE t (id INTEGER DEFAULT 7 PRIMARY KEY)');
525 self::assertNotNull($definition);
526 self::assertSame(['id' => '7'], $definition->columnDefaults);
527 self::assertSame(['id'], $definition->primaryKeys);
528 }
529 public function testParseMultipleUniqueConstraints(): void
530 {
531 $parser = new PgSqlSchemaParser();
532 $sql = 'CREATE TABLE test (
533 id INTEGER PRIMARY KEY,
534 email TEXT,
535 username TEXT,
536 CONSTRAINT email_unique UNIQUE (email),
537 CONSTRAINT username_unique UNIQUE (username)
538 )';
539 $def = $parser->parse($sql);
540 self::assertNotNull($def);
541 self::assertArrayHasKey('email_unique', $def->uniqueConstraints);
542 self::assertArrayHasKey('username_unique', $def->uniqueConstraints);
543 self::assertSame(['email'], $def->uniqueConstraints['email_unique']);
544 self::assertSame(['username'], $def->uniqueConstraints['username_unique']);
545 }
546 public function testParseDoublePrecision(): void
547 {
548 $parser = new PgSqlSchemaParser();
549 $sql = 'CREATE TABLE t (v DOUBLE PRECISION)';
550 $def = $parser->parse($sql);
551 self::assertNotNull($def);
552 self::assertSame('DOUBLE PRECISION', $def->columnTypes['v']);
553 self::assertSame(ColumnTypeFamily::DOUBLE, $def->typedColumns['v']->family);
554 }
555 public function testParseFloat4(): void
556 {
557 $parser = new PgSqlSchemaParser();
558 $sql = 'CREATE TABLE t (v FLOAT4)';
559 $def = $parser->parse($sql);
560 self::assertNotNull($def);
561 self::assertSame(ColumnTypeFamily::FLOAT, $def->typedColumns['v']->family);
562 }
563 public function testParseFloat8(): void
564 {
565 $parser = new PgSqlSchemaParser();
566 $sql = 'CREATE TABLE t (v FLOAT8)';
567 $def = $parser->parse($sql);
568 self::assertNotNull($def);
569 self::assertSame(ColumnTypeFamily::DOUBLE, $def->typedColumns['v']->family);
570 }
571 public function testParseRealType(): void
572 {
573 $parser = new PgSqlSchemaParser();
574 $sql = 'CREATE TABLE t (v REAL)';
575 $def = $parser->parse($sql);
576 self::assertNotNull($def);
577 self::assertSame(ColumnTypeFamily::FLOAT, $def->typedColumns['v']->family);
578 }
579 public function testParseDecimalType(): void
580 {
581 $parser = new PgSqlSchemaParser();
582 $sql = 'CREATE TABLE t (v DECIMAL(10,2))';
583 $def = $parser->parse($sql);
584 self::assertNotNull($def);
585 self::assertSame('DECIMAL(10,2)', $def->columnTypes['v']);
586 self::assertSame(ColumnTypeFamily::DECIMAL, $def->typedColumns['v']->family);
587 }
588 public function testParseSmallint(): void
589 {
590 $parser = new PgSqlSchemaParser();
591 $sql = 'CREATE TABLE t (v SMALLINT)';
592 $def = $parser->parse($sql);
593 self::assertNotNull($def);
594 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['v']->family);
595 }
596 public function testParseBigint(): void
597 {
598 $parser = new PgSqlSchemaParser();
599 $sql = 'CREATE TABLE t (v BIGINT)';
600 $def = $parser->parse($sql);
601 self::assertNotNull($def);
602 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['v']->family);
603 }
604 public function testParseSmallserial(): void
605 {
606 $parser = new PgSqlSchemaParser();
607 $sql = 'CREATE TABLE t (id SMALLSERIAL)';
608 $def = $parser->parse($sql);
609 self::assertNotNull($def);
610 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['id']->family);
611 }
612 public function testParseInt2Int4Int8(): void
613 {
614 $parser = new PgSqlSchemaParser();
615 $def = $parser->parse('CREATE TABLE t (a INT2, b INT4, c INT8)');
616 self::assertNotNull($def);
617 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['a']->family);
618 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['b']->family);
619 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['c']->family);
620 }
621 public function testParseDateType(): void
622 {
623 $parser = new PgSqlSchemaParser();
624 $def = $parser->parse('CREATE TABLE t (v DATE)');
625 self::assertNotNull($def);
626 self::assertSame(ColumnTypeFamily::DATE, $def->typedColumns['v']->family);
627 }
628 public function testParseTimeType(): void
629 {
630 $parser = new PgSqlSchemaParser();
631 $def = $parser->parse('CREATE TABLE t (v TIME)');
632 self::assertNotNull($def);
633 self::assertSame(ColumnTypeFamily::TIME, $def->typedColumns['v']->family);
634 }
635 public function testParseTimetzType(): void
636 {
637 $parser = new PgSqlSchemaParser();
638 $def = $parser->parse('CREATE TABLE t (v TIMETZ)');
639 self::assertNotNull($def);
640 self::assertSame(ColumnTypeFamily::TIME, $def->typedColumns['v']->family);
641 }
642 public function testParseTimeWithTimezoneMultiWord(): void
643 {
644 $parser = new PgSqlSchemaParser();
645 $def = $parser->parse('CREATE TABLE t (v TIME WITH TIME ZONE)');
646 self::assertNotNull($def);
647 self::assertSame('TIME WITH', $def->columnTypes['v']);
648 }
649 public function testParseTimestampWithoutTimezoneMultiWord(): void
650 {
651 $parser = new PgSqlSchemaParser();
652 $def = $parser->parse('CREATE TABLE t (v TIMESTAMP WITHOUT TIME ZONE)');
653 self::assertNotNull($def);
654 self::assertSame('TIMESTAMP WITHOUT', $def->columnTypes['v']);
655 }
656 public function testParseTimestampWithTimezoneMultiWord(): void
657 {
658 $parser = new PgSqlSchemaParser();
659 $def = $parser->parse('CREATE TABLE t (v TIMESTAMP WITH TIME ZONE)');
660 self::assertNotNull($def);
661 self::assertSame('TIMESTAMP WITH', $def->columnTypes['v']);
662 }
663 public function testParseJsonType(): void
664 {
665 $parser = new PgSqlSchemaParser();
666 $def = $parser->parse('CREATE TABLE t (v JSON)');
667 self::assertNotNull($def);
668 self::assertSame(ColumnTypeFamily::JSON, $def->typedColumns['v']->family);
669 }
670 public function testParseBoolType(): void
671 {
672 $parser = new PgSqlSchemaParser();
673 $def = $parser->parse('CREATE TABLE t (v BOOL)');
674 self::assertNotNull($def);
675 self::assertSame(ColumnTypeFamily::BOOLEAN, $def->typedColumns['v']->family);
676 }
677 public function testParseCharType(): void
678 {
679 $parser = new PgSqlSchemaParser();
680 $def = $parser->parse('CREATE TABLE t (v CHAR(5))');
681 self::assertNotNull($def);
682 self::assertSame('CHAR(5)', $def->columnTypes['v']);
683 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['v']->family);
684 }
685 public function testParseCharacterType(): void
686 {
687 $parser = new PgSqlSchemaParser();
688 $def = $parser->parse('CREATE TABLE t (v CHARACTER(10))');
689 self::assertNotNull($def);
690 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['v']->family);
691 }
692 public function testParseCharacterVaryingType(): void
693 {
694 $parser = new PgSqlSchemaParser();
695 $def = $parser->parse('CREATE TABLE t (v CHARACTER VARYING(50))');
696 self::assertNotNull($def);
697 self::assertSame('CHARACTER VARYING(50)', $def->columnTypes['v']);
698 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['v']->family);
699 }
700 public function testParseNameType(): void
701 {
702 $parser = new PgSqlSchemaParser();
703 $def = $parser->parse('CREATE TABLE t (v NAME)');
704 self::assertNotNull($def);
705 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['v']->family);
706 }
707 public function testParseCitextType(): void
708 {
709 $parser = new PgSqlSchemaParser();
710 $def = $parser->parse('CREATE TABLE t (v CITEXT)');
711 self::assertNotNull($def);
712 self::assertSame(ColumnTypeFamily::TEXT, $def->typedColumns['v']->family);
713 }
714 public function testParseVarcharType(): void
715 {
716 $parser = new PgSqlSchemaParser();
717 $def = $parser->parse('CREATE TABLE t (v VARCHAR)');
718 self::assertNotNull($def);
719 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['v']->family);
720 }
721 public function testParseUnknownType(): void
722 {
723 $parser = new PgSqlSchemaParser();
724 $def = $parser->parse('CREATE TABLE t (v GEOMETRY)');
725 self::assertNotNull($def);
726 self::assertSame(ColumnTypeFamily::UNKNOWN, $def->typedColumns['v']->family);
727 }
728 public function testParseArrayType(): void
729 {
730 $parser = new PgSqlSchemaParser();
731 $def = $parser->parse('CREATE TABLE t (v INTEGER[])');
732 self::assertNotNull($def);
733 self::assertSame('INTEGER[]', $def->columnTypes['v']);
734 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['v']->family);
735 }
736 public function testParsePrimaryKeyImpliesNotNull(): void
737 {
738 $parser = new PgSqlSchemaParser();
739 $def = $parser->parse('CREATE TABLE t (id INTEGER PRIMARY KEY)');
740 self::assertNotNull($def);
741 self::assertContains('id', $def->notNullColumns);
742 self::assertSame(['id'], $def->primaryKeys);
743 }
744 public function testParsePrimaryKeyConstraintDoesNotDuplicate(): void
745 {
746 $parser = new PgSqlSchemaParser();
747 $def = $parser->parse('CREATE TABLE t (id INTEGER PRIMARY KEY, PRIMARY KEY (id))');
748 self::assertNotNull($def);
749 self::assertSame(['id'], $def->primaryKeys);
750 }
751 public function testParseUniqueConstraintWithoutName(): void
752 {
753 $parser = new PgSqlSchemaParser();
754 $def = $parser->parse('CREATE TABLE t (id INTEGER, email TEXT, UNIQUE (email))');
755 self::assertNotNull($def);
756 self::assertContains(['email'], $def->uniqueConstraints);
757 }
758 public function testParseCheckConstraintIgnored(): void
759 {
760 $parser = new PgSqlSchemaParser();
761 $def = $parser->parse('CREATE TABLE t (id INTEGER, CHECK (id > 0))');
762 self::assertNotNull($def);
763 self::assertSame(['id'], $def->columns);
764 }
765 public function testParseForeignKeyConstraintIgnored(): void
766 {
767 $parser = new PgSqlSchemaParser();
768 $def = $parser->parse('CREATE TABLE t (id INTEGER, FOREIGN KEY (id) REFERENCES other(id))');
769 self::assertNotNull($def);
770 self::assertSame(['id'], $def->columns);
771 }
772 public function testParseExcludeConstraintIgnored(): void
773 {
774 $parser = new PgSqlSchemaParser();
775 $def = $parser->parse('CREATE TABLE t (id INTEGER, EXCLUDE USING gist (id WITH =))');
776 self::assertNotNull($def);
777 self::assertSame(['id'], $def->columns);
778 }
779 public function testParseColumnDefaultNotNull(): void
780 {
781 $parser = new PgSqlSchemaParser();
782 $def = $parser->parse("CREATE TABLE t (s TEXT NOT NULL DEFAULT 'active')");
783 self::assertNotNull($def);
784 self::assertContains('s', $def->notNullColumns);
785 }
786 public function testParseUnloggedTable(): void
787 {
788 $parser = new PgSqlSchemaParser();
789 $def = $parser->parse('CREATE UNLOGGED TABLE t (id INTEGER)');
790 self::assertNotNull($def);
791 self::assertSame(['id'], $def->columns);
792 }
793 public function testParseTempTable(): void
794 {
795 $parser = new PgSqlSchemaParser();
796 $def = $parser->parse('CREATE TEMP TABLE t (id INTEGER)');
797 self::assertNotNull($def);
798 self::assertSame(['id'], $def->columns);
799 }
800 public function testParseCompositeUniqueConstraint(): void
801 {
802 $parser = new PgSqlSchemaParser();
803 $def = $parser->parse('CREATE TABLE t (a INTEGER, b INTEGER, CONSTRAINT uq_ab UNIQUE (a, b))');
804 self::assertNotNull($def);
805 self::assertSame(['a', 'b'], $def->uniqueConstraints['uq_ab']);
806 }
807 public function testParseColumnTypeUppercased(): void
808 {
809 $parser = new PgSqlSchemaParser();
810 $def = $parser->parse('CREATE TABLE t (v integer not null)');
811 self::assertNotNull($def);
812 self::assertSame('INTEGER', $def->columnTypes['v']);
813 self::assertContains('v', $def->notNullColumns);
814 }
815 public function testParseUniqueConstraintReferencingNonExistentColumnReturnsNull(): void
816 {
817 $parser = new PgSqlSchemaParser();
818 $def = $parser->parse('CREATE TABLE t (id INTEGER, CONSTRAINT uq UNIQUE (nonexistent))');
819 self::assertNull($def);
820 }
821 public function testParseTimestamptzColumnType(): void
822 {
823 $parser = new PgSqlSchemaParser();
824 $def = $parser->parse('CREATE TABLE t (v TIMESTAMPTZ)');
825 self::assertNotNull($def);
826 self::assertSame('TIMESTAMPTZ', $def->columnTypes['v']);
827 }
828 public function testParseEscapedDoubleQuoteIdentifierReturnsNull(): void
829 {
830 $parser = new PgSqlSchemaParser();
831 self::assertNull($parser->parse('CREATE TABLE t ("a""b" INTEGER)'));
832 }
833 public function testParseDefaultWithParentheses(): void
834 {
835 $parser = new PgSqlSchemaParser();
836 $def = $parser->parse('CREATE TABLE t (v TIMESTAMP DEFAULT NOW(), id INTEGER)');
837 self::assertNotNull($def);
838 self::assertSame(['v', 'id'], $def->columns);
839 }
840 public function testParseColumnWithSingleQuoteDefault(): void
841 {
842 $parser = new PgSqlSchemaParser();
843 $def = $parser->parse("CREATE TABLE t (v TEXT DEFAULT 'a,b', id INTEGER)");
844 self::assertNotNull($def);
845 self::assertSame(['v', 'id'], $def->columns);
846 }
847 public function testParseIntType(): void
848 {
849 $parser = new PgSqlSchemaParser();
850 $def = $parser->parse('CREATE TABLE t (v INT)');
851 self::assertNotNull($def);
852 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['v']->family);
853 self::assertSame('INT', $def->columnTypes['v']);
854 }
855 public function testParseTimeWithoutTimezoneMultiWord(): void
856 {
857 $parser = new PgSqlSchemaParser();
858 $def = $parser->parse('CREATE TABLE t (v TIME WITHOUT TIME ZONE)');
859 self::assertNotNull($def);
860 self::assertSame('TIME WITHOUT', $def->columnTypes['v']);
861 }
862 public function testParseColumnWithGeneratedClause(): void
863 {
864 $parser = new PgSqlSchemaParser();
865 $def = $parser->parse('CREATE TABLE t (id INTEGER GENERATED ALWAYS AS IDENTITY)');
866 self::assertNotNull($def);
867 self::assertSame(['id'], $def->columns);
868 self::assertSame(['id' => IdentityGenerationStrategy::Sequence], $def->identityStrategies);
869 }
870 public function testParseMultiWordTypeNotConflictWithConstraint(): void
871 {
872 $parser = new PgSqlSchemaParser();
873 $def = $parser->parse('CREATE TABLE t (v DOUBLE PRECISION NOT NULL)');
874 self::assertNotNull($def);
875 self::assertSame('DOUBLE PRECISION', $def->columnTypes['v']);
876 self::assertContains('v', $def->notNullColumns);
877 }
878 public function testParseBitType(): void
879 {
880 $parser = new PgSqlSchemaParser();
881 $def = $parser->parse('CREATE TABLE t (v BIT(8))');
882 self::assertNotNull($def);
883 self::assertSame('BIT(8)', $def->columnTypes['v']);
884 }
885 public function testParseIntervalType(): void
886 {
887 $parser = new PgSqlSchemaParser();
888 $def = $parser->parse('CREATE TABLE t (v INTERVAL)');
889 self::assertNotNull($def);
890 self::assertSame('INTERVAL', $def->columnTypes['v']);
891 }
892 public function testParseColumnNamedConstraintKeyword(): void
893 {
894 $parser = new PgSqlSchemaParser();
895 $def = $parser->parse('CREATE TABLE t ("primary" INTEGER, "check" TEXT)');
896 self::assertNotNull($def);
897 self::assertSame(['primary', 'check'], $def->columns);
898 }
899 public function testParseNoColumnsReturnsNull(): void
900 {
901 $parser = new PgSqlSchemaParser();
902 self::assertNull($parser->parse('CREATE TABLE t (PRIMARY KEY (id))'));
903 }
904 public function testParseColumnNativeTypeIsUpper(): void
905 {
906 $parser = new PgSqlSchemaParser();
907 $def = $parser->parse('CREATE TABLE t (v varchar(50))');
908 self::assertNotNull($def);
909 self::assertSame('VARCHAR(50)', $def->columnTypes['v']);
910 self::assertSame('VARCHAR(50)', $def->typedColumns['v']->nativeType);
911 }
912 public function testParseMultilineCreateTable(): void
913 {
914 $parser = new PgSqlSchemaParser();
915 $def = $parser->parse("CREATE TABLE t (\nid INTEGER,\nname TEXT\n)");
916 self::assertNotNull($def);
917 self::assertSame(['id', 'name'], $def->columns);
918 }
919 public function testParseLowercaseCreateTable(): void
920 {
921 $parser = new PgSqlSchemaParser();
922 $def = $parser->parse('create table t (id integer, name text)');
923 self::assertNotNull($def);
924 self::assertSame(['id', 'name'], $def->columns);
925 }
926 public function testParseLowercasePrimaryKey(): void
927 {
928 $parser = new PgSqlSchemaParser();
929 $def = $parser->parse('create table t (id integer, name text, primary key (id))');
930 self::assertNotNull($def);
931 self::assertSame(['id'], $def->primaryKeys);
932 }
933 public function testParseLowercaseUnique(): void
934 {
935 $parser = new PgSqlSchemaParser();
936 $def = $parser->parse('create table t (id integer, email text, unique (email))');
937 self::assertNotNull($def);
938 self::assertNotEmpty($def->uniqueConstraints);
939 }
940 public function testParseLowercaseNotNull(): void
941 {
942 $parser = new PgSqlSchemaParser();
943 $def = $parser->parse('create table t (id integer not null, name text)');
944 self::assertNotNull($def);
945 self::assertContains('id', $def->notNullColumns);
946 }
947 public function testParseLowercaseInlinePrimaryKey(): void
948 {
949 $parser = new PgSqlSchemaParser();
950 $def = $parser->parse('create table t (id integer primary key, name text)');
951 self::assertNotNull($def);
952 self::assertSame(['id'], $def->primaryKeys);
953 }
954 public function testParseLowercaseInlineUnique(): void
955 {
956 $parser = new PgSqlSchemaParser();
957 $def = $parser->parse('create table t (id integer, email text unique)');
958 self::assertNotNull($def);
959 self::assertNotEmpty($def->uniqueConstraints);
960 }
961 public function testParseMultilineColumnDefinition(): void
962 {
963 $parser = new PgSqlSchemaParser();
964 $def = $parser->parse("CREATE TABLE t (\nid\nINTEGER\nNOT NULL,\nname TEXT\n)");
965 self::assertNotNull($def);
966 self::assertSame(['id', 'name'], $def->columns);
967 self::assertContains('id', $def->notNullColumns);
968 }
969 public function testParseColumnWithLeadingWhitespace(): void
970 {
971 $parser = new PgSqlSchemaParser();
972 $def = $parser->parse('CREATE TABLE t ( id INTEGER, name TEXT )');
973 self::assertNotNull($def);
974 self::assertSame(['id', 'name'], $def->columns);
975 }
976 public function testParseDoubleQuotedColumnName(): void
977 {
978 $parser = new PgSqlSchemaParser();
979 $def = $parser->parse('CREATE TABLE t ("Column Name" TEXT, id INTEGER)');
980 self::assertNotNull($def);
981 self::assertContains('Column Name', $def->columns);
982 }
983 public function testParseCharTypeNoLength(): void
984 {
985 $parser = new PgSqlSchemaParser();
986 $def = $parser->parse('CREATE TABLE t (c CHAR)');
987 self::assertNotNull($def);
988 self::assertSame('CHAR', $def->columnTypes['c']);
989 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['c']->family);
990 }
991 public function testParseCharTypeWithLength(): void
992 {
993 $parser = new PgSqlSchemaParser();
994 $def = $parser->parse('CREATE TABLE t (c CHAR(5))');
995 self::assertNotNull($def);
996 self::assertSame('CHAR(5)', $def->columnTypes['c']);
997 }
998 public function testParseVarcharNoLength(): void
999 {
1000 $parser = new PgSqlSchemaParser();
1001 $def = $parser->parse('CREATE TABLE t (v VARCHAR)');
1002 self::assertNotNull($def);
1003 self::assertSame('VARCHAR', $def->columnTypes['v']);
1004 }
1005 public function testParseNumericWithPrecisionOnly(): void
1006 {
1007 $parser = new PgSqlSchemaParser();
1008 $def = $parser->parse('CREATE TABLE t (n NUMERIC(10))');
1009 self::assertNotNull($def);
1010 self::assertSame('NUMERIC(10)', $def->columnTypes['n']);
1011 }
1012 public function testParseNumericNoParams(): void
1013 {
1014 $parser = new PgSqlSchemaParser();
1015 $def = $parser->parse('CREATE TABLE t (n NUMERIC)');
1016 self::assertNotNull($def);
1017 self::assertSame('NUMERIC', $def->columnTypes['n']);
1018 }
1019 public function testParseDoublePrecisionType(): void
1020 {
1021 $parser = new PgSqlSchemaParser();
1022 $def = $parser->parse('CREATE TABLE t (d DOUBLE PRECISION)');
1023 self::assertNotNull($def);
1024 self::assertSame('DOUBLE PRECISION', $def->columnTypes['d']);
1025 self::assertSame(ColumnTypeFamily::DOUBLE, $def->typedColumns['d']->family);
1026 }
1027 public function testParseTimestampType(): void
1028 {
1029 $parser = new PgSqlSchemaParser();
1030 $def = $parser->parse('CREATE TABLE t (ts TIMESTAMP)');
1031 self::assertNotNull($def);
1032 self::assertSame(ColumnTypeFamily::TIMESTAMP, $def->typedColumns['ts']->family);
1033 }
1034 public function testParseBooleanType(): void
1035 {
1036 $parser = new PgSqlSchemaParser();
1037 $def = $parser->parse('CREATE TABLE t (b BOOLEAN)');
1038 self::assertNotNull($def);
1039 self::assertSame(ColumnTypeFamily::BOOLEAN, $def->typedColumns['b']->family);
1040 }
1041 public function testParseJsonbType(): void
1042 {
1043 $parser = new PgSqlSchemaParser();
1044 $def = $parser->parse('CREATE TABLE t (j JSONB)');
1045 self::assertNotNull($def);
1046 self::assertSame(ColumnTypeFamily::JSON, $def->typedColumns['j']->family);
1047 }
1048 public function testParseSmallintType(): void
1049 {
1050 $parser = new PgSqlSchemaParser();
1051 $def = $parser->parse('CREATE TABLE t (s SMALLINT)');
1052 self::assertNotNull($def);
1053 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['s']->family);
1054 }
1055 public function testParseBigintType(): void
1056 {
1057 $parser = new PgSqlSchemaParser();
1058 $def = $parser->parse('CREATE TABLE t (b BIGINT)');
1059 self::assertNotNull($def);
1060 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['b']->family);
1061 }
1062 public function testParseTextType(): void
1063 {
1064 $parser = new PgSqlSchemaParser();
1065 $def = $parser->parse('CREATE TABLE t (t TEXT)');
1066 self::assertNotNull($def);
1067 self::assertSame(ColumnTypeFamily::TEXT, $def->typedColumns['t']->family);
1068 }
1069 public function testParseMultiDimensionalArray(): void
1070 {
1071 $parser = new PgSqlSchemaParser();
1072 $def = $parser->parse('CREATE TABLE t (a INTEGER[][])');
1073 self::assertNotNull($def);
1074 self::assertSame('INTEGER[][]', $def->columnTypes['a']);
1075 }
1076 public function testParseNamedConstraintPrimaryKey(): void
1077 {
1078 $parser = new PgSqlSchemaParser();
1079 $def = $parser->parse('CREATE TABLE t (id INTEGER, CONSTRAINT pk_t PRIMARY KEY (id))');
1080 self::assertNotNull($def);
1081 self::assertSame(['id'], $def->primaryKeys);
1082 }
1083 public function testParseNamedConstraintUnique(): void
1084 {
1085 $parser = new PgSqlSchemaParser();
1086 $def = $parser->parse('CREATE TABLE t (id INTEGER, email TEXT, CONSTRAINT uq_email UNIQUE (email))');
1087 self::assertNotNull($def);
1088 self::assertArrayHasKey('uq_email', $def->uniqueConstraints);
1089 self::assertSame(['email'], $def->uniqueConstraints['uq_email']);
1090 }
1091 public function testParseConstraintEntryCheck(): void
1092 {
1093 $parser = new PgSqlSchemaParser();
1094 $def = $parser->parse('CREATE TABLE t (id INTEGER, CHECK (id > 0))');
1095 self::assertNotNull($def);
1096 self::assertSame(['id'], $def->columns);
1097 }
1098 public function testParseConstraintEntryForeignKey(): void
1099 {
1100 $parser = new PgSqlSchemaParser();
1101 $def = $parser->parse('CREATE TABLE t (id INTEGER, FOREIGN KEY (id) REFERENCES other(id))');
1102 self::assertNotNull($def);
1103 self::assertSame(['id'], $def->columns);
1104 }
1105 public function testParseConstraintEntryExclude(): void
1106 {
1107 $parser = new PgSqlSchemaParser();
1108 $def = $parser->parse('CREATE TABLE t (id INTEGER, EXCLUDE USING gist (id WITH =))');
1109 self::assertNotNull($def);
1110 self::assertSame(['id'], $def->columns);
1111 }
1112 public function testParseWithLeadingWhitespace(): void
1113 {
1114 $parser = new PgSqlSchemaParser();
1115 $def = $parser->parse(' CREATE TABLE t (id INTEGER)');
1116 self::assertNotNull($def);
1117 self::assertSame(['id'], $def->columns);
1118 }
1119 public function testParseNonCreateTableReturnsNull(): void
1120 {
1121 $parser = new PgSqlSchemaParser();
1122 self::assertNull($parser->parse('SELECT 1'));
1123 }
1124 public function testParseEmptyBody(): void
1125 {
1126 $parser = new PgSqlSchemaParser();
1127 self::assertNull($parser->parse('CREATE TABLE t ()'));
1128 }
1129 public function testParseSingleQuoteInDefaultValue(): void
1130 {
1131 $parser = new PgSqlSchemaParser();
1132 $def = $parser->parse("CREATE TABLE t (name TEXT DEFAULT 'it''s', id INTEGER)");
1133 self::assertNotNull($def);
1134 self::assertSame(['name', 'id'], $def->columns);
1135 }
1136 public function testParseDoubleQuotedColumnWithComma(): void
1137 {
1138 $parser = new PgSqlSchemaParser();
1139 $def = $parser->parse('CREATE TABLE t ("a,b" TEXT, id INTEGER)');
1140 self::assertNotNull($def);
1141 self::assertContains('a,b', $def->columns);
1142 }
1143 public function testParsePrimaryKeyAddsToNotNull(): void
1144 {
1145 $parser = new PgSqlSchemaParser();
1146 $def = $parser->parse('CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)');
1147 self::assertNotNull($def);
1148 self::assertContains('id', $def->notNullColumns);
1149 self::assertContains('id', $def->primaryKeys);
1150 }
1151 public function testParseUniqueConstraintWithNonExistentColumnReturnsNull(): void
1152 {
1153 $parser = new PgSqlSchemaParser();
1154 self::assertNull($parser->parse('CREATE TABLE t (id INTEGER, UNIQUE (nonexistent))'));
1155 }
1156 public function testParseIfNotExistsTable(): void
1157 {
1158 $parser = new PgSqlSchemaParser();
1159 $def = $parser->parse('CREATE TABLE IF NOT EXISTS t (id INTEGER)');
1160 self::assertNotNull($def);
1161 self::assertSame(['id'], $def->columns);
1162 }
1163 public function testRejectsMalformedCreateTablePreambleAndBodyDelimiters(): void
1164 {
1165 $parser = new PgSqlSchemaParser();
1166 self::assertNull($parser->parse('CREATE TABLE IF EXISTS t (id INTEGER)'));
1167 self::assertNull($parser->parse('CREATE TABLE IF WRONG EXISTS t (id INTEGER)'));
1168 self::assertNull($parser->parse('CREATE TABLE IF NOT MISSING t (id INTEGER)'));
1169 self::assertNull($parser->parse('CREATE TABLE t [id INTEGER])'));
1170 self::assertNull($parser->parse('CREATE TABLE [t] (id INTEGER)'));
1171 self::assertNull($parser->parse('CREATE TABLE public. (id INTEGER)'));
1172 self::assertNull($parser->parse('CREATE TABLE t'));
1173 }
1174 public function testParseLowercaseTimestamp(): void
1175 {
1176 $parser = new PgSqlSchemaParser();
1177 $def = $parser->parse('create table t (ts timestamp)');
1178 self::assertNotNull($def);
1179 self::assertSame(ColumnTypeFamily::TIMESTAMP, $def->typedColumns['ts']->family);
1180 }
1181 public function testParseLowercaseBoolean(): void
1182 {
1183 $parser = new PgSqlSchemaParser();
1184 $def = $parser->parse('create table t (b boolean)');
1185 self::assertNotNull($def);
1186 self::assertSame(ColumnTypeFamily::BOOLEAN, $def->typedColumns['b']->family);
1187 }
1188 public function testParseDoubleQuotedTableName(): void
1189 {
1190 $parser = new PgSqlSchemaParser();
1191 $def = $parser->parse('CREATE TABLE "My Table" (id INTEGER)');
1192 self::assertNotNull($def);
1193 self::assertSame(['id'], $def->columns);
1194 }
1195 public function testParseInt2Type(): void
1196 {
1197 $parser = new PgSqlSchemaParser();
1198 $def = $parser->parse('CREATE TABLE t (n INT2)');
1199 self::assertNotNull($def);
1200 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['n']->family);
1201 }
1202 public function testParseInt4Type(): void
1203 {
1204 $parser = new PgSqlSchemaParser();
1205 $def = $parser->parse('CREATE TABLE t (n INT4)');
1206 self::assertNotNull($def);
1207 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['n']->family);
1208 }
1209 public function testParseInt8Type(): void
1210 {
1211 $parser = new PgSqlSchemaParser();
1212 $def = $parser->parse('CREATE TABLE t (n INT8)');
1213 self::assertNotNull($def);
1214 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['n']->family);
1215 }
1216 public function testParseFloat4Type(): void
1217 {
1218 $parser = new PgSqlSchemaParser();
1219 $def = $parser->parse('CREATE TABLE t (f FLOAT4)');
1220 self::assertNotNull($def);
1221 self::assertSame(ColumnTypeFamily::FLOAT, $def->typedColumns['f']->family);
1222 }
1223 public function testParseFloat8Type(): void
1224 {
1225 $parser = new PgSqlSchemaParser();
1226 $def = $parser->parse('CREATE TABLE t (f FLOAT8)');
1227 self::assertNotNull($def);
1228 self::assertSame(ColumnTypeFamily::DOUBLE, $def->typedColumns['f']->family);
1229 }
1230 public function testParseSmallserialType(): void
1231 {
1232 $parser = new PgSqlSchemaParser();
1233 $def = $parser->parse('CREATE TABLE t (s SMALLSERIAL)');
1234 self::assertNotNull($def);
1235 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['s']->family);
1236 self::assertSame(['s' => IdentityGenerationStrategy::Sequence], $def->identityStrategies);
1237 }
1238 public function testParseColumnTypeWithWhitespaceBeforeParams(): void
1239 {
1240 $parser = new PgSqlSchemaParser();
1241 $def = $parser->parse('CREATE TABLE t (v VARCHAR (50))');
1242 self::assertNotNull($def);
1243 self::assertSame('VARCHAR(50)', $def->columnTypes['v']);
1244 }
1245 public function testParseMultiWordTypePrefix(): void
1246 {
1247 $parser = new PgSqlSchemaParser();
1248 $def = $parser->parse('CREATE TABLE t (d DOUBLE PRECISION, t TIMESTAMP)');
1249 self::assertNotNull($def);
1250 self::assertSame('DOUBLE PRECISION', $def->columnTypes['d']);
1251 self::assertSame('TIMESTAMP', $def->columnTypes['t']);
1252 }
1253 public function testParseMultiWordTypeWithConstraintAfter(): void
1254 {
1255 $parser = new PgSqlSchemaParser();
1256 $def = $parser->parse('CREATE TABLE t (d DOUBLE PRECISION NOT NULL)');
1257 self::assertNotNull($def);
1258 self::assertSame('DOUBLE PRECISION', $def->columnTypes['d']);
1259 self::assertContains('d', $def->notNullColumns);
1260 }
1261 public function testParseTimestampMultiWordFollowedByConstraint(): void
1262 {
1263 $parser = new PgSqlSchemaParser();
1264 $def = $parser->parse('CREATE TABLE t (ts TIMESTAMP NOT NULL)');
1265 self::assertNotNull($def);
1266 self::assertSame('TIMESTAMP', $def->columnTypes['ts']);
1267 self::assertContains('ts', $def->notNullColumns);
1268 }
1269 public function testParseColumnWithDefaultAndNotNull(): void
1270 {
1271 $parser = new PgSqlSchemaParser();
1272 $def = $parser->parse('CREATE TABLE t (active BOOLEAN DEFAULT TRUE NOT NULL)');
1273 self::assertNotNull($def);
1274 self::assertContains('active', $def->notNullColumns);
1275 }
1276 public function testParseLowercaseDoublePrecision(): void
1277 {
1278 $parser = new PgSqlSchemaParser();
1279 $def = $parser->parse('create table t (d double precision)');
1280 self::assertNotNull($def);
1281 self::assertSame('DOUBLE PRECISION', $def->columnTypes['d']);
1282 }
1283 public function testParseCompositeUnique(): void
1284 {
1285 $parser = new PgSqlSchemaParser();
1286 $def = $parser->parse('CREATE TABLE t (a INTEGER, b INTEGER, UNIQUE (a, b))');
1287 self::assertNotNull($def);
1288 $uk = array_values($def->uniqueConstraints);
1289 self::assertSame(['a', 'b'], $uk[0]);
1290 }
1291 public function testParsePrimaryKeyNoDuplicate(): void
1292 {
1293 $parser = new PgSqlSchemaParser();
1294 $def = $parser->parse('CREATE TABLE t (id INTEGER PRIMARY KEY, PRIMARY KEY (id))');
1295 self::assertNotNull($def);
1296 self::assertCount(1, $def->primaryKeys);
1297 }
1298 public function testParseInlineUniqueColumnNameFormat(): void
1299 {
1300 $parser = new PgSqlSchemaParser();
1301 $def = $parser->parse('CREATE TABLE t (email TEXT UNIQUE)');
1302 self::assertNotNull($def);
1303 self::assertArrayHasKey('email_UNIQUE', $def->uniqueConstraints);
1304 self::assertSame(['email'], $def->uniqueConstraints['email_UNIQUE']);
1305 }
1306 public function testParseColumnDefinitionWithMultilineTypeAndConstraints(): void
1307 {
1308 $parser = new PgSqlSchemaParser();
1309 $sql = "CREATE TABLE t (bio TEXT\nNOT NULL\nUNIQUE)";
1310 $def = $parser->parse($sql);
1311 self::assertNotNull($def);
1312 self::assertContains('bio', $def->notNullColumns);
1313 self::assertArrayHasKey('bio_UNIQUE', $def->uniqueConstraints);
1314 }
1315 public function testParseLowercaseConstraintPrimaryKey(): void
1316 {
1317 $parser = new PgSqlSchemaParser();
1318 $def = $parser->parse('CREATE TABLE t (id integer, primary key (id))');
1319 self::assertNotNull($def);
1320 self::assertSame(['id'], $def->primaryKeys);
1321 }
1322 public function testParseLowercaseConstraintUnique(): void
1323 {
1324 $parser = new PgSqlSchemaParser();
1325 $def = $parser->parse('CREATE TABLE t (email text, unique (email))');
1326 self::assertNotNull($def);
1327 $uk = array_values($def->uniqueConstraints);
1328 self::assertSame(['email'], $uk[0]);
1329 }
1330 public function testParseLowercaseConstraintKeyword(): void
1331 {
1332 $parser = new PgSqlSchemaParser();
1333 $def = $parser->parse('CREATE TABLE t (id integer, constraint pk_t primary key (id))');
1334 self::assertNotNull($def);
1335 self::assertSame(['id'], $def->primaryKeys);
1336 }
1337 public function testParseArrayTypeColumn(): void
1338 {
1339 $parser = new PgSqlSchemaParser();
1340 $def = $parser->parse('CREATE TABLE t (tags TEXT[])');
1341 self::assertNotNull($def);
1342 self::assertSame(['tags'], $def->columns);
1343 self::assertSame('TEXT[]', $def->columnTypes['tags']);
1344 }
1345 public function testParseMultiWordTypeWithConstraintKeyword(): void
1346 {
1347 $parser = new PgSqlSchemaParser();
1348 $def = $parser->parse('CREATE TABLE t (ts TIMESTAMP NOT NULL)');
1349 self::assertNotNull($def);
1350 self::assertSame('TIMESTAMP', $def->columnTypes['ts']);
1351 self::assertContains('ts', $def->notNullColumns);
1352 }
1353 public function testParseMultiWordTypeTimestampWithTimeZone(): void
1354 {
1355 $parser = new PgSqlSchemaParser();
1356 $def = $parser->parse('CREATE TABLE t (d DOUBLE PRECISION, ts TIMESTAMP)');
1357 self::assertNotNull($def);
1358 self::assertSame('DOUBLE PRECISION', $def->columnTypes['d']);
1359 }
1360 public function testParseEmptyEntrySkipped(): void
1361 {
1362 $parser = new PgSqlSchemaParser();
1363 $def = $parser->parse('CREATE TABLE t (id INTEGER, , name TEXT)');
1364 self::assertNotNull($def);
1365 self::assertSame(['id', 'name'], $def->columns);
1366 }
1367 public function testParseQuotedColumnName(): void
1368 {
1369 $parser = new PgSqlSchemaParser();
1370 $def = $parser->parse('CREATE TABLE t ("My Column" TEXT)');
1371 self::assertNotNull($def);
1372 self::assertSame(['My Column'], $def->columns);
1373 }
1374 public function testParseDefaultValueWithStringContainingComma(): void
1375 {
1376 $parser = new PgSqlSchemaParser();
1377 $def = $parser->parse("CREATE TABLE t (id INTEGER, note TEXT DEFAULT 'a,b,c')");
1378 self::assertNotNull($def);
1379 self::assertSame(['id', 'note'], $def->columns);
1380 }
1381 public function testParseCheckConstraintSkipped(): void
1382 {
1383 $parser = new PgSqlSchemaParser();
1384 $def = $parser->parse('CREATE TABLE t (age INTEGER, CHECK (age > 0))');
1385 self::assertNotNull($def);
1386 self::assertSame(['age'], $def->columns);
1387 }
1388 public function testParseForeignKeyConstraintSkipped(): void
1389 {
1390 $parser = new PgSqlSchemaParser();
1391 $def = $parser->parse('CREATE TABLE t (user_id INTEGER, FOREIGN KEY (user_id) REFERENCES users(id))');
1392 self::assertNotNull($def);
1393 self::assertSame(['user_id'], $def->columns);
1394 }
1395 public function testParseExcludeConstraintSkipped(): void
1396 {
1397 $parser = new PgSqlSchemaParser();
1398 $def = $parser->parse('CREATE TABLE t (tsrange TSRANGE, EXCLUDE USING gist (tsrange WITH &&))');
1399 self::assertNotNull($def);
1400 self::assertSame(['tsrange'], $def->columns);
1401 }
1402 public function testParseInvalidSqlReturnsNull(): void
1403 {
1404 $parser = new PgSqlSchemaParser();
1405 self::assertNull($parser->parse('NOT A CREATE TABLE'));
1406 }
1407 public function testParseTemporaryTableLowercase(): void
1408 {
1409 $parser = new PgSqlSchemaParser();
1410 $def = $parser->parse('create temporary table t (id integer)');
1411 self::assertNotNull($def);
1412 self::assertSame(['id'], $def->columns);
1413 }
1414 public function testParseTempTableLowercase(): void
1415 {
1416 $parser = new PgSqlSchemaParser();
1417 $def = $parser->parse('create temp table t (id integer)');
1418 self::assertNotNull($def);
1419 self::assertSame(['id'], $def->columns);
1420 }
1421 public function testParseUnloggedTableLowercase(): void
1422 {
1423 $parser = new PgSqlSchemaParser();
1424 $def = $parser->parse('create unlogged table t (id integer)');
1425 self::assertNotNull($def);
1426 self::assertSame(['id'], $def->columns);
1427 }
1428 public function testParseIfNotExistsLowercase(): void
1429 {
1430 $parser = new PgSqlSchemaParser();
1431 $def = $parser->parse('create table if not exists t (id integer)');
1432 self::assertNotNull($def);
1433 self::assertSame(['id'], $def->columns);
1434 }
1435 public function testParseColumnWithDoubleQuoteName(): void
1436 {
1437 $parser = new PgSqlSchemaParser();
1438 $def = $parser->parse('CREATE TABLE t ("col_name" TEXT)');
1439 self::assertNotNull($def);
1440 self::assertSame(['col_name'], $def->columns);
1441 }
1442 public function testParseSingleQuoteInDefault(): void
1443 {
1444 $parser = new PgSqlSchemaParser();
1445 $def = $parser->parse("CREATE TABLE t (name TEXT DEFAULT 'it''s', id INTEGER)");
1446 self::assertNotNull($def);
1447 self::assertSame(['name', 'id'], $def->columns);
1448 }
1449 public function testParseTypedColumnsPresent(): void
1450 {
1451 $parser = new PgSqlSchemaParser();
1452 $def = $parser->parse('CREATE TABLE t (id INTEGER, name TEXT)');
1453 self::assertNotNull($def);
1454 self::assertArrayHasKey('id', $def->typedColumns);
1455 self::assertArrayHasKey('name', $def->typedColumns);
1456 self::assertSame(ColumnTypeFamily::INTEGER, $def->typedColumns['id']->family);
1457 self::assertSame(ColumnTypeFamily::TEXT, $def->typedColumns['name']->family);
1458 }
1459 public function testParseColumnWithLeadingWhitespaceInDefinition(): void
1460 {
1461 $parser = new PgSqlSchemaParser();
1462 $def = $parser->parse("CREATE TABLE t (\n id INTEGER,\n name TEXT\n)");
1463 self::assertNotNull($def);
1464 self::assertSame(['id', 'name'], $def->columns);
1465 }
1466 public function testParseColumnDefinitionLowercaseType(): void
1467 {
1468 $parser = new PgSqlSchemaParser();
1469 $def = $parser->parse('CREATE TABLE t (id integer, name text)');
1470 self::assertNotNull($def);
1471 self::assertSame('INTEGER', $def->columnTypes['id']);
1472 self::assertSame('TEXT', $def->columnTypes['name']);
1473 }
1474 public function testParseColumnWithDefaultContainingParenInString(): void
1475 {
1476 $parser = new PgSqlSchemaParser();
1477 $def = $parser->parse("CREATE TABLE t (note TEXT DEFAULT '(value)', id INTEGER)");
1478 self::assertNotNull($def);
1479 self::assertSame(['note', 'id'], $def->columns);
1480 }
1481 public function testParseMultiWordTypeDoublePrecsion(): void
1482 {
1483 $parser = new PgSqlSchemaParser();
1484 $def = $parser->parse('CREATE TABLE t (val DOUBLE PRECISION)');
1485 self::assertNotNull($def);
1486 self::assertSame('DOUBLE PRECISION', $def->columnTypes['val']);
1487 self::assertSame(ColumnTypeFamily::DOUBLE, $def->typedColumns['val']->family);
1488 }
1489 public function testParseVarcharWithLength(): void
1490 {
1491 $parser = new PgSqlSchemaParser();
1492 $def = $parser->parse('CREATE TABLE t (name VARCHAR(255))');
1493 self::assertNotNull($def);
1494 self::assertSame('VARCHAR(255)', $def->columnTypes['name']);
1495 self::assertSame(ColumnTypeFamily::STRING, $def->typedColumns['name']->family);
1496 }
1497 public function testParseUnknownTypeFamily(): void
1498 {
1499 $parser = new PgSqlSchemaParser();
1500 $def = $parser->parse('CREATE TABLE t (geom GEOMETRY)');
1501 self::assertNotNull($def);
1502 self::assertSame(ColumnTypeFamily::UNKNOWN, $def->typedColumns['geom']->family);
1503 }
1504 public function testParseUniqueConstraintReferencesValidColumn(): void
1505 {
1506 $parser = new PgSqlSchemaParser();
1507 $def = $parser->parse('CREATE TABLE t (id INTEGER, name TEXT, UNIQUE(name))');
1508 self::assertNotNull($def);
1509 self::assertNotEmpty($def->uniqueConstraints);
1510 self::assertContains(['name'], $def->uniqueConstraints);
1511 }
1512 public function testParsePrimaryKeyConstraintInColumn(): void
1513 {
1514 $parser = new PgSqlSchemaParser();
1515 $def = $parser->parse('CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)');
1516 self::assertNotNull($def);
1517 self::assertSame(['id'], $def->primaryKeys);
1518 self::assertContains('id', $def->notNullColumns);
1519 }
1520 public function testParseColumnWithLeadingWhitespaceInType(): void
1521 {
1522 $parser = new PgSqlSchemaParser();
1523 $def = $parser->parse("CREATE TABLE t (\n id INTEGER\n)");
1524 self::assertNotNull($def);
1525 self::assertSame(['id'], $def->columns);
1526 }
1527 public function testParseConstraintLowercaseConstraintKeyword(): void
1528 {
1529 $parser = new PgSqlSchemaParser();
1530 $def = $parser->parse('CREATE TABLE t (id INTEGER, name TEXT, constraint uq_name UNIQUE(name))');
1531 self::assertNotNull($def);
1532 self::assertNotEmpty($def->uniqueConstraints);
1533 self::assertArrayHasKey('uq_name', $def->uniqueConstraints);
1534 self::assertSame(['name'], $def->uniqueConstraints['uq_name']);
1535 }
1536 public function testParseIsConstraintEntryWithLeadingWhitespace(): void
1537 {
1538 $parser = new PgSqlSchemaParser();
1539 $def = $parser->parse("CREATE TABLE t (\n id INTEGER,\n PRIMARY KEY (id)\n)");
1540 self::assertNotNull($def);
1541 self::assertSame(['id'], $def->primaryKeys);
1542 }
1543 public function testParseColumnDefinitionMultilineEntry(): void
1544 {
1545 $parser = new PgSqlSchemaParser();
1546 $def = $parser->parse("CREATE TABLE t (id\nINTEGER)");
1547 self::assertNotNull($def);
1548 self::assertSame(['id'], $def->columns);
1549 }
1550 public function testParseColumnArrayTypeBrackets(): void
1551 {
1552 $parser = new PgSqlSchemaParser();
1553 $def = $parser->parse('CREATE TABLE t (tags TEXT[])');
1554 self::assertNotNull($def);
1555 self::assertSame(['tags'], $def->columns);
1556 }
1557 public function testParseColumnArrayTypeMultipleBrackets(): void
1558 {
1559 $parser = new PgSqlSchemaParser();
1560 $def = $parser->parse('CREATE TABLE t (matrix INTEGER[ ][ ])');
1561 self::assertNotNull($def);
1562 self::assertSame(['matrix'], $def->columns);
1563 }
1564 public function testParseColumnArrayTypeBracketsWithSpaceBeforeNext(): void
1565 {
1566 $parser = new PgSqlSchemaParser();
1567 $def = $parser->parse('CREATE TABLE t (tags TEXT[] NOT NULL)');
1568 self::assertNotNull($def);
1569 self::assertSame(['tags'], $def->columns);
1570 self::assertContains('tags', $def->notNullColumns);
1571 }
1572 public function testParseMultiWordTypeWithSecondWordAsConstraint(): void
1573 {
1574 $parser = new PgSqlSchemaParser();
1575 $def = $parser->parse('CREATE TABLE t (t1 TIMESTAMP NOT NULL)');
1576 self::assertNotNull($def);
1577 self::assertSame(['t1'], $def->columns);
1578 self::assertContains('t1', $def->notNullColumns);
1579 }
1580 public function testParseDoublePrecsionLowercase(): void
1581 {
1582 $parser = new PgSqlSchemaParser();
1583 $def = $parser->parse('CREATE TABLE t (x double precision)');
1584 self::assertNotNull($def);
1585 self::assertSame(['x'], $def->columns);
1586 }
1587 public function testParseCharacterVaryingTypeWithDifferentLength(): void
1588 {
1589 $parser = new PgSqlSchemaParser();
1590 $def = $parser->parse('CREATE TABLE t (name CHARACTER VARYING(100))');
1591 self::assertNotNull($def);
1592 self::assertSame(['name'], $def->columns);
1593 }
1594 public function testParseColumnDefaultWithCommaInSingleQuotedString(): void
1595 {
1596 $parser = new PgSqlSchemaParser();
1597 $def = $parser->parse("CREATE TABLE t (id INTEGER, name TEXT DEFAULT 'a, b')");
1598 self::assertNotNull($def);
1599 self::assertSame(['id', 'name'], $def->columns);
1600 }
1601 public function testParseColumnWithDoubleQuotedNameContainingComma(): void
1602 {
1603 $parser = new PgSqlSchemaParser();
1604 $def = $parser->parse('CREATE TABLE t ("a,b" INTEGER, c TEXT)');
1605 self::assertNotNull($def);
1606 self::assertSame(['a,b', 'c'], $def->columns);
1607 }
1608 public function testParseConstraintEntryLowercaseCheckKeyword(): void
1609 {
1610 $parser = new PgSqlSchemaParser();
1611 $def = $parser->parse('CREATE TABLE t (id INTEGER, check (id > 0))');
1612 self::assertNotNull($def);
1613 self::assertSame(['id'], $def->columns);
1614 }
1615 public function testParseConstraintEntryLowercaseForeignKey(): void
1616 {
1617 $parser = new PgSqlSchemaParser();
1618 $def = $parser->parse('CREATE TABLE t (id INTEGER, foreign key (id) REFERENCES other(id))');
1619 self::assertNotNull($def);
1620 self::assertSame(['id'], $def->columns);
1621 self::assertCount(1, $def->foreignKeys);
1622 self::assertSame('other', array_values($def->foreignKeys)[0]->referencedTable);
1623 self::assertSame(['id'], array_values($def->foreignKeys)[0]->columns);
1624 }
1625 public function testParseColumnWithDefaultContainingParenthesis(): void
1626 {
1627 $parser = new PgSqlSchemaParser();
1628 $def = $parser->parse("CREATE TABLE t (id INTEGER DEFAULT (nextval('seq')))");
1629 self::assertNotNull($def);
1630 self::assertSame(['id'], $def->columns);
1631 self::assertSame([], $def->columnDefaults);
1632 }
1633 public function testParseBitVaryingMultiWordType(): void
1634 {
1635 $parser = new PgSqlSchemaParser();
1636 $def = $parser->parse('CREATE TABLE t (flags BIT VARYING(8))');
1637 self::assertNotNull($def);
1638 self::assertSame(['flags'], $def->columns);
1639 }
1640 public function testParseIntervalYearMultiWordType(): void
1641 {
1642 $parser = new PgSqlSchemaParser();
1643 $def = $parser->parse('CREATE TABLE t (duration INTERVAL YEAR)');
1644 self::assertNotNull($def);
1645 self::assertSame(['duration'], $def->columns);
1646 }
1647 public function testParseLowercaseTableLevelPrimaryKey(): void
1648 {
1649 $parser = new PgSqlSchemaParser();
1650 $def = $parser->parse('CREATE TABLE t (id INTEGER, primary key (id))');
1651 self::assertNotNull($def);
1652 self::assertSame(['id'], $def->primaryKeys);
1653 }
1654 public function testConstraintKeywordPrefixesRemainColumnNames(): void
1655 {
1656 $parser = new PgSqlSchemaParser();
1657 $sql = 'CREATE TABLE bookings (id INT, check_in TEXT, checkout_date TEXT, unique_value TEXT, constraint_name TEXT, foreign_key_id INT, exclude_reason TEXT, "CHECK" TEXT, PRIMARY KEY (id))';
1658 $def = $parser->parse($sql);
1659 self::assertNotNull($def);
1660 self::assertSame(['id', 'check_in', 'checkout_date', 'unique_value', 'constraint_name', 'foreign_key_id', 'exclude_reason', 'CHECK'], $def->columns);
1661 self::assertSame(['id'], $def->primaryKeys);
1662 }
1663 public function testParseRejectsPrimaryKeysReferencingUndeclaredColumns(): void
1664 {
1665 $parser = new PgSqlSchemaParser();
1666 self::assertNull($parser->parse('CREATE TABLE users (id INTEGER, PRIMARY KEY (missing_id))'));
1667 self::assertNull($parser->parse('CREATE TABLE users (id INTEGER, PRIMARY KEY (id, missing_id))'));
1668 }
1669}
1670