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

1<?php
2
3declare(strict_types=1);
4
5namespace Tests\Integration\Sqlite;
6
7use PDO;
8use PDOStatement;
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 PreparedExecutionTest extends TestCase
17{
18    public function testPreparedInsertCanBeUpdatedAndObservedByRepreparedSelect(): void
19    {
20        $raw = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
21        $raw->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
22        $ztdPdo = ZtdPdo::fromPdo($raw);
23
24        $statement = $ztdPdo->prepare('INSERT INTO users VALUES (?, ?)');
25        self::assertInstanceOf(PDOStatement::class, $statement);
26        self::assertTrue($statement->execute([1, 'Alice']));
27
28        $statement = $ztdPdo->prepare('UPDATE users SET name = ? WHERE id = ?');
29        self::assertInstanceOf(PDOStatement::class, $statement);
30        self::assertTrue($statement->execute(['Alicia', 1]));
31
32        $statement = $ztdPdo->prepare('SELECT name FROM users WHERE id = 1');
33        self::assertInstanceOf(PDOStatement::class, $statement);
34        self::assertTrue($statement->execute([]));
35        self::assertSame('Alicia', $statement->fetchColumn());
36    }
37
38    public function testPreparedExpressionsAndReexecutionUseCurrentTypedShadowRows(): void
39    {
40        $raw = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
41        $raw->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL, stock INTEGER)');
42        $ztdPdo = ZtdPdo::fromPdo($raw);
43        $ztdPdo->exec("INSERT INTO products VALUES (1, 'Widget', 10, 100), (2, 'Gadget', 20, 50), (3, 'Sprocket', NULL, 5)");
44
45        $select = $ztdPdo->prepare('SELECT name FROM products WHERE COALESCE(price, ?) < ? ORDER BY id');
46        self::assertInstanceOf(PDOStatement::class, $select);
47        self::assertTrue($select->execute([0.0, 15.0]));
48        self::assertSame(['Widget', 'Sprocket'], $select->fetchAll(PDO::FETCH_COLUMN));
49
50        $delete = $ztdPdo->prepare('DELETE FROM products WHERE MIN(COALESCE(price, ?), stock) < ?');
51        self::assertInstanceOf(PDOStatement::class, $delete);
52        self::assertTrue($delete->execute([0.0, 10]));
53        self::assertSame(1, $delete->rowCount());
54
55        self::assertTrue($select->execute([0.0, 25.0]));
56        self::assertSame(['Widget', 'Gadget'], $select->fetchAll(PDO::FETCH_COLUMN));
57    }
58}
59