packages/ztd-query-pdo-adapter/tests/Integration/Sqlite/SelectWhereTest.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 SelectWhereTest extends TestCase
21{
22 public function testWhereEquality(): 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 users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
26 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
27 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
28 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
29
30 $stmt = $rawPdo->query('SELECT * FROM users WHERE id = 1');
31 self::assertNotFalse($stmt);
32 /** @var list<Row> $rawRows */
33 $rawRows = $stmt->fetchAll();
34 $stmt = $ztdPdo->query('SELECT * FROM users WHERE id = 1');
35 self::assertNotFalse($stmt);
36 /** @var list<Row> $ztdRows */
37 $ztdRows = $stmt->fetchAll();
38 self::assertSame($rawRows, $ztdRows);
39 }
40
41 public function testWhereComparison(): void
42 {
43 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
44 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
45 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
46 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
47 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
48
49 $stmt = $rawPdo->query('SELECT * FROM users WHERE age > 28 ORDER BY id');
50 self::assertNotFalse($stmt);
51 /** @var list<Row> $rawRows */
52 $rawRows = $stmt->fetchAll();
53 $stmt = $ztdPdo->query('SELECT * FROM users WHERE age > 28 ORDER BY id');
54 self::assertNotFalse($stmt);
55 /** @var list<Row> $ztdRows */
56 $ztdRows = $stmt->fetchAll();
57 self::assertSame($rawRows, $ztdRows);
58 }
59
60 public function testWhereAnd(): void
61 {
62 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
63 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
64 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
65 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
66 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
67
68 $stmt = $rawPdo->query("SELECT * FROM users WHERE age > 20 AND name = 'Bob'");
69 self::assertNotFalse($stmt);
70 /** @var list<Row> $rawRows */
71 $rawRows = $stmt->fetchAll();
72 $stmt = $ztdPdo->query("SELECT * FROM users WHERE age > 20 AND name = 'Bob'");
73 self::assertNotFalse($stmt);
74 /** @var list<Row> $ztdRows */
75 $ztdRows = $stmt->fetchAll();
76 self::assertSame($rawRows, $ztdRows);
77 }
78
79 public function testWhereOr(): void
80 {
81 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
82 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
83 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
84 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
85 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
86
87 $stmt = $rawPdo->query("SELECT * FROM users WHERE name = 'Alice' OR name = 'Charlie' ORDER BY id");
88 self::assertNotFalse($stmt);
89 /** @var list<Row> $rawRows */
90 $rawRows = $stmt->fetchAll();
91 $stmt = $ztdPdo->query("SELECT * FROM users WHERE name = 'Alice' OR name = 'Charlie' ORDER BY id");
92 self::assertNotFalse($stmt);
93 /** @var list<Row> $ztdRows */
94 $ztdRows = $stmt->fetchAll();
95 self::assertSame($rawRows, $ztdRows);
96 }
97
98 public function testWhereIn(): void
99 {
100 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
101 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
102 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
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 * FROM users WHERE id IN (1, 3) ORDER BY id');
107 self::assertNotFalse($stmt);
108 /** @var list<Row> $rawRows */
109 $rawRows = $stmt->fetchAll();
110 $stmt = $ztdPdo->query('SELECT * FROM users WHERE id IN (1, 3) ORDER BY id');
111 self::assertNotFalse($stmt);
112 /** @var list<Row> $ztdRows */
113 $ztdRows = $stmt->fetchAll();
114 self::assertSame($rawRows, $ztdRows);
115 }
116
117 public function testWhereBetween(): void
118 {
119 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
120 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
121 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
122 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
123 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
124
125 $stmt = $rawPdo->query('SELECT * FROM users WHERE age BETWEEN 25 AND 32 ORDER BY id');
126 self::assertNotFalse($stmt);
127 /** @var list<Row> $rawRows */
128 $rawRows = $stmt->fetchAll();
129 $stmt = $ztdPdo->query('SELECT * FROM users WHERE age BETWEEN 25 AND 32 ORDER BY id');
130 self::assertNotFalse($stmt);
131 /** @var list<Row> $ztdRows */
132 $ztdRows = $stmt->fetchAll();
133 self::assertSame($rawRows, $ztdRows);
134 }
135
136 public function testWhereLike(): void
137 {
138 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
139 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
140 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
141 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
142 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
143
144 $stmt = $rawPdo->query("SELECT * FROM users WHERE name LIKE 'A%'");
145 self::assertNotFalse($stmt);
146 /** @var list<Row> $rawRows */
147 $rawRows = $stmt->fetchAll();
148 $stmt = $ztdPdo->query("SELECT * FROM users WHERE name LIKE 'A%'");
149 self::assertNotFalse($stmt);
150 /** @var list<Row> $ztdRows */
151 $ztdRows = $stmt->fetchAll();
152 self::assertSame($rawRows, $ztdRows);
153 }
154
155 public function testWhereIsNull(): void
156 {
157 $rawPdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC]);
158 $rawPdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)');
159 $rawPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
160 $ztdPdo = ZtdPdo::fromPdo($rawPdo);
161 $ztdPdo->exec("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35)");
162
163 $stmt = $rawPdo->query('SELECT * FROM users WHERE name IS NOT NULL ORDER BY id');
164 self::assertNotFalse($stmt);
165 /** @var list<Row> $rawRows */
166 $rawRows = $stmt->fetchAll();
167 $stmt = $ztdPdo->query('SELECT * FROM users WHERE name IS NOT NULL ORDER BY id');
168 self::assertNotFalse($stmt);
169 /** @var list<Row> $ztdRows */
170 $ztdRows = $stmt->fetchAll();
171 self::assertSame($rawRows, $ztdRows);
172 }
173}
174