packages/ztd-query-pdo-adapter/tests/Integration/PostgreSqlCteShadowingTest.php
1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration;
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 * @phpstan-type Row array<string, mixed>
20 */
21#[CoversNothing]
22#[Large]
23final class PostgreSqlCteShadowingTest extends TestCase
24{
25 public function testSelectOnCleanShadowReturnsEmpty(): void
26 {
27 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
28 /** @var PDO $rawPdo */
29 $rawPdo = new PDO(
30 $endpoint->dsn(),
31 $endpoint->username,
32 $endpoint->password,
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(sprintf(
44 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
45 $table
46 ));
47 $rawPdo->exec(sprintf(
48 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
49 $table
50 ));
51
52 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
53
54 $stmt = $ztdPdo->query(sprintf('SELECT * FROM %s ORDER BY id', $table));
55 self::assertNotFalse($stmt);
56 /** @var list<Row> */
57 $rows = $stmt->fetchAll();
58
59 self::assertCount(0, $rows);
60 } finally {
61 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
62 }
63 }
64
65 public function testInsertDoesNotModifyPhysicalDatabase(): void
66 {
67 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
68 /** @var PDO $rawPdo */
69 $rawPdo = new PDO(
70 $endpoint->dsn(),
71 $endpoint->username,
72 $endpoint->password,
73 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
74 );
75
76 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
77 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
78 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
79
80 $table = 'prefix_' . bin2hex(random_bytes(8));
81
82 try {
83 $rawPdo->exec(sprintf(
84 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
85 $table
86 ));
87 $rawPdo->exec(sprintf(
88 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
89 $table
90 ));
91
92 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
93
94 $ztdPdo->exec(sprintf(
95 "INSERT INTO %s (name, age) VALUES ('Charlie', 35)",
96 $table
97 ));
98
99 $stmt = $rawPdo->query(sprintf('SELECT * FROM %s', $table));
100 self::assertNotFalse($stmt);
101 /** @var list<Row> */
102 $rawRows = $stmt->fetchAll();
103
104 self::assertCount(2, $rawRows);
105 } finally {
106 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
107 }
108 }
109
110 public function testInsertIsVisibleViaZtdSelect(): void
111 {
112 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
113 /** @var PDO $rawPdo */
114 $rawPdo = new PDO(
115 $endpoint->dsn(),
116 $endpoint->username,
117 $endpoint->password,
118 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
119 );
120
121 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
122 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
123 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
124
125 $table = 'prefix_' . bin2hex(random_bytes(8));
126
127 try {
128 $rawPdo->exec(sprintf(
129 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
130 $table
131 ));
132 $rawPdo->exec(sprintf(
133 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
134 $table
135 ));
136
137 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
138
139 $ztdPdo->exec(sprintf(
140 "INSERT INTO %s (name, age) VALUES ('Charlie', 35)",
141 $table
142 ));
143
144 $stmt = $ztdPdo->query(sprintf('SELECT name, age FROM %s ORDER BY name', $table));
145 self::assertNotFalse($stmt);
146 /** @var list<Row> */
147 $ztdRows = $stmt->fetchAll();
148
149 self::assertCount(1, $ztdRows);
150 self::assertSame('Charlie', $ztdRows[0]['name']);
151 } finally {
152 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
153 }
154 }
155
156 public function testMultipleInsertsAccumulate(): void
157 {
158 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
159 /** @var PDO $rawPdo */
160 $rawPdo = new PDO(
161 $endpoint->dsn(),
162 $endpoint->username,
163 $endpoint->password,
164 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
165 );
166
167 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
168 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
169 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
170
171 $table = 'prefix_' . bin2hex(random_bytes(8));
172
173 try {
174 $rawPdo->exec(sprintf(
175 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
176 $table
177 ));
178 $rawPdo->exec(sprintf(
179 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
180 $table
181 ));
182
183 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
184
185 $ztdPdo->exec(sprintf(
186 "INSERT INTO %s (name, age) VALUES ('Charlie', 35)",
187 $table
188 ));
189 $ztdPdo->exec(sprintf(
190 "INSERT INTO %s (name, age) VALUES ('Diana', 28)",
191 $table
192 ));
193
194 $stmt = $ztdPdo->query(sprintf('SELECT name FROM %s ORDER BY name', $table));
195 self::assertNotFalse($stmt);
196 /** @var list<Row> */
197 $ztdRows = $stmt->fetchAll();
198
199 self::assertCount(2, $ztdRows);
200 $names = array_column($ztdRows, 'name');
201 self::assertContains('Charlie', $names);
202 self::assertContains('Diana', $names);
203 } finally {
204 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
205 }
206 }
207
208 public function testPhysicalDatabaseRemainsUnchangedAfterMutations(): void
209 {
210 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
211 /** @var PDO $rawPdo */
212 $rawPdo = new PDO(
213 $endpoint->dsn(),
214 $endpoint->username,
215 $endpoint->password,
216 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
217 );
218
219 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
220 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
221 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
222
223 $table = 'prefix_' . bin2hex(random_bytes(8));
224
225 try {
226 $rawPdo->exec(sprintf(
227 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
228 $table
229 ));
230 $rawPdo->exec(sprintf(
231 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
232 $table
233 ));
234
235 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
236
237 $ztdPdo->exec(sprintf(
238 "INSERT INTO %s (name, age) VALUES ('Charlie', 35)",
239 $table
240 ));
241 $ztdPdo->exec(sprintf(
242 "INSERT INTO %s (name, age) VALUES ('Diana', 28)",
243 $table
244 ));
245
246 $stmt = $rawPdo->query(sprintf('SELECT * FROM %s ORDER BY id', $table));
247 self::assertNotFalse($stmt);
248 /** @var list<Row> */
249 $rawRows = $stmt->fetchAll();
250
251 self::assertCount(2, $rawRows);
252 self::assertSame('Alice', $rawRows[0]['name']);
253 self::assertSame('Bob', $rawRows[1]['name']);
254 } finally {
255 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
256 }
257 }
258
259 public function testDisableZtdBypassesRewriting(): void
260 {
261 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
262 /** @var PDO $rawPdo */
263 $rawPdo = new PDO(
264 $endpoint->dsn(),
265 $endpoint->username,
266 $endpoint->password,
267 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
268 );
269
270 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
271 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
272 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
273
274 $table = 'prefix_' . bin2hex(random_bytes(8));
275
276 try {
277 $rawPdo->exec(sprintf(
278 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
279 $table
280 ));
281 $rawPdo->exec(sprintf(
282 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
283 $table
284 ));
285
286 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
287
288 $ztdPdo->disableZtd();
289
290 $ztdPdo->exec(sprintf(
291 "INSERT INTO %s (name, age) VALUES ('Direct', 40)",
292 $table
293 ));
294
295 $stmt = $rawPdo->query(sprintf('SELECT * FROM %s', $table));
296 self::assertNotFalse($stmt);
297 /** @var list<Row> */
298 $rawRows = $stmt->fetchAll();
299
300 self::assertCount(3, $rawRows);
301
302 $rawPdo->exec(sprintf("DELETE FROM %s WHERE name = 'Direct'", $table));
303 } finally {
304 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
305 }
306 }
307
308 public function testEnableDisableToggle(): void
309 {
310 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
311 /** @var PDO $rawPdo */
312 $rawPdo = new PDO(
313 $endpoint->dsn(),
314 $endpoint->username,
315 $endpoint->password,
316 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
317 );
318
319 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
320 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
321 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
322
323 $table = 'prefix_' . bin2hex(random_bytes(8));
324
325 try {
326 $rawPdo->exec(sprintf(
327 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
328 $table
329 ));
330 $rawPdo->exec(sprintf(
331 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
332 $table
333 ));
334
335 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
336
337 self::assertTrue($ztdPdo->isZtdEnabled());
338
339 $ztdPdo->disableZtd();
340 self::assertFalse($ztdPdo->isZtdEnabled());
341
342 $ztdPdo->enableZtd();
343 self::assertTrue($ztdPdo->isZtdEnabled());
344 } finally {
345 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
346 }
347 }
348
349 public function testSelectWithWhereOnShadowData(): void
350 {
351 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
352 /** @var PDO $rawPdo */
353 $rawPdo = new PDO(
354 $endpoint->dsn(),
355 $endpoint->username,
356 $endpoint->password,
357 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
358 );
359
360 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
361 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
362 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
363
364 $table = 'prefix_' . bin2hex(random_bytes(8));
365
366 try {
367 $rawPdo->exec(sprintf(
368 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
369 $table
370 ));
371 $rawPdo->exec(sprintf(
372 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
373 $table
374 ));
375
376 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
377
378 $ztdPdo->exec(sprintf(
379 "INSERT INTO %s (name, age) VALUES ('Charlie', 35)",
380 $table
381 ));
382 $ztdPdo->exec(sprintf(
383 "INSERT INTO %s (name, age) VALUES ('Diana', 28)",
384 $table
385 ));
386
387 $stmt = $ztdPdo->query(sprintf('SELECT name FROM %s WHERE age > 30 ORDER BY name', $table));
388 self::assertNotFalse($stmt);
389 /** @var list<Row> */
390 $rows = $stmt->fetchAll();
391
392 $names = array_column($rows, 'name');
393 self::assertSame(['Charlie'], $names, 'WHERE age > 30 must return only Charlie(35) from shadow data');
394 } finally {
395 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
396 }
397 }
398
399 public function testUpdateDoesNotModifyPhysicalDatabase(): void
400 {
401 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
402 /** @var PDO $rawPdo */
403 $rawPdo = new PDO(
404 $endpoint->dsn(),
405 $endpoint->username,
406 $endpoint->password,
407 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
408 );
409
410 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
411 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
412 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
413
414 $table = 'prefix_' . bin2hex(random_bytes(8));
415
416 try {
417 $rawPdo->exec(sprintf(
418 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
419 $table
420 ));
421 $rawPdo->exec(sprintf(
422 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
423 $table
424 ));
425
426 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
427
428 $ztdPdo->exec(sprintf(
429 "UPDATE %s SET age = 99 WHERE name = 'Alice'",
430 $table
431 ));
432
433 $stmt = $rawPdo->query(sprintf("SELECT name, age FROM %s WHERE name = 'Alice'", $table));
434 self::assertNotFalse($stmt);
435 /** @var list<Row> */
436 $rawRows = $stmt->fetchAll();
437
438 self::assertCount(1, $rawRows);
439 self::assertSame('Alice', $rawRows[0]['name']);
440 self::assertSame(30, $rawRows[0]['age']);
441 } finally {
442 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
443 }
444 }
445
446 public function testDeleteDoesNotModifyPhysicalDatabase(): void
447 {
448 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
449 /** @var PDO $rawPdo */
450 $rawPdo = new PDO(
451 $endpoint->dsn(),
452 $endpoint->username,
453 $endpoint->password,
454 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
455 );
456
457 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
458 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
459 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
460
461 $table = 'prefix_' . bin2hex(random_bytes(8));
462
463 try {
464 $rawPdo->exec(sprintf(
465 'CREATE TABLE %s (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)',
466 $table
467 ));
468 $rawPdo->exec(sprintf(
469 "INSERT INTO %s (name, age) VALUES ('Alice', 30), ('Bob', 25)",
470 $table
471 ));
472
473 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
474
475 $ztdPdo->exec(sprintf(
476 "DELETE FROM %s WHERE name = 'Alice'",
477 $table
478 ));
479
480 $stmt = $rawPdo->query(sprintf('SELECT name FROM %s ORDER BY name', $table));
481 self::assertNotFalse($stmt);
482 /** @var list<Row> */
483 $rawRows = $stmt->fetchAll();
484
485 $names = array_column($rawRows, 'name');
486 self::assertSame(['Alice', 'Bob'], $names, 'Physical database must be unchanged after ZTD DELETE');
487 } finally {
488 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
489 }
490 }
491
492 public function testCommentsRemainLexicalWhitespaceAcrossPostgreSqlMutations(): void
493 {
494 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
495 /** @var PDO $rawPdo */
496 $rawPdo = new PDO(
497 $endpoint->dsn(),
498 $endpoint->username,
499 $endpoint->password,
500 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
501 );
502
503 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
504 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
505 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
506
507 $table = 'comment_' . bin2hex(random_bytes(8));
508
509 try {
510 $rawPdo->exec(sprintf('CREATE TABLE %s (id INTEGER PRIMARY KEY, status INTEGER)', $table));
511 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
512
513 $ztdPdo->exec(sprintf('INSERT INTO %s VALUES (1, 1)', $table));
514 $ztdPdo->exec(sprintf('INSERT INTO/* table */%s VALUES (2, 1)', $table));
515 $ztdPdo->exec(sprintf("-- deactivate item\nUPDATE/* table */%s SET status = 0 WHERE id = 1", $table));
516 $ztdPdo->exec(sprintf('DELETE FROM/* table */%s WHERE id = 2', $table));
517
518 $status = $ztdPdo->query(sprintf('SELECT status FROM/* table */%s WHERE id = 1', $table));
519 self::assertNotFalse($status);
520 self::assertSame(0, $status->fetchColumn());
521
522 $ids = $ztdPdo->query(sprintf("-- SELECT * FROM other_table WHERE DELETE UPDATE INSERT\nSELECT id FROM %s ORDER BY id", $table));
523 self::assertNotFalse($ids);
524 self::assertSame([1], $ids->fetchAll(PDO::FETCH_COLUMN));
525 } finally {
526 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
527 }
528 }
529
530 public function testPostgreSqlQuotedStringFormsPreserveWhereOffsets(): void
531 {
532 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
533 /** @var PDO $rawPdo */
534 $rawPdo = new PDO(
535 $endpoint->dsn(),
536 $endpoint->username,
537 $endpoint->password,
538 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
539 );
540
541 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
542 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
543 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
544
545 $table = 'literal_' . bin2hex(random_bytes(8));
546
547 try {
548 $rawPdo->exec(sprintf('CREATE TABLE %s (id INTEGER PRIMARY KEY, body TEXT)', $table));
549 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
550 $ztdPdo->exec(sprintf("INSERT INTO %s VALUES (1, 'original')", $table));
551
552 $ztdPdo->exec(sprintf("UPDATE %s SET body = 'it''s updated' WHERE id = 1", $table));
553 $body = $ztdPdo->query(sprintf('SELECT body FROM %s WHERE id = 1', $table));
554 self::assertNotFalse($body);
555 self::assertSame("it's updated", $body->fetchColumn());
556
557 $ztdPdo->exec(sprintf('UPDATE %s SET body = $text$reference to table$text$ WHERE id = 1', $table));
558 $body = $ztdPdo->query(sprintf('SELECT body FROM %s WHERE id = 1', $table));
559 self::assertNotFalse($body);
560 self::assertSame('reference to table', $body->fetchColumn());
561
562 $ztdPdo->exec(sprintf("UPDATE %s SET body = E'line1\\nline2' WHERE id = 1", $table));
563 $body = $ztdPdo->query(sprintf('SELECT body FROM %s WHERE id = 1', $table));
564 self::assertNotFalse($body);
565 self::assertSame("line1\nline2", $body->fetchColumn());
566 } finally {
567 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
568 }
569 }
570
571 public function testInsertWithoutColumnListSupportsConstraintKeywordPrefixes(): void
572 {
573 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
574 /** @var PDO $rawPdo */
575 $rawPdo = new PDO(
576 $endpoint->dsn(),
577 $endpoint->username,
578 $endpoint->password,
579 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
580 );
581
582 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
583 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
584 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
585
586 $table = 'booking_' . bin2hex(random_bytes(8));
587
588 try {
589 $rawPdo->exec(sprintf('CREATE TABLE %s (id INT PRIMARY KEY, guest TEXT, check_in TEXT, check_out TEXT)', $table));
590 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
591
592 self::assertSame(1, $ztdPdo->exec(sprintf(
593 "INSERT INTO %s VALUES (1, 'Alice', '2024-01-01', '2024-01-03')",
594 $table,
595 )));
596
597 $bookings = $ztdPdo->query(sprintf('SELECT * FROM %s', $table));
598 self::assertNotFalse($bookings);
599 self::assertSame([
600 [
601 'id' => 1,
602 'guest' => 'Alice',
603 'check_in' => '2024-01-01',
604 'check_out' => '2024-01-03',
605 ],
606 ], $bookings->fetchAll());
607 } finally {
608 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
609 }
610 }
611
612 public function testQuotedInsertSourceKeywordsRemainIdentifiers(): void
613 {
614 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
615 /** @var PDO $rawPdo */
616 $rawPdo = new PDO(
617 $endpoint->dsn(),
618 $endpoint->username,
619 $endpoint->password,
620 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
621 );
622
623 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
624 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
625 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
626
627
628 try {
629 $rawPdo->exec('CREATE TABLE "select" (id INTEGER PRIMARY KEY, val TEXT)');
630 $rawPdo->exec('CREATE TABLE "values" (id INTEGER PRIMARY KEY, val TEXT)');
631 $rawPdo->exec('CREATE TABLE keyword_columns (id INTEGER PRIMARY KEY, "select" TEXT, "values" TEXT)');
632 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
633
634 self::assertSame(1, $ztdPdo->exec("INSERT INTO \"select\" VALUES (1, 'table-select')"));
635 self::assertSame(1, $ztdPdo->exec("INSERT INTO \"values\" VALUES (2, 'table-values')"));
636 self::assertSame(1, $ztdPdo->exec("INSERT INTO keyword_columns (id, \"select\", \"values\") VALUES (3, 'column-select', 'column-values')"));
637 self::assertSame(1, $ztdPdo->exec("INSERT INTO \"select\" SELECT 4 AS id, 'insert-select' AS val"));
638
639 $selectRows = $ztdPdo->query('SELECT * FROM "select" ORDER BY id');
640 self::assertNotFalse($selectRows);
641 self::assertSame([
642 ['id' => 1, 'val' => 'table-select'],
643 ['id' => 4, 'val' => 'insert-select'],
644 ], $selectRows->fetchAll());
645
646 $valuesRows = $ztdPdo->query('SELECT * FROM "values"');
647 self::assertNotFalse($valuesRows);
648 self::assertSame([['id' => 2, 'val' => 'table-values']], $valuesRows->fetchAll());
649
650 $columnRows = $ztdPdo->query('SELECT id, "select", "values" FROM keyword_columns');
651 self::assertNotFalse($columnRows);
652 self::assertSame([
653 ['id' => 3, 'select' => 'column-select', 'values' => 'column-values'],
654 ], $columnRows->fetchAll());
655 } finally {
656 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
657 }
658 }
659
660 public function testJsonExistenceOperatorsRemainDistinctFromPlaceholders(): void
661 {
662 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
663 /** @var PDO $rawPdo */
664 $rawPdo = new PDO(
665 $endpoint->dsn(),
666 $endpoint->username,
667 $endpoint->password,
668 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
669 );
670
671 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
672 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
673 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
674
675 $table = 'json_docs_' . bin2hex(random_bytes(8));
676
677 try {
678 $rawPdo->exec(sprintf('CREATE TABLE %s (id INTEGER PRIMARY KEY, name TEXT, meta JSONB)', $table));
679 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
680 $ztdPdo->exec(sprintf("INSERT INTO %s VALUES (1, 'Doc A', '{\"author\":\"Alice\",\"reviewed\":true}'::jsonb)", $table));
681 $ztdPdo->exec(sprintf("INSERT INTO %s VALUES (2, 'Doc B', '{\"author\":\"Bob\"}'::jsonb)", $table));
682
683 $exists = $ztdPdo->query(sprintf("SELECT name FROM %s WHERE meta ? 'reviewed'", $table));
684 self::assertNotFalse($exists);
685 self::assertSame(['Doc A'], $exists->fetchAll(PDO::FETCH_COLUMN));
686
687 $existsAny = $ztdPdo->query(sprintf("SELECT name FROM %s WHERE meta ?| array['reviewed', 'missing']", $table));
688 self::assertNotFalse($existsAny);
689 self::assertSame(['Doc A'], $existsAny->fetchAll(PDO::FETCH_COLUMN));
690
691 $existsAll = $ztdPdo->query(sprintf("SELECT name FROM %s WHERE meta ?& array['author', 'reviewed']", $table));
692 self::assertNotFalse($existsAll);
693 self::assertSame(['Doc A'], $existsAll->fetchAll(PDO::FETCH_COLUMN));
694
695 $preparedOperator = $ztdPdo->prepare(sprintf('SELECT name FROM %s WHERE meta ? ? ORDER BY name', $table));
696 self::assertNotFalse($preparedOperator);
697 self::assertTrue($preparedOperator->execute(['author']));
698 self::assertSame(['Doc A', 'Doc B'], $preparedOperator->fetchAll(PDO::FETCH_COLUMN));
699
700 $preparedValue = $ztdPdo->prepare(sprintf('SELECT name FROM %s WHERE id = ?', $table));
701 self::assertNotFalse($preparedValue);
702 self::assertTrue($preparedValue->execute([2]));
703 self::assertSame(['Doc B'], $preparedValue->fetchAll(PDO::FETCH_COLUMN));
704 } finally {
705 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
706 }
707 }
708
709 public function testPreparedBooleanAndBigIntValuesRetainTheirTypes(): void
710 {
711 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
712 /** @var PDO $rawPdo */
713 $rawPdo = new PDO(
714 $endpoint->dsn(),
715 $endpoint->username,
716 $endpoint->password,
717 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
718 );
719
720 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
721 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
722 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
723
724 $table = 'typed_' . bin2hex(random_bytes(8));
725
726 try {
727 $rawPdo->exec(sprintf('CREATE TABLE %s (id INTEGER PRIMARY KEY, enabled BOOLEAN, quantity BIGINT)', $table));
728 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
729
730 $insert = $ztdPdo->prepare(sprintf('INSERT INTO %s (id, enabled, quantity) VALUES (?, ?, ?)', $table));
731 self::assertNotFalse($insert);
732 self::assertTrue($insert->execute([1, false, 9223372036854775807]));
733
734 $result = $ztdPdo->query(sprintf('SELECT enabled::int, quantity::text FROM %s', $table));
735 self::assertNotFalse($result);
736 self::assertSame(['enabled' => 0, 'quantity' => '9223372036854775807'], $result->fetch());
737 } finally {
738 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
739 }
740 }
741
742 public function testArrayAndBinaryValuesRetainNativeTypes(): void
743 {
744 $endpoint = \Testcontainers\Testcontainers::run(PostgreSqlRelease::fromEnvironment())->getData(Endpoint::class);
745 /** @var PDO $rawPdo */
746 $rawPdo = new PDO(
747 $endpoint->dsn(),
748 $endpoint->username,
749 $endpoint->password,
750 [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
751 );
752
753 $schemaName = 'ztd_' . bin2hex(random_bytes(8));
754 $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
755 $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
756
757 $table = 'typed_' . bin2hex(random_bytes(8));
758
759 try {
760 $rawPdo->exec(sprintf('CREATE TABLE %s (id INTEGER PRIMARY KEY, scores INTEGER[], payload BYTEA)', $table));
761 $ztdPdo = ZtdPdo::fromPdo($rawPdo, null);
762 $payload = "\x00\x01\xFF";
763
764 $insert = $ztdPdo->prepare(sprintf('INSERT INTO %s (id, scores, payload) VALUES (?, ?::integer[], ?)', $table));
765 self::assertNotFalse($insert);
766 self::assertTrue($insert->bindValue(1, 1, PDO::PARAM_INT));
767 self::assertTrue($insert->bindValue(2, '{90,85,92}'));
768 self::assertTrue($insert->bindValue(3, $payload, PDO::PARAM_LOB));
769 self::assertTrue($insert->execute());
770 self::assertSame(1, $ztdPdo->exec(sprintf('INSERT INTO %s (id, scores, payload) VALUES (2, ARRAY[1,2,3], NULL)', $table)));
771
772 $result = $ztdPdo->query(sprintf("SELECT scores::text, encode(payload, 'hex') AS payload FROM %s ORDER BY id", $table));
773 self::assertNotFalse($result);
774 self::assertSame([
775 ['scores' => '{90,85,92}', 'payload' => '0001ff'],
776 ['scores' => '{1,2,3}', 'payload' => null],
777 ], $result->fetchAll());
778 } finally {
779 $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
780 }
781 }
782
783}
784