packages/ztd-query-mysqli-adapter/tests/Integration/MysqliForeignKeyCascadeTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration;
6
7use Container\MySql80Container;
8use Container\MySql84Container;
9use mysqli;
10use mysqli_result;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use Testcontainers\Testcontainers;
14use ZtdQuery\Adapter\Mysqli\ZtdMysqli;
15use ZtdQuery\Adapter\Mysqli\ZtdMysqliException;
16
17#[\PHPUnit\Framework\Attributes\CoversClass(ZtdMysqli::class)]
18#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Session\ConnectionExecution::class)]
19#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\ZtdMysqliStatement::class)]
20#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Session\StatementExecution::class)]
21#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Driver\MysqliConnection::class)]
22#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Driver\MysqliResultStatement::class)]
23#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Driver\MysqliResultColumnExtractor::class)]
24#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Native\MysqliStatementBindingBridge::class)]
25#[\PHPUnit\Framework\Attributes\CoversClass(ZtdMysqliException::class)]
26#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Native\MysqliPropertyReader::class)]
27#[\PHPUnit\Framework\Attributes\CoversClass(\ZtdQuery\Adapter\Mysqli\Session\MysqliResultProcessor::class)]
28#[Large]
29final class MysqliForeignKeyCascadeTest extends TestCase
30{
31    public function testForeignKeysValidateAndCascadeUpdatesAndDeletes(): void
32    {
33        $container = Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
34        try {
35            $mysqli = new mysqli(str_replace('localhost', '127.0.0.1', $container->getHost()), 'root', 'root', 'test', $container->getMappedPort(3306));
36            $mysqli->set_charset('utf8mb4');
37
38            $mysqli->query('CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB');
39            $mysqli->query('CREATE TABLE employees (id INT PRIMARY KEY, department_id INT, '
40                . 'CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id) '
41                . 'ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB');
42            $mysqli->query('CREATE TABLE tasks (id INT PRIMARY KEY, employee_id INT, '
43                . 'CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees (id) '
44                . 'ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB');
45            $ztdMysqli = ZtdMysqli::fromMysqli($mysqli, null);
46
47            self::assertNotFalse($ztdMysqli->query("INSERT INTO departments VALUES (1, 'Engineering')"));
48            self::assertNotFalse($ztdMysqli->query('INSERT INTO employees VALUES (10, 1)'));
49            self::assertNotFalse($ztdMysqli->query('INSERT INTO tasks VALUES (100, 10)'));
50
51            try {
52                $ztdMysqli->query('INSERT INTO employees VALUES (11, 999)');
53                self::fail('Expected foreign-key validation to reject the orphan row.');
54            } catch (ZtdMysqliException $exception) {
55                self::assertStringContainsString("Foreign key constraint 'fk_department' violated", $exception->getMessage());
56            }
57            $employees = $ztdMysqli->query('SELECT id, department_id FROM employees ORDER BY id');
58            self::assertInstanceOf(mysqli_result::class, $employees);
59            self::assertSame([['id' => 10, 'department_id' => 1]], $employees->fetch_all(MYSQLI_ASSOC));
60
61            self::assertNotFalse($ztdMysqli->query('UPDATE departments SET id = 2 WHERE id = 1'));
62            self::assertNotFalse($ztdMysqli->query('UPDATE employees SET id = 20 WHERE id = 10'));
63            $task = $ztdMysqli->query('SELECT employee_id FROM tasks WHERE id = 100');
64            self::assertInstanceOf(mysqli_result::class, $task);
65            self::assertSame([['employee_id' => 20]], $task->fetch_all(MYSQLI_ASSOC));
66
67            self::assertNotFalse($ztdMysqli->query('DELETE FROM departments WHERE id = 2'));
68            $employees = $ztdMysqli->query('SELECT id FROM employees');
69            $tasks = $ztdMysqli->query('SELECT id FROM tasks');
70            self::assertInstanceOf(mysqli_result::class, $employees);
71            self::assertInstanceOf(mysqli_result::class, $tasks);
72            self::assertSame([], $employees->fetch_all(MYSQLI_ASSOC));
73            self::assertSame([], $tasks->fetch_all(MYSQLI_ASSOC));
74
75            $physical = $mysqli->query('SELECT '
76                . '(SELECT COUNT(*) FROM departments) + '
77                . '(SELECT COUNT(*) FROM employees) + '
78                . '(SELECT COUNT(*) FROM tasks) AS row_count');
79            self::assertInstanceOf(mysqli_result::class, $physical);
80            self::assertSame([['row_count' => '0']], $physical->fetch_all(MYSQLI_ASSOC));
81        } finally {
82            $container->stop();
83        }
84    }
85}
86