<?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\Convert;
use Std\Lib\Db\Convert\DbSource;
use Std\Lib\Db\Convert\DbTarget;
use Std\Lib\Db\Convert\DumpSource;
use Std\Lib\Db\Convert\DumpTarget;
use Std\Lib\Db\Convert\JsonSource;
use Std\Lib\Db\Convert\JsonTarget;
use Std\Lib\Db\Convert\Sql;
use Std\Lib\Db\Convert\Stores;
use Std\Lib\Db\DbException;
use Std\Lib\Db\Sqlite\Blob;
use Std\Test\Helper\MockServer;

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

// 本物のサーバーでも試す場合（任意、lb_test_のテーブルを作って消す）：LB_TEST_PGSQL=pgsql://ユーザー:パスワード@ホスト:ポート/データベース

$tmp = sys_get_temp_dir().'/lbconvert_'.bin2hex(random_bytes(6));
mkdir($tmp, 0700);
register_shutdown_function(function() use ($tmp): void {
  $it = new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($tmp, \FilesystemIterator::SKIP_DOTS), \RecursiveIteratorIterator::CHILD_FIRST);
  foreach ($it as $f) $f->isDir() ? @rmdir($f->getPathname()) : @unlink($f->getPathname());
  @rmdir($tmp);
});
$fixtures = json_decode((string)file_get_contents(__DIR__.'/Helper/convert.json'), true);

