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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\Endpoint;
8use Container\PostgreSqlRelease;
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_pgsql
17 * @group integration
18 * @group postgres
19 *
20 * @phpstan-type Row array<string, mixed>
21 */
22#[CoversNothing]
23#[Large]
24final class SelectCteTest extends TestCase
25{
26    public function testSimpleCte(): void
27    {
28        $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
29        /** @var PDO $rawPdo */
30        $rawPdo = new PDO(
31            $endpoint->dsn(),
32            $endpoint->username,
33            $endpoint->password,
34            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
35        );
36
37        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
38        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
39        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
40
41        $table = 'prefix_' . bin2hex(random_bytes(8));
42
43        try {
44            $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
45            $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
46
47            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
48
49            $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
50
51            $sql = "WITH seniors AS (SELECT * FROM {$table} WHERE age >= 30) SELECT * FROM seniors ORDER BY id";
52
53            $stmt = $rawPdo->query($sql);
54            self::assertNotFalse($stmt);
55            /** @var list<Row> */
56            $rawRows = $stmt->fetchAll();
57
58            $stmt = $ztdPdo->query($sql);
59            self::assertNotFalse($stmt);
60            /** @var list<Row> */
61            $ztdRows = $stmt->fetchAll();
62
63            self::assertSame($rawRows, $ztdRows);
64        } finally {
65            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
66        }
67    }
68
69    public function testMultipleCtes(): void
70    {
71        $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
72        /** @var PDO $rawPdo */
73        $rawPdo = new PDO(
74            $endpoint->dsn(),
75            $endpoint->username,
76            $endpoint->password,
77            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
78        );
79
80        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
81        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
82        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
83
84        $table = 'prefix_' . bin2hex(random_bytes(8));
85
86        try {
87            $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
88            $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
89
90            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
91
92            $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
93
94            $sql = "WITH young AS (SELECT * FROM {$table} WHERE age < 30), old AS (SELECT * FROM {$table} WHERE age >= 30) SELECT * FROM young UNION ALL SELECT * FROM old ORDER BY id";
95
96            $stmt = $rawPdo->query($sql);
97            self::assertNotFalse($stmt);
98            /** @var list<Row> */
99            $rawRows = $stmt->fetchAll();
100
101            $stmt = $ztdPdo->query($sql);
102            self::assertNotFalse($stmt);
103            /** @var list<Row> */
104            $ztdRows = $stmt->fetchAll();
105
106            self::assertSame($rawRows, $ztdRows);
107        } finally {
108            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
109        }
110    }
111}
112