<?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\Convert;

use Std\Lib\Db\Convert;
use Std\Lib\Db\DbException;
use Std\Lib\Db\Sqlite\Blob;

/**
 * 変換用のSQLの解析（MySQL・PostgreSQL・SQLiteの方言、ダンプの文の分け方、CREATE TABLE、INSERTの値）
 */
final class Sql {
  // 列の定義で型の後に来る言葉
  private const COLUMN_WORDS = ['NOT', 'NULL', 'DEFAULT', 'PRIMARY', 'UNIQUE', 'AUTO_INCREMENT', 'AUTOINCREMENT', 'COLLATE', 'COMMENT',
    'REFERENCES', 'CHECK', 'CONSTRAINT', 'GENERATED', 'ON', 'CHARSET', 'STORAGE', 'COLUMN_FORMAT', 'INVISIBLE', 'VISIBLE', 'AS',
    'IDENTITY', 'KEY', 'SRID', 'ENCODE', 'COMPRESSION'];

  ///////////////
  // 字句解析

  /**
   * 1つの文をトークンにする
   *
   * @param string $sql
   * @param string $dialect  mysql、pgsql、sqlite
   * @return array  t（id、qid、str、num、blob、op、param）、v（値）、u（大文字、idのみ）
   */
  public static function tokens(string $sql, string $dialect): array {
    $out = [];
    $len = strlen($sql);
    $i = 0;
    while ($i < $len) {
      $c = $sql[$i];
      if (ctype_space($c)) {
        $i++;
        continue;
      }
      if ($c === '-' && ($sql[$i + 1] ?? '') === '-' || ($c === '#' && $dialect === 'mysql')) {
        $j = strpos($sql, "\n", $i);
        $i = $j === false ? $len : $j + 1;
        continue;
      }
      if ($c === '/' && ($sql[$i + 1] ?? '') === '*') {
        $j = strpos($sql, '*/', $i + 2);
        $i = $j === false ? $len : $j + 2;
        continue;
      }
      // E'…'、X'…'、B'…'、N'…'、_binary'…'
      if (($c === "'" || (ctype_alpha($c) && ($sql[$i + 1] ?? '') === "'"))) {
        $prefix = $c === "'" ? '' : strtoupper($c);
        if ($prefix !== '') $i++;
        $backslash = $dialect === 'mysql' || $prefix === 'E';
        [$v, $i] = self::readQuoted($sql, $i, "'", $backslash);
        if ($prefix === 'X') {
          $out[] = ['t' => 'blob', 'v' => (string)hex2bin(strlen($v) % 2 ? '0'.$v : $v)];
        } else if ($prefix === 'B') {
          $out[] = ['t' => 'num', 'v' => bindec($v)];
        } else {
          $out[] = ['t' => 'str', 'v' => $v];
        }
        continue;
      }
      if ($c === '"') {
        [$v, $i] = self::readQuoted($sql, $i, '"', $dialect === 'mysql');
        // MySQLの「"」は文字列（ANSI_QUOTESでない場合）
        $out[] = $dialect === 'mysql' ? ['t' => 'str', 'v' => $v] : ['t' => 'qid', 'v' => $v];
        continue;
      }
      if ($c === '`') {
        [$v, $i] = self::readQuoted($sql, $i, '`', false);
        $out[] = ['t' => 'qid', 'v' => $v];
        continue;
      }
      if ($c === '[' && $dialect === 'sqlite') {
        $j = strpos($sql, ']', $i);
        $out[] = ['t' => 'qid', 'v' => substr($sql, $i + 1, ($j === false ? $len : $j) - $i - 1)];
        $i = $j === false ? $len : $j + 1;
        continue;
      }
      if ($c === '$' && $dialect === 'pgsql' && preg_match('/\G\$([A-Za-z_][A-Za-z0-9_]*)?\$/', $sql, $m, 0, $i)) {
        $end = strpos($sql, $m[0], $i + strlen($m[0]));
        $body = $end === false ? substr($sql, $i + strlen($m[0])) : substr($sql, $i + strlen($m[0]), $end - $i - strlen($m[0]));
        $out[] = ['t' => 'str', 'v' => $body];
        $i = $end === false ? $len : $end + strlen($m[0]);
        continue;
      }
      if ($c === '0' && ($sql[$i + 1] ?? '') !== '' && strtolower($sql[$i + 1]) === 'x' && preg_match('/\G0[xX]([0-9a-fA-F]*)/', $sql, $m, 0, $i)) {
        $i += strlen($m[0]);
        $hex = $m[1];
        $out[] = ['t' => 'blob', 'v' => (string)hex2bin(strlen($hex) % 2 ? '0'.$hex : $hex)];
        continue;
      }
      if (ctype_digit($c) || ($c === '.' && ctype_digit($sql[$i + 1] ?? ''))) {
        preg_match('/\G(?:\d+(?:\.\d*)?|\.\d+)(?:[eE][+-]?\d+)?/', $sql, $m, 0, $i);
        $i += strlen($m[0]);
        $t = $m[0];
        $v = ctype_digit($t) ? ((string)(int)ltrim($t, '0') === ltrim($t, '0') || ltrim($t, '0') === '' ? (int)$t : $t) : (float)$t;
        $out[] = ['t' => 'num', 'v' => $v, 'raw' => $t];
        continue;
      }
      if (ctype_alpha($c) || $c === '_' || ord($c) >= 0x80) {
        preg_match('/\G[A-Za-z0-9_$\x80-\xFF]+/', $sql, $m, 0, $i);
        $i += strlen($m[0]);
        // MySQLの文字セットの前置き（_binary 'x'、_utf8mb4'x'）
        if ($m[0][0] === '_' && $dialect === 'mysql' && preg_match('/\G\s*\'/', $sql, $mm, 0, $i)) {
          $i += strlen($mm[0]) - 1;
          [$v, $i] = self::readQuoted($sql, $i, "'", true);
          $out[] = strtolower($m[0]) === '_binary' ? ['t' => 'blob', 'v' => $v] : ['t' => 'str', 'v' => $v];
          continue;
        }
        $out[] = ['t' => 'id', 'v' => $m[0], 'u' => strtoupper($m[0])];
        continue;
      }
      foreach (['::', '<>', '!=', '<=', '>=', '||', '->>', '->'] as $op) {
        if (substr($sql, $i, strlen($op)) === $op) {
          $out[] = ['t' => 'op', 'v' => $op];
          $i += strlen($op);
          continue 2;
        }
      }
      $out[] = ['t' => 'op', 'v' => $c];
      $i++;
    }
    return $out;
  }

