### ### Description of mysql game-tables: Games ### ########################################################################### ## --- Games --- Description: Table to store static and dynamic information about game. - store invited game, running game, game in fair-komi-negotiation, finished game | Field | Type | Null | Key | Default | Extra | +--------------------+--------------------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | 0 | | | ShapeID | int(10) unsigned | NO | | 0 | | | Starttime | datetime | NO | | 0000-00-00 00:00:00 | | | Lastchanged | datetime | NO | MUL | 0000-00-00 00:00:00 | | | mid | int(11) | NO | | 0 | | | DoubleGame_ID | int(11) | NO | | 0 | | | Black_ID | int(11) | NO | MUL | NULL | | | White_ID | int(11) | NO | MUL | NULL | | | ToMove_ID | int(11) | NO | MUL | 0 | | | GameType | enum('GO','TEAM_GO','ZEN_GO') | NO | | GO | | | GamePlayers | char(5) | NO | | | | | Ruleset | enum('JAPANESE','CHINESE') | NO | | JAPANESE | | | Size | tinyint(3) unsigned | NO | MUL | 19 | | | Komi | decimal(4,1) | NO | | 6.5 | | | Handicap | tinyint(3) unsigned | NO | MUL | 0 | | | Status | enum('KOMI','SETUP','INVITED','PLAY','PASS','SCORE','SCORE2','FINISHED') ... | NO | MUL | INVITED | | | Moves | smallint(5) unsigned | NO | MUL | 0 | | | Black_Prisoners | smallint(5) unsigned | NO | | 0 | | | White_Prisoners | smallint(5) unsigned | NO | | 0 | | | Last_X | tinyint(4) | NO | | -1 | | | Last_Y | tinyint(4) | NO | | -1 | | | Last_Move | char(2) | NO | | | | | Flags | smallint(5) unsigned | NO | MUL | 0 | | | Score | decimal(5,1) | NO | MUL | 0.0 | | | Maintime | smallint(6) | NO | | 0 | | | Byotype | enum('JAP','CAN','FIS') | NO | | JAP | | | Byotime | smallint(6) | NO | | 0 | | | Byoperiods | tinyint(4) | NO | | 0 | | | Black_Maintime | smallint(6) | NO | | 0 | | | White_Maintime | smallint(6) | NO | | 0 | | | Black_Byotime | smallint(6) | NO | | 0 | | | White_Byotime | smallint(6) | NO | | 0 | | | Black_Byoperiods | tinyint(4) | NO | | -1 | | | White_Byoperiods | tinyint(4) | NO | | -1 | | | LastTicks | int(11) | NO | | 0 | | | ClockUsed | smallint(6) | NO | MUL | 0 | | | TimeOutDate | int(11) | NO | | 0 | | | Rated | enum('N','Y','Done') | NO | | N | | | StdHandicap | enum('N','Y') | NO | | N | | | WeekendClock | enum('N','Y') | NO | | Y | | | Black_Start_Rating | double | NO | | -9999 | | | White_Start_Rating | double | NO | | -9999 | | | Black_End_Rating | double | NO | | -9999 | | | White_End_Rating | double | NO | | -9999 | | | Snapshot | varchar(216) | NO | | | | | ShapeSnapshot | varchar(255) | NO | | | | | GameSetup | varchar(255) | NO | | | | ## Fields: * ID : primary key, games-ID * tid : tournament-id, FK to Tournament.ID - 0 = no tournament-game * ShapeID : shape-id, FK to Shape.ID - 0 = no shape-game (standard) - maybe not sync'ed with ShapeSnapshot-field - the shape cannot be disputed in invitations * Starttime : timestamp when game has started (clock starts ticking) - for invited game it's the creation date (of the invitation), which will be updated when the game actually starts * Lastchanged : timestamp when there was a move-like change to the game - not changed on adding-time (because reserved for email-notifications on moves) * mid : foreign key to Messages.ID - field is set, if game has been created by an invitation, then it refers to the last invitation message or the result-message for finished games - field is 0 if game has not been created by an invitation (from waiting-room or tournament) * DoubleGame_ID : foreign key to Games.ID - referring to other game if started as double-game * Black_ID, White_ID : foreign key to Players.ID - for normal-game : representing current Black and White players - for invite-game : - Black_ID, White_ID = the two users of a game-invitation - Black_ID is the user that initially sent the first invitation-message * ToMove_ID : sometimes foreign key to Players.ID; other times integer-value - for started games : Players.ID that has to move next in game - this includes KOMI-status for fair-komi-negotiation - for finished games (Status=FINISHED) : 0 - for multi-player-games on Status=SETUP : Players.ID of game-master - for invite-game (Status=INVITED): - Players.ID of user, that sent the last invitation- or dispute-message - user-specific game-setup properties are stored in GameInvitation-table in two entries, one entry for the inviting user and one for the invited user (=opponent). All fields, that both users can change are stored in their GameInvitation-table-entry. The latest game-setup on sending the 1st invitation or a dispute-message will be copied from GameInvitation to the Games-table; see 'make_invite_game()'-func. Games-table-fields, that can NOT be disputed: ShapeID, ShapeSnapshot Games-table-fields, that are not supported for invitations: GameType=GO, GamePlayers='' - value > 0 : old-style is OBSOLETE, but since 1.0.16 'values > 0' are re-used in a different way (see above)! - all old way-games has been migrated to use GameInvitation-table instead - positive value was indicator for FIX-color handicap-type; it was always refer to the black-player - value < 0 : OBSOLETE since 1.0.16 - used to keep handicap-type, see consts 'INVITE_HANDI_...' - for value INVITE_HANDI_FIXCOL the handicaptype HTYPE_BLACK/WHITE was calculated by matching the current Black_ID with the logged-in user - for value INVITE_HANDI_DIV_CHOOSE the handicaptype HTYPE_YOU_KOMI_I_COLOR/I_KOMI_YOU_COLOR was calculated by matching the current logged-in user as pivot-user in the Games.GameSetup-field taking that handicap-type - for available handicap-types and more details, see Waitingroom.Handicaptype in 'specs/db/table-Waitingroom.txt' - for game on KOMI-status: - the Players.ID of player who must do the next bidding/choice for negotiating fair-komi - the JigoMode to be able to start a fair-komi after negotiation is saved in the GameSetup-field and must be decoded from that GameSetup-field. - JigoMode : Komi adjustment concerning Jigo-check on fair-komi-negotiation - KEEP_KOMI = Komi is not changed regarding Jigo - ALLOW_JIGO = Komi-bid must not be fractional x.0 - NO_JIGO = Komi-bid must be fractional x.5 * GameType : game-type, see also Waitingroom.GameType - GO = standard Go (1:1) - TEAM_GO = Team-Go (N:M) with N+M > 2, two teams of 1..N and 1..M players, each team has a fix color (B|W) - ZEN_GO = Multi-player Go (N) with odd N > 2, N players with switching colors * GamePlayers : matching of players for game, see also Waitingroom.GamePlayers - format: "" (Go), "N:M" (Team-Go), "N" (Zen-Go) - GameType is determined from GamePlayers-info - "" | 1:1 | 2 = Go (standard Go); for normal Go the field must be empty - N:M = Team-Go, N+M > 2, 2:2 = Rengo - N = Zen-Go, N > 2 - for invitations, GamePlayers is empty ='' - IMPORTANT NOTE: When a MP-game is started (leaving SETUP-status), the 'GamePlayers'-fields may need to be updated into order 'B:W', which is needed for playing. see also: game_players.php#check_game_setup() * Size : board-size for game - within range MIN_BOARD_SIZE..MAX_BOARD_SIZE, which is 5..25 * Komi : komi for game * Handicap : number of handicap stones for game - within range 0..MAX_HANDICAP, which is 0..21 * Status : current status for game - KOMI: game-entry is used to negotiate fair-komi, fields Black/White_Prisoners are used to store B/W-komi-bids during negotiation - SETUP: game-entry is used to configure game-players for multi-player-game - INVITED: game-entry is used to hold game-settings for invitation-game negotiation, - If game is on this status, there are also two corresponding GameInvitation-entries holding game-setup properties for the two users negotiating the invitation. See also notes for 'ToMove_ID'-field for more details. - PLAY: running game, playing mode - PASS: running game, player has passed (transition to scoring-step) - SCORE: running game, first player marks dead stones (scoring-step 1) - SCORE2: running game, second player marks dead stones (scoring-step 2) - FINISHED: finished game, game has finished, ratings adjusted eventually * Moves : number of moves in game, including handicap-stones - for normal games (GameType=GO) if game is running: used as moves-counter including handicap-stones, pass-moves and score-steps - for multi-player-games (GameType<>GO) if Games.Status=SETUP: used as counter of joined players, shown on status-page in MP-games-list - for multi-player-games (GameType<>GO) if game is running: used as counter/cursor/index to check who is next to move - starts at 0, use modulo-counter using info from GamePlayers-field to find next player to move in GamePlayers-table - handicap-stones must be substracted * Black_Prisoners, White_Prisoners - number of Black and White (stone) prisoners * Last_X, Last_Y : last-move (sometimes) - for game-status PLAY: x/y coordinates of last move (start at 0..size-1) - for game-status PASS: Last_X=POSX_PASS, Last_Y undefined - for game-status SCORE/SCORE2: Last_X=POSX_SCORE - for game-status FINISHED: Last_X= POSX_RESIGN/TIME/FORFEIT/NO_RESULT/SCORE - for other game-status: undefined - since 10-Dec-2013 used to hold last-move for last-move-marker in game-thumbnail - NOTE (till 10-Dec-2013): purpose was unclear. sure it holds the last position, but for what is unclear - a possibility is to check for last-move in the "old" quick_play-suite * Last_Move : legacy-field - according to comment in 'scripts/game_consistency.php', field-usage is: old way to detect Ko. Could be removed when no more old way games. * Flags : game-flags indicating certain state of game - 0x0001 = Ko: last move has taken a single stone, used to check for Ko - 0x0002 = HiddenMsg: one of the game-messages contain a hidden message ("...") - 0x0004 = AdmResult: game-result was set by admin - 0x0008 = TGDetached: tournament-game detached from tournament (e.g. because user removed from T) - 0x0010 = AttachedSgf: indicates that there at least one SGF is attached to game - 0x0020 = NoResult: indicates game-result was NO-RESULT (set by game-admin) * Score : score of game for finished game - 0 = jigo, or no-result (=void, then additionally GAMEFLAGS_NO_RESULT set in Games.Flags) - -1000, 1000 = Black(-1000)/White(1000) has won game by resignation - -2000, 2000 = Black(-2000)/White(2000) has won game by timeout - -3000, 3000 = Black(-3000)/White(3000) has won game by forfeit, can only be set by game-admin and for tournament-games! * Maintime : int, main-time for game - see Waitingroom.Maintime in 'specs/db/table-Waitingroom.txt' * Byotype : time-mode for game - see Waitingroom.Byotype in 'specs/db/table-Waitingroom.txt' * Byotime, Byoperiods : extra-time and extra-periods for game, depending on 'Byotype' - see Waitingroom.Byotime/Byoperiods in 'specs/db/table-Waitingroom.txt' * Black_Maintime, Black_Byotime, Black_Byoperiods : current remaining time for Black player - Maintime = remaining main-time in hours (15h = 1d incl. 9h sleeping-time), if 0 byo-yomi has started for japanese and canadian time-systems - Byotime = remaining time of byo-yomi-period for japanese and canadian, 0 for Fischer - Byoperiods = - for japanese and canadian: -1 if byo-yomi not started yet, otherwise remaining number of byo-yomi periods - for Fischer always -1 * White_Maintime, White_Byotime, White_Byoperiods : - current remaining time for White player * LastTicks : last-ticks used for time-calculus - NOTE: TICK_FREQUENCY = 12 ticks/hour, i.e. 1 tick spans 5 min - if Games.ClockUsed >= 0, then the player-to-move is NOT on vacation and LastTicks are the copied from the player-to-move's Clock.Ticks (for Players.ClockUsed), when a player moved: T (= elapsed ticks since last move) := Clock[ID=Games.ClockUsed].Ticks - Games.LastTicks Value T is used to calculate hours since last move (see 'ticks_to_hours(..)'), then to check if the remaining time of the player-to-move is enough or the time is up. - if Games.ClockUsed < 0, then the player-to-move is on vacation and LastTicks is < 0 containing the time spent (in negative ticks) being independent from any clock. This is done by substracting the value of Clock.Ticks of the current game-clock. When the vacation ends, the value of LastTicks is corrected by adding the absolute value of Clock.Ticks of the current game-clock (=Players.ClockUsed of player-to-move corrected for weekend-clock if required). * ClockUsed : foreign key to Clock.ID, which clock to use for current player - normal clock (0-23) - normal weekend-clock (100-123) - "vacation-clock" (-1), or weekend-vacation-clock (-101), obsolete but can still appear and is not wrong NOTE: therefore check for vacation-clock with "clock_id < 0". * TimeOutDate : absolute "date" expressed in ticks aligned to the timeleft-clock (Clock.ID=204) - used for remaining-time ordering for status-games - only valid for player-to-move - value is changed if player starts or changes the length of the vacation: - If player is on vacation, the remaining-time changes as well, because the TimeOutDate is an absolute date. - If user starts vacation, the TimeOutDate of all games-to-move for that user is extended by the number of ticks representing the length of vacation-days. - If user changes the length of vacation, the TimeOutDate of all the games-to-move for that user is adjusted by the respective number of ticks. - If the vacation of a user ends, the TimeOutDate is not changed, because the additional time (gained by vacation) has been consumed. - value is not changed if player changes night-time or timezone, because current game-clocks are not changed. Actually the game-clocks for games to move in MUST NOT be changed, because the clock-ticks for other clocks are not aligned and so the used time by the current player may jump, because Games.LastTicks refers to the ticks of the currently used-clock as specified by Games.ClockUsed rather than being relative to the Clock.Ticks of the new clock. Only on the next move the clock-change influences the game-time, but then also TimeOutDate is correctly calculated anew with the changed Players.ClockUsed. - Weekend-handling is NOT implemented, because: Weekend-handling would have a flaw, which after outages for which the clock are not "ticking" (like maintenance) would need that admins have to recalculate the Games.TimeOutDate. The weekend-handling would need to use the current day and time to compensate all weekends during the time left. This weekend-compensation would make it unsynchronized with the Clock-aligned TimeOutDate. Also the weekend-clock (and normal clocks) are 15-hour days, while the timeleft-clock resembles a 24-hour day. And additionally the night-time would need to be taken into account when calculating for the time gained by the respective weekends. => Admins can run fix_games_timeleft-script for all games to fix Games.TimeOutDate after longer outages. However, the TimeOutDate is "corrected" on the players next-move, so it can be accepted to skip the fix-step after maintenance if not taking too long. CONCLUSION: Games with weekend-clock stopped is not handled, because it would be too complex to correctly calculate the time-diff. So in effect the TimeOutDate is not accurate for games with clock-stopped-on-weekends, but not (completely) wrong as the player has the weekend-time as additional time-left for those games. * Rated : game-setting - game is rated if field-value != 'N' ('Y', 'Done'), non-rated otherwise ('N') - 'Done' = A game start with the 'N' or 'Y' status. After it has the 'Y'-status at the end of a game, the rating of the player is updated and after that the 'Rated'-status is set to 'Done' to avoid doing this stuff twice. So, when a game is finished it had either the 'N' or 'Done' status; so 'Done' means 'Y' + rating-recorded. * StdHandicap : game-setting - game uses standard handicap stones placement on setup-phase if value == 'Y', free handicap placement otherwise ('N') * WeekendClock : clock running on weekend - specifies wether clock is running on weekend ('Y') or not running ('N') - see also Waitingroom.WeekendClock in 'specs/db/table-Waitingroom.txt' * Black_Start_Rating, Black_End_Rating : - start and end rating of Black player for rated finished game * White_Start_Rating, White_End_Rating : - start and end rating of White player for rated finished game * Snapshot : base64-encoded snapshot of game-position - see 'include/classlib_game.php' in GameSnapshot::make_game_snapshot()-function * ShapeSnapshot : extended snapshot of shape-game - only set if ShapeID != 0 - base64-encoded snapshot of shape-game (without dead-stone-markers) - format: " S [W|B]", see GameSnapshot::parse_extended_snapshot() in 'include/classlib_game.php' - not (always) sync'ed with ShapeID-field, i.e. when author changes the shape-data of the shape-id, then the Games.ShapeSnapshot keeps the version with which the game has been started, while the ShapeID keeps the original reference to shape-id => opening the view-shape-page with shape-id AND the current snapshot of the game shows the differences * GameSetup : game-settings to handle rematch, fair-komi-negotiation, invitation - can be empty (for older game-invitations) -> default-handling is used in such cases - for normal (non-invitation) games, Format (A) is used - for game-invitations since 1.0.16, Format (A) is used, but see notes about format (B) as well - for game-invitations up to 1.0.15, Format (B) was used - General Format: - values separated by ':' (const 'GS_SEP') - groups for Type, User, Handicap, Komi, Restrictions, Comments - see class GameSetup (encode-/new_from_game_setup-func) and map $MAP_GAME_SETUP in "include/game_functions.php" - values already stored in Games-table are not encoded, e.g. fields std-H/time-info/size/rated/ruleset; though for invitations there are needed to calculate diffs for invitation-order. - (A) Format for started games & game-invitations (1st part): - "T:U:H::::K::J:FK:R:::::C" - : handicap-type - : user-id (=Players.ID), game started/initiated by given user - : number of handicap-stones - : [+|-] number to adjust handicap - : min handicap-stones - : max handicap-stones, -1 = calculated default-max-handicap - : komi-bid of user, can be empty, used for fair-komi - : [+|-] number to adjust komi - : jigo-mode (0=keep-komi, 1=allow-jigo, 2=no-jigo) - : komi-bid of opponent, can be empty, used for fair-komi - : 0=false, 1=true - : min/max-rating for waiting-room - : min. rated finished games, see WaitingRoom.MinRatedGames in 'specs/db/table-Waitingroom.txt' - : see Waitingroom.SameOpponent in 'specs/db/table-Waitingroom.txt' - : waiting-room-comment or invitation-message (part) - Fields , , , have no meaning for invitations, default-values are stored - Example: "T1:U123:H0:-2:0:9:K6.5:0:J2:FK:R1:-800:2000:5:-101:Cslow game" - (B) Format for game-invitations (2nd part): - OBSOLETE since 1.0.16 for game-invitations, replaced with GameInvitation-table - but NOT OBSOLETE and in use for GameSetup-part for invite-profile-templates, see 'specs/db/table-Profiles.txt' - for invitations (up to 1.0.15), field 'GameSetup' contained two strings, one for each player: - old-usage: used to store all game-setup to tell the difference on disputes - Format (A) is used in Games-table when invitation is accepted - format GameSetup := "T..C:I... T..C:I.." with empty, part-separator is space ' ' - "I::r::t::::" - 'I' stands for invitation-game-setup-part - : board-size - : 0=unrated, 1=rated - : 1=japanese, 2=chinese - : 0=free-handicap, 1=std-handicap - : J=japanese-byo-yomi, C=canadian-byo-yomi, F=Fischer-time - , , : number (=hours) - : 0=clock-stopped-on-weekend, 1=clock-running-on-weekend - Example: - old-invitation: "T1:U12:H0:0:0:-1:K6:0:J2:FK:R0:0:0:0:0:C:I19:1:r1:0:tF:60:15:0:1 T1:U12:H0:0:0:21:K6:0:J2:FK:R0:0:0:0:0:C:I19:1:r0:0:tF:45:15:0:0" - invite-template GameSetup-part: "T1:U12:H0:0:0:-1:K6:0:J2:FK:R1:-900:2900:0:0:C:I19:1:r1:0:tF:60:15:0:1" ########################################################################### ## --- GamesPriority --- Description: Table to be able to sort status-games by priority. - table-entries are only kept for RUNNING games - table-entries are deleted for finished games - table-entry only inserted on demand by user, there are games without GamesPriority-entries | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | gid | int(11) | NO | PRI | NULL | | | uid | int(11) | NO | PRI | NULL | | | Priority | smallint(6) | NO | | 0 | | ## Fields: * gid : foreign key to Games.ID * uid : foreign key to Players.ID * Priority : integer-value with priority ########################################################################### ## --- GamePlayers --- Description: Table to model multi-player-games like Team-Go and Zen-Go. - uid non-UNIQUE per game-id, allow to have same player more than once per group | Field | Type | Null | Key | Default | Extra | +------------+------------------------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | gid | int(11) | NO | MUL | NULL | | | GroupColor | enum('B','W','G1','G2','BW') | NO | | BW | | | GroupOrder | tinyint(4) | NO | | 0 | | | uid | int(11) | NO | MUL | 0 | | | Flags | smallint(5) unsigned | NO | | 0 | | ## Fields: * ID : primary key, game-player-ID - same uid allowed more than once per game-id, and so for delete we need PK to identify (single) correct entity * gid : foreign key to Games.ID * GroupColor : team/group-color of user for multi-player-game - BW = both colors (unset AND used for Zen-Go) - B = black-group - W = white-group - G1 = user in same group 1, but without assigned color - G2 = user in same group 2, but without assigned color * GroupOrder : order within team/group - range: 1..n for each team/group - < 0 = inactive ex-group-member, replaced by admin on game-players request, e.g. if game is stalled * uid : foreign key to Players.ID * Flags : flags for game-player - see 'include/game_functions.php' GPFLAG_... ########################################################################### ## --- Moves --- Description: - table storing all moves, setting-handicap, prisoners, PASS/SCORE-sequence, ADD-TIME-entries Notes: - the number of records of Moves could be greater than the number of corresponding Games.Moves if: - there are prisoners - a sequence like PASS/PASS/SCORE.../RESUME had already occured. - time has been added to opponent //FIXME(?) on game-end by time-out/resignation !??? /* To store the last $hours info (never used, to be checked) $to_move= ( $ToMove_ID == $Black_ID ? BLACK : WHITE ); db_query( "clock_tick.update_moves($gid)", "INSERT INTO Moves SET " . "gid=$gid, " . "MoveNr=$Moves+1, " . //CAUTION: problem it's > Games.Moves "Stone=$to_move, " . "PosX=".POSX_TIME.", PosY=0, " . "Hours=$hours" ); if ( mysql_affected_rows() < 1 ) error('mysql_insert_move', "clock_tick.update_moves($gid)"); */ | Field | Type | Null | Key | Default | Extra | +--------+----------------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | gid | int(11) | NO | MUL | NULL | | | MoveNr | smallint(5) unsigned | NO | | NULL | | | Stone | tinyint(3) unsigned | NO | | 0 | | | PosX | tinyint(4) | NO | | NULL | | | PosY | tinyint(4) | NO | | NULL | | | Hours | smallint(5) unsigned | NO | | NULL | | ## Fields: * ID : primary key * gid : foreign key to Games.ID * MoveNr : move-number - not unique - correlates with Games.Moves * Stone : player-color for "move"-action (see PosX) - 0 (NONE) = prisoner taken at coordinates (PosX,PosY) with Hours=0 - 1 (BLACK) = "move"-action by black-player - 2 (WHITE) = "move"-action by white-player - 7 (MARKED_BY_WHITE) = point marked by black-player - 8 (MARKED_BY_BLACK) = point marked by white-player * PosX : describing move-action, or coordinates of point/move - -1 (POSX_PASS) = Pass-move, Stone=BLACK|WHITE, PosY=0, Hours=passed-time - -2 (POSX_SCORE) = scoring-step by Stone=BLACK|WHITE, PosY=0, Hours=passed-time - points marked first or differently than opponent are stored as separate Moves-entries with same MoveNr and Stone=7|8 respectively - -3 (POSX_RESIGN) = resigned by Stone=BLACK|WHITE, PosY=0, Hours=passed-time - -4 (POSX_TIME) = timeout for Stone=BLACK|WHITE, PosY=0, Hours=passed-time - -5 (POSX_SETUP) = setup for shape-game for Stone=BLACK: PosY=0, Hours=0 - initial shape-setup for board is taken from Games.ShapeSnapshot - -50 (POSX_ADDTIME) = added time by Stone=BLACK|WHITE, PosY = bitmask with bit 1 (0x01)=byoyomi-reset, bit 2 (0x02)=added-by-TD), Hours = added-hours (can be 0 if only byoyomi-reset) - >= 0: - (PosX,PosY) represents number-coordinates 0..24 with (0,0) = upper-left corner - player-color determined by Stone=1|2 - prisoners are stored as separate Moves-entries as well with Stone=0 and same MoveNr * PosY : y-coordinate, see also 'PosX'-field * Hours : number of hours - for PosX >= 0 (coordinate): - Hours >= 0 with passed time since last board-move - rounded down, so moving within 1 hour gives Hours=0 - for PosX = -1 (pass), -2 (score), -3 (resign), -4 (timeout): - number of hours passed since last board-move was played - for PosX = -5 (shape-setup): - Hours = 0 - for PosX = -50 (add-time): - Hours >=0 : number of added hours ########################################################################### ## --- Shape --- Description: Table to store shape-game boards. | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | uid | int(11) | NO | MUL | NULL | | | Name | varchar(40) | NO | | | | | Size | tinyint(3) unsigned | NO | | NULL | | | Flags | tinyint(3) unsigned | NO | | 0 | | | Notes | text | NO | | NULL | | | Snapshot | varchar(216) | NO | | | | | Created | datetime | NO | | 0000-00-00 00:00:00 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | ## Fields: * ID : primary key * uid : foreign key to Players.ID * Name : unique name of shape, given by user * Size : board-size 5..25 * Flags : flags for shape - see 'include/game_functions.php' SHPFLAG_... - 0x01 = play-color-white, default black - 0x02 = public-flag, i.e. visible to all per default * Snapshot : base64-encoded snapshot of game-position - see 'include/classlib_game.php' in GameSnapshot::make_game_snapshot()-function * Notes : user-comment * Created : creation-date of shape * Lastchanged : date of last-update of shape ########################################################################### ## --- MoveStats --- Description: Table to store move counts per time-slot to create moving-profile for user. | Field | Type | Null | Key | Default | Extra | +----------+-----------------------+------+-----+---------+-------+ | uid | int(11) | NO | PRI | 0 | | | SlotTime | smallint(5) unsigned | NO | PRI | 0 | | | SlotWDay | tinyint(4) | NO | PRI | 0 | | | SlotWeek | tinyint(4) | NO | PRI | 0 | | | Counter | mediumint(8) unsigned | NO | | 0 | | ## Fields: * uid : foreign key to Players.ID * SlotTime : time-slot, hour:min of day - format: hhmm, hh = hour of day (0..23), mm = minute of hour (0..59) - minimum time-slot not smaller than 5min, otherwise int-types of table are not sufficient * SlotWDay : time-slot, day of week - format: day of week (0..6) * SlotWeek : week of year, 0..51 * Counter : number of moves per user done for a specific time-slot ########################################################################### ## --- GameSgf --- Description: Table to store SGFs attached (saved) for finished game. One SGF per user allowed. | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------------------+-------+ | gid | int(11) | NO | PRI | NULL | | | uid | int(11) | NO | PRI | NULL | | | Flags | tinyint(3) unsigned | NO | | 0 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | SgfData | blob | NO | | NULL | | ## Fields: * gid : foreign key to Games.ID * uid : foreign key to Players.ID * Flags : flags (currently none) * Lastchanged : date of last-update of shape * SgfData : binary-blob with raw SGF ########################################################################### ## --- GameInvitation --- Description: Table to store game-setup of game-invitation between two players before game is started. Notes: - At any time there must be exactly two entries per invitation (Games with Status='INVITED'). - Fields from GameInvitation-entry are copied to Games-table when respective user sends out the first invitational message or sends a dispute. The 'Games.ToMove_ID'-field represents the user, that last sent the invitation/dispute-message. See also 'Games.ToMove_ID'-field for more details. - GameInvitation-entries are kept till game is finished, then the two entries are removed. After invitation-game has been started and game is running, GameInvitation-entries are not used and could be deleted, but are kept for safety (in case something goes wrong with encoding Games.GameSetup). | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------------------------------------+------+-----+---------------------+-------+ | gid | int(11) | NO | PRI | NULL | | | uid | int(11) | NO | PRI | NULL | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | Ruleset | enum('JAPANESE','CHINESE') | NO | | JAPANESE | | | Size | tinyint(3) unsigned | NO | | 19 | | | Komi | decimal(4,1) | NO | | 6.5 | | | Handicap | tinyint(3) unsigned | NO | | 0 | | | Handicaptype | enum('conv','proper','nigiri','double','black','white','auko_sec','auko_opn','div_ykic','div_ikyc') | NO | | conv | | | AdjKomi | decimal(4,1) | NO | | 0.0 | | | JigoMode | enum('KEEP_KOMI','ALLOW_JIGO','NO_JIGO') | NO | | KEEP_KOMI | | | AdjHandicap | tinyint(4) | NO | | 0 | | | MinHandicap | tinyint(3) unsigned | NO | | 0 | | | MaxHandicap | tinyint(4) | NO | | -1 | | | Maintime | smallint(6) | NO | | 0 | | | Byotype | enum('JAP','CAN','FIS') | NO | | JAP | | | Byotime | smallint(6) | NO | | 0 | | | Byoperiods | tinyint(4) | NO | | 0 | | | Rated | enum('N','Y') | NO | | N | | | StdHandicap | enum('N','Y') | NO | | N | | | WeekendClock | enum('N','Y') | NO | | Y | | ## Fields: * gid : foreign key to Games.ID * uid : foreign key to Players.ID - two entries for the two participating parties for a game-invitation, one entry GameInvitation.uid joins to Games.Black_ID, the other to Games.White_ID * Lastchanged : time of last changed to data-row * Handicaptype : handicap-type for player given by uid-field - initial row-entries for handicap-type for both players have to be oppositely set, i.e. if uid1 chooses 'black', for uid2 'white' must be set (or vice versa). Later on disputes they are not both changed when the users dispute the invitation-game-settings. * Ruleset, Size, Komi, Handicap, Handicaptype, AdjKomi, JigoMode, AdjHandicap, MinHandicap, MaxHandicap, Maintime, Byotype, Byotime, Byoperiods, Rated, StdHandicap, WeekendClock - fields exactly like in Waitingroom-table, see 'specs/db/table-Waitingroom.txt'