warehouse: a relative SQLite file: URI still follows the working directory, and the fix is platform-dependent

Bắt đầu từ phần xử lý kết nối warehouse liên quan đến URI của tệp SQLite và so sánh với nỗ lực sửa lỗi #1204 được mô tả trong issue. Tái hiện bằng một cơ sở dữ liệu thực và một cơ sở dữ liệu mồi trong khi thay đổi cwd/--dir, sau đó thêm bước thăm dò chế độ URI lúc chạy và hành vi viết lại chỉ dành cho SQLite. Được xem là hoàn tất khi mọi nhánh được liệt kê đều được bao phủ bởi các bài kiểm thử bằng binary đã biên dịch trên các nền tảng được phát hành, với dữ liệu mồi chứng minh rằng cơ sở dữ liệu dự định được mở.

What happens

A connection configured with a relative SQLite file: URI follows the process working directory, so --dir can point it at a different database. On macOS this is a genuine instance of the #1203 defect class, and a nastier one: the create-on-open guard cannot catch it, because the failure is opening the wrong existing database rather than making a new empty one.

Reproduced on macOS with bun:sqlite, one real store and one decoy:

config:            file:warehouse.db
cwd:               <decoy dir>
opens:             ["decoy_table"]      ← the decoy, not the configured store

with an absolute rewrite:
config:            file:/abs/path/warehouse.db
opens:             ["zorbulax_ledger"]  ← the intended store

Why it is not fixed in #1204

I implemented the rewrite in #1204 and then removed it, because it is platform-dependent in a way I could not verify across the shipped targets.

bun:sqlite's URI handling is not uniform. On macOS, file:warehouse.db is parsed as a URI (SQLITE_OPEN_URI behaviour): the reproduction above is real. On Linux CI the same test failed — the configured path opened nothing, which is consistent with file: being treated as a literal filename rather than a URI there. Windows was never verified at all, and it is a shipped build target (packages/opencode/script/build.ts).

That matters because the rewrite changes which database opens. Applying it on a platform where file: is a literal filename turns a working config into a broken one — precisely the class of bug #1203 is about. Shipping it half-verified would have been worse than leaving the exotic case alone.

The attempt also produced four separate regressions during review, each caught only by empirical testing, which is a fair signal about how much care this needs:

• Percent-encoded absolute paths. file:%2Fvar%2Fwh.db is absolute; SQLite decodes before opening. Treating it as relative produced a path that does not exist.
• file::memory: is SQLite's in-memory URI. Absolutizing it turned an in-memory database into a file on disk.
• Case sensitivity. SQLite recognises only a lowercase file:. A case-insensitive match rewrote FILE:warehouse.db, a literal filename, into a different path.
• Special characters in the base directory. A project path containing a literal %, ?, or # is URI syntax and needs encoding before being joined.

What a fix needs

1. Detect at runtime whether bun:sqlite on the current platform actually honours URI mode — e.g. probe new Database("file::memory:", { readwrite: true, create: false }) once, which succeeds only when URI parsing is active — and rewrite only when it does.
2. Decide relative-versus-absolute on the decoded path.
3. Decline the exact :memory: and decoded-colon-led forms.
4. Match the scheme case-sensitively.
5. Percent-encode the base directory before joining.
6. SQLite only. DuckDB reads file: as an extension scheme and errors with Extension "file.duckdb_extension" not found, so a rewrite there dresses up a path that never worked.
7. Cover every branch with a compiled-binary test on each shipped platform, with a decoy database planted so a wrong resolution reads plausible wrong data rather than nothing.

Severity

Low in practice. No evidence any user writes file: URIs into connections.json; the ordinary relative path form, which is what #1203 reported, is fixed on every platform by #1204. Filing so the hole is recorded with its evidence rather than forgotten.