DIR : /home/kozerus/public_html/go/dgs0/specs/db/table-Waitingroom.txt

/home/kozerus/public_html/go/dgs0/specs/db

###
### Description of mysql tables:  Waitingroom, WaitingroomJoined
###

###########################################################################
## --- Waitingroom ---

Description:
   Table to store game-offers from user who search for game challenges.

Important Note:
   - Must be kept synchron with table 'TournamentRules' for fields relevant for tournaments.
   - Must be kept synchron with 'Invite'-feature, but Invites have less features,
     because of the manual nature of an invitation.
     The settings for invitations are stored in the GameInvitation-table.

| Field         | Type                                     | Null | Key | Default             | Extra          |
+---------------+------------------------------------------+------+-----+---------------------+----------------+
| ID            | int(11)                                  | NO   | PRI | NULL                | auto_increment |
| uid           | int(11)                                  | NO   |     | NULL                |                |
| gid           | int(11)                                  | NO   |     | 0                   |                |
| ShapeID       | int(10) unsigned                         | NO   |     | 0                   |                |
| nrGames       | tinyint(3) unsigned                      | NO   |     | 1                   |                |
| Time          | datetime                                 | NO   |     | 0000-00-00 00:00:00 |                |
| GameType      | enum('GO','TEAM_GO','ZEN_GO')            | NO   |     | GO                  |                |
| GamePlayers   | char(5)                                  | NO   |     |                     |                |
| 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   | MUL | 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                   |                |
| MustBeRated   | enum('N','Y')                            | NO   |     | N                   |                |
| RatingMin     | smallint(6)                              | NO   |     | -9999               |                |
| RatingMax     | smallint(6)                              | NO   |     | -9999               |                |
| MinRatedGames | smallint(6)                              | NO   |     | 0                   |                |
| SameOpponent  | tinyint(4)                               | NO   |     | 0                   |                |
| ShapeSnapshot | varchar(255)                             | NO   |     |                     |                |
| Comment       | varchar(40)                              | NO   |     |                     |                |


## Fields:

* ID : primary key, auto-increment for game-offer

* uid : foreign key to Players.ID
   - player placing a game-offer

* gid : foreign key to Games.ID
   - 0 for standard-go-game
   - game-id (>0) for team-go/zen-go SETUP-status (ready to be joined)

* ShapeID : shape-id, FK to Shape.ID
   - see Games.ShapeID in 'specs/db/table-Games.txt'

* nrGames : int, count of entities
   - for game-type 'GO' : number of game-offers with same setting
      - max = 10
   - for game-type 'TEAM_GO / ZEN_GO' : number of reserved joins for game
      - (implicit) number of games always = 1
      - limited by game-type in Games.GameType
      - decreaed when one player joins MP-game taking one reserved placeholder

* Time : creation-date of game-offer

* GameType : game-type, see also Games.GameType

* GamePlayers : matching for game with number of players, see also Games.GamePlayers

* Ruleset : supported rulesets on DGS
   - JAPANESE = using territory-scoring (default)
   - CHINESE = using area-scoring

* Size : board-size for game
   - within range MIN_BOARD_SIZE..MAX_BOARD_SIZE, which is 5..25

* Komi : komi for game
   - only used if Type is not Conventional or Proper game

* Handicap : number of handicap stones for game
   - only used if Type is not Conventional or Proper game

* Handicaptype : game-mode, handicap-type, manual color setting
   - representation of 4 game-modes:
      - Conventional handicap: conv
      - Proper handicap: proper
      - Manual setting (color-setting): nigiri, double, black, white
      - Fair komi: auko_sec, auko_opn, div_ykic, div_ikyc

   - conv = Conventional handicap
   - proper = Proper handicap
   - nigiri = Even game with nigiri
   - double = Double game
   - black = manual game-mode, game-offerer wants to play as Black
   - white = manual game-mode, game-offerer wants to play as White
   - auko_sec = Secret Auction Komi, both players choose komi, higher bid gets Black
     giving his bid of komi to White, use nigiri on equal bids
   - auko_opn = Open Auction Komi, like Secret but with open bidding
   - div_ykic = Divide & Choose "You choose Komi, I choose Color"
   - div_ikyc = Divide & Choose "I choose Komi, You choose Color"

   - [future?] pie_ymic = Pie Rule "You choose first Move, I choose Color"
   - [future?] pie_imyc = Pie Rule "I choose first Move, You choose Color"

* AdjKomi : Komi adjustment and Jigo-mode
   - Calculated or fix komi is adjusted by value of 'AdjKomi' (+/- komi)
   - AdjKomi can be natural number x.0 (no fraction) or fractional with x.5

