packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/PreparedExecutionTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\PostgreSql;
6
7use Container\PostgreSql16Container;
8use PDO;
9use PDOStatement;
10use PHPUnit\Framework\Attributes\CoversNothing;
11use PHPUnit\Framework\Attributes\Large;
12use PHPUnit\Framework\TestCase;
13use ZtdQuery\Adapter\Pdo\ZtdPdo;
14
15#[CoversNothing]
16#[Large]
17final class PreparedExecutionTest extends TestCase
18{
19    public function testNativePositionsRemainBoundAcrossExpressionsAndMutations(): void
20    {
21        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
22        /** @var PDO $rawPdo */
23        $rawPdo = new PDO(
24            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
25            'test',
26            'test',
27            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
28        );
29
30        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
31        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
32        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
33
34        $products = 'products_' . bin2hex(random_bytes(8));
35        $categories = 'categories_' . bin2hex(random_bytes(8));
36
37        try {
38            $rawPdo->exec("CREATE TABLE {$categories} (category_id INTEGER PRIMARY KEY, name TEXT)");
39            $rawPdo->exec("CREATE TABLE {$products} (id INTEGER PRIMARY KEY, category_id INTEGER, name TEXT, price INTEGER, status TEXT)");
40            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
41
42            $insertCategory = $ztdPdo->prepare("INSERT INTO {$categories} VALUES (\$1, \$2)");
43            self::assertInstanceOf(PDOStatement::class, $insertCategory);
44            self::assertTrue($insertCategory->execute([1, 'tools']));
45
46            $insertProduct = $ztdPdo->prepare("INSERT INTO {$products} VALUES (\$1, \$2, \$3, \$4, \$5)");
47            self::assertInstanceOf(PDOStatement::class, $insertProduct);
48            self::assertTrue($insertProduct->execute([1, 1, 'Hammer', 20, 'active']));
49            self::assertTrue($insertProduct->execute([2, 1, 'Saw', 40, 'active']));
50
51            $aggregate = $ztdPdo->prepare("SELECT category_id, SUM(price) FILTER (WHERE status = \$1) AS total FROM {$products} GROUP BY category_id HAVING SUM(price) > \$2");
52            self::assertInstanceOf(PDOStatement::class, $aggregate);
53            self::assertTrue($aggregate->execute(['active', 50]));
54            self::assertSame(60, $aggregate->fetchColumn(1));
55
56            $joined = $ztdPdo->prepare("SELECT p.name FROM {$products} p JOIN {$categories} c USING (category_id) WHERE p.price BETWEEN \$1 AND \$2 ORDER BY p.id");
57            self::assertInstanceOf(PDOStatement::class, $joined);
58            self::assertTrue($joined->execute([10, 30]));
59            self::assertSame(['Hammer'], $joined->fetchAll(PDO::FETCH_COLUMN));
60
61            $update = $ztdPdo->prepare("UPDATE {$products} SET status = CASE WHEN price >= \$1 THEN \$2 ELSE status END WHERE id IN (\$3, \$4)");
62            self::assertInstanceOf(PDOStatement::class, $update);
63            self::assertTrue($update->execute([30, 'premium', 1, 2]));
64
65            $current = $ztdPdo->prepare("SELECT status FROM {$products} WHERE id = \$1");
66            self::assertInstanceOf(PDOStatement::class, $current);
67            self::assertTrue($current->execute([2]));
68            self::assertSame('premium', $current->fetchColumn());
69        } finally {
70            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
71        }
72    }
73}
74