packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectWindowTest.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 SelectWindowTest extends TestCase
24{
25 public function testRowNumber(): 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 id, category, amount, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM {$table} ORDER BY id";
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 testRankPartitionBy(): 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 id, category, amount, RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rnk FROM {$table} ORDER BY id";
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 public function testSumOver(): void
112 {
113 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
114 /** @var PDO $rawPdo */
115 $rawPdo = new PDO(
116 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
117 'test',
118 'test',
119 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
120 );
121
122 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
123 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
124 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
125
126 $table = 'prefix_' . bin2hex(random_bytes(8));
127
128 try {
129 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)");
130 $rawPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
131
132 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
133
134 $ztdPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
135
136 $sql = "SELECT id, category, amount, SUM(amount) OVER (PARTITION BY category) AS cat_total FROM {$table} ORDER BY id";
137
138 $stmt = $rawPdo->query($sql);
139 self::assertNotFalse($stmt);
140 /** @var list<Row> */
141 $rawRows = $stmt->fetchAll();
142
143 $stmt = $ztdPdo->query($sql);
144 self::assertNotFalse($stmt);
145 /** @var list<Row> */
146 $ztdRows = $stmt->fetchAll();
147
148 self::assertSame($rawRows, $ztdRows);
149 } finally {
150 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
151 }
152 }
153}
154