GNU Backgammon provides relational database support. Besides mysql and postgresql the standard database is sqlite. After the installation of gnubg you’ll find the database file in your home directory (~/.gnubg/gnubg.db on Linux and MacOS). You find the menu entry for using the database in "Analyze" – "Relational Database".
Besides getting information by the GUI of gnubg you can also put queries directly into the database. I’ll describe some important stuff for terminal usage here. If you work on MSWindows systems either get a sqlite browser or the Firefox Addon "Sqlite Manager."
In a terminal you start sqlite with:
#: sqlite .gnubg/gnubg.db (or wherever your database is located sqlite>Loading resources from /Users/ace/.sqliterc SQLite version 3.4.0 Enter ".help" for instructions sqlite>
Important sqlite commands:
| sqlite>.help | = | show help |
| sqlite>.tables | = | show tables |
| sqlite>.headers ON|OFF | = | set table headers on|off |
| sqlite>.mode | = | set output mode |
| sqlite>.exit | = | exit program |
Gnubg stores all data into five tables (actually there is a 6th table called "control", but forget about that for now):
- game
- gamestat
- matchstat
- player
- session
sqlite>.mode line sqlite> select * from player limit 1; player_id = 73 name = cdforex notes = sqlite> select * from game limit 1; game_id = 1 session_id = 1 player_id0 = 1 player_id1 = 2 score_0 = 0 score_1 = 0 result = 1 added = 02:00:22 game_number = 1 crawford = 0 sqlite> select * from session limit 1; session_id = 80 checksum = 112f66856248c6b6a6b0f211071b5d3d player_id0 = 73 player_id1 = 74 result = 0 length = 1 added = 2008-08-04 05:50:07 rating0 = NULL rating1 = NULL event = NULL round = NULL place = NULL annotator = NULL comment = NULL date = 2008-04-01
".mode" and "limit 1" are only set for displaying it here on the webpage more conveniently. The other three tables look similar but with much more rows:
game: game_id session_id player_id0 player_id1 score_0 score_1 result added game_number crawford
gamestat: gamestat_id game_id player_id total_moves unforced_moves unmarked_moves good_moves doubtful_moves bad_moves very_bad_moves chequer_error_total_normalised chequer_error_total chequer_error_per_move_normalised chequer_error_per_move chequer_rating very_lucky_rolls lucky_rolls unmarked_rolls unlucky_rolls very_unlucky_rolls luck_total_normalised luck_total luck_per_move_normalised luck_per_move luck_rating total_cube_decisions close_cube_decisions doubles takes passes missed_doubles_below_cp missed_doubles_above_cp wrong_doubles_below_dp wrong_doubles_above_tg wrong_takes wrong_passes error_missed_doubles_below_cp_normalised error_missed_doubles_above_cp_normalised error_wrong_doubles_below_dp_normalised error_wrong_doubles_above_tg_normalised error_wrong_takes_normalised error_wrong_passes_normalised error_missed_doubles_below_cp error_missed_doubles_above_cp error_wrong_doubles_below_dp error_wrong_doubles_above_tg error_wrong_takes error_wrong_passes cube_error_total_normalised cube_error_total cube_error_per_move_normalised cube_error_per_move cube_rating overall_error_total_normalised overall_error_total overall_error_per_move_normalised overall_error_per_move overall_rating actual_result luck_adjusted_result snowie_moves snowie_error_rate_per_move luck_based_fibs_rating_diff error_based_fibs_rating chequer_rating_loss cube_rating_loss actual_advantage actual_advantage_ci luck_adjusted_advantage luck_adjusted_advantage_ci time_penalties time_penalty_loss_normalised time_penalty_loss
matchstat: matchstat_id session_id player_id total_moves unforced_moves unmarked_moves good_moves doubtful_moves bad_moves very_bad_moves chequer_error_total_normalised chequer_error_total chequer_error_per_move_normalised chequer_error_per_move chequer_rating very_lucky_rolls lucky_rolls unmarked_rolls unlucky_rolls very_unlucky_rolls luck_total_normalised luck_total luck_per_move_normalised luck_per_move luck_rating total_cube_decisions close_cube_decisions doubles takes passes missed_doubles_below_cp missed_doubles_above_cp wrong_doubles_below_dp wrong_doubles_above_tg wrong_takes wrong_passes error_missed_doubles_below_cp_normalised error_missed_doubles_above_cp_normalised error_wrong_doubles_below_dp_normalised error_wrong_doubles_above_tg_normalised error_wrong_takes_normalised error_wrong_passes_normalised error_missed_doubles_below_cp error_missed_doubles_above_cp error_wrong_doubles_below_dp error_wrong_doubles_above_tg error_wrong_takes error_wrong_passes cube_error_total_normalised cube_error_total cube_error_per_move_normalised cube_error_per_move cube_rating overall_error_total_normalised overall_error_total overall_error_per_move_normalised overall_error_per_move overall_rating actual_result luck_adjusted_result snowie_moves snowie_error_rate_per_move luck_based_fibs_rating_diff error_based_fibs_rating chequer_rating_loss cube_rating_loss actual_advantage actual_advantage_ci luck_adjusted_advantage luck_adjusted_advantage_ci time_penalties time_penalty_loss_normalised time_penalty_loss
A simple query could now be:
sqlite> select * from player where name = "ace"; player_id name notes ---------- ---------- ---------- 12 ace
And now you have one of the most important information about a player, his "player_id" which is necessary for further queries. It’s also possible to deliver a search string:
sqlite> select * from player where name like "%ace%"; player_id name notes ---------- ---------- ---------- 12 ace 26 Acepoint
As you can see the search is not case sensitive.
