packages/ztd-query-pdo-adapter/tests/Integration/MySql/MultiTableMutationTest.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;
14
15#[CoversNothing]
16#[Large]
17final class MultiTableMutationTest extends TestCase
18{
19    public function testUpdatesAndDeletesEveryListedTable(): void
20    {
21        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
22        /** @var PDO $rawPdo */
23        $rawPdo = new PDO(
24            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
25            'root',
26            'root',
27            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
28        );
29
30        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
31        $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
32        $rawPdo->exec(sprintf('USE `%s`', $databaseName));
33
34        $users = 'users_' . bin2hex(random_bytes(8));
35        $orders = 'orders_' . bin2hex(random_bytes(8));
36
37        try {
38            $rawPdo->exec("CREATE TABLE `{$users}` (id INT PRIMARY KEY, name VARCHAR(50))");
39            $rawPdo->exec("CREATE TABLE `{$orders}` (order_id INT PRIMARY KEY, user_id INT, status VARCHAR(50))");
40            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
41            $ztdPdo->exec("INSERT INTO `{$users}` VALUES (1, 'Alice'), (2, 'Bob')");
42            $ztdPdo->exec("INSERT INTO `{$orders}` VALUES (10, 1, 'pending'), (20, 2, 'pending')");
43
44            $ztdPdo->exec(
45                "UPDATE `{$users}` u, `{$orders}` o SET u.name = 'Updated', o.status = 'done'"
46                . ' WHERE u.id = o.user_id AND u.id = 2',
47            );
48
49            $userRows = $ztdPdo->query("SELECT id, name FROM `{$users}` ORDER BY id");
50            $orderRows = $ztdPdo->query("SELECT order_id, user_id, status FROM `{$orders}` ORDER BY order_id");
51            self::assertNotFalse($userRows);
52            self::assertNotFalse($orderRows);
53            self::assertSame(
54                [['id' => 1, 'name' => 'Alice'], ['id' => 2, 'name' => 'Updated']],
55                $userRows->fetchAll(PDO::FETCH_ASSOC),
56            );
57            self::assertSame(
58                [
59                    ['order_id' => 10, 'user_id' => 1, 'status' => 'pending'],
60                    ['order_id' => 20, 'user_id' => 2, 'status' => 'done'],
61                ],
62                $orderRows->fetchAll(PDO::FETCH_ASSOC),
63            );
64
65            $ztdPdo->exec(
66                "DELETE u, o FROM `{$users}` u JOIN `{$orders}` o ON u.id = o.user_id WHERE u.id = 2",
67            );
68
69            $remainingUsers = $ztdPdo->query("SELECT id FROM `{$users}` ORDER BY id");
70            $remainingOrders = $ztdPdo->query("SELECT order_id FROM `{$orders}` ORDER BY order_id");
71            self::assertNotFalse($remainingUsers);
72            self::assertNotFalse($remainingOrders);
73            self::assertSame([1], $remainingUsers->fetchAll(PDO::FETCH_COLUMN));
74            self::assertSame([10], $remainingOrders->fetchAll(PDO::FETCH_COLUMN));
75
76            $physicalUsers = $rawPdo->query("SELECT * FROM `{$users}`");
77            $physicalOrders = $rawPdo->query("SELECT * FROM `{$orders}`");
78            self::assertNotFalse($physicalUsers);
79            self::assertNotFalse($physicalOrders);
80            self::assertSame([], $physicalUsers->fetchAll());
81            self::assertSame([], $physicalOrders->fetchAll());
82        } finally {
83            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
84        }
85    }
86}
87