<?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\Sqlite;
use Std\Lib\Db\Sqlite\Blob;
use Std\Lib\Db\Sqlite\Btree;
use Std\Lib\Db\Sqlite\Pager;
use Std\Lib\Db\Sqlite\Parser;
use Std\Lib\Db\Sqlite\Record;
use Std\Lib\Db\Sqlite\Value;

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

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

$tmp = sys_get_temp_dir().'/lbsqlite_'.bin2hex(random_bytes(6));
mkdir($tmp, 0700);
register_shutdown_function(function() use ($tmp): void {
  foreach (glob($tmp.'/*') ?: [] as $f) @unlink($f);
  @rmdir($tmp);
});
$fixtures = json_decode((string)file_get_contents(__DIR__.'/Helper/sqlite.json'), true);

$catch = function(callable $fn): ?\Throwable {
  try {
    $fn();
  } catch (\Throwable $e) {
    return $e;
  }
  return null;
};
$open = function(string $name, array $o = []) use ($tmp): Sqlite {
  $db = new Sqlite($o + ['path' => $tmp.'/'.$name]);
  $db->connect();
  return $db;
};
$rows = fn(Sqlite $db, string $sql) => $db->query($sql)['rows'];
$one = fn(Sqlite $db, string $sql) => array_values($db->query($sql)['rows'][0] ?? [])[0] ?? null;
$run = function(Sqlite $db, string $sql, array $params = []): array {
  $id = $db->prepare($sql);
  try {
    return $db->execute($id, $params);
  } finally {
    $db->demolish($id);
  }
};
// pdo_sqlite（本物のSQLite）が有れば、互換性も確認する
$pdo = fn(string $file): ?\PDO => class_exists('PDO') && in_array('sqlite', \PDO::getAvailableDrivers(), true) ? new \PDO('sqlite:'.$file) : null;


$test->describe('SQLite（レコードと値）', function($test) use ($catch): void {
  $test->it('可変長整数を読み書き出来るはず', function($test): void {
    foreach ([0, 1, 127, 128, 240, 2287, 16383, 16384, 0x00FFFFFFFFFFFFFF, 0x0100000000000000, PHP_INT_MAX, -1, PHP_INT_MIN] as $v) {
      $b = Record::putVarint($v);
      $pos = 0;
      $test->assertSame($v, Record::getVarint($b, $pos), (string)$v);
      $test->assertSame(strlen($b), $pos);
      $test->assertSame(strlen($b), Record::varintLen($v), "長さ：{$v}");
    }
    $test->assertSame("\x81\x00", Record::putVarint(128));
    $test->assertSame(9, strlen(Record::putVarint(-1)));
  });

  $test->it('レコードの全部の型を読み書き出来るはず', function($test): void {
    $vals = [null, 0, 1, -1, 127, -128, 128, 32767, -32769, 8388607, -8388609, 2147483647, -2147483649, 140737488355327, -140737488355329,
             PHP_INT_MAX, PHP_INT_MIN, 1.5, -0.0, 1e300, '', '日本語', new Blob(''), new Blob("\x00\xff")];
    $rec = Record::encode($vals);
    $back = Record::decode($rec);
    $test->assertCount(count($vals), $back);
    foreach ($vals as $i => $v) {
      if ($v instanceof Blob) {
        $test->assertTrue($back[$i] instanceof Blob && $back[$i]->data === $v->data, "BLOB {$i}");
      } else {
        $test->assertSame($v, $back[$i], "値{$i}");
      }
    }
    // 0と1は値の無いシリアル型（8、9）
    $test->assertSame("\x03\x08\x09", Record::encode([0, 1]));
    // 長いヘッダー
    $many = array_fill(0, 200, 'x');
    $test->assertSame($many, Record::decode(Record::encode($many)));
  });

  $test->it('SQLiteの順番で比べるはず', function($test): void {
    $sorted = [null, -5, 1, 1.5, 2, 'A', 'B', 'a', new Blob('A')];
    for ($i = 0; $i < count($sorted) - 1; $i++) {
      $test->assertSame(-1, Record::compare($sorted[$i], $sorted[$i + 1]), "{$i}");
    }
    $test->assertSame(0, Record::compare(1, 1.0));
    $test->assertSame(0, Record::compare('abc', 'ABC', 'NOCASE'));
    $test->assertSame(0, Record::compare('a  ', 'a', 'RTRIM'));
    $test->assertSame(1, Record::compare(PHP_INT_MAX, 9.2e18));
  });

  $test->it('型の親和性と数値の文字列', function($test): void {
    $test->assertSame(Value::INTEGER, Value::affinity('BIGINT UNSIGNED'));
    $test->assertSame(Value::TEXT, Value::affinity('VARCHAR(255)'));
    $test->assertSame(Value::BLOB, Value::affinity(''));
    $test->assertSame(Value::REAL, Value::affinity('DOUBLE PRECISION'));
    $test->assertSame(Value::NUMERIC, Value::affinity('DECIMAL(10,2)'));
    $test->assertSame(Value::INTEGER, Value::affinity('POINT'), 'POINTは「INT」を含む');
    $test->assertSame(12, Value::apply('12', Value::INTEGER));
    $test->assertSame(3, Value::apply('3.0', Value::NUMERIC));
    $test->assertSame(2.5, Value::apply(' 2.5 ', Value::NUMERIC));
    $test->assertSame('12abc', Value::apply('12abc', Value::INTEGER));
    $test->assertSame('1.0', Value::apply(1.0, Value::TEXT));
    $test->assertSame('1.0e+100', Value::realToText(1e100));
    $test->assertSame('1.0e-05', Value::realToText(1e-5));
    $test->assertSame('0.1', Value::realToText(0.1));
    $test->assertSame('123456789012345.0', Value::realToText(123456789012345.0));
  });

  $test->it('SQLを構文解析出来るはず', function($test) use ($catch): void {
    $p = new Parser();
    $st = $p->parseAll("SELECT a, 'x;y' AS b FROM t WHERE c = ? AND d = :d -- コメント\n; /* 2つ目 */ INSERT INTO t VALUES (1)");
    $test->assertCount(2, $st);
    $test->assertSame('select', $st[0]['t']);
    $test->assertSame(2, $st[0]['nParam']);
    $test->assertSame([':d' => 2], $st[0]['names']);
    $test->assertSame('insert', $st[1]['t']);
    $e = $catch(fn() => $p->parseAll('SELEC 1'));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('構文エラー', $e->getMessage());
    $test->assertStringContains('未対応', $catch(fn() => $p->parseAll('CREATE TRIGGER x AFTER INSERT ON t BEGIN SELECT 1; END'))->getMessage());
    $test->assertStringContains('未対応', $catch(fn() => $p->parseAll('SELECT rank() OVER (ORDER BY a) FROM t'))->getMessage());
  });
});

