suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Test / LibSqlite.php
36.9 KB、614行 生 Blame 履歴
1<?php
2/*************************************************************
3# 076 License
4
5Copyright (c) テクニカル諏訪子
6
7Permission is hereby granted to any person obtaining a copy of the software
8Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
9sublicense, and/or sell copies of the Software, subject to the following conditions:
10
11 1. **Origin Attribution**:
12 - You must not misrepresent the origin of the Software; you must not claim
13 you created the original Software.
14 - If the Software is used in a product, you must either:
15 a. Provide clear attribution in the product's documentation, user interface,
16 or other visible areas, **OR**
17 b. Pay the original developers a fee they specify in writing.
18 2. **Usage Restriction**:
19 - The Software, or any derivative works, dependencies, or libraries
20 incorporating it, must not be used for censorship or to suppress freedom of
21 speech, expression, or creativity. Prohibited uses include, but are not
22 limited to:
23 - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
24 ideas, or objective reality.
25 - Tools or systems designed to restrict access to information or
26 artistic works.
27 3. **Notice Preservation**:
28 - This license and the above copyright notice must remain intact in all copies
29 of the source code.
30
31THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
32INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
33PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
34HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
35CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
36OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
37 */
38namespace Std\Test;
39
40require_once __DIR__.'/../../../autoload.php';
41
42use Std\Lib\Tester;
43use Std\Lib\Db;
44use Std\Lib\Db\DbException;
45use Std\Lib\Db\Sqlite;
46use Std\Lib\Db\Sqlite\Blob;
47use Std\Lib\Db\Sqlite\Btree;
48use Std\Lib\Db\Sqlite\Pager;
49use Std\Lib\Db\Sqlite\Parser;
50use Std\Lib\Db\Sqlite\Record;
51use Std\Lib\Db\Sqlite\Value;
52
53$test = new Tester([
54 'colorOutput' => true,
55 'verboseOutput' => true
56]);
57
58enum LibSqliteColor: string {
59 case Red = 'あか';
60}
61
62$tmp = sys_get_temp_dir().'/lbsqlite_'.bin2hex(random_bytes(6));
63mkdir($tmp, 0700);
64register_shutdown_function(function() use ($tmp): void {
65 foreach (glob($tmp.'/*') ?: [] as $f) @unlink($f);
66 @rmdir($tmp);
67});
68$fixtures = json_decode((string)file_get_contents(__DIR__.'/Helper/sqlite.json'), true);
69
70$catch = function(callable $fn): ?\Throwable {
71 try {
72 $fn();
73 } catch (\Throwable $e) {
74 return $e;
75 }
76 return null;
77};
78$open = function(string $name, array $o = []) use ($tmp): Sqlite {
79 $db = new Sqlite($o + ['path' => $tmp.'/'.$name]);
80 $db->connect();
81 return $db;
82};
83$rows = fn(Sqlite $db, string $sql) => $db->query($sql)['rows'];
84$one = fn(Sqlite $db, string $sql) => array_values($db->query($sql)['rows'][0] ?? [])[0] ?? null;
85$run = function(Sqlite $db, string $sql, array $params = []): array {
86 $id = $db->prepare($sql);
87 try {
88 return $db->execute($id, $params);
89 } finally {
90 $db->demolish($id);
91 }
92};
93// pdo_sqlite（本物のSQLite）が有れば、互換性も確認する
94$pdo = fn(string $file): ?\PDO => class_exists('PDO') && in_array('sqlite', \PDO::getAvailableDrivers(), true) ? new \PDO('sqlite:'.$file) : null;
95
96
97$test->describe('SQLite（レコードと値）', function($test) use ($catch): void {
98 $test->it('可変長整数を読み書き出来るはず', function($test): void {
99 foreach ([0, 1, 127, 128, 240, 2287, 16383, 16384, 0x00FFFFFFFFFFFFFF, 0x0100000000000000, PHP_INT_MAX, -1, PHP_INT_MIN] as $v) {
100 $b = Record::putVarint($v);
101 $pos = 0;
102 $test->assertSame($v, Record::getVarint($b, $pos), (string)$v);
103 $test->assertSame(strlen($b), $pos);
104 $test->assertSame(strlen($b), Record::varintLen($v), "長さ：{$v}");
105 }
106 $test->assertSame("\x81\x00", Record::putVarint(128));
107 $test->assertSame(9, strlen(Record::putVarint(-1)));
108 });
109
110 $test->it('レコードの全部の型を読み書き出来るはず', function($test): void {
111 $vals = [null, 0, 1, -1, 127, -128, 128, 32767, -32769, 8388607, -8388609, 2147483647, -2147483649, 140737488355327, -140737488355329,
112 PHP_INT_MAX, PHP_INT_MIN, 1.5, -0.0, 1e300, '', '日本語', new Blob(''), new Blob("\x00\xff")];
113 $rec = Record::encode($vals);
114 $back = Record::decode($rec);
115 $test->assertCount(count($vals), $back);
116 foreach ($vals as $i => $v) {
117 if ($v instanceof Blob) {
118 $test->assertTrue($back[$i] instanceof Blob && $back[$i]->data === $v->data, "BLOB {$i}");
119 } else {
120 $test->assertSame($v, $back[$i], "値{$i}");
121 }
122 }
123 // 0と1は値の無いシリアル型（8、9）
124 $test->assertSame("\x03\x08\x09", Record::encode([0, 1]));
125 // 長いヘッダー
126 $many = array_fill(0, 200, 'x');
127 $test->assertSame($many, Record::decode(Record::encode($many)));
128 });
129
130 $test->it('SQLiteの順番で比べるはず', function($test): void {
131 $sorted = [null, -5, 1, 1.5, 2, 'A', 'B', 'a', new Blob('A')];
132 for ($i = 0; $i < count($sorted) - 1; $i++) {
133 $test->assertSame(-1, Record::compare($sorted[$i], $sorted[$i + 1]), "{$i}");
134 }
135 $test->assertSame(0, Record::compare(1, 1.0));
136 $test->assertSame(0, Record::compare('abc', 'ABC', 'NOCASE'));
137 $test->assertSame(0, Record::compare('a ', 'a', 'RTRIM'));
138 $test->assertSame(1, Record::compare(PHP_INT_MAX, 9.2e18));
139 });
140
141 $test->it('型の親和性と数値の文字列', function($test): void {
142 $test->assertSame(Value::INTEGER, Value::affinity('BIGINT UNSIGNED'));
143 $test->assertSame(Value::TEXT, Value::affinity('VARCHAR(255)'));
144 $test->assertSame(Value::BLOB, Value::affinity(''));
145 $test->assertSame(Value::REAL, Value::affinity('DOUBLE PRECISION'));
146 $test->assertSame(Value::NUMERIC, Value::affinity('DECIMAL(10,2)'));
147 $test->assertSame(Value::INTEGER, Value::affinity('POINT'), 'POINTは「INT」を含む');
148 $test->assertSame(12, Value::apply('12', Value::INTEGER));
149 $test->assertSame(3, Value::apply('3.0', Value::NUMERIC));
150 $test->assertSame(2.5, Value::apply(' 2.5 ', Value::NUMERIC));
151 $test->assertSame('12abc', Value::apply('12abc', Value::INTEGER));
152 $test->assertSame('1.0', Value::apply(1.0, Value::TEXT));
153 $test->assertSame('1.0e+100', Value::realToText(1e100));
154 $test->assertSame('1.0e-05', Value::realToText(1e-5));
155 $test->assertSame('0.1', Value::realToText(0.1));
156 $test->assertSame('123456789012345.0', Value::realToText(123456789012345.0));
157 });
158
159 $test->it('SQLを構文解析出来るはず', function($test) use ($catch): void {
160 $p = new Parser();
161 $st = $p->parseAll("SELECT a, 'x;y' AS b FROM t WHERE c = ? AND d = :d -- コメント\n; /* 2つ目 */ INSERT INTO t VALUES (1)");
162 $test->assertCount(2, $st);
163 $test->assertSame('select', $st[0]['t']);
164 $test->assertSame(2, $st[0]['nParam']);
165 $test->assertSame([':d' => 2], $st[0]['names']);
166 $test->assertSame('insert', $st[1]['t']);
167 $e = $catch(fn() => $p->parseAll('SELEC 1'));
168 $test->assertTrue($e instanceof DbException);
169 $test->assertStringContains('構文エラー', $e->getMessage());
170 $test->assertStringContains('未対応', $catch(fn() => $p->parseAll('CREATE TRIGGER x AFTER INSERT ON t BEGIN SELECT 1; END'))->getMessage());
171 $test->assertStringContains('未対応', $catch(fn() => $p->parseAll('SELECT rank() OVER (ORDER BY a) FROM t'))->getMessage());
172 });
173});
174
175$test->describe('SQLite（テーブルとクエリ）', function($test) use ($open, $rows, $one, $run, $catch, $tmp, $pdo): void {
176 $test->it('テーブルを作って、データを入れて読めるはず', function($test) use ($open, $rows, $one, $run): void {
177 $db = $open('a.sqlite');
178 $test->assertTrue($db->isConnected());
179 $test->assertTrue($db->ping());
180 $db->query('CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL UNIQUE, age INT DEFAULT 17, score REAL, data BLOB, note TEXT)');
181 $res = $db->query("INSERT INTO users (name, age, score) VALUES ('諏訪子', 1000, 9.5), ('神奈子', 999, 8.25)");
182 $test->assertSame(2, $res['affectedRows']);
183 $test->assertSame(2, $res['insertId']);
184 $res = $run($db, 'INSERT INTO users (name, data, note) VALUES (?, ?, ?)', ['早苗', "\x00\xffバイナリ", str_repeat('長い', 5000)]);
185 $test->assertSame(3, $res['insertId']);
186 $test->assertSame(3, $db->lastInsertId());
187
188 $res = $db->query('SELECT * FROM users ORDER BY id');
189 $test->assertSame(['id', 'name', 'age', 'score', 'data', 'note'], array_column($res['fields'], 'name'));
190 $test->assertSame('INTEGER', $res['fields'][0]['type']);
191 $test->assertSame('users', $res['fields'][0]['table']);
192 $test->assertSame(['id' => 1, 'name' => '諏訪子', 'age' => 1000, 'score' => 9.5, 'data' => null, 'note' => null], $res['rows'][0]);
193 $test->assertSame(17, $res['rows'][2]['age']);
194 $test->assertSame("\x00\xffバイナリ", $res['rows'][2]['data']);
195 $test->assertSame(30000, strlen($res['rows'][2]['note']));
196 $test->assertSame('blob', $one($db, 'SELECT typeof(data) FROM users WHERE id = 3'));
197 $test->assertSame(['神奈子'], array_column($run($db, 'SELECT name FROM users WHERE age = ?', [999])['rows'], 'name'));
198 $test->assertSame([['name' => '諏訪子']], $run($db, 'SELECT name FROM users WHERE id = :id', ['id' => 1])['rows']);
199 $test->assertSame([['name' => '諏訪子']], $run($db, 'SELECT name FROM users WHERE id = ?1 OR id = ?1', [1])['rows']);
200
201 $res = $db->query('UPDATE users SET age = age + 1 WHERE age > 100');
202 $test->assertSame(2, $res['affectedRows']);
203 $test->assertSame(0, $res['insertId']);
204 $test->assertSame(1, $db->query('DELETE FROM users WHERE id = 2')['affectedRows']);
205 $test->assertSame(2, $one($db, 'SELECT count(*) FROM users'));
206 // AUTOINCREMENTは消した番号を使わない
207 $db->query('DELETE FROM users WHERE id = 3');
208 $test->assertSame(4, $db->query("INSERT INTO users (name) VALUES ('霊夢')")['insertId']);
209 $test->assertSame(4, $one($db, "SELECT seq FROM sqlite_sequence WHERE name = 'users'"));
210 });
211
212 $test->it('PHPの色々な型をパラメータとして送れるはず', function($test) use ($open, $run, $catch): void {
213 $db = $open('a.sqlite');
214 $cases = [
215 [42, 42, 'integer'], [-1.5, -1.5, 'real'], [true, 1, 'integer'], [false, 0, 'integer'], [null, null, 'null'], ['', '', 'text'],
216 ['諏訪子', '諏訪子', 'text'], ["\xff\xfe", "\xff\xfe", 'blob'], [new Blob('abc'), 'abc', 'blob'],
217 [new \DateTimeImmutable('2026-11-12 10:20:30'), '2026-11-12 10:20:30', 'text'],
218 [new \DateTimeImmutable('2026-11-12 10:20:30.5'), '2026-11-12 10:20:30.500', 'text'], [LibSqliteColor::Red, 'あか', 'text'],
219 ];
220 foreach ($cases as [$in, $out, $type]) {
221 $r = $run($db, 'SELECT ? AS v, typeof(?) AS t', [$in, $in])['rows'][0];
222 $test->assertSame($out, $r['v'], var_export($in, true));
223 $test->assertSame($type, $r['t'], var_export($in, true));
224 }
225 $e = $catch(fn() => $run($db, 'SELECT ?', [[1]]));
226 $test->assertStringContains('パラメータ0の型は未対応です', $e->getMessage());
227 $e = $catch(fn() => $run($db, 'SELECT ?, ?', [1]));
228 $test->assertStringContains('パラメータ数が一致しません', $e->getMessage());
229 $e = $catch(fn() => $run($db, 'SELECT :a', ['b' => 1]));
230 $test->assertStringContains('パラメータがありません：b', $e->getMessage());
231 // query()のパラメータはNULL
232 $test->assertSame([['?' => null]], $db->query('SELECT ?')['rows']);
233 });
234
235 $test->it('型の親和性で値を変換するはず', function($test) use ($open, $rows): void {
236 $db = $open('b.sqlite');
237 $db->query('CREATE TABLE t (i INTEGER, r REAL, t TEXT, n NUMERIC, b BLOB, x)');
238 $db->query("INSERT INTO t VALUES ('12', '1', 3, '4.0', '5', '6')");
239 $db->query("INSERT INTO t VALUES ('a', 'b', 1.5, '1e2', x'00', 7.0)");
240 $test->assertSame([
241 ['i' => 'integer', 'r' => 'real', 't' => 'text', 'n' => 'integer', 'b' => 'text', 'x' => 'text'],
242 ['i' => 'text', 'r' => 'text', 't' => 'text', 'n' => 'integer', 'b' => 'blob', 'x' => 'real'],
243 ], $rows($db, 'SELECT typeof(i) AS i, typeof(r) AS r, typeof(t) AS t, typeof(n) AS n, typeof(b) AS b, typeof(x) AS x FROM t'));
244 $test->assertSame([['t' => '3', 'n' => 4], ['t' => '1.5', 'n' => 100]], $rows($db, 'SELECT t, n FROM t'));
245 // 比較の親和性
246 $test->assertSame(1, count($rows($db, "SELECT * FROM t WHERE i = '12'")));
247 $test->assertSame(1, count($rows($db, 'SELECT * FROM t WHERE t = 3')));
248 $test->assertSame([['a' => 1, 'b' => 0, 'c' => 1]], $rows($db, "SELECT 10 = 10.0 AS a, '10' = 10 AS b, 'b' > 5 AS c"));
249 });
250
251 $test->it('式と関数', function($test) use ($open, $rows): void {
252 $db = $open('b.sqlite');
253 $r = $rows($db, "SELECT 7 / 2 AS a, 7.0 / 2 AS b, 7 % 3 AS c, 1 / 0 AS d, 'a' || 1 || 2.5 AS e, -(-9223372036854775807 - 1) AS f,
254 abs(-3) AS g, round(2.567, 2) AS h, length('日本語') AS i, substr('日本語テキスト', 2, 3) AS j, upper('abc') AS k,
255 coalesce(NULL, 'x') AS l, nullif(1, 1) AS m, instr('hello', 'll') AS n, replace('aaa', 'a', 'b') AS o, trim(' x ') AS p,
256 hex('AB') AS q, quote('it''s') AS r, typeof(1.0) AS s, CAST('12abc' AS INTEGER) AS t, CAST(12 AS TEXT) AS u,
257 printf('%05.1f|%s', 3.14159, 'x') AS v, iif(1, 'y', 'n') AS w, 'abc' LIKE 'A_C' AS x, 'abc' GLOB 'A*' AS y, max(1, 5, 3) AS z")[0];
258 $test->assertSame(['a' => 3, 'b' => 3.5, 'c' => 1, 'd' => null, 'e' => 'a12.5', 'f' => 9.2233720368547758e18, 'g' => 3, 'h' => 2.57,
259 'i' => 3, 'j' => '本語テ', 'k' => 'ABC', 'l' => 'x', 'm' => null, 'n' => 3, 'o' => 'bbb', 'p' => 'x', 'q' => '4142', 'r' => "'it''s'",
260 's' => 'real', 't' => 12, 'u' => '12', 'v' => '003.1|x', 'w' => 'y', 'x' => 1, 'y' => 0, 'z' => 5], $r);
261 $r = $rows($db, "SELECT 1 IN (1, 2) AS a, NULL IN (1) AS b, 3 IN (NULL, 1) AS c, 2 BETWEEN 1 AND 3 AS d,
262 CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' END AS e, NULL IS NULL AS f, 1 IS NOT 2 AS g")[0];
263 $test->assertSame(['a' => 1, 'b' => null, 'c' => null, 'd' => 1, 'e' => 'two', 'f' => 1, 'g' => 1], $r);
264 $r = $rows($db, "SELECT datetime('2026-01-31 03:04:05', '+1 month') AS a, date('2026-11-12', 'start of month', '-1 day') AS b,
265 strftime('%Y/%m/%d %H:%M', '2026-11-12 10:20:30') AS c, unixepoch('2026-01-01') AS d, date(1767225600, 'unixepoch') AS e,
266 julianday('2000-01-01 12:00:00') AS f")[0];
267 $test->assertSame(['a' => '2026-03-03 03:04:05', 'b' => '2026-10-31', 'c' => '2026/11/12 10:20', 'd' => 1767225600, 'e' => '2026-01-01', 'f' => 2451545.0], $r);
268 $r = $rows($db, "SELECT json_extract('{\"a\": [1, {\"b\": \"x\"}]}', '$.a[1].b') AS a, '{\"a\": {\"b\": 2}}' ->> '$.a.b' AS b,
269 json_object('k', 1) AS c, json_array(1, 'a', NULL) AS d")[0];
270 $test->assertSame(['a' => 'x', 'b' => 2, 'c' => '{"k":1}', 'd' => '[1,"a",null]'], $r);
271 });
272
273 $test->it('結合・集約・サブクエリ・複合', function($test) use ($open, $rows): void {
274 $db = $open('c.sqlite');
275 $db->query('CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INT, boss INT)');
276 $db->query("INSERT INTO emp (name, dept, salary, boss) VALUES ('A', 'eng', 100, NULL), ('B', 'eng', 80, 1), ('C', 'ops', 70, 1), ('D', 'ops', NULL, 3), ('E', NULL, 50, 3)");
277 $test->assertSame([['name' => 'A', 'boss' => null], ['name' => 'B', 'boss' => 'A'], ['name' => 'C', 'boss' => 'A'], ['name' => 'D', 'boss' => 'C'], ['name' => 'E', 'boss' => 'C']],
278 $rows($db, 'SELECT e.name, b.name AS boss FROM emp e LEFT JOIN emp b ON b.id = e.boss ORDER BY e.id'));
279 $test->assertSame([['dept' => null, 'n' => 1, 's' => 50, 'a' => 50.0], ['dept' => 'eng', 'n' => 2, 's' => 180, 'a' => 90.0], ['dept' => 'ops', 'n' => 2, 's' => 70, 'a' => 70.0]],
280 $rows($db, 'SELECT dept, count(*) AS n, sum(salary) AS s, avg(salary) AS a FROM emp GROUP BY dept'));
281 $test->assertSame([['dept' => 'eng', 's' => 180]], $rows($db, 'SELECT dept, sum(salary) AS s FROM emp GROUP BY dept HAVING s > 100'));
282 $test->assertSame([['name' => 'A', 'm' => 100]], $rows($db, 'SELECT name, max(salary) AS m FROM emp'), 'max()の行の他の列');
283 $test->assertSame(['A', 'B'], array_column($rows($db, 'SELECT name FROM emp WHERE salary > (SELECT avg(salary) FROM emp) ORDER BY name'), 'name'));
284 $test->assertSame(['A', 'C'], array_column($rows($db, 'SELECT name FROM emp e WHERE EXISTS (SELECT 1 FROM emp x WHERE x.boss = e.id) ORDER BY 1'), 'name'));
285 $test->assertSame([['name' => 'A', 'r' => 2], ['name' => 'C', 'r' => 2]],
286 $rows($db, 'SELECT name, (SELECT count(*) FROM emp x WHERE x.boss = emp.id) AS r FROM emp WHERE r > 0 ORDER BY name'));
287 $test->assertSame(['E', 'D', 'C', 'B', 'A'], array_column($rows($db, "SELECT name FROM emp WHERE dept = 'eng' UNION SELECT name FROM emp ORDER BY 1 DESC"), 'name'));
288 $test->assertSame([['dept' => 'eng'], ['dept' => 'ops']], $rows($db, 'SELECT DISTINCT dept FROM emp WHERE dept IS NOT NULL ORDER BY dept'));
289 $test->assertSame(['B', 'C'], array_column($rows($db, 'SELECT name FROM emp ORDER BY salary DESC NULLS LAST LIMIT 2 OFFSET 1'), 'name'));
290 $test->assertSame([['s' => 300]], $rows($db, 'WITH t AS (SELECT salary FROM emp WHERE salary IS NOT NULL) SELECT sum(salary) AS s FROM t'));
291 $test->assertSame([['column1' => 1, 'column2' => 'a'], ['column1' => 2, 'column2' => 'b']], $rows($db, "VALUES (1, 'a'), (2, 'b')"));
292 $res = $db->query('SELECT 1 AS a; SELECT 2 AS b');
293 $test->assertSame([['a' => 1]], $res['rows']);
294 $test->assertSame([['b' => 2]], $res['more'][0]['rows']);
295 $db->query('CREATE VIEW bosses AS SELECT DISTINCT b.name FROM emp e JOIN emp b ON b.id = e.boss');
296 $test->assertSame(['A', 'C'], array_column($rows($db, 'SELECT * FROM bosses ORDER BY name'), 'name'));
297 });
298
299 $test->it('インデックスを使って検索するはず（結果は同じ）', function($test) use ($open, $rows, $run): void {
300 $db = $open('d.sqlite');
301 $db->query('CREATE TABLE t (id INTEGER PRIMARY KEY, a INT, b TEXT COLLATE NOCASE, c REAL)');
302 $db->beginTransaction();
303 $ins = $db->prepare('INSERT INTO t (a, b, c) VALUES (?, ?, ?)');
304 for ($i = 0; $i < 3000; $i++) $db->execute($ins, [$i % 97, 'name'.($i % 50), $i / 7]);
305 $db->commit();
306 $before = [
307 $rows($db, 'SELECT id FROM t WHERE a = 5 ORDER BY id'), $rows($db, "SELECT id FROM t WHERE b = 'NAME7' ORDER BY id"),
308 $rows($db, 'SELECT id FROM t WHERE a BETWEEN 3 AND 4 AND c > 100 ORDER BY id'), $rows($db, 'SELECT id FROM t WHERE a IN (1, 2) ORDER BY id'),
309 $rows($db, 'SELECT count(*) AS n FROM t WHERE a > 90'), $rows($db, 'SELECT id FROM t WHERE id > 2990 OR id = 5 ORDER BY id'),
310 ];
311 $db->query('CREATE INDEX t_a ON t(a)');
312 $db->query('CREATE INDEX t_b ON t(b)');
313 $after = [
314 $rows($db, 'SELECT id FROM t WHERE a = 5 ORDER BY id'), $rows($db, "SELECT id FROM t WHERE b = 'NAME7' ORDER BY id"),
315 $rows($db, 'SELECT id FROM t WHERE a BETWEEN 3 AND 4 AND c > 100 ORDER BY id'), $rows($db, 'SELECT id FROM t WHERE a IN (1, 2) ORDER BY id'),
316 $rows($db, 'SELECT count(*) AS n FROM t WHERE a > 90'), $rows($db, 'SELECT id FROM t WHERE id > 2990 OR id = 5 ORDER BY id'),
317 ];
318 $test->assertSame($before, $after);
319 $test->assertCount(31, $after[0]);
320 $test->assertCount(60, $after[1]);
321 $test->assertSame([['ok' => 'ok']], $rows($db, 'SELECT integrity_check AS ok FROM (SELECT 1) JOIN (SELECT \'ok\' AS integrity_check)'));
322 // 速さ：インデックスで1000回
323 $q = $db->prepare('SELECT count(*) AS n FROM t WHERE a = ?');
324 $start = microtime(true);
325 for ($i = 0; $i < 1000; $i++) $n = $db->execute($q, [$i % 97])['rows'][0]['n'];
326 $test->assertTrue(microtime(true) - $start < 10, 'インデックスが遅い');
327 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
328 });
329
330 $test->it('制約と衝突の解決', function($test) use ($open, $rows, $one, $catch): void {
331 $db = $open('e.sqlite');
332 $db->query('CREATE TABLE u (id INTEGER PRIMARY KEY, n TEXT UNIQUE NOT NULL, c INT CHECK (c > 0) DEFAULT 1)');
333 $db->query("INSERT INTO u (n) VALUES ('a')");
334 foreach (["INSERT INTO u (n) VALUES ('a')" => 'UNIQUE制約に違反しました：u.n', 'INSERT INTO u (n) VALUES (NULL)' => 'NOT NULL制約に違反しました：u.n',
335 "INSERT INTO u (n, c) VALUES ('b', 0)" => 'CHECK制約に違反しました', "INSERT INTO u (id, n) VALUES (1, 'z')" => 'UNIQUE制約に違反しました：u.id',
336 "INSERT INTO u (id, n) VALUES ('x', 'z')" => 'データ型が違います'] as $sql => $msg) {
337 $e = $catch(fn() => $db->query($sql));
338 $test->assertTrue($e instanceof DbException, $sql);
339 $test->assertStringContains($msg, $e->getMessage());
340 }
341 $e = $catch(fn() => $db->query("INSERT INTO u (n) VALUES ('a')"));
342 $test->assertSame(19, $e->getCode());
343 $test->assertSame('23000', $e->sqlState);
344 // 文の途中で失敗したら、その文は全部取り消す
345 $catch(fn() => $db->query("INSERT INTO u (n) VALUES ('b'), ('c'), ('a')"));
346 $test->assertSame(1, $one($db, 'SELECT count(*) FROM u'));
347 $db->query("INSERT OR IGNORE INTO u (n) VALUES ('a'), ('b')");
348 $test->assertSame(['a', 'b'], array_column($rows($db, 'SELECT n FROM u ORDER BY id'), 'n'));
349 $db->query("INSERT OR REPLACE INTO u (n, c) VALUES ('a', 5)");
350 $test->assertSame([['id' => 2, 'n' => 'b', 'c' => 1], ['id' => 3, 'n' => 'a', 'c' => 5]], $rows($db, 'SELECT * FROM u ORDER BY id'));
351 $catch(fn() => $db->query("INSERT OR FAIL INTO u (n) VALUES ('x'), ('a'), ('y')"));
352 $test->assertSame(['a', 'b', 'x'], array_column($rows($db, 'SELECT n FROM u ORDER BY n'), 'n'), 'FAILは前の行を残す');
353 // ON CONFLICT、RETURNING
354 $res = $db->query("INSERT INTO u (n, c) VALUES ('a', 9) ON CONFLICT(n) DO UPDATE SET c = c + excluded.c RETURNING *");
355 $test->assertSame([], $res['rows']);
356 $test->assertSame(14, $one($db, "SELECT c FROM u WHERE n = 'a'"));
357 $db->query("INSERT INTO u (n) VALUES ('a') ON CONFLICT DO NOTHING");
358 $test->assertSame([['id' => 5, 'n' => 'z']], $db->query("INSERT INTO u (n) VALUES ('z') RETURNING id, n")['rows']);
359 $test->assertSame([['n' => 'z']], $db->query("DELETE FROM u WHERE id = 5 RETURNING n")['rows']);
360 $test->assertSame([['n' => 'b', 'c' => 2]], $db->query("UPDATE u SET c = 2 WHERE n = 'b' RETURNING n, c")['rows']);
361 $e = $catch(fn() => $db->query("UPDATE u SET n = 'a' WHERE n = 'b'"));
362 $test->assertStringContains('UNIQUE制約', $e->getMessage());
363 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
364 });
365
366 $test->it('トランザクションとセーブポイント', function($test) use ($open, $rows, $one, $catch, $tmp): void {
367 $db = $open('f.sqlite');
368 $db->query('CREATE TABLE t (v TEXT UNIQUE)');
369 $test->assertFalse($db->inTransaction());
370 $db->beginTransaction();
371 $test->assertTrue($db->inTransaction());
372 $db->query("INSERT INTO t VALUES ('a')");
373 $catch(fn() => $db->query("INSERT INTO t VALUES ('a')"));
374 $test->assertTrue($db->inTransaction(), 'エラーの後もトランザクションの中');
375 $db->query("INSERT INTO t VALUES ('b')");
376 $db->rollback();
377 $test->assertFalse($db->inTransaction());
378 $test->assertSame(0, $one($db, 'SELECT count(*) FROM t'));
379
380 $db->beginTransaction();
381 $db->query("INSERT INTO t VALUES ('a')");
382 $db->query('SAVEPOINT s1');
383 $db->query("INSERT INTO t VALUES ('b')");
384 $db->query('SAVEPOINT s2');
385 $db->query("INSERT INTO t VALUES ('c')");
386 $db->query('ROLLBACK TO s2');
387 $db->query("INSERT INTO t VALUES ('d')");
388 $db->query('RELEASE s1');
389 $db->commit();
390 $test->assertSame(['a', 'b', 'd'], array_column($rows($db, 'SELECT v FROM t ORDER BY v'), 'v'));
391 $e = $catch(fn() => $db->query('COMMIT'));
392 $test->assertStringContains('トランザクションが開始されていません', $e->getMessage());
393 // 他の接続から見える
394 $db2 = $open('f.sqlite');
395 $test->assertSame(3, $one($db2, 'SELECT count(*) FROM t'));
396
397 $tx = new Db(['driver' => 'sqlite', 'path' => $tmp.'/f.sqlite']);
398 $tx->transaction(fn(Db $d) => $d->query('INSERT INTO t VALUES (?)', ['e']));
399 $e = $catch(fn() => $tx->transaction(function(Db $d): void {
400 $d->query('INSERT INTO t VALUES (?)', ['f']);
401 throw new \RuntimeException('やめる');
402 }));
403 $test->assertSame('やめる', $e->getMessage());
404 $test->assertSame(['a', 'b', 'd', 'e'], array_column($rows($db, 'SELECT v FROM t ORDER BY v'), 'v'));
405 });
406
407 $test->it('同じファイルの2つの接続（ロック）', function($test) use ($open, $one, $catch): void {
408 $a = $open('g.sqlite');
409 $a->query('CREATE TABLE c (n INT)');
410 $a->query('INSERT INTO c VALUES (0)');
411 $b = $open('g.sqlite', ['busy_timeout' => 100]);
412 $test->assertSame(0, $one($b, 'SELECT n FROM c'));
413 $a->query('BEGIN IMMEDIATE');
414 $a->query('UPDATE c SET n = 1');
415 $e = $catch(fn() => $b->query('UPDATE c SET n = 2'));
416 $test->assertTrue($e instanceof DbException);
417 $test->assertStringContains('ロックされています', $e->getMessage());
418 $test->assertSame(5, $e->getCode());
419 $a->query('COMMIT');
420 $test->assertSame(1, $one($b, 'SELECT n FROM c'), '他の接続の変更が見える');
421 $b->query('UPDATE c SET n = n + 1');
422 $test->assertSame(2, $one($a, 'SELECT n FROM c'));
423 });
424
425 $test->it('スキーマを変更出来るはず', function($test) use ($open, $rows, $one, $catch): void {
426 $db = $open('h.sqlite');
427 $db->query('CREATE TABLE p (id INTEGER PRIMARY KEY, name TEXT, n INT)');
428 $db->query("INSERT INTO p (name, n) VALUES ('a', 1), ('b', 2)");
429 $db->query('CREATE INDEX p_n ON p(n)');
430 $db->query("ALTER TABLE p ADD COLUMN extra TEXT DEFAULT 'def'");
431 $test->assertSame(['def', 'def'], array_column($rows($db, 'SELECT extra FROM p'), 'extra'));
432 $db->query('ALTER TABLE p RENAME COLUMN name TO title');
433 $test->assertSame(['a', 'b'], array_column($rows($db, 'SELECT title FROM p ORDER BY id'), 'title'));
434 $db->query('ALTER TABLE p RENAME TO q');
435 $test->assertSame('CREATE INDEX p_n ON "q"(n)', $one($db, "SELECT sql FROM sqlite_master WHERE name = 'p_n'"));
436 $db->query('ALTER TABLE q DROP COLUMN extra');
437 $test->assertSame([['id' => 1, 'title' => 'a', 'n' => 1], ['id' => 2, 'title' => 'b', 'n' => 2]], $rows($db, 'SELECT * FROM q ORDER BY id'));
438 $test->assertStringContains('インデックスの有る列は消せません', $catch(fn() => $db->query('ALTER TABLE q DROP COLUMN n'))->getMessage());
439 $db->query('CREATE TABLE r AS SELECT id, title || \'!\' AS t FROM q');
440 $test->assertSame('CREATE TABLE r(id INT,t)', $one($db, "SELECT sql FROM sqlite_master WHERE name = 'r'"));
441 $test->assertSame([['cid' => 0, 'name' => 'id', 'type' => 'INTEGER', 'notnull' => 0, 'dflt_value' => null, 'pk' => 1],
442 ['cid' => 1, 'name' => 'title', 'type' => 'TEXT', 'notnull' => 0, 'dflt_value' => null, 'pk' => 0],
443 ['cid' => 2, 'name' => 'n', 'type' => 'INT', 'notnull' => 0, 'dflt_value' => null, 'pk' => 0]], $rows($db, 'PRAGMA table_info(q)'));
444 $test->assertSame([['seq' => 0, 'name' => 'p_n', 'unique' => 0, 'origin' => 'c', 'partial' => 0]], $rows($db, 'PRAGMA index_list(q)'));
445 $db->query('DROP INDEX p_n');
446 $db->query('DROP TABLE r');
447 $db->query('DROP TABLE IF EXISTS nope');
448 $test->assertStringContains('テーブルがありません', $catch(fn() => $db->query('DROP TABLE nope'))->getMessage());
449 $test->assertStringContains('既に有ります', $catch(fn() => $db->query('CREATE TABLE q (a)'))->getMessage());
450 $db->query('CREATE TABLE IF NOT EXISTS q (a)');
451 $test->assertSame([['name' => 'q']], $rows($db, "SELECT name FROM sqlite_master WHERE type = 'table'"));
452 $db->query('PRAGMA user_version = 7');
453 $test->assertSame(7, $one($db, 'PRAGMA user_version'));
454 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
455 });
456
457 $test->it('エラーと未対応の機能', function($test) use ($open, $catch, $tmp): void {
458 $db = $open('i.sqlite');
459 $db->query('CREATE TABLE t (a INT)');
460 foreach (['SELECT * FROM nope' => 'テーブルがありません：nope', 'SELECT b FROM t' => '列がありません：b', 'SELECT count(*) FROM t WHERE count(*) > 1' => 'WHEREに集約関数',
461 'SELECT nofunc(1)' => '関数がありません', 'SELECT 1 UNION SELECT 1, 2' => '列の数が違います', 'CREATE TABLE w (a) WITHOUT ROWID' => 'WITHOUT ROWID',
462 'SELECT * FROM t a JOIN t b' => '', 'ATTACH DATABASE \'x\' AS y' => '未対応', 'SELECT a FROM t a, t b' => '曖昧'] as $sql => $msg) {
463 $e = $catch(fn() => $db->query($sql));
464 if ($msg === '') {
465 $test->assertNull($e, $sql);
466 continue;
467 }
468 $test->assertTrue($e instanceof DbException, $sql);
469 $test->assertStringContains($msg, $e->getMessage());
470 }
471 $file = $tmp.'/notdb.sqlite';
472 file_put_contents($file, str_repeat('x', 4096));
473 $e = $catch(fn() => (new Sqlite(['path' => $file]))->connect());
474 $test->assertStringContains('SQLiteのデータベースではありません', $e->getMessage());
475 $e = $catch(fn() => (new Sqlite(['path' => $tmp.'/none.sqlite', 'create' => false]))->connect());
476 $test->assertStringContains('ファイルがありません', $e->getMessage());
477 $e = $catch(fn() => (new Sqlite(['path' => $tmp.'/nodir/x.sqlite']))->connect());
478 $test->assertStringContains('ディレクトリがありません', $e->getMessage());
479 $ro = $open('i.sqlite', ['readonly' => true]);
480 $test->assertStringContains('読み込み専用', $catch(fn() => $ro->query('INSERT INTO t VALUES (1)'))->getMessage());
481 $mem = new Sqlite(['path' => ':memory:']);
482 $mem->connect();
483 $mem->query('CREATE TABLE m (a)');
484 $mem->query('BEGIN');
485 $mem->query('INSERT INTO m VALUES (1)');
486 $mem->query('ROLLBACK');
487 $test->assertSame([['c' => 0]], $mem->query('SELECT count(*) AS c FROM m')['rows']);
488 $test->assertFalse(file_exists(':memory:'));
489 $closed = new Sqlite(['path' => ':memory:']);
490 $test->assertStringContains('接続していません', $catch(fn() => $closed->query('SELECT 1'))->getMessage());
491 $test->assertFalse($closed->ping());
492 });
493});
494
495$test->describe('SQLite（ファイル形式の互換性）', function($test) use ($fixtures, $tmp, $rows, $one, $pdo): void {
496 $test->it('本物のSQLiteが書いたファイルを読めるはず', function($test) use ($fixtures, $tmp, $rows, $one): void {
497 $file = $tmp.'/real.sqlite';
498 file_put_contents($file, base64_decode($fixtures['real']));
499 $db = new Sqlite(['path' => $file, 'readonly' => true]);
500 $db->connect();
501 $exp = $fixtures['expected'];
502 $test->assertSame($exp['items'], $rows($db, 'SELECT id, name, price, qty, hex(data) AS data, length(note) AS note, extra FROM items ORDER BY id'));
503 $test->assertSame($exp['tags'], $rows($db, 'SELECT count(*) AS c, max(tag) AS m FROM tags'));
504 $test->assertSame($exp['cheap'], $rows($db, 'SELECT * FROM cheap ORDER BY name'));
505 $test->assertSame($exp['byPrice'], array_column($rows($db, 'SELECT id FROM items WHERE price > 300 ORDER BY price DESC'), 'id'));
506 $test->assertSame($exp['seq'], $one($db, 'SELECT seq FROM sqlite_sequence'));
507 $test->assertSame(1024, $db->getServerInfo()['pageSize']);
508 $test->assertSame(42, $one($db, 'PRAGMA user_version'));
509 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
510 // NOCASEのUNIQUE
511 $test->assertSame(1, count($rows($db, "SELECT * FROM items WHERE name = '品物1'")));
512 });
513
514 $test->it('本物のSQLiteのファイルに書けるはず', function($test) use ($fixtures, $tmp, $rows, $one, $pdo): void {
515 $file = $tmp.'/real2.sqlite';
516 file_put_contents($file, base64_decode($fixtures['real']));
517 $db = new Sqlite(['path' => $file]);
518 $db->connect();
519 $db->beginTransaction();
520 for ($i = 0; $i < 200; $i++) $db->query("INSERT INTO items (name, price, note) VALUES ('新しい$i', $i, '".str_repeat('x', $i * 10)."')");
521 $db->query('DELETE FROM items WHERE id < 20');
522 $db->query("UPDATE items SET qty = 99 WHERE price < 50");
523 $db->commit();
524 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
525 $test->assertSame($fixtures['expected']['seq'] + 200, $one($db, 'SELECT seq FROM sqlite_sequence'));
526 $p = $pdo($file);
527 if ($p === null) {
528 $test->skip('本物のSQLiteで確認', 'pdo_sqliteが無い');
529 return;
530 }
531 $test->assertSame('ok', $p->query('PRAGMA integrity_check')->fetchColumn());
532 $test->assertSame($one($db, 'SELECT count(*) FROM items'), (int)$p->query('SELECT count(*) FROM items')->fetchColumn());
533 $test->assertSame(md5(json_encode($rows($db, 'SELECT * FROM items ORDER BY id'))), md5(json_encode($p->query('SELECT * FROM items ORDER BY id')->fetchAll(\PDO::FETCH_ASSOC))));
534 });
535
536 $test->it('途中で止まった書き込み（本物のSQLiteのジャーナル）を元に戻すはず', function($test) use ($fixtures, $tmp, $rows): void {
537 $file = $tmp.'/hot.sqlite';
538 file_put_contents($file, base64_decode($fixtures['hot']));
539 file_put_contents($file.'-journal', base64_decode($fixtures['hotJournal']));
540 $db = new Sqlite(['path' => $file]);
541 $db->connect();
542 $test->assertSame([$fixtures['expected']['hot']], $rows($db, 'SELECT count(*) AS c, group_concat(v) AS g FROM t'));
543 $test->assertFalse(file_exists($file.'-journal'));
544 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
545 });
546
547 $test->it('自分のジャーナルも元に戻せるはず（途中でファイルに書いた場合）', function($test) use ($tmp, $rows, $one, $pdo): void {
548 $file = $tmp.'/spill.sqlite';
549 $db = new Sqlite(['path' => $file]);
550 $db->connect();
551 $db->query('CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)');
552 $db->query("INSERT INTO t (v) VALUES ('元')");
553 $pg = new Pager($file);
554 $pg->spillPages = 5;
555 $bt = new Btree($pg);
556 $pg->beginRead();
557 $pg->beginWrite();
558 $root = Record::decode($bt->get(1, 1))[3];
559 for ($i = 2; $i < 300; $i++) $bt->insert($root, $i, Record::encode([null, str_repeat('壊', 100)]));
560 $bt->insert($root, 1, Record::encode([null, '壊れた']));
561 $bt->flush();
562 $test->assertTrue(file_exists($file.'-journal'), 'ジャーナルに書いた');
563 // ここで止まった事にする
564 copy($file, $tmp.'/spill2.sqlite');
565 copy($file.'-journal', $tmp.'/spill2.sqlite-journal');
566 $pg->rollback();
567 $test->assertSame([['v' => '元']], $rows($db, 'SELECT v FROM t'), 'ロールバック');
568 $test->assertFalse(file_exists($file.'-journal'));
569 $db2 = new Sqlite(['path' => $tmp.'/spill2.sqlite']);
570 $db2->connect();
571 $test->assertSame([['v' => '元']], $rows($db2, 'SELECT v FROM t'), '途中で止まった');
572 $test->assertSame([['integrity_check' => 'ok']], $rows($db2, 'PRAGMA integrity_check'));
573 $pg->close();
574 });
575
576 $test->it('このクラスが書いたファイルを本物のSQLiteが読めるはず', function($test) use ($tmp, $rows, $pdo): void {
577 $file = $tmp.'/mine.sqlite';
578 $db = new Sqlite(['path' => $file]);
579 $db->connect();
580 $db->query('CREATE TABLE a (id INTEGER PRIMARY KEY, n TEXT UNIQUE, x REAL, b BLOB)');
581 $db->query('CREATE INDEX a_x ON a(x DESC)');
582 $db->beginTransaction();
583 $ins = $db->prepare('INSERT INTO a (n, x, b) VALUES (?, ?, ?)');
584 for ($i = 0; $i < 1500; $i++) $db->execute($ins, ["名前$i", $i / 3, new Blob(str_repeat(chr($i % 256), $i % 700))]);
585 $db->commit();
586 $db->query('DELETE FROM a WHERE id % 3 = 0');
587 $db->query('UPDATE a SET n = n || \'!\' WHERE id % 5 = 0');
588 $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
589 $p = $pdo($file);
590 if ($p === null) {
591 $test->skip('本物のSQLiteで確認', 'pdo_sqliteが無い');
592 return;
593 }
594 $test->assertSame('ok', $p->query('PRAGMA integrity_check')->fetchColumn());
595 $test->assertSame(1000, (int)$p->query('SELECT count(*) FROM a')->fetchColumn());
596 $test->assertSame(md5(json_encode($rows($db, 'SELECT * FROM a ORDER BY id'))), md5(json_encode($p->query('SELECT * FROM a ORDER BY id')->fetchAll(\PDO::FETCH_ASSOC))));
597 $test->assertSame($rows($db, 'SELECT count(*) AS c FROM a WHERE x > 200')[0]['c'], (int)$p->query('SELECT count(*) FROM a INDEXED BY a_x WHERE x > 200')->fetchColumn());
598 });
599
600 $test->it('Db経由で使えるはず', function($test) use ($tmp): void {
601 $test->assertTrue(Db::isSupported('sqlite'));
602 $test->assertTrue(Db::isSupported('SQLite3'));
603 $db = new Db(['driver' => 'sqlite', 'path' => $tmp.'/db.sqlite']);
604 $test->assertTrue($db->driver() instanceof Sqlite);
605 $db->query('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
606 $test->assertSame(1, $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId']);
607 $test->assertSame([['name' => '諏訪子']], $db->query('SELECT name FROM users WHERE id = ?', [1])['rows']);
608 $test->assertSame('"a""b"', $db->quoteIdentifier('a"b'));
609 $db->close();
610 $test->assertFalse($db->isConnected());
611 });
612});
613
614$test->printSummary();