packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/TemporaryTableTest.php

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PHPUnit\Framework\Attributes\CoversNothing;
9use PHPUnit\Framework\Attributes\Large;
10use PHPUnit\Framework\TestCase;
11use ZtdQuery\Adapter\Pdo\ZtdPdo;
12
13/**
14 * @requires extension pdo_sqlite
15 */
16#[CoversNothing]
17#[Large]
18final class TemporaryTableTest extends TestCase
19{
20    public function testDmlContinuesAcrossTemporaryTableLifecycle(): void
21    {
22        $rawPdo = new PDO('sqlite::memory:');
23        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
24        $ztdPdo->exec('CREATE TABLE source (id INTEGER PRIMARY KEY, value TEXT)');
25        $ztdPdo->exec("INSERT INTO source VALUES (1, 'a')");
26        $ztdPdo->exec('CREATE TEMP TABLE staging (id INTEGER PRIMARY KEY, value TEXT)');
27        $ztdPdo->exec('INSERT INTO staging SELECT * FROM source');
28        $ztdPdo->exec("UPDATE staging SET value = 'b' WHERE id = 1");
29        $ztdPdo->exec('DELETE FROM staging WHERE id = 1');
30        $ztdPdo->exec("INSERT INTO staging VALUES (2, 'c')");
31        $ztdPdo->exec('INSERT INTO source SELECT * FROM staging');
32
33        $statement = $ztdPdo->query('SELECT * FROM source ORDER BY id');
34
35        self::assertNotFalse($statement);
36        self::assertSame(
37            [['id' => 1, 'value' => 'a'], ['id' => 2, 'value' => 'c']],
38            $statement->fetchAll(PDO::FETCH_ASSOC),
39        );
40    }
41
42    public function testTemporaryTableCreatedBeforeWrappingIsReflected(): void
43    {
44        $rawPdo = new PDO('sqlite::memory:');
45        $rawPdo->exec('CREATE TEMPORARY TABLE staging (id INTEGER PRIMARY KEY, value TEXT)');
46        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
47
48        $ztdPdo->exec("INSERT INTO staging VALUES (1, 'a')");
49        $ztdPdo->exec("UPDATE staging SET value = 'b' WHERE id = 1");
50        $statement = $ztdPdo->query('SELECT * FROM staging');
51
52        self::assertNotFalse($statement);
53        self::assertSame([['id' => 1, 'value' => 'b']], $statement->fetchAll(PDO::FETCH_ASSOC));
54    }
55}
56