$test->describe('SQLite（テーブルとクエリ）', function($test) use ($open, $rows, $one, $run, $catch, $tmp, $pdo): void {
  $test->it('テーブルを作って、データを入れて読めるはず', function($test) use ($open, $rows, $one, $run): void {
    $db = $open('a.sqlite');
    $test->assertTrue($db->isConnected());
    $test->assertTrue($db->ping());
    $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)');
    $res = $db->query("INSERT INTO users (name, age, score) VALUES ('諏訪子', 1000, 9.5), ('神奈子', 999, 8.25)");
    $test->assertSame(2, $res['affectedRows']);
    $test->assertSame(2, $res['insertId']);
    $res = $run($db, 'INSERT INTO users (name, data, note) VALUES (?, ?, ?)', ['早苗', "\x00\xffバイナリ", str_repeat('長い', 5000)]);
    $test->assertSame(3, $res['insertId']);
    $test->assertSame(3, $db->lastInsertId());

    $res = $db->query('SELECT * FROM users ORDER BY id');
    $test->assertSame(['id', 'name', 'age', 'score', 'data', 'note'], array_column($res['fields'], 'name'));
    $test->assertSame('INTEGER', $res['fields'][0]['type']);
    $test->assertSame('users', $res['fields'][0]['table']);
    $test->assertSame(['id' => 1, 'name' => '諏訪子', 'age' => 1000, 'score' => 9.5, 'data' => null, 'note' => null], $res['rows'][0]);
    $test->assertSame(17, $res['rows'][2]['age']);
    $test->assertSame("\x00\xffバイナリ", $res['rows'][2]['data']);
    $test->assertSame(30000, strlen($res['rows'][2]['note']));
    $test->assertSame('blob', $one($db, 'SELECT typeof(data) FROM users WHERE id = 3'));
    $test->assertSame(['神奈子'], array_column($run($db, 'SELECT name FROM users WHERE age = ?', [999])['rows'], 'name'));
    $test->assertSame([['name' => '諏訪子']], $run($db, 'SELECT name FROM users WHERE id = :id', ['id' => 1])['rows']);
    $test->assertSame([['name' => '諏訪子']], $run($db, 'SELECT name FROM users WHERE id = ?1 OR id = ?1', [1])['rows']);

    $res = $db->query('UPDATE users SET age = age + 1 WHERE age > 100');
    $test->assertSame(2, $res['affectedRows']);
    $test->assertSame(0, $res['insertId']);
    $test->assertSame(1, $db->query('DELETE FROM users WHERE id = 2')['affectedRows']);
    $test->assertSame(2, $one($db, 'SELECT count(*) FROM users'));
    // AUTOINCREMENTは消した番号を使わない
    $db->query('DELETE FROM users WHERE id = 3');
    $test->assertSame(4, $db->query("INSERT INTO users (name) VALUES ('霊夢')")['insertId']);
    $test->assertSame(4, $one($db, "SELECT seq FROM sqlite_sequence WHERE name = 'users'"));
  });

  $test->it('PHPの色々な型をパラメータとして送れるはず', function($test) use ($open, $run, $catch): void {
    $db = $open('a.sqlite');
    $cases = [
      [42, 42, 'integer'], [-1.5, -1.5, 'real'], [true, 1, 'integer'], [false, 0, 'integer'], [null, null, 'null'], ['', '', 'text'],
      ['諏訪子', '諏訪子', 'text'], ["\xff\xfe", "\xff\xfe", 'blob'], [new Blob('abc'), 'abc', 'blob'],
      [new \DateTimeImmutable('2026-11-12 10:20:30'), '2026-11-12 10:20:30', 'text'],
      [new \DateTimeImmutable('2026-11-12 10:20:30.5'), '2026-11-12 10:20:30.500', 'text'], [LibSqliteColor::Red, 'あか', 'text'],
    ];
    foreach ($cases as [$in, $out, $type]) {
      $r = $run($db, 'SELECT ? AS v, typeof(?) AS t', [$in, $in])['rows'][0];
      $test->assertSame($out, $r['v'], var_export($in, true));
      $test->assertSame($type, $r['t'], var_export($in, true));
    }
    $e = $catch(fn() => $run($db, 'SELECT ?', [[1]]));
    $test->assertStringContains('パラメータ0の型は未対応です', $e->getMessage());
    $e = $catch(fn() => $run($db, 'SELECT ?, ?', [1]));
    $test->assertStringContains('パラメータ数が一致しません', $e->getMessage());
    $e = $catch(fn() => $run($db, 'SELECT :a', ['b' => 1]));
    $test->assertStringContains('パラメータがありません：b', $e->getMessage());
    // query()のパラメータはNULL
    $test->assertSame([['?' => null]], $db->query('SELECT ?')['rows']);
  });

  $test->it('型の親和性で値を変換するはず', function($test) use ($open, $rows): void {
    $db = $open('b.sqlite');
    $db->query('CREATE TABLE t (i INTEGER, r REAL, t TEXT, n NUMERIC, b BLOB, x)');
    $db->query("INSERT INTO t VALUES ('12', '1', 3, '4.0', '5', '6')");
    $db->query("INSERT INTO t VALUES ('a', 'b', 1.5, '1e2', x'00', 7.0)");
    $test->assertSame([
      ['i' => 'integer', 'r' => 'real', 't' => 'text', 'n' => 'integer', 'b' => 'text', 'x' => 'text'],
      ['i' => 'text', 'r' => 'text', 't' => 'text', 'n' => 'integer', 'b' => 'blob', 'x' => 'real'],
    ], $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'));
    $test->assertSame([['t' => '3', 'n' => 4], ['t' => '1.5', 'n' => 100]], $rows($db, 'SELECT t, n FROM t'));
    // 比較の親和性
    $test->assertSame(1, count($rows($db, "SELECT * FROM t WHERE i = '12'")));
    $test->assertSame(1, count($rows($db, 'SELECT * FROM t WHERE t = 3')));
    $test->assertSame([['a' => 1, 'b' => 0, 'c' => 1]], $rows($db, "SELECT 10 = 10.0 AS a, '10' = 10 AS b, 'b' > 5 AS c"));
  });

  $test->it('式と関数', function($test) use ($open, $rows): void {
    $db = $open('b.sqlite');
    $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,
      abs(-3) AS g, round(2.567, 2) AS h, length('日本語') AS i, substr('日本語テキスト', 2, 3) AS j, upper('abc') AS k,
      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,
      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,
      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];
    $test->assertSame(['a' => 3, 'b' => 3.5, 'c' => 1, 'd' => null, 'e' => 'a12.5', 'f' => 9.2233720368547758e18, 'g' => 3, 'h' => 2.57,
      'i' => 3, 'j' => '本語テ', 'k' => 'ABC', 'l' => 'x', 'm' => null, 'n' => 3, 'o' => 'bbb', 'p' => 'x', 'q' => '4142', 'r' => "'it''s'",
      's' => 'real', 't' => 12, 'u' => '12', 'v' => '003.1|x', 'w' => 'y', 'x' => 1, 'y' => 0, 'z' => 5], $r);
    $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,
      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];
    $test->assertSame(['a' => 1, 'b' => null, 'c' => null, 'd' => 1, 'e' => 'two', 'f' => 1, 'g' => 1], $r);
    $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,
      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,
      julianday('2000-01-01 12:00:00') AS f")[0];
    $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);
    $r = $rows($db, "SELECT json_extract('{\"a\": [1, {\"b\": \"x\"}]}', '$.a[1].b') AS a, '{\"a\": {\"b\": 2}}' ->> '$.a.b' AS b,
      json_object('k', 1) AS c, json_array(1, 'a', NULL) AS d")[0];
    $test->assertSame(['a' => 'x', 'b' => 2, 'c' => '{"k":1}', 'd' => '[1,"a",null]'], $r);
  });

  $test->it('結合・集約・サブクエリ・複合', function($test) use ($open, $rows): void {
    $db = $open('c.sqlite');
    $db->query('CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INT, boss INT)');
    $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)");
    $test->assertSame([['name' => 'A', 'boss' => null], ['name' => 'B', 'boss' => 'A'], ['name' => 'C', 'boss' => 'A'], ['name' => 'D', 'boss' => 'C'], ['name' => 'E', 'boss' => 'C']],
      $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'));
    $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]],
      $rows($db, 'SELECT dept, count(*) AS n, sum(salary) AS s, avg(salary) AS a FROM emp GROUP BY dept'));
    $test->assertSame([['dept' => 'eng', 's' => 180]], $rows($db, 'SELECT dept, sum(salary) AS s FROM emp GROUP BY dept HAVING s > 100'));
    $test->assertSame([['name' => 'A', 'm' => 100]], $rows($db, 'SELECT name, max(salary) AS m FROM emp'), 'max()の行の他の列');
    $test->assertSame(['A', 'B'], array_column($rows($db, 'SELECT name FROM emp WHERE salary > (SELECT avg(salary) FROM emp) ORDER BY name'), 'name'));
    $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'));
    $test->assertSame([['name' => 'A', 'r' => 2], ['name' => 'C', 'r' => 2]],
      $rows($db, 'SELECT name, (SELECT count(*) FROM emp x WHERE x.boss = emp.id) AS r FROM emp WHERE r > 0 ORDER BY name'));
    $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'));
    $test->assertSame([['dept' => 'eng'], ['dept' => 'ops']], $rows($db, 'SELECT DISTINCT dept FROM emp WHERE dept IS NOT NULL ORDER BY dept'));
    $test->assertSame(['B', 'C'], array_column($rows($db, 'SELECT name FROM emp ORDER BY salary DESC NULLS LAST LIMIT 2 OFFSET 1'), 'name'));
    $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'));
    $test->assertSame([['column1' => 1, 'column2' => 'a'], ['column1' => 2, 'column2' => 'b']], $rows($db, "VALUES (1, 'a'), (2, 'b')"));
    $res = $db->query('SELECT 1 AS a; SELECT 2 AS b');
    $test->assertSame([['a' => 1]], $res['rows']);
    $test->assertSame([['b' => 2]], $res['more'][0]['rows']);
    $db->query('CREATE VIEW bosses AS SELECT DISTINCT b.name FROM emp e JOIN emp b ON b.id = e.boss');
    $test->assertSame(['A', 'C'], array_column($rows($db, 'SELECT * FROM bosses ORDER BY name'), 'name'));
  });

  $test->it('インデックスを使って検索するはず（結果は同じ）', function($test) use ($open, $rows, $run): void {
    $db = $open('d.sqlite');
    $db->query('CREATE TABLE t (id INTEGER PRIMARY KEY, a INT, b TEXT COLLATE NOCASE, c REAL)');
    $db->beginTransaction();
    $ins = $db->prepare('INSERT INTO t (a, b, c) VALUES (?, ?, ?)');
    for ($i = 0; $i < 3000; $i++) $db->execute($ins, [$i % 97, 'name'.($i % 50), $i / 7]);
    $db->commit();
    $before = [
      $rows($db, 'SELECT id FROM t WHERE a = 5 ORDER BY id'), $rows($db, "SELECT id FROM t WHERE b = 'NAME7' ORDER BY id"),
      $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'),
      $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'),
    ];
    $db->query('CREATE INDEX t_a ON t(a)');
    $db->query('CREATE INDEX t_b ON t(b)');
    $after = [
      $rows($db, 'SELECT id FROM t WHERE a = 5 ORDER BY id'), $rows($db, "SELECT id FROM t WHERE b = 'NAME7' ORDER BY id"),
      $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'),
      $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'),
    ];
    $test->assertSame($before, $after);
    $test->assertCount(31, $after[0]);
    $test->assertCount(60, $after[1]);
    $test->assertSame([['ok' => 'ok']], $rows($db, 'SELECT integrity_check AS ok FROM (SELECT 1) JOIN (SELECT \'ok\' AS integrity_check)'));
    // 速さ：インデックスで1000回
    $q = $db->prepare('SELECT count(*) AS n FROM t WHERE a = ?');
    $start = microtime(true);
    for ($i = 0; $i < 1000; $i++) $n = $db->execute($q, [$i % 97])['rows'][0]['n'];
    $test->assertTrue(microtime(true) - $start < 10, 'インデックスが遅い');
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
  });

  $test->it('制約と衝突の解決', function($test) use ($open, $rows, $one, $catch): void {
    $db = $open('e.sqlite');
    $db->query('CREATE TABLE u (id INTEGER PRIMARY KEY, n TEXT UNIQUE NOT NULL, c INT CHECK (c > 0) DEFAULT 1)');
    $db->query("INSERT INTO u (n) VALUES ('a')");
    foreach (["INSERT INTO u (n) VALUES ('a')" => 'UNIQUE制約に違反しました：u.n', 'INSERT INTO u (n) VALUES (NULL)' => 'NOT NULL制約に違反しました：u.n',
              "INSERT INTO u (n, c) VALUES ('b', 0)" => 'CHECK制約に違反しました', "INSERT INTO u (id, n) VALUES (1, 'z')" => 'UNIQUE制約に違反しました：u.id',
              "INSERT INTO u (id, n) VALUES ('x', 'z')" => 'データ型が違います'] as $sql => $msg) {
      $e = $catch(fn() => $db->query($sql));
      $test->assertTrue($e instanceof DbException, $sql);
      $test->assertStringContains($msg, $e->getMessage());
    }
    $e = $catch(fn() => $db->query("INSERT INTO u (n) VALUES ('a')"));
    $test->assertSame(19, $e->getCode());
    $test->assertSame('23000', $e->sqlState);
    // 文の途中で失敗したら、その文は全部取り消す
    $catch(fn() => $db->query("INSERT INTO u (n) VALUES ('b'), ('c'), ('a')"));
    $test->assertSame(1, $one($db, 'SELECT count(*) FROM u'));
    $db->query("INSERT OR IGNORE INTO u (n) VALUES ('a'), ('b')");
    $test->assertSame(['a', 'b'], array_column($rows($db, 'SELECT n FROM u ORDER BY id'), 'n'));
    $db->query("INSERT OR REPLACE INTO u (n, c) VALUES ('a', 5)");
    $test->assertSame([['id' => 2, 'n' => 'b', 'c' => 1], ['id' => 3, 'n' => 'a', 'c' => 5]], $rows($db, 'SELECT * FROM u ORDER BY id'));
    $catch(fn() => $db->query("INSERT OR FAIL INTO u (n) VALUES ('x'), ('a'), ('y')"));
    $test->assertSame(['a', 'b', 'x'], array_column($rows($db, 'SELECT n FROM u ORDER BY n'), 'n'), 'FAILは前の行を残す');
    // ON CONFLICT、RETURNING
    $res = $db->query("INSERT INTO u (n, c) VALUES ('a', 9) ON CONFLICT(n) DO UPDATE SET c = c + excluded.c RETURNING *");
    $test->assertSame([], $res['rows']);
    $test->assertSame(14, $one($db, "SELECT c FROM u WHERE n = 'a'"));
    $db->query("INSERT INTO u (n) VALUES ('a') ON CONFLICT DO NOTHING");
    $test->assertSame([['id' => 5, 'n' => 'z']], $db->query("INSERT INTO u (n) VALUES ('z') RETURNING id, n")['rows']);
    $test->assertSame([['n' => 'z']], $db->query("DELETE FROM u WHERE id = 5 RETURNING n")['rows']);
    $test->assertSame([['n' => 'b', 'c' => 2]], $db->query("UPDATE u SET c = 2 WHERE n = 'b' RETURNING n, c")['rows']);
    $e = $catch(fn() => $db->query("UPDATE u SET n = 'a' WHERE n = 'b'"));
    $test->assertStringContains('UNIQUE制約', $e->getMessage());
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
  });

  $test->it('トランザクションとセーブポイント', function($test) use ($open, $rows, $one, $catch, $tmp): void {
    $db = $open('f.sqlite');
    $db->query('CREATE TABLE t (v TEXT UNIQUE)');
    $test->assertFalse($db->inTransaction());
    $db->beginTransaction();
    $test->assertTrue($db->inTransaction());
    $db->query("INSERT INTO t VALUES ('a')");
    $catch(fn() => $db->query("INSERT INTO t VALUES ('a')"));
    $test->assertTrue($db->inTransaction(), 'エラーの後もトランザクションの中');
    $db->query("INSERT INTO t VALUES ('b')");
    $db->rollback();
    $test->assertFalse($db->inTransaction());
    $test->assertSame(0, $one($db, 'SELECT count(*) FROM t'));

    $db->beginTransaction();
    $db->query("INSERT INTO t VALUES ('a')");
    $db->query('SAVEPOINT s1');
    $db->query("INSERT INTO t VALUES ('b')");
    $db->query('SAVEPOINT s2');
    $db->query("INSERT INTO t VALUES ('c')");
    $db->query('ROLLBACK TO s2');
    $db->query("INSERT INTO t VALUES ('d')");
    $db->query('RELEASE s1');
    $db->commit();
    $test->assertSame(['a', 'b', 'd'], array_column($rows($db, 'SELECT v FROM t ORDER BY v'), 'v'));
    $e = $catch(fn() => $db->query('COMMIT'));
    $test->assertStringContains('トランザクションが開始されていません', $e->getMessage());
    // 他の接続から見える
    $db2 = $open('f.sqlite');
    $test->assertSame(3, $one($db2, 'SELECT count(*) FROM t'));

    $tx = new Db(['driver' => 'sqlite', 'path' => $tmp.'/f.sqlite']);
    $tx->transaction(fn(Db $d) => $d->query('INSERT INTO t VALUES (?)', ['e']));
    $e = $catch(fn() => $tx->transaction(function(Db $d): void {
      $d->query('INSERT INTO t VALUES (?)', ['f']);
      throw new \RuntimeException('やめる');
    }));
    $test->assertSame('やめる', $e->getMessage());
    $test->assertSame(['a', 'b', 'd', 'e'], array_column($rows($db, 'SELECT v FROM t ORDER BY v'), 'v'));
  });

  $test->it('同じファイルの2つの接続（ロック）', function($test) use ($open, $one, $catch): void {
    $a = $open('g.sqlite');
    $a->query('CREATE TABLE c (n INT)');
    $a->query('INSERT INTO c VALUES (0)');
    $b = $open('g.sqlite', ['busy_timeout' => 100]);
    $test->assertSame(0, $one($b, 'SELECT n FROM c'));
    $a->query('BEGIN IMMEDIATE');
    $a->query('UPDATE c SET n = 1');
    $e = $catch(fn() => $b->query('UPDATE c SET n = 2'));
    $test->assertTrue($e instanceof DbException);
    $test->assertStringContains('ロックされています', $e->getMessage());
    $test->assertSame(5, $e->getCode());
    $a->query('COMMIT');
    $test->assertSame(1, $one($b, 'SELECT n FROM c'), '他の接続の変更が見える');
    $b->query('UPDATE c SET n = n + 1');
    $test->assertSame(2, $one($a, 'SELECT n FROM c'));
  });

  $test->it('スキーマを変更出来るはず', function($test) use ($open, $rows, $one, $catch): void {
    $db = $open('h.sqlite');
    $db->query('CREATE TABLE p (id INTEGER PRIMARY KEY, name TEXT, n INT)');
    $db->query("INSERT INTO p (name, n) VALUES ('a', 1), ('b', 2)");
    $db->query('CREATE INDEX p_n ON p(n)');
    $db->query("ALTER TABLE p ADD COLUMN extra TEXT DEFAULT 'def'");
    $test->assertSame(['def', 'def'], array_column($rows($db, 'SELECT extra FROM p'), 'extra'));
    $db->query('ALTER TABLE p RENAME COLUMN name TO title');
    $test->assertSame(['a', 'b'], array_column($rows($db, 'SELECT title FROM p ORDER BY id'), 'title'));
    $db->query('ALTER TABLE p RENAME TO q');
    $test->assertSame('CREATE INDEX p_n ON "q"(n)', $one($db, "SELECT sql FROM sqlite_master WHERE name = 'p_n'"));
    $db->query('ALTER TABLE q DROP COLUMN extra');
    $test->assertSame([['id' => 1, 'title' => 'a', 'n' => 1], ['id' => 2, 'title' => 'b', 'n' => 2]], $rows($db, 'SELECT * FROM q ORDER BY id'));
    $test->assertStringContains('インデックスの有る列は消せません', $catch(fn() => $db->query('ALTER TABLE q DROP COLUMN n'))->getMessage());
    $db->query('CREATE TABLE r AS SELECT id, title || \'!\' AS t FROM q');
    $test->assertSame('CREATE TABLE r(id INT,t)', $one($db, "SELECT sql FROM sqlite_master WHERE name = 'r'"));
    $test->assertSame([['cid' => 0, 'name' => 'id', 'type' => 'INTEGER', 'notnull' => 0, 'dflt_value' => null, 'pk' => 1],
                       ['cid' => 1, 'name' => 'title', 'type' => 'TEXT', 'notnull' => 0, 'dflt_value' => null, 'pk' => 0],
                       ['cid' => 2, 'name' => 'n', 'type' => 'INT', 'notnull' => 0, 'dflt_value' => null, 'pk' => 0]], $rows($db, 'PRAGMA table_info(q)'));
    $test->assertSame([['seq' => 0, 'name' => 'p_n', 'unique' => 0, 'origin' => 'c', 'partial' => 0]], $rows($db, 'PRAGMA index_list(q)'));
    $db->query('DROP INDEX p_n');
    $db->query('DROP TABLE r');
    $db->query('DROP TABLE IF EXISTS nope');
    $test->assertStringContains('テーブルがありません', $catch(fn() => $db->query('DROP TABLE nope'))->getMessage());
    $test->assertStringContains('既に有ります', $catch(fn() => $db->query('CREATE TABLE q (a)'))->getMessage());
    $db->query('CREATE TABLE IF NOT EXISTS q (a)');
    $test->assertSame([['name' => 'q']], $rows($db, "SELECT name FROM sqlite_master WHERE type = 'table'"));
    $db->query('PRAGMA user_version = 7');
    $test->assertSame(7, $one($db, 'PRAGMA user_version'));
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
  });

  $test->it('エラーと未対応の機能', function($test) use ($open, $catch, $tmp): void {
    $db = $open('i.sqlite');
    $db->query('CREATE TABLE t (a INT)');
    foreach (['SELECT * FROM nope' => 'テーブルがありません：nope', 'SELECT b FROM t' => '列がありません：b', 'SELECT count(*) FROM t WHERE count(*) > 1' => 'WHEREに集約関数',
              'SELECT nofunc(1)' => '関数がありません', 'SELECT 1 UNION SELECT 1, 2' => '列の数が違います', 'CREATE TABLE w (a) WITHOUT ROWID' => 'WITHOUT ROWID',
              'SELECT * FROM t a JOIN t b' => '', 'ATTACH DATABASE \'x\' AS y' => '未対応', 'SELECT a FROM t a, t b' => '曖昧'] as $sql => $msg) {
      $e = $catch(fn() => $db->query($sql));
      if ($msg === '') {
        $test->assertNull($e, $sql);
        continue;
      }
      $test->assertTrue($e instanceof DbException, $sql);
      $test->assertStringContains($msg, $e->getMessage());
    }
    $file = $tmp.'/notdb.sqlite';
    file_put_contents($file, str_repeat('x', 4096));
    $e = $catch(fn() => (new Sqlite(['path' => $file]))->connect());
    $test->assertStringContains('SQLiteのデータベースではありません', $e->getMessage());
    $e = $catch(fn() => (new Sqlite(['path' => $tmp.'/none.sqlite', 'create' => false]))->connect());
    $test->assertStringContains('ファイルがありません', $e->getMessage());
    $e = $catch(fn() => (new Sqlite(['path' => $tmp.'/nodir/x.sqlite']))->connect());
    $test->assertStringContains('ディレクトリがありません', $e->getMessage());
    $ro = $open('i.sqlite', ['readonly' => true]);
    $test->assertStringContains('読み込み専用', $catch(fn() => $ro->query('INSERT INTO t VALUES (1)'))->getMessage());
    $mem = new Sqlite(['path' => ':memory:']);
    $mem->connect();
    $mem->query('CREATE TABLE m (a)');
    $mem->query('BEGIN');
    $mem->query('INSERT INTO m VALUES (1)');
    $mem->query('ROLLBACK');
    $test->assertSame([['c' => 0]], $mem->query('SELECT count(*) AS c FROM m')['rows']);
    $test->assertFalse(file_exists(':memory:'));
    $closed = new Sqlite(['path' => ':memory:']);
    $test->assertStringContains('接続していません', $catch(fn() => $closed->query('SELECT 1'))->getMessage());
    $test->assertFalse($closed->ping());
  });
});

