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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration;
6
7use Container\Endpoint;
8use Container\MySqlRelease;
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(MySqlRelease::fromEnvironment());
34        $endpoint = $container->getData(Endpoint::class);
35        try {
36            $mysqli = new mysqli($endpoint->host, $endpoint->username, $endpoint->password, $endpoint->database, $endpoint->port);
37            $mysqli->set_charset('utf8mb4');
38
39            $mysqli->query('CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50)) ENGINE=InnoDB');
40            $mysqli->query('CREATE TABLE employees (id INT PRIMARY KEY, department_id INT, '
41                . 'CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments (id) '
42                . 'ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB');
43            $mysqli->query('CREATE TABLE tasks (id INT PRIMARY KEY, employee_id INT, '
44                . 'CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES employees (id) '
45                . 'ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB');
46            $ztdMysqli = ZtdMysqli::fromMysqli($mysqli, null);
47
48            self::assertNotFalse($ztdMysqli->query("INSERT INTO departments VALUES (1, 'Engineering')"));
49            self::assertNotFalse($ztdMysqli->query('INSERT INTO employees VALUES (10, 1)'));
50            self::assertNotFalse($ztdMysqli->query('INSERT INTO tasks VALUES (100, 10)'));
51
52            try {
53                $ztdMysqli->query('INSERT INTO employees VALUES (11, 999)');
54                self::fail('Expected foreign-key validation to reject the orphan row.');
55            } catch (ZtdMysqliException $exception) {
56                self::assertStringContainsString("Foreign key constraint 'fk_department' violated", $exception->getMessage());
57            }
58            $employees = $ztdMysqli->query('SELECT id, department_id FROM employees ORDER BY id');
59            self::assertInstanceOf(mysqli_result::class, $employees);
60            self::assertSame([['id' => 10, 'department_id' => 1]], $employees->fetch_all(MYSQLI_ASSOC));
61
62            self::assertNotFalse($ztdMysqli->query('UPDATE departments SET id = 2 WHERE id = 1'));
63            self::assertNotFalse($ztdMysqli->query('UPDATE employees SET id = 20 WHERE id = 10'));
64            $task = $ztdMysqli->query('SELECT employee_id FROM tasks WHERE id = 100');
65            self::assertInstanceOf(mysqli_result::class, $task);
66            self::assertSame([['employee_id' => 20]], $task->fetch_all(MYSQLI_ASSOC));
67
68            self::assertNotFalse($ztdMysqli->query('DELETE FROM departments WHERE id = 2'));
69            $employees = $ztdMysqli->query('SELECT id FROM employees');
70            $tasks = $ztdMysqli->query('SELECT id FROM tasks');
71            self::assertInstanceOf(mysqli_result::class, $employees);
72            self::assertInstanceOf(mysqli_result::class, $tasks);
73            self::assertSame([], $employees->fetch_all(MYSQLI_ASSOC));
74            self::assertSame([], $tasks->fetch_all(MYSQLI_ASSOC));
75
76            $physical = $mysqli->query('SELECT '
77                . '(SELECT COUNT(*) FROM departments) + '
78                . '(SELECT COUNT(*) FROM employees) + '
79                . '(SELECT COUNT(*) FROM tasks) AS row_count');
80            self::assertInstanceOf(mysqli_result::class, $physical);
81            self::assertSame([['row_count' => '0']], $physical->fetch_all(MYSQLI_ASSOC));
82        } finally {
83            $container->stop();
84        }
85    }
86}
87