| 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 | // テスト用の偽物のPostgreSQLサーバー（MockServerが起動する）。 |
| 39 | // 使い方：php pg-server.php ポート [証明書のPEM] [秘密鍵のPEM] [オプション（カンマ区切り）] |
| 40 | // SQLはメモリーのSQLiteで実行し、結果をPostgreSQLのプロトコル3.0で返す（pdo_sqliteが必要）。 |
| 41 | // 証明書と秘密鍵を渡すと、TLS（SSLRequest）とSCRAM-SHA-256-PLUS（tls-server-end-point）に対応する。 |
| 42 | // オプション：nocb（TLSでもSCRAM-SHA-256-PLUSを出さない）、badsig（SCRAMのサーバーの署名を間違える）、 |
| 43 | // badnonce（SCRAMのnonceを変える）、nofinal（SCRAMのSASLFinalを送らずにAuthenticationOk） |
| 44 | // |
| 45 | // ユーザー（パスワード、認証）： |
| 46 | // lb（ひみつ、SCRAM-SHA-256）、md5user（md5ひみつ、MD5）、clear（clearひみつ、パスワードをそのまま）、 |
| 47 | // trust（なし）、tlsonly（tlsひみつ、SCRAM-SHA-256、TLSのみ）、gss（GSSAPI＝未対応） |
| 48 | // データベース：「nodb」以外は何でも存在する事にする |
| 49 | // 偽物なので、Describe（ステートメント）は列を返さない（NoData）。列はポータルのDescribeで返す。 |
| 50 | // 「$1::型」・INSERTの列・「列 = $1」からパラメータの型を決める（無ければtext）。 |
| 51 | // 特別なSQL： |
| 52 | // SELECT lb_disconnect() 返事をせずに接続を切る |
| 53 | // SELECT lb_fatal() FATALのエラーを送って切る |
| 54 | // SELECT lb_sleep(秒) 返事を遅らせる |
| 55 | // SELECT lb_notice() NOTICEとWARNINGを送る |
| 56 | // SELECT lb_async() 結果の途中にNotificationResponseとParameterStatusを送る |
| 57 | // SELECT lb_types() 色々な型の列（本物のPostgreSQL 16の出力と同じ） |
| 58 | // lb_tls()・lb_auth()・lb_cb()（チャネルバインディングの種類）・current_user・current_database()・ |
| 59 | // pg_backend_pid()・version() |
| 60 | // COPY … FROM STDIN、COPY … TO STDOUT |
| 61 | // information_schema.tablesのtable_name（テーブルの一覧）、setval()（何もしない） |
| 62 | |
| 63 | require_once __DIR__.'/../../../../autoload.php'; |
| 64 | |
| 65 | use Std\Lib\Crypto\X509; |
| 66 | use Std\Lib\Tls; |
| 67 | |
| 68 | $port = (int)($argv[1] ?? 0); |
| 69 | $certFile = $argv[2] ?? ''; |
| 70 | $keyFile = $argv[3] ?? ''; |
| 71 | $options = array_filter(explode(',', $argv[4] ?? '')); |
| 72 | |
| 73 | if (!class_exists('PDO') || !in_array('sqlite', PDO::getAvailableDrivers(), true)) { |
| 74 | fwrite(STDOUT, "error pdo_sqlite\n"); |
| 75 | exit(1); |
| 76 | } |
| 77 | |
| 78 | $server = @stream_socket_server("tcp://127.0.0.1:{$port}", $errno, $errstr); |
| 79 | if (!$server) { |
| 80 | fwrite(STDOUT, "error {$errstr}\n"); |
| 81 | exit(1); |
| 82 | } |
| 83 | |
| 84 | $pg = new MockPgsql($certFile, $keyFile, $options); |
| 85 | fwrite(STDOUT, "ready\n"); |
| 86 | fflush(STDOUT); |
| 87 | $pg->run($server); |
| 88 | |
| 89 | /** エラーを返す為の例外 */ |
| 90 | class PgError extends \Exception { |
| 91 | public function __construct(public string $sqlState, string $message, public string $detail = '', public string $severity = 'ERROR') { |
| 92 | parent::__construct($message); |
| 93 | } |
| 94 | } |
| 95 | |
| 96 | class MockPgsql { |
| 97 | private const VERSION = '16.0 (lbmock)'; |
| 98 | |
| 99 | private const USERS = [ |
| 100 | 'lb' => ['ひみつ', 'scram'], |
| 101 | 'md5user' => ['md5ひみつ', 'md5'], |
| 102 | 'clear' => ['clearひみつ', 'password'], |
| 103 | 'trust' => ['', 'trust'], |
| 104 | 'tlsonly' => ['tlsひみつ', 'scram'], |
| 105 | 'gss' => ['', 'gss'], |
| 106 | ]; |
| 107 | |
| 108 | // SQLiteの宣言された型 => OID |
| 109 | private const DECL = [ |
| 110 | 'BOOL' => 16, 'BYTEA' => 17, 'BIGINT' => 20, 'INT8' => 20, 'BIGSERIAL' => 20, 'SMALLINT' => 21, 'INT2' => 21, |
| 111 | 'INTEGER' => 23, 'INT' => 23, 'INT4' => 23, 'SERIAL' => 23, 'TEXT' => 25, 'JSON' => 114, 'REAL' => 700, 'FLOAT4' => 700, |
| 112 | 'DOUBLE' => 701, 'FLOAT8' => 701, 'FLOAT' => 701, 'VARCHAR' => 1043, 'DATE' => 1082, 'TIMESTAMP' => 1114, |
| 113 | 'TIMESTAMPTZ' => 1184, 'NUMERIC' => 1700, 'DECIMAL' => 1700, 'UUID' => 2950, 'JSONB' => 3802, |
| 114 | ]; |
| 115 | |
| 116 | // 本物のPostgreSQL 16（TimeZone = Asia/Tokyo）の出力（escはbytea_output = escapeの場合、big8はint8に入らない値） |
| 117 | private const TYPES = [ |
| 118 | 'i2' => [21, '-5'], 'i4' => [23, '42'], 'i8' => [20, '9223372036854775807'], 'num' => [1700, '12345678901234567890.5'], |
| 119 | 'f4' => [700, '2.5'], 'nan' => [701, 'NaN'], 'ninf' => [701, '-Infinity'], 'big' => [701, '1e+100'], |
| 120 | 't' => [16, 't'], 'f' => [16, 'f'], 'bin' => [17, '\\x00ff5c'], 'txts' => [1009, '{"a,b","c\\"d",NULL,"NULL",""," x "}'], |
| 121 | 'ints' => [1007, '{{1,2},{3,NULL}}'], 'ranged' => [1007, '[0:1]={1,2}'], 'bools' => [1000, '{t,f}'], |
| 122 | 'bins' => [1001, '{"\\\\x01"}'], 'floats' => [1022, '{1.5,NaN}'], 'j' => [114, '{"a": [1, "x"]}'], 'jb' => [3802, '{"b": 2}'], |
| 123 | 'd' => [1082, '2026-01-02'], 'ts' => [1184, '2026-01-02 03:04:05.123456+09'], 'u' => [2950, 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'], |
| 124 | 'o' => [26, '1259'], 'n' => [25, null], 'v' => [1043, '日本語'], 'i8s' => [20, '2147483648'], 'empty' => [1009, '{}'], |
| 125 | 'esc' => [17, '\\000\\377\\\\A'], 'big8' => [20, '99999999999999999999'], |
| 126 | ]; |
| 127 | |
| 128 | private \PDO $pdo; |
| 129 | private array $conns = []; |
| 130 | private int $nextId = 1000; |
| 131 | private string $certDer = ''; |
| 132 | private string $certSigAlg = ''; |
| 133 | |
| 134 | public function __construct(private string $certFile, private string $keyFile, private array $options) { |
| 135 | $this->pdo = new \PDO('sqlite::memory:'); |
| 136 | $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); |
| 137 | if ($certFile !== '') { |
| 138 | $cert = X509::fromPem((string)file_get_contents($certFile)); |
| 139 | $this->certDer = $cert->der; |
| 140 | $this->certSigAlg = $cert->sigAlg; |
| 141 | } |
| 142 | } |
| 143 | |
| 144 | public function run($server): void { |
| 145 | while (true) { |
| 146 | // TLSで既に復号されたデータが残っている接続を先に |
| 147 | $pending = []; |
| 148 | foreach ($this->conns as $id => $cn) { |
| 149 | if ($cn['tls'] && Tls::pending($cn['sock'])) $pending[] = $id; |
| 150 | } |
| 151 | |
| 152 | if (empty($pending)) { |
| 153 | $r = [$server]; |
| 154 | foreach ($this->conns as $cn) $r[] = $cn['sock']; |
| 155 | $w = $e = null; |
| 156 | $n = @stream_select($r, $w, $e, 120); |
| 157 | if ($n === false || $n === 0) return; |
| 158 | |
| 159 | foreach ($r as $s) { |
| 160 | if ($s === $server) { |
| 161 | $c = @stream_socket_accept($server, 5); |
| 162 | if ($c) $this->accept($c); |
| 163 | continue; |
| 164 | } |
| 165 | foreach ($this->conns as $id => $cn) { |
| 166 | if ($cn['sock'] === $s) $pending[] = $id; |
| 167 | } |
| 168 | } |
| 169 | } |
| 170 | |
| 171 | foreach ($pending as $id) { |
| 172 | if (!isset($this->conns[$id])) continue; |
| 173 | try { |
| 174 | $res = $this->handle($id); |
| 175 | } catch (PgError $e) { |
| 176 | // 認証中のエラー等（FATAL） |
| 177 | $this->error($id, $e); |
| 178 | $res = false; |
| 179 | } |
| 180 | if ($res === 'quit') return; |
| 181 | if ($res === false) $this->drop($id); |
| 182 | } |
| 183 | } |
| 184 | } |
| 185 | |
| 186 | /////////////// |
| 187 | // 接続・認証 |
| 188 | |
| 189 | private function accept($c): void { |
| 190 | stream_set_blocking($c, true); |
| 191 | stream_set_timeout($c, 30); |
| 192 | // 読み込みのバッファを無くす（SSLRequestと一緒にTLSのClientHelloまで読んでしまうと、TLSを開始出来ない） |
| 193 | stream_set_read_buffer($c, 0); |
| 194 | $id = $this->nextId++; |
| 195 | $this->conns[$id] = [ |
| 196 | 'sock' => $c, 'id' => $id, 'state' => 'new', 'tls' => false, 'user' => '', 'db' => '', 'params' => [], |
| 197 | 'auth' => '', 'cb' => '', 'scram' => null, 'salt' => '', 'txn' => 'I', 'skip' => false, 'stmts' => [], 'portals' => [], |
| 198 | ]; |
| 199 | } |
| 200 | |
| 201 | private function drop(int $id): void { |
| 202 | // 切れた接続のトランザクションはロールバック（SQLiteは1つなので） |
| 203 | if ($this->conns[$id]['txn'] !== 'I' && $this->pdo->inTransaction()) $this->pdo->rollBack(); |
| 204 | @fclose($this->conns[$id]['sock']); |
| 205 | unset($this->conns[$id]); |
| 206 | } |
| 207 | |
| 208 | /** |
| 209 | * 1つのメッセージを処理する |
| 210 | * |
| 211 | * @return bool|string false＝接続を切る、'quit'＝サーバーを止める |
| 212 | */ |
| 213 | private function handle(int $id): bool|string { |
| 214 | $cn = &$this->conns[$id]; |
| 215 | |
| 216 | if ($cn['state'] === 'new') { |
| 217 | $head = $this->readRaw($id, 4); |
| 218 | if ($head === null) return false; |
| 219 | // MockServer::stop()の終了のリクエスト |
| 220 | if ($head === 'QUIT') return 'quit'; |
| 221 | $len = unpack('N', $head)[1]; |
| 222 | if ($len < 8 || $len > 10000) return false; |
| 223 | $body = $this->readRaw($id, $len - 4); |
| 224 | if ($body === null) return false; |
| 225 | return $this->startup($id, $body); |
| 226 | } |
| 227 | |
| 228 | $msg = $this->recv($id); |
| 229 | if ($msg === null) return false; |
| 230 | [$type, $body] = $msg; |
| 231 | if ($type === 'X') return false; |
| 232 | |
| 233 | if ($cn['state'] === 'auth') return $this->authMessage($id, $type, $body); |
| 234 | return $this->message($id, $type, $body); |
| 235 | } |
| 236 | |
| 237 | private function startup(int $id, string $body): bool { |
| 238 | $cn = &$this->conns[$id]; |
| 239 | $code = unpack('N', $body)[1]; |
| 240 | |
| 241 | if ($code === 80877103) { // SSLRequest |
| 242 | if ($cn['tls'] || $this->certFile === '') { |
| 243 | $this->raw($id, 'N'); |
| 244 | return true; |
| 245 | } |
| 246 | $this->raw($id, 'S'); |
| 247 | try { |
| 248 | $cn['sock'] = Tls::accept($cn['sock'], ['cert' => $this->certFile, 'key' => $this->keyFile]); |
| 249 | } catch (\Throwable $e) { |
| 250 | return false; |
| 251 | } |
| 252 | $cn['tls'] = true; |
| 253 | return true; |
| 254 | } |
| 255 | if ($code === 80877104) { // GSSENCRequest |
| 256 | $this->raw($id, 'N'); |
| 257 | return true; |
| 258 | } |
| 259 | if ($code === 80877102) return false; // CancelRequest |
| 260 | if ($code !== 196608) throw new PgError('0A000', 'unsupported frontend protocol '.($code >> 16).'.'.($code & 0xFFFF), '', 'FATAL'); |
| 261 | |
| 262 | $kv = explode("\0", substr($body, 4)); |
| 263 | for ($i = 0; $i + 1 < count($kv) && $kv[$i] !== ''; $i += 2) $cn['params'][$kv[$i]] = $kv[$i + 1]; |
| 264 | $user = $cn['params']['user'] ?? ''; |
| 265 | if ($user === '') throw new PgError('28000', 'no PostgreSQL user name specified in startup packet', '', 'FATAL'); |
| 266 | $cn['user'] = $user; |
| 267 | $cn['db'] = $cn['params']['database'] ?? $user; |
| 268 | |
| 269 | [$pw, $method] = self::USERS[$user] ?? ['', 'scram']; |
| 270 | if ($user === 'tlsonly' && !$cn['tls']) { |
| 271 | throw new PgError('28000', "no pg_hba.conf entry for host \"127.0.0.1\", user \"{$user}\", database \"{$cn['db']}\", no encryption", '', 'FATAL'); |
| 272 | } |
| 273 | $cn['auth'] = $method; |
| 274 | $cn['state'] = 'auth'; |
| 275 | |
| 276 | switch ($method) { |
| 277 | case 'trust': |
| 278 | $this->ready($id); |
| 279 | return true; |
| 280 | case 'password': |
| 281 | $this->send($id, 'R', pack('N', 3)); |
| 282 | return true; |
| 283 | case 'md5': |
| 284 | $cn['salt'] = random_bytes(4); |
| 285 | $this->send($id, 'R', pack('N', 5).$cn['salt']); |
| 286 | return true; |
| 287 | case 'gss': |
| 288 | $this->send($id, 'R', pack('N', 7)); |
| 289 | return true; |
| 290 | } |
| 291 | $plus = $cn['tls'] && !in_array('nocb', $this->options, true); |
| 292 | $this->send($id, 'R', pack('N', 10).($plus ? "SCRAM-SHA-256-PLUS\0" : '')."SCRAM-SHA-256\0\0"); |
| 293 | return true; |
| 294 | } |
| 295 | |
| 296 | /** 認証中のメッセージ（PasswordMessage、SASLInitialResponse、SASLResponse） */ |
| 297 | private function authMessage(int $id, string $type, string $body): bool { |
| 298 | $cn = &$this->conns[$id]; |
| 299 | if ($type !== 'p') throw new PgError('08P01', "invalid frontend message type {$type}", '', 'FATAL'); |
| 300 | $user = $cn['user']; |
| 301 | $pw = self::USERS[$user][0] ?? null; |
| 302 | $failed = new PgError('28P01', "password authentication failed for user \"{$user}\"", '', 'FATAL'); |
| 303 | |
| 304 | if ($cn['auth'] === 'password') { |
| 305 | if ($pw === null || rtrim($body, "\0") !== $pw) throw $failed; |
| 306 | $this->ready($id); |
| 307 | return true; |
| 308 | } |
| 309 | if ($cn['auth'] === 'md5') { |
| 310 | if ($pw === null || rtrim($body, "\0") !== 'md5'.md5(md5($pw.$user).$cn['salt'])) throw $failed; |
| 311 | $this->ready($id); |
| 312 | return true; |
| 313 | } |
| 314 | |
| 315 | if ($cn['scram'] === null) { |
| 316 | // SASLInitialResponse |
| 317 | $end = strpos($body, "\0"); |
| 318 | $mech = substr($body, 0, $end); |
| 319 | $len = unpack('N', $body, $end + 1)[1]; |
| 320 | $data = substr($body, $end + 5, $len); |
| 321 | $plusOffered = $cn['tls'] && !in_array('nocb', $this->options, true); |
| 322 | if (!preg_match('/^([pny])(?:=([\w-]+))?,,(n=[^,]*,r=([^,]+))$/', $data, $m)) { |
| 323 | throw new PgError('08P01', 'malformed SCRAM message', '', 'FATAL'); |
| 324 | } |
| 325 | [, $flag, $cbType, $first, $nonce] = $m + ['', '', '', '', '']; |
| 326 | if ($mech === 'SCRAM-SHA-256-PLUS') { |
| 327 | if (!$plusOffered || $flag !== 'p' || $cbType !== 'tls-server-end-point') { |
| 328 | throw new PgError('08P01', 'unsupported SCRAM channel-binding type', '', 'FATAL'); |
| 329 | } |
| 330 | } else if ($mech === 'SCRAM-SHA-256') { |
| 331 | if ($flag === 'p') throw new PgError('08P01', 'channel binding not supported with SCRAM-SHA-256', '', 'FATAL'); |
| 332 | if ($flag === 'y' && $plusOffered) { |
| 333 | throw new PgError('08P01', 'SCRAM channel binding negotiation error', 'The client supports SCRAM channel binding but thinks the server does not. However, this server does support channel binding.', 'FATAL'); |
| 334 | } |
| 335 | } else { |
| 336 | throw new PgError('08P01', 'client selected an invalid SASL authentication mechanism', '', 'FATAL'); |
| 337 | } |
| 338 | $cn['cb'] = $flag === 'p' ? $cbType : ($flag === 'y' ? 'y' : 'none'); |
| 339 | $salt = random_bytes(16); |
| 340 | $snonce = $nonce.rtrim(base64_encode(random_bytes(18)), '='); |
| 341 | if (in_array('badnonce', $this->options, true)) $snonce = 'x'.substr($snonce, 1); |
| 342 | $serverFirst = 'r='.$snonce.',s='.base64_encode($salt).',i=4096'; |
| 343 | $cn['scram'] = ['gs2' => substr($data, 0, strlen($data) - strlen($first)), 'first' => $first, 'nonce' => $snonce, |
| 344 | 'server' => $serverFirst, 'salt' => $salt]; |
| 345 | $this->send($id, 'R', pack('N', 11).$serverFirst); |
| 346 | return true; |
| 347 | } |
| 348 | |
| 349 | // SASLResponse（client-final-message） |
| 350 | $s = $cn['scram']; |
| 351 | if (!preg_match('/^c=([^,]*),r=([^,]*),p=([^,]*)$/', $body, $m)) throw new PgError('08P01', 'malformed SCRAM message', '', 'FATAL'); |
| 352 | $cbData = $cn['cb'] === 'tls-server-end-point' ? $this->channelBinding() : ''; |
| 353 | if (base64_decode($m[1], true) !== $s['gs2'].$cbData) { |
| 354 | throw new PgError('08P01', 'SCRAM channel binding check failed', '', 'FATAL'); |
| 355 | } |
| 356 | if ($m[2] !== $s['nonce']) throw new PgError('08P01', 'SCRAM nonce mismatch', '', 'FATAL'); |
| 357 | if ($pw === null) throw $failed; |
| 358 | |
| 359 | $salted = hash_pbkdf2('sha256', $pw, $s['salt'], 4096, 32, true); |
| 360 | $clientKey = hash_hmac('sha256', 'Client Key', $salted, true); |
| 361 | $authMessage = $s['first'].','.$s['server'].',c='.$m[1].',r='.$m[2]; |
| 362 | $proof = (string)base64_decode($m[3], true); |
| 363 | $recovered = $proof ^ hash_hmac('sha256', $authMessage, hash('sha256', $clientKey, true), true); |
| 364 | if (strlen($proof) !== 32 || !hash_equals(hash('sha256', $clientKey, true), hash('sha256', $recovered, true))) throw $failed; |
| 365 | |
| 366 | $sig = hash_hmac('sha256', $authMessage, hash_hmac('sha256', 'Server Key', $salted, true), true); |
| 367 | if (in_array('badsig', $this->options, true)) $sig = ~$sig; |
| 368 | if (!in_array('nofinal', $this->options, true)) $this->send($id, 'R', pack('N', 12).'v='.base64_encode($sig)); |
| 369 | $this->ready($id); |
| 370 | return true; |
| 371 | } |
| 372 | |
| 373 | /** サーバーの証明書のハッシュ（RFC 5929） */ |
| 374 | private function channelBinding(): string { |
| 375 | $hash = match ($this->certSigAlg) { |
| 376 | '1.2.840.113549.1.1.12', '1.2.840.10045.4.3.3' => 'sha384', |
| 377 | '1.2.840.113549.1.1.13', '1.2.840.10045.4.3.4' => 'sha512', |
| 378 | default => 'sha256', |
| 379 | }; |
| 380 | return hash($hash, $this->certDer, true); |
| 381 | } |
| 382 | |
| 383 | /** 認証の完了：AuthenticationOk、ParameterStatus、BackendKeyData、ReadyForQuery */ |
| 384 | private function ready(int $id): void { |
| 385 | $cn = &$this->conns[$id]; |
| 386 | if ($cn['db'] === 'nodb') throw new PgError('3D000', "database \"{$cn['db']}\" does not exist", '', 'FATAL'); |
| 387 | $this->send($id, 'R', pack('N', 0)); |
| 388 | $status = [ |
| 389 | 'application_name' => $cn['params']['application_name'] ?? '', 'client_encoding' => $cn['params']['client_encoding'] ?? 'UTF8', |
| 390 | 'DateStyle' => $cn['params']['DateStyle'] ?? 'ISO, MDY', 'integer_datetimes' => 'on', 'IntervalStyle' => 'postgres', |
| 391 | 'is_superuser' => 'off', 'server_encoding' => 'UTF8', 'server_version' => self::VERSION, |
| 392 | 'session_authorization' => $cn['user'], 'standard_conforming_strings' => 'on', 'TimeZone' => 'Asia/Tokyo', |
| 393 | ]; |
| 394 | foreach ($status as $k => $v) $this->send($id, 'S', "{$k}\0{$v}\0"); |
| 395 | $this->send($id, 'K', pack('NN', $id, random_int(0, 0x7FFFFFFF))); |
| 396 | $cn['state'] = 'ready'; |
| 397 | $this->send($id, 'Z', 'I'); |
| 398 | } |
| 399 | |
| 400 | /////////////// |
| 401 | // クエリ |
| 402 | |
| 403 | private function message(int $id, string $type, string $body): bool { |
| 404 | $cn = &$this->conns[$id]; |
| 405 | // 拡張クエリのエラーの後はSyncまで無視する |
| 406 | if ($cn['skip'] && $type !== 'S') return true; |
| 407 | |
| 408 | try { |
| 409 | switch ($type) { |
| 410 | case 'Q': |
| 411 | return $this->simple($id, rtrim($body, "\0")); |
| 412 | case 'P': |
| 413 | $this->parse($id, $body); |
| 414 | return true; |
| 415 | case 'B': |
| 416 | $this->bind($id, $body); |
| 417 | return true; |
| 418 | case 'D': |
| 419 | $this->describe($id, $body[0], substr($body, 1, -1)); |
| 420 | return true; |
| 421 | case 'E': |
| 422 | $this->executePortal($id, rtrim(substr($body, 0, -4), "\0")); |
| 423 | return true; |
| 424 | case 'C': |
| 425 | if ($body[0] === 'S') { |
| 426 | unset($cn['stmts'][substr($body, 1, -1)]); |
| 427 | } else { |
| 428 | unset($cn['portals'][substr($body, 1, -1)]); |
| 429 | } |
| 430 | $this->send($id, '3', ''); |
| 431 | return true; |
| 432 | case 'S': |
| 433 | $cn['skip'] = false; |
| 434 | $cn['portals'] = []; |
| 435 | $this->send($id, 'Z', $cn['txn']); |
| 436 | return true; |
| 437 | case 'H': // Flush |
| 438 | return true; |
| 439 | } |
| 440 | throw new PgError('08P01', "invalid frontend message type {$type}", '', 'FATAL'); |
| 441 | } catch (PgError $e) { |
| 442 | if ($e->severity === 'FATAL') throw $e; |
| 443 | $this->error($id, $e); |
| 444 | $cn['skip'] = true; |
| 445 | return true; |
| 446 | } |
| 447 | } |
| 448 | |
| 449 | /** 単純クエリ */ |
| 450 | private function simple(int $id, string $sql): bool { |
| 451 | $cn = &$this->conns[$id]; |
| 452 | $stmts = array_values(array_filter(self::split($sql), fn($s) => trim($s) !== '')); |
| 453 | if (empty($stmts)) { |
| 454 | $this->send($id, 'I', ''); |
| 455 | $this->send($id, 'Z', $cn['txn']); |
| 456 | return true; |
| 457 | } |
| 458 | |
| 459 | foreach ($stmts as $one) { |
| 460 | try { |
| 461 | $res = $this->statement($id, trim($one), []); |
| 462 | } catch (PgError $e) { |
| 463 | if ($e->severity === 'FATAL') throw $e; |
| 464 | $this->error($id, $e); |
| 465 | break; |
| 466 | } |
| 467 | if ($res === false) return false; |
| 468 | if ($res['cols'] !== null) $this->send($id, 'T', $this->rowDescription($res['cols'])); |
| 469 | foreach ($res['rows'] as $row) $this->send($id, 'D', $this->dataRow($row)); |
| 470 | $this->send($id, 'C', $res['tag']."\0"); |
| 471 | } |
| 472 | $this->send($id, 'Z', $cn['txn']); |
| 473 | return true; |
| 474 | } |
| 475 | |
| 476 | private function parse(int $id, string $body): void { |
| 477 | $cn = &$this->conns[$id]; |
| 478 | $p = 0; |
| 479 | $name = self::cstr($body, $p); |
| 480 | $sql = self::cstr($body, $p); |
| 481 | $n = unpack('n', $body, $p)[1]; |
| 482 | $given = $n > 0 ? array_values(unpack("N{$n}", $body, $p + 2)) : []; |
| 483 | |
| 484 | if ($name !== '' && isset($cn['stmts'][$name])) throw new PgError('42P05', "prepared statement \"{$name}\" already exists"); |
| 485 | if (count(array_filter(self::split($sql), fn($s) => trim($s) !== '')) > 1) { |
| 486 | throw new PgError('42601', 'cannot insert multiple commands into a prepared statement'); |
| 487 | } |
| 488 | $sql = trim(rtrim(trim($sql), ';')); |
| 489 | $types = $this->paramTypes($sql); |
| 490 | foreach ($given as $i => $oid) if ($oid !== 0) $types[$i] = $oid; |
| 491 | // 構文・テーブルの確認（特別なSQLは実行の時に） |
| 492 | if (!preg_match('/^(BEGIN|START|COMMIT|END|ROLLBACK|SET|SHOW|COPY)\b|lb_\w+\(|setval\(|information_schema\./i', $sql)) { |
| 493 | try { |
| 494 | $this->pdo->prepare($this->rewrite($id, $sql)); |
| 495 | } catch (\PDOException $e) { |
| 496 | throw $this->sqlError($e); |
| 497 | } |
| 498 | } |
| 499 | $cn['stmts'][$name] = ['sql' => $sql, 'types' => $types]; |
| 500 | $this->send($id, '1', ''); |
| 501 | } |
| 502 | |
| 503 | private function bind(int $id, string $body): void { |
| 504 | $cn = &$this->conns[$id]; |
| 505 | $p = 0; |
| 506 | $portal = self::cstr($body, $p); |
| 507 | $name = self::cstr($body, $p); |
| 508 | if (!isset($cn['stmts'][$name])) throw new PgError('26000', "prepared statement \"{$name}\" does not exist"); |
| 509 | $stmt = $cn['stmts'][$name]; |
| 510 | |
| 511 | $nf = unpack('n', $body, $p)[1]; |
| 512 | $formats = $nf > 0 ? array_values(unpack("n{$nf}", $body, $p + 2)) : []; |
| 513 | $p += 2 + 2 * $nf; |
| 514 | $np = unpack('n', $body, $p)[1]; |
| 515 | $p += 2; |
| 516 | if ($np !== count($stmt['types'])) { |
| 517 | throw new PgError('08P01', "bind message supplies {$np} parameters, but prepared statement \"{$name}\" requires ".count($stmt['types'])); |
| 518 | } |
| 519 | $params = []; |
| 520 | for ($i = 0; $i < $np; $i++) { |
| 521 | $len = unpack('N', $body, $p)[1]; |
| 522 | $p += 4; |
| 523 | $format = $formats[$nf === 1 ? 0 : $i] ?? 0; |
| 524 | if ($len === 0xFFFFFFFF) { |
| 525 | $params[] = null; |
| 526 | continue; |
| 527 | } |
| 528 | $params[] = $this->decodeParam(substr($body, $p, $len), $format, $stmt['types'][$i]); |
| 529 | $p += $len; |
| 530 | } |
| 531 | $nr = unpack('n', $body, $p)[1]; |
| 532 | if ($nr > 0 && in_array(1, array_values(unpack("n{$nr}", $body, $p + 2)), true)) { |
| 533 | throw new PgError('0A000', 'binary result format is not supported by lbmock'); |
| 534 | } |
| 535 | |
| 536 | $res = $this->statement($id, $stmt['sql'], $params); |
| 537 | if ($res === false) throw new PgError('57P01', 'terminating connection', '', 'FATAL'); |
| 538 | $cn['portals'][$portal] = $res; |
| 539 | $this->send($id, '2', ''); |
| 540 | } |
| 541 | |
| 542 | private function describe(int $id, string $kind, string $name): void { |
| 543 | $cn = &$this->conns[$id]; |
| 544 | if ($kind === 'S') { |
| 545 | if (!isset($cn['stmts'][$name])) throw new PgError('26000', "prepared statement \"{$name}\" does not exist"); |
| 546 | $types = $cn['stmts'][$name]['types']; |
| 547 | $this->send($id, 't', pack('n', count($types)).(empty($types) ? '' : pack('N*', ...$types))); |
| 548 | $this->send($id, 'n', ''); |
| 549 | return; |
| 550 | } |
| 551 | if (!isset($cn['portals'][$name])) throw new PgError('34000', "portal \"{$name}\" does not exist"); |
| 552 | $cols = $cn['portals'][$name]['cols']; |
| 553 | $this->send($id, $cols === null ? 'n' : 'T', $cols === null ? '' : $this->rowDescription($cols)); |
| 554 | } |
| 555 | |
| 556 | private function executePortal(int $id, string $name): void { |
| 557 | $cn = &$this->conns[$id]; |
| 558 | if (!isset($cn['portals'][$name])) throw new PgError('34000', "portal \"{$name}\" does not exist"); |
| 559 | $res = $cn['portals'][$name]; |
| 560 | foreach ($res['rows'] as $row) $this->send($id, 'D', $this->dataRow($row)); |
| 561 | $this->send($id, 'C', $res['tag']."\0"); |
| 562 | } |
| 563 | |
| 564 | /** |
| 565 | * 1つの文を実行する |
| 566 | * |
| 567 | * @return array|false cols（nullなら行なし）、rows（テキストの値）、tag、又は接続を切る場合false |
| 568 | * @throws PgError |
| 569 | */ |
| 570 | private function statement(int $id, string $sql, array $params): array|false { |
| 571 | $cn = &$this->conns[$id]; |
| 572 | $sql = rtrim(trim($sql), ';'); |
| 573 | $none = fn(string $tag): array => ['cols' => null, 'rows' => [], 'tag' => $tag]; |
| 574 | $one = fn(string $name, int $oid, ?string $v): array => ['cols' => [[$name, $oid]], 'rows' => [[$v]], 'tag' => 'SELECT 1']; |
| 575 | |
| 576 | if (preg_match('/^(BEGIN|START\s+TRANSACTION)$/i', $sql)) { |
| 577 | if ($cn['txn'] !== 'I') { |
| 578 | $this->notice($id, 'WARNING', '25001', 'there is already a transaction in progress'); |
| 579 | } else { |
| 580 | $this->pdo->beginTransaction(); |
| 581 | $cn['txn'] = 'T'; |
| 582 | } |
| 583 | return $none('BEGIN'); |
| 584 | } |
| 585 | if (preg_match('/^(COMMIT|END|ROLLBACK)$/i', $sql, $m)) { |
| 586 | if ($cn['txn'] === 'I') { |
| 587 | $this->notice($id, 'WARNING', '25P01', 'there is no transaction in progress'); |
| 588 | return $none(strtoupper($m[1]) === 'ROLLBACK' ? 'ROLLBACK' : 'COMMIT'); |
| 589 | } |
| 590 | $commit = strtoupper($m[1]) !== 'ROLLBACK' && $cn['txn'] === 'T'; |
| 591 | $commit ? $this->pdo->commit() : $this->pdo->rollBack(); |
| 592 | $cn['txn'] = 'I'; |
| 593 | return $none($commit ? 'COMMIT' : 'ROLLBACK'); |
| 594 | } |
| 595 | if ($cn['txn'] === 'E') throw new PgError('25P02', 'current transaction is aborted, commands ignored until end of transaction block'); |
| 596 | |
| 597 | try { |
| 598 | if (preg_match('/^SET\s+(?:SESSION\s+)?(\w+)\s*(?:=|TO)\s*\'?([^\']*)\'?$/i', $sql, $m)) { |
| 599 | $key = ['application_name' => 'application_name', 'client_encoding' => 'client_encoding', 'datestyle' => 'DateStyle', 'timezone' => 'TimeZone'][strtolower($m[1])] ?? null; |
| 600 | if ($key !== null) { |
| 601 | $cn['params'][$key] = $m[2]; |
| 602 | $this->send($id, 'S', "{$key}\0{$m[2]}\0"); |
| 603 | } |
| 604 | return $none('SET'); |
| 605 | } |
| 606 | if (preg_match('/^SHOW\s+(\w+)$/i', $sql, $m)) { |
| 607 | $v = ['server_version' => self::VERSION, 'client_encoding' => $cn['params']['client_encoding'] ?? 'UTF8'][strtolower($m[1])] ?? ''; |
| 608 | return $one(strtolower($m[1]), 25, $v); |
| 609 | } |
| 610 | if (preg_match('/^COPY\s.*\sFROM\s+STDIN/is', $sql)) return $this->copyIn($id); |
| 611 | if (preg_match('/^COPY\s.*\sTO\s+STDOUT/is', $sql)) { |
| 612 | $this->send($id, 'H', "\0".pack('nn', 1, 0)); |
| 613 | $this->send($id, 'd', "1\t一\n"); |
| 614 | $this->send($id, 'c', ''); |
| 615 | return $none('COPY 1'); |
| 616 | } |
| 617 | if (preg_match('/^SELECT\s+table_name\s+FROM\s+information_schema\.tables\b/i', $sql)) { |
| 618 | $names = $this->pdo->query("SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name")->fetchAll(\PDO::FETCH_COLUMN); |
| 619 | return ['cols' => [['table_name', 25]], 'rows' => array_map(fn($n) => [$n], $names), 'tag' => 'SELECT '.count($names)]; |
| 620 | } |
| 621 | if (preg_match('/^SELECT\s+setval\(/i', $sql)) return $one('setval', 20, '1'); |
| 622 | if (preg_match('/^SELECT\s+lb_disconnect\(\)$/i', $sql)) return false; |
| 623 | if (preg_match('/^SELECT\s+lb_fatal\(\)$/i', $sql)) throw new PgError('57P01', 'terminating connection due to administrator command', '', 'FATAL'); |
| 624 | if (preg_match('/^SELECT\s+lb_notice\(\)$/i', $sql)) { |
| 625 | $this->notice($id, 'NOTICE', '00000', 'やあ'); |
| 626 | $this->notice($id, 'WARNING', '01000', 'w'); |
| 627 | return $one('lb_notice', 2278, ''); |
| 628 | } |
| 629 | if (preg_match('/^SELECT\s+lb_async\(\)$/i', $sql)) { |
| 630 | $this->send($id, 'A', pack('N', $id)."lbchan\0hi\0"); |
| 631 | $this->send($id, 'S', "TimeZone\0UTC\0"); |
| 632 | return $one('lb_async', 25, 'ok'); |
| 633 | } |
| 634 | if (preg_match('/^SELECT\s+lb_types\(\)$/i', $sql)) { |
| 635 | $cols = []; |
| 636 | foreach (self::TYPES as $k => [$oid]) $cols[] = [$k, $oid]; |
| 637 | return ['cols' => $cols, 'rows' => [array_column(array_values(self::TYPES), 1)], 'tag' => 'SELECT 1']; |
| 638 | } |
| 639 | if (preg_match('/lb_sleep\(([\d.]+)\)/i', $sql, $m)) { |
| 640 | usleep((int)((float)$m[1] * 1000000)); |
| 641 | $sql = preg_replace('/lb_sleep\([\d.]+\)/i', "''", $sql); |
| 642 | } |
| 643 | if (preg_match('/^DROP\s+TABLE\s+IF\s+EXISTS\s+(\w+)$/i', $sql, $m)) { |
| 644 | if (!$this->columns($m[1])) { |
| 645 | $this->notice($id, 'NOTICE', '00000', "table \"{$m[1]}\" does not exist, skipping"); |
| 646 | return $none('DROP TABLE'); |
| 647 | } |
| 648 | } |
| 649 | |
| 650 | $st = $this->pdo->prepare($this->rewrite($id, $sql)); |
| 651 | foreach ($params as $i => $v) { |
| 652 | $type = match (true) { |
| 653 | $v === null => \PDO::PARAM_NULL, |
| 654 | is_int($v) => \PDO::PARAM_INT, |
| 655 | $v instanceof Blob => \PDO::PARAM_LOB, |
| 656 | default => \PDO::PARAM_STR, |
| 657 | }; |
| 658 | $st->bindValue(':p'.($i + 1), $v instanceof Blob ? $v->data : $v, $type); |
| 659 | } |
| 660 | $st->execute(); |
| 661 | $rows = $st->columnCount() > 0 ? $st->fetchAll(\PDO::FETCH_NUM) : []; |
| 662 | } catch (\PDOException $e) { |
| 663 | if ($cn['txn'] === 'T') $cn['txn'] = 'E'; |
| 664 | throw $this->sqlError($e); |
| 665 | } catch (PgError $e) { |
| 666 | if ($cn['txn'] === 'T') $cn['txn'] = 'E'; |
| 667 | throw $e; |
| 668 | } |
| 669 | |
| 670 | $word = strtoupper((string)strtok($sql, " \t\r\n(")); |
| 671 | $count = $st->columnCount() > 0 ? count($rows) : $st->rowCount(); |
| 672 | $tag = match ($word) { |
| 673 | 'SELECT', 'VALUES', 'WITH' => "SELECT {$count}", |
| 674 | 'INSERT' => "INSERT 0 {$count}", |
| 675 | 'UPDATE', 'DELETE' => "{$word} {$count}", |
| 676 | 'CREATE', 'DROP', 'ALTER' => $word.' '.strtoupper(preg_split('/\s+/', $sql)[1] ?? ''), |
| 677 | default => $word, |
| 678 | }; |
| 679 | if ($st->columnCount() === 0) return $none($tag); |
| 680 | |
| 681 | // 列の型 |
| 682 | $cols = []; |
| 683 | for ($i = 0; $i < $st->columnCount(); $i++) { |
| 684 | $meta = $st->getColumnMeta($i) ?: []; |
| 685 | $decl = strtoupper(preg_replace('/\(.*$/', '', (string)($meta['sqlite:decl_type'] ?? ''))); |
| 686 | $oid = self::DECL[$decl] ?? (str_starts_with($decl, 'BOOL') ? 16 : null); |
| 687 | if ($oid === null && $decl !== '') $oid = 25; |
| 688 | if ($oid === null) { |
| 689 | // 式の列：値から |
| 690 | $oid = 25; |
| 691 | foreach ($rows as $r) { |
| 692 | if ($r[$i] === null) continue; |
| 693 | $oid = is_int($r[$i]) ? ($r[$i] >= -2147483648 && $r[$i] <= 2147483647 ? 23 : 20) : (is_float($r[$i]) ? 701 : 25); |
| 694 | break; |
| 695 | } |
| 696 | } |
| 697 | $cols[] = [(string)($meta['name'] ?? "?column?"), $oid]; |
| 698 | } |
| 699 | $out = []; |
| 700 | foreach ($rows as $r) { |
| 701 | $line = []; |
| 702 | foreach ($r as $i => $v) $line[] = $this->textValue($v, $cols[$i][1]); |
| 703 | $out[] = $line; |
| 704 | } |
| 705 | return ['cols' => $cols, 'rows' => $out, 'tag' => $tag]; |
| 706 | } |
| 707 | |
| 708 | /** COPY FROM STDIN：CopyData・CopyDone・CopyFailを待つ */ |
| 709 | private function copyIn(int $id): array { |
| 710 | $this->send($id, 'G', "\0".pack('nn', 1, 0)); |
| 711 | $n = 0; |
| 712 | while (true) { |
| 713 | $msg = $this->recv($id); |
| 714 | if ($msg === null) throw new PgError('08006', 'unexpected EOF on client connection with an open transaction', '', 'FATAL'); |
| 715 | [$type, $body] = $msg; |
| 716 | if ($type === 'd') { |
| 717 | $n += substr_count($body, "\n"); |
| 718 | } else if ($type === 'c') { |
| 719 | return ['cols' => null, 'rows' => [], 'tag' => "COPY {$n}"]; |
| 720 | } else if ($type === 'f') { |
| 721 | throw new PgError('57014', 'COPY from stdin failed: '.rtrim($body, "\0")); |
| 722 | } else if ($type !== 'H' && $type !== 'S') { |
| 723 | throw new PgError('08P01', "unexpected message type 0x".bin2hex($type)." during COPY from stdin"); |
| 724 | } |
| 725 | } |
| 726 | } |
| 727 | |
| 728 | /////////////// |
| 729 | // SQLite ⇔ PostgreSQL |
| 730 | |
| 731 | /** PostgreSQLの書き方をSQLiteにする、特別な関数を値にする */ |
| 732 | private function rewrite(int $id, string $sql): string { |
| 733 | $cn = $this->conns[$id]; |
| 734 | $q = fn(string $s): string => $this->pdo->quote($s); |
| 735 | $sql = preg_replace('/\b(?:BIG)?SERIAL\s+PRIMARY\s+KEY\b/i', 'INTEGER PRIMARY KEY AUTOINCREMENT', $sql); |
| 736 | |
| 737 | $sql = strtr($sql, [ |
| 738 | 'current_database()' => $q($cn['db']), |
| 739 | 'current_user' => $q($cn['user']), |
| 740 | 'pg_backend_pid()' => (string)$cn['id'], |
| 741 | 'version()' => $q('PostgreSQL '.self::VERSION), |
| 742 | 'lb_tls()' => $cn['tls'] ? '1' : '0', |
| 743 | 'lb_auth()' => $q($cn['auth']), |
| 744 | 'lb_cb()' => $q($cn['cb']), |
| 745 | ]); |
| 746 | |
| 747 | // 「$1」→「:p1」、「値::型」→「CAST(値 AS 型)」（文字列・識別子の中は変えない） |
| 748 | return preg_replace_callback('/((?:\$\d+|\'(?:[^\']|\'\')*\'|\w+)(?:::\w+(?:\[\])?)+)|\'(?:[^\']|\'\')*\'|"(?:[^"]|"")*"|\$(\d+)/', function(array $m): string { |
| 749 | if (isset($m[2]) && $m[2] !== '') return ':p'.$m[2]; |
| 750 | if (!isset($m[1]) || $m[1] === '') return $m[0]; |
| 751 | $parts = explode('::', $m[1]); |
| 752 | $v = array_shift($parts); |
| 753 | if (preg_match('/^\$(\d+)$/', $v, $p)) $v = ':p'.$p[1]; |
| 754 | foreach ($parts as $t) { |
| 755 | $t = strtoupper($t); |
| 756 | $aff = match (true) { |
| 757 | str_contains($t, 'INT') || str_starts_with($t, 'BOOL') => 'INTEGER', |
| 758 | str_starts_with($t, 'FLOAT') || in_array($t, ['REAL', 'DOUBLE', 'NUMERIC', 'DECIMAL'], true) => 'REAL', |
| 759 | $t === 'BYTEA' => 'BLOB', |
| 760 | default => 'TEXT', |
| 761 | }; |
| 762 | $v = "CAST({$v} AS {$aff})"; |
| 763 | } |
| 764 | return $v; |
| 765 | }, $sql); |
| 766 | } |
| 767 | |
| 768 | /** パラメータの型（「$1::型」、INSERTの列、「列 = $1」から、無ければtext） */ |
| 769 | private function paramTypes(string $sql): array { |
| 770 | preg_match_all('/\$(\d+)/', preg_replace('/\'(?:[^\']|\'\')*\'/', "''", $sql), $all); |
| 771 | $n = empty($all[1]) ? 0 : max(array_map('intval', $all[1])); |
| 772 | $types = array_fill(0, $n, 25); |
| 773 | $toOid = fn(string $t): int => self::DECL[strtoupper($t)] ?? (str_starts_with(strtoupper($t), 'BOOL') ? 16 : 25); |
| 774 | |
| 775 | // テーブルの列 |
| 776 | $cols = []; |
| 777 | if (preg_match_all('/\b(?:FROM|INTO|UPDATE|JOIN)\s+"?(\w+)"?/i', $sql, $t)) { |
| 778 | foreach ($t[1] as $table) $cols += $this->columns($table); |
| 779 | } |
| 780 | // INSERT INTO t (列, …) VALUES (…), (…)：「"」の名前、複数の行も |
| 781 | if (preg_match('/INSERT\s+INTO\s+"?\w+"?\s*\(([^)]*)\)\s*VALUES\s*(.*)$/is', $sql, $m)) { |
| 782 | $names = array_map(fn($n) => strtolower(trim($n, " \t\n\"")), explode(',', $m[1])); |
| 783 | preg_match_all('/\(([^()]*)\)/', $m[2], $tuples); |
| 784 | foreach ($tuples[1] as $tuple) { |
| 785 | foreach (array_map('trim', explode(',', $tuple)) as $i => $v) { |
| 786 | if (preg_match('/^\$(\d+)$/', $v, $p) && isset($cols[$names[$i] ?? ''])) { |
| 787 | $types[(int)$p[1] - 1] = $toOid($cols[$names[$i]]); |
| 788 | } |
| 789 | } |
| 790 | } |
| 791 | } |
| 792 | if (preg_match_all('/(\w+)\s*(?:=|<>|!=|<=|>=|<|>)\s*\$(\d+)(?![\d:])/', $sql, $mm, PREG_SET_ORDER)) { |
| 793 | foreach ($mm as $m) { |
| 794 | if (isset($cols[strtolower($m[1])])) $types[(int)$m[2] - 1] = $toOid($cols[strtolower($m[1])]); |
| 795 | } |
| 796 | } |
| 797 | if (preg_match_all('/\$(\d+)::(\w+)(\[\])?/', $sql, $mm, PREG_SET_ORDER)) { |
| 798 | foreach ($mm as $m) $types[(int)$m[1] - 1] = empty($m[3]) ? $toOid($m[2]) : 1009; |
| 799 | } |
| 800 | return $types; |
| 801 | } |
| 802 | |
| 803 | /** テーブルの列 => 宣言された型（小文字の名前） */ |
| 804 | private function columns(string $table): array { |
| 805 | $out = []; |
| 806 | try { |
| 807 | foreach ($this->pdo->query('PRAGMA table_info('.$this->pdo->quote($table).')')->fetchAll(\PDO::FETCH_ASSOC) as $c) { |
| 808 | $out[strtolower($c['name'])] = preg_replace('/\(.*$/', '', $c['type']); |
| 809 | } |
| 810 | } catch (\PDOException $e) { |
| 811 | // 無い |
| 812 | } |
| 813 | return $out; |
| 814 | } |
| 815 | |
| 816 | /** パラメータ（テキスト又はバイナリ）をSQLiteの値にする */ |
| 817 | private function decodeParam(string $v, int $format, int $oid): mixed { |
| 818 | if ($format === 1) { |
| 819 | if ($oid !== 17) throw new PgError('0A000', "binary format for type {$oid} is not supported by lbmock"); |
| 820 | return new Blob($v); |
| 821 | } |
| 822 | switch ($oid) { |
| 823 | case 16: |
| 824 | $b = strtolower(trim($v)); |
| 825 | if (in_array($b, ['t', 'true', 'y', 'yes', 'on', '1'], true)) return 1; |
| 826 | if (in_array($b, ['f', 'false', 'n', 'no', 'off', '0'], true)) return 0; |
| 827 | throw new PgError('22P02', "invalid input syntax for type boolean: \"{$v}\""); |
| 828 | case 20: |
| 829 | case 21: |
| 830 | case 23: |
| 831 | if (!preg_match('/^\s*[+-]?\d+\s*$/', $v)) throw new PgError('22P02', "invalid input syntax for type integer: \"{$v}\""); |
| 832 | return (int)$v; |
| 833 | case 700: |
| 834 | case 701: |
| 835 | if (!is_numeric($v) && !in_array($v, ['NaN', 'Infinity', '-Infinity'], true)) { |
| 836 | throw new PgError('22P02', "invalid input syntax for type double precision: \"{$v}\""); |
| 837 | } |
| 838 | return is_numeric($v) ? (float)$v : $v; |
| 839 | } |
| 840 | // テキストはUTF-8のみ（0x00も不可） |
| 841 | if (!mb_check_encoding($v, 'UTF-8') || str_contains($v, "\0")) { |
| 842 | throw new PgError('22021', 'invalid byte sequence for encoding "UTF8"'); |
| 843 | } |
| 844 | if ($oid === 17) { |
| 845 | // テキスト形式のbytea（\x…、又はescape形式） |
| 846 | if (str_starts_with($v, '\\x')) return new Blob((string)hex2bin(substr($v, 2))); |
| 847 | $bad = false; |
| 848 | $out = preg_replace_callback('/\\\\(\\\\|[0-3][0-7]{2}|.?)/s', function(array $m) use (&$bad): string { |
| 849 | if ($m[1] === '\\') return '\\'; |
| 850 | if (strlen($m[1]) === 3) return chr(octdec($m[1])); |
| 851 | $bad = true; |
| 852 | return ''; |
| 853 | }, $v); |
| 854 | if ($bad) throw new PgError('22P02', 'invalid input syntax for type bytea'); |
| 855 | return new Blob($out); |
| 856 | } |
| 857 | return $v; |
| 858 | } |
| 859 | |
| 860 | /** SQLiteの値をPostgreSQLのテキストにする */ |
| 861 | private function textValue(mixed $v, int $oid): ?string { |
| 862 | if ($v === null) return null; |
| 863 | return match ($oid) { |
| 864 | 16 => in_array($v, [1, '1', 't', 'true'], true) ? 't' : 'f', |
| 865 | 17 => '\\x'.bin2hex((string)$v), |
| 866 | 700, 701 => is_string($v) && !is_numeric($v) ? $v : self::floatText((float)$v), |
| 867 | default => (string)$v, |
| 868 | }; |
| 869 | } |
| 870 | |
| 871 | private static function floatText(float $f): string { |
| 872 | if (is_nan($f)) return 'NaN'; |
| 873 | if (is_infinite($f)) return $f > 0 ? 'Infinity' : '-Infinity'; |
| 874 | return str_replace(['.0E', 'E'], ['e', 'e'], (string)$f); |
| 875 | } |
| 876 | |
| 877 | private function sqlError(\PDOException $e): PgError { |
| 878 | $msg = $e->getMessage(); |
| 879 | if (preg_match('/no such table: (\S+)/', $msg, $m)) return new PgError('42P01', "relation \"{$m[1]}\" does not exist"); |
| 880 | if (preg_match('/UNIQUE constraint failed: (\w+)\.(\w+)/', $msg, $m)) { |
| 881 | return new PgError('23505', "duplicate key value violates unique constraint \"{$m[1]}_{$m[2]}_key\"", "Key ({$m[2]}) already exists."); |
| 882 | } |
| 883 | if (preg_match('/NOT NULL constraint failed: (\w+)\.(\w+)/', $msg, $m)) { |
| 884 | return new PgError('23502', "null value in column \"{$m[2]}\" of relation \"{$m[1]}\" violates not-null constraint"); |
| 885 | } |
| 886 | if (preg_match('/no such column: (\S+)/', $msg, $m)) return new PgError('42703', "column \"{$m[1]}\" does not exist"); |
| 887 | if (preg_match('/near "([^"]*)": syntax error/', $msg, $m)) return new PgError('42601', "syntax error at or near \"{$m[1]}\""); |
| 888 | if (preg_match('/already exists/', $msg)) return new PgError('42P07', preg_replace('/^SQLSTATE\[\w+\]: [^:]*: \d+ /', '', $msg)); |
| 889 | return new PgError('XX000', preg_replace('/^SQLSTATE\[\w+\]: [^:]*: \d+ /', '', $msg)); |
| 890 | } |
| 891 | |
| 892 | /** セミコロンで文を分ける（文字列・識別子・$$・コメントの中は分けない） */ |
| 893 | private static function split(string $sql): array { |
| 894 | $out = []; |
| 895 | $cur = ''; |
| 896 | $len = strlen($sql); |
| 897 | for ($i = 0; $i < $len; $i++) { |
| 898 | $c = $sql[$i]; |
| 899 | if ($c === "'" || $c === '"') { |
| 900 | $j = $i + 1; |
| 901 | while ($j < $len && !($sql[$j] === $c && ($sql[$j + 1] ?? '') !== $c)) $j += $sql[$j] === $c ? 2 : 1; |
| 902 | $cur .= substr($sql, $i, $j - $i + 1); |
| 903 | $i = $j; |
| 904 | } else if ($c === '$' && preg_match('/\G\$(\w*)\$/', $sql, $m, 0, $i) && !ctype_digit($m[1])) { |
| 905 | $end = strpos($sql, $m[0], $i + strlen($m[0])); |
| 906 | $j = $end === false ? $len : $end + strlen($m[0]); |
| 907 | $cur .= substr($sql, $i, $j - $i); |
| 908 | $i = $j - 1; |
| 909 | } else if ($c === '-' && ($sql[$i + 1] ?? '') === '-') { |
| 910 | $j = strpos($sql, "\n", $i); |
| 911 | $i = $j === false ? $len : $j; |
| 912 | } else if ($c === ';') { |
| 913 | $out[] = $cur; |
| 914 | $cur = ''; |
| 915 | } else { |
| 916 | $cur .= $c; |
| 917 | } |
| 918 | } |
| 919 | $out[] = $cur; |
| 920 | return $out; |
| 921 | } |
| 922 | |
| 923 | /////////////// |
| 924 | // メッセージ |
| 925 | |
| 926 | private function rowDescription(array $cols): string { |
| 927 | $b = pack('n', count($cols)); |
| 928 | foreach ($cols as [$name, $oid]) { |
| 929 | $size = [16 => 1, 20 => 8, 21 => 2, 23 => 4, 700 => 4, 701 => 8, 1082 => 4, 1114 => 8, 1184 => 8][$oid] ?? -1; |
| 930 | $b .= $name."\0".pack('NnNnNn', 0, 0, $oid, $size & 0xFFFF, 0xFFFFFFFF, 0); |
| 931 | } |
| 932 | return $b; |
| 933 | } |
| 934 | |
| 935 | private function dataRow(array $row): string { |
| 936 | $b = pack('n', count($row)); |
| 937 | foreach ($row as $v) $b .= $v === null ? pack('N', 0xFFFFFFFF) : pack('N', strlen($v)).$v; |
| 938 | return $b; |
| 939 | } |
| 940 | |
| 941 | private function notice(int $id, string $severity, string $code, string $msg): void { |
| 942 | $this->send($id, 'N', "S{$severity}\0V{$severity}\0C{$code}\0M{$msg}\0\0"); |
| 943 | } |
| 944 | |
| 945 | private function error(int $id, PgError $e): void { |
| 946 | $b = "S{$e->severity}\0V{$e->severity}\0C{$e->sqlState}\0M{$e->getMessage()}\0"; |
| 947 | if ($e->detail !== '') $b .= "D{$e->detail}\0"; |
| 948 | $this->send($id, 'E', $b."\0"); |
| 949 | } |
| 950 | |
| 951 | private static function cstr(string $b, int &$pos): string { |
| 952 | $end = strpos($b, "\0", $pos); |
| 953 | $s = substr($b, $pos, $end - $pos); |
| 954 | $pos = $end + 1; |
| 955 | return $s; |
| 956 | } |
| 957 | |
| 958 | private function send(int $id, string $type, string $body): void { |
| 959 | $this->raw($id, $type.pack('N', strlen($body) + 4).$body); |
| 960 | } |
| 961 | |
| 962 | private function raw(int $id, string $data): void { |
| 963 | for ($w = 0; $w < strlen($data);) { |
| 964 | $n = @fwrite($this->conns[$id]['sock'], substr($data, $w, 1048576)); |
| 965 | if ($n === false || $n === 0) return; |
| 966 | $w += $n; |
| 967 | } |
| 968 | } |
| 969 | |
| 970 | private function readRaw(int $id, int $n): ?string { |
| 971 | $buf = ''; |
| 972 | while (strlen($buf) < $n) { |
| 973 | $x = @fread($this->conns[$id]['sock'], $n - strlen($buf)); |
| 974 | if ($x === false || $x === '') return null; |
| 975 | $buf .= $x; |
| 976 | } |
| 977 | return $buf; |
| 978 | } |
| 979 | |
| 980 | private function recv(int $id): ?array { |
| 981 | $head = $this->readRaw($id, 5); |
| 982 | if ($head === null) return null; |
| 983 | $len = unpack('N', $head, 1)[1]; |
| 984 | if ($len < 4) return null; |
| 985 | $body = $len > 4 ? $this->readRaw($id, $len - 4) : ''; |
| 986 | return $body === null ? null : [$head[0], $body]; |
| 987 | } |
| 988 | } |
| 989 | |
| 990 | /** byteaの値（SQLiteにBLOBとして渡す） */ |
| 991 | class Blob { |
| 992 | public function __construct(public string $data) {} |
| 993 | } |