suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Lib / Db / Sqlite.php
12.5 KB、327行 生 Blame 履歴
1<?php
2/*************************************************************
3# 076 License
4
5Copyright (c) テクニカル諏訪子
6
7Permission is hereby granted to any person obtaining a copy of the software
8Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
9sublicense, 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
31THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
32INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
33PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
34HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
35CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
36OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
37 */
38namespace Std\Lib\Db;
39
40use Std\Lib\Db\Sqlite\Blob;
41use Std\Lib\Db\Sqlite\Engine;
42use Std\Lib\Db\Sqlite\Pager;
43
44/**
45 * PHP純正のSQLiteドライバ（sqlite3・pdo_sqliteのモジュールが不要）
46 *
47 * SQLite 3のファイル形式をそのまま読み書きするので、sqlite3のコマンド等でも開けます（ロールバックジャーナル、UTF-8）。
48 * 対応：CREATE/DROP TABLE・INDEX・VIEW、ALTER TABLE（RENAME、ADD・DROP・RENAME COLUMN）、INSERT（OR REPLACE、
49 * ON CONFLICT、RETURNING）、UPDATE（FROM）、DELETE、SELECT（JOIN、GROUP BY、集約、サブクエリ、UNION等、WITH）、
50 * トランザクション、SAVEPOINT、PRAGMA（table_info、index_list、integrity_check、user_version等）
51 * 未対応：WALモード、トリガー、WITHOUT ROWID、仮想テーブル（FTS）、ウィンドウ関数、再帰のWITH、外部キーの確認、
52 * 部分・式のインデックス、ATTACH
53 *
54 * ロックはflock()なので、同時に書き込むのはPHPのプロセス同士だけにして下さい。
55 * プレースホルダは「?」、「?1」、「:name」。パラメータの文字列は、UTF-8でなければBLOBになる（Sqlite\Blobで明示も可）。
56 *
57 * $db = new Sqlite(['path' => ROOT.'/data/site.sqlite']);
58 * $db->connect();
59 * $id = $db->query("INSERT INTO users (name) VALUES ('諏訪子')")['insertId'];
60 */
61class Sqlite implements DbInterface {
62 public string $path;
63 private bool $readOnly;
64 private bool $create;
65 private int $busyTimeout;
66 private bool $debug;
67 private ?Pager $pager = null;
68 private ?Engine $engine = null;
69 private array $prepared = [];
70 private int $nextStmt = 1;
71
72 /**
73 * コンストラクタ
74 *
75 * @param array|null $info path（又はdbname。相対パスはROOTから。「:memory:」はメモリー）、readonly、create（無ければ作る、既定true）、
76 * busy_timeout（ロックを待つミリ秒、5000）、debug
77 * @throws \Exception $infoを省略し、DB_ENABLEDが無効な場合
78 */
79 public function __construct(?array $info = null) {
80 if ($info === null && !(defined('DB_ENABLED') && DB_ENABLED)) {
81 throw new \Exception('データベースは無効です。');
82 }
83 $info ??= DBINFO;
84 $path = (string)($info['path'] ?? $info['dbname'] ?? ':memory:');
85 if ($path === '') $path = ':memory:';
86 if ($path !== ':memory:' && !preg_match('#^([A-Za-z]:)?[/\\\\]#', $path) && defined('ROOT')) $path = ROOT.'/'.$path;
87 $this->path = $path;
88 $this->readOnly = (bool)($info['readonly'] ?? false);
89 $this->create = (bool)($info['create'] ?? true);
90 $this->busyTimeout = (int)($info['busy_timeout'] ?? $info['timeout'] ?? 5000);
91 if (isset($info['timeout']) && !isset($info['busy_timeout'])) $this->busyTimeout = (int)$info['timeout'] * 1000;
92 $this->debug = (bool)($info['debug'] ?? false);
93 }
94
95 public function __destruct() {
96 $this->close();
97 }
98
99 public function setDebug(bool $debug): Sqlite {
100 $this->debug = $debug;
101 return $this;
102 }
103
104 /**
105 * ファイルを開く
106 *
107 * @return bool
108 * @throws DbException
109 */
110 public function connect(): bool {
111 if ($this->engine !== null) return true;
112 try {
113 $this->pager = new Pager($this->path, $this->readOnly, $this->create, $this->busyTimeout);
114 $this->engine = new Engine($this->pager);
115 // ファイルの確認（壊れたファイル・SQLiteでないファイル）
116 $this->pager->beginRead();
117 $this->engine->schema();
118 $this->pager->endRead();
119 } catch (DbException $e) {
120 $this->pager?->close();
121 $this->pager = null;
122 $this->engine = null;
123 $this->log($e->getMessage());
124 throw $e;
125 }
126 return true;
127 }
128
129 public function close(): void {
130 $this->pager?->close();
131 $this->pager = null;
132 $this->engine = null;
133 $this->prepared = [];
134 }
135
136 public function isConnected(): bool {
137 return $this->engine !== null;
138 }
139
140 public function ping(): bool {
141 return $this->engine !== null && ($this->path === ':memory:' || is_file($this->path));
142 }
143
144 /**
145 * SQLを実行する（セミコロンで区切った複数の文も可）
146 *
147 * @return array fields、rows、affectedRows、insertId、warningCount（、more）
148 * @throws DbException
149 */
150 public function query(string $query): array {
151 $this->requireConnection();
152 try {
153 $stmts = $this->engine->prepare($query);
154 } catch (DbException $e) {
155 $this->fail('クエリに失敗', $e);
156 }
157 if (empty($stmts)) return self::emptyResult();
158 $results = [];
159 // パラメータはNULL（SQLiteと同じ）
160 foreach ($stmts as $st) $results[] = $this->runOne($st, [], 'クエリに失敗');
161 $first = array_shift($results);
162 if (!empty($results)) $first['more'] = $results;
163 return $first;
164 }
165
166 /**
167 * プリペアドステートメント
168 *
169 * @return int ステートメントID
170 * @throws DbException
171 */
172 public function prepare(string $query): int {
173 $this->requireConnection();
174 try {
175 $stmts = $this->engine->prepare($query);
176 } catch (DbException $e) {
177 $this->fail('準備に失敗', $e);
178 }
179 if (empty($stmts)) $this->fail('準備に失敗', new DbException('空のSQL', 1, 'HY000'));
180 $id = $this->nextStmt++;
181 $this->prepared[$id] = $stmts;
182 return $id;
183 }
184
185 /**
186 * プリペアドステートメントの実行
187 *
188 * @param int $statementId
189 * @param array $params 順番の配列、又は名前（「:name」の「name」）の連想配列
190 * @return array
191 * @throws DbException
192 */
193 public function execute(int $statementId, array $params = []): array {
194 $this->requireConnection();
195 if (!isset($this->prepared[$statementId])) $this->fail('実行に失敗', new DbException("不正なステートメントID: {$statementId}", 1, 'HY000'));
196 $results = [];
197 foreach ($this->prepared[$statementId] as $st) {
198 $results[] = $this->runOne($st, $this->bind($st, $params), '実行に失敗');
199 }
200 $first = array_shift($results);
201 if (!empty($results)) $first['more'] = $results;
202 return $first;
203 }
204
205 public function demolish(int $statementId): bool {
206 if (!isset($this->prepared[$statementId])) return false;
207 unset($this->prepared[$statementId]);
208 return true;
209 }
210
211 public function beginTransaction(): bool {
212 $this->query('BEGIN');
213 return true;
214 }
215
216 public function commit(): bool {
217 $this->query('COMMIT');
218 return true;
219 }
220
221 public function rollback(): bool {
222 $this->query('ROLLBACK');
223 return true;
224 }
225
226 public function inTransaction(): bool {
227 return $this->engine !== null && $this->engine->inTransaction();
228 }
229
230 public function quoteIdentifier(string $name): string {
231 return '"'.str_replace('"', '""', $name).'"';
232 }
233
234 /** 最後に入れた行のrowid */
235 public function lastInsertId(): int {
236 return $this->engine?->lastInsertRowid ?? 0;
237 }
238
239 /** 情報：version、path、pageSize、pages、journal */
240 public function getServerInfo(): array {
241 return ['version' => '3.45.1', 'path' => $this->path, 'pageSize' => $this->pager?->pageSize, 'pages' => $this->pager?->nPages,
242 'readOnly' => $this->pager?->readOnly ?? $this->readOnly];
243 }
244
245 ///////////////
246
247 private static function emptyResult(): array {
248 return ['fields' => [], 'rows' => [], 'affectedRows' => 0, 'insertId' => 0, 'warningCount' => 0];
249 }
250
251 /** パラメータを番号（1から）の配列にする */
252 private function bind(array $st, array $params): array {
253 $out = [];
254 $isList = array_is_list($params);
255 if ($isList) {
256 if (count($params) !== $st['nParam']) {
257 $this->fail('実行に失敗', new DbException("パラメータ数が一致しません: 期待 {$st['nParam']}, 取得 ".count($params), 1, 'HY000'));
258 }
259 foreach ($params as $i => $v) $out[$i + 1] = $this->value($v, $i);
260 return $out;
261 }
262 foreach ($params as $k => $v) {
263 if (is_int($k)) {
264 $out[$k + 1] = $this->value($v, $k);
265 continue;
266 }
267 $name = in_array($k[0] ?? '', [':', '@', '$'], true) ? $k : null;
268 $i = null;
269 foreach ($name !== null ? [$name] : [':'.$k, '@'.$k, '$'.$k] as $n) {
270 if (isset($st['names'][$n])) $i = $st['names'][$n];
271 }
272 if ($i === null) $this->fail('実行に失敗', new DbException("パラメータがありません：{$k}", 1, 'HY000'));
273 $out[$i] = $this->value($v, $k);
274 }
275 return $out;
276 }
277
278 private function value(mixed $v, int|string $i): mixed {
279 if ($v instanceof \BackedEnum) $v = $v->value;
280 if ($v instanceof \DateTimeInterface) $v = $v->format('u') === '000000' ? $v->format('Y-m-d H:i:s') : $v->format('Y-m-d H:i:s.v');
281 if ($v === null || is_int($v) || is_float($v) || $v instanceof Blob) return $v;
282 if (is_bool($v)) return (int)$v;
283 if (is_string($v)) return mb_check_encoding($v, 'UTF-8') ? $v : new Blob($v);
284 if ($v instanceof \Stringable) return (string)$v;
285 $this->fail('実行に失敗', new DbException("パラメータ{$i}の型は未対応です: ".get_debug_type($v), 1, 'HY000'));
286 }
287
288 private function runOne(array $st, array $params, string $what): array {
289 if ($this->debug) echo "=== SQLite === {$st['sql']}\n";
290 $before = $this->engine->lastInsertRowid;
291 try {
292 $res = $this->engine->run($st, $params);
293 } catch (DbException $e) {
294 $this->fail($what, $e);
295 }
296 $fields = [];
297 foreach ($res['cols'] as $i => $n) $fields[] = ['name' => $n, 'table' => $res['tables'][$i] ?? '', 'type' => $res['types'][$i] ?? ''];
298 $rows = [];
299 foreach ($res['rows'] as $vals) {
300 $row = [];
301 foreach ($res['cols'] as $i => $n) {
302 $v = $vals[$i] ?? null;
303 $row[$n] = $v instanceof Blob ? $v->data : $v;
304 }
305 $rows[] = $row;
306 }
307 $isInsert = $st['t'] === 'insert';
308 return ['fields' => $fields, 'rows' => $rows, 'affectedRows' => in_array($st['t'], ['insert', 'update', 'delete'], true) ? $res['changes'] : count($rows),
309 'insertId' => $isInsert && $this->engine->lastInsertRowid !== $before ? $this->engine->lastInsertRowid : ($isInsert ? $this->engine->lastInsertRowid : 0),
310 'warningCount' => 0];
311 }
312
313 private function requireConnection(): void {
314 if ($this->engine === null) $this->fail('', new DbException('接続していません（先にconnect()を呼んで下さい）', 0, 'HY000'));
315 }
316
317 /** @return never */
318 private function fail(string $what, DbException $e): never {
319 $msg = $what === '' ? $e->getMessage() : "{$what}: ".$e->getMessage();
320 $this->log($msg);
321 throw new DbException($msg, $e->getCode(), $e->sqlState, $e);
322 }
323
324 private function log(string $msg): void {
325 if (function_exists('logger')) logger(\LogType::Db, $msg);
326 }
327}