<?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\Lib\Db\Sqlite;

use Std\Lib\Db\DbException;

/**
 * SQLiteのSQLを実行する
 *
 * SELECTは結合（入れ子のループ、rowid・インデックスでの検索）、GROUP BY、集約、HAVING、DISTINCT、ORDER BY、LIMIT、
 * 複合（UNION等）、サブクエリ（相関も）、ビュー、WITH（再帰無し）に対応。
 * 式は一度クロージャにしてから行毎に実行する。
 */
final class Engine {
  public int $changes = 0;
  public int $totalChanges = 0;
  public int $lastInsertRowid = 0;

  private Btree $bt;
  private Parser $parser;
  private ?Schema $schema = null;
  private int $schemaGen = -1;
  private int $schemaCookie = -1;
  private array $params = [];
  /** 相関サブクエリ用：深さ => 外の行 */
  private array $stack = [];
  private array $cte = [];
  /** 明示的なトランザクション（BEGIN） */
  private bool $txn = false;
  private array $savepoints = [];
  private bool $failKeep = false;
  private bool $rollbackAll = false;
  /** 最後のaccess()で使ったインデックスの列（rowid・全部ならnull） */
  private ?array $lastAccess = null;

  public function __construct(private Pager $pager) {
    $this->bt = new Btree($pager);
    $this->parser = new Parser();
  }

  public function inTransaction(): bool {
    return $this->txn;
  }

  public function btree(): Btree {
    return $this->bt;
  }

  /** SQLを文に分ける（構文木） */
  public function prepare(string $sql): array {
    return $this->parser->parseAll($sql);
  }

  /**
   * 1つの文を実行する
   *
   * @param array $st  prepare()の結果の1つ
   * @param array $params  1からの番号 => 値
   * @return array  cols（名前）、types（宣言された型）、tables、rows（値の配列の配列）、changes、lastId、isQuery
   * @throws DbException
   */
  public function run(array $st, array $params = []): array {
    $this->params = $params;
    switch ($st['t']) {
      case 'begin':
        return $this->begin($st['mode']);
      case 'commit':
        return $this->commit();
      case 'rollback':
        return $this->rollback();
      case 'savepoint':
        return $this->savepoint($st['name']);
      case 'release':
        return $this->release($st['name']);
      case 'rollbackTo':
        return $this->rollbackTo($st['name']);
      case 'noop':
        return self::result();
    }

    $write = $this->isWrite($st);
    $this->pager->beginRead();
    $snap = $bsnap = null;
    $this->failKeep = $this->rollbackAll = false;
    try {
      if ($write) {
        $this->pager->beginWrite();
        if ($this->pager->nPages === 0) $this->pager->initialize();
        $snap = $this->pager->snapPush();
        $bsnap = $this->bt->snapPush();
      }
      if (in_array($st['t'], ['insert', 'update', 'delete'], true)) $this->changes = 0;
      $this->stack = [];
      $this->cte = [];
      $res = $this->dispatch($st);
      if ($write) $this->finishWrite($snap, $bsnap);
      return $res;
    } catch (\Throwable $e) {
      if ($write && $this->pager->inTransaction()) {
        $this->schema = null;
        if ($this->failKeep) {
          // FAIL：それまでの変更は残す
          $this->finishWrite($snap, $bsnap);
        } else if ($this->rollbackAll || !$this->txn) {
          $this->bt->reset();
          $this->pager->rollback();
          if ($this->rollbackAll) {
            $this->txn = false;
            $this->savepoints = [];
          }
        } else {
          $this->bt->snapRestore($bsnap);
          $this->bt->snapPop($bsnap);
          $this->pager->snapRestore($snap);
          $this->pager->snapPop($snap);
        }
      }
      if ($e instanceof DbException) throw $e;
      if ($e instanceof \LogicException || $e instanceof \UnexpectedValueException) throw new DbException('SQLite：'.$e->getMessage(), 11, 'HY000', $e);
      throw $e;
    } finally {
      $this->pager->hold = $this->txn;
      $this->pager->endRead();
    }
  }

  private function finishWrite(?int $snap, ?int $bsnap): void {
    $this->pager->snapPop($snap);
    $this->bt->snapPop($bsnap);
    if (!$this->txn) {
      $this->bt->flush();
      $this->pager->commit();
    }
  }

  private function isWrite(array $st): bool {
    if (in_array($st['t'], ['insert', 'update', 'delete', 'createTable', 'createIndex', 'createView', 'drop', 'alter'], true)) return true;
    if ($st['t'] === 'pragma' && $st['arg'] !== null && in_array($st['name'], ['user_version', 'application_id'], true)) return true;
    return false;
  }

  private function dispatch(array $st): array {
    if (!empty($st['with']) && $st['t'] !== 'select') $this->cte[] = $st['with'];
    switch ($st['t']) {
      case 'select':
        $plan = $this->planSelect($st, null);
        $rows = ($plan->run)(-1);
        return self::result($plan->cols, $plan->types, $rows, 0, $plan->tables);
      case 'insert':
        return $this->insert($st);
      case 'update':
        return $this->update($st);
      case 'delete':
        return $this->delete($st);
      case 'createTable':
        return $this->createTable($st);
      case 'createIndex':
        return $this->createIndex($st);
      case 'createView':
        return $this->createView($st);
      case 'drop':
        return $this->drop($st);
      case 'alter':
        return $this->alter($st);
      case 'pragma':
        return $this->pragma($st);
    }
    throw new DbException("未対応の文：{$st['t']}", 1, 'HY000');
  }

  private static function result(array $cols = [], array $types = [], array $rows = [], int $changes = 0, array $tables = []): array {
    return ['cols' => $cols, 'types' => $types, 'rows' => $rows, 'changes' => $changes, 'tables' => $tables];
  }

  /** スキーマ（変更されていれば読み直す） */
  public function schema(): Schema {
    if ($this->schema === null || $this->schemaGen !== $this->pager->generation || $this->schemaCookie !== $this->pager->hdr['cookie']) {
      $this->schema = Schema::load($this->bt, $this->pager->nPages);
      $this->schemaGen = $this->pager->generation;
      $this->schemaCookie = $this->pager->hdr['cookie'];
    }
    return $this->schema;
  }

  ///////////////
  // トランザクション

  private function begin(string $mode): array {
    if ($this->txn) throw new DbException('トランザクションの中でトランザクションは開始できません', 1, 'HY000');
    $this->pager->beginRead();
    if ($mode !== 'DEFERRED') {
      try {
        $this->pager->beginWrite();
        if ($this->pager->nPages === 0) $this->pager->initialize();
      } catch (DbException $e) {
        $this->pager->endRead();
        throw $e;
      }
    }
    $this->txn = true;
    $this->pager->hold = true;
    return self::result();
  }

  private function commit(): array {
    if (!$this->txn) throw new DbException('トランザクションが開始されていません', 1, 'HY000');
    $this->bt->flush();
    $this->bt->snapPop(0);
    $this->pager->commit();
    $this->txn = false;
    $this->savepoints = [];
    $this->pager->hold = false;
    $this->pager->endRead();
    return self::result();
  }

  private function rollback(): array {
    if (!$this->txn) throw new DbException('トランザクションが開始されていません', 1, 'HY000');
    $this->bt->reset();
    $this->pager->rollback();
    $this->txn = false;
    $this->savepoints = [];
    $this->pager->hold = false;
    $this->pager->endRead();
    return self::result();
  }

  private function savepoint(string $name): array {
    $started = false;
    if (!$this->txn) {
      $this->begin('IMMEDIATE');
      $started = true;
    } else {
      $this->pager->beginWrite();
      if ($this->pager->nPages === 0) $this->pager->initialize();
    }
    $this->savepoints[] = [strtolower($name), $this->pager->snapPush(), $started, $this->bt->snapPush()];
    return self::result();
  }

  private function findSavepoint(string $name): int {
    for ($i = count($this->savepoints) - 1; $i >= 0; $i--) {
      if ($this->savepoints[$i][0] === strtolower($name)) return $i;
    }
    throw new DbException("セーブポイントがありません：{$name}", 1, 'HY000');
  }

  private function release(string $name): array {
    $i = $this->findSavepoint($name);
    [, $snap, $started, $bsnap] = $this->savepoints[$i];
    $this->pager->snapPop($snap);
    $this->bt->snapPop($bsnap);
    array_splice($this->savepoints, $i);
    if ($started) return $this->commit();
    return self::result();
  }

  private function rollbackTo(string $name): array {
    $i = $this->findSavepoint($name);
    [, $snap, , $bsnap] = $this->savepoints[$i];
    $this->bt->snapRestore($bsnap);
    $this->pager->snapRestore($snap);
    $this->schema = null;
    array_splice($this->savepoints, $i + 1);
    return self::result();
  }

  ///////////////
  // スコープ（列の場所）

  /**
   * スコープを作る
   *
   * @param array $srcs  各ソース：alias、name、cols（表示の名前）、types、affs、colls、table（テーブルの定義、又はnull）
   */
  private function scope(array $srcs, ?\stdClass $parent): \stdClass {
    $sc = new \stdClass();
    $sc->parent = $parent;
    $sc->level = $parent ? $parent->level + 1 : 0;
    $sc->srcs = [];
    $sc->srcOf = [];
    $off = 0;
    foreach ($srcs as $j => $s) {
      $s['base'] = $off;
      $s['map'] = [];
      foreach ($s['cols'] as $i => $n) {
        $s['map'][strtolower($n)] ??= $off + $i;
        $sc->srcOf[$off + $i] = $j;
      }
      $s['rowid'] = $off + count($s['cols']);
      $sc->srcOf[$s['rowid']] = $j;
      $s['hidden'] ??= [];
      $off += count($s['cols']) + 1;
      $sc->srcs[$j] = $s;
    }
    $sc->width = $off;
    $sc->usedOuter = false;
    return $sc;
  }

  /**
   * 列を探す
   *
   * @return array|null  level、off、aff、coll、type、src、table
   */
  private function resolve(\stdClass $sc, ?string $tbl, string $name): ?array {
    $ln = strtolower($name);
    $lt = $tbl === null ? null : strtolower($tbl);
    for ($s = $sc; $s !== null; $s = $s->parent) {
      $found = null;
      foreach ($s->srcs as $j => $src) {
        if ($lt !== null && $lt !== strtolower((string)$src['alias'])) continue;
        $off = $src['map'][$ln] ?? null;
        if ($off === null && in_array($ln, ['rowid', 'oid', '_rowid_'], true) && ($src['table'] ?? null) !== null) $off = $src['rowid'];
        if ($off === null) continue;
        if ($lt === null && isset($src['hidden'][$ln])) continue;
        if ($found !== null && $lt === null) throw new DbException("列の名前が曖昧です：{$name}", 1, 'HY000');
        $i = $off - $src['base'];
        $found = ['level' => $s->level, 'off' => $off, 'src' => $j, 'aff' => $src['affs'][$i] ?? Value::INTEGER,
                  'coll' => $src['colls'][$i] ?? 'BINARY', 'type' => $src['types'][$i] ?? 'INTEGER', 'table' => $src['name'] ?? null,
                  'name' => $src['cols'][$i] ?? $name, 'col' => $i];
      }
      if ($found !== null) {
        if ($s !== $sc) {
          for ($x = $sc; $x !== $s; $x = $x->parent) $x->usedOuter = true;
        }
        return $found;
      }
    }
    return null;
  }

  ///////////////
  // 式

