packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/GeneratedColumnTest.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 GeneratedColumnTest extends TestCase
19{
20    public function testGeneratedValuesDriveReadsAggregatesUpdatesAndDeletes(): void
21    {
22        $pdo = new PDO('sqlite::memory:', null, null, [
23            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
24            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
25        ]);
26        $pdo->exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL, unit_price REAL NOT NULL, total REAL GENERATED ALWAYS AS (qty * unit_price) STORED)');
27        $ztdPdo = ZtdPdo::fromPdo($pdo);
28
29        $ztdPdo->exec('INSERT INTO orders (id, qty, unit_price) VALUES (1, 5, 10), (2, 2, 7)');
30
31        $rows = $ztdPdo->query('SELECT id, CAST(total AS INTEGER) AS total FROM orders ORDER BY id');
32        self::assertNotFalse($rows);
33        self::assertSame(
34            [['id' => 1, 'total' => 50], ['id' => 2, 'total' => 14]],
35            $rows->fetchAll(),
36        );
37        $sum = $ztdPdo->query('SELECT CAST(SUM(total) AS INTEGER) FROM orders');
38        self::assertNotFalse($sum);
39        self::assertSame(64, $sum->fetchColumn());
40        $filtered = $ztdPdo->query('SELECT id FROM orders WHERE total > 20');
41        self::assertNotFalse($filtered);
42        self::assertSame([1], $filtered->fetchAll(PDO::FETCH_COLUMN));
43
44        self::assertSame(1, $ztdPdo->exec('UPDATE orders SET qty = 7 WHERE id = 1'));
45        $updated = $ztdPdo->query('SELECT CAST(total AS INTEGER) FROM orders WHERE id = 1');
46        self::assertNotFalse($updated);
47        self::assertSame(70, $updated->fetchColumn());
48        self::assertSame(1, $ztdPdo->exec('DELETE FROM orders WHERE total >= 70'));
49        $remaining = $ztdPdo->query('SELECT id FROM orders');
50        self::assertNotFalse($remaining);
51        self::assertSame([2], $remaining->fetchAll(PDO::FETCH_COLUMN));
52    }
53}
54