packages/ztd-query-postgres/tests/Unit/Sql/Merge/PgSqlMergeParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql\Merge;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\TestWith;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Exception\UnsupportedSqlException;
12use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeActionKind;
13use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeClause;
14use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeMatchKind;
15use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeParser;
16use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeStatement;
17
18#[CoversClass(PgSqlMergeParser::class)]
19#[UsesClass(PgSqlMergeActionKind::class)]
20#[UsesClass(PgSqlMergeClause::class)]
21#[UsesClass(PgSqlMergeMatchKind::class)]
22#[UsesClass(PgSqlMergeStatement::class)]
23#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Cte\PgSqlCteShadowComposer::class)]
24#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
25#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Merge\ActionClause::class)]
26#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Merge\BranchTokens::class)]
27#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Merge\RelationTarget::class)]
28#[CoversClass(\ZtdQuery\Platform\Postgres\Sql\Merge\StatementParts::class)]
29#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Cte\HeaderParser::class)]
30#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Cte\IdentifierReferences::class)]
31#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Cte\PrefixMerge::class)]
32#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Cte\ShadowDependencies::class)]
33#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\FromClause::class)]
34#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\RelationReference::class)]
35#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\PgSqlSelectRelationParser::class)]
36final class PgSqlMergeParserTest extends TestCase
37{
38 public function testParsesMatchedUpdateAndNotMatchedInsert(): void
39 {
40 $statement = (new PgSqlMergeParser())->parse(
41 'MERGE INTO target AS t USING source AS s ON t.id = s.id '
42 . 'WHEN MATCHED AND s.enabled THEN UPDATE SET name = s.name, value = s.value '
43 . 'WHEN NOT MATCHED THEN INSERT (id, name, value) VALUES (s.id, s.name, $1);',
44 );
45
46 self::assertSame('target', $statement->targetTable);
47 self::assertSame('target', $statement->targetSql);
48 self::assertSame('t', $statement->targetAlias);
49 self::assertSame('source AS s', $statement->sourceSql);
50 self::assertSame('t.id = s.id', $statement->joinConditionSql);
51 self::assertCount(2, $statement->clauses);
52 self::assertSame(PgSqlMergeMatchKind::Matched, $statement->clauses[0]->matchKind);
53 self::assertSame('s.enabled', $statement->clauses[0]->conditionSql);
54 self::assertSame(PgSqlMergeActionKind::Update, $statement->clauses[0]->actionKind);
55 self::assertSame(['name' => 's.name', 'value' => 's.value'], $statement->clauses[0]->assignments);
56 self::assertSame(PgSqlMergeMatchKind::NotMatched, $statement->clauses[1]->matchKind);
57 self::assertSame(PgSqlMergeActionKind::Insert, $statement->clauses[1]->actionKind);
58 self::assertSame(['id', 'name', 'value'], $statement->clauses[1]->insertColumns);
59 self::assertSame(['s.id', 's.name', '$1'], $statement->clauses[1]->insertValues);
60 }
61
62 public function testParsesCteQualifiedTargetJoinSourceAndQuotedAlias(): void
63 {
64 $statement = (new PgSqlMergeParser())->parse(
65 'WITH incoming AS (SELECT 1 AS id) '
66 . 'MERGE INTO ONLY public."Target" * AS "T" '
67 . 'USING incoming JOIN flags ON flags.id = incoming.id AS source '
68 . 'ON "T".id = source.id '
69 . 'WHEN MATCHED AND CASE WHEN source.id > 0 THEN TRUE ELSE FALSE END '
70 . 'THEN DELETE '
71 . 'WHEN MATCHED THEN DO NOTHING',
72 );
73
74 self::assertSame('Target', $statement->targetTable);
75 self::assertSame('public."Target"', $statement->targetSql);
76 self::assertSame('T', $statement->targetAlias);
77 self::assertSame('incoming JOIN flags ON flags.id = incoming.id AS source', $statement->sourceSql);
78 self::assertSame('"T".id = source.id', $statement->joinConditionSql);
79 self::assertSame(PgSqlMergeActionKind::Delete, $statement->clauses[0]->actionKind);
80 self::assertSame('CASE WHEN source.id > 0 THEN TRUE ELSE FALSE END', $statement->clauses[0]->conditionSql);
81 self::assertSame(PgSqlMergeActionKind::DoNothing, $statement->clauses[1]->actionKind);
82 }
83
84 public function testParsesValuesSourceAndDefaultValues(): void
85 {
86 $statement = (new PgSqlMergeParser())->parse(
87 'MERGE users USING (VALUES ($1, $2)) AS s(id, name) ON users.id = s.id '
88 . 'WHEN NOT MATCHED AND s.name IS NOT NULL THEN INSERT DEFAULT VALUES',
89 );
90
91 self::assertSame('users', $statement->targetAlias);
92 self::assertSame('(VALUES ($1, $2)) AS s(id, name)', $statement->sourceSql);
93 self::assertSame('s.name IS NOT NULL', $statement->clauses[0]->conditionSql);
94 self::assertSame([], $statement->clauses[0]->insertColumns);
95 self::assertSame([], $statement->clauses[0]->insertValues);
96 }
97
98 public function testParsesDefaultUpdateAndInsertWithoutColumnList(): void
99 {
100 $statement = (new PgSqlMergeParser())->parse(
101 'MERGE INTO users u USING source s ON u.id = s.id '
102 . 'WHEN MATCHED THEN UPDATE SET name = DEFAULT '
103 . 'WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name)',
104 );
105
106 self::assertSame(['name' => 'DEFAULT'], $statement->clauses[0]->assignments);
107 self::assertSame([], $statement->clauses[1]->insertColumns);
108 self::assertSame(['s.id', 's.name'], $statement->clauses[1]->insertValues);
109 }
110
111 public function testFoldsUnquotedTargetAliasAndAssignmentIdentifiers(): void
112 {
113 $statement = (new PgSqlMergeParser())->parse(
114 'MERGE INTO Users AS Target USING source ON Target.ID = source.id '
115 . 'WHEN MATCHED THEN UPDATE SET Name = source.name',
116 );
117
118 self::assertSame('users', $statement->targetTable);
119 self::assertSame('target', $statement->targetAlias);
120 self::assertSame(['name' => 'source.name'], $statement->clauses[0]->assignments);
121 }
122
123 public function testPreservesEscapedQuotedIdentifiersAndNestedLists(): void
124 {
125 $statement = (new PgSqlMergeParser())->parse(
126 'MERGE INTO audit."A""B" AS target USING source ON target.id = source.id '
127 . 'WHEN NOT MATCHED THEN INSERT (id, payload) VALUES ((source.id), jsonb_build_array(1, 2))',
128 );
129
130 self::assertSame('A"B', $statement->targetTable);
131 self::assertSame('audit."A""B"', $statement->targetSql);
132 self::assertSame(['id', 'payload'], $statement->clauses[0]->insertColumns);
133 self::assertSame(['(source.id)', 'jsonb_build_array(1, 2)'], $statement->clauses[0]->insertValues);
134 }
135
136 public function testIgnoresOnAndWhenKeywordsOutsideMergeClauseBoundaries(): void
137 {
138 $statement = (new PgSqlMergeParser())->parse(
139 'MERGE INTO using.on.audit.users target '
140 . "USING (SELECT CASE WHEN matched THEN 'ON' ELSE 'off' END AS value) source "
141 . 'ON target.id = source.id '
142 . 'WHEN MATCHED AND on THEN DO NOTHING '
143 . 'WHEN NOT MATCHED THEN INSERT (id) VALUES (source.id)',
144 );
145
146 self::assertSame('using.on.audit.users', $statement->targetSql);
147 self::assertSame("(SELECT CASE WHEN matched THEN 'ON' ELSE 'off' END AS value) source", $statement->sourceSql);
148 self::assertSame('target.id = source.id', $statement->joinConditionSql);
149 self::assertSame('on', $statement->clauses[0]->conditionSql);
150 self::assertCount(2, $statement->clauses);
151 }
152
153 #[TestWith(['', 'Malformed MERGE statement'])]
154 #[TestWith(['SELECT 1', 'Malformed MERGE statement'])]
155 #[TestWith(['MERGE INTO public."" USING source ON TRUE WHEN MATCHED THEN DELETE', 'Cannot resolve MERGE target'])]
156 #[TestWith(['MERGE INTO users USING ON TRUE WHEN MATCHED THEN DELETE', 'MERGE requires a source and join condition'])]
157 #[TestWith(['MERGE INTO users USING source ON WHEN MATCHED THEN DELETE', 'MERGE requires a source and join condition'])]
158 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED BY SOURCE THEN DELETE', 'MERGE BY SOURCE and BY TARGET are not supported'])]
159 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED THEN DO UPDATE', 'MERGE action is not supported'])]
160 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED THEN DO NOTHING EXTRA', 'MERGE action is not supported'])]
161 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN UPDATE SET name = source.name', 'MERGE action is not supported'])]
162 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED THEN UPDATE', 'MERGE UPDATE requires SET'])]
163 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED THEN UPDATE USING name = source.name', 'MERGE UPDATE requires SET'])]
164 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED THEN UPDATE SET target.name = source.name', 'MERGE UPDATE requires simple column assignments'])]
165 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN MATCHED THEN UPDATE SET name =', 'MERGE UPDATE requires simple column assignments'])]
166 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT', 'MERGE INSERT requires VALUES'])]
167 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT FOO VALUES', 'MERGE INSERT requires VALUES'])]
168 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT FOO (source.id)', 'MERGE INSERT requires VALUES'])]
169 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT DEFAULT FOO', 'MERGE INSERT requires VALUES'])]
170 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT DEFAULT VALUES EXTRA', 'MERGE INSERT requires VALUES'])]
171 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT (id) DEFAULT VALUES', 'MERGE INSERT DEFAULT VALUES cannot name columns'])]
172 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT VALUES', 'MERGE INSERT requires VALUES'])]
173 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT MATCHED THEN INSERT VALUES id', 'MERGE INSERT requires VALUES'])]
174 #[TestWith(['MERGE INTO on.users USING source WHEN MATCHED THEN DELETE', 'MERGE requires an ON condition'])]
175 #[TestWith(['MERGE INTO users USING source ON TRUE WHEN NOT FOO THEN DELETE', 'MERGE requires a WHEN clause'])]
176 public function testRejectsMalformedMergeWithSpecificReason(string $sql, string $reason): void
177 {
178 $this->expectException(UnsupportedSqlException::class);
179 $this->expectExceptionMessage($reason);
180
181 (new PgSqlMergeParser())->parse($sql);
182 }
183
184 public function testEndIdentifierDoesNotCloseANonexistentCaseExpression(): void
185 {
186 $statement = (new PgSqlMergeParser())->parse(
187 'MERGE INTO users USING end ON users.id = end.id '
188 . 'WHEN MATCHED AND end THEN DELETE',
189 );
190
191 self::assertSame('end', $statement->sourceSql);
192 self::assertSame('end', $statement->clauses[0]->conditionSql);
193 self::assertSame(PgSqlMergeActionKind::Delete, $statement->clauses[0]->actionKind);
194 }
195
196 public function testSimpleCaseWhenMatchedExpressionIsNotAMergeClause(): void
197 {
198 $statement = (new PgSqlMergeParser())->parse(
199 'MERGE INTO users USING source ON users.id = source.id '
200 . 'WHEN MATCHED AND CASE source.kind WHEN matched THEN TRUE ELSE FALSE END THEN DELETE',
201 );
202
203 self::assertCount(1, $statement->clauses);
204 self::assertSame(
205 'CASE source.kind WHEN matched THEN TRUE ELSE FALSE END',
206 $statement->clauses[0]->conditionSql,
207 );
208 self::assertSame(PgSqlMergeActionKind::Delete, $statement->clauses[0]->actionKind);
209 }
210
211 #[TestWith(['SELECT 1'])]
212 #[TestWith(['MERGE INTO users USING source WHEN MATCHED THEN DELETE'])]
213 #[TestWith(['MERGE INTO users USING source ON users.id = source.id'])]
214 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN MATCHED BY SOURCE THEN DELETE'])]
215 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN UPDATE (name, value) = (source.name, source.value)'])]
216 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN NOT MATCHED THEN INSERT (id) VALUES (source.id, source.name)'])]
217 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN INSERT (id) VALUES (source.id)'])]
218 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN NOT MATCHED THEN DELETE'])]
219 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN DELETE RETURNING *'])]
220 #[TestWith(['MERGE INTO "" USING source ON TRUE WHEN MATCHED THEN DELETE'])]
221 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN MATCHED THEN UPDATE SET name = source.name, Name = source.other_name'])]
222 #[TestWith(['MERGE INTO users USING source ON users.id = source.id WHEN NOT MATCHED THEN INSERT (id, ID) VALUES (1, 2)'])]
223 public function testRejectsMalformedOrUnsupportedMergeShapes(string $sql): void
224 {
225 $this->expectException(UnsupportedSqlException::class);
226
227 (new PgSqlMergeParser())->parse($sql);
228 }
229}
230