  /** 引用符の中（$iは開く引用符の位置） */
  private static function readQuoted(string $s, int $i, string $q, bool $backslash): array {
    $len = strlen($s);
    $v = '';
    $i++;
    while ($i < $len) {
      $c = $s[$i];
      if ($backslash && $c === '\\') {
        $n = $s[$i + 1] ?? '';
        $v .= match ($n) { '0' => "\0", 'n' => "\n", 'r' => "\r", 't' => "\t", 'b' => "\x08", 'Z' => "\x1A", default => $n };
        $i += 2;
        continue;
      }
      if ($c === $q) {
        if (($s[$i + 1] ?? '') === $q) {
          $v .= $q;
          $i += 2;
          continue;
        }
        return [$v, $i + 1];
      }
      $j = strcspn($s, $q.($backslash ? '\\' : ''), $i);
      $v .= substr($s, $i, $j);
      $i += $j;
    }
    return [$v, $len];
  }

  ///////////////
  // 解析

  /** トークンの名前（スキーマ付きは最後の部分） */
  public static function name(array $tok, int &$p): string {
    $t = $tok[$p] ?? null;
    if ($t === null || !in_array($t['t'], ['id', 'qid', 'str'], true)) throw new DbException('SQLの解析：名前が必要です', 1, 'HY000');
    $p++;
    $n = $t['v'];
    while (($tok[$p]['t'] ?? '') === 'op' && $tok[$p]['v'] === '.' && in_array($tok[$p + 1]['t'] ?? '', ['id', 'qid'], true)) {
      $n = $tok[$p + 1]['v'];
      $p += 2;
    }
    return $n;
  }

  private static function is(array $tok, int $p, string ...$words): bool {
    return ($tok[$p]['t'] ?? '') === 'id' && in_array($tok[$p]['u'], $words, true);
  }

