packages/ztd-query-core/tests/Unit/Shadow/Mutation/UpsertMutationTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Shadow\Mutation;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Exception\UnsupportedSqlException;
11use ZtdQuery\Schema\Key\CandidateKeyConflict;
12use ZtdQuery\Schema\Key\CandidateKeySet;
13use ZtdQuery\Schema\TableDefinition;
14use ZtdQuery\Shadow\Mutation\Upsert\UpsertOperator;
15use ZtdQuery\Shadow\Mutation\UpsertColumnSource;
16use ZtdQuery\Shadow\Mutation\UpsertExpression;
17use ZtdQuery\Shadow\Mutation\UpsertExpressionKind;
18use ZtdQuery\Shadow\Mutation\UpsertMutation;
19use ZtdQuery\Shadow\Mutation\UpsertMutationRow;
20use ZtdQuery\Shadow\Mutation\UpsertUpdate;
21use ZtdQuery\Shadow\ShadowStore;
22
23#[UsesClass(ShadowStore::class)]
24#[UsesClass(UnsupportedSqlException::class)]
25#[UsesClass(CandidateKeyConflict::class)]
26#[UsesClass(CandidateKeySet::class)]
27#[UsesClass(TableDefinition::class)]
28#[UsesClass(UpsertExpression::class)]
29#[UsesClass(UpsertMutationRow::class)]
30#[CoversClass(UpsertMutation::class)]
31#[UsesClass(\ZtdQuery\Schema\Key\CandidateKeyMatch::class)]
32#[UsesClass(\ZtdQuery\Shadow\Mutation\ConflictSearch::class)]
33#[UsesClass(\ZtdQuery\Shadow\Mutation\Upsert\UpsertColumn::class)]
34#[UsesClass(\ZtdQuery\Shadow\Mutation\Upsert\UpsertComparison::class)]
35#[UsesClass(\ZtdQuery\Shadow\Mutation\Upsert\UpsertNumber::class)]
36#[UsesClass(\ZtdQuery\Shadow\Mutation\Upsert\UpsertTruth::class)]
37#[UsesClass(UpsertUpdate::class)]
38#[UsesClass(UpsertOperator::class)]
39#[UsesClass(\ZtdQuery\Schema\RowSet::class)]
40#[UsesClass(\ZtdQuery\Shadow\Mutation\Upsert\UpsertLiteral::class)]
41final class UpsertMutationTest extends TestCase
42{
43 public function testApplyInsertsNewRowWhenNoDuplicate(): void
44 {
45 $store = new ShadowStore();
46 $store->set('users', [
47 ['id' => 1, 'name' => 'Alice', 'visits' => 10],
48 ]);
49
50 $mutation = new UpsertMutation('users', ['id']);
51 $mutation->apply($store, [['id' => 2, 'name' => 'Bob', 'visits' => 1]]);
52
53 $rows = $store->get('users');
54 self::assertCount(2, $rows);
55 self::assertSame('Bob', $rows[1]['name']);
56 }
57
58 public function testApplyUpdatesExistingRowOnDuplicate(): void
59 {
60 $store = new ShadowStore();
61 $store->set('users', [
62 ['id' => 1, 'name' => 'Alice', 'visits' => 10],
63 ]);
64
65 $mutation = new UpsertMutation('users', ['id'], ['visits']);
66 $mutation->apply($store, [['id' => 1, 'name' => 'Alice', 'visits' => 11]]);
67
68 $rows = $store->get('users');
69 self::assertCount(1, $rows);
70 self::assertSame(11, $rows[0]['visits']);
71 }
72
73 public function testSkippedConditionalRowDoesNotStopFollowingRows(): void
74 {
75 $store = new ShadowStore();
76 $store->set('users', [
77 ['id' => 1, 'name' => 'first', 'score' => 10],
78 ]);
79 $mutation = new UpsertMutation(
80 'users',
81 ['id'],
82 ['name'],
83 ['name' => UpsertExpression::column(UpsertColumnSource::Incoming, 'name')],
84 updatePredicate: UpsertExpression::binary(
85 UpsertExpressionKind::GreaterOrEqual,
86 UpsertExpression::column(UpsertColumnSource::Existing, 'score'),
87 UpsertExpression::literal(80),
88 ),
89 );
90
91 $mutation->apply($store, [
92 ['id' => 1, 'name' => 'skipped', 'score' => 20],
93 ['id' => 2, 'name' => 'inserted', 'score' => 90],
94 ]);
95
96 self::assertSame([
97 ['id' => 1, 'name' => 'first', 'score' => 10],
98 ['id' => 2, 'name' => 'inserted', 'score' => 90],
99 ], $store->get('users'));
100 self::assertSame([
101 ['id' => 2, 'name' => 'inserted', 'score' => 90],
102 ], $mutation->resultRows());
103 }
104
105 public function testApplyUpdatesExistingRowOnUniqueKeyConflict(): void
106 {
107 $definition = new TableDefinition(
108 ['id', 'email', 'name'],
109 ['id' => 'INT', 'email' => 'TEXT', 'name' => 'TEXT'],
110 ['id'],
111 ['id'],
112 ['users_email' => ['email']],
113 );
114 $store = new ShadowStore();
115 $store->set('users', [['id' => 1, 'email' => 'alice@example.com', 'name' => 'Alice']]);
116
117 $mutation = new UpsertMutation(
118 'users',
119 ['id'],
120 ['name'],
121 ['name' => UpsertExpression::column(UpsertColumnSource::Incoming, 'name')],
122 $definition->candidateKeys(),
123 );
124 $mutation->apply($store, [['id' => 2, 'email' => 'alice@example.com', 'name' => 'Updated']]);
125
126 self::assertSame([
127 ['id' => 1, 'email' => 'alice@example.com', 'name' => 'Updated'],
128 ], $store->get('users'));
129 }
130
131 public function testTableNameReturnsTableName(): void
132 {
133 $mutation = new UpsertMutation('users', ['id']);
134
135 self::assertSame('users', $mutation->tableName());
136 }
137
138 public function testApplyWithUpdateValuesExpression(): void
139 {
140 $store = new ShadowStore();
141 $store->set('users', [
142 ['id' => 1, 'name' => 'Alice', 'visits' => 10],
143 ]);
144
145 $mutation = new UpsertMutation(
146 'users',
147 ['id'],
148 ['visits'],
149 ['visits' => UpsertExpression::column(UpsertColumnSource::Incoming, 'visits')]
150 );
151 $mutation->apply($store, [['id' => 1, 'name' => 'Alice', 'visits' => 15]]);
152
153 $rows = $store->get('users');
154 self::assertSame(15, $rows[0]['visits']);
155 }
156
157 public function testApplyWithLiteralUpdateValue(): void
158 {
159 $store = new ShadowStore();
160 $store->set('users', [
161 ['id' => 1, 'name' => 'Alice', 'status' => 'pending'],
162 ]);
163
164 $mutation = new UpsertMutation(
165 'users',
166 ['id'],
167 ['status'],
168 ['status' => UpsertExpression::literal('updated')]
169 );
170 $mutation->apply($store, [['id' => 1, 'name' => 'Alice', 'status' => 'ignored']]);
171
172 $rows = $store->get('users');
173 self::assertSame('updated', $rows[0]['status']);
174 }
175
176 public function testApplyUpdatesAllNonPrimaryColumnsWhenNoUpdateColumnsSpecified(): void
177 {
178 $store = new ShadowStore();
179 $store->set('users', [
180 ['id' => 1, 'name' => 'Alice', 'visits' => 10],
181 ]);
182
183 $mutation = new UpsertMutation('users', ['id']);
184 $mutation->apply($store, [['id' => 1, 'name' => 'Alice Updated', 'visits' => 20]]);
185
186 $rows = $store->get('users');
187 self::assertSame('Alice Updated', $rows[0]['name']);
188 self::assertSame(20, $rows[0]['visits']);
189 }
190
191 public function testApplyHandlesMixedInsertAndUpdate(): void
192 {
193 $store = new ShadowStore();
194 $store->set('users', [
195 ['id' => 1, 'name' => 'Alice'],
196 ]);
197
198 $mutation = new UpsertMutation('users', ['id'], ['name']);
199 $mutation->apply($store, [
200 ['id' => 1, 'name' => 'Alice Updated'],
201 ['id' => 2, 'name' => 'Bob'],
202 ]);
203
204 $rows = $store->get('users');
205 self::assertCount(2, $rows);
206 self::assertSame('Alice Updated', $rows[0]['name']);
207 self::assertSame('Bob', $rows[1]['name']);
208 }
209
210 public function testApplyWithCompositePrimaryKey(): void
211 {
212 $store = new ShadowStore();
213 $store->set('order_items', [
214 ['order_id' => 1, 'product_id' => 100, 'quantity' => 1],
215 ]);
216
217 $mutation = new UpsertMutation('order_items', ['order_id', 'product_id'], ['quantity']);
218 $mutation->apply($store, [['order_id' => 1, 'product_id' => 100, 'quantity' => 5]]);
219
220 $rows = $store->get('order_items');
221 self::assertCount(1, $rows);
222 self::assertSame(5, $rows[0]['quantity']);
223 }
224
225 public function testApplyWithMissingPrimaryKeyInsertsNewRow(): void
226 {
227 $store = new ShadowStore();
228 $store->set('users', [
229 ['id' => 1, 'name' => 'Alice'],
230 ]);
231
232 $mutation = new UpsertMutation('users', ['id']);
233 $mutation->apply($store, [['name' => 'Bob']]);
234
235 $rows = $store->get('users');
236 self::assertCount(2, $rows);
237 }
238
239 public function testApplyWithExcludedColumnReference(): void
240 {
241 $store = new ShadowStore();
242 $store->set('users', [
243 ['id' => 1, 'name' => 'Alice', 'visits' => 10],
244 ]);
245
246 $mutation = new UpsertMutation(
247 'users',
248 ['id'],
249 ['name'],
250 ['name' => UpsertExpression::column(UpsertColumnSource::Incoming, 'name')]
251 );
252 $mutation->apply($store, [['id' => 1, 'name' => 'Bob', 'visits' => 15]]);
253
254 $rows = $store->get('users');
255 self::assertSame('Bob', $rows[0]['name']);
256 }
257
258 public function testApplyWithExcludedQuotedColumnReference(): void
259 {
260 $store = new ShadowStore();
261 $store->set('users', [
262 ['id' => 1, 'name' => 'Alice'],
263 ]);
264
265 $mutation = new UpsertMutation(
266 'users',
267 ['id'],
268 ['name'],
269 ['name' => UpsertExpression::column(UpsertColumnSource::Incoming, 'name')]
270 );
271 $mutation->apply($store, [['id' => 1, 'name' => 'Charlie']]);
272
273 $rows = $store->get('users');
274 self::assertSame('Charlie', $rows[0]['name']);
275 }
276
277 public function testApplyAccumulatesExistingAndIncomingValues(): void
278 {
279 $store = new ShadowStore();
280 $store->set('items', [
281 ['id' => 1, 'quantity' => 100],
282 ]);
283
284 $mutation = new UpsertMutation(
285 'items',
286 ['id'],
287 ['quantity'],
288 ['quantity' => UpsertExpression::binary(
289 UpsertExpressionKind::Add,
290 UpsertExpression::column(UpsertColumnSource::Existing, 'quantity'),
291 UpsertExpression::column(UpsertColumnSource::Incoming, 'quantity'),
292 )],
293 );
294 $mutation->apply($store, [['id' => 1, 'quantity' => 5]]);
295
296 self::assertSame([['id' => 1, 'quantity' => 105]], $store->get('items'));
297 }
298
299 public function testApplyAccumulatesSequentialConflictsAgainstLatestRow(): void
300 {
301 $store = new ShadowStore();
302 $store->set('items', [
303 ['id' => 1, 'quantity' => 100],
304 ]);
305
306 $mutation = new UpsertMutation(
307 'items',
308 ['id'],
309 ['quantity'],
310 ['quantity' => UpsertExpression::binary(
311 UpsertExpressionKind::Add,
312 UpsertExpression::column(UpsertColumnSource::Existing, 'quantity'),
313 UpsertExpression::column(UpsertColumnSource::Incoming, 'quantity'),
314 )],
315 );
316 $mutation->apply($store, [
317 ['id' => 1, 'quantity' => 5],
318 ['id' => 1, 'quantity' => 7],
319 ]);
320
321 self::assertSame([['id' => 1, 'quantity' => 112]], $store->get('items'));
322 }
323
324 public function testApplyUsesConflictPredicateAndReportsOnlyAppliedRows(): void
325 {
326 $store = new ShadowStore();
327 $store->set('items', [
328 ['id' => 1, 'name' => 'original', 'score' => 50],
329 ]);
330 $mutation = new UpsertMutation(
331 'items',
332 ['id'],
333 ['name'],
334 ['name' => UpsertExpression::column(UpsertColumnSource::Incoming, 'name')],
335 updatePredicate: UpsertExpression::binary(
336 UpsertExpressionKind::GreaterOrEqual,
337 UpsertExpression::column(UpsertColumnSource::Existing, 'score'),
338 UpsertExpression::literal(80),
339 ),
340 );
341
342 $mutation->apply($store, [['id' => 1, 'name' => 'skipped', 'score' => 95]]);
343 self::assertSame([['id' => 1, 'name' => 'original', 'score' => 50]], $store->get('items'));
344 self::assertSame([], $mutation->resultRows());
345
346 $store->set('items', [['id' => 1, 'name' => 'original', 'score' => 85]]);
347 $mutation->apply($store, [['id' => 1, 'name' => 'updated', 'score' => 95]]);
348 self::assertSame([['id' => 1, 'name' => 'updated', 'score' => 85]], $store->get('items'));
349 self::assertSame([['id' => 1, 'name' => 'updated', 'score' => 85]], $mutation->resultRows());
350 }
351
352 public function testApplyUsesDatabaseEvaluatedValuesAndStripsMetadata(): void
353 {
354 $store = new ShadowStore();
355 $store->set('items', [['id' => 1, 'value' => 'original']]);
356 $codec = new UpsertMutationRow();
357 $mutation = new UpsertMutation(
358 'items',
359 ['id'],
360 ['value'],
361 ['value' => null],
362 databaseEvaluated: true,
363 updateSqlValues: ['value' => 'unsupported(native_expression)'],
364 updateSqlPredicate: 'unsupported(native_predicate)',
365 );
366
367 $mutation->apply($store, [[
368 'id' => 1,
369 'value' => 'incoming',
370 $codec->valueColumn(0) => 'evaluated',
371 $codec->predicateColumn() => 1,
372 ]]);
373
374 self::assertSame([['id' => 1, 'value' => 'evaluated']], $store->get('items'));
375 self::assertSame([['id' => 1, 'value' => 'evaluated']], $mutation->resultRows());
376
377 $mutation->apply($store, [[
378 'id' => 2,
379 'value' => 'inserted',
380 $codec->valueColumn(0) => null,
381 $codec->predicateColumn() => null,
382 ]]);
383 self::assertSame(['id' => 2, 'value' => 'inserted'], $store->get('items')[1]);
384 }
385
386 public function testDatabaseEvaluatedBatchReevaluatesAgainstLatestConflictRow(): void
387 {
388 $store = new ShadowStore();
389 $store->set('items', [['id' => 1, 'quantity' => 100]]);
390 $codec = new UpsertMutationRow();
391 $mutation = new UpsertMutation(
392 'items',
393 ['id'],
394 ['quantity'],
395 ['quantity' => UpsertExpression::binary(
396 UpsertExpressionKind::Add,
397 UpsertExpression::column(UpsertColumnSource::Existing, 'quantity'),
398 UpsertExpression::column(UpsertColumnSource::Incoming, 'quantity'),
399 )],
400 databaseEvaluated: true,
401 updateSqlValues: ['quantity' => 'quantity + EXCLUDED.quantity'],
402 );
403
404 $mutation->apply($store, [
405 ['id' => 1, 'quantity' => 5, $codec->valueColumn(0) => 105],
406 ['id' => 1, 'quantity' => 7, $codec->valueColumn(0) => 107],
407 ]);
408
409 self::assertSame([['id' => 1, 'quantity' => 112]], $store->get('items'));
410 }
411
412 public function testDatabaseEvaluatedPredicateFallsBackWhenMetadataIsMissing(): void
413 {
414 $store = new ShadowStore();
415 $store->set('items', [['id' => 1, 'name' => 'original', 'score' => 50]]);
416 $codec = new UpsertMutationRow();
417 $mutation = new UpsertMutation(
418 'items',
419 ['id'],
420 ['name'],
421 ['name' => UpsertExpression::column(UpsertColumnSource::Incoming, 'name')],
422 updatePredicate: UpsertExpression::binary(
423 UpsertExpressionKind::GreaterOrEqual,
424 UpsertExpression::column(UpsertColumnSource::Existing, 'score'),
425 UpsertExpression::literal(80),
426 ),
427 databaseEvaluated: true,
428 updateSqlValues: ['name' => 'EXCLUDED.name'],
429 updateSqlPredicate: 'score >= 80',
430 );
431
432 $mutation->apply($store, [
433 [
434 'id' => 1,
435 'name' => 'skipped',
436 'score' => 95,
437 $codec->valueColumn(0) => 'skipped',
438 ],
439 [
440 'id' => 2,
441 'name' => 'inserted',
442 'score' => 95,
443 $codec->valueColumn(0) => null,
444 ],
445 ]);
446 self::assertSame([
447 ['id' => 1, 'name' => 'original', 'score' => 50],
448 ['id' => 2, 'name' => 'inserted', 'score' => 95],
449 ], $store->get('items'));
450
451 $store->set('items', [['id' => 1, 'name' => 'original', 'score' => 85]]);
452 $mutation->apply($store, [[
453 'id' => 1,
454 'name' => 'updated',
455 'score' => 95,
456 $codec->valueColumn(0) => 'updated',
457 ]]);
458 self::assertSame([['id' => 1, 'name' => 'updated', 'score' => 85]], $store->get('items'));
459 }
460
461 public function testDatabaseEvaluatedBatchDetectsConflictsWithRowsInsertedEarlierInStatement(): void
462 {
463 $store = new ShadowStore();
464 $store->set('items', []);
465 $codec = new UpsertMutationRow();
466 $mutation = new UpsertMutation(
467 'items',
468 ['id'],
469 ['quantity'],
470 ['quantity' => UpsertExpression::binary(
471 UpsertExpressionKind::Add,
472 UpsertExpression::column(UpsertColumnSource::Existing, 'quantity'),
473 UpsertExpression::column(UpsertColumnSource::Incoming, 'quantity'),
474 )],
475 databaseEvaluated: true,
476 updateSqlValues: ['quantity' => 'quantity + EXCLUDED.quantity'],
477 );
478
479 $mutation->apply($store, [
480 ['id' => 1, 'quantity' => 5, $codec->valueColumn(0) => null],
481 ['id' => 1, 'quantity' => 7, $codec->valueColumn(0) => null],
482 ]);
483
484 self::assertSame([['id' => 1, 'quantity' => 12]], $store->get('items'));
485 }
486
487 public function testDatabaseEvaluatedBatchRejectsStaleUnsupportedExpression(): void
488 {
489 $store = new ShadowStore();
490 $store->set('items', [['id' => 1, 'quantity' => 100]]);
491 $codec = new UpsertMutationRow();
492 $mutation = new UpsertMutation(
493 'items',
494 ['id'],
495 ['quantity'],
496 ['quantity' => null],
497 databaseEvaluated: true,
498 updateSqlValues: ['quantity' => 'COALESCE(quantity, 0) + EXCLUDED.quantity'],
499 );
500
501 $this->expectException(UnsupportedSqlException::class);
502 $mutation->apply($store, [
503 ['id' => 1, 'quantity' => 5, $codec->valueColumn(0) => 105],
504 ['id' => 1, 'quantity' => 7, $codec->valueColumn(0) => 107],
505 ]);
506 }
507
508 public function testConflictPredicateRestrictsIncomingAndExistingCandidateRows(): void
509 {
510 $store = new ShadowStore();
511 $store->set('users', [[
512 'email' => 'alice@example.com',
513 'status' => 'inactive',
514 'login_count' => 2,
515 ]]);
516 $mutation = new UpsertMutation(
517 'users',
518 [],
519 ['login_count'],
520 ['login_count' => UpsertExpression::binary(
521 UpsertExpressionKind::Add,
522 UpsertExpression::column(UpsertColumnSource::Existing, 'login_count'),
523 UpsertExpression::literal(1),
524 )],
525 CandidateKeySet::fromSchema([], ['users_active_email' => ['email']]),
526 conflictPredicate: UpsertExpression::binary(
527 UpsertExpressionKind::Equal,
528 UpsertExpression::column(UpsertColumnSource::Existing, 'status'),
529 UpsertExpression::literal('active'),
530 ),
531 );
532
533 $mutation->apply($store, [
534 ['email' => 'alice@example.com', 'status' => 'active', 'login_count' => 5],
535 ['email' => 'alice@example.com', 'status' => 'active', 'login_count' => 1],
536 ['email' => 'alice@example.com', 'status' => 'inactive', 'login_count' => 9],
537 ]);
538
539 self::assertSame([
540 ['email' => 'alice@example.com', 'status' => 'inactive', 'login_count' => 2],
541 ['email' => 'alice@example.com', 'status' => 'active', 'login_count' => 6],
542 ['email' => 'alice@example.com', 'status' => 'inactive', 'login_count' => 9],
543 ], $store->get('users'));
544 }
545 public function testResultRowsAnswersNothingUntilTheStatementHasBeenApplied(): void
546 {
547 self::assertSame([], (new UpsertMutation('users', ['id']))->resultRows());
548 }
549
550 public function testResultRowsAnswersEveryRowTheStatementWroteOrLeftAsItWas(): void
551 {
552 $store = new ShadowStore();
553 $store->set('users', [['id' => 1, 'name' => 'a']]);
554 $mutation = new UpsertMutation(
555 'users',
556 ['id'],
557 ['name'],
558 ['name' => UpsertExpression::literal('b')],
559 CandidateKeySet::fromSchema(['id']),
560 );
561
562 $mutation->apply($store, [['id' => 1, 'name' => 'incoming'], ['id' => 2, 'name' => 'c']]);
563
564 self::assertSame(
565 [['id' => 1, 'name' => 'b'], ['id' => 2, 'name' => 'c']],
566 $mutation->resultRows(),
567 );
568 }
569
570}
571