packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectWindowTest.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 * @phpstan-type Row array<string, mixed>
17 */
18#[CoversNothing]
19#[Large]
20final class SelectWindowTest extends TestCase
21{
22    public function testRowNumber(): void
23    {
24        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
25        $rawPdo->exec('CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)');
26        $rawPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
27        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
28        $ztdPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
29
30        $sql = 'SELECT id, category, amount, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM sales ORDER BY id';
31        $stmt = $rawPdo->query($sql);
32        self::assertNotFalse($stmt);
33        /** @var list<Row> $rawRows */
34        $rawRows = $stmt->fetchAll();
35        $stmt = $ztdPdo->query($sql);
36        self::assertNotFalse($stmt);
37        /** @var list<Row> $ztdRows */
38        $ztdRows = $stmt->fetchAll();
39        self::assertSame($rawRows, $ztdRows);
40    }
41
42    public function testRankPartitionBy(): void
43    {
44        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
45        $rawPdo->exec('CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)');
46        $rawPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
47        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
48        $ztdPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
49
50        $sql = 'SELECT id, category, amount, RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rnk FROM sales ORDER BY id';
51        $stmt = $rawPdo->query($sql);
52        self::assertNotFalse($stmt);
53        /** @var list<Row> $rawRows */
54        $rawRows = $stmt->fetchAll();
55        $stmt = $ztdPdo->query($sql);
56        self::assertNotFalse($stmt);
57        /** @var list<Row> $ztdRows */
58        $ztdRows = $stmt->fetchAll();
59        self::assertSame($rawRows, $ztdRows);
60    }
61
62    public function testSumOver(): void
63    {
64        $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
65        $rawPdo->exec('CREATE TABLE sales (id INTEGER PRIMARY KEY, category TEXT NOT NULL, amount INTEGER NOT NULL)');
66        $rawPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
67        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
68        $ztdPdo->exec("INSERT INTO sales (id, category, amount) VALUES (1, 'A', 100), (2, 'B', 200), (3, 'A', 150), (4, 'B', 50), (5, 'C', 300)");
69
70        $sql = 'SELECT id, category, amount, SUM(amount) OVER (PARTITION BY category) AS cat_total FROM sales ORDER BY id';
71        $stmt = $rawPdo->query($sql);
72        self::assertNotFalse($stmt);
73        /** @var list<Row> $rawRows */
74        $rawRows = $stmt->fetchAll();
75        $stmt = $ztdPdo->query($sql);
76        self::assertNotFalse($stmt);
77        /** @var list<Row> $ztdRows */
78        $ztdRows = $stmt->fetchAll();
79        self::assertSame($rawRows, $ztdRows);
80    }
81}
82