rippled/docs/skills/sql.md

SQL Database

SQLite via SOCI for ledger/transaction history. Only SQLite is supported; any non-sqlite backend value in config throws at parse time (detail::getSociInit in SociDB.cpp).

Key Invariants

• Two main databases: lgrdb_ (ledger) and txdb_ (transactions, optional via useTxTables config)
• Transaction tables are optional; disabling them disables transaction history and account_tx queries
• WAL checkpointing offloads to JobQueue (jtWAL); at most one checkpoint job in flight per DatabaseCon (guarded by running_ mutex in WALCheckpointer)
• Database init failure is fatal (throws exception, prevents construction)
• Free disk space < 512 MB triggers fatal error on write operations
• File extension inconsistency: validators and peerfinder use .sqlite; all other DBs use .db. Historical artifact enforced in detail::getSociInit

Schema

• Ledgers: seq, hash, parent hash, total coins, close time, etc. Indexed by LedgerSeq
• Transactions: TransID, TransType, FromAcct, FromSeq, LedgerSeq, Status, RawTxn, TxnMeta. Indexed by LedgerSeq
• AccountTransactions: TransID, Account, LedgerSeq, TxnSeq. Triple-indexed for account_tx queries
• Secondary DBs: Wallet (node identity, manifests), PeerFinder (bootstrap cache), State (deletion tracking)
• Schema defined in src/xrpld/app/main/DBInit.h
• No schema migration system; CREATE TABLE IF NOT EXISTS silently preserves old schemas with missing columns. Exception: PeerFinder has schema versioning via a SchemaVersion table.

Configuration

Option	Section	Values	Default
backend	[sqdb] / [relational_db]	sqlite only	sqlite
page_size	[sqlite]	512–65536, power of 2	4096
safety_level	[sqlite]	high, medium, low	high
journal_size_limit	[sqlite]	integer >= 0	1582080

safety_level: low changes journal_mode and synchronous settings — can lose data on crash.

WAL Checkpointing Architecture

The checkpointer subsystem is the trickiest part of this module. SQLite's WAL hook is a C callback registered on the native sqlite3* connection, but the work runs on a JobQueue thread that may still be executing when the owning DatabaseCon is destroyed.

Two-file split

• SociDB.cpp: WALCheckpointer class (anonymous namespace) — installs the hook, implements schedule() and checkpoint(), holds the weak_ptr<soci::session>.
• DatabaseCon.cpp: CheckpointersCollection class — process-wide singleton registry (checkpointers, namespace-scope variable) mapping monotonically-incrementing integer IDs to shared_ptr<Checkpointer>; exposes create, fromId, erase. All DatabaseCon instances share this one registry.

DatabaseCon.cpp has no direct SQLite dependency; it only manages the Checkpointer abstract interface.

ID-based hook indirection

• WALCheckpointer is registered with sqlite3_wal_hook using its std::uintptr_t id_ cast to void*, not a raw this pointer.
• The C hook calls checkpointerFromId() → CheckpointersCollection::fromId() (process-wide singleton). If lookup returns null (connection torn down), the hook deregisters itself via sqlite3_wal_hook(conn, nullptr, nullptr).
• Prevents use-after-free: the hook may fire on a writer thread after DatabaseCon begins destruction.

Session ownership split

• DatabaseCon holds std::shared_ptr<soci::session>; WALCheckpointer holds only std::weak_ptr<soci::session>.
• If the checkpointer held a shared_ptr, an in-flight job would keep the WAL lock alive, blocking a freshly-opened replacement DatabaseCon on the same file.
• WALCheckpointer::checkpoint() calls session_.lock() and bails silently if expired.

Destructor sequence (DatabaseCon::~DatabaseCon)

Order matters:

1. checkpointers.erase(checkpointer_->id()) — future hook invocations now no-op and self-deregister.
2. Take weak_ptr<Checkpointer> wk(checkpointer_), then checkpointer_.reset().
3. Busy-poll wk.use_count() != 0 with 100 ms sleeps until all in-flight job lambdas release their shared_ptr<Checkpointer>.

The 100 ms poll is deliberate (rare event; simpler than a condvar). Without this wait, reopening the same SQLite file immediately after destruction can fail because the old checkpoint job may still hold the WAL lock.

setupCheckpointing() — deferred wiring

• Separated from constructors so checkpointing is opt-in.
• Constructors accepting CheckpointerSetup open the DB first, then call setupCheckpointing(JobQueue*, ServiceRegistry&).
• Null JobQueue* throws std::logic_error (programming error, not runtime).
• The checkpointer must be inserted into CheckpointersCollection before setupCheckpointing returns, because the WAL hook is armed inside the WALCheckpointer constructor and writes can fire it immediately.

Checkpoint job behavior

