Table of Contents

SqliteRateCache Class

Definition

Namespace
Bodu.Financial.ExchangeRates.Caching
Assembly
Bodu.Financial.ExchangeRates.Caching.Sqlite.dll
Package
Bodu.Financial.ExchangeRates.Caching.Sqlite 1.0.0
Source
SqliteRateCache.cs

An IRateCache that persists a single provider's rates and fetch-coverage windows in a SQLite database, expiring them through the same freshness mechanism as the in-memory and TOML caches.

public sealed class SqliteRateCache : IRateCache, IDisposable
Inheritance
SqliteRateCache
Implements
Inherited Members
Extension Methods

Examples

// A SQLite caching provider persisting to a file under the system temp path.
var options = new SqliteRateCacheOptions { Provider = "RBA", DatabaseFilePath = "/var/cache/rba.db" };
using var cache = new SqliteRateCache(options);
IDatedRateProvider cached = new CachingRateProvider(rba, cache, new CachingRateOptions());

Several providers can share one database file: the leading provider key column keeps each provider's series partitioned, and one cache covers all of that provider's currency pairs, so there is never a cache per pair.

var options = new CachingRateOptions { DefaultExpiry = TimeSpan.FromHours(24) };

// One shared .db file, one cache per provider; each cache covers all of that provider's pairs.
using var rbaCache = new SqliteRateCache("RBA", "/var/cache/fx.db");
using var ofxCache = new SqliteRateCache("OFX", "/var/cache/fx.db");

IDatedRateProvider rba = new CachingRateProvider(rbaSource, rbaCache, options);
IDatedRateProvider ofx = new CachingRateProvider(ofxSource, ofxCache, options);

Remarks

Rates and coverage live in two tables. The rates table is keyed by (provider, from_code, to_code, obs_date), one row per dated observation, written through an UPSERT so a re-stored date replaces the prior row; its nullable observed_at column carries the upstream fetch instant when the source supplied one. The coverage table records (provider, from_code, to_code, start_date, end_date, fetched_at), allowing multiple windows per pair so a sparse fetch history is preserved exactly. Decimal rates are stored as invariant strings and all dates and instants as invariant ISO text (a DateOnly as yyyy-MM-dd, a DateTimeOffset in round-trip "O" form) so the full precision and scale round-trips losslessly, mirroring the TOML cache's string-decimal choice.

Expiry is by caching duration rather than by storage: stale and semantically invalid rows are filtered on read and pruned on write, and stale coverage windows are pruned when coverage is recorded, so the database self-cleans over time. The freshness, validity, merge, and coverage rules are delegated to the shared RateCacheRules so this backend stays behaviourally identical to the in-memory, file, and distributed caches; this class contributes only its SQLite storage and locking. The two halves of a pair's state are written independently through Store(CurrencyPair, IReadOnlyList<CachedRate>, TimeSpan, DateTimeOffset) and RecordCoverage(CurrencyPair, DateOnly, DateOnly, TimeSpan, DateTimeOffset) - storing rates never drops recorded coverage, and recording coverage never drops cached rows - while StoreFetchedRange(CurrencyPair, IReadOnlyList<CachedRate>, DateOnly, DateOnly, TimeSpan, DateTimeOffset) writes both halves in one transaction.

The cache is a single-process best-effort store. Writes for the same pair are serialized under a per-pair lock and run in a transaction so concurrent same-pair writes cannot lose either half, matching the file cache's guarantee. As required by IRateCache, a storage failure surfaces as an empty read or a skipped write rather than an exception: SqliteException and IOException degrade gracefully, while argument validation still throws.

So that this graceful degradation is not silent, each swallowed storage failure is logged at Warning through the optional logger supplied at construction. The first failure is logged immediately and subsequent failures are rate-limited to at most one warning per minute, each carrying the count of failures suppressed since the previous warning, so a sustained outage is visible to operators without flooding the log. With no logger the degradation is unreported; set ThrowOnStorageFailure instead when a failure must surface as an exception.

A single keep-alive connection is held open for the instance lifetime so that a shared in-memory database ( Mode=Memory;Cache=Shared) survives between operations, which would otherwise be torn down when the last connection closes. Per-operation connections are still opened and closed normally and are pooled by Microsoft.Data.Sqlite. Dispose the cache to release the keep-alive connection.