  private static function isOp(array $tok, int $p, string $op): bool {
    return ($tok[$p]['t'] ?? '') === 'op' && $tok[$p]['v'] === $op;
  }

  /** 対応する閉じ括弧まで（$pは開き括弧） */
  private static function skipParens(array $tok, int $p): int {
    $depth = 0;
    for ($n = count($tok); $p < $n; $p++) {
      if (self::isOp($tok, $p, '(')) $depth++;
      else if (self::isOp($tok, $p, ')') && --$depth === 0) return $p + 1;
    }
    return $p;
  }

  /** トークンを元のSQLに近い形に戻す（DEFAULTの式・型用） */
  public static function text(array $toks): string {
    $out = '';
    foreach ($toks as $t) {
      $s = match ($t['t']) {
        'str' => "'".str_replace("'", "''", $t['v'])."'",
        'qid' => '"'.$t['v'].'"',
        'blob' => "X'".bin2hex($t['v'])."'",
        'num' => (string)($t['raw'] ?? $t['v']),
        default => $t['v'],
      };
      if ($out !== '' && !in_array($s, ['(', ')', ',', '::', '[', ']'], true) && !str_ends_with($out, '(') && !str_ends_with($out, '::')) $out .= ' ';
      $out .= $s;
    }
    return $out;
  }

  /** (列, 列(長さ) ASC, …) の列の名前 */
  public static function columnList(array $tok, int &$p): array {
    if (!self::isOp($tok, $p, '(')) throw new DbException('SQLの解析：(が必要です', 1, 'HY000');
    $end = self::skipParens($tok, $p);
    // 要素毎のトークン
    $items = [[]];
    $depth = 0;
    for ($p++; $p < $end - 1; $p++) {
      if (self::isOp($tok, $p, '(')) $depth++;
      else if (self::isOp($tok, $p, ')')) $depth--;
      else if ($depth === 0 && self::isOp($tok, $p, ',')) {
        $items[] = [];
        continue;
      }
      $items[count($items) - 1][] = $tok[$p];
    }
    $cols = [];
    foreach ($items as $it) {
      // 列の名前の後は、MySQLの(長さ)、ASC・DESC・COLLATE・演算子クラス等だけなら列、他は式（null）
      $ok = !empty($it) && in_array($it[0]['t'], ['id', 'qid'], true);
      if ($ok && isset($it[1])) {
        if ($it[1]['t'] === 'op' && $it[1]['v'] === '(') $ok = ($it[2]['t'] ?? '') === 'num' && ($it[3]['v'] ?? '') === ')';
        else $ok = in_array($it[1]['t'], ['id', 'qid'], true);
      }
      $cols[] = $ok ? $it[0]['v'] : null;
    }
    $p = $end;
    return $cols;
  }

  /**
   * CREATE TABLEを解析する
   *
   * @return array|null  テーブル（Convertの形）、CREATE TABLEでなければnull
   */
  public static function createTable(string $sql, string $dialect, array &$warnings = []): ?array {
    $tok = self::tokens($sql, $dialect);
    $p = 0;
    if (!self::is($tok, $p++, 'CREATE')) return null;
    while (self::is($tok, $p, 'TEMP', 'TEMPORARY', 'UNLOGGED', 'GLOBAL', 'LOCAL', 'OR', 'REPLACE')) $p++;
    if (!self::is($tok, $p++, 'TABLE')) return null;
    if (self::is($tok, $p, 'IF')) $p += 3;
    $name = self::name($tok, $p);
    if (!self::isOp($tok, $p, '(')) return null;
    $end = self::skipParens($tok, $p);
    $t = ['name' => $name, 'columns' => [], 'pk' => [], 'indexes' => [], 'collate' => null];
    // 項目に分ける
    $items = [];
    $cur = [];
    $depth = 0;
    for ($k = $p + 1; $k < $end - 1; $k++) {
      if (self::isOp($tok, $k, '(')) $depth++;
      if (self::isOp($tok, $k, ')')) $depth--;
      if ($depth === 0 && self::isOp($tok, $k, ',')) {
        $items[] = $cur;
        $cur = [];
        continue;
      }
      $cur[] = $tok[$k];
    }
    if ($cur) $items[] = $cur;
    foreach ($items as $it) self::tableItem($t, $it, $dialect, $warnings);
    // テーブルのオプション（MySQL）
    for ($k = $end; $k < count($tok); $k++) {
      if (self::is($tok, $k, 'COLLATE') && isset($tok[$k + 1])) {
        $j = self::isOp($tok, $k + 1, '=') ? $k + 2 : $k + 1;
        $t['collate'] = $tok[$j]['v'] ?? null;
      }
      if (self::is($tok, $k, 'WITHOUT')) $warnings[] = "WITHOUT ROWIDは普通のテーブルにします：{$name}";
    }
    if ($dialect !== 'mysql') unset($t['collate']);
    return $t;
  }

