packages/ztd-query-core/tests/Unit/Shadow/ReferentialIntegrityEnforcerTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Unit\Shadow;
6
7use PHPUnit\Framework\Attributes\CoversClass;
8use PHPUnit\Framework\Attributes\UsesClass;
9use PHPUnit\Framework\TestCase;
10use ZtdQuery\Exception\ForeignKeyViolationException;
11use ZtdQuery\Schema\Key\CandidateKeySet;
12use ZtdQuery\Schema\Key\ForeignKeyDefinition;
13use ZtdQuery\Schema\Key\ReferentialAction;
14use ZtdQuery\Schema\TableDefinition;
15use ZtdQuery\Schema\TableDefinitionRegistry;
16use ZtdQuery\Shadow\CascadedChildren;
17use ZtdQuery\Shadow\FollowedConstraint;
18use ZtdQuery\Shadow\Mutation\MutationRowIdentity;
19use ZtdQuery\Shadow\Mutation\Row\DeleteMutation;
20use ZtdQuery\Shadow\Mutation\Row\InsertMutation;
21use ZtdQuery\Shadow\Mutation\Row\UpdateMutation;
22use ZtdQuery\Shadow\Mutation\ShadowMutation;
23use ZtdQuery\Shadow\Mutation\Table\CreateTableMutation;
24use ZtdQuery\Shadow\Mutation\Table\MultiTruncateMutation;
25use ZtdQuery\Shadow\Mutation\Table\SynchronizeMutation;
26use ZtdQuery\Shadow\ReferentialIntegrityEnforcer;
27use ZtdQuery\Shadow\Row\RowPairing;
28use ZtdQuery\Shadow\ShadowStore;
29
30#[CoversClass(ReferentialIntegrityEnforcer::class)]
31#[UsesClass(ForeignKeyViolationException::class)]
32#[UsesClass(CandidateKeySet::class)]
33#[UsesClass(ForeignKeyDefinition::class)]
34#[UsesClass(TableDefinition::class)]
35#[UsesClass(TableDefinitionRegistry::class)]
36#[UsesClass(DeleteMutation::class)]
37#[UsesClass(CreateTableMutation::class)]
38#[UsesClass(InsertMutation::class)]
39#[UsesClass(MultiTruncateMutation::class)]
40#[UsesClass(SynchronizeMutation::class)]
41#[UsesClass(UpdateMutation::class)]
42#[UsesClass(MutationRowIdentity::class)]
43#[UsesClass(ShadowStore::class)]
44#[UsesClass(\ZtdQuery\Shadow\ForeignKeyCascade::class)]
45#[UsesClass(\ZtdQuery\Shadow\ForeignKeyEnds::class)]
46#[UsesClass(\ZtdQuery\Shadow\ForeignKeyIntegrity::class)]
47#[UsesClass(\ZtdQuery\Shadow\Mutation\ConflictSearch::class)]
48#[UsesClass(\ZtdQuery\Shadow\Mutation\RowConstraints::class)]
49#[UsesClass(\ZtdQuery\Shadow\ParentKeyLookup::class)]
50#[UsesClass(\ZtdQuery\Shadow\Row\RowChange::class)]
51#[UsesClass(\ZtdQuery\Shadow\Row\RowMatch::class)]
52#[UsesClass(\ZtdQuery\Shadow\Row\RowMultiset::class)]
53#[UsesClass(\ZtdQuery\Shadow\Row\TableTransition::class)]
54#[UsesClass(\ZtdQuery\Shadow\TableTransitions::class)]
55#[UsesClass(CascadedChildren::class)]
56#[UsesClass(FollowedConstraint::class)]
57#[UsesClass(RowPairing::class)]
58#[UsesClass(\ZtdQuery\Schema\RowSet::class)]
59final class ReferentialIntegrityEnforcerTest extends TestCase
60{
61 public function testSchemaMutationSkipsRowIntegrityChecks(): void
62 {
63 $registry = new TableDefinitionRegistry();
64 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
65 $registry->register('children', new TableDefinition(
66 ['id', 'parent_id'],
67 ['id' => 'INT', 'parent_id' => 'INT'],
68 ['id'],
69 ['id'],
70 [],
71 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(['parent_id'], 'parents', ['id'])],
72 ));
73 $store = new ShadowStore();
74 $store->set('parents', []);
75 $store->set('children', [['id' => 1, 'parent_id' => 99]]);
76 $mutation = new class () implements ShadowMutation {
77 public function apply(ShadowStore $store, array $rows): void
78 {
79 }
80
81 public function tableName(): string
82 {
83 return 'children';
84 }
85 };
86
87 (new ReferentialIntegrityEnforcer($registry))->synchronize(
88 $store->snapshot(),
89 $store,
90 $mutation,
91 [],
92 'CREATE TABLE',
93 );
94
95 self::assertSame([['id' => 1, 'parent_id' => 99]], $store->get('children'));
96 }
97
98 public function testSynchronizeCascadesDeletedRows(): void
99 {
100 $registry = new TableDefinitionRegistry();
101 $parent = new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []);
102 $registry->register('parents', $parent);
103 $registry->register('children', new TableDefinition(
104 ['id', 'parent_id'],
105 ['id' => 'INT', 'parent_id' => 'INT'],
106 ['id'],
107 ['id'],
108 [],
109 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
110 ['parent_id'],
111 'parents',
112 ['id'],
113 onDelete: ReferentialAction::Cascade,
114 )],
115 ));
116 $store = new ShadowStore();
117 $store->set('parents', [['id' => 1]]);
118 $store->set('children', [['id' => 10, 'parent_id' => 1]]);
119 $before = $store->snapshot();
120 $mutation = new SynchronizeMutation('parents', $parent);
121 $mutation->apply($store, []);
122
123 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, [], 'MERGE');
124
125 self::assertSame([], $store->get('children'));
126 }
127
128 public function testRejectsInsertWhoseParentDoesNotExist(): void
129 {
130 $registry = new TableDefinitionRegistry();
131 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
132 $registry->register('children', new TableDefinition(
133 ['id', 'parent_id'],
134 ['id' => 'INT', 'parent_id' => 'INT'],
135 ['id'],
136 ['id'],
137 [],
138 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(['parent_id'], 'parents', ['id'])],
139 ));
140 $store = new ShadowStore();
141 $store->set('parents', []);
142 $store->set('children', []);
143 $before = $store->snapshot();
144 $mutation = new InsertMutation('children');
145 $mutation->apply($store, [['id' => 1, 'parent_id' => 99]]);
146
147 $this->expectException(ForeignKeyViolationException::class);
148 (new ReferentialIntegrityEnforcer($registry))->synchronize(
149 $before,
150 $store,
151 $mutation,
152 [['id' => 1, 'parent_id' => 99]],
153 'INSERT',
154 );
155 }
156
157 public function testAcceptsExplicitReferenceToNonPrimaryCandidateKey(): void
158 {
159 $registry = new TableDefinitionRegistry();
160 $registry->register('parents', new TableDefinition(
161 ['id', 'code'],
162 ['id' => 'INT', 'code' => 'TEXT'],
163 ['id'],
164 ['id', 'code'],
165 ['parents_code_key' => ['code']],
166 ));
167 $registry->register('children', new TableDefinition(
168 ['id', 'parent_code'],
169 ['id' => 'INT', 'parent_code' => 'TEXT'],
170 ['id'],
171 ['id'],
172 [],
173 foreignKeys: ['fk_parent_code' => new ForeignKeyDefinition(
174 ['parent_code'],
175 'parents',
176 ['code'],
177 )],
178 ));
179 $store = new ShadowStore();
180 $store->set('parents', [['id' => 1, 'code' => 'parent-a']]);
181 $store->set('children', []);
182 $before = $store->snapshot();
183 $mutation = new InsertMutation('children');
184 $mutation->apply($store, [['id' => 10, 'parent_code' => 'parent-a']]);
185
186 (new ReferentialIntegrityEnforcer($registry))->synchronize(
187 $before,
188 $store,
189 $mutation,
190 [['id' => 10, 'parent_code' => 'parent-a']],
191 'INSERT',
192 );
193
194 self::assertSame([['id' => 10, 'parent_code' => 'parent-a']], $store->get('children'));
195 }
196
197 public function testDeleteCascadePropagatesAcrossMultipleLevels(): void
198 {
199 $registry = new TableDefinitionRegistry();
200 $registry->register('departments', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
201 $registry->register('employees', new TableDefinition(
202 ['id', 'department_id'],
203 ['id' => 'INT', 'department_id' => 'INT'],
204 ['id'],
205 ['id'],
206 [],
207 foreignKeys: ['fk_department' => new ForeignKeyDefinition(
208 ['department_id'],
209 'departments',
210 ['id'],
211 ReferentialAction::Cascade,
212 )],
213 ));
214 $registry->register('tasks', new TableDefinition(
215 ['id', 'employee_id'],
216 ['id' => 'INT', 'employee_id' => 'INT'],
217 ['id'],
218 ['id'],
219 [],
220 foreignKeys: ['fk_employee' => new ForeignKeyDefinition(
221 ['employee_id'],
222 'employees',
223 ['id'],
224 ReferentialAction::Cascade,
225 )],
226 ));
227 $store = new ShadowStore();
228 $store->set('departments', [['id' => 1]]);
229 $store->set('employees', [['id' => 10, 'department_id' => 1]]);
230 $store->set('tasks', [['id' => 100, 'employee_id' => 10]]);
231 $before = $store->snapshot();
232 $mutation = new DeleteMutation('departments', ['id']);
233 $mutation->apply($store, [['id' => 1]]);
234
235 (new ReferentialIntegrityEnforcer($registry))->synchronize(
236 $before,
237 $store,
238 $mutation,
239 [['id' => 1]],
240 'DELETE',
241 );
242
243 self::assertSame([], $store->get('departments'));
244 self::assertSame([], $store->get('employees'));
245 self::assertSame([], $store->get('tasks'));
246 }
247
248 public function testPrimaryKeyUpdateCascadesThroughOriginalRowIdentity(): void
249 {
250 $registry = new TableDefinitionRegistry();
251 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
252 $registry->register('children', new TableDefinition(
253 ['id', 'parent_id'],
254 ['id' => 'INT', 'parent_id' => 'INT'],
255 ['id'],
256 ['id'],
257 [],
258 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
259 ['parent_id'],
260 'parents',
261 ['id'],
262 onUpdate: ReferentialAction::Cascade,
263 )],
264 ));
265 $store = new ShadowStore();
266 $store->set('parents', [['id' => 1]]);
267 $store->set('children', [['id' => 10, 'parent_id' => 1]]);
268 $before = $store->snapshot();
269 $mutation = new UpdateMutation('parents', ['id']);
270 $resultRows = [['id' => 2, '__ztd_original_id' => 1]];
271 $mutation->apply($store, $resultRows);
272
273 (new ReferentialIntegrityEnforcer($registry))->synchronize(
274 $before,
275 $store,
276 $mutation,
277 $resultRows,
278 'UPDATE',
279 );
280
281 self::assertSame([['id' => 2]], $store->get('parents'));
282 self::assertSame([['id' => 10, 'parent_id' => 2]], $store->get('children'));
283 }
284
285 public function testDeleteSetNullUpdatesChild(): void
286 {
287 $registry = new TableDefinitionRegistry();
288 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
289 $registry->register('children', new TableDefinition(
290 ['id', 'parent_id'],
291 ['id' => 'INT', 'parent_id' => 'INT'],
292 ['id'],
293 ['id'],
294 [],
295 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
296 ['parent_id'],
297 'parents',
298 ['id'],
299 ReferentialAction::SetNull,
300 )],
301 ));
302 $store = new ShadowStore();
303 $store->set('parents', [['id' => 1]]);
304 $store->set('children', [['id' => 10, 'parent_id' => 1]]);
305 $before = $store->snapshot();
306 $mutation = new DeleteMutation('parents', ['id']);
307 $mutation->apply($store, [['id' => 1]]);
308
309 (new ReferentialIntegrityEnforcer($registry))->synchronize(
310 $before,
311 $store,
312 $mutation,
313 [['id' => 1]],
314 'DELETE',
315 );
316
317 self::assertSame([['id' => 10, 'parent_id' => null]], $store->get('children'));
318 }
319
320 public function testDeleteRestrictRejectsParentRemoval(): void
321 {
322 $registry = new TableDefinitionRegistry();
323 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
324 $registry->register('children', new TableDefinition(
325 ['id', 'parent_id'],
326 ['id' => 'INT', 'parent_id' => 'INT'],
327 ['id'],
328 ['id'],
329 [],
330 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
331 ['parent_id'],
332 'parents',
333 ['id'],
334 ReferentialAction::Restrict,
335 )],
336 ));
337 $store = new ShadowStore();
338 $store->set('parents', [['id' => 2]]);
339 $store->set('children', [['id' => 20, 'parent_id' => 2]]);
340 $before = $store->snapshot();
341 $mutation = new DeleteMutation('parents', ['id']);
342 $mutation->apply($store, [['id' => 2]]);
343
344 $this->expectException(ForeignKeyViolationException::class);
345 (new ReferentialIntegrityEnforcer($registry))->synchronize(
346 $before,
347 $store,
348 $mutation,
349 [['id' => 2]],
350 'DELETE',
351 );
352 }
353
354 public function testSchemaMutationDoesNotTriggerRowValidation(): void
355 {
356 $registry = new TableDefinitionRegistry();
357 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
358 $registry->register('children', new TableDefinition(
359 ['id', 'parent_id'],
360 ['id' => 'INT', 'parent_id' => 'INT'],
361 ['id'],
362 ['id'],
363 [],
364 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(['parent_id'], 'parents', ['id'])],
365 ));
366 $store = new ShadowStore();
367 $store->set('parents', []);
368 $store->set('children', [['id' => 1, 'parent_id' => 99]]);
369
370 (new ReferentialIntegrityEnforcer($registry))->synchronize(
371 $store->snapshot(),
372 $store,
373 new CreateTableMutation('archive', null, $registry, 'fixture statement'),
374 [],
375 'CREATE TABLE archive',
376 );
377
378 self::assertSame([['id' => 1, 'parent_id' => 99]], $store->get('children'));
379 }
380
381 public function testMultiTruncateCascadesFromEveryChangedParentTable(): void
382 {
383 $registry = new TableDefinitionRegistry();
384 $registry->register('parent_a', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
385 $registry->register('parent_b', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
386 $registry->register('child_a', new TableDefinition(
387 ['id', 'parent_id'],
388 ['id' => 'INT', 'parent_id' => 'INT'],
389 ['id'],
390 ['id'],
391 [],
392 foreignKeys: ['fk_a' => new ForeignKeyDefinition(
393 ['parent_id'],
394 'parent_a',
395 ['id'],
396 ReferentialAction::Cascade,
397 )],
398 ));
399 $registry->register('child_b', new TableDefinition(
400 ['id', 'parent_id'],
401 ['id' => 'INT', 'parent_id' => 'INT'],
402 ['id'],
403 ['id'],
404 [],
405 foreignKeys: ['fk_b' => new ForeignKeyDefinition(
406 ['parent_id'],
407 'parent_b',
408 ['id'],
409 ReferentialAction::Cascade,
410 )],
411 ));
412 $store = new ShadowStore();
413 $store->set('parent_a', [['id' => 1]]);
414 $store->set('parent_b', [['id' => 2]]);
415 $store->set('child_a', [['id' => 10, 'parent_id' => 1]]);
416 $store->set('child_b', [['id' => 20, 'parent_id' => 2]]);
417 $before = $store->snapshot();
418 $mutation = new MultiTruncateMutation(['parent_a', 'parent_b']);
419 $mutation->apply($store, []);
420
421 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, [], 'TRUNCATE');
422
423 self::assertSame([], $store->get('parent_a'));
424 self::assertSame([], $store->get('parent_b'));
425 self::assertSame([], $store->get('child_a'));
426 self::assertSame([], $store->get('child_b'));
427 }
428
429 public function testMatchingConstraintAfterUnrelatedConstraintStillCascades(): void
430 {
431 $registry = new TableDefinitionRegistry();
432 $registry->register('parent_a', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
433 $registry->register('parent_b', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
434 $registry->register('children', new TableDefinition(
435 ['id', 'a_id', 'b_id'],
436 ['id' => 'INT', 'a_id' => 'INT', 'b_id' => 'INT'],
437 ['id'],
438 ['id'],
439 [],
440 foreignKeys: [
441 'fk_a' => new ForeignKeyDefinition(['a_id'], 'parent_a', ['id']),
442 'fk_b' => new ForeignKeyDefinition(['b_id'], 'parent_b', ['id'], ReferentialAction::Cascade),
443 ],
444 ));
445 $store = new ShadowStore();
446 $store->set('parent_a', [['id' => 1]]);
447 $store->set('parent_b', [['id' => 2]]);
448 $store->set('children', [['id' => 10, 'a_id' => 1, 'b_id' => 2]]);
449 $before = $store->snapshot();
450 $mutation = new DeleteMutation('parent_b', ['id']);
451 $mutation->apply($store, [['id' => 2]]);
452
453 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, [], 'DELETE');
454
455 self::assertSame([], $store->get('children'));
456 }
457
458 public function testCompositePrimaryKeyUpdateCascadesOnlyMatchingChildren(): void
459 {
460 $registry = new TableDefinitionRegistry();
461 $registry->register('parents', new TableDefinition(
462 ['tenant_id', 'id'],
463 ['tenant_id' => 'INT', 'id' => 'INT'],
464 ['tenant_id', 'id'],
465 ['tenant_id', 'id'],
466 [],
467 ));
468 $registry->register('children', new TableDefinition(
469 ['id', 'tenant_id', 'parent_id'],
470 ['id' => 'INT', 'tenant_id' => 'INT', 'parent_id' => 'INT'],
471 ['id'],
472 ['id'],
473 [],
474 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
475 ['tenant_id', 'parent_id'],
476 'parents',
477 ['tenant_id', 'id'],
478 onUpdate: ReferentialAction::Cascade,
479 )],
480 ));
481 $store = new ShadowStore();
482 $store->set('parents', [['tenant_id' => 7, 'id' => 1], ['tenant_id' => 7, 'id' => 2]]);
483 $store->set('children', [
484 ['id' => 10, 'tenant_id' => 7, 'parent_id' => 1],
485 ['id' => 20, 'tenant_id' => 7, 'parent_id' => 2],
486 ]);
487 $before = $store->snapshot();
488 $mutation = new UpdateMutation('parents', ['tenant_id', 'id']);
489 $resultRows = [[
490 'tenant_id' => 8,
491 'id' => 3,
492 '__ztd_original_tenant_id' => 7,
493 '__ztd_original_id' => 1,
494 ]];
495 $mutation->apply($store, $resultRows);
496
497 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, $resultRows, 'UPDATE');
498
499 self::assertSame([['tenant_id' => 8, 'id' => 3], ['tenant_id' => 7, 'id' => 2]], $store->get('parents'));
500 self::assertSame([
501 ['id' => 10, 'tenant_id' => 8, 'parent_id' => 3],
502 ['id' => 20, 'tenant_id' => 7, 'parent_id' => 2],
503 ], $store->get('children'));
504 }
505
506 public function testUpdateDoesNotCascadeWhileOldParentKeyStillExists(): void
507 {
508 $registry = new TableDefinitionRegistry();
509 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
510 $registry->register('children', new TableDefinition(
511 ['id', 'parent_id'],
512 ['id' => 'INT', 'parent_id' => 'INT'],
513 ['id'],
514 ['id'],
515 [],
516 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
517 ['parent_id'],
518 'parents',
519 ['id'],
520 onUpdate: ReferentialAction::Cascade,
521 )],
522 ));
523 $before = new ShadowStore();
524 $before->set('parents', [['id' => 1, 'name' => 'updated'], ['id' => 1, 'name' => 'retained']]);
525 $before->set('children', [['id' => 10, 'parent_id' => 1]]);
526 $after = $before->snapshot();
527 $after->set('parents', [['id' => 2, 'name' => 'updated'], ['id' => 1, 'name' => 'retained']]);
528
529 (new ReferentialIntegrityEnforcer($registry))->synchronize(
530 $before,
531 $after,
532 new UpdateMutation('parents', ['id']),
533 [['id' => 2, 'name' => 'updated', '__ztd_original_id' => 1]],
534 'UPDATE',
535 );
536
537 self::assertSame([['id' => 10, 'parent_id' => 1]], $after->get('children'));
538 }
539
540 public function testExplicitReferencedColumnsAreNotReplacedByPrimaryKey(): void
541 {
542 $registry = new TableDefinitionRegistry();
543 $registry->register('parents', new TableDefinition(
544 ['id', 'code'],
545 ['id' => 'TEXT', 'code' => 'TEXT'],
546 ['id'],
547 ['id', 'code'],
548 ['uq_code' => ['code']],
549 ));
550 $registry->register('children', new TableDefinition(
551 ['id', 'parent_code'],
552 ['id' => 'INT', 'parent_code' => 'TEXT'],
553 ['id'],
554 ['id'],
555 [],
556 foreignKeys: ['fk_code' => new ForeignKeyDefinition(['parent_code'], 'parents', ['code'])],
557 ));
558 $store = new ShadowStore();
559 $store->set('parents', [['id' => 'wrong-key', 'code' => 'expected-code']]);
560 $store->set('children', []);
561 $before = $store->snapshot();
562 $mutation = new InsertMutation('children');
563 $mutation->apply($store, [['id' => 1, 'parent_code' => 'expected-code']]);
564
565 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, [], 'INSERT');
566
567 self::assertSame([['id' => 1, 'parent_code' => 'expected-code']], $store->get('children'));
568 }
569
570 public function testValidationContinuesPastMissingAndNullForeignKeys(): void
571 {
572 $registry = new TableDefinitionRegistry();
573 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
574 $registry->register('children', new TableDefinition(
575 ['id', 'parent_id'],
576 ['id' => 'INT', 'parent_id' => 'INT'],
577 ['id'],
578 ['id'],
579 [],
580 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(['parent_id'], 'parents', ['id'])],
581 ));
582 $store = new ShadowStore();
583 $store->set('parents', [['id' => 1]]);
584 $store->set('children', []);
585 $before = $store->snapshot();
586 $rows = [['id' => 10], ['id' => 20, 'parent_id' => null], ['id' => 30, 'parent_id' => 99]];
587 $mutation = new InsertMutation('children');
588 $mutation->apply($store, $rows);
589
590 $this->expectException(ForeignKeyViolationException::class);
591 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, $rows, 'INSERT');
592 }
593
594 public function testCompositeDeleteSetNullNormalizesEveryForeignKeyColumn(): void
595 {
596 $registry = new TableDefinitionRegistry();
597 $registry->register('parents', new TableDefinition(
598 ['tenant_id', 'id'],
599 ['tenant_id' => 'INT', 'id' => 'INT'],
600 ['tenant_id', 'id'],
601 ['tenant_id', 'id'],
602 [],
603 ));
604 $registry->register('children', new TableDefinition(
605 ['id', 'tenant_id', 'parent_id'],
606 ['id' => 'INT', 'tenant_id' => 'INT', 'parent_id' => 'INT'],
607 ['id'],
608 ['id'],
609 [],
610 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
611 ['tenant_id', 'parent_id'],
612 'parents',
613 ['tenant_id', 'id'],
614 ReferentialAction::SetNull,
615 )],
616 ));
617 $store = new ShadowStore();
618 $store->set('parents', [['tenant_id' => 7, 'id' => 1], ['tenant_id' => 7, 'id' => 2]]);
619 $store->set('children', [
620 ['id' => 10, 'tenant_id' => 7, 'parent_id' => 1],
621 ['id' => 20, 'tenant_id' => 7, 'parent_id' => 1],
622 ['id' => 30, 'tenant_id' => 7, 'parent_id' => 2],
623 ]);
624 $before = $store->snapshot();
625 $mutation = new DeleteMutation('parents', ['tenant_id', 'id']);
626 $mutation->apply($store, [['tenant_id' => 7, 'id' => 1]]);
627
628 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, [], 'DELETE');
629
630 self::assertSame([
631 ['id' => 10, 'tenant_id' => null, 'parent_id' => null],
632 ['id' => 20, 'tenant_id' => null, 'parent_id' => null],
633 ['id' => 30, 'tenant_id' => 7, 'parent_id' => 2],
634 ], $store->get('children'));
635 }
636
637 public function testDeleteCascadeRemovesAllMatchingChildrenAndKeepsOthers(): void
638 {
639 $registry = new TableDefinitionRegistry();
640 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
641 $registry->register('children', new TableDefinition(
642 ['id', 'parent_id'],
643 ['id' => 'INT', 'parent_id' => 'INT'],
644 ['id'],
645 ['id'],
646 [],
647 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
648 ['parent_id'],
649 'parents',
650 ['id'],
651 ReferentialAction::Cascade,
652 )],
653 ));
654 $store = new ShadowStore();
655 $store->set('parents', [['id' => 1], ['id' => 2]]);
656 $store->set('children', [
657 ['id' => 10, 'parent_id' => 1],
658 ['id' => 20, 'parent_id' => 1],
659 ['id' => 30, 'parent_id' => 2],
660 ]);
661 $before = $store->snapshot();
662 $mutation = new DeleteMutation('parents', ['id']);
663 $mutation->apply($store, [['id' => 1]]);
664
665 (new ReferentialIntegrityEnforcer($registry))->synchronize($before, $store, $mutation, [], 'DELETE');
666
667 self::assertSame([['id' => 30, 'parent_id' => 2]], $store->get('children'));
668 }
669
670 public function testFallbackReferencedPrimaryKeyAppearsInUpdateViolation(): void
671 {
672 $registry = new TableDefinitionRegistry();
673 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
674 $registry->register('children', new TableDefinition(
675 ['id', 'parent_id'],
676 ['id' => 'INT', 'parent_id' => 'INT'],
677 ['id'],
678 ['id'],
679 [],
680 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(['parent_id'], 'parents', [])],
681 ));
682 $before = new ShadowStore();
683 $before->set('parents', [['id' => 1]]);
684 $before->set('children', [['id' => 10, 'parent_id' => 1]]);
685 $after = $before->snapshot();
686 $after->set('parents', [['id' => 2]]);
687
688 $this->expectException(ForeignKeyViolationException::class);
689 $this->expectExceptionMessage("referenced row not found in 'parents.id'");
690 (new ReferentialIntegrityEnforcer($registry))->synchronize(
691 $before,
692 $after,
693 new UpdateMutation('parents', ['id']),
694 [['id' => 2, '__ztd_original_id' => 1]],
695 'UPDATE',
696 );
697 }
698
699 public function testFallbackTransitionPropagatesUpdateAndDeleteFromOneTable(): void
700 {
701 $registry = new TableDefinitionRegistry();
702 $registry->register('parents', new TableDefinition(
703 ['id', 'code'],
704 ['id' => 'INT', 'code' => 'TEXT'],
705 ['id'],
706 ['id', 'code'],
707 ['uq_code' => ['code']],
708 ));
709 $registry->register('children', new TableDefinition(
710 ['id', 'parent_code'],
711 ['id' => 'INT', 'parent_code' => 'TEXT'],
712 ['id'],
713 ['id'],
714 [],
715 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
716 ['parent_code'],
717 'parents',
718 ['code'],
719 ReferentialAction::Cascade,
720 ReferentialAction::Cascade,
721 )],
722 ));
723 $before = new ShadowStore();
724 $before->set('parents', [
725 ['id' => 2, 'code' => 'b'],
726 ['id' => 1, 'code' => 'a'],
727 ['id' => 3, 'code' => 'c'],
728 ]);
729 $before->set('children', [
730 ['id' => 30, 'parent_code' => 'c'],
731 ['id' => 10, 'parent_code' => 'a'],
732 ['id' => 20, 'parent_code' => 'b'],
733 ]);
734 $after = $before->snapshot();
735 $after->set('parents', [
736 ['id' => 1, 'code' => 'aa'],
737 ['id' => 3, 'code' => 'c'],
738 ]);
739
740 (new ReferentialIntegrityEnforcer($registry))->synchronize(
741 $before,
742 $after,
743 new InsertMutation('unrelated'),
744 [],
745 'CHANGE',
746 );
747
748 self::assertSame([
749 ['id' => 30, 'parent_code' => 'c'],
750 ['id' => 10, 'parent_code' => 'aa'],
751 ], $after->get('children'));
752 }
753
754 public function testIdentityTransitionSkipsIncompleteChangeAndProcessesFollowingChange(): void
755 {
756 $registry = new TableDefinitionRegistry();
757 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
758 $registry->register('children', new TableDefinition(
759 ['id', 'parent_id'],
760 ['id' => 'INT', 'parent_id' => 'INT'],
761 ['id'],
762 ['id'],
763 [],
764 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
765 ['parent_id'],
766 'parents',
767 ['id'],
768 onUpdate: ReferentialAction::Cascade,
769 )],
770 ));
771 $before = new ShadowStore();
772 $before->set('parents', [['id' => 1], ['id' => 2]]);
773 $before->set('children', [['id' => 10, 'parent_id' => 1], ['id' => 20, 'parent_id' => 2]]);
774 $after = $before->snapshot();
775 $after->set('parents', [['id' => 1], ['id' => 20]]);
776 $resultRows = [
777 ['id' => 99, '__ztd_original_id' => 1],
778 ['id' => 20, '__ztd_original_id' => 2],
779 ];
780
781 (new ReferentialIntegrityEnforcer($registry))->synchronize(
782 $before,
783 $after,
784 new UpdateMutation('parents', ['id']),
785 $resultRows,
786 'UPDATE',
787 );
788
789 self::assertSame([
790 ['id' => 10, 'parent_id' => 1],
791 ['id' => 20, 'parent_id' => 20],
792 ], $after->get('children'));
793 }
794
795 public function testRetainedOldKeyDoesNotStopFollowingUpdateCascade(): void
796 {
797 $registry = new TableDefinitionRegistry();
798 $registry->register('parents', new TableDefinition(
799 ['id', 'code'],
800 ['id' => 'INT', 'code' => 'TEXT'],
801 ['id'],
802 ['id', 'code'],
803 [],
804 ));
805 $registry->register('children', new TableDefinition(
806 ['id', 'parent_code'],
807 ['id' => 'INT', 'parent_code' => 'TEXT'],
808 ['id'],
809 ['id'],
810 [],
811 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
812 ['parent_code'],
813 'parents',
814 ['code'],
815 onUpdate: ReferentialAction::Cascade,
816 )],
817 ));
818 $before = new ShadowStore();
819 $before->set('parents', [
820 ['id' => 1, 'code' => 'a'],
821 ['id' => 2, 'code' => 'a'],
822 ['id' => 3, 'code' => 'b'],
823 ]);
824 $before->set('children', [
825 ['id' => 10, 'parent_code' => 'a'],
826 ['id' => 20, 'parent_code' => 'b'],
827 ]);
828 $after = $before->snapshot();
829 $after->set('parents', [
830 ['id' => 1, 'code' => 'c'],
831 ['id' => 2, 'code' => 'a'],
832 ['id' => 3, 'code' => 'd'],
833 ]);
834 $resultRows = [
835 ['id' => 1, 'code' => 'c', '__ztd_original_id' => 1],
836 ['id' => 3, 'code' => 'd', '__ztd_original_id' => 3],
837 ];
838
839 (new ReferentialIntegrityEnforcer($registry))->synchronize(
840 $before,
841 $after,
842 new UpdateMutation('parents', ['id']),
843 $resultRows,
844 'UPDATE',
845 );
846
847 self::assertSame([
848 ['id' => 10, 'parent_code' => 'a'],
849 ['id' => 20, 'parent_code' => 'd'],
850 ], $after->get('children'));
851 }
852
853 public function testMissingReferencedValuesDoNotStopLaterUpdateOrDeleteCascades(): void
854 {
855 $registry = new TableDefinitionRegistry();
856 $registry->register('parents', new TableDefinition(
857 ['id', 'code'],
858 ['id' => 'INT', 'code' => 'TEXT'],
859 ['id'],
860 ['id'],
861 [],
862 ));
863 $registry->register('children', new TableDefinition(
864 ['id', 'parent_code'],
865 ['id' => 'INT', 'parent_code' => 'TEXT'],
866 ['id'],
867 ['id'],
868 [],
869 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
870 ['parent_code'],
871 'parents',
872 ['code'],
873 ReferentialAction::Cascade,
874 ReferentialAction::Cascade,
875 )],
876 ));
877 $beforeUpdate = new ShadowStore();
878 $beforeUpdate->set('parents', [['id' => 1], ['id' => 2, 'code' => 'b']]);
879 $beforeUpdate->set('children', [['id' => 20, 'parent_code' => 'b']]);
880 $afterUpdate = $beforeUpdate->snapshot();
881 $afterUpdate->set('parents', [['id' => 1, 'code' => 'x'], ['id' => 2, 'code' => 'c']]);
882
883 (new ReferentialIntegrityEnforcer($registry))->synchronize(
884 $beforeUpdate,
885 $afterUpdate,
886 new InsertMutation('unrelated'),
887 [],
888 'UPDATE',
889 );
890
891 self::assertSame([['id' => 20, 'parent_code' => 'c']], $afterUpdate->get('children'));
892
893 $beforeDelete = new ShadowStore();
894 $beforeDelete->set('parents', [['id' => 1], ['id' => 2, 'code' => 'b']]);
895 $beforeDelete->set('children', [['id' => 20, 'parent_code' => 'b']]);
896 $afterDelete = $beforeDelete->snapshot();
897 $afterDelete->set('parents', []);
898
899 (new ReferentialIntegrityEnforcer($registry))->synchronize(
900 $beforeDelete,
901 $afterDelete,
902 new InsertMutation('unrelated'),
903 [],
904 'DELETE',
905 );
906
907 self::assertSame([], $afterDelete->get('children'));
908 }
909
910 public function testUpdateCascadeCarriesOriginalRowIntoNextLevel(): void
911 {
912 $registry = new TableDefinitionRegistry();
913 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
914 $registry->register('children', new TableDefinition(
915 ['id', 'parent_id'],
916 ['id' => 'INT', 'parent_id' => 'INT'],
917 ['id'],
918 ['id'],
919 [],
920 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
921 ['parent_id'],
922 'parents',
923 ['id'],
924 onUpdate: ReferentialAction::Cascade,
925 )],
926 ));
927 $registry->register('grandchildren', new TableDefinition(
928 ['id', 'child_parent_id'],
929 ['id' => 'INT', 'child_parent_id' => 'INT'],
930 ['id'],
931 ['id'],
932 [],
933 foreignKeys: ['fk_child' => new ForeignKeyDefinition(
934 ['child_parent_id'],
935 'children',
936 ['parent_id'],
937 onUpdate: ReferentialAction::Cascade,
938 )],
939 ));
940 $before = new ShadowStore();
941 $before->set('parents', [['id' => 1]]);
942 $before->set('children', [['id' => 10, 'parent_id' => 1]]);
943 $before->set('grandchildren', [['id' => 100, 'child_parent_id' => 1]]);
944 $after = $before->snapshot();
945 $after->set('parents', [['id' => 2]]);
946
947 (new ReferentialIntegrityEnforcer($registry))->synchronize(
948 $before,
949 $after,
950 new UpdateMutation('parents', ['id']),
951 [['id' => 2, '__ztd_original_id' => 1]],
952 'UPDATE',
953 );
954
955 self::assertSame([['id' => 10, 'parent_id' => 2]], $after->get('children'));
956 self::assertSame([['id' => 100, 'child_parent_id' => 2]], $after->get('grandchildren'));
957 }
958
959 public function testDeleteSetNullCarriesOriginalRowIntoNextLevel(): void
960 {
961 $registry = new TableDefinitionRegistry();
962 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
963 $registry->register('children', new TableDefinition(
964 ['id', 'parent_id'],
965 ['id' => 'INT', 'parent_id' => 'INT'],
966 ['id'],
967 ['id'],
968 [],
969 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
970 ['parent_id'],
971 'parents',
972 ['id'],
973 ReferentialAction::SetNull,
974 )],
975 ));
976 $registry->register('grandchildren', new TableDefinition(
977 ['id', 'child_parent_id'],
978 ['id' => 'INT', 'child_parent_id' => 'INT'],
979 ['id'],
980 ['id'],
981 [],
982 foreignKeys: ['fk_child' => new ForeignKeyDefinition(
983 ['child_parent_id'],
984 'children',
985 ['parent_id'],
986 onUpdate: ReferentialAction::SetNull,
987 )],
988 ));
989 $store = new ShadowStore();
990 $store->set('parents', [['id' => 1]]);
991 $store->set('children', [['id' => 10, 'parent_id' => 1]]);
992 $store->set('grandchildren', [['id' => 100, 'child_parent_id' => 1]]);
993 $before = $store->snapshot();
994 $store->set('parents', []);
995
996 (new ReferentialIntegrityEnforcer($registry))->synchronize(
997 $before,
998 $store,
999 new DeleteMutation('parents', ['id']),
1000 [['id' => 1]],
1001 'DELETE',
1002 );
1003
1004 self::assertSame([['id' => 10, 'parent_id' => null]], $store->get('children'));
1005 self::assertSame([['id' => 100, 'child_parent_id' => null]], $store->get('grandchildren'));
1006 }
1007
1008 public function testEmptySelfReferentialCascadeTerminatesWithoutSyntheticEvents(): void
1009 {
1010 $registry = new TableDefinitionRegistry();
1011 $registry->register('nodes', new TableDefinition(
1012 ['id', 'parent_id'],
1013 ['id' => 'INT', 'parent_id' => 'INT'],
1014 ['id'],
1015 ['id'],
1016 [],
1017 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
1018 ['parent_id'],
1019 'nodes',
1020 ['id'],
1021 ReferentialAction::Cascade,
1022 )],
1023 ));
1024 $before = new ShadowStore();
1025 $before->set('nodes', [['id' => 1, 'parent_id' => null], ['id' => 2, 'parent_id' => null]]);
1026 $after = $before->snapshot();
1027 $after->set('nodes', [['id' => 1, 'parent_id' => null]]);
1028
1029 (new ReferentialIntegrityEnforcer($registry))->synchronize(
1030 $before,
1031 $after,
1032 new DeleteMutation('nodes', ['id']),
1033 [['id' => 2, 'parent_id' => null]],
1034 'DELETE',
1035 );
1036
1037 self::assertSame([['id' => 1, 'parent_id' => null]], $after->get('nodes'));
1038 }
1039
1040 public function testCascadeNormalizesSparseChildRowIndexes(): void
1041 {
1042 $registry = new TableDefinitionRegistry();
1043 $registry->register('parents', new TableDefinition(['id'], ['id' => 'INT'], ['id'], ['id'], []));
1044 $registry->register('children', new TableDefinition(
1045 ['id', 'parent_id'],
1046 ['id' => 'INT', 'parent_id' => 'INT'],
1047 ['id'],
1048 ['id'],
1049 [],
1050 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
1051 ['parent_id'],
1052 'parents',
1053 ['id'],
1054 onUpdate: ReferentialAction::Cascade,
1055 )],
1056 ));
1057 $before = new ShadowStore();
1058 $before->set('parents', [['id' => 1]]);
1059 $before->set('children', [4 => ['id' => 10, 'parent_id' => 1]]);
1060 $after = $before->snapshot();
1061 $after->set('parents', [['id' => 2]]);
1062
1063 (new ReferentialIntegrityEnforcer($registry))->synchronize(
1064 $before,
1065 $after,
1066 new UpdateMutation('parents', ['id']),
1067 [['id' => 2, '__ztd_original_id' => 1]],
1068 'UPDATE',
1069 );
1070
1071 self::assertSame([0], array_keys($after->get('children')));
1072 self::assertSame([['id' => 10, 'parent_id' => 2]], $after->get('children'));
1073 }
1074
1075 public function testRowsWithMissingPrimaryKeyCannotMatchRowsThatHaveTheKey(): void
1076 {
1077 $registry = new TableDefinitionRegistry();
1078 $registry->register('parents', new TableDefinition(
1079 ['id', 'code'],
1080 ['id' => 'INT', 'code' => 'TEXT'],
1081 ['id'],
1082 ['id'],
1083 [],
1084 ));
1085 $before = new ShadowStore();
1086 $before->set('parents', [['code' => 'a']]);
1087 $after = new ShadowStore();
1088 $after->set('parents', [['id' => 1, 'code' => 'a']]);
1089
1090 (new ReferentialIntegrityEnforcer($registry))->synchronize(
1091 $before,
1092 $after,
1093 new InsertMutation('unrelated'),
1094 [],
1095 'CHANGE',
1096 );
1097
1098 self::assertSame([['id' => 1, 'code' => 'a']], $after->get('parents'));
1099 }
1100
1101 public function testFallbackContinuesAfterIdentityMatchedRow(): void
1102 {
1103 $registry = new TableDefinitionRegistry();
1104 $registry->register('parents', new TableDefinition(
1105 ['id', 'code'],
1106 ['id' => 'INT', 'code' => 'TEXT'],
1107 ['id'],
1108 ['id', 'code'],
1109 [],
1110 ));
1111 $registry->register('children', new TableDefinition(
1112 ['id', 'parent_code'],
1113 ['id' => 'INT', 'parent_code' => 'TEXT'],
1114 ['id'],
1115 ['id'],
1116 [],
1117 foreignKeys: ['fk_parent' => new ForeignKeyDefinition(
1118 ['parent_code'],
1119 'parents',
1120 ['code'],
1121 onUpdate: ReferentialAction::Cascade,
1122 )],
1123 ));
1124 $before = new ShadowStore();
1125 $before->set('parents', [['id' => 1, 'code' => 'a'], ['id' => 2, 'code' => 'b']]);
1126 $before->set('children', [['id' => 10, 'parent_code' => 'a'], ['id' => 20, 'parent_code' => 'b']]);
1127 $after = $before->snapshot();
1128 $after->set('parents', [['id' => 1, 'code' => 'aa'], ['id' => 2, 'code' => 'bb']]);
1129
1130 (new ReferentialIntegrityEnforcer($registry))->synchronize(
1131 $before,
1132 $after,
1133 new UpdateMutation('parents', ['id']),
1134 [['id' => 1, 'code' => 'aa', '__ztd_original_id' => 1]],
1135 'UPDATE',
1136 );
1137
1138 self::assertSame([
1139 ['id' => 10, 'parent_code' => 'aa'],
1140 ['id' => 20, 'parent_code' => 'bb'],
1141 ], $after->get('children'));
1142 }
1143}
1144