| 1 | <?php |
| 2 | /************************************************************* |
| 3 | # 076 License |
| 4 | |
| 5 | Copyright (c) テクニカル諏訪子 |
| 6 | |
| 7 | Permission is hereby granted to any person obtaining a copy of the software |
| 8 | Little Beast (the "Software") to use, modify, merge, copy, publish, distribute, |
| 9 | sublicense, and/or sell copies of the Software, subject to the following conditions: |
| 10 | |
| 11 | 1. **Origin Attribution**: |
| 12 | - You must not misrepresent the origin of the Software; you must not claim |
| 13 | you created the original Software. |
| 14 | - If the Software is used in a product, you must either: |
| 15 | a. Provide clear attribution in the product's documentation, user interface, |
| 16 | or other visible areas, **OR** |
| 17 | b. Pay the original developers a fee they specify in writing. |
| 18 | 2. **Usage Restriction**: |
| 19 | - The Software, or any derivative works, dependencies, or libraries |
| 20 | incorporating it, must not be used for censorship or to suppress freedom of |
| 21 | speech, expression, or creativity. Prohibited uses include, but are not |
| 22 | limited to: |
| 23 | - Censorship of so-called "hate speech", visuals, non-mainstream opinions, |
| 24 | ideas, or objective reality. |
| 25 | - Tools or systems designed to restrict access to information or |
| 26 | artistic works. |
| 27 | 3. **Notice Preservation**: |
| 28 | - This license and the above copyright notice must remain intact in all copies |
| 29 | of the source code. |
| 30 | |
| 31 | THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, |
| 32 | INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A |
| 33 | PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT |
| 34 | HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF |
| 35 | CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE |
| 36 | OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. |
| 37 | */ |
| 38 | namespace Std\Lib\Db\Sqlite; |
| 39 | |
| 40 | use Std\Lib\Db\DbException; |
| 41 | |
| 42 | /** |
| 43 | * SQLiteのSQLを実行する |
| 44 | * |
| 45 | * SELECTは結合（入れ子のループ、rowid・インデックスでの検索）、GROUP BY、集約、HAVING、DISTINCT、ORDER BY、LIMIT、 |
| 46 | * 複合（UNION等）、サブクエリ（相関も）、ビュー、WITH（再帰無し）に対応。 |
| 47 | * 式は一度クロージャにしてから行毎に実行する。 |
| 48 | */ |
| 49 | final class Engine { |
| 50 | public int $changes = 0; |
| 51 | public int $totalChanges = 0; |
| 52 | public int $lastInsertRowid = 0; |
| 53 | |
| 54 | private Btree $bt; |
| 55 | private Parser $parser; |
| 56 | private ?Schema $schema = null; |
| 57 | private int $schemaGen = -1; |
| 58 | private int $schemaCookie = -1; |
| 59 | private array $params = []; |
| 60 | /** 相関サブクエリ用：深さ => 外の行 */ |
| 61 | private array $stack = []; |
| 62 | private array $cte = []; |
| 63 | /** 明示的なトランザクション（BEGIN） */ |
| 64 | private bool $txn = false; |
| 65 | private array $savepoints = []; |
| 66 | private bool $failKeep = false; |
| 67 | private bool $rollbackAll = false; |
| 68 | /** 最後のaccess()で使ったインデックスの列（rowid・全部ならnull） */ |
| 69 | private ?array $lastAccess = null; |
| 70 | |
| 71 | public function __construct(private Pager $pager) { |
| 72 | $this->bt = new Btree($pager); |
| 73 | $this->parser = new Parser(); |
| 74 | } |
| 75 | |
| 76 | public function inTransaction(): bool { |
| 77 | return $this->txn; |
| 78 | } |
| 79 | |
| 80 | public function btree(): Btree { |
| 81 | return $this->bt; |
| 82 | } |
| 83 | |
| 84 | /** SQLを文に分ける（構文木） */ |
| 85 | public function prepare(string $sql): array { |
| 86 | return $this->parser->parseAll($sql); |
| 87 | } |
| 88 | |
| 89 | /** |
| 90 | * 1つの文を実行する |
| 91 | * |
| 92 | * @param array $st prepare()の結果の1つ |
| 93 | * @param array $params 1からの番号 => 値 |
| 94 | * @return array cols（名前）、types（宣言された型）、tables、rows（値の配列の配列）、changes、lastId、isQuery |
| 95 | * @throws DbException |
| 96 | */ |
| 97 | public function run(array $st, array $params = []): array { |
| 98 | $this->params = $params; |
| 99 | switch ($st['t']) { |
| 100 | case 'begin': |
| 101 | return $this->begin($st['mode']); |
| 102 | case 'commit': |
| 103 | return $this->commit(); |
| 104 | case 'rollback': |
| 105 | return $this->rollback(); |
| 106 | case 'savepoint': |
| 107 | return $this->savepoint($st['name']); |
| 108 | case 'release': |
| 109 | return $this->release($st['name']); |
| 110 | case 'rollbackTo': |
| 111 | return $this->rollbackTo($st['name']); |
| 112 | case 'noop': |
| 113 | return self::result(); |
| 114 | } |
| 115 | |
| 116 | $write = $this->isWrite($st); |
| 117 | $this->pager->beginRead(); |
| 118 | $snap = $bsnap = null; |
| 119 | $this->failKeep = $this->rollbackAll = false; |
| 120 | try { |
| 121 | if ($write) { |
| 122 | $this->pager->beginWrite(); |
| 123 | if ($this->pager->nPages === 0) $this->pager->initialize(); |
| 124 | $snap = $this->pager->snapPush(); |
| 125 | $bsnap = $this->bt->snapPush(); |
| 126 | } |
| 127 | if (in_array($st['t'], ['insert', 'update', 'delete'], true)) $this->changes = 0; |
| 128 | $this->stack = []; |
| 129 | $this->cte = []; |
| 130 | $res = $this->dispatch($st); |
| 131 | if ($write) $this->finishWrite($snap, $bsnap); |
| 132 | return $res; |
| 133 | } catch (\Throwable $e) { |
| 134 | if ($write && $this->pager->inTransaction()) { |
| 135 | $this->schema = null; |
| 136 | if ($this->failKeep) { |
| 137 | // FAIL：それまでの変更は残す |
| 138 | $this->finishWrite($snap, $bsnap); |
| 139 | } else if ($this->rollbackAll || !$this->txn) { |
| 140 | $this->bt->reset(); |
| 141 | $this->pager->rollback(); |
| 142 | if ($this->rollbackAll) { |
| 143 | $this->txn = false; |
| 144 | $this->savepoints = []; |
| 145 | } |
| 146 | } else { |
| 147 | $this->bt->snapRestore($bsnap); |
| 148 | $this->bt->snapPop($bsnap); |
| 149 | $this->pager->snapRestore($snap); |
| 150 | $this->pager->snapPop($snap); |
| 151 | } |
| 152 | } |
| 153 | if ($e instanceof DbException) throw $e; |
| 154 | if ($e instanceof \LogicException || $e instanceof \UnexpectedValueException) throw new DbException('SQLite：'.$e->getMessage(), 11, 'HY000', $e); |
| 155 | throw $e; |
| 156 | } finally { |
| 157 | $this->pager->hold = $this->txn; |
| 158 | $this->pager->endRead(); |
| 159 | } |
| 160 | } |
| 161 | |
| 162 | private function finishWrite(?int $snap, ?int $bsnap): void { |
| 163 | $this->pager->snapPop($snap); |
| 164 | $this->bt->snapPop($bsnap); |
| 165 | if (!$this->txn) { |
| 166 | $this->bt->flush(); |
| 167 | $this->pager->commit(); |
| 168 | } |
| 169 | } |
| 170 | |
| 171 | private function isWrite(array $st): bool { |
| 172 | if (in_array($st['t'], ['insert', 'update', 'delete', 'createTable', 'createIndex', 'createView', 'drop', 'alter'], true)) return true; |
| 173 | if ($st['t'] === 'pragma' && $st['arg'] !== null && in_array($st['name'], ['user_version', 'application_id'], true)) return true; |
| 174 | return false; |
| 175 | } |
| 176 | |
| 177 | private function dispatch(array $st): array { |
| 178 | if (!empty($st['with']) && $st['t'] !== 'select') $this->cte[] = $st['with']; |
| 179 | switch ($st['t']) { |
| 180 | case 'select': |
| 181 | $plan = $this->planSelect($st, null); |
| 182 | $rows = ($plan->run)(-1); |
| 183 | return self::result($plan->cols, $plan->types, $rows, 0, $plan->tables); |
| 184 | case 'insert': |
| 185 | return $this->insert($st); |
| 186 | case 'update': |
| 187 | return $this->update($st); |
| 188 | case 'delete': |
| 189 | return $this->delete($st); |
| 190 | case 'createTable': |
| 191 | return $this->createTable($st); |
| 192 | case 'createIndex': |
| 193 | return $this->createIndex($st); |
| 194 | case 'createView': |
| 195 | return $this->createView($st); |
| 196 | case 'drop': |
| 197 | return $this->drop($st); |
| 198 | case 'alter': |
| 199 | return $this->alter($st); |
| 200 | case 'pragma': |
| 201 | return $this->pragma($st); |
| 202 | } |
| 203 | throw new DbException("未対応の文：{$st['t']}", 1, 'HY000'); |
| 204 | } |
| 205 | |
| 206 | private static function result(array $cols = [], array $types = [], array $rows = [], int $changes = 0, array $tables = []): array { |
| 207 | return ['cols' => $cols, 'types' => $types, 'rows' => $rows, 'changes' => $changes, 'tables' => $tables]; |
| 208 | } |
| 209 | |
| 210 | /** スキーマ（変更されていれば読み直す） */ |
| 211 | public function schema(): Schema { |
| 212 | if ($this->schema === null || $this->schemaGen !== $this->pager->generation || $this->schemaCookie !== $this->pager->hdr['cookie']) { |
| 213 | $this->schema = Schema::load($this->bt, $this->pager->nPages); |
| 214 | $this->schemaGen = $this->pager->generation; |
| 215 | $this->schemaCookie = $this->pager->hdr['cookie']; |
| 216 | } |
| 217 | return $this->schema; |
| 218 | } |
| 219 | |
| 220 | /////////////// |
| 221 | // トランザクション |
| 222 | |
| 223 | private function begin(string $mode): array { |
| 224 | if ($this->txn) throw new DbException('トランザクションの中でトランザクションは開始できません', 1, 'HY000'); |
| 225 | $this->pager->beginRead(); |
| 226 | if ($mode !== 'DEFERRED') { |
| 227 | try { |
| 228 | $this->pager->beginWrite(); |
| 229 | if ($this->pager->nPages === 0) $this->pager->initialize(); |
| 230 | } catch (DbException $e) { |
| 231 | $this->pager->endRead(); |
| 232 | throw $e; |
| 233 | } |
| 234 | } |
| 235 | $this->txn = true; |
| 236 | $this->pager->hold = true; |
| 237 | return self::result(); |
| 238 | } |
| 239 | |
| 240 | private function commit(): array { |
| 241 | if (!$this->txn) throw new DbException('トランザクションが開始されていません', 1, 'HY000'); |
| 242 | $this->bt->flush(); |
| 243 | $this->bt->snapPop(0); |
| 244 | $this->pager->commit(); |
| 245 | $this->txn = false; |
| 246 | $this->savepoints = []; |
| 247 | $this->pager->hold = false; |
| 248 | $this->pager->endRead(); |
| 249 | return self::result(); |
| 250 | } |
| 251 | |
| 252 | private function rollback(): array { |
| 253 | if (!$this->txn) throw new DbException('トランザクションが開始されていません', 1, 'HY000'); |
| 254 | $this->bt->reset(); |
| 255 | $this->pager->rollback(); |
| 256 | $this->txn = false; |
| 257 | $this->savepoints = []; |
| 258 | $this->pager->hold = false; |
| 259 | $this->pager->endRead(); |
| 260 | return self::result(); |
| 261 | } |
| 262 | |
| 263 | private function savepoint(string $name): array { |
| 264 | $started = false; |
| 265 | if (!$this->txn) { |
| 266 | $this->begin('IMMEDIATE'); |
| 267 | $started = true; |
| 268 | } else { |
| 269 | $this->pager->beginWrite(); |
| 270 | if ($this->pager->nPages === 0) $this->pager->initialize(); |
| 271 | } |
| 272 | $this->savepoints[] = [strtolower($name), $this->pager->snapPush(), $started, $this->bt->snapPush()]; |
| 273 | return self::result(); |
| 274 | } |
| 275 | |
| 276 | private function findSavepoint(string $name): int { |
| 277 | for ($i = count($this->savepoints) - 1; $i >= 0; $i--) { |
| 278 | if ($this->savepoints[$i][0] === strtolower($name)) return $i; |
| 279 | } |
| 280 | throw new DbException("セーブポイントがありません：{$name}", 1, 'HY000'); |
| 281 | } |
| 282 | |
| 283 | private function release(string $name): array { |
| 284 | $i = $this->findSavepoint($name); |
| 285 | [, $snap, $started, $bsnap] = $this->savepoints[$i]; |
| 286 | $this->pager->snapPop($snap); |
| 287 | $this->bt->snapPop($bsnap); |
| 288 | array_splice($this->savepoints, $i); |
| 289 | if ($started) return $this->commit(); |
| 290 | return self::result(); |
| 291 | } |
| 292 | |
| 293 | private function rollbackTo(string $name): array { |
| 294 | $i = $this->findSavepoint($name); |
| 295 | [, $snap, , $bsnap] = $this->savepoints[$i]; |
| 296 | $this->bt->snapRestore($bsnap); |
| 297 | $this->pager->snapRestore($snap); |
| 298 | $this->schema = null; |
| 299 | array_splice($this->savepoints, $i + 1); |
| 300 | return self::result(); |
| 301 | } |
| 302 | |
| 303 | /////////////// |
| 304 | // スコープ（列の場所） |
| 305 | |
| 306 | /** |
| 307 | * スコープを作る |
| 308 | * |
| 309 | * @param array $srcs 各ソース：alias、name、cols（表示の名前）、types、affs、colls、table（テーブルの定義、又はnull） |
| 310 | */ |
| 311 | private function scope(array $srcs, ?\stdClass $parent): \stdClass { |
| 312 | $sc = new \stdClass(); |
| 313 | $sc->parent = $parent; |
| 314 | $sc->level = $parent ? $parent->level + 1 : 0; |
| 315 | $sc->srcs = []; |
| 316 | $sc->srcOf = []; |
| 317 | $off = 0; |
| 318 | foreach ($srcs as $j => $s) { |
| 319 | $s['base'] = $off; |
| 320 | $s['map'] = []; |
| 321 | foreach ($s['cols'] as $i => $n) { |
| 322 | $s['map'][strtolower($n)] ??= $off + $i; |
| 323 | $sc->srcOf[$off + $i] = $j; |
| 324 | } |
| 325 | $s['rowid'] = $off + count($s['cols']); |
| 326 | $sc->srcOf[$s['rowid']] = $j; |
| 327 | $s['hidden'] ??= []; |
| 328 | $off += count($s['cols']) + 1; |
| 329 | $sc->srcs[$j] = $s; |
| 330 | } |
| 331 | $sc->width = $off; |
| 332 | $sc->usedOuter = false; |
| 333 | return $sc; |
| 334 | } |
| 335 | |
| 336 | /** |
| 337 | * 列を探す |
| 338 | * |
| 339 | * @return array|null level、off、aff、coll、type、src、table |
| 340 | */ |
| 341 | private function resolve(\stdClass $sc, ?string $tbl, string $name): ?array { |
| 342 | $ln = strtolower($name); |
| 343 | $lt = $tbl === null ? null : strtolower($tbl); |
| 344 | for ($s = $sc; $s !== null; $s = $s->parent) { |
| 345 | $found = null; |
| 346 | foreach ($s->srcs as $j => $src) { |
| 347 | if ($lt !== null && $lt !== strtolower((string)$src['alias'])) continue; |
| 348 | $off = $src['map'][$ln] ?? null; |
| 349 | if ($off === null && in_array($ln, ['rowid', 'oid', '_rowid_'], true) && ($src['table'] ?? null) !== null) $off = $src['rowid']; |
| 350 | if ($off === null) continue; |
| 351 | if ($lt === null && isset($src['hidden'][$ln])) continue; |
| 352 | if ($found !== null && $lt === null) throw new DbException("列の名前が曖昧です：{$name}", 1, 'HY000'); |
| 353 | $i = $off - $src['base']; |
| 354 | $found = ['level' => $s->level, 'off' => $off, 'src' => $j, 'aff' => $src['affs'][$i] ?? Value::INTEGER, |
| 355 | 'coll' => $src['colls'][$i] ?? 'BINARY', 'type' => $src['types'][$i] ?? 'INTEGER', 'table' => $src['name'] ?? null, |
| 356 | 'name' => $src['cols'][$i] ?? $name, 'col' => $i]; |
| 357 | } |
| 358 | if ($found !== null) { |
| 359 | if ($s !== $sc) { |
| 360 | for ($x = $sc; $x !== $s; $x = $x->parent) $x->usedOuter = true; |
| 361 | } |
| 362 | return $found; |
| 363 | } |
| 364 | } |
| 365 | return null; |
| 366 | } |
| 367 | |
| 368 | /////////////// |
| 369 | // 式 |
| 370 | |
| 371 | /** |
| 372 | * 式をクロージャにする |
| 373 | * |
| 374 | * @param array $e 式の構文木 |
| 375 | * @param \stdClass $sc スコープ |
| 376 | * @param \stdClass|null $agg 集約の文脈（集約関数を集める） |
| 377 | * @return \Closure fn(array $row): mixed |
| 378 | */ |
| 379 | private function compile(array $e, \stdClass $sc, ?\stdClass $agg = null): \Closure { |
| 380 | switch ($e['t']) { |
| 381 | case 'lit': |
| 382 | $v = $e['v']; |
| 383 | return fn(array $r) => $v; |
| 384 | case 'param': |
| 385 | $i = $e['i']; |
| 386 | return fn(array $r) => $this->params[$i] ?? null; |
| 387 | case 'col': |
| 388 | $c = $this->resolve($sc, $e['tbl'], $e['name']); |
| 389 | if ($c === null) { |
| 390 | if (!empty($e['dq']) && $e['tbl'] === null) { |
| 391 | $v = $e['name']; |
| 392 | return fn(array $r) => $v; |
| 393 | } |
| 394 | throw new DbException('列がありません：'.($e['tbl'] !== null ? $e['tbl'].'.' : '').$e['name'], 1, 'HY000'); |
| 395 | } |
| 396 | $o = $c['off']; |
| 397 | if ($c['level'] === $sc->level) return fn(array $r) => $r[$o] ?? null; |
| 398 | $L = $c['level']; |
| 399 | return fn(array $r) => $this->stack[$L][$o] ?? null; |
| 400 | case 'collate': |
| 401 | return $this->compile($e['e'], $sc, $agg); |
| 402 | case 'un': |
| 403 | $f = $this->compile($e['e'], $sc, $agg); |
| 404 | switch ($e['op']) { |
| 405 | case '-': |
| 406 | return function(array $r) use ($f) { |
| 407 | $v = $f($r); |
| 408 | if ($v === null) return null; |
| 409 | $n = Value::toNumber($v); |
| 410 | if ($n === PHP_INT_MIN) return 9.2233720368547758e18; |
| 411 | return -$n; |
| 412 | }; |
| 413 | case '+': |
| 414 | return $f; |
| 415 | case '~': |
| 416 | return function(array $r) use ($f) { |
| 417 | $v = $f($r); |
| 418 | return $v === null ? null : ~(int)Value::toInt($v); |
| 419 | }; |
| 420 | default: |
| 421 | return function(array $r) use ($f) { |
| 422 | $t = Value::truth($f($r)); |
| 423 | return $t === null ? null : ($t ? 0 : 1); |
| 424 | }; |
| 425 | } |
| 426 | case 'bin': |
| 427 | return $this->compileBinary($e, $sc, $agg); |
| 428 | case 'is': |
| 429 | $l = $this->compile($e['l'], $sc, $agg); |
| 430 | $rf = $this->compile($e['r'], $sc, $agg); |
| 431 | $affL = $this->exprAff($e['l'], $sc); |
| 432 | $affR = $this->exprAff($e['r'], $sc); |
| 433 | $coll = $this->cmpColl($e['l'], $e['r'], $sc); |
| 434 | $not = $e['not']; |
| 435 | return function(array $r) use ($l, $rf, $affL, $affR, $coll, $not) { |
| 436 | $a = $l($r); |
| 437 | $b = $rf($r); |
| 438 | if ($a === null || $b === null) $eq = $a === null && $b === null; |
| 439 | else { |
| 440 | [$a, $b] = Value::prepareCompare($a, $affL, $b, $affR); |
| 441 | $eq = Record::compare($a, $b, $coll) === 0; |
| 442 | } |
| 443 | return ($eq xor $not) ? 1 : 0; |
| 444 | }; |
| 445 | case 'isnull': |
| 446 | $f = $this->compile($e['e'], $sc, $agg); |
| 447 | $not = $e['not']; |
| 448 | return fn(array $r) => (($f($r) === null) xor $not) ? 1 : 0; |
| 449 | case 'between': |
| 450 | $f = $this->compile($e['e'], $sc, $agg); |
| 451 | $lo = $this->compile($e['lo'], $sc, $agg); |
| 452 | $hi = $this->compile($e['hi'], $sc, $agg); |
| 453 | $aff = $this->exprAff($e['e'], $sc); |
| 454 | $affLo = $this->exprAff($e['lo'], $sc); |
| 455 | $affHi = $this->exprAff($e['hi'], $sc); |
| 456 | $coll1 = $this->cmpColl($e['e'], $e['lo'], $sc); |
| 457 | $coll2 = $this->cmpColl($e['e'], $e['hi'], $sc); |
| 458 | $not = $e['not']; |
| 459 | return function(array $r) use ($f, $lo, $hi, $aff, $affLo, $affHi, $coll1, $coll2, $not) { |
| 460 | $v = $f($r); |
| 461 | $a = $lo($r); |
| 462 | $b = $hi($r); |
| 463 | $ge = $v === null || $a === null ? null : (function() use ($v, $a, $aff, $affLo, $coll1) { |
| 464 | [$x, $y] = Value::prepareCompare($v, $aff, $a, $affLo); |
| 465 | return Record::compare($x, $y, $coll1) >= 0; |
| 466 | })(); |
| 467 | $le = $v === null || $b === null ? null : (function() use ($v, $b, $aff, $affHi, $coll2) { |
| 468 | [$x, $y] = Value::prepareCompare($v, $aff, $b, $affHi); |
| 469 | return Record::compare($x, $y, $coll2) <= 0; |
| 470 | })(); |
| 471 | if ($ge === false || $le === false) $res = false; |
| 472 | else if ($ge === null || $le === null) return null; |
| 473 | else $res = true; |
| 474 | return ($res xor $not) ? 1 : 0; |
| 475 | }; |
| 476 | case 'like': |
| 477 | $l = $this->compile($e['l'], $sc, $agg); |
| 478 | $p = $this->compile($e['r'], $sc, $agg); |
| 479 | $esc = $e['esc'] ? $this->compile($e['esc'], $sc, $agg) : null; |
| 480 | $glob = $e['op'] === 'GLOB'; |
| 481 | $not = $e['not']; |
| 482 | return function(array $r) use ($l, $p, $esc, $glob, $not) { |
| 483 | $v = $l($r); |
| 484 | $pat = $p($r); |
| 485 | if ($v === null || $pat === null) return null; |
| 486 | $x = $esc ? $esc($r) : null; |
| 487 | if ($esc && ($x === null || mb_strlen((string)$x) !== 1)) throw new DbException('ESCAPEは1文字です', 1, 'HY000'); |
| 488 | $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); |
| 489 | return ($m xor $not) ? 1 : 0; |
| 490 | }; |
| 491 | case 'in': |
| 492 | return $this->compileIn($e, $sc, $agg); |
| 493 | case 'case': |
| 494 | $base = $e['base'] ? $this->compile($e['base'], $sc, $agg) : null; |
| 495 | $affB = $e['base'] ? $this->exprAff($e['base'], $sc) : ''; |
| 496 | $whens = []; |
| 497 | foreach ($e['when'] as [$c, $v]) { |
| 498 | $whens[] = [$this->compile($c, $sc, $agg), $this->compile($v, $sc, $agg), $e['base'] ? $this->exprAff($c, $sc) : '', |
| 499 | $e['base'] ? $this->cmpColl($e['base'], $c, $sc) : 'BINARY']; |
| 500 | } |
| 501 | $else = $e['else'] ? $this->compile($e['else'], $sc, $agg) : null; |
| 502 | return function(array $r) use ($base, $affB, $whens, $else) { |
| 503 | $b = $base ? $base($r) : null; |
| 504 | foreach ($whens as [$c, $v, $affW, $coll]) { |
| 505 | if ($base) { |
| 506 | $w = $c($r); |
| 507 | if ($b === null || $w === null) continue; |
| 508 | [$x, $y] = Value::prepareCompare($b, $affB, $w, $affW); |
| 509 | if (Record::compare($x, $y, $coll) === 0) return $v($r); |
| 510 | } else if (Value::truth($c($r))) { |
| 511 | return $v($r); |
| 512 | } |
| 513 | } |
| 514 | return $else ? $else($r) : null; |
| 515 | }; |
| 516 | case 'cast': |
| 517 | $f = $this->compile($e['e'], $sc, $agg); |
| 518 | $aff = Value::castAffinity($e['type']); |
| 519 | return fn(array $r) => self::cast($f($r), $aff); |
| 520 | case 'fn': |
| 521 | return $this->compileFunction($e, $sc, $agg); |
| 522 | case 'exists': |
| 523 | $plan = $this->planSelect($e['sel'], $sc); |
| 524 | return $this->subqueryRunner($plan, $sc, 1, fn(array $rows) => empty($rows) ? 0 : 1); |
| 525 | case 'sub': |
| 526 | $plan = $this->planSelect($e['sel'], $sc); |
| 527 | if (count($plan->cols) !== 1) throw new DbException('サブクエリの列は1つです（'.count($plan->cols).'列）', 1, 'HY000'); |
| 528 | return $this->subqueryRunner($plan, $sc, 1, fn(array $rows) => $rows[0][0] ?? null); |
| 529 | case 'vector': |
| 530 | throw new DbException('行の値（(a, b)）は未対応です', 1, 'HY000'); |
| 531 | } |
| 532 | throw new DbException("未対応の式：{$e['t']}", 1, 'HY000'); |
| 533 | } |
| 534 | |
| 535 | /** サブクエリを実行するクロージャ（相関が無ければ1回だけ） */ |
| 536 | private function subqueryRunner(\stdClass $plan, \stdClass $sc, int $limit, \Closure $map): \Closure { |
| 537 | $L = $sc->level; |
| 538 | if (!$plan->correlated) { |
| 539 | $done = false; |
| 540 | $val = null; |
| 541 | return function(array $r) use (&$done, &$val, $plan, $limit, $map, $L) { |
| 542 | if (!$done) { |
| 543 | $this->stack[$L] = $r; |
| 544 | $val = $map(($plan->run)($limit)); |
| 545 | $done = true; |
| 546 | } |
| 547 | return $val; |
| 548 | }; |
| 549 | } |
| 550 | return function(array $r) use ($plan, $limit, $map, $L) { |
| 551 | $saved = $this->stack[$L] ?? null; |
| 552 | $this->stack[$L] = $r; |
| 553 | try { |
| 554 | return $map(($plan->run)($limit)); |
| 555 | } finally { |
| 556 | $this->stack[$L] = $saved; |
| 557 | } |
| 558 | }; |
| 559 | } |
| 560 | |
| 561 | private function compileBinary(array $e, \stdClass $sc, ?\stdClass $agg): \Closure { |
| 562 | $l = $this->compile($e['l'], $sc, $agg); |
| 563 | $rf = $this->compile($e['r'], $sc, $agg); |
| 564 | switch ($e['op']) { |
| 565 | case 'AND': |
| 566 | return function(array $r) use ($l, $rf) { |
| 567 | $a = Value::truth($l($r)); |
| 568 | if ($a === false) return 0; |
| 569 | $b = Value::truth($rf($r)); |
| 570 | if ($b === false) return 0; |
| 571 | return $a === null || $b === null ? null : 1; |
| 572 | }; |
| 573 | case 'OR': |
| 574 | return function(array $r) use ($l, $rf) { |
| 575 | $a = Value::truth($l($r)); |
| 576 | if ($a === true) return 1; |
| 577 | $b = Value::truth($rf($r)); |
| 578 | if ($b === true) return 1; |
| 579 | return $a === null || $b === null ? null : 0; |
| 580 | }; |
| 581 | case '=': |
| 582 | case '!=': |
| 583 | case '<': |
| 584 | case '<=': |
| 585 | case '>': |
| 586 | case '>=': |
| 587 | $affL = $this->exprAff($e['l'], $sc); |
| 588 | $affR = $this->exprAff($e['r'], $sc); |
| 589 | $coll = $this->cmpColl($e['l'], $e['r'], $sc); |
| 590 | $op = $e['op']; |
| 591 | return function(array $r) use ($l, $rf, $affL, $affR, $coll, $op) { |
| 592 | $a = $l($r); |
| 593 | $b = $rf($r); |
| 594 | if ($a === null || $b === null) return null; |
| 595 | [$a, $b] = Value::prepareCompare($a, $affL, $b, $affR); |
| 596 | $c = Record::compare($a, $b, $coll); |
| 597 | return match ($op) { |
| 598 | '=' => (int)($c === 0), '!=' => (int)($c !== 0), '<' => (int)($c < 0), '<=' => (int)($c <= 0), '>' => (int)($c > 0), default => (int)($c >= 0), |
| 599 | }; |
| 600 | }; |
| 601 | case '||': |
| 602 | return function(array $r) use ($l, $rf) { |
| 603 | $a = $l($r); |
| 604 | $b = $rf($r); |
| 605 | if ($a === null || $b === null) return null; |
| 606 | return Value::toText($a).Value::toText($b); |
| 607 | }; |
| 608 | case '+': |
| 609 | case '-': |
| 610 | case '*': |
| 611 | $op = $e['op']; |
| 612 | return function(array $r) use ($l, $rf, $op) { |
| 613 | $a = $l($r); |
| 614 | $b = $rf($r); |
| 615 | if ($a === null || $b === null) return null; |
| 616 | $a = Value::toNumber($a); |
| 617 | $b = Value::toNumber($b); |
| 618 | return match ($op) { '+' => $a + $b, '-' => $a - $b, default => $a * $b }; |
| 619 | }; |
| 620 | case '/': |
| 621 | return function(array $r) use ($l, $rf) { |
| 622 | $a = $l($r); |
| 623 | $b = $rf($r); |
| 624 | if ($a === null || $b === null) return null; |
| 625 | $a = Value::toNumber($a); |
| 626 | $b = Value::toNumber($b); |
| 627 | if ($b == 0) return null; |
| 628 | if (is_int($a) && is_int($b)) { |
| 629 | if ($a === PHP_INT_MIN && $b === -1) return 9.2233720368547758e18; |
| 630 | return intdiv($a, $b); |
| 631 | } |
| 632 | return $a / $b; |
| 633 | }; |
| 634 | case '%': |
| 635 | return function(array $r) use ($l, $rf) { |
| 636 | $a = $l($r); |
| 637 | $b = $rf($r); |
| 638 | if ($a === null || $b === null) return null; |
| 639 | $a = Value::toNumber($a); |
| 640 | $b = Value::toNumber($b); |
| 641 | if (is_int($a) && is_int($b)) { |
| 642 | if ($b === 0) return null; |
| 643 | if ($b === -1) return 0; |
| 644 | return $a % $b; |
| 645 | } |
| 646 | // SQLite 3.45：整数にしてから計算し、REALの結果 |
| 647 | $ia = (int)Value::toInt($a); |
| 648 | $ib = (int)Value::toInt($b); |
| 649 | if ($ib === 0) return null; |
| 650 | return (float)($ib === -1 ? 0 : $ia % $ib); |
| 651 | }; |
| 652 | case '&': |
| 653 | case '|': |
| 654 | case '<<': |
| 655 | case '>>': |
| 656 | $op = $e['op']; |
| 657 | return function(array $r) use ($l, $rf, $op) { |
| 658 | $a = $l($r); |
| 659 | $b = $rf($r); |
| 660 | if ($a === null || $b === null) return null; |
| 661 | $a = (int)Value::toInt($a); |
| 662 | $b = (int)Value::toInt($b); |
| 663 | switch ($op) { |
| 664 | case '&': |
| 665 | return $a & $b; |
| 666 | case '|': |
| 667 | return $a | $b; |
| 668 | case '<<': |
| 669 | if ($b < 0) return $b <= -64 ? ($a < 0 ? -1 : 0) : $a >> -$b; |
| 670 | return $b >= 64 ? 0 : $a << $b; |
| 671 | default: |
| 672 | if ($b < 0) return $b <= -64 ? 0 : $a << -$b; |
| 673 | return $b >= 64 ? ($a < 0 ? -1 : 0) : $a >> $b; |
| 674 | } |
| 675 | }; |
| 676 | } |
| 677 | throw new DbException("未対応の演算子：{$e['op']}", 1, 'HY000'); |
| 678 | } |
| 679 | |
| 680 | private function compileIn(array $e, \stdClass $sc, ?\stdClass $agg): \Closure { |
| 681 | $f = $this->compile($e['e'], $sc, $agg); |
| 682 | $aff = $this->exprAff($e['e'], $sc); |
| 683 | $not = $e['not']; |
| 684 | $test = function(mixed $v, iterable $list, string $affR, string $coll) use ($aff, $not) { |
| 685 | $hasNull = false; |
| 686 | $any = false; |
| 687 | foreach ($list as $x) { |
| 688 | $any = true; |
| 689 | if ($x === null) { |
| 690 | $hasNull = true; |
| 691 | continue; |
| 692 | } |
| 693 | if ($v === null) continue; |
| 694 | [$a, $b] = Value::prepareCompare($v, $aff, $x, $affR); |
| 695 | if (Record::compare($a, $b, $coll) === 0) return $not ? 0 : 1; |
| 696 | } |
| 697 | if (!$any) return $not ? 1 : 0; |
| 698 | if ($v === null || $hasNull) return null; |
| 699 | return $not ? 1 : 0; |
| 700 | }; |
| 701 | if ($e['list'] !== null) { |
| 702 | $items = []; |
| 703 | foreach ($e['list'] as $x) $items[] = [$this->compile($x, $sc, $agg), $this->exprAff($x, $sc), $this->cmpColl($e['e'], $x, $sc)]; |
| 704 | return function(array $r) use ($f, $items, $aff, $not) { |
| 705 | $v = $f($r); |
| 706 | if (empty($items)) return $not ? 1 : 0; |
| 707 | $hasNull = false; |
| 708 | foreach ($items as [$g, $affR, $coll]) { |
| 709 | $x = $g($r); |
| 710 | if ($x === null) { |
| 711 | $hasNull = true; |
| 712 | continue; |
| 713 | } |
| 714 | if ($v === null) continue; |
| 715 | [$a, $b] = Value::prepareCompare($v, $aff, $x, $affR); |
| 716 | if (Record::compare($a, $b, $coll) === 0) return $not ? 0 : 1; |
| 717 | } |
| 718 | if ($v === null || $hasNull) return null; |
| 719 | return $not ? 1 : 0; |
| 720 | }; |
| 721 | } |
| 722 | $sel = $e['sel'] ?? ['t' => 'select', 'with' => [], 'cores' => [['distinct' => false, 'cols' => [['star' => null]], |
| 723 | 'from' => [['table' => $e['table'], 'as' => null, 'join' => 'inner', 'on' => null, 'using' => null, 'natural' => false]], |
| 724 | 'where' => null, 'group' => [], 'having' => null]], 'ops' => [], 'order' => [], 'limit' => null, 'offset' => null]; |
| 725 | $plan = $this->planSelect($sel, $sc); |
| 726 | if (count($plan->cols) !== 1) throw new DbException('INのサブクエリの列は1つです', 1, 'HY000'); |
| 727 | $affR = $plan->affs[0] ?? ''; |
| 728 | $coll = $this->exprColl($e['e'], $sc) ?? ($plan->colls[0] ?? 'BINARY'); |
| 729 | $values = $this->subqueryRunner($plan, $sc, -1, fn(array $rows) => array_column($rows, 0)); |
| 730 | return function(array $r) use ($f, $values, $test, $affR, $coll) { |
| 731 | return $test($f($r), $values($r), $affR, $coll); |
| 732 | }; |
| 733 | } |
| 734 | |
| 735 | private function compileFunction(array $e, \stdClass $sc, ?\stdClass $agg): \Closure { |
| 736 | $name = $e['name']; |
| 737 | if (Func::isAggregate($name, count($e['args']), $e['star'])) { |
| 738 | if ($agg === null || $agg->inAgg) throw new DbException("集約関数の使い方が間違っています：{$name}()", 1, 'HY000'); |
| 739 | $agg->inAgg = true; |
| 740 | $args = []; |
| 741 | foreach ($e['args'] as $a) $args[] = $this->compile($a, $sc, $agg); |
| 742 | $agg->inAgg = false; |
| 743 | $k = count($agg->list); |
| 744 | $filter = null; |
| 745 | if (!empty($e['filter'])) { |
| 746 | $agg->inAgg = true; |
| 747 | $filter = $this->compile($e['filter'], $sc, $agg); |
| 748 | $agg->inAgg = false; |
| 749 | } |
| 750 | $agg->list[] = ['name' => $name, 'args' => $args, 'star' => $e['star'], 'distinct' => $e['distinct'], 'filter' => $filter, |
| 751 | 'coll' => isset($e['args'][0]) ? ($this->exprColl($e['args'][0], $sc) ?? 'BINARY') : 'BINARY']; |
| 752 | $o = $agg->base + $k; |
| 753 | return fn(array $r) => $r[$o] ?? null; |
| 754 | } |
| 755 | if ($e['star']) throw new DbException("関数{$name}(*)は使えません", 1, 'HY000'); |
| 756 | $args = []; |
| 757 | $colls = []; |
| 758 | foreach ($e['args'] as $a) { |
| 759 | $args[] = $this->compile($a, $sc, $agg); |
| 760 | $colls[] = $this->exprColl($a, $sc) ?? 'BINARY'; |
| 761 | } |
| 762 | if (in_array($name, ['random', 'randomblob', 'changes', 'total_changes', 'last_insert_rowid', 'current_timestamp', 'current_date', 'current_time'], true) |
| 763 | || !empty($args)) { |
| 764 | return function(array $r) use ($name, $args, $colls) { |
| 765 | $vals = []; |
| 766 | foreach ($args as $g) $vals[] = $g($r); |
| 767 | return Func::call($name, $vals, $this, $colls); |
| 768 | }; |
| 769 | } |
| 770 | return fn(array $r) => Func::call($name, [], $this, $colls); |
| 771 | } |
| 772 | |
| 773 | /** CAST */ |
| 774 | public static function cast(mixed $v, string $aff): mixed { |
| 775 | if ($v === null) return null; |
| 776 | switch ($aff) { |
| 777 | case Value::INTEGER: |
| 778 | return Value::toInt($v); |
| 779 | case Value::REAL: |
| 780 | return (float)Value::toNumber($v); |
| 781 | case Value::TEXT: |
| 782 | return Value::toText($v); |
| 783 | case Value::BLOB: |
| 784 | return $v instanceof Blob ? $v : new Blob((string)Value::toText($v)); |
| 785 | default: |
| 786 | if (is_int($v) || is_float($v)) return Value::apply($v, Value::NUMERIC); |
| 787 | $n = Value::toNumber($v); |
| 788 | return is_float($n) && floor($n) === $n && abs($n) < 9.2e18 ? (int)$n : $n; |
| 789 | } |
| 790 | } |
| 791 | |
| 792 | /** 式の親和性（列・CASTだけ） */ |
| 793 | private function exprAff(array $e, \stdClass $sc): string { |
| 794 | if ($e['t'] === 'col') { |
| 795 | try { |
| 796 | $c = $this->resolve($sc, $e['tbl'], $e['name']); |
| 797 | } catch (DbException $ex) { |
| 798 | return ''; |
| 799 | } |
| 800 | return $c['aff'] ?? ''; |
| 801 | } |
| 802 | if ($e['t'] === 'cast') return Value::castAffinity($e['type']); |
| 803 | if ($e['t'] === 'collate') return $this->exprAff($e['e'], $sc); |
| 804 | return ''; |
| 805 | } |
| 806 | |
| 807 | /** 式の照合順序（明示的なCOLLATE、列） */ |
| 808 | private function exprColl(array $e, \stdClass $sc, bool $explicitOnly = false): ?string { |
| 809 | if ($e['t'] === 'collate') return $e['coll']; |
| 810 | if ($explicitOnly) return null; |
| 811 | if ($e['t'] === 'col') { |
| 812 | try { |
| 813 | $c = $this->resolve($sc, $e['tbl'], $e['name']); |
| 814 | } catch (DbException $ex) { |
| 815 | return null; |
| 816 | } |
| 817 | return $c['coll'] ?? null; |
| 818 | } |
| 819 | return null; |
| 820 | } |
| 821 | |
| 822 | /** 比較の照合順序（左の明示、右の明示、左の列、右の列） */ |
| 823 | private function cmpColl(array $l, array $r, \stdClass $sc): string { |
| 824 | return $this->exprColl($l, $sc, true) ?? $this->exprColl($r, $sc, true) ?? $this->exprColl($l, $sc) ?? $this->exprColl($r, $sc) ?? 'BINARY'; |
| 825 | } |
| 826 | |
| 827 | /** 式の中の集約関数が有るか */ |
| 828 | private static function hasAggregate(?array $e): bool { |
| 829 | if ($e === null) return false; |
| 830 | if ($e['t'] === 'fn' && Func::isAggregate($e['name'], count($e['args']), $e['star'])) return true; |
| 831 | if (in_array($e['t'], ['sub', 'exists'], true)) return false; |
| 832 | foreach ($e as $k => $v) { |
| 833 | if ($k === 'sel' || !is_array($v)) continue; |
| 834 | if (isset($v['t'])) { |
| 835 | if (self::hasAggregate($v)) return true; |
| 836 | } else { |
| 837 | foreach ($v as $x) { |
| 838 | if (is_array($x) && isset($x['t']) && self::hasAggregate($x)) return true; |
| 839 | if (is_array($x) && !isset($x['t'])) foreach ($x as $y) if (is_array($y) && isset($y['t']) && self::hasAggregate($y)) return true; |
| 840 | } |
| 841 | } |
| 842 | } |
| 843 | return false; |
| 844 | } |
| 845 | |
| 846 | /** ANDで分ける */ |
| 847 | private static function conjuncts(?array $e): array { |
| 848 | if ($e === null) return []; |
| 849 | if ($e['t'] === 'bin' && $e['op'] === 'AND') return array_merge(self::conjuncts($e['l']), self::conjuncts($e['r'])); |
| 850 | return [$e]; |
| 851 | } |
| 852 | |
| 853 | /** |
| 854 | * 式が使うソース（このスコープの） |
| 855 | * |
| 856 | * @return array|null ソースの番号の配列、サブクエリが有ればnull（全部） |
| 857 | */ |
| 858 | private function refs(array $e, \stdClass $sc): ?array { |
| 859 | if (in_array($e['t'], ['sub', 'exists'], true) || ($e['t'] === 'in' && ($e['sel'] !== null || isset($e['table'])))) return null; |
| 860 | if ($e['t'] === 'col') { |
| 861 | $c = $this->resolve($sc, $e['tbl'], $e['name']); |
| 862 | if ($c === null || $c['level'] !== $sc->level) return []; |
| 863 | return [$c['src']]; |
| 864 | } |
| 865 | $out = []; |
| 866 | foreach ($e as $k => $v) { |
| 867 | if (!is_array($v)) continue; |
| 868 | $list = isset($v['t']) ? [$v] : $v; |
| 869 | foreach ($list as $x) { |
| 870 | if (!is_array($x)) continue; |
| 871 | $subs = isset($x['t']) ? [$x] : $x; |
| 872 | foreach ($subs as $y) { |
| 873 | if (!is_array($y) || !isset($y['t'])) continue; |
| 874 | $r = $this->refs($y, $sc); |
| 875 | if ($r === null) return null; |
| 876 | $out = array_merge($out, $r); |
| 877 | } |
| 878 | } |
| 879 | } |
| 880 | return array_values(array_unique($out)); |
| 881 | } |
| 882 | |
| 883 | /////////////// |
| 884 | // SELECT |
| 885 | |
| 886 | /** |
| 887 | * SELECTの計画 |
| 888 | * |
| 889 | * @return \stdClass cols、types、affs、colls、tables、correlated、run（fn(int $limit): array 行の配列） |
| 890 | */ |
| 891 | private function planSelect(array $sel, ?\stdClass $parent): \stdClass { |
| 892 | $pushed = false; |
| 893 | if (!empty($sel['with'])) { |
| 894 | $this->cte[] = $sel['with']; |
| 895 | $pushed = true; |
| 896 | } |
| 897 | try { |
| 898 | $single = count($sel['cores']) === 1; |
| 899 | $cores = []; |
| 900 | $correlated = false; |
| 901 | foreach ($sel['cores'] as $i => $core) { |
| 902 | $cores[] = $this->planCore($core, $parent, $single ? $sel['order'] : []); |
| 903 | $correlated = $correlated || $cores[$i]->correlated; |
| 904 | } |
| 905 | $first = $cores[0]; |
| 906 | foreach ($cores as $c) { |
| 907 | if (count($c->cols) !== count($first->cols)) throw new DbException('複合のSELECTの列の数が違います', 1, 'HY000'); |
| 908 | } |
| 909 | $limitF = $sel['limit'] ? $this->compile($sel['limit'], $this->scope([], $parent)) : null; |
| 910 | $offsetF = $sel['offset'] ? $this->compile($sel['offset'], $this->scope([], $parent)) : null; |
| 911 | |
| 912 | // 複合のORDER BY：出力の列 |
| 913 | $orderIdx = []; |
| 914 | if (!$single && $sel['order']) { |
| 915 | foreach ($sel['order'] as $o) { |
| 916 | $k = $this->outputIndex($o['e'], $first); |
| 917 | if ($k === null) throw new DbException('複合のSELECTのORDER BYは結果の列だけです', 1, 'HY000'); |
| 918 | $orderIdx[] = [$k, $o['desc'], $o['nulls'], $o['e']['t'] === 'collate' ? $o['e']['coll'] : ($first->colls[$k] ?? 'BINARY')]; |
| 919 | } |
| 920 | } |
| 921 | $ops = $sel['ops']; |
| 922 | $order = $sel['order']; |
| 923 | $plan = new \stdClass(); |
| 924 | $plan->cols = $first->cols; |
| 925 | $plan->types = $first->types; |
| 926 | $plan->affs = $first->affs; |
| 927 | $plan->colls = $first->colls; |
| 928 | $plan->tables = $first->tables; |
| 929 | $plan->correlated = $correlated; |
| 930 | $plan->run = function(int $hint) use ($cores, $single, $ops, $order, $orderIdx, $limitF, $offsetF): array { |
| 931 | $limit = $limitF ? Value::toInt($limitF([])) : -1; |
| 932 | $offset = $offsetF ? max(0, (int)Value::toInt($offsetF([]))) : 0; |
| 933 | if ($limit === null) $limit = -1; |
| 934 | if ($hint >= 0 && ($limit < 0 || $hint < $limit)) $limit = $hint; |
| 935 | if ($single) { |
| 936 | $rows = ($cores[0]->run)(empty($order) && $limit >= 0 ? $limit + $offset : -1); |
| 937 | if ($order) { |
| 938 | usort($rows, fn($a, $b) => self::compareSort($a[1], $b[1], $cores[0]->sort)); |
| 939 | } |
| 940 | $rows = array_column($rows, 0); |
| 941 | } else { |
| 942 | $rows = array_column(($cores[0]->run)(-1), 0); |
| 943 | foreach ($ops as $i => $op) { |
| 944 | $next = array_column(($cores[$i + 1]->run)(-1), 0); |
| 945 | $rows = self::compound($op, $rows, $next); |
| 946 | } |
| 947 | if ($orderIdx) { |
| 948 | $spec = []; |
| 949 | foreach ($orderIdx as [$k, $desc, $nulls, $coll]) $spec[] = [$desc, $nulls, $coll]; |
| 950 | usort($rows, function($a, $b) use ($orderIdx, $spec) { |
| 951 | $ka = $kb = []; |
| 952 | foreach ($orderIdx as [$k]) { |
| 953 | $ka[] = $a[$k]; |
| 954 | $kb[] = $b[$k]; |
| 955 | } |
| 956 | return self::compareSort($ka, $kb, $spec); |
| 957 | }); |
| 958 | } |
| 959 | } |
| 960 | if ($offset > 0 || $limit >= 0) $rows = array_slice($rows, $offset, $limit >= 0 ? $limit : null); |
| 961 | return $rows; |
| 962 | }; |
| 963 | return $plan; |
| 964 | } finally { |
| 965 | if ($pushed) array_pop($this->cte); |
| 966 | } |
| 967 | } |
| 968 | |
| 969 | private static function compound(string $op, array $a, array $b): array { |
| 970 | $key = fn(array $row) => implode("\x1F", array_map(fn($v) => Value::key($v), $row)); |
| 971 | switch ($op) { |
| 972 | case 'UNION ALL': |
| 973 | return array_merge($a, $b); |
| 974 | case 'UNION': |
| 975 | $out = []; |
| 976 | foreach (array_merge($a, $b) as $row) $out[$key($row)] ??= $row; |
| 977 | return array_values($out); |
| 978 | case 'INTERSECT': |
| 979 | $in = []; |
| 980 | foreach ($b as $row) $in[$key($row)] = true; |
| 981 | $out = []; |
| 982 | foreach ($a as $row) if (isset($in[$k = $key($row)])) $out[$k] ??= $row; |
| 983 | return array_values($out); |
| 984 | default: |
| 985 | $in = []; |
| 986 | foreach ($b as $row) $in[$key($row)] = true; |
| 987 | $out = []; |
| 988 | foreach ($a as $row) if (!isset($in[$k = $key($row)])) $out[$k] ??= $row; |
| 989 | return array_values($out); |
| 990 | } |
| 991 | } |
| 992 | |
| 993 | /** ORDER BYの比較 */ |
| 994 | private static function compareSort(array $a, array $b, array $spec): int { |
| 995 | foreach ($spec as $i => [$desc, $nulls, $coll]) { |
| 996 | $x = $a[$i]; |
| 997 | $y = $b[$i]; |
| 998 | if ($x === null || $y === null) { |
| 999 | if ($x === $y) continue; |
| 1000 | $nullFirst = $nulls === null ? !$desc : $nulls === 'FIRST'; |
| 1001 | return ($x === null) === $nullFirst ? -1 : 1; |
| 1002 | } |
| 1003 | $c = Record::compare($x, $y, $coll); |
| 1004 | if ($c !== 0) return $desc ? -$c : $c; |
| 1005 | } |
| 1006 | return 0; |
| 1007 | } |
| 1008 | |
| 1009 | /** ORDER BYの式が結果の何番目の列か（番号、別名、同じ式） */ |
| 1010 | private function outputIndex(array $e, \stdClass $core): ?int { |
| 1011 | if ($e['t'] === 'collate') $e = $e['e']; |
| 1012 | if ($e['t'] === 'lit' && is_int($e['v'])) { |
| 1013 | if ($e['v'] < 1 || $e['v'] > count($core->cols)) throw new DbException("ORDER BYの番号が範囲外です：{$e['v']}", 1, 'HY000'); |
| 1014 | return $e['v'] - 1; |
| 1015 | } |
| 1016 | if ($e['t'] === 'col' && $e['tbl'] === null) { |
| 1017 | foreach ($core->aliases as $k => $a) if ($a !== null && strcasecmp($a, $e['name']) === 0) return $k; |
| 1018 | foreach ($core->cols as $k => $n) if (strcasecmp($n, $e['name']) === 0) return $k; |
| 1019 | } |
| 1020 | return null; |
| 1021 | } |
| 1022 | |
| 1023 | /** FROMのテーブル名から、ソース（テーブル・ビュー・WITH） */ |
| 1024 | private function sourceFor(string $name, ?string $alias, ?\stdClass $parent): array { |
| 1025 | $l = strtolower($name); |
| 1026 | for ($i = count($this->cte) - 1; $i >= 0; $i--) { |
| 1027 | if (isset($this->cte[$i][$l])) { |
| 1028 | $c = $this->cte[$i][$l]; |
| 1029 | // 自分自身を参照（再帰）は未対応 |
| 1030 | $saved = $this->cte; |
| 1031 | $this->cte = array_slice($this->cte, 0, $i); |
| 1032 | $level = $saved[$i]; |
| 1033 | unset($level[$l]); |
| 1034 | $this->cte[] = $level; |
| 1035 | try { |
| 1036 | $plan = $this->planSelect($c['sel'], null); |
| 1037 | } catch (DbException $e) { |
| 1038 | if ($c['recursive'] && str_contains($e->getMessage(), $name)) throw new DbException('再帰のWITHは未対応です', 1, 'HY000'); |
| 1039 | throw $e; |
| 1040 | } finally { |
| 1041 | $this->cte = $saved; |
| 1042 | } |
| 1043 | return $this->planSource($plan, $alias ?? $c['name'], $c['cols']); |
| 1044 | } |
| 1045 | } |
| 1046 | $schema = $this->schema(); |
| 1047 | if (isset($schema->views[$l])) { |
| 1048 | $v = $schema->views[$l]; |
| 1049 | $saved = $this->cte; |
| 1050 | $this->cte = []; |
| 1051 | try { |
| 1052 | $plan = $this->planSelect($v['sel'], null); |
| 1053 | } finally { |
| 1054 | $this->cte = $saved; |
| 1055 | } |
| 1056 | return $this->planSource($plan, $alias ?? $v['name'], $v['cols']); |
| 1057 | } |
| 1058 | $t = $schema->table_($name); |
| 1059 | if ($t === null) throw new DbException("テーブルがありません：{$name}", 1, 'HY000'); |
| 1060 | if (isset($t['unsupported'])) throw new DbException($t['unsupported'], 1, 'HY000'); |
| 1061 | $names = array_column($t['cols'], 'name'); |
| 1062 | return ['alias' => $alias ?? $t['name'], 'name' => $t['name'], 'cols' => $names, 'types' => array_column($t['cols'], 'type'), |
| 1063 | 'affs' => array_column($t['cols'], 'aff'), 'colls' => array_column($t['cols'], 'collate'), 'table' => $t]; |
| 1064 | } |
| 1065 | |
| 1066 | private function planSource(\stdClass $plan, string $alias, ?array $cols): array { |
| 1067 | $names = $cols ?? $plan->cols; |
| 1068 | if (count($names) !== count($plan->cols)) throw new DbException("{$alias}の列の数が違います", 1, 'HY000'); |
| 1069 | return ['alias' => $alias, 'name' => $alias, 'cols' => $names, 'types' => $plan->types, 'affs' => $plan->affs, |
| 1070 | 'colls' => $plan->colls, 'table' => null, 'plan' => $plan]; |
| 1071 | } |
| 1072 | |
| 1073 | /** |
| 1074 | * SELECTの1つ（FROM・WHERE・GROUP BY・結果の列） |
| 1075 | * |
| 1076 | * @return \stdClass cols、aliases、types、affs、colls、tables、sort、correlated、run（fn(int $limit): array [値, ソートのキー]の配列） |
| 1077 | */ |
| 1078 | private function planCore(array $core, ?\stdClass $parent, array $order): \stdClass { |
| 1079 | if (isset($core['sub'])) { |
| 1080 | $p = $this->planSelect($core['sub'], $parent); |
| 1081 | $c = new \stdClass(); |
| 1082 | foreach (['cols', 'types', 'affs', 'colls', 'tables', 'correlated'] as $k) $c->$k = $p->$k; |
| 1083 | $c->aliases = array_fill(0, count($p->cols), null); |
| 1084 | $c->sort = []; |
| 1085 | $c->run = fn(int $hint) => array_map(fn($r) => [$r, []], ($p->run)($hint)); |
| 1086 | return $c; |
| 1087 | } |
| 1088 | if (isset($core['values'])) return $this->planValues($core['values'], $parent, $order); |
| 1089 | |
| 1090 | // FROM |
| 1091 | $srcs = []; |
| 1092 | foreach ($core['from'] as $item) { |
| 1093 | if (isset($item['sel'])) { |
| 1094 | $p = $this->planSelect($item['sel'], null); |
| 1095 | $srcs[] = $this->planSource($p, $item['as'] ?? '(subquery-'.count($srcs).')', null); |
| 1096 | } else { |
| 1097 | $srcs[] = $this->sourceFor($item['table'], $item['as'], $parent); |
| 1098 | } |
| 1099 | } |
| 1100 | // USING・NATURAL：右の列を隠して、条件にする |
| 1101 | $joinOn = []; |
| 1102 | foreach ($core['from'] as $j => $item) { |
| 1103 | $on = $item['on'] ?? null; |
| 1104 | $using = $item['using'] ?? null; |
| 1105 | if ($j > 0 && ($item['natural'] || $using)) { |
| 1106 | if ($item['natural']) { |
| 1107 | $using = []; |
| 1108 | $right = array_map('strtolower', $srcs[$j]['cols']); |
| 1109 | for ($k = 0; $k < $j; $k++) { |
| 1110 | foreach ($srcs[$k]['cols'] as $n) if (in_array(strtolower($n), $right, true) && !in_array(strtolower($n), array_map('strtolower', $using), true)) $using[] = $n; |
| 1111 | } |
| 1112 | } |
| 1113 | foreach ($using as $n) { |
| 1114 | $ln = strtolower($n); |
| 1115 | $leftSrc = null; |
| 1116 | for ($k = 0; $k < $j; $k++) if (in_array($ln, array_map('strtolower', $srcs[$k]['cols']), true) && !isset($srcs[$k]['hidden'][$ln])) $leftSrc = $k; |
| 1117 | if ($leftSrc === null || !in_array($ln, array_map('strtolower', $srcs[$j]['cols']), true)) throw new DbException("USINGの列がありません：{$n}", 1, 'HY000'); |
| 1118 | $cond = ['t' => 'bin', 'op' => '=', 'l' => ['t' => 'col', 'tbl' => $srcs[$leftSrc]['alias'], 'name' => $n], |
| 1119 | 'r' => ['t' => 'col', 'tbl' => $srcs[$j]['alias'], 'name' => $n]]; |
| 1120 | $on = $on ? ['t' => 'bin', 'op' => 'AND', 'l' => $on, 'r' => $cond] : $cond; |
| 1121 | $srcs[$j]['hidden'][$ln] = true; |
| 1122 | } |
| 1123 | } |
| 1124 | $joinOn[$j] = $on; |
| 1125 | } |
| 1126 | $sc = $this->scope($srcs, $parent); |
| 1127 | |
| 1128 | // 結果の列の別名（WHERE・GROUP BY・HAVINGでも使える、SQLiteの拡張） |
| 1129 | $aliasMap = []; |
| 1130 | foreach ($core['cols'] as $c) { |
| 1131 | if (isset($c['e']) && $c['as'] !== null) $aliasMap[strtolower($c['as'])] ??= $c['e']; |
| 1132 | } |
| 1133 | if ($aliasMap) { |
| 1134 | if ($core['where']) $core['where'] = $this->substAliases($core['where'], $aliasMap, $sc0 = $this->scope($srcs, $parent)); |
| 1135 | if ($core['having']) $core['having'] = $this->substAliases($core['having'], $aliasMap, $sc0 ??= $this->scope($srcs, $parent)); |
| 1136 | foreach ($core['group'] as $k => $g) $core['group'][$k] = $this->substAliases($g, $aliasMap, $sc0 ??= $this->scope($srcs, $parent)); |
| 1137 | } |
| 1138 | |
| 1139 | // 条件の場所 |
| 1140 | $n = count($srcs); |
| 1141 | $where = self::conjuncts($core['where']); |
| 1142 | $levelOn = array_fill(0, max(1, $n), []); |
| 1143 | $levelPost = array_fill(0, max(1, $n), []); |
| 1144 | $levelCand = array_fill(0, max(1, $n), []); |
| 1145 | foreach ($core['from'] as $j => $item) { |
| 1146 | if ($joinOn[$j] === null) continue; |
| 1147 | if ($item['join'] === 'left') { |
| 1148 | foreach (self::conjuncts($joinOn[$j]) as $c) { |
| 1149 | $levelOn[$j][] = $c; |
| 1150 | $levelCand[$j][] = $c; |
| 1151 | } |
| 1152 | } else { |
| 1153 | $where = array_merge($where, self::conjuncts($joinOn[$j])); |
| 1154 | } |
| 1155 | } |
| 1156 | foreach ($where as $c) { |
| 1157 | if (self::hasAggregate($c)) throw new DbException('WHEREに集約関数は使えません', 1, 'HY000'); |
| 1158 | $r = $this->refs($c, $sc); |
| 1159 | $lvl = $r === null ? max(0, $n - 1) : (empty($r) ? 0 : max($r)); |
| 1160 | $levelPost[$lvl][] = $c; |
| 1161 | // LEFT JOINの右のテーブルの条件は検索に使わない |
| 1162 | if ($r !== null && ($core['from'][$lvl]['join'] ?? 'inner') !== 'left') $levelCand[$lvl][] = $c; |
| 1163 | } |
| 1164 | $levels = []; |
| 1165 | for ($j = 0; $j < $n; $j++) { |
| 1166 | $levels[] = [ |
| 1167 | 'src' => $this->access($srcs[$j], $j, $levelCand[$j], $sc), |
| 1168 | 'on' => array_map(fn($c) => $this->compile($c, $sc), $levelOn[$j]), |
| 1169 | 'post' => array_map(fn($c) => $this->compile($c, $sc), $levelPost[$j]), |
| 1170 | 'left' => ($core['from'][$j]['join'] ?? 'inner') === 'left', |
| 1171 | 'nulls' => array_fill(0, count($srcs[$j]['cols']) + 1, null), |
| 1172 | ]; |
| 1173 | } |
| 1174 | $constFilters = $n === 0 ? array_map(fn($c) => $this->compile($c, $sc), $levelPost[0]) : []; |
| 1175 | |
| 1176 | // 結果の列 |
| 1177 | $isAgg = !empty($core['group']) || self::hasAggregate($core['having']); |
| 1178 | foreach ($core['cols'] as $c) if (isset($c['e']) && self::hasAggregate($c['e'])) $isAgg = true; |
| 1179 | foreach ($order as $o) if (self::hasAggregate($o['e'])) $isAgg = true; |
| 1180 | $agg = null; |
| 1181 | if ($isAgg) { |
| 1182 | $agg = new \stdClass(); |
| 1183 | $agg->list = []; |
| 1184 | $agg->base = $sc->width; |
| 1185 | $agg->inAgg = false; |
| 1186 | } |
| 1187 | $outFns = $cols = $aliases = $types = $affs = $colls = $tables = []; |
| 1188 | foreach ($core['cols'] as $c) { |
| 1189 | if (array_key_exists('star', $c)) { |
| 1190 | $found = false; |
| 1191 | foreach ($sc->srcs as $j => $s) { |
| 1192 | if ($c['star'] !== null && strcasecmp($c['star'], (string)$s['alias']) !== 0) continue; |
| 1193 | $found = true; |
| 1194 | foreach ($s['cols'] as $i => $name) { |
| 1195 | if ($c['star'] === null && isset($s['hidden'][strtolower($name)])) continue; |
| 1196 | $o = $s['base'] + $i; |
| 1197 | $outFns[] = fn(array $r) => $r[$o] ?? null; |
| 1198 | $cols[] = $name; |
| 1199 | $aliases[] = null; |
| 1200 | $types[] = $s['types'][$i] ?? ''; |
| 1201 | $affs[] = $s['affs'][$i] ?? ''; |
| 1202 | $colls[] = $s['colls'][$i] ?? 'BINARY'; |
| 1203 | $tables[] = $s['table']['name'] ?? ''; |
| 1204 | } |
| 1205 | } |
| 1206 | if (!$found) throw new DbException($c['star'] === null ? 'テーブルが指定されていません' : "テーブルがありません：{$c['star']}", 1, 'HY000'); |
| 1207 | continue; |
| 1208 | } |
| 1209 | $outFns[] = $this->compile($c['e'], $sc, $agg); |
| 1210 | $name = $c['as']; |
| 1211 | $info = $c['e']['t'] === 'col' ? $this->resolve($sc, $c['e']['tbl'], $c['e']['name']) : null; |
| 1212 | if ($name === null) $name = $info !== null ? $info['name'] : $c['text']; |
| 1213 | $cols[] = $name; |
| 1214 | $aliases[] = $c['as']; |
| 1215 | $types[] = $info['type'] ?? ($c['e']['t'] === 'cast' ? $c['e']['type'] : ''); |
| 1216 | $affs[] = $this->exprAff($c['e'], $sc); |
| 1217 | $colls[] = $this->exprColl($c['e'], $sc) ?? 'BINARY'; |
| 1218 | $tables[] = $info !== null && $info['level'] === $sc->level ? (string)($sc->srcs[$info['src']]['table']['name'] ?? '') : ''; |
| 1219 | } |
| 1220 | $groupFns = array_map(fn($g) => [$this->compileGroupTerm($g, $core, $sc, $cols, $aliases, $outFns), $this->exprColl($g, $sc) ?? 'BINARY'], $core['group']); |
| 1221 | $having = $core['having'] ? $this->compile($core['having'], $sc, $agg) : null; |
| 1222 | if ($having && !$isAgg) throw new DbException('HAVINGはGROUP BYと一緒に使います', 1, 'HY000'); |
| 1223 | |
| 1224 | // ORDER BY（単一のSELECT） |
| 1225 | $sortFns = []; |
| 1226 | $sort = []; |
| 1227 | $tmp = new \stdClass(); |
| 1228 | $tmp->cols = $cols; |
| 1229 | $tmp->aliases = $aliases; |
| 1230 | foreach ($order as $o) { |
| 1231 | $k = $this->outputIndex($o['e'], $tmp); |
| 1232 | if ($k !== null) { |
| 1233 | $sortFns[] = ['out', $k]; |
| 1234 | $coll = $o['e']['t'] === 'collate' ? $o['e']['coll'] : $colls[$k]; |
| 1235 | } else { |
| 1236 | $sortFns[] = ['expr', $this->compile($o['e'], $sc, $agg)]; |
| 1237 | $coll = $this->exprColl($o['e'], $sc) ?? 'BINARY'; |
| 1238 | } |
| 1239 | $sort[] = [$o['desc'], $o['nulls'], $coll]; |
| 1240 | } |
| 1241 | |
| 1242 | $distinct = $core['distinct']; |
| 1243 | $aggList = $agg ? $agg->list : []; |
| 1244 | $hasGroup = !empty($core['group']); |
| 1245 | $width = $sc->width; |
| 1246 | $out = new \stdClass(); |
| 1247 | $out->cols = $cols; |
| 1248 | $out->aliases = $aliases; |
| 1249 | $out->types = $types; |
| 1250 | $out->affs = $affs; |
| 1251 | $out->colls = $colls; |
| 1252 | $out->tables = $tables; |
| 1253 | $out->sort = $sort; |
| 1254 | $out->correlated = $sc->usedOuter; |
| 1255 | $out->run = function(int $hint) use ($levels, $n, $constFilters, $outFns, $sortFns, $distinct, $isAgg, $aggList, $hasGroup, $groupFns, $having, $width): array { |
| 1256 | $emit = function(array $r) use ($outFns, $sortFns): array { |
| 1257 | $vals = []; |
| 1258 | foreach ($outFns as $f) $vals[] = $f($r); |
| 1259 | $keys = []; |
| 1260 | foreach ($sortFns as [$kind, $x]) $keys[] = $kind === 'out' ? $vals[$x] : $x($r); |
| 1261 | return [$vals, $keys]; |
| 1262 | }; |
| 1263 | $rows = []; |
| 1264 | $seen = []; |
| 1265 | if ($n === 0) { |
| 1266 | foreach ($constFilters as $f) if (!Value::truth($f([]))) return $isAgg && !$hasGroup ? $this->aggregateRows([], $aggList, $hasGroup, $groupFns, $having, $width, $emit, $distinct) : []; |
| 1267 | $gen = (function() { yield []; })(); |
| 1268 | } else { |
| 1269 | $gen = $this->joinLoop($levels, 0, []); |
| 1270 | } |
| 1271 | if ($isAgg) return $this->aggregateRows($gen, $aggList, $hasGroup, $groupFns, $having, $width, $emit, $distinct); |
| 1272 | foreach ($gen as $r) { |
| 1273 | $row = $emit($r); |
| 1274 | if ($distinct) { |
| 1275 | $k = implode("\x1F", array_map(fn($v) => Value::key($v), $row[0])); |
| 1276 | if (isset($seen[$k])) continue; |
| 1277 | $seen[$k] = true; |
| 1278 | } |
| 1279 | $rows[] = $row; |
| 1280 | if ($hint >= 0 && count($rows) >= $hint) break; |
| 1281 | } |
| 1282 | return $rows; |
| 1283 | }; |
| 1284 | return $out; |
| 1285 | } |
| 1286 | |
| 1287 | /** 列が見つからない名前を結果の列の別名の式にする */ |
| 1288 | private function substAliases(array $e, array $map, \stdClass $sc): array { |
| 1289 | if ($e['t'] === 'col') { |
| 1290 | if ($e['tbl'] === null && isset($map[strtolower($e['name'])]) && $this->resolve($sc, null, $e['name']) === null) return $map[strtolower($e['name'])]; |
| 1291 | return $e; |
| 1292 | } |
| 1293 | if (in_array($e['t'], ['sub', 'exists'], true)) return $e; |
| 1294 | foreach ($e as $k => $v) { |
| 1295 | if (!is_array($v) || $k === 'sel') continue; |
| 1296 | if (isset($v['t'])) { |
| 1297 | $e[$k] = $this->substAliases($v, $map, $sc); |
| 1298 | } else { |
| 1299 | foreach ($v as $i => $x) { |
| 1300 | if (is_array($x) && isset($x['t'])) $e[$k][$i] = $this->substAliases($x, $map, $sc); |
| 1301 | 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); |
| 1302 | } |
| 1303 | } |
| 1304 | } |
| 1305 | return $e; |
| 1306 | } |
| 1307 | |
| 1308 | /** GROUP BYの項（番号・別名は結果の列） */ |
| 1309 | private function compileGroupTerm(array $g, array $core, \stdClass $sc, array $cols, array $aliases, array $outFns): \Closure { |
| 1310 | if ($g['t'] === 'lit' && is_int($g['v'])) { |
| 1311 | if ($g['v'] < 1 || $g['v'] > count($cols)) throw new DbException("GROUP BYの番号が範囲外です：{$g['v']}", 1, 'HY000'); |
| 1312 | $k = $g['v'] - 1; |
| 1313 | $e = $core['cols'][$k]['e'] ?? null; |
| 1314 | if ($e === null) throw new DbException('GROUP BYの番号が「*」です', 1, 'HY000'); |
| 1315 | return $this->compile($e, $sc); |
| 1316 | } |
| 1317 | if ($g['t'] === 'col' && $g['tbl'] === null && $this->resolve($sc, null, $g['name']) === null) { |
| 1318 | foreach ($aliases as $k => $a) { |
| 1319 | if ($a !== null && strcasecmp($a, $g['name']) === 0) return $this->compile($core['cols'][$k]['e'], $sc); |
| 1320 | } |
| 1321 | } |
| 1322 | if (self::hasAggregate($g)) throw new DbException('GROUP BYに集約関数は使えません', 1, 'HY000'); |
| 1323 | return $this->compile($g, $sc); |
| 1324 | } |
| 1325 | |
| 1326 | /** 集約（グループ毎） */ |
| 1327 | private function aggregateRows(iterable $gen, array $aggList, bool $hasGroup, array $groupFns, ?\Closure $having, int $width, \Closure $emit, bool $distinct): array { |
| 1328 | $groups = []; |
| 1329 | // min()・max()が1つだけなら、他の列はその行の値（SQLiteと同じ） |
| 1330 | $minmax = null; |
| 1331 | if (count($aggList) === 1 && in_array($aggList[0]['name'], ['min', 'max'], true)) $minmax = 0; |
| 1332 | foreach ($gen as $r) { |
| 1333 | $gk = ''; |
| 1334 | $gv = []; |
| 1335 | foreach ($groupFns as [$f, $coll]) { |
| 1336 | $v = $f($r); |
| 1337 | $gv[] = $v; |
| 1338 | $gk .= Value::key($v, $coll)."\x1F"; |
| 1339 | } |
| 1340 | if (!isset($groups[$gk])) { |
| 1341 | $st = []; |
| 1342 | foreach ($aggList as $a) $st[] = Func::aggInit($a['name']); |
| 1343 | $groups[$gk] = ['row' => $r, 'st' => $st, 'seen' => [], 'key' => $gv]; |
| 1344 | } |
| 1345 | $g = &$groups[$gk]; |
| 1346 | if ($minmax !== null) $before = $g['st'][0]['v']; |
| 1347 | foreach ($aggList as $k => $a) { |
| 1348 | if ($a['filter'] && !Value::truth(($a['filter'])($r))) continue; |
| 1349 | $args = []; |
| 1350 | foreach ($a['args'] as $f) $args[] = $f($r); |
| 1351 | if ($a['distinct']) { |
| 1352 | if (($args[0] ?? null) === null) continue; |
| 1353 | $dk = Value::key($args[0], $a['coll']); |
| 1354 | if (isset($g['seen'][$k][$dk])) continue; |
| 1355 | $g['seen'][$k][$dk] = true; |
| 1356 | } |
| 1357 | Func::aggStep($a['name'], $g['st'][$k], $args, $a['star'], $a['coll']); |
| 1358 | } |
| 1359 | if ($minmax !== null && $g['st'][0]['v'] !== $before) $g['row'] = $r; |
| 1360 | unset($g); |
| 1361 | } |
| 1362 | // GROUP BYはキーの順番（SQLiteと同じ） |
| 1363 | if ($hasGroup && count($groups) > 1) { |
| 1364 | $spec = array_map(fn($x) => [false, null, $x[1]], $groupFns); |
| 1365 | uasort($groups, fn($a, $b) => self::compareSort($a['key'], $b['key'], $spec)); |
| 1366 | } |
| 1367 | if (!$hasGroup && empty($groups)) { |
| 1368 | $st = []; |
| 1369 | foreach ($aggList as $a) $st[] = Func::aggInit($a['name']); |
| 1370 | $groups[''] = ['row' => array_fill(0, $width, null), 'st' => $st]; |
| 1371 | } |
| 1372 | $rows = []; |
| 1373 | $seen = []; |
| 1374 | foreach ($groups as $g) { |
| 1375 | $er = $g['row']; |
| 1376 | foreach ($aggList as $k => $a) $er[$width + $k] = Func::aggFinal($a['name'], $g['st'][$k]); |
| 1377 | if ($having && !Value::truth($having($er))) continue; |
| 1378 | $row = $emit($er); |
| 1379 | if ($distinct) { |
| 1380 | $key = implode("\x1F", array_map(fn($v) => Value::key($v), $row[0])); |
| 1381 | if (isset($seen[$key])) continue; |
| 1382 | $seen[$key] = true; |
| 1383 | } |
| 1384 | $rows[] = $row; |
| 1385 | } |
| 1386 | return $rows; |
| 1387 | } |
| 1388 | |
| 1389 | /** VALUES (…), (…) */ |
| 1390 | private function planValues(array $values, ?\stdClass $parent, array $order): \stdClass { |
| 1391 | $sc = $this->scope([], $parent); |
| 1392 | $rows = []; |
| 1393 | $nc = count($values[0]); |
| 1394 | foreach ($values as $row) { |
| 1395 | if (count($row) !== $nc) throw new DbException('VALUESの行の値の数が違います', 1, 'HY000'); |
| 1396 | $rows[] = array_map(fn($e) => $this->compile($e, $sc), $row); |
| 1397 | } |
| 1398 | $out = new \stdClass(); |
| 1399 | $out->cols = array_map(fn($i) => 'column'.($i + 1), range(0, $nc - 1)); |
| 1400 | $out->aliases = array_fill(0, $nc, null); |
| 1401 | $out->types = array_fill(0, $nc, ''); |
| 1402 | $out->affs = array_fill(0, $nc, ''); |
| 1403 | $out->colls = array_fill(0, $nc, 'BINARY'); |
| 1404 | $out->tables = array_fill(0, $nc, ''); |
| 1405 | $out->correlated = $sc->usedOuter; |
| 1406 | $sortIdx = []; |
| 1407 | foreach ($order as $o) { |
| 1408 | $k = $this->outputIndex($o['e'], $out); |
| 1409 | if ($k === null) throw new DbException('VALUESのORDER BYは列の番号です', 1, 'HY000'); |
| 1410 | $sortIdx[] = $k; |
| 1411 | $out->sort[] = [$o['desc'], $o['nulls'], 'BINARY']; |
| 1412 | } |
| 1413 | $out->sort ??= []; |
| 1414 | $out->run = function(int $hint) use ($rows, $sortIdx): array { |
| 1415 | $res = []; |
| 1416 | foreach ($rows as $fns) { |
| 1417 | $vals = array_map(fn($f) => $f([]), $fns); |
| 1418 | $res[] = [$vals, array_map(fn($k) => $vals[$k], $sortIdx)]; |
| 1419 | } |
| 1420 | return $res; |
| 1421 | }; |
| 1422 | return $out; |
| 1423 | } |
| 1424 | |
| 1425 | /** 入れ子のループ（LEFT JOINはNULLの行も） */ |
| 1426 | private function joinLoop(array $levels, int $j, array $row): \Generator { |
| 1427 | if ($j === count($levels)) { |
| 1428 | yield $row; |
| 1429 | return; |
| 1430 | } |
| 1431 | $L = $levels[$j]; |
| 1432 | $matched = false; |
| 1433 | foreach (($L['src'])($row) as $vals) { |
| 1434 | $r = $j === 0 ? $vals : array_merge($row, $vals); |
| 1435 | foreach ($L['on'] as $f) if (!Value::truth($f($r))) continue 2; |
| 1436 | $matched = true; |
| 1437 | foreach ($L['post'] as $f) if (!Value::truth($f($r))) continue 2; |
| 1438 | yield from $this->joinLoop($levels, $j + 1, $r); |
| 1439 | } |
| 1440 | if (!$matched && $L['left']) { |
| 1441 | $r = array_merge($row, $L['nulls']); |
| 1442 | foreach ($L['post'] as $f) if (!Value::truth($f($r))) return; |
| 1443 | yield from $this->joinLoop($levels, $j + 1, $r); |
| 1444 | } |
| 1445 | } |
| 1446 | |
| 1447 | /////////////// |
| 1448 | // テーブルの読み方（rowid、インデックス、全部） |
| 1449 | |
| 1450 | /** テーブルの行の値（足りない列はDEFAULT、rowidの別名、最後にrowid） */ |
| 1451 | private function rowReader(array $t): \Closure { |
| 1452 | $nc = count($t['cols']); |
| 1453 | $ipk = $t['ipk'] ?? null; |
| 1454 | $defaults = []; |
| 1455 | foreach ($t['cols'] as $i => $c) { |
| 1456 | $defaults[$i] = $c['default'] !== null ? Value::apply($this->constValue($c['default']), $c['aff']) : null; |
| 1457 | } |
| 1458 | return function(int $rowid, string $rec) use ($nc, $ipk, $defaults): array { |
| 1459 | $v = Record::decode($rec); |
| 1460 | $k = count($v); |
| 1461 | if ($k < $nc) { |
| 1462 | for ($i = $k; $i < $nc; $i++) $v[$i] = $defaults[$i]; |
| 1463 | } else if ($k > $nc) { |
| 1464 | $v = array_slice($v, 0, $nc); |
| 1465 | } |
| 1466 | if ($ipk !== null) $v[$ipk] = $rowid; |
| 1467 | $v[] = $rowid; |
| 1468 | return $v; |
| 1469 | }; |
| 1470 | } |
| 1471 | |
| 1472 | /** 定数の式の値（DEFAULT） */ |
| 1473 | private function constValue(array $e): mixed { |
| 1474 | return ($this->compile($e, $this->scope([], null)))([]); |
| 1475 | } |
| 1476 | |
| 1477 | /** |
| 1478 | * ソースの行を出すクロージャ（条件からrowid・インデックスを選ぶ） |
| 1479 | * |
| 1480 | * @return \Closure fn(array $row): iterable 値の配列 |
| 1481 | */ |
| 1482 | private function access(array $src, int $j, array $cands, \stdClass $sc): \Closure { |
| 1483 | if (isset($src['plan'])) { |
| 1484 | $plan = $src['plan']; |
| 1485 | $cache = null; |
| 1486 | return function(array $row) use ($plan, &$cache) { |
| 1487 | if ($cache === null) { |
| 1488 | $cache = []; |
| 1489 | foreach (($plan->run)(-1) as $vals) { |
| 1490 | $vals[] = null; |
| 1491 | $cache[] = $vals; |
| 1492 | } |
| 1493 | } |
| 1494 | return $cache; |
| 1495 | }; |
| 1496 | } |
| 1497 | $t = $src['table']; |
| 1498 | $root = $t['root']; |
| 1499 | $read = $this->rowReader($t); |
| 1500 | $this->lastAccess = null; |
| 1501 | $bt = $this->bt; |
| 1502 | $schema = $this->schema(); |
| 1503 | |
| 1504 | // 条件を調べる：このソースの列 演算子 他のソース・定数の式 |
| 1505 | $eq = []; |
| 1506 | $ranges = []; |
| 1507 | $ins = []; |
| 1508 | foreach ($cands as $c) { |
| 1509 | if ($c['t'] === 'bin' && in_array($c['op'], ['=', '<', '<=', '>', '>='], true)) { |
| 1510 | foreach ([[$c['l'], $c['r'], $c['op']], [$c['r'], $c['l'], self::flip($c['op'])]] as [$a, $b, $op]) { |
| 1511 | $col = $this->columnOf($a, $j, $sc); |
| 1512 | if ($col === null) continue; |
| 1513 | $r = $this->refs($b, $sc); |
| 1514 | if ($r === null || in_array($j, $r, true) || ($r && max($r) > $j)) continue; |
| 1515 | $info = ['fn' => $this->compile($b, $sc), 'aff' => $this->exprAff($b, $sc), 'coll' => $this->cmpColl($c['l'], $c['r'], $sc), 'op' => $op]; |
| 1516 | if ($op === '=') $eq[$col] ??= $info; |
| 1517 | else $ranges[$col][] = $info; |
| 1518 | break; |
| 1519 | } |
| 1520 | } else if ($c['t'] === 'in' && !$c['not'] && $c['list'] !== null && count($c['list']) > 0) { |
| 1521 | $col = $this->columnOf($c['e'], $j, $sc); |
| 1522 | if ($col === null) continue; |
| 1523 | $ok = true; |
| 1524 | $fns = []; |
| 1525 | foreach ($c['list'] as $x) { |
| 1526 | $r = $this->refs($x, $sc); |
| 1527 | if ($r === null || in_array($j, $r, true) || ($r && max($r) > $j)) { |
| 1528 | $ok = false; |
| 1529 | break; |
| 1530 | } |
| 1531 | $fns[] = [$this->compile($x, $sc), $this->exprAff($x, $sc), $this->cmpColl($c['e'], $x, $sc)]; |
| 1532 | } |
| 1533 | if ($ok) $ins[$col] ??= $fns; |
| 1534 | } else if ($c['t'] === 'between' && !$c['not']) { |
| 1535 | $col = $this->columnOf($c['e'], $j, $sc); |
| 1536 | if ($col === null) continue; |
| 1537 | $r1 = $this->refs($c['lo'], $sc); |
| 1538 | $r2 = $this->refs($c['hi'], $sc); |
| 1539 | if ($r1 === null || $r2 === null || in_array($j, array_merge($r1, $r2), true) || (($r1 || $r2) && max(array_merge($r1, $r2)) > $j)) continue; |
| 1540 | $ranges[$col][] = ['fn' => $this->compile($c['lo'], $sc), 'aff' => $this->exprAff($c['lo'], $sc), 'coll' => $this->cmpColl($c['e'], $c['lo'], $sc), 'op' => '>=']; |
| 1541 | $ranges[$col][] = ['fn' => $this->compile($c['hi'], $sc), 'aff' => $this->exprAff($c['hi'], $sc), 'coll' => $this->cmpColl($c['e'], $c['hi'], $sc), 'op' => '<=']; |
| 1542 | } |
| 1543 | } |
| 1544 | |
| 1545 | // rowid |
| 1546 | if (isset($eq['rowid'])) { |
| 1547 | $f = $eq['rowid']['fn']; |
| 1548 | $aff = $eq['rowid']['aff']; |
| 1549 | return function(array $row) use ($f, $aff, $bt, $root, $read) { |
| 1550 | $id = self::rowidKey($f($row), $aff); |
| 1551 | if ($id === null) return; |
| 1552 | $rec = $bt->get($root, $id); |
| 1553 | if ($rec !== null) yield $read($id, $rec); |
| 1554 | }; |
| 1555 | } |
| 1556 | if (isset($ins['rowid'])) { |
| 1557 | $fns = $ins['rowid']; |
| 1558 | return function(array $row) use ($fns, $bt, $root, $read) { |
| 1559 | $ids = []; |
| 1560 | foreach ($fns as [$f, $aff]) { |
| 1561 | $id = self::rowidKey($f($row), $aff); |
| 1562 | if ($id !== null) $ids[$id] = true; |
| 1563 | } |
| 1564 | $ids = array_keys($ids); |
| 1565 | sort($ids); |
| 1566 | foreach ($ids as $id) { |
| 1567 | $rec = $bt->get($root, $id); |
| 1568 | if ($rec !== null) yield $read($id, $rec); |
| 1569 | } |
| 1570 | }; |
| 1571 | } |
| 1572 | |
| 1573 | // インデックス（等号の列が一番多い物） |
| 1574 | $best = null; |
| 1575 | $bestN = 0; |
| 1576 | foreach ($t['indexes'] as $iname) { |
| 1577 | $idx = $schema->indexes[$iname]; |
| 1578 | $k = 0; |
| 1579 | foreach ($idx['cols'] as $p => $ci) { |
| 1580 | if (!isset($eq[$ci]) || $eq[$ci]['coll'] !== $idx['colls'][$p]) break; |
| 1581 | $k++; |
| 1582 | } |
| 1583 | $score = $k * 2 + ($idx['unique'] && $k === count($idx['cols']) ? 1 : 0); |
| 1584 | if ($k > 0 && $score > $bestN) { |
| 1585 | $best = $idx; |
| 1586 | $bestN = $score; |
| 1587 | } |
| 1588 | } |
| 1589 | if ($best !== null) { |
| 1590 | $this->lastAccess = $best['cols']; |
| 1591 | $parts = []; |
| 1592 | foreach ($best['cols'] as $p => $ci) { |
| 1593 | if (!isset($eq[$ci]) || $eq[$ci]['coll'] !== $best['colls'][$p]) break; |
| 1594 | $parts[] = [$eq[$ci]['fn'], $eq[$ci]['aff'], $t['cols'][$ci]['aff']]; |
| 1595 | } |
| 1596 | return function(array $row) use ($parts, $best, $bt, $root, $read) { |
| 1597 | $key = []; |
| 1598 | foreach ($parts as [$f, $aff, $colAff]) { |
| 1599 | $v = $f($row); |
| 1600 | if ($v === null) return; |
| 1601 | $key[] = self::lookupValue($v, $colAff, $aff); |
| 1602 | } |
| 1603 | $ids = []; |
| 1604 | foreach ($bt->indexScan($best, $key, true, $key, true) as $k) $ids[] = end($k); |
| 1605 | foreach ($ids as $id) { |
| 1606 | $rec = $bt->get($root, $id); |
| 1607 | if ($rec !== null) yield $read($id, $rec); |
| 1608 | } |
| 1609 | }; |
| 1610 | } |
| 1611 | // INのインデックス（最初の列） |
| 1612 | foreach ($ins as $ci => $fns) { |
| 1613 | if ($ci === 'rowid') continue; |
| 1614 | foreach ($t['indexes'] as $iname) { |
| 1615 | $idx = $schema->indexes[$iname]; |
| 1616 | if ($idx['cols'][0] !== $ci || $fns[0][2] !== $idx['colls'][0]) continue; |
| 1617 | $this->lastAccess = $idx['cols']; |
| 1618 | $colAff = $t['cols'][$ci]['aff']; |
| 1619 | return function(array $row) use ($fns, $idx, $bt, $root, $read, $colAff) { |
| 1620 | $done = []; |
| 1621 | foreach ($fns as [$f, $aff]) { |
| 1622 | $v = $f($row); |
| 1623 | if ($v === null) continue; |
| 1624 | $v = self::lookupValue($v, $colAff, $aff); |
| 1625 | $dk = Value::key($v); |
| 1626 | if (isset($done[$dk])) continue; |
| 1627 | $done[$dk] = true; |
| 1628 | $ids = []; |
| 1629 | foreach ($bt->indexScan($idx, [$v], true, [$v], true) as $k) $ids[] = end($k); |
| 1630 | foreach ($ids as $id) { |
| 1631 | $rec = $bt->get($root, $id); |
| 1632 | if ($rec !== null) yield $read($id, $rec); |
| 1633 | } |
| 1634 | } |
| 1635 | }; |
| 1636 | } |
| 1637 | } |
| 1638 | // rowidの範囲 |
| 1639 | if (isset($ranges['rowid'])) { |
| 1640 | $rs = $ranges['rowid']; |
| 1641 | return function(array $row) use ($rs, $bt, $root, $read) { |
| 1642 | $from = null; |
| 1643 | $to = null; |
| 1644 | foreach ($rs as $r) { |
| 1645 | $v = $r['fn']($row); |
| 1646 | if ($v === null) return; |
| 1647 | if (is_string($v)) $v = Value::apply($v, Value::NUMERIC); |
| 1648 | if (!is_int($v) && !is_float($v)) { |
| 1649 | // 文字列・BLOBは全部の数値より大きい |
| 1650 | if ($r['op'] === '>' || $r['op'] === '>=') return; |
| 1651 | continue; |
| 1652 | } |
| 1653 | [$lo, $hi] = self::rowidBounds($r['op'], $v); |
| 1654 | if ($lo !== null) $from = $from === null ? $lo : max($from, $lo); |
| 1655 | if ($hi !== null) $to = $to === null ? $hi : min($to, $hi); |
| 1656 | } |
| 1657 | if ($from !== null && $to !== null && $from > $to) return; |
| 1658 | foreach ($bt->scan($root, $from, $to) as $id => $rec) yield $read($id, $rec); |
| 1659 | }; |
| 1660 | } |
| 1661 | // インデックスの範囲（最初の列、昇順） |
| 1662 | foreach ($ranges as $ci => $rs) { |
| 1663 | if ($ci === 'rowid') continue; |
| 1664 | foreach ($t['indexes'] as $iname) { |
| 1665 | $idx = $schema->indexes[$iname]; |
| 1666 | if ($idx['cols'][0] !== $ci || $idx['desc'][0]) continue; |
| 1667 | $ok = true; |
| 1668 | foreach ($rs as $r) if ($r['coll'] !== $idx['colls'][0]) $ok = false; |
| 1669 | if (!$ok) continue; |
| 1670 | $this->lastAccess = $idx['cols']; |
| 1671 | $colAff = $t['cols'][$ci]['aff']; |
| 1672 | return function(array $row) use ($rs, $idx, $bt, $root, $read, $colAff) { |
| 1673 | $lo = [null]; |
| 1674 | $loInc = false; |
| 1675 | $hi = null; |
| 1676 | $hiInc = true; |
| 1677 | foreach ($rs as $r) { |
| 1678 | $v = $r['fn']($row); |
| 1679 | if ($v === null) return; |
| 1680 | $v = self::lookupValue($v, $colAff, $r['aff']); |
| 1681 | if ($r['op'] === '>' || $r['op'] === '>=') { |
| 1682 | $c = $lo[0] === null ? 1 : Record::compare($v, $lo[0], $idx['colls'][0]); |
| 1683 | if ($c > 0 || ($c === 0 && $r['op'] === '>')) { |
| 1684 | $lo = [$v]; |
| 1685 | $loInc = $r['op'] === '>='; |
| 1686 | } |
| 1687 | } else { |
| 1688 | $c = $hi === null ? -1 : Record::compare($v, $hi[0], $idx['colls'][0]); |
| 1689 | if ($c < 0 || ($c === 0 && $r['op'] === '<')) { |
| 1690 | $hi = [$v]; |
| 1691 | $hiInc = $r['op'] === '<='; |
| 1692 | } |
| 1693 | } |
| 1694 | } |
| 1695 | $ids = []; |
| 1696 | foreach ($bt->indexScan($idx, $lo, $loInc, $hi, $hiInc) as $k) $ids[] = end($k); |
| 1697 | foreach ($ids as $id) { |
| 1698 | $rec = $bt->get($root, $id); |
| 1699 | if ($rec !== null) yield $read($id, $rec); |
| 1700 | } |
| 1701 | }; |
| 1702 | } |
| 1703 | } |
| 1704 | // 全部 |
| 1705 | return function(array $row) use ($bt, $root, $read) { |
| 1706 | foreach ($bt->scan($root) as $id => $rec) yield $read($id, $rec); |
| 1707 | }; |
| 1708 | } |
| 1709 | |
| 1710 | private static function flip(string $op): string { |
| 1711 | return match ($op) { '<' => '>', '<=' => '>=', '>' => '<', '>=' => '<=', default => $op }; |
| 1712 | } |
| 1713 | |
| 1714 | /** 式がソース$jの列なら列の番号（rowidは'rowid'） */ |
| 1715 | private function columnOf(array $e, int $j, \stdClass $sc): int|string|null { |
| 1716 | if ($e['t'] !== 'col') return null; |
| 1717 | $c = $this->resolve($sc, $e['tbl'], $e['name']); |
| 1718 | if ($c === null || $c['level'] !== $sc->level || $c['src'] !== $j) return null; |
| 1719 | $src = $sc->srcs[$j]; |
| 1720 | if ($c['off'] === $src['rowid']) return 'rowid'; |
| 1721 | if (($src['table']['ipk'] ?? null) === $c['col']) return 'rowid'; |
| 1722 | return $c['col']; |
| 1723 | } |
| 1724 | |
| 1725 | /** rowidの検索の値（整数でなければnull） */ |
| 1726 | private static function rowidKey(mixed $v, string $aff): ?int { |
| 1727 | if ($v === null) return null; |
| 1728 | if (is_string($v)) $v = Value::apply($v, Value::NUMERIC); |
| 1729 | if (is_float($v) && floor($v) === $v && abs($v) < 9.2e18) $v = (int)$v; |
| 1730 | return is_int($v) ? $v : null; |
| 1731 | } |
| 1732 | |
| 1733 | private static function rowidBounds(string $op, int|float $v): array { |
| 1734 | if (is_float($v)) { |
| 1735 | if (is_nan($v)) return [PHP_INT_MAX, PHP_INT_MIN]; |
| 1736 | if ($v >= 9.2233720368547758e18) return $op === '>' || $op === '>=' ? [PHP_INT_MAX, null] : [null, PHP_INT_MAX]; |
| 1737 | if ($v <= -9.2233720368547758e18) return $op === '>' || $op === '>=' ? [PHP_INT_MIN, null] : [null, PHP_INT_MIN]; |
| 1738 | $fl = (int)floor($v); |
| 1739 | $ce = (int)ceil($v); |
| 1740 | return match ($op) { '>' => [$fl + 1, null], '>=' => [$ce, null], '<' => [null, $ce - 1], default => [null, $fl] }; |
| 1741 | } |
| 1742 | return match ($op) { |
| 1743 | '>' => [$v === PHP_INT_MAX ? PHP_INT_MAX : $v + 1, $v === PHP_INT_MAX ? PHP_INT_MIN : null], |
| 1744 | '>=' => [$v, null], |
| 1745 | '<' => [null, $v === PHP_INT_MIN ? PHP_INT_MIN : $v - 1], |
| 1746 | default => [null, $v], |
| 1747 | }; |
| 1748 | } |
| 1749 | |
| 1750 | /** インデックスで探す値（比較と同じ親和性） */ |
| 1751 | private static function lookupValue(mixed $v, string $colAff, string $exprAff): mixed { |
| 1752 | [$col, $x] = Value::prepareCompare(null, $colAff, $v, $exprAff); |
| 1753 | return $x; |
| 1754 | } |
| 1755 | |
| 1756 | /////////////// |
| 1757 | // INSERT・UPDATE・DELETE |
| 1758 | |
| 1759 | private function writableTable(string $name): array { |
| 1760 | $schema = $this->schema(); |
| 1761 | $l = strtolower($name); |
| 1762 | if (isset($schema->views[$l])) throw new DbException("ビューは変更できません：{$name}", 1, 'HY000'); |
| 1763 | $t = $schema->table_($name); |
| 1764 | if ($t === null) throw new DbException("テーブルがありません：{$name}", 1, 'HY000'); |
| 1765 | if (isset($t['unsupported'])) throw new DbException($t['unsupported'], 1, 'HY000'); |
| 1766 | if (!empty($t['system'])) throw new DbException("テーブル{$t['name']}は変更できません", 1, 'HY000'); |
| 1767 | if (isset($schema->triggers[strtolower($t['name'])])) throw new DbException("トリガーの有るテーブルの変更は未対応です：{$t['name']}", 1, 'HY000'); |
| 1768 | return $t; |
| 1769 | } |
| 1770 | |
| 1771 | /** テーブルのソース（スコープ用） */ |
| 1772 | private function tableSource(array $t, ?string $alias): array { |
| 1773 | return ['alias' => $alias ?? $t['name'], 'name' => $t['name'], 'cols' => array_column($t['cols'], 'name'), 'types' => array_column($t['cols'], 'type'), |
| 1774 | 'affs' => array_column($t['cols'], 'aff'), 'colls' => array_column($t['cols'], 'collate'), 'table' => $t]; |
| 1775 | } |
| 1776 | |
| 1777 | /** RETURNING */ |
| 1778 | private function returningPlan(?array $ret, array $t, ?string $alias): ?\stdClass { |
| 1779 | if ($ret === null) return null; |
| 1780 | $sc = $this->scope([$this->tableSource($t, $alias)], null); |
| 1781 | $p = new \stdClass(); |
| 1782 | $p->fns = $p->cols = $p->types = []; |
| 1783 | foreach ($ret as $c) { |
| 1784 | if (array_key_exists('star', $c)) { |
| 1785 | foreach ($t['cols'] as $i => $col) { |
| 1786 | $p->fns[] = fn(array $r) => $r[$i] ?? null; |
| 1787 | $p->cols[] = $col['name']; |
| 1788 | $p->types[] = $col['type']; |
| 1789 | } |
| 1790 | continue; |
| 1791 | } |
| 1792 | if (self::hasAggregate($c['e'])) throw new DbException('RETURNINGに集約関数は使えません', 1, 'HY000'); |
| 1793 | $p->fns[] = $this->compile($c['e'], $sc); |
| 1794 | $info = $c['e']['t'] === 'col' ? $this->resolve($sc, $c['e']['tbl'], $c['e']['name']) : null; |
| 1795 | $p->cols[] = $c['as'] ?? ($info['name'] ?? $c['text']); |
| 1796 | $p->types[] = $info['type'] ?? ''; |
| 1797 | } |
| 1798 | $p->rows = []; |
| 1799 | return $p; |
| 1800 | } |
| 1801 | |
| 1802 | private function checks(array $t): array { |
| 1803 | if (empty($t['checks'])) return []; |
| 1804 | $sc = $this->scope([$this->tableSource($t, null)], null); |
| 1805 | return array_map(fn($c) => $this->compile($c, $sc), $t['checks']); |
| 1806 | } |
| 1807 | |
| 1808 | private function insert(array $st): array { |
| 1809 | $t = $this->writableTable($st['table']); |
| 1810 | $nc = count($t['cols']); |
| 1811 | $targets = []; |
| 1812 | if ($st['cols'] !== null) { |
| 1813 | foreach ($st['cols'] as $n) { |
| 1814 | $ln = strtolower($n); |
| 1815 | $ci = $t['map'][$ln] ?? (in_array($ln, ['rowid', 'oid', '_rowid_'], true) ? 'rowid' : null); |
| 1816 | if ($ci === null) throw new DbException("テーブル{$t['name']}に列がありません：{$n}", 1, 'HY000'); |
| 1817 | $targets[] = $ci; |
| 1818 | } |
| 1819 | } else { |
| 1820 | $targets = array_keys($t['cols']); |
| 1821 | } |
| 1822 | // 値の行 |
| 1823 | $rows = []; |
| 1824 | if ($st['default']) { |
| 1825 | $rows[] = []; |
| 1826 | } else { |
| 1827 | $sel = $st['sel']; |
| 1828 | $simple = count($sel['cores']) === 1 && isset($sel['cores'][0]['values']) && !$sel['order'] && !$sel['limit'] && empty($sel['with']); |
| 1829 | if ($simple) { |
| 1830 | $sc = $this->scope([], null); |
| 1831 | foreach ($sel['cores'][0]['values'] as $vals) { |
| 1832 | if (count($vals) !== count($targets)) throw new DbException("{$t['name']}は".count($targets).'列ですが、'.count($vals).'個の値が有ります', 1, 'HY000'); |
| 1833 | $rows[] = array_map(fn($e) => ($this->compile($e, $sc))([]), $vals); |
| 1834 | } |
| 1835 | } else { |
| 1836 | $plan = $this->planSelect($sel, null); |
| 1837 | if (count($plan->cols) !== count($targets)) throw new DbException("{$t['name']}は".count($targets).'列ですが、'.count($plan->cols).'個の値が有ります', 1, 'HY000'); |
| 1838 | $rows = ($plan->run)(-1); |
| 1839 | } |
| 1840 | } |
| 1841 | $defaults = []; |
| 1842 | foreach ($t['cols'] as $i => $c) $defaults[$i] = $c['default'] !== null ? $this->compile($c['default'], $this->scope([], null)) : null; |
| 1843 | $checks = $this->checks($t); |
| 1844 | $ret = $this->returningPlan($st['returning'], $t, $st['alias']); |
| 1845 | $upsert = $this->upsertPlan($st, $t); |
| 1846 | $or = $st['or'] ?? 'ABORT'; |
| 1847 | |
| 1848 | foreach ($rows as $vals) { |
| 1849 | $row = array_fill(0, $nc, null); |
| 1850 | $given = array_fill(0, $nc, false); |
| 1851 | $rowid = null; |
| 1852 | foreach ($targets as $k => $ci) { |
| 1853 | if ($ci === 'rowid') { |
| 1854 | $rowid = $vals[$k]; |
| 1855 | continue; |
| 1856 | } |
| 1857 | $row[$ci] = $vals[$k]; |
| 1858 | $given[$ci] = true; |
| 1859 | } |
| 1860 | foreach ($t['cols'] as $i => $c) { |
| 1861 | if (!$given[$i] && $defaults[$i] !== null) $row[$i] = $defaults[$i]([]); |
| 1862 | } |
| 1863 | if ($t['ipk'] !== null && $row[$t['ipk']] !== null) $rowid = $row[$t['ipk']]; |
| 1864 | $res = $this->insertRow($t, $row, $rowid, $or, $checks, $upsert); |
| 1865 | if ($res !== null && $ret) { |
| 1866 | $r = $res; |
| 1867 | $ret->rows[] = array_map(fn($f) => $f($r), $ret->fns); |
| 1868 | } |
| 1869 | } |
| 1870 | if ($ret) return self::result($ret->cols, $ret->types, $ret->rows, $this->changes); |
| 1871 | return self::result([], [], [], $this->changes); |
| 1872 | } |
| 1873 | |
| 1874 | /** ON CONFLICT … DO UPDATE */ |
| 1875 | private function upsertPlan(array $st, array $t): array { |
| 1876 | $out = []; |
| 1877 | foreach ($st['upsert'] as $u) { |
| 1878 | $p = ['target' => null, 'nothing' => $u['nothing']]; |
| 1879 | if ($u['target'] !== null) { |
| 1880 | $p['target'] = array_map(fn($n) => $t['map'][strtolower($n)] ?? throw new DbException("列がありません：{$n}", 1, 'HY000'), $u['target']); |
| 1881 | sort($p['target']); |
| 1882 | $ok = $t['ipk'] !== null && $p['target'] === [$t['ipk']]; |
| 1883 | foreach ($t['indexes'] as $iname) { |
| 1884 | $idx = $this->schema()->indexes[$iname]; |
| 1885 | $cols = $idx['cols']; |
| 1886 | sort($cols); |
| 1887 | if ($idx['unique'] && $cols === $p['target']) $ok = true; |
| 1888 | } |
| 1889 | if (!$ok) throw new DbException('ON CONFLICTの列がPRIMARY KEY・UNIQUEの制約と合いません', 1, 'HY000'); |
| 1890 | } |
| 1891 | if (!$u['nothing']) { |
| 1892 | // excludedの列は「excluded.」を付けた場合だけ |
| 1893 | $ex = ['alias' => 'excluded', 'name' => 'excluded'] + array_slice($this->tableSource($t, null), 2); |
| 1894 | $ex['hidden'] = array_fill_keys(array_map('strtolower', $ex['cols']), true); |
| 1895 | $srcs = [$this->tableSource($t, $st['alias']), $ex]; |
| 1896 | $sc = $this->scope($srcs, null); |
| 1897 | $p['set'] = []; |
| 1898 | foreach ($u['set'] as [$n, $e]) { |
| 1899 | $ci = $t['map'][strtolower($n)] ?? null; |
| 1900 | if ($ci === null) throw new DbException("列がありません：{$n}", 1, 'HY000'); |
| 1901 | $p['set'][] = [$ci, $this->compile($e, $sc)]; |
| 1902 | } |
| 1903 | $p['where'] = $u['where'] ? $this->compile($u['where'], $sc) : null; |
| 1904 | } |
| 1905 | $out[] = $p; |
| 1906 | } |
| 1907 | return $out; |
| 1908 | } |
| 1909 | |
| 1910 | /** |
| 1911 | * 1行を入れる（制約の確認と衝突の解決） |
| 1912 | * |
| 1913 | * @return array|null 入れた行（値…、rowid）、入れなかった場合null |
| 1914 | */ |
| 1915 | private function insertRow(array $t, array $row, mixed $rowid, string $or, array $checks, array $upsert = []): ?array { |
| 1916 | $nc = count($t['cols']); |
| 1917 | foreach ($t['cols'] as $i => $c) { |
| 1918 | if ($i !== $t['ipk']) $row[$i] = Value::apply($row[$i], $c['aff']); |
| 1919 | } |
| 1920 | if ($rowid !== null) { |
| 1921 | $rowid = Value::apply($rowid, Value::INTEGER); |
| 1922 | if (!is_int($rowid)) throw new DbException('データ型が違います（rowidは整数）', 20, 'HY000'); |
| 1923 | } |
| 1924 | // NOT NULL |
| 1925 | foreach ($t['cols'] as $i => $c) { |
| 1926 | if ($i === $t['ipk'] || !$c['notNull'] || $row[$i] !== null) continue; |
| 1927 | if ($or === 'REPLACE' && $c['default'] !== null) { |
| 1928 | $row[$i] = Value::apply($this->constValue($c['default']), $c['aff']); |
| 1929 | if ($row[$i] !== null) continue; |
| 1930 | } |
| 1931 | if ($or === 'IGNORE') return null; |
| 1932 | $this->conflictFail($or); |
| 1933 | throw new DbException("NOT NULL制約に違反しました：{$t['name']}.{$c['name']}", 19, '23000'); |
| 1934 | } |
| 1935 | $full = $row; |
| 1936 | $full[] = $rowid; |
| 1937 | if ($t['ipk'] !== null) $full[$t['ipk']] = $rowid; |
| 1938 | foreach ($checks as $k => $f) { |
| 1939 | if (Value::truth($f($full)) === false) { |
| 1940 | if ($or === 'IGNORE') return null; |
| 1941 | $this->conflictFail($or); |
| 1942 | throw new DbException("CHECK制約に違反しました：{$t['name']}", 19, '23000'); |
| 1943 | } |
| 1944 | } |
| 1945 | |
| 1946 | // 一意の制約（新しいrowidは先に決める：REPLACEで一番大きいrowidの行を消しても、同じrowidにならない） |
| 1947 | $schema = $this->schema(); |
| 1948 | $auto = $rowid === null; |
| 1949 | if ($auto) $rowid = $this->newRowid($t); |
| 1950 | while (true) { |
| 1951 | $conflict = null; |
| 1952 | if (!$auto && $this->bt->get($t['root'], $rowid) !== null) { |
| 1953 | $conflict = [$rowid, $t['ipk'] !== null ? [$t['ipk']] : ['rowid'], 'PRIMARY KEY']; |
| 1954 | } |
| 1955 | if ($conflict === null) { |
| 1956 | foreach ($t['indexes'] as $iname) { |
| 1957 | $idx = $schema->indexes[$iname]; |
| 1958 | if (!$idx['unique']) continue; |
| 1959 | $other = $this->uniqueConflict($idx, $row, null); |
| 1960 | if ($other !== null) { |
| 1961 | $conflict = [$other, $idx['cols'], 'UNIQUE']; |
| 1962 | break; |
| 1963 | } |
| 1964 | } |
| 1965 | } |
| 1966 | if ($conflict === null) break; |
| 1967 | [$other, $cols] = $conflict; |
| 1968 | // ON CONFLICT |
| 1969 | foreach ($upsert as $u) { |
| 1970 | $sorted = $cols; |
| 1971 | sort($sorted); |
| 1972 | if ($u['target'] !== null && $u['target'] !== $sorted && !($sorted === ['rowid'] && $u['target'] === [$t['ipk']])) continue; |
| 1973 | if ($u['nothing']) return null; |
| 1974 | $this->upsertUpdate($t, $other, $full, $u); |
| 1975 | return null; |
| 1976 | } |
| 1977 | if (!empty($upsert) && $or === 'ABORT') { |
| 1978 | // 指定した制約以外の衝突 |
| 1979 | } |
| 1980 | if ($or === 'REPLACE') { |
| 1981 | $this->deleteRow($t, $other); |
| 1982 | continue; |
| 1983 | } |
| 1984 | if ($or === 'IGNORE') return null; |
| 1985 | $this->conflictFail($or); |
| 1986 | $names = implode(', ', array_map(fn($ci) => $t['name'].'.'.($ci === 'rowid' ? 'rowid' : $t['cols'][$ci]['name']), $cols)); |
| 1987 | throw new DbException("UNIQUE制約に違反しました：{$names}", 19, '23000'); |
| 1988 | } |
| 1989 | |
| 1990 | $full[$nc] = $rowid; |
| 1991 | if ($t['ipk'] !== null) { |
| 1992 | $full[$t['ipk']] = $rowid; |
| 1993 | $row[$t['ipk']] = null; |
| 1994 | } |
| 1995 | $this->bt->insert($t['root'], $rowid, Record::encode($row)); |
| 1996 | $vals = array_slice($full, 0, $nc); |
| 1997 | foreach ($t['indexes'] as $iname) { |
| 1998 | $idx = $schema->indexes[$iname]; |
| 1999 | $this->bt->indexInsert($idx, $this->indexKey($idx, $vals, $rowid)); |
| 2000 | } |
| 2001 | if ($t['autoinc']) $this->updateSequence($t, $rowid); |
| 2002 | $this->lastInsertRowid = $rowid; |
| 2003 | $this->changes++; |
| 2004 | $this->totalChanges++; |
| 2005 | return $full; |
| 2006 | } |
| 2007 | |
| 2008 | /** FAIL・ROLLBACKの衝突 */ |
| 2009 | private function conflictFail(string $or): void { |
| 2010 | if ($or === 'FAIL') $this->failKeep = true; |
| 2011 | if ($or === 'ROLLBACK') $this->rollbackAll = true; |
| 2012 | } |
| 2013 | |
| 2014 | private function indexKey(array $idx, array $vals, int $rowid): array { |
| 2015 | $k = []; |
| 2016 | foreach ($idx['cols'] as $ci) $k[] = $vals[$ci]; |
| 2017 | $k[] = $rowid; |
| 2018 | return $k; |
| 2019 | } |
| 2020 | |
| 2021 | /** 一意のインデックスで同じキーの他の行（無ければnull） */ |
| 2022 | private function uniqueConflict(array $idx, array $vals, ?int $self): ?int { |
| 2023 | $key = []; |
| 2024 | foreach ($idx['cols'] as $ci) { |
| 2025 | if ($vals[$ci] === null) return null; |
| 2026 | $key[] = $vals[$ci]; |
| 2027 | } |
| 2028 | return $this->bt->indexFind($idx, $key, $self); |
| 2029 | } |
| 2030 | |
| 2031 | private function newRowid(array $t): int { |
| 2032 | $max = $this->bt->maxRowid($t['root']); |
| 2033 | if ($t['autoinc']) { |
| 2034 | $seq = $this->sequenceValue($t['name']); |
| 2035 | $max = max($max, $seq); |
| 2036 | if ($max === PHP_INT_MAX) throw new DbException('データベースが一杯です（AUTOINCREMENT）', 13, 'HY000'); |
| 2037 | return $max + 1; |
| 2038 | } |
| 2039 | if ($max < PHP_INT_MAX) return $max + 1; |
| 2040 | for ($i = 0; $i < 100; $i++) { |
| 2041 | $id = random_int(1, PHP_INT_MAX); |
| 2042 | if ($this->bt->get($t['root'], $id) === null) return $id; |
| 2043 | } |
| 2044 | throw new DbException('データベースが一杯です', 13, 'HY000'); |
| 2045 | } |
| 2046 | |
| 2047 | private function sequenceValue(string $table): int { |
| 2048 | $seq = $this->schema()->table_('sqlite_sequence'); |
| 2049 | if ($seq === null) return 0; |
| 2050 | foreach ($this->bt->scan($seq['root']) as $rec) { |
| 2051 | $v = Record::decode($rec); |
| 2052 | if (strcasecmp((string)($v[0] ?? ''), $table) === 0) return (int)($v[1] ?? 0); |
| 2053 | } |
| 2054 | return 0; |
| 2055 | } |
| 2056 | |
| 2057 | private function updateSequence(array $t, int $rowid): void { |
| 2058 | $seq = $this->schema()->table_('sqlite_sequence'); |
| 2059 | if ($seq === null) return; |
| 2060 | foreach ($this->bt->scan($seq['root']) as $id => $rec) { |
| 2061 | $v = Record::decode($rec); |
| 2062 | if (strcasecmp((string)($v[0] ?? ''), $t['name']) === 0) { |
| 2063 | if ((int)($v[1] ?? 0) < $rowid) $this->bt->insert($seq['root'], $id, Record::encode([$v[0], $rowid])); |
| 2064 | return; |
| 2065 | } |
| 2066 | } |
| 2067 | $this->bt->insert($seq['root'], $this->bt->maxRowid($seq['root']) + 1, Record::encode([$t['name'], $rowid])); |
| 2068 | } |
| 2069 | |
| 2070 | private function upsertUpdate(array $t, int $rowid, array $excluded, array $u): void { |
| 2071 | $rec = $this->bt->get($t['root'], $rowid); |
| 2072 | if ($rec === null) return; |
| 2073 | $old = $this->rowReader($t)($rowid, $rec); |
| 2074 | $r = array_merge($old, $excluded); |
| 2075 | if ($u['where'] && !Value::truth(($u['where'])($r))) return; |
| 2076 | $new = array_slice($old, 0, count($t['cols'])); |
| 2077 | foreach ($u['set'] as [$ci, $f]) $new[$ci] = $f($r); |
| 2078 | $this->updateRow($t, $rowid, $old, $new, 'ABORT', $this->checks($t)); |
| 2079 | } |
| 2080 | |
| 2081 | /** 行を消す（インデックスも） */ |
| 2082 | private function deleteRow(array $t, int $rowid, ?array $vals = null): void { |
| 2083 | if ($vals === null) { |
| 2084 | $rec = $this->bt->get($t['root'], $rowid); |
| 2085 | if ($rec === null) return; |
| 2086 | $vals = $this->rowReader($t)($rowid, $rec); |
| 2087 | } |
| 2088 | $schema = $this->schema(); |
| 2089 | foreach ($t['indexes'] as $iname) { |
| 2090 | $idx = $schema->indexes[$iname]; |
| 2091 | $this->bt->indexDelete($idx, $this->indexKey($idx, $vals, $rowid)); |
| 2092 | } |
| 2093 | $this->bt->delete($t['root'], $rowid); |
| 2094 | } |
| 2095 | |
| 2096 | /** |
| 2097 | * 1行を変える |
| 2098 | * |
| 2099 | * @param array $old 元の値（…、rowid） |
| 2100 | * @param array $new 新しい列の値 |
| 2101 | * @return array|null 変えた行（値…、rowid）、変えなかった場合null |
| 2102 | */ |
| 2103 | private function updateRow(array $t, int $rowid, array $old, array $new, string $or, array $checks, mixed $newRowid = null): ?array { |
| 2104 | $nc = count($t['cols']); |
| 2105 | foreach ($t['cols'] as $i => $c) { |
| 2106 | if ($i !== $t['ipk']) $new[$i] = Value::apply($new[$i], $c['aff']); |
| 2107 | } |
| 2108 | $target = $rowid; |
| 2109 | if ($t['ipk'] !== null && $new[$t['ipk']] !== $old[$t['ipk']]) $newRowid = $new[$t['ipk']]; |
| 2110 | if ($newRowid !== null) { |
| 2111 | $newRowid = Value::apply($newRowid, Value::INTEGER); |
| 2112 | if (!is_int($newRowid)) throw new DbException('データ型が違います（rowidは整数）', 20, 'HY000'); |
| 2113 | $target = $newRowid; |
| 2114 | } else if ($t['ipk'] !== null && $new[$t['ipk']] === null) { |
| 2115 | throw new DbException("NOT NULL制約に違反しました：{$t['name']}.{$t['cols'][$t['ipk']]['name']}", 19, '23000'); |
| 2116 | } |
| 2117 | foreach ($t['cols'] as $i => $c) { |
| 2118 | if ($i === $t['ipk'] || !$c['notNull'] || $new[$i] !== null) continue; |
| 2119 | if ($or === 'REPLACE' && $c['default'] !== null) { |
| 2120 | $new[$i] = Value::apply($this->constValue($c['default']), $c['aff']); |
| 2121 | if ($new[$i] !== null) continue; |
| 2122 | } |
| 2123 | if ($or === 'IGNORE') return null; |
| 2124 | $this->conflictFail($or); |
| 2125 | throw new DbException("NOT NULL制約に違反しました：{$t['name']}.{$c['name']}", 19, '23000'); |
| 2126 | } |
| 2127 | $full = $new; |
| 2128 | $full[$nc] = $target; |
| 2129 | if ($t['ipk'] !== null) $full[$t['ipk']] = $target; |
| 2130 | foreach ($checks as $f) { |
| 2131 | if (Value::truth($f($full)) === false) { |
| 2132 | if ($or === 'IGNORE') return null; |
| 2133 | $this->conflictFail($or); |
| 2134 | throw new DbException("CHECK制約に違反しました：{$t['name']}", 19, '23000'); |
| 2135 | } |
| 2136 | } |
| 2137 | $schema = $this->schema(); |
| 2138 | while (true) { |
| 2139 | $conflict = null; |
| 2140 | if ($target !== $rowid && $this->bt->get($t['root'], $target) !== null) $conflict = [$target, $t['ipk'] !== null ? [$t['ipk']] : ['rowid']]; |
| 2141 | if ($conflict === null) { |
| 2142 | foreach ($t['indexes'] as $iname) { |
| 2143 | $idx = $schema->indexes[$iname]; |
| 2144 | if (!$idx['unique']) continue; |
| 2145 | $other = $this->uniqueConflict($idx, array_slice($full, 0, $nc), $rowid); |
| 2146 | if ($other !== null) { |
| 2147 | $conflict = [$other, $idx['cols']]; |
| 2148 | break; |
| 2149 | } |
| 2150 | } |
| 2151 | } |
| 2152 | if ($conflict === null) break; |
| 2153 | if ($or === 'REPLACE') { |
| 2154 | $this->deleteRow($t, $conflict[0]); |
| 2155 | continue; |
| 2156 | } |
| 2157 | if ($or === 'IGNORE') return null; |
| 2158 | $this->conflictFail($or); |
| 2159 | $names = implode(', ', array_map(fn($ci) => $t['name'].'.'.($ci === 'rowid' ? 'rowid' : $t['cols'][$ci]['name']), $conflict[1])); |
| 2160 | throw new DbException("UNIQUE制約に違反しました：{$names}", 19, '23000'); |
| 2161 | } |
| 2162 | $oldVals = array_slice($old, 0, $nc); |
| 2163 | $newVals = array_slice($full, 0, $nc); |
| 2164 | foreach ($t['indexes'] as $iname) { |
| 2165 | $idx = $schema->indexes[$iname]; |
| 2166 | $ok = $this->indexKey($idx, $oldVals, $rowid); |
| 2167 | $nk = $this->indexKey($idx, $newVals, $target); |
| 2168 | if ($target === $rowid && self::sameKey($ok, $nk)) continue; |
| 2169 | $this->bt->indexDelete($idx, $ok); |
| 2170 | $this->bt->indexInsert($idx, $nk); |
| 2171 | } |
| 2172 | $rec = $new; |
| 2173 | if ($t['ipk'] !== null) $rec[$t['ipk']] = null; |
| 2174 | if ($target !== $rowid) $this->bt->delete($t['root'], $rowid); |
| 2175 | $this->bt->insert($t['root'], $target, Record::encode($rec)); |
| 2176 | $this->changes++; |
| 2177 | $this->totalChanges++; |
| 2178 | return $full; |
| 2179 | } |
| 2180 | |
| 2181 | private static function sameKey(array $a, array $b): bool { |
| 2182 | foreach ($a as $i => $v) { |
| 2183 | if (Value::type($v) !== Value::type($b[$i]) || Record::compare($v, $b[$i]) !== 0) return false; |
| 2184 | } |
| 2185 | return true; |
| 2186 | } |
| 2187 | |
| 2188 | /** UPDATE・DELETEの対象の行 */ |
| 2189 | private function targetRows(array $t, ?string $alias, array $from, ?array $where, ?array &$setScope = null, array $setCols = []): array { |
| 2190 | $srcs = [$this->tableSource($t, $alias)]; |
| 2191 | foreach ($from as $item) { |
| 2192 | if (isset($item['sel'])) { |
| 2193 | $srcs[] = $this->planSource($this->planSelect($item['sel'], null), $item['as'] ?? '(subquery)', null); |
| 2194 | } else { |
| 2195 | $srcs[] = $this->sourceFor($item['table'], $item['as'], null); |
| 2196 | } |
| 2197 | } |
| 2198 | $core = ['distinct' => false, 'cols' => [], 'from' => [], 'where' => $where, 'group' => [], 'having' => null]; |
| 2199 | $sc = $this->scope($srcs, null); |
| 2200 | $setScope = [$sc]; |
| 2201 | $n = count($srcs); |
| 2202 | $conj = self::conjuncts($where); |
| 2203 | $levelPost = array_fill(0, $n, []); |
| 2204 | $levelCand = array_fill(0, $n, []); |
| 2205 | foreach ($from as $j => $item) { |
| 2206 | if (!empty($item['on'])) $conj = array_merge($conj, self::conjuncts($item['on'])); |
| 2207 | } |
| 2208 | foreach ($conj as $c) { |
| 2209 | if (self::hasAggregate($c)) throw new DbException('WHEREに集約関数は使えません', 1, 'HY000'); |
| 2210 | $r = $this->refs($c, $sc); |
| 2211 | $lvl = $r === null ? $n - 1 : (empty($r) ? 0 : max($r)); |
| 2212 | $levelPost[$lvl][] = $c; |
| 2213 | if ($r !== null) $levelCand[$lvl][] = $c; |
| 2214 | } |
| 2215 | $levels = []; |
| 2216 | $byIndex = null; |
| 2217 | for ($j = 0; $j < $n; $j++) { |
| 2218 | $levels[] = ['src' => $this->access($srcs[$j], $j, $levelCand[$j], $sc), 'on' => [], |
| 2219 | 'post' => array_map(fn($c) => $this->compile($c, $sc), $levelPost[$j]), 'left' => false, 'nulls' => []]; |
| 2220 | if ($j === 0) $byIndex = $this->lastAccess; |
| 2221 | } |
| 2222 | $out = []; |
| 2223 | $nc = count($t['cols']); |
| 2224 | foreach ($this->joinLoop($levels, 0, []) as $r) { |
| 2225 | $id = $r[$nc]; |
| 2226 | if (!isset($out[$id])) $out[$id] = $r; |
| 2227 | } |
| 2228 | // 変える列のインデックスで読んだ場合は、rowidの順番（SQLiteと同じ） |
| 2229 | if ($n > 1 || $byIndex === null || in_array('*', $setCols, true) || array_intersect($byIndex, $setCols)) ksort($out); |
| 2230 | return $out; |
| 2231 | } |
| 2232 | |
| 2233 | private function update(array $st): array { |
| 2234 | $t = $this->writableTable($st['table']); |
| 2235 | $nc = count($t['cols']); |
| 2236 | $setCols = []; |
| 2237 | foreach ($st['set'] as [$n]) $setCols[] = $t['map'][strtolower($n)] ?? 'rowid'; |
| 2238 | // REPLACEは行を消すかもしれないので、rowidの順番（SQLiteと同じ） |
| 2239 | if (($st['or'] ?? '') === 'REPLACE') $setCols[] = '*'; |
| 2240 | $rows = $this->targetRows($t, $st['alias'], $st['from'], $st['where'], $scopes, $setCols); |
| 2241 | $sc = $scopes[0]; |
| 2242 | $sets = []; |
| 2243 | foreach ($st['set'] as [$n, $e]) { |
| 2244 | $ln = strtolower($n); |
| 2245 | $ci = $t['map'][$ln] ?? (in_array($ln, ['rowid', 'oid', '_rowid_'], true) ? 'rowid' : null); |
| 2246 | if ($ci === null) throw new DbException("列がありません：{$n}", 1, 'HY000'); |
| 2247 | $sets[] = [$ci, $this->compile($e, $sc)]; |
| 2248 | } |
| 2249 | $checks = $this->checks($t); |
| 2250 | $ret = $this->returningPlan($st['returning'], $t, $st['alias']); |
| 2251 | $or = $st['or'] ?? 'ABORT'; |
| 2252 | foreach ($rows as $id => $r) { |
| 2253 | // 他の行のREPLACEで消えた |
| 2254 | if ($this->bt->get($t['root'], $id) === null) continue; |
| 2255 | $new = array_slice($r, 0, $nc); |
| 2256 | $newRowid = null; |
| 2257 | foreach ($sets as [$ci, $f]) { |
| 2258 | if ($ci === 'rowid') $newRowid = $f($r); |
| 2259 | else $new[$ci] = $f($r); |
| 2260 | } |
| 2261 | $old = array_slice($r, 0, $nc + 1); |
| 2262 | $res = $this->updateRow($t, $id, $old, $new, $or, $checks, $newRowid); |
| 2263 | if ($res !== null && $ret) $ret->rows[] = array_map(fn($f) => $f($res), $ret->fns); |
| 2264 | } |
| 2265 | if ($ret) return self::result($ret->cols, $ret->types, $ret->rows, $this->changes); |
| 2266 | return self::result([], [], [], $this->changes); |
| 2267 | } |
| 2268 | |
| 2269 | private function delete(array $st): array { |
| 2270 | $t = $this->writableTable($st['table']); |
| 2271 | $nc = count($t['cols']); |
| 2272 | $schema = $this->schema(); |
| 2273 | if ($st['where'] === null && $st['returning'] === null) { |
| 2274 | // 全部：ツリーを空にする |
| 2275 | $n = $this->bt->count($t['root']); |
| 2276 | $this->bt->clear($t['root']); |
| 2277 | foreach ($t['indexes'] as $iname) $this->bt->clear($schema->indexes[$iname]['root']); |
| 2278 | $this->changes = $n; |
| 2279 | $this->totalChanges += $n; |
| 2280 | return self::result([], [], [], $n); |
| 2281 | } |
| 2282 | $rows = $this->targetRows($t, $st['alias'], [], $st['where']); |
| 2283 | $ret = $this->returningPlan($st['returning'], $t, $st['alias']); |
| 2284 | foreach ($rows as $id => $r) { |
| 2285 | $vals = array_slice($r, 0, $nc + 1); |
| 2286 | $this->deleteRow($t, $id, $vals); |
| 2287 | if ($ret) $ret->rows[] = array_map(fn($f) => $f($vals), $ret->fns); |
| 2288 | $this->changes++; |
| 2289 | $this->totalChanges++; |
| 2290 | } |
| 2291 | if ($ret) return self::result($ret->cols, $ret->types, $ret->rows, $this->changes); |
| 2292 | return self::result([], [], [], $this->changes); |
| 2293 | } |
| 2294 | |
| 2295 | /////////////// |
| 2296 | // スキーマの変更 |
| 2297 | |
| 2298 | private function schemaInsert(string $type, string $name, string $tbl, int $root, ?string $sql): void { |
| 2299 | $id = $this->bt->maxRowid(1) + 1; |
| 2300 | $this->bt->insert(1, $id, Record::encode([$type, $name, $tbl, $root, $sql])); |
| 2301 | $this->schemaChanged(); |
| 2302 | } |
| 2303 | |
| 2304 | private function schemaChanged(): void { |
| 2305 | $this->pager->hdr['cookie'] = ($this->pager->hdr['cookie'] + 1) & 0x7FFFFFFF; |
| 2306 | $this->schema = null; |
| 2307 | } |
| 2308 | |
| 2309 | private function createTable(array $st): array { |
| 2310 | $schema = $this->schema(); |
| 2311 | if ($schema->exists($st['name'])) { |
| 2312 | if ($st['ifNotExists'] && $schema->table_($st['name']) !== null) return self::result(); |
| 2313 | throw new DbException("テーブル{$st['name']}は既に有ります", 1, 'HY000'); |
| 2314 | } |
| 2315 | if (stripos($st['name'], 'sqlite_') === 0 && strcasecmp($st['name'], 'sqlite_sequence') !== 0) { |
| 2316 | throw new DbException("「sqlite_」で始まる名前は使えません：{$st['name']}", 1, 'HY000'); |
| 2317 | } |
| 2318 | if (isset($st['as'])) { |
| 2319 | $plan = $this->planSelect($st['as'], null); |
| 2320 | $defs = []; |
| 2321 | foreach ($plan->cols as $i => $n) { |
| 2322 | $ty = match ($plan->affs[$i]) { Value::INTEGER => 'INT', Value::REAL => 'REAL', Value::TEXT => 'TEXT', Value::NUMERIC => 'NUM', default => '' }; |
| 2323 | $defs[] = self::quoteName($n).($ty !== '' ? ' '.$ty : ''); |
| 2324 | } |
| 2325 | $sql = 'CREATE TABLE '.self::quoteName($st['name']).'('.implode(',', $defs).')'; |
| 2326 | $this->createTable($this->parser->parseAll($sql)[0]); |
| 2327 | $rows = ($plan->run)(-1); |
| 2328 | $t = $this->schema()->table_($st['name']); |
| 2329 | foreach ($rows as $vals) $this->insertRow($t, $vals, null, 'ABORT', []); |
| 2330 | return self::result(); |
| 2331 | } |
| 2332 | $def = $st['def']; |
| 2333 | $sql = 'CREATE TABLE '.substr($st['sql'], $st['nameStart'] - $st['start']); |
| 2334 | if ($st['ifNotExists'] === false && !isset($st['nameStart'])) $sql = $st['sql']; |
| 2335 | $t = Schema::table($st['name'], 0, $sql, $def); |
| 2336 | $root = $this->bt->create(true); |
| 2337 | $this->schemaInsert('table', $st['name'], $st['name'], $root, $sql); |
| 2338 | foreach ($t['constraints'] as $k => [$kind, $list]) { |
| 2339 | $iroot = $this->bt->create(false); |
| 2340 | $this->schemaInsert('index', 'sqlite_autoindex_'.$st['name'].'_'.($k + 1), $st['name'], $iroot, null); |
| 2341 | } |
| 2342 | if ($t['autoinc'] && $this->schema()->table_('sqlite_sequence') === null) { |
| 2343 | $sroot = $this->bt->create(true); |
| 2344 | $this->schemaInsert('table', 'sqlite_sequence', 'sqlite_sequence', $sroot, 'CREATE TABLE sqlite_sequence(name,seq)'); |
| 2345 | } |
| 2346 | return self::result(); |
| 2347 | } |
| 2348 | |
| 2349 | private function createIndex(array $st): array { |
| 2350 | $schema = $this->schema(); |
| 2351 | if ($schema->exists($st['name'])) { |
| 2352 | if ($st['ifNotExists'] && isset($schema->indexes[strtolower($st['name'])])) return self::result(); |
| 2353 | throw new DbException("インデックス{$st['name']}は既に有ります", 1, 'HY000'); |
| 2354 | } |
| 2355 | $t = $schema->table_($st['table']); |
| 2356 | if ($t === null) throw new DbException("テーブルがありません：{$st['table']}", 1, 'HY000'); |
| 2357 | if (!empty($t['system']) || isset($t['unsupported'])) throw new DbException("テーブル{$t['name']}にインデックスは作れません", 1, 'HY000'); |
| 2358 | $idx = Schema::indexInfo(['name' => $st['name'], 'root' => 0, 'table' => strtolower($t['name']), 'unique' => $st['unique'], 'def' => $st['cols'], 'origin' => 'c'], $t); |
| 2359 | $sql = 'CREATE '.($st['unique'] ? 'UNIQUE ' : '').'INDEX '.substr($st['sql'], $st['nameStart'] - $st['start']); |
| 2360 | $idx['root'] = $this->bt->create(false); |
| 2361 | $read = $this->rowReader($t); |
| 2362 | foreach ($this->bt->scan($t['root']) as $id => $rec) { |
| 2363 | $vals = $read($id, $rec); |
| 2364 | if ($st['unique'] && $this->uniqueConflict($idx, $vals, $id) !== null) { |
| 2365 | $names = implode(', ', array_map(fn($ci) => $t['name'].'.'.$t['cols'][$ci]['name'], $idx['cols'])); |
| 2366 | throw new DbException("UNIQUE制約に違反しました：{$names}", 19, '23000'); |
| 2367 | } |
| 2368 | $this->bt->indexInsert($idx, $this->indexKey($idx, $vals, $id)); |
| 2369 | } |
| 2370 | $this->schemaInsert('index', $st['name'], $t['name'], $idx['root'], $sql); |
| 2371 | return self::result(); |
| 2372 | } |
| 2373 | |
| 2374 | private function createView(array $st): array { |
| 2375 | $schema = $this->schema(); |
| 2376 | if ($schema->exists($st['name'])) { |
| 2377 | if ($st['ifNotExists'] && isset($schema->views[strtolower($st['name'])])) return self::result(); |
| 2378 | throw new DbException("ビュー{$st['name']}は既に有ります", 1, 'HY000'); |
| 2379 | } |
| 2380 | // 確認 |
| 2381 | $plan = $this->planSelect($st['sel'], null); |
| 2382 | if ($st['cols'] !== null && count($st['cols']) !== count($plan->cols)) throw new DbException('ビューの列の数が違います', 1, 'HY000'); |
| 2383 | $sql = 'CREATE VIEW '.substr($st['sql'], $st['nameStart'] - $st['start']); |
| 2384 | $this->schemaInsert('view', $st['name'], $st['name'], 0, $sql); |
| 2385 | return self::result(); |
| 2386 | } |
| 2387 | |
| 2388 | private function drop(array $st): array { |
| 2389 | $schema = $this->schema(); |
| 2390 | $l = strtolower($st['name']); |
| 2391 | switch ($st['kind']) { |
| 2392 | case 'table': |
| 2393 | $t = $schema->tables[$l] ?? null; |
| 2394 | if ($t === null) { |
| 2395 | if ($st['ifExists']) return self::result(); |
| 2396 | throw new DbException("テーブルがありません：{$st['name']}", 1, 'HY000'); |
| 2397 | } |
| 2398 | if (!empty($t['system']) || $l === 'sqlite_sequence') throw new DbException("テーブル{$t['name']}は消せません", 1, 'HY000'); |
| 2399 | foreach ($t['indexes'] ?? [] as $iname) { |
| 2400 | $idx = $schema->indexes[$iname]; |
| 2401 | $this->bt->clear($idx['root'], true); |
| 2402 | $this->bt->delete(1, $idx['schemaRowid']); |
| 2403 | } |
| 2404 | // 自動のインデックスの行（テーブルが読めない場合も） |
| 2405 | foreach ($this->bt->scan(1) as $id => $rec) { |
| 2406 | $v = Record::decode($rec); |
| 2407 | if (($v[0] ?? '') === 'trigger' && strcasecmp((string)$v[2], $t['name']) === 0) $this->bt->delete(1, $id); |
| 2408 | } |
| 2409 | $this->bt->clear($t['root'], true); |
| 2410 | $this->bt->delete(1, $t['schemaRowid']); |
| 2411 | if (!empty($t['autoinc']) && ($seq = $schema->table_('sqlite_sequence')) !== null) { |
| 2412 | foreach ($this->bt->scan($seq['root']) as $id => $rec) { |
| 2413 | if (strcasecmp((string)(Record::decode($rec)[0] ?? ''), $t['name']) === 0) { |
| 2414 | $this->bt->delete($seq['root'], $id); |
| 2415 | break; |
| 2416 | } |
| 2417 | } |
| 2418 | } |
| 2419 | $this->schemaChanged(); |
| 2420 | return self::result(); |
| 2421 | case 'index': |
| 2422 | $idx = $schema->indexes[$l] ?? null; |
| 2423 | if ($idx === null) { |
| 2424 | if ($st['ifExists']) return self::result(); |
| 2425 | throw new DbException("インデックスがありません：{$st['name']}", 1, 'HY000'); |
| 2426 | } |
| 2427 | if ($idx['sql'] === null) throw new DbException('UNIQUE・PRIMARY KEYの制約のインデックスは消せません', 1, 'HY000'); |
| 2428 | $this->bt->clear($idx['root'], true); |
| 2429 | $this->bt->delete(1, $idx['schemaRowid']); |
| 2430 | $this->schemaChanged(); |
| 2431 | return self::result(); |
| 2432 | case 'view': |
| 2433 | $v = $schema->views[$l] ?? null; |
| 2434 | if ($v === null) { |
| 2435 | if ($st['ifExists']) return self::result(); |
| 2436 | throw new DbException("ビューがありません：{$st['name']}", 1, 'HY000'); |
| 2437 | } |
| 2438 | $this->bt->delete(1, $v['schemaRowid']); |
| 2439 | $this->schemaChanged(); |
| 2440 | return self::result(); |
| 2441 | default: |
| 2442 | $id = $schema->triggers['@'.$l] ?? null; |
| 2443 | if ($id === null) { |
| 2444 | if ($st['ifExists']) return self::result(); |
| 2445 | throw new DbException("トリガーがありません：{$st['name']}", 1, 'HY000'); |
| 2446 | } |
| 2447 | $this->bt->delete(1, $id); |
| 2448 | $this->schemaChanged(); |
| 2449 | return self::result(); |
| 2450 | } |
| 2451 | } |
| 2452 | |
| 2453 | public static function quoteName(string $n): string { |
| 2454 | 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) |
| 2455 | ? $n : '"'.str_replace('"', '""', $n).'"'; |
| 2456 | } |
| 2457 | |
| 2458 | /** スキーマの行を変える */ |
| 2459 | private function schemaUpdate(int $rowid, array $vals): void { |
| 2460 | $this->bt->insert(1, $rowid, Record::encode($vals)); |
| 2461 | } |
| 2462 | |
| 2463 | private function alter(array $st): array { |
| 2464 | $schema = $this->schema(); |
| 2465 | $t = $this->writableTable($st['table']); |
| 2466 | $toks = $this->parser->tokens($t['sql']); |
| 2467 | switch ($st['op']) { |
| 2468 | case 'rename': |
| 2469 | if ($schema->exists($st['to'])) throw new DbException("{$st['to']}は既に有ります", 1, 'HY000'); |
| 2470 | $quoted = '"'.str_replace('"', '""', $st['to']).'"'; |
| 2471 | $sql = $this->replaceToken($t['sql'], $toks, $this->nameTokenIndex($toks), $quoted); |
| 2472 | $this->schemaUpdate($t['schemaRowid'], ['table', $st['to'], $st['to'], $t['root'], $sql]); |
| 2473 | foreach ($t['indexes'] as $iname) { |
| 2474 | $idx = $schema->indexes[$iname]; |
| 2475 | $iname2 = $idx['name']; |
| 2476 | $isql = $idx['sql']; |
| 2477 | if ($isql === null) { |
| 2478 | $iname2 = 'sqlite_autoindex_'.$st['to'].substr($idx['name'], strlen('sqlite_autoindex_'.$t['name'])); |
| 2479 | } else { |
| 2480 | $itoks = $this->parser->tokens($isql); |
| 2481 | foreach ($itoks as $k => $tk) { |
| 2482 | if ($tk['t'] === 'id' && !$tk['q'] && $tk['u'] === 'ON') { |
| 2483 | $isql = $this->replaceToken($isql, $itoks, $k + 1, $quoted); |
| 2484 | break; |
| 2485 | } |
| 2486 | } |
| 2487 | } |
| 2488 | $this->schemaUpdate($idx['schemaRowid'], ['index', $iname2, $st['to'], $idx['root'], $isql]); |
| 2489 | } |
| 2490 | if ($t['autoinc'] && ($seq = $schema->table_('sqlite_sequence')) !== null) { |
| 2491 | foreach ($this->bt->scan($seq['root']) as $id => $rec) { |
| 2492 | $v = Record::decode($rec); |
| 2493 | if (strcasecmp((string)$v[0], $t['name']) === 0) $this->bt->insert($seq['root'], $id, Record::encode([$st['to'], $v[1]])); |
| 2494 | } |
| 2495 | } |
| 2496 | $this->schemaChanged(); |
| 2497 | return self::result(); |
| 2498 | |
| 2499 | case 'renameColumn': |
| 2500 | $ci = $t['map'][strtolower($st['from'])] ?? null; |
| 2501 | if ($ci === null) throw new DbException("列がありません：{$st['from']}", 1, 'HY000'); |
| 2502 | if (isset($t['map'][strtolower($st['to'])])) throw new DbException("列{$st['to']}は既に有ります", 1, 'HY000'); |
| 2503 | $nameIdx = $this->nameTokenIndex($toks); |
| 2504 | $sql = $this->replaceIdentifiers($t['sql'], $toks, $st['from'], $st['to'], $nameIdx + 1); |
| 2505 | $this->schemaUpdate($t['schemaRowid'], ['table', $t['name'], $t['name'], $t['root'], $sql]); |
| 2506 | foreach ($t['indexes'] as $iname) { |
| 2507 | $idx = $schema->indexes[$iname]; |
| 2508 | if ($idx['sql'] === null) continue; |
| 2509 | $itoks = $this->parser->tokens($idx['sql']); |
| 2510 | $start = 0; |
| 2511 | foreach ($itoks as $k => $tk) if ($tk['t'] === 'op' && $tk['v'] === '(') { |
| 2512 | $start = $k; |
| 2513 | break; |
| 2514 | } |
| 2515 | $isql = $this->replaceIdentifiers($idx['sql'], $itoks, $st['from'], $st['to'], $start); |
| 2516 | $this->schemaUpdate($idx['schemaRowid'], ['index', $idx['name'], $t['name'], $idx['root'], $isql]); |
| 2517 | } |
| 2518 | $this->schemaChanged(); |
| 2519 | return self::result(); |
| 2520 | |
| 2521 | case 'addColumn': |
| 2522 | $c = $st['col']; |
| 2523 | if (isset($t['map'][strtolower($c['name'])])) throw new DbException("列{$c['name']}は既に有ります", 1, 'HY000'); |
| 2524 | if ($c['pk']) throw new DbException('PRIMARY KEYの列は追加できません', 1, 'HY000'); |
| 2525 | if ($c['unique']) throw new DbException('UNIQUEの列は追加できません', 1, 'HY000'); |
| 2526 | if ($c['default'] !== null && !in_array($c['default']['t'], ['lit', 'un'], true)) throw new DbException('定数でないDEFAULTの列は追加できません', 1, 'HY000'); |
| 2527 | if ($c['notNull'] && ($c['default'] === null || $this->constValue($c['default']) === null) && $this->bt->count($t['root']) > 0) { |
| 2528 | throw new DbException('NOT NULLの列にはNULLでないDEFAULTが必要です', 1, 'HY000'); |
| 2529 | } |
| 2530 | // 最後の列の定義の後（テーブルの制約の前）に入れる |
| 2531 | [$open, $close] = $this->bodyParens($toks); |
| 2532 | $depth = 0; |
| 2533 | $end = $open; |
| 2534 | $inCol = true; |
| 2535 | for ($k = $open + 1; $k < $close; $k++) { |
| 2536 | $tk = $toks[$k]; |
| 2537 | if ($tk['t'] === 'op' && $tk['v'] === '(') $depth++; |
| 2538 | else if ($tk['t'] === 'op' && $tk['v'] === ')') $depth--; |
| 2539 | if ($depth === 0 && $tk['t'] === 'op' && $tk['v'] === ',') { |
| 2540 | $nx = $toks[$k + 1]; |
| 2541 | $inCol = !($nx['t'] === 'id' && !$nx['q'] && in_array($nx['u'], ['CONSTRAINT', 'PRIMARY', 'UNIQUE', 'CHECK', 'FOREIGN'], true)); |
| 2542 | continue; |
| 2543 | } |
| 2544 | if ($inCol) $end = $k; |
| 2545 | } |
| 2546 | $at = $toks[$end]['e']; |
| 2547 | $sql = substr($t['sql'], 0, $at).', '.$st['text'].substr($t['sql'], $at); |
| 2548 | $this->schemaUpdate($t['schemaRowid'], ['table', $t['name'], $t['name'], $t['root'], $sql]); |
| 2549 | $this->schemaChanged(); |
| 2550 | return self::result(); |
| 2551 | |
| 2552 | case 'dropColumn': |
| 2553 | $ci = $t['map'][strtolower($st['col'])] ?? null; |
| 2554 | if ($ci === null) throw new DbException("列がありません：{$st['col']}", 1, 'HY000'); |
| 2555 | if (count($t['cols']) === 1) throw new DbException('最後の列は消せません', 1, 'HY000'); |
| 2556 | if ($t['cols'][$ci]['pk']) throw new DbException('PRIMARY KEYの列は消せません', 1, 'HY000'); |
| 2557 | foreach ($t['indexes'] as $iname) { |
| 2558 | if (in_array($ci, $schema->indexes[$iname]['cols'], true)) throw new DbException("インデックスの有る列は消せません：{$st['col']}", 1, 'HY000'); |
| 2559 | } |
| 2560 | // 列の定義の場所 |
| 2561 | [$open, $close] = $this->bodyParens($toks); |
| 2562 | $depth = 0; |
| 2563 | $starts = [$open + 1]; |
| 2564 | for ($k = $open + 1; $k < $close; $k++) { |
| 2565 | $tk = $toks[$k]; |
| 2566 | if ($tk['t'] === 'op' && $tk['v'] === '(') $depth++; |
| 2567 | else if ($tk['t'] === 'op' && $tk['v'] === ')') $depth--; |
| 2568 | else if ($depth === 0 && $tk['t'] === 'op' && $tk['v'] === ',') $starts[] = $k + 1; |
| 2569 | } |
| 2570 | $starts[] = $close + 1; |
| 2571 | $seg = null; |
| 2572 | foreach ($starts as $s => $k) { |
| 2573 | if (!isset($starts[$s + 1])) break; |
| 2574 | $tk = $toks[$k]; |
| 2575 | if ($tk['t'] === 'id' && strcasecmp($tk['v'], $t['cols'][$ci]['name']) === 0) $seg = $s; |
| 2576 | } |
| 2577 | if ($seg === null) throw new DbException('列の定義が見つかりません', 1, 'HY000'); |
| 2578 | $from = $seg === 0 ? $toks[$starts[0]]['s'] : $toks[$starts[$seg] - 1]['s']; |
| 2579 | $to = $seg === 0 ? $toks[$starts[1]]['s'] : $toks[$starts[$seg + 1] - 1]['s']; |
| 2580 | $sql = substr($t['sql'], 0, $from).substr($t['sql'], $to); |
| 2581 | // 行を書き直す |
| 2582 | $updates = []; |
| 2583 | foreach ($this->bt->scan($t['root']) as $id => $rec) { |
| 2584 | $v = Record::decode($rec); |
| 2585 | if (count($v) > $ci) { |
| 2586 | array_splice($v, $ci, 1); |
| 2587 | $updates[$id] = Record::encode($v); |
| 2588 | } |
| 2589 | } |
| 2590 | foreach ($updates as $id => $rec) $this->bt->insert($t['root'], $id, $rec); |
| 2591 | $this->schemaUpdate($t['schemaRowid'], ['table', $t['name'], $t['name'], $t['root'], $sql]); |
| 2592 | $this->schemaChanged(); |
| 2593 | // 自動のインデックスの番号は変わらない（UNIQUEの列は消せないので） |
| 2594 | return self::result(); |
| 2595 | } |
| 2596 | throw new DbException('未対応のALTER TABLE', 1, 'HY000'); |
| 2597 | } |
| 2598 | |
| 2599 | /** CREATE TABLE/INDEX [IF NOT EXISTS] 名前 の名前のトークン */ |
| 2600 | private function nameTokenIndex(array $toks): int { |
| 2601 | $k = 0; |
| 2602 | 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++; |
| 2603 | if (($toks[$k + 1]['t'] ?? '') === 'op' && $toks[$k + 1]['v'] === '.') $k += 2; |
| 2604 | return $k; |
| 2605 | } |
| 2606 | |
| 2607 | private function replaceToken(string $sql, array $toks, int $k, string $new): string { |
| 2608 | return substr($sql, 0, $toks[$k]['s']).$new.substr($sql, $toks[$k]['e']); |
| 2609 | } |
| 2610 | |
| 2611 | private function replaceIdentifiers(string $sql, array $toks, string $from, string $to, int $start): string { |
| 2612 | $out = ''; |
| 2613 | $pos = 0; |
| 2614 | foreach ($toks as $k => $tk) { |
| 2615 | if ($k < $start || $tk['t'] !== 'id' || strcasecmp($tk['v'], $from) !== 0) continue; |
| 2616 | $out .= substr($sql, $pos, $tk['s'] - $pos).self::quoteName($to); |
| 2617 | $pos = $tk['e']; |
| 2618 | } |
| 2619 | return $out.substr($sql, $pos); |
| 2620 | } |
| 2621 | |
| 2622 | /** テーブルの定義の括弧（開く、閉じる）のトークン */ |
| 2623 | private function bodyParens(array $toks): array { |
| 2624 | $open = null; |
| 2625 | $depth = 0; |
| 2626 | foreach ($toks as $k => $tk) { |
| 2627 | if ($tk['t'] !== 'op') continue; |
| 2628 | if ($tk['v'] === '(') { |
| 2629 | if ($open === null) $open = $k; |
| 2630 | $depth++; |
| 2631 | } else if ($tk['v'] === ')') { |
| 2632 | $depth--; |
| 2633 | if ($depth === 0 && $open !== null) return [$open, $k]; |
| 2634 | } |
| 2635 | } |
| 2636 | throw new DbException('テーブルのSQLが不正です', 11, 'HY000'); |
| 2637 | } |
| 2638 | |
| 2639 | /////////////// |
| 2640 | // PRAGMA |
| 2641 | |
| 2642 | private function pragma(array $st): array { |
| 2643 | $schema = $this->schema(); |
| 2644 | $arg = $st['arg']; |
| 2645 | $one = fn(string $name, mixed $v) => self::result([$name], [''], [[$v]]); |
| 2646 | switch ($st['name']) { |
| 2647 | case 'table_info': |
| 2648 | case 'table_xinfo': |
| 2649 | $t = $schema->table_((string)$arg); |
| 2650 | if ($t === null) { |
| 2651 | $v = $schema->views[strtolower((string)$arg)] ?? null; |
| 2652 | if ($v === null) return self::result(['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk']); |
| 2653 | $plan = $this->planSelect($v['sel'], null); |
| 2654 | $rows = []; |
| 2655 | foreach ($v['cols'] ?? $plan->cols as $i => $n) $rows[] = [$i, $n, $plan->types[$i] ?? '', 0, null, 0]; |
| 2656 | return self::result(['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk'], [], $rows); |
| 2657 | } |
| 2658 | $rows = []; |
| 2659 | foreach ($t['cols'] as $i => $c) $rows[] = [$i, $c['name'], $c['type'], (int)$c['notNull'], $c['defaultText'], $c['pk']]; |
| 2660 | return self::result(['cid', 'name', 'type', 'notnull', 'dflt_value', 'pk'], [], $rows); |
| 2661 | case 'index_list': |
| 2662 | $t = $schema->table_((string)$arg); |
| 2663 | $rows = []; |
| 2664 | foreach (array_reverse($t['indexes'] ?? []) as $k => $iname) { |
| 2665 | $idx = $schema->indexes[$iname]; |
| 2666 | $rows[] = [$k, $idx['name'], (int)$idx['unique'], $idx['origin'], 0]; |
| 2667 | } |
| 2668 | return self::result(['seq', 'name', 'unique', 'origin', 'partial'], [], $rows); |
| 2669 | case 'index_info': |
| 2670 | case 'index_xinfo': |
| 2671 | $idx = $schema->indexes[strtolower((string)$arg)] ?? null; |
| 2672 | $rows = []; |
| 2673 | if ($idx) { |
| 2674 | $t = $schema->tables[$idx['table']]; |
| 2675 | foreach ($idx['cols'] as $k => $ci) { |
| 2676 | $rows[] = $st['name'] === 'index_info' ? [$k, $ci, $t['cols'][$ci]['name']] |
| 2677 | : [$k, $ci, $t['cols'][$ci]['name'], (int)$idx['desc'][$k], $idx['colls'][$k], 1]; |
| 2678 | } |
| 2679 | if ($st['name'] === 'index_xinfo') $rows[] = [count($idx['cols']), -1, null, 0, 'BINARY', 0]; |
| 2680 | } |
| 2681 | return self::result($st['name'] === 'index_info' ? ['seqno', 'cid', 'name'] : ['seqno', 'cid', 'name', 'desc', 'coll', 'key'], [], $rows); |
| 2682 | case 'table_list': |
| 2683 | $rows = []; |
| 2684 | foreach ($schema->tables as $t) $rows[] = ['main', $t['name'] === 'sqlite_schema' ? 'sqlite_schema' : $t['name'], 'table', count($t['cols']), 0, 0]; |
| 2685 | foreach ($schema->views as $v) $rows[] = ['main', $v['name'], 'view', count($v['cols'] ?? []), 0, 0]; |
| 2686 | return self::result(['schema', 'name', 'type', 'ncol', 'wr', 'strict'], [], $rows); |
| 2687 | case 'foreign_key_list': |
| 2688 | return self::result(['id', 'seq', 'table', 'from', 'to', 'on_update', 'on_delete', 'match']); |
| 2689 | case 'foreign_keys': |
| 2690 | case 'recursive_triggers': |
| 2691 | case 'case_sensitive_like': |
| 2692 | return $arg === null ? $one($st['name'], 0) : self::result(); |
| 2693 | case 'user_version': |
| 2694 | case 'application_id': |
| 2695 | $k = $st['name'] === 'user_version' ? 'userVersion' : 'appId'; |
| 2696 | if ($arg === null) return $one($st['name'], $this->pager->hdr[$k] >= 0x80000000 ? $this->pager->hdr[$k] - 0x100000000 : $this->pager->hdr[$k]); |
| 2697 | $this->pager->hdr[$k] = ((int)Value::toInt($arg)) & 0xFFFFFFFF; |
| 2698 | $this->pager->write(1, $this->pager->read(1)); |
| 2699 | return self::result(); |
| 2700 | case 'schema_version': |
| 2701 | return $one('schema_version', $this->pager->hdr['cookie']); |
| 2702 | case 'page_size': |
| 2703 | return $one('page_size', $this->pager->pageSize); |
| 2704 | case 'page_count': |
| 2705 | return $one('page_count', $this->pager->nPages); |
| 2706 | case 'freelist_count': |
| 2707 | return $one('freelist_count', $this->pager->hdr['freeCount']); |
| 2708 | case 'journal_mode': |
| 2709 | return $one('journal_mode', $this->pager->isMemory() ? 'memory' : 'delete'); |
| 2710 | case 'encoding': |
| 2711 | return $one('encoding', 'UTF-8'); |
| 2712 | case 'database_list': |
| 2713 | return self::result(['seq', 'name', 'file'], [], [[0, 'main', $this->pager->isMemory() ? '' : $this->pager->path]]); |
| 2714 | case 'integrity_check': |
| 2715 | case 'quick_check': |
| 2716 | $msgs = (new Integrity($this->pager, $this->bt, $schema))->check(); |
| 2717 | return self::result([$st['name']], [''], array_map(fn($m) => [$m], $msgs ?: ['ok'])); |
| 2718 | case 'synchronous': |
| 2719 | case 'cache_size': |
| 2720 | case 'temp_store': |
| 2721 | case 'busy_timeout': |
| 2722 | case 'locking_mode': |
| 2723 | case 'mmap_size': |
| 2724 | case 'auto_vacuum': |
| 2725 | case 'secure_delete': |
| 2726 | return $arg === null ? $one($st['name'], 0) : self::result(); |
| 2727 | } |
| 2728 | // 知らないPRAGMAは何もしない（SQLiteと同じ） |
| 2729 | return self::result(); |
| 2730 | } |
| 2731 | } |