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