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\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 SelectWindowTest extends TestCase
25{
26 public function testRowNumber(): 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, category TEXT NOT NULL, amount INTEGER NOT NULL)");
45 $rawPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
46
47 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
48
49 $ztdPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
50
51 $sql = "SELECT id, category, amount, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM {$table} 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 testRankPartitionBy(): 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, category TEXT NOT NULL, amount INTEGER NOT NULL)");
88 $rawPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
89
90 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
91
92 $ztdPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
93
94 $sql = "SELECT id, category, amount, RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rnk FROM {$table} 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 public function testSumOver(): void
113 {
114 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
115 /** @var PDO $rawPdo */
116 $rawPdo = new PDO(
117 $endpoint->dsn(),
118 $endpoint->username,
119 $endpoint->password,
120 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
121 );
122
123 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
124 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
125 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
126
127 $table = 'prefix_' . bin2hex(random_bytes(8));
128
129 try {
130 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)");
131 $rawPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
132
133 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
134
135 $ztdPdo->exec("INSERT INTO {$table} (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
136
137 $sql = "SELECT id, category, amount, SUM(amount) OVER (PARTITION BY category) AS cat_total FROM {$table} ORDER BY id";
138
139 $stmt = $rawPdo->query($sql);
140 self::assertNotFalse($stmt);
141 /** @var list<Row> */
142 $rawRows = $stmt->fetchAll();
143
144 $stmt = $ztdPdo->query($sql);
145 self::assertNotFalse($stmt);
146 /** @var list<Row> */
147 $ztdRows = $stmt->fetchAll();
148
149 self::assertSame($rawRows, $ztdRows);
150 } finally {
151 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
152 }
153 }
154}
155