packages/ztd-query-mysql/tests/Unit/Rewrite/Transformer/InsertTransformerTest.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 RuntimeException;
11use ZtdQuery\Exception\UnsupportedSqlException;
12use ZtdQuery\Platform\CastRenderer;
13use ZtdQuery\Platform\MySql\Rewrite\Transformer\InsertSelectRenderer;
14use ZtdQuery\Platform\MySql\Rewrite\Transformer\InsertTransformer;
15use ZtdQuery\Platform\MySql\Rewrite\Transformer\MySqlSelectListAliaser;
16use ZtdQuery\Platform\MySql\Rewrite\Transformer\SelectTransformer;
17use ZtdQuery\Platform\MySql\Sql\Dml\InsertSelectSourceExtractor;
18use ZtdQuery\Platform\MySql\Sql\MySqlIdentifierQuoter;
19use ZtdQuery\Platform\MySql\Sql\MySqlParser;
20use ZtdQuery\Platform\MySql\Sql\Relation\MySqlSelectRelationParser;
21use ZtdQuery\Platform\MySql\Sql\Upsert\MySqlUpsertAssignmentExtractor;
22use ZtdQuery\Platform\MySql\Sql\Value\MySqlCastRenderer;
23use ZtdQuery\Schema\ColumnDeclaration;
24use ZtdQuery\Schema\ColumnTypeFamily;
25use ZtdQuery\Schema\Key\IdentityGenerationStrategy;
26
27#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\MySqlPartitionSelectionRewriter::class)]
28#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\HeaderParser::class)]
29#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Cte\IdentifierReferences::class)]
30#[UsesClass(\ZtdQuery\Platform\MySql\Sql\OptionalInsertIntoNormalizer::class)]
31#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Relation\ReferenceReader::class)]
32#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Upsert\AssignmentReader::class)]
33#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\ExpressionEditor::class)]
34#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SelectionReader::class)]
35#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Partition\SourceProjection::class)]
36#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\ConflictPredicate::class)]
37#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\ExpressionBinder::class)]
38#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\MetadataColumns::class)]
39#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\QualifiedColumn::class)]
40
41
42#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Select\ExpressionAliaser::class)]
43#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\OrderRewriter::class)]
44#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Set\ValueNormalizer::class)]
45#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Shadow\CteRows::class)]
46#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\CastTypeResolver::class)]
47#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\RankEdits::class)]
48#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\ScalarExpression::class)]
49#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\StringCoercion::class)]
50#[CoversClass(InsertTransformer::class)]
51#[UsesClass(MySqlParser::class)]
52#[UsesClass(MySqlSelectRelationParser::class)]
53#[UsesClass(MySqlUpsertAssignmentExtractor::class)]
54#[UsesClass(SelectTransformer::class)]
55#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\FullText\MySqlFullTextSearchRewriter::class)]
56#[UsesClass(InsertSelectSourceExtractor::class)]
57#[UsesClass(MySqlCastRenderer::class)]
58#[UsesClass(MySqlIdentifierQuoter::class)]
59#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\InsertRowRenderer::class)]
60#[UsesClass(InsertSelectRenderer::class)]
61#[UsesClass(MySqlSelectListAliaser::class)]
62#[UsesClass(\ZtdQuery\Platform\MySql\Sql\Value\MySqlValueRenderer::class)]
63#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Type\MySqlTypeSemantics::class)]
64#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Cte\MySqlCteShadowComposer::class)]
65#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\MySqlNativeUpsertProjector::class)]
66#[UsesClass(\ZtdQuery\Platform\MySql\Rewrite\GeneratedColumn\MySqlGeneratedColumnProjector::class)]
67#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
68#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Insert\InsertTarget::class)]
69#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Transformer\Insert\ResultProjection::class)]
70final class InsertTransformerTest extends TestCase
71{
72 public function testProjectsUpsertExpressionUsingCandidateKeys(): void
73 {
74 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
75 $tables = [
76 'users' => [
77 'rows' => [],
78 'columns' => ['id', 'name'],
79 'columnTypes' => [],
80 'candidateKeys' => ['PRIMARY' => ['id']],
81 ],
82 ];
83
84 $result = $transformer->transform(
85 "INSERT INTO users (id, name) VALUES (1, 'Alice') ON DUPLICATE KEY UPDATE name = VALUES(name)",
86 $tables,
87 );
88
89 self::assertStringContainsString('`__ztd_incoming`.`name`', $result);
90 self::assertStringContainsString('__ztd_upsert_value_0', $result);
91 self::assertStringNotContainsString('VALUES(', $result);
92 }
93
94 public function testTransformInsertFromCompoundSelectPreservesEveryBranch(): void
95 {
96 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
97 $tables = [
98 'combined' => [
99 'rows' => [],
100 'columns' => ['name', 'amount'],
101 'columnTypes' => [],
102 ],
103 ];
104
105 $result = $transformer->transform(
106 'INSERT INTO combined (name, amount) SELECT name, amount FROM archive UNION ALL SELECT name, amount FROM current',
107 $tables,
108 );
109
110 self::assertStringContainsString(
111 'FROM archive UNION ALL SELECT name, amount FROM current',
112 $result,
113 );
114 self::assertStringContainsString('`__ztd_insert_0` AS `name`', $result);
115 self::assertStringContainsString('`__ztd_insert_1` AS `amount`', $result);
116 }
117
118 public function testUsesInjectedCastRendererAndColumnTypes(): void
119 {
120 $castRenderer = self::createStub(CastRenderer::class);
121 $castRenderer->method('renderCast')->willReturn('CUSTOM_CAST');
122 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer(), $castRenderer);
123 $tables = [
124 'users' => [
125 'rows' => [],
126 'columns' => ['id'],
127 'columnTypes' => ['id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT')],
128 ],
129 ];
130
131 $result = $transformer->transform('INSERT INTO users (id) VALUES (1)', $tables);
132
133 self::assertStringContainsString('SELECT CUSTOM_CAST AS `id`', $result);
134 }
135
136 public function testTransformInsertWithValues(): void
137 {
138 $parser = new MySqlParser();
139 $selectTransformer = new SelectTransformer();
140 $transformer = new InsertTransformer($parser, $selectTransformer);
141
142 $sql = "INSERT INTO users (id, name) VALUES (1, 'Alice')";
143 $tables = [
144 'users' => [
145 'rows' => [],
146 'columns' => ['id', 'name'],
147 'columnTypes' => [],
148 ],
149 ];
150
151 $result = $transformer->transform($sql, $tables);
152 self::assertStringContainsString('SELECT', $result);
153 }
154
155 public function testTransformThrowsForNonInsertStatement(): void
156 {
157 $parser = new MySqlParser();
158 $selectTransformer = new SelectTransformer();
159 $transformer = new InsertTransformer($parser, $selectTransformer);
160
161 $this->expectException(UnsupportedSqlException::class);
162 $this->expectExceptionMessage('Expected INSERT statement');
163 $transformer->transform('SELECT 1', []);
164 }
165
166 public function testTransformInsertWithSetSyntax(): void
167 {
168 $parser = new MySqlParser();
169 $selectTransformer = new SelectTransformer();
170 $transformer = new InsertTransformer($parser, $selectTransformer);
171
172 $sql = "INSERT INTO users SET id = 1, name = 'Alice'";
173 $tables = [
174 'users' => [
175 'rows' => [],
176 'columns' => ['id', 'name'],
177 'columnTypes' => [],
178 ],
179 ];
180
181 $result = $transformer->transform($sql, $tables);
182 self::assertStringContainsString('SELECT', $result);
183 }
184
185 public function testTransformInsertMultipleRowsUsesUnionAll(): void
186 {
187 $parser = new MySqlParser();
188 $selectTransformer = new SelectTransformer();
189 $transformer = new InsertTransformer($parser, $selectTransformer);
190
191 $sql = "INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')";
192 $tables = [
193 'users' => [
194 'rows' => [],
195 'columns' => ['id', 'name'],
196 'columnTypes' => [],
197 ],
198 ];
199
200 $result = $transformer->transform($sql, $tables);
201 self::assertStringContainsString('UNION ALL', $result);
202 self::assertStringContainsString('AS `id`', $result);
203 self::assertStringContainsString('AS `name`', $result);
204 }
205
206 public function testTransformInsertResolvesColumnsFromTablesContext(): void
207 {
208 $parser = new MySqlParser();
209 $selectTransformer = new SelectTransformer();
210 $transformer = new InsertTransformer($parser, $selectTransformer);
211
212 $sql = "INSERT INTO users VALUES (1, 'Alice')";
213 $tables = [
214 'users' => [
215 'rows' => [],
216 'columns' => ['id', 'name'],
217 'columnTypes' => [],
218 ],
219 ];
220
221 $result = $transformer->transform($sql, $tables);
222 self::assertStringContainsString('AS `id`', $result);
223 self::assertStringContainsString('AS `name`', $result);
224 }
225
226 public function testTransformInsertThrowsWhenNoColumnsResolvable(): void
227 {
228 $parser = new MySqlParser();
229 $selectTransformer = new SelectTransformer();
230 $transformer = new InsertTransformer($parser, $selectTransformer);
231
232 $sql = "INSERT INTO users VALUES (1, 'Alice')";
233
234 $this->expectException(UnsupportedSqlException::class);
235 $this->expectExceptionMessage('Cannot determine columns');
236 $transformer->transform($sql, []);
237 }
238
239 public function testTransformInsertSelectSubquery(): void
240 {
241 $parser = new MySqlParser();
242 $selectTransformer = new SelectTransformer();
243 $transformer = new InsertTransformer($parser, $selectTransformer);
244
245 $sql = 'INSERT INTO archive (id, name) SELECT id, name FROM users WHERE active = 0';
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 self::assertStringContainsString('AS `id`', $result);
257 self::assertStringContainsString('AS `name`', $result);
258 }
259
260 public function testTransformInsertSetProjectsCorrectColumns(): void
261 {
262 $parser = new MySqlParser();
263 $selectTransformer = new SelectTransformer();
264 $transformer = new InsertTransformer($parser, $selectTransformer);
265
266 $sql = "INSERT INTO users SET id = 1, name = 'Alice'";
267 $tables = [
268 'users' => [
269 'rows' => [],
270 'columns' => ['id', 'name'],
271 'columnTypes' => [],
272 ],
273 ];
274
275 $result = $transformer->transform($sql, $tables);
276 self::assertStringContainsString('1 AS `id`', $result);
277 self::assertStringContainsString("'Alice' AS `name`", $result);
278 }
279
280 public function testTransformInsertValuesProjectsColumnAliases(): void
281 {
282 $parser = new MySqlParser();
283 $selectTransformer = new SelectTransformer();
284 $transformer = new InsertTransformer($parser, $selectTransformer);
285
286 $sql = "INSERT INTO users (id, name) VALUES (1, 'Alice')";
287 $tables = [];
288
289 $result = $transformer->transform($sql, $tables);
290 self::assertStringContainsString('1 AS `id`', $result);
291 self::assertStringContainsString("'Alice' AS `name`", $result);
292 }
293
294 public function testTransformInsertValuesExactSelectFormat(): void
295 {
296 $parser = new MySqlParser();
297 $selectTransformer = new SelectTransformer();
298 $transformer = new InsertTransformer($parser, $selectTransformer);
299
300 $sql = 'INSERT INTO t (a, b) VALUES (1, 2)';
301 $tables = [];
302
303 $result = $transformer->transform($sql, $tables);
304 self::assertStringStartsWith('SELECT 1 AS `a`, 2 AS `b`', $result);
305 }
306
307 public function testTransformInsertMultipleRowsExactUnionFormat(): void
308 {
309 $parser = new MySqlParser();
310 $selectTransformer = new SelectTransformer();
311 $transformer = new InsertTransformer($parser, $selectTransformer);
312
313 $sql = 'INSERT INTO t (a) VALUES (1), (2)';
314 $tables = [];
315
316 $result = $transformer->transform($sql, $tables);
317 self::assertStringContainsString('SELECT 1 AS `a` UNION ALL SELECT 2 AS `a`', $result);
318 }
319
320 public function testTransformInsertFromSelectWithWhereAndGroupBy(): void
321 {
322 $parser = new MySqlParser();
323 $selectTransformer = new SelectTransformer();
324 $transformer = new InsertTransformer($parser, $selectTransformer);
325
326 $sql = 'INSERT INTO archive (cnt) SELECT COUNT(*) FROM users WHERE active = 1 GROUP BY dept';
327 $tables = [
328 'archive' => [
329 'rows' => [],
330 'columns' => ['cnt'],
331 'columnTypes' => [],
332 ],
333 ];
334
335 $result = $transformer->transform($sql, $tables);
336 self::assertStringContainsString('AS `cnt`', $result);
337 self::assertStringContainsString('FROM users', $result);
338 self::assertStringContainsString('WHERE active = 1', $result);
339 self::assertStringContainsString('GROUP BY dept', $result);
340 }
341
342 public function testTransformInsertFromSelectWithOrderByAndLimit(): void
343 {
344 $parser = new MySqlParser();
345 $selectTransformer = new SelectTransformer();
346 $transformer = new InsertTransformer($parser, $selectTransformer);
347
348 $sql = 'INSERT INTO archive (id) SELECT id FROM users ORDER BY id LIMIT 10';
349 $tables = [
350 'archive' => [
351 'rows' => [],
352 'columns' => ['id'],
353 'columnTypes' => [],
354 ],
355 ];
356
357 $result = $transformer->transform($sql, $tables);
358 self::assertStringContainsString('AS `id`', $result);
359 self::assertStringContainsString('FROM users', $result);
360 self::assertStringContainsString('ORDER BY', $result);
361 self::assertStringContainsString('id', $result);
362 self::assertStringContainsString('LIMIT', $result);
363 }
364
365 public function testTransformInsertFromSelectWithHaving(): void
366 {
367 $parser = new MySqlParser();
368 $selectTransformer = new SelectTransformer();
369 $transformer = new InsertTransformer($parser, $selectTransformer);
370
371 $sql = 'INSERT INTO archive (cnt) SELECT COUNT(*) FROM users GROUP BY dept HAVING COUNT(*) > 1';
372 $tables = [
373 'archive' => [
374 'rows' => [],
375 'columns' => ['cnt'],
376 'columnTypes' => [],
377 ],
378 ];
379
380 $result = $transformer->transform($sql, $tables);
381 self::assertStringContainsString('AS `cnt`', $result);
382 self::assertStringContainsString('FROM users', $result);
383 self::assertStringContainsString('GROUP BY dept', $result);
384 self::assertStringContainsString('HAVING COUNT(*) > 1', $result);
385 }
386
387 public function testTransformInsertFromSelectWithAliasedExpression(): void
388 {
389 $parser = new MySqlParser();
390 $selectTransformer = new SelectTransformer();
391 $transformer = new InsertTransformer($parser, $selectTransformer);
392
393 $sql = 'INSERT INTO archive (total) SELECT SUM(amount) AS total FROM orders';
394 $tables = [
395 'archive' => [
396 'rows' => [],
397 'columns' => ['total'],
398 'columnTypes' => [],
399 ],
400 ];
401
402 $result = $transformer->transform($sql, $tables);
403 self::assertStringContainsString('AS `total`', $result);
404 }
405
406 public function testTransformInsertFromSelectWithCaseExpression(): void
407 {
408 $parser = new MySqlParser();
409 $selectTransformer = new SelectTransformer();
410 $transformer = new InsertTransformer($parser, $selectTransformer);
411
412 $sql = "INSERT INTO statuses (status) SELECT CASE WHEN active = 1 THEN 'yes' ELSE 'no' END FROM users";
413 $tables = [
414 'statuses' => [
415 'rows' => [],
416 'columns' => ['status'],
417 'columnTypes' => [],
418 ],
419 ];
420
421 $result = $transformer->transform($sql, $tables);
422 self::assertStringContainsString('AS `status`', $result);
423 self::assertStringContainsString('CASE', $result);
424 }
425
426 public function testTransformInsertFromSelectExactOrderByLimitFormat(): void
427 {
428 $parser = new MySqlParser();
429 $selectTransformer = new SelectTransformer();
430 $transformer = new InsertTransformer($parser, $selectTransformer);
431
432 $sql = 'INSERT INTO archive (id) SELECT id FROM users ORDER BY id DESC LIMIT 5';
433 $tables = [
434 'archive' => [
435 'rows' => [],
436 'columns' => ['id'],
437 'columnTypes' => [],
438 ],
439 ];
440
441 $result = $transformer->transform($sql, $tables);
442 self::assertStringContainsString('ORDER BY id DESC', $result);
443 self::assertStringContainsString('LIMIT', $result);
444 self::assertStringContainsString('SELECT', $result);
445 self::assertStringContainsString('FROM', $result);
446 }
447
448 public function testTransformInsertFromSelectSubqueryWrapping(): void
449 {
450 $parser = new MySqlParser();
451 $selectTransformer = new SelectTransformer();
452 $transformer = new InsertTransformer($parser, $selectTransformer);
453
454 $sql = 'INSERT INTO archive (id, name) SELECT id, name FROM users WHERE active = 0';
455 $tables = [
456 'archive' => [
457 'rows' => [],
458 'columns' => ['id', 'name'],
459 'columnTypes' => [],
460 ],
461 ];
462
463 $result = $transformer->transform($sql, $tables);
464 self::assertStringContainsString('`__ztd_insert_source`', $result);
465 self::assertStringContainsString('AS (SELECT id AS `__ztd_insert_0`, name AS `__ztd_insert_1` FROM users WHERE active = 0)', $result);
466 self::assertStringContainsString('SELECT `__ztd_insert_0` AS `id`', $result);
467 }
468
469 public function testTransformInsertFromSelectUsesTableColumnsWhenColumnListIsOmitted(): void
470 {
471 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
472 $tables = ['archive' => [
473 'rows' => [],
474 'columns' => ['id', 'name'],
475 'columnTypes' => [],
476 ]];
477
478 $result = $transformer->transform('INSERT INTO archive SELECT id, name FROM users', $tables);
479
480 self::assertSame(
481 'WITH `__ztd_insert_source` (`__ztd_insert_0`, `__ztd_insert_1`) AS (SELECT id AS `__ztd_insert_0`, name AS `__ztd_insert_1` FROM users) SELECT `__ztd_insert_0` AS `id`, `__ztd_insert_1` AS `name` FROM `__ztd_insert_source`',
482 $result,
483 );
484 }
485
486 public function testTransformInsertFromSelectColumnCountMismatchThrows(): void
487 {
488 $parser = new MySqlParser();
489 $selectTransformer = new SelectTransformer();
490 $transformer = new InsertTransformer($parser, $selectTransformer);
491
492 $sql = 'INSERT INTO archive (id, name, email) SELECT id, name FROM users';
493 $tables = [
494 'archive' => [
495 'rows' => [],
496 'columns' => ['id', 'name', 'email'],
497 'columnTypes' => [],
498 ],
499 ];
500
501 $this->expectException(RuntimeException::class);
502 $transformer->transform($sql, $tables);
503 }
504
505 public function testTransformInsertSetSelectFormat(): void
506 {
507 $parser = new MySqlParser();
508 $selectTransformer = new SelectTransformer();
509 $transformer = new InsertTransformer($parser, $selectTransformer);
510
511 $sql = "INSERT INTO users SET id = 1, name = 'Alice'";
512 $tables = [
513 'users' => [
514 'rows' => [],
515 'columns' => ['id', 'name'],
516 'columnTypes' => [],
517 ],
518 ];
519
520 $result = $transformer->transform($sql, $tables);
521 self::assertStringStartsWith('SELECT', $result);
522 self::assertStringContainsString('1 AS `id`', $result);
523 }
524
525 public function testTransformInsertValuesCountMismatchThrows(): void
526 {
527 $parser = new MySqlParser();
528 $selectTransformer = new SelectTransformer();
529 $transformer = new InsertTransformer($parser, $selectTransformer);
530
531 $sql = 'INSERT INTO users (id, name) VALUES (1)';
532 $tables = [];
533
534 $this->expectException(RuntimeException::class);
535 $this->expectExceptionCode(0);
536 $transformer->transform($sql, $tables);
537 }
538
539 public function testTransformInsertFromSelectWithWhereClauseExactFormat(): void
540 {
541 $parser = new MySqlParser();
542 $selectTransformer = new SelectTransformer();
543 $transformer = new InsertTransformer($parser, $selectTransformer);
544
545 $sql = 'INSERT INTO archive (id) SELECT id FROM users WHERE id > 10';
546 $tables = [
547 'archive' => [
548 'rows' => [],
549 'columns' => ['id'],
550 'columnTypes' => [],
551 ],
552 ];
553
554 $result = $transformer->transform($sql, $tables);
555 self::assertStringContainsString(' WHERE id > 10', $result);
556 }
557
558 public function testTransformInsertFromSelectWithGroupByExactFormat(): void
559 {
560 $parser = new MySqlParser();
561 $selectTransformer = new SelectTransformer();
562 $transformer = new InsertTransformer($parser, $selectTransformer);
563
564 $sql = 'INSERT INTO archive (cnt) SELECT COUNT(*) FROM users GROUP BY status';
565 $tables = [
566 'archive' => [
567 'rows' => [],
568 'columns' => ['cnt'],
569 'columnTypes' => [],
570 ],
571 ];
572
573 $result = $transformer->transform($sql, $tables);
574 self::assertStringContainsString(' GROUP BY status', $result);
575 }
576
577 public function testTransformInsertFromSelectWithHavingExactFormat(): void
578 {
579 $parser = new MySqlParser();
580 $selectTransformer = new SelectTransformer();
581 $transformer = new InsertTransformer($parser, $selectTransformer);
582
583 $sql = 'INSERT INTO archive (cnt) SELECT COUNT(*) FROM users GROUP BY dept HAVING COUNT(*) > 5';
584 $tables = [
585 'archive' => [
586 'rows' => [],
587 'columns' => ['cnt'],
588 'columnTypes' => [],
589 ],
590 ];
591
592 $result = $transformer->transform($sql, $tables);
593 self::assertStringContainsString(' HAVING COUNT(*) > 5', $result);
594 }
595
596 public function testTransformInsertFromSelectLimitValueIncluded(): void
597 {
598 $parser = new MySqlParser();
599 $selectTransformer = new SelectTransformer();
600 $transformer = new InsertTransformer($parser, $selectTransformer);
601
602 $sql = 'INSERT INTO archive (id) SELECT id FROM users LIMIT 7';
603 $tables = [
604 'archive' => [
605 'rows' => [],
606 'columns' => ['id'],
607 'columnTypes' => [],
608 ],
609 ];
610
611 $result = $transformer->transform($sql, $tables);
612 self::assertMatchesRegularExpression('/LIMIT\s+\d/', $result);
613 }
614
615 public function testTransformInsertFromSelectWrappedSubqueryHasSelectKeyword(): void
616 {
617 $parser = new MySqlParser();
618 $selectTransformer = new SelectTransformer();
619 $transformer = new InsertTransformer($parser, $selectTransformer);
620
621 $sql = 'INSERT INTO archive (id) SELECT id FROM users WHERE id > 0';
622 $tables = [
623 'archive' => [
624 'rows' => [],
625 'columns' => ['id'],
626 'columnTypes' => [],
627 ],
628 ];
629
630 $result = $transformer->transform($sql, $tables);
631 self::assertStringContainsString('SELECT', $result);
632 self::assertStringContainsString('__ztd_insert_source', $result);
633 }
634
635 public function testTransformInsertFromSelectGroupByValueIncluded(): void
636 {
637 $parser = new MySqlParser();
638 $selectTransformer = new SelectTransformer();
639 $transformer = new InsertTransformer($parser, $selectTransformer);
640
641 $sql = 'INSERT INTO archive (cnt) SELECT COUNT(*) FROM users GROUP BY department';
642 $tables = [
643 'archive' => [
644 'rows' => [],
645 'columns' => ['cnt'],
646 'columnTypes' => [],
647 ],
648 ];
649
650 $result = $transformer->transform($sql, $tables);
651 self::assertStringContainsString('GROUP BY', $result);
652 self::assertStringContainsString('department', $result);
653 }
654
655 public function testTransformInsertFromSelectWrappedExactOutput(): void
656 {
657 $parser = new MySqlParser();
658 $selectTransformer = new SelectTransformer();
659 $transformer = new InsertTransformer($parser, $selectTransformer);
660
661 $sql = 'INSERT INTO t (a, b) SELECT x, y FROM src WHERE x > 0';
662 $tables = [];
663
664 $result = $transformer->transform($sql, $tables);
665 self::assertSame('WITH `__ztd_insert_source` (`__ztd_insert_0`, `__ztd_insert_1`) AS (SELECT x AS `__ztd_insert_0`, y AS `__ztd_insert_1` FROM src WHERE x > 0) SELECT `__ztd_insert_0` AS `a`, `__ztd_insert_1` AS `b` FROM `__ztd_insert_source`', $result);
666 }
667
668 public function testTransformInsertFromSelectAliasedExpressionDropsAlias(): void
669 {
670 $parser = new MySqlParser();
671 $selectTransformer = new SelectTransformer();
672 $transformer = new InsertTransformer($parser, $selectTransformer);
673
674 $sql = 'INSERT INTO t (val) SELECT x AS alias FROM src';
675 $tables = [
676 't' => [
677 'rows' => [],
678 'columns' => ['val'],
679 'columnTypes' => [],
680 ],
681 ];
682
683 $result = $transformer->transform($sql, $tables);
684 self::assertStringContainsString('`__ztd_insert_0`', $result);
685 self::assertStringContainsString('__ztd_insert_source', $result);
686 self::assertStringNotContainsString('AS `alias`', $result);
687 }
688
689 public function testTransformInsertSetExactSqlOutput(): void
690 {
691 $parser = new MySqlParser();
692 $selectTransformer = new SelectTransformer();
693 $transformer = new InsertTransformer($parser, $selectTransformer);
694
695 $sql = "INSERT INTO t SET name = 'Alice', age = 30";
696 $tables = [
697 't' => [
698 'rows' => [],
699 'columns' => ['name', 'age'],
700 'columnTypes' => [],
701 ],
702 ];
703
704 $result = $transformer->transform($sql, $tables);
705 self::assertStringContainsString("'Alice' AS `name`", $result);
706 self::assertStringContainsString('30 AS `age`', $result);
707 }
708
709 public function testTransformInsertValuesTrimsWhitespace(): void
710 {
711 $parser = new MySqlParser();
712 $selectTransformer = new SelectTransformer();
713 $transformer = new InsertTransformer($parser, $selectTransformer);
714
715 $sql = "INSERT INTO t (a) VALUES ( 'hello' )";
716 $tables = [];
717
718 $result = $transformer->transform($sql, $tables);
719 self::assertSame("SELECT 'hello' AS `a`", $result);
720 }
721
722 public function testTransformInsertFromSelectWithAllClauses(): void
723 {
724 $parser = new MySqlParser();
725 $selectTransformer = new SelectTransformer();
726 $transformer = new InsertTransformer($parser, $selectTransformer);
727
728 $sql = 'INSERT INTO t (val) SELECT cnt FROM src WHERE cnt > 0 GROUP BY dept HAVING cnt > 1 ORDER BY cnt LIMIT 10';
729 $tables = [
730 't' => [
731 'rows' => [],
732 'columns' => ['val'],
733 'columnTypes' => [],
734 ],
735 ];
736
737 $result = $transformer->transform($sql, $tables);
738 self::assertStringContainsString('WHERE', $result);
739 self::assertStringContainsString('GROUP BY', $result);
740 self::assertStringContainsString('HAVING', $result);
741 self::assertStringContainsString('ORDER BY', $result);
742 self::assertStringContainsString('LIMIT', $result);
743 }
744
745 public function testTransformInsertColumnsResolvedFromTableContext(): void
746 {
747 $parser = new MySqlParser();
748 $selectTransformer = new SelectTransformer();
749 $transformer = new InsertTransformer($parser, $selectTransformer);
750
751 $sql = "INSERT INTO t VALUES (1, 'x')";
752 $tables = [
753 't' => [
754 'rows' => [],
755 'columns' => ['id', 'name'],
756 'columnTypes' => [],
757 ],
758 ];
759
760 $result = $transformer->transform($sql, $tables);
761 self::assertStringContainsString('1 AS `id`', $result);
762 self::assertStringContainsString("'x' AS `name`", $result);
763 }
764
765 public function testTransformInsertNoColumnsNoContextThrows(): void
766 {
767 $parser = new MySqlParser();
768 $selectTransformer = new SelectTransformer();
769 $transformer = new InsertTransformer($parser, $selectTransformer);
770
771 $sql = "INSERT INTO t VALUES (1, 'x')";
772
773 $this->expectException(UnsupportedSqlException::class);
774 $this->expectExceptionMessage('Cannot determine columns');
775 $transformer->transform($sql, []);
776 }
777
778 public function testTransformInsertFromSelectWithFromAndWhereExact(): void
779 {
780 $parser = new MySqlParser();
781 $selectTransformer = new SelectTransformer();
782 $transformer = new InsertTransformer($parser, $selectTransformer);
783
784 $sql = 'INSERT INTO t (a) SELECT x FROM src WHERE x > 0';
785 $tables = [];
786
787 $result = $transformer->transform($sql, $tables);
788 self::assertSame(
789 'WITH `__ztd_insert_source` (`__ztd_insert_0`) AS (SELECT x AS `__ztd_insert_0` FROM src WHERE x > 0) SELECT `__ztd_insert_0` AS `a` FROM `__ztd_insert_source`',
790 $result
791 );
792 }
793
794 public function testTransformInsertFromSelectMultipleColumnsFromMultipleTables(): void
795 {
796 $parser = new MySqlParser();
797 $selectTransformer = new SelectTransformer();
798 $transformer = new InsertTransformer($parser, $selectTransformer);
799
800 $sql = 'INSERT INTO t (a, b) SELECT s1.x, s2.y FROM s1, s2 WHERE s1.id = s2.id';
801 $tables = [];
802
803 $result = $transformer->transform($sql, $tables);
804 self::assertStringContainsString('FROM s1, s2', $result);
805 self::assertStringContainsString('WHERE s1.id = s2.id', $result);
806 self::assertStringContainsString('AS `a`', $result);
807 self::assertStringContainsString('AS `b`', $result);
808 }
809
810 public function testTransformInsertFromSelectWithAllClausesExactOutput(): void
811 {
812 $parser = new MySqlParser();
813 $selectTransformer = new SelectTransformer();
814 $transformer = new InsertTransformer($parser, $selectTransformer);
815
816 $sql = 'INSERT INTO t (cnt) SELECT COUNT(*) FROM src WHERE active = 1 GROUP BY dept HAVING COUNT(*) > 1 ORDER BY dept ASC LIMIT 10';
817 $tables = [];
818
819 $result = $transformer->transform($sql, $tables);
820 self::assertStringContainsString('FROM src', $result);
821 self::assertStringContainsString('WHERE active = 1', $result);
822 self::assertStringContainsString('GROUP BY dept', $result);
823 self::assertStringContainsString('HAVING COUNT(*) > 1', $result);
824 self::assertStringContainsString('ORDER BY', $result);
825 self::assertStringContainsString('LIMIT', $result);
826 }
827
828 public function testTransformInsertFromSelectAliasedExprDropsAliasExactOutput(): void
829 {
830 $parser = new MySqlParser();
831 $selectTransformer = new SelectTransformer();
832 $transformer = new InsertTransformer($parser, $selectTransformer);
833
834 $sql = 'INSERT INTO t (val) SELECT x AS myalias FROM src';
835 $tables = [];
836
837 $result = $transformer->transform($sql, $tables);
838 self::assertSame(
839 'WITH `__ztd_insert_source` (`__ztd_insert_0`) AS (SELECT x AS `__ztd_insert_0` FROM src) SELECT `__ztd_insert_0` AS `val` FROM `__ztd_insert_source`',
840 $result
841 );
842 }
843
844 public function testTransformInsertValuesWithWhitespaceTrimsCorrectly(): void
845 {
846 $parser = new MySqlParser();
847 $selectTransformer = new SelectTransformer();
848 $transformer = new InsertTransformer($parser, $selectTransformer);
849
850 $sql = "INSERT INTO t (a, b) VALUES ( 1 , 'hello' )";
851 $tables = [];
852
853 $result = $transformer->transform($sql, $tables);
854 self::assertStringContainsString('1 AS `a`', $result);
855 self::assertStringContainsString("'hello' AS `b`", $result);
856 }
857
858 public function testTransformProjectsOmittedAndExplicitDefaults(): void
859 {
860 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
861 $tables = ['users' => [
862 'rows' => [],
863 'columns' => ['id', 'status', 'note'],
864 'columnTypes' => [],
865 'columnDefaults' => ['status' => "'active'"],
866 ]];
867
868 $result = $transformer->transform('INSERT INTO users (id, status) VALUES (1, DEFAULT)', $tables);
869
870 self::assertStringContainsString('1 AS `id`', $result);
871 self::assertStringContainsString("'active' AS `status`", $result);
872 self::assertStringContainsString('NULL AS `note`', $result);
873 }
874
875 public function testTransformDefaultOnlyInsertProjectsCompleteRow(): void
876 {
877 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
878 $tables = ['settings' => [
879 'rows' => [],
880 'columns' => ['enabled', 'label'],
881 'columnTypes' => [],
882 'columnDefaults' => ['enabled' => '1', 'label' => "'new'"],
883 ]];
884
885 $result = $transformer->transform('INSERT INTO settings () VALUES ()', $tables);
886
887 self::assertStringContainsString('1 AS `enabled`', $result);
888 self::assertStringContainsString("'new' AS `label`", $result);
889 }
890
891 public function testTransformProjectsDefaultWhenIntoIsOmitted(): void
892 {
893 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
894 $tables = ['settings' => [
895 'rows' => [],
896 'columns' => ['enabled'],
897 'columnTypes' => [],
898 'columnDefaults' => ['enabled' => '1'],
899 ]];
900
901 $result = $transformer->transform(
902 "INSERT # lead\n HIGH_PRIORITY -- priority\n IGNORE settings VALUE(/* value */ DEFAULT)",
903 $tables,
904 );
905
906 self::assertStringContainsString('1 AS `enabled`', $result);
907 }
908
909 public function testTransformNormalizesSparseTableColumnKeys(): void
910 {
911 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
912 $tables = ['users' => [
913 'rows' => [],
914 'columns' => [2 => 'id', 5 => 'name'],
915 'columnTypes' => [],
916 ]];
917
918 $result = $transformer->transform("INSERT INTO users (id, name) VALUES (1, 'Alice')", $tables);
919
920 self::assertStringContainsString('1 AS `id`', $result);
921 self::assertStringContainsString("'Alice' AS `name`", $result);
922 }
923
924 public function testCommitRewriteStateTransformAllocatesMonotonicAutoIncrementValues(): void
925 {
926 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
927 $tables = ['users' => [
928 'rows' => [],
929 'columns' => ['id', 'name'],
930 'columnTypes' => [],
931 'identityStrategies' => ['id' => IdentityGenerationStrategy::MaxValue],
932 ]];
933
934 $first = $transformer->transform("INSERT INTO users (name) VALUES ('Alice'), ('Bob')", $tables);
935 $transformer->commitRewriteState();
936 $second = $transformer->transform("INSERT INTO users (name) VALUES ('Carol')", $tables);
937
938 self::assertStringContainsString('1 AS `id`', $first);
939 self::assertStringContainsString('2 AS `id`', $first);
940 self::assertStringContainsString('3 AS `id`', $second);
941 }
942
943 public function testUncommittedTransformDoesNotConsumeAutoIncrementValue(): void
944 {
945 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
946 $tables = ['users' => [
947 'rows' => [],
948 'columns' => ['id', 'name'],
949 'columnTypes' => [],
950 'identityStrategies' => ['id' => IdentityGenerationStrategy::MaxValue],
951 ]];
952
953 $preview = $transformer->transform("INSERT INTO users (name) VALUES ('preview')", $tables);
954 $executed = $transformer->transform("INSERT INTO users (name) VALUES ('executed')", $tables);
955
956 self::assertStringContainsString('1 AS `id`', $preview);
957 self::assertStringContainsString('1 AS `id`', $executed);
958 }
959
960 public function testTransformAllocatesAfterExistingAutoIncrementRows(): void
961 {
962 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
963 $tables = ['users' => [
964 'rows' => [['id' => 7, 'name' => 'Existing']],
965 'columns' => ['id', 'name'],
966 'columnTypes' => [],
967 'identityStrategies' => ['id' => IdentityGenerationStrategy::MaxValue],
968 ]];
969
970 $result = $transformer->transform("INSERT INTO users (name) VALUES ('Alice')", $tables);
971
972 self::assertStringContainsString('8 AS `id`', $result);
973 }
974
975 public function testTransformInsertSelectAllocatesOnlyOmittedIdentityColumns(): void
976 {
977 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
978 $tables = ['users' => [
979 'rows' => [['id' => 7, 'name' => 'Existing']],
980 'columns' => ['id', 'name'],
981 'columnTypes' => [],
982 'identityStrategies' => ['id' => IdentityGenerationStrategy::MaxValue],
983 ]];
984
985 $explicit = $transformer->transform('INSERT INTO users (id, name) SELECT id, name FROM source', $tables);
986 $generated = $transformer->transform('INSERT INTO users (name) SELECT name FROM source', $tables);
987
988 self::assertStringContainsString('8 + ROW_NUMBER() OVER () - 1 AS `id`', $generated);
989 self::assertStringContainsString('`__ztd_insert_0` AS `id`', $explicit);
990 self::assertStringNotContainsString('ROW_NUMBER()', $explicit);
991 }
992
993 public function testExplicitValuesIdentityDoesNotConsumeGeneratedIdentity(): void
994 {
995 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
996 $tables = ['users' => [
997 'rows' => [],
998 'columns' => ['id', 'name'],
999 'columnTypes' => [],
1000 'identityStrategies' => ['id' => IdentityGenerationStrategy::MaxValue],
1001 ]];
1002
1003 $transformer->transform("INSERT INTO users (id, name) VALUES (42, 'explicit')", $tables);
1004 $transformer->commitRewriteState();
1005 $generated = $transformer->transform("INSERT INTO users (name) VALUES ('generated')", $tables);
1006
1007 self::assertStringContainsString('1 AS `id`', $generated);
1008 }
1009
1010 public function testExplicitSetIdentityDoesNotConsumeGeneratedIdentity(): void
1011 {
1012 $transformer = new InsertTransformer(new MySqlParser(), new SelectTransformer());
1013 $tables = ['users' => [
1014 'rows' => [],
1015 'columns' => ['id', 'name'],
1016 'columnTypes' => [],
1017 'identityStrategies' => ['id' => IdentityGenerationStrategy::MaxValue],
1018 ]];
1019
1020 $transformer->transform("INSERT INTO users SET id = 42, name = 'explicit'", $tables);
1021 $transformer->commitRewriteState();
1022 $generated = $transformer->transform("INSERT INTO users SET name = 'generated'", $tables);
1023
1024 self::assertStringContainsString('1 AS `id`', $generated);
1025 }
1026 public function testInsertSelectCastsDestinationTypesAndQuotesUntypedColumns(): void
1027 {
1028 $tables = [
1029 'years' => [
1030 'rows' => [],
1031 'columns' => ['id', 'value', 'note`tag'],
1032 'columnTypes' => [
1033 'id' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'INT'),
1034 'value' => new ColumnDeclaration(ColumnTypeFamily::INTEGER, 'YEAR'),
1035 ],
1036 ],
1037 ];
1038 $sql = (new InsertTransformer(new MySqlParser(), new SelectTransformer()))->transform(
1039 "INSERT INTO years SELECT 1, 93, 'ok'",
1040 $tables,
1041 );
1042 self::assertSame(
1043 'SELECT CAST(_ztd_insert_cast.`id` AS SIGNED) AS `id`, CAST(CAST(_ztd_insert_cast.`value` AS YEAR) AS SIGNED) AS `value`, _ztd_insert_cast.`note``tag` AS `note``tag` FROM ('
1044 . 'WITH `__ztd_insert_source` (`__ztd_insert_0`, `__ztd_insert_1`, `__ztd_insert_2`) AS ('
1045 . "SELECT 1 AS `__ztd_insert_0`, 93 AS `__ztd_insert_1`, 'ok' AS `__ztd_insert_2` ) "
1046 . 'SELECT `__ztd_insert_0` AS `id`, `__ztd_insert_1` AS `value`, `__ztd_insert_2` AS `note``tag` FROM `__ztd_insert_source`) AS _ztd_insert_cast',
1047 $sql,
1048 );
1049 }
1050
1051}
1052