packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectUnionTest.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 SelectUnionTest extends TestCase
25{
26 public function testUnion(): 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 $t1 = 'prefix_' . bin2hex(random_bytes(8));
42 $t2 = 'prefix_' . bin2hex(random_bytes(8));
43
44 try {
45 $rawPdo->exec("CREATE TABLE {$t1} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
46 $rawPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
47
48 $rawPdo->exec("CREATE TABLE {$t2} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
49 $rawPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
50
51 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
52
53 $ztdPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
54 $ztdPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
55
56 $sql = "SELECT name FROM {$t1} UNION SELECT name FROM {$t2} ORDER BY name";
57
58 $stmt = $rawPdo->query($sql);
59 self::assertNotFalse($stmt);
60 /** @var list<Row> */
61 $rawRows = $stmt->fetchAll();
62
63 $stmt = $ztdPdo->query($sql);
64 self::assertNotFalse($stmt);
65 /** @var list<Row> */
66 $ztdRows = $stmt->fetchAll();
67
68 self::assertSame($rawRows, $ztdRows);
69 } finally {
70 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
71 }
72 }
73
74 public function testUnionAll(): void
75 {
76 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
77 /** @var PDO $rawPdo */
78 $rawPdo = new PDO(
79 $endpoint->dsn(),
80 $endpoint->username,
81 $endpoint->password,
82 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
83 );
84
85 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
86 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
87 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
88
89 $t1 = 'prefix_' . bin2hex(random_bytes(8));
90 $t2 = 'prefix_' . bin2hex(random_bytes(8));
91
92 try {
93 $rawPdo->exec("CREATE TABLE {$t1} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
94 $rawPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
95
96 $rawPdo->exec("CREATE TABLE {$t2} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
97 $rawPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
98
99 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
100
101 $ztdPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
102 $ztdPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
103
104 $sql = "SELECT name FROM {$t1} UNION ALL SELECT name FROM {$t2} ORDER BY name";
105
106 $stmt = $rawPdo->query($sql);
107 self::assertNotFalse($stmt);
108 /** @var list<Row> */
109 $rawRows = $stmt->fetchAll();
110
111 $stmt = $ztdPdo->query($sql);
112 self::assertNotFalse($stmt);
113 /** @var list<Row> */
114 $ztdRows = $stmt->fetchAll();
115
116 self::assertSame($rawRows, $ztdRows);
117 } finally {
118 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
119 }
120 }
121
122 public function testExcept(): void
123 {
124 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
125 /** @var PDO $rawPdo */
126 $rawPdo = new PDO(
127 $endpoint->dsn(),
128 $endpoint->username,
129 $endpoint->password,
130 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
131 );
132
133 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
134 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
135 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
136
137 $t1 = 'prefix_' . bin2hex(random_bytes(8));
138 $t2 = 'prefix_' . bin2hex(random_bytes(8));
139
140 try {
141 $rawPdo->exec("CREATE TABLE {$t1} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
142 $rawPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
143
144 $rawPdo->exec("CREATE TABLE {$t2} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
145 $rawPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
146
147 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
148
149 $ztdPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
150 $ztdPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
151
152 $sql = "SELECT name FROM {$t1} EXCEPT SELECT name FROM {$t2} ORDER BY name";
153
154 $stmt = $rawPdo->query($sql);
155 self::assertNotFalse($stmt);
156 /** @var list<Row> */
157 $rawRows = $stmt->fetchAll();
158
159 $stmt = $ztdPdo->query($sql);
160 self::assertNotFalse($stmt);
161 /** @var list<Row> */
162 $ztdRows = $stmt->fetchAll();
163
164 self::assertSame($rawRows, $ztdRows);
165 } finally {
166 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
167 }
168 }
169
170 public function testIntersect(): void
171 {
172 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
173 /** @var PDO $rawPdo */
174 $rawPdo = new PDO(
175 $endpoint->dsn(),
176 $endpoint->username,
177 $endpoint->password,
178 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
179 );
180
181 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
182 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
183 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
184
185 $t1 = 'prefix_' . bin2hex(random_bytes(8));
186 $t2 = 'prefix_' . bin2hex(random_bytes(8));
187
188 try {
189 $rawPdo->exec("CREATE TABLE {$t1} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
190 $rawPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
191
192 $rawPdo->exec("CREATE TABLE {$t2} (id INTEGER PRIMARY KEY, name TEXT NOT NULL)");
193 $rawPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
194
195 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
196
197 $ztdPdo->exec("INSERT INTO {$t1} (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
198 $ztdPdo->exec("INSERT INTO {$t2} (id, name) VALUES (2, 'Bob'), (3, 'Charlie')");
199
200 $sql = "SELECT name FROM {$t1} INTERSECT SELECT name FROM {$t2} ORDER BY name";
201
202 $stmt = $rawPdo->query($sql);
203 self::assertNotFalse($stmt);
204 /** @var list<Row> */
205 $rawRows = $stmt->fetchAll();
206
207 $stmt = $ztdPdo->query($sql);
208 self::assertNotFalse($stmt);
209 /** @var list<Row> */
210 $ztdRows = $stmt->fetchAll();
211
212 self::assertSame($rawRows, $ztdRows);
213 } finally {
214 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
215 }
216 }
217}
218