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