1-- 2-- gnubg.sql 3-- 4-- by Joern Thyssen <jth@gnubg.org>, 2004. 5-- 6-- 7-- This program is free software: you can redistribute it and/or modify 8-- it under the terms of the GNU General Public License as published by 9-- the Free Software Foundation, either version 3 of the License, or 10-- (at your option) any later version. 11-- 12-- This program is distributed in the hope that it will be useful, 13-- but WITHOUT ANY WARRANTY; without even the implied warranty of 14-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 15-- GNU General Public License for more details. 16-- 17-- You should have received a copy of the GNU General Public License 18-- along with this program. If not, see <http://www.gnu.org/licenses/>. 19-- 20-- $Id: gnubg.sql,v 1.14 2013/08/21 03:45:22 mdpetch Exp $ 21-- 22 23-- Table: control 24-- Holds the most current value of xxx_id for every table. 25 26CREATE TABLE control ( 27 tablename CHAR(80) NOT NULL 28 ,next_id INTEGER NOT NULL 29 ,PRIMARY KEY (tablename) 30); 31 32CREATE UNIQUE INDEX icontrol ON control ( 33 tablename 34); 35 36-- Table: player 37-- a player 38 39CREATE TABLE player ( 40 player_id INTEGER NOT NULL 41 -- Name of player 42 ,name CHAR(80) NOT NULL 43 -- Misc notes about this player 44 ,notes VARCHAR(1000) NOT NULL 45 ,PRIMARY KEY (player_id) 46); 47 48CREATE UNIQUE INDEX iplayer ON player ( 49 player_id 50); 51 52-- Table: session 53 54CREATE TABLE session ( 55 session_id INTEGER NOT NULL 56 ,checksum CHAR(33) NOT NULL 57 -- Player 0 58 ,player_id0 INTEGER NOT NULL 59 -- Player 1 60 ,player_id1 INTEGER NOT NULL 61 -- The result of the match/session: 62 -- - the total number of points won or lost 63 -- - +1/0/-1 for player 0 won the match, match not complete, and 64 -- player 1 won match, respectively 65 ,result INTEGER NOT NULL 66 -- Length of match 67 -- 0=session, >0=match 68 ,length INTEGER NOT NULL 69 -- Timestamp for insert into database 70 ,added TIMESTAMP NOT NULL 71 -- Match info 72 ,rating0 CHAR(80) NOT NULL 73 ,rating1 CHAR(80) NOT NULL 74 ,event CHAR(80) NOT NULL 75 ,round CHAR(80) NOT NULL 76 ,place CHAR(80) NOT NULL 77 ,annotator CHAR(80) NOT NULL 78 ,comment CHAR(80) NOT NULL 79 ,date DATE 80 ,PRIMARY KEY (session_id) 81 ,FOREIGN KEY (player_id0) REFERENCES player (player_id) 82 ON DELETE RESTRICT 83 ,FOREIGN KEY (player_id1) REFERENCES player (player_id) 84 ON DELETE RESTRICT 85); 86 87CREATE UNIQUE INDEX isession ON session ( 88 session_id 89); 90 91-- Table: statistics 92-- Used from session and game tables to store match and game statistics, 93-- respectively. 94 95CREATE TABLE matchstat ( 96 matchstat_id INTEGER NOT NULL 97 -- session identification 98 ,session_id INTEGER NOT NULL 99 -- player identification 100 ,player_id INTEGER NOT NULL 101 -- chequerplay statistics 102 ,total_moves INTEGER NOT NULL 103 ,unforced_moves INTEGER NOT NULL 104 ,unmarked_moves INTEGER NOT NULL 105 ,good_moves INTEGER NOT NULL 106 ,doubtful_moves INTEGER NOT NULL 107 ,bad_moves INTEGER NOT NULL 108 ,very_bad_moves INTEGER NOT NULL 109 ,chequer_error_total_normalised FLOAT NOT NULL 110 ,chequer_error_total FLOAT NOT NULL 111 ,chequer_error_per_move_normalised FLOAT NOT NULL 112 ,chequer_error_per_move FLOAT NOT NULL 113 ,chequer_rating INTEGER NOT NULL 114 -- luck statistics 115 ,very_lucky_rolls INTEGER NOT NULL 116 ,lucky_rolls INTEGER NOT NULL 117 ,unmarked_rolls INTEGER NOT NULL 118 ,unlucky_rolls INTEGER NOT NULL 119 ,very_unlucky_rolls INTEGER NOT NULL 120 ,luck_total_normalised FLOAT NOT NULL 121 ,luck_total FLOAT NOT NULL 122 ,luck_per_move_normalised FLOAT NOT NULL 123 ,luck_per_move FLOAT NOT NULL 124 ,luck_rating INTEGER NOT NULL 125 -- cube statistics 126 ,total_cube_decisions INTEGER NOT NULL 127 ,close_cube_decisions INTEGER NOT NULL 128 ,doubles INTEGER NOT NULL 129 ,takes INTEGER NOT NULL 130 ,passes INTEGER NOT NULL 131 ,missed_doubles_below_cp INTEGER NOT NULL 132 ,missed_doubles_above_cp INTEGER NOT NULL 133 ,wrong_doubles_below_dp INTEGER NOT NULL 134 ,wrong_doubles_above_tg INTEGER NOT NULL 135 ,wrong_takes INTEGER NOT NULL 136 ,wrong_passes INTEGER NOT NULL 137 ,error_missed_doubles_below_cp_normalised FLOAT NOT NULL 138 ,error_missed_doubles_above_cp_normalised FLOAT NOT NULL 139 ,error_wrong_doubles_below_dp_normalised FLOAT NOT NULL 140 ,error_wrong_doubles_above_tg_normalised FLOAT NOT NULL 141 ,error_wrong_takes_normalised FLOAT NOT NULL 142 ,error_wrong_passes_normalised FLOAT NOT NULL 143 ,error_missed_doubles_below_cp FLOAT NOT NULL 144 ,error_missed_doubles_above_cp FLOAT NOT NULL 145 ,error_wrong_doubles_below_dp FLOAT NOT NULL 146 ,error_wrong_doubles_above_tg FLOAT NOT NULL 147 ,error_wrong_takes FLOAT NOT NULL 148 ,error_wrong_passes FLOAT NOT NULL 149 ,cube_error_total_normalised FLOAT NOT NULL 150 ,cube_error_total FLOAT NOT NULL 151 ,cube_error_per_move_normalised FLOAT NOT NULL 152 ,cube_error_per_move FLOAT NOT NULL 153 ,cube_rating INTEGER NOT NULL 154 -- overall statistics 155 ,overall_error_total_normalised FLOAT NOT NULL 156 ,overall_error_total FLOAT NOT NULL 157 ,overall_error_per_move_normalised FLOAT NOT NULL 158 ,overall_error_per_move FLOAT NOT NULL 159 ,overall_rating INTEGER NOT NULL 160 ,actual_result FLOAT NOT NULL 161 ,luck_adjusted_result FLOAT NOT NULL 162 ,snowie_moves INTEGER NOT NULL 163 ,snowie_error_rate_per_move FLOAT NOT NULL 164 -- for matches only 165 ,luck_based_fibs_rating_diff FLOAT 166 ,error_based_fibs_rating FLOAT 167 ,chequer_rating_loss FLOAT 168 ,cube_rating_loss FLOAT 169 -- for money sesisons only 170 ,actual_advantage FLOAT 171 ,actual_advantage_ci FLOAT 172 ,luck_adjusted_advantage FLOAT 173 ,luck_adjusted_advantage_ci FLOAT 174 -- time penalties 175 ,time_penalties INTEGER NOT NULL 176 ,time_penalty_loss_normalised FLOAT NOT NULL 177 ,time_penalty_loss FLOAT NOT NULL 178 -- 179 ,PRIMARY KEY (matchstat_id) 180 ,FOREIGN KEY (player_id) REFERENCES player (player_id) 181 ON DELETE RESTRICT 182 ,FOREIGN KEY (session_id) REFERENCES session (session_id) 183 ON DELETE CASCADE 184); 185 186CREATE UNIQUE INDEX ismatchstat ON matchstat ( 187 matchstat_id 188); 189 190 191-- Table: game 192 193CREATE TABLE game ( 194 game_id INTEGER NOT NULL 195 ,session_id INTEGER NOT NULL 196 -- Player 0 197 ,player_id0 INTEGER NOT NULL 198 -- Player 1 199 ,player_id1 INTEGER NOT NULL 200 -- score at start of game (since end of game is not meaningful if incomplete) 201 ,score_0 INTEGER NOT NULL 202 ,score_1 INTEGER NOT NULL 203 -- The result of the game 204 -- the total number of points won or lost 205 -- positive means player 0 won game, negative player 1 won game, 206 -- zero - game not complete 207 ,result INTEGER NOT NULL 208 -- Timestamp for insert into database 209 ,added TIMESTAMP NOT NULL 210 -- which game in the match/session this is 211 ,game_number INTEGER NOT NULL 212 -- if Crawford game 213 ,crawford INTEGER NOT NULL 214 ,PRIMARY KEY (game_id) 215 ,FOREIGN KEY (session_id) REFERENCES session (session_id) 216 ON DELETE CASCADE 217 ,FOREIGN KEY (player_id0) REFERENCES player (player_id) 218 ON DELETE RESTRICT 219 ,FOREIGN KEY (player_id1) REFERENCES player (player_id) 220 ON DELETE RESTRICT 221); 222 223CREATE UNIQUE INDEX igame ON game ( 224 game_id 225); 226 227-- Table: statistics 228-- Used from match and game tables to store match and game statistics, 229-- respectively. 230 231CREATE TABLE gamestat ( 232 gamestat_id INTEGER NOT NULL 233 -- game identification 234 ,game_id INTEGER NOT NULL 235 -- player identification 236 ,player_id INTEGER NOT NULL 237 -- chequerplay statistics 238 ,total_moves INTEGER NOT NULL 239 ,unforced_moves INTEGER NOT NULL 240 ,unmarked_moves INTEGER NOT NULL 241 ,good_moves INTEGER NOT NULL 242 ,doubtful_moves INTEGER NOT NULL 243 ,bad_moves INTEGER NOT NULL 244 ,very_bad_moves INTEGER NOT NULL 245 ,chequer_error_total_normalised FLOAT NOT NULL 246 ,chequer_error_total FLOAT NOT NULL 247 ,chequer_error_per_move_normalised FLOAT NOT NULL 248 ,chequer_error_per_move FLOAT NOT NULL 249 ,chequer_rating INTEGER NOT NULL 250 -- luck statistics 251 ,very_lucky_rolls INTEGER NOT NULL 252 ,lucky_rolls INTEGER NOT NULL 253 ,unmarked_rolls INTEGER NOT NULL 254 ,unlucky_rolls INTEGER NOT NULL 255 ,very_unlucky_rolls INTEGER NOT NULL 256 ,luck_total_normalised FLOAT NOT NULL 257 ,luck_total FLOAT NOT NULL 258 ,luck_per_move_normalised FLOAT NOT NULL 259 ,luck_per_move FLOAT NOT NULL 260 ,luck_rating INTEGER NOT NULL 261 -- cube statistics 262 ,total_cube_decisions INTEGER NOT NULL 263 ,close_cube_decisions INTEGER NOT NULL 264 ,doubles INTEGER NOT NULL 265 ,takes INTEGER NOT NULL 266 ,passes INTEGER NOT NULL 267 ,missed_doubles_below_cp INTEGER NOT NULL 268 ,missed_doubles_above_cp INTEGER NOT NULL 269 ,wrong_doubles_below_dp INTEGER NOT NULL 270 ,wrong_doubles_above_tg INTEGER NOT NULL 271 ,wrong_takes INTEGER NOT NULL 272 ,wrong_passes INTEGER NOT NULL 273 ,error_missed_doubles_below_cp_normalised FLOAT NOT NULL 274 ,error_missed_doubles_above_cp_normalised FLOAT NOT NULL 275 ,error_wrong_doubles_below_dp_normalised FLOAT NOT NULL 276 ,error_wrong_doubles_above_tg_normalised FLOAT NOT NULL 277 ,error_wrong_takes_normalised FLOAT NOT NULL 278 ,error_wrong_passes_normalised FLOAT NOT NULL 279 ,error_missed_doubles_below_cp FLOAT NOT NULL 280 ,error_missed_doubles_above_cp FLOAT NOT NULL 281 ,error_wrong_doubles_below_dp FLOAT NOT NULL 282 ,error_wrong_doubles_above_tg FLOAT NOT NULL 283 ,error_wrong_takes FLOAT NOT NULL 284 ,error_wrong_passes FLOAT NOT NULL 285 ,cube_error_total_normalised FLOAT NOT NULL 286 ,cube_error_total FLOAT NOT NULL 287 ,cube_error_per_move_normalised FLOAT NOT NULL 288 ,cube_error_per_move FLOAT NOT NULL 289 ,cube_rating INTEGER NOT NULL 290 -- overall statistics 291 ,overall_error_total_normalised FLOAT NOT NULL 292 ,overall_error_total FLOAT NOT NULL 293 ,overall_error_per_move_normalised FLOAT NOT NULL 294 ,overall_error_per_move FLOAT NOT NULL 295 ,overall_rating INTEGER NOT NULL 296 ,actual_result FLOAT NOT NULL 297 ,luck_adjusted_result FLOAT NOT NULL 298 ,snowie_moves INTEGER NOT NULL 299 ,snowie_error_rate_per_move FLOAT NOT NULL 300 -- for matches only 301 ,luck_based_fibs_rating_diff FLOAT 302 ,error_based_fibs_rating FLOAT 303 ,chequer_rating_loss FLOAT 304 ,cube_rating_loss FLOAT 305 -- for money sesisons only 306 ,actual_advantage FLOAT 307 ,actual_advantage_ci FLOAT 308 ,luck_adjusted_advantage FLOAT 309 ,luck_adjusted_advantage_ci FLOAT 310 -- time penalties 311 ,time_penalties INTEGER NOT NULL 312 ,time_penalty_loss_normalised FLOAT NOT NULL 313 ,time_penalty_loss FLOAT NOT NULL 314 -- 315 ,PRIMARY KEY (gamestat_id) 316 ,FOREIGN KEY (player_id) REFERENCES player (player_id) 317 ON DELETE RESTRICT 318 ,FOREIGN KEY (game_id) REFERENCES game (game_id) 319 ON DELETE CASCADE 320); 321 322CREATE UNIQUE INDEX isgamestat ON gamestat ( 323 gamestat_id 324); 325