packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectGroupByTest.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 * @phpstan-type Row array<string, mixed>
20 */
21#[CoversNothing]
22#[Large]
23final class SelectGroupByTest extends TestCase
24{
25    public function testGroupByWithCount(): void
26    {
27        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
28        /** @var PDO $rawPdo */
29        $rawPdo = new PDO(
30            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
31            'test',
32            'test',
33            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
34        );
35
36        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
37        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
38        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
39
40        $table = 'prefix_' . bin2hex(random_bytes(8));
41
42        try {
43            $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)");
44            $rawPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
45
46            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
47
48            $ztdPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
49
50            $sql = "SELECT category, COUNT(*) AS cnt FROM {$table} GROUP BY category ORDER BY category";
51
52            $stmt = $rawPdo->query($sql);
53            self::assertNotFalse($stmt);
54            /** @var list<Row> */
55            $rawRows = $stmt->fetchAll();
56
57            $stmt = $ztdPdo->query($sql);
58            self::assertNotFalse($stmt);
59            /** @var list<Row> */
60            $ztdRows = $stmt->fetchAll();
61
62            self::assertSame($rawRows, $ztdRows);
63        } finally {
64            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
65        }
66    }
67
68    public function testGroupByWithSum(): void
69    {
70        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
71        /** @var PDO $rawPdo */
72        $rawPdo = new PDO(
73            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
74            'test',
75            'test',
76            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
77        );
78
79        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
80        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
81        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
82
83        $table = 'prefix_' . bin2hex(random_bytes(8));
84
85        try {
86            $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)");
87            $rawPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
88
89            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
90
91            $ztdPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
92
93            $sql = "SELECT category, SUM(amount) AS total FROM {$table} GROUP BY category ORDER BY category";
94
95            $stmt = $rawPdo->query($sql);
96            self::assertNotFalse($stmt);
97            /** @var list<Row> */
98            $rawRows = $stmt->fetchAll();
99
100            $stmt = $ztdPdo->query($sql);
101            self::assertNotFalse($stmt);
102            /** @var list<Row> */
103            $ztdRows = $stmt->fetchAll();
104
105            self::assertSame($rawRows, $ztdRows);
106        } finally {
107            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
108        }
109    }
110}
111