| 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; |
| 39 | |
| 40 | use Std\Lib\Db\Convert\Source; |
| 41 | use Std\Lib\Db\Convert\Target; |
| 42 | use Std\Lib\Db\Sqlite\Blob; |
| 43 | |
| 44 | /** |
| 45 | * データベースの変換（MySQL・PostgreSQL・SQLite・SQLのダンプ・LittleBeastのJSONのデータ） |
| 46 | * |
| 47 | * Source（DbSource、DumpSource、JsonSource）から読んで、Target（DbTarget、DumpTarget、JsonTarget）に書く。 |
| 48 | * テーブルの定義は共通の形にしてから、書く先の方言のCREATE TABLEにする： |
| 49 | * name、columns（name、type、size、scale、unsigned、notNull、default、autoinc、tz、collate）、pk（列の名前）、 |
| 50 | * indexes（name、columns、unique） |
| 51 | * 共通の型：int（bits：16、32、64）、real、decimal、varchar、char、text、blob、bool、date、datetime（tz）、time、json、uuid |
| 52 | * 値：null、int、float、string、bool、Sqlite\Blob（バイナリ）。日付は「Y-m-d H:i:s」（時差が有ればUTC）。 |
| 53 | * 外部キー、ビュー、トリガー、関数は変換しない。 |
| 54 | * |
| 55 | * $report = Convert::run(new DbSource(new Db(['driver' => 'pgsql', …])), new DbTarget(new Db(['driver' => 'sqlite', 'path' => 'data/x.sqlite']))); |
| 56 | */ |
| 57 | final class Convert { |
| 58 | public const TYPES = ['int', 'real', 'decimal', 'varchar', 'char', 'text', 'blob', 'bool', 'date', 'datetime', 'time', 'json', 'uuid']; |
| 59 | |
| 60 | /** |
| 61 | * 変換する |
| 62 | * |
| 63 | * @param Source $src |
| 64 | * @param Target $dst |
| 65 | * @param array $opt tables（変換するテーブルの名前の配列、nullは全部）、drop（書く先の同じ名前のテーブルを消す）、 |
| 66 | * progress（fn(string $table, int $rows)） |
| 67 | * @return array tables（名前 => 行数）、warnings |
| 68 | * @throws \Throwable 失敗した場合（書く先は途中までの状態） |
| 69 | */ |
| 70 | public static function run(Source $src, Target $dst, array $opt = []): array { |
| 71 | $only = isset($opt['tables']) ? array_map('strtolower', (array)$opt['tables']) : null; |
| 72 | $progress = $opt['progress'] ?? null; |
| 73 | $warnings = []; |
| 74 | $tables = []; |
| 75 | foreach ($src->tables() as $t) { |
| 76 | if ($only !== null && !in_array(strtolower($t['name']), $only, true)) continue; |
| 77 | $tables[strtolower($t['name'])] = self::normalizeTable($t); |
| 78 | } |
| 79 | // MySQLのインデックスの有るTEXT・BLOBは長さが必要（VARCHAR・VARBINARYにする） |
| 80 | if (property_exists($dst, 'dialect') && $dst->dialect === 'mysql' && method_exists($src, 'maxLengths')) { |
| 81 | foreach ($tables as $k => $t) { |
| 82 | $indexed = array_map('strtolower', array_merge($t['pk'], ...array_map(fn($i) => $i['columns'], $t['indexes'] ?: [['columns' => []]]))); |
| 83 | $need = false; |
| 84 | foreach ($t['columns'] as $c) if (in_array(strtolower($c['name']), $indexed, true) && in_array($c['type'], ['text', 'blob'], true)) $need = true; |
| 85 | if (!$need) continue; |
| 86 | $lens = $src->maxLengths($t); |
| 87 | foreach ($t['columns'] as $i => $c) { |
| 88 | if (in_array(strtolower($c['name']), $indexed, true) && in_array($c['type'], ['text', 'blob'], true)) { |
| 89 | $len = (int)($lens[$c['name']] ?? 0); |
| 90 | if ($len > ($c['type'] === 'text' ? 768 : 3072)) $warnings[] = "インデックスには長過ぎる値が有ります：{$t['name']}.{$c['name']}（{$len}）"; |
| 91 | $tables[$k]['columns'][$i]['size'] = max($c['size'] ?? 0, $len); |
| 92 | } |
| 93 | } |
| 94 | } |
| 95 | } |
| 96 | $counts = array_fill_keys(array_map(fn($t) => $t['name'], $tables), 0); |
| 97 | try { |
| 98 | $dst->begin(!empty($opt['drop'])); |
| 99 | foreach ($tables as $t) $dst->createTable($t); |
| 100 | $src->rows(array_keys($tables), function(string $name, array $rows) use ($tables, $dst, &$counts, $progress): void { |
| 101 | $t = $tables[strtolower($name)] ?? null; |
| 102 | if ($t === null || empty($rows)) return; |
| 103 | $norm = []; |
| 104 | foreach ($rows as $r) $norm[] = self::normalizeRow($t, $r); |
| 105 | try { |
| 106 | $dst->insertRows($t, $norm); |
| 107 | } catch (DbException $e) { |
| 108 | throw new DbException("{$t['name']}の行（".($counts[$t['name']] + 1).'行目から）：'.$e->getMessage(), $e->getCode(), $e->sqlState, $e); |
| 109 | } |
| 110 | $counts[$t['name']] += count($norm); |
| 111 | if ($progress) $progress($t['name'], $counts[$t['name']]); |
| 112 | }); |
| 113 | foreach ($tables as $t) $dst->finishTable($t); |
| 114 | $dst->end(); |
| 115 | } catch (\Throwable $e) { |
| 116 | $dst->abort(); |
| 117 | throw $e; |
| 118 | } |
| 119 | return ['tables' => $counts, 'warnings' => array_values(array_unique(array_merge($warnings, $src->warnings(), $dst->warnings())))]; |
| 120 | } |
| 121 | |
| 122 | /** テーブルの定義を確認して、足りない値を補う */ |
| 123 | public static function normalizeTable(array $t): array { |
| 124 | $t += ['pk' => [], 'indexes' => []]; |
| 125 | $names = []; |
| 126 | foreach ($t['columns'] as $i => $c) { |
| 127 | $c += ['size' => null, 'scale' => null, 'unsigned' => false, 'notNull' => false, 'default' => null, 'autoinc' => false, 'tz' => false, 'collate' => null, 'array' => false, 'binary' => false]; |
| 128 | if ($c['type'] === 'int') $c['size'] ??= 64; |
| 129 | if (!in_array($c['type'], self::TYPES, true)) throw new \InvalidArgumentException("不明な型：{$c['type']}（{$t['name']}.{$c['name']}）"); |
| 130 | $t['columns'][$i] = $c; |
| 131 | $names[strtolower($c['name'])] = true; |
| 132 | } |
| 133 | foreach ($t['pk'] as $n) if (!isset($names[strtolower($n)])) throw new \InvalidArgumentException("主キーの列がありません：{$t['name']}.{$n}"); |
| 134 | foreach ($t['indexes'] as $k => $idx) { |
| 135 | $t['indexes'][$k] += ['unique' => false]; |
| 136 | foreach ($idx['columns'] as $n) if (!isset($names[strtolower($n)])) throw new \InvalidArgumentException("インデックスの列がありません：{$t['name']}.{$n}"); |
| 137 | } |
| 138 | return $t; |
| 139 | } |
| 140 | |
| 141 | /** |
| 142 | * 行の値を共通の形にする（型に合わせる） |
| 143 | * |
| 144 | * @param array $t テーブル |
| 145 | * @param array $row 列の順番の値 |
| 146 | * @return array |
| 147 | */ |
| 148 | public static function normalizeRow(array $t, array $row): array { |
| 149 | $out = []; |
| 150 | foreach ($t['columns'] as $i => $c) { |
| 151 | $v = $row[$i] ?? null; |
| 152 | $out[] = $v === null ? null : self::normalizeValue($v, $c); |
| 153 | } |
| 154 | return $out; |
| 155 | } |
| 156 | |
| 157 | public static function normalizeValue(mixed $v, array $c): mixed { |
| 158 | if (is_array($v)) $v = json_encode($v, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES); |
| 159 | switch ($c['type']) { |
| 160 | case 'bool': |
| 161 | if (is_bool($v)) return $v; |
| 162 | if (is_string($v)) { |
| 163 | $l = strtolower(trim($v)); |
| 164 | if (in_array($l, ['t', 'true', 'y', 'yes', 'on', '1'], true)) return true; |
| 165 | if (in_array($l, ['f', 'false', 'n', 'no', 'off', '0', ''], true)) return false; |
| 166 | } |
| 167 | return (bool)(is_object($v) ? (string)$v : $v); |
| 168 | case 'int': |
| 169 | if (is_int($v)) return $v; |
| 170 | if (is_bool($v)) return (int)$v; |
| 171 | if (is_float($v) && floor($v) === $v && abs($v) < 9.2e18) return (int)$v; |
| 172 | if (is_string($v) && preg_match('/^\s*[+-]?\d+\s*$/', $v) && (string)(int)trim($v) === ltrim(trim($v), '+')) return (int)trim($v); |
| 173 | return is_object($v) ? (string)$v : $v; |
| 174 | case 'real': |
| 175 | if (is_float($v) || is_int($v)) return (float)$v; |
| 176 | if (is_string($v) && is_numeric(trim($v))) return (float)trim($v); |
| 177 | if (is_string($v) && in_array(strtolower($v), ['nan', 'infinity', '-infinity', 'inf', '-inf'], true)) return match (strtolower($v)) { 'nan' => NAN, 'infinity', 'inf' => INF, default => -INF }; |
| 178 | return is_object($v) ? (string)$v : $v; |
| 179 | case 'decimal': |
| 180 | if (is_float($v)) return rtrim(rtrim(sprintf('%.'.max(0, (int)($c['scale'] ?? 10)).'F', $v), '0'), '.') ?: '0'; |
| 181 | return is_bool($v) ? (string)(int)$v : (string)$v; |
| 182 | case 'blob': |
| 183 | if ($v instanceof Blob) return $v; |
| 184 | return new Blob(is_bool($v) ? (string)(int)$v : (string)$v); |
| 185 | case 'datetime': |
| 186 | case 'date': |
| 187 | case 'time': |
| 188 | return self::normalizeTime((string)$v, $c['type']); |
| 189 | default: |
| 190 | if ($v instanceof Blob) return $v->data; |
| 191 | if (is_bool($v)) return $c['type'] === 'json' ? ($v ? 'true' : 'false') : (string)(int)$v; |
| 192 | if (is_float($v)) return Sqlite\Value::realToText($v); |
| 193 | return (string)$v; |
| 194 | } |
| 195 | } |
| 196 | |
| 197 | /** 日付・時間の文字列（時差が有ればUTCにする、0000-00-00はnull） */ |
| 198 | public static function normalizeTime(string $v, string $type): ?string { |
| 199 | $v = trim($v); |
| 200 | if ($v === '' || str_starts_with($v, '0000-00-00')) return $type === 'time' ? $v : null; |
| 201 | if ($type === 'time') return $v; |
| 202 | if (preg_match('/^(\d{4}-\d{2}-\d{2})(?:[ T](\d{2}:\d{2}(?::\d{2})?)(\.\d+)?)?\s*(Z|[+-]\d{2}(?::?\d{2})?)?$/i', $v, $m)) { |
| 203 | if ($type === 'date') return $m[1]; |
| 204 | $time = ($m[2] ?? '') === '' ? '00:00:00' : (strlen($m[2]) === 5 ? $m[2].':00' : $m[2]); |
| 205 | // 「.000000」（MySQLのDATETIME(6)）は無し |
| 206 | $frac = rtrim($m[3] ?? '', '0'); |
| 207 | if ($frac === '.') $frac = ''; |
| 208 | if (!empty($m[4])) { |
| 209 | $d = new \DateTimeImmutable($m[1].' '.$time.' '.(strtoupper($m[4]) === 'Z' ? '+00:00' : (strlen($m[4]) === 3 ? $m[4].':00' : $m[4]))); |
| 210 | $d = $d->setTimezone(new \DateTimeZone('UTC')); |
| 211 | return $d->format('Y-m-d H:i:s').$frac; |
| 212 | } |
| 213 | return $m[1].' '.$time.$frac; |
| 214 | } |
| 215 | // 数値（UNIXの秒） |
| 216 | if (is_numeric($v)) return gmdate($type === 'date' ? 'Y-m-d' : 'Y-m-d H:i:s', (int)$v); |
| 217 | return $v; |
| 218 | } |
| 219 | |
| 220 | /////////////// |
| 221 | // 方言 |
| 222 | |
| 223 | /** 識別子を囲む */ |
| 224 | public static function quote(string $dialect, string $name): string { |
| 225 | if ($dialect === 'mysql') return '`'.str_replace('`', '``', $name).'`'; |
| 226 | return '"'.str_replace('"', '""', $name).'"'; |
| 227 | } |
| 228 | |
| 229 | /** |
| 230 | * 列の型（方言のSQL） |
| 231 | */ |
| 232 | public static function columnType(string $dialect, array $c, bool $indexed = false): string { |
| 233 | $size = $c['size']; |
| 234 | switch ($dialect) { |
| 235 | case 'mysql': |
| 236 | return match ($c['type']) { |
| 237 | 'int' => ($c['size'] <= 16 ? 'SMALLINT' : ($c['size'] <= 32 ? 'INT' : 'BIGINT')).($c['unsigned'] ? ' UNSIGNED' : ''), |
| 238 | 'real' => 'DOUBLE', |
| 239 | 'decimal' => $size ? 'DECIMAL('.min(65, $size).','.min(30, (int)$c['scale']).')' : 'DECIMAL(65,30)', |
| 240 | 'varchar' => $size && $size <= 16383 ? "VARCHAR({$size})" : ($indexed ? 'VARCHAR(768)' : 'LONGTEXT'), |
| 241 | 'char' => $size && $size <= 255 ? "CHAR({$size})" : 'VARCHAR(255)', |
| 242 | 'text' => $indexed ? 'VARCHAR('.max(255, min(768, (int)$size)).')' : 'LONGTEXT', |
| 243 | 'blob' => $indexed ? 'VARBINARY('.max(255, min(3072, (int)$size)).')' : 'LONGBLOB', |
| 244 | 'bool' => 'TINYINT(1)', |
| 245 | 'date' => 'DATE', |
| 246 | 'datetime' => 'DATETIME(6)', |
| 247 | 'time' => 'TIME(6)', |
| 248 | 'json' => 'JSON', |
| 249 | 'uuid' => 'CHAR(36)', |
| 250 | }; |
| 251 | case 'pgsql': |
| 252 | return match ($c['type']) { |
| 253 | 'int' => $c['size'] <= 16 ? ($c['unsigned'] ? 'INTEGER' : 'SMALLINT') : ($c['size'] <= 32 ? ($c['unsigned'] ? 'BIGINT' : 'INTEGER') : ($c['unsigned'] ? 'NUMERIC(20)' : 'BIGINT')), |
| 254 | 'real' => 'DOUBLE PRECISION', |
| 255 | 'decimal' => $size ? "NUMERIC({$size},".(int)$c['scale'].')' : 'NUMERIC', |
| 256 | 'varchar' => $size ? "VARCHAR({$size})" : 'TEXT', |
| 257 | 'char' => $size ? "CHAR({$size})" : 'TEXT', |
| 258 | 'text' => 'TEXT', |
| 259 | 'blob' => 'BYTEA', |
| 260 | 'bool' => 'BOOLEAN', |
| 261 | 'date' => 'DATE', |
| 262 | 'datetime' => $c['tz'] ? 'TIMESTAMPTZ' : 'TIMESTAMP', |
| 263 | 'time' => 'TIME', |
| 264 | 'json' => !empty($c['binary']) ? 'JSONB' : 'JSON', |
| 265 | 'uuid' => 'UUID', |
| 266 | }; |
| 267 | default: |
| 268 | return match ($c['type']) { |
| 269 | 'int' => 'INTEGER', |
| 270 | 'real' => 'REAL', |
| 271 | 'decimal' => $size ? "NUMERIC({$size},".(int)$c['scale'].')' : 'NUMERIC', |
| 272 | 'varchar' => $size ? "VARCHAR({$size})" : 'TEXT', |
| 273 | 'char' => $size ? "CHAR({$size})" : 'TEXT', |
| 274 | 'text' => 'TEXT', |
| 275 | 'blob' => 'BLOB', |
| 276 | 'bool' => 'BOOLEAN', |
| 277 | 'date' => 'DATE', |
| 278 | 'datetime' => $c['tz'] ? 'TIMESTAMPTZ' : 'DATETIME', |
| 279 | 'time' => 'TIME', |
| 280 | 'json' => 'JSON', |
| 281 | 'uuid' => 'UUID', |
| 282 | }; |
| 283 | } |
| 284 | } |
| 285 | |
| 286 | /** |
| 287 | * CREATE TABLE（インデックスは別） |
| 288 | * |
| 289 | * @param string $dialect mysql、pgsql、sqlite |
| 290 | * @param array $t テーブル |
| 291 | * @param array $warnings 警告を足す |
| 292 | * @return string |
| 293 | */ |
| 294 | public static function createTable(string $dialect, array $t, array &$warnings = []): string { |
| 295 | $q = fn(string $n) => self::quote($dialect, $n); |
| 296 | $indexed = []; |
| 297 | foreach ($t['pk'] as $n) $indexed[strtolower($n)] = true; |
| 298 | foreach ($t['indexes'] as $idx) foreach ($idx['columns'] as $n) $indexed[strtolower($n)] = true; |
| 299 | $single = count($t['pk']) === 1 ? strtolower($t['pk'][0]) : null; |
| 300 | $lines = []; |
| 301 | $pkInline = false; |
| 302 | foreach ($t['columns'] as $c) { |
| 303 | $isPk = $single !== null && strtolower($c['name']) === $single; |
| 304 | $auto = $c['autoinc'] && $isPk && $c['type'] === 'int'; |
| 305 | if ($c['autoinc'] && !$auto) $warnings[] = "自動の番号は主キーの整数の列だけです：{$t['name']}.{$c['name']}"; |
| 306 | if ($dialect === 'sqlite' && $auto) { |
| 307 | $lines[] = $q($c['name']).' INTEGER PRIMARY KEY'; |
| 308 | $pkInline = true; |
| 309 | continue; |
| 310 | } |
| 311 | if ($dialect === 'pgsql' && $auto) { |
| 312 | $lines[] = $q($c['name']).' '.($c['size'] <= 32 && !$c['unsigned'] ? 'SERIAL' : 'BIGSERIAL').' PRIMARY KEY'; |
| 313 | $pkInline = true; |
| 314 | continue; |
| 315 | } |
| 316 | $line = $q($c['name']).' '.self::columnType($dialect, $c, isset($indexed[strtolower($c['name'])])); |
| 317 | if ($dialect === 'mysql' && $c['collate'] && in_array($c['type'], ['varchar', 'char', 'text'], true)) $line .= ' COLLATE '.$c['collate']; |
| 318 | if ($c['notNull'] || ($isPk && $dialect !== 'sqlite')) $line .= ' NOT NULL'; |
| 319 | if ($dialect === 'mysql' && $auto) $line .= ' AUTO_INCREMENT'; |
| 320 | if ($c['default'] !== null) { |
| 321 | $d = self::defaultSql($dialect, $c); |
| 322 | if ($d === null) $warnings[] = "DEFAULTを変換できません：{$t['name']}.{$c['name']}"; |
| 323 | else $line .= ' DEFAULT '.$d; |
| 324 | } |
| 325 | $lines[] = $line; |
| 326 | } |
| 327 | if (!$pkInline && $t['pk']) $lines[] = 'PRIMARY KEY ('.implode(', ', array_map($q, $t['pk'])).')'; |
| 328 | $sql = 'CREATE TABLE '.$q($t['name'])." (\n ".implode(",\n ", $lines)."\n)"; |
| 329 | if ($dialect === 'mysql') $sql .= ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE='.($t['collate'] ?? 'utf8mb4_bin'); |
| 330 | return $sql; |
| 331 | } |
| 332 | |
| 333 | /** DEFAULTの値（変換できなければnull） */ |
| 334 | private static function defaultSql(string $dialect, array $c): ?string { |
| 335 | $d = $c['default']; |
| 336 | if (array_key_exists('now', $d)) { |
| 337 | if (!in_array($c['type'], ['datetime', 'date', 'time'], true)) return null; |
| 338 | if ($dialect === 'mysql' && $c['type'] !== 'datetime') return null; |
| 339 | return $dialect === 'mysql' ? 'CURRENT_TIMESTAMP(6)' : ($c['type'] === 'date' ? 'CURRENT_DATE' : ($c['type'] === 'time' ? 'CURRENT_TIME' : 'CURRENT_TIMESTAMP')); |
| 340 | } |
| 341 | if (!array_key_exists('value', $d)) return null; |
| 342 | $v = $d['value']; |
| 343 | if ($v === null) return 'NULL'; |
| 344 | // MySQLのTEXT・BLOBにはDEFAULTを付けられない |
| 345 | if ($dialect === 'mysql' && in_array($c['type'], ['text', 'blob', 'json'], true)) return null; |
| 346 | try { |
| 347 | $v = self::normalizeValue($v, $c); |
| 348 | } catch (\Throwable $e) { |
| 349 | return null; |
| 350 | } |
| 351 | if ($v === null) return 'NULL'; |
| 352 | return self::literal($dialect, $v, $c); |
| 353 | } |
| 354 | |
| 355 | /** |
| 356 | * 値のSQLのリテラル |
| 357 | */ |
| 358 | public static function literal(string $dialect, mixed $v, ?array $c = null): string { |
| 359 | if ($v === null) return 'NULL'; |
| 360 | if (is_bool($v)) return $dialect === 'pgsql' ? ($v ? 'TRUE' : 'FALSE') : ($v ? '1' : '0'); |
| 361 | if (is_int($v)) return (string)$v; |
| 362 | if (is_float($v)) { |
| 363 | if (is_nan($v) || is_infinite($v)) { |
| 364 | if ($dialect === 'pgsql') return "'".(is_nan($v) ? 'NaN' : ($v > 0 ? 'Infinity' : '-Infinity'))."'"; |
| 365 | return $dialect === 'sqlite' && is_infinite($v) ? ($v > 0 ? '9e999' : '-9e999') : 'NULL'; |
| 366 | } |
| 367 | $s = var_export($v, true); |
| 368 | return str_contains($s, '.') || str_contains($s, 'E') ? $s : $s.'.0'; |
| 369 | } |
| 370 | if ($v instanceof Blob) { |
| 371 | $hex = bin2hex($v->data); |
| 372 | if ($dialect === 'pgsql') return "'\\x{$hex}'::bytea"; |
| 373 | return $dialect === 'mysql' ? ($hex === '' ? "''" : "0x{$hex}") : "X'{$hex}'"; |
| 374 | } |
| 375 | $s = (string)$v; |
| 376 | if ($dialect === 'mysql') return "'".strtr($s, ["\\" => "\\\\", "'" => "\\'", "\0" => "\\0", "\n" => "\\n", "\r" => "\\r", "\x1A" => "\\Z"])."'"; |
| 377 | return "'".str_replace("'", "''", $s)."'"; |
| 378 | } |
| 379 | |
| 380 | /** インデックスの名前（PostgreSQL・SQLiteはデータベース全体で一意なので、テーブルの名前を付ける） */ |
| 381 | public static function indexName(string $dialect, array $t, array $idx): string { |
| 382 | $n = $idx['name'] !== '' && $idx['name'] !== null ? $idx['name'] : $t['name'].'_'.implode('_', $idx['columns']).'_idx'; |
| 383 | if ($dialect !== 'mysql' && stripos($n, $t['name']) !== 0) $n = $t['name'].'_'.$n; |
| 384 | return $n; |
| 385 | } |
| 386 | |
| 387 | /** CREATE INDEX */ |
| 388 | public static function createIndex(string $dialect, array $t, array $idx): string { |
| 389 | $q = fn(string $n) => self::quote($dialect, $n); |
| 390 | return 'CREATE '.($idx['unique'] ? 'UNIQUE ' : '').'INDEX '.$q(self::indexName($dialect, $t, $idx)).' ON '.$q($t['name']) |
| 391 | .' ('.implode(', ', array_map($q, $idx['columns'])).')'; |
| 392 | } |
| 393 | |
| 394 | /////////////// |
| 395 | // 型の名前から共通の型 |
| 396 | |
| 397 | /** |
| 398 | * 型の名前（どの方言でも）を共通の型にする |
| 399 | * |
| 400 | * @param string $type 例：varchar(255)、int unsigned、timestamp with time zone、TINYINT(1) |
| 401 | * @return array type、size、scale、unsigned、tz |
| 402 | */ |
| 403 | public static function parseType(string $type, string $dialect = ''): array { |
| 404 | $t = strtolower(trim($type)); |
| 405 | $t = preg_replace('/\s+/', ' ', $t); |
| 406 | $unsigned = (bool)preg_match('/\bunsigned\b/', $t); |
| 407 | $t = trim(preg_replace('/\b(unsigned|zerofill|signed)\b/', '', $t)); |
| 408 | $array = str_ends_with($t, '[]'); |
| 409 | if ($array) return ['type' => 'json', 'size' => null, 'scale' => null, 'unsigned' => false, 'tz' => false, 'array' => true]; |
| 410 | $args = []; |
| 411 | if (preg_match('/\(([^)]*)\)/', $t, $m)) $args = array_map('trim', explode(',', $m[1])); |
| 412 | $base = trim(preg_replace('/\([^)]*\)/', '', $t)); |
| 413 | $size = isset($args[0]) && is_numeric($args[0]) ? (int)$args[0] : null; |
| 414 | $scale = isset($args[1]) && is_numeric($args[1]) ? (int)$args[1] : null; |
| 415 | $r = fn(string $type, ?int $size = null, ?int $scale = null, bool $tz = false) => ['type' => $type, 'size' => $size, 'scale' => $scale, 'unsigned' => $unsigned, 'tz' => $tz]; |
| 416 | switch (true) { |
| 417 | case $base === 'tinyint' && $size === 1: |
| 418 | case in_array($base, ['bool', 'boolean', 'bit'], true) && ($base !== 'bit' || ($size ?? 1) === 1): |
| 419 | return $r('bool'); |
| 420 | case in_array($base, ['tinyint', 'smallint', 'int2', 'smallserial', 'serial2'], true): |
| 421 | return $r('int', $base === 'tinyint' && $unsigned ? 16 : ($unsigned ? 32 : 16)); |
| 422 | case in_array($base, ['mediumint', 'int', 'integer', 'int4', 'serial', 'serial4'], true): |
| 423 | return $r('int', $unsigned || $base === 'integer' && $dialect === 'sqlite' ? 64 : 32); |
| 424 | case in_array($base, ['bigint', 'int8', 'bigserial', 'serial8'], true) || $base === 'year': |
| 425 | return $r('int', $base === 'year' ? 16 : 64); |
| 426 | case in_array($base, ['float', 'double', 'double precision', 'real', 'float4', 'float8'], true): |
| 427 | return $r('real'); |
| 428 | case in_array($base, ['decimal', 'numeric', 'dec', 'fixed', 'money'], true): |
| 429 | return $r('decimal', $size, $scale); |
| 430 | case in_array($base, ['varchar', 'character varying', 'nvarchar', 'varchar2', 'varying character', 'native character', 'nchar varying'], true): |
| 431 | return $r('varchar', $size); |
| 432 | case in_array($base, ['char', 'character', 'nchar', 'bpchar'], true): |
| 433 | return $r('char', $size ?? 1); |
| 434 | case in_array($base, ['text', 'tinytext', 'mediumtext', 'longtext', 'clob', 'citext', 'name', 'string', 'enum', 'set', 'inet', 'cidr', 'macaddr', 'xml', 'interval', 'tsvector'], true): |
| 435 | return $r('text'); |
| 436 | case in_array($base, ['blob', 'tinyblob', 'mediumblob', 'longblob', 'bytea', 'binary', 'varbinary', 'bit varying', 'image'], true) || $base === 'bit': |
| 437 | return $r('blob', in_array($base, ['binary', 'varbinary'], true) ? $size : null); |
| 438 | case $base === 'date': |
| 439 | return $r('date'); |
| 440 | case in_array($base, ['datetime', 'timestamp', 'timestamp without time zone', 'smalldatetime', 'datetime2'], true): |
| 441 | return $r('datetime'); |
| 442 | case in_array($base, ['timestamptz', 'timestamp with time zone', 'datetimeoffset'], true): |
| 443 | return $r('datetime', null, null, true); |
| 444 | case in_array($base, ['time', 'time without time zone', 'timetz', 'time with time zone'], true): |
| 445 | return $r('time'); |
| 446 | case in_array($base, ['json', 'jsonb'], true): |
| 447 | return $r('json') + ['binary' => $base === 'jsonb']; |
| 448 | case $base === 'uuid' || $base === 'uniqueidentifier': |
| 449 | return $r('uuid'); |
| 450 | } |
| 451 | // SQLiteの親和性の規則 |
| 452 | if ($base === '') return $r($dialect === 'sqlite' ? 'text' : 'text'); |
| 453 | if (str_contains($base, 'int')) return $r('int', 64); |
| 454 | if (str_contains($base, 'char') || str_contains($base, 'clob') || str_contains($base, 'text')) return $r('text'); |
| 455 | if (str_contains($base, 'blob')) return $r('blob'); |
| 456 | if (str_contains($base, 'real') || str_contains($base, 'floa') || str_contains($base, 'doub')) return $r('real'); |
| 457 | if (str_contains($base, 'date') || str_contains($base, 'time')) return $r('datetime'); |
| 458 | return $r($dialect === 'sqlite' ? 'decimal' : 'text'); |
| 459 | } |
| 460 | |
| 461 | /** |
| 462 | * DEFAULTの式を共通の形にする |
| 463 | * |
| 464 | * @param string|null $expr SQLの式（例：'abc'、0、CURRENT_TIMESTAMP、now()、nextval('seq'::regclass)） |
| 465 | * @return array|null ['value' => 値]、['now' => true]、['seq' => true]（自動の番号）、変換できなければnull |
| 466 | */ |
| 467 | public static function parseDefault(?string $expr): ?array { |
| 468 | if ($expr === null) return null; |
| 469 | $e = trim($expr); |
| 470 | while (preg_match('/^\((.*)\)$/s', $e, $m)) $e = trim($m[1]); |
| 471 | // キャスト（'abc'::character varying） |
| 472 | $e = preg_replace("/::[a-z_ ]+(\\([^)]*\\))?(\\[\\])?$/i", '', $e); |
| 473 | if (preg_match('/^nextval\(/i', $e)) return ['seq' => true]; |
| 474 | if (preg_match('/^(current_timestamp|now|localtimestamp|current_date|current_time|getdate|sysdate)(\s*\(\s*\d*\s*\))?$/i', $e)) return ['now' => true]; |
| 475 | if (preg_match("/^datetime\\s*\\(\\s*'now'/i", $e)) return ['now' => true]; |
| 476 | if (strcasecmp($e, 'null') === 0) return ['value' => null]; |
| 477 | if (strcasecmp($e, 'true') === 0) return ['value' => true]; |
| 478 | if (strcasecmp($e, 'false') === 0) return ['value' => false]; |
| 479 | if (preg_match('/^[+-]?(\d+(\.\d*)?|\.\d+)([eE][+-]?\d+)?$/', $e)) return ['value' => preg_match('/^[+-]?\d+$/', $e) ? (int)$e : (float)$e]; |
| 480 | if (preg_match("/^[Ee]?'((?:[^']|'')*)'$/s", $e, $m)) { |
| 481 | $s = str_replace("''", "'", $m[1]); |
| 482 | if ($e[0] === 'E' || $e[0] === 'e') $s = stripcslashes($s); |
| 483 | return ['value' => $s]; |
| 484 | } |
| 485 | if (preg_match('/^b\'([01]*)\'$/i', $e, $m)) return ['value' => bindec($m[1]) !== 0]; |
| 486 | return null; |
| 487 | } |
| 488 | } |