suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Test / LibDb.php
28.2 KB、596行 生 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\Mysql;
46use Std\Test\Helper\MockServer;
47use Std\Test\Helper\TestCert;
48
49$test = new Tester([
50 'colorOutput' => true,
51 'verboseOutput' => true
52]);
53
54enum LibDbColor: string {
55 case Red = 'あか';
56}
57
58// 例外を受け取る（コード・SQLSTATE・メッセージを確認する為）
59$catch = function(callable $fn): ?\Throwable {
60 try {
61 $fn();
62 } catch (\Throwable $e) {
63 return $e;
64 }
65 return null;
66};
67
68
69$test->describe('Db（ドライバの選択）', function($test) use ($catch): void {
70 $test->it('DB_ENABLEDが無効なら設定なしで作れないはず', function($test): void {
71 if (defined('DB_ENABLED') && DB_ENABLED) {
72 $test->assertNotNull(new Db());
73 return;
74 }
75 $test->assertThrows(fn() => new Db(), \Exception::class);
76 $test->assertThrows(fn() => new Mysql(), \Exception::class);
77 });
78
79 $test->it('ドライバを選べるはず（省略するとmysql）', function($test): void {
80 $db = new Db(['username' => 'lb']);
81 $test->assertSame('mysql', $db->driverName);
82 $test->assertTrue($db->driver() instanceof Mysql);
83 $test->assertTrue((new Db(['driver' => 'MariaDB']))->driver() instanceof Mysql);
84 $test->assertFalse($db->isConnected());
85 });
86
87 $test->it('未対応のドライバは例外のはず', function($test) use ($catch): void {
88 $e = $catch(fn() => new Db(['driver' => 'oracle']));
89 $test->assertTrue($e instanceof \RuntimeException);
90 $test->assertStringContains('oracle', $e->getMessage());
91 $test->assertTrue(Db::isSupported('mysql'));
92 $test->assertTrue(Db::isSupported('MARIADB'));
93 $test->assertFalse(Db::isSupported('oracle'));
94 });
95
96 $test->it('未対応の文字セットは例外のはず', function($test): void {
97 $test->assertThrows(fn() => new Mysql(['charset' => 'klingon']), \InvalidArgumentException::class);
98 });
99
100 $test->it('識別子を囲めるはず', function($test): void {
101 $db = new Db([]);
102 $test->assertSame('`users`', $db->quoteIdentifier('users'));
103 $test->assertSame('`a``b`', $db->quoteIdentifier('a`b'));
104 });
105
106 $test->it('接続していなければ例外のはず', function($test) use ($catch): void {
107 $my = new Mysql([]);
108 foreach ([fn() => $my->query('SELECT 1'), fn() => $my->prepare('SELECT 1'), fn() => $my->execute(1), fn() => $my->selectDatabase('lb')] as $fn) {
109 $e = $catch($fn);
110 $test->assertTrue($e instanceof DbException);
111 $test->assertStringContains('接続していません', $e->getMessage());
112 }
113 $test->assertFalse($my->ping());
114 $test->assertFalse($my->inTransaction());
115 $test->assertFalse($my->demolish(1));
116 });
117
118 $test->it('サーバーが無ければ接続に失敗するはず', function($test) use ($catch): void {
119 $probe = stream_socket_server('tcp://127.0.0.1:0');
120 $port = (int)substr(strrchr(stream_socket_get_name($probe, false), ':'), 1);
121 fclose($probe);
122
123 $e = $catch(fn() => (new Mysql(['port' => $port, 'timeout' => 2]))->connect());
124 $test->assertTrue($e instanceof DbException);
125 $test->assertStringContains('ソケットに接続に失敗', $e->getMessage());
126 });
127});
128
129// TLS・RSA用の証明書（RSAの鍵、CAが発行した物）
130$cert = TestCert::get('rsa');
131
132$test->describe('Mysql（偽物のMySQLサーバー）', function($test) use ($catch, $cert): void {
133 $srv = MockServer::start('mysql-server.php', ['mysql_native_password', $cert['cert'], $cert['key']]);
134 if ($srv === null) {
135 $test->skipAll('偽物のサーバーを起動出来ない（proc_openが無効、又はpdo_sqliteが無い？）');
136 return;
137 }
138
139 $info = fn(array $o = []): array => $o + [
140 'host' => '127.0.0.1',
141 'port' => $srv->port,
142 'username' => 'lb',
143 'password' => 'ひみつ',
144 'dbname' => 'lb',
145 'timeout' => 5,
146 ];
147 $open = function(array $o = []) use ($info): Mysql {
148 $my = new Mysql($info($o));
149 $my->connect();
150 return $my;
151 };
152 $one = fn(Mysql $my, string $sql) => array_values($my->query($sql)['rows'][0] ?? [])[0] ?? null;
153 // プリペアドステートメントで1回だけ実行する
154 $run = function(Mysql $my, string $sql, array $params = []): array {
155 $id = $my->prepare($sql);
156 try {
157 return $my->execute($id, $params);
158 } finally {
159 $my->demolish($id);
160 }
161 };
162
163 $test->it('接続して切断出来るはず', function($test) use ($open, $one): void {
164 $my = $open();
165 $test->assertTrue($my->isConnected());
166 $test->assertTrue($my->connect()); // 2回目は何もしない
167 $info = $my->getServerInfo();
168 $test->assertSame(10, $info['protocol']);
169 $test->assertSame('8.4.0-lbmock', $info['version']);
170 $test->assertSame('mysql_native_password', $info['authPlugin']);
171 $test->assertFalse(isset($info['scramble']));
172 $test->assertSame('lb', $one($my, 'SELECT DATABASE()'));
173 $test->assertSame($info['threadId'], $one($my, 'SELECT CONNECTION_ID()'));
174 $test->assertSame('utf8mb4', $one($my, 'SELECT @@character_set_client'));
175 $test->assertTrue($my->ping());
176
177 $my->close();
178 $test->assertFalse($my->isConnected());
179 $test->assertFalse($my->ping());
180 $my->close(); // 2回目は何もしない
181 });
182
183 $test->it('正しい機能フラグを送るはず（TLSを求めない、PROTOCOL_41等）', function($test) use ($open, $one): void {
184 $caps = $one($open(), 'SELECT @@lb_caps');
185 $test->assertSame(0, $caps & 0x800, 'TLSなしでCLIENT_SSLを送った');
186 foreach ([0x200, 0x8000, 0x2000, 0x20000, 0x80000, 0x200000, 0x8] as $flag) {
187 $test->assertSame($flag, $caps & $flag, sprintf('0x%X が無い', $flag));
188 }
189 $test->assertSame(0, $one($open(['dbname' => '']), 'SELECT @@lb_caps') & 0x8, 'データベースなしでCONNECT_WITH_DBを送った');
190 });
191
192 $test->it('間違ったパスワード・ユーザー・データベースは例外のはず', function($test) use ($info, $catch): void {
193 foreach ([['password' => 'ちがう'], ['username' => 'nobody'], ['password' => '']] as $o) {
194 $my = new Mysql($info($o));
195 $e = $catch(fn() => $my->connect());
196 $test->assertTrue($e instanceof DbException);
197 $test->assertSame(1045, $e->getCode());
198 $test->assertSame('28000', $e->sqlState);
199 $test->assertStringContains('認証に失敗: 1045 - Access denied', $e->getMessage());
200 $test->assertFalse($my->isConnected());
201 }
202
203 $e = $catch(fn() => (new Mysql($info(['dbname' => 'nodb'])))->connect());
204 $test->assertSame(1049, $e->getCode());
205 });
206
207 $test->it('パスワードなしのユーザーで接続出来るはず', function($test) use ($open, $one): void {
208 $test->assertSame(1, $one($open(['username' => 'nopw', 'password' => '']), 'SELECT 1'));
209 });
210
211 $test->it('文字セットを指定出来るはず', function($test) use ($open, $one): void {
212 $test->assertSame('sjis', $one($open(['charset' => 'sjis']), 'SELECT @@character_set_client'));
213 $test->assertSame('cp932', $one($open(['charset' => 'CP932']), 'SELECT @@character_set_client'));
214 });
215
216 $test->it('データベースを選択出来るはず', function($test) use ($open, $one, $catch): void {
217 $my = $open(['dbname' => '']);
218 $test->assertNull($one($my, 'SELECT DATABASE()'));
219 $test->assertTrue($my->selectDatabase('other'));
220 $test->assertSame('other', $my->dbname);
221 $test->assertSame('other', $one($my, 'SELECT DATABASE()'));
222
223 $e = $catch(fn() => $my->selectDatabase('nodb'));
224 $test->assertSame(1049, $e->getCode());
225 $test->assertStringContains("データベースの選択に失敗: 1049 - Unknown database 'nodb'", $e->getMessage());
226 $test->assertTrue($my->isConnected());
227 });
228
229 $test->it('caching_sha2_password（Auth Switch、RSA、高速認証）で接続出来るはず', function($test) use ($open, $one, $cert, $info, $catch): void {
230 // 1回目：キャッシュに無いので、公開鍵を求めて暗号化する
231 $my = $open(['username' => 'sha2', 'password' => 'sha2ひみつ']);
232 $test->assertSame('caching_sha2_password', $my->getServerInfo()['authPlugin']);
233 $test->assertSame(1, $one($my, 'SELECT @@lb_full_auth'));
234 // 2回目：高速認証
235 $test->assertSame(0, $one($open(['username' => 'sha2', 'password' => 'sha2ひみつ']), 'SELECT @@lb_full_auth'));
236 // 間違ったパスワード
237 $e = $catch(fn() => $open(['username' => 'sha2', 'password' => 'x']));
238 $test->assertSame(1045, $e->getCode());
239 });
240
241 $test->it('sha256_password（設定した公開鍵、又はサーバーから）で接続出来るはず', function($test) use ($open, $one, $cert): void {
242 $test->assertSame('sha256_password', $one($open(['username' => 'sha256', 'password' => 'sha256ひみつ']), 'SELECT @@lb_plugin'));
243 $test->assertSame(1, $one($open(['username' => 'sha256', 'password' => 'sha256ひみつ', 'pubkey' => $cert['pub']]), 'SELECT @@lb_full_auth'));
244 });
245
246 $test->it('TLSで接続出来るはず（証明書を確認する）', function($test) use ($open, $one, $cert, $catch): void {
247 $tls = ['cafile' => $cert['ca']];
248 $my = $open(['tls' => $tls]);
249 $test->assertSame(1, $one($my, 'SELECT @@lb_tls'));
250 $test->assertSame('諏訪子', $my->query("SELECT '諏訪子' AS n")['rows'][0]['n']);
251 $test->assertSame(0, $one($open(), 'SELECT @@lb_tls'));
252
253 // TLSならパスワードをそのまま送れる
254 $test->assertSame(1, $one($open(['tls' => $tls, 'username' => 'clear', 'password' => 'clearひみつ']), 'SELECT @@lb_tls'));
255 $test->assertSame(1, $one($open(['tls' => $tls, 'username' => 'sha256', 'password' => 'sha256ひみつ']), 'SELECT 1'));
256
257 // 信頼していない証明書
258 $e = $catch(fn() => $open(['tls' => true]));
259 $test->assertTrue($e instanceof DbException);
260 $test->assertStringContains('TLSの開始に失敗', $e->getMessage());
261 });
262
263 $test->it('TLSなしでmysql_clear_passwordを使わないはず', function($test) use ($open, $catch): void {
264 $e = $catch(fn() => $open(['username' => 'clear', 'password' => 'clearひみつ']));
265 $test->assertTrue($e instanceof DbException);
266 $test->assertStringContains('TLS', $e->getMessage());
267 });
268
269 $test->it('テーブルを作って、データを入れて読めるはず（テキストプロトコル）', function($test) use ($open): void {
270 $my = $open();
271 $my->query('DROP TABLE IF EXISTS items');
272 $res = $my->query('CREATE TABLE items (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(10,2), ratio DOUBLE, f FLOAT, tiny TINYINT, small SMALLINT, big BIGINT, d DATE, dt DATETIME(6), tm TIME, y YEAR, b BLOB, txt TEXT, j JSON, note VARCHAR(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');
273 $test->assertSame(0, $res['affectedRows']);
274 $test->assertSame([], $res['rows']);
275
276 $res = $my->query("INSERT INTO items (name, price, ratio, f, tiny, small, big, d, dt, tm, y, b, txt, j) VALUES ('諏訪子', 12.5, 0.1, 1.5, -5, -300, 9007199254740993, '2026-11-12', '2026-11-12 10:20:30.123456', '-838:59:59', 2026, x'00ff10', 'テキスト', '{\"a\":1}')");
277 $test->assertSame(1, $res['affectedRows']);
278 $test->assertSame(1, $res['insertId']);
279 $test->assertSame(2, $my->query("INSERT INTO items (name) VALUES ('神奈子')")['insertId']);
280
281 $res = $my->query('SELECT * FROM items ORDER BY id');
282 $test->assertCount(2, $res['rows']);
283 $test->assertCount(16, $res['fields']);
284 $test->assertSame('items', $res['fields'][0]['table']);
285 $test->assertSame('id', $res['fields'][0]['name']);
286 $test->assertSame(Mysql::TYPE_LONG, $res['fields'][0]['type']);
287 $test->assertSame(Mysql::TYPE_NEWDECIMAL, $res['fields'][2]['type']);
288 $test->assertSame(2, $res['fields'][2]['decimals']);
289
290 $test->assertSame([
291 'id' => 1, 'name' => '諏訪子', 'price' => '12.50', 'ratio' => 0.1, 'f' => 1.5, 'tiny' => -5, 'small' => -300,
292 'big' => 9007199254740993, 'd' => '2026-11-12', 'dt' => '2026-11-12 10:20:30.123456', 'tm' => '-838:59:59',
293 'y' => 2026, 'b' => "\x00\xff\x10", 'txt' => 'テキスト', 'j' => '{"a":1}', 'note' => null,
294 ], $res['rows'][0]);
295 $test->assertNull($res['rows'][1]['price']);
296
297 $res = $my->query("UPDATE items SET note = 'x'");
298 $test->assertSame(2, $res['affectedRows']);
299 $test->assertSame(0, $res['insertId']);
300 });
301
302 $test->it('プリペアドステートメントの結果はテキストと同じはず（バイナリプロトコル）', function($test) use ($open, $run): void {
303 $my = $open();
304 $text = $my->query('SELECT * FROM items ORDER BY id')['rows'];
305 $bin = $run($my, 'SELECT * FROM items WHERE id > ? ORDER BY id', [0]);
306 $test->assertSame($text, $bin['rows']);
307 $test->assertSame('items', $bin['fields'][0]['table']);
308
309 $test->assertSame('神奈子', $run($my, 'SELECT name FROM items WHERE id = ?', [2])['rows'][0]['name']);
310 $test->assertSame([], $run($my, 'SELECT name FROM items WHERE id = ?', [99])['rows']);
311 });
312
313 $test->it('日付と時間の色々な形を読めるはず', function($test) use ($open, $run): void {
314 $my = $open();
315 $my->query('DROP TABLE IF EXISTS dates');
316 $my->query('CREATE TABLE dates (id INT AUTO_INCREMENT PRIMARY KEY, d DATE, dt DATETIME, dt3 DATETIME(3), tm TIME, tm6 TIME(6))');
317 $my->query("INSERT INTO dates (d, dt, dt3, tm, tm6) VALUES ('0000-00-00', '2026-01-02 00:00:00', '2026-01-02 03:04:05.5', '00:00:00', '123:45:06.000007')");
318 $my->query("INSERT INTO dates (d, dt, dt3, tm, tm6) VALUES ('1999-12-31', '0000-00-00 00:00:00', '2026-01-02 03:04:05', '-01:02:03', '-00:00:00.5')");
319
320 $text = $my->query('SELECT d, dt, dt3, tm, tm6 FROM dates ORDER BY id')['rows'];
321 $test->assertSame([
322 ['d' => '0000-00-00', 'dt' => '2026-01-02 00:00:00', 'dt3' => '2026-01-02 03:04:05.500', 'tm' => '00:00:00', 'tm6' => '123:45:06.000007'],
323 ['d' => '1999-12-31', 'dt' => '0000-00-00 00:00:00', 'dt3' => '2026-01-02 03:04:05.000', 'tm' => '-01:02:03', 'tm6' => '-00:00:00.500000'],
324 ], $text);
325 $test->assertSame($text, $run($my, 'SELECT d, dt, dt3, tm, tm6 FROM dates WHERE id > ? ORDER BY id', [0])['rows']);
326 });
327
328 $test->it('PHPの色々な型をパラメータとして送れるはず', function($test) use ($open, $run, $catch): void {
329 $my = $open();
330 $cases = [
331 [42, 42],
332 [-42, -42],
333 [PHP_INT_MAX, PHP_INT_MAX],
334 [PHP_INT_MIN, PHP_INT_MIN],
335 [1.25, 1.25],
336 [-0.1, -0.1],
337 [true, 1],
338 [false, 0],
339 [null, null],
340 ['', ''],
341 ['諏訪子', '諏訪子'],
342 ["\x00\x01バイナリ\xff", "\x00\x01バイナリ\xff"],
343 [new \DateTimeImmutable('2026-11-12 10:20:30.5'), '2026-11-12 10:20:30.500000'],
344 [LibDbColor::Red, 'あか'],
345 ];
346 foreach ($cases as [$in, $out]) {
347 $test->assertSame($out, $run($my, 'SELECT ? AS v', [$in])['rows'][0]['v'], 'パラメータ：'.var_export($in, true));
348 }
349
350 $res = $run($my, 'SELECT ? AS a, ? AS b, ? AS c, ? AS d, ? AS e, ? AS f, ? AS g, ? AS h, ? AS i', [1, null, 'x', null, null, null, null, null, 9]);
351 $test->assertSame(['a' => 1, 'b' => null, 'c' => 'x', 'd' => null, 'e' => null, 'f' => null, 'g' => null, 'h' => null, 'i' => 9], $res['rows'][0]);
352
353 // 連想配列でも順番通り
354 $test->assertSame(['a' => 1, 'b' => 2], $run($my, 'SELECT ? AS a, ? AS b', ['x' => 1, 'y' => 2])['rows'][0]);
355
356 $e = $catch(fn() => $run($my, 'SELECT ? AS v', [[1, 2]]));
357 $test->assertTrue($e instanceof DbException);
358 $test->assertStringContains('未対応', $e->getMessage());
359 });
360
361 $test->it('プリペアドステートメントを何回も実行出来るはず', function($test) use ($open, $one, $catch): void {
362 $my = $open();
363 $my->query('DROP TABLE IF EXISTS logs');
364 $my->query('CREATE TABLE logs (id INT AUTO_INCREMENT PRIMARY KEY, msg TEXT, n INT)');
365
366 $id = $my->prepare('INSERT INTO logs (msg, n) VALUES (?, ?)');
367 $ids = [];
368 foreach (['一', '二', '三'] as $i => $msg) {
369 $ids[] = $my->execute($id, [$msg, $i])['insertId'];
370 }
371 $test->assertSame([1, 2, 3], $ids);
372 $test->assertSame(3, $one($my, 'SELECT COUNT(*) FROM logs'));
373
374 $e = $catch(fn() => $my->execute($id, ['足りない']));
375 $test->assertStringContains('パラメータ数が一致しません: 期待 2, 取得 1', $e->getMessage());
376
377 $test->assertTrue($my->demolish($id));
378 $test->assertFalse($my->demolish($id));
379 $e = $catch(fn() => $my->execute($id, ['x', 1]));
380 $test->assertStringContains("不正なステートメントID: {$id}", $e->getMessage());
381 });
382
383 $test->it('SQLのエラーは例外で、その後も使えるはず', function($test) use ($open, $one, $catch): void {
384 $my = $open();
385 $e = $catch(fn() => $my->query('SELECT * FROM nope'));
386 $test->assertTrue($e instanceof DbException);
387 $test->assertSame(1146, $e->getCode());
388 $test->assertSame('42S02', $e->sqlState);
389 $test->assertSame("クエリに失敗: 1146 - Table 'lb.nope' doesn't exist", $e->getMessage());
390
391 $e = $catch(fn() => $my->query('SELEC 1'));
392 $test->assertSame(1064, $e->getCode());
393
394 $e = $catch(fn() => $my->prepare('SELECT * FROM nope WHERE id = ?'));
395 $test->assertStringContains('準備に失敗: 1146', $e->getMessage());
396
397 $my->query('DROP TABLE IF EXISTS uniq');
398 $my->query('CREATE TABLE uniq (name VARCHAR(10) UNIQUE)');
399 $my->query("INSERT INTO uniq VALUES ('a')");
400 $id = $my->prepare('INSERT INTO uniq VALUES (?)');
401 $e = $catch(fn() => $my->execute($id, ['a']));
402 $test->assertSame(1062, $e->getCode());
403 $test->assertSame('23000', $e->sqlState);
404 $test->assertStringContains('実行に失敗: 1062', $e->getMessage());
405
406 $test->assertTrue($my->isConnected());
407 $test->assertSame(1, $one($my, 'SELECT COUNT(*) FROM uniq'));
408 });
409
410 $test->it('長い値（長さエンコードの全ての大きさ）を送受信出来るはず', function($test) use ($open, $run): void {
411 $my = $open();
412 $my->query('DROP TABLE IF EXISTS big');
413 $my->query('CREATE TABLE big (id INT AUTO_INCREMENT PRIMARY KEY, v LONGBLOB)');
414 foreach ([250, 251, 300, 65535, 65536, 70000] as $len) {
415 $v = str_repeat('あ', intdiv($len, 3)).str_repeat('x', $len % 3);
416 $id = $run($my, 'INSERT INTO big (v) VALUES (?)', [$v])['insertId'];
417 $test->assertSame($v, $my->query("SELECT v FROM big WHERE id = {$id}")['rows'][0]['v'], "{$len}バイト（テキスト）");
418 $test->assertSame($v, $run($my, 'SELECT v FROM big WHERE id = ?', [$id])['rows'][0]['v'], "{$len}バイト（バイナリ）");
419 }
420 });
421
422 $test->it('16MiB以上のパケットを送受信出来るはず', function($test) use ($open, $run): void {
423 $my = $open(['timeout' => 30]);
424 $v = str_repeat("0123456789abcdef", 0x110000); // 17MiB
425 $id = $run($my, 'INSERT INTO big (v) VALUES (?)', [$v])['insertId'];
426 $test->assertSame(strlen($v), strlen($my->query("SELECT v FROM big WHERE id = {$id}")['rows'][0]['v']));
427 $test->assertTrue($v === $run($my, 'SELECT v FROM big WHERE id = ?', [$id])['rows'][0]['v']);
428 // 次のコマンドの順序番号が正しいか
429 $test->assertTrue($my->ping());
430 });
431
432 $test->it('結果が複数ある場合はmoreに入るはず（CALL）', function($test) use ($open, $one): void {
433 $my = $open();
434 $res = $my->query('CALL lb_multi()');
435 $test->assertSame([['a' => 1]], $res['rows']);
436 $test->assertCount(2, $res['more']);
437 $test->assertSame([['b' => '二']], $res['more'][0]['rows']);
438 $test->assertSame([], $res['more'][1]['rows']);
439 // 全部読んだので、次のクエリが使える
440 $test->assertSame(2, $one($my, 'SELECT 2'));
441 $test->assertFalse(isset($my->query('SELECT 1')['more']));
442 });
443
444 $test->it('トランザクションを使えるはず', function($test) use ($open, $one): void {
445 $my = $open();
446 $my->query('DROP TABLE IF EXISTS tx');
447 $my->query('CREATE TABLE tx (n INT)');
448 $test->assertFalse($my->inTransaction());
449
450 $test->assertTrue($my->beginTransaction());
451 $test->assertTrue($my->inTransaction());
452 $my->query('INSERT INTO tx VALUES (1)');
453 $test->assertTrue($my->rollback());
454 $test->assertFalse($my->inTransaction());
455 $test->assertSame(0, $one($my, 'SELECT COUNT(*) FROM tx'));
456
457 $my->beginTransaction();
458 $my->query('INSERT INTO tx VALUES (2)');
459 $test->assertTrue($my->commit());
460 $test->assertFalse($my->inTransaction());
461 $test->assertSame(2, $one($my, 'SELECT n FROM tx'));
462 });
463
464 $test->it('接続が切れたら例外で、また接続出来るはず', function($test) use ($open, $one, $catch): void {
465 $my = $open();
466 $e = $catch(fn() => $my->query('SELECT LB_DISCONNECT()'));
467 $test->assertTrue($e instanceof DbException);
468 $test->assertStringContains('サーバーとの接続が切れました', $e->getMessage());
469 $test->assertFalse($my->isConnected());
470 $test->assertFalse($my->ping());
471
472 $test->assertTrue($my->connect());
473 $test->assertSame(1, $one($my, 'SELECT 1'));
474 });
475
476 $test->it('返事が遅すぎたらタイムアウトするはず', function($test) use ($open, $catch): void {
477 $my = $open(['timeout' => 1]);
478 $e = $catch(fn() => $my->query('SELECT LB_SLEEP(2)'));
479 $test->assertTrue($e instanceof DbException);
480 $test->assertStringContains('タイムアウト', $e->getMessage());
481 $test->assertFalse($my->isConnected());
482 });
483
484 $test->it('デバッグモードでパケットを記録するはず', function($test) use ($open): void {
485 $my = $open();
486 ob_start();
487 $my->setDebug(true)->query('SELECT 1 AS a');
488 $out = ob_get_clean();
489 $my->setDebug(false);
490
491 $log = $my->getPacketLog();
492 $test->assertSame('SEND', $log[0]['direction']);
493 $test->assertSame("\x03SELECT 1 AS a", $log[0]['data']);
494 $test->assertSame(0, $log[0]['seqNum']);
495 $test->assertSame('RECV', $log[1]['direction']);
496 $test->assertSame(1, $log[1]['seqNum']);
497 $test->assertStringContains('=== SEND パケット', $out);
498 $test->assertStringContains('解釈: フィールド説明パケット', $out);
499
500 $file = 'lbtest_mysql_'.bin2hex(random_bytes(4)).'.txt';
501 $bytes = $my->savePacketLogToFile($file);
502 $test->assertTrue(is_int($bytes) && $bytes > 0);
503 $saved = (string)file_get_contents(ROOT.'/log/'.$file);
504 @unlink(ROOT.'/log/'.$file);
505 $test->assertStringContains('=== パケット #0', $saved);
506 $test->assertStringContains('ASCII: .SELECT 1 AS a', $saved);
507 });
508
509 $test->it('Db経由で使えるはず（自動で接続、パラメータ、トランザクション）', function($test) use ($info, $catch): void {
510 $db = new Db($info());
511 $test->assertFalse($db->isConnected());
512 $db->query('DROP TABLE IF EXISTS users');
513 $test->assertTrue($db->isConnected());
514 $db->query('CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), points INT)');
515
516 $id = $db->query('INSERT INTO users (name, points) VALUES (?, ?)', ['諏訪子', 100])['insertId'];
517 $test->assertSame(1, $id);
518 $db->query('INSERT INTO users (name, points) VALUES (?, ?)', ['神奈子', 0]);
519 $test->assertSame([['name' => '諏訪子']], $db->query('SELECT name FROM users WHERE points > ?', [50])['rows']);
520
521 // コミット（戻り値はそのまま）
522 $res = $db->transaction(function(Db $db): string {
523 $db->query('UPDATE users SET points = points - ? WHERE id = ?', [30, 1]);
524 $db->query('UPDATE users SET points = points + ? WHERE id = ?', [30, 2]);
525 return '完了';
526 });
527 $test->assertSame('完了', $res);
528 $test->assertFalse($db->inTransaction());
529 $test->assertSame([70, 30], array_column($db->query('SELECT points FROM users ORDER BY id')['rows'], 'points'));
530
531 // 例外ならロールバックして投げ直す
532 $e = $catch(fn() => $db->transaction(function(Db $db): void {
533 $db->query('UPDATE users SET points = 0');
534 throw new \LogicException('やめた');
535 }));
536 $test->assertTrue($e instanceof \LogicException);
537 $test->assertFalse($db->inTransaction());
538 $test->assertSame([70, 30], array_column($db->query('SELECT points FROM users ORDER BY id')['rows'], 'points'));
539
540 // 準備したステートメントを繰り返す
541 $st = $db->prepare('SELECT name FROM users WHERE id = ?');
542 $test->assertSame('諏訪子', $db->execute($st, [1])['rows'][0]['name']);
543 $test->assertSame('神奈子', $db->execute($st, [2])['rows'][0]['name']);
544 $test->assertTrue($db->demolish($st));
545
546 // query()のパラメータ付きはステートメントを解放する（失敗しても）
547 $e = $catch(fn() => $db->query('SELECT * FROM nope WHERE id = ?', [1]));
548 $test->assertSame(1146, $e->getCode());
549 $test->assertTrue($db->ping());
550
551 $db->beginTransaction();
552 $test->assertTrue($db->inTransaction());
553 $db->rollback();
554 $db->close();
555 $test->assertFalse($db->isConnected());
556 });
557
558 $srv->stop();
559});
560
561$test->describe('Mysql（caching_sha2_passwordが既定、TLSなしのサーバー）', function($test) use ($catch): void {
562 $srv = MockServer::start('mysql-server.php', ['caching_sha2_password']);
563 if ($srv === null) {
564 $test->skipAll('偽物のサーバーを起動出来ない（proc_openが無効、又はpdo_sqliteが無い？）');
565 return;
566 }
567 $info = fn(array $o = []): array => $o + ['host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'timeout' => 5];
568
569 $test->it('mysql_native_passwordのユーザーに切り替えられるはず', function($test) use ($info): void {
570 $my = new Mysql($info());
571 $my->connect();
572 $test->assertSame('caching_sha2_password', $my->getServerInfo()['plugin']);
573 $test->assertSame('mysql_native_password', $my->getServerInfo()['authPlugin']);
574 });
575
576 $test->it('caching_sha2_passwordの空のパスワードは追加の認証が無いはず', function($test) use ($info): void {
577 $my = new Mysql($info(['username' => 'nopw', 'password' => '']));
578 $test->assertTrue($my->connect());
579 });
580
581 $test->it('TLSに対応していないサーバーでTLSを求めたら例外のはず', function($test) use ($info, $catch): void {
582 $e = $catch(fn() => (new Mysql($info(['tls' => true])))->connect());
583 $test->assertTrue($e instanceof DbException);
584 $test->assertStringContains('サーバーがTLSに対応していません', $e->getMessage());
585 });
586
587 $test->it('サーバーが公開鍵を出せなければ認証に失敗するはず', function($test) use ($info, $catch): void {
588 $e = $catch(fn() => (new Mysql($info(['username' => 'sha2', 'password' => 'sha2ひみつ'])))->connect());
589 $test->assertTrue($e instanceof DbException);
590 $test->assertFalse(str_contains($e->getMessage(), 'sha2ひみつ'), 'パスワードがメッセージに入った');
591 });
592
593 $srv->stop();
594});
595
596$test->printSummary();