$catch = function(callable $fn): ?\Throwable {
  try {
    $fn();
  } catch (\Throwable $e) {
    return $e;
  }
  return null;
};
$sqlite = fn(string $name): Db => new Db(['driver' => 'sqlite', 'path' => $tmp.'/'.$name]);
// 比べられる形の行（BLOBは16進数）
$dump = function(Db $db, string $sql): array {
  return array_map(fn($r) => array_map(fn($v) => $v instanceof Blob ? 'x'.bin2hex($v->data) : (is_string($v) && !mb_check_encoding($v, 'UTF-8') ? 'x'.bin2hex($v) : $v), $r), $db->query($sql)['rows']);
};
// 色々な型の元のSQLiteのデータベース
$sample = function(string $name) use ($sqlite): Db {
  $db = $sqlite($name);
  $db->query('CREATE TABLE users (id INTEGER PRIMARY KEY, name VARCHAR(100) NOT NULL, email TEXT, active BOOLEAN NOT NULL DEFAULT 1, score NUMERIC(10,2) DEFAULT 0,
    ratio REAL, created DATETIME DEFAULT CURRENT_TIMESTAMP, born DATE, avatar BLOB, prefs JSON)');
  $db->query('CREATE UNIQUE INDEX users_name ON users (name)');
  $db->query('CREATE INDEX users_email ON users (email)');
  $db->query('CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, title TEXT NOT NULL, body TEXT, published DATETIME)');
  $db->query('CREATE INDEX posts_user ON posts (user_id, published)');
  $db->query('CREATE TABLE kv (k TEXT NOT NULL, n INTEGER NOT NULL, v TEXT, PRIMARY KEY (k, n))');
  $ins = 'INSERT INTO users (name, email, active, score, ratio, created, born, avatar, prefs) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)';
  $db->query($ins, ['諏訪子', 'suwako@example.com', 1, 123.45, 0.5, '2026-01-02 03:04:05', '1990-05-06', new Blob("\x00\xff\\'\n"), '{"theme":"dark","n":[1,2]}']);
  $db->query($ins, ['神奈子', null, 0, -1, null, '2026-01-02 00:00:00', null, null, null]);
  $db->query($ins, ["it's \"q\" \\ tab\tx; -- /* */", 'x@x', 1, 0, 1e100, '2026-11-12 23:59:59', '2000-02-29', new Blob(''), '[]']);
  $db->query('BEGIN');
  for ($i = 1; $i <= 1200; $i++) $db->query('INSERT INTO posts (user_id, title, body, published) VALUES (?, ?, ?, ?)', [1 + $i % 3, "タイトル{$i}", str_repeat('本文', $i % 50), gmdate('Y-m-d H:i:s', 1767225600 + $i * 3600)]);
  $db->query('COMMIT');
  $db->query("INSERT INTO kv VALUES ('a', 1, 'x'), ('a', 2, 'y'), ('b', 1, NULL)");
  return $db;
};
$same = function($test, Db $a, Db $b, array $tables = ['users', 'posts', 'kv']) use ($dump): void {
  foreach ($tables as $t) $test->assertSame($dump($a, "SELECT * FROM {$t} ORDER BY 1, 2"), $dump($b, "SELECT * FROM {$t} ORDER BY 1, 2"), $t);
};
// LittleBeastのJSONのデータ
$data = function(string $dir): void {
  $put = function(string $rel, mixed $v) use ($dir): void {
    @mkdir(dirname($dir.'/'.$rel), 0755, true);
    file_put_contents($dir.'/'.$rel, is_string($v) ? $v : json_encode($v, Stores::JSON_FLAGS));
  };
  $put('user/1.alice.json', json_encode(['id' => 1, 'username' => 'alice', 'email' => 'a@example.com', 'role' => 3, 'prefs' => new \stdClass(), 'list' => []], JSON_PRETTY_PRINT));
  $put('user/2.bob.json', ['id' => 2, 'username' => 'bob', 'role' => 1]);
  $put('comments/blog/hello.json', ['title' => 'こんにちは', 'comments' => ['0190a1' => ['user' => 'alice', 'body' => '一'], '0190a2' => ['user' => 'bob', 'body' => '二', 'parent' => '0190a1']]]);
  $put('comments/tutorial/php/basics.json', ['title' => 'PHP', 'comments' => []]);
  $put('comments/ap.json', ['https://remote.example/notes/1' => ['thread' => 'blog/hello', 'id' => '0190a2']]);
  $put('notifications/alice.json', [['type' => 'reply', 'from' => 'bob'], ['type' => 'like', 'from' => 'bob']]);
  $put('cron/cron.json', [['name' => 'apdeliver', 'schedule' => '* * * * *'], ['name' => 'purge', 'schedule' => '0 3 * * *']]);
  $put('cron/state.json', ['apdeliver' => 1234]);
  $put('followers.txt', "https://a.example/users/x\nhttps://b.example/users/y\n");
  $put('ap/notes/0190b1.json', ['id' => '0190b1', 'content' => '<p>投稿</p>']);
  $put('ap/users/alice/private.pem', "-----BEGIN PRIVATE KEY-----\nabc\n-----END PRIVATE KEY-----\n");
  $put('ap/remote/media/x.bin', "\x00\x01\xfe\xff".str_repeat("\x80", 300));
  $put('broken.json', '{not json');
  $put('cache/foo/bar.json', '{"x":1}');
  $put('ratelimit/1.2.3.4.json', '{}');
  $put('cron/run.lock', '');
  $put('site.sqlite', "SQLite format 3\0".str_repeat("\0", 100));
};
// ディレクトリの全部のファイル（相対パス => 中身）
$files = function(string $dir): array {
  $out = [];
  if (!is_dir($dir)) return $out;
  foreach (new \RecursiveIteratorIterator(new \RecursiveDirectoryIterator($dir, \FilesystemIterator::SKIP_DOTS)) as $f) {
    $out[str_replace('\\', '/', substr($f->getPathname(), strlen($dir) + 1))] = (string)file_get_contents($f->getPathname());
  }
  ksort($out);
  return $out;
};
// 変換された事を確認する（JSONは同じ値なら良い）
$sameFiles = function($test, array $a, array $b): void {
  $test->assertSame(array_keys($a), array_keys($b));
  foreach ($a as $rel => $x) {
    if (!isset($b[$rel])) continue;
    $ja = json_decode($x, true);
    if ($x !== $b[$rel] && $ja !== null) $test->assertSame($ja, json_decode($b[$rel], true), $rel);
    else $test->assertSame(bin2hex($x), bin2hex($b[$rel]), $rel);
  }
};


