triangles_v5/WALLET-SQLITE-MIGRATION.md

# Wallet storage: Berkeley DB → SQLite

Goal: retire Berkeley DB as the wallet store and make **SQLite the default**
wallet backend, with a transparent, non-destructive migration of existing
`wallet.dat` files. This removes the single ugliest build dependency (BDB 5.3
with C++ bindings, hand-built on RHEL/MSYS2) and gives the wallet a modern,
maintainable, single-file store — the kind exchanges expect.

No consensus or wire behavior changes. The on-disk *record encoding* is
unchanged: keys and values are the exact `SER_DISK / CLIENT_VERSION` bytes
`CWalletDB` already produces, just stored as `(key BLOB, value BLOB)` rows in
SQLite instead of Berkeley B-tree entries. That byte-for-byte identity is what
makes migration a verbatim copy.

## Delivered in this pass

New, self-contained modules (do not disturb the working Berkeley path):

| File | Purpose |
|------|---------|
| `src/walletdb-base.h` | Backend-agnostic seam: `WalletDatabase`, `WalletBatch` (raw byte Read/Write/Erase/Has + cursor + txn), `WalletCursor`; `ResolveWalletDbKind()` / `MakeWalletDatabase()` declarations. |
| `src/walletdb-sqlite.h/.cpp` | `SQLiteDatabase` / `SQLiteBatch` — single `main(key BLOB PRIMARY KEY, value BLOB)` table, `synchronous=FULL`, prepared statements, transactions, cursor, online-backup, `integrity_check`. App-id/user-version stamping to reject foreign DBs. |
| `src/walletmigrate.h/.cpp` | `MaybeMigrateBerkeleyWalletToSQLite()` — detects a Berkeley `wallet.dat`, copies every record verbatim into a temp SQLite file, verifies the row count, backs up the original to `wallet.dat.bdb.bak`, then swaps SQLite into place. Idempotent and non-destructive. |
| `src/walletdb-factory.cpp` | `ResolveWalletDbKind()` (default **sqlite**, `-walletdb=bdb` fallback) and `MakeWalletDatabase()` (SQLite implemented). |
| `src/walletdb-batch.h` | `CWalletBatchTyped` — typed Read/Write/Erase/Exists + cursor over `WalletBatch`, byte-identical to the old `CDB` templates. The drop-in base for `CWalletDB`. |

Build wiring:
- `find_package(SQLite3 REQUIRED)` in the top-level `CMakeLists.txt`.
- `SQLite::SQLite3` linked into `triangles_common`; the new sources added to `CORE_SOURCES`.

## Remaining integration (compile-in-the-loop)

The new modules are complete but `CWalletDB` is not yet routed through the seam
— it still inherits Berkeley `CDB`. This is the mechanical-but-careful step that
needs a compiler in the loop. **It must be done and landed as one unit** (it
touches `walletdb.h`, `walletdb.cpp`, `wallet.cpp`, `db.cpp`, and `init.cpp`):
re-basing ~800 lines of funds-critical code is exactly the kind of change that
should be compiled and run against a real `wallet.dat` rather than committed
blind.

1. **Typed wrappers over the batch — DONE.** `src/walletdb-batch.h`
   (`CWalletBatchTyped`) provides `Read/Write/Erase/Exists` + cursor over a
   `WalletBatch`, byte-identical to `CDB`'s templates. `CWalletDB` derives from
   it instead of `CDB`.
2. **Re-base `CWalletDB`.** Hold a `std::unique_ptr<WalletDatabase>` +
   `WalletBatch` obtained from `MakeWalletDatabase("wallet.dat", err)` instead of
   deriving from `CDB`. Route `TxnBegin/Commit/Abort` to the batch.
3. **Cursors.** Replace `GetAtCursor` / `GetTxnCursor` / `ReadAtCursor`
   (Berkeley `Dbc*`, `DB_NEXT`) in `walletdb.cpp` (`LoadWallet`,
   `ReorderTransactions`) with `WalletBatch::GetNewCursor()` + `WalletCursor::Next()`.
4. **Berkeley-specific call sites.**
   - `BackupWallet()` / `AutoBackupWallet()` → `WalletDatabase::Backup()`.
   - `CDB::Rewrite()` (used by `CWallet::EncryptWallet`) → `WalletDatabase::Rewrite()`
     (VACUUM). Unencrypted-key cleanup already happens via explicit `Erase`.
   - `bitdb.Flush()` / env shutdown in `init.cpp` → `WalletDatabase::Flush()/Close()`
     (no-op for SQLite).
5. **Berkeley behind the same seam (optional but recommended).** Add a thin
   `BerkeleyDatabase`/`BerkeleyBatch` adapter wrapping the existing `CDBEnv`/`CDB`
   so `-walletdb=bdb` routes through `MakeWalletDatabase` too, instead of the
   legacy path. Keeps one code path for one release, then delete BDB entirely.
6. **Run the migration on startup.** In `init.cpp`, before the wallet is loaded
   and when the backend is SQLite, call
   `MaybeMigrateBerkeleyWalletToSQLite(GetDataDir()/strWalletFileName, err)`.

## Gating

```
trianglesd                 # SQLite (default)
trianglesd -walletdb=bdb   # Berkeley fallback (retained for one release)
```

## Validation checklist (must pass before release)

Cannot be verified without a build + a real wallet. Run on a node:

1. **Build** with `-DBUILD_TESTS=ON`; confirm SQLite is found and linked.
2. **Fresh wallet**: start with no wallet → a SQLite `wallet.dat` is created;
   `getnewaddress`, `getinfo` work; restart preserves keys/balance.
3. **Migration**: copy a real Berkeley `wallet.dat` into the datadir, start the
   node. Confirm: `wallet.dat.bdb.bak` is created, `wallet.dat` is now SQLite
   (`sqlite3 wallet.dat "PRAGMA integrity_check;"` → `ok`), and
   `listaddressgroupings` / `getbalance` / `dumpwallet` match a `-walletdb=bdb`
   run against the `.bdb.bak` original.
4. **Key parity**: `dumpwallet` before (bdb) and after (sqlite); diff must be
   empty (same keys, labels, metadata, HD seed).
5. **Encryption**: `encryptwallet`, restart, `walletpassphrase`, sign/spend.
6. **Backup/restore**: `backupwallet`, restore into a fresh datadir, verify
   balance and spend.
7. **Send/receive + staking** over a few blocks; confirm new keys/txns persist
   across restart.
8. **Crash safety**: kill -9 mid-write; restart; `integrity_check` ok, no loss.

## Follow-ups

- Add `test_wallet_sqlite` unit tests (round-trip, migration parity, cursor).
- Once SQLite is validated for a release, remove `-walletdb=bdb`, delete
  `db.cpp`/`walletdb`'s Berkeley code, and drop the `BerkeleyDB` CMake
  dependency — completing the retirement.