Gitler

suwako / LittleBeast

PostgreSQLドライバー

守矢諏訪子 <suwako@076.moe>、2026-11-12 14:52
コミット f9c291946edaf7476f047001ba33f473d4ef4eda ・親 b5a37f2 ・ファイルを見る

7個のファイル、+2695 -21

変更 README-JP.md

		@@ -207,25 +207,29 @@
207	207
208	208	テンプレートやコードでは、URLは `ass_url('article/a.png')`、ディスク上のファイルは `ROOT.ass_path('article/a.png')` を使います。
209	209
210		-### データベース（MySQL・MariaDB）
211		-Little Beast 自体はデータベースを使いませんが、必要な場合（既存のアプリの移植等）は `Std\Lib\Db` で繋げます。MySQL のドライバは PHP だけで書かれているので、mysqli・pdo_mysql・sockets は不要です。\
212		-`config/config.php` で `DB_ENABLED` を `true` にして、`DBINFO` を設定して下さい（`driver` は `mysql`、MariaDB も同じ。`socket` を指定すると `host`・`port` の代わりに UNIX ソケットを使う。`tls` は `true`、又は `['cafile' => '/etc/ssl/ca.pem']` 等の SSL コンテキストオプションの配列）。
	210	+### データベース（MySQL・MariaDB、PostgreSQL）
	211	+Little Beast 自体はデータベースを使いませんが、必要な場合（既存のアプリの移植等）は `Std\Lib\Db` で繋げます。ドライバは PHP だけで書かれているので、mysqli・pdo_mysql・pgsql・pdo_pgsql・sockets は不要です。\
	212	+`config/config.php` で `DB_ENABLED` を `true` にして、`DBINFO` を設定して下さい（`driver` は `mysql`（MariaDB も同じ）、又は PostgreSQL の `pgsql`（ポートは普通5432）。`socket` を指定すると `host`・`port` の代わりに UNIX ソケットを使う、PostgreSQL ではソケットのファイル又は `.s.PGSQL.5432` が有るディレクトリ（例：`/tmp`）。`tls` は `true`、又は `['cafile' => '/etc/ssl/ca.pem']` 等の SSL コンテキストオプションの配列）。
213	213	```php
214	214	$db = new \Std\Lib\Db();
215	215	$rows = $db->query('SELECT * FROM users WHERE id = ?', [1])['rows'];
216		-$id = $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId'];
	216	+$id = $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId']; // MySQL
	217	+$id = $db->query('INSERT INTO users (name) VALUES (?) RETURNING id', ['諏訪子'])['rows'][0]['id']; // PostgreSQL
217	218	$db->transaction(function(\Std\Lib\Db $db): void {
218	219	$db->query('UPDATE users SET points = points - ? WHERE id = ?', [100, 1]);
219	220	$db->query('UPDATE users SET points = points + ? WHERE id = ?', [100, 2]);
220	221	});
221	222	```
222	223	* 最初のクエリで接続します。パラメータ付きのクエリはプリペアドステートメントで実行するので、値をエスケープする必要はありません。
223		-* 整数の列は `int`、`FLOAT`・`DOUBLE` は `float`、`NULL` は `null`、その他（`DECIMAL`、日付、`BLOB` 等）は文字列で返します。
224		-* エラーは `Std\Lib\Db\DbException`（`getCode()` はサーバーのエラーコード、例：重複は1062、`sqlState` は SQLSTATE）を投げ、`log/db_log.txt` に書きます。
225		-* 認証：`mysql_native_password`、`caching_sha2_password`（MySQL 8 の既定）と `sha256_password`（TLS でない TCP では、パスワードをサーバーの RSA の鍵で暗号化する）、`mysql_clear_password`（TLS か UNIX ソケットのみ）。MariaDB の `client_ed25519`、圧縮、`LOAD DATA LOCAL INFILE` は未対応です。
	224	+* 整数の列は `int`、`FLOAT`・`DOUBLE`（PostgreSQL は `real`・`double precision`）は `float`、`NULL` は `null`、その他（`DECIMAL`・`numeric`、日付、JSON、`BLOB` 等）は文字列で返します。PostgreSQL の `boolean` は `bool`、`bytea` は生のバイト、配列は PHP の配列で返します。
	225	+* エラーは `Std\Lib\Db\DbException`（`getCode()` は MySQL のエラーコード、例：重複は1062、`sqlState` は SQLSTATE、例：PostgreSQL の重複は23505）を投げ、`log/db_log.txt` に書きます。
	226	+* MySQL の認証：`mysql_native_password`、`caching_sha2_password`（MySQL 8 の既定）と `sha256_password`（TLS でない TCP では、パスワードをサーバーの RSA の鍵で暗号化する）、`mysql_clear_password`（TLS か UNIX ソケットのみ）。MariaDB の `client_ed25519`、圧縮、`LOAD DATA LOCAL INFILE` は未対応です。
	227	+* PostgreSQL の認証：SCRAM-SHA-256（TLS ならチャネルバインディング付きの SCRAM-SHA-256-PLUS、オプションの `channel_binding` は `prefer`・`require`・`disable`）、MD5、`password`（`allow_cleartext` を `true` にしない限り、TLS か UNIX ソケットのみ）、`trust`。GSSAPI・SSPI は未対応です。
	228	+* PostgreSQL：`?` のプレースホルダは `$1`、`$2`… にします（文字列・識別子・コメントの外のみ。`?` そのもの（jsonb の演算子等）は `??` と書く）。`$1` もそのまま使えます。挿入した ID は無いので、`RETURNING` を使って下さい。トランザクションの中でエラーが起きた後の `COMMIT` は、PostgreSQL がロールバックするので `DbException`（SQLSTATE 25P02）を投げます。`COPY`、`LISTEN` の通知、実行中のクエリの取り消しは未対応です。PostgreSQL 16 で確認しました。
	229	+* テストは偽物のサーバーを使います。PostgreSQL のテストを本物のサーバーでも実行するには、`LB_TEST_PGSQL=pgsql://ユーザー:パスワード@ホスト:ポート/データベース` を設定して下さい（TLS は `?cafile=/path/ca.pem` を追加）。名前が `lb_test_` で始まるテーブルを作って消します。
226	230
227	231	### TLS 1.3・1.2（php_openssl は不要）
228		-HTTPS のリクエスト（`Curl`、ActivityPub）、SMTP の STARTTLS（`Mailer`）、MySQL の TLS は PHP だけで書かれた TLS 1.3・1.2 のクライアント `Std\Lib\Tls`（`php_gmp` が必要）を使うので、`php_openssl` は不要です。RSA の鍵と署名（ActivityPub）は `Std\Lib\Crypto\Rsa` を使い、今までの PEM の鍵もそのまま使えます。
	232	+HTTPS のリクエスト（`Curl`、ActivityPub）、SMTP の STARTTLS（`Mailer`）、MySQL・PostgreSQL の TLS は PHP だけで書かれた TLS 1.3・1.2 のクライアント `Std\Lib\Tls`（`php_gmp` が必要）を使うので、`php_openssl` は不要です。RSA の鍵と署名（ActivityPub）は `Std\Lib\Crypto\Rsa` を使い、今までの PEM の鍵もそのまま使えます。
229	233	* TLS 1.3 を優先し、TLS 1.2 にしか対応していないサーバーとは安全な暗号スイートだけで TLS 1.2 を使います（ECDHE と AES-GCM 又は ChaCha20-Poly1305、サーバーが対応していれば Extended Master Secret、再ネゴシエーションはしない）。CBC、RSA の鍵交換、DHE、TLS 1.1 以前は未対応です。TLS 1.3 に対応しているサーバーとの通信を攻撃者が TLS 1.2 に下げた場合は分かります。TLS 1.2 を拒否するには、オプションの `'min_version' => '1.3'` を使って下さい。
230	234	* 証明書は `config/config.php` の `TLS_CAFILE`（信頼する CA の PEM ファイル）で確認します。空なら、普通のシステムの場所を探します（OpenBSD は `/etc/ssl/cert.pem`）。httpd・php-fpm の chroot の中では `/var/www/etc/ssl/cert.pem` になるので、システムのファイルをそこにコピーして下さい。Windows にはこのファイルが無いので、`TLS_CAFILE` を設定して下さい（例：curl の `cacert.pem`）。
231	235	* 暗号スイート：TLS 1.3 は `TLS_CHACHA20_POLY1305_SHA256`、`TLS_AES_128_GCM_SHA256`、`TLS_AES_256_GCM_SHA384`、TLS 1.2 は `TLS_ECDHE_{ECDSA,RSA}_WITH_{CHACHA20_POLY1305_SHA256,AES_128_GCM_SHA256,AES_256_GCM_SHA384}`。鍵交換：x25519、secp256r1、secp384r1。署名：RSA-PSS、ECDSA（P-256、P-384、P-521）、RSA PKCS#1 v1.5（証明書と TLS 1.2 のみ）。セッションの再開、0-RTT、クライアント証明書、失効の確認は未対応です。