Constructors

SqliteRateCache(SqliteRateCacheOptions, TimeProvider?, ILogger?)

Initializes a new instance of the SqliteRateCache class.

public SqliteRateCache(SqliteRateCacheOptions options, TimeProvider? timeProvider = null, ILogger? logger = null)

Parameters

options SqliteRateCacheOptions

The options carrying the bound provider and the database location.

timeProvider TimeProvider

The time source the degradation-warning cooldown is measured against, or null to use System.

logger ILogger

The logger that receives the rate-limited best-effort degradation warnings, or null to leave the degradation unreported.

Remarks

The schema is created if it does not already exist, and a pre-existing rates table is migrated to add the observed_at column when it is absent, in one transaction, when the instance is constructed. A failure to create or migrate the schema is swallowed - and logged at Warning through logger - so a transiently unwritable database surfaces later as empty reads and skipped writes rather than a construction-time exception, unless ValidateStorageOnStart or ThrowOnStorageFailure is set, in which case the failure propagates from the constructor.

Exceptions

ArgumentNullException

Thrown when options is null.

ArgumentException

Thrown when options fails validation.

SqliteRateCache(string, string)

Initializes a new instance of the SqliteRateCache class bound to a provider and a database file.

public SqliteRateCache(string provider, string databaseFilePath)

Parameters

provider string

The provider the cache stores rates for.

databaseFilePath string

The path to the SQLite database file used by the cache.

Exceptions

ArgumentNullException

Thrown when provider or databaseFilePath is null.

ArgumentException

Thrown when provider or databaseFilePath is empty or white space.

Properties

Provider

Gets the name of the provider this cache stores rates for.

public string Provider { get; }

Property Value

string

The provider identifier the cache is bound to.

Methods

Dispose()

Releases the keep-alive connection held for the instance lifetime and clears the connection pool, so every handle to the database is closed and the file is deletable immediately after disposal.

public void Dispose()

Remarks

Idempotent: a second or concurrent call is a safe no-op. The disposed flag is flipped with Exchange(ref int, int) so exactly one caller wins the transition and disposes the keep-alive connection, preventing a double dispose of the underlying handle. Clearing the pool is required because Microsoft.Data.Sqlite pools closed connections by default: without it, the pooled handles from per-operation connections (and the returned keep-alive) keep the database file open past disposal, which blocks deletion on Windows and leaves the write-ahead-log sidecar unpurged everywhere.

GetCoverage(CurrencyPair, TimeSpan, DateTimeOffset)

Returns the union of the still-fresh coverage windows recorded for the supplied pair, evaluated against asOf.

public DateRangeCoverage GetCoverage(CurrencyPair pair, TimeSpan duration, DateTimeOffset asOf)

Parameters

pair CurrencyPair

The currency pair.

duration TimeSpan

The duration a recorded coverage window remains fresh after it was fetched.

asOf DateTimeOffset

The instant against which coverage freshness is evaluated.

Returns

DateRangeCoverage

A DateRangeCoverage describing the days known to have been fetched and still fresh; empty when no fresh coverage exists.

Remarks

Coverage answers which days were actually fetched, not merely which days have a cached rate. A range lookup should be served from the cache only when this coverage Contains(DateOnly, DateOnly) the whole requested window, so an interior day that was never fetched forces a refetch rather than being served from a sparse set of rows.

GetRates(CurrencyPair, TimeSpan, DateTimeOffset)

Returns the fresh cached rates for the supplied pair, evaluated against asOf.

public IReadOnlyList<CachedRate> GetRates(CurrencyPair pair, TimeSpan duration, DateTimeOffset asOf)

Parameters

pair CurrencyPair

The currency pair.

duration TimeSpan

The duration a cached rate remains fresh after it was cached.

asOf DateTimeOffset

The instant against which freshness is evaluated.

Returns

IReadOnlyList<CachedRate>

The fresh cached rates ordered by date, or an empty list when none are fresh or available.

RecordCoverage(CurrencyPair, DateOnly, DateOnly, TimeSpan, DateTimeOffset)

Records that the inclusive range start..end was fetched for the supplied pair, stamping the window at asOf and pruning windows that are no longer fresh under duration.

