packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/GeneratedColumnTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\PostgreSql16Container;
8use PDO;
9use PHPUnit\Framework\Attributes\CoversNothing;
10use PHPUnit\Framework\Attributes\Large;
11use PHPUnit\Framework\TestCase;
12use ZtdQuery\Adapter\Pdo\ZtdPdo;
13
14/**
15 * @requires extension pdo_pgsql
16 * @group integration
17 * @group postgres
18 */
19#[CoversNothing]
20#[Large]
21final class GeneratedColumnTest extends TestCase
22{
23    public function testGeneratedValuesDriveReadsAggregatesUpdatesAndDeletes(): void
24    {
25        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
26        /** @var PDO $pdo */
27        $pdo = new PDO(
28            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
29            'test',
30            'test',
31            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
32        );
33
34        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
35        $pdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
36        $pdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
37
38
39        try {
40            $pdo->exec('CREATE TABLE orders (id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, qty INTEGER NOT NULL, unit_price NUMERIC(10,2) NOT NULL, total NUMERIC(12,2) GENERATED ALWAYS AS (qty * unit_price) STORED)');
41            $ztdPdo = ZtdPdo::fromPdo($pdo);
42
43            $ztdPdo->exec('INSERT INTO orders (qty, unit_price) VALUES (5, 10), (2, 7)');
44
45            $rows = $ztdPdo->query('SELECT id::integer AS id, total::integer AS total FROM orders ORDER BY id');
46            self::assertNotFalse($rows);
47            self::assertSame(
48                [['id' => 1, 'total' => 50], ['id' => 2, 'total' => 14]],
49                $rows->fetchAll(),
50            );
51            $sum = $ztdPdo->query('SELECT SUM(total)::integer FROM orders');
52            self::assertNotFalse($sum);
53            self::assertSame(64, $sum->fetchColumn());
54            $filtered = $ztdPdo->query('SELECT id::integer FROM orders WHERE total > 20');
55            self::assertNotFalse($filtered);
56            self::assertSame([1], $filtered->fetchAll(PDO::FETCH_COLUMN));
57
58            self::assertSame(1, $ztdPdo->exec('UPDATE orders SET qty = 7 WHERE id = 1'));
59            $updated = $ztdPdo->query('SELECT total::integer FROM orders WHERE id = 1');
60            self::assertNotFalse($updated);
61            self::assertSame(70, $updated->fetchColumn());
62            self::assertSame(1, $ztdPdo->exec('DELETE FROM orders WHERE total >= 70'));
63            $remaining = $ztdPdo->query('SELECT id::integer FROM orders');
64            self::assertNotFalse($remaining);
65            self::assertSame([2], $remaining->fetchAll(PDO::FETCH_COLUMN));
66        } finally {
67            $pdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
68        }
69    }
70}
71