変更 README.md

		@@ -205,25 +205,29 @@
205	205
206	206	In templates and code, use `ass_url('article/a.png')` for the URL and `ROOT.ass_path('article/a.png')` for the file on disk.
207	207
208		-### Database (MySQL/MariaDB)
209		-Little Beast itself needs no database, but `Std\Lib\Db` can talk to one when a site has to (for example, when porting an existing application). The MySQL driver is written in pure PHP, so mysqli, pdo_mysql and sockets are not needed.\
210		-Set `DB_ENABLED` to `true` and fill in `DBINFO` in `config/config.php` (`driver` is `mysql`, which also works with MariaDB; `socket` uses a UNIX socket instead of `host` and `port`; `tls` is `true` or an array of SSL context options such as `['cafile' => '/etc/ssl/ca.pem']`).
	208	+### Database (MySQL/MariaDB, PostgreSQL)
	209	+Little Beast itself needs no database, but `Std\Lib\Db` can talk to one when a site has to (for example, when porting an existing application). The drivers are written in pure PHP, so mysqli, pdo_mysql, pgsql, pdo_pgsql and sockets are not needed.\
	210	+Set `DB_ENABLED` to `true` and fill in `DBINFO` in `config/config.php` (`driver` is `mysql`, which also works with MariaDB, or `pgsql` for PostgreSQL, whose port is usually 5432; `socket` uses a UNIX socket instead of `host` and `port`, for PostgreSQL either the socket file or the directory that has `.s.PGSQL.5432`, such as `/tmp`; `tls` is `true` or an array of SSL context options such as `['cafile' => '/etc/ssl/ca.pem']`).