public void RecordCoverage(CurrencyPair pair, DateOnly start, DateOnly end, TimeSpan duration, DateTimeOffset asOf)

Parameters

pair CurrencyPair

The currency pair.

start DateOnly

The inclusive first date of the fetched range.

end DateOnly

The inclusive last date of the fetched range.

duration TimeSpan

The duration a recorded coverage window remains fresh after it was fetched.

asOf DateTimeOffset

The instant the fetched window is stamped with and against which stale windows are pruned.

Remarks

This is a low-level building block that records the coverage half only, with no rows. It exists for the cache's own composition and is not the path a provider fetch should use: recording coverage here and writing rows through a separate Store(CurrencyPair, IReadOnlyList<CachedRate>, TimeSpan, DateTimeOffset) risks persisting coverage a backend later serves as a false hit. A range fetch must instead use StoreFetchedRange(CurrencyPair, IReadOnlyList<CachedRate>, DateOnly, DateOnly, TimeSpan, DateTimeOffset), which writes both halves atomically.

Exceptions

ArgumentOutOfRangeException

Thrown when start is later than end.

Store(CurrencyPair, IReadOnlyList<CachedRate>, TimeSpan, DateTimeOffset)

Stores rates for the supplied pair, merging with any existing entry so the most recently cached rate wins per date, and pruning rows that are no longer fresh under duration.

public void Store(CurrencyPair pair, IReadOnlyList<CachedRate> rates, TimeSpan duration, DateTimeOffset asOf)

Parameters

pair CurrencyPair

The currency pair.

rates IReadOnlyList<CachedRate>

The rates to store.

duration TimeSpan

The duration a cached rate remains fresh after it was cached.

asOf DateTimeOffset

The instant against which stale rows are pruned.

Remarks

This stores rate rows only and records no coverage window, so it is the path a single-date miss caches its resolved row through. Because no coverage is recorded, a later range lookup that spans those dates still refetches rather than serving from these rows - by design, since only a range fetch (through StoreFetchedRange(CurrencyPair, IReadOnlyList<CachedRate>, DateOnly, DateOnly, TimeSpan, DateTimeOffset)) establishes the contiguous coverage a range serve requires.

StoreFetchedRange(CurrencyPair, IReadOnlyList<CachedRate>, DateOnly, DateOnly, TimeSpan, DateTimeOffset)

Atomically merges rows into the pair's cached rows and records the inclusive range start..end as covered, persisting both halves together or neither.

public RateCacheWriteStatus StoreFetchedRange(CurrencyPair pair, IReadOnlyList<CachedRate> rows, DateOnly start, DateOnly end, TimeSpan duration, DateTimeOffset asOf)

Parameters

pair CurrencyPair

The currency pair.

rows IReadOnlyList<CachedRate>

The rows fetched for the range, which may be empty when the fetch returned no observation.

start DateOnly

The inclusive first date of the fetched range.

end DateOnly

The inclusive last date of the fetched range.

duration TimeSpan

The duration a cached row and a recorded coverage window remain fresh after they were cached or fetched.

asOf DateTimeOffset

The instant newly cached rows and the fetched window are stamped with and against which stale rows and windows are pruned.

Returns

RateCacheWriteStatus

Stored when both halves were persisted; Failed when a storage error was swallowed and nothing was persisted; Skipped for a cache that intentionally stores nothing.

Remarks

The coverage window is recorded even when rows is empty: a successful fetch that returned no observation (a weekend, a holiday, a true gap) must still mark the window covered so a later lookup of the same window is served rather than refetched.

The merge follows the same most-recent-per-date rule as Store(CurrencyPair, IReadOnlyList<CachedRate>, TimeSpan, DateTimeOffset) and the same window pruning as RecordCoverage(CurrencyPair, DateOnly, DateOnly, TimeSpan, DateTimeOffset). The write is atomic per pair - under the per-pair lock for the in-memory and file caches, in one transaction for SQLite, and as one read-modify-write of the per-pair blob for a distributed cache - so a reader never observes coverage without its rows. As with the other write paths, a swallowed storage error degrades to Failed rather than throwing; argument validation still throws.

Exceptions

ArgumentNullException

Thrown when rows is null.

ArgumentOutOfRangeException

Thrown when start is later than end.

Applies to

ProductVersions
.NET8, 10