$test->describe('Convert（SQLの解析）', function($test): void {
  $test->it('方言毎の文字列・名前を解析出来るはず', function($test): void {
    $t = Sql::tokens("SELECT 'it''s', 'a\\'b', \"x\", `y`, 0x00ff, _binary 'z', X'41'", 'mysql');
    $vals = array_map(fn($k) => [$k['t'], $k['t'] === 'blob' ? bin2hex($k['v']) : $k['v']], array_values(array_filter($t, fn($k) => $k['t'] !== 'op')));
    $test->assertSame([['id', 'SELECT'], ['str', "it's"], ['str', "a'b"], ['str', 'x'], ['qid', 'y'], ['blob', '00ff'], ['blob', '7a'], ['blob', '41']], $vals);
    $t = Sql::tokens("SELECT 'a\\b', E'c\\nd', \"x\"\"y\", \$q\$it's\$q\$", 'pgsql');
    $test->assertSame(['a\\b', "c\nd", 'x"y', "it's"], array_column(array_values(array_filter($t, fn($k) => in_array($k['t'], ['str', 'qid'], true))), 'v'));
  });

  $test->it('CREATE TABLEを解析出来るはず', function($test): void {
    $w = [];
    $t = Sql::createTable("CREATE TABLE `a` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
      `price` decimal(10,2) DEFAULT '0.00',
      `ok` tinyint(1) NOT NULL DEFAULT '1',
      `at` datetime(3) DEFAULT CURRENT_TIMESTAMP(3),
      `pid` int DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `a_name` (`name`(20)),
      KEY `a_low` ((lower(`name`))),
      CONSTRAINT `fk` FOREIGN KEY (`pid`) REFERENCES `a` (`id`)
    ) ENGINE=InnoDB", 'mysql', $w);
    $test->assertSame('a', $t['name']);
    $test->assertSame(['id', 'name', 'price', 'ok', 'at', 'pid'], array_column($t['columns'], 'name'));
    $test->assertSame(['int', 'varchar', 'decimal', 'bool', 'datetime', 'int'], array_column($t['columns'], 'type'));
    $test->assertTrue($t['columns'][0]['autoinc'] && $t['columns'][0]['unsigned']);
    $test->assertSame([50, 'utf8mb4_bin', ['value' => '']], [$t['columns'][1]['size'], $t['columns'][1]['collate'], $t['columns'][1]['default']]);
    $test->assertSame([10, 2], [$t['columns'][2]['size'], $t['columns'][2]['scale']]);
    $test->assertSame(['now' => true], $t['columns'][4]['default']);
    $test->assertSame(['id'], $t['pk']);
    $test->assertSame([['name' => 'a_name', 'columns' => ['name'], 'unique' => true]], $t['indexes']);
    $test->assertCount(2, $w); // 式のインデックスと外部キー

    $t = Sql::createTable('CREATE TABLE public.b (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, s serial, t timestamp with time zone NOT NULL DEFAULT now(),
      u uuid, j jsonb, tags text[], d double precision, c character varying(9) UNIQUE)', 'pgsql');
    $test->assertSame('b', $t['name']);
    $test->assertSame(['int', 'int', 'datetime', 'uuid', 'json', 'json', 'real', 'varchar'], array_column($t['columns'], 'type'));
    $test->assertSame([true, true, false], [$t['columns'][0]['autoinc'], $t['columns'][1]['autoinc'], $t['columns'][2]['autoinc']]);
    $test->assertTrue($t['columns'][2]['tz'] && $t['columns'][2]['notNull']);
    $test->assertTrue($t['columns'][5]['array']);
    $test->assertSame(['id'], $t['pk']);
    $test->assertSame([['c']], array_column($t['indexes'], 'columns'));
    $test->assertNull(Sql::createTable('CREATE VIEW v AS SELECT 1', 'pgsql'));
  });

  $test->it('型を共通の型にするはず', function($test): void {
    $p = fn(string $t, string $d = 'mysql') => array_intersect_key(Convert::parseType($t, $d), ['type' => 1, 'size' => 1, 'scale' => 1, 'unsigned' => 1, 'tz' => 1]);
    $test->assertSame(['type' => 'int', 'size' => 16, 'scale' => null, 'unsigned' => false, 'tz' => false], $p('smallint(6)'));
    $test->assertSame('bool', $p('tinyint(1)')['type']);
    $test->assertSame(['int', 16], [$p('tinyint unsigned')['type'], $p('tinyint unsigned')['size']]);
    $test->assertSame(['int', 64], [$p('int unsigned')['type'], $p('int unsigned')['size']]);
    $test->assertSame('text', $p("enum('a','b')")['type']);
    $test->assertSame('blob', $p('varbinary(16)')['type']);
    $test->assertSame('datetime', $p('timestamp', 'mysql')['type']);
    $test->assertSame(true, $p('timestamptz', 'pgsql')['tz']);
    $test->assertSame('real', $p('double precision', 'pgsql')['type']);
    $test->assertSame(['decimal', 12, 4], [$p('numeric(12,4)', 'pgsql')['type'], $p('numeric(12,4)', 'pgsql')['size'], $p('numeric(12,4)', 'pgsql')['scale']]);
    // SQLiteの型の親和性
    $test->assertSame('int', $p('INTEGER', 'sqlite')['type']);
    $test->assertSame('text', $p('', 'sqlite')['type']);
    $test->assertSame('text', $p('STRING', 'sqlite')['type']);
  });
});

