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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\MySql;
6
7use Container\MySql80Container;
8use Container\MySql84Container;
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_mysql
18 * @group integration
19 * @group mysql
20 */
21#[CoversNothing]
22#[Large]
23final class ForeignKeyCascadeTest extends TestCase
24{
25    public function testForeignKeysValidateAndCascadeUpdatesAndDeletes(): void
26    {
27        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
28        /** @var PDO $pdo */
29        $pdo = new PDO(
30            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
31            'root',
32            'root',
33            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
34        );
35
36        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
37        $pdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
38        $pdo->exec(sprintf('USE `%s`', $databaseName));
39
40
41        try {
42            $pdo->exec('CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB');
43            $pdo->exec('CREATE TABLE employees (id INT PRIMARY KEY, department_id INT, '
44                . 'CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id) '
45                . 'ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB');
46            $pdo->exec('CREATE TABLE tasks (id INT PRIMARY KEY, employee_id INT, '
47                . 'CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees (id) '
48                . 'ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB');
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 DATABASE IF EXISTS `%s`', $databaseName));
86        }
87    }
88}
89