$test->describe('SQLite（ファイル形式の互換性）', function($test) use ($fixtures, $tmp, $rows, $one, $pdo): void {
  $test->it('本物のSQLiteが書いたファイルを読めるはず', function($test) use ($fixtures, $tmp, $rows, $one): void {
    $file = $tmp.'/real.sqlite';
    file_put_contents($file, base64_decode($fixtures['real']));
    $db = new Sqlite(['path' => $file, 'readonly' => true]);
    $db->connect();
    $exp = $fixtures['expected'];
    $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'));
    $test->assertSame($exp['tags'], $rows($db, 'SELECT count(*) AS c, max(tag) AS m FROM tags'));
    $test->assertSame($exp['cheap'], $rows($db, 'SELECT * FROM cheap ORDER BY name'));
    $test->assertSame($exp['byPrice'], array_column($rows($db, 'SELECT id FROM items WHERE price > 300 ORDER BY price DESC'), 'id'));
    $test->assertSame($exp['seq'], $one($db, 'SELECT seq FROM sqlite_sequence'));
    $test->assertSame(1024, $db->getServerInfo()['pageSize']);
    $test->assertSame(42, $one($db, 'PRAGMA user_version'));
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
    // NOCASEのUNIQUE
    $test->assertSame(1, count($rows($db, "SELECT * FROM items WHERE name = '品物1'")));
  });

  $test->it('本物のSQLiteのファイルに書けるはず', function($test) use ($fixtures, $tmp, $rows, $one, $pdo): void {
    $file = $tmp.'/real2.sqlite';
    file_put_contents($file, base64_decode($fixtures['real']));
    $db = new Sqlite(['path' => $file]);
    $db->connect();
    $db->beginTransaction();
    for ($i = 0; $i < 200; $i++) $db->query("INSERT INTO items (name, price, note) VALUES ('新しい$i', $i, '".str_repeat('x', $i * 10)."')");
    $db->query('DELETE FROM items WHERE id < 20');
    $db->query("UPDATE items SET qty = 99 WHERE price < 50");
    $db->commit();
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
    $test->assertSame($fixtures['expected']['seq'] + 200, $one($db, 'SELECT seq FROM sqlite_sequence'));
    $p = $pdo($file);
    if ($p === null) {
      $test->skip('本物のSQLiteで確認', 'pdo_sqliteが無い');
      return;
    }
    $test->assertSame('ok', $p->query('PRAGMA integrity_check')->fetchColumn());
    $test->assertSame($one($db, 'SELECT count(*) FROM items'), (int)$p->query('SELECT count(*) FROM items')->fetchColumn());
    $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))));
  });

  $test->it('途中で止まった書き込み（本物のSQLiteのジャーナル）を元に戻すはず', function($test) use ($fixtures, $tmp, $rows): void {
    $file = $tmp.'/hot.sqlite';
    file_put_contents($file, base64_decode($fixtures['hot']));
    file_put_contents($file.'-journal', base64_decode($fixtures['hotJournal']));
    $db = new Sqlite(['path' => $file]);
    $db->connect();
    $test->assertSame([$fixtures['expected']['hot']], $rows($db, 'SELECT count(*) AS c, group_concat(v) AS g FROM t'));
    $test->assertFalse(file_exists($file.'-journal'));
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
  });

  $test->it('自分のジャーナルも元に戻せるはず（途中でファイルに書いた場合）', function($test) use ($tmp, $rows, $one, $pdo): void {
    $file = $tmp.'/spill.sqlite';
    $db = new Sqlite(['path' => $file]);
    $db->connect();
    $db->query('CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)');
    $db->query("INSERT INTO t (v) VALUES ('元')");
    $pg = new Pager($file);
    $pg->spillPages = 5;
    $bt = new Btree($pg);
    $pg->beginRead();
    $pg->beginWrite();
    $root = Record::decode($bt->get(1, 1))[3];
    for ($i = 2; $i < 300; $i++) $bt->insert($root, $i, Record::encode([null, str_repeat('壊', 100)]));
    $bt->insert($root, 1, Record::encode([null, '壊れた']));
    $bt->flush();
    $test->assertTrue(file_exists($file.'-journal'), 'ジャーナルに書いた');
    // ここで止まった事にする
    copy($file, $tmp.'/spill2.sqlite');
    copy($file.'-journal', $tmp.'/spill2.sqlite-journal');
    $pg->rollback();
    $test->assertSame([['v' => '元']], $rows($db, 'SELECT v FROM t'), 'ロールバック');
    $test->assertFalse(file_exists($file.'-journal'));
    $db2 = new Sqlite(['path' => $tmp.'/spill2.sqlite']);
    $db2->connect();
    $test->assertSame([['v' => '元']], $rows($db2, 'SELECT v FROM t'), '途中で止まった');
    $test->assertSame([['integrity_check' => 'ok']], $rows($db2, 'PRAGMA integrity_check'));
    $pg->close();
  });

  $test->it('このクラスが書いたファイルを本物のSQLiteが読めるはず', function($test) use ($tmp, $rows, $pdo): void {
    $file = $tmp.'/mine.sqlite';
    $db = new Sqlite(['path' => $file]);
    $db->connect();
    $db->query('CREATE TABLE a (id INTEGER PRIMARY KEY, n TEXT UNIQUE, x REAL, b BLOB)');
    $db->query('CREATE INDEX a_x ON a(x DESC)');
    $db->beginTransaction();
    $ins = $db->prepare('INSERT INTO a (n, x, b) VALUES (?, ?, ?)');
    for ($i = 0; $i < 1500; $i++) $db->execute($ins, ["名前$i", $i / 3, new Blob(str_repeat(chr($i % 256), $i % 700))]);
    $db->commit();
    $db->query('DELETE FROM a WHERE id % 3 = 0');
    $db->query('UPDATE a SET n = n || \'!\' WHERE id % 5 = 0');
    $test->assertSame([['integrity_check' => 'ok']], $rows($db, 'PRAGMA integrity_check'));
    $p = $pdo($file);
    if ($p === null) {
      $test->skip('本物のSQLiteで確認', 'pdo_sqliteが無い');
      return;
    }
    $test->assertSame('ok', $p->query('PRAGMA integrity_check')->fetchColumn());
    $test->assertSame(1000, (int)$p->query('SELECT count(*) FROM a')->fetchColumn());
    $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))));
    $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());
  });

  $test->it('Db経由で使えるはず', function($test) use ($tmp): void {
    $test->assertTrue(Db::isSupported('sqlite'));
    $test->assertTrue(Db::isSupported('SQLite3'));
    $db = new Db(['driver' => 'sqlite', 'path' => $tmp.'/db.sqlite']);
    $test->assertTrue($db->driver() instanceof Sqlite);
    $db->query('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
    $test->assertSame(1, $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId']);
    $test->assertSame([['name' => '諏訪子']], $db->query('SELECT name FROM users WHERE id = ?', [1])['rows']);
    $test->assertSame('"a""b"', $db->quoteIdentifier('a"b'));
    $db->close();
    $test->assertFalse($db->isConnected());
  });
});

$test->printSummary();