Skip to content

sqlite.md

Latest commit

 

History

History
131 lines (108 loc) · 3.87 KB

sqlite.md

File metadata and controls

131 lines (108 loc) · 3.87 KB
Raw

SQLite provider API

Provider catalog · Tabular schema mapping

The sqlite provider reflects one local SQLite table or view and queries historical OHLCV through Python's built-in SQLite driver. It requires no optional package.

Construct the provider

from pineforge_data import SqliteBarProvider

provider = SqliteBarProvider(
    "./warehouse.sqlite3",
    table="price candles",
    venue="warehouse",
    mapping={
        "timestamp": "epoch seconds",
        "open": "first px",
        "high": "top px",
        "low": "bottom px",
        "close": "last px",
        "volume": "traded qty",
        "symbol": "security code",
        "timeframe": "bar interval",
    },
    timestamp_unit="seconds",
)

Table and column names may contain spaces or reserved words. The provider first checks the table or view and mapping against sqlite_schema and PRAGMA table_info, then quotes the reflected identifiers.

Inspect the table or view

schema = await provider.inspect_schema()
for column in schema.columns:
    print(column.name, column.data_type, column.nullable)

The provider opens a fresh read-only SQLite connection for each operation. It never creates tables, runs migrations, or changes source data.

Resolve and fetch bars

from pineforge_data import BarRequest


async def load_sqlite():
    listing = await provider.resolve_market("AAPL")
    return await provider.fetch_bars(
        BarRequest(
            listing.instrument,
            timeframe="1m",
            start_ms=1_751_328_000_000,
            end_ms=1_751_414_400_000,
        )
    )

Mapped symbol and timeframe values are bound parameters. Numeric timestamp bounds are translated into the configured source unit and pushed into the SQL query. The common normalization layer checks the interval again after reading each row.

ISO-8601 timestamps are filtered after conversion because arbitrary stored text formats do not always preserve chronological SQL ordering. For large text-time tables, expose a view with a consistently sortable numeric epoch column.

Constructor reference

Argument Default Purpose
path required local SQLite database path
table required exact table or view name
venue local source identity attached to instruments and provenance
mapping inferred BarColumnMapping or partial overrides
timestamp_unit milliseconds numeric timestamp unit or iso8601
timestamp_timezone UTC IANA zone for naive date/time values
instrument None optional fixed instrument template
timeframe None optional fixed timeframe assertion

Harness configuration

{
  "path": "/data/warehouse.sqlite3",
  "table": "price candles",
  "timestamp_unit": "seconds",
  "columns": {
    "timestamp": "epoch seconds",
    "open": "first px",
    "high": "top px",
    "low": "bottom px",
    "close": "last px",
    "volume": "traded qty",
    "symbol": "security code",
    "timeframe": "bar interval"
  }
}
pineforge-backtest \
  --pine strategy.pine \
  --provider sqlite \
  --provider-config sqlite.json \
  --venue warehouse \
  --symbol AAPL \
  --timeframe 1m \
  --start 2025-07-01T00:00:00Z \
  --end 2025-07-02T00:00:00Z

Registry configuration accepts path, table, and the shared tabular configuration keys. Unknown keys fail early.

Errors and limitations

  • A missing database path raises FileNotFoundError.
  • A missing table/view or a source with no columns raises TabularDataError.
  • Missing or ambiguous mapped fields raise SchemaMappingError.
  • SQLite connections use URI mode=ro; the filesystem must permit reads.
  • Complex joins, expressions, or cleanup should be exposed as a SQLite view.
  • The provider uses synchronous SQLite calls moved off the asyncio event loop.