$test->describe('Convert（SQLのダンプ）', function($test) use ($tmp, $fixtures, $sqlite, $sample, $same, $dump, $catch): void {
  $test->it('mysqldumpのファイルを読めるはず', function($test) use ($tmp, $fixtures, $sqlite): void {
    file_put_contents($tmp.'/my.sql', $fixtures['mysqldump']);
    $src = new DumpSource($tmp.'/my.sql');
    $test->assertSame('mysql', $src->dialect);
    $r = Convert::run($src, new DbTarget($sqlite('my.sqlite')));
    $test->assertSame(['customers' => 3, 'orders' => 2], $r['tables']);
    $test->assertTrue(count($r['warnings']) >= 3); // 全文のインデックス、外部キー、ビュー
    $db = $sqlite('my.sqlite');
    $rows = $db->query('SELECT * FROM customers ORDER BY id')['rows'];
    $test->assertSame(['諏訪子', '神奈子', "O'Brien"], array_column($rows, 'name'));
    $test->assertSame("\0\\'\"\n\r\x1a", $rows[0]['photo']);
    $test->assertSame("\x00\xff\x10", $rows[1]['photo']);
    $test->assertSame("it's; a \"test\"\\ -- not a comment", $rows[0]['note']);
    $test->assertSame("行1\n行2\ttab", $rows[1]['note']);
    $test->assertSame('/* x */ # y', $rows[2]['note']);
    $test->assertSame([1, 0, 1], array_column($rows, 'active'));
    $test->assertSame([1234.5, -0.01, 0], array_column($rows, 'balance'));
    $test->assertSame(['2026-01-02 03:04:05', null, '2026-11-12 00:00:00'], array_column($rows, 'created_at'));
    $test->assertSame('2026-01-02 03:04:05.123', $rows[0]['updated_at']);
    $test->assertSame([['id' => 1, 'total' => 99.5, 'ordered' => '2026-01-03'], ['id' => 2, 'total' => 1000.0, 'ordered' => null]],
                      $db->query('SELECT id, total, ordered FROM orders ORDER BY id')['rows']);
    $idx = array_column($db->query("SELECT name FROM sqlite_master WHERE type = 'index' ORDER BY name")['rows'], 'name');
    $test->assertSame(['customers_email_unique', 'customers_idx_name_level', 'orders_customer_id_foreign'], $idx);
    // 自動の番号の続き
    $test->assertSame(4, $db->query("INSERT INTO customers (name) VALUES ('新')")['insertId']);
  });

  $test->it('pg_dumpのファイル（COPY）を読めるはず', function($test) use ($tmp, $fixtures, $sqlite): void {
    file_put_contents($tmp.'/pg.sql', $fixtures['pgdump']);
    $src = new DumpSource($tmp.'/pg.sql');
    $test->assertSame('pgsql', $src->dialect);
    $r = Convert::run($src, new DbTarget($sqlite('pg.sqlite')));
    $test->assertSame(['items' => 3, 'tags' => 3], $r['tables']);
    $test->assertTrue(in_array('式・部分のインデックスは変換しません：items_name', $r['warnings'], true));
    $db = $sqlite('pg.sqlite');
    $rows = $db->query('SELECT * FROM items ORDER BY id')['rows'];
    $test->assertSame(['りんご', 'みかん', "it's"], array_column($rows, 'name'));
    $test->assertSame("\x00\xff\x0a\x5c", $rows[0]['data']);
    $test->assertSame('', $rows[2]['data']);
    $test->assertSame('["a","b c",null,"d\"e"]', $rows[0]['labels']); // 配列はJSON
    $test->assertSame('[]', $rows[1]['labels']);
    $test->assertSame(['2026-01-01 18:04:05.5', '2026-11-12 00:00:00', '2000-02-29 12:00:00'], array_column($rows, 'at')); // UTC
    $test->assertSame("行1\n行2\tタブ\\", $rows[0]['note']);
    $test->assertSame([1, null, 0], array_column($rows, 'flags'));
    $test->assertSame([120.5, null, -0.01], array_column($rows, 'price'));
    $test->assertSame(['id', 'name', 'price', 'flags', 'data', 'meta', 'labels', 'at', 'day', 'uid', 'note'], array_column($db->query('PRAGMA table_info(items)')['rows'], 'name'));
    $test->assertSame([[1, '果物', 1], [1, '赤', 2], [2, '果物', 3]], array_map('array_values', $db->query('SELECT * FROM tags ORDER BY item_id, n')['rows']));
  });

  $test->it('sqlite3の.dumpのファイルを読めるはず', function($test) use ($tmp, $fixtures, $sqlite): void {
    file_put_contents($tmp.'/sq.sql', $fixtures['sqlitedump']);
    $src = new DumpSource($tmp.'/sq.sql');
    $test->assertSame('sqlite', $src->dialect);
    $r = Convert::run($src, new DbTarget($sqlite('sq.sqlite')));
    $test->assertSame(['users' => 3, 'q t' => 1, 'u2' => 2], $r['tables']);
    $db = $sqlite('sq.sqlite');
    $test->assertSame([[1, "a'b", "\x00\xff", 1.5], [2, "x\ny", null, -2.0e-5], [3, "a\r\nb", null, null]],
                      array_map('array_values', $db->query('SELECT id, name, b, r FROM users ORDER BY id')['rows']));
    $test->assertSame(["a\nb\\c😀", "x\ty"], array_column($db->query('SELECT s FROM u2 ORDER BY rowid')['rows'], 's'));
    $test->assertSame(4, $db->query("INSERT INTO users (name) VALUES ('新')")['insertId']);
  });

  $test->it('ダンプに書いて、読み戻せるはず（全部の方言）', function($test) use ($tmp, $sqlite, $sample, $same): void {
    $a = $sample('rt.sqlite');
    foreach (['mysql', 'pgsql', 'sqlite'] as $d) {
      foreach (['', '.gz'] as $gz) {
        if ($gz !== '' && !function_exists('gzopen')) continue;
        $file = "{$tmp}/rt-{$d}.sql{$gz}";
        $r = Convert::run(new DbSource($a), new DumpTarget($file, $d));
        $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']);
        $head = $gz ? (string)gzdecode((string)file_get_contents($file)) : (string)file_get_contents($file);
        $test->assertStringContains("-- LittleBeastのデータベースの変換（{$d}）", $head);
        $src = new DumpSource($file);
        $test->assertSame($d, $src->dialect);
        $r = Convert::run($src, new DbTarget($sqlite("rt-{$d}{$gz}.sqlite")));
        $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables'], $d);
        $b = $sqlite("rt-{$d}{$gz}.sqlite");
        if ($d === 'sqlite') {
          $same($test, $a, $b);
        } else {
          // 型が変わる所（BOOLEAN、NUMERIC）以外は同じ
          $same($test, $a, $b, ['posts', 'kv']);
          $test->assertSame($a->query('SELECT name, email, avatar, prefs, created FROM users ORDER BY id')['rows'] == $b->query('SELECT name, email, avatar, prefs, created FROM users ORDER BY id')['rows'], true, $d);
        }
        $test->assertSame(4, $b->query("INSERT INTO users (name) VALUES ('新')")['insertId'], $d);
      }
    }
  });

  $test->it('SQLiteのダンプは本物のSQLiteで読めるはず', function($test) use ($tmp): void {
    if (!class_exists('PDO') || !in_array('sqlite', \PDO::getAvailableDrivers(), true)) {
      $test->assertTrue(true);
      return;
    }
    $p = new \PDO('sqlite::memory:');
    $p->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    $p->exec((string)file_get_contents($tmp.'/rt-sqlite.sql'));
    $test->assertSame(1200, (int)$p->query('SELECT count(*) FROM posts')->fetchColumn());
    $test->assertSame('00ff5c270a', bin2hex((string)$p->query('SELECT avatar FROM users WHERE id = 1')->fetchColumn()));
    $test->assertSame('ok', $p->query('PRAGMA integrity_check')->fetchColumn());
  });

  $test->it('壊れたダンプは例外で、書く先のファイルは消すはず', function($test) use ($tmp, $sqlite, $sample, $catch): void {
    file_put_contents($tmp.'/bad.sql', "CREATE TABLE t (id INT PRIMARY KEY, s TEXT);\nINSERT INTO t VALUES (1, 'a'), (2, 'b);\n");
    $e = $catch(fn() => Convert::run(new DumpSource($tmp.'/bad.sql'), new DumpTarget($tmp.'/bad-out.sql', 'pgsql')));
    $test->assertTrue($e instanceof DbException);
    $test->assertFalse(is_file($tmp.'/bad-out.sql'));
    $e = $catch(fn() => Convert::run(new DbSource($sample('x.sqlite')), new DumpTarget($tmp.'/x.sql', 'oracle')));
    $test->assertTrue($e instanceof \InvalidArgumentException);
  });
});

