packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/CteDmlTest.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#[CoversNothing]
15#[Large]
16final class CteDmlTest extends TestCase
17{
18    public function testCteDefinitionsRemainVisibleToRewrittenInsertUpdateAndDelete(): void
19    {
20        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
21        /** @var PDO $rawPdo */
22        $rawPdo = new PDO(
23            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
24            'test',
25            'test',
26            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
27        );
28
29        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
30        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
31        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
32
33        $table = 'prefix_' . bin2hex(random_bytes(8));
34
35        try {
36            $rawPdo->exec("CREATE TABLE {$table} (id INTEGER PRIMARY KEY, value TEXT)");
37            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
38
39            self::assertSame(2, $ztdPdo->exec("WITH source(id, value) AS (VALUES (1, 'one'), (2, 'two')) INSERT INTO {$table} SELECT * FROM source"));
40            self::assertSame(1, $ztdPdo->exec("WITH chosen AS (SELECT id FROM {$table} WHERE value = 'two') UPDATE {$table} SET value = 'changed' WHERE id IN (SELECT id FROM chosen)"));
41            self::assertSame(1, $ztdPdo->exec("WITH chosen AS (SELECT id FROM {$table} WHERE value = 'one') DELETE FROM {$table} WHERE id IN (SELECT id FROM chosen)"));
42
43            $statement = $ztdPdo->query("SELECT * FROM {$table}");
44            self::assertNotFalse($statement);
45            self::assertSame([['id' => 2, 'value' => 'changed']], $statement->fetchAll(PDO::FETCH_ASSOC));
46        } finally {
47            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
48        }
49    }
50}
51