packages/ztd-query-sqlite/tests/Unit/Sql/Dml/Expression/AssignmentParserTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Sql\Dml\Expression;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentParser;
11
12#[CoversClass(AssignmentParser::class)]
13#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Dml\Expression\AssignmentColumnParser::class)]
14#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\ExpressionSpan::class)]
15#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\IdentifierDecoder::class)]
16#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\Lexing\QuotedSpan::class)]
17#[UsesClass(\ZtdQuery\Platform\Sqlite\Sql\SqliteLexerProfile::class)]
18final class AssignmentParserTest extends TestCase
19{
20 public function testExtractUpdateAssignments(): void
21 {
22 $parser = new AssignmentParser();
23 $assignments = $parser->extractUpdateAssignments("UPDATE users SET name = 'Bob', age = 30 WHERE id = 1");
24 self::assertSame("'Bob'", $assignments['name']);
25 self::assertSame('30', $assignments['age']);
26 }
27
28 public function testExtractOnConflictUpdates(): void
29 {
30 $parser = new AssignmentParser();
31 $updates = $parser->extractOnConflictUpdates(
32 'INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO UPDATE SET name = excluded.name'
33 );
34 self::assertArrayHasKey('name', $updates);
35 self::assertSame('excluded.name', $updates['name']);
36 }
37
38 public function testExtractOnConflictUpdatesExtractOnConflictStopsBeforeReturningWithoutWhere(): void
39 {
40 $parser = new AssignmentParser();
41
42 self::assertSame(
43 ['name' => 'excluded.name'],
44 $parser->extractOnConflictUpdates(
45 'INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO UPDATE SET name = excluded.name RETURNING id'
46 ),
47 );
48 }
49
50 public function testExtractOnConflictUpdatesExtractOnConflictRejectsNonUpdateActionContainingSet(): void
51 {
52 $parser = new AssignmentParser();
53
54 self::assertSame(
55 [],
56 $parser->extractOnConflictUpdates(
57 'INSERT INTO users (id, name) VALUES (1, "Alice") ON CONFLICT (id) DO NOTHING SET name = excluded.name'
58 ),
59 );
60 }
61
62 public function testExtractUpdateAssignmentsQuotedColumn(): void
63 {
64 $parser = new AssignmentParser();
65 $assignments = $parser->extractUpdateAssignments('UPDATE users SET "name" = \'Bob\' WHERE id = 1');
66 self::assertArrayHasKey('name', $assignments);
67 self::assertSame("'Bob'", $assignments['name']);
68 }
69
70 public function testExtractUpdateAssignmentsBacktickColumn(): void
71 {
72 $parser = new AssignmentParser();
73 $assignments = $parser->extractUpdateAssignments('UPDATE users SET `name` = \'Bob\' WHERE id = 1');
74 self::assertArrayHasKey('name', $assignments);
75 }
76
77 public function testExtractUpdateAssignmentsBracketColumn(): void
78 {
79 $parser = new AssignmentParser();
80 $assignments = $parser->extractUpdateAssignments('UPDATE users SET [name] = \'Bob\' WHERE id = 1');
81 self::assertArrayHasKey('name', $assignments);
82 }
83
84 public function testExtractUpdateAssignmentsTableQualified(): void
85 {
86 $parser = new AssignmentParser();
87 $assignments = $parser->extractUpdateAssignments('UPDATE users SET users.name = \'Bob\' WHERE id = 1');
88 self::assertArrayHasKey('name', $assignments);
89 }
90
91 public function testExtractUpdateAssignmentsWithFunction(): void
92 {
93 $parser = new AssignmentParser();
94 $assignments = $parser->extractUpdateAssignments("UPDATE t SET x = COALESCE(a, 'b') WHERE id = 1");
95 self::assertArrayHasKey('x', $assignments);
96 self::assertStringContainsString('COALESCE', $assignments['x']);
97 }
98
99 public function testExtractUpdateAssignmentsEmpty(): void
100 {
101 $parser = new AssignmentParser();
102 $assignments = $parser->extractUpdateAssignments('SELECT * FROM users');
103 self::assertSame([], $assignments);
104 }
105
106 public function testExtractUpdateAssignmentsWithOrderBy(): void
107 {
108 $parser = new AssignmentParser();
109 $assignments = $parser->extractUpdateAssignments('UPDATE t SET x = 1 ORDER BY id');
110 self::assertArrayHasKey('x', $assignments);
111 self::assertSame('1', $assignments['x']);
112 }
113
114 public function testExtractUpdateAssignmentsWithLimit(): void
115 {
116 $parser = new AssignmentParser();
117 $assignments = $parser->extractUpdateAssignments('UPDATE t SET x = 1 LIMIT 5');
118 self::assertArrayHasKey('x', $assignments);
119 self::assertSame('1', $assignments['x']);
120 }
121
122 public function testExtractOnConflictUpdatesDoNothing(): void
123 {
124 $parser = new AssignmentParser();
125 $updates = $parser->extractOnConflictUpdates('INSERT INTO t (id) VALUES (1) ON CONFLICT (id) DO NOTHING');
126 self::assertSame([], $updates);
127 }
128
129 public function testExtractOnConflictUpdatesNoConflict(): void
130 {
131 $parser = new AssignmentParser();
132 $updates = $parser->extractOnConflictUpdates('INSERT INTO t (id) VALUES (1)');
133 self::assertSame([], $updates);
134 }
135
136 public function testExtractOnConflictUpdatesMultiple(): void
137 {
138 $parser = new AssignmentParser();
139 $updates = $parser->extractOnConflictUpdates(
140 'INSERT INTO t (id, name, age) VALUES (1, \'a\', 2) ON CONFLICT (id) DO UPDATE SET name = excluded.name, age = excluded.age'
141 );
142 self::assertArrayHasKey('name', $updates);
143 self::assertArrayHasKey('age', $updates);
144 self::assertSame('excluded.name', $updates['name']);
145 self::assertSame('excluded.age', $updates['age']);
146 }
147
148 public function testExtractUpdateAssignmentsWithQuotedValue(): void
149 {
150 $parser = new AssignmentParser();
151 $assignments = $parser->extractUpdateAssignments("UPDATE t SET name = 'it''s cool' WHERE id = 1");
152 self::assertArrayHasKey('name', $assignments);
153 self::assertSame("'it''s cool'", $assignments['name']);
154 }
155
156 public function testExtractUpdateAssignmentsLowercase(): void
157 {
158 $parser = new AssignmentParser();
159 $assignments = $parser->extractUpdateAssignments("update users set name = 'Bob' where id = 1");
160 self::assertArrayHasKey('name', $assignments);
161 }
162
163 public function testExtractOnConflictUpdatesLowercase(): void
164 {
165 $parser = new AssignmentParser();
166 $updates = $parser->extractOnConflictUpdates(
167 "insert into t (id, name) values (1, 'a') on conflict (id) do update set name = excluded.name"
168 );
169 self::assertArrayHasKey('name', $updates);
170 }
171
172 public function testExtractUpdateAssignmentsWithLimitLowercase(): void
173 {
174 $parser = new AssignmentParser();
175 $assignments = $parser->extractUpdateAssignments('update t set x = 1 limit 5');
176 self::assertArrayHasKey('x', $assignments);
177 }
178
179 public function testExtractUpdateAssignmentsWithOrderByLowercase(): void
180 {
181 $parser = new AssignmentParser();
182 $assignments = $parser->extractUpdateAssignments('update t set x = 1 order by id');
183 self::assertArrayHasKey('x', $assignments);
184 }
185
186 public function testExtractUpdateAssignmentsParseAssignmentsQuotedColumnName(): void
187 {
188 $parser = new AssignmentParser();
189 $result = $parser->extractUpdateAssignments('UPDATE t SET "col name" = 1 WHERE id = 1');
190 self::assertArrayHasKey('col name', $result);
191 self::assertSame('1', $result['col name']);
192 }
193
194 public function testExtractUpdateAssignmentsParseAssignmentsBacktickColumnName(): void
195 {
196 $parser = new AssignmentParser();
197 $result = $parser->extractUpdateAssignments('UPDATE t SET `col` = 1 WHERE id = 2');
198 self::assertArrayHasKey('col', $result);
199 self::assertSame('1', $result['col']);
200 }
201
202 public function testExtractUpdateAssignmentsParseAssignmentsBracketColumnName(): void
203 {
204 $parser = new AssignmentParser();
205 $result = $parser->extractUpdateAssignments('UPDATE t SET [col] = 1 WHERE id = 3');
206 self::assertArrayHasKey('col', $result);
207 self::assertSame('1', $result['col']);
208 }
209
210 public function testExtractUpdateAssignmentsParseAssignmentsQualifiedColumnName(): void
211 {
212 $parser = new AssignmentParser();
213 $result = $parser->extractUpdateAssignments('UPDATE t SET t.name = \'Bob\' WHERE id = 1');
214 self::assertArrayHasKey('name', $result);
215 self::assertSame("'Bob'", $result['name']);
216 }
217
218 public function testExtractUpdateAssignmentsParseAssignmentsValueWithQuotes(): void
219 {
220 $parser = new AssignmentParser();
221 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 'it''s', b = 2 WHERE id = 1");
222 self::assertSame("'it''s'", $result['a']);
223 self::assertSame('2', $result['b']);
224 }
225
226 public function testExtractUpdateAssignmentsParseAssignmentsValueWithParens(): void
227 {
228 $parser = new AssignmentParser();
229 $result = $parser->extractUpdateAssignments('UPDATE t SET a = COALESCE(b, 0) WHERE id = 1');
230 self::assertSame('COALESCE(b, 0)', $result['a']);
231 }
232
233 public function testExtractUpdateAssignmentsParseAssignmentsNoSetClause(): void
234 {
235 $parser = new AssignmentParser();
236 $result = $parser->extractUpdateAssignments('UPDATE t WHERE id = 1');
237 self::assertSame([], $result);
238 }
239
240 public function testExtractOnConflictUpdatesV2(): void
241 {
242 $parser = new AssignmentParser();
243 $result = $parser->extractOnConflictUpdates('INSERT INTO t (a) VALUES (1) ON CONFLICT (a) DO UPDATE SET b = 2, c = 3');
244 self::assertSame(['b' => '2', 'c' => '3'], $result);
245 }
246
247 public function testExtractOnConflictUpdatesDoNothingV2(): void
248 {
249 $parser = new AssignmentParser();
250 $result = $parser->extractOnConflictUpdates('INSERT INTO t (a) VALUES (1) ON CONFLICT (a) DO NOTHING');
251 self::assertSame([], $result);
252 }
253
254 public function testExtractUpdateAssignmentsParseAssignmentsMultiple(): void
255 {
256 $parser = new AssignmentParser();
257 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 1, b = 'x', c = NULL WHERE id = 1");
258 self::assertCount(3, $result);
259 self::assertSame('1', $result['a']);
260 self::assertSame("'x'", $result['b']);
261 self::assertSame('NULL', $result['c']);
262 }
263
264 public function testExtractUpdateAssignmentsParseAssignmentsValueWithNestedParensAndClose(): void
265 {
266 $parser = new AssignmentParser();
267 $result = $parser->extractUpdateAssignments('UPDATE t SET a = (SELECT MAX(b) FROM t2) WHERE id = 1');
268 self::assertSame('(SELECT MAX(b) FROM t2)', $result['a']);
269 }
270
271 public function testExtractUpdateAssignmentsParseAssignmentsDoubleQuotedValueInExpression(): void
272 {
273 $parser = new AssignmentParser();
274 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 'x''y' WHERE id = 1");
275 self::assertSame("'x''y'", $result['a']);
276 }
277
278 public function testExtractUpdateAssignmentsParseAssignmentsValueEndingWithCloseParen(): void
279 {
280 $parser = new AssignmentParser();
281 $result = $parser->extractUpdateAssignments('UPDATE t SET a = FUNC(1)');
282 self::assertSame('FUNC(1)', $result['a']);
283 }
284
285 public function testExtractUpdateAssignmentsCaseInsensitive(): void
286 {
287 $parser = new AssignmentParser();
288 $result = $parser->extractUpdateAssignments("update t set name = 'bob' where id = 1");
289 self::assertSame('\'bob\'', $result['name']);
290 }
291
292 public function testExtractUpdateAssignmentsParseAssignmentsTablePrefixed(): void
293 {
294 $parser = new AssignmentParser();
295 $result = $parser->extractUpdateAssignments('UPDATE t SET t.name = 1');
296 self::assertSame('1', $result['name']);
297 }
298
299 public function testExtractUpdateAssignmentsParseAssignmentsEmptyColumnSkipped(): void
300 {
301 $parser = new AssignmentParser();
302 $result = $parser->extractUpdateAssignments('UPDATE t SET = 1');
303 self::assertSame([], $result);
304 }
305
306 public function testExtractUpdateAssignmentsParseAssignmentsQuotedStringValue(): void
307 {
308 $parser = new AssignmentParser();
309 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 'it''s', b = 2");
310 self::assertSame("'it''s'", $result['a']);
311 self::assertSame('2', $result['b']);
312 }
313
314 public function testExtractUpdateAssignmentsParseAssignmentsParenthesizedValue(): void
315 {
316 $parser = new AssignmentParser();
317 $result = $parser->extractUpdateAssignments('UPDATE t SET a = (SELECT 1), b = 2');
318 self::assertSame('(SELECT 1)', $result['a']);
319 self::assertSame('2', $result['b']);
320 }
321
322 public function testExtractOnConflictUpdatesCaseInsensitive(): void
323 {
324 $parser = new AssignmentParser();
325 $result = $parser->extractOnConflictUpdates('insert into t values (1) on conflict (id) do update set a = excluded.a');
326 self::assertArrayHasKey('a', $result);
327 }
328
329 public function testExtractUpdateAssignmentsParseAssignmentsMultipleCommaValues(): void
330 {
331 $parser = new AssignmentParser();
332 $result = $parser->extractUpdateAssignments("UPDATE t SET a = 1, b = 'two', c = 3");
333 self::assertSame('1', $result['a']);
334 self::assertSame("'two'", $result['b']);
335 self::assertSame('3', $result['c']);
336 }
337
338 public function testExtractUpdateAssignmentsMultiline(): void
339 {
340 $parser = new AssignmentParser();
341 $result = $parser->extractUpdateAssignments("UPDATE t SET name = 'bob',\nemail = 'x' WHERE id = 1");
342 self::assertSame("'bob'", $result['name']);
343 self::assertSame("'x'", $result['email']);
344 }
345
346 public function testExtractOnConflictUpdatesMultiline(): void
347 {
348 $parser = new AssignmentParser();
349 $result = $parser->extractOnConflictUpdates("INSERT INTO t VALUES (1) ON CONFLICT (id) DO UPDATE SET name = excluded.name,\nemail = excluded.email");
350 self::assertArrayHasKey('name', $result);
351 self::assertArrayHasKey('email', $result);
352 }
353
354 public function testExtractOnConflictUpdatesConflictAssignmentsIgnoreNestedWhereAndReturningKeywords(): void
355 {
356 $parser = new AssignmentParser();
357 $sql = 'INSERT INTO target (id, value) VALUES (1, 2) ON CONFLICT (id) DO UPDATE SET value = (SELECT value FROM source WHERE source.id = excluded.id), note = \'returning where\' WHERE target.active RETURNING *';
358
359 self::assertSame([
360 'value' => '(SELECT value FROM source WHERE source.id = excluded.id)',
361 'note' => "'returning where'",
362 ], $parser->extractOnConflictUpdates($sql));
363 }
364
365 public function testParseAssignmentsRetainsNestedCommas(): void
366 {
367 self::assertSame(['name' => "coalesce('a,b', name)", 'id' => 'id + 1'], (new AssignmentParser())->parseAssignments("name = coalesce('a,b', name), id = id + 1"));
368 }
369
370}
371