$test->describe('Convert（データベース同士）', function($test) use ($tmp, $sqlite, $sample, $same, $dump, $catch): void {
  $test->it('SQLiteからSQLiteに変換出来て、テーブルを選べるはず', function($test) use ($sqlite, $sample, $same, $catch): void {
    $a = $sample('a.sqlite');
    $n = [];
    $r = Convert::run(new DbSource($a), new DbTarget($sqlite('b.sqlite')), ['progress' => function(string $t, int $rows) use (&$n): void { $n[$t] = $rows; }]);
    $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']);
    $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $n);
    $test->assertSame([], $r['warnings']);
    $b = $sqlite('b.sqlite');
    $same($test, $a, $b);
    $test->assertSame('ok', $b->query('PRAGMA integrity_check')['rows'][0]['integrity_check']);
    // 既に有るテーブルは、dropが無ければ例外
    $e = $catch(fn() => Convert::run(new DbSource($a), new DbTarget($b)));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('既に有ります', $e->getMessage());
    $r = Convert::run(new DbSource($a), new DbTarget($b), ['tables' => ['KV'], 'drop' => true]);
    $test->assertSame(['kv' => 3], $r['tables']);
    $same($test, $a, $b);
    // 空のデータベース
    $r = Convert::run(new DbSource($sqlite('empty.sqlite')), new DbTarget($sqlite('empty2.sqlite')));
    $test->assertSame([], $r['tables']);
  });

  $test->it('偽物のMySQLサーバーに書いて、読み戻せるはず', function($test) use ($sqlite, $sample, $same): void {
    $srv = MockServer::start('mysql-server.php', []);
    if ($srv === null) {
      $test->assertTrue(true);
      return;
    }
    try {
      $a = $sample('my-a.sqlite');
      $my = new Db(['driver' => 'mysql', 'host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb', 'timeout' => 5]);
      $r = Convert::run(new DbSource($a), new DbTarget($my));
      $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']);
      $ddl = $my->query('SHOW CREATE TABLE `users`')['rows'][0]['Create Table'];
      $test->assertStringContains('AUTO_INCREMENT', $ddl);
      $test->assertStringContains('utf8mb4', $ddl);
      $r = Convert::run(new DbSource($my), new DbTarget($sqlite('my-b.sqlite')));
      $test->assertEquals(['kv' => 3, 'posts' => 1200, 'users' => 3], $r['tables']);
      $same($test, $a, $sqlite('my-b.sqlite'), ['posts', 'kv']);
      $test->assertSame(array_column($a->query('SELECT name FROM users ORDER BY id')['rows'], 'name'), array_column($sqlite('my-b.sqlite')->query('SELECT name FROM users ORDER BY id')['rows'], 'name'));
    } finally {
      $srv->stop();
    }
  });

  $test->it('偽物のPostgreSQLサーバーに書けるはず', function($test) use ($sample): void {
    $srv = MockServer::start('pg-server.php', []);
    if ($srv === null) {
      $test->assertTrue(true);
      return;
    }
    try {
      $a = $sample('pg-a.sqlite');
      $pg = new Db(['driver' => 'pgsql', 'host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb', 'timeout' => 5]);
      $r = Convert::run(new DbSource($a), new DbTarget($pg));
      $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3], $r['tables']);
      $test->assertSame(1200, (int)$pg->query('SELECT count(*) AS c FROM posts')['rows'][0]['c']);
      $test->assertSame(['諏訪子', '神奈子'], array_column($pg->query('SELECT name FROM users WHERE id < 3 ORDER BY id')['rows'], 'name'));
    } finally {
      $srv->stop();
    }
  });

  $test->it('本物のPostgreSQLと往復出来るはず（LB_TEST_PGSQL）', function($test) use ($sqlite, $sample, $same): void {
    $url = (string)getenv('LB_TEST_PGSQL');
    if ($url === '') {
      $test->assertTrue(true);
      return;
    }
    $u = parse_url($url);
    $pg = new Db(['driver' => 'pgsql', 'host' => $u['host'] ?? '127.0.0.1', 'port' => $u['port'] ?? 5432, 'username' => urldecode($u['user'] ?? ''),
                  'password' => urldecode($u['pass'] ?? ''), 'dbname' => ltrim($u['path'] ?? '', '/'), 'timeout' => 5]);
    $a = $sample('real-a.sqlite');
    $names = ['lb_test_users', 'lb_test_posts', 'lb_test_kv'];
    foreach (['users', 'posts', 'kv'] as $i => $t) $a->query("ALTER TABLE {$t} RENAME TO {$names[$i]}");
    try {
      Convert::run(new DbSource($a), new DbTarget($pg), ['drop' => true]);
      $r = Convert::run(new DbSource($pg), new DbTarget($sqlite('real-b.sqlite')), ['tables' => $names]);
      $test->assertEquals(['lb_test_kv' => 3, 'lb_test_posts' => 1200, 'lb_test_users' => 3], $r['tables']);
      $same($test, $a, $sqlite('real-b.sqlite'), ['lb_test_posts', 'lb_test_kv']);
      $test->assertSame(4, (int)$pg->query("INSERT INTO lb_test_users (name) VALUES ('新') RETURNING id")['rows'][0]['id']);
    } finally {
      foreach ($names as $t) $pg->query("DROP TABLE IF EXISTS {$t}");
    }
  });
});

