| 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\Convert; |
| 39 | |
| 40 | use Std\Lib\Db; |
| 41 | use Std\Lib\Db\Convert; |
| 42 | use Std\Lib\Db\DbException; |
| 43 | |
| 44 | /** |
| 45 | * データベース（MySQL・PostgreSQL・SQLite）から読む |
| 46 | * |
| 47 | * MySQLはSHOW CREATE TABLE、PostgreSQLはinformation_schemaとpg_catalog、SQLiteはsqlite_masterでテーブルの定義を読む。 |
| 48 | */ |
| 49 | final class DbSource implements Source { |
| 50 | public string $dialect; |
| 51 | private array $warnings = []; |
| 52 | private ?array $tables = null; |
| 53 | |
| 54 | /** |
| 55 | * @param Db $db 接続（まだ接続していなくても可） |
| 56 | * @param int $batch 1回に読む行の数 |
| 57 | */ |
| 58 | public function __construct(private Db $db, private int $batch = 1000) { |
| 59 | $this->dialect = self::dialectOf($db); |
| 60 | } |
| 61 | |
| 62 | public static function dialectOf(Db $db): string { |
| 63 | return match ($db->driverName) { |
| 64 | 'mysql', 'mariadb' => 'mysql', |
| 65 | 'pgsql', 'postgres', 'postgresql' => 'pgsql', |
| 66 | default => 'sqlite', |
| 67 | }; |
| 68 | } |
| 69 | |
| 70 | public function warnings(): array { |
| 71 | return array_values(array_unique($this->warnings)); |
| 72 | } |
| 73 | |
| 74 | /** |
| 75 | * テーブルの名前 |
| 76 | * |
| 77 | * @return array |
| 78 | */ |
| 79 | public static function tableNames(Db $db): array { |
| 80 | switch (self::dialectOf($db)) { |
| 81 | case 'mysql': |
| 82 | $out = []; |
| 83 | foreach ($db->query('SHOW FULL TABLES')['rows'] as $r) { |
| 84 | $v = array_values($r); |
| 85 | if (($v[1] ?? 'BASE TABLE') === 'BASE TABLE') $out[] = (string)$v[0]; |
| 86 | } |
| 87 | return $out; |
| 88 | case 'pgsql': |
| 89 | return array_column($db->query("SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema() AND table_type = 'BASE TABLE' ORDER BY table_name")['rows'], 'table_name'); |
| 90 | default: |
| 91 | return array_column($db->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite\\_%' ESCAPE '\\' ORDER BY name")['rows'], 'name'); |
| 92 | } |
| 93 | } |
| 94 | |
| 95 | public function tables(): array { |
| 96 | if ($this->tables !== null) return $this->tables; |
| 97 | $out = []; |
| 98 | foreach (self::tableNames($this->db) as $name) { |
| 99 | $t = match ($this->dialect) { |
| 100 | 'mysql' => $this->mysqlTable($name), |
| 101 | 'pgsql' => $this->pgsqlTable($name), |
| 102 | default => $this->sqliteTable($name), |
| 103 | }; |
| 104 | if ($t !== null) $out[] = $t; |
| 105 | } |
| 106 | return $this->tables = $out; |
| 107 | } |
| 108 | |
| 109 | private function mysqlTable(string $name): ?array { |
| 110 | $r = $this->db->query('SHOW CREATE TABLE '.Convert::quote('mysql', $name))['rows'][0] ?? null; |
| 111 | $sql = $r === null ? '' : (string)(array_values($r)[1] ?? ''); |
| 112 | return Sql::createTable($sql, 'mysql', $this->warnings); |
| 113 | } |
| 114 | |
| 115 | private function sqliteTable(string $name): ?array { |
| 116 | $sql = (string)($this->db->query("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?", [$name])['rows'][0]['sql'] ?? ''); |
| 117 | $t = Sql::createTable($sql, 'sqlite', $this->warnings); |
| 118 | if ($t === null) return null; |
| 119 | // INTEGER PRIMARY KEYはrowid（自動の番号） |
| 120 | if (count($t['pk']) === 1) { |
| 121 | foreach ($t['columns'] as $i => $c) { |
| 122 | if (strcasecmp($c['name'], $t['pk'][0]) === 0 && $c['type'] === 'int') { |
| 123 | $info = $this->db->query('PRAGMA table_info('.Convert::quote('sqlite', $name).')')['rows']; |
| 124 | foreach ($info as $col) { |
| 125 | if (strcasecmp($col['name'], $c['name']) === 0 && strtoupper($col['type']) === 'INTEGER') $t['columns'][$i]['autoinc'] = true; |
| 126 | } |
| 127 | } |
| 128 | } |
| 129 | } |
| 130 | foreach ($this->db->query("SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = ? AND sql IS NOT NULL ORDER BY name", [$name])['rows'] as $r) { |
| 131 | $idx = self::parseIndex((string)$r['sql'], 'sqlite'); |
| 132 | if ($idx === null) { |
| 133 | $this->warnings[] = "式・部分のインデックスは変換しません：{$r['name']}"; |
| 134 | continue; |
| 135 | } |
| 136 | $t['indexes'][] = $idx; |
| 137 | } |
| 138 | return $t; |
| 139 | } |
| 140 | |
| 141 | /** CREATE [UNIQUE] INDEX 名前 ON テーブル (列) */ |
| 142 | public static function parseIndex(string $sql, string $dialect): ?array { |
| 143 | $tok = Sql::tokens($sql, $dialect); |
| 144 | $p = 1; |
| 145 | $unique = ($tok[$p]['u'] ?? '') === 'UNIQUE'; |
| 146 | if ($unique) $p++; |
| 147 | $p++; |
| 148 | if (($tok[$p]['u'] ?? '') === 'IF') $p += 3; |
| 149 | $name = Sql::name($tok, $p); |
| 150 | $p++; |
| 151 | Sql::name($tok, $p); |
| 152 | if (($tok[$p]['u'] ?? '') === 'USING') $p += 2; |
| 153 | $cols = Sql::columnList($tok, $p); |
| 154 | if (in_array(null, $cols, true) || ($tok[$p]['u'] ?? '') === 'WHERE') return null; |
| 155 | return ['name' => $name, 'columns' => $cols, 'unique' => $unique]; |
| 156 | } |
| 157 | |
| 158 | private function pgsqlTable(string $name): array { |
| 159 | $t = ['name' => $name, 'columns' => [], 'pk' => [], 'indexes' => []]; |
| 160 | $cols = $this->db->query('SELECT column_name, data_type, udt_name, character_maximum_length, numeric_precision, numeric_scale, is_nullable, column_default, is_identity |
| 161 | FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = ? ORDER BY ordinal_position', [$name])['rows']; |
| 162 | foreach ($cols as $c) { |
| 163 | $dt = strtolower((string)$c['data_type']); |
| 164 | $type = match (true) { |
| 165 | $dt === 'array' => 'json[]', |
| 166 | $dt === 'user-defined' => (string)$c['udt_name'], |
| 167 | $dt === 'character varying' && $c['character_maximum_length'] !== null => "varchar({$c['character_maximum_length']})", |
| 168 | $dt === 'character' => 'char('.($c['character_maximum_length'] ?? 1).')', |
| 169 | $dt === 'numeric' && $c['numeric_precision'] !== null => "numeric({$c['numeric_precision']},".(int)$c['numeric_scale'].')', |
| 170 | default => $dt, |
| 171 | }; |
| 172 | $col = Convert::parseType($type, 'pgsql') + ['name' => $c['column_name'], 'notNull' => $c['is_nullable'] === 'NO', 'default' => null, 'autoinc' => $c['is_identity'] === 'YES']; |
| 173 | if (in_array($dt, ['user-defined'], true) && !in_array($c['udt_name'], ['citext', 'uuid', 'json', 'jsonb'], true)) $col['type'] = 'text'; |
| 174 | if ($c['column_default'] !== null) { |
| 175 | $d = Convert::parseDefault((string)$c['column_default']); |
| 176 | if ($d !== null && isset($d['seq'])) $col['autoinc'] = true; |
| 177 | else if ($d === null) $this->warnings[] = "DEFAULTを変換できません：{$name}.{$c['column_name']}（{$c['column_default']}）"; |
| 178 | else $col['default'] = $d; |
| 179 | } |
| 180 | $t['columns'][] = $col; |
| 181 | } |
| 182 | $idx = $this->db->query("SELECT i.relname AS name, ix.indisunique AS uniq, ix.indisprimary AS prim, ix.indexprs IS NOT NULL OR ix.indpred IS NOT NULL AS expr, |
| 183 | array_to_string(ARRAY(SELECT a.attname FROM unnest(ix.indkey) WITH ORDINALITY AS k(attnum, n) JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum ORDER BY k.n), ',') AS cols |
| 184 | FROM pg_index ix JOIN pg_class t ON t.oid = ix.indrelid JOIN pg_class i ON i.oid = ix.indexrelid JOIN pg_namespace ns ON ns.oid = t.relnamespace |
| 185 | WHERE t.relname = ? AND ns.nspname = current_schema() ORDER BY i.relname", [$name])['rows']; |
| 186 | foreach ($idx as $r) { |
| 187 | if ($r['expr']) { |
| 188 | $this->warnings[] = "式・部分のインデックスは変換しません：{$r['name']}"; |
| 189 | continue; |
| 190 | } |
| 191 | $list = explode(',', (string)$r['cols']); |
| 192 | if ($r['prim']) $t['pk'] = $list; |
| 193 | else $t['indexes'][] = ['name' => $r['name'], 'columns' => $list, 'unique' => (bool)$r['uniq']]; |
| 194 | } |
| 195 | return $t; |
| 196 | } |
| 197 | |
| 198 | /** 列毎の一番長い値（インデックスの有る文字列の列、MySQLのVARCHARの長さ用） */ |
| 199 | public function maxLengths(array $t): array { |
| 200 | $out = []; |
| 201 | $q = fn(string $n) => Convert::quote($this->dialect, $n); |
| 202 | $fn = $this->dialect === 'mysql' ? 'CHAR_LENGTH' : 'LENGTH'; |
| 203 | foreach ($t['columns'] as $c) { |
| 204 | if (!in_array($c['type'], ['text', 'varchar', 'blob'], true)) continue; |
| 205 | $out[$c['name']] = (int)(array_values($this->db->query("SELECT MAX({$fn}({$q($c['name'])})) FROM {$q($t['name'])}")['rows'][0] ?? [])[0] ?? 0); |
| 206 | } |
| 207 | return $out; |
| 208 | } |
| 209 | |
| 210 | public function rows(array $tables, callable $sink): void { |
| 211 | $q = fn(string $n) => Convert::quote($this->dialect, $n); |
| 212 | foreach ($this->tables() as $t) { |
| 213 | if (!in_array(strtolower($t['name']), $tables, true)) continue; |
| 214 | $cols = implode(', ', array_map(fn($c) => $q($c['name']), $t['columns'])); |
| 215 | $key = count($t['pk']) === 1 ? $t['pk'][0] : null; |
| 216 | $from = $q($t['name']); |
| 217 | if ($key !== null) { |
| 218 | // 主キーの順番で、少しずつ |
| 219 | $last = null; |
| 220 | while (true) { |
| 221 | $sql = "SELECT {$cols} FROM {$from}".($last !== null ? " WHERE {$q($key)} > ?" : '')." ORDER BY {$q($key)} LIMIT {$this->batch}"; |
| 222 | $rows = $this->db->query($sql, $last !== null ? [$last] : [])['rows']; |
| 223 | if (empty($rows)) break; |
| 224 | $sink($t['name'], array_map('array_values', $rows)); |
| 225 | $lastRow = end($rows); |
| 226 | $last = $lastRow[$this->keyName($rows, $key)]; |
| 227 | if (count($rows) < $this->batch) break; |
| 228 | } |
| 229 | } else { |
| 230 | $order = $t['pk'] ? ' ORDER BY '.implode(', ', array_map($q, $t['pk'])) : ''; |
| 231 | for ($off = 0; ; $off += $this->batch) { |
| 232 | $rows = $this->db->query("SELECT {$cols} FROM {$from}{$order} LIMIT {$this->batch} OFFSET {$off}")['rows']; |
| 233 | if (empty($rows)) break; |
| 234 | $sink($t['name'], array_map('array_values', $rows)); |
| 235 | if (count($rows) < $this->batch) break; |
| 236 | } |
| 237 | } |
| 238 | } |
| 239 | } |
| 240 | |
| 241 | /** 結果の行の主キーの名前（大文字と小文字） */ |
| 242 | private function keyName(array $rows, string $key): string { |
| 243 | foreach (array_keys($rows[0]) as $k) if (strcasecmp((string)$k, $key) === 0) return (string)$k; |
| 244 | return $key; |
| 245 | } |
| 246 | } |