  /**
   * 式をクロージャにする
   *
   * @param array $e  式の構文木
   * @param \stdClass $sc  スコープ
   * @param \stdClass|null $agg  集約の文脈（集約関数を集める）
   * @return \Closure  fn(array $row): mixed
   */
  private function compile(array $e, \stdClass $sc, ?\stdClass $agg = null): \Closure {
    switch ($e['t']) {
      case 'lit':
        $v = $e['v'];
        return fn(array $r) => $v;
      case 'param':
        $i = $e['i'];
        return fn(array $r) => $this->params[$i] ?? null;
      case 'col':
        $c = $this->resolve($sc, $e['tbl'], $e['name']);
        if ($c === null) {
          if (!empty($e['dq']) && $e['tbl'] === null) {
            $v = $e['name'];
            return fn(array $r) => $v;
          }
          throw new DbException('列がありません：'.($e['tbl'] !== null ? $e['tbl'].'.' : '').$e['name'], 1, 'HY000');
        }
        $o = $c['off'];
        if ($c['level'] === $sc->level) return fn(array $r) => $r[$o] ?? null;
        $L = $c['level'];
        return fn(array $r) => $this->stack[$L][$o] ?? null;
      case 'collate':
        return $this->compile($e['e'], $sc, $agg);
      case 'un':
        $f = $this->compile($e['e'], $sc, $agg);
        switch ($e['op']) {
          case '-':
            return function(array $r) use ($f) {
              $v = $f($r);
              if ($v === null) return null;
              $n = Value::toNumber($v);
              if ($n === PHP_INT_MIN) return 9.2233720368547758e18;
              return -$n;
            };
          case '+':
            return $f;
          case '~':
            return function(array $r) use ($f) {
              $v = $f($r);
              return $v === null ? null : ~(int)Value::toInt($v);
            };
          default:
            return function(array $r) use ($f) {
              $t = Value::truth($f($r));
              return $t === null ? null : ($t ? 0 : 1);
            };
        }
      case 'bin':
        return $this->compileBinary($e, $sc, $agg);
      case 'is':
        $l = $this->compile($e['l'], $sc, $agg);
        $rf = $this->compile($e['r'], $sc, $agg);
        $affL = $this->exprAff($e['l'], $sc);
        $affR = $this->exprAff($e['r'], $sc);
        $coll = $this->cmpColl($e['l'], $e['r'], $sc);
        $not = $e['not'];
        return function(array $r) use ($l, $rf, $affL, $affR, $coll, $not) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) $eq = $a === null && $b === null;
          else {
            [$a, $b] = Value::prepareCompare($a, $affL, $b, $affR);
            $eq = Record::compare($a, $b, $coll) === 0;
          }
          return ($eq xor $not) ? 1 : 0;
        };
      case 'isnull':
        $f = $this->compile($e['e'], $sc, $agg);
        $not = $e['not'];
        return fn(array $r) => (($f($r) === null) xor $not) ? 1 : 0;
      case 'between':
        $f = $this->compile($e['e'], $sc, $agg);
        $lo = $this->compile($e['lo'], $sc, $agg);
        $hi = $this->compile($e['hi'], $sc, $agg);
        $aff = $this->exprAff($e['e'], $sc);
        $affLo = $this->exprAff($e['lo'], $sc);
        $affHi = $this->exprAff($e['hi'], $sc);
        $coll1 = $this->cmpColl($e['e'], $e['lo'], $sc);
        $coll2 = $this->cmpColl($e['e'], $e['hi'], $sc);
        $not = $e['not'];
        return function(array $r) use ($f, $lo, $hi, $aff, $affLo, $affHi, $coll1, $coll2, $not) {
          $v = $f($r);
          $a = $lo($r);
          $b = $hi($r);
          $ge = $v === null || $a === null ? null : (function() use ($v, $a, $aff, $affLo, $coll1) {
            [$x, $y] = Value::prepareCompare($v, $aff, $a, $affLo);
            return Record::compare($x, $y, $coll1) >= 0;
          })();
          $le = $v === null || $b === null ? null : (function() use ($v, $b, $aff, $affHi, $coll2) {
            [$x, $y] = Value::prepareCompare($v, $aff, $b, $affHi);
            return Record::compare($x, $y, $coll2) <= 0;
          })();
          if ($ge === false || $le === false) $res = false;
          else if ($ge === null || $le === null) return null;
          else $res = true;
          return ($res xor $not) ? 1 : 0;
        };
      case 'like':
        $l = $this->compile($e['l'], $sc, $agg);
        $p = $this->compile($e['r'], $sc, $agg);
        $esc = $e['esc'] ? $this->compile($e['esc'], $sc, $agg) : null;
        $glob = $e['op'] === 'GLOB';
        $not = $e['not'];
        return function(array $r) use ($l, $p, $esc, $glob, $not) {
          $v = $l($r);
          $pat = $p($r);
          if ($v === null || $pat === null) return null;
          $x = $esc ? $esc($r) : null;
          if ($esc && ($x === null || mb_strlen((string)$x) !== 1)) throw new DbException('ESCAPEは1文字です', 1, 'HY000');
          $m = $glob ? Func::glob((string)Value::toText($pat), (string)Value::toText($v)) : Func::like((string)Value::toText($pat), (string)Value::toText($v), $x === null ? null : (string)$x);
          return ($m xor $not) ? 1 : 0;
        };
      case 'in':
        return $this->compileIn($e, $sc, $agg);
      case 'case':
        $base = $e['base'] ? $this->compile($e['base'], $sc, $agg) : null;
        $affB = $e['base'] ? $this->exprAff($e['base'], $sc) : '';
        $whens = [];
        foreach ($e['when'] as [$c, $v]) {
          $whens[] = [$this->compile($c, $sc, $agg), $this->compile($v, $sc, $agg), $e['base'] ? $this->exprAff($c, $sc) : '',
                      $e['base'] ? $this->cmpColl($e['base'], $c, $sc) : 'BINARY'];
        }
        $else = $e['else'] ? $this->compile($e['else'], $sc, $agg) : null;
        return function(array $r) use ($base, $affB, $whens, $else) {
          $b = $base ? $base($r) : null;
          foreach ($whens as [$c, $v, $affW, $coll]) {
            if ($base) {
              $w = $c($r);
              if ($b === null || $w === null) continue;
              [$x, $y] = Value::prepareCompare($b, $affB, $w, $affW);
              if (Record::compare($x, $y, $coll) === 0) return $v($r);
            } else if (Value::truth($c($r))) {
              return $v($r);
            }
          }
          return $else ? $else($r) : null;
        };
      case 'cast':
        $f = $this->compile($e['e'], $sc, $agg);
        $aff = Value::castAffinity($e['type']);
        return fn(array $r) => self::cast($f($r), $aff);
      case 'fn':
        return $this->compileFunction($e, $sc, $agg);
      case 'exists':
        $plan = $this->planSelect($e['sel'], $sc);
        return $this->subqueryRunner($plan, $sc, 1, fn(array $rows) => empty($rows) ? 0 : 1);
      case 'sub':
        $plan = $this->planSelect($e['sel'], $sc);
        if (count($plan->cols) !== 1) throw new DbException('サブクエリの列は1つです（'.count($plan->cols).'列）', 1, 'HY000');
        return $this->subqueryRunner($plan, $sc, 1, fn(array $rows) => $rows[0][0] ?? null);
      case 'vector':
        throw new DbException('行の値（(a, b)）は未対応です', 1, 'HY000');
    }
    throw new DbException("未対応の式：{$e['t']}", 1, 'HY000');
  }

  /** サブクエリを実行するクロージャ（相関が無ければ1回だけ） */
  private function subqueryRunner(\stdClass $plan, \stdClass $sc, int $limit, \Closure $map): \Closure {
    $L = $sc->level;
    if (!$plan->correlated) {
      $done = false;
      $val = null;
      return function(array $r) use (&$done, &$val, $plan, $limit, $map, $L) {
        if (!$done) {
          $this->stack[$L] = $r;
          $val = $map(($plan->run)($limit));
          $done = true;
        }
        return $val;
      };
    }
    return function(array $r) use ($plan, $limit, $map, $L) {
      $saved = $this->stack[$L] ?? null;
      $this->stack[$L] = $r;
      try {
        return $map(($plan->run)($limit));
      } finally {
        $this->stack[$L] = $saved;
      }
    };
  }

  private function compileBinary(array $e, \stdClass $sc, ?\stdClass $agg): \Closure {
    $l = $this->compile($e['l'], $sc, $agg);
    $rf = $this->compile($e['r'], $sc, $agg);
    switch ($e['op']) {
      case 'AND':
        return function(array $r) use ($l, $rf) {
          $a = Value::truth($l($r));
          if ($a === false) return 0;
          $b = Value::truth($rf($r));
          if ($b === false) return 0;
          return $a === null || $b === null ? null : 1;
        };
      case 'OR':
        return function(array $r) use ($l, $rf) {
          $a = Value::truth($l($r));
          if ($a === true) return 1;
          $b = Value::truth($rf($r));
          if ($b === true) return 1;
          return $a === null || $b === null ? null : 0;
        };
      case '=':
      case '!=':
      case '<':
      case '<=':
      case '>':
      case '>=':
        $affL = $this->exprAff($e['l'], $sc);
        $affR = $this->exprAff($e['r'], $sc);
        $coll = $this->cmpColl($e['l'], $e['r'], $sc);
        $op = $e['op'];
        return function(array $r) use ($l, $rf, $affL, $affR, $coll, $op) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) return null;
          [$a, $b] = Value::prepareCompare($a, $affL, $b, $affR);
          $c = Record::compare($a, $b, $coll);
          return match ($op) {
            '=' => (int)($c === 0), '!=' => (int)($c !== 0), '<' => (int)($c < 0), '<=' => (int)($c <= 0), '>' => (int)($c > 0), default => (int)($c >= 0),
          };
        };
      case '||':
        return function(array $r) use ($l, $rf) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) return null;
          return Value::toText($a).Value::toText($b);
        };
      case '+':
      case '-':
      case '*':
        $op = $e['op'];
        return function(array $r) use ($l, $rf, $op) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) return null;
          $a = Value::toNumber($a);
          $b = Value::toNumber($b);
          return match ($op) { '+' => $a + $b, '-' => $a - $b, default => $a * $b };
        };
      case '/':
        return function(array $r) use ($l, $rf) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) return null;
          $a = Value::toNumber($a);
          $b = Value::toNumber($b);
          if ($b == 0) return null;
          if (is_int($a) && is_int($b)) {
            if ($a === PHP_INT_MIN && $b === -1) return 9.2233720368547758e18;
            return intdiv($a, $b);
          }
          return $a / $b;
        };
      case '%':
        return function(array $r) use ($l, $rf) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) return null;
          $a = Value::toNumber($a);
          $b = Value::toNumber($b);
          if (is_int($a) && is_int($b)) {
            if ($b === 0) return null;
            if ($b === -1) return 0;
            return $a % $b;
          }
          // SQLite 3.45：整数にしてから計算し、REALの結果
          $ia = (int)Value::toInt($a);
          $ib = (int)Value::toInt($b);
          if ($ib === 0) return null;
          return (float)($ib === -1 ? 0 : $ia % $ib);
        };
      case '&':
      case '|':
      case '<<':
      case '>>':
        $op = $e['op'];
        return function(array $r) use ($l, $rf, $op) {
          $a = $l($r);
          $b = $rf($r);
          if ($a === null || $b === null) return null;
          $a = (int)Value::toInt($a);
          $b = (int)Value::toInt($b);
          switch ($op) {
            case '&':
              return $a & $b;
            case '|':
              return $a | $b;
            case '<<':
              if ($b < 0) return $b <= -64 ? ($a < 0 ? -1 : 0) : $a >> -$b;
              return $b >= 64 ? 0 : $a << $b;
            default:
              if ($b < 0) return $b <= -64 ? 0 : $a << -$b;
              return $b >= 64 ? ($a < 0 ? -1 : 0) : $a >> $b;
          }
        };
    }
    throw new DbException("未対応の演算子：{$e['op']}", 1, 'HY000');
  }

  private function compileIn(array $e, \stdClass $sc, ?\stdClass $agg): \Closure {
    $f = $this->compile($e['e'], $sc, $agg);
    $aff = $this->exprAff($e['e'], $sc);
    $not = $e['not'];
    $test = function(mixed $v, iterable $list, string $affR, string $coll) use ($aff, $not) {
      $hasNull = false;
      $any = false;
      foreach ($list as $x) {
        $any = true;
        if ($x === null) {
          $hasNull = true;
          continue;
        }
        if ($v === null) continue;
        [$a, $b] = Value::prepareCompare($v, $aff, $x, $affR);
        if (Record::compare($a, $b, $coll) === 0) return $not ? 0 : 1;
      }
      if (!$any) return $not ? 1 : 0;
      if ($v === null || $hasNull) return null;
      return $not ? 1 : 0;
    };
    if ($e['list'] !== null) {
      $items = [];
      foreach ($e['list'] as $x) $items[] = [$this->compile($x, $sc, $agg), $this->exprAff($x, $sc), $this->cmpColl($e['e'], $x, $sc)];
      return function(array $r) use ($f, $items, $aff, $not) {
        $v = $f($r);
        if (empty($items)) return $not ? 1 : 0;
        $hasNull = false;
        foreach ($items as [$g, $affR, $coll]) {
          $x = $g($r);
          if ($x === null) {
            $hasNull = true;
            continue;
          }
          if ($v === null) continue;
          [$a, $b] = Value::prepareCompare($v, $aff, $x, $affR);
          if (Record::compare($a, $b, $coll) === 0) return $not ? 0 : 1;
        }
        if ($v === null || $hasNull) return null;
        return $not ? 1 : 0;
      };
    }
    $sel = $e['sel'] ?? ['t' => 'select', 'with' => [], 'cores' => [['distinct' => false, 'cols' => [['star' => null]],
      'from' => [['table' => $e['table'], 'as' => null, 'join' => 'inner', 'on' => null, 'using' => null, 'natural' => false]],
      'where' => null, 'group' => [], 'having' => null]], 'ops' => [], 'order' => [], 'limit' => null, 'offset' => null];
    $plan = $this->planSelect($sel, $sc);
    if (count($plan->cols) !== 1) throw new DbException('INのサブクエリの列は1つです', 1, 'HY000');
    $affR = $plan->affs[0] ?? '';
    $coll = $this->exprColl($e['e'], $sc) ?? ($plan->colls[0] ?? 'BINARY');
    $values = $this->subqueryRunner($plan, $sc, -1, fn(array $rows) => array_column($rows, 0));
    return function(array $r) use ($f, $values, $test, $affR, $coll) {
      return $test($f($r), $values($r), $affR, $coll);
    };
  }

  private function compileFunction(array $e, \stdClass $sc, ?\stdClass $agg): \Closure {
    $name = $e['name'];
    if (Func::isAggregate($name, count($e['args']), $e['star'])) {
      if ($agg === null || $agg->inAgg) throw new DbException("集約関数の使い方が間違っています：{$name}()", 1, 'HY000');
      $agg->inAgg = true;
      $args = [];
      foreach ($e['args'] as $a) $args[] = $this->compile($a, $sc, $agg);
      $agg->inAgg = false;
      $k = count($agg->list);
      $filter = null;
      if (!empty($e['filter'])) {
        $agg->inAgg = true;
        $filter = $this->compile($e['filter'], $sc, $agg);
        $agg->inAgg = false;
      }
      $agg->list[] = ['name' => $name, 'args' => $args, 'star' => $e['star'], 'distinct' => $e['distinct'], 'filter' => $filter,
                      'coll' => isset($e['args'][0]) ? ($this->exprColl($e['args'][0], $sc) ?? 'BINARY') : 'BINARY'];
      $o = $agg->base + $k;
      return fn(array $r) => $r[$o] ?? null;
    }
    if ($e['star']) throw new DbException("関数{$name}(*)は使えません", 1, 'HY000');
    $args = [];
    $colls = [];
    foreach ($e['args'] as $a) {
      $args[] = $this->compile($a, $sc, $agg);
      $colls[] = $this->exprColl($a, $sc) ?? 'BINARY';
    }
    if (in_array($name, ['random', 'randomblob', 'changes', 'total_changes', 'last_insert_rowid', 'current_timestamp', 'current_date', 'current_time'], true)
        || !empty($args)) {
      return function(array $r) use ($name, $args, $colls) {
        $vals = [];
        foreach ($args as $g) $vals[] = $g($r);
        return Func::call($name, $vals, $this, $colls);
      };
    }
    return fn(array $r) => Func::call($name, [], $this, $colls);
  }

  /** CAST */
  public static function cast(mixed $v, string $aff): mixed {
    if ($v === null) return null;
    switch ($aff) {
      case Value::INTEGER:
        return Value::toInt($v);
      case Value::REAL:
        return (float)Value::toNumber($v);
      case Value::TEXT:
        return Value::toText($v);
      case Value::BLOB:
        return $v instanceof Blob ? $v : new Blob((string)Value::toText($v));
      default:
        if (is_int($v) || is_float($v)) return Value::apply($v, Value::NUMERIC);
        $n = Value::toNumber($v);
        return is_float($n) && floor($n) === $n && abs($n) < 9.2e18 ? (int)$n : $n;
    }
  }

  /** 式の親和性（列・CASTだけ） */
  private function exprAff(array $e, \stdClass $sc): string {
    if ($e['t'] === 'col') {
      try {
        $c = $this->resolve($sc, $e['tbl'], $e['name']);
      } catch (DbException $ex) {
        return '';
      }
      return $c['aff'] ?? '';
    }
    if ($e['t'] === 'cast') return Value::castAffinity($e['type']);
    if ($e['t'] === 'collate') return $this->exprAff($e['e'], $sc);
    return '';
  }

  /** 式の照合順序（明示的なCOLLATE、列） */
  private function exprColl(array $e, \stdClass $sc, bool $explicitOnly = false): ?string {
    if ($e['t'] === 'collate') return $e['coll'];
    if ($explicitOnly) return null;
    if ($e['t'] === 'col') {
      try {
        $c = $this->resolve($sc, $e['tbl'], $e['name']);
      } catch (DbException $ex) {
        return null;
      }
      return $c['coll'] ?? null;
    }
    return null;
  }

  /** 比較の照合順序（左の明示、右の明示、左の列、右の列） */
  private function cmpColl(array $l, array $r, \stdClass $sc): string {
    return $this->exprColl($l, $sc, true) ?? $this->exprColl($r, $sc, true) ?? $this->exprColl($l, $sc) ?? $this->exprColl($r, $sc) ?? 'BINARY';
  }

  /** 式の中の集約関数が有るか */
  private static function hasAggregate(?array $e): bool {
    if ($e === null) return false;
    if ($e['t'] === 'fn' && Func::isAggregate($e['name'], count($e['args']), $e['star'])) return true;
    if (in_array($e['t'], ['sub', 'exists'], true)) return false;
    foreach ($e as $k => $v) {
      if ($k === 'sel' || !is_array($v)) continue;
      if (isset($v['t'])) {
        if (self::hasAggregate($v)) return true;
      } else {
        foreach ($v as $x) {
          if (is_array($x) && isset($x['t']) && self::hasAggregate($x)) return true;
          if (is_array($x) && !isset($x['t'])) foreach ($x as $y) if (is_array($y) && isset($y['t']) && self::hasAggregate($y)) return true;
        }
      }
    }
    return false;
  }

  /** ANDで分ける */
  private static function conjuncts(?array $e): array {
    if ($e === null) return [];
    if ($e['t'] === 'bin' && $e['op'] === 'AND') return array_merge(self::conjuncts($e['l']), self::conjuncts($e['r']));
    return [$e];
  }

  /**
   * 式が使うソース（このスコープの）
   *
   * @return array|null  ソースの番号の配列、サブクエリが有ればnull（全部）
   */
  private function refs(array $e, \stdClass $sc): ?array {
    if (in_array($e['t'], ['sub', 'exists'], true) || ($e['t'] === 'in' && ($e['sel'] !== null || isset($e['table'])))) return null;
    if ($e['t'] === 'col') {
      $c = $this->resolve($sc, $e['tbl'], $e['name']);
      if ($c === null || $c['level'] !== $sc->level) return [];
      return [$c['src']];
    }
    $out = [];
    foreach ($e as $k => $v) {
      if (!is_array($v)) continue;
      $list = isset($v['t']) ? [$v] : $v;
      foreach ($list as $x) {
        if (!is_array($x)) continue;
        $subs = isset($x['t']) ? [$x] : $x;
        foreach ($subs as $y) {
          if (!is_array($y) || !isset($y['t'])) continue;
          $r = $this->refs($y, $sc);
          if ($r === null) return null;
          $out = array_merge($out, $r);
        }
      }
    }
    return array_values(array_unique($out));
  }

  ///////////////
  // SELECT

  /**
   * SELECTの計画
   *
   * @return \stdClass  cols、types、affs、colls、tables、correlated、run（fn(int $limit): array 行の配列）
   */
  private function planSelect(array $sel, ?\stdClass $parent): \stdClass {
    $pushed = false;
    if (!empty($sel['with'])) {
      $this->cte[] = $sel['with'];
      $pushed = true;
    }
    try {
      $single = count($sel['cores']) === 1;
      $cores = [];
      $correlated = false;
      foreach ($sel['cores'] as $i => $core) {
        $cores[] = $this->planCore($core, $parent, $single ? $sel['order'] : []);
        $correlated = $correlated || $cores[$i]->correlated;
      }
      $first = $cores[0];
      foreach ($cores as $c) {
        if (count($c->cols) !== count($first->cols)) throw new DbException('複合のSELECTの列の数が違います', 1, 'HY000');
      }
      $limitF = $sel['limit'] ? $this->compile($sel['limit'], $this->scope([], $parent)) : null;
      $offsetF = $sel['offset'] ? $this->compile($sel['offset'], $this->scope([], $parent)) : null;

      // 複合のORDER BY：出力の列
      $orderIdx = [];
      if (!$single && $sel['order']) {
        foreach ($sel['order'] as $o) {
          $k = $this->outputIndex($o['e'], $first);
          if ($k === null) throw new DbException('複合のSELECTのORDER BYは結果の列だけです', 1, 'HY000');
          $orderIdx[] = [$k, $o['desc'], $o['nulls'], $o['e']['t'] === 'collate' ? $o['e']['coll'] : ($first->colls[$k] ?? 'BINARY')];
        }
      }
      $ops = $sel['ops'];
      $order = $sel['order'];
      $plan = new \stdClass();
      $plan->cols = $first->cols;
      $plan->types = $first->types;
      $plan->affs = $first->affs;
      $plan->colls = $first->colls;
      $plan->tables = $first->tables;
      $plan->correlated = $correlated;
      $plan->run = function(int $hint) use ($cores, $single, $ops, $order, $orderIdx, $limitF, $offsetF): array {
        $limit = $limitF ? Value::toInt($limitF([])) : -1;
        $offset = $offsetF ? max(0, (int)Value::toInt($offsetF([]))) : 0;
        if ($limit === null) $limit = -1;
        if ($hint >= 0 && ($limit < 0 || $hint < $limit)) $limit = $hint;
        if ($single) {
          $rows = ($cores[0]->run)(empty($order) && $limit >= 0 ? $limit + $offset : -1);
          if ($order) {
            usort($rows, fn($a, $b) => self::compareSort($a[1], $b[1], $cores[0]->sort));
          }
          $rows = array_column($rows, 0);
        } else {
          $rows = array_column(($cores[0]->run)(-1), 0);
          foreach ($ops as $i => $op) {
            $next = array_column(($cores[$i + 1]->run)(-1), 0);
            $rows = self::compound($op, $rows, $next);
          }
          if ($orderIdx) {
            $spec = [];
            foreach ($orderIdx as [$k, $desc, $nulls, $coll]) $spec[] = [$desc, $nulls, $coll];
            usort($rows, function($a, $b) use ($orderIdx, $spec) {
              $ka = $kb = [];
              foreach ($orderIdx as [$k]) {
                $ka[] = $a[$k];
                $kb[] = $b[$k];
              }
              return self::compareSort($ka, $kb, $spec);
            });
          }
        }
        if ($offset > 0 || $limit >= 0) $rows = array_slice($rows, $offset, $limit >= 0 ? $limit : null);
        return $rows;
      };
      return $plan;
    } finally {
      if ($pushed) array_pop($this->cte);
    }
  }

  private static function compound(string $op, array $a, array $b): array {
    $key = fn(array $row) => implode("\x1F", array_map(fn($v) => Value::key($v), $row));
    switch ($op) {
      case 'UNION ALL':
        return array_merge($a, $b);
      case 'UNION':
        $out = [];
        foreach (array_merge($a, $b) as $row) $out[$key($row)] ??= $row;
        return array_values($out);
      case 'INTERSECT':
        $in = [];
        foreach ($b as $row) $in[$key($row)] = true;
        $out = [];
        foreach ($a as $row) if (isset($in[$k = $key($row)])) $out[$k] ??= $row;
        return array_values($out);
      default:
        $in = [];
        foreach ($b as $row) $in[$key($row)] = true;
        $out = [];
        foreach ($a as $row) if (!isset($in[$k = $key($row)])) $out[$k] ??= $row;
        return array_values($out);
    }
  }

  /** ORDER BYの比較 */
  private static function compareSort(array $a, array $b, array $spec): int {
    foreach ($spec as $i => [$desc, $nulls, $coll]) {
      $x = $a[$i];
      $y = $b[$i];
      if ($x === null || $y === null) {
        if ($x === $y) continue;
        $nullFirst = $nulls === null ? !$desc : $nulls === 'FIRST';
        return ($x === null) === $nullFirst ? -1 : 1;
      }
      $c = Record::compare($x, $y, $coll);
      if ($c !== 0) return $desc ? -$c : $c;
    }
    return 0;
  }

  /** ORDER BYの式が結果の何番目の列か（番号、別名、同じ式） */
  private function outputIndex(array $e, \stdClass $core): ?int {
    if ($e['t'] === 'collate') $e = $e['e'];
    if ($e['t'] === 'lit' && is_int($e['v'])) {
      if ($e['v'] < 1 || $e['v'] > count($core->cols)) throw new DbException("ORDER BYの番号が範囲外です：{$e['v']}", 1, 'HY000');
      return $e['v'] - 1;
    }
    if ($e['t'] === 'col' && $e['tbl'] === null) {
      foreach ($core->aliases as $k => $a) if ($a !== null && strcasecmp($a, $e['name']) === 0) return $k;
      foreach ($core->cols as $k => $n) if (strcasecmp($n, $e['name']) === 0) return $k;
    }
    return null;
  }

  /** FROMのテーブル名から、ソース（テーブル・ビュー・WITH） */
  private function sourceFor(string $name, ?string $alias, ?\stdClass $parent): array {
    $l = strtolower($name);
    for ($i = count($this->cte) - 1; $i >= 0; $i--) {
      if (isset($this->cte[$i][$l])) {
        $c = $this->cte[$i][$l];
        // 自分自身を参照（再帰）は未対応
        $saved = $this->cte;
        $this->cte = array_slice($this->cte, 0, $i);
        $level = $saved[$i];
        unset($level[$l]);
        $this->cte[] = $level;
        try {
          $plan = $this->planSelect($c['sel'], null);
        } catch (DbException $e) {
          if ($c['recursive'] && str_contains($e->getMessage(), $name)) throw new DbException('再帰のWITHは未対応です', 1, 'HY000');
          throw $e;
        } finally {
          $this->cte = $saved;
        }
        return $this->planSource($plan, $alias ?? $c['name'], $c['cols']);
      }
    }
    $schema = $this->schema();
    if (isset($schema->views[$l])) {
      $v = $schema->views[$l];
      $saved = $this->cte;
      $this->cte = [];
      try {
        $plan = $this->planSelect($v['sel'], null);
      } finally {
        $this->cte = $saved;
      }
      return $this->planSource($plan, $alias ?? $v['name'], $v['cols']);
    }
    $t = $schema->table_($name);
    if ($t === null) throw new DbException("テーブルがありません：{$name}", 1, 'HY000');
    if (isset($t['unsupported'])) throw new DbException($t['unsupported'], 1, 'HY000');
    $names = array_column($t['cols'], 'name');
    return ['alias' => $alias ?? $t['name'], 'name' => $t['name'], 'cols' => $names, 'types' => array_column($t['cols'], 'type'),
            'affs' => array_column($t['cols'], 'aff'), 'colls' => array_column($t['cols'], 'collate'), 'table' => $t];
  }

  private function planSource(\stdClass $plan, string $alias, ?array $cols): array {
    $names = $cols ?? $plan->cols;
    if (count($names) !== count($plan->cols)) throw new DbException("{$alias}の列の数が違います", 1, 'HY000');
    return ['alias' => $alias, 'name' => $alias, 'cols' => $names, 'types' => $plan->types, 'affs' => $plan->affs,
            'colls' => $plan->colls, 'table' => null, 'plan' => $plan];
  }

  /**
   * SELECTの1つ（FROM・WHERE・GROUP BY・結果の列）
   *
   * @return \stdClass  cols、aliases、types、affs、colls、tables、sort、correlated、run（fn(int $limit): array [値, ソートのキー]の配列）
   */
  private function planCore(array $core, ?\stdClass $parent, array $order): \stdClass {
    if (isset($core['sub'])) {
      $p = $this->planSelect($core['sub'], $parent);
      $c = new \stdClass();
      foreach (['cols', 'types', 'affs', 'colls', 'tables', 'correlated'] as $k) $c->$k = $p->$k;
      $c->aliases = array_fill(0, count($p->cols), null);
      $c->sort = [];
      $c->run = fn(int $hint) => array_map(fn($r) => [$r, []], ($p->run)($hint));
      return $c;
    }
    if (isset($core['values'])) return $this->planValues($core['values'], $parent, $order);

    // FROM
    $srcs = [];
    foreach ($core['from'] as $item) {
      if (isset($item['sel'])) {
        $p = $this->planSelect($item['sel'], null);
        $srcs[] = $this->planSource($p, $item['as'] ?? '(subquery-'.count($srcs).')', null);
      } else {
        $srcs[] = $this->sourceFor($item['table'], $item['as'], $parent);
      }
    }
    // USING・NATURAL：右の列を隠して、条件にする
    $joinOn = [];
    foreach ($core['from'] as $j => $item) {
      $on = $item['on'] ?? null;
      $using = $item['using'] ?? null;
      if ($j > 0 && ($item['natural'] || $using)) {
        if ($item['natural']) {
          $using = [];
          $right = array_map('strtolower', $srcs[$j]['cols']);
          for ($k = 0; $k < $j; $k++) {
            foreach ($srcs[$k]['cols'] as $n) if (in_array(strtolower($n), $right, true) && !in_array(strtolower($n), array_map('strtolower', $using), true)) $using[] = $n;
          }
        }
        foreach ($using as $n) {
          $ln = strtolower($n);
          $leftSrc = null;
          for ($k = 0; $k < $j; $k++) if (in_array($ln, array_map('strtolower', $srcs[$k]['cols']), true) && !isset($srcs[$k]['hidden'][$ln])) $leftSrc = $k;
          if ($leftSrc === null || !in_array($ln, array_map('strtolower', $srcs[$j]['cols']), true)) throw new DbException("USINGの列がありません：{$n}", 1, 'HY000');
          $cond = ['t' => 'bin', 'op' => '=', 'l' => ['t' => 'col', 'tbl' => $srcs[$leftSrc]['alias'], 'name' => $n],
                   'r' => ['t' => 'col', 'tbl' => $srcs[$j]['alias'], 'name' => $n]];
          $on = $on ? ['t' => 'bin', 'op' => 'AND', 'l' => $on, 'r' => $cond] : $cond;
          $srcs[$j]['hidden'][$ln] = true;
        }
      }
      $joinOn[$j] = $on;
    }
    $sc = $this->scope($srcs, $parent);

    // 結果の列の別名（WHERE・GROUP BY・HAVINGでも使える、SQLiteの拡張）
    $aliasMap = [];
    foreach ($core['cols'] as $c) {
      if (isset($c['e']) && $c['as'] !== null) $aliasMap[strtolower($c['as'])] ??= $c['e'];
    }
    if ($aliasMap) {
      if ($core['where']) $core['where'] = $this->substAliases($core['where'], $aliasMap, $sc0 = $this->scope($srcs, $parent));
      if ($core['having']) $core['having'] = $this->substAliases($core['having'], $aliasMap, $sc0 ??= $this->scope($srcs, $parent));
      foreach ($core['group'] as $k => $g) $core['group'][$k] = $this->substAliases($g, $aliasMap, $sc0 ??= $this->scope($srcs, $parent));
    }

    // 条件の場所
    $n = count($srcs);
    $where = self::conjuncts($core['where']);
    $levelOn = array_fill(0, max(1, $n), []);
    $levelPost = array_fill(0, max(1, $n), []);
    $levelCand = array_fill(0, max(1, $n), []);
    foreach ($core['from'] as $j => $item) {
      if ($joinOn[$j] === null) continue;
      if ($item['join'] === 'left') {
        foreach (self::conjuncts($joinOn[$j]) as $c) {
          $levelOn[$j][] = $c;
          $levelCand[$j][] = $c;
        }
      } else {
        $where = array_merge($where, self::conjuncts($joinOn[$j]));
      }
    }
    foreach ($where as $c) {
      if (self::hasAggregate($c)) throw new DbException('WHEREに集約関数は使えません', 1, 'HY000');
      $r = $this->refs($c, $sc);
      $lvl = $r === null ? max(0, $n - 1) : (empty($r) ? 0 : max($r));
      $levelPost[$lvl][] = $c;
      // LEFT JOINの右のテーブルの条件は検索に使わない
      if ($r !== null && ($core['from'][$lvl]['join'] ?? 'inner') !== 'left') $levelCand[$lvl][] = $c;
    }
    $levels = [];
    for ($j = 0; $j < $n; $j++) {
      $levels[] = [
        'src' => $this->access($srcs[$j], $j, $levelCand[$j], $sc),
        'on' => array_map(fn($c) => $this->compile($c, $sc), $levelOn[$j]),
        'post' => array_map(fn($c) => $this->compile($c, $sc), $levelPost[$j]),
        'left' => ($core['from'][$j]['join'] ?? 'inner') === 'left',
        'nulls' => array_fill(0, count($srcs[$j]['cols']) + 1, null),
      ];
    }
    $constFilters = $n === 0 ? array_map(fn($c) => $this->compile($c, $sc), $levelPost[0]) : [];

    // 結果の列
    $isAgg = !empty($core['group']) || self::hasAggregate($core['having']);
    foreach ($core['cols'] as $c) if (isset($c['e']) && self::hasAggregate($c['e'])) $isAgg = true;
    foreach ($order as $o) if (self::hasAggregate($o['e'])) $isAgg = true;
    $agg = null;
    if ($isAgg) {
      $agg = new \stdClass();
      $agg->list = [];
      $agg->base = $sc->width;
      $agg->inAgg = false;
    }
    $outFns = $cols = $aliases = $types = $affs = $colls = $tables = [];
    foreach ($core['cols'] as $c) {
      if (array_key_exists('star', $c)) {
        $found = false;
        foreach ($sc->srcs as $j => $s) {
          if ($c['star'] !== null && strcasecmp($c['star'], (string)$s['alias']) !== 0) continue;
          $found = true;
          foreach ($s['cols'] as $i => $name) {
            if ($c['star'] === null && isset($s['hidden'][strtolower($name)])) continue;
            $o = $s['base'] + $i;
            $outFns[] = fn(array $r) => $r[$o] ?? null;
            $cols[] = $name;
            $aliases[] = null;
            $types[] = $s['types'][$i] ?? '';
            $affs[] = $s['affs'][$i] ?? '';
            $colls[] = $s['colls'][$i] ?? 'BINARY';
            $tables[] = $s['table']['name'] ?? '';
          }
        }
        if (!$found) throw new DbException($c['star'] === null ? 'テーブルが指定されていません' : "テーブルがありません：{$c['star']}", 1, 'HY000');
        continue;
      }
      $outFns[] = $this->compile($c['e'], $sc, $agg);
      $name = $c['as'];
      $info = $c['e']['t'] === 'col' ? $this->resolve($sc, $c['e']['tbl'], $c['e']['name']) : null;
      if ($name === null) $name = $info !== null ? $info['name'] : $c['text'];
      $cols[] = $name;
      $aliases[] = $c['as'];
      $types[] = $info['type'] ?? ($c['e']['t'] === 'cast' ? $c['e']['type'] : '');
      $affs[] = $this->exprAff($c['e'], $sc);
      $colls[] = $this->exprColl($c['e'], $sc) ?? 'BINARY';
      $tables[] = $info !== null && $info['level'] === $sc->level ? (string)($sc->srcs[$info['src']]['table']['name'] ?? '') : '';
    }
    $groupFns = array_map(fn($g) => [$this->compileGroupTerm($g, $core, $sc, $cols, $aliases, $outFns), $this->exprColl($g, $sc) ?? 'BINARY'], $core['group']);
    $having = $core['having'] ? $this->compile($core['having'], $sc, $agg) : null;
    if ($having && !$isAgg) throw new DbException('HAVINGはGROUP BYと一緒に使います', 1, 'HY000');

    // ORDER BY（単一のSELECT）
    $sortFns = [];
    $sort = [];
    $tmp = new \stdClass();
    $tmp->cols = $cols;
    $tmp->aliases = $aliases;
    foreach ($order as $o) {
      $k = $this->outputIndex($o['e'], $tmp);
      if ($k !== null) {
        $sortFns[] = ['out', $k];
        $coll = $o['e']['t'] === 'collate' ? $o['e']['coll'] : $colls[$k];
      } else {
        $sortFns[] = ['expr', $this->compile($o['e'], $sc, $agg)];
        $coll = $this->exprColl($o['e'], $sc) ?? 'BINARY';
      }
      $sort[] = [$o['desc'], $o['nulls'], $coll];
    }

    $distinct = $core['distinct'];
    $aggList = $agg ? $agg->list : [];
    $hasGroup = !empty($core['group']);
    $width = $sc->width;
    $out = new \stdClass();
    $out->cols = $cols;
    $out->aliases = $aliases;
    $out->types = $types;
    $out->affs = $affs;
    $out->colls = $colls;
    $out->tables = $tables;
    $out->sort = $sort;
    $out->correlated = $sc->usedOuter;
    $out->run = function(int $hint) use ($levels, $n, $constFilters, $outFns, $sortFns, $distinct, $isAgg, $aggList, $hasGroup, $groupFns, $having, $width): array {
      $emit = function(array $r) use ($outFns, $sortFns): array {
        $vals = [];
        foreach ($outFns as $f) $vals[] = $f($r);
        $keys = [];
        foreach ($sortFns as [$kind, $x]) $keys[] = $kind === 'out' ? $vals[$x] : $x($r);
        return [$vals, $keys];
      };
      $rows = [];
      $seen = [];
      if ($n === 0) {
        foreach ($constFilters as $f) if (!Value::truth($f([]))) return $isAgg && !$hasGroup ? $this->aggregateRows([], $aggList, $hasGroup, $groupFns, $having, $width, $emit, $distinct) : [];
        $gen = (function() { yield []; })();
      } else {
        $gen = $this->joinLoop($levels, 0, []);
      }
      if ($isAgg) return $this->aggregateRows($gen, $aggList, $hasGroup, $groupFns, $having, $width, $emit, $distinct);
      foreach ($gen as $r) {
        $row = $emit($r);
        if ($distinct) {
          $k = implode("\x1F", array_map(fn($v) => Value::key($v), $row[0]));
          if (isset($seen[$k])) continue;
          $seen[$k] = true;
        }
        $rows[] = $row;
        if ($hint >= 0 && count($rows) >= $hint) break;
      }
      return $rows;
    };
    return $out;
  }

  /** 列が見つからない名前を結果の列の別名の式にする */
  private function substAliases(array $e, array $map, \stdClass $sc): array {
    if ($e['t'] === 'col') {
      if ($e['tbl'] === null && isset($map[strtolower($e['name'])]) && $this->resolve($sc, null, $e['name']) === null) return $map[strtolower($e['name'])];
      return $e;
    }
    if (in_array($e['t'], ['sub', 'exists'], true)) return $e;
    foreach ($e as $k => $v) {
      if (!is_array($v) || $k === 'sel') continue;
      if (isset($v['t'])) {
        $e[$k] = $this->substAliases($v, $map, $sc);
      } else {
        foreach ($v as $i => $x) {
          if (is_array($x) && isset($x['t'])) $e[$k][$i] = $this->substAliases($x, $map, $sc);
          else if (is_array($x)) foreach ($x as $m => $y) if (is_array($y) && isset($y['t'])) $e[$k][$i][$m] = $this->substAliases($y, $map, $sc);
        }
      }
    }
    return $e;
  }

  /** GROUP BYの項（番号・別名は結果の列） */
  private function compileGroupTerm(array $g, array $core, \stdClass $sc, array $cols, array $aliases, array $outFns): \Closure {
    if ($g['t'] === 'lit' && is_int($g['v'])) {
      if ($g['v'] < 1 || $g['v'] > count($cols)) throw new DbException("GROUP BYの番号が範囲外です：{$g['v']}", 1, 'HY000');
      $k = $g['v'] - 1;
      $e = $core['cols'][$k]['e'] ?? null;
      if ($e === null) throw new DbException('GROUP BYの番号が「*」です', 1, 'HY000');
      return $this->compile($e, $sc);
    }
    if ($g['t'] === 'col' && $g['tbl'] === null && $this->resolve($sc, null, $g['name']) === null) {
      foreach ($aliases as $k => $a) {
        if ($a !== null && strcasecmp($a, $g['name']) === 0) return $this->compile($core['cols'][$k]['e'], $sc);
      }
    }
    if (self::hasAggregate($g)) throw new DbException('GROUP BYに集約関数は使えません', 1, 'HY000');
    return $this->compile($g, $sc);
  }

  /** 集約（グループ毎） */
  private function aggregateRows(iterable $gen, array $aggList, bool $hasGroup, array $groupFns, ?\Closure $having, int $width, \Closure $emit, bool $distinct): array {
    $groups = [];
    // min()・max()が1つだけなら、他の列はその行の値（SQLiteと同じ）
    $minmax = null;
    if (count($aggList) === 1 && in_array($aggList[0]['name'], ['min', 'max'], true)) $minmax = 0;
    foreach ($gen as $r) {
      $gk = '';
      $gv = [];
      foreach ($groupFns as [$f, $coll]) {
        $v = $f($r);
        $gv[] = $v;
        $gk .= Value::key($v, $coll)."\x1F";
      }
      if (!isset($groups[$gk])) {
        $st = [];
        foreach ($aggList as $a) $st[] = Func::aggInit($a['name']);
        $groups[$gk] = ['row' => $r, 'st' => $st, 'seen' => [], 'key' => $gv];
      }
      $g = &$groups[$gk];
      if ($minmax !== null) $before = $g['st'][0]['v'];
      foreach ($aggList as $k => $a) {
        if ($a['filter'] && !Value::truth(($a['filter'])($r))) continue;
        $args = [];
        foreach ($a['args'] as $f) $args[] = $f($r);
        if ($a['distinct']) {
          if (($args[0] ?? null) === null) continue;
          $dk = Value::key($args[0], $a['coll']);
          if (isset($g['seen'][$k][$dk])) continue;
          $g['seen'][$k][$dk] = true;
        }
        Func::aggStep($a['name'], $g['st'][$k], $args, $a['star'], $a['coll']);
      }
      if ($minmax !== null && $g['st'][0]['v'] !== $before) $g['row'] = $r;
      unset($g);
    }
    // GROUP BYはキーの順番（SQLiteと同じ）
    if ($hasGroup && count($groups) > 1) {
      $spec = array_map(fn($x) => [false, null, $x[1]], $groupFns);
      uasort($groups, fn($a, $b) => self::compareSort($a['key'], $b['key'], $spec));
    }
    if (!$hasGroup && empty($groups)) {
      $st = [];
      foreach ($aggList as $a) $st[] = Func::aggInit($a['name']);
      $groups[''] = ['row' => array_fill(0, $width, null), 'st' => $st];
    }
    $rows = [];
    $seen = [];
    foreach ($groups as $g) {
      $er = $g['row'];
      foreach ($aggList as $k => $a) $er[$width + $k] = Func::aggFinal($a['name'], $g['st'][$k]);
      if ($having && !Value::truth($having($er))) continue;
      $row = $emit($er);
      if ($distinct) {
        $key = implode("\x1F", array_map(fn($v) => Value::key($v), $row[0]));
        if (isset($seen[$key])) continue;
        $seen[$key] = true;
      }
      $rows[] = $row;
    }
    return $rows;
  }

  /** VALUES (…), (…) */
  private function planValues(array $values, ?\stdClass $parent, array $order): \stdClass {
    $sc = $this->scope([], $parent);
    $rows = [];
    $nc = count($values[0]);
    foreach ($values as $row) {
      if (count($row) !== $nc) throw new DbException('VALUESの行の値の数が違います', 1, 'HY000');
      $rows[] = array_map(fn($e) => $this->compile($e, $sc), $row);
    }
    $out = new \stdClass();
    $out->cols = array_map(fn($i) => 'column'.($i + 1), range(0, $nc - 1));
    $out->aliases = array_fill(0, $nc, null);
    $out->types = array_fill(0, $nc, '');
    $out->affs = array_fill(0, $nc, '');
    $out->colls = array_fill(0, $nc, 'BINARY');
    $out->tables = array_fill(0, $nc, '');
    $out->correlated = $sc->usedOuter;
    $sortIdx = [];
    foreach ($order as $o) {
      $k = $this->outputIndex($o['e'], $out);
      if ($k === null) throw new DbException('VALUESのORDER BYは列の番号です', 1, 'HY000');
      $sortIdx[] = $k;
      $out->sort[] = [$o['desc'], $o['nulls'], 'BINARY'];
    }
    $out->sort ??= [];
    $out->run = function(int $hint) use ($rows, $sortIdx): array {
      $res = [];
      foreach ($rows as $fns) {
        $vals = array_map(fn($f) => $f([]), $fns);
        $res[] = [$vals, array_map(fn($k) => $vals[$k], $sortIdx)];
      }
      return $res;
    };
    return $out;
  }

  /** 入れ子のループ（LEFT JOINはNULLの行も） */
  private function joinLoop(array $levels, int $j, array $row): \Generator {
    if ($j === count($levels)) {
      yield $row;
      return;
    }
    $L = $levels[$j];
    $matched = false;
    foreach (($L['src'])($row) as $vals) {
      $r = $j === 0 ? $vals : array_merge($row, $vals);
      foreach ($L['on'] as $f) if (!Value::truth($f($r))) continue 2;
      $matched = true;
      foreach ($L['post'] as $f) if (!Value::truth($f($r))) continue 2;
      yield from $this->joinLoop($levels, $j + 1, $r);
    }
    if (!$matched && $L['left']) {
      $r = array_merge($row, $L['nulls']);
      foreach ($L['post'] as $f) if (!Value::truth($f($r))) return;
      yield from $this->joinLoop($levels, $j + 1, $r);
    }
  }

  ///////////////
  // テーブルの読み方（rowid、インデックス、全部）

  /** テーブルの行の値（足りない列はDEFAULT、rowidの別名、最後にrowid） */
  private function rowReader(array $t): \Closure {
    $nc = count($t['cols']);
    $ipk = $t['ipk'] ?? null;
    $defaults = [];
    foreach ($t['cols'] as $i => $c) {
      $defaults[$i] = $c['default'] !== null ? Value::apply($this->constValue($c['default']), $c['aff']) : null;
    }
    return function(int $rowid, string $rec) use ($nc, $ipk, $defaults): array {
      $v = Record::decode($rec);
      $k = count($v);
      if ($k < $nc) {
        for ($i = $k; $i < $nc; $i++) $v[$i] = $defaults[$i];
      } else if ($k > $nc) {
        $v = array_slice($v, 0, $nc);
      }
      if ($ipk !== null) $v[$ipk] = $rowid;
      $v[] = $rowid;
      return $v;
    };
  }

  /** 定数の式の値（DEFAULT） */
  private function constValue(array $e): mixed {
    return ($this->compile($e, $this->scope([], null)))([]);
  }

  /**
   * ソースの行を出すクロージャ（条件からrowid・インデックスを選ぶ）
   *
   * @return \Closure  fn(array $row): iterable 値の配列
   */
  private function access(array $src, int $j, array $cands, \stdClass $sc): \Closure {
    if (isset($src['plan'])) {
      $plan = $src['plan'];
      $cache = null;
      return function(array $row) use ($plan, &$cache) {
        if ($cache === null) {
          $cache = [];
          foreach (($plan->run)(-1) as $vals) {
            $vals[] = null;
            $cache[] = $vals;
          }
        }
        return $cache;
      };
    }
    $t = $src['table'];
    $root = $t['root'];
    $read = $this->rowReader($t);
    $this->lastAccess = null;
    $bt = $this->bt;
    $schema = $this->schema();

    // 条件を調べる：このソースの列 演算子 他のソース・定数の式
    $eq = [];
    $ranges = [];
    $ins = [];
    foreach ($cands as $c) {
      if ($c['t'] === 'bin' && in_array($c['op'], ['=', '<', '<=', '>', '>='], true)) {
        foreach ([[$c['l'], $c['r'], $c['op']], [$c['r'], $c['l'], self::flip($c['op'])]] as [$a, $b, $op]) {
          $col = $this->columnOf($a, $j, $sc);
          if ($col === null) continue;
          $r = $this->refs($b, $sc);
          if ($r === null || in_array($j, $r, true) || ($r && max($r) > $j)) continue;
          $info = ['fn' => $this->compile($b, $sc), 'aff' => $this->exprAff($b, $sc), 'coll' => $this->cmpColl($c['l'], $c['r'], $sc), 'op' => $op];
          if ($op === '=') $eq[$col] ??= $info;
          else $ranges[$col][] = $info;
          break;
        }
      } else if ($c['t'] === 'in' && !$c['not'] && $c['list'] !== null && count($c['list']) > 0) {
        $col = $this->columnOf($c['e'], $j, $sc);
        if ($col === null) continue;
        $ok = true;
        $fns = [];
        foreach ($c['list'] as $x) {
          $r = $this->refs($x, $sc);
          if ($r === null || in_array($j, $r, true) || ($r && max($r) > $j)) {
            $ok = false;
            break;
          }
          $fns[] = [$this->compile($x, $sc), $this->exprAff($x, $sc), $this->cmpColl($c['e'], $x, $sc)];
        }
        if ($ok) $ins[$col] ??= $fns;
      } else if ($c['t'] === 'between' && !$c['not']) {
        $col = $this->columnOf($c['e'], $j, $sc);
        if ($col === null) continue;
        $r1 = $this->refs($c['lo'], $sc);
        $r2 = $this->refs($c['hi'], $sc);
        if ($r1 === null || $r2 === null || in_array($j, array_merge($r1, $r2), true) || (($r1 || $r2) && max(array_merge($r1, $r2)) > $j)) continue;
        $ranges[$col][] = ['fn' => $this->compile($c['lo'], $sc), 'aff' => $this->exprAff($c['lo'], $sc), 'coll' => $this->cmpColl($c['e'], $c['lo'], $sc), 'op' => '>='];
        $ranges[$col][] = ['fn' => $this->compile($c['hi'], $sc), 'aff' => $this->exprAff($c['hi'], $sc), 'coll' => $this->cmpColl($c['e'], $c['hi'], $sc), 'op' => '<='];
      }
    }

    // rowid
    if (isset($eq['rowid'])) {
      $f = $eq['rowid']['fn'];
      $aff = $eq['rowid']['aff'];
      return function(array $row) use ($f, $aff, $bt, $root, $read) {
        $id = self::rowidKey($f($row), $aff);
        if ($id === null) return;
        $rec = $bt->get($root, $id);
        if ($rec !== null) yield $read($id, $rec);
      };
    }
    if (isset($ins['rowid'])) {
      $fns = $ins['rowid'];
      return function(array $row) use ($fns, $bt, $root, $read) {
        $ids = [];
        foreach ($fns as [$f, $aff]) {
          $id = self::rowidKey($f($row), $aff);
          if ($id !== null) $ids[$id] = true;
        }
        $ids = array_keys($ids);
        sort($ids);
        foreach ($ids as $id) {
          $rec = $bt->get($root, $id);
          if ($rec !== null) yield $read($id, $rec);
        }
      };
    }

    // インデックス（等号の列が一番多い物）
    $best = null;
    $bestN = 0;
    foreach ($t['indexes'] as $iname) {
      $idx = $schema->indexes[$iname];
      $k = 0;
      foreach ($idx['cols'] as $p => $ci) {
        if (!isset($eq[$ci]) || $eq[$ci]['coll'] !== $idx['colls'][$p]) break;
        $k++;
      }
      $score = $k * 2 + ($idx['unique'] && $k === count($idx['cols']) ? 1 : 0);
      if ($k > 0 && $score > $bestN) {
        $best = $idx;
        $bestN = $score;
      }
    }
    if ($best !== null) {
      $this->lastAccess = $best['cols'];
      $parts = [];
      foreach ($best['cols'] as $p => $ci) {
        if (!isset($eq[$ci]) || $eq[$ci]['coll'] !== $best['colls'][$p]) break;
        $parts[] = [$eq[$ci]['fn'], $eq[$ci]['aff'], $t['cols'][$ci]['aff']];
      }
      return function(array $row) use ($parts, $best, $bt, $root, $read) {
        $key = [];
        foreach ($parts as [$f, $aff, $colAff]) {
          $v = $f($row);
          if ($v === null) return;
          $key[] = self::lookupValue($v, $colAff, $aff);
        }
        $ids = [];
        foreach ($bt->indexScan($best, $key, true, $key, true) as $k) $ids[] = end($k);
        foreach ($ids as $id) {
          $rec = $bt->get($root, $id);
          if ($rec !== null) yield $read($id, $rec);
        }
      };
    }
    // INのインデックス（最初の列）
    foreach ($ins as $ci => $fns) {
      if ($ci === 'rowid') continue;
      foreach ($t['indexes'] as $iname) {
        $idx = $schema->indexes[$iname];
        if ($idx['cols'][0] !== $ci || $fns[0][2] !== $idx['colls'][0]) continue;
        $this->lastAccess = $idx['cols'];
        $colAff = $t['cols'][$ci]['aff'];
        return function(array $row) use ($fns, $idx, $bt, $root, $read, $colAff) {
          $done = [];
          foreach ($fns as [$f, $aff]) {
            $v = $f($row);
            if ($v === null) continue;
            $v = self::lookupValue($v, $colAff, $aff);
            $dk = Value::key($v);
            if (isset($done[$dk])) continue;
            $done[$dk] = true;
            $ids = [];
            foreach ($bt->indexScan($idx, [$v], true, [$v], true) as $k) $ids[] = end($k);
            foreach ($ids as $id) {
              $rec = $bt->get($root, $id);
              if ($rec !== null) yield $read($id, $rec);
            }
          }
        };
      }
    }
    // rowidの範囲
    if (isset($ranges['rowid'])) {
      $rs = $ranges['rowid'];
      return function(array $row) use ($rs, $bt, $root, $read) {
        $from = null;
        $to = null;
        foreach ($rs as $r) {
          $v = $r['fn']($row);
          if ($v === null) return;
          if (is_string($v)) $v = Value::apply($v, Value::NUMERIC);
          if (!is_int($v) && !is_float($v)) {
            // 文字列・BLOBは全部の数値より大きい
            if ($r['op'] === '>' || $r['op'] === '>=') return;
            continue;
          }
          [$lo, $hi] = self::rowidBounds($r['op'], $v);
          if ($lo !== null) $from = $from === null ? $lo : max($from, $lo);
          if ($hi !== null) $to = $to === null ? $hi : min($to, $hi);
        }
        if ($from !== null && $to !== null && $from > $to) return;
        foreach ($bt->scan($root, $from, $to) as $id => $rec) yield $read($id, $rec);
      };
    }
    // インデックスの範囲（最初の列、昇順）
    foreach ($ranges as $ci => $rs) {
      if ($ci === 'rowid') continue;
      foreach ($t['indexes'] as $iname) {
        $idx = $schema->indexes[$iname];
        if ($idx['cols'][0] !== $ci || $idx['desc'][0]) continue;
        $ok = true;
        foreach ($rs as $r) if ($r['coll'] !== $idx['colls'][0]) $ok = false;
        if (!$ok) continue;
        $this->lastAccess = $idx['cols'];
        $colAff = $t['cols'][$ci]['aff'];
        return function(array $row) use ($rs, $idx, $bt, $root, $read, $colAff) {
          $lo = [null];
          $loInc = false;
          $hi = null;
          $hiInc = true;
          foreach ($rs as $r) {
            $v = $r['fn']($row);
            if ($v === null) return;
            $v = self::lookupValue($v, $colAff, $r['aff']);
            if ($r['op'] === '>' || $r['op'] === '>=') {
              $c = $lo[0] === null ? 1 : Record::compare($v, $lo[0], $idx['colls'][0]);
              if ($c > 0 || ($c === 0 && $r['op'] === '>')) {
                $lo = [$v];
                $loInc = $r['op'] === '>=';
              }
            } else {
              $c = $hi === null ? -1 : Record::compare($v, $hi[0], $idx['colls'][0]);
              if ($c < 0 || ($c === 0 && $r['op'] === '<')) {
                $hi = [$v];
                $hiInc = $r['op'] === '<=';
              }
            }
          }
          $ids = [];
          foreach ($bt->indexScan($idx, $lo, $loInc, $hi, $hiInc) as $k) $ids[] = end($k);
          foreach ($ids as $id) {
            $rec = $bt->get($root, $id);
            if ($rec !== null) yield $read($id, $rec);
          }
        };
      }
    }
    // 全部
    return function(array $row) use ($bt, $root, $read) {
      foreach ($bt->scan($root) as $id => $rec) yield $read($id, $rec);
    };
  }

  private static function flip(string $op): string {
    return match ($op) { '<' => '>', '<=' => '>=', '>' => '<', '>=' => '<=', default => $op };
  }

  /** 式がソース$jの列なら列の番号（rowidは'rowid'） */
  private function columnOf(array $e, int $j, \stdClass $sc): int|string|null {
    if ($e['t'] !== 'col') return null;
    $c = $this->resolve($sc, $e['tbl'], $e['name']);
    if ($c === null || $c['level'] !== $sc->level || $c['src'] !== $j) return null;
    $src = $sc->srcs[$j];
    if ($c['off'] === $src['rowid']) return 'rowid';
    if (($src['table']['ipk'] ?? null) === $c['col']) return 'rowid';
    return $c['col'];
  }

  /** rowidの検索の値（整数でなければnull） */
  private static function rowidKey(mixed $v, string $aff): ?int {
    if ($v === null) return null;
    if (is_string($v)) $v = Value::apply($v, Value::NUMERIC);
    if (is_float($v) && floor($v) === $v && abs($v) < 9.2e18) $v = (int)$v;
    return is_int($v) ? $v : null;
  }

  private static function rowidBounds(string $op, int|float $v): array {
    if (is_float($v)) {
      if (is_nan($v)) return [PHP_INT_MAX, PHP_INT_MIN];
      if ($v >= 9.2233720368547758e18) return $op === '>' || $op === '>=' ? [PHP_INT_MAX, null] : [null, PHP_INT_MAX];
      if ($v <= -9.2233720368547758e18) return $op === '>' || $op === '>=' ? [PHP_INT_MIN, null] : [null, PHP_INT_MIN];
      $fl = (int)floor($v);
      $ce = (int)ceil($v);
      return match ($op) { '>' => [$fl + 1, null], '>=' => [$ce, null], '<' => [null, $ce - 1], default => [null, $fl] };
    }
    return match ($op) {
      '>' => [$v === PHP_INT_MAX ? PHP_INT_MAX : $v + 1, $v === PHP_INT_MAX ? PHP_INT_MIN : null],
      '>=' => [$v, null],
      '<' => [null, $v === PHP_INT_MIN ? PHP_INT_MIN : $v - 1],
      default => [null, $v],
    };
  }

  /** インデックスで探す値（比較と同じ親和性） */
  private static function lookupValue(mixed $v, string $colAff, string $exprAff): mixed {
    [$col, $x] = Value::prepareCompare(null, $colAff, $v, $exprAff);
    return $x;
  }

  ///////////////
  // INSERT・UPDATE・DELETE

  private function writableTable(string $name): array {
    $schema = $this->schema();
    $l = strtolower($name);
    if (isset($schema->views[$l])) throw new DbException("ビューは変更できません：{$name}", 1, 'HY000');
    $t = $schema->table_($name);
    if ($t === null) throw new DbException("テーブルがありません：{$name}", 1, 'HY000');
    if (isset($t['unsupported'])) throw new DbException($t['unsupported'], 1, 'HY000');
    if (!empty($t['system'])) throw new DbException("テーブル{$t['name']}は変更できません", 1, 'HY000');
    if (isset($schema->triggers[strtolower($t['name'])])) throw new DbException("トリガーの有るテーブルの変更は未対応です：{$t['name']}", 1, 'HY000');
    return $t;
  }

  /** テーブルのソース（スコープ用） */
  private function tableSource(array $t, ?string $alias): array {
    return ['alias' => $alias ?? $t['name'], 'name' => $t['name'], 'cols' => array_column($t['cols'], 'name'), 'types' => array_column($t['cols'], 'type'),
            'affs' => array_column($t['cols'], 'aff'), 'colls' => array_column($t['cols'], 'collate'), 'table' => $t];
  }

  /** RETURNING */
  private function returningPlan(?array $ret, array $t, ?string $alias): ?\stdClass {
    if ($ret === null) return null;
    $sc = $this->scope([$this->tableSource($t, $alias)], null);
    $p = new \stdClass();
    $p->fns = $p->cols = $p->types = [];
    foreach ($ret as $c) {
      if (array_key_exists('star', $c)) {
        foreach ($t['cols'] as $i => $col) {
          $p->fns[] = fn(array $r) => $r[$i] ?? null;
          $p->cols[] = $col['name'];
          $p->types[] = $col['type'];
        }
        continue;
      }
      if (self::hasAggregate($c['e'])) throw new DbException('RETURNINGに集約関数は使えません', 1, 'HY000');
      $p->fns[] = $this->compile($c['e'], $sc);
      $info = $c['e']['t'] === 'col' ? $this->resolve($sc, $c['e']['tbl'], $c['e']['name']) : null;
      $p->cols[] = $c['as'] ?? ($info['name'] ?? $c['text']);
      $p->types[] = $info['type'] ?? '';
    }
    $p->rows = [];
    return $p;
  }

  private function checks(array $t): array {
    if (empty($t['checks'])) return [];
    $sc = $this->scope([$this->tableSource($t, null)], null);
    return array_map(fn($c) => $this->compile($c, $sc), $t['checks']);
  }

  private function insert(array $st): array {
    $t = $this->writableTable($st['table']);
    $nc = count($t['cols']);
    $targets = [];
    if ($st['cols'] !== null) {
      foreach ($st['cols'] as $n) {
        $ln = strtolower($n);
        $ci = $t['map'][$ln] ?? (in_array($ln, ['rowid', 'oid', '_rowid_'], true) ? 'rowid' : null);
        if ($ci === null) throw new DbException("テーブル{$t['name']}に列がありません：{$n}", 1, 'HY000');
        $targets[] = $ci;
      }
    } else {
      $targets = array_keys($t['cols']);
    }
    // 値の行
    $rows = [];
    if ($st['default']) {
      $rows[] = [];
    } else {
      $sel = $st['sel'];
      $simple = count($sel['cores']) === 1 && isset($sel['cores'][0]['values']) && !$sel['order'] && !$sel['limit'] && empty($sel['with']);
      if ($simple) {
        $sc = $this->scope([], null);
        foreach ($sel['cores'][0]['values'] as $vals) {
          if (count($vals) !== count($targets)) throw new DbException("{$t['name']}は".count($targets).'列ですが、'.count($vals).'個の値が有ります', 1, 'HY000');
          $rows[] = array_map(fn($e) => ($this->compile($e, $sc))([]), $vals);
        }
      } else {
        $plan = $this->planSelect($sel, null);
        if (count($plan->cols) !== count($targets)) throw new DbException("{$t['name']}は".count($targets).'列ですが、'.count($plan->cols).'個の値が有ります', 1, 'HY000');
        $rows = ($plan->run)(-1);
      }
    }
    $defaults = [];
    foreach ($t['cols'] as $i => $c) $defaults[$i] = $c['default'] !== null ? $this->compile($c['default'], $this->scope([], null)) : null;
    $checks = $this->checks($t);
    $ret = $this->returningPlan($st['returning'], $t, $st['alias']);
    $upsert = $this->upsertPlan($st, $t);
    $or = $st['or'] ?? 'ABORT';

    foreach ($rows as $vals) {
      $row = array_fill(0, $nc, null);
      $given = array_fill(0, $nc, false);
      $rowid = null;
      foreach ($targets as $k => $ci) {
        if ($ci === 'rowid') {
          $rowid = $vals[$k];
          continue;
        }
        $row[$ci] = $vals[$k];
        $given[$ci] = true;
      }
      foreach ($t['cols'] as $i => $c) {
        if (!$given[$i] && $defaults[$i] !== null) $row[$i] = $defaults[$i]([]);
      }
      if ($t['ipk'] !== null && $row[$t['ipk']] !== null) $rowid = $row[$t['ipk']];
      $res = $this->insertRow($t, $row, $rowid, $or, $checks, $upsert);
      if ($res !== null && $ret) {
        $r = $res;
        $ret->rows[] = array_map(fn($f) => $f($r), $ret->fns);
      }
    }
    if ($ret) return self::result($ret->cols, $ret->types, $ret->rows, $this->changes);
    return self::result([], [], [], $this->changes);
  }

  /** ON CONFLICT … DO UPDATE */
  private function upsertPlan(array $st, array $t): array {
    $out = [];
    foreach ($st['upsert'] as $u) {
      $p = ['target' => null, 'nothing' => $u['nothing']];
      if ($u['target'] !== null) {
        $p['target'] = array_map(fn($n) => $t['map'][strtolower($n)] ?? throw new DbException("列がありません：{$n}", 1, 'HY000'), $u['target']);
        sort($p['target']);
        $ok = $t['ipk'] !== null && $p['target'] === [$t['ipk']];
        foreach ($t['indexes'] as $iname) {
          $idx = $this->schema()->indexes[$iname];
          $cols = $idx['cols'];
          sort($cols);
          if ($idx['unique'] && $cols === $p['target']) $ok = true;
        }
        if (!$ok) throw new DbException('ON CONFLICTの列がPRIMARY KEY・UNIQUEの制約と合いません', 1, 'HY000');
      }
      if (!$u['nothing']) {
        // excludedの列は「excluded.」を付けた場合だけ
        $ex = ['alias' => 'excluded', 'name' => 'excluded'] + array_slice($this->tableSource($t, null), 2);
        $ex['hidden'] = array_fill_keys(array_map('strtolower', $ex['cols']), true);
        $srcs = [$this->tableSource($t, $st['alias']), $ex];
        $sc = $this->scope($srcs, null);
        $p['set'] = [];
        foreach ($u['set'] as [$n, $e]) {
          $ci = $t['map'][strtolower($n)] ?? null;
          if ($ci === null) throw new DbException("列がありません：{$n}", 1, 'HY000');
          $p['set'][] = [$ci, $this->compile($e, $sc)];
        }
        $p['where'] = $u['where'] ? $this->compile($u['where'], $sc) : null;
      }
      $out[] = $p;
    }
    return $out;
  }

  /**
   * 1行を入れる（制約の確認と衝突の解決）
   *
   * @return array|null  入れた行（値…、rowid）、入れなかった場合null
   */
  private function insertRow(array $t, array $row, mixed $rowid, string $or, array $checks, array $upsert = []): ?array {
    $nc = count($t['cols']);
    foreach ($t['cols'] as $i => $c) {
      if ($i !== $t['ipk']) $row[$i] = Value::apply($row[$i], $c['aff']);
    }
    if ($rowid !== null) {
      $rowid = Value::apply($rowid, Value::INTEGER);
      if (!is_int($rowid)) throw new DbException('データ型が違います（rowidは整数）', 20, 'HY000');
    }
    // NOT NULL
    foreach ($t['cols'] as $i => $c) {
      if ($i === $t['ipk'] || !$c['notNull'] || $row[$i] !== null) continue;
      if ($or === 'REPLACE' && $c['default'] !== null) {
        $row[$i] = Value::apply($this->constValue($c['default']), $c['aff']);
        if ($row[$i] !== null) continue;
      }
      if ($or === 'IGNORE') return null;
      $this->conflictFail($or);
      throw new DbException("NOT NULL制約に違反しました：{$t['name']}.{$c['name']}", 19, '23000');
    }
    $full = $row;
    $full[] = $rowid;
    if ($t['ipk'] !== null) $full[$t['ipk']] = $rowid;
    foreach ($checks as $k => $f) {
      if (Value::truth($f($full)) === false) {
        if ($or === 'IGNORE') return null;
        $this->conflictFail($or);
        throw new DbException("CHECK制約に違反しました：{$t['name']}", 19, '23000');
      }
    }

    // 一意の制約（新しいrowidは先に決める：REPLACEで一番大きいrowidの行を消しても、同じrowidにならない）
    $schema = $this->schema();
    $auto = $rowid === null;
    if ($auto) $rowid = $this->newRowid($t);
    while (true) {
      $conflict = null;
      if (!$auto && $this->bt->get($t['root'], $rowid) !== null) {
        $conflict = [$rowid, $t['ipk'] !== null ? [$t['ipk']] : ['rowid'], 'PRIMARY KEY'];
      }
      if ($conflict === null) {
        foreach ($t['indexes'] as $iname) {
          $idx = $schema->indexes[$iname];
          if (!$idx['unique']) continue;
          $other = $this->uniqueConflict($idx, $row, null);
          if ($other !== null) {
            $conflict = [$other, $idx['cols'], 'UNIQUE'];
            break;
          }
        }
      }
      if ($conflict === null) break;
      [$other, $cols] = $conflict;
      // ON CONFLICT
      foreach ($upsert as $u) {
        $sorted = $cols;
        sort($sorted);
        if ($u['target'] !== null && $u['target'] !== $sorted && !($sorted === ['rowid'] && $u['target'] === [$t['ipk']])) continue;
        if ($u['nothing']) return null;
        $this->upsertUpdate($t, $other, $full, $u);
        return null;
      }
      if (!empty($upsert) && $or === 'ABORT') {
        // 指定した制約以外の衝突
      }
      if ($or === 'REPLACE') {
        $this->deleteRow($t, $other);
        continue;
      }
      if ($or === 'IGNORE') return null;
      $this->conflictFail($or);
      $names = implode(', ', array_map(fn($ci) => $t['name'].'.'.($ci === 'rowid' ? 'rowid' : $t['cols'][$ci]['name']), $cols));
      throw new DbException("UNIQUE制約に違反しました：{$names}", 19, '23000');
    }

    $full[$nc] = $rowid;
    if ($t['ipk'] !== null) {
      $full[$t['ipk']] = $rowid;
      $row[$t['ipk']] = null;
    }
    $this->bt->insert($t['root'], $rowid, Record::encode($row));
    $vals = array_slice($full, 0, $nc);
    foreach ($t['indexes'] as $iname) {
      $idx = $schema->indexes[$iname];
      $this->bt->indexInsert($idx, $this->indexKey($idx, $vals, $rowid));
    }
    if ($t['autoinc']) $this->updateSequence($t, $rowid);
    $this->lastInsertRowid = $rowid;
    $this->changes++;
    $this->totalChanges++;
    return $full;
  }

  /** FAIL・ROLLBACKの衝突 */
  private function conflictFail(string $or): void {
    if ($or === 'FAIL') $this->failKeep = true;
    if ($or === 'ROLLBACK') $this->rollbackAll = true;
  }

  private function indexKey(array $idx, array $vals, int $rowid): array {
    $k = [];
    foreach ($idx['cols'] as $ci) $k[] = $vals[$ci];
    $k[] = $rowid;
    return $k;
  }

  /** 一意のインデックスで同じキーの他の行（無ければnull） */
  private function uniqueConflict(array $idx, array $vals, ?int $self): ?int {
    $key = [];
    foreach ($idx['cols'] as $ci) {
      if ($vals[$ci] === null) return null;
      $key[] = $vals[$ci];
    }
    return $this->bt->indexFind($idx, $key, $self);
  }

  private function newRowid(array $t): int {
    $max = $this->bt->maxRowid($t['root']);
    if ($t['autoinc']) {
      $seq = $this->sequenceValue($t['name']);
      $max = max($max, $seq);
      if ($max === PHP_INT_MAX) throw new DbException('データベースが一杯です（AUTOINCREMENT）', 13, 'HY000');
      return $max + 1;
    }
    if ($max < PHP_INT_MAX) return $max + 1;
    for ($i = 0; $i < 100; $i++) {
      $id = random_int(1, PHP_INT_MAX);
      if ($this->bt->get($t['root'], $id) === null) return $id;
    }
    throw new DbException('データベースが一杯です', 13, 'HY000');
  }

  private function sequenceValue(string $table): int {
    $seq = $this->schema()->table_('sqlite_sequence');
    if ($seq === null) return 0;
    foreach ($this->bt->scan($seq['root']) as $rec) {
      $v = Record::decode($rec);
      if (strcasecmp((string)($v[0] ?? ''), $table) === 0) return (int)($v[1] ?? 0);
    }
    return 0;
  }

  private function updateSequence(array $t, int $rowid): void {
    $seq = $this->schema()->table_('sqlite_sequence');
    if ($seq === null) return;
    foreach ($this->bt->scan($seq['root']) as $id => $rec) {
      $v = Record::decode($rec);
      if (strcasecmp((string)($v[0] ?? ''), $t['name']) === 0) {
        if ((int)($v[1] ?? 0) < $rowid) $this->bt->insert($seq['root'], $id, Record::encode([$v[0], $rowid]));
        return;
      }
    }
    $this->bt->insert($seq['root'], $this->bt->maxRowid($seq['root']) + 1, Record::encode([$t['name'], $rowid]));
  }

  private function upsertUpdate(array $t, int $rowid, array $excluded, array $u): void {
    $rec = $this->bt->get($t['root'], $rowid);
    if ($rec === null) return;
    $old = $this->rowReader($t)($rowid, $rec);
    $r = array_merge($old, $excluded);
    if ($u['where'] && !Value::truth(($u['where'])($r))) return;
    $new = array_slice($old, 0, count($t['cols']));
    foreach ($u['set'] as [$ci, $f]) $new[$ci] = $f($r);
    $this->updateRow($t, $rowid, $old, $new, 'ABORT', $this->checks($t));
  }

  /** 行を消す（インデックスも） */
  private function deleteRow(array $t, int $rowid, ?array $vals = null): void {
    if ($vals === null) {
      $rec = $this->bt->get($t['root'], $rowid);
      if ($rec === null) return;
      $vals = $this->rowReader($t)($rowid, $rec);
    }
    $schema = $this->schema();
    foreach ($t['indexes'] as $iname) {
      $idx = $schema->indexes[$iname];
      $this->bt->indexDelete($idx, $this->indexKey($idx, $vals, $rowid));
    }
    $this->bt->delete($t['root'], $rowid);
  }

  /**
   * 1行を変える
   *
   * @param array $old  元の値（…、rowid）
   * @param array $new  新しい列の値
   * @return array|null  変えた行（値…、rowid）、変えなかった場合null
   */
  private function updateRow(array $t, int $rowid, array $old, array $new, string $or, array $checks, mixed $newRowid = null): ?array {
    $nc = count($t['cols']);
    foreach ($t['cols'] as $i => $c) {
      if ($i !== $t['ipk']) $new[$i] = Value::apply($new[$i], $c['aff']);
    }
    $target = $rowid;
    if ($t['ipk'] !== null && $new[$t['ipk']] !== $old[$t['ipk']]) $newRowid = $new[$t['ipk']];
    if ($newRowid !== null) {
      $newRowid = Value::apply($newRowid, Value::INTEGER);
      if (!is_int($newRowid)) throw new DbException('データ型が違います（rowidは整数）', 20, 'HY000');
      $target = $newRowid;
    } else if ($t['ipk'] !== null && $new[$t['ipk']] === null) {
      throw new DbException("NOT NULL制約に違反しました：{$t['name']}.{$t['cols'][$t['ipk']]['name']}", 19, '23000');
    }
    foreach ($t['cols'] as $i => $c) {
      if ($i === $t['ipk'] || !$c['notNull'] || $new[$i] !== null) continue;
      if ($or === 'REPLACE' && $c['default'] !== null) {
        $new[$i] = Value::apply($this->constValue($c['default']), $c['aff']);
        if ($new[$i] !== null) continue;
      }
      if ($or === 'IGNORE') return null;
      $this->conflictFail($or);
      throw new DbException("NOT NULL制約に違反しました：{$t['name']}.{$c['name']}", 19, '23000');
    }
    $full = $new;
    $full[$nc] = $target;
    if ($t['ipk'] !== null) $full[$t['ipk']] = $target;
    foreach ($checks as $f) {
      if (Value::truth($f($full)) === false) {
        if ($or === 'IGNORE') return null;
        $this->conflictFail($or);
        throw new DbException("CHECK制約に違反しました：{$t['name']}", 19, '23000');
      }
    }
    $schema = $this->schema();
    while (true) {
      $conflict = null;
      if ($target !== $rowid && $this->bt->get($t['root'], $target) !== null) $conflict = [$target, $t['ipk'] !== null ? [$t['ipk']] : ['rowid']];
      if ($conflict === null) {
        foreach ($t['indexes'] as $iname) {
          $idx = $schema->indexes[$iname];
          if (!$idx['unique']) continue;
          $other = $this->uniqueConflict($idx, array_slice($full, 0, $nc), $rowid);
          if ($other !== null) {
            $conflict = [$other, $idx['cols']];
            break;
          }
        }
      }
      if ($conflict === null) break;
      if ($or === 'REPLACE') {
        $this->deleteRow($t, $conflict[0]);
        continue;
      }
      if ($or === 'IGNORE') return null;
      $this->conflictFail($or);
      $names = implode(', ', array_map(fn($ci) => $t['name'].'.'.($ci === 'rowid' ? 'rowid' : $t['cols'][$ci]['name']), $conflict[1]));
      throw new DbException("UNIQUE制約に違反しました：{$names}", 19, '23000');
    }
    $oldVals = array_slice($old, 0, $nc);
    $newVals = array_slice($full, 0, $nc);
    foreach ($t['indexes'] as $iname) {
      $idx = $schema->indexes[$iname];
      $ok = $this->indexKey($idx, $oldVals, $rowid);
      $nk = $this->indexKey($idx, $newVals, $target);
      if ($target === $rowid && self::sameKey($ok, $nk)) continue;
      $this->bt->indexDelete($idx, $ok);
      $this->bt->indexInsert($idx, $nk);
    }
    $rec = $new;
    if ($t['ipk'] !== null) $rec[$t['ipk']] = null;
    if ($target !== $rowid) $this->bt->delete($t['root'], $rowid);
    $this->bt->insert($t['root'], $target, Record::encode($rec));
    $this->changes++;
    $this->totalChanges++;
    return $full;
  }

  private static function sameKey(array $a, array $b): bool {
    foreach ($a as $i => $v) {
      if (Value::type($v) !== Value::type($b[$i]) || Record::compare($v, $b[$i]) !== 0) return false;
    }
    return true;
  }

  /** UPDATE・DELETEの対象の行 */
  private function targetRows(array $t, ?string $alias, array $from, ?array $where, ?array &$setScope = null, array $setCols = []): array {
    $srcs = [$this->tableSource($t, $alias)];
    foreach ($from as $item) {
      if (isset($item['sel'])) {
        $srcs[] = $this->planSource($this->planSelect($item['sel'], null), $item['as'] ?? '(subquery)', null);
      } else {
        $srcs[] = $this->sourceFor($item['table'], $item['as'], null);
      }
    }
    $core = ['distinct' => false, 'cols' => [], 'from' => [], 'where' => $where, 'group' => [], 'having' => null];
    $sc = $this->scope($srcs, null);
    $setScope = [$sc];
    $n = count($srcs);
    $conj = self::conjuncts($where);
    $levelPost = array_fill(0, $n, []);
    $levelCand = array_fill(0, $n, []);
    foreach ($from as $j => $item) {
      if (!empty($item['on'])) $conj = array_merge($conj, self::conjuncts($item['on']));
    }
    foreach ($conj as $c) {
      if (self::hasAggregate($c)) throw new DbException('WHEREに集約関数は使えません', 1, 'HY000');
      $r = $this->refs($c, $sc);
      $lvl = $r === null ? $n - 1 : (empty($r) ? 0 : max($r));
      $levelPost[$lvl][] = $c;
      if ($r !== null) $levelCand[$lvl][] = $c;
    }
    $levels = [];
    $byIndex = null;
    for ($j = 0; $j < $n; $j++) {
      $levels[] = ['src' => $this->access($srcs[$j], $j, $levelCand[$j], $sc), 'on' => [],
                   'post' => array_map(fn($c) => $this->compile($c, $sc), $levelPost[$j]), 'left' => false, 'nulls' => []];
      if ($j === 0) $byIndex = $this->lastAccess;
    }
    $out = [];
    $nc = count($t['cols']);
    foreach ($this->joinLoop($levels, 0, []) as $r) {
      $id = $r[$nc];
      if (!isset($out[$id])) $out[$id] = $r;
    }
    // 変える列のインデックスで読んだ場合は、rowidの順番（SQLiteと同じ）
    if ($n > 1 || $byIndex === null || in_array('*', $setCols, true) || array_intersect($byIndex, $setCols)) ksort($out);
    return $out;
  }

  private function update(array $st): array {
    $t = $this->writableTable($st['table']);
    $nc = count($t['cols']);
    $setCols = [];
    foreach ($st['set'] as [$n]) $setCols[] = $t['map'][strtolower($n)] ?? 'rowid';
    // REPLACEは行を消すかもしれないので、rowidの順番（SQLiteと同じ）
    if (($st['or'] ?? '') === 'REPLACE') $setCols[] = '*';
    $rows = $this->targetRows($t, $st['alias'], $st['from'], $st['where'], $scopes, $setCols);
    $sc = $scopes[0];
    $sets = [];
    foreach ($st['set'] as [$n, $e]) {
      $ln = strtolower($n);
      $ci = $t['map'][$ln] ?? (in_array($ln, ['rowid', 'oid', '_rowid_'], true) ? 'rowid' : null);
      if ($ci === null) throw new DbException("列がありません：{$n}", 1, 'HY000');
      $sets[] = [$ci, $this->compile($e, $sc)];
    }
    $checks = $this->checks($t);
    $ret = $this->returningPlan($st['returning'], $t, $st['alias']);
    $or = $st['or'] ?? 'ABORT';
    foreach ($rows as $id => $r) {
      // 他の行のREPLACEで消えた
      if ($this->bt->get($t['root'], $id) === null) continue;
      $new = array_slice($r, 0, $nc);
      $newRowid = null;
      foreach ($sets as [$ci, $f]) {
        if ($ci === 'rowid') $newRowid = $f($r);
        else $new[$ci] = $f($r);
      }
      $old = array_slice($r, 0, $nc + 1);
      $res = $this->updateRow($t, $id, $old, $new, $or, $checks, $newRowid);
      if ($res !== null && $ret) $ret->rows[] = array_map(fn($f) => $f($res), $ret->fns);
    }
    if ($ret) return self::result($ret->cols, $ret->types, $ret->rows, $this->changes);
    return self::result([], [], [], $this->changes);
  }

  private function delete(array $st): array {
    $t = $this->writableTable($st['table']);
    $nc = count($t['cols']);
    $schema = $this->schema();
    if ($st['where'] === null && $st['returning'] === null) {
      // 全部：ツリーを空にする
      $n = $this->bt->count($t['root']);
      $this->bt->clear($t['root']);
      foreach ($t['indexes'] as $iname) $this->bt->clear($schema->indexes[$iname]['root']);
      $this->changes = $n;
      $this->totalChanges += $n;
      return self::result([], [], [], $n);
    }
    $rows = $this->targetRows($t, $st['alias'], [], $st['where']);
    $ret = $this->returningPlan($st['returning'], $t, $st['alias']);
    foreach ($rows as $id => $r) {
      $vals = array_slice($r, 0, $nc + 1);
      $this->deleteRow($t, $id, $vals);
      if ($ret) $ret->rows[] = array_map(fn($f) => $f($vals), $ret->fns);
      $this->changes++;
      $this->totalChanges++;
    }
    if ($ret) return self::result($ret->cols, $ret->types, $ret->rows, $this->changes);
    return self::result([], [], [], $this->changes);
  }

  ///////////////
  // スキーマの変更

  private function schemaInsert(string $type, string $name, string $tbl, int $root, ?string $sql): void {
    $id = $this->bt->maxRowid(1) + 1;
    $this->bt->insert(1, $id, Record::encode([$type, $name, $tbl, $root, $sql]));
    $this->schemaChanged();
  }

  private function schemaChanged(): void {
    $this->pager->hdr['cookie'] = ($this->pager->hdr['cookie'] + 1) & 0x7FFFFFFF;
    $this->schema = null;
  }

  private function createTable(array $st): array {
    $schema = $this->schema();
    if ($schema->exists($st['name'])) {
      if ($st['ifNotExists'] && $schema->table_($st['name']) !== null) return self::result();
      throw new DbException("テーブル{$st['name']}は既に有ります", 1, 'HY000');
    }
    if (stripos($st['name'], 'sqlite_') === 0 && strcasecmp($st['name'], 'sqlite_sequence') !== 0) {
      throw new DbException("「sqlite_」で始まる名前は使えません：{$st['name']}", 1, 'HY000');
    }
    if (isset($st['as'])) {
      $plan = $this->planSelect($st['as'], null);
      $defs = [];
      foreach ($plan->cols as $i => $n) {
        $ty = match ($plan->affs[$i]) { Value::INTEGER => 'INT', Value::REAL => 'REAL', Value::TEXT => 'TEXT', Value::NUMERIC => 'NUM', default => '' };
        $defs[] = self::quoteName($n).($ty !== '' ? ' '.$ty : '');
      }
      $sql = 'CREATE TABLE '.self::quoteName($st['name']).'('.implode(',', $defs).')';
      $this->createTable($this->parser->parseAll($sql)[0]);
      $rows = ($plan->run)(-1);
      $t = $this->schema()->table_($st['name']);
      foreach ($rows as $vals) $this->insertRow($t, $vals, null, 'ABORT', []);
      return self::result();
    }
    $def = $st['def'];
    $sql = 'CREATE TABLE '.substr($st['sql'], $st['nameStart'] - $st['start']);
    if ($st['ifNotExists'] === false && !isset($st['nameStart'])) $sql = $st['sql'];
    $t = Schema::table($st['name'], 0, $sql, $def);
    $root = $this->bt->create(true);
    $this->schemaInsert('table', $st['name'], $st['name'], $root, $sql);
    foreach ($t['constraints'] as $k => [$kind, $list]) {
      $iroot = $this->bt->create(false);
      $this->schemaInsert('index', 'sqlite_autoindex_'.$st['name'].'_'.($k + 1), $st['name'], $iroot, null);
    }
    if ($t['autoinc'] && $this->schema()->table_('sqlite_sequence') === null) {
      $sroot = $this->bt->create(true);
      $this->schemaInsert('table', 'sqlite_sequence', 'sqlite_sequence', $sroot, 'CREATE TABLE sqlite_sequence(name,seq)');
    }
    return self::result();
  }

  private function createIndex(array $st): array {
    $schema = $this->schema();
    if ($schema->exists($st['name'])) {
      if ($st['ifNotExists'] && isset($schema->indexes[strtolower($st['name'])])) return self::result();
      throw new DbException("インデックス{$st['name']}は既に有ります", 1, 'HY000');
    }
    $t = $schema->table_($st['table']);
    if ($t === null) throw new DbException("テーブルがありません：{$st['table']}", 1, 'HY000');
    if (!empty($t['system']) || isset($t['unsupported'])) throw new DbException("テーブル{$t['name']}にインデックスは作れません", 1, 'HY000');
    $idx = Schema::indexInfo(['name' => $st['name'], 'root' => 0, 'table' => strtolower($t['name']), 'unique' => $st['unique'], 'def' => $st['cols'], 'origin' => 'c'], $t);
    $sql = 'CREATE '.($st['unique'] ? 'UNIQUE ' : '').'INDEX '.substr($st['sql'], $st['nameStart'] - $st['start']);
    $idx['root'] = $this->bt->create(false);
    $read = $this->rowReader($t);
    foreach ($this->bt->scan($t['root']) as $id => $rec) {
      $vals = $read($id, $rec);
      if ($st['unique'] && $this->uniqueConflict($idx, $vals, $id) !== null) {
        $names = implode(', ', array_map(fn($ci) => $t['name'].'.'.$t['cols'][$ci]['name'], $idx['cols']));
        throw new DbException("UNIQUE制約に違反しました：{$names}", 19, '23000');
      }
      $this->bt->indexInsert($idx, $this->indexKey($idx, $vals, $id));
    }
    $this->schemaInsert('index', $st['name'], $t['name'], $idx['root'], $sql);
    return self::result();
  }

  private function createView(array $st): array {
    $schema = $this->schema();
    if ($schema->exists($st['name'])) {
      if ($st['ifNotExists'] && isset($schema->views[strtolower($st['name'])])) return self::result();
      throw new DbException("ビュー{$st['name']}は既に有ります", 1, 'HY000');
    }
    // 確認
    $plan = $this->planSelect($st['sel'], null);
    if ($st['cols'] !== null && count($st['cols']) !== count($plan->cols)) throw new DbException('ビューの列の数が違います', 1, 'HY000');
    $sql = 'CREATE VIEW '.substr($st['sql'], $st['nameStart'] - $st['start']);
    $this->schemaInsert('view', $st['name'], $st['name'], 0, $sql);
    return self::result();
  }

  private function drop(array $st): array {
    $schema = $this->schema();
    $l = strtolower($st['name']);
    switch ($st['kind']) {
      case 'table':
        $t = $schema->tables[$l] ?? null;
        if ($t === null) {
          if ($st['ifExists']) return self::result();
          throw new DbException("テーブルがありません：{$st['name']}", 1, 'HY000');
        }
        if (!empty($t['system']) || $l === 'sqlite_sequence') throw new DbException("テーブル{$t['name']}は消せません", 1, 'HY000');
        foreach ($t['indexes'] ?? [] as $iname) {
          $idx = $schema->indexes[$iname];
          $this->bt->clear($idx['root'], true);
          $this->bt->delete(1, $idx['schemaRowid']);
        }
        // 自動のインデックスの行（テーブルが読めない場合も）
        foreach ($this->bt->scan(1) as $id => $rec) {
          $v = Record::decode($rec);
          if (($v[0] ?? '') === 'trigger' && strcasecmp((string)$v[2], $t['name']) === 0) $this->bt->delete(1, $id);
        }
        $this->bt->clear($t['root'], true);
        $this->bt->delete(1, $t['schemaRowid']);
        if (!empty($t['autoinc']) && ($seq = $schema->table_('sqlite_sequence')) !== null) {
          foreach ($this->bt->scan($seq['root']) as $id => $rec) {
            if (strcasecmp((string)(Record::decode($rec)[0] ?? ''), $t['name']) === 0) {
              $this->bt->delete($seq['root'], $id);
              break;
            }
          }
        }
        $this->schemaChanged();
        return self::result();
      case 'index':
        $idx = $schema->indexes[$l] ?? null;
        if ($idx === null) {
          if ($st['ifExists']) return self::result();
          throw new DbException("インデックスがありません：{$st['name']}", 1, 'HY000');
        }
        if ($idx['sql'] === null) throw new DbException('UNIQUE・PRIMARY KEYの制約のインデックスは消せません', 1, 'HY000');
        $this->bt->clear($idx['root'], true);
        $this->bt->delete(1, $idx['schemaRowid']);
        $this->schemaChanged();
        return self::result();
      case 'view':
        $v = $schema->views[$l] ?? null;
        if ($v === null) {
          if ($st['ifExists']) return self::result();
          throw new DbException("ビューがありません：{$st['name']}", 1, 'HY000');
        }
        $this->bt->delete(1, $v['schemaRowid']);
        $this->schemaChanged();
        return self::result();
      default:
        $id = $schema->triggers['@'.$l] ?? null;
        if ($id === null) {
          if ($st['ifExists']) return self::result();
          throw new DbException("トリガーがありません：{$st['name']}", 1, 'HY000');
        }
        $this->bt->delete(1, $id);
        $this->schemaChanged();
        return self::result();
    }
  }

  public static function quoteName(string $n): string {
    return preg_match('/^[A-Za-z_][A-Za-z0-9_]*$/', $n) && !in_array(strtoupper($n), ['SELECT', 'FROM', 'WHERE', 'TABLE', 'ORDER', 'GROUP', 'INDEX', 'KEY', 'DEFAULT'], true)
      ? $n : '"'.str_replace('"', '""', $n).'"';
  }

  /** スキーマの行を変える */
  private function schemaUpdate(int $rowid, array $vals): void {
    $this->bt->insert(1, $rowid, Record::encode($vals));
  }

  private function alter(array $st): array {
    $schema = $this->schema();
    $t = $this->writableTable($st['table']);
    $toks = $this->parser->tokens($t['sql']);
    switch ($st['op']) {
      case 'rename':
        if ($schema->exists($st['to'])) throw new DbException("{$st['to']}は既に有ります", 1, 'HY000');
        $quoted = '"'.str_replace('"', '""', $st['to']).'"';
        $sql = $this->replaceToken($t['sql'], $toks, $this->nameTokenIndex($toks), $quoted);
        $this->schemaUpdate($t['schemaRowid'], ['table', $st['to'], $st['to'], $t['root'], $sql]);
        foreach ($t['indexes'] as $iname) {
          $idx = $schema->indexes[$iname];
          $iname2 = $idx['name'];
          $isql = $idx['sql'];
          if ($isql === null) {
            $iname2 = 'sqlite_autoindex_'.$st['to'].substr($idx['name'], strlen('sqlite_autoindex_'.$t['name']));
          } else {
            $itoks = $this->parser->tokens($isql);
            foreach ($itoks as $k => $tk) {
              if ($tk['t'] === 'id' && !$tk['q'] && $tk['u'] === 'ON') {
                $isql = $this->replaceToken($isql, $itoks, $k + 1, $quoted);
                break;
              }
            }
          }
          $this->schemaUpdate($idx['schemaRowid'], ['index', $iname2, $st['to'], $idx['root'], $isql]);
        }
        if ($t['autoinc'] && ($seq = $schema->table_('sqlite_sequence')) !== null) {
          foreach ($this->bt->scan($seq['root']) as $id => $rec) {
            $v = Record::decode($rec);
            if (strcasecmp((string)$v[0], $t['name']) === 0) $this->bt->insert($seq['root'], $id, Record::encode([$st['to'], $v[1]]));
          }
        }
        $this->schemaChanged();
        return self::result();

      case 'renameColumn':
        $ci = $t['map'][strtolower($st['from'])] ?? null;
        if ($ci === null) throw new DbException("列がありません：{$st['from']}", 1, 'HY000');
        if (isset($t['map'][strtolower($st['to'])])) throw new DbException("列{$st['to']}は既に有ります", 1, 'HY000');
        $nameIdx = $this->nameTokenIndex($toks);
        $sql = $this->replaceIdentifiers($t['sql'], $toks, $st['from'], $st['to'], $nameIdx + 1);
        $this->schemaUpdate($t['schemaRowid'], ['table', $t['name'], $t['name'], $t['root'], $sql]);
        foreach ($t['indexes'] as $iname) {
          $idx = $schema->indexes[$iname];
          if ($idx['sql'] === null) continue;
          $itoks = $this->parser->tokens($idx['sql']);
          $start = 0;
          foreach ($itoks as $k => $tk) if ($tk['t'] === 'op' && $tk['v'] === '(') {
            $start = $k;
            break;
          }
          $isql = $this->replaceIdentifiers($idx['sql'], $itoks, $st['from'], $st['to'], $start);
          $this->schemaUpdate($idx['schemaRowid'], ['index', $idx['name'], $t['name'], $idx['root'], $isql]);
        }
        $this->schemaChanged();
        return self::result();

      case 'addColumn':
        $c = $st['col'];
        if (isset($t['map'][strtolower($c['name'])])) throw new DbException("列{$c['name']}は既に有ります", 1, 'HY000');
        if ($c['pk']) throw new DbException('PRIMARY KEYの列は追加できません', 1, 'HY000');
        if ($c['unique']) throw new DbException('UNIQUEの列は追加できません', 1, 'HY000');
        if ($c['default'] !== null && !in_array($c['default']['t'], ['lit', 'un'], true)) throw new DbException('定数でないDEFAULTの列は追加できません', 1, 'HY000');
        if ($c['notNull'] && ($c['default'] === null || $this->constValue($c['default']) === null) && $this->bt->count($t['root']) > 0) {
          throw new DbException('NOT NULLの列にはNULLでないDEFAULTが必要です', 1, 'HY000');
        }
        // 最後の列の定義の後（テーブルの制約の前）に入れる
        [$open, $close] = $this->bodyParens($toks);
        $depth = 0;
        $end = $open;
        $inCol = true;
        for ($k = $open + 1; $k < $close; $k++) {
          $tk = $toks[$k];
          if ($tk['t'] === 'op' && $tk['v'] === '(') $depth++;
          else if ($tk['t'] === 'op' && $tk['v'] === ')') $depth--;
          if ($depth === 0 && $tk['t'] === 'op' && $tk['v'] === ',') {
            $nx = $toks[$k + 1];
            $inCol = !($nx['t'] === 'id' && !$nx['q'] && in_array($nx['u'], ['CONSTRAINT', 'PRIMARY', 'UNIQUE', 'CHECK', 'FOREIGN'], true));
            continue;
          }
          if ($inCol) $end = $k;
        }
        $at = $toks[$end]['e'];
        $sql = substr($t['sql'], 0, $at).', '.$st['text'].substr($t['sql'], $at);
        $this->schemaUpdate($t['schemaRowid'], ['table', $t['name'], $t['name'], $t['root'], $sql]);
        $this->schemaChanged();
        return self::result();

      case 'dropColumn':
        $ci = $t['map'][strtolower($st['col'])] ?? null;
        if ($ci === null) throw new DbException("列がありません：{$st['col']}", 1, 'HY000');
        if (count($t['cols']) === 1) throw new DbException('最後の列は消せません', 1, 'HY000');
        if ($t['cols'][$ci]['pk']) throw new DbException('PRIMARY KEYの列は消せません', 1, 'HY000');
        foreach ($t['indexes'] as $iname) {
          if (in_array($ci, $schema->indexes[$iname]['cols'], true)) throw new DbException("インデックスの有る列は消せません：{$st['col']}", 1, 'HY000');
        }
        // 列の定義の場所
        [$open, $close] = $this->bodyParens($toks);
        $depth = 0;
        $starts = [$open + 1];
        for ($k = $open + 1; $k < $close; $k++) {
          $tk = $toks[$k];
          if ($tk['t'] === 'op' && $tk['v'] === '(') $depth++;
          else if ($tk['t'] === 'op' && $tk['v'] === ')') $depth--;
          else if ($depth === 0 && $tk['t'] === 'op' && $tk['v'] === ',') $starts[] = $k + 1;
        }
        $starts[] = $close + 1;
        $seg = null;
        foreach ($starts as $s => $k) {
          if (!isset($starts[$s + 1])) break;
          $tk = $toks[$k];
          if ($tk['t'] === 'id' && strcasecmp($tk['v'], $t['cols'][$ci]['name']) === 0) $seg = $s;
        }
        if ($seg === null) throw new DbException('列の定義が見つかりません', 1, 'HY000');
        $from = $seg === 0 ? $toks[$starts[0]]['s'] : $toks[$starts[$seg] - 1]['s'];
        $to = $seg === 0 ? $toks[$starts[1]]['s'] : $toks[$starts[$seg + 1] - 1]['s'];
        $sql = substr($t['sql'], 0, $from).substr($t['sql'], $to);
        // 行を書き直す
        $updates = [];
        foreach ($this->bt->scan($t['root']) as $id => $rec) {
          $v = Record::decode($rec);
          if (count($v) > $ci) {
            array_splice($v, $ci, 1);
            $updates[$id] = Record::encode($v);
          }
        }
        foreach ($updates as $id => $rec) $this->bt->insert($t['root'], $id, $rec);
        $this->schemaUpdate($t['schemaRowid'], ['table', $t['name'], $t['name'], $t['root'], $sql]);
        $this->schemaChanged();
        // 自動のインデックスの番号は変わらない（UNIQUEの列は消せないので）
        return self::result();
    }
    throw new DbException('未対応のALTER TABLE', 1, 'HY000');
  }

  /** CREATE TABLE/INDEX [IF NOT EXISTS] 名前 の名前のトークン */
  private function nameTokenIndex(array $toks): int {
    $k = 0;
    while (isset($toks[$k]) && $toks[$k]['t'] === 'id' && !$toks[$k]['q'] && in_array($toks[$k]['u'], ['CREATE', 'TABLE', 'UNIQUE', 'INDEX', 'VIEW', 'IF', 'NOT', 'EXISTS', 'TEMP', 'TEMPORARY'], true)) $k++;
    if (($toks[$k + 1]['t'] ?? '') === 'op' && $toks[$k + 1]['v'] === '.') $k += 2;
    return $k;
  }

  private function replaceToken(string $sql, array $toks, int $k, string $new): string {
    return substr($sql, 0, $toks[$k]['s']).$new.substr($sql, $toks[$k]['e']);
  }

  private function replaceIdentifiers(string $sql, array $toks, string $from, string $to, int $start): string {
    $out = '';
    $pos = 0;
    foreach ($toks as $k => $tk) {
      if ($k < $start || $tk['t'] !== 'id' || strcasecmp($tk['v'], $from) !== 0) continue;
      $out .= substr($sql, $pos, $tk['s'] - $pos).self::quoteName($to);
      $pos = $tk['e'];
    }
    return $out.substr($sql, $pos);
  }

  /** テーブルの定義の括弧（開く、閉じる）のトークン */
  private function bodyParens(array $toks): array {
    $open = null;
    $depth = 0;
    foreach ($toks as $k => $tk) {
      if ($tk['t'] !== 'op') continue;
      if ($tk['v'] === '(') {
        if ($open === null) $open = $k;
        $depth++;
      } else if ($tk['v'] === ')') {
        $depth--;
        if ($depth === 0 && $open !== null) return [$open, $k];
      }
    }
    throw new DbException('テーブルのSQLが不正です', 11, 'HY000');
  }

  ///////////////
  // PRAGMA

  private function pragma(array $st): array {
    $schema = $this->schema();
    $arg = $st['arg'];
    $one = fn(string $name, mixed $v) => self::result([$name], [''], [[$v]]);
    switch ($st['name']) {
      case 'table_info':
      case 'table_xinfo':
        $t = $schema->table_((string)$arg);
        if ($t === null) {
          $v = $schema->views[strtolower((string)$arg)] ?? null;
          if ($v === null) return self::result(['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk']);
          $plan = $this->planSelect($v['sel'], null);
          $rows = [];
          foreach ($v['cols'] ?? $plan->cols as $i => $n) $rows[] = [$i, $n, $plan->types[$i] ?? '', 0, null, 0];
          return self::result(['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk'], [], $rows);
        }
        $rows = [];
        foreach ($t['cols'] as $i => $c) $rows[] = [$i, $c['name'], $c['type'], (int)$c['notNull'], $c['defaultText'], $c['pk']];
        return self::result(['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk'], [], $rows);
      case 'index_list':
        $t = $schema->table_((string)$arg);
        $rows = [];
        foreach (array_reverse($t['indexes'] ?? []) as $k => $iname) {
          $idx = $schema->indexes[$iname];
          $rows[] = [$k, $idx['name'], (int)$idx['unique'], $idx['origin'], 0];
        }
        return self::result(['seq', 'name', 'unique', 'origin', 'partial'], [], $rows);
      case 'index_info':
      case 'index_xinfo':
        $idx = $schema->indexes[strtolower((string)$arg)] ?? null;
        $rows = [];
        if ($idx) {
          $t = $schema->tables[$idx['table']];
          foreach ($idx['cols'] as $k => $ci) {
            $rows[] = $st['name'] === 'index_info' ? [$k, $ci, $t['cols'][$ci]['name']]
              : [$k, $ci, $t['cols'][$ci]['name'], (int)$idx['desc'][$k], $idx['colls'][$k], 1];
          }
          if ($st['name'] === 'index_xinfo') $rows[] = [count($idx['cols']), -1, null, 0, 'BINARY', 0];
        }
        return self::result($st['name'] === 'index_info' ? ['seqno', 'cid', 'name'] : ['seqno', 'cid', 'name', 'desc', 'coll', 'key'], [], $rows);
      case 'table_list':
        $rows = [];
        foreach ($schema->tables as $t) $rows[] = ['main', $t['name'] === 'sqlite_schema' ? 'sqlite_schema' : $t['name'], 'table', count($t['cols']), 0, 0];
        foreach ($schema->views as $v) $rows[] = ['main', $v['name'], 'view', count($v['cols'] ?? []), 0, 0];
        return self::result(['schema', 'name', 'type', 'ncol', 'wr', 'strict'], [], $rows);
      case 'foreign_key_list':
        return self::result(['id', 'seq', 'table', 'from', 'to', 'on_update', 'on_delete', 'match']);
      case 'foreign_keys':
      case 'recursive_triggers':
      case 'case_sensitive_like':
        return $arg === null ? $one($st['name'], 0) : self::result();
      case 'user_version':
      case 'application_id':
        $k = $st['name'] === 'user_version' ? 'userVersion' : 'appId';
        if ($arg === null) return $one($st['name'], $this->pager->hdr[$k] >= 0x80000000 ? $this->pager->hdr[$k] - 0x100000000 : $this->pager->hdr[$k]);
        $this->pager->hdr[$k] = ((int)Value::toInt($arg)) & 0xFFFFFFFF;
        $this->pager->write(1, $this->pager->read(1));
        return self::result();
      case 'schema_version':
        return $one('schema_version', $this->pager->hdr['cookie']);
      case 'page_size':
        return $one('page_size', $this->pager->pageSize);
      case 'page_count':
        return $one('page_count', $this->pager->nPages);
      case 'freelist_count':
        return $one('freelist_count', $this->pager->hdr['freeCount']);
      case 'journal_mode':
        return $one('journal_mode', $this->pager->isMemory() ? 'memory' : 'delete');
      case 'encoding':
        return $one('encoding', 'UTF-8');
      case 'database_list':
        return self::result(['seq', 'name', 'file'], [], [[0, 'main', $this->pager->isMemory() ? '' : $this->pager->path]]);
      case 'integrity_check':
      case 'quick_check':
        $msgs = (new Integrity($this->pager, $this->bt, $schema))->check();
        return self::result([$st['name']], [''], array_map(fn($m) => [$m], $msgs ?: ['ok']));
      case 'synchronous':
      case 'cache_size':
      case 'temp_store':
      case 'busy_timeout':
      case 'locking_mode':
      case 'mmap_size':
      case 'auto_vacuum':
      case 'secure_delete':
        return $arg === null ? $one($st['name'], 0) : self::result();
    }
    // 知らないPRAGMAは何もしない（SQLiteと同じ）
    return self::result();
  }
}