packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/ExecutionResultTest.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 ExecutionResultTest extends TestCase
19{
20    public function testReturningProjectsInsertUpdateAndDeleteRows(): void
21    {
22        $reference = new PDO('sqlite::memory:', null, null, [
23            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
24            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
25        ]);
26        $underlying = new PDO('sqlite::memory:', null, null, [
27            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
28            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
29        ]);
30        $schema = 'CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, score INTEGER)';
31        $reference->exec($schema);
32        $underlying->exec($schema);
33        $pdo = ZtdPdo::fromPdo($underlying);
34
35        $insert = "INSERT INTO items (name, score) VALUES ('Alice', 90), ('Bob', 80) RETURNING id, name";
36        $referenceInsert = $reference->query($insert);
37        $ztdInsert = $pdo->query($insert);
38        self::assertNotFalse($referenceInsert);
39        self::assertNotFalse($ztdInsert);
40        self::assertSame($referenceInsert->fetchAll(), $ztdInsert->fetchAll());
41
42        $update = 'UPDATE items SET score = score + 5 WHERE id = 1 RETURNING id, name, score';
43        $referenceUpdate = $reference->query($update);
44        $ztdUpdate = $pdo->query($update);
45        self::assertNotFalse($referenceUpdate);
46        self::assertNotFalse($ztdUpdate);
47        self::assertSame($referenceUpdate->fetchAll(), $ztdUpdate->fetchAll());
48
49        $delete = 'DELETE FROM items WHERE id = 2 RETURNING *';
50        $referenceDelete = $reference->query($delete);
51        $ztdDelete = $pdo->query($delete);
52        self::assertNotFalse($referenceDelete);
53        self::assertNotFalse($ztdDelete);
54        self::assertSame($referenceDelete->fetchAll(), $ztdDelete->fetchAll());
55    }
56
57    public function testPreparedReturningAndLastInsertIdTrackShadowIdentity(): void
58    {
59        $underlying = new PDO('sqlite::memory:', null, null, [
60            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
61            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
62        ]);
63        $underlying->exec('CREATE TABLE items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');
64        $pdo = ZtdPdo::fromPdo($underlying);
65        $statement = $pdo->prepare('INSERT INTO items (name) VALUES (?) RETURNING id, name');
66        self::assertNotFalse($statement);
67
68        $statement->execute(['first']);
69        self::assertSame([['id' => 1, 'name' => 'first']], $statement->fetchAll());
70        self::assertSame('1', $pdo->lastInsertId());
71        $statement->execute(['second']);
72        self::assertSame(['id' => 2, 'name' => 'second'], $statement->fetch());
73        self::assertSame('2', $pdo->lastInsertId());
74
75        $pdo->exec("INSERT INTO items (id, name) VALUES (42, 'explicit')");
76        self::assertSame('42', $pdo->lastInsertId());
77    }
78
79    public function testRowCountUsesNativeMatchedRowConvention(): void
80    {
81        $reference = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
82        $underlying = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
83        $schema = 'CREATE TABLE items (id INTEGER PRIMARY KEY, score INTEGER)';
84        $reference->exec($schema);
85        $underlying->exec($schema);
86        $pdo = ZtdPdo::fromPdo($underlying);
87        $reference->exec('INSERT INTO items VALUES (1, 10)');
88        $pdo->exec('INSERT INTO items VALUES (1, 10)');
89
90        self::assertSame(
91            $reference->exec('UPDATE items SET score = 10 WHERE id = 1'),
92            $pdo->exec('UPDATE items SET score = 10 WHERE id = 1'),
93        );
94        $referenceStatement = $reference->prepare('UPDATE items SET score = ? WHERE id = ?');
95        $ztdStatement = $pdo->prepare('UPDATE items SET score = ? WHERE id = ?');
96        self::assertNotFalse($referenceStatement);
97        self::assertNotFalse($ztdStatement);
98        $referenceStatement->execute([10, 1]);
99        $ztdStatement->execute([10, 1]);
100        self::assertSame($referenceStatement->rowCount(), $ztdStatement->rowCount());
101    }
102}
103