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