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.

Versioning and migrations

The database schema is versioned. The current schema version is 2.57.0; it is independent of the application version and is only incremented 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.

A database also records in its metadata (key min_reader_version) the minimum schema version a blunderDB must read to open it. From this version on, blunderDB knows this key: if it reads a schema older than that minimum, it refuses to open the database, in the graphical interface, on the command line, with serve as with migrate, and shows both versions: the database is left untouched. Earlier versions ignore this key. A database read without being opened, the source of an import or a file compared with the open one, is refused in the same way.

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).

  • Change journal: change_log dates the last write of each object, and position_part_fact that of a position’s drawing and variant link, erasures included. Two libraries use them to synchronise.

  • Marks on a position: position_drawing holds the drawing and position_derivation the link from a variant to the position it comes from; the column position.public_id holds a label chosen by the caller.

  • 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).

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 stored in a compressed binary format (zstd with a shared dictionary; older analyses, in JSON, remain readable and are converted by vacuum or reencode), which greatly reduces the file size.

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