packages/ztd-query-mysql/tests/Unit/Rewrite/Transformer/DeleteTransformerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Transformer;
6
7use PhpMyAdmin\SqlParser\Parser;
8use PHPUnit\Framework\Attributes\CoversClass;
9use PHPUnit\Framework\Attributes\UsesClass;
10use PHPUnit\Framework\TestCase;
11use RuntimeException;
12use ZtdQuery\Platform\MySql\Rewrite\Transformer\DeleteTransformer;
13use ZtdQuery\Platform\MySql\Rewrite\Transformer\SelectTransformer;
14use ZtdQuery\Platform\MySql\Sql\Dml\DmlWhereClauseExtractor;
15use ZtdQuery\Platform\MySql\Sql\MySqlIdentifierQuoter;
16use ZtdQuery\Platform\MySql\Sql\MySqlParser;
17use ZtdQuery\Platform\MySql\Sql\Value\MySqlCastRenderer;
18
19#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\MySqlPartitionSelectionRewriter::class)]
20#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\HeaderParser::class)]
21#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\IdentifierReferences::class)]
22#[UsesClass(\ZtdQuery\Platform\MySql\Sql\OptionalInsertIntoNormalizer::class)]
23#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\ExpressionNames::class)]
24#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\ReferenceReader::class)]
25#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\ExpressionEditor::class)]
26#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SelectionReader::class)]
27#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SourceProjection::class)]
28
29
30#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\OrderRewriter::class)]
31#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\ValueNormalizer::class)]
32#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Shadow\CteRows::class)]
33#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\CastTypeResolver::class)]
34#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\RankEdits::class)]
35#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\ScalarExpression::class)]
36#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\StringCoercion::class)]
37#[CoversClass(DeleteTransformer::class)]
38#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\MySqlSelectRelationParser::class)]
39#[UsesClass(MySqlParser::class)]
40#[UsesClass(SelectTransformer::class)]
41#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\MySqlFullTextSearchRewriter::class)]
42#[UsesClass(MySqlCastRenderer::class)]
43#[UsesClass(MySqlIdentifierQuoter::class)]
44#[UsesClass(DmlWhereClauseExtractor::class)]
45#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\MySqlValueRenderer::class)]
46#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\MySqlTypeSemantics::class)]
47#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Cte\MySqlCteShadowComposer::class)]
48#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\GeneratedColumn\MySqlGeneratedColumnProjector::class)]
49#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
50#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Delete\ResultSelect::class)]
51#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Delete\TargetProjection::class)]
52final class DeleteTransformerTest extends TestCase
53{
54 public function testTransformPreservesCaseWhereExpression(): void
55 {
56 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
57 $tables = [
58 't' => [
59 'rows' => [['id' => 1, 'score' => 85], ['id' => 2, 'score' => 60]],
60 'columns' => ['id', 'score'],
61 'columnTypes' => [],
62 ],
63 ];
64
65 $result = $transformer->transform(
66 'DELETE FROM t WHERE CASE WHEN score > 80 THEN 1 ELSE 0 END = 1',
67 $tables,
68 );
69
70 self::assertStringContainsString('WHERE CASE WHEN score > 80 THEN 1 ELSE 0 END = 1', $result);
71 }
72
73 public function testBuildDeleteWithJoinAlias(): void
74 {
75 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
76 $sql = "DELETE u FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'canceled'";
77 $parser = new Parser($sql);
78 $statement = $parser->statements[0];
79 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
80
81 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
82
83 self::assertSame('users', $result['table']);
84 self::assertStringContainsString('SELECT `u`.`id` AS `id`, `u`.`name` AS `name`', $result['sql']);
85 self::assertStringContainsString('FROM users', $result['sql']);
86 self::assertStringContainsString('AS `u`', $result['sql']);
87 self::assertStringContainsString('JOIN orders', $result['sql']);
88 self::assertStringContainsString('AS `o`', $result['sql']);
89 self::assertStringContainsString("WHERE o.status = 'canceled'", $result['sql']);
90 }
91
92 public function testBuildDeleteWithPartitionIsBlocked(): void
93 {
94 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
95 $sql = 'DELETE FROM users PARTITION (p0) WHERE id = 1';
96 $parser = new Parser($sql);
97 $statement = $parser->statements[0];
98 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
99
100 self::expectException(RuntimeException::class);
101 self::expectExceptionMessage('PARTITION clause');
102
103 $transformer->buildProjection($statement, $sql, ['id']);
104 }
105
106 public function testBuildDeleteWithMultipleTargetsReturnsAllTables(): void
107 {
108 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
109 $sql = "DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'canceled'";
110 $parser = new Parser($sql);
111 $statement = $parser->statements[0];
112 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
113
114 $result = $transformer->buildProjection($statement, $sql, ['id']);
115
116 self::assertSame('users', $result['table']);
117 self::assertArrayHasKey('tables', $result);
118 self::assertCount(2, $result['tables']);
119 self::assertArrayHasKey('users', $result['tables']);
120 self::assertArrayHasKey('orders', $result['tables']);
121 self::assertSame('u', $result['tables']['users']['alias']);
122 self::assertSame('o', $result['tables']['orders']['alias']);
123 }
124
125 public function testBuildDeleteWithSingleTargetReturnsSingleTable(): void
126 {
127 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
128 $sql = 'DELETE FROM users WHERE id = 1';
129 $parser = new Parser($sql);
130 $statement = $parser->statements[0];
131 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
132
133 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
134
135 self::assertSame('users', $result['table']);
136 self::assertArrayHasKey('tables', $result);
137 self::assertCount(1, $result['tables']);
138 self::assertArrayHasKey('users', $result['tables']);
139 }
140
141 public function testBuildDeleteWithUsingSyntaxMultiTable(): void
142 {
143 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
144 $sql = "DELETE u FROM users u USING users u, orders o WHERE u.id = o.user_id AND o.status = 'canceled'";
145 $parser = new Parser($sql);
146 $statement = $parser->statements[0];
147 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
148
149 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
150
151 self::assertSame('users', $result['table']);
152 self::assertArrayHasKey('tables', $result);
153 }
154
155 public function testTransformThrowsForNonDeleteStatement(): void
156 {
157 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
158
159 self::expectException(\ZtdQuery\Exception\UnsupportedSqlException::class);
160 $transformer->transform('SELECT 1', []);
161 }
162
163 public function testTransformSimpleDeleteReturnsSelect(): void
164 {
165 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
166 $sql = 'DELETE FROM users WHERE id = 1';
167 $tables = [
168 'users' => [
169 'rows' => [],
170 'columns' => ['id', 'name'],
171 'columnTypes' => [],
172 ],
173 ];
174
175 $result = $transformer->transform($sql, $tables);
176 self::assertStringContainsString('SELECT', $result);
177 self::assertStringContainsString('AS `id`', $result);
178 self::assertStringContainsString('AS `name`', $result);
179 }
180
181 public function testBuildDeleteWithWhereClause(): void
182 {
183 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
184 $sql = 'DELETE FROM users WHERE id = 1';
185 $parser = new Parser($sql);
186 $statement = $parser->statements[0];
187 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
188
189 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
190
191 self::assertStringContainsString('WHERE id = 1', $result['sql']);
192 self::assertStringContainsString('AS `id`', $result['sql']);
193 self::assertStringContainsString('AS `name`', $result['sql']);
194 }
195
196 public function testBuildDeleteWithOrderByAndLimit(): void
197 {
198 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
199 $sql = 'DELETE FROM users ORDER BY id LIMIT 10';
200 $parser = new Parser($sql);
201 $statement = $parser->statements[0];
202 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
203
204 $result = $transformer->buildProjection($statement, $sql, ['id']);
205
206 self::assertStringContainsString('ORDER BY', $result['sql']);
207 self::assertStringContainsString('LIMIT', $result['sql']);
208 }
209
210 public function testBuildDeleteWithNoColumnsUsesWildcard(): void
211 {
212 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
213 $sql = 'DELETE FROM users WHERE id = 1';
214 $parser = new Parser($sql);
215 $statement = $parser->statements[0];
216 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
217
218 $result = $transformer->buildProjection($statement, $sql, []);
219
220 self::assertStringContainsString('`users`.*', $result['sql']);
221 }
222
223 public function testTransformDeleteWithShadowDataAddsCte(): void
224 {
225 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
226 $sql = 'DELETE FROM users WHERE id = 1';
227 $tables = [
228 'users' => [
229 'rows' => [['id' => 1, 'name' => 'Alice']],
230 'columns' => ['id', 'name'],
231 'columnTypes' => [],
232 ],
233 ];
234
235 $result = $transformer->transform($sql, $tables);
236 self::assertStringContainsString('WITH', $result);
237 self::assertStringContainsString('`users`', $result);
238 }
239
240 public function testBuildDeleteProjectionFromClause(): void
241 {
242 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
243 $sql = 'DELETE FROM users';
244 $parser = new Parser($sql);
245 $statement = $parser->statements[0];
246 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
247
248 $result = $transformer->buildProjection($statement, $sql, ['id']);
249 self::assertStringContainsString('FROM', $result['sql']);
250 self::assertStringContainsString('SELECT', $result['sql']);
251 self::assertSame('users', $result['table']);
252 }
253
254 public function testBuildDeleteProjectionIncludesJoinClause(): void
255 {
256 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
257 $sql = 'DELETE u FROM users u JOIN orders o ON u.id = o.user_id';
258 $parser = new Parser($sql);
259 $statement = $parser->statements[0];
260 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
261
262 $result = $transformer->buildProjection($statement, $sql, []);
263 self::assertStringContainsString('JOIN', $result['sql']);
264 self::assertStringContainsString('FROM', $result['sql']);
265 }
266
267 public function testBuildDeleteProjectionSelectListFormat(): void
268 {
269 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
270 $sql = 'DELETE FROM users WHERE id = 1';
271 $parser = new Parser($sql);
272 $statement = $parser->statements[0];
273 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
274
275 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
276 self::assertStringStartsWith('SELECT `users`.`id` AS `id`, `users`.`name` AS `name`', $result['sql']);
277 }
278
279 public function testBuildDeleteTargetFromJoinAlias(): void
280 {
281 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
282 $sql = 'DELETE o FROM users u JOIN orders o ON u.id = o.user_id WHERE u.active = 0';
283 $parser = new Parser($sql);
284 $statement = $parser->statements[0];
285 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
286
287 $result = $transformer->buildProjection($statement, $sql, []);
288 self::assertSame('orders', $result['table']);
289 }
290
291 public function testBuildDeleteWithFromAlias(): void
292 {
293 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
294 $sql = 'DELETE FROM users u WHERE u.id = 1';
295 $parser = new Parser($sql);
296 $statement = $parser->statements[0];
297 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
298
299 $result = $transformer->buildProjection($statement, $sql, ['id']);
300 self::assertSame('users', $result['table']);
301 self::assertStringContainsString('`u`.`id` AS `id`', $result['sql']);
302 }
303
304 public function testBuildMultiDeleteResolvesAliasFromUsing(): void
305 {
306 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
307 $sql = 'DELETE u FROM users u USING users u, orders o WHERE u.id = o.user_id';
308 $parser = new Parser($sql);
309 $statement = $parser->statements[0];
310 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
311
312 $result = $transformer->buildProjection($statement, $sql, []);
313 self::assertSame('users', $result['table']);
314 }
315
316 public function testBuildDeleteMultiTargetResolvesFromJoin(): void
317 {
318 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
319 $sql = 'DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id WHERE u.active = 0';
320 $parser = new Parser($sql);
321 $statement = $parser->statements[0];
322 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
323
324 $result = $transformer->buildProjection($statement, $sql, []);
325 self::assertSame('users', $result['table']);
326 self::assertArrayHasKey('users', $result['tables']);
327 self::assertArrayHasKey('orders', $result['tables']);
328 }
329
330 public function testTransformDeleteWithShadowDataProducesCteSelect(): void
331 {
332 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
333 $sql = 'DELETE FROM users WHERE id = 1';
334 $tables = [
335 'users' => [
336 'rows' => [['id' => 1, 'name' => 'Alice']],
337 'columns' => ['id', 'name'],
338 'columnTypes' => [],
339 ],
340 ];
341
342 $result = $transformer->transform($sql, $tables);
343 self::assertStringContainsString('WITH', $result);
344 self::assertStringContainsString('`users` AS', $result);
345 self::assertStringContainsString('AS `id`', $result);
346 self::assertStringContainsString('AS `name`', $result);
347 self::assertStringContainsString('WHERE id = 1', $result);
348 }
349
350 public function testBuildDeleteWithJoinAndOrderByLimitClause(): void
351 {
352 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
353 $sql = 'DELETE FROM users WHERE id > 5 ORDER BY id LIMIT 10';
354 $parser = new Parser($sql);
355 $statement = $parser->statements[0];
356 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
357
358 $result = $transformer->buildProjection($statement, $sql, ['id']);
359 self::assertStringContainsString('ORDER BY', $result['sql']);
360 self::assertStringContainsString('id', $result['sql']);
361 self::assertStringContainsString('LIMIT', $result['sql']);
362 self::assertStringContainsString('WHERE id > 5', $result['sql']);
363 }
364
365 public function testBuildDeleteWithLowercasePartitionThrows(): void
366 {
367 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
368 $sql = 'DELETE FROM users partition (p0) WHERE id = 1';
369 $parser = new Parser($sql);
370 $statement = $parser->statements[0];
371 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
372
373 self::expectException(RuntimeException::class);
374 self::expectExceptionMessage('PARTITION');
375 $transformer->buildProjection($statement, $sql, ['id']);
376 }
377
378 public function testBuildDeleteOrderByExactFormat(): void
379 {
380 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
381 $sql = 'DELETE FROM users ORDER BY id ASC LIMIT 5';
382 $parser = new Parser($sql);
383 $statement = $parser->statements[0];
384 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
385
386 $result = $transformer->buildProjection($statement, $sql, ['id']);
387 self::assertStringContainsString(' ORDER BY id ASC', $result['sql']);
388 self::assertStringContainsString(' LIMIT ', $result['sql']);
389 }
390
391 public function testBuildDeleteWithMultipleColumnsSelectFormat(): void
392 {
393 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
394 $sql = 'DELETE FROM users WHERE id > 0';
395 $parser = new Parser($sql);
396 $statement = $parser->statements[0];
397 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
398
399 $result = $transformer->buildProjection($statement, $sql, ['id', 'name', 'email']);
400 self::assertStringContainsString('`users`.`id` AS `id`', $result['sql']);
401 self::assertStringContainsString('`users`.`name` AS `name`', $result['sql']);
402 self::assertStringContainsString('`users`.`email` AS `email`', $result['sql']);
403 }
404
405 public function testBuildDeleteTargetFromJoinWhenAliasNotInFrom(): void
406 {
407 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
408 $sql = 'DELETE o FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount < 10';
409 $parser = new Parser($sql);
410 $statement = $parser->statements[0];
411 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
412
413 $result = $transformer->buildProjection($statement, $sql, ['id', 'amount']);
414 self::assertSame('orders', $result['table']);
415 self::assertStringContainsString('`o`.`id` AS `id`', $result['sql']);
416 self::assertStringContainsString('`o`.`amount` AS `amount`', $result['sql']);
417 }
418
419 public function testBuildDeleteWithNoWhereClause(): void
420 {
421 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
422 $sql = 'DELETE FROM users';
423 $parser = new Parser($sql);
424 $statement = $parser->statements[0];
425 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
426
427 $result = $transformer->buildProjection($statement, $sql, ['id']);
428 self::assertStringNotContainsString('WHERE', $result['sql']);
429 }
430
431 public function testBuildDeleteWithNoOrderByClause(): void
432 {
433 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
434 $sql = 'DELETE FROM users WHERE id = 1';
435 $parser = new Parser($sql);
436 $statement = $parser->statements[0];
437 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
438
439 $result = $transformer->buildProjection($statement, $sql, ['id']);
440 self::assertStringNotContainsString('ORDER BY', $result['sql']);
441 self::assertStringNotContainsString('LIMIT', $result['sql']);
442 }
443
444 public function testBuildDeleteResolvedTablesContainsAlias(): void
445 {
446 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
447 $sql = "DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'canceled'";
448 $parser = new Parser($sql);
449 $statement = $parser->statements[0];
450 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
451
452 $result = $transformer->buildProjection($statement, $sql, ['id']);
453 self::assertSame('u', $result['tables']['users']['alias']);
454 self::assertSame('o', $result['tables']['orders']['alias']);
455 }
456
457 public function testBuildDeleteSingleTargetAliasMatchesTable(): void
458 {
459 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
460 $sql = 'DELETE FROM users WHERE id = 1';
461 $parser = new Parser($sql);
462 $statement = $parser->statements[0];
463 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
464
465 $result = $transformer->buildProjection($statement, $sql, ['id']);
466 self::assertSame('users', $result['tables']['users']['alias']);
467 }
468
469 public function testTransformDeleteColumnsFromTablesArg(): void
470 {
471 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
472 $sql = 'DELETE FROM users WHERE id = 1';
473 $tables = [
474 'users' => [
475 'rows' => [],
476 'columns' => ['id', 'email'],
477 'columnTypes' => [],
478 ],
479 ];
480
481 $result = $transformer->transform($sql, $tables);
482 self::assertStringContainsString('AS `id`', $result);
483 self::assertStringContainsString('AS `email`', $result);
484 }
485
486 public function testTransformDeleteWithNoColumnsUsesWildcard(): void
487 {
488 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
489 $sql = 'DELETE FROM unknown_table WHERE id = 1';
490 $tables = [];
491
492 $result = $transformer->transform($sql, $tables);
493 self::assertStringContainsString('.*', $result);
494 }
495
496 public function testBuildDeleteWithUsingClauseOverridesFrom(): void
497 {
498 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
499 $sql = 'DELETE u FROM users u USING users u, orders o WHERE u.id = o.user_id AND o.amount < 5';
500 $parser = new Parser($sql);
501 $statement = $parser->statements[0];
502 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
503
504 $result = $transformer->buildProjection($statement, $sql, ['id']);
505 self::assertStringContainsString('FROM', $result['sql']);
506 self::assertStringContainsString('WHERE', $result['sql']);
507 }
508
509 public function testBuildDeleteLimitClauseContainsActualValue(): void
510 {
511 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
512 $sql = 'DELETE FROM users LIMIT 10';
513 $parser = new Parser($sql);
514 $statement = $parser->statements[0];
515 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
516
517 $result = $transformer->buildProjection($statement, $sql, ['id']);
518 self::assertMatchesRegularExpression('/LIMIT\s+\d/', $result['sql']);
519 }
520
521 public function testBuildDeleteFromClauseContainsTableName(): void
522 {
523 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
524 $sql = 'DELETE FROM users WHERE active = 0';
525 $parser = new Parser($sql);
526 $statement = $parser->statements[0];
527 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
528
529 $result = $transformer->buildProjection($statement, $sql, ['id']);
530 self::assertStringContainsString('FROM users', $result['sql']);
531 }
532
533 public function testBuildDeleteJoinClauseContainsJoinTableName(): void
534 {
535 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
536 $sql = 'DELETE u FROM users u JOIN orders o ON u.id = o.user_id';
537 $parser = new Parser($sql);
538 $statement = $parser->statements[0];
539 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
540
541 $result = $transformer->buildProjection($statement, $sql, []);
542 self::assertStringContainsString('JOIN', $result['sql']);
543 self::assertStringContainsString('orders', $result['sql']);
544 }
545
546 public function testBuildDeleteResolveAliasFromUsingNotFromOrJoin(): void
547 {
548 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
549 $sql = 'DELETE u FROM users u USING users u, orders o WHERE u.id = o.user_id';
550 $parser = new Parser($sql);
551 $statement = $parser->statements[0];
552 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
553
554 $result = $transformer->buildProjection($statement, $sql, ['id']);
555 self::assertSame('users', $result['table']);
556 self::assertArrayHasKey('users', $result['tables']);
557 }
558
559 public function testBuildDeleteMultiTargetResolvesSecondAliasFromJoin(): void
560 {
561 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
562 $sql = "DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'old'";
563 $parser = new Parser($sql);
564 $statement = $parser->statements[0];
565 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
566
567 $result = $transformer->buildProjection($statement, $sql, []);
568 self::assertSame('u', $result['tables']['users']['alias']);
569 self::assertSame('o', $result['tables']['orders']['alias']);
570 self::assertSame('users', $result['table']);
571 }
572
573 public function testBuildDeleteOrderByExactValueIncluded(): void
574 {
575 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
576 $sql = 'DELETE FROM users ORDER BY id DESC LIMIT 3';
577 $parser = new Parser($sql);
578 $statement = $parser->statements[0];
579 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
580
581 $result = $transformer->buildProjection($statement, $sql, ['id']);
582 self::assertStringContainsString('ORDER BY id DESC', $result['sql']);
583 self::assertMatchesRegularExpression('/LIMIT\s+\d/', $result['sql']);
584 }
585
586 public function testBuildProjectionExactSqlForSimpleDelete(): void
587 {
588 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
589 $sql = 'DELETE FROM users WHERE id = 1';
590 $parser = new Parser($sql);
591 $statement = $parser->statements[0];
592 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
593
594 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
595 self::assertSame('SELECT `users`.`id` AS `id`, `users`.`name` AS `name` FROM users WHERE id = 1', $result['sql']);
596 self::assertSame('users', $result['table']);
597 self::assertSame(['users' => ['alias' => 'users']], $result['tables']);
598 }
599
600 public function testBuildProjectionExactSqlForDeleteWithJoinAlias(): void
601 {
602 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
603 $sql = "DELETE u FROM users u JOIN orders o ON u.id = o.user_id WHERE o.status = 'canceled'";
604 $parser = new Parser($sql);
605 $statement = $parser->statements[0];
606 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
607
608 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
609 self::assertSame("SELECT `u`.`id` AS `id`, `u`.`name` AS `name` FROM users AS `u` JOIN orders AS `o` ON u.id = o.user_id WHERE o.status = 'canceled'", $result['sql']);
610 self::assertSame('users', $result['table']);
611 self::assertSame(['users' => ['alias' => 'u']], $result['tables']);
612 }
613
614 public function testBuildProjectionExactSqlForMultiTargetDelete(): void
615 {
616 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
617 $sql = 'DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id';
618 $parser = new Parser($sql);
619 $statement = $parser->statements[0];
620 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
621
622 $result = $transformer->buildProjection($statement, $sql, []);
623 self::assertSame('SELECT `u`.* FROM users AS `u` JOIN orders AS `o` ON u.id = o.user_id ', $result['sql']);
624 self::assertSame('users', $result['table']);
625 self::assertSame(['users' => ['alias' => 'u'], 'orders' => ['alias' => 'o']], $result['tables']);
626 }
627
628 public function testBuildProjectionExactSqlForDeleteWithFromAlias(): void
629 {
630 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
631 $sql = 'DELETE FROM users u WHERE u.id = 1';
632 $parser = new Parser($sql);
633 $statement = $parser->statements[0];
634 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
635
636 $result = $transformer->buildProjection($statement, $sql, ['id']);
637 self::assertSame('SELECT `u`.`id` AS `id` FROM users AS `u` WHERE u.id = 1', $result['sql']);
638 self::assertSame('users', $result['table']);
639 self::assertSame(['users' => ['alias' => 'u']], $result['tables']);
640 }
641
642 public function testBuildProjectionExactSqlForDeleteWithUsingSyntax(): void
643 {
644 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
645 $sql = 'DELETE u FROM users u USING users u, orders o WHERE u.id = o.user_id';
646 $parser = new Parser($sql);
647 $statement = $parser->statements[0];
648 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
649
650 $result = $transformer->buildProjection($statement, $sql, ['id']);
651 self::assertSame('SELECT `u`.`id` AS `id` FROM users AS `u`, orders AS `o` WHERE u.id = o.user_id', $result['sql']);
652 self::assertSame('users', $result['table']);
653 self::assertSame(['users' => ['alias' => 'u']], $result['tables']);
654 }
655
656 public function testBuildProjectionExactSqlForDeleteWithOrderByAndLimit(): void
657 {
658 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
659 $sql = 'DELETE FROM users ORDER BY id DESC LIMIT 3';
660 $parser = new Parser($sql);
661 $statement = $parser->statements[0];
662 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
663
664 $result = $transformer->buildProjection($statement, $sql, ['id']);
665 self::assertSame('SELECT `users`.`id` AS `id` FROM users ORDER BY id DESC LIMIT 0, 3', $result['sql']);
666 }
667
668 public function testBuildProjectionExactSqlForDeleteWithNoColumns(): void
669 {
670 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
671 $sql = 'DELETE FROM users WHERE id = 1';
672 $parser = new Parser($sql);
673 $statement = $parser->statements[0];
674 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
675
676 $result = $transformer->buildProjection($statement, $sql, []);
677 self::assertSame('SELECT `users`.* FROM users WHERE id = 1', $result['sql']);
678 }
679
680 public function testTransformExactOutputWithShadowData(): void
681 {
682 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
683 $sql = 'DELETE FROM users WHERE id = 1';
684 $tables = [
685 'users' => [
686 'rows' => [['id' => 1, 'name' => 'Alice']],
687 'columns' => ['id', 'name'],
688 'columnTypes' => [],
689 ],
690 ];
691
692 $result = $transformer->transform($sql, $tables);
693 self::assertSame(
694 'WITH `users` AS (SELECT CAST(1 AS SIGNED) AS `id`, CAST(' . "'" . 'Alice' . "'" . ' AS CHAR) AS `name`)' . "\n"
695 . 'SELECT `users`.`id` AS `id`, `users`.`name` AS `name` FROM users WHERE id = 1',
696 $result
697 );
698 }
699
700 public function testTransformExactOutputWithEmptyRows(): void
701 {
702 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
703 $sql = 'DELETE FROM users WHERE id = 1';
704 $tables = [
705 'users' => [
706 'rows' => [],
707 'columns' => ['id', 'name'],
708 'columnTypes' => [],
709 ],
710 ];
711
712 $result = $transformer->transform($sql, $tables);
713 self::assertSame(
714 'WITH `users` AS (SELECT CAST(NULL AS CHAR) AS `id`, CAST(NULL AS CHAR) AS `name` FROM DUAL WHERE 0)' . "\n"
715 . 'SELECT `users`.`id` AS `id`, `users`.`name` AS `name` FROM users WHERE id = 1',
716 $result
717 );
718 }
719
720 public function testTransformExactOutputWildcardWhenNoTableContext(): void
721 {
722 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
723 $sql = 'DELETE FROM users WHERE id = 1';
724
725 $result = $transformer->transform($sql, []);
726 self::assertSame('SELECT `users`.* FROM users WHERE id = 1', $result);
727 }
728
729 public function testBuildDeleteTargetResolvedFromJoinNotFrom(): void
730 {
731 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
732 $sql = 'DELETE o FROM users u JOIN orders o ON u.id = o.user_id WHERE u.active = 0';
733 $parser = new Parser($sql);
734 $statement = $parser->statements[0];
735 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
736
737 $result = $transformer->buildProjection($statement, $sql, ['id', 'amount']);
738 self::assertSame('orders', $result['table']);
739 self::assertSame('SELECT `o`.`id` AS `id`, `o`.`amount` AS `amount` FROM users AS `u` JOIN orders AS `o` ON u.id = o.user_id WHERE u.active = 0', $result['sql']);
740 self::assertSame(['orders' => ['alias' => 'o']], $result['tables']);
741 }
742
743 public function testBuildDeleteMultiTargetResolvesFromUsingClause(): void
744 {
745 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
746 $sql = 'DELETE u, o FROM users u USING users u, orders o WHERE u.id = o.user_id';
747 $parser = new Parser($sql);
748 $statement = $parser->statements[0];
749 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
750
751 $result = $transformer->buildProjection($statement, $sql, []);
752 self::assertSame('users', $result['table']);
753 self::assertArrayHasKey('users', $result['tables']);
754 self::assertArrayHasKey('orders', $result['tables']);
755 self::assertSame('u', $result['tables']['users']['alias']);
756 self::assertSame('o', $result['tables']['orders']['alias']);
757 }
758
759 public function testBuildProjectionFromClauseResolvesTargetTableName(): void
760 {
761 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
762 $sql = 'DELETE FROM users WHERE id = 1';
763 $parser = new Parser($sql);
764 $statement = $parser->statements[0];
765 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
766
767 $result = $transformer->buildProjection($statement, $sql, ['id', 'name']);
768 self::assertSame('users', $result['table']);
769 self::assertSame('SELECT `users`.`id` AS `id`, `users`.`name` AS `name` FROM users WHERE id = 1', $result['sql']);
770 self::assertSame(['users' => ['alias' => 'users']], $result['tables']);
771 }
772
773 public function testBuildProjectionAliasedFromResolvesAlias(): void
774 {
775 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
776 $sql = 'DELETE FROM users AS u WHERE u.id = 1';
777 $parser = new Parser($sql);
778 $statement = $parser->statements[0];
779 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
780
781 $result = $transformer->buildProjection($statement, $sql, ['id']);
782 self::assertSame('users', $result['table']);
783 self::assertStringContainsString('`u`.`id`', $result['sql']);
784 }
785
786 public function testBuildProjectionPartitionThrowsRuntimeException(): void
787 {
788 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
789 $sql = 'DELETE FROM users PARTITION (p0) WHERE id = 1';
790 $parser = new Parser($sql);
791 $statement = $parser->statements[0];
792 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
793
794 self::expectException(RuntimeException::class);
795 self::expectExceptionMessage('PARTITION');
796 $transformer->buildProjection($statement, $sql, []);
797 }
798
799 public function testBuildProjectionWithOrderByAndLimit(): void
800 {
801 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
802 $sql = 'DELETE FROM users WHERE active = 0 ORDER BY id LIMIT 10';
803 $parser = new Parser($sql);
804 $statement = $parser->statements[0];
805 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
806
807 $result = $transformer->buildProjection($statement, $sql, []);
808 self::assertStringContainsString('ORDER BY', $result['sql']);
809 self::assertStringContainsString('LIMIT', $result['sql']);
810 }
811
812 public function testBuildProjectionWithJoinClause(): void
813 {
814 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
815 $sql = 'DELETE users FROM users JOIN orders ON users.id = orders.user_id WHERE orders.total = 0';
816 $parser = new Parser($sql);
817 $statement = $parser->statements[0];
818 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
819
820 $result = $transformer->buildProjection($statement, $sql, []);
821 self::assertStringContainsString('JOIN', $result['sql']);
822 self::assertSame('users', $result['table']);
823 }
824
825 public function testBuildProjectionEmptyColumnsUsesWildcard(): void
826 {
827 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
828 $sql = 'DELETE FROM users WHERE id = 1';
829 $parser = new Parser($sql);
830 $statement = $parser->statements[0];
831 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
832
833 $result = $transformer->buildProjection($statement, $sql, []);
834 self::assertStringContainsString('`users`.*', $result['sql']);
835 }
836
837 public function testTransformDeleteFromSimpleExactSql(): void
838 {
839 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
840 $sql = 'DELETE FROM items WHERE id = 5';
841 $tables = [
842 'items' => [
843 'rows' => [['id' => 5, 'val' => 'x']],
844 'columns' => ['id', 'val'],
845 'columnTypes' => [],
846 ],
847 ];
848
849 $result = $transformer->transform($sql, $tables);
850 self::assertStringContainsString('AS `id`', $result);
851 self::assertStringContainsString('AS `val`', $result);
852 self::assertStringContainsString('WHERE id = 5', $result);
853 }
854
855 public function testTransformDeleteMultiTableFromJoinExactOutput(): void
856 {
857 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
858 $sql = 'DELETE o FROM users u JOIN orders o ON u.id = o.user_id WHERE u.active = 0';
859 $tables = [
860 'orders' => [
861 'rows' => [['id' => 1, 'user_id' => 10]],
862 'columns' => ['id', 'user_id'],
863 'columnTypes' => [],
864 ],
865 ];
866
867 $result = $transformer->transform($sql, $tables);
868 self::assertStringContainsString('AS `id`', $result);
869 self::assertStringContainsString('AS `user_id`', $result);
870 }
871
872 public function testBuildProjectionMultiDeleteTargetFromUsingResolvesBothTables(): void
873 {
874 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
875 $sql = 'DELETE a FROM t1 a USING t1 a, t2 b WHERE a.id = b.ref_id';
876 $parser = new Parser($sql);
877 $statement = $parser->statements[0];
878 self::assertInstanceOf(\PhpMyAdmin\SqlParser\Statements\DeleteStatement::class, $statement);
879
880 $result = $transformer->buildProjection($statement, $sql, []);
881 self::assertSame('t1', $result['table']);
882 self::assertSame(['t1' => ['alias' => 'a']], $result['tables']);
883 }
884
885 public function testTransformMultiDeleteProjectsEveryTargetIdentity(): void
886 {
887 $transformer = new DeleteTransformer(new MySqlParser(), new SelectTransformer());
888 $tables = [
889 'users' => [
890 'rows' => [['id' => 2, 'name' => 'Bob']],
891 'columns' => ['id', 'name'],
892 'columnTypes' => [],
893 'primaryKeys' => ['id'],
894 ],
895 'orders' => [
896 'rows' => [['order_id' => 9, 'user_id' => 2]],
897 'columns' => ['order_id', 'user_id'],
898 'columnTypes' => [],
899 'primaryKeys' => ['order_id'],
900 ],
901 ];
902
903 $result = $transformer->transform(
904 'DELETE u, o FROM users u JOIN orders o ON u.id = o.user_id WHERE u.id = 2',
905 $tables,
906 );
907
908 self::assertStringContainsString(
909 'SELECT `u`.`id` AS `__ztd_multi_0_value_0`, `o`.`order_id` AS `__ztd_multi_1_value_0`',
910 $result,
911 );
912 self::assertStringNotContainsString('`o`.`user_id` AS `__ztd_multi_1_value_1`', $result);
913 }
914}
915