12. Annex: Database Schema

A blunderDB database is a plain SQLite file (extension .db). Without blunderDB, it can therefore be opened and inspected with any SQLite editor or file browser.

12.1. Versioning and migrations

The database schema is versioned. The current schema version is 2.14.0; it is independent of the application version and is incremented only when the internal structure changes. The schema version of an open database is shown in the Metadata panel (meta command).

Important

Always back up your .db file before opening a database created with an earlier version of blunderDB.

Note

Since version 0.10.0, all schema migrations are performed automatically when a database is opened. No manual migration command is needed. The migration is done in place: a database migrated to a recent schema can no longer be opened by older versions of blunderDB, hence the importance of backing it up first.

12.2. Main tables

The current schema is built around the following tables:

  • Positions and analyses: position (the positions, deduplicated), analysis (the associated analysis data) and comment (the comments).

  • Matches: match, game, move and move_analysis store the imported matches, their games, their moves and the analysis of each move.

  • Collections: collection and collection_position (link table) group together manually chosen positions.

  • Tournaments: tournament groups matches into tournaments.

  • Spaced repetition (Anki): anki_deck, anki_card and anki_review_log manage the decks, the cards and the review log (FSRS algorithm).

  • History and miscellaneous: command_history and search_history keep the history of commands and searches, filter_library the filter library, and metadata the database metadata (including the schema version).

12.3. Design principles

From schema 2.0.0 onwards, several design choices speed up searches and reduce the file size:

  • Position deduplication: each position carries a Zobrist hash and a unique index, so that the same position encountered across several imports is stored only once.

  • Denormalised filter columns: frequently searched criteria (decision type, dice, race difference, checkers borne off, back checkers, checker-play or cube error, chances of winning, etc.) are precomputed into dedicated columns for fast filtering.

  • Bitboard prefilter: occupancy and point-mask columns enable a very fast integer prefilter when searching for structure patterns.

  • Compact storage: positions are encoded compactly and analysis data is compressed (zlib), which greatly reduces the file size.

  • WAL journaling: WAL mode and tuned PRAGMAs improve read and write performance.