packages/ztd-query-pdo-adapter/tests/Integration/PostgreSql/TemporaryTableTest.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/**
15 * @requires extension pdo_pgsql
16 * @group integration
17 * @group postgres
18 */
19#[CoversNothing]
20#[Large]
21final class TemporaryTableTest extends TestCase
22{
23    public function testDmlContinuesAcrossTemporaryTableLifecycle(): void
24    {
25        $containerInstance = \Testcontainers\Testcontainers::run(PostgreSql16Container::class);
26        /** @var PDO $rawPdo */
27        $rawPdo = new PDO(
28            sprintf('pgsql:host=%s;port=%d;dbname=test', str_replace('localhost', '127.0.0.1', $containerInstance->getHost()), $containerInstance->getMappedPort(5432)),
29            'test',
30            'test',
31            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC],
32        );
33
34        $schemaName = 'ztd_' . bin2hex(random_bytes(8));
35        $rawPdo->exec(sprintf('CREATE SCHEMA "%s"', $schemaName));
36        $rawPdo->exec(sprintf('SET search_path TO "%s"', $schemaName));
37
38
39        try {
40            $ztdPdo = ZtdPdo::fromPdo($rawPdo);
41            $ztdPdo->exec('CREATE TABLE source (id INT PRIMARY KEY, value TEXT)');
42            $ztdPdo->exec("INSERT INTO source VALUES (1, 'a')");
43            $ztdPdo->exec('CREATE TEMP TABLE staging (id INT PRIMARY KEY, value TEXT)');
44            $ztdPdo->exec('INSERT INTO staging SELECT * FROM source');
45            $ztdPdo->exec("UPDATE staging SET value = 'b' WHERE id = 1");
46            $ztdPdo->exec('DELETE FROM staging WHERE id = 1');
47            $ztdPdo->exec("INSERT INTO staging VALUES (2, 'c')");
48            $ztdPdo->exec('INSERT INTO source SELECT * FROM staging');
49
50            $statement = $ztdPdo->query('SELECT * FROM source ORDER BY id');
51            self::assertNotFalse($statement);
52            self::assertSame(
53                [['id' => 1, 'value' => 'a'], ['id' => 2, 'value' => 'c']],
54                $statement->fetchAll(PDO::FETCH_ASSOC),
55            );
56        } finally {
57            $rawPdo->exec(sprintf('DROP SCHEMA IF EXISTS "%s" CASCADE', $schemaName));
58        }
59    }
60}
61