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