packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/ViewTest.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 ViewTest extends TestCase
19{
20    public function testViewsReadShadowWritesAcrossFiltersJoinsAggregatesAndPreparation(): 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 accounts (id INTEGER PRIMARY KEY, region TEXT, amount INTEGER, active INTEGER)');
27        $pdo->exec('CREATE TABLE regions (code TEXT PRIMARY KEY, label TEXT)');
28        $pdo->exec('CREATE VIEW active_accounts AS SELECT id, region, amount FROM accounts WHERE active = 1');
29        $pdo->exec('CREATE VIEW account_labels AS SELECT a.id, r.label, a.amount FROM active_accounts a JOIN regions r ON r.code = a.region');
30        $pdo->exec('CREATE VIEW region_totals AS SELECT region, COUNT(*) AS account_count, SUM(amount) AS total_amount FROM active_accounts GROUP BY region');
31        $ztdPdo = ZtdPdo::fromPdo($pdo);
32
33        $ztdPdo->exec("INSERT INTO accounts VALUES (1, 'north', 100, 1), (2, 'south', 200, 0), (3, 'north', 300, 1)");
34        $ztdPdo->exec("INSERT INTO regions VALUES ('north', 'North'), ('south', 'South')");
35
36        $simple = $ztdPdo->query('SELECT id FROM active_accounts ORDER BY id');
37        self::assertNotFalse($simple);
38        self::assertSame([1, 3], $simple->fetchAll(PDO::FETCH_COLUMN));
39
40        $prepared = $ztdPdo->prepare('SELECT id FROM active_accounts WHERE amount >= ? ORDER BY id');
41        self::assertNotFalse($prepared);
42        self::assertTrue($prepared->execute([150]));
43        self::assertSame([3], $prepared->fetchAll(PDO::FETCH_COLUMN));
44
45        $joined = $ztdPdo->query('SELECT id, label FROM account_labels ORDER BY id');
46        self::assertNotFalse($joined);
47        self::assertSame(
48            [['id' => 1, 'label' => 'North'], ['id' => 3, 'label' => 'North']],
49            $joined->fetchAll(),
50        );
51
52        $aggregate = $ztdPdo->query('SELECT region, account_count, total_amount FROM region_totals');
53        self::assertNotFalse($aggregate);
54        self::assertSame(
55            [['region' => 'north', 'account_count' => 2, 'total_amount' => 400]],
56            $aggregate->fetchAll(),
57        );
58    }
59}
60