packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectFromTest.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 SelectFromTest extends TestCase
21{
22    public function testSelectAllColumns(): 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 users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
29        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
30
31        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
32        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
33
34        $stmt = $rawPdo->query('SELECT * FROM users ORDER BY id');
35        self::assertNotFalse($stmt);
36        /** @var list<Row> $rawRows */
37        $rawRows = $stmt->fetchAll();
38        $stmt = $ztdPdo->query('SELECT * FROM users ORDER BY id');
39        self::assertNotFalse($stmt);
40        /** @var list<Row> $ztdRows */
41        $ztdRows = $stmt->fetchAll();
42
43        self::assertSame($rawRows, $ztdRows);
44    }
45
46    public function testSelectSpecificColumns(): void
47    {
48        $rawPdo = new PDO('sqlite::memory:', null, null, [
49            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
50            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
51        ]);
52        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
53        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
54
55        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
56        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
57
58        $stmt = $rawPdo->query('SELECT name, age FROM users ORDER BY id');
59        self::assertNotFalse($stmt);
60        /** @var list<Row> $rawRows */
61        $rawRows = $stmt->fetchAll();
62        $stmt = $ztdPdo->query('SELECT name, age FROM users ORDER BY id');
63        self::assertNotFalse($stmt);
64        /** @var list<Row> $ztdRows */
65        $ztdRows = $stmt->fetchAll();
66
67        self::assertSame($rawRows, $ztdRows);
68    }
69
70    public function testSelectWithAlias(): void
71    {
72        $rawPdo = new PDO('sqlite::memory:', null, null, [
73            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
74            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
75        ]);
76        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
77        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
78
79        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
80        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
81
82        $stmt = $rawPdo->query('SELECT name AS user_name, age AS user_age FROM users ORDER BY id');
83        self::assertNotFalse($stmt);
84        /** @var list<Row> $rawRows */
85        $rawRows = $stmt->fetchAll();
86        $stmt = $ztdPdo->query('SELECT name AS user_name, age AS user_age FROM users ORDER BY id');
87        self::assertNotFalse($stmt);
88        /** @var list<Row> $ztdRows */
89        $ztdRows = $stmt->fetchAll();
90
91        self::assertSame($rawRows, $ztdRows);
92    }
93
94    public function testSelectWithTableAlias(): void
95    {
96        $rawPdo = new PDO('sqlite::memory:', null, null, [
97            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
98            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
99        ]);
100        $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
101        $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
102
103        $ztdPdo = ZtdPdo::fromPdo($rawPdo);
104        $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
105
106        $stmt = $rawPdo->query('SELECT u.name, u.age FROM users u ORDER BY u.id');
107        self::assertNotFalse($stmt);
108        /** @var list<Row> $rawRows */
109        $rawRows = $stmt->fetchAll();
110        $stmt = $ztdPdo->query('SELECT u.name, u.age FROM users u ORDER BY u.id');
111        self::assertNotFalse($stmt);
112        /** @var list<Row> $ztdRows */
113        $ztdRows = $stmt->fetchAll();
114
115        self::assertSame($rawRows, $ztdRows);
116    }
117}
118