211	211	```php
212	212	$db = new \Std\Lib\Db();
213	213	$rows = $db->query('SELECT * FROM users WHERE id = ?', [1])['rows'];
214		-$id = $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId'];
	214	+$id = $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId']; // MySQL
	215	+$id = $db->query('INSERT INTO users (name) VALUES (?) RETURNING id', ['諏訪子'])['rows'][0]['id']; // PostgreSQL
215	216	$db->transaction(function(\Std\Lib\Db $db): void {
216	217	$db->query('UPDATE users SET points = points - ? WHERE id = ?', [100, 1]);
217	218	$db->query('UPDATE users SET points = points + ? WHERE id = ?', [100, 2]);
218	219	});
219	220	```
220	221	* The connection is opened on the first query. Queries with parameters use prepared statements, so values never need escaping.
221		-* Integer columns come back as `int`, `FLOAT`/`DOUBLE` as `float`, `NULL` as `null`, and everything else (including `DECIMAL`, dates and `BLOB`) as strings.
222		-* Errors throw `Std\Lib\Db\DbException` (`getCode()` is the server's error code, for example 1062 for a duplicate key, and `sqlState` the SQLSTATE) and are written to `log/db_log.txt`.
223		-* Authentication: `mysql_native_password`, `caching_sha2_password` (the MySQL 8 default) and `sha256_password` (over plain TCP the password is encrypted with the server's RSA key), and `mysql_clear_password` (TLS or UNIX socket only). MariaDB's `client_ed25519`, compression and `LOAD DATA LOCAL INFILE` are not supported.
	222	+* Integer columns come back as `int`, `FLOAT`/`DOUBLE` (PostgreSQL: `real`/`double precision`) as `float`, `NULL` as `null`, and everything else (including `DECIMAL`/`numeric`, dates, JSON and `BLOB`) as strings. PostgreSQL's `boolean` comes back as `bool`, `bytea` as the raw bytes and arrays as PHP arrays.
	223	+* Errors throw `Std\Lib\Db\DbException` (`getCode()` is MySQL's error code, for example 1062 for a duplicate key, and `sqlState` the SQLSTATE, for example 23505 for a duplicate key in PostgreSQL) and are written to `log/db_log.txt`.
	224	+* MySQL authentication: `mysql_native_password`, `caching_sha2_password` (the MySQL 8 default) and `sha256_password` (over plain TCP the password is encrypted with the server's RSA key), and `mysql_clear_password` (TLS or UNIX socket only). MariaDB's `client_ed25519`, compression and `LOAD DATA LOCAL INFILE` are not supported.
	225	+* PostgreSQL authentication: SCRAM-SHA-256 (over TLS with channel binding, SCRAM-SHA-256-PLUS; the option `channel_binding` is `prefer`, `require` or `disable`), MD5, `password` (TLS or UNIX socket only, unless `allow_cleartext` is `true`) and `trust`. GSSAPI and SSPI are not supported.
	226	+* PostgreSQL: `?` placeholders become `$1`, `$2`… (outside strings, identifiers and comments; write `??` for a literal `?`, such as the jsonb operator), and `$1` can be used directly too. There is no insert ID, so use `RETURNING`. A `COMMIT` after an error in the transaction throws `DbException` (SQLSTATE 25P02), because PostgreSQL rolls the transaction back. `COPY`, `LISTEN` notifications and cancelling a running query are not supported. Tested with PostgreSQL 16.
	227	+* The tests use a mock server. To run the PostgreSQL tests against a real server as well, set `LB_TEST_PGSQL=pgsql://user:password@host:port/database` (add `?cafile=/path/ca.pem` for TLS); they create and drop tables whose names start with `lb_test_`.
224	228
225	229	### TLS 1.3 and 1.2 (no php_openssl)
226		-HTTPS requests (`Curl`, ActivityPub), SMTP STARTTLS (`Mailer`) and MySQL over TLS use `Std\Lib\Tls`, a TLS 1.3 and 1.2 client written in pure PHP (it needs `php_gmp`), so `php_openssl` is not needed. RSA keys and signatures (ActivityPub) use `Std\Lib\Crypto\Rsa`, and existing PEM keys keep working.
	230	+HTTPS requests (`Curl`, ActivityPub), SMTP STARTTLS (`Mailer`) and MySQL and PostgreSQL over TLS use `Std\Lib\Tls`, a TLS 1.3 and 1.2 client written in pure PHP (it needs `php_gmp`), so `php_openssl` is not needed. RSA keys and signatures (ActivityPub) use `Std\Lib\Crypto\Rsa`, and existing PEM keys keep working.
227	231	* TLS 1.3 is preferred. Servers that only speak TLS 1.2 get TLS 1.2 with secure cipher suites only (ECDHE with AES-GCM or ChaCha20-Poly1305, Extended Master Secret when the server supports it, no renegotiation); CBC, RSA key exchange and DHE are not supported, and neither is TLS 1.1 or older. A TLS 1.3 server that is pushed down to TLS 1.2 by an attacker is detected. Use the option `'min_version' => '1.3'` to refuse TLS 1.2.
228	232	* Certificates are checked against `TLS_CAFILE` in `config/config.php` (a PEM file of trusted CAs). When it is empty, the usual system locations are tried (`/etc/ssl/cert.pem` on OpenBSD). Inside the httpd/php-fpm chroot that path is `/var/www/etc/ssl/cert.pem`, so copy the system file there. Windows has no such file, so set `TLS_CAFILE` (for example to curl's `cacert.pem`).
229	233	* Cipher suites: TLS 1.3 `TLS_CHACHA20_POLY1305_SHA256`, `TLS_AES_128_GCM_SHA256`, `TLS_AES_256_GCM_SHA384`; TLS 1.2 `TLS_ECDHE_{ECDSA,RSA}_WITH_{CHACHA20_POLY1305_SHA256,AES_128_GCM_SHA256,AES_256_GCM_SHA384}`. Key exchange: x25519, secp256r1, secp384r1. Signatures: RSA-PSS, ECDSA (P-256, P-384, P-521) and RSA PKCS#1 v1.5 (certificates and TLS 1.2 only). Session resumption, 0-RTT, client certificates and revocation checks are not supported.

変更 config/config.sample.php

		@@ -76,10 +76,11 @@
76	76	'pubkey' => ROOT.'/public/static/pub.pem',
77	77	'privkey' => ROOT.'/data/priv.pem',
78	78	]);
79		-// データベース（DB_ENABLEDが必要）。driver：mysql（MariaDBも）
80		-// socket：UNIXソケットのパス（指定するとhost・portの代わりに使う、例：/var/run/mysql/mysql.sock）
	79	+// データベース（DB_ENABLEDが必要）。driver：mysql（MariaDBも）、pgsql（PostgreSQL、portは普通5432、charsetはUTF8）
	80	+// socket：UNIXソケットのパス（指定するとhost・portの代わりに使う、例：/var/run/mysql/mysql.sock、PostgreSQLはディレクトリも可：/tmp）
81	81	// tls：trueで証明書を確認するTLS、又はsslのコンテキストオプションの配列（例：['cafile' => '/etc/ssl/ca.pem']）
82	82	// debug：送受信したパケットを出力する
	83	+// PostgreSQLのみ：channel_binding（prefer、require、disable）、allow_cleartext（TLSでなくてもパスワードをそのまま送る）
83	84	define('DBINFO', [
84	85	'driver' => 'mysql',
85	86	'host' => '127.0.0.1',

変更 src/Std/Lib/Db.php

		@@ -39,6 +39,7 @@
39	39
40	40	use Std\Lib\Db\DbInterface;
41	41	use Std\Lib\Db\Mysql;
	42	+use Std\Lib\Db\Pgsql;
42	43
43	44	/**
44	45	* データベース
		@@ -48,7 +49,7 @@
48	49	* 使い方：
49	50	* $db = new Db();
50	51	* $users = $db->query('SELECT * FROM users WHERE role > ?', [0])['rows'];
51		- * $id = $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId'];
	52	+ * $id = $db->query('INSERT INTO users (name) VALUES (?)', ['諏訪子'])['insertId']; // MySQL（PostgreSQLはRETURNINGを使う）
52	53	* $db->transaction(function(Db $db): void {
53	54	* $db->query('UPDATE users SET points = points - ? WHERE id = ?', [100, 1]);
54	55	* $db->query('UPDATE users SET points = points + ? WHERE id = ?', [100, 2]);
		@@ -56,8 +57,11 @@
56	57	*/
57	58	class Db {
58	59	private const SUPPORTED = [
59		- 'mysql' => Mysql::class,
60		- 'mariadb' => Mysql::class,
	60	+ 'mysql' => Mysql::class,
	61	+ 'mariadb' => Mysql::class,
	62	+ 'pgsql' => Pgsql::class,
	63	+ 'postgres' => Pgsql::class,
	64	+ 'postgresql' => Pgsql::class,
61	65	];
62	66
63	67	public string $driverName;

追加 src/Std/Lib/Db/Pgsql.php

		@@ -0,0 +1,1006 @@
	1	+<?php
	2	+/*************************************************************
	3	+# 076 License
	4	+
	5	+Copyright (c) テクニカル諏訪子
	6	+
	7	+Permission is hereby granted to any person obtaining a copy of the software
	8	+Little Beast (the "Software") to use, modify, merge, copy, publish, distribute,
	9	+sublicense, and/or sell copies of the Software, subject to the following conditions:
	10	+
	11	+ 1. Origin Attribution:
	12	+ - You must not misrepresent the origin of the Software; you must not claim
	13	+ you created the original Software.
	14	+ - If the Software is used in a product, you must either:
	15	+ a. Provide clear attribution in the product's documentation, user interface,
	16	+ or other visible areas, OR
	17	+ b. Pay the original developers a fee they specify in writing.
	18	+ 2. Usage Restriction:
	19	+ - The Software, or any derivative works, dependencies, or libraries
	20	+ incorporating it, must not be used for censorship or to suppress freedom of
	21	+ speech, expression, or creativity. Prohibited uses include, but are not
	22	+ limited to:
	23	+ - Censorship of so-called "hate speech", visuals, non-mainstream opinions,
	24	+ ideas, or objective reality.
	25	+ - Tools or systems designed to restrict access to information or
	26	+ artistic works.
	27	+ 3. Notice Preservation:
	28	+ - This license and the above copyright notice must remain intact in all copies
	29	+ of the source code.
	30	+
	31	+THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED,
	32	+INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A
	33	+PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
	34	+HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF
	35	+CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE
	36	+OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
	37	+ */
	38	+namespace Std\Lib\Db;
	39	+
	40	+use Std\Lib\Tls;
	41	+use Std\Lib\Tls\TlsException;
	42	+
	43	+/**
	44	+ * PHP純正のPostgreSQLドライバ（プロトコル3.0）。pgsql・pdo_pgsql・opensslのモジュールが不要。
	45	+ *
	46	+ * 対応：TCP・UNIXソケット、TLS（Std\Lib\Tls）、認証（SCRAM-SHA-256、TLSならチャネルバインディング付きの
	47	+ * SCRAM-SHA-256-PLUS、MD5、パスワード、trust）、単純・拡張クエリ、複数の結果、トランザクション、通知（NOTICE）、
	48	+ * 型の変換（整数・浮動小数点数・真偽値・bytea・配列）
	49	+ * 未対応：COPY（データは無視する）、LISTEN・NOTIFYの待ち受け、GSSAPI・SSPI・Kerberos、取り消し要求
	50	+ *
	51	+ * プレースホルダは「?」（$1、$2…に変換する、「??」は「?」そのもの）又は「$1」。
	52	+ * PostgreSQLにはAUTO_INCREMENTのIDが無いので、insertIdは常に0（RETURNINGを使って下さい）。
	53	+ *
	54	+ * 普通はStd\Lib\Db経由で使う。直接使う場合：
	55	+ * $pg = new Pgsql(['host' => '127.0.0.1', 'username' => 'lb', 'password' => 'ひみつ', 'dbname' => 'lb']);
	56	+ * $pg->connect();
	57	+ * $id = $pg->prepare('SELECT * FROM users WHERE id = ?');
	58	+ * $res = $pg->execute($id, [1]); // $res['rows']
	59	+ * $pg->demolish($id);
	60	+ */
	61	+class Pgsql implements DbInterface {
	62	+ // 型のOID
	63	+ public const OID_BOOL = 16;
	64	+ public const OID_BYTEA = 17;
	65	+ public const OID_INT8 = 20;
	66	+ public const OID_INT2 = 21;
	67	+ public const OID_INT4 = 23;
	68	+ public const OID_TEXT = 25;
	69	+ public const OID_OID = 26;
	70	+ public const OID_JSON = 114;
	71	+ public const OID_FLOAT4 = 700;
	72	+ public const OID_FLOAT8 = 701;
	73	+ public const OID_VARCHAR = 1043;
	74	+ public const OID_DATE = 1082;
	75	+ public const OID_TIMESTAMP = 1114;
	76	+ public const OID_TIMESTAMPTZ = 1184;
	77	+ public const OID_NUMERIC = 1700;
	78	+ public const OID_UUID = 2950;
	79	+ public const OID_JSONB = 3802;
	80	+
	81	+ // 配列の型 => 要素の型
	82	+ private const ARRAYS = [
	83	+ 1000 => 16, 1001 => 17, 1005 => 21, 1007 => 23, 1016 => 20, 1009 => 25, 1015 => 1043, 1014 => 1042,
	84	+ 1021 => 700, 1022 => 701, 1231 => 1700, 1182 => 1082, 1115 => 1114, 1185 => 1184, 2951 => 2950, 199 => 114, 3807 => 3802,
	85	+ ];
	86	+
	87	+ private const PROTOCOL = 196608; // 3.0
	88	+ private const SSL_REQUEST = 80877103;
	89	+
	90	+ /** @var resource\|null */
	91	+ private $socket = null;
	92	+ /** @var resource\|null TLSの下のソケット（タイムアウトの確認用） */
	93	+ private $raw = null;
	94	+ private bool $connected = false;
	95	+ private bool $secure = false;
	96	+ private bool $debug = false;
	97	+ private array $packetLog = [];
	98	+ private string $out = '';
	99	+ public string $username;
	100	+ private string $password;
	101	+ public string $dbname;
	102	+ public string $host;
	103	+ private int $port;
	104	+ private string $socketPath;
	105	+ private string $charset;
	106	+ private bool\|array $tls;
	107	+ private string $channelBinding;
	108	+ private bool $allowCleartext;
	109	+ private int $timeout;
	110	+
	111	+ private string $status = '';
	112	+ private array $params = [];
	113	+ private int $pid = 0;
	114	+ private string $authMethod = '';
	115	+ private array $notices = [];
	116	+ private array $prepared = [];
	117	+ private int $nextStmt = 1;
	118	+
	119	+ /**
	120	+ * コンストラクタ
	121	+ *
	122	+ * 接続に必要な設定を初期化します。$infoを省略するとDBINFOを使います。
	123	+ *
	124	+ * @param array\|null $info host、port（5432）、socket（UNIXソケットのファイル又はディレクトリ）、username、password、dbname、
	125	+ * charset（UTF8）、tls（true＝証明書を確認するTLS、又はStd\Lib\Tlsのオプションの配列）、
	126	+ * channel_binding（prefer＝TLSなら使う、require、disable）、
	127	+ * allow_cleartext（TLSでないTCPでもパスワードをそのまま送る、既定false）、timeout（秒、10）、debug
	128	+ * @throws \Exception $infoを省略し、DB_ENABLEDが無効な場合
	129	+ */
	130	+ public function __construct(?array $info = null) {
	131	+ if ($info === null && !(defined('DB_ENABLED') && DB_ENABLED)) {
	132	+ throw new \Exception('データベースは無効です。');
	133	+ }
	134	+
	135	+ $info ??= DBINFO;
	136	+ $this->host = (string)($info['host'] ?? '127.0.0.1');
	137	+ $this->port = (int)($info['port'] ?? 5432);
	138	+ $this->socketPath = (string)($info['socket'] ?? '');
	139	+ $this->username = (string)($info['username'] ?? '');
	140	+ $this->password = (string)($info['password'] ?? '');
	141	+ $this->dbname = (string)($info['dbname'] ?? '');
	142	+ $this->charset = (string)($info['charset'] ?? 'UTF8');
	143	+ $this->tls = $info['tls'] ?? false;
	144	+ $this->channelBinding = strtolower((string)($info['channel_binding'] ?? 'prefer'));
	145	+ $this->allowCleartext = (bool)($info['allow_cleartext'] ?? false);
	146	+ $this->timeout = max(1, (int)($info['timeout'] ?? 10));
	147	+ $this->debug = (bool)($info['debug'] ?? false);
	148	+
	149	+ if (!in_array($this->channelBinding, ['prefer', 'require', 'disable'], true)) {
	150	+ throw new \InvalidArgumentException("channel_bindingはprefer、require、disable： {$this->channelBinding}");
	151	+ }
	152	+ // PostgreSQLの「utf8mb4」は無いので、MySQLと同じ設定でも動く様に
	153	+ if (in_array(strtolower($this->charset), ['utf8mb4', 'utf8', 'utf-8'], true)) $this->charset = 'UTF8';
	154	+ }
	155	+
	156	+ public function __destruct() {
	157	+ $this->close();
	158	+ }
	159	+
	160	+ /**
	161	+ * デバッギングの有無（有効にすると送受信したメッセージを出力し、ログに残す）
	162	+ *
	163	+ * @param bool $debug
	164	+ * @return Pgsql
	165	+ */
	166	+ public function setDebug(bool $debug): Pgsql {
	167	+ $this->debug = $debug;
	168	+ return $this;
	169	+ }
	170	+
	171	+ /**
	172	+ * メッセージのログ
	173	+ *
	174	+ * @return array direction、type、length、data、timestamp
	175	+ */
	176	+ public function getPacketLog(): array {
	177	+ return $this->packetLog;
	178	+ }
	179	+
	180	+ /**
	181	+ * サーバーの情報
	182	+ *
	183	+ * @return array サーバーのパラメータ（server_version、client_encoding等）、pid、tls、authMethod
	184	+ */
	185	+ public function getServerInfo(): array {
	186	+ return $this->params + ['pid' => $this->pid, 'tls' => $this->secure && $this->socketPath === '', 'authMethod' => $this->authMethod];
	187	+ }
	188	+
	189	+ /**
	190	+ * 最後のクエリの通知（NOTICE、WARNING等）
	191	+ *
	192	+ * @return array ['severity' => …, 'code' => SQLSTATE, 'message' => …]の配列
	193	+ */
	194	+ public function getNotices(): array {
	195	+ return $this->notices;
	196	+ }
	197	+
	198	+ /**
	199	+ * PostgreSQLサーバーに接続
	200	+ *
	201	+ * @return bool
	202	+ * @throws DbException 接続または認証に失敗した場合
	203	+ */
	204	+ public function connect(): bool {
	205	+ if ($this->connected) return true;
	206	+
	207	+ $unix = $this->socketPath !== '';
	208	+ if ($unix) {
	209	+ $path = @is_dir($this->socketPath) ? rtrim($this->socketPath, '/')."/.s.PGSQL.{$this->port}" : $this->socketPath;
	210	+ $target = "unix://{$path}";
	211	+ } else {
	212	+ $target = 'tcp://'.(str_contains($this->host, ':') ? "[{$this->host}]" : $this->host).":{$this->port}";
	213	+ }
	214	+ $errno = 0;
	215	+ $errstr = '';
	216	+ $socket = @stream_socket_client($target, $errno, $errstr, $this->timeout, STREAM_CLIENT_CONNECT, stream_context_create());
	217	+ if ($socket === false) $this->fail("ソケットに接続に失敗: {$errstr} ({$errno})");
	218	+ stream_set_timeout($socket, $this->timeout);
	219	+ $this->socket = $this->raw = $socket;
	220	+ $this->secure = $unix;
	221	+
	222	+ try {
	223	+ if ($this->tls !== false) $this->startTls();
	224	+ $this->startup();
	225	+ $this->connected = true;
	226	+ } catch (\Throwable $e) {
	227	+ $this->drop();
	228	+ throw $e;
	229	+ }
	230	+ return true;
	231	+ }
	232	+
	233	+ public function close(): void {
	234	+ if ($this->socket && $this->connected) {
	235	+ try {
	236	+ $this->send('X', ''); // Terminate
	237	+ $this->flush();
	238	+ } catch (\Throwable $e) {
	239	+ // 既に切れている
	240	+ }
	241	+ }
	242	+ $this->drop();
	243	+ }
	244	+
	245	+ public function isConnected(): bool {
	246	+ return $this->connected;
	247	+ }
	248	+
	249	+ /**
	250	+ * 接続がまだ生きているか確認する（空のクエリ）
	251	+ *
	252	+ * @return bool
	253	+ */
	254	+ public function ping(): bool {
	255	+ if (!$this->connected) return false;
	256	+ try {
	257	+ $this->query('');
	258	+ return true;
	259	+ } catch (\Throwable $e) {
	260	+ return false;
	261	+ }
	262	+ }
	263	+
	264	+ /**
	265	+ * SQLをそのまま実行する（単純クエリ、セミコロンで区切った複数の文も可）
	266	+ *
	267	+ * @param string $query SQL
	268	+ * @return array fields、rows、affectedRows、insertId（常に0）、warningCount、command（例：「INSERT 0 1」）（、more）
	269	+ * @throws DbException
	270	+ */
	271	+ public function query(string $query): array {
	272	+ $this->requireConnection();
	273	+ $this->notices = [];
	274	+ $this->send('Q', $query."\0");
	275	+ return $this->collect('クエリに失敗');
	276	+ }
	277	+
	278	+ /**
	279	+ * プリペアドステートメントの準備
	280	+ *
	281	+ * @param string $query プレースホルダ（「?」又は「$1」）付きのSQL
	282	+ * @return int ステートメントID
	283	+ * @throws DbException
	284	+ */
	285	+ public function prepare(string $query): int {
	286	+ $this->requireConnection();
	287	+ $id = $this->nextStmt++;
	288	+ $name = "lb{$id}";
	289	+
	290	+ $this->send('P', $name."\0".self::placeholders($query)."\0\0\0");
	291	+ $this->send('D', 'S'.$name."\0");
	292	+ $this->send('S', '');
	293	+
	294	+ $params = [];
	295	+ $error = null;
	296	+ while (true) {
	297	+ [$type, $body] = $this->readMessage();
	298	+ switch ($type) {
	299	+ case '1': // ParseComplete
	300	+ case 'n': // NoData
	301	+ case 'T': // RowDescription（列は実行の時にポータルのDescribeで受け取る）
	302	+ break;
	303	+ case 't': // ParameterDescription
	304	+ $n = unpack('n', $body)[1];
	305	+ $params = $n > 0 ? array_values(unpack("N{$n}", $body, 2)) : [];
	306	+ break;
	307	+ case 'E':
	308	+ $error = $this->parseError($body);
	309	+ if ($this->fatal($error)) $this->failError('準備に失敗', $error);
	310	+ break;
	311	+ case 'Z':
	312	+ $this->status = $body;
	313	+ if ($error) $this->failError('準備に失敗', $error);
	314	+ $this->prepared[$id] = ['name' => $name, 'params' => $params];
	315	+ return $id;
	316	+ default:
	317	+ $this->other($type, $body);
	318	+ }
	319	+ }
	320	+ }
	321	+
	322	+ /**
	323	+ * プリペアドステートメントの実行
	324	+ *
	325	+ * int・float・bool・null・文字列（bytea型には生のバイト）・DateTimeInterface・BackedEnumを送れます。
	326	+ *
	327	+ * @param int $statementId ステートメントID
	328	+ * @param array $params パラメータ（順番通り）
	329	+ * @return array 結果（query()と同じ形）
	330	+ * @throws DbException
	331	+ */
	332	+ public function execute(int $statementId, array $params = []): array {
	333	+ $this->requireConnection();
	334	+ if (!isset($this->prepared[$statementId])) $this->fail("不正なステートメントID: {$statementId}");
	335	+
	336	+ $stmt = $this->prepared[$statementId];
	337	+ $params = array_values($params);
	338	+ $n = count($stmt['params']);
	339	+ if (count($params) !== $n) {
	340	+ $this->fail("パラメータ数が一致しません: 期待 {$n}, 取得 ".count($params));
	341	+ }
	342	+
	343	+ $formats = '';
	344	+ $values = '';
	345	+ foreach ($params as $i => $v) {
	346	+ [$format, $data] = $this->encodeParam($v, $stmt['params'][$i], $i);
	347	+ $formats .= pack('n', $format);
	348	+ $values .= $data === null ? pack('N', 0xFFFFFFFF) : pack('N', strlen($data)).$data;
	349	+ }
	350	+
	351	+ $this->notices = [];
	352	+ $this->send('B', "\0".$stmt['name']."\0".pack('n', $n).$formats.pack('n', $n).$values."\0\0");
	353	+ $this->send('D', "P\0");
	354	+ $this->send('E', "\0".pack('N', 0));
	355	+ $this->send('S', '');
	356	+ return $this->collect('実行に失敗');
	357	+ }
	358	+
	359	+ /**
	360	+ * プリペアドステートメントの解放
	361	+ *
	362	+ * @param int $statementId
	363	+ * @return bool
	364	+ */
	365	+ public function demolish(int $statementId): bool {
	366	+ if (!isset($this->prepared[$statementId])) return false;
	367	+ $name = $this->prepared[$statementId]['name'];
	368	+ unset($this->prepared[$statementId]);
	369	+ if (!$this->connected) return false;
	370	+
	371	+ $this->send('C', 'S'.$name."\0");
	372	+ $this->send('S', '');
	373	+ while (true) {
	374	+ [$type, $body] = $this->readMessage();
	375	+ if ($type === 'Z') {
	376	+ $this->status = $body;
	377	+ return true;
	378	+ }
	379	+ if ($type !== '3' && $type !== 'E') $this->other($type, $body);
	380	+ }
	381	+ }
	382	+
	383	+ public function beginTransaction(): bool {
	384	+ $this->query('BEGIN');
	385	+ return true;
	386	+ }
	387	+
	388	+ /**
	389	+ * トランザクションをコミットする
	390	+ *
	391	+ * 失敗したトランザクション（エラーの後）は、PostgreSQLがCOMMITをROLLBACKにするので、例外にする。
	392	+ *
	393	+ * @return bool
	394	+ * @throws DbException 失敗したトランザクションの場合（SQLSTATEは25P02、ロールバック済み）
	395	+ */
	396	+ public function commit(): bool {
	397	+ $res = $this->query('COMMIT');
	398	+ if ($res['command'] === 'ROLLBACK') {
	399	+ $this->fail('コミットに失敗: トランザクションの中でエラーが起きたので、ロールバックしました', 0, '25P02');
	400	+ }
	401	+ return true;
	402	+ }
	403	+
	404	+ public function rollback(): bool {
	405	+ $this->query('ROLLBACK');
	406	+ return true;
	407	+ }
	408	+
	409	+ /** トランザクション中（失敗したトランザクションも）かどうか */
	410	+ public function inTransaction(): bool {
	411	+ return $this->connected && ($this->status === 'T' \|\| $this->status === 'E');
	412	+ }
	413	+
	414	+ public function quoteIdentifier(string $name): string {
	415	+ return '"'.str_replace('"', '""', $name).'"';
	416	+ }
	417	+
	418	+ /**
	419	+ * 「?」のプレースホルダを「$1」「$2」…にする
	420	+ *
	421	+ * 文字列（'…'、E'…'）、識別子（"…"）、$tag$…$tag$、コメントの中は変えない。「??」は「?」そのもの（jsonbの演算子等）。
	422	+ *
	423	+ * @param string $sql
	424	+ * @return string
	425	+ */
	426	+ public static function placeholders(string $sql): string {
	427	+ if (!str_contains($sql, '?')) return $sql;
	428	+ $out = '';
	429	+ $n = 0;
	430	+ $len = strlen($sql);
	431	+ for ($i = 0; $i < $len; $i++) {
	432	+ $c = $sql[$i];
	433	+ $prev = $i > 0 ? $sql[$i - 1] : '';
	434	+ $word = fn(string $ch): bool => $ch !== '' && (ctype_alnum($ch) \|\| $ch === '_' \|\| ord($ch) >= 0x80);
	435	+
	436	+ if ($c === "'") {
	437	+ $escape = ($prev === 'E' \|\| $prev === 'e') && !$word($i > 1 ? $sql[$i - 2] : '');
	438	+ $j = $i + 1;
	439	+ while ($j < $len) {
	440	+ if ($escape && $sql[$j] === '\\') {
	441	+ $j += 2;
	442	+ continue;
	443	+ }
	444	+ if ($sql[$j] === "'") {
	445	+ if (($sql[$j + 1] ?? '') === "'") {
	446	+ $j += 2;
	447	+ continue;
	448	+ }
	449	+ break;
	450	+ }
	451	+ $j++;
	452	+ }
	453	+ $out .= substr($sql, $i, $j - $i + 1);
	454	+ $i = $j;
	455	+ } else if ($c === '"') {
	456	+ $j = $i + 1;
	457	+ while ($j < $len && !($sql[$j] === '"' && ($sql[$j + 1] ?? '') !== '"')) $j += $sql[$j] === '"' ? 2 : 1;
	458	+ $out .= substr($sql, $i, $j - $i + 1);
	459	+ $i = $j;
	460	+ } else if ($c === '$' && !$word($prev) && preg_match('/\G\$([A-Za-z_][A-Za-z0-9_]*)?\$/', $sql, $m, 0, $i)) {
	461	+ $end = strpos($sql, $m[0], $i + strlen($m[0]));
	462	+ $j = $end === false ? $len : $end + strlen($m[0]);
	463	+ $out .= substr($sql, $i, $j - $i);
	464	+ $i = $j - 1;
	465	+ } else if ($c === '-' && ($sql[$i + 1] ?? '') === '-') {
	466	+ $j = strpos($sql, "\n", $i);
	467	+ $j = $j === false ? $len : $j;
	468	+ $out .= substr($sql, $i, $j - $i);
	469	+ $i = $j - 1;
	470	+ } else if ($c === '/' && ($sql[$i + 1] ?? '') === '*') {
	471	+ // 入れ子のコメント
	472	+ $depth = 0;
	473	+ $j = $i;
	474	+ while ($j < $len) {
	475	+ if (substr($sql, $j, 2) === '/*') {
	476	+ $depth++;
	477	+ $j += 2;
	478	+ } else if (substr($sql, $j, 2) === '*/') {
	479	+ $depth--;
	480	+ $j += 2;
	481	+ if ($depth === 0) break;
	482	+ } else {
	483	+ $j++;
	484	+ }
	485	+ }
	486	+ $out .= substr($sql, $i, $j - $i);
	487	+ $i = $j - 1;
	488	+ } else if ($c === '?') {
	489	+ if (($sql[$i + 1] ?? '') === '?') {
	490	+ $out .= '?';
	491	+ $i++;
	492	+ } else {
	493	+ $out .= '$'.(++$n);
	494	+ }
	495	+ } else {
	496	+ $out .= $c;
	497	+ }
	498	+ }
	499	+ return $out;
	500	+ }
	501	+
	502	+ /**
	503	+ * 配列のテキスト（例：{1,2,NULL}、{{a,b},{"c,d",e}}）をPHPの配列にする
	504	+ *
	505	+ * @param string $text PostgreSQLの配列の書き方
	506	+ * @param int $elementOid 要素の型（値の変換に使う）
	507	+ * @return array\|null
	508	+ */
	509	+ public static function parseArray(string $text, int $elementOid = self::OID_TEXT): ?array {
	510	+ // 範囲の指定（例：[0:1]={1,2}）は無視する
	511	+ if ($text !== '' && $text[0] === '[') $text = substr($text, strpos($text, '=') + 1);
	512	+ $pos = 0;
	513	+ $res = self::parseArrayLevel($text, $pos, $elementOid);
	514	+ return $pos === strlen($text) ? $res : null;
	515	+ }
	516	+
	517	+ private static function parseArrayLevel(string $t, int &$pos, int $oid): ?array {
	518	+ if (($t[$pos] ?? '') !== '{') return null;
	519	+ $pos++;
	520	+ $out = [];
	521	+ $len = strlen($t);
	522	+ if (($t[$pos] ?? '') === '}') {
	523	+ $pos++;
	524	+ return [];
	525	+ }
	526	+ while ($pos < $len) {
	527	+ if ($t[$pos] === '{') {
	528	+ $out[] = self::parseArrayLevel($t, $pos, $oid);
	529	+ } else if ($t[$pos] === '"') {
	530	+ $v = '';
	531	+ for ($pos++; $pos < $len && $t[$pos] !== '"'; $pos++) {
	532	+ if ($t[$pos] === '\\') $pos++;
	533	+ $v .= $t[$pos] ?? '';
	534	+ }
	535	+ $pos++;
	536	+ $out[] = self::castText($v, $oid);
	537	+ } else {
	538	+ $start = $pos;
	539	+ while ($pos < $len && $t[$pos] !== ',' && $t[$pos] !== '}') $pos++;
	540	+ $v = substr($t, $start, $pos - $start);
	541	+ $out[] = strtoupper($v) === 'NULL' ? null : self::castText($v, $oid);
	542	+ }
	543	+ $c = $t[$pos] ?? '';
	544	+ $pos++;
	545	+ if ($c === '}') return $out;
	546	+ if ($c !== ',') return null;
	547	+ }
	548	+ return null;
	549	+ }
	550	+
	551	+ /**
	552	+ * テキストの値をPHPの型にする
	553	+ *
	554	+ * @param string $v 値
	555	+ * @param int $oid 型
	556	+ * @return mixed 整数はint、浮動小数点数はfloat、真偽値はbool、byteaはバイト、配列は配列、その他は文字列
	557	+ */
	558	+ public static function castText(string $v, int $oid): mixed {
	559	+ switch ($oid) {
	560	+ case self::OID_INT2:
	561	+ case self::OID_INT4:
	562	+ case self::OID_INT8:
	563	+ case self::OID_OID:
	564	+ $i = (int)$v;
	565	+ return (string)$i === $v ? $i : $v;
	566	+ case self::OID_FLOAT4:
	567	+ case self::OID_FLOAT8:
	568	+ return match ($v) {
	569	+ 'NaN' => NAN,
	570	+ 'Infinity' => INF,
	571	+ '-Infinity' => -INF,
	572	+ default => (float)$v,
	573	+ };
	574	+ case self::OID_BOOL:
	575	+ return $v === 't';
	576	+ case self::OID_BYTEA:
	577	+ if (str_starts_with($v, '\\x')) return (string)hex2bin(substr($v, 2));
	578	+ // escape形式（bytea_output = escape）
	579	+ return preg_replace_callback('/\\\\(\\\\\|[0-7]{3})/', fn($m) => $m[1] === '\\' ? '\\' : chr(octdec($m[1])), $v);
	580	+ }
	581	+ if (isset(self::ARRAYS[$oid])) return self::parseArray($v, self::ARRAYS[$oid]) ?? $v;
	582	+ return $v;
	583	+ }
	584	+
	585	+ ///////////////
	586	+ // 接続・認証
	587	+
	588	+ /** SSLRequestを送って、TLSを開始する */
	589	+ private function startTls(): void {
	590	+ $this->send(null, pack('NN', 8, self::SSL_REQUEST));
	591	+ $answer = $this->readBytes(1);
	592	+ if ($answer !== 'S') $this->fail('サーバーがTLSに対応していません');
	593	+
	594	+ $opts = is_array($this->tls) ? $this->tls : [];
	595	+ $opts += ['peer_name' => $this->host];
	596	+ try {
	597	+ $this->socket = Tls::enable($this->raw, $opts);
	598	+ } catch (TlsException $e) {
	599	+ $this->fail('TLSの開始に失敗: '.$e->getMessage());
	600	+ }
	601	+ $this->secure = true;
	602	+ }
	603	+
	604	+ /** StartupMessageと認証、ReadyForQueryまで */
	605	+ private function startup(): void {
	606	+ $params = ['user' => $this->username, 'database' => $this->dbname, 'client_encoding' => $this->charset,
	607	+ 'application_name' => 'LittleBeast', 'DateStyle' => 'ISO, YMD'];
	608	+ $body = pack('N', self::PROTOCOL);
	609	+ foreach ($params as $k => $v) {
	610	+ if ($v !== '') $body .= $k."\0".$v."\0";
	611	+ }
	612	+ $this->send(null, pack('N', strlen($body) + 5).$body."\0");
	613	+
	614	+ $scram = null;
	615	+ while (true) {
	616	+ [$type, $body] = $this->readMessage();
	617	+ if ($type === 'E') $this->failError('接続に失敗', $this->parseError($body));
	618	+ if ($type === 'Z') {
	619	+ $this->status = $body;
	620	+ return;
	621	+ }
	622	+ if ($type !== 'R') {
	623	+ $this->other($type, $body);
	624	+ continue;
	625	+ }
	626	+
	627	+ $code = unpack('N', $body)[1];
	628	+ switch ($code) {
	629	+ case 0: // AuthenticationOk
	630	+ if ($scram !== null && empty($scram['verified'])) $this->fail('認証に失敗: サーバーがSCRAMを完了しませんでした');
	631	+ if ($this->authMethod === '') $this->authMethod = 'trust';
	632	+ break;
	633	+ case 3: // パスワードをそのまま
	634	+ if (!$this->secure && !$this->allowCleartext) {
	635	+ $this->fail('認証に失敗: サーバーがパスワードをそのまま送る事を求めました（TLS・UNIXソケットでない場合は、allow_cleartextが必要）');
	636	+ }
	637	+ $this->authMethod = 'password';
	638	+ $this->send('p', $this->password."\0");
	639	+ break;
	640	+ case 5: // MD5
	641	+ $this->authMethod = 'md5';
	642	+ $this->send('p', 'md5'.md5(md5($this->password.$this->username).substr($body, 4, 4))."\0");
	643	+ break;
	644	+ case 10: // SASL
	645	+ $scram = $this->scramStart(array_filter(explode("\0", substr($body, 4))));
	646	+ break;
	647	+ case 11: // SASLContinue
	648	+ if ($scram === null) $this->fail('認証に失敗: 想定外のSASLContinue');
	649	+ $scram = $this->scramContinue($scram, substr($body, 4));
	650	+ break;
	651	+ case 12: // SASLFinal
	652	+ if ($scram === null) $this->fail('認証に失敗: 想定外のSASLFinal');
	653	+ $this->scramFinal($scram, substr($body, 4));
	654	+ $scram['verified'] = true;
	655	+ break;
	656	+ default:
	657	+ $this->fail("未対応の認証方式: {$code}（GSSAPI・SSPI・Kerberosは未対応）");
	658	+ }
	659	+ }
	660	+ }
	661	+
	662	+ /** SCRAM-SHA-256（-PLUS）を始める（RFC 5802、RFC 7677） */
	663	+ private function scramStart(array $mechanisms): array {
	664	+ $cbData = $this->secure && $this->socketPath === '' && $this->channelBinding !== 'disable' ? $this->channelBindingData() : null;
	665	+ if (in_array('SCRAM-SHA-256-PLUS', $mechanisms, true) && $cbData !== null) {
	666	+ $mech = 'SCRAM-SHA-256-PLUS';
	667	+ $gs2 = 'p=tls-server-end-point,,';
	668	+ } else if (in_array('SCRAM-SHA-256', $mechanisms, true)) {
	669	+ if ($this->channelBinding === 'require') $this->fail('認証に失敗: チャネルバインディングが使えません（channel_binding = require）');
	670	+ $mech = 'SCRAM-SHA-256';
	671	+ // TLSなのにサーバーが-PLUSを出さない＝クライアントは対応している（ダウングレードを見つける為の「y」）
	672	+ $gs2 = $cbData !== null ? 'y,,' : 'n,,';
	673	+ $cbData = null;
	674	+ } else {
	675	+ $this->fail('未対応のSASLの方式: '.implode(', ', $mechanisms));
	676	+ }
	677	+
	678	+ $nonce = rtrim(strtr(base64_encode(random_bytes(18)), '+/', '-_'), '=');
	679	+ $first = 'n=,r='.$nonce; // ユーザー名はStartupMessageの物を使う
	680	+ $this->authMethod = $mech;
	681	+ $this->send('p', $mech."\0".pack('N', strlen($gs2.$first)).$gs2.$first);
	682	+ return ['nonce' => $nonce, 'first' => $first, 'gs2' => $gs2, 'cb' => $cbData];
	683	+ }
	684	+
	685	+ private function scramContinue(array $scram, string $serverFirst): array {
	686	+ $a = [];
	687	+ foreach (explode(',', $serverFirst) as $kv) {
	688	+ if (strlen($kv) > 2 && $kv[1] === '=') $a[$kv[0]] = substr($kv, 2);
	689	+ }
	690	+ $salt = base64_decode($a['s'] ?? '', true);
	691	+ $iter = (int)($a['i'] ?? 0);
	692	+ if (!isset($a['r']) \|\| !str_starts_with($a['r'], $scram['nonce']) \|\| strlen($a['r']) <= strlen($scram['nonce']) \|\| $salt === false \|\| $iter < 1) {
	693	+ $this->fail('認証に失敗: 不正なSCRAMのメッセージ');
	694	+ }
	695	+
	696	+ $salted = hash_pbkdf2('sha256', self::saslPrep($this->password), $salt, $iter, 32, true);
	697	+ $clientKey = hash_hmac('sha256', 'Client Key', $salted, true);
	698	+ $finalBare = 'c='.base64_encode($scram['gs2'].($scram['cb'] ?? '')).',r='.$a['r'];
	699	+ $authMessage = $scram['first'].','.$serverFirst.','.$finalBare;
	700	+ $proof = $clientKey ^ hash_hmac('sha256', $authMessage, hash('sha256', $clientKey, true), true);
	701	+ $this->send('p', $finalBare.',p='.base64_encode($proof));
	702	+
	703	+ return $scram + ['salted' => $salted, 'auth' => $authMessage];
	704	+ }
	705	+
	706	+ /** サーバーの署名を確認する（偽物のサーバーではない事） */
	707	+ private function scramFinal(array $scram, string $serverFinal): void {
	708	+ if (str_starts_with($serverFinal, 'e=')) $this->fail('認証に失敗: '.substr($serverFinal, 2));
	709	+ $expected = hash_hmac('sha256', $scram['auth'], hash_hmac('sha256', 'Server Key', $scram['salted'], true), true);
	710	+ if (!str_starts_with($serverFinal, 'v=') \|\| !hash_equals($expected, (string)base64_decode(substr($serverFinal, 2), true))) {
	711	+ $this->fail('認証に失敗: サーバーの署名が不正です');
	712	+ }
	713	+ }
	714	+
	715	+ /** tls-server-end-point（RFC 5929）：サーバーの証明書のハッシュ（MD5・SHA-1ならSHA-256） */
	716	+ private function channelBindingData(): ?string {
	717	+ $cert = Tls::info($this->socket)['peer_certificates'][0] ?? null;
	718	+ if ($cert === null) return null;
	719	+ $hash = match ($cert->sigAlg) {
	720	+ '1.2.840.113549.1.1.12', '1.2.840.10045.4.3.3' => 'sha384',
	721	+ '1.2.840.113549.1.1.13', '1.2.840.10045.4.3.4' => 'sha512',
	722	+ default => 'sha256',
	723	+ };
	724	+ return hash($hash, $cert->der, true);
	725	+ }
	726	+
	727	+ /**
	728	+ * パスワードの正規化（SASLprep、RFC 4013の簡略版）
	729	+ *
	730	+ * intlのNormalizerが有ればNFKC、無ければそのまま（ASCIIと普通の日本語は同じ）。
	731	+ */
	732	+ private static function saslPrep(string $pw): string {
	733	+ if (preg_match('/^[\x20-\x7E]*$/', $pw) \|\| !mb_check_encoding($pw, 'UTF-8')) return $pw;
	734	+ // 色々なスペースはスペース、ソフトハイフン等は消す
	735	+ $pw = preg_replace(['/[\x{00A0}\x{1680}\x{2000}-\x{200B}\x{202F}\x{205F}\x{3000}]/u', '/[\x{00AD}\x{034F}\x{1806}\x{180B}-\x{180D}\x{200C}\x{200D}\x{2060}\x{FE00}-\x{FE0F}\x{FEFF}]/u'], [' ', ''], $pw);
	736	+ return class_exists('Normalizer') ? (\Normalizer::normalize($pw, \Normalizer::FORM_KC) ?: $pw) : $pw;
	737	+ }
	738	+
	739	+ ///////////////
	740	+ // 結果
	741	+
	742	+ /**
	743	+ * ReadyForQueryまでの結果を集める
	744	+ *
	745	+ * @param string $what 失敗した時のメッセージ
	746	+ * @return array
	747	+ * @throws DbException
	748	+ */
	749	+ private function collect(string $what): array {
	750	+ $results = [];
	751	+ $cur = ['fields' => [], 'rows' => []];
	752	+ $error = null;
	753	+
	754	+ while (true) {
	755	+ [$type, $body] = $this->readMessage();
	756	+ switch ($type) {
	757	+ case 'T': // RowDescription
	758	+ $cur = ['fields' => $this->parseRowDescription($body), 'rows' => []];
	759	+ break;
	760	+ case 'D': // DataRow
	761	+ $cur['rows'][] = $this->parseDataRow($body, $cur['fields']);
	762	+ break;
	763	+ case 'C': // CommandComplete
	764	+ $tag = rtrim($body, "\0");
	765	+ $results[] = $cur + ['command' => $tag, 'affectedRows' => $this->affected($tag)];
	766	+ $cur = ['fields' => [], 'rows' => []];
	767	+ break;
	768	+ case 'I': // EmptyQueryResponse
	769	+ $results[] = ['fields' => [], 'rows' => [], 'command' => '', 'affectedRows' => 0];
	770	+ break;
	771	+ case 's': // PortalSuspended
	772	+ $results[] = $cur + ['command' => '', 'affectedRows' => 0];
	773	+ break;
	774	+ case '2': // BindComplete
	775	+ case 'n': // NoData（行を返さない文）
	776	+ break;
	777	+ case 'E':
	778	+ $error = $this->parseError($body);
	779	+ if ($this->fatal($error)) $this->failError($what, $error);
	780	+ break;
	781	+ case 'G': // CopyInResponse：断る
	782	+ $this->send('f', "COPYは未対応です\0");
	783	+ break;
	784	+ case 'H': // CopyOutResponse：データは無視する
	785	+ case 'd':
	786	+ case 'c':
	787	+ break;
	788	+ case 'Z':
	789	+ $this->status = $body;
	790	+ if ($error) $this->failError($what, $error);
	791	+ if (empty($results)) $results[] = ['fields' => [], 'rows' => [], 'command' => '', 'affectedRows' => 0];
	792	+ $warnings = count($this->notices);
	793	+ foreach ($results as &$r) $r += ['insertId' => 0, 'warningCount' => $warnings];
	794	+ unset($r);
	795	+ $first = array_shift($results);
	796	+ if (!empty($results)) $first['more'] = $results;
	797	+ return $first;
	798	+ default:
	799	+ $this->other($type, $body);
	800	+ }
	801	+ }
	802	+ }
	803	+
	804	+ /** 「INSERT 0 5」「UPDATE 3」等から行数 */
	805	+ private function affected(string $tag): int {
	806	+ return preg_match('/^(?:INSERT \d+\|UPDATE\|DELETE\|SELECT\|MOVE\|FETCH\|COPY\|MERGE) (\d+)$/', $tag, $m) ? (int)$m[1] : 0;
	807	+ }
	808	+
	809	+ private function parseRowDescription(string $b): array {
	810	+ $n = unpack('n', $b)[1];
	811	+ $pos = 2;
	812	+ $fields = [];
	813	+ for ($i = 0; $i < $n; $i++) {
	814	+ $end = strpos($b, "\0", $pos);
	815	+ $name = substr($b, $pos, $end - $pos);
	816	+ $f = unpack('Ntable/ncolumn/Ntype/nsize/Nmodifier/nformat', $b, $end + 1);
	817	+ $pos = $end + 19;
	818	+ $fields[] = ['name' => $name, 'table' => $f['table'], 'column' => $f['column'], 'type' => $f['type'],
	819	+ 'size' => $f['size'] >= 0x8000 ? $f['size'] - 0x10000 : $f['size'],
	820	+ 'modifier' => $f['modifier'] >= 0x80000000 ? $f['modifier'] - 0x100000000 : $f['modifier'], 'format' => $f['format']];
	821	+ }
	822	+ return $fields;
	823	+ }
	824	+
	825	+ private function parseDataRow(string $b, array $fields): array {
	826	+ $n = unpack('n', $b)[1];
	827	+ $pos = 2;
	828	+ $row = [];
	829	+ for ($i = 0; $i < $n; $i++) {
	830	+ $len = unpack('N', $b, $pos)[1];
	831	+ $pos += 4;
	832	+ $f = $fields[$i] ?? ['name' => (string)$i, 'type' => self::OID_TEXT];
	833	+ if ($len === 0xFFFFFFFF) {
	834	+ $row[$f['name']] = null;
	835	+ continue;
	836	+ }
	837	+ $row[$f['name']] = self::castText(substr($b, $pos, $len), $f['type']);
	838	+ $pos += $len;
	839	+ }
	840	+ return $row;
	841	+ }
	842	+
	843	+ /**
	844	+ * パラメータを送る形にする
	845	+ *
	846	+ * @return array [形式（0＝テキスト、1＝バイナリ）, データ（nullならNULL）]
	847	+ */
	848	+ private function encodeParam(mixed $v, int $oid, int $i): array {
	849	+ if ($v instanceof \BackedEnum) $v = $v->value;
	850	+ if ($v instanceof \DateTimeInterface) $v = $v->format('Y-m-d H:i:s.uP');
	851	+ if ($v === null) return [0, null];
	852	+ if (is_bool($v)) {
	853	+ $int = in_array($oid, [self::OID_INT2, self::OID_INT4, self::OID_INT8], true);
	854	+ return [0, $int ? ($v ? '1' : '0') : ($v ? 't' : 'f')];
	855	+ }
	856	+ if (is_int($v)) return [0, (string)$v];
	857	+ if (is_float($v)) {
	858	+ if (is_nan($v)) return [0, 'NaN'];
	859	+ if (is_infinite($v)) return [0, $v > 0 ? 'Infinity' : '-Infinity'];
	860	+ return [0, var_export($v, true)];
	861	+ }
	862	+ if (is_scalar($v) \|\| $v instanceof \Stringable) {
	863	+ // byteaには生のバイト（バイナリ形式）を送る（テキストだと「\」が特別な意味になる）
	864	+ return $oid === self::OID_BYTEA ? [1, (string)$v] : [0, (string)$v];
	865	+ }
	866	+ $this->fail("パラメータ{$i}の型は未対応です: ".get_debug_type($v));
	867	+ }
	868	+
	869	+ ///////////////
	870	+ // メッセージ
	871	+
	872	+ /** 接続の処理中に来る、結果と関係の無いメッセージ */
	873	+ private function other(string $type, string $body): void {
	874	+ switch ($type) {
	875	+ case 'S': // ParameterStatus
	876	+ [$k, $v] = explode("\0", $body) + ['', ''];
	877	+ $this->params[$k] = $v;
	878	+ return;
	879	+ case 'K': // BackendKeyData
	880	+ $this->pid = unpack('N', $body)[1];
	881	+ return;
	882	+ case 'N': // NoticeResponse
	883	+ $e = $this->parseError($body);
	884	+ $this->notices[] = ['severity' => $e['V'] ?? $e['S'] ?? '', 'code' => $e['C'] ?? '', 'message' => $e['M'] ?? ''];
	885	+ return;
	886	+ case 'A': // NotificationResponse（LISTEN）：無視する
	887	+ return;
	888	+ }
	889	+ $this->fail("想定外のメッセージ: {$type}");
	890	+ }
	891	+
	892	+ /** ErrorResponse・NoticeResponseのフィールド（S、V、C、M、D、H等） */
	893	+ private function parseError(string $b): array {
	894	+ $out = [];
	895	+ foreach (explode("\0", rtrim($b, "\0")) as $f) {
	896	+ if ($f !== '') $out[$f[0]] = substr($f, 1);
	897	+ }
	898	+ return $out;
	899	+ }
	900	+
	901	+ /** FATAL・PANICのエラーの後はサーバーが接続を切る */
	902	+ private function fatal(array $e): bool {
	903	+ return in_array($e['V'] ?? $e['S'] ?? '', ['FATAL', 'PANIC'], true);
	904	+ }
	905	+
	906	+ /**
	907	+ * サーバーのエラーで例外を投げる
	908	+ *
	909	+ * @return never
	910	+ */
	911	+ private function failError(string $what, array $e): never {
	912	+ if ($this->fatal($e)) $this->drop();
	913	+ $code = $e['C'] ?? '';
	914	+ $msg = "{$what}: {$code} - ".($e['M'] ?? '不明なエラー');
	915	+ if (!empty($e['D'])) $msg .= "（{$e['D']}）";
	916	+ $this->fail($msg, 0, $code);
	917	+ }
	918	+
	919	+ private function readMessage(): array {
	920	+ $head = $this->readBytes(5);
	921	+ $type = $head[0];
	922	+ $len = unpack('N', $head, 1)[1];
	923	+ if ($len < 4 \|\| $len > 0x40000000) {
	924	+ $this->drop();
	925	+ $this->fail("不正なメッセージの長さ: {$len}");
	926	+ }
	927	+ $body = $len > 4 ? $this->readBytes($len - 4) : '';
	928	+ if ($this->debug) $this->log('RECV', $type, $body);
	929	+ return [$type, $body];
	930	+ }
	931	+
	932	+ private function readBytes(int $len): string {
	933	+ if (!$this->socket) $this->fail('接続していません');
	934	+ if ($this->out !== '') $this->flush();
	935	+ $buf = '';
	936	+ while (strlen($buf) < $len) {
	937	+ $chunk = @fread($this->socket, min($len - strlen($buf), 1048576));
	938	+ if ($chunk === false \|\| $chunk === '') {
	939	+ $timedOut = Tls::timedOut($this->socket) \|\| (bool)(stream_get_meta_data($this->raw ?? $this->socket)['timed_out'] ?? false);
	940	+ if (!$timedOut && $chunk === '' && !feof($this->socket)) continue;
	941	+ $this->drop();
	942	+ $this->fail($timedOut ? "メッセージの読み込みがタイムアウト（{$this->timeout}秒）" : 'サーバーとの接続が切れました');
	943	+ }
	944	+ $buf .= $chunk;
	945	+ }
	946	+ return $buf;
	947	+ }
	948	+
	949	+ /**
	950	+ * メッセージを送る（バッファに溜めて、次に読む時にまとめて送る）
	951	+ *
	952	+ * Parse・Bind・Execute・Syncを別々に送ると、Nagleと遅延ACKで1回40ms程待たされる為。
	953	+ *
	954	+ * @param string\|null $type 種類（StartupMessage・SSLRequestはnull）
	955	+ * @param string $body 中身
	956	+ */
	957	+ private function send(?string $type, string $body): void {
	958	+ if (!$this->socket) $this->fail('接続していません');
	959	+ $this->out .= $type === null ? $body : $type.pack('N', strlen($body) + 4).$body;
	960	+ if ($this->debug) $this->log('SEND', $type ?? '-', $body);
	961	+ }
	962	+
	963	+ /** 溜めたメッセージを送る */
	964	+ private function flush(): void {
	965	+ $data = $this->out;
	966	+ $this->out = '';
	967	+ for ($w = 0, $len = strlen($data); $w < $len; ) {
	968	+ $n = @fwrite($this->socket, substr($data, $w, 1048576));
	969	+ if ($n === false \|\| $n === 0) {
	970	+ $this->drop();
	971	+ $this->fail('メッセージの送信に失敗');
	972	+ }
	973	+ $w += $n;
	974	+ }
	975	+ }
	976	+
	977	+ private function log(string $dir, string $type, string $body): void {
	978	+ $this->packetLog[] = ['direction' => $dir, 'type' => $type, 'length' => strlen($body), 'data' => $body, 'timestamp' => microtime(true)];
	979	+ $show = preg_replace('/[^\x20-\x7E]/', '.', substr($body, 0, 200));
	980	+ echo "=== {$dir} {$type} (長さ: ".strlen($body).") === {$show}\n";
	981	+ }
	982	+
	983	+ private function requireConnection(): void {
	984	+ if (!$this->connected) $this->fail('接続していません（先にconnect()を呼んで下さい）');
	985	+ }
	986	+
	987	+ private function drop(): void {
	988	+ if ($this->socket) @fclose($this->socket);
	989	+ if ($this->raw && is_resource($this->raw)) @fclose($this->raw);
	990	+ $this->socket = $this->raw = null;
	991	+ $this->out = '';
	992	+ $this->connected = false;
	993	+ $this->secure = false;
	994	+ $this->status = '';
	995	+ $this->prepared = [];
	996	+ }
	997	+
	998	+ /**
	999	+ * @return never
	1000	+ * @throws DbException
	1001	+ */
	1002	+ private function fail(string $msg, int $code = 0, string $sqlState = ''): never {
	1003	+ if (function_exists('logger')) logger(\LogType::Db, $msg);
	1004	+ throw new DbException($msg, $code, $sqlState);
	1005	+ }
	1006	+}

追加 src/Std/Test/Helper/pg-server.php

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

追加 src/Std/Test/LibPgsql.php

		@@ -0,0 +1,672 @@
	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\Test;
	39	+
	40	+require_once __DIR__.'/../../../autoload.php';
	41	+
	42	+use Std\Lib\Tester;
	43	+use Std\Lib\Db;
	44	+use Std\Lib\Db\DbException;
	45	+use Std\Lib\Db\Pgsql;
	46	+use Std\Test\Helper\MockServer;
	47	+use 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	+
	57	+enum 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();