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