packages/ztd-query-mysql/tests/Unit/Rewrite/Upsert/MySqlNativeUpsertProjectorTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Rewrite\Upsert;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Platform\MySql\Rewrite\Upsert\MySqlNativeUpsertProjector;
11
12#[CoversClass(MySqlNativeUpsertProjector::class)]
13#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlIdentifierQuoter::class)]
14#[UsesClass(\ZtdQuery\Platform\MySql\Sql\MySqlLexerProfile::class)]
15#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\ConflictPredicate::class)]
16#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\ExpressionBinder::class)]
17#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\MetadataColumns::class)]
18#[CoversClass(\ZtdQuery\Platform\MySql\Rewrite\Upsert\QualifiedColumn::class)]
19final class MySqlNativeUpsertProjectorTest extends TestCase
20{
21 public function testProjectsMySqlFunctionsThroughExistingAndIncomingRows(): void
22 {
23 $projector = new MySqlNativeUpsertProjector();
24
25 $result = $projector->project(
26 'SELECT 1 AS `id`, 15 AS `price`, 2 AS `version`, \'{}\' AS `meta`',
27 'products',
28 ['id', 'price', 'version', 'meta'],
29 ['empty_key' => [], 'PRIMARY' => ['id'], 'version_key' => ['price', 'version']],
30 [
31 'price' => 'IF(VALUES(version) > version, VALUES(price), price)',
32 'meta' => "JSON_SET(meta, '$.price', VALUES(price))",
33 ],
34 'VALUES(version) > version',
35 );
36
37 self::assertSame(
38 'SELECT `__ztd_incoming`.`id` AS `id`, `__ztd_incoming`.`price` AS `price`, '
39 . '`__ztd_incoming`.`version` AS `version`, `__ztd_incoming`.`meta` AS `meta`, '
40 . '(SELECT IF(`__ztd_incoming`.`version` > `__ztd_existing`.`version`, '
41 . '`__ztd_incoming`.`price`, `__ztd_existing`.`price`) FROM `products` AS `__ztd_existing` '
42 . 'WHERE ((`__ztd_existing`.`id` = `__ztd_incoming`.`id`) OR '
43 . '(`__ztd_existing`.`price` = `__ztd_incoming`.`price` AND '
44 . '`__ztd_existing`.`version` = `__ztd_incoming`.`version`)) LIMIT 1) AS `__ztd_upsert_value_0`, '
45 . "(SELECT JSON_SET(`__ztd_existing`.`meta`, '$.price', `__ztd_incoming`.`price`) "
46 . 'FROM `products` AS `__ztd_existing` WHERE ((`__ztd_existing`.`id` = `__ztd_incoming`.`id`) OR '
47 . '(`__ztd_existing`.`price` = `__ztd_incoming`.`price` AND '
48 . '`__ztd_existing`.`version` = `__ztd_incoming`.`version`)) LIMIT 1) AS `__ztd_upsert_value_1`, '
49 . '(SELECT `__ztd_incoming`.`version` > `__ztd_existing`.`version` '
50 . 'FROM `products` AS `__ztd_existing` WHERE ((`__ztd_existing`.`id` = `__ztd_incoming`.`id`) OR '
51 . '(`__ztd_existing`.`price` = `__ztd_incoming`.`price` AND '
52 . '`__ztd_existing`.`version` = `__ztd_incoming`.`version`)) LIMIT 1) AS `__ztd_upsert_predicate` '
53 . "FROM (SELECT 1 AS `id`, 15 AS `price`, 2 AS `version`, '{}' AS `meta`) AS `__ztd_incoming`",
54 $result,
55 );
56 }
57
58 public function testLeavesIncompleteIncomingFunctionSyntaxUnchanged(): void
59 {
60 $projector = new MySqlNativeUpsertProjector();
61
62 $missingColumn = $projector->project(
63 'SELECT 1 AS `id`, 2 AS `price`',
64 'items',
65 ['id', 'price'],
66 ['PRIMARY' => ['id']],
67 ['price' => 'VALUES('],
68 );
69 $missingClose = $projector->project(
70 'SELECT 1 AS `id`, 2 AS `price`',
71 'items',
72 ['id', 'price'],
73 ['PRIMARY' => ['id']],
74 ['price' => 'VALUES(price'],
75 );
76
77 self::assertStringContainsString('(SELECT VALUES( ', $missingColumn);
78 self::assertStringContainsString('(SELECT VALUES(`__ztd_existing`.`price` ', $missingClose);
79 }
80
81 public function testReturnsIncomingProjectionWhenConflictMetadataIsUnavailable(): void
82 {
83 $projector = new MySqlNativeUpsertProjector();
84
85 self::assertSame(
86 'SELECT 1 AS "id"',
87 $projector->project('SELECT 1 AS "id"', 'items', ['id'], [], ['id' => 'EXCLUDED.id']),
88 );
89 self::assertSame(
90 'SELECT 1 AS "id"',
91 $projector->project('SELECT 1 AS "id"', 'items', ['id'], ['PRIMARY' => ['id']], []),
92 );
93 }
94
95 public function testKeepsIdentifiersInsideScalarSubqueryInTheirNativeScope(): void
96 {
97 $result = (new MySqlNativeUpsertProjector())->project(
98 'SELECT 1 AS `id`, 0 AS `price`',
99 'prices',
100 ['id', 'price'],
101 ['PRIMARY' => ['id']],
102 ['price' => 'price + (SELECT price FROM price_list WHERE id = prices.id) + COALESCE(VALUES(price), 0)'],
103 );
104
105 self::assertSame(
106 'SELECT `__ztd_incoming`.`id` AS `id`, `__ztd_incoming`.`price` AS `price`, '
107 . '(SELECT `__ztd_existing`.`price` + (SELECT price FROM price_list WHERE id = prices.id) '
108 . '+ COALESCE(`__ztd_incoming`.`price`, 0) FROM `prices` AS `__ztd_existing` '
109 . 'WHERE ((`__ztd_existing`.`id` = `__ztd_incoming`.`id`)) LIMIT 1) AS `__ztd_upsert_value_0` '
110 . 'FROM (SELECT 1 AS `id`, 0 AS `price`) AS `__ztd_incoming`',
111 $result,
112 );
113 }
114
115 public function testBindsQualifiedAndQuotedIdentifiersWithoutChangingForeignNamespaces(): void
116 {
117 $result = (new MySqlNativeUpsertProjector())->project(
118 'SELECT 1 AS `id`, 2 AS `odd``name`, 3 AS `price`',
119 'prices',
120 ['id', 'odd`name', 'price'],
121 ['PRIMARY' => ['id']],
122 ['price' => 'VALUES(`odd``name`) + prices.price + foreign.price + price(price) + CONSTANT'],
123 );
124
125 self::assertSame(
126 'SELECT `__ztd_incoming`.`id` AS `id`, `__ztd_incoming`.`odd``name` AS `odd``name`, '
127 . '`__ztd_incoming`.`price` AS `price`, '
128 . '(SELECT `__ztd_incoming`.`odd``name` + `__ztd_existing`.`price` + foreign.price '
129 . '+ price(`__ztd_existing`.`price`) + CONSTANT FROM `prices` AS `__ztd_existing` '
130 . 'WHERE ((`__ztd_existing`.`id` = `__ztd_incoming`.`id`)) LIMIT 1) AS `__ztd_upsert_value_0` '
131 . 'FROM (SELECT 1 AS `id`, 2 AS `odd``name`, 3 AS `price`) AS `__ztd_incoming`',
132 $result,
133 );
134 }
135
136 public function testMatchesColumnsAndIncomingNamespaceCaseInsensitively(): void
137 {
138 $result = (new MySqlNativeUpsertProjector())->project(
139 'SELECT 1 AS `ID`, 2 AS `PRICE`',
140 'ITEMS',
141 ['ID', 'PRICE'],
142 ['PRIMARY' => ['ID']],
143 ['PRICE' => 'values(PRICE) + PRICE + ITEMS.PRICE'],
144 );
145
146 self::assertSame(
147 'SELECT `__ztd_incoming`.`ID` AS `ID`, `__ztd_incoming`.`PRICE` AS `PRICE`, '
148 . '(SELECT `__ztd_incoming`.`PRICE` + `__ztd_existing`.`PRICE` + `__ztd_existing`.`PRICE` '
149 . 'FROM `ITEMS` AS `__ztd_existing` WHERE ((`__ztd_existing`.`ID` = `__ztd_incoming`.`ID`)) '
150 . 'LIMIT 1) AS `__ztd_upsert_value_0` '
151 . 'FROM (SELECT 1 AS `ID`, 2 AS `PRICE`) AS `__ztd_incoming`',
152 $result,
153 );
154 }
155
156 public function testBindsExplicitIncomingRowAlias(): void
157 {
158 $result = (new MySqlNativeUpsertProjector())->project(
159 'SELECT 1 AS `id`, \'new\' AS `odd``name`',
160 'users',
161 ['id', 'odd`name'],
162 ['PRIMARY' => ['id']],
163 ['odd`name' => '`InCoMiNg`.`odd``name`'],
164 incomingNamespace: 'incoming',
165 );
166
167 self::assertStringContainsString(
168 '(SELECT `__ztd_incoming`.`odd``name` FROM `users` AS `__ztd_existing`',
169 $result,
170 );
171 self::assertStringNotContainsString('`InCoMiNg`.', $result);
172 }
173
174 public function testBindsUnqualifiedQuotedIdentifier(): void
175 {
176 $result = (new MySqlNativeUpsertProjector())->project(
177 'SELECT 1 AS `id`, 2 AS `odd``name`',
178 'items',
179 ['id', 'odd`name'],
180 ['PRIMARY' => ['id']],
181 ['odd`name' => '`odd``name` + 1'],
182 );
183
184 self::assertStringContainsString(
185 '(SELECT `__ztd_existing`.`odd``name` + 1 FROM `items` AS `__ztd_existing`',
186 $result,
187 );
188 }
189
190 public function testBindsPartialConflictPredicateToExistingAndIncomingRows(): void
191 {
192 $result = (new MySqlNativeUpsertProjector())->project(
193 'SELECT \'alice@example.com\' AS `email`, \'active\' AS `status`, 1 AS `login_count`',
194 'users',
195 ['email', 'status', 'login_count'],
196 ['users_active_email' => ['email']],
197 ['login_count' => 'GREATEST(login_count, VALUES(login_count))'],
198 conflictPredicate: "status = 'active'",
199 );
200
201 self::assertStringContainsString(
202 '`__ztd_existing`.`email` = `__ztd_incoming`.`email`',
203 $result,
204 );
205 self::assertStringContainsString(
206 '(`__ztd_existing`.`status` = \'active\') AND (`__ztd_incoming`.`status` = \'active\')',
207 $result,
208 );
209 }
210}
211