suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Lib / Db / Sqlite / Schema.php
10.1 KB、213行 生 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\Sqlite;
39
40use Std\Lib\Db\DbException;
41
42/**
43 * データベースのスキーマ（sqlite_schemaのテーブル・インデックス・ビュー）
44 */
45final class Schema {
46 /** 小文字の名前 => テーブル */
47 public array $tables = [];
48 /** 小文字の名前 => インデックス */
49 public array $indexes = [];
50 /** 小文字の名前 => ビュー（name、sel、cols） */
51 public array $views = [];
52 /** トリガーの有るテーブル（小文字） */
53 public array $triggers = [];
54 public int $maxRowid = 0;
55
56 /**
57 * sqlite_schemaを読む
58 */
59 public static function load(Btree $bt, int $pages): self {
60 $s = new self();
61 $s->tables['sqlite_schema'] = self::schemaTable();
62 if ($pages === 0) return $s;
63 $parser = new Parser();
64 $autos = [];
65 foreach ($bt->scan(1) as $rowid => $rec) {
66 $s->maxRowid = max($s->maxRowid, $rowid);
67 [$type, $name, $tbl, $root, $sql] = Record::decode($rec) + [null, null, null, null, null];
68 $type = (string)$type;
69 $lname = strtolower((string)$name);
70 try {
71 if ($type === 'table') {
72 $st = $parser->parseAll((string)$sql)[0] ?? null;
73 if (($st['t'] ?? '') !== 'createTable' || !isset($st['def'])) throw new DbException("不正なテーブルのSQL：{$name}", 11, 'HY000');
74 $s->tables[$lname] = self::table((string)$name, (int)$root, (string)$sql, $st['def']) + ['schemaRowid' => $rowid];
75 } else if ($type === 'index') {
76 if ($sql === null) {
77 $autos[] = [(string)$name, strtolower((string)$tbl), (int)$root, $rowid];
78 continue;
79 }
80 $st = $parser->parseAll((string)$sql)[0] ?? null;
81 if (($st['t'] ?? '') !== 'createIndex') throw new DbException("不正なインデックスのSQL：{$name}", 11, 'HY000');
82 $s->indexes[$lname] = ['name' => (string)$name, 'root' => (int)$root, 'table' => strtolower((string)$tbl), 'unique' => $st['unique'],
83 'def' => $st['cols'], 'origin' => 'c', 'sql' => (string)$sql, 'schemaRowid' => $rowid];
84 } else if ($type === 'view') {
85 $st = $parser->parseAll((string)$sql)[0] ?? null;
86 if (($st['t'] ?? '') !== 'createView') throw new DbException("不正なビューのSQL：{$name}", 11, 'HY000');
87 $s->views[$lname] = ['name' => (string)$name, 'sel' => $st['sel'], 'cols' => $st['cols'], 'sql' => (string)$sql, 'schemaRowid' => $rowid];
88 } else if ($type === 'trigger') {
89 $s->triggers[strtolower((string)$tbl)] = true;
90 $s->triggers['@'.$lname] = $rowid;
91 }
92 } catch (DbException $e) {
93 if ($type === 'table' && str_contains($e->getMessage(), '未対応')) {
94 // WITHOUT ROWID等：読めないテーブルとして
95 $s->tables[$lname] = ['name' => (string)$name, 'unsupported' => $e->getMessage(), 'root' => (int)$root, 'cols' => [], 'map' => [],
96 'indexes' => [], 'schemaRowid' => $rowid, 'sql' => (string)$sql];
97 continue;
98 }
99 throw $e;
100 }
101 }
102
103 // 自動のインデックス（UNIQUE・PRIMARY KEYの制約）
104 foreach ($autos as [$name, $tbl, $root, $rowid]) {
105 $t = $s->tables[$tbl] ?? null;
106 if (!$t || !preg_match('/_(\d+)$/', $name, $m)) continue;
107 $c = $t['constraints'][(int)$m[1] - 1] ?? null;
108 if ($c === null) throw new DbException("データベースが壊れています（{$name}）", 11, 'HY000');
109 $s->indexes[strtolower($name)] = ['name' => $name, 'root' => $root, 'table' => $tbl, 'unique' => true, 'def' => $c[1],
110 'origin' => $c[0] === 'pk' ? 'pk' : 'u', 'sql' => null, 'schemaRowid' => $rowid];
111 }
112 foreach ($s->indexes as $k => $idx) {
113 if (!isset($s->tables[$idx['table']])) continue;
114 $s->indexes[$k] = self::indexInfo($idx, $s->tables[$idx['table']]);
115 $s->tables[$idx['table']]['indexes'][] = $k;
116 }
117 return $s;
118 }
119
120 /** sqlite_schemaのテーブル */
121 private static function schemaTable(): array {
122 $cols = [];
123 foreach (['type' => 'text', 'name' => 'text', 'tbl_name' => 'text', 'rootpage' => 'int', 'sql' => 'text'] as $n => $ty) {
124 $cols[] = ['name' => $n, 'type' => $ty, 'aff' => Value::affinity($ty), 'notNull' => false, 'default' => null, 'defaultText' => null,
125 'collate' => 'BINARY', 'pk' => 0];
126 }
127 return ['name' => 'sqlite_schema', 'root' => 1, 'sql' => '', 'cols' => $cols, 'map' => ['type' => 0, 'name' => 1, 'tbl_name' => 2, 'rootpage' => 3, 'sql' => 4],
128 'ipk' => null, 'autoinc' => false, 'checks' => [], 'indexes' => [], 'constraints' => [], 'system' => true];
129 }
130
131 /**
132 * テーブルの定義を作る
133 *
134 * @param array $def Parser::tableBody()の結果
135 */
136 public static function table(string $name, int $root, string $sql, array $def): array {
137 if ($def['withoutRowid']) throw new DbException("WITHOUT ROWIDのテーブルは未対応です：{$name}", 1, 'HY000');
138 $cols = [];
139 $map = [];
140 foreach ($def['cols'] as $i => $c) {
141 $lc = strtolower($c['name']);
142 if (isset($map[$lc])) throw new DbException("重複した列：{$c['name']}", 1, 'HY000');
143 $map[$lc] = $i;
144 $cols[] = ['name' => $c['name'], 'type' => $c['type'], 'aff' => Value::affinity($c['type']), 'notNull' => $c['notNull'],
145 'default' => $c['default'], 'defaultText' => $c['defaultText'], 'collate' => $c['collate'] ?? 'BINARY', 'pk' => 0,
146 'checks' => $c['checks']];
147 }
148 $ipk = null;
149 $pkCols = [];
150 if ($def['pk'] !== null) {
151 foreach ($def['pk'] as $k => $p) {
152 $ci = $map[strtolower($p['name'])] ?? null;
153 if ($ci === null) throw new DbException("主キーの列がありません：{$p['name']}", 1, 'HY000');
154 $cols[$ci]['pk'] = $k + 1;
155 $pkCols[] = $ci;
156 }
157 if (count($pkCols) === 1 && strtoupper($cols[$pkCols[0]]['type']) === 'INTEGER') {
158 // 列の「INTEGER PRIMARY KEY DESC」だけはrowidの別名にならない
159 $colDesc = false;
160 foreach ($def['cols'] as $c) if ($c['pk'] && $c['pkDesc']) $colDesc = true;
161 if (!$colDesc) $ipk = $pkCols[0];
162 }
163 }
164 if ($def['pkAuto'] && $ipk === null) throw new DbException('AUTOINCREMENTはINTEGER PRIMARY KEYだけに使えます', 1, 'HY000');
165
166 // 自動のインデックスの元になる制約（重複は1つ）
167 $constraints = [];
168 $seen = [];
169 foreach ($def['order'] as [$kind, $list]) {
170 if ($kind === 'pk' && $ipk !== null) continue;
171 $key = implode(',', array_map(fn($p) => strtolower($p['name']), $list));
172 if (isset($seen[$key])) continue;
173 $seen[$key] = true;
174 $constraints[] = [$kind, $list];
175 }
176 $checks = $def['checks'];
177 foreach ($cols as $c) foreach ($c['checks'] as $ch) $checks[] = $ch;
178 return ['name' => $name, 'root' => $root, 'sql' => $sql, 'cols' => $cols, 'map' => $map, 'ipk' => $ipk, 'autoinc' => $def['pkAuto'],
179 'checks' => $checks, 'indexes' => [], 'constraints' => $constraints];
180 }
181
182 /** インデックスの列・照合順序・降順 */
183 public static function indexInfo(array $idx, array $t): array {
184 $cols = $colls = $desc = [];
185 foreach ($idx['def'] as $d) {
186 $ci = $t['map'][strtolower($d['name'])] ?? null;
187 if ($ci === null) {
188 if (in_array(strtolower($d['name']), ['rowid', 'oid', '_rowid_'], true)) throw new DbException('rowidのインデックスは未対応です', 1, 'HY000');
189 throw new DbException("インデックスの列がありません：{$d['name']}", 1, 'HY000');
190 }
191 $cols[] = $ci;
192 $colls[] = $d['collate'] ?? $t['cols'][$ci]['collate'];
193 $desc[] = $d['desc'];
194 }
195 $colls[] = 'BINARY';
196 $desc[] = false;
197 return $idx + ['cols' => $cols, 'colls' => $colls, 'desc' => $desc];
198 }
199
200 /** テーブル（無ければnull） */
201 public function table_(string $name): ?array {
202 $l = strtolower($name);
203 if ($l === 'sqlite_master' || $l === 'sqlite_temp_master' || $l === 'sqlite_temp_schema') $l = 'sqlite_schema';
204 return $this->tables[$l] ?? null;
205 }
206
207 /** 名前が使われているか */
208 public function exists(string $name): bool {
209 $l = strtolower($name);
210 return isset($this->tables[$l]) || isset($this->indexes[$l]) || isset($this->views[$l]) || isset($this->triggers['@'.$l])
211 || $l === 'sqlite_master';
212 }
213}