packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/ForeignKeyCascadeTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\PostgreSql16Container;
8use PDO;
9use PHPUnit\Framework\Attributes\CoversNothing;
10use PHPUnit\Framework\Attributes\Large;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Adapter\Pdo\ZtdPdo;
13use ZtdQuery\Adapter\Pdo\ZtdPdoException;
14
15/**
16 * @requires extension pdo_pgsql
17 * @group integration
18 * @group postgres
19 */
20#[CoversNothing]
21#[Large]
22final class ForeignKeyCascadeTest extends TestCase
23{
24    public function testForeignKeysValidateAndCascadeUpdatesAndDeletes(): void
25    {
26        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
27        /** @var PDO $pdo */
28        $pdo = new PDO(
29            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
30            'test',
31            'test',
32            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33        );
34
35        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
36        $pdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
37        $pdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
38
39
40        try {
41            $pdo->exec('CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT)');
42            $pdo->exec('CREATE TABLE employees (id INTEGER PRIMARY KEY, department_id INTEGER, '
43                . 'CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id) '
44                . 'ON DELETE CASCADE ON UPDATE CASCADE)');
45            $pdo->exec('CREATE TABLE tasks (id INTEGER PRIMARY KEY, employee_id INTEGER, '
46                . 'CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees (id) '
47                . 'ON DELETE CASCADE ON UPDATE CASCADE)');
48            $ztdPdo = ZtdPdo::fromPdo($pdo);
49
50            self::assertSame(1, $ztdPdo->exec("INSERT INTO departments VALUES (1, 'Engineering')"));
51            self::assertSame(1, $ztdPdo->exec('INSERT INTO employees VALUES (10, 1)'));
52            self::assertSame(1, $ztdPdo->exec('INSERT INTO tasks VALUES (100, 10)'));
53
54            try {
55                $ztdPdo->exec('INSERT INTO employees VALUES (11, 999)');
56                self::fail('Expected foreign-key validation to reject the orphan row.');
57            } catch (ZtdPdoException $exception) {
58                self::assertStringContainsString("Foreign key constraint 'fk_department' violated", $exception->getMessage());
59            }
60            $employees = $ztdPdo->query('SELECT id, department_id FROM employees ORDER BY id');
61            self::assertNotFalse($employees);
62            self::assertSame([['id' => 10, 'department_id' => 1]], $employees->fetchAll());
63
64            self::assertSame(1, $ztdPdo->exec('UPDATE departments SET id = 2 WHERE id = 1'));
65            self::assertSame(1, $ztdPdo->exec('UPDATE employees SET id = 20 WHERE id = 10'));
66            $task = $ztdPdo->query('SELECT employee_id FROM tasks WHERE id = 100');
67            self::assertNotFalse($task);
68            self::assertSame(20, $task->fetchColumn());
69
70            self::assertSame(1, $ztdPdo->exec('DELETE FROM departments WHERE id = 2'));
71            $orphans = $ztdPdo->query('SELECT employees.id FROM employees LEFT JOIN departments '
72                . 'ON employees.department_id = departments.id WHERE departments.id IS NULL');
73            self::assertNotFalse($orphans);
74            self::assertSame([], $orphans->fetchAll());
75            $tasks = $ztdPdo->query('SELECT id FROM tasks');
76            self::assertNotFalse($tasks);
77            self::assertSame([], $tasks->fetchAll());
78
79            $physical = $pdo->query('SELECT (SELECT COUNT(*) FROM departments) '
80                . '+ (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM tasks)');
81            self::assertNotFalse($physical);
82            self::assertSame(0, (int) $physical->fetchColumn());
83        } finally {
84            $pdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
85        }
86    }
87}
88