packages/ztd-query-pdo-adapter/tests/Integration/MySql/UpdateDerivedSourceTest.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 UpdateDerivedSourceTest extends TestCase
18{
19    public function testUpdateJoinPreservesDerivedAggregateSource(): 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        $products = 'prefix_' . bin2hex(random_bytes(8));
35        $summary = 'prefix_' . bin2hex(random_bytes(8));
36
37        try {
38            $rawPdo->exec("CREATE TABLE `{$products}` (id INT PRIMARY KEY, category VARCHAR(50), price DECIMAL(10,2))");
39            $rawPdo->exec("CREATE TABLE `{$summary}` (id INT PRIMARY KEY, category VARCHAR(50), min_price DECIMAL(10,2), item_count INT)");
40            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
41            $ztdPdo->exec("INSERT INTO `{$products}` VALUES (1, 'electronics', 99.99), (2, 'electronics', 199.99), (3, 'books', 20.00)");
42            $ztdPdo->exec("INSERT INTO `{$summary}` VALUES (1, 'electronics', NULL, NULL), (2, 'books', NULL, NULL)");
43
44            self::assertSame(2, $ztdPdo->exec("UPDATE `{$summary}` AS s JOIN (SELECT category, COUNT(*) AS cnt, MIN(price) AS mn FROM `{$products}` GROUP BY category) AS p ON s.category = p.category SET s.min_price = p.mn, s.item_count = p.cnt"));
45
46            $statement = $ztdPdo->query("SELECT id, category, min_price, item_count FROM `{$summary}` ORDER BY id");
47            self::assertNotFalse($statement);
48            self::assertSame([
49                ['id' => 1, 'category' => 'electronics', 'min_price' => '99.99', 'item_count' => 2],
50                ['id' => 2, 'category' => 'books', 'min_price' => '20.00', 'item_count' => 1],
51            ], $statement->fetchAll(PDO::FETCH_ASSOC));
52
53            $physical = $rawPdo->query("SELECT * FROM `{$summary}`");
54            self::assertNotFalse($physical);
55            self::assertSame([], $physical->fetchAll(PDO::FETCH_ASSOC));
56        } finally {
57            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
58        }
59    }
60
61    public function testUpdateJoinPreservesWindowedDerivedSource(): void
62    {
63        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
64        /** @var PDO $rawPdo */
65        $rawPdo = new PDO(
66            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
67            'root',
68            'root',
69            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
70        );
71
72        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
73        $rawPdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
74        $rawPdo->exec(sprintf('USE `%s`', $databaseName));
75
76        $scores = 'prefix_' . bin2hex(random_bytes(8));
77        $rankings = 'prefix_' . bin2hex(random_bytes(8));
78
79        try {
80            $rawPdo->exec("CREATE TABLE `{$scores}` (id INT PRIMARY KEY, player VARCHAR(30), score INT)");
81            $rawPdo->exec("CREATE TABLE `{$rankings}` (id INT PRIMARY KEY, player VARCHAR(30), rank_pos INT)");
82            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
83            $ztdPdo->exec("INSERT INTO `{$scores}` VALUES (1, 'Alice', 100), (2, 'Alice', 150), (3, 'Bob', 200)");
84            $ztdPdo->exec("INSERT INTO `{$rankings}` VALUES (1, 'Alice', NULL), (2, 'Bob', NULL)");
85
86            self::assertSame(2, $ztdPdo->exec("UPDATE `{$rankings}` AS r JOIN (SELECT player, DENSE_RANK() OVER (ORDER BY MAX(score) DESC) AS ranking FROM `{$scores}` GROUP BY player) AS s ON r.player = s.player SET r.rank_pos = s.ranking"));
87
88            $statement = $ztdPdo->query("SELECT player, rank_pos FROM `{$rankings}` ORDER BY player");
89            self::assertNotFalse($statement);
90            self::assertSame([
91                ['player' => 'Alice', 'rank_pos' => 2],
92                ['player' => 'Bob', 'rank_pos' => 1],
93            ], $statement->fetchAll(PDO::FETCH_ASSOC));
94
95            $physical = $rawPdo->query("SELECT * FROM `{$rankings}`");
96            self::assertNotFalse($physical);
97            self::assertSame([], $physical->fetchAll(PDO::FETCH_ASSOC));
98        } finally {
99            $rawPdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
100        }
101    }
102}
103