packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/ViewTest.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 ViewTest extends TestCase
22{
23    public function testViewsReadShadowWritesAcrossFiltersJoinsAggregatesAndPreparation(): 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 accounts (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER, active BOOLEAN)');
41            $pdo->exec('CREATE TABLE regions (code TEXT PRIMARY KEY, label TEXT)');
42            $pdo->exec('CREATE VIEW active_accounts AS SELECT id, region, amount FROM accounts WHERE active');
43            $pdo->exec('CREATE VIEW account_labels AS SELECT a.id, r.label, a.amount FROM active_accounts a JOIN regions r ON r.code = a.region');
44            $pdo->exec('CREATE VIEW region_totals AS SELECT region, COUNT(*) AS account_count, SUM(amount) AS total_amount FROM active_accounts GROUP BY region');
45            $ztdPdo = ZtdPdo::fromPdo($pdo);
46
47            $ztdPdo->exec("INSERT INTO accounts VALUES (1, 'north', 100, TRUE), (2, 'south', 200, FALSE), (3, 'north', 300, TRUE)");
48            $ztdPdo->exec("INSERT INTO regions VALUES ('north', 'North'), ('south', 'South')");
49
50            $simple = $ztdPdo->query('SELECT id FROM active_accounts ORDER BY id');
51            self::assertNotFalse($simple);
52            self::assertSame([1, 3], $simple->fetchAll(PDO::FETCH_COLUMN));
53
54            $prepared = $ztdPdo->prepare('SELECT id FROM active_accounts WHERE amount >= ? ORDER BY id');
55            self::assertNotFalse($prepared);
56            self::assertTrue($prepared->execute([150]));
57            self::assertSame([3], $prepared->fetchAll(PDO::FETCH_COLUMN));
58
59            $joined = $ztdPdo->query('SELECT id, label FROM account_labels ORDER BY id');
60            self::assertNotFalse($joined);
61            self::assertSame(
62                [['id' => 1, 'label' => 'North'], ['id' => 3, 'label' => 'North']],
63                $joined->fetchAll(),
64            );
65
66            $aggregate = $ztdPdo->query('SELECT region, account_count::integer AS account_count, total_amount::integer AS total_amount FROM region_totals');
67            self::assertNotFalse($aggregate);
68            self::assertSame(
69                [['region' => 'north', 'account_count' => 2, 'total_amount' => 400]],
70                $aggregate->fetchAll(),
71            );
72        } finally {
73            $pdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
74        }
75    }
76}
77