packages/ztd-query-pdo-adapter/tests/Integration/MySql/GeneratedColumnTest.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/**
16 * @requires extension pdo_mysql
17 * @group integration
18 * @group mysql
19 */
20#[CoversNothing]
21#[Large]
22final class GeneratedColumnTest extends TestCase
23{
24    public function testGeneratedValuesDriveReadsAggregatesUpdatesAndDeletes(): void
25    {
26        $containerInstance = \Testcontainers\Testcontainers::run(getenv('MYSQL_VERSION') === '8.4.7' ? MySql84Container::class : MySql80Container::class);
27        /** @var PDO $pdo */
28        $pdo = new PDO(
29            sprintf('mysql:host=%s;port=%d;dbname=test;charset=utf8mb4', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(3306)),
30            'root',
31            'root',
32            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
33        );
34
35        $databaseName = 'ztd_' . bin2hex(random_bytes(8));
36        $pdo->exec(sprintf('CREATE DATABASE `%s` CHARACTER SET utf8mb4', $databaseName));
37        $pdo->exec(sprintf('USE `%s`', $databaseName));
38
39
40        try {
41            $pdo->exec('CREATE TABLE orders (id BIGINT AUTO_INCREMENT PRIMARY KEY, qty INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL, total DECIMAL(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED)');
42            $ztdPdo = ZtdPdo::fromPdo($pdo);
43
44            $ztdPdo->exec('INSERT INTO orders (qty, unit_price) VALUES (5, 10), (2, 7)');
45
46            $rows = $ztdPdo->query('SELECT CAST(id AS SIGNED) AS id, CAST(total AS SIGNED) AS total FROM orders ORDER BY id');
47            self::assertNotFalse($rows);
48            self::assertSame(
49                [['id' => 1, 'total' => 50], ['id' => 2, 'total' => 14]],
50                $rows->fetchAll(),
51            );
52            $sum = $ztdPdo->query('SELECT CAST(SUM(total) AS SIGNED) FROM orders');
53            self::assertNotFalse($sum);
54            self::assertSame(64, $sum->fetchColumn());
55            $filtered = $ztdPdo->query('SELECT CAST(id AS SIGNED) FROM orders WHERE total > 20');
56            self::assertNotFalse($filtered);
57            self::assertSame([1], $filtered->fetchAll(PDO::FETCH_COLUMN));
58
59            self::assertSame(1, $ztdPdo->exec('UPDATE orders SET qty = 7 WHERE id = 1'));
60            $updated = $ztdPdo->query('SELECT CAST(total AS SIGNED) FROM orders WHERE id = 1');
61            self::assertNotFalse($updated);
62            self::assertSame(70, $updated->fetchColumn());
63            self::assertSame(1, $ztdPdo->exec('DELETE FROM orders WHERE total >= 70'));
64            $remaining = $ztdPdo->query('SELECT CAST(id AS SIGNED) FROM orders');
65            self::assertNotFalse($remaining);
66            self::assertSame([2], $remaining->fetchAll(PDO::FETCH_COLUMN));
67        } finally {
68            $pdo->exec(sprintf('DROP DATABASE IF EXISTS `%s`', $databaseName));
69        }
70    }
71}
72