<?php
/*************************************************************
# 076 License

Copyright (c) テクニカル諏訪子

Permission is hereby granted to any person obtaining a copy of the software
Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
sublicense, and/or sell copies of the Software, subject to the following conditions:

    1. **Origin Attribution**:
       - You must not misrepresent the origin of the Software; you must not claim
         you created the original Software.
       - If the Software is used in a product, you must either:
         a. Provide clear attribution in the product's documentation, user interface,
            or other visible areas, **OR**
         b. Pay the original developers a fee they specify in writing.
    2. **Usage Restriction**:
       - The Software, or any derivative works, dependencies, or libraries
         incorporating it, must not be used for censorship or to suppress freedom of
         speech, expression, or creativity. Prohibited uses include, but are not
         limited to:
         - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
           ideas, or objective reality.
         - Tools or systems designed to restrict access to information or
           artistic works.
    3. **Notice Preservation**:
       - This license and the above copyright notice must remain intact in all copies
         of the source code.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
namespace Std\Test;

require_once __DIR__.'/../../../autoload.php';

use Std\Lib\Tester;
use Std\Lib\Db;
use Std\Lib\Db\DbException;
use Std\Lib\Db\Mysql;
use Std\Test\Helper\MockServer;
use Std\Test\Helper\TestCert;

$test = new Tester([
  'colorOutput' => true,
  'verboseOutput' => true
]);

enum LibDbColor: string {
  case Red = 'あか';
}

// 例外を受け取る（コード・SQLSTATE・メッセージを確認する為）
$catch = function(callable $fn): ?\Throwable {
  try {
    $fn();
  } catch (\Throwable $e) {
    return $e;
  }
  return null;
};


$test->describe('Db（ドライバの選択）', function($test) use ($catch): void {
  $test->it('DB_ENABLEDが無効なら設定なしで作れないはず', function($test): void {
    if (defined('DB_ENABLED') && DB_ENABLED) {
      $test->assertNotNull(new Db());
      return;
    }
    $test->assertThrows(fn() => new Db(), \Exception::class);
    $test->assertThrows(fn() => new Mysql(), \Exception::class);
  });

  $test->it('ドライバを選べるはず（省略するとmysql）', function($test): void {
    $db = new Db(['username' => 'lb']);
    $test->assertSame('mysql', $db->driverName);
    $test->assertTrue($db->driver() instanceof Mysql);
    $test->assertTrue((new Db(['driver' => 'MariaDB']))->driver() instanceof Mysql);
    $test->assertFalse($db->isConnected());
  });

  $test->it('未対応のドライバは例外のはず', function($test) use ($catch): void {
    $e = $catch(fn() => new Db(['driver' => 'oracle']));
    $test->assertTrue($e instanceof \RuntimeException);
    $test->assertStringContains('oracle', $e->getMessage());
    $test->assertTrue(Db::isSupported('mysql'));
    $test->assertTrue(Db::isSupported('MARIADB'));
    $test->assertFalse(Db::isSupported('oracle'));
  });

  $test->it('未対応の文字セットは例外のはず', function($test): void {
    $test->assertThrows(fn() => new Mysql(['charset' => 'klingon']), \InvalidArgumentException::class);
  });

  $test->it('識別子を囲めるはず', function($test): void {
    $db = new Db([]);
    $test->assertSame('`users`', $db->quoteIdentifier('users'));
    $test->assertSame('`a``b`', $db->quoteIdentifier('a`b'));
  });

  $test->it('接続していなければ例外のはず', function($test) use ($catch): void {
    $my = new Mysql([]);
    foreach ([fn() => $my->query('SELECT 1'), fn() => $my->prepare('SELECT 1'), fn() => $my->execute(1), fn() => $my->selectDatabase('lb')] as $fn) {
      $e = $catch($fn);
      $test->assertTrue($e instanceof DbException);
      $test->assertStringContains('接続していません', $e->getMessage());
    }
    $test->assertFalse($my->ping());
    $test->assertFalse($my->inTransaction());
    $test->assertFalse($my->demolish(1));
  });

  $test->it('サーバーが無ければ接続に失敗するはず', function($test) use ($catch): void {
    $probe = stream_socket_server('tcp://127.0.0.1:0');
    $port = (int)substr(strrchr(stream_socket_get_name($probe, false), ':'), 1);
    fclose($probe);

    $e = $catch(fn() => (new Mysql(['port' => $port, 'timeout' => 2]))->connect());
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('ソケットに接続に失敗', $e->getMessage());
  });
});

// TLS・RSA用の証明書（RSAの鍵、CAが発行した物）
$cert = TestCert::get('rsa');

$test->describe('Mysql（偽物のMySQLサーバー）', function($test) use ($catch, $cert): void {
  $srv = MockServer::start('mysql-server.php', ['mysql_native_password', $cert['cert'], $cert['key']]);
  if ($srv === null) {
    $test->skipAll('偽物のサーバーを起動出来ない（proc_openが無効、又はpdo_sqliteが無い？）');
    return;
  }

  $info = fn(array $o = []): array => $o + [
    'host' => '127.0.0.1',
    'port' => $srv->port,
    'username' => 'lb',
    'password' => 'ひみつ',
    'dbname' => 'lb',
    'timeout' => 5,
  ];
  $open = function(array $o = []) use ($info): Mysql {
    $my = new Mysql($info($o));
    $my->connect();
    return $my;
  };
  $one = fn(Mysql $my, string $sql) => array_values($my->query($sql)['rows'][0] ?? [])[0] ?? null;
  // プリペアドステートメントで1回だけ実行する
  $run = function(Mysql $my, string $sql, array $params = []): array {
    $id = $my->prepare($sql);
    try {
      return $my->execute($id, $params);
    } finally {
      $my->demolish($id);
    }
  };

  $test->it('接続して切断出来るはず', function($test) use ($open, $one): void {
    $my = $open();
    $test->assertTrue($my->isConnected());
    $test->assertTrue($my->connect()); // 2回目は何もしない
    $info = $my->getServerInfo();
    $test->assertSame(10, $info['protocol']);
    $test->assertSame('8.4.0-lbmock', $info['version']);
    $test->assertSame('mysql_native_password', $info['authPlugin']);
    $test->assertFalse(isset($info['scramble']));
    $test->assertSame('lb', $one($my, 'SELECT DATABASE()'));
    $test->assertSame($info['threadId'], $one($my, 'SELECT CONNECTION_ID()'));
    $test->assertSame('utf8mb4', $one($my, 'SELECT @@character_set_client'));
    $test->assertTrue($my->ping());

    $my->close();
    $test->assertFalse($my->isConnected());
    $test->assertFalse($my->ping());
    $my->close(); // 2回目は何もしない
  });

  $test->it('正しい機能フラグを送るはず（TLSを求めない、PROTOCOL_41等）', function($test) use ($open, $one): void {
    $caps = $one($open(), 'SELECT @@lb_caps');
    $test->assertSame(0, $caps & 0x800, 'TLSなしでCLIENT_SSLを送った');
    foreach ([0x200, 0x8000, 0x2000, 0x20000, 0x80000, 0x200000, 0x8] as $flag) {
      $test->assertSame($flag, $caps & $flag, sprintf('0x%X が無い', $flag));
    }
    $test->assertSame(0, $one($open(['dbname' => '']), 'SELECT @@lb_caps') & 0x8, 'データベースなしでCONNECT_WITH_DBを送った');
  });

  $test->it('間違ったパスワード・ユーザー・データベースは例外のはず', function($test) use ($info, $catch): void {
    foreach ([['password' => 'ちがう'], ['username' => 'nobody'], ['password' => '']] as $o) {
      $my = new Mysql($info($o));
      $e = $catch(fn() => $my->connect());
      $test->assertTrue($e instanceof DbException);
      $test->assertSame(1045, $e->getCode());
      $test->assertSame('28000', $e->sqlState);
      $test->assertStringContains('認証に失敗: 1045 - Access denied', $e->getMessage());
      $test->assertFalse($my->isConnected());
    }

    $e = $catch(fn() => (new Mysql($info(['dbname' => 'nodb'])))->connect());
    $test->assertSame(1049, $e->getCode());
  });

  $test->it('パスワードなしのユーザーで接続出来るはず', function($test) use ($open, $one): void {
    $test->assertSame(1, $one($open(['username' => 'nopw', 'password' => '']), 'SELECT 1'));
  });

  $test->it('文字セットを指定出来るはず', function($test) use ($open, $one): void {
    $test->assertSame('sjis', $one($open(['charset' => 'sjis']), 'SELECT @@character_set_client'));
    $test->assertSame('cp932', $one($open(['charset' => 'CP932']), 'SELECT @@character_set_client'));
  });

  $test->it('データベースを選択出来るはず', function($test) use ($open, $one, $catch): void {
    $my = $open(['dbname' => '']);
    $test->assertNull($one($my, 'SELECT DATABASE()'));
    $test->assertTrue($my->selectDatabase('other'));
    $test->assertSame('other', $my->dbname);
    $test->assertSame('other', $one($my, 'SELECT DATABASE()'));

    $e = $catch(fn() => $my->selectDatabase('nodb'));
    $test->assertSame(1049, $e->getCode());
    $test->assertStringContains("データベースの選択に失敗: 1049 - Unknown database 'nodb'", $e->getMessage());
    $test->assertTrue($my->isConnected());
  });

  $test->it('caching_sha2_password（Auth Switch、RSA、高速認証）で接続出来るはず', function($test) use ($open, $one, $cert, $info, $catch): void {
    // 1回目：キャッシュに無いので、公開鍵を求めて暗号化する
    $my = $open(['username' => 'sha2', 'password' => 'sha2ひみつ']);
    $test->assertSame('caching_sha2_password', $my->getServerInfo()['authPlugin']);
    $test->assertSame(1, $one($my, 'SELECT @@lb_full_auth'));
    // 2回目：高速認証
    $test->assertSame(0, $one($open(['username' => 'sha2', 'password' => 'sha2ひみつ']), 'SELECT @@lb_full_auth'));
    // 間違ったパスワード
    $e = $catch(fn() => $open(['username' => 'sha2', 'password' => 'x']));
    $test->assertSame(1045, $e->getCode());
  });

  $test->it('sha256_password（設定した公開鍵、又はサーバーから）で接続出来るはず', function($test) use ($open, $one, $cert): void {
    $test->assertSame('sha256_password', $one($open(['username' => 'sha256', 'password' => 'sha256ひみつ']), 'SELECT @@lb_plugin'));
    $test->assertSame(1, $one($open(['username' => 'sha256', 'password' => 'sha256ひみつ', 'pubkey' => $cert['pub']]), 'SELECT @@lb_full_auth'));
  });

  $test->it('TLSで接続出来るはず（証明書を確認する）', function($test) use ($open, $one, $cert, $catch): void {
    $tls = ['cafile' => $cert['ca']];
    $my = $open(['tls' => $tls]);
    $test->assertSame(1, $one($my, 'SELECT @@lb_tls'));
    $test->assertSame('諏訪子', $my->query("SELECT '諏訪子' AS n")['rows'][0]['n']);
    $test->assertSame(0, $one($open(), 'SELECT @@lb_tls'));

    // TLSならパスワードをそのまま送れる
    $test->assertSame(1, $one($open(['tls' => $tls, 'username' => 'clear', 'password' => 'clearひみつ']), 'SELECT @@lb_tls'));
    $test->assertSame(1, $one($open(['tls' => $tls, 'username' => 'sha256', 'password' => 'sha256ひみつ']), 'SELECT 1'));

    // 信頼していない証明書
    $e = $catch(fn() => $open(['tls' => true]));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('TLSの開始に失敗', $e->getMessage());
  });

  $test->it('TLSなしでmysql_clear_passwordを使わないはず', function($test) use ($open, $catch): void {
    $e = $catch(fn() => $open(['username' => 'clear', 'password' => 'clearひみつ']));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('TLS', $e->getMessage());
  });

  $test->it('テーブルを作って、データを入れて読めるはず（テキストプロトコル）', function($test) use ($open): void {
    $my = $open();
    $my->query('DROP TABLE IF EXISTS items');
    $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');
    $test->assertSame(0, $res['affectedRows']);
    $test->assertSame([], $res['rows']);

    $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}')");
    $test->assertSame(1, $res['affectedRows']);
    $test->assertSame(1, $res['insertId']);
    $test->assertSame(2, $my->query("INSERT INTO items (name) VALUES ('神奈子')")['insertId']);

    $res = $my->query('SELECT * FROM items ORDER BY id');
    $test->assertCount(2, $res['rows']);
    $test->assertCount(16, $res['fields']);
    $test->assertSame('items', $res['fields'][0]['table']);
    $test->assertSame('id', $res['fields'][0]['name']);
    $test->assertSame(Mysql::TYPE_LONG, $res['fields'][0]['type']);
    $test->assertSame(Mysql::TYPE_NEWDECIMAL, $res['fields'][2]['type']);
    $test->assertSame(2, $res['fields'][2]['decimals']);

    $test->assertSame([
      'id' => 1, 'name' => '諏訪子', 'price' => '12.50', 'ratio' => 0.1, 'f' => 1.5, 'tiny' => -5, 'small' => -300,
      'big' => 9007199254740993, 'd' => '2026-11-12', 'dt' => '2026-11-12 10:20:30.123456', 'tm' => '-838:59:59',
      'y' => 2026, 'b' => "\x00\xff\x10", 'txt' => 'テキスト', 'j' => '{"a":1}', 'note' => null,
    ], $res['rows'][0]);
    $test->assertNull($res['rows'][1]['price']);

    $res = $my->query("UPDATE items SET note = 'x'");
    $test->assertSame(2, $res['affectedRows']);
    $test->assertSame(0, $res['insertId']);
  });

  $test->it('プリペアドステートメントの結果はテキストと同じはず（バイナリプロトコル）', function($test) use ($open, $run): void {
    $my = $open();
    $text = $my->query('SELECT * FROM items ORDER BY id')['rows'];
    $bin = $run($my, 'SELECT * FROM items WHERE id > ? ORDER BY id', [0]);
    $test->assertSame($text, $bin['rows']);
    $test->assertSame('items', $bin['fields'][0]['table']);

    $test->assertSame('神奈子', $run($my, 'SELECT name FROM items WHERE id = ?', [2])['rows'][0]['name']);
    $test->assertSame([], $run($my, 'SELECT name FROM items WHERE id = ?', [99])['rows']);
  });

  $test->it('日付と時間の色々な形を読めるはず', function($test) use ($open, $run): void {
    $my = $open();
    $my->query('DROP TABLE IF EXISTS dates');
    $my->query('CREATE TABLE dates (id INT AUTO_INCREMENT PRIMARY KEY, d DATE, dt DATETIME, dt3 DATETIME(3), tm TIME, tm6 TIME(6))');
    $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')");
    $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')");

    $text = $my->query('SELECT d, dt, dt3, tm, tm6 FROM dates ORDER BY id')['rows'];
    $test->assertSame([
      ['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'],
      ['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'],
    ], $text);
    $test->assertSame($text, $run($my, 'SELECT d, dt, dt3, tm, tm6 FROM dates WHERE id > ? ORDER BY id', [0])['rows']);
  });

  $test->it('PHPの色々な型をパラメータとして送れるはず', function($test) use ($open, $run, $catch): void {
    $my = $open();
    $cases = [
      [42, 42],
      [-42, -42],
      [PHP_INT_MAX, PHP_INT_MAX],
      [PHP_INT_MIN, PHP_INT_MIN],
      [1.25, 1.25],
      [-0.1, -0.1],
      [true, 1],
      [false, 0],
      [null, null],
      ['', ''],
      ['諏訪子', '諏訪子'],
      ["\x00\x01バイナリ\xff", "\x00\x01バイナリ\xff"],
      [new \DateTimeImmutable('2026-11-12 10:20:30.5'), '2026-11-12 10:20:30.500000'],
      [LibDbColor::Red, 'あか'],
    ];
    foreach ($cases as [$in, $out]) {
      $test->assertSame($out, $run($my, 'SELECT ? AS v', [$in])['rows'][0]['v'], 'パラメータ：'.var_export($in, true));
    }

    $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]);
    $test->assertSame(['a' => 1, 'b' => null, 'c' => 'x', 'd' => null, 'e' => null, 'f' => null, 'g' => null, 'h' => null, 'i' => 9], $res['rows'][0]);

    // 連想配列でも順番通り
    $test->assertSame(['a' => 1, 'b' => 2], $run($my, 'SELECT ? AS a, ? AS b', ['x' => 1, 'y' => 2])['rows'][0]);

    $e = $catch(fn() => $run($my, 'SELECT ? AS v', [[1, 2]]));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('未対応', $e->getMessage());
  });

  $test->it('プリペアドステートメントを何回も実行出来るはず', function($test) use ($open, $one, $catch): void {
    $my = $open();
    $my->query('DROP TABLE IF EXISTS logs');
    $my->query('CREATE TABLE logs (id INT AUTO_INCREMENT PRIMARY KEY, msg TEXT, n INT)');

    $id = $my->prepare('INSERT INTO logs (msg, n) VALUES (?, ?)');
    $ids = [];
    foreach (['一', '二', '三'] as $i => $msg) {
      $ids[] = $my->execute($id, [$msg, $i])['insertId'];
    }
    $test->assertSame([1, 2, 3], $ids);
    $test->assertSame(3, $one($my, 'SELECT COUNT(*) FROM logs'));

    $e = $catch(fn() => $my->execute($id, ['足りない']));
    $test->assertStringContains('パラメータ数が一致しません: 期待 2, 取得 1', $e->getMessage());

    $test->assertTrue($my->demolish($id));
    $test->assertFalse($my->demolish($id));
    $e = $catch(fn() => $my->execute($id, ['x', 1]));
    $test->assertStringContains("不正なステートメントID: {$id}", $e->getMessage());
  });

  $test->it('SQLのエラーは例外で、その後も使えるはず', function($test) use ($open, $one, $catch): void {
    $my = $open();
    $e = $catch(fn() => $my->query('SELECT * FROM nope'));
    $test->assertTrue($e instanceof DbException);
    $test->assertSame(1146, $e->getCode());
    $test->assertSame('42S02', $e->sqlState);
    $test->assertSame("クエリに失敗: 1146 - Table 'lb.nope' doesn't exist", $e->getMessage());

    $e = $catch(fn() => $my->query('SELEC 1'));
    $test->assertSame(1064, $e->getCode());

    $e = $catch(fn() => $my->prepare('SELECT * FROM nope WHERE id = ?'));
    $test->assertStringContains('準備に失敗: 1146', $e->getMessage());

    $my->query('DROP TABLE IF EXISTS uniq');
    $my->query('CREATE TABLE uniq (name VARCHAR(10) UNIQUE)');
    $my->query("INSERT INTO uniq VALUES ('a')");
    $id = $my->prepare('INSERT INTO uniq VALUES (?)');
    $e = $catch(fn() => $my->execute($id, ['a']));
    $test->assertSame(1062, $e->getCode());
    $test->assertSame('23000', $e->sqlState);
    $test->assertStringContains('実行に失敗: 1062', $e->getMessage());

    $test->assertTrue($my->isConnected());
    $test->assertSame(1, $one($my, 'SELECT COUNT(*) FROM uniq'));
  });

  $test->it('長い値（長さエンコードの全ての大きさ）を送受信出来るはず', function($test) use ($open, $run): void {
    $my = $open();
    $my->query('DROP TABLE IF EXISTS big');
    $my->query('CREATE TABLE big (id INT AUTO_INCREMENT PRIMARY KEY, v LONGBLOB)');
    foreach ([250, 251, 300, 65535, 65536, 70000] as $len) {
      $v = str_repeat('あ', intdiv($len, 3)).str_repeat('x', $len % 3);
      $id = $run($my, 'INSERT INTO big (v) VALUES (?)', [$v])['insertId'];
      $test->assertSame($v, $my->query("SELECT v FROM big WHERE id = {$id}")['rows'][0]['v'], "{$len}バイト（テキスト）");
      $test->assertSame($v, $run($my, 'SELECT v FROM big WHERE id = ?', [$id])['rows'][0]['v'], "{$len}バイト（バイナリ）");
    }
  });

  $test->it('16MiB以上のパケットを送受信出来るはず', function($test) use ($open, $run): void {
    $my = $open(['timeout' => 30]);
    $v = str_repeat("0123456789abcdef", 0x110000); // 17MiB
    $id = $run($my, 'INSERT INTO big (v) VALUES (?)', [$v])['insertId'];
    $test->assertSame(strlen($v), strlen($my->query("SELECT v FROM big WHERE id = {$id}")['rows'][0]['v']));
    $test->assertTrue($v === $run($my, 'SELECT v FROM big WHERE id = ?', [$id])['rows'][0]['v']);
    // 次のコマンドの順序番号が正しいか
    $test->assertTrue($my->ping());
  });

  $test->it('結果が複数ある場合はmoreに入るはず（CALL）', function($test) use ($open, $one): void {
    $my = $open();
    $res = $my->query('CALL lb_multi()');
    $test->assertSame([['a' => 1]], $res['rows']);
    $test->assertCount(2, $res['more']);
    $test->assertSame([['b' => '二']], $res['more'][0]['rows']);
    $test->assertSame([], $res['more'][1]['rows']);
    // 全部読んだので、次のクエリが使える
    $test->assertSame(2, $one($my, 'SELECT 2'));
    $test->assertFalse(isset($my->query('SELECT 1')['more']));
  });

  $test->it('トランザクションを使えるはず', function($test) use ($open, $one): void {
    $my = $open();
    $my->query('DROP TABLE IF EXISTS tx');
    $my->query('CREATE TABLE tx (n INT)');
    $test->assertFalse($my->inTransaction());

    $test->assertTrue($my->beginTransaction());
    $test->assertTrue($my->inTransaction());
    $my->query('INSERT INTO tx VALUES (1)');
    $test->assertTrue($my->rollback());
    $test->assertFalse($my->inTransaction());
    $test->assertSame(0, $one($my, 'SELECT COUNT(*) FROM tx'));

    $my->beginTransaction();
    $my->query('INSERT INTO tx VALUES (2)');
    $test->assertTrue($my->commit());
    $test->assertFalse($my->inTransaction());
    $test->assertSame(2, $one($my, 'SELECT n FROM tx'));
  });

  $test->it('接続が切れたら例外で、また接続出来るはず', function($test) use ($open, $one, $catch): void {
    $my = $open();
    $e = $catch(fn() => $my->query('SELECT LB_DISCONNECT()'));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('サーバーとの接続が切れました', $e->getMessage());
    $test->assertFalse($my->isConnected());
    $test->assertFalse($my->ping());

    $test->assertTrue($my->connect());
    $test->assertSame(1, $one($my, 'SELECT 1'));
  });

  $test->it('返事が遅すぎたらタイムアウトするはず', function($test) use ($open, $catch): void {
    $my = $open(['timeout' => 1]);
    $e = $catch(fn() => $my->query('SELECT LB_SLEEP(2)'));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('タイムアウト', $e->getMessage());
    $test->assertFalse($my->isConnected());
  });

  $test->it('デバッグモードでパケットを記録するはず', function($test) use ($open): void {
    $my = $open();
    ob_start();
    $my->setDebug(true)->query('SELECT 1 AS a');
    $out = ob_get_clean();
    $my->setDebug(false);

    $log = $my->getPacketLog();
    $test->assertSame('SEND', $log[0]['direction']);
    $test->assertSame("\x03SELECT 1 AS a", $log[0]['data']);
    $test->assertSame(0, $log[0]['seqNum']);
    $test->assertSame('RECV', $log[1]['direction']);
    $test->assertSame(1, $log[1]['seqNum']);
    $test->assertStringContains('=== SEND パケット', $out);
    $test->assertStringContains('解釈: フィールド説明パケット', $out);

    $file = 'lbtest_mysql_'.bin2hex(random_bytes(4)).'.txt';
    $bytes = $my->savePacketLogToFile($file);
    $test->assertTrue(is_int($bytes) && $bytes > 0);
    $saved = (string)file_get_contents(ROOT.'/log/'.$file);
    @unlink(ROOT.'/log/'.$file);
    $test->assertStringContains('=== パケット #0', $saved);
    $test->assertStringContains('ASCII: .SELECT 1 AS a', $saved);
  });

  $test->it('Db経由で使えるはず（自動で接続、パラメータ、トランザクション）', function($test) use ($info, $catch): void {
    $db = new Db($info());
    $test->assertFalse($db->isConnected());
    $db->query('DROP TABLE IF EXISTS users');
    $test->assertTrue($db->isConnected());
    $db->query('CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20), points INT)');

    $id = $db->query('INSERT INTO users (name, points) VALUES (?, ?)', ['諏訪子', 100])['insertId'];
    $test->assertSame(1, $id);
    $db->query('INSERT INTO users (name, points) VALUES (?, ?)', ['神奈子', 0]);
    $test->assertSame([['name' => '諏訪子']], $db->query('SELECT name FROM users WHERE points > ?', [50])['rows']);

    // コミット（戻り値はそのまま）
    $res = $db->transaction(function(Db $db): string {
      $db->query('UPDATE users SET points = points - ? WHERE id = ?', [30, 1]);
      $db->query('UPDATE users SET points = points + ? WHERE id = ?', [30, 2]);
      return '完了';
    });
    $test->assertSame('完了', $res);
    $test->assertFalse($db->inTransaction());
    $test->assertSame([70, 30], array_column($db->query('SELECT points FROM users ORDER BY id')['rows'], 'points'));

    // 例外ならロールバックして投げ直す
    $e = $catch(fn() => $db->transaction(function(Db $db): void {
      $db->query('UPDATE users SET points = 0');
      throw new \LogicException('やめた');
    }));
    $test->assertTrue($e instanceof \LogicException);
    $test->assertFalse($db->inTransaction());
    $test->assertSame([70, 30], array_column($db->query('SELECT points FROM users ORDER BY id')['rows'], 'points'));

    // 準備したステートメントを繰り返す
    $st = $db->prepare('SELECT name FROM users WHERE id = ?');
    $test->assertSame('諏訪子', $db->execute($st, [1])['rows'][0]['name']);
    $test->assertSame('神奈子', $db->execute($st, [2])['rows'][0]['name']);
    $test->assertTrue($db->demolish($st));

    // query()のパラメータ付きはステートメントを解放する（失敗しても）
    $e = $catch(fn() => $db->query('SELECT * FROM nope WHERE id = ?', [1]));
    $test->assertSame(1146, $e->getCode());
    $test->assertTrue($db->ping());

    $db->beginTransaction();
    $test->assertTrue($db->inTransaction());
    $db->rollback();
    $db->close();
    $test->assertFalse($db->isConnected());
  });

  $srv->stop();
});

$test->describe('Mysql（caching_sha2_passwordが既定、TLSなしのサーバー）', function($test) use ($catch): void {
  $srv = MockServer::start('mysql-server.php', ['caching_sha2_password']);
  if ($srv === null) {
    $test->skipAll('偽物のサーバーを起動出来ない（proc_openが無効、又はpdo_sqliteが無い？）');
    return;
  }
  $info = fn(array $o = []): array => $o + ['host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'timeout' => 5];

  $test->it('mysql_native_passwordのユーザーに切り替えられるはず', function($test) use ($info): void {
    $my = new Mysql($info());
    $my->connect();
    $test->assertSame('caching_sha2_password', $my->getServerInfo()['plugin']);
    $test->assertSame('mysql_native_password', $my->getServerInfo()['authPlugin']);
  });

  $test->it('caching_sha2_passwordの空のパスワードは追加の認証が無いはず', function($test) use ($info): void {
    $my = new Mysql($info(['username' => 'nopw', 'password' => '']));
    $test->assertTrue($my->connect());
  });

  $test->it('TLSに対応していないサーバーでTLSを求めたら例外のはず', function($test) use ($info, $catch): void {
    $e = $catch(fn() => (new Mysql($info(['tls' => true])))->connect());
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('サーバーがTLSに対応していません', $e->getMessage());
  });

  $test->it('サーバーが公開鍵を出せなければ認証に失敗するはず', function($test) use ($info, $catch): void {
    $e = $catch(fn() => (new Mysql($info(['username' => 'sha2', 'password' => 'sha2ひみつ'])))->connect());
    $test->assertTrue($e instanceof DbException);
    $test->assertFalse(str_contains($e->getMessage(), 'sha2ひみつ'), 'パスワードがメッセージに入った');
  });

  $srv->stop();
});

$test->printSummary();