packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/SelectWhereTest.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 SelectWhereTest extends TestCase
24{
25 public function testWhereEquality(): 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, name TEXT NOT NULL, age INTEGER NOT NULL)");
44 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
45
46 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
47
48 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
49
50 $stmt = $rawPdo->query("SELECT * FROM {$table} WHERE id = 1");
51 self::assertNotFalse($stmt);
52 /** @var list<Row> */
53 $rawRows = $stmt->fetchAll();
54
55 $stmt = $ztdPdo->query("SELECT * FROM {$table} WHERE id = 1");
56 self::assertNotFalse($stmt);
57 /** @var list<Row> */
58 $ztdRows = $stmt->fetchAll();
59
60 self::assertSame($rawRows, $ztdRows);
61 } finally {
62 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
63 }
64 }
65
66 public function testWhereComparison(): void
67 {
68 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
69 /** @var PDO $rawPdo */
70 $rawPdo = new PDO(
71 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
72 'test',
73 'test',
74 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
75 );
76
77 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
78 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
79 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
80
81 $table = 'prefix_' . bin2hex(random_bytes(8));
82
83 try {
84 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
85 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
86
87 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
88
89 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
90
91 $stmt = $rawPdo->query("SELECT * FROM {$table} WHERE age > 28 ORDER BY id");
92 self::assertNotFalse($stmt);
93 /** @var list<Row> */
94 $rawRows = $stmt->fetchAll();
95
96 $stmt = $ztdPdo->query("SELECT * FROM {$table} WHERE age > 28 ORDER BY id");
97 self::assertNotFalse($stmt);
98 /** @var list<Row> */
99 $ztdRows = $stmt->fetchAll();
100
101 self::assertSame($rawRows, $ztdRows);
102 } finally {
103 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
104 }
105 }
106
107 public function testWhereAnd(): void
108 {
109 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
110 /** @var PDO $rawPdo */
111 $rawPdo = new PDO(
112 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
113 'test',
114 'test',
115 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
116 );
117
118 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
119 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
120 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
121
122 $table = 'prefix_' . bin2hex(random_bytes(8));
123
124 try {
125 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
126 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
127
128 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
129
130 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
131
132 $stmt = $rawPdo->query("SELECT * FROM {$table} WHERE age > 20 AND name = 'Bob'");
133 self::assertNotFalse($stmt);
134 /** @var list<Row> */
135 $rawRows = $stmt->fetchAll();
136
137 $stmt = $ztdPdo->query("SELECT * FROM {$table} WHERE age > 20 AND name = 'Bob'");
138 self::assertNotFalse($stmt);
139 /** @var list<Row> */
140 $ztdRows = $stmt->fetchAll();
141
142 self::assertSame($rawRows, $ztdRows);
143 } finally {
144 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
145 }
146 }
147
148 public function testWhereIn(): void
149 {
150 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
151 /** @var PDO $rawPdo */
152 $rawPdo = new PDO(
153 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
154 'test',
155 'test',
156 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
157 );
158
159 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
160 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
161 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
162
163 $table = 'prefix_' . bin2hex(random_bytes(8));
164
165 try {
166 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
167 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
168
169 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
170
171 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
172
173 $stmt = $rawPdo->query("SELECT * FROM {$table} WHERE id IN (1, 3) ORDER BY id");
174 self::assertNotFalse($stmt);
175 /** @var list<Row> */
176 $rawRows = $stmt->fetchAll();
177
178 $stmt = $ztdPdo->query("SELECT * FROM {$table} WHERE id IN (1, 3) ORDER BY id");
179 self::assertNotFalse($stmt);
180 /** @var list<Row> */
181 $ztdRows = $stmt->fetchAll();
182
183 self::assertSame($rawRows, $ztdRows);
184 } finally {
185 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
186 }
187 }
188
189 public function testWhereBetween(): void
190 {
191 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
192 /** @var PDO $rawPdo */
193 $rawPdo = new PDO(
194 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
195 'test',
196 'test',
197 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
198 );
199
200 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
201 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
202 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
203
204 $table = 'prefix_' . bin2hex(random_bytes(8));
205
206 try {
207 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
208 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
209
210 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
211
212 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
213
214 $stmt = $rawPdo->query("SELECT * FROM {$table} WHERE age BETWEEN 25 AND 32 ORDER BY id");
215 self::assertNotFalse($stmt);
216 /** @var list<Row> */
217 $rawRows = $stmt->fetchAll();
218
219 $stmt = $ztdPdo->query("SELECT * FROM {$table} WHERE age BETWEEN 25 AND 32 ORDER BY id");
220 self::assertNotFalse($stmt);
221 /** @var list<Row> */
222 $ztdRows = $stmt->fetchAll();
223
224 self::assertSame($rawRows, $ztdRows);
225 } finally {
226 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
227 }
228 }
229
230 public function testWhereLike(): void
231 {
232 $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
233 /** @var PDO $rawPdo */
234 $rawPdo = new PDO(
235 sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
236 'test',
237 'test',
238 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
239 );
240
241 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
242 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
243 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
244
245 $table = 'prefix_' . bin2hex(random_bytes(8));
246
247 try {
248 $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)");
249 $rawPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
250
251 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
252
253 $ztdPdo->exec("INSERT INTO {$table} (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
254
255 $stmt = $rawPdo->query("SELECT * FROM {$table} WHERE name LIKE 'A%'");
256 self::assertNotFalse($stmt);
257 /** @var list<Row> */
258 $rawRows = $stmt->fetchAll();
259
260 $stmt = $ztdPdo->query("SELECT * FROM {$table} WHERE name LIKE 'A%'");
261 self::assertNotFalse($stmt);
262 /** @var list<Row> */
263 $ztdRows = $stmt->fetchAll();
264
265 self::assertSame($rawRows, $ztdRows);
266 } finally {
267 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
268 }
269 }
270}
271