packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/TransactionTest.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 TransactionTest extends TestCase
19{
20    public function testRollbackRestoresInsertUpdateAndDeleteTogether(): void
21    {
22        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
23        $rawPdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)');
24        $pdo = ZtdPdo::fromPdo($rawPdo);
25        $pdo->exec("INSERT INTO items VALUES (1, 'one')");
26        $pdo->exec("INSERT INTO items VALUES (2, 'two')");
27
28        $pdo->beginTransaction();
29        $pdo->exec("INSERT INTO items VALUES (3, 'three')");
30        $pdo->exec("UPDATE items SET name = 'changed' WHERE id = 1");
31        $pdo->exec('DELETE FROM items WHERE id = 2');
32        $pdo->rollBack();
33
34        $statement = $pdo->query('SELECT * FROM items ORDER BY id');
35        self::assertNotFalse($statement);
36        self::assertSame([
37            ['id' => 1, 'name' => 'one'],
38            ['id' => 2, 'name' => 'two'],
39        ], $statement->fetchAll(PDO::FETCH_ASSOC));
40    }
41
42    public function testRollbackToSavepointRestoresOnlyNestedMutations(): void
43    {
44        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
45        $rawPdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)');
46        $pdo = ZtdPdo::fromPdo($rawPdo);
47        $pdo->exec("INSERT INTO items VALUES (1, 'one')");
48
49        $pdo->beginTransaction();
50        $pdo->exec("INSERT INTO items VALUES (2, 'two')");
51        $pdo->exec('SAVEPOINT nested');
52        $pdo->exec("INSERT INTO items VALUES (3, 'three')");
53        $pdo->exec('ROLLBACK TO SAVEPOINT nested');
54        $pdo->exec('RELEASE SAVEPOINT nested');
55        $pdo->commit();
56
57        $statement = $pdo->query('SELECT * FROM items ORDER BY id');
58        self::assertNotFalse($statement);
59        self::assertSame([
60            ['id' => 1, 'name' => 'one'],
61            ['id' => 2, 'name' => 'two'],
62        ], $statement->fetchAll(PDO::FETCH_ASSOC));
63    }
64
65    public function testSqlTransactionStatementsUseTheSameShadowSnapshots(): void
66    {
67        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
68        $rawPdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)');
69        $pdo = ZtdPdo::fromPdo($rawPdo);
70
71        $pdo->exec('BEGIN');
72        $pdo->exec("INSERT INTO items VALUES (1, 'one')");
73        $pdo->exec('ROLLBACK');
74
75        $statement = $pdo->query('SELECT * FROM items');
76        self::assertNotFalse($statement);
77        self::assertSame([], $statement->fetchAll(PDO::FETCH_ASSOC));
78    }
79
80    public function testRollbackRestoresVirtualSchemaRegistry(): void
81    {
82        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
83        $pdo = ZtdPdo::fromPdo($rawPdo);
84
85        $pdo->beginTransaction();
86        $pdo->exec('CREATE TABLE virtual_items (id INTEGER PRIMARY KEY)');
87        $pdo->rollBack();
88        $pdo->exec('CREATE TABLE virtual_items (id INTEGER PRIMARY KEY)');
89        $pdo->exec('INSERT INTO virtual_items VALUES (1)');
90
91        $statement = $pdo->query('SELECT * FROM virtual_items');
92        self::assertNotFalse($statement);
93        self::assertSame([['id' => 1]], $statement->fetchAll(PDO::FETCH_ASSOC));
94    }
95}
96