packages/ztd-query-mysql/tests/Unit/Rewrite/Transformer/ReplaceTransformerTest.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\MySql\Rewrite\Transformer\InsertTransformer;
12use ZtdQuery\Platform\MySql\Rewrite\Transformer\ReplaceTransformer;
13use ZtdQuery\Platform\MySql\Rewrite\Transformer\SelectTransformer;
14use ZtdQuery\Platform\MySql\Sql\Dml\InsertSelectSourceExtractor;
15use ZtdQuery\Platform\MySql\Sql\MySqlIdentifierQuoter;
16use ZtdQuery\Platform\MySql\Sql\MySqlParser;
17use ZtdQuery\Platform\MySql\Sql\Upsert\MySqlUpsertAssignmentExtractor;
18use ZtdQuery\Platform\MySql\Sql\Value\MySqlCastRenderer;
19use ZtdQuery\Schema\ColumnDeclaration;
20use ZtdQuery\Schema\ColumnTypeFamily;
21
22#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\MySqlPartitionSelectionRewriter::class)]
23#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\MySqlSelectRelationParser::class)]
24#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\HeaderParser::class)]
25#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\IdentifierReferences::class)]
26#[UsesClass(\ZtdQuery\Platform\MySql\Sql\OptionalInsertIntoNormalizer::class)]
27#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\ReferenceReader::class)]
28#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Upsert\AssignmentReader::class)]
29#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\ExpressionEditor::class)]
30#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SelectionReader::class)]
31#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SourceProjection::class)]
32#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\ConflictPredicate::class)]
33#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\ExpressionBinder::class)]
34#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\MetadataColumns::class)]
35#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\QualifiedColumn::class)]
36#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Insert\InsertTarget::class)]
37
38#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Insert\ResultProjection::class)]
39#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Select\ExpressionAliaser::class)]
40#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\OrderRewriter::class)]
41#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\ValueNormalizer::class)]
42#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Shadow\CteRows::class)]
43#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\CastTypeResolver::class)]
44#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\RankEdits::class)]
45#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\ScalarExpression::class)]
46#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\StringCoercion::class)]
47#[CoversClass(ReplaceTransformer::class)]
48#[UsesClass(InsertTransformer::class)]
49#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\InsertRowRenderer::class)]
50#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\InsertSelectRenderer::class)]
51#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\MySqlSelectListAliaser::class)]
52#[UsesClass(InsertSelectSourceExtractor::class)]
53#[UsesClass(MySqlParser::class)]
54#[UsesClass(MySqlUpsertAssignmentExtractor::class)]
55#[UsesClass(SelectTransformer::class)]
56#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\MySqlFullTextSearchRewriter::class)]
57#[UsesClass(MySqlCastRenderer::class)]
58#[UsesClass(MySqlIdentifierQuoter::class)]
59#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\MySqlValueRenderer::class)]
60#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\MySqlTypeSemantics::class)]
61#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Cte\MySqlCteShadowComposer::class)]
62#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\MySqlNativeUpsertProjector::class)]
63#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\GeneratedColumn\MySqlGeneratedColumnProjector::class)]
64#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
65#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Insert\ReplaceStatementConverter::class)]
66final class ReplaceTransformerTest extends TestCase
67{
68 public function testTransformReplaceCastsParametersToTargetColumnTypes(): void
69 {
70 $parser = new MySqlParser();
71 $transformer = new ReplaceTransformer($parser, new SelectTransformer());
72 $tables = [
73 'users' => [
74 'rows' => [],
75 'columns' => ['id', 'name'],
76 'columnTypes' => [
77 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
78 'name' => new ColumnDeclaration(ColumnTypeFamily::STRING, 'VARCHAR(50)'),
79 ],
80 ],
81 ];
82
83 $result = $transformer->transform('REPLACE INTO users VALUES (?, ?)', $tables);
84
85 self::assertSame('SELECT CAST(? AS SIGNED) AS `id`, CAST(? AS CHAR) AS `name`', $result);
86 }
87
88 public function testTransformReplaceWithValues(): void
89 {
90 $parser = new MySqlParser();
91 $selectTransformer = new SelectTransformer();
92 $transformer = new ReplaceTransformer($parser, $selectTransformer);
93
94 $sql = "REPLACE INTO users (id, name) VALUES (1, 'Alice')";
95 $tables = [
96 'users' => [
97 'rows' => [],
98 'columns' => ['id', 'name'],
99 'columnTypes' => [],
100 ],
101 ];
102
103 $result = $transformer->transform($sql, $tables);
104 self::assertStringContainsString('SELECT', $result);
105 }
106
107 public function testTransformReplaceIgnoresLeadingHashComment(): void
108 {
109 $transformer = new ReplaceTransformer(new MySqlParser(), new SelectTransformer());
110 $tables = [
111 'users' => [
112 'rows' => [],
113 'columns' => ['id', 'name'],
114 'columnTypes' => [],
115 ],
116 ];
117
118 $result = $transformer->transform("# REPLACE INTO hidden VALUES (0)\nREPLACE INTO users VALUES (1, 'Alice')", $tables);
119
120 self::assertStringContainsString('1 AS `id`', $result);
121 self::assertStringContainsString("'Alice' AS `name`", $result);
122 }
123
124 public function testTransformThrowsForNonReplaceStatement(): void
125 {
126 $parser = new MySqlParser();
127 $selectTransformer = new SelectTransformer();
128 $transformer = new ReplaceTransformer($parser, $selectTransformer);
129
130 $this->expectException(UnsupportedSqlException::class);
131 $this->expectExceptionMessage('Expected REPLACE statement');
132 $transformer->transform('SELECT 1', []);
133 }
134
135 public function testTransformThrowsForEmptySql(): void
136 {
137 $transformer = new ReplaceTransformer(new MySqlParser(), new SelectTransformer());
138
139 $this->expectException(UnsupportedSqlException::class);
140 $this->expectExceptionMessage('Expected REPLACE statement');
141
142 $transformer->transform('', []);
143 }
144
145 public function testTransformRejectsEmptyReplaceValues(): void
146 {
147 $transformer = new ReplaceTransformer(new MySqlParser(), new SelectTransformer());
148
149 $this->expectException(UnsupportedSqlException::class);
150 $this->expectExceptionMessage('Invalid REPLACE statement');
151
152 $transformer->transform('REPLACE INTO users VALUE ()', [
153 'users' => ['rows' => [], 'columns' => ['id'], 'columnTypes' => []],
154 ]);
155 }
156
157 public function testTransformReplaceWithSetSyntax(): void
158 {
159 $parser = new MySqlParser();
160 $selectTransformer = new SelectTransformer();
161 $transformer = new ReplaceTransformer($parser, $selectTransformer);
162
163 $sql = "REPLACE INTO users SET id = 1, name = 'Alice'";
164 $tables = [
165 'users' => [
166 'rows' => [],
167 'columns' => ['id', 'name'],
168 'columnTypes' => [],
169 ],
170 ];
171
172 $result = $transformer->transform($sql, $tables);
173 self::assertStringContainsString('1 AS `id`', $result);
174 self::assertStringContainsString("'Alice' AS `name`", $result);
175 }
176
177 public function testTransformReplaceMultipleRows(): void
178 {
179 $parser = new MySqlParser();
180 $selectTransformer = new SelectTransformer();
181 $transformer = new ReplaceTransformer($parser, $selectTransformer);
182
183 $sql = "REPLACE INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')";
184 $tables = [];
185
186 $result = $transformer->transform($sql, $tables);
187 self::assertStringContainsString('UNION ALL', $result);
188 self::assertStringContainsString('AS `id`', $result);
189 self::assertStringContainsString('AS `name`', $result);
190 }
191
192 public function testTransformReplaceResolvesColumnsFromContext(): void
193 {
194 $parser = new MySqlParser();
195 $selectTransformer = new SelectTransformer();
196 $transformer = new ReplaceTransformer($parser, $selectTransformer);
197
198 $sql = "REPLACE INTO users VALUES (1, 'Alice')";
199 $tables = [
200 'users' => [
201 'rows' => [],
202 'columns' => ['id', 'name'],
203 'columnTypes' => [],
204 ],
205 ];
206
207 $result = $transformer->transform($sql, $tables);
208 self::assertStringContainsString('AS `id`', $result);
209 self::assertStringContainsString('AS `name`', $result);
210 }
211
212 public function testTransformReplaceThrowsWhenNoColumnsResolvable(): void
213 {
214 $parser = new MySqlParser();
215 $selectTransformer = new SelectTransformer();
216 $transformer = new ReplaceTransformer($parser, $selectTransformer);
217
218 $sql = "REPLACE INTO users VALUES (1, 'Alice')";
219
220 $this->expectException(UnsupportedSqlException::class);
221 $this->expectExceptionMessage('Cannot determine columns');
222 $transformer->transform($sql, []);
223 }
224
225 public function testTransformReplaceProjectsCorrectColumnValues(): void
226 {
227 $parser = new MySqlParser();
228 $selectTransformer = new SelectTransformer();
229 $transformer = new ReplaceTransformer($parser, $selectTransformer);
230
231 $sql = "REPLACE INTO users (id, name) VALUES (1, 'Alice')";
232 $tables = [];
233
234 $result = $transformer->transform($sql, $tables);
235 self::assertStringContainsString('1 AS `id`', $result);
236 self::assertStringContainsString("'Alice' AS `name`", $result);
237 }
238
239 public function testTransformReplaceSelectSubquery(): void
240 {
241 $parser = new MySqlParser();
242 $selectTransformer = new SelectTransformer();
243 $transformer = new ReplaceTransformer($parser, $selectTransformer);
244
245 $sql = 'REPLACE INTO archive (id, name) SELECT id, name FROM users';
246 $tables = [
247 'archive' => [
248 'rows' => [],
249 'columns' => ['id', 'name'],
250 'columnTypes' => [],
251 ],
252 ];
253
254 $result = $transformer->transform($sql, $tables);
255 self::assertStringContainsString('SELECT', $result);
256 }
257
258 public function testTransformReplaceValuesExactSelectFormat(): void
259 {
260 $parser = new MySqlParser();
261 $selectTransformer = new SelectTransformer();
262 $transformer = new ReplaceTransformer($parser, $selectTransformer);
263
264 $sql = 'REPLACE INTO t (a, b) VALUES (1, 2)';
265 $tables = [];
266
267 $result = $transformer->transform($sql, $tables);
268 self::assertStringStartsWith('SELECT 1 AS `a`, 2 AS `b`', $result);
269 }
270
271 public function testTransformReplaceSetExactFormat(): void
272 {
273 $parser = new MySqlParser();
274 $selectTransformer = new SelectTransformer();
275 $transformer = new ReplaceTransformer($parser, $selectTransformer);
276
277 $sql = 'REPLACE INTO t SET a = 1, b = 2';
278 $tables = ['t' => ['columns' => ['a', 'b'], 'columnTypes' => [], 'rows' => []]];
279
280 $result = $transformer->transform($sql, $tables);
281 self::assertStringContainsString('1 AS `a`', $result);
282 self::assertStringContainsString('2 AS `b`', $result);
283 }
284
285 public function testTransformReplaceMultiRowsExactUnionFormat(): void
286 {
287 $parser = new MySqlParser();
288 $selectTransformer = new SelectTransformer();
289 $transformer = new ReplaceTransformer($parser, $selectTransformer);
290
291 $sql = 'REPLACE INTO t (a) VALUES (1), (2)';
292 $tables = [];
293
294 $result = $transformer->transform($sql, $tables);
295 self::assertStringContainsString('SELECT 1 AS `a` UNION ALL SELECT 2 AS `a`', $result);
296 }
297
298 public function testTransformReplaceValuesCountMismatchReturnsInvalidReplace(): void
299 {
300 $parser = new MySqlParser();
301 $selectTransformer = new SelectTransformer();
302 $transformer = new ReplaceTransformer($parser, $selectTransformer);
303
304 $sql = 'REPLACE INTO t (a, b) VALUES (1)';
305 $tables = [];
306
307 $this->expectException(UnsupportedSqlException::class);
308 $this->expectExceptionMessage('Insert values count does not match column count.');
309 $transformer->transform($sql, $tables);
310 }
311
312 public function testTransformReplaceSelectSubqueryPassesThrough(): void
313 {
314 $parser = new MySqlParser();
315 $selectTransformer = new SelectTransformer();
316 $transformer = new ReplaceTransformer($parser, $selectTransformer);
317
318 $sql = 'REPLACE INTO archive (id, name) SELECT id, name FROM users WHERE active = 0';
319 $tables = [
320 'archive' => [
321 'rows' => [],
322 'columns' => ['id', 'name'],
323 'columnTypes' => [],
324 ],
325 ];
326
327 $result = $transformer->transform($sql, $tables);
328 self::assertStringContainsString('SELECT', $result);
329 self::assertStringContainsString('FROM users', $result);
330 self::assertStringContainsString('WHERE active = 0', $result);
331 }
332
333 public function testTransformReplaceSetSelectContainsColumnValues(): void
334 {
335 $parser = new MySqlParser();
336 $selectTransformer = new SelectTransformer();
337 $transformer = new ReplaceTransformer($parser, $selectTransformer);
338
339 $sql = 'REPLACE INTO t SET a = 10, b = 20';
340 $tables = ['t' => ['columns' => ['a', 'b'], 'columnTypes' => [], 'rows' => []]];
341
342 $result = $transformer->transform($sql, $tables);
343 self::assertStringContainsString('10 AS `a`', $result);
344 self::assertStringContainsString('20 AS `b`', $result);
345 }
346
347 public function testTransformReplaceValuesExactSqlAssertion(): void
348 {
349 $parser = new MySqlParser();
350 $selectTransformer = new SelectTransformer();
351 $transformer = new ReplaceTransformer($parser, $selectTransformer);
352
353 $sql = "REPLACE INTO t (id, name) VALUES (1, 'Alice')";
354 $tables = [];
355
356 $result = $transformer->transform($sql, $tables);
357 self::assertSame("SELECT 1 AS `id`, 'Alice' AS `name`", $result);
358 }
359
360 public function testTransformReplaceSetExactSqlAssertion(): void
361 {
362 $parser = new MySqlParser();
363 $selectTransformer = new SelectTransformer();
364 $transformer = new ReplaceTransformer($parser, $selectTransformer);
365
366 $sql = "REPLACE INTO t SET id = 1, name = 'Bob'";
367 $tables = [
368 't' => [
369 'rows' => [],
370 'columns' => ['id', 'name'],
371 'columnTypes' => [],
372 ],
373 ];
374
375 $result = $transformer->transform($sql, $tables);
376 self::assertStringContainsString('1 AS `id`', $result);
377 self::assertStringContainsString("'Bob' AS `name`", $result);
378 }
379
380 public function testTransformReplaceMultiRowExactSqlAssertion(): void
381 {
382 $parser = new MySqlParser();
383 $selectTransformer = new SelectTransformer();
384 $transformer = new ReplaceTransformer($parser, $selectTransformer);
385
386 $sql = 'REPLACE INTO t (id) VALUES (1), (2), (3)';
387 $tables = [];
388
389 $result = $transformer->transform($sql, $tables);
390 self::assertSame('SELECT 1 AS `id` UNION ALL SELECT 2 AS `id` UNION ALL SELECT 3 AS `id`', $result);
391 }
392
393 public function testTransformReplaceColumnsResolvedFromTableContext(): void
394 {
395 $parser = new MySqlParser();
396 $selectTransformer = new SelectTransformer();
397 $transformer = new ReplaceTransformer($parser, $selectTransformer);
398
399 $sql = "REPLACE INTO t VALUES (1, 'x')";
400 $tables = [
401 't' => [
402 'rows' => [],
403 'columns' => ['id', 'name'],
404 'columnTypes' => [],
405 ],
406 ];
407
408 $result = $transformer->transform($sql, $tables);
409 self::assertStringContainsString('1 AS `id`', $result);
410 self::assertStringContainsString("'x' AS `name`", $result);
411 }
412
413 public function testTransformReplaceNoColumnsNoContextThrows(): void
414 {
415 $parser = new MySqlParser();
416 $selectTransformer = new SelectTransformer();
417 $transformer = new ReplaceTransformer($parser, $selectTransformer);
418
419 $sql = "REPLACE INTO t VALUES (1, 'x')";
420
421 $this->expectException(UnsupportedSqlException::class);
422 $this->expectExceptionMessage('Cannot determine columns');
423 $transformer->transform($sql, []);
424 }
425
426 public function testTransformReplaceValuesCountMismatchReturnsInvalid(): void
427 {
428 $parser = new MySqlParser();
429 $selectTransformer = new SelectTransformer();
430 $transformer = new ReplaceTransformer($parser, $selectTransformer);
431
432 $sql = 'REPLACE INTO t (id, name) VALUES (1)';
433
434 $this->expectException(UnsupportedSqlException::class);
435 $this->expectExceptionMessage('Insert values count does not match column count.');
436 $transformer->transform($sql, []);
437 }
438
439 public function testTransformReplaceTrimsWhitespaceInValues(): void
440 {
441 $parser = new MySqlParser();
442 $selectTransformer = new SelectTransformer();
443 $transformer = new ReplaceTransformer($parser, $selectTransformer);
444
445 $sql = "REPLACE INTO t (a) VALUES ( 'hello' )";
446 $tables = [];
447
448 $result = $transformer->transform($sql, $tables);
449 self::assertSame("SELECT 'hello' AS `a`", $result);
450 }
451
452 public function testTransformReplaceSetExactSqlContainsSelectPrefix(): void
453 {
454 $parser = new MySqlParser();
455 $selectTransformer = new SelectTransformer();
456 $transformer = new ReplaceTransformer($parser, $selectTransformer);
457
458 $sql = 'REPLACE INTO t SET x = 42';
459 $tables = ['t' => ['columns' => ['x'], 'columnTypes' => [], 'rows' => []]];
460
461 $result = $transformer->transform($sql, $tables);
462 self::assertStringContainsString('SELECT 42 AS `x`', $result);
463 }
464
465 public function testTransformReplaceValuesTrimsWhitespace(): void
466 {
467 $parser = new MySqlParser();
468 $selectTransformer = new SelectTransformer();
469 $transformer = new ReplaceTransformer($parser, $selectTransformer);
470
471 $sql = "REPLACE INTO t (a, b) VALUES ( 1 , 'hello' )";
472 $tables = [];
473
474 $result = $transformer->transform($sql, $tables);
475 self::assertSame("SELECT 1 AS `a`, 'hello' AS `b`", $result);
476 }
477
478 public function testTransformReplaceNullDestThrows(): void
479 {
480 $parser = new MySqlParser();
481 $selectTransformer = new SelectTransformer();
482 $transformer = new ReplaceTransformer($parser, $selectTransformer);
483
484 $this->expectException(UnsupportedSqlException::class);
485 $this->expectExceptionMessage('Cannot resolve INSERT target');
486 $transformer->transform('REPLACE SELECT 1', []);
487 }
488
489 public function testCommitRewriteState(): void
490 {
491 $transformer = new ReplaceTransformer(new MySqlParser(), new SelectTransformer());
492 $tables = ['users' => ['rows' => [], 'columns' => ['id', 'name'], 'columnTypes' => [], 'identityStrategies' => ['id' => \ZtdQuery\Schema\Key\IdentityGenerationStrategy::MaxValue]]];
493 self::assertSame("SELECT 1 AS `id`, 'a' AS `name`", $transformer->transform("REPLACE INTO users (name) VALUES ('a')", $tables));
494 $transformer->commitRewriteState();
495 self::assertSame("SELECT 2 AS `id`, 'b' AS `name`", $transformer->transform("REPLACE INTO users (name) VALUES ('b')", $tables));
496 }
497}
498