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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12use ZtdQuery\Adapter\Pdo\ZtdPdoException;
13
14/**
15 * @requires extension pdo_sqlite
16 */
17#[CoversNothing]
18#[Large]
19final class ForeignKeyCascadeTest extends TestCase
20{
21    public function testForeignKeysValidateAndCascadeUpdatesAndDeletes(): void
22    {
23        $pdo = new PDO('sqlite::memory:', null, null, [
24            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
25            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
26        ]);
27        $pdo->exec('PRAGMA foreign_keys = ON');
28        $pdo->exec('CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT)');
29        $pdo->exec('CREATE TABLE employees (id INTEGER PRIMARY KEY, department_id INTEGER, '
30            . 'CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id) '
31            . 'ON DELETE CASCADE ON UPDATE CASCADE)');
32        $pdo->exec('CREATE TABLE tasks (id INTEGER PRIMARY KEY, employee_id INTEGER, '
33            . 'CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees (id) '
34            . 'ON DELETE CASCADE ON UPDATE CASCADE)');
35        $ztdPdo = ZtdPdo::fromPdo($pdo);
36
37        self::assertSame(1, $ztdPdo->exec("INSERT INTO departments VALUES (1, 'Engineering')"));
38        self::assertSame(1, $ztdPdo->exec('INSERT INTO employees VALUES (10, 1)'));
39        self::assertSame(1, $ztdPdo->exec('INSERT INTO tasks VALUES (100, 10)'));
40
41        try {
42            $ztdPdo->exec('INSERT INTO employees VALUES (11, 999)');
43            self::fail('Expected foreign-key validation to reject the orphan row.');
44        } catch (ZtdPdoException $exception) {
45            self::assertStringContainsString("Foreign key constraint 'fk_department' violated", $exception->getMessage());
46        }
47        $employees = $ztdPdo->query('SELECT id, department_id FROM employees ORDER BY id');
48        self::assertNotFalse($employees);
49        self::assertSame([['id' => 10, 'department_id' => 1]], $employees->fetchAll());
50
51        self::assertSame(1, $ztdPdo->exec('UPDATE departments SET id = 2 WHERE id = 1'));
52        self::assertSame(1, $ztdPdo->exec('UPDATE employees SET id = 20 WHERE id = 10'));
53        $task = $ztdPdo->query('SELECT employee_id FROM tasks WHERE id = 100');
54        self::assertNotFalse($task);
55        self::assertSame(20, $task->fetchColumn());
56
57        self::assertSame(1, $ztdPdo->exec('DELETE FROM departments WHERE id = 2'));
58        $orphans = $ztdPdo->query('SELECT employees.id FROM employees LEFT JOIN departments '
59            . 'ON employees.department_id = departments.id WHERE departments.id IS NULL');
60        self::assertNotFalse($orphans);
61        self::assertSame([], $orphans->fetchAll());
62        $tasks = $ztdPdo->query('SELECT id FROM tasks');
63        self::assertNotFalse($tasks);
64        self::assertSame([], $tasks->fetchAll());
65
66        $physical = $pdo->query('SELECT (SELECT COUNT(*) FROM departments) '
67            . '+ (SELECT COUNT(*) FROM employees) + (SELECT COUNT(*) FROM tasks)');
68        self::assertNotFalse($physical);
69        self::assertSame(0, (int) $physical->fetchColumn());
70    }
71}
72