packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectRecursiveCteTest.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 SelectRecursiveCteTest extends TestCase
21{
22    public function testRecursiveCte(): void
23    {
24        $rawPdo = new PDO('sqlite::memory:', null, null, [
25            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
26            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
27        ]);
28        $rawPdo->exec('CREATE TABLE categories (id INTEGER PRIMARY KEY, parent_id INTEGER, name TEXT NOT NULL)');
29        $rawPdo->exec("INSERT INTO categories (id, parent_id, name) VALUES (1, NULL, 'Root'), (2, 1, 'Child1'), (3, 1, 'Child2'), (4, 2, 'Grandchild1')");
30
31        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
32        $ztdPdo->exec("INSERT INTO categories (id, parent_id, name) VALUES (1, NULL, 'Root'), (2, 1, 'Child1'), (3, 1, 'Child2'), (4, 2, 'Grandchild1')");
33
34        $sql = 'WITH RECURSIVE tree AS ('
35            . 'SELECT id, parent_id, name, 0 AS depth FROM categories WHERE parent_id IS NULL '
36            . 'UNION ALL '
37            . 'SELECT c.id, c.parent_id, c.name, t.depth + 1 FROM categories c INNER JOIN tree t ON c.parent_id = t.id'
38            . ') SELECT * FROM tree ORDER BY id';
39
40        $stmt = $rawPdo->query($sql);
41        self::assertNotFalse($stmt);
42        /** @var list<Row> $rawRows */
43        $rawRows = $stmt->fetchAll();
44        $stmt = $ztdPdo->query($sql);
45        self::assertNotFalse($stmt);
46        /** @var list<Row> $ztdRows */
47        $ztdRows = $stmt->fetchAll();
48
49        self::assertSame($rawRows, $ztdRows);
50    }
51}
52