* JigoMode : Komi adjustment concerning Jigo
   - KEEP_KOMI = Komi is not changed regarding Jigo
   - ALLOW_JIGO = Komi is rounded down to allow Jigo (resulting Komi = x.0)
   - NO_JIGO = Komi is rounded up to forbid Jigo (resulting Komi = x.5)

* AdjHandicap, MinHandicap, MaxHandicap : Handicap stones adjustment
   - AdjHandicap: adjust number of handicap stone by reduction (if <0) or increase (>0)
   - MinHandicap: minimum limitation of resulting handicap stones, set to MAX_HANDICAP if exceeding
   - MaxHandicap: maximum limitation of resulting handicap stones, set to MAX_HANDICAP if exceeding;
     value -1 = default-max-handicap calculated by formula depending on board-size

* Maintime : int, main-time for game
   - unit is one "tick" (=5 minutes); see 'TICK_FREQUENCY' in include/quick_common.php,
     so 15 is 1h (=one hour)
   - can be 0

* Byotype : time-mode for game
   - JAP = Japanese Byo-yomi
   - CAN = Canadian Byo-yomi
   - FIS = (Capped) Fischer time without initial time, Maintime is also cap

* Byotime, Byoperiods : extra-time and extra-periods for game, depending on 'Byotype'
   - for JAP: extra-time is Byoperiods times Byotime, e.g. 10 x 1day
   - for CAN: extra-time is Byotime time for Byoperiods number of stones,
     e.g. 10days for 5 stones
   - for FIS: extra-time is Byotime added for each submitted move capped by Maintime,
     Byoperiods is not used for Fischer-time-mode
   - Byotime can be 0 for absolute time (no extra-time) for all time-modes

* Rated : boolean, specifies if game should be rated ('Y') or unrated ('N')

* StdHandicap : boolean
   - specifies if standard placement for handicap stones should be used ('Y') or not ('N')

* WeekendClock : boolean
   - specifies wether clock is running on weekend ('Y') or not running ('N')
   - at the moment (bug), the weekend-clock of timezone UTC is used

* MustBeRated : boolean
   - specifies if challenger (opponent) must have a rating ('Y') or not ('N')
   - if 'Y', RatingMin and RatingMax are used to restrict rating range

* RatingMin, RatingMax : rating range challengers rating has to be in
   - only integer-precision required

* MinRatedGames : number of minimum rated finished games of challenger

* SameOpponent : restrictions on challenger for same game-offer
   - join-status is kept in table 'WaitingroomJoined'
   -  0 = no restriction if same opponent joins more than one game of same game-offer,
          table WaitingroomJoined is not used in this case
   - < -100 = restrict same opponent for game-offer to max. number of total games,
          so number of all started games with opponent must be < -N -100
   - <0 = restrict same opponent for game-offer to max. number of times to join,
          keeping track of joined number of games with 'WaitingroomJoined.JoinedCount'
   - >0 = restrict same opponent for game-offer to min. time [days] to pass for next join,
          keeping track of joined number of games with 'WaitingroomJoined.ExpiresDate'

* ShapeSnapshot : extended snapshot of shape-game
   - see Games.ShapeSnapshot in 'specs/db/table-Games.txt'

* Comment : short note (40 chars) of user placing game-offer in waiting-room



###########################################################################
## --- WaitingroomJoined ---

Description:
   Table to keep track of restrictions of joined game-offers in waiting room.
   Depends on value of corresponding Waitingroom.SameOpponent (not used if =0).

Notes:
   - PRIMARY KEY is (wroom_id,opp_id):
     wroom_id comes first, because need leading index for cleanup in daily_cron
   - db-entries without existing Waitingroom-reference will be deleted (see daily_cron)

| Field       | Type       | Null | Key | Default             | Extra |
+-------------+------------+------+-----+---------------------+-------+
| opp_id      | int(11)    | NO   | PRI | NULL                |       |
| wroom_id    | int(11)    | NO   | PRI | NULL                |       |
| JoinedCount | tinyint(4) | NO   |     | 0                   |       |
| ExpireDate  | datetime   | NO   |     | 0000-00-00 00:00:00 |       |


## Fields:

* opp_id : foreign key to Players.ID
   - identify opponent to keep track of for game-offer restrictions

* wroom_id : foreign key to Waitingroom.ID
   - identify game-offer to keep track of opponent-based restriction for

* JoinedCount : int
   - number of joined games of Player (opp_id) for game-offer (wroom_id)
   - mutual exlusive with use of ExpireDate
   - only used if Waitingroom.SameOpponent < 0

* ExpireDate : date
   - expire-date on which opponent (opp_id) can join another game for game-offer (wroom_id)
   - mutual exlusive with use of JoinedCount
   - only used if Waitingroom.SameOpponent > 0