• Triggered by sqlite3_wal_hook after every WAL write; static checkpointPageCount = 1000 mirrors SQLite's auto-checkpoint threshold.
• schedule() uses running_ bool under mutex to enforce single in-flight job; if JobQueue rejects the job, running_ is reset.
• Enqueued lambda captures std::weak_ptr<Checkpointer>; destroyed DatabaseCon causes the job to exit without touching the session.
• checkpoint() calls sqlite3_wal_checkpoint_v2 with SQLITE_CHECKPOINT_PASSIVE. SQLITE_LOCKED logged at trace (expected under reader contention); other errors logged as warnings. running_ reset under mutex after each attempt.
• Net effect: routes checkpoint work off the writer thread onto jtWAL. Without this, SQLite does it synchronously on whichever thread crosses the page threshold.

SOCI Adapter Notes (SociDB.cpp)

• DBConfig is two-phase: parse params, open later. detail::getSociInit and detail::getSociSqliteInit resolve backend + path; the .sqlite vs .db extension fork lives in getSociInit. getSociSqliteInit throws std::runtime_error if the database name is empty.
• Two free-function open() overloads: config-based (delegates through DBConfig) and explicit-string (enforces same "sqlite only" constraint). Both paths call s.open(soci::sqlite3, connectionString).
• getConnection(session&) recovers the raw sqlite3* via dynamic_cast<soci::sqlite3_session_backend*> — the only intentional break in the SOCI abstraction. Throws std::logic_error if the cast fails. Required for WAL hooks and sqlite3_db_status.
• getKBUsedAll() → sqlite3_memory_used() (process-global). getKBUsedDB() → SQLITE_DBSTATUS_CACHE_USED (per-connection).
• Four convert() overloads bridge soci::blob ↔ std::vector<uint8_t> / std::string. Empty blobs require blob.trim(0) rather than blob.write(nullptr, 0).
• SociDB.cpp opens with #pragma clang diagnostic ignored "-Wdeprecated" because SOCI headers use deprecated constructs; scoped to this TU only.

Common Bug Patterns

• No schema migration system; CREATE TABLE IF NOT EXISTS silently preserves old schemas with missing columns. New columns on existing deployments require manual ALTER TABLE or explicit documentation that the column may be absent.
• page_size must be power of 2 between 512–65536; invalid values cause init failure.
• Online deletion coordinates between NodeStore rotation and SQL table pruning; race conditions here lose history.
• Empty database name passed to detail::getSociSqliteInit throws — no silent fallback.
• A WALCheckpointer registered with sqlite3_wal_hook can outlive its DatabaseCon if a checkpoint job is in flight; teardown must wait for the job to drain (see Destructor sequence above).
• Opening a new DatabaseCon to the same file immediately after destroying the old one can fail if the destructor busy-poll is skipped or shortened — the old checkpoint job may still hold the WAL lock.

Key Patterns

Schema Evolution Caveat

// No migration system — old databases keep old schemas.
// CREATE TABLE IF NOT EXISTS silently skips if table exists with old columns.
// New columns require manual ALTER TABLE or must be treated as optional/absent.
// PeerFinder is the exception: it has a SchemaVersion table.

Disk Space Guard

// Required on all write paths.
if (freeDiskSpace < minDiskFree)
    Throw<std::runtime_error>("Not enough disk space for database write");

WAL Hook Cookie

// Always pass an integer ID, never `this`.
// DatabaseCon may be destroyed while a hook invocation is mid-flight on a writer thread.
sqlite3_wal_hook(conn, &walHookCallback,
                 reinterpret_cast<void*>(checkpointer->id()));

Penetrating the SOCI Abstraction

// getConnection() is the only intentional SOCI abstraction break.
// Required for sqlite3_wal_hook and sqlite3_db_status APIs.
auto* be = dynamic_cast<soci::sqlite3_session_backend*>(s.get_backend());
if (!be || !be->conn_) throw std::logic_error("Not a sqlite3 session");
sqlite3* conn = be->conn_;

Key Files

File	Purpose
src/libxrpl/rdb/SociDB.cpp	SOCI/SQLite adapter, WALCheckpointer, blob conversion, memory stats
src/libxrpl/rdb/DatabaseCon.cpp	Connection lifecycle, CheckpointersCollection, destructor drain
src/xrpld/app/main/DBInit.h	Schema definitions (CREATE TABLE statements)
src/xrpld/app/rdb/backend/detail/SQLiteDatabase.cpp	Main SQLiteDatabase implementation
src/xrpld/app/rdb/backend/detail/Node.cpp	Ledger/tx read-write operations
src/xrpld/app/rdb/detail/State.cpp	Deletion state tracking
src/xrpld/core/detail/DatabaseCon.cpp	Legacy reference; lifecycle now in libxrpl