packages/ztd-query-postgres/tests/Unit/Schema/Key/PgSqlForeignKeyDefinitionParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Schema\Key;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Platform\Postgres\Schema\Key\PgSqlForeignKeyDefinitionParser;
11use ZtdQuery\Schema\Key\ReferentialAction;
12
13#[CoversClass(PgSqlForeignKeyDefinitionParser::class)]
14#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
15#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Key\DefinitionEntry::class)]
16#[CoversClass(\ZtdQuery\Platform\Postgres\Schema\Key\DefinitionTokens::class)]
17final class PgSqlForeignKeyDefinitionParserTest extends TestCase
18{
19 public function testParseCreateTableParsesNamedCompositeConstraintWithoutRegexSubstitution(): void
20 {
21 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
22 'CREATE TABLE child (id INT, tenant_id INT, parent_id INT, '
23 . 'CONSTRAINT "fk child parent" FOREIGN KEY (tenant_id, parent_id) '
24 . 'REFERENCES app.parents (tenant_id, id) ON UPDATE CASCADE ON DELETE SET NULL)',
25 );
26
27 self::assertSame(['fk child parent'], array_keys($foreignKeys));
28 self::assertSame(['tenant_id', 'parent_id'], $foreignKeys['fk child parent']->columns);
29 self::assertSame('parents', $foreignKeys['fk child parent']->referencedTable);
30 self::assertSame(['tenant_id', 'id'], $foreignKeys['fk child parent']->referencedColumns);
31 self::assertSame(ReferentialAction::SetNull, $foreignKeys['fk child parent']->onDelete);
32 self::assertSame(ReferentialAction::Cascade, $foreignKeys['fk child parent']->onUpdate);
33 }
34
35 public function testParsesInlineAndPostgreSqlQuotedReferences(): void
36 {
37 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
38 'CREATE TABLE "child" ("id" INT, "parent_id" INT REFERENCES "app"."parents" ("id") ON DELETE RESTRICT)',
39 );
40
41 self::assertCount(1, $foreignKeys);
42 $foreignKey = array_values($foreignKeys)[0];
43 self::assertSame(['parent_id'], $foreignKey->columns);
44 self::assertSame('parents', $foreignKey->referencedTable);
45 self::assertSame(['id'], $foreignKey->referencedColumns);
46 self::assertSame(ReferentialAction::Restrict, $foreignKey->onDelete);
47 self::assertSame(ReferentialAction::NoAction, $foreignKey->onUpdate);
48 }
49
50 public function testUsesReferencedPrimaryKeyWhenColumnListIsOmitted(): void
51 {
52 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
53 'CREATE TABLE child (parent_id INTEGER REFERENCES parent ON DELETE CASCADE)',
54 );
55
56 self::assertSame([], array_values($foreignKeys)[0]->referencedColumns);
57 self::assertSame(ReferentialAction::Cascade, array_values($foreignKeys)[0]->onDelete);
58 }
59
60 public function testRejectsStatementsAndMalformedReferencesWithoutTableBody(): void
61 {
62 $parser = new PgSqlForeignKeyDefinitionParser();
63
64 self::assertSame([], $parser->parseCreateTable('CREATE TABLE child AS SELECT 1'));
65 self::assertSame([], $parser->parseCreateTable('CREATE TABLE child (id INT, FOREIGN KEY REFERENCES parent(id))'));
66 }
67
68 public function testParsesEveryActionAndAssignsSequentialSyntheticNames(): void
69 {
70 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
71 'create table child ('
72 . 'id int, '
73 . 'cascade_id int references cascade_parent(id) on delete cascade on update restrict, '
74 . 'default_id int references default_parent(id) on delete set default on update no action, '
75 . 'null_id int references null_parent(id) on delete set null, '
76 . 'foreign key (id, cascade_id) references composite_parent(tenant_id, id) '
77 . 'on delete restrict on update cascade'
78 . ')',
79 );
80
81 self::assertSame(['foreign_0', 'foreign_1', 'foreign_2', 'foreign_3'], array_keys($foreignKeys));
82 self::assertSame(ReferentialAction::Cascade, $foreignKeys['foreign_0']->onDelete);
83 self::assertSame(ReferentialAction::Restrict, $foreignKeys['foreign_0']->onUpdate);
84 self::assertSame(ReferentialAction::SetDefault, $foreignKeys['foreign_1']->onDelete);
85 self::assertSame(ReferentialAction::NoAction, $foreignKeys['foreign_1']->onUpdate);
86 self::assertSame(ReferentialAction::SetNull, $foreignKeys['foreign_2']->onDelete);
87 self::assertSame(['id', 'cascade_id'], $foreignKeys['foreign_3']->columns);
88 self::assertSame(['tenant_id', 'id'], $foreignKeys['foreign_3']->referencedColumns);
89 self::assertSame(ReferentialAction::Restrict, $foreignKeys['foreign_3']->onDelete);
90 self::assertSame(ReferentialAction::Cascade, $foreignKeys['foreign_3']->onUpdate);
91 }
92
93 public function testUsesOnlyCreateTableTopLevelParentheses(): void
94 {
95 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
96 "CREATE TABLE child (id INT, note TEXT DEFAULT '(not structure)', "
97 . 'parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id)) WITH (fillfactor = 80)',
98 );
99
100 self::assertSame(['foreign_0'], array_keys($foreignKeys));
101 self::assertSame(['parent_id'], $foreignKeys['foreign_0']->columns);
102 self::assertSame('parent', $foreignKeys['foreign_0']->referencedTable);
103 self::assertSame(['id'], $foreignKeys['foreign_0']->referencedColumns);
104 }
105
106 public function testRejectsNonCreateAndMalformedForeignKeyStructures(): void
107 {
108 $parser = new PgSqlForeignKeyDefinitionParser();
109
110 self::assertSame([], $parser->parseCreateTable(
111 'WRAP (FOREIGN KEY (bad_id) REFERENCES wrong(id)) CREATE TABLE child '
112 . '(parent_id INT REFERENCES parent(id))',
113 ));
114 self::assertSame([], $parser->parseCreateTable(
115 'CREATE TABLE child (id INT, FOREIGN (id) REFERENCES parent(id))',
116 ));
117 self::assertSame([], $parser->parseCreateTable(
118 'CREATE TABLE child (id INT, FOREIGN KEY (id) REFERENCES)',
119 ));
120 self::assertSame([], $parser->parseCreateTable(
121 'CREATE TABLE child (id INT, FOREIGN KEY (id REFERENCES parent(id))',
122 ));
123 }
124
125 public function testDoesNotTreatOtherCreateStatementParenthesesAsTableBodies(): void
126 {
127 $parser = new PgSqlForeignKeyDefinitionParser();
128
129 self::assertSame([], $parser->parseCreateTable(
130 'CREATE VIEW child AS fn(parent_id INT REFERENCES parent(id))',
131 ));
132 self::assertSame([], $parser->parseCreateTable(
133 'CREATE TYPE child AS (parent_id INT REFERENCES parent(id))',
134 ));
135 self::assertSame([], $parser->parseCreateTable(
136 'CREATE TABLE child (id INT), fake_id INT REFERENCES fake_parent(id)',
137 ));
138 }
139
140 public function testMalformedActionsRemainNoActionWithoutTokenLookaheadFailures(): void
141 {
142 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
143 'CREATE TABLE child ('
144 . 'bare_id INT REFERENCES bare_parent, '
145 . 'on_id INT REFERENCES on_parent(id) ON, '
146 . 'delete_id INT REFERENCES delete_parent(id) ON DELETE, '
147 . 'set_id INT REFERENCES set_parent(id) ON DELETE SET, '
148 . 'unknown_id INT REFERENCES unknown_parent(id) ON DELETE UNKNOWN NULL'
149 . ')',
150 );
151
152 self::assertSame(
153 ['foreign_0', 'foreign_1', 'foreign_2', 'foreign_3', 'foreign_4'],
154 array_keys($foreignKeys),
155 );
156 self::assertSame('bare_parent', $foreignKeys['foreign_0']->referencedTable);
157 self::assertSame([], $foreignKeys['foreign_0']->referencedColumns);
158 self::assertSame(ReferentialAction::NoAction, $foreignKeys['foreign_1']->onDelete);
159 self::assertSame(ReferentialAction::NoAction, $foreignKeys['foreign_2']->onDelete);
160 self::assertSame(ReferentialAction::NoAction, $foreignKeys['foreign_3']->onDelete);
161 self::assertSame(ReferentialAction::NoAction, $foreignKeys['foreign_4']->onDelete);
162 }
163
164 public function testNestedActionTokensCannotOverrideTopLevelAction(): void
165 {
166 $foreignKeys = (new PgSqlForeignKeyDefinitionParser())->parseCreateTable(
167 'CREATE TABLE child ('
168 . 'parent_id INT REFERENCES parent(id) CHECK (ON DELETE CASCADE) ON DELETE RESTRICT'
169 . ')',
170 );
171
172 self::assertSame(ReferentialAction::Restrict, $foreignKeys['foreign_0']->onDelete);
173 }
174
175 public function testForeignKeyClauseRequiresImmediateKeyAndColumnListTokens(): void
176 {
177 $parser = new PgSqlForeignKeyDefinitionParser();
178
179 self::assertSame([], $parser->parseCreateTable(
180 'CREATE TABLE child (id INT, FOREIGN WRONG KEY (id) REFERENCES parent(id))',
181 ));
182 self::assertSame([], $parser->parseCreateTable(
183 'CREATE TABLE child (id INT, FOREIGN WRONG (id) REFERENCES parent(id))',
184 ));
185 self::assertSame([], $parser->parseCreateTable(
186 'CREATE TABLE child (id INT, FOREIGN KEY id REFERENCES parent(id))',
187 ));
188 self::assertSame([], $parser->parseCreateTable(
189 'CREATE TABLE child (id INT, FOREIGN KEY () REFERENCES parent(id))',
190 ));
191 self::assertSame([], $parser->parseCreateTable(
192 'CREATE TABLE child (id INT, CONSTRAINT fk REFERENCES parent(id) FOREIGN KEY (id))',
193 ));
194 self::assertSame([], $parser->parseCreateTable(
195 'CREATE TABLE child (id INT, CONSTRAINT KEY (id) REFERENCES parent(id))',
196 ));
197 self::assertSame([], $parser->parseCreateTable(
198 'CREATE TABLE child (id INT, FOREIGN KEY (id) REFERENCES parent.)',
199 ));
200 }
201}
202