packages/ztd-query-postgres/tests/Unit/Rewrite/Transformer/MergeTransformerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Transformer;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Exception\UnsupportedSqlException;
11use ZtdQuery\Platform\Postgres\Rewrite\Sampling\PgSqlTableSampleRewriter;
12use ZtdQuery\Platform\Postgres\Rewrite\Transformer\MergeTransformer;
13use ZtdQuery\Platform\Postgres\Rewrite\Transformer\SelectTransformer;
14use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeActionKind;
15use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeClause;
16use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeMatchKind;
17use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeParser;
18use ZtdQuery\Platform\Postgres\Sql\Merge\PgSqlMergeStatement;
19use ZtdQuery\Platform\Postgres\Sql\PgSqlIdentifierQuoter;
20use ZtdQuery\Platform\Postgres\Sql\Sampling\PgSqlTableSampleParser;
21use ZtdQuery\Platform\Postgres\Sql\Value\PgSqlCastRenderer;
22use ZtdQuery\Platform\Postgres\Sql\Value\PgSqlValueRenderer;
23use ZtdQuery\Schema\ColumnDeclaration;
24use ZtdQuery\Schema\ColumnTypeFamily;
25use ZtdQuery\Schema\Key\IdentityGenerationStrategy;
26
27#[CoversClass(MergeTransformer::class)]
28#[UsesClass(PgSqlCastRenderer::class)]
29#[UsesClass(PgSqlIdentifierQuoter::class)]
30#[UsesClass(PgSqlMergeActionKind::class)]
31#[UsesClass(PgSqlMergeClause::class)]
32#[UsesClass(PgSqlMergeMatchKind::class)]
33#[UsesClass(PgSqlMergeParser::class)]
34#[UsesClass(PgSqlMergeStatement::class)]
35#[UsesClass(PgSqlTableSampleParser::class)]
36#[UsesClass(PgSqlTableSampleRewriter::class)]
37#[UsesClass(PgSqlValueRenderer::class)]
38#[UsesClass(SelectTransformer::class)]
39#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Cte\PgSqlCteShadowComposer::class)]
40#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\GeneratedColumn\PgSqlGeneratedColumnProjector::class)]
41#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Transformer\InsertSelectRenderer::class)]
42#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Transformer\InsertRowRenderer::class)]
43#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\PgSqlSelectRelationParser::class)]
44#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\PgSqlLexerProfile::class)]
45#[CoversClass(\ZtdQuery\Platform\Postgres\Rewrite\Transformer\Merge\MatchConditions::class)]
46#[CoversClass(\ZtdQuery\Platform\Postgres\Rewrite\Transformer\Merge\RowActions::class)]
47#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Cte\HeaderParser::class)]
48#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Cte\IdentifierReferences::class)]
49#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Cte\PrefixMerge::class)]
50#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Cte\ShadowDependencies::class)]
51#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Merge\ActionClause::class)]
52#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Merge\BranchTokens::class)]
53#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Merge\RelationTarget::class)]
54#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Merge\StatementParts::class)]
55#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\FromClause::class)]
56#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Relation\RelationReference::class)]
57#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Sampling\SampleClause::class)]
58#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Sampling\SampleTokens::class)]
59#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Sampling\PgSqlTableSample::class)]
60#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Sampling\PgSqlTableSampleMethod::class)]
61#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Sampling\SampleProjection::class)]
62#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Sampling\TableColumns::class)]
63#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Value\CastTypeMapper::class)]
64#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Value\NativeCastTarget::class)]
65#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Value\BinaryStream::class)]
66#[UsesClass(\ZtdQuery\Platform\Postgres\Sql\Value\LiteralText::class)]
67#[UsesClass(\ZtdQuery\Platform\Postgres\Rewrite\Transformer\Cte\RowSourceRenderer::class)]
68final class MergeTransformerTest extends TestCase
69{
70 public function testTransformsMixedUpdateAndInsertIntoCompleteTargetState(): void
71 {
72 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
73 $result = $transformer->transform(
74 'MERGE INTO target AS t USING source AS s ON t.id = s.id '
75 . 'WHEN MATCHED THEN UPDATE SET name = s.name '
76 . 'WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, $1)',
77 [
78 'target' => [
79 'rows' => [['id' => 1, 'name' => 'old']],
80 'columns' => ['id', 'name'],
81 'columnTypes' => [
82 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER'),
83 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'TEXT'),
84 ],
85 ],
86 'source' => [
87 'rows' => [['id' => 1, 'name' => 'updated'], ['id' => 2, 'name' => 'inserted']],
88 'columns' => ['id', 'name'],
89 'columnTypes' => [
90 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER'),
91 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'TEXT'),
92 ],
93 ],
94 ],
95 );
96
97 self::assertStringContainsString('"target" AS MATERIALIZED', $result);
98 self::assertStringContainsString('"source" AS MATERIALIZED', $result);
99 self::assertStringContainsString('WHERE NOT (EXISTS', $result);
100 self::assertStringContainsString('JOIN source AS s ON (t.id = s.id)', $result);
101 self::assertStringContainsString('$1 AS "name"', $result);
102 self::assertStringContainsString('WHERE NOT EXISTS', $result);
103 self::assertSame(2, substr_count($result, 'UNION ALL'));
104 self::assertSame(
105 '8deccc308f3216f4695d5d03e93ee1a0deb7cbfe4ca451102125f6b2b691356c',
106 hash('sha256', $result),
107 );
108 }
109
110 public function testPreservesFirstEligibleClauseOrderingAndCtePrefix(): void
111 {
112 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
113 $result = $transformer->transform(
114 'WITH incoming AS (SELECT 1 AS id, TRUE AS skip) '
115 . 'MERGE INTO target t USING incoming s ON t.id = s.id '
116 . 'WHEN MATCHED AND s.skip THEN DO NOTHING '
117 . 'WHEN MATCHED THEN DELETE',
118 [
119 'target' => [
120 'rows' => [['id' => 1]],
121 'columns' => ['id'],
122 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER')],
123 ],
124 ],
125 );
126
127 self::assertStringContainsString('WITH "target" AS MATERIALIZED', $result);
128 self::assertStringContainsString('incoming AS (SELECT 1 AS id, TRUE AS skip)', $result);
129 self::assertStringContainsString('COALESCE((s.skip), FALSE)', $result);
130 self::assertStringContainsString('AND NOT (COALESCE((s.skip), FALSE))', $result);
131 self::assertSame(
132 '7c6934f735d990a45c2488968dfc74fb82557e87ef5bdaab9cba5e07f9de691e',
133 hash('sha256', $result),
134 );
135 }
136
137 public function testProjectsDefaultsAndGeneratedColumns(): void
138 {
139 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
140 $result = $transformer->transform(
141 'MERGE INTO totals t USING (VALUES (1, 3)) AS s(id, amount) ON t.id = s.id '
142 . 'WHEN MATCHED THEN UPDATE SET amount = DEFAULT '
143 . 'WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, DEFAULT)',
144 [
145 'totals' => [
146 'rows' => [],
147 'columns' => ['id', 'amount', 'doubled'],
148 'columnTypes' => [
149 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER'),
150 'amount' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER'),
151 'doubled' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INTEGER'),
152 ],
153 'columnDefaults' => ['amount' => '7'],
154 'generatedExpressions' => ['doubled' => '"amount" * 2'],
155 ],
156 ],
157 );
158
159 self::assertStringContainsString('7 AS "amount"', $result);
160 self::assertStringContainsString('"amount" * 2 AS "doubled"', $result);
161 self::assertSame(
162 'd743861318ec598cf546902c70cb11b2c0be5b6c42e6fcc341afd99ee9d9fa34',
163 hash('sha256', $result),
164 );
165 }
166
167 public function testRejectsUnknownUpdateColumn(): void
168 {
169 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
170
171 $this->expectException(UnsupportedSqlException::class);
172
173 $transformer->transform(
174 'MERGE INTO target t USING source s ON t.id = s.id '
175 . 'WHEN MATCHED THEN UPDATE SET missing = s.value',
176 [
177 'target' => ['rows' => [], 'columns' => ['id'], 'columnTypes' => []],
178 'source' => ['rows' => [], 'columns' => ['id', 'value'], 'columnTypes' => []],
179 ],
180 );
181 }
182
183 public function testRejectsUnknownTargetAndViewTarget(): void
184 {
185 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
186 $sql = 'MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN DELETE';
187
188 try {
189 $transformer->transform($sql, []);
190 self::fail('Unknown target must be rejected');
191 } catch (UnsupportedSqlException $exception) {
192 self::assertStringContainsString('Cannot resolve MERGE target schema', $exception->getMessage());
193 }
194
195 $this->expectException(UnsupportedSqlException::class);
196 $this->expectExceptionMessage('Cannot resolve MERGE target schema');
197 $transformer->transform($sql, ['target' => ['viewSql' => 'SELECT 1']]);
198 }
199
200 public function testRejectsNonListColumnMetadata(): void
201 {
202 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
203
204 $this->expectException(UnsupportedSqlException::class);
205 $this->expectExceptionMessage('MERGE target columns must preserve declaration order');
206
207 $transformer->transform(
208 'MERGE INTO target USING source ON TRUE WHEN MATCHED THEN DELETE',
209 ['target' => ['rows' => [], 'columns' => [2 => 'id'], 'columnTypes' => []]],
210 );
211 }
212
213 public function testProjectsDefaultValuesAndIdentity(): void
214 {
215 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
216 $result = $transformer->transform(
217 'MERGE INTO users target USING source ON FALSE '
218 . 'WHEN NOT MATCHED THEN INSERT DEFAULT VALUES',
219 [
220 'users' => [
221 'rows' => [],
222 'columns' => ['id', 'name'],
223 'columnTypes' => [],
224 'columnDefaults' => ['name' => "'anonymous'"],
225 'identityStrategies' => ['id' => IdentityGenerationStrategy::Sequence],
226 ],
227 ],
228 );
229
230 self::assertStringContainsString('1 + ROW_NUMBER() OVER () - 1 AS "id"', $result);
231 self::assertStringContainsString("'anonymous' AS \"name\"", $result);
232 }
233
234 public function testRejectsUnknownInsertColumn(): void
235 {
236 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
237
238 $this->expectException(UnsupportedSqlException::class);
239 $this->expectExceptionMessage('MERGE INSERT references an unknown target column');
240
241 $transformer->transform(
242 'MERGE INTO users target USING source ON FALSE '
243 . 'WHEN NOT MATCHED THEN INSERT (missing) VALUES (source.value)',
244 ['users' => ['rows' => [], 'columns' => ['id'], 'columnTypes' => []]],
245 );
246 }
247
248 public function testExplicitIdentityValueIsNotGenerated(): void
249 {
250 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
251 $result = $transformer->transform(
252 'MERGE INTO users target USING source ON FALSE '
253 . 'WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name)',
254 [
255 'users' => [
256 'rows' => [],
257 'columns' => ['id', 'name'],
258 'columnTypes' => [],
259 'identityStrategies' => ['id' => IdentityGenerationStrategy::Sequence],
260 ],
261 ],
262 );
263
264 self::assertStringContainsString('source.id AS "id"', $result);
265 self::assertStringNotContainsString('ROW_NUMBER()', $result);
266 }
267
268 public function testDefaultIdentityValueIsGenerated(): void
269 {
270 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
271 $result = $transformer->transform(
272 'MERGE INTO users target USING source ON FALSE '
273 . 'WHEN NOT MATCHED THEN INSERT (id, name) VALUES (DEFAULT, source.name)',
274 [
275 'users' => [
276 'rows' => [],
277 'columns' => ['id', 'name'],
278 'columnTypes' => [],
279 'identityStrategies' => ['id' => IdentityGenerationStrategy::Sequence],
280 ],
281 ],
282 );
283
284 self::assertStringContainsString('1 + ROW_NUMBER() OVER () - 1 AS "id"', $result);
285 self::assertStringContainsString('source.name AS "name"', $result);
286 }
287
288 public function testRejectsChildPartitionTargetBeforeStateCanBeTruncated(): void
289 {
290 $transformer = new MergeTransformer(new PgSqlMergeParser(), new SelectTransformer());
291
292 $this->expectException(UnsupportedSqlException::class);
293
294 $transformer->transform(
295 'MERGE INTO child c USING source s ON c.id = s.id WHEN MATCHED THEN DELETE',
296 [
297 'child' => [
298 'rows' => [['id' => 1]],
299 'columns' => ['id'],
300 'columnTypes' => [],
301 'storageTable' => 'parent',
302 ],
303 ],
304 );
305 }
306
307}
308