| 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\Convert; |
| 41 | use Std\Lib\Db\DbException; |
| 42 | use Std\Lib\Db\Sqlite\Blob; |
| 43 | |
| 44 | /** |
| 45 | * 変換用のSQLの解析（MySQL・PostgreSQL・SQLiteの方言、ダンプの文の分け方、CREATE TABLE、INSERTの値） |
| 46 | */ |
| 47 | final class Sql { |
| 48 | // 列の定義で型の後に来る言葉 |
| 49 | private const COLUMN_WORDS = ['NOT', 'NULL', 'DEFAULT', 'PRIMARY', 'UNIQUE', 'AUTO_INCREMENT', 'AUTOINCREMENT', 'COLLATE', 'COMMENT', |
| 50 | 'REFERENCES', 'CHECK', 'CONSTRAINT', 'GENERATED', 'ON', 'CHARSET', 'STORAGE', 'COLUMN_FORMAT', 'INVISIBLE', 'VISIBLE', 'AS', |
| 51 | 'IDENTITY', 'KEY', 'SRID', 'ENCODE', 'COMPRESSION']; |
| 52 | |
| 53 | /////////////// |
| 54 | // 字句解析 |
| 55 | |
| 56 | /** |
| 57 | * 1つの文をトークンにする |
| 58 | * |
| 59 | * @param string $sql |
| 60 | * @param string $dialect mysql、pgsql、sqlite |
| 61 | * @return array t（id、qid、str、num、blob、op、param）、v（値）、u（大文字、idのみ） |
| 62 | */ |
| 63 | public static function tokens(string $sql, string $dialect): array { |
| 64 | $out = []; |
| 65 | $len = strlen($sql); |
| 66 | $i = 0; |
| 67 | while ($i < $len) { |
| 68 | $c = $sql[$i]; |
| 69 | if (ctype_space($c)) { |
| 70 | $i++; |
| 71 | continue; |
| 72 | } |
| 73 | if ($c === '-' && ($sql[$i + 1] ?? '') === '-' || ($c === '#' && $dialect === 'mysql')) { |
| 74 | $j = strpos($sql, "\n", $i); |
| 75 | $i = $j === false ? $len : $j + 1; |
| 76 | continue; |
| 77 | } |
| 78 | if ($c === '/' && ($sql[$i + 1] ?? '') === '*') { |
| 79 | $j = strpos($sql, '*/', $i + 2); |
| 80 | $i = $j === false ? $len : $j + 2; |
| 81 | continue; |
| 82 | } |
| 83 | // E'…'、X'…'、B'…'、N'…'、_binary'…' |
| 84 | if (($c === "'" || (ctype_alpha($c) && ($sql[$i + 1] ?? '') === "'"))) { |
| 85 | $prefix = $c === "'" ? '' : strtoupper($c); |
| 86 | if ($prefix !== '') $i++; |
| 87 | $backslash = $dialect === 'mysql' || $prefix === 'E'; |
| 88 | [$v, $i] = self::readQuoted($sql, $i, "'", $backslash); |
| 89 | if ($prefix === 'X') { |
| 90 | $out[] = ['t' => 'blob', 'v' => (string)hex2bin(strlen($v) % 2 ? '0'.$v : $v)]; |
| 91 | } else if ($prefix === 'B') { |
| 92 | $out[] = ['t' => 'num', 'v' => bindec($v)]; |
| 93 | } else { |
| 94 | $out[] = ['t' => 'str', 'v' => $v]; |
| 95 | } |
| 96 | continue; |
| 97 | } |
| 98 | if ($c === '"') { |
| 99 | [$v, $i] = self::readQuoted($sql, $i, '"', $dialect === 'mysql'); |
| 100 | // MySQLの「"」は文字列（ANSI_QUOTESでない場合） |
| 101 | $out[] = $dialect === 'mysql' ? ['t' => 'str', 'v' => $v] : ['t' => 'qid', 'v' => $v]; |
| 102 | continue; |
| 103 | } |
| 104 | if ($c === '`') { |
| 105 | [$v, $i] = self::readQuoted($sql, $i, '`', false); |
| 106 | $out[] = ['t' => 'qid', 'v' => $v]; |
| 107 | continue; |
| 108 | } |
| 109 | if ($c === '[' && $dialect === 'sqlite') { |
| 110 | $j = strpos($sql, ']', $i); |
| 111 | $out[] = ['t' => 'qid', 'v' => substr($sql, $i + 1, ($j === false ? $len : $j) - $i - 1)]; |
| 112 | $i = $j === false ? $len : $j + 1; |
| 113 | continue; |
| 114 | } |
| 115 | if ($c === '$' && $dialect === 'pgsql' && preg_match('/\G\$([A-Za-z_][A-Za-z0-9_]*)?\$/', $sql, $m, 0, $i)) { |
| 116 | $end = strpos($sql, $m[0], $i + strlen($m[0])); |
| 117 | $body = $end === false ? substr($sql, $i + strlen($m[0])) : substr($sql, $i + strlen($m[0]), $end - $i - strlen($m[0])); |
| 118 | $out[] = ['t' => 'str', 'v' => $body]; |
| 119 | $i = $end === false ? $len : $end + strlen($m[0]); |
| 120 | continue; |
| 121 | } |
| 122 | if ($c === '0' && ($sql[$i + 1] ?? '') !== '' && strtolower($sql[$i + 1]) === 'x' && preg_match('/\G0[xX]([0-9a-fA-F]*)/', $sql, $m, 0, $i)) { |
| 123 | $i += strlen($m[0]); |
| 124 | $hex = $m[1]; |
| 125 | $out[] = ['t' => 'blob', 'v' => (string)hex2bin(strlen($hex) % 2 ? '0'.$hex : $hex)]; |
| 126 | continue; |
| 127 | } |
| 128 | if (ctype_digit($c) || ($c === '.' && ctype_digit($sql[$i + 1] ?? ''))) { |
| 129 | preg_match('/\G(?:\d+(?:\.\d*)?|\.\d+)(?:[eE][+-]?\d+)?/', $sql, $m, 0, $i); |
| 130 | $i += strlen($m[0]); |
| 131 | $t = $m[0]; |
| 132 | $v = ctype_digit($t) ? ((string)(int)ltrim($t, '0') === ltrim($t, '0') || ltrim($t, '0') === '' ? (int)$t : $t) : (float)$t; |
| 133 | $out[] = ['t' => 'num', 'v' => $v, 'raw' => $t]; |
| 134 | continue; |
| 135 | } |
| 136 | if (ctype_alpha($c) || $c === '_' || ord($c) >= 0x80) { |
| 137 | preg_match('/\G[A-Za-z0-9_$\x80-\xFF]+/', $sql, $m, 0, $i); |
| 138 | $i += strlen($m[0]); |
| 139 | // MySQLの文字セットの前置き（_binary 'x'、_utf8mb4'x'） |
| 140 | if ($m[0][0] === '_' && $dialect === 'mysql' && preg_match('/\G\s*\'/', $sql, $mm, 0, $i)) { |
| 141 | $i += strlen($mm[0]) - 1; |
| 142 | [$v, $i] = self::readQuoted($sql, $i, "'", true); |
| 143 | $out[] = strtolower($m[0]) === '_binary' ? ['t' => 'blob', 'v' => $v] : ['t' => 'str', 'v' => $v]; |
| 144 | continue; |
| 145 | } |
| 146 | $out[] = ['t' => 'id', 'v' => $m[0], 'u' => strtoupper($m[0])]; |
| 147 | continue; |
| 148 | } |
| 149 | foreach (['::', '<>', '!=', '<=', '>=', '||', '->>', '->'] as $op) { |
| 150 | if (substr($sql, $i, strlen($op)) === $op) { |
| 151 | $out[] = ['t' => 'op', 'v' => $op]; |
| 152 | $i += strlen($op); |
| 153 | continue 2; |
| 154 | } |
| 155 | } |
| 156 | $out[] = ['t' => 'op', 'v' => $c]; |
| 157 | $i++; |
| 158 | } |
| 159 | return $out; |
| 160 | } |
| 161 | |
| 162 | /** 引用符の中（$iは開く引用符の位置） */ |
| 163 | private static function readQuoted(string $s, int $i, string $q, bool $backslash): array { |
| 164 | $len = strlen($s); |
| 165 | $v = ''; |
| 166 | $i++; |
| 167 | while ($i < $len) { |
| 168 | $c = $s[$i]; |
| 169 | if ($backslash && $c === '\\') { |
| 170 | $n = $s[$i + 1] ?? ''; |
| 171 | $v .= match ($n) { '0' => "\0", 'n' => "\n", 'r' => "\r", 't' => "\t", 'b' => "\x08", 'Z' => "\x1A", default => $n }; |
| 172 | $i += 2; |
| 173 | continue; |
| 174 | } |
| 175 | if ($c === $q) { |
| 176 | if (($s[$i + 1] ?? '') === $q) { |
| 177 | $v .= $q; |
| 178 | $i += 2; |
| 179 | continue; |
| 180 | } |
| 181 | return [$v, $i + 1]; |
| 182 | } |
| 183 | $j = strcspn($s, $q.($backslash ? '\\' : ''), $i); |
| 184 | $v .= substr($s, $i, $j); |
| 185 | $i += $j; |
| 186 | } |
| 187 | return [$v, $len]; |
| 188 | } |
| 189 | |
| 190 | /////////////// |
| 191 | // 解析 |
| 192 | |
| 193 | /** トークンの名前（スキーマ付きは最後の部分） */ |
| 194 | public static function name(array $tok, int &$p): string { |
| 195 | $t = $tok[$p] ?? null; |
| 196 | if ($t === null || !in_array($t['t'], ['id', 'qid', 'str'], true)) throw new DbException('SQLの解析：名前が必要です', 1, 'HY000'); |
| 197 | $p++; |
| 198 | $n = $t['v']; |
| 199 | while (($tok[$p]['t'] ?? '') === 'op' && $tok[$p]['v'] === '.' && in_array($tok[$p + 1]['t'] ?? '', ['id', 'qid'], true)) { |
| 200 | $n = $tok[$p + 1]['v']; |
| 201 | $p += 2; |
| 202 | } |
| 203 | return $n; |
| 204 | } |
| 205 | |
| 206 | private static function is(array $tok, int $p, string ...$words): bool { |
| 207 | return ($tok[$p]['t'] ?? '') === 'id' && in_array($tok[$p]['u'], $words, true); |
| 208 | } |
| 209 | |
| 210 | private static function isOp(array $tok, int $p, string $op): bool { |
| 211 | return ($tok[$p]['t'] ?? '') === 'op' && $tok[$p]['v'] === $op; |
| 212 | } |
| 213 | |
| 214 | /** 対応する閉じ括弧まで（$pは開き括弧） */ |
| 215 | private static function skipParens(array $tok, int $p): int { |
| 216 | $depth = 0; |
| 217 | for ($n = count($tok); $p < $n; $p++) { |
| 218 | if (self::isOp($tok, $p, '(')) $depth++; |
| 219 | else if (self::isOp($tok, $p, ')') && --$depth === 0) return $p + 1; |
| 220 | } |
| 221 | return $p; |
| 222 | } |
| 223 | |
| 224 | /** トークンを元のSQLに近い形に戻す（DEFAULTの式・型用） */ |
| 225 | public static function text(array $toks): string { |
| 226 | $out = ''; |
| 227 | foreach ($toks as $t) { |
| 228 | $s = match ($t['t']) { |
| 229 | 'str' => "'".str_replace("'", "''", $t['v'])."'", |
| 230 | 'qid' => '"'.$t['v'].'"', |
| 231 | 'blob' => "X'".bin2hex($t['v'])."'", |
| 232 | 'num' => (string)($t['raw'] ?? $t['v']), |
| 233 | default => $t['v'], |
| 234 | }; |
| 235 | if ($out !== '' && !in_array($s, ['(', ')', ',', '::', '[', ']'], true) && !str_ends_with($out, '(') && !str_ends_with($out, '::')) $out .= ' '; |
| 236 | $out .= $s; |
| 237 | } |
| 238 | return $out; |
| 239 | } |
| 240 | |
| 241 | /** (列, 列(長さ) ASC, …) の列の名前 */ |
| 242 | public static function columnList(array $tok, int &$p): array { |
| 243 | if (!self::isOp($tok, $p, '(')) throw new DbException('SQLの解析：(が必要です', 1, 'HY000'); |
| 244 | $end = self::skipParens($tok, $p); |
| 245 | // 要素毎のトークン |
| 246 | $items = [[]]; |
| 247 | $depth = 0; |
| 248 | for ($p++; $p < $end - 1; $p++) { |
| 249 | if (self::isOp($tok, $p, '(')) $depth++; |
| 250 | else if (self::isOp($tok, $p, ')')) $depth--; |
| 251 | else if ($depth === 0 && self::isOp($tok, $p, ',')) { |
| 252 | $items[] = []; |
| 253 | continue; |
| 254 | } |
| 255 | $items[count($items) - 1][] = $tok[$p]; |
| 256 | } |
| 257 | $cols = []; |
| 258 | foreach ($items as $it) { |
| 259 | // 列の名前の後は、MySQLの(長さ)、ASC・DESC・COLLATE・演算子クラス等だけなら列、他は式（null） |
| 260 | $ok = !empty($it) && in_array($it[0]['t'], ['id', 'qid'], true); |
| 261 | if ($ok && isset($it[1])) { |
| 262 | if ($it[1]['t'] === 'op' && $it[1]['v'] === '(') $ok = ($it[2]['t'] ?? '') === 'num' && ($it[3]['v'] ?? '') === ')'; |
| 263 | else $ok = in_array($it[1]['t'], ['id', 'qid'], true); |
| 264 | } |
| 265 | $cols[] = $ok ? $it[0]['v'] : null; |
| 266 | } |
| 267 | $p = $end; |
| 268 | return $cols; |
| 269 | } |
| 270 | |
| 271 | /** |
| 272 | * CREATE TABLEを解析する |
| 273 | * |
| 274 | * @return array|null テーブル（Convertの形）、CREATE TABLEでなければnull |
| 275 | */ |
| 276 | public static function createTable(string $sql, string $dialect, array &$warnings = []): ?array { |
| 277 | $tok = self::tokens($sql, $dialect); |
| 278 | $p = 0; |
| 279 | if (!self::is($tok, $p++, 'CREATE')) return null; |
| 280 | while (self::is($tok, $p, 'TEMP', 'TEMPORARY', 'UNLOGGED', 'GLOBAL', 'LOCAL', 'OR', 'REPLACE')) $p++; |
| 281 | if (!self::is($tok, $p++, 'TABLE')) return null; |
| 282 | if (self::is($tok, $p, 'IF')) $p += 3; |
| 283 | $name = self::name($tok, $p); |
| 284 | if (!self::isOp($tok, $p, '(')) return null; |
| 285 | $end = self::skipParens($tok, $p); |
| 286 | $t = ['name' => $name, 'columns' => [], 'pk' => [], 'indexes' => [], 'collate' => null]; |
| 287 | // 項目に分ける |
| 288 | $items = []; |
| 289 | $cur = []; |
| 290 | $depth = 0; |
| 291 | for ($k = $p + 1; $k < $end - 1; $k++) { |
| 292 | if (self::isOp($tok, $k, '(')) $depth++; |
| 293 | if (self::isOp($tok, $k, ')')) $depth--; |
| 294 | if ($depth === 0 && self::isOp($tok, $k, ',')) { |
| 295 | $items[] = $cur; |
| 296 | $cur = []; |
| 297 | continue; |
| 298 | } |
| 299 | $cur[] = $tok[$k]; |
| 300 | } |
| 301 | if ($cur) $items[] = $cur; |
| 302 | foreach ($items as $it) self::tableItem($t, $it, $dialect, $warnings); |
| 303 | // テーブルのオプション（MySQL） |
| 304 | for ($k = $end; $k < count($tok); $k++) { |
| 305 | if (self::is($tok, $k, 'COLLATE') && isset($tok[$k + 1])) { |
| 306 | $j = self::isOp($tok, $k + 1, '=') ? $k + 2 : $k + 1; |
| 307 | $t['collate'] = $tok[$j]['v'] ?? null; |
| 308 | } |
| 309 | if (self::is($tok, $k, 'WITHOUT')) $warnings[] = "WITHOUT ROWIDは普通のテーブルにします：{$name}"; |
| 310 | } |
| 311 | if ($dialect !== 'mysql') unset($t['collate']); |
| 312 | return $t; |
| 313 | } |
| 314 | |
| 315 | /** テーブルの定義の1つの項目（列又は制約） */ |
| 316 | private static function tableItem(array &$t, array $it, string $dialect, array &$warnings): void { |
| 317 | $p = 0; |
| 318 | if (self::is($it, 0, 'CONSTRAINT')) $p = 2; |
| 319 | $cname = self::is($it, 0, 'CONSTRAINT') ? (string)($it[1]['v'] ?? '') : null; |
| 320 | if (self::is($it, $p, 'PRIMARY') && self::is($it, $p + 1, 'KEY')) { |
| 321 | $p += 2; |
| 322 | while (!self::isOp($it, $p, '(') && $p < count($it)) $p++; |
| 323 | $t['pk'] = array_values(array_filter(self::columnList($it, $p))); |
| 324 | return; |
| 325 | } |
| 326 | if (self::is($it, $p, 'UNIQUE', 'KEY', 'INDEX') && !self::isOp($it, $p + 1, '.')) { |
| 327 | $unique = self::is($it, $p, 'UNIQUE'); |
| 328 | $p++; |
| 329 | if (self::is($it, $p, 'KEY', 'INDEX')) $p++; |
| 330 | $iname = $cname; |
| 331 | if (!self::isOp($it, $p, '(') && $p < count($it)) $iname = (string)$it[$p++]['v']; |
| 332 | while (!self::isOp($it, $p, '(') && $p < count($it)) $p++; |
| 333 | $cols = self::columnList($it, $p); |
| 334 | if (in_array(null, $cols, true)) { |
| 335 | $warnings[] = "式のインデックスは変換しません：{$t['name']}"; |
| 336 | return; |
| 337 | } |
| 338 | $t['indexes'][] = ['name' => $iname ?? '', 'columns' => $cols, 'unique' => $unique]; |
| 339 | return; |
| 340 | } |
| 341 | if (self::is($it, $p, 'FULLTEXT', 'SPATIAL')) { |
| 342 | $warnings[] = "全文・空間のインデックスは変換しません：{$t['name']}"; |
| 343 | return; |
| 344 | } |
| 345 | if (self::is($it, $p, 'FOREIGN', 'CHECK', 'EXCLUDE') || ($cname !== null)) { |
| 346 | if (self::is($it, $p, 'FOREIGN')) $warnings[] = "外部キーは変換しません：{$t['name']}"; |
| 347 | return; |
| 348 | } |
| 349 | if (self::is($it, $p, 'PERIOD', 'LIKE')) return; |
| 350 | $col = self::columnDef($it, $dialect, $t['name'], $warnings); |
| 351 | if ($col === null) return; |
| 352 | if (!empty($col['pk'])) $t['pk'] = [$col['name']]; |
| 353 | if (!empty($col['unique'])) $t['indexes'][] = ['name' => '', 'columns' => [$col['name']], 'unique' => true]; |
| 354 | unset($col['pk'], $col['unique']); |
| 355 | $t['columns'][] = $col; |
| 356 | } |
| 357 | |
| 358 | /** |
| 359 | * 列の定義 |
| 360 | * |
| 361 | * @return array|null Convertの列（pk・uniqueも） |
| 362 | */ |
| 363 | public static function columnDef(array $it, string $dialect, string $table, array &$warnings): ?array { |
| 364 | $p = 0; |
| 365 | if (!in_array($it[0]['t'] ?? '', ['id', 'qid', 'str'], true)) return null; |
| 366 | $name = $it[$p++]['v']; |
| 367 | $typeToks = []; |
| 368 | $n = count($it); |
| 369 | while ($p < $n) { |
| 370 | $tk = $it[$p]; |
| 371 | if ($tk['t'] === 'id' && in_array($tk['u'], self::COLUMN_WORDS, true)) break; |
| 372 | if ($tk['t'] === 'id' && $tk['u'] === 'CHARACTER' && self::is($it, $p + 1, 'SET')) break; |
| 373 | if (self::isOp($it, $p, '(')) { |
| 374 | $e = self::skipParens($it, $p); |
| 375 | $typeToks = array_merge($typeToks, array_slice($it, $p, $e - $p)); |
| 376 | $p = $e; |
| 377 | continue; |
| 378 | } |
| 379 | $typeToks[] = $tk; |
| 380 | $p++; |
| 381 | } |
| 382 | $typeText = self::text($typeToks); |
| 383 | $col = Convert::parseType($typeText, $dialect) + ['name' => $name, 'notNull' => false, 'default' => null, 'autoinc' => false, 'collate' => null]; |
| 384 | if (in_array(strtolower(preg_replace('/\(.*$/', '', $typeText)), ['serial', 'bigserial', 'smallserial', 'serial4', 'serial8', 'serial2'], true)) { |
| 385 | $col['autoinc'] = true; |
| 386 | $col['notNull'] = true; |
| 387 | } |
| 388 | while ($p < $n) { |
| 389 | if (self::is($it, $p, 'NOT') && self::is($it, $p + 1, 'NULL')) { |
| 390 | $col['notNull'] = true; |
| 391 | $p += 2; |
| 392 | } else if (self::is($it, $p, 'NULL')) { |
| 393 | $p++; |
| 394 | } else if (self::is($it, $p, 'PRIMARY')) { |
| 395 | $col['pk'] = true; |
| 396 | $p += 2; |
| 397 | if (self::is($it, $p, 'ASC', 'DESC')) $p++; |
| 398 | } else if (self::is($it, $p, 'UNIQUE')) { |
| 399 | $col['unique'] = true; |
| 400 | $p++; |
| 401 | if (self::is($it, $p, 'KEY')) $p++; |
| 402 | } else if (self::is($it, $p, 'AUTO_INCREMENT', 'AUTOINCREMENT')) { |
| 403 | $col['autoinc'] = true; |
| 404 | $p++; |
| 405 | } else if (self::is($it, $p, 'DEFAULT')) { |
| 406 | $p++; |
| 407 | $s = $p; |
| 408 | if (self::isOp($it, $p, '(')) { |
| 409 | $p = self::skipParens($it, $p); |
| 410 | } else { |
| 411 | while ($p < $n && !($it[$p]['t'] === 'id' && in_array($it[$p]['u'], ['NOT', 'NULL', 'PRIMARY', 'UNIQUE', 'AUTO_INCREMENT', 'COLLATE', 'COMMENT', 'REFERENCES', 'CHECK', 'CONSTRAINT', 'ON', 'GENERATED', 'CHARACTER'], true) && $p > $s)) { |
| 412 | if (self::isOp($it, $p, '(')) { |
| 413 | $p = self::skipParens($it, $p); |
| 414 | continue; |
| 415 | } |
| 416 | $p++; |
| 417 | } |
| 418 | } |
| 419 | $expr = self::text(array_slice($it, $s, $p - $s)); |
| 420 | $d = Convert::parseDefault($expr); |
| 421 | if ($d !== null && isset($d['seq'])) { |
| 422 | $col['autoinc'] = true; |
| 423 | } else if ($d === null) { |
| 424 | $warnings[] = "DEFAULTを変換できません：{$table}.{$name}（{$expr}）"; |
| 425 | } else { |
| 426 | $col['default'] = $d; |
| 427 | } |
| 428 | } else if (self::is($it, $p, 'GENERATED')) { |
| 429 | // GENERATED … AS IDENTITY（自動の番号）、GENERATED ALWAYS AS (式)（生成列） |
| 430 | $k = $p; |
| 431 | while ($k < $n && !self::is($it, $k, 'IDENTITY') && !self::isOp($it, $k, '(')) $k++; |
| 432 | if (self::is($it, $k, 'IDENTITY')) { |
| 433 | $col['autoinc'] = true; |
| 434 | $col['notNull'] = true; |
| 435 | $p = $k + 1; |
| 436 | if (self::isOp($it, $p, '(')) $p = self::skipParens($it, $p); |
| 437 | } else { |
| 438 | $warnings[] = "生成列は普通の列にします：{$table}.{$name}"; |
| 439 | $p = $k < $n ? self::skipParens($it, $k) : $n; |
| 440 | } |
| 441 | } else if (self::is($it, $p, 'AS') && self::isOp($it, $p + 1, '(')) { |
| 442 | $warnings[] = "生成列は普通の列にします：{$table}.{$name}"; |
| 443 | $p = self::skipParens($it, $p + 1); |
| 444 | } else if (self::is($it, $p, 'COLLATE')) { |
| 445 | $col['collate'] = $dialect === 'mysql' ? (string)($it[$p + 1]['v'] ?? '') : null; |
| 446 | $p += 2; |
| 447 | } else if (self::is($it, $p, 'CHARACTER') && self::is($it, $p + 1, 'SET')) { |
| 448 | $p += 3; |
| 449 | } else if (self::is($it, $p, 'CHARSET', 'COMMENT', 'STORAGE', 'COLUMN_FORMAT', 'SRID', 'ENCODE', 'COMPRESSION')) { |
| 450 | $p += 2; |
| 451 | } else if (self::is($it, $p, 'ON') && self::is($it, $p + 1, 'UPDATE')) { |
| 452 | $p += 3; |
| 453 | if (self::isOp($it, $p, '(')) $p = self::skipParens($it, $p); |
| 454 | } else if (self::is($it, $p, 'REFERENCES')) { |
| 455 | $p += 2; |
| 456 | if (self::isOp($it, $p, '(')) $p = self::skipParens($it, $p); |
| 457 | while ($p < $n && self::is($it, $p, 'ON', 'MATCH', 'DEFERRABLE', 'INITIALLY', 'NOT', 'CASCADE', 'RESTRICT', 'SET', 'NO', 'ACTION', 'DELETE', 'UPDATE', 'NULL', 'FULL', 'SIMPLE', 'PARTIAL', 'DEFERRED', 'IMMEDIATE')) $p++; |
| 458 | } else if (self::is($it, $p, 'CHECK')) { |
| 459 | $p = self::skipParens($it, $p + 1); |
| 460 | } else if (self::is($it, $p, 'CONSTRAINT')) { |
| 461 | $p += 2; |
| 462 | } else { |
| 463 | $p++; |
| 464 | } |
| 465 | } |
| 466 | return $col; |
| 467 | } |
| 468 | |
| 469 | /////////////// |
| 470 | // 値 |
| 471 | |
| 472 | /** |
| 473 | * INSERT INTO t [(列)] VALUES (…), (…) |
| 474 | * |
| 475 | * @return array|null table、columns（null＝全部）、rows（PHPの値の配列の配列） |
| 476 | */ |
| 477 | public static function insert(string $sql, string $dialect): ?array { |
| 478 | $tok = self::tokens($sql, $dialect); |
| 479 | $p = 0; |
| 480 | if (self::is($tok, 0, 'REPLACE')) $p = 1; |
| 481 | else if (self::is($tok, 0, 'INSERT')) { |
| 482 | $p = 1; |
| 483 | while (self::is($tok, $p, 'IGNORE', 'OR', 'REPLACE', 'LOW_PRIORITY', 'DELAYED', 'HIGH_PRIORITY', 'ABORT', 'FAIL', 'ROLLBACK')) $p++; |
| 484 | } else { |
| 485 | return null; |
| 486 | } |
| 487 | if (!self::is($tok, $p++, 'INTO')) return null; |
| 488 | $table = self::name($tok, $p); |
| 489 | $cols = null; |
| 490 | if (self::isOp($tok, $p, '(')) $cols = self::columnList($tok, $p); |
| 491 | if (!self::is($tok, $p, 'VALUES', 'VALUE')) return ['table' => $table, 'columns' => $cols, 'rows' => [], 'select' => true]; |
| 492 | $p++; |
| 493 | $rows = []; |
| 494 | $n = count($tok); |
| 495 | while ($p < $n && self::isOp($tok, $p, '(')) { |
| 496 | $end = self::skipParens($tok, $p); |
| 497 | $row = []; |
| 498 | $val = []; |
| 499 | $depth = 0; |
| 500 | for ($k = $p + 1; $k < $end - 1; $k++) { |
| 501 | if (self::isOp($tok, $k, '(')) $depth++; |
| 502 | if (self::isOp($tok, $k, ')')) $depth--; |
| 503 | if ($depth === 0 && self::isOp($tok, $k, ',')) { |
| 504 | $row[] = self::value($val); |
| 505 | $val = []; |
| 506 | continue; |
| 507 | } |
| 508 | $val[] = $tok[$k]; |
| 509 | } |
| 510 | if ($val || $end - $p > 2) $row[] = self::value($val); |
| 511 | $rows[] = $row; |
| 512 | $p = $end; |
| 513 | if (self::isOp($tok, $p, ',')) $p++; |
| 514 | } |
| 515 | return ['table' => $table, 'columns' => $cols, 'rows' => $rows]; |
| 516 | } |
| 517 | |
| 518 | /** 値のトークン（リテラル、負の数、キャスト、一部の関数） */ |
| 519 | public static function value(array $v): mixed { |
| 520 | // キャスト（'x'::bytea、CAST(… AS …)）を除く |
| 521 | $cast = null; |
| 522 | $k = count($v); |
| 523 | while ($k >= 3 && $v[$k - 2]['t'] === 'op' && $v[$k - 2]['v'] === '::') { |
| 524 | $cast = strtolower((string)$v[$k - 1]['v']); |
| 525 | $v = array_slice($v, 0, $k - 2); |
| 526 | $k = count($v); |
| 527 | } |
| 528 | if ($k >= 3 && $v[$k - 1]['t'] === 'op' && $v[$k - 1]['v'] === ']' && $v[$k - 2]['t'] === 'op' && $v[$k - 2]['v'] === '[') { |
| 529 | $v = array_slice($v, 0, $k - 2); |
| 530 | $k -= 2; |
| 531 | } |
| 532 | if ($k === 0) return null; |
| 533 | if ($k === 1) { |
| 534 | $t = $v[0]; |
| 535 | switch ($t['t']) { |
| 536 | case 'num': |
| 537 | return $t['v']; |
| 538 | case 'blob': |
| 539 | return new Blob($t['v']); |
| 540 | case 'str': |
| 541 | if ($cast === 'bytea' && str_starts_with($t['v'], '\\x')) return new Blob((string)hex2bin(substr($t['v'], 2))); |
| 542 | return $t['v']; |
| 543 | case 'id': |
| 544 | return match ($t['u']) { 'NULL' => null, 'TRUE' => true, 'FALSE' => false, 'DEFAULT' => null, default => $t['v'] }; |
| 545 | } |
| 546 | } |
| 547 | if ($k === 2 && $v[0]['t'] === 'op' && ($v[0]['v'] === '-' || $v[0]['v'] === '+') && $v[1]['t'] === 'num') { |
| 548 | if ($v[0]['v'] === '+') return $v[1]['v']; |
| 549 | $n = $v[1]['v']; |
| 550 | if (is_string($n)) return $n === '9223372036854775808' ? PHP_INT_MIN : '-'.$n; |
| 551 | return -$n; |
| 552 | } |
| 553 | // CAST(x AS type) |
| 554 | if ($v[0]['t'] === 'id' && $v[0]['u'] === 'CAST' && ($v[1]['v'] ?? '') === '(') { |
| 555 | $inner = []; |
| 556 | for ($i = 2; $i < $k && !($v[$i]['t'] === 'id' && $v[$i]['u'] === 'AS'); $i++) $inner[] = $v[$i]; |
| 557 | return self::value($inner); |
| 558 | } |
| 559 | // sqlite3の.dumpの文字列（replace('…','\n',char(10))、unistr('…')、||） |
| 560 | $parts = self::split($v, '||'); |
| 561 | if (count($parts) > 1) { |
| 562 | $out = ''; |
| 563 | foreach ($parts as $part) { |
| 564 | $x = self::value($part); |
| 565 | if (!is_string($x) && !is_int($x)) return self::text($v); |
| 566 | $out .= $x; |
| 567 | } |
| 568 | return $out; |
| 569 | } |
| 570 | if ($v[0]['t'] === 'id' && ($v[1]['v'] ?? '') === '(' && $v[$k - 1]['v'] === ')' && in_array($v[0]['u'], ['REPLACE', 'CHAR', 'UNISTR'], true)) { |
| 571 | $args = array_map([self::class, 'value'], self::split(array_slice($v, 2, $k - 3), ',')); |
| 572 | foreach ($args as $a) if (!is_string($a) && !is_int($a)) return self::text($v); |
| 573 | if ($v[0]['u'] === 'REPLACE' && count($args) === 3) return str_replace((string)$args[1], (string)$args[2], (string)$args[0]); |
| 574 | if ($v[0]['u'] === 'CHAR') return implode('', array_map(fn($c) => (string)mb_chr((int)$c, 'UTF-8'), $args)); |
| 575 | if ($v[0]['u'] === 'UNISTR' && count($args) === 1) { |
| 576 | return (string)preg_replace_callback('/\\\\\\\\|\\\\(?:u?([0-9A-Fa-f]{4})|\+([0-9A-Fa-f]{6})|U([0-9A-Fa-f]{8}))/', |
| 577 | fn($m) => $m[0] === '\\\\' ? '\\' : (string)mb_chr((int)hexdec($m[1] ?: ($m[2] ?? '') ?: ($m[3] ?? '')), 'UTF-8'), (string)$args[0]); |
| 578 | } |
| 579 | } |
| 580 | // 他の関数（now()等）は文字列のまま |
| 581 | return self::text($v); |
| 582 | } |
| 583 | |
| 584 | /** トークンを括弧の外の演算子で分ける */ |
| 585 | private static function split(array $v, string $op): array { |
| 586 | $out = [[]]; |
| 587 | $depth = 0; |
| 588 | foreach ($v as $t) { |
| 589 | if ($t['t'] === 'op' && $t['v'] === '(') $depth++; |
| 590 | else if ($t['t'] === 'op' && $t['v'] === ')') $depth--; |
| 591 | else if ($depth === 0 && $t['t'] === 'op' && $t['v'] === $op) { |
| 592 | $out[] = []; |
| 593 | continue; |
| 594 | } |
| 595 | $out[count($out) - 1][] = $t; |
| 596 | } |
| 597 | return $out; |
| 598 | } |
| 599 | |
| 600 | /** |
| 601 | * COPYのデータの1行（タブ区切り、\N＝NULL、\のエスケープ） |
| 602 | * |
| 603 | * @return array 文字列又はnull |
| 604 | */ |
| 605 | public static function copyLine(string $line): array { |
| 606 | $out = []; |
| 607 | foreach (explode("\t", $line) as $f) { |
| 608 | if ($f === '\\N') { |
| 609 | $out[] = null; |
| 610 | continue; |
| 611 | } |
| 612 | if (!str_contains($f, '\\')) { |
| 613 | $out[] = $f; |
| 614 | continue; |
| 615 | } |
| 616 | $out[] = preg_replace_callback('/\\\\(x[0-9a-fA-F]{1,2}|[0-7]{1,3}|.)/', function(array $m): string { |
| 617 | $c = $m[1]; |
| 618 | if ($c[0] === 'x' && strlen($c) > 1) return chr(hexdec(substr($c, 1))); |
| 619 | if (ctype_digit($c)) return chr(octdec($c) & 0xFF); |
| 620 | return match ($c) { 'b' => "\x08", 'f' => "\f", 'n' => "\n", 'r' => "\r", 't' => "\t", 'v' => "\v", default => $c }; |
| 621 | }, $f); |
| 622 | } |
| 623 | return $out; |
| 624 | } |
| 625 | |
| 626 | /** COPYのデータの1行を作る */ |
| 627 | public static function copyField(mixed $v): string { |
| 628 | if ($v === null) return '\\N'; |
| 629 | return strtr((string)$v, ["\\" => "\\\\", "\t" => "\\t", "\n" => "\\n", "\r" => "\\r"]); |
| 630 | } |
| 631 | } |