$test->describe('Convert（JSONのデータ）', function($test) use ($tmp, $sqlite, $sample, $same, $data, $files, $sameFiles, $catch): void {
  $test->it('データのディレクトリが無い・空なら、テーブルも無いはず', function($test) use ($tmp, $sqlite): void {
    $test->assertSame([], (new JsonSource($tmp.'/nothing'))->tables());
    mkdir($tmp.'/emptydata');
    $r = Convert::run(new JsonSource($tmp.'/emptydata'), new DbTarget($sqlite('e.sqlite')));
    $test->assertSame([], $r['tables']);
    $r = Convert::run(new JsonSource($tmp.'/emptydata'), new JsonTarget($tmp.'/emptyout'));
    $test->assertTrue(is_dir($tmp.'/emptyout'));
  });

  $test->it('JSONのデータをテーブルにするはず', function($test) use ($tmp, $sqlite, $data): void {
    $data($tmp.'/data');
    $src = new JsonSource($tmp.'/data');
    $r = Convert::run($src, new DbTarget($sqlite('j.sqlite')));
    $test->assertSame(['lb_users' => 2, 'lb_comment_threads' => 2, 'lb_comments' => 2, 'lb_comment_ap' => 1, 'lb_notifications' => 2, 'lb_cron' => 2,
                       'lb_followers' => 2, 'lb_ap_notes' => 1, 'lb_files' => 4], $r['tables']);
    $test->assertTrue(in_array('SQLiteのファイルは変換しません：site.sqlite', $r['warnings'], true));
    $db = $sqlite('j.sqlite');
    $test->assertSame([['id' => 1, 'username' => 'alice', 'email' => 'a@example.com', 'role' => 3], ['id' => 2, 'username' => 'bob', 'email' => null, 'role' => 1]],
                      $db->query('SELECT id, username, email, role FROM lb_users ORDER BY id')['rows']);
    $test->assertSame([['id' => '0190a1', 'seq' => 0], ['id' => '0190a2', 'seq' => 1]], $db->query("SELECT id, seq FROM lb_comments WHERE thread = 'blog/hello' ORDER BY seq")['rows']);
    $test->assertSame('二', json_decode($db->query("SELECT data FROM lb_comments WHERE id = '0190a2'")['rows'][0]['data'], true)['body']);
    // キャッシュ・ロック・SQLiteのファイルは入れない、壊れたJSONは普通のファイル
    $test->assertSame(['ap/remote/media/x.bin', 'ap/users/alice/private.pem', 'broken.json', 'cron/state.json'],
                      array_column($db->query('SELECT path FROM lb_files ORDER BY path')['rows'], 'path'));
  });

  $test->it('テーブルからJSONのデータに戻せるはず', function($test) use ($tmp, $sqlite, $files, $sameFiles, $catch): void {
    $want = array_filter($files($tmp.'/data'), fn($k) => !preg_match('#^(cache|ratelimit)/|\.lock$|^site\.sqlite$#', $k), ARRAY_FILTER_USE_KEY);
    $r = Convert::run(new DbSource($sqlite('j.sqlite')), new JsonTarget($tmp.'/out1'));
    $test->assertSame(2, $r['tables']['lb_users']);
    $sameFiles($test, $want, $files($tmp.'/out1'));
    // ユーザーのファイルはそのまま
    $test->assertSame(file_get_contents($tmp.'/data/user/1.alice.json'), file_get_contents($tmp.'/out1/user/1.alice.json'));
    // 空でないディレクトリには、上書きを許さなければ書かない
    $e = $catch(fn() => Convert::run(new DbSource($sqlite('j.sqlite')), new JsonTarget($tmp.'/out1')));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('空ではありません', $e->getMessage());
    Convert::run(new DbSource($sqlite('j.sqlite')), new JsonTarget($tmp.'/out1', true));
    $sameFiles($test, $want, $files($tmp.'/out1'));
  });

  $test->it('JSONのデータをダンプ経由で往復出来るはず', function($test) use ($tmp, $files, $sameFiles): void {
    $want = $files($tmp.'/out1');
    foreach (['mysql', 'pgsql', 'sqlite'] as $d) {
      Convert::run(new JsonSource($tmp.'/out1'), new DumpTarget("{$tmp}/j-{$d}.sql", $d));
      Convert::run(new DumpSource("{$tmp}/j-{$d}.sql"), new JsonTarget("{$tmp}/out-{$d}"));
      $sameFiles($test, $want, $files("{$tmp}/out-{$d}"));
    }
  });

  $test->it('普通のテーブルはdata/dbに書いて、読み戻せるはず', function($test) use ($tmp, $sqlite, $sample, $same, $files): void {
    $a = $sample('g.sqlite');
    $a->query('CREATE TABLE "odd/name" (id INTEGER PRIMARY KEY, f REAL, b BLOB)');
    $a->query('INSERT INTO "odd/name" (f, b) VALUES (1.0, X\'00ff\'), (NULL, NULL)');
    $r = Convert::run(new DbSource($a), new JsonTarget($tmp.'/gen'));
    $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3, 'odd/name' => 2], $r['tables']);
    $test->assertSame(['db/kv.json', 'db/odd%2Fname.json', 'db/posts.json', 'db/users.json'], array_keys($files($tmp.'/gen')));
    $j = json_decode((string)file_get_contents($tmp.'/gen/db/odd%2Fname.json'), true);
    $test->assertSame('odd/name', $j['table']);
    $test->assertSame([[1, 1.0, ['$blob' => base64_encode("\x00\xff")]], [2, null, null]], $j['rows']);
    $r = Convert::run(new JsonSource($tmp.'/gen'), new DbTarget($sqlite('g2.sqlite')));
    $test->assertEquals(['users' => 3, 'posts' => 1200, 'kv' => 3, 'odd/name' => 2], array_intersect_key($r['tables'], ['users' => 1, 'posts' => 1, 'kv' => 1, 'odd/name' => 1]));
    $b = $sqlite('g2.sqlite');
    $same($test, $a, $b, ['users', 'posts', 'kv', '"odd/name"']);
  });

  $test->it('不正なパス・名前は書かないはず', function($test) use ($tmp, $sqlite, $catch): void {
    foreach (['../x', '/etc/passwd', 'a//b', 'a/./b', "a\0b", 'C:/x', 'a\\b', ''] as $p) $test->assertFalse(Stores::safePath($p), $p);
    $test->assertTrue(Stores::safePath('ap/users/alice/private.pem'));
    $test->assertFalse(Stores::safeThread('../../etc'));
    $test->assertTrue(Stores::safeThread('tutorial/php/basics'));
    $db = $sqlite('evil.sqlite');
    $db->query('CREATE TABLE lb_files (path VARCHAR(700) NOT NULL PRIMARY KEY, content BLOB NOT NULL)');
    $db->query("INSERT INTO lb_files VALUES ('../evil.txt', X'41')");
    $e = $catch(fn() => Convert::run(new DbSource($db), new JsonTarget($tmp.'/evil')));
    $test->assertTrue($e instanceof DbException);
    $test->assertFalse(is_file($tmp.'/evil.txt'));
  });
});

$test->printSummary();