suwako / LittleBeast

master
ブランチ master
LittleBeast / src / Std / Test / LibPgsql.php
32.2 KB、672行 生 Blame 履歴
1<?php
2/*************************************************************
3# 076 License
4
5Copyright (c) テクニカル諏訪子
6
7Permission is hereby granted to any person obtaining a copy of the software
8Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
9sublicense, and/or sell copies of the Software, subject to the following conditions:
10
11 1. **Origin Attribution**:
12 - You must not misrepresent the origin of the Software; you must not claim
13 you created the original Software.
14 - If the Software is used in a product, you must either:
15 a. Provide clear attribution in the product's documentation, user interface,
16 or other visible areas, **OR**
17 b. Pay the original developers a fee they specify in writing.
18 2. **Usage Restriction**:
19 - The Software, or any derivative works, dependencies, or libraries
20 incorporating it, must not be used for censorship or to suppress freedom of
21 speech, expression, or creativity. Prohibited uses include, but are not
22 limited to:
23 - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
24 ideas, or objective reality.
25 - Tools or systems designed to restrict access to information or
26 artistic works.
27 3. **Notice Preservation**:
28 - This license and the above copyright notice must remain intact in all copies
29 of the source code.
30
31THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
32INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
33PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
34HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
35CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
36OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
37 */
38namespace Std\Test;
39
40require_once __DIR__.'/../../../autoload.php';
41
42use Std\Lib\Tester;
43use Std\Lib\Db;
44use Std\Lib\Db\DbException;
45use Std\Lib\Db\Pgsql;
46use Std\Test\Helper\MockServer;
47use Std\Test\Helper\TestCert;
48
49// 本物のサーバーでも試す場合（任意）：LB_TEST_PGSQL=pgsql://ユーザー:パスワード@ホスト:ポート/データベース
50// 「lb_test_」で始まるテーブルを作って消します。
51
52$test = new Tester([
53 'colorOutput' => true,
54 'verboseOutput' => true
55]);
56
57enum LibPgsqlColor: string {
58 case Red = 'あか';
59}
60
61// 例外を受け取る（SQLSTATE・メッセージを確認する為）
62$catch = function(callable $fn): ?\Throwable {
63 try {
64 $fn();
65 } catch (\Throwable $e) {
66 return $e;
67 }
68 return null;
69};
70
71// 偽物と本物の両方のサーバーで同じ結果になるはずのテスト
72$common = function(Tester $test, callable $info) use ($catch): void {
73 $open = function(array $o = []) use ($info): Pgsql {
74 $pg = new Pgsql($info($o));
75 $pg->connect();
76 return $pg;
77 };
78 $one = fn(Pgsql $pg, string $sql) => array_values($pg->query($sql)['rows'][0] ?? [])[0] ?? null;
79 // プリペアドステートメントで1回だけ実行する
80 $run = function(Pgsql $pg, string $sql, array $params = []): array {
81 $id = $pg->prepare($sql);
82 try {
83 return $pg->execute($id, $params);
84 } finally {
85 $pg->demolish($id);
86 }
87 };
88
89 $test->it('接続して切断出来るはず', function($test) use ($open, $one, $info): void {
90 $pg = $open();
91 $test->assertTrue($pg->isConnected());
92 $test->assertTrue($pg->connect()); // 2回目は何もしない
93 $si = $pg->getServerInfo();
94 $test->assertSame('UTF8', $si['client_encoding']);
95 $test->assertSame('LittleBeast', $si['application_name']);
96 $test->assertTrue($si['server_version'] !== '');
97 $test->assertSame($si['pid'], $one($pg, 'SELECT pg_backend_pid() AS pid'));
98 $test->assertSame($info()['username'], $one($pg, 'SELECT current_user AS u'));
99 $test->assertSame($info()['dbname'], $one($pg, 'SELECT current_database() AS d'));
100 $test->assertTrue($pg->ping());
101 $test->assertFalse($pg->inTransaction());
102
103 $pg->close();
104 $test->assertFalse($pg->isConnected());
105 $test->assertFalse($pg->ping());
106 $pg->close(); // 2回目は何もしない
107 });
108
109 $test->it('テーブルを作って、データを入れて読めるはず', function($test) use ($open, $run): void {
110 $pg = $open();
111 $pg->query('DROP TABLE IF EXISTS lb_test_items');
112 $res = $pg->query('CREATE TABLE lb_test_items (id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE, n INT8, f FLOAT8, b BOOLEAN, d BYTEA)');
113 $test->assertSame('CREATE TABLE', $res['command']);
114 $test->assertSame(0, $res['affectedRows']);
115 $test->assertSame([], $res['rows']);
116
117 $bin = "\x00\xff\\x'\"\n";
118 $id = $pg->prepare('INSERT INTO lb_test_items (name, n, f, b, d) VALUES (?, ?, ?, ?, ?) RETURNING id');
119 $res = $pg->execute($id, ['諏訪子', PHP_INT_MAX, 1.5, true, $bin]);
120 $test->assertSame('INSERT 0 1', $res['command']);
121 $test->assertSame(1, $res['affectedRows']);
122 $test->assertSame(0, $res['insertId']);
123 $test->assertSame([['id' => 1]], $res['rows']);
124 $test->assertSame(Pgsql::OID_INT4, $res['fields'][0]['type']);
125 $test->assertSame([['id' => 2]], $pg->execute($id, ['神奈子', null, -0.25, false, ''])['rows']);
126 $test->assertSame([['id' => 3]], $pg->execute($id, ['早苗', -7, null, null, null])['rows']);
127 $pg->demolish($id);
128
129 $res = $pg->query('SELECT * FROM lb_test_items ORDER BY id');
130 $test->assertSame('SELECT 3', $res['command']);
131 $test->assertSame(3, $res['affectedRows']);
132 $test->assertSame(['id', 'name', 'n', 'f', 'b', 'd'], array_column($res['fields'], 'name'));
133 $test->assertSame([Pgsql::OID_INT4, Pgsql::OID_TEXT, Pgsql::OID_INT8, Pgsql::OID_FLOAT8, Pgsql::OID_BOOL, Pgsql::OID_BYTEA], array_column($res['fields'], 'type'));
134 $test->assertSame([
135 ['id' => 1, 'name' => '諏訪子', 'n' => PHP_INT_MAX, 'f' => 1.5, 'b' => true, 'd' => $bin],
136 ['id' => 2, 'name' => '神奈子', 'n' => null, 'f' => -0.25, 'b' => false, 'd' => ''],
137 ['id' => 3, 'name' => '早苗', 'n' => -7, 'f' => null, 'b' => null, 'd' => null],
138 ], $res['rows']);
139
140 // 拡張クエリの結果も同じ
141 $test->assertSame($res['rows'], $run($pg, 'SELECT * FROM lb_test_items WHERE id > ? ORDER BY id', [0])['rows']);
142 $test->assertSame([['name' => '神奈子']], $run($pg, 'SELECT name FROM lb_test_items WHERE b = ?', [false])['rows']);
143 $test->assertSame([], $run($pg, 'SELECT name FROM lb_test_items WHERE id = ?', [99])['rows']);
144
145 $res = $run($pg, 'UPDATE lb_test_items SET n = ? WHERE id > ?', [5, 1]);
146 $test->assertSame('UPDATE 2', $res['command']);
147 $test->assertSame(2, $res['affectedRows']);
148 $test->assertSame([], $res['fields']);
149 $test->assertSame(1, $run($pg, 'DELETE FROM lb_test_items WHERE id = ?', [3])['affectedRows']);
150 });
151
152 $test->it('「?」のプレースホルダは文字列の中で変えないはず', function($test) use ($open, $run): void {
153 $pg = $open();
154 $res = $run($pg, "SELECT name FROM lb_test_items WHERE name = ? AND 'a?' <> ? -- ?\n", ['諏訪子', 'b']);
155 $test->assertSame([['name' => '諏訪子']], $res['rows']);
156 $res = $run($pg, 'SELECT name FROM lb_test_items WHERE id = $1 OR id = $1', [2]);
157 $test->assertSame([['name' => '神奈子']], $res['rows']);
158 });
159
160 $test->it('PHPの色々な型をパラメータとして送れるはず', function($test) use ($open, $run, $catch): void {
161 $pg = $open();
162 $pg->query('DROP TABLE IF EXISTS lb_test_params');
163 $pg->query('CREATE TABLE lb_test_params (id SERIAL PRIMARY KEY, s TEXT, i INT8, f FLOAT8)');
164 $ins = $pg->prepare('INSERT INTO lb_test_params (s, i, f) VALUES (?, ?, ?) RETURNING id');
165 $get = $pg->prepare('SELECT s, i, f FROM lb_test_params WHERE id = ?');
166 $stringable = new class {
167 public function __toString(): string {
168 return '文字列';
169 }
170 };
171 $cases = [
172 [[42, 42, 42], ['42', 42, 42.0]],
173 [['', PHP_INT_MIN, -0.1], ['', PHP_INT_MIN, -0.1]],
174 [[1.25, true, 1e100], ['1.25', 1, 1.0E+100]],
175 [[false, false, NAN], ['f', 0, NAN]],
176 [[new \DateTimeImmutable('2026-11-12 10:20:30.5', new \DateTimeZone('Asia/Tokyo')), null, INF], ['2026-11-12 10:20:30.500000+09:00', null, INF]],
177 [[LibPgsqlColor::Red, null, -INF], ['あか', null, -INF]],
178 [[$stringable, null, null], ['文字列', null, null]],
179 ];
180 foreach ($cases as [$in, $out]) {
181 $id = $pg->execute($ins, $in)['rows'][0]['id'];
182 $row = array_values($pg->execute($get, [$id])['rows'][0]);
183 $label = 'パラメータ：'.var_export($in[0], true);
184 $test->assertSame($out[0], $row[0], $label);
185 $test->assertSame($out[1], $row[1], $label);
186 is_float($out[2]) && is_nan($out[2]) ? $test->assertTrue(is_nan($row[2]), $label) : $test->assertSame($out[2], $row[2], $label);
187 }
188
189 // 連想配列でも順番通り
190 $id = $pg->execute($ins, ['x' => 'あ', 'y' => 1, 'z' => 2])['rows'][0]['id'];
191 $test->assertSame(['s' => 'あ', 'i' => 1, 'f' => 2.0], $pg->execute($get, [$id])['rows'][0]);
192
193 $e = $catch(fn() => $pg->execute($ins, [[1, 2], 1, 1]));
194 $test->assertTrue($e instanceof DbException);
195 $test->assertStringContains('パラメータ0の型は未対応です: array', $e->getMessage());
196 $e = $catch(fn() => $pg->execute($ins, ['足りない']));
197 $test->assertStringContains('パラメータ数が一致しません: 期待 3, 取得 1', $e->getMessage());
198
199 $test->assertTrue($pg->demolish($ins));
200 $test->assertFalse($pg->demolish($ins));
201 $e = $catch(fn() => $pg->execute($ins, ['x', 1, 1]));
202 $test->assertStringContains("不正なステートメントID: {$ins}", $e->getMessage());
203 $test->assertTrue($pg->ping());
204 });
205
206 $test->it('SQLのエラーは例外で、その後も使えるはず', function($test) use ($open, $one, $catch): void {
207 $pg = $open();
208 $e = $catch(fn() => $pg->query('SELECT * FROM lb_test_nope'));
209 $test->assertTrue($e instanceof DbException);
210 $test->assertSame('42P01', $e->sqlState);
211 $test->assertSame('クエリに失敗: 42P01 - relation "lb_test_nope" does not exist', $e->getMessage());
212
213 $e = $catch(fn() => $pg->query('SELEC 1'));
214 $test->assertSame('42601', $e->sqlState);
215 $test->assertStringContains('syntax error at or near "SELEC"', $e->getMessage());
216
217 $e = $catch(fn() => $pg->prepare('SELECT * FROM lb_test_nope WHERE id = ?'));
218 $test->assertStringContains('準備に失敗: 42P01', $e->getMessage());
219
220 $id = $pg->prepare('INSERT INTO lb_test_items (name) VALUES (?)');
221 $e = $catch(fn() => $pg->execute($id, ['諏訪子']));
222 $test->assertSame('23505', $e->sqlState);
223 $test->assertStringContains('実行に失敗: 23505 - duplicate key value violates unique constraint "lb_test_items_name_key"', $e->getMessage());
224 $test->assertStringContains('Key (name)', $e->getMessage());
225 $e = $catch(fn() => $pg->execute($id, [null]));
226 $test->assertSame('23502', $e->sqlState);
227
228 $test->assertTrue($pg->isConnected());
229 $test->assertFalse($pg->inTransaction());
230 $test->assertSame(2, $one($pg, 'SELECT count(*) AS c FROM lb_test_items'));
231 });
232
233 $test->it('複数の文の結果を全部受け取れるはず', function($test) use ($open): void {
234 $pg = $open();
235 $res = $pg->query("SELECT 1 AS a; UPDATE lb_test_items SET n = 6; SELECT '二' AS b;");
236 $test->assertSame([['a' => 1]], $res['rows']);
237 $test->assertCount(2, $res['more']);
238 $test->assertSame('UPDATE 2', $res['more'][0]['command']);
239 $test->assertSame(2, $res['more'][0]['affectedRows']);
240 $test->assertSame([['b' => '二']], $res['more'][1]['rows']);
241 $test->assertFalse(isset($res['more'][1]['more']));
242
243 // 空のクエリ
244 $res = $pg->query('');
245 $test->assertSame('', $res['command']);
246 $test->assertSame([], $res['rows']);
247 });
248
249 $test->it('通知を受け取れるはず', function($test) use ($open): void {
250 $pg = $open();
251 $res = $pg->query('DROP TABLE IF EXISTS lb_test_none');
252 $test->assertSame(1, $res['warningCount']);
253 $test->assertSame([['severity' => 'NOTICE', 'code' => '00000', 'message' => 'table "lb_test_none" does not exist, skipping']], $pg->getNotices());
254 $pg->query('SELECT 1');
255 $test->assertSame([], $pg->getNotices());
256 $pg->query('COMMIT');
257 $test->assertSame('25P01', $pg->getNotices()[0]['code']);
258 });
259
260 $test->it('トランザクションを使えるはず', function($test) use ($open, $one, $catch): void {
261 $pg = $open();
262 $count = fn(): int => $one($pg, 'SELECT count(*) AS c FROM lb_test_items');
263
264 $pg->beginTransaction();
265 $test->assertTrue($pg->inTransaction());
266 $pg->query("INSERT INTO lb_test_items (name) VALUES ('あ')");
267 $pg->rollback();
268 $test->assertFalse($pg->inTransaction());
269 $test->assertSame(2, $count());
270
271 $pg->beginTransaction();
272 $pg->query("INSERT INTO lb_test_items (name) VALUES ('い')");
273 $test->assertTrue($pg->commit());
274 $test->assertSame(3, $count());
275
276 // エラーの後は失敗したトランザクション：COMMITはロールバックになるので例外
277 $pg->beginTransaction();
278 $pg->query("INSERT INTO lb_test_items (name) VALUES ('う')");
279 $catch(fn() => $pg->query('SELECT * FROM lb_test_nope'));
280 $test->assertTrue($pg->inTransaction());
281 $e = $catch(fn() => $pg->query('SELECT 1'));
282 $test->assertSame('25P02', $e->sqlState);
283 $e = $catch(fn() => $pg->commit());
284 $test->assertTrue($e instanceof DbException);
285 $test->assertSame('25P02', $e->sqlState);
286 $test->assertStringContains('ロールバックしました', $e->getMessage());
287 $test->assertFalse($pg->inTransaction());
288 $test->assertSame(3, $count());
289 });
290
291 $test->it('COPYは断るはず（接続はそのまま）', function($test) use ($open, $one, $catch): void {
292 $pg = $open();
293 $e = $catch(fn() => $pg->query('COPY lb_test_items (name) FROM STDIN'));
294 $test->assertTrue($e instanceof DbException);
295 $test->assertSame('57014', $e->sqlState);
296 $test->assertStringContains('COPYは未対応です', $e->getMessage());
297 $test->assertSame(1, $one($pg, 'SELECT 1 AS x'));
298 });
299
300 $test->it('長い値を送受信出来るはず', function($test) use ($open, $run): void {
301 $pg = $open();
302 $pg->query('DROP TABLE IF EXISTS lb_test_big');
303 $pg->query('CREATE TABLE lb_test_big (id SERIAL PRIMARY KEY, t TEXT, d BYTEA)');
304 $text = str_repeat('あいう', 400000);
305 $bin = random_bytes(1500000);
306 $id = $run($pg, 'INSERT INTO lb_test_big (t, d) VALUES (?, ?) RETURNING id', [$text, $bin])['rows'][0]['id'];
307 $row = $run($pg, 'SELECT t, d FROM lb_test_big WHERE id = ?', [$id])['rows'][0];
308 $test->assertSame(strlen($text), strlen($row['t']));
309 $test->assertTrue($row['t'] === $text && $row['d'] === $bin, '値が違う');
310 $test->assertTrue($pg->query("SELECT d FROM lb_test_big WHERE id = {$id}")['rows'][0]['d'] === $bin, '値が違う（単純クエリ）');
311 });
312
313 $test->it('Db経由で使えるはず', function($test) use ($info, $catch): void {
314 $db = new Db(['driver' => 'pgsql'] + $info());
315 $test->assertSame('pgsql', $db->driverName);
316 $test->assertSame('"a""b"', $db->quoteIdentifier('a"b'));
317 $test->assertSame([['c' => 2]], $db->query('SELECT count(*) AS c FROM lb_test_items WHERE id > ? AND id < ?', [0, 3])['rows']);
318
319 $db->transaction(fn(Db $db) => $db->query('UPDATE lb_test_items SET n = ? WHERE id = ?', [100, 1]));
320 $test->assertSame(100, $db->query('SELECT n FROM lb_test_items WHERE id = 1')['rows'][0]['n']);
321 $e = $catch(fn() => $db->transaction(function(Db $db): void {
322 $db->query('UPDATE lb_test_items SET n = ? WHERE id = ?', [200, 1]);
323 throw new \RuntimeException('やめる');
324 }));
325 $test->assertSame('やめる', $e->getMessage());
326 $test->assertSame(100, $db->query('SELECT n FROM lb_test_items WHERE id = ?', [1])['rows'][0]['n']);
327
328 // query()のパラメータ付きはステートメントを解放する（失敗しても）
329 $e = $catch(fn() => $db->query('SELECT * FROM lb_test_nope WHERE id = ?', [1]));
330 $test->assertSame('42P01', $e->sqlState);
331 $test->assertTrue($db->ping());
332
333 $db->query('DROP TABLE lb_test_items');
334 $db->query('DROP TABLE lb_test_params');
335 $db->query('DROP TABLE lb_test_big');
336 $db->close();
337 $test->assertFalse($db->isConnected());
338 });
339};
340
341
342$test->describe('Pgsql（サーバーなし）', function($test) use ($catch): void {
343 $test->it('DB_ENABLEDが無効なら設定なしで作れないはず', function($test): void {
344 if (defined('DB_ENABLED') && DB_ENABLED) {
345 $test->assertNotNull(new Pgsql());
346 return;
347 }
348 $test->assertThrows(fn() => new Pgsql(), \Exception::class);
349 });
350
351 $test->it('Dbでドライバを選べるはず', function($test): void {
352 foreach (['pgsql', 'postgres', 'PostgreSQL'] as $name) {
353 $test->assertTrue((new Db(['driver' => $name]))->driver() instanceof Pgsql, $name);
354 $test->assertTrue(Db::isSupported($name), $name);
355 }
356 });
357
358 $test->it('不正なchannel_bindingは例外のはず', function($test): void {
359 $test->assertThrows(fn() => new Pgsql(['channel_binding' => 'always']), \InvalidArgumentException::class);
360 $test->assertNotNull(new Pgsql(['channel_binding' => 'REQUIRE']));
361 });
362
363 $test->it('識別子を囲めるはず', function($test): void {
364 $pg = new Pgsql([]);
365 $test->assertSame('"users"', $pg->quoteIdentifier('users'));
366 $test->assertSame('"a""b"', $pg->quoteIdentifier('a"b'));
367 });
368
369 $test->it('接続していなければ例外のはず', function($test) use ($catch): void {
370 $pg = new Pgsql([]);
371 foreach ([fn() => $pg->query('SELECT 1'), fn() => $pg->prepare('SELECT 1'), fn() => $pg->execute(1), fn() => $pg->beginTransaction()] as $fn) {
372 $e = $catch($fn);
373 $test->assertTrue($e instanceof DbException);
374 $test->assertStringContains('接続していません', $e->getMessage());
375 }
376 $test->assertFalse($pg->ping());
377 $test->assertFalse($pg->inTransaction());
378 $test->assertFalse($pg->demolish(1));
379 });
380
381 $test->it('サーバーが無ければ接続に失敗するはず', function($test) use ($catch): void {
382 $probe = stream_socket_server('tcp://127.0.0.1:0');
383 $port = (int)substr(strrchr(stream_socket_get_name($probe, false), ':'), 1);
384 fclose($probe);
385
386 $e = $catch(fn() => (new Pgsql(['port' => $port, 'timeout' => 2]))->connect());
387 $test->assertTrue($e instanceof DbException);
388 $test->assertStringContains('ソケットに接続に失敗', $e->getMessage());
389 $e = $catch(fn() => (new Pgsql(['socket' => sys_get_temp_dir().'/lb-no-such-dir']))->connect());
390 $test->assertStringContains('ソケットに接続に失敗', $e->getMessage());
391 });
392
393 $test->it('「?」を「$1」等に変えるはず', function($test): void {
394 $cases = [
395 'SELECT 1' => 'SELECT 1',
396 'SELECT ?, ?' => 'SELECT $1, $2',
397 "SELECT '?', ?, 'it''s ?', ?" => "SELECT '?', \$1, 'it''s ?', \$2",
398 'SELECT "a?""b", ?' => 'SELECT "a?""b", $1',
399 "SELECT E'\\'?', ?" => "SELECT E'\\'?', \$1",
400 "SELECT e'\\\\', ?" => "SELECT e'\\\\', \$1",
401 "SELECT x'?', ?" => "SELECT x'?', \$1",
402 'SELECT $$?$$, $tag$ ? $$ ? $tag$, ?' => 'SELECT $$?$$, $tag$ ? $$ ? $tag$, $1',
403 "SELECT ? -- ?\n, ?" => "SELECT \$1 -- ?\n, \$2",
404 'SELECT /* ? /* ? */ ? */ ?' => 'SELECT /* ? /* ? */ ? */ $1',
405 "SELECT j ?? 'a', ?" => "SELECT j ? 'a', \$1",
406 'SELECT a$1, ?' => 'SELECT a$1, $1',
407 "SELECT 'unterminated ?" => "SELECT 'unterminated ?",
408 'SELECT 名前?' => 'SELECT 名前$1',
409 ];
410 foreach ($cases as $in => $out) $test->assertSame($out, Pgsql::placeholders($in), $in);
411 });
412
413 $test->it('配列と型を変換出来るはず', function($test): void {
414 $test->assertSame([1, 2, null], Pgsql::parseArray('{1,2,NULL}', Pgsql::OID_INT4));
415 $test->assertSame([['a', 'b,c'], ['d"e', '\\']], Pgsql::parseArray('{{a,"b,c"},{"d\\"e","\\\\"}}'));
416 $test->assertSame(['NULL', null, ''], Pgsql::parseArray('{"NULL",null,""}'));
417 $test->assertSame([1, 2], Pgsql::parseArray('[0:1]={1,2}', Pgsql::OID_INT4));
418 $test->assertSame([], Pgsql::parseArray('{}'));
419 $test->assertNull(Pgsql::parseArray('{1,2'));
420 $test->assertNull(Pgsql::parseArray('{1}x'));
421 $test->assertNull(Pgsql::parseArray('1,2'));
422
423 $test->assertSame(-5, Pgsql::castText('-5', Pgsql::OID_INT2));
424 $test->assertSame('99999999999999999999', Pgsql::castText('99999999999999999999', Pgsql::OID_INT8));
425 $test->assertSame(INF, Pgsql::castText('Infinity', Pgsql::OID_FLOAT4));
426 $test->assertSame(1.0E-5, Pgsql::castText('1e-05', Pgsql::OID_FLOAT8));
427 $test->assertSame(false, Pgsql::castText('f', Pgsql::OID_BOOL));
428 $test->assertSame("\x00\xff", Pgsql::castText('\\x00ff', Pgsql::OID_BYTEA));
429 $test->assertSame("\x00\xff\\A", Pgsql::castText('\\000\\377\\\\A', Pgsql::OID_BYTEA));
430 $test->assertSame('1.50', Pgsql::castText('1.50', Pgsql::OID_NUMERIC));
431 $test->assertSame([true, false], Pgsql::castText('{t,f}', 1000));
432 $test->assertSame('{bad', Pgsql::castText('{bad', 1009));
433 });
434});
435
436// TLS用の証明書（RSAの鍵、CAが発行した物）
437$cert = TestCert::get('rsa');
438
439$test->describe('Pgsql（偽物のPostgreSQLサーバー）', function($test) use ($catch, $cert, $common): void {
440 $srv = MockServer::start('pg-server.php', [$cert['cert'], $cert['key']]);
441 if ($srv === null) {
442 $test->skipAll('偽物のサーバーを起動出来ない（proc_openが無効、又はpdo_sqliteが無い？）');
443 return;
444 }
445
446 $info = fn(array $o = []): array => $o + [
447 'host' => '127.0.0.1',
448 'port' => $srv->port,
449 'username' => 'lb',
450 'password' => 'ひみつ',
451 'dbname' => 'lb',
452 'timeout' => 5,
453 ];
454 $tls = ['cafile' => $cert['ca'], 'peer_name' => 'localhost'];
455 $open = function(array $o = []) use ($info): Pgsql {
456 $pg = new Pgsql($info($o));
457 $pg->connect();
458 return $pg;
459 };
460 $one = fn(Pgsql $pg, string $sql) => array_values($pg->query($sql)['rows'][0] ?? [])[0] ?? null;
461
462 $common($test, $info);
463
464 $test->it('SCRAM-SHA-256で接続出来るはず', function($test) use ($open, $one, $info, $catch): void {
465 $pg = $open();
466 $test->assertSame('SCRAM-SHA-256', $pg->getServerInfo()['authMethod']);
467 $test->assertFalse($pg->getServerInfo()['tls']);
468 $test->assertSame('none', $one($pg, 'SELECT lb_cb() AS cb'));
469
470 foreach ([['password' => 'ちがう'], ['username' => 'nobody'], ['password' => '']] as $o) {
471 $pg = new Pgsql($info($o));
472 $e = $catch(fn() => $pg->connect());
473 $test->assertTrue($e instanceof DbException);
474 $test->assertSame('28P01', $e->sqlState);
475 $test->assertStringContains('接続に失敗: 28P01 - password authentication failed', $e->getMessage());
476 $test->assertFalse($pg->isConnected());
477 }
478 });
479
480 $test->it('MD5・trustで接続出来るはず', function($test) use ($open, $one, $catch): void {
481 $pg = $open(['username' => 'md5user', 'password' => 'md5ひみつ']);
482 $test->assertSame('md5', $pg->getServerInfo()['authMethod']);
483 $test->assertSame('md5user', $one($pg, 'SELECT current_user AS u'));
484 $e = $catch(fn() => $open(['username' => 'md5user', 'password' => 'md5']));
485 $test->assertSame('28P01', $e->sqlState);
486
487 $pg = $open(['username' => 'trust', 'password' => '']);
488 $test->assertSame('trust', $pg->getServerInfo()['authMethod']);
489 });
490
491 $test->it('TLSなしでパスワードをそのまま送らないはず', function($test) use ($open, $one, $catch, $tls): void {
492 $e = $catch(fn() => $open(['username' => 'clear', 'password' => 'clearひみつ']));
493 $test->assertTrue($e instanceof DbException);
494 $test->assertStringContains('allow_cleartext', $e->getMessage());
495
496 $test->assertSame('password', $one($open(['username' => 'clear', 'password' => 'clearひみつ', 'allow_cleartext' => true]), 'SELECT lb_auth() AS a'));
497 $pg = $open(['username' => 'clear', 'password' => 'clearひみつ', 'tls' => $tls]);
498 $test->assertSame('password', $pg->getServerInfo()['authMethod']);
499 $test->assertTrue($pg->getServerInfo()['tls']);
500 });
501
502 $test->it('TLSとチャネルバインディング（SCRAM-SHA-256-PLUS）で接続出来るはず', function($test) use ($open, $one, $catch, $tls): void {
503 $pg = $open(['tls' => $tls]);
504 $test->assertSame('SCRAM-SHA-256-PLUS', $pg->getServerInfo()['authMethod']);
505 $test->assertTrue($pg->getServerInfo()['tls']);
506 $test->assertSame(1, $one($pg, 'SELECT lb_tls() AS t'));
507 $test->assertSame('tls-server-end-point', $one($pg, 'SELECT lb_cb() AS cb'));
508 $test->assertSame('諏訪子', $one($pg, "SELECT '諏訪子' AS n"));
509
510 $pg = $open(['tls' => $tls, 'channel_binding' => 'disable']);
511 $test->assertSame('SCRAM-SHA-256', $pg->getServerInfo()['authMethod']);
512 $test->assertSame('none', $one($pg, 'SELECT lb_cb() AS cb'));
513
514 $pg = $open(['username' => 'tlsonly', 'password' => 'tlsひみつ', 'tls' => $tls, 'channel_binding' => 'require']);
515 $test->assertSame('tls-server-end-point', $one($pg, 'SELECT lb_cb() AS cb'));
516 $e = $catch(fn() => $open(['username' => 'tlsonly', 'password' => 'tlsひみつ']));
517 $test->assertSame('28000', $e->sqlState);
518 $test->assertStringContains('no encryption', $e->getMessage());
519
520 // TLSなしでチャネルバインディングを求める
521 $e = $catch(fn() => $open(['channel_binding' => 'require']));
522 $test->assertStringContains('チャネルバインディングが使えません', $e->getMessage());
523
524 // 信頼していない証明書
525 $e = $catch(fn() => $open(['tls' => true]));
526 $test->assertTrue($e instanceof DbException);
527 $test->assertStringContains('TLSの開始に失敗', $e->getMessage());
528 });
529
530 $test->it('未対応の認証方式・無いデータベースは例外のはず', function($test) use ($open, $catch): void {
531 $e = $catch(fn() => $open(['username' => 'gss']));
532 $test->assertStringContains('未対応の認証方式: 7', $e->getMessage());
533 $e = $catch(fn() => $open(['dbname' => 'nodb']));
534 $test->assertSame('3D000', $e->sqlState);
535 $test->assertSame('接続に失敗: 3D000 - database "nodb" does not exist', $e->getMessage());
536 });
537
538 $test->it('色々な型を変換するはず', function($test) use ($open): void {
539 $row = $open()->query('SELECT lb_types()')['rows'][0];
540 // NANは同じにならないので別に
541 $test->assertTrue(is_nan($row['nan']));
542 $test->assertSame(1.5, $row['floats'][0]);
543 $test->assertTrue(is_nan($row['floats'][1]));
544 unset($row['nan'], $row['floats']);
545 $test->assertSame([
546 'i2' => -5, 'i4' => 42, 'i8' => PHP_INT_MAX, 'num' => '12345678901234567890.5', 'f4' => 2.5, 'ninf' => -INF, 'big' => 1.0E+100,
547 't' => true, 'f' => false, 'bin' => "\x00\xff\\", 'txts' => ['a,b', 'c"d', null, 'NULL', '', ' x '], 'ints' => [[1, 2], [3, null]],
548 'ranged' => [1, 2], 'bools' => [true, false], 'bins' => ["\x01"], 'j' => '{"a": [1, "x"]}', 'jb' => '{"b": 2}',
549 'd' => '2026-01-02', 'ts' => '2026-01-02 03:04:05.123456+09', 'u' => 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'o' => 1259, 'n' => null,
550 'v' => '日本語', 'i8s' => 2147483648, 'empty' => [], 'esc' => "\x00\xff\\A", 'big8' => '99999999999999999999',
551 ], $row);
552 });
553
554 $test->it('結果の途中の非同期のメッセージを処理するはず', function($test) use ($open, $one): void {
555 $pg = $open();
556 $test->assertSame('Asia/Tokyo', $pg->getServerInfo()['TimeZone']);
557 $test->assertSame('ok', $one($pg, 'SELECT lb_async()'));
558 $test->assertSame('UTC', $pg->getServerInfo()['TimeZone']);
559 $pg->query("SET TimeZone = 'Europe/Amsterdam'");
560 $test->assertSame('Europe/Amsterdam', $pg->getServerInfo()['TimeZone']);
561 });
562
563 $test->it('COPY TO STDOUTのデータは無視するはず', function($test) use ($open): void {
564 $res = $open()->query('COPY lb_test TO STDOUT');
565 $test->assertSame('COPY 1', $res['command']);
566 $test->assertSame(1, $res['affectedRows']);
567 $test->assertSame([], $res['rows']);
568 });
569
570 $test->it('FATAL・切断・タイムアウトで接続を閉じるはず', function($test) use ($open, $catch): void {
571 $pg = $open();
572 $pg->beginTransaction();
573 $e = $catch(fn() => $pg->query('SELECT lb_fatal()'));
574 $test->assertSame('57P01', $e->sqlState);
575 $test->assertFalse($pg->isConnected());
576 $test->assertFalse($pg->inTransaction());
577
578 $pg = $open();
579 $e = $catch(fn() => $pg->query('SELECT lb_disconnect()'));
580 $test->assertSame('サーバーとの接続が切れました', $e->getMessage());
581 $test->assertFalse($pg->isConnected());
582 // 再接続
583 $test->assertTrue($pg->connect());
584 $test->assertTrue($pg->ping());
585
586 $pg = $open(['timeout' => 1]);
587 $start = microtime(true);
588 $e = $catch(fn() => $pg->query('SELECT lb_sleep(2.5) AS z'));
589 $test->assertStringContains('タイムアウト（1秒）', $e->getMessage());
590 $test->assertTrue(microtime(true) - $start < 2.3, 'タイムアウトが遅い');
591 $test->assertFalse($pg->isConnected());
592 });
593
594 $test->it('デバッグでメッセージを記録するはず', function($test) use ($open): void {
595 $pg = $open();
596 $pg->setDebug(true);
597 ob_start();
598 $pg->query('SELECT 1 AS a');
599 $out = ob_get_clean();
600 $pg->setDebug(false);
601 $test->assertStringContains('=== SEND Q', $out);
602 $log = $pg->getPacketLog();
603 $test->assertSame(['SEND', 'Q', "SELECT 1 AS a\0"], [$log[0]['direction'], $log[0]['type'], $log[0]['data']]);
604 $test->assertSame(['T', 'D', 'C', 'Z'], array_column(array_slice($log, 1), 'type'));
605 });
606
607 $srv->stop();
608});
609
610$test->describe('Pgsql（偽物の悪いサーバー）', function($test) use ($catch, $cert): void {
611 $tls = ['cafile' => $cert['ca'], 'peer_name' => 'localhost'];
612 // 接続して、出来たらSQLの結果、出来なかったら例外
613 $connect = function(array $args, array $o = [], string $sql = 'SELECT 1 AS x'): mixed {
614 $srv = MockServer::start('pg-server.php', $args);
615 if ($srv === null) return new \RuntimeException('起動出来ない');
616 try {
617 $pg = new Pgsql($o + ['host' => '127.0.0.1', 'port' => $srv->port, 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb', 'timeout' => 5]);
618 $pg->connect();
619 return array_values($pg->query($sql)['rows'][0])[0];
620 } catch (\Throwable $e) {
621 return $e;
622 } finally {
623 $srv->stop();
624 }
625 };
626
627 $test->it('TLSに対応していないサーバーでTLSを求めたら例外のはず', function($test) use ($connect): void {
628 $e = $connect([], ['tls' => true]);
629 $test->assertTrue($e instanceof DbException);
630 $test->assertStringContains('サーバーがTLSに対応していません', $e->getMessage());
631 });
632
633 $test->it('サーバーの署名・nonceが不正なら例外のはず', function($test) use ($connect): void {
634 $e = $connect(['', '', 'badsig']);
635 $test->assertTrue($e instanceof DbException);
636 $test->assertStringContains('サーバーの署名が不正です', $e->getMessage());
637 $e = $connect(['', '', 'badnonce']);
638 $test->assertStringContains('不正なSCRAMのメッセージ', $e->getMessage());
639 $e = $connect(['', '', 'nofinal']);
640 $test->assertStringContains('サーバーがSCRAMを完了しませんでした', $e->getMessage());
641 $test->assertSame(1, $connect(['', '', 'nofinal'], ['username' => 'trust']));
642 });
643
644 $test->it('チャネルバインディングを出さないTLSのサーバー', function($test) use ($connect, $cert, $tls): void {
645 // preferなら「y」（クライアントは対応している）で接続する
646 $test->assertSame('y', $connect([$cert['cert'], $cert['key'], 'nocb'], ['tls' => $tls], 'SELECT lb_cb() AS cb'));
647 $e = $connect([$cert['cert'], $cert['key'], 'nocb'], ['tls' => $tls, 'channel_binding' => 'require']);
648 $test->assertStringContains('チャネルバインディングが使えません', $e->getMessage());
649 });
650});
651
652$test->describe('Pgsql（本物のサーバー、LB_TEST_PGSQL）', function($test) use ($common): void {
653 $url = (string)getenv('LB_TEST_PGSQL');
654 $u = $url === '' ? false : parse_url($url);
655 if ($u === false) {
656 $test->skipAll('LB_TEST_PGSQL（pgsql://ユーザー:パスワード@ホスト:ポート/データベース）が無い');
657 return;
658 }
659 parse_str($u['query'] ?? '', $q);
660 $info = fn(array $o = []): array => $o + [
661 'host' => $u['host'] ?? '127.0.0.1',
662 'port' => $u['port'] ?? 5432,
663 'username' => urldecode($u['user'] ?? ''),
664 'password' => urldecode($u['pass'] ?? ''),
665 'dbname' => ltrim(urldecode($u['path'] ?? ''), '/'),
666 'tls' => isset($q['cafile']) ? ['cafile' => $q['cafile']] : false,
667 'timeout' => 10,
668 ];
669 $common($test, $info);
670});
671
672$test->printSummary();