  /** テーブルの定義の1つの項目（列又は制約） */
  private static function tableItem(array &$t, array $it, string $dialect, array &$warnings): void {
    $p = 0;
    if (self::is($it, 0, 'CONSTRAINT')) $p = 2;
    $cname = self::is($it, 0, 'CONSTRAINT') ? (string)($it[1]['v'] ?? '') : null;
    if (self::is($it, $p, 'PRIMARY') && self::is($it, $p + 1, 'KEY')) {
      $p += 2;
      while (!self::isOp($it, $p, '(') && $p < count($it)) $p++;
      $t['pk'] = array_values(array_filter(self::columnList($it, $p)));
      return;
    }
    if (self::is($it, $p, 'UNIQUE', 'KEY', 'INDEX') && !self::isOp($it, $p + 1, '.')) {
      $unique = self::is($it, $p, 'UNIQUE');
      $p++;
      if (self::is($it, $p, 'KEY', 'INDEX')) $p++;
      $iname = $cname;
      if (!self::isOp($it, $p, '(') && $p < count($it)) $iname = (string)$it[$p++]['v'];
      while (!self::isOp($it, $p, '(') && $p < count($it)) $p++;
      $cols = self::columnList($it, $p);
      if (in_array(null, $cols, true)) {
        $warnings[] = "式のインデックスは変換しません：{$t['name']}";
        return;
      }
      $t['indexes'][] = ['name' => $iname ?? '', 'columns' => $cols, 'unique' => $unique];
      return;
    }
    if (self::is($it, $p, 'FULLTEXT', 'SPATIAL')) {
      $warnings[] = "全文・空間のインデックスは変換しません：{$t['name']}";
      return;
    }
    if (self::is($it, $p, 'FOREIGN', 'CHECK', 'EXCLUDE') || ($cname !== null)) {
      if (self::is($it, $p, 'FOREIGN')) $warnings[] = "外部キーは変換しません：{$t['name']}";
      return;
    }
    if (self::is($it, $p, 'PERIOD', 'LIKE')) return;
    $col = self::columnDef($it, $dialect, $t['name'], $warnings);
    if ($col === null) return;
    if (!empty($col['pk'])) $t['pk'] = [$col['name']];
    if (!empty($col['unique'])) $t['indexes'][] = ['name' => '', 'columns' => [$col['name']], 'unique' => true];
    unset($col['pk'], $col['unique']);
    $t['columns'][] = $col;
  }

  /**
   * 列の定義
   *
   * @return array|null  Convertの列（pk・uniqueも）
   */
  public static function columnDef(array $it, string $dialect, string $table, array &$warnings): ?array {
    $p = 0;
    if (!in_array($it[0]['t'] ?? '', ['id', 'qid', 'str'], true)) return null;
    $name = $it[$p++]['v'];
    $typeToks = [];
    $n = count($it);
    while ($p < $n) {
      $tk = $it[$p];
      if ($tk['t'] === 'id' && in_array($tk['u'], self::COLUMN_WORDS, true)) break;
      if ($tk['t'] === 'id' && $tk['u'] === 'CHARACTER' && self::is($it, $p + 1, 'SET')) break;
      if (self::isOp($it, $p, '(')) {
        $e = self::skipParens($it, $p);
        $typeToks = array_merge($typeToks, array_slice($it, $p, $e - $p));
        $p = $e;
        continue;
      }
      $typeToks[] = $tk;
      $p++;
    }
    $typeText = self::text($typeToks);
    $col = Convert::parseType($typeText, $dialect) + ['name' => $name, 'notNull' => false, 'default' => null, 'autoinc' => false, 'collate' => null];
    if (in_array(strtolower(preg_replace('/\(.*$/', '', $typeText)), ['serial', 'bigserial', 'smallserial', 'serial4', 'serial8', 'serial2'], true)) {
      $col['autoinc'] = true;
      $col['notNull'] = true;
    }
    while ($p < $n) {
      if (self::is($it, $p, 'NOT') && self::is($it, $p + 1, 'NULL')) {
        $col['notNull'] = true;
        $p += 2;
      } else if (self::is($it, $p, 'NULL')) {
        $p++;
      } else if (self::is($it, $p, 'PRIMARY')) {
        $col['pk'] = true;
        $p += 2;
        if (self::is($it, $p, 'ASC', 'DESC')) $p++;
      } else if (self::is($it, $p, 'UNIQUE')) {
        $col['unique'] = true;
        $p++;
        if (self::is($it, $p, 'KEY')) $p++;
      } else if (self::is($it, $p, 'AUTO_INCREMENT', 'AUTOINCREMENT')) {
        $col['autoinc'] = true;
        $p++;
      } else if (self::is($it, $p, 'DEFAULT')) {
        $p++;
        $s = $p;
        if (self::isOp($it, $p, '(')) {
          $p = self::skipParens($it, $p);
        } else {
          while ($p < $n && !($it[$p]['t'] === 'id' && in_array($it[$p]['u'], ['NOT', 'NULL', 'PRIMARY', 'UNIQUE', 'AUTO_INCREMENT', 'COLLATE', 'COMMENT', 'REFERENCES', 'CHECK', 'CONSTRAINT', 'ON', 'GENERATED', 'CHARACTER'], true) && $p > $s)) {
            if (self::isOp($it, $p, '(')) {
              $p = self::skipParens($it, $p);
              continue;
            }
            $p++;
          }
        }
        $expr = self::text(array_slice($it, $s, $p - $s));
        $d = Convert::parseDefault($expr);
        if ($d !== null && isset($d['seq'])) {
          $col['autoinc'] = true;
        } else if ($d === null) {
          $warnings[] = "DEFAULTを変換できません：{$table}.{$name}（{$expr}）";
        } else {
          $col['default'] = $d;
        }
      } else if (self::is($it, $p, 'GENERATED')) {
        // GENERATED … AS IDENTITY（自動の番号）、GENERATED ALWAYS AS (式)（生成列）
        $k = $p;
        while ($k < $n && !self::is($it, $k, 'IDENTITY') && !self::isOp($it, $k, '(')) $k++;
        if (self::is($it, $k, 'IDENTITY')) {
          $col['autoinc'] = true;
          $col['notNull'] = true;
          $p = $k + 1;
          if (self::isOp($it, $p, '(')) $p = self::skipParens($it, $p);
        } else {
          $warnings[] = "生成列は普通の列にします：{$table}.{$name}";
          $p = $k < $n ? self::skipParens($it, $k) : $n;
        }
      } else if (self::is($it, $p, 'AS') && self::isOp($it, $p + 1, '(')) {
        $warnings[] = "生成列は普通の列にします：{$table}.{$name}";
        $p = self::skipParens($it, $p + 1);
      } else if (self::is($it, $p, 'COLLATE')) {
        $col['collate'] = $dialect === 'mysql' ? (string)($it[$p + 1]['v'] ?? '') : null;
        $p += 2;
      } else if (self::is($it, $p, 'CHARACTER') && self::is($it, $p + 1, 'SET')) {
        $p += 3;
      } else if (self::is($it, $p, 'CHARSET', 'COMMENT', 'STORAGE', 'COLUMN_FORMAT', 'SRID', 'ENCODE', 'COMPRESSION')) {
        $p += 2;
      } else if (self::is($it, $p, 'ON') && self::is($it, $p + 1, 'UPDATE')) {
        $p += 3;
        if (self::isOp($it, $p, '(')) $p = self::skipParens($it, $p);
      } else if (self::is($it, $p, 'REFERENCES')) {
        $p += 2;
        if (self::isOp($it, $p, '(')) $p = self::skipParens($it, $p);
        while ($p < $n && self::is($it, $p, 'ON', 'MATCH', 'DEFERRABLE', 'INITIALLY', 'NOT', 'CASCADE', 'RESTRICT', 'SET', 'NO', 'ACTION', 'DELETE', 'UPDATE', 'NULL', 'FULL', 'SIMPLE', 'PARTIAL', 'DEFERRED', 'IMMEDIATE')) $p++;
      } else if (self::is($it, $p, 'CHECK')) {
        $p = self::skipParens($it, $p + 1);
      } else if (self::is($it, $p, 'CONSTRAINT')) {
        $p += 2;
      } else {
        $p++;
      }
    }
    return $col;
  }

  ///////////////
  // 値

  /**
   * INSERT INTO t [(列)] VALUES (…), (…)
   *
   * @return array|null  table、columns（null＝全部）、rows（PHPの値の配列の配列）
   */
  public static function insert(string $sql, string $dialect): ?array {
    $tok = self::tokens($sql, $dialect);
    $p = 0;
    if (self::is($tok, 0, 'REPLACE')) $p = 1;
    else if (self::is($tok, 0, 'INSERT')) {
      $p = 1;
      while (self::is($tok, $p, 'IGNORE', 'OR', 'REPLACE', 'LOW_PRIORITY', 'DELAYED', 'HIGH_PRIORITY', 'ABORT', 'FAIL', 'ROLLBACK')) $p++;
    } else {
      return null;
    }
    if (!self::is($tok, $p++, 'INTO')) return null;
    $table = self::name($tok, $p);
    $cols = null;
    if (self::isOp($tok, $p, '(')) $cols = self::columnList($tok, $p);
    if (!self::is($tok, $p, 'VALUES', 'VALUE')) return ['table' => $table, 'columns' => $cols, 'rows' => [], 'select' => true];
    $p++;
    $rows = [];
    $n = count($tok);
    while ($p < $n && self::isOp($tok, $p, '(')) {
      $end = self::skipParens($tok, $p);
      $row = [];
      $val = [];
      $depth = 0;
      for ($k = $p + 1; $k < $end - 1; $k++) {
        if (self::isOp($tok, $k, '(')) $depth++;
        if (self::isOp($tok, $k, ')')) $depth--;
        if ($depth === 0 && self::isOp($tok, $k, ',')) {
          $row[] = self::value($val);
          $val = [];
          continue;
        }
        $val[] = $tok[$k];
      }
      if ($val || $end - $p > 2) $row[] = self::value($val);
      $rows[] = $row;
      $p = $end;
      if (self::isOp($tok, $p, ',')) $p++;
    }
    return ['table' => $table, 'columns' => $cols, 'rows' => $rows];
  }

  /** 値のトークン（リテラル、負の数、キャスト、一部の関数） */
  public static function value(array $v): mixed {
    // キャスト（'x'::bytea、CAST(… AS …)）を除く
    $cast = null;
    $k = count($v);
    while ($k >= 3 && $v[$k - 2]['t'] === 'op' && $v[$k - 2]['v'] === '::') {
      $cast = strtolower((string)$v[$k - 1]['v']);
      $v = array_slice($v, 0, $k - 2);
      $k = count($v);
    }
    if ($k >= 3 && $v[$k - 1]['t'] === 'op' && $v[$k - 1]['v'] === ']' && $v[$k - 2]['t'] === 'op' && $v[$k - 2]['v'] === '[') {
      $v = array_slice($v, 0, $k - 2);
      $k -= 2;
    }
    if ($k === 0) return null;
    if ($k === 1) {
      $t = $v[0];
      switch ($t['t']) {
        case 'num':
          return $t['v'];
        case 'blob':
          return new Blob($t['v']);
        case 'str':
          if ($cast === 'bytea' && str_starts_with($t['v'], '\\x')) return new Blob((string)hex2bin(substr($t['v'], 2)));
          return $t['v'];
        case 'id':
          return match ($t['u']) { 'NULL' => null, 'TRUE' => true, 'FALSE' => false, 'DEFAULT' => null, default => $t['v'] };
      }
    }
    if ($k === 2 && $v[0]['t'] === 'op' && ($v[0]['v'] === '-' || $v[0]['v'] === '+') && $v[1]['t'] === 'num') {
      if ($v[0]['v'] === '+') return $v[1]['v'];
      $n = $v[1]['v'];
      if (is_string($n)) return $n === '9223372036854775808' ? PHP_INT_MIN : '-'.$n;
      return -$n;
    }
    // CAST(x AS type)
    if ($v[0]['t'] === 'id' && $v[0]['u'] === 'CAST' && ($v[1]['v'] ?? '') === '(') {
      $inner = [];
      for ($i = 2; $i < $k && !($v[$i]['t'] === 'id' && $v[$i]['u'] === 'AS'); $i++) $inner[] = $v[$i];
      return self::value($inner);
    }
    // sqlite3の.dumpの文字列（replace('…','\n',char(10))、unistr('…')、||）
    $parts = self::split($v, '||');
    if (count($parts) > 1) {
      $out = '';
      foreach ($parts as $part) {
        $x = self::value($part);
        if (!is_string($x) && !is_int($x)) return self::text($v);
        $out .= $x;
      }
      return $out;
    }
    if ($v[0]['t'] === 'id' && ($v[1]['v'] ?? '') === '(' && $v[$k - 1]['v'] === ')' && in_array($v[0]['u'], ['REPLACE', 'CHAR', 'UNISTR'], true)) {
      $args = array_map([self::class, 'value'], self::split(array_slice($v, 2, $k - 3), ','));
      foreach ($args as $a) if (!is_string($a) && !is_int($a)) return self::text($v);
      if ($v[0]['u'] === 'REPLACE' && count($args) === 3) return str_replace((string)$args[1], (string)$args[2], (string)$args[0]);
      if ($v[0]['u'] === 'CHAR') return implode('', array_map(fn($c) => (string)mb_chr((int)$c, 'UTF-8'), $args));
      if ($v[0]['u'] === 'UNISTR' && count($args) === 1) {
        return (string)preg_replace_callback('/\\\\\\\\|\\\\(?:u?([0-9A-Fa-f]{4})|\+([0-9A-Fa-f]{6})|U([0-9A-Fa-f]{8}))/',
          fn($m) => $m[0] === '\\\\' ? '\\' : (string)mb_chr((int)hexdec($m[1] ?: ($m[2] ?? '') ?: ($m[3] ?? '')), 'UTF-8'), (string)$args[0]);
      }
    }
    // 他の関数（now()等）は文字列のまま
    return self::text($v);
  }

  /** トークンを括弧の外の演算子で分ける */
  private static function split(array $v, string $op): array {
    $out = [[]];
    $depth = 0;
    foreach ($v as $t) {
      if ($t['t'] === 'op' && $t['v'] === '(') $depth++;
      else if ($t['t'] === 'op' && $t['v'] === ')') $depth--;
      else if ($depth === 0 && $t['t'] === 'op' && $t['v'] === $op) {
        $out[] = [];
        continue;
      }
      $out[count($out) - 1][] = $t;
    }
    return $out;
  }

  /**
   * COPYのデータの1行（タブ区切り、\N＝NULL、\のエスケープ）
   *
   * @return array  文字列又はnull
   */
  public static function copyLine(string $line): array {
    $out = [];
    foreach (explode("\t", $line) as $f) {
      if ($f === '\\N') {
        $out[] = null;
        continue;
      }
      if (!str_contains($f, '\\')) {
        $out[] = $f;
        continue;
      }
      $out[] = preg_replace_callback('/\\\\(x[0-9a-fA-F]{1,2}|[0-7]{1,3}|.)/', function(array $m): string {
        $c = $m[1];
        if ($c[0] === 'x' && strlen($c) > 1) return chr(hexdec(substr($c, 1)));
        if (ctype_digit($c)) return chr(octdec($c) & 0xFF);
        return match ($c) { 'b' => "\x08", 'f' => "\f", 'n' => "\n", 'r' => "\r", 't' => "\t", 'v' => "\v", default => $c };
      }, $f);
    }
    return $out;
  }

  /** COPYのデータの1行を作る */
  public static function copyField(mixed $v): string {
    if ($v === null) return '\\N';
    return strtr((string)$v, ["\\" => "\\\\", "\t" => "\\t", "\n" => "\\n", "\r" => "\\r"]);
  }
}