10. Annex: Database Schema

Important

Always back up your .db file before performing database migrations.

10.1. Version 1.0.0

Version 1.0.0 of the database contains the following tables:

position: Stores the positions with the columns id (primary key) and state (state of the position in JSON format).
analysis: Stores the analyses of the positions with the columns id (primary key), position_id (foreign key referencing position), and data (analysis data in JSON format).
comment: Stores the comments associated with the positions with the columns id (primary key), position_id (foreign key referencing position), and text (comment text).
metadata: Stores the metadata of the database with the columns key (primary key) and value (value associated with the key).

Version 1.1.0 of the database adds the following table:

command_history: Stores the command history with the columns id (primary key), command (text of the command), and timestamp (date and time of command execution).

The other tables remain unchanged from version 1.0.0.

To migrate the database from version 1.0.0 to version 1.1.0, execute the command migrate_from_1_0_to_1_1 in blunderDB.

Version 1.2.0 of the database adds the following table:

filter_library: Stores search filters with the columns id (primary key), name (filter name), command (command associated with the filter), and edit_position (position edited when saving the filter).

The other tables remain unchanged from version 1.1.0.

To migrate the database from version 1.1.0 to version 1.2.0, execute the command migrate_from_1_1_to_1_2 in blunderDB.

Version 1.3.0 of the database adds the following table:

search_history: Stores position search history with the columns id (primary key), command (search command text), position (position state at the time of search), and timestamp (date and time of the search).

The other tables remain unchanged from version 1.2.0.

To migrate the database from version 1.2.0 to version 1.3.0, execute the command migrate_from_1_2_to_1_3 in blunderDB.

Version 1.4.0 of the database adds the following tables for match management:

match: Stores imported matches with the columns id (primary key), player1_name, player2_name, event, location, round, match_length, match_date, import_date, file_path, game_count, and match_hash (hash for duplicate detection).
game: Stores the games of a match with the columns id (primary key), match_id (foreign key referencing match), game_number, initial_score_1, initial_score_2, winner, points_won, and move_count.
move: Stores the moves of a game with the columns id (primary key), game_id (foreign key referencing game), move_number, move_type, position_id (foreign key referencing position), player, dice_1, dice_2, checker_move, and cube_action.
move_analysis: Stores the analysis of each move with the columns id (primary key), move_id (foreign key referencing move), analysis_type, depth, equity, equity_error, win_rate, gammon_rate, backgammon_rate, opponent_win_rate, opponent_gammon_rate, and opponent_backgammon_rate.

Migration from 1.3.0 to 1.4.0 is automatic when opening the database.

Version 1.5.0 of the database adds the following tables for collection management:

collection: Stores position collections with the columns id (primary key), name, description, sort_order, created_at, and updated_at.
collection_position: Junction table that associates positions with collections with the columns id (primary key), collection_id (foreign key referencing collection), position_id (foreign key referencing position), sort_order, and added_at. The pair (collection_id, position_id) is unique.

Migration from 1.4.0 to 1.5.0 is automatic when opening the database.

Version 1.6.0 of the database adds the following table for tournament management:

tournament: Stores tournaments with the columns id (primary key), name, date, location, sort_order, created_at, and updated_at.
Addition of the tournament_id column (foreign key referencing tournament) in the match table to assign a match to a tournament.

Migration from 1.5.0 to 1.6.0 is automatic when opening the database.

Version 1.7.0 of the database adds the following column:

Addition of the last_visited_position column in the match table to remember the last visited position in each match.

Migration from 1.6.0 to 1.7.0 is automatic when opening the database.

Note

Since version 0.10.0, all database migrations are performed automatically when opening a database file.