### ### Description of mysql tables: Tournament ### Here you'll find some infos about all the tournament tables. Legend: - T = Tournament - TD = Tournament Director - TP = Tournament Participant (registered player) Important Notes: - Guests can NOT perform "writing" operations for Ts - Admin can edit all Ts - Admin can set T-status at any time to any status - Admin and T-owner can add, delete & edit all TDs of a T - All TDs can edit their T ########################################################################### ## --- Tournament --- Description: Tournament-management-data with scope, type, status, owner, dates and description. | Field | Type | Null | Key | Default | Extra | +--------------+------------------------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Scope | enum('DRAGON','PUBLIC','PRIVATE') | NO | | PUBLIC | | | Type | enum('LADDER','ROUNDROBIN','LEAGUE') | NO | | NULL | | | WizardType | tinyint(4) | NO | | NULL | | | Title | varchar(255) | NO | | NULL | | | Description | text | NO | | NULL | | | Owner_ID | int(11) | NO | | NULL | | | Status | enum('ADM','NEW','REG','PAIR','PLAY','CLOSED','DEL') | NO | MUL | NEW | | | Flags | smallint(5) unsigned | NO | | 0 | | | Created | datetime | NO | | NULL | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | | StartTime | datetime | NO | MUL | 0000-00-00 00:00:00 | | | EndTime | datetime | NO | | 0000-00-00 00:00:00 | | | Rounds | tinyint(3) unsigned | NO | | 1 | | | CurrentRound | tinyint(3) unsigned | NO | | 1 | | | RegisteredTP | smallint(6) | NO | | 0 | | | LockNote | varchar(255) | NO | | | | | Prev_tid | int(11) | NO | | 0 | | | Next_tid | int(11) | NO | | 0 | | ## Fields: * Scope : - DRAGON = tournament sponsored by Dragon-server, official Dragon tourney, can only be "started" by DGS-admins - PUBLIC = public tournament, can be created by every DGS-user, open to everyone - PRIVATE = private tournament, registration only with token or by TD-invitation * Type : Tournament-type - LADDER = TP can "climb" up ladder (=KING-OF-THE-HILL) - ROUNDROBIN = TPs playing in pooled rounds - LEAGUE = TPs playing in pools in pyramid structured tiers (=leagues) * WizardType : type of tournament created by tournament-wizard (more precise than Type) - reflects variations of main-Type with different attributes and limitations, e.g. Dragon-Ladder <-> Private-Ladder (the latter being restricted in some way) * Title : name of the T * Description : description of the T * Owner_ID : Players.ID of the T-creator * Status : current process-state of the T - ADM = reserved state (to migrate old Ts) - NEW = freshly created T (needs setup) - REG = registration-period can start - PAIR = pairing for next round starts - PLAY = pairing finished, games are started automatically, TPs play until all games are finished - CLOSED = T finished - DEL = T about to be deleted * Flags : bitmask with tournament-flags - values defined in 'tournaments/include/tournament_globals.php' as TOURNEY_FLAG_..., also see descriptions in 'tournaments/include/tournament.php' getFlagsText()-func - 0x0001 (=LOCK_ADMIN): - prohibits all read-write ops on tourney for non-TAdmins: - prohibits user to all register-ops, challenge on ladder, withdraw from ladder - prohibits TD/owner to edit-tourney/director/props/rules/rounds/participants/locks/status, game-admin - prohibits TD/owner to edit-ladder-props, admin/edit-ladder - prohibits cron from processing for locked tourney - 0x0002 (=LOCK_REGISTER): - prohibits user to join tournament by apply/register/ack-invite or edit-registration - 0x0004 (=LOCK_TDWORK): lock for (some) work of TD - prohibits cron (having less prio than tdwork-lock) - stop cron for current tourney with set ladder-lock - prohibits user to join tournament by apply/register/ack-invite or edit-registration - prohibits user to challenge on ladder, withdraw from ladder - needed to allow TD/owner/admin to admin/edit-ladder - 0x0008 (=LOCK_CRON): - can block some ops to admin/edit tournament for TD/Owner/Admin (e.g. for ladder) - 0x0010 (=LOCK_CLOSE): prepares tournament for transition to CLOSED-status - prohibits user to join tournament - prohibits TD/owner to edit-registration - for ladder: prohibits user to challenge/withdraw * Created : date of creation of T * Lastchanged : date of last-update of T (on Status, Name, Description, Start/EndTime) * ChangedBy : handle-list of users, that last changed data-row in format '[last-user] [prev-user] ...' * StartTime : official start-date of T (informal) * EndTime : datetime when T closed, T can be changed afterwards still by TDs * Rounds, CurrentRound : number of rounds, current round-number of T * RegisteredTP : aggregate of number of registered participants of tournament - is kept up-to-date on registering-operations - count can be greater than overall TournamentProperties.MaxParticipants, because of set players for later rounds * LockNote : note for other TDs/T-Admins when tournament-locks are set * Prev_tid, Next_tid : FK to Tournament.ID - previous & next league-tournament linked with current league-tournament ########################################################################### ## --- TournamentDirector --- Description: List of TDs for tournament with additional flags and comment about TD. | Field | Type | Null | Key | Default | Extra | +---------+----------------------+------+-----+---------+-------+ | tid | int(11) | NO | PRI | NULL | | | uid | int(11) | NO | PRI | NULL | | | Flags | smallint(5) unsigned | NO | | 0 | | | Comment | varchar(255) | NO | | | | ## Fields: * tid, uid : primary key of this table - Existing DB-entry makes User [uid] a TD for T [tid] * Flags : bitmask with flags for TD - 0x0001 = TD_FLAG_GAME_END = TD is allowed to end tournament-game prematurely, (only when on TournamentGames.Status=PLAY) - 0x0002 = TD_FLAG_GAME_ADD_TIME = TD is allowed to add time to players of tournament-game as long as game is running - 0x0004 = TD_FLAG_ADMIN_EDIT = TD is allowed to edit tournament-info & rules even if not in T-status 'NEW' (similar to what a T-admin can do) * Comment : public comment about TD, may be empty ########################################################################### ## --- TournamentParticipant --- Description: list with TPs of tournament and user-specific tournament data | Field | Type | Null | Key | Default | Extra | +-----------------+-----------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | uid | int(11) | NO | MUL | NULL | | | Status | enum('APPLY','REGISTER','INVITE') | NO | | APPLY | | | Flags | smallint(5) unsigned | NO | | 0 | | | Rating | double | NO | | -9999 | | | StartRound | tinyint(3) unsigned | NO | | 1 | | | NextRound | tinyint(3) unsigned | NO | | 0 | | | Created | datetime | NO | | 0000-00-00 00:00:00 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | | Comment | varchar(60) | NO | | | | | DirectorMessage | text | NO | | NULL | | | Finished | mediumint(8) unsigned | NO | | 0 | | | Won | mediumint(8) unsigned | NO | | 0 | | | Lost | mediumint(8) unsigned | NO | | 0 | | | PenaltyPoints | mediumint(9) | NO | | 0 | | | Lastmoved | datetime | NO | | 0000-00-00 00:00:00 | | ## Fields: * tid, uid : tournament-ID and user-id, that registers - Existing DB-entry makes User [uid] a candidate for participating in the T [tid] - User can register himself on non-private Ts - User can be invited by TD * Status : - APPLY = user applied for T, but needs to be verified by TD, TD can approve or reject application of user - REGISTER = user is registered, can be unregistered by user or TD - INVITE = user is invited, user need to approve or reject invitation - State-Transitions for TD & User: - INIT = not registered to T - Edit-C = edit customized fields - Edit-M = edit non-customized fields like messages, comment - Edit-All = edit all fields - " ---[action]---> " - State-Transitions for User: INIT ---[apply]---------> APPLY (customized registration) APPLY ---[EDIT-All]------> APPLY APPLY ---[remove]--------> INIT INIT ---[register]---> REGISTER (matching T-restrictions, no customization) REGISTER ---[EDIT-C]-----> APPLY REGISTER ---[EDIT-M]-----> REGISTER REGISTER ---[remove]-----> INIT INVITE ---[EDIT-C]-------> APPLY INVITE ---[ACK-Invite]---> REGISTER INVITE ---[remove]-------> INIT - State-Transitions for TD: INIT ---[invite]-----> INVITE [ + notify ] INVITE ---[EDIT-All]---> INVITE INVITE ---[remove]-----> INIT [ + notify ] APPLY ---[ACK-Apply]---> REGISTER [ + notify ] APPLY ---[EDIT-C]------> INVITE [ + notify ] APPLY ---[EDIT-M]------> APPLY APPLY ---[remove]------> INIT [ + notify ] REGISTER ---[EDIT-C]---> INVITE [ + notify ] REGISTER ---[EDIT-M]---> REGISTER REGISTER ---[remove]---> INIT [ + notify ] * Flags : - additional properties of registered user: - 0x0001 (=INVITED): invited by TD - 0x0002 (=ACK_INVITE): invite by TD approved by user - 0x0004 (=ACK_APPLY): user-application approved by TD - 0x0008 (=VIOLATE): user-registration violates T-restrictions - 0x0100 (=TIMEOUT_LOSS): TP lost by timeout - future: LOCK (user blocked/locked by TD for arbitrary reason) * Rating : Rating used for T, depends on TournamentProperties.RatingUseMode * StartRound : Round user wants to start in for T - first round = 1, higher start-round > 1 * NextRound : Round user is going to play in for T - player is included in pooling for round: TP.NextRound == T.CurrentRound - assert: NextRound always >= StartRound * Created : date of initial application/registration/invitation for T * Lastchanged : date of last-update on TP * ChangedBy : handle-list of users, that last changed data-row in format '[last-user] [prev-user] ...' * Comment : public comment from user * DirectorMessage : private message from TD to user * Finished, Won, Lost : number of finished/won/lost tournament-games of user - Finished: annulled (=detached) tournament-games are not counted, other games including no-results games are counting - Won, Lost: jigo and no-result games are not counted as won or lost * PenaltyPoints : points - increase and decrease on tournament-specific configurations and trigger action on configured threshold - default 0, negative points allowed * Lastmoved : date of last moved - 0 = not moved yet ########################################################################### ## --- TournamentProperties --- Description: List of properties of a tournament, that influences registration process. | Field | Type | Null | Key | Default | Extra | +----------------------+-------------------------------------------+------+-----+---------------------+-------+ | tid | int(11) | NO | PRI | NULL | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | | MinParticipants | smallint(6) | NO | | 2 | | | MaxParticipants | smallint(6) | NO | | 0 | | | MaxStartRound | tinyint(3) unsigned | NO | | 1 | | | MinRatingStartRound | smallint(6) | NO | | -9999 | | | RatingUseMode | enum('COPY_CUSTOM','CURR_FIX','COPY_FIX') | NO | | COPY_CUSTOM | | | RegisterEndTime | datetime | NO | | 0000-00-00 00:00:00 | | | UserMinRating | smallint(6) | NO | | -9999 | | | UserMaxRating | smallint(6) | NO | | -9999 | | | UserRated | enum('N','Y') | NO | | N | | | UserMinGamesFinished | smallint(6) | NO | | 0 | | | UserMinGamesRated | smallint(6) | NO | | 0 | | | Notes | text | NO | | NULL | | ## Fields: * tid : tournament-ID * Lastchanged : update of table-entry * ChangedBy : handle-list of users, that last changed data-row in format '[last-user] [prev-user] ...' * MinParticipants, MaxParticipants : minimum / maximum number of TPs - 0 = no limits * MaxStartRound : max. number of starting round - restricts not only registration by user, but also editing participant by tournament-director - range 1..X, X determined by tournament-type specific limit - should be 1 for tournament-types without rounds (e.g. ladder) * MinRatingStartRound : min. rating for user-registration to allow higher start-round - restricts registration by user to change start-round - does not restrict edititing participant by tournament-director, if MaxStartRound allows it - min. rating to allow user on registration to change start-round - -9999 (=NO_RATING) = user can not enter at higher start-round, only tournament-director may do so * RatingUseMode : mode to handle tournament-rating on registration (and tournament playing) - COPY_CUSTOM = copies existing user-rating for T-rating, but allow user to overwrite it (customizable) - CURR_FIX = always use current DGS-rating during T, no customization of rating allowed by TD or user - COPY_FIX = copies exising user-rating for T-rating, can't be changed afterwards by user (fix = not customizable) * RegisterEndTime : datetime for end of registration-phase * UserMinRating, UserMaxRating : minimum / maximum rating of TPs - -9999 = not limited - is not used, if UserRated='N' - only integer-precision required - 2000 (=1k) .. 2600 (=6d) means, that a user with rating between Elo rating >= 1950 and < 2650, respectively "1k (-50%)" .. "6d (+50%)" would match the rating range of UserMinRating..UserMaxRating * UserRated : - Y = user must have a rating on registering - N = user need no rating on registering * UserMinGamesFinished, UserMinGamesRated : - minimum number of finished / rated-finished games of TPs - 0 = no limits * Notes : additional Notes to be shown for registration-section ########################################################################### ## --- TournamentRules --- Description: Game settings for tournament. | Field | Type | Null | Key | Default | Extra | +---------------+---------------------------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | ShapeID | int(10) unsigned | NO | | 0 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | | Flags | smallint(5) unsigned | NO | | 0 | | | Ruleset | enum('JAPANESE','CHINESE') | NO | | JAPANESE | | | Size | tinyint(3) unsigned | NO | MUL | 19 | | | Handicaptype | enum('CONV','PROPER','NIGIRI','DOUBLE','BLACK','WHITE','ALTERNATE') | NO | | CONV | | | AdjKomi | decimal(4,1) | NO | | 0.0 | | | JigoMode | enum('KEEP_KOMI','ALLOW_JIGO','NO_JIGO') | NO | | KEEP_KOMI | | | Handicap | tinyint(3) unsigned | NO | | 0 | | | Komi | decimal(4,1) | NO | | 6.5 | | | AdjHandicap | tinyint(4) | NO | | 0 | | | MinHandicap | tinyint(3) unsigned | NO | | 0 | | | MaxHandicap | tinyint(4) | NO | | -1 | | | StdHandicap | enum('N','Y') | NO | | N | | | Maintime | smallint(6) | NO | | 0 | | | Byotype | enum('JAP','CAN','FIS') | NO | | JAP | | | Byotime | smallint(6) | NO | | 0 | | | Byoperiods | tinyint(4) | NO | | 0 | | | WeekendClock | enum('N','Y') | NO | | Y | | | Rated | enum('N','Y') | NO | | N | | | ShapeSnapshot | varchar(255) | NO | | | | | Notes | text | NO | | NULL | | ## Fields: * tid : tournament-ID * ShapeID : shape-id, FK to Shape.ID - see Games.ShapeID in 'specs/db/table-Games.txt' * Lastchanged : update of table-entry * ChangedBy : handle-list of users, that last changed data-row in format '[last-user] [prev-user] ...' * Flags : not-used * Ruleset : ruleset, see 'specs/db/table-Waitingroom.txt' * Size : board-size in range 5..25 * Handicaptype : - CONV = conventional handicap - PROPER = proper handicap - NIGIRI = even game with nigiri - BLACK | WHITE = - ladder-tournament: manual game with challenger getting black | white - round-robin-tournament: manual game with stronger player getting black | white - DOUBLE = double game - ladder-tournament: not supported, because it makes no sense for ladders - round-robin-tournament: supported - ALTERNATE = alternate Black and White for one player - ladder-tournament: not supported, because it makes no sense for ladders - round-robin-tournament: supported - in relation to Waitingroom-table noted enums in upper-case * AdjKomi : used to adjust (calculated) komi * JigoMode : adjusts (calculated) komi according to: - KEEP_KOMI = komi is not changed (default) - ALLOW_JIGO = komi is (absolutely) rounded down to non-real komi (0.5 -> 0) - NO_JIGO = komi is (absolutely) increased to avoid jigo * Handicap : number of handicap stones * Komi : komi * AdjHandicap, MinHandicap, MaxHandicap : - Handicap-stone adjustment after applying handicap-type - like in waiting-room but with less settings * StdHandicap : standard-placement of handicap-stones - Y = use standard-placement - N = otherwise * Maintime, Byotype, Byotime, Byoperiods : * WeekendClock : - Y = use weekend-clock - N = otherwise * Rated : - Y = games played in T do adjust rating of TPs, need rated user - N = otherwise * ShapeSnapshot : extended snapshot of shape-game - see Games.ShapeSnapshot in 'specs/db/table-Games.txt' * Notes : additional Notes to be shown for tournament-rules ########################################################################### ## --- TournamentRound --- Description: Pool-setup for one tournament round. | Field | Type | Null | Key | Default | Extra | +-----------------+------------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | Round | tinyint(3) unsigned | NO | | 1 | | | Status | enum('INIT','POOL','PAIR','PLAY','DONE') | NO | | INIT | | | MinPoolSize | tinyint(3) unsigned | NO | | 0 | | | MaxPoolSize | tinyint(3) unsigned | NO | | 0 | | | MaxPoolCount | smallint(5) unsigned | NO | | 0 | | | PoolWinnerRanks | tinyint(3) unsigned | NO | | 0 | | | Pools | smallint(5) unsigned | NO | | 0 | | | PoolSize | tinyint(3) unsigned | NO | | 0 | | | PoolNamesFormat | varchar(64) | NO | | | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | char(54) | NO | | | | ## Fields: * tid : tournament-ID * Round : round number * Status : pairing status for tournament round - INIT = new round started, setting up round - POOL = create/setup pools, assign TPs to pools - PAIR = setup T-games, games ready to be created and started - PLAY = play all games, take over pool-winners to next round or final results - DONE = round is completed * MinPoolSize, MaxPoolSize : min. and max. size of pools for tournament round * MaxPoolCount : how many pools are being used at maximum - minimum pool-count is implicitly = 1 * PoolWinnerRanks : what top ranks make pool winners to either proceed to next round or win the tournament - 0 = setting will be configured later; value 0 is allowed for round-robin tournament on round-INIT status, but must be >0 later when defining pools * Pools : number of pools actually used for tournament-round * PoolSize : max. size of every pool actually used for tournament-round * PoolNamesFormat : round-specific format for pool-names - NOTE: default = "%P %t(uc)%p(num)" - NOTE: see also PoolNameFormatter-class - %P = translated text for 'Pool' - %L = translated text for 'League' - %p(fmt) = formatted pool-index - %p(num) : format pool-index as number 1.. - %p(uc) : format pool-index as upper-case letter A..Z; pool-index > 26 will fallback to number-format 27.. - %t(fmt) = formatted tier-index (only for league-tournament); fmt like for %p(fmt) - %% = escaping '%' * Lastchanged : date of last update of table-entry * ChangedBy : user-list that updated table-entry in format: "[user2] [user1] ..." ########################################################################### ## --- TournamentLadder --- Description: Table to keep track of ladder of ladder-typed tournaments. | Field | Type | Null | Key | Default | Extra | +---------------+----------------------+------+-----+---------------------+-------+ | tid | int(11) | NO | PRI | NULL | | | rid | int(11) | NO | PRI | NULL | | | uid | int(11) | NO | MUL | NULL | | | Flags | tinyint(3) unsigned | NO | | 0 | | | Created | datetime | NO | | 0000-00-00 00:00:00 | | | RankChanged | datetime | NO | | 0000-00-00 00:00:00 | | | Rank | smallint(5) unsigned | NO | | 0 | | | BestRank | smallint(5) unsigned | NO | | 0 | | | StartRank | smallint(5) unsigned | NO | | 0 | | | PeriodRank | smallint(5) unsigned | NO | | 0 | | | HistoryRank | smallint(5) unsigned | NO | | 0 | | | ChallengesIn | tinyint(3) unsigned | NO | | 0 | | | ChallengesOut | tinyint(3) unsigned | NO | | 0 | | | SeqWins | smallint(5) unsigned | NO | | 0 | | | SeqWinsBest | smallint(5) unsigned | NO | | 0 | | ## Fields: * tid : FK to Tournament.ID * rid : FK to TournamentParticipant.ID * uid : FK to TournamentParticipant.uid (=redundant with rid, added for faster-access) * Flags : flags of participant in ladder-tournament - 0x01 (=HOLD_WITHDRAWAL): HOLD-state to indicate scheduled withdrawal from ladder-tournament, prevents being challenged * Created : start-date of user in ladder * RankChanged : date of last rank-changed caused by direct win or loss of user - not updated if leap-frogged by other TPs * Rank : current ladder-rank (=position) for user * BestRank : maximum of reached ladder-ranks for user caused by direct win or loss - initially := Rank * StartRank : starting rank, doesn't change - defaults to Rank (initial rank) * PeriodRank : rank at start of new period - initially 0 - period defined by ladder configuration - e.g. one month, so could be rank from start of month * HistoryRank : rank from previous period - initially 0 - period defined by ladder configuration - e.g. one month, so could be rank from previous month * ChallengesIn : number of current incoming challenges for ladder-user * ChallengesOut : number of current outgoing challenges for ladder-user * SeqWins : sequenced wins - number of consecutive wins (including jigo) for ladder-user - resetted to 0 if winning-streak broken * SeqWinsBest : track best (=max.) number of consecutive wins (including jigo) for ladder-user ########################################################################### ## --- TournamentLadderProps --- Description: Table for ladder-specific tournament properties. | Field | Type | Null | Key | Default | Extra | +------------------------+--------------------------------------------------------------------------------+------+-----+---------------------+-------+ | tid | int(11) | NO | PRI | NULL | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | | ChallengeRangeAbsolute | smallint(6) | NO | | 0 | | | ChallengeRangeRelative | tinyint(3) unsigned | NO | | 0 | | | ChallengeRangeRating | smallint(6) | NO | | -32768 | | | ChallengeRematchWait | smallint(5) unsigned | NO | | 0 | | | MaxDefenses | tinyint(3) unsigned | NO | | NULL | | | MaxDefenses1 | tinyint(3) unsigned | NO | | 0 | | | MaxDefenses2 | tinyint(3) unsigned | NO | | 0 | | | MaxDefensesStart1 | tinyint(3) unsigned | NO | | 0 | | | MaxDefensesStart2 | tinyint(3) unsigned | NO | | 0 | | | MaxChallenges | tinyint(3) unsigned | NO | | 0 | | | DetermineChallenger | enum('GAME_START','GAME_END') | NO | | GAME_END | | | GameEndNormal | enum('CH_ABOVE','CH_BELOW','SWITCH','DF_BELOW','DF_LAST') | NO | | CH_ABOVE | | | GameEndJigo | enum('NO_CHANGE','CH_ABOVE','CH_BELOW') | NO | | CH_BELOW | | | GameEndTimeoutWin | enum('NO_CHANGE','CH_ABOVE','CH_BELOW','SWITCH','DF_BELOW','DF_LAST','DF_DEL') | NO | | DF_BELOW | | | GameEndTimeoutLoss | enum('NO_CHANGE','CH_LAST','CH_DEL') | NO | | CH_LAST | | | PenaltyTimeout | smallint(5) unsigned | NO | | 0 | | | PenaltyLimit | smallint(5) unsigned | NO | | 0 | | | UserJoinOrder | enum('REGTIME','RATING','RANDOM') | NO | | REGTIME | | | UserAbsenceDays | tinyint(3) unsigned | NO | MUL | 0 | | | RankPeriodLength | tinyint(3) unsigned | NO | | 1 | | | SeqWinsThreshold | tinyint(3) unsigned | NO | | 0 | | ## Fields: * tid : FK to Tournament.ID * Lastchanged : date of last update of table-entry * ChangedBy : user-list that updated table-entry in format: "[user2] [user1] ..." * ChallengeRangeAbsolute : - number of rank-positions a ladder-user can challenge users above own position - -1 = no limit, challenger can challenge all users above own position - 0 = unused option, i.e. other challenge ranges must configure challenge-range * ChallengeRangeRelative : - percentage of ladder-users above current pos that can be challenged - 0 = unused option, i.e. other challenge ranges must configure challenge-range * ChallengeRangeRating : - challenge-range is [ChallengeRangeRating] ranks down from theoretical position in laddered ordered by user/tourney-rating - Example: - rank of 1dans on ladder is 7, user-rating is 1dan, ChallengeRangeRating=5 - Then user can challenge ladder-users up to rank 12 (=7+5). - value = -32768 : unused option, i.e. other challenge ranges must configure challenge-range * ChallengeRematchWait : how many hours to wait till rematch same user - 0 = unused, challenger can immediately rematch same user - >0 = hours to wait before a rematch with same user * MaxDefenses : default max. number of defenses * MaxDefenses1 : max. number of defenses for 1st rank-group * MaxDefenses2 : max. number of defenses for 2nd rank-group * MaxDefensesStart1 : start-rank for MaxDefenses1 (counting up) for 1st rank-group * MaxDefensesStart2 : start-rank for MaxDefenses2 (counting up) for 2nd rank-group - There can be 3 groups of different max-defenses. Example: - restrict ranks 1..5 to 5 games, ranks 6..10 to 4 games, other ranks to 3 games MaxDefenses1=5, MaxDefensesStart1=5, MaxDefenses2=4, MaxDefensesStart1=10, MaxDefenses=3 - With 2 rank-groups MaxDefenses2,Start2=0 - With 1 rank-groups MaxDefenses1/2,Start1/2=0 - There must be a default limitation > 0 - rank-groups1/2 restrictions are optional => use 0 = not-used * MaxChallenges : max. number of outgoing challenges (for challenger) - must not be 0 (= unlimited) * DetermineChallenger : determines role of challenger for applying game-end-process - GAME_START : challenger is player with lower-ladder-position at game-start - GAME_END : challenger is player with lower-ladder-position determined at game-end (default), leads to a more lively ladder * GameEndNormal : normal handling on game-end when challenger wins over defender - default = CH_ABOVE - Enum-values subset: see tournaments/include/tournament_globals.php * GameEndJigo : handling of game-end with jigo - default = CH_BELOW - Enum-values subset: see tournaments/include/tournament_globals.php * GameEndTimeoutWin : handling of game-end when challengers wins by timeout (=defender loses by timeout) - default = DF_BELOW - Enum-values: see tournaments/include/tournament_globals.php * GameEndTimeoutLoss : handling of game-end when challengers loses by timeout (=defender wins by timeout) - default = CH_LAST - Enum-values: see tournaments/include/tournament_globals.php * PenaltyTimeout : penalty added for loss on timeout - TournamentParticipant.PenaltyPoints is increased by this amount on loss by timeout - value-range 0-1000 - 0 = disabled, then PenaltyLimit must be 0 as well * PenaltyLimit : penalty reaching limit starts certain actions - for ladders: user is withdrawn for ladder, if TournamentParticipant.PenaltyPoints reach limit - value-range 0-50000 - 0 = disabled, then PenaltyTimeout must be 0 as well * UserJoinOrder : determines position in ladder for newly joined user - REGTIME : add users in ladder-position ordered by register-time (new at bottom); default - RATING : add users in ladder-position below user with same rating; dependent on TournamentProperties.RatingUseMode this may be either the current user rating (CURR_FIX) or the copied tournament-rating (COPY_CUSTOM|COPY_FIX) - RANDOM : add users in random ladder-positions in the ladder * UserAbsenceDays : days of user being absent without taking actions - 0 = disabled absent check - days, not counting user on vacation user is withdrawn from ladder for not having accessed the server for $UserAbsenceDays days * RankPeriodLength : length (in months) of one rank-period after which rank is "archived" - min. value = 1 - unit is number of months - each new period at start of month, the ladder-tournament current ranks are copied to PeriodRank-field and former PeriodRank copied to HistoryRank-field * SeqWinsThreshold : min-threshold to be crossed by ladder-user SeqWinsBest to enter tournament-result - 0 = disabled - >0 = if ladder-user SeqWinsBest crosses threshold adds or updates tournament-result for best mark ########################################################################### ## --- TournamentGames --- Description: Table to keep track of tournament-games. | Field | Type | Null | Key | Default | Extra | +----------------+-------------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | Round_ID | int(11) | NO | | 0 | | | Tier | tinyint(3) unsigned | NO | | 1 | | | Pool | smallint(5) unsigned | NO | | 0 | | | gid | int(11) | NO | MUL | 0 | | | Status | enum('INIT','PLAY','SCORE','WAIT','DONE') | NO | MUL | INIT | | | TicksDue | int(11) | NO | | 0 | | | Flags | smallint(5) unsigned | NO | | 0 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | Challenger_uid | int(11) | NO | MUL | NULL | | | Challenger_rid | int(11) | NO | | NULL | | | Defender_uid | int(11) | NO | MUL | NULL | | | Defender_rid | int(11) | NO | | NULL | | | StartTime | datetime | NO | | 0000-00-00 00:00:00 | | | EndTime | datetime | NO | | 0000-00-00 00:00:00 | | | Score | decimal(5,1) | NO | | 0.0 | | ## Fields: * tid : FK to Tournament.ID * Round_ID : FK to TournamentRound.ID - 0 for ladder-type tournaments - only set for round-robin-type tournaments - Q: why is this field needed ? A: problem is that T-game is not unique for different rounds. Two players could meet again in later rounds. T-game most often be used to process T-game-end. Round-number makes a bad index, so used TRound.ID * Tier : tier number - value: 1..n - for round-robin-/league-type tournaments: corresponding TournamentPool.Tier for T-game - NOTE: always 1 for ladder- & round-robin-tournaments * Pool : pool number - for ladder-type tournaments: 0 - for round-robin-type tournaments: corresponding TournamentPool.Pool for T-game - NOTE: field is needed to speed up db-queries on filling-ranks of finished pools * gid : FK to Games.ID, 0 = unset yet - must not be set to 0, once Games-entry has been started (needed for tournament-game-admin) - annulled (=detached) tournament-games have updated Games.tid := 0, but TournamentGames.gid is still kept pointing to corresponding Games.ID even if back-reference from game to tournament removed * Status : status for Tournament-game - INIT = Tournament-game scheduled, need to be started (not used for ladder-tournament) - PLAY = Tournament-game is being played by challenger and defender - SCORE = Tournament-game has ended by score/resign/jigo/timeout/forfeit/no-result and needs processing for tournament - WAIT = mark Tournament-game for rematch-waiting with same user, see TicksDue-field - DONE = Tournament-game is finished and results booked in tournament * TicksDue : 5min clock-ticks to keep track of rematch-lock - has meaning only if status=WAIT - TicksDue contains the absolute number of 5min-clock-ticks (from CLOCK_TIMELEFT) when the status is changed to DONE to allow rematch of challenger and defender * Flags : flags - 0x0001 = game-result (Score) has been set by tournament-director (not taken from Games.Score) - 0x0002 = game is detached from tournament (on user-removal for example), - TG.Score has no meaning if this flag is set, - for ladder-tournament the corresponding incoming- and outgoing challenges are freed for the involved tournament-participants - 0x0004 = if set, indicates that role of challenger and defender has switched at game-end - 0x0008 = game-result NO-RESULT (then Score should be 0) * Lastchanged : date of last update of table-entry * Challenger_uid : FK to Players.ID for challenger * Challenger_rid : FK to TournamentParticipant.ID = TournamentLadder.rid * Challenger_uid : FK to Players.ID for defender * Challenger_rid : FK to TournamentParticipant.ID = TournamentLadder.rid * StartTime : like Games.Startime * EndTime : like Games.Lastchanged when game finished - can be 0 if tournament-game ended prematurely, e.g. by detaching on ladder-user-removal * Score : similar to Games.Score but meaning is oriented on challenger/defender-roles - Score has no meaning if game is "detached" from tournament (see TG.Flags), then Score should normally be 0. - 0 = unset yet, only for INIT/PLAY-status - set for SCORE/DONE-status: - 0 = jigo, or no-result (additionally TG_FLAG_GAME_NO_RESULT set in TournamentGames.Flags) - -1000, 1000 = Challenger(-1000)/Defender(1000) has won game by resignation - -2000, 2000 = Challenger(-2000)/Defender(2000) has won game by timeout - -3000, 3000 = Challenger(-2000)/Defender(2000) has won game by forfeit (=bye) ########################################################################### ## --- TournamentExtension --- Description: Table with extended tournament-data. | Field | Type | Null | Key | Default | Extra | +-------------+----------------------+------+-----+---------------------+-------+ | tid | int(11) | NO | PRI | NULL | | | Property | smallint(5) unsigned | NO | PRI | NULL | | | IntValue | int(11) | NO | | 0 | | | DateValue | datetime | NO | MUL | 0000-00-00 00:00:00 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | ## Fields: * tid : FK to Tournament.ID * Property : key for property-extension - 1 = date-property with next date to update Ladder-Tournament Rank-history, initialized to first day of current month, handled in cron by adding TournamentLadderProps.RankPeriodLength months and copying TournamentLadder.PeriodRank to HistoryRank and Rank to PeriodRank-field - 2 = date-property with date when TD has started to create all (pool-)games for current round. Extension-entry only serves as "token" and "lock" to prevent others to do the same at the same time. The row will be deleted after successfully starting of all games. * IntValue : optional integer-like value * DateValue : optional date-like value * Lastchanged : date of last update of table-entry * ChangedBy : user-list that updated table-entry in format: "[user2] [user1] ..." ########################################################################### ## --- TournamentPool --- Description: Table to store pool-entries for round-robin & league tournaments with assigned users. Notes: - TournamentPool-table is actually keeping track of user-assignment in a pool along with the users pool-rank. A better table-name would be TournamentPoolEntry, but TournamentPool is used as it is shorter. Also to note is, that the table is only in 2NF containing some redundancies (tid+Round_ID+Tier+Pool could be extracted as FK for example), but these fields are kept due to performance reasons. - number of running tournament-games : restricted by const TROUND_MAX_POOLSIZE (=max. number of opponents for a pool-user + 1) and number of games per round for the same user (single- or double round-robin). | Field | Type | Null | Key | Default | Extra | +-------+----------------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | Round | tinyint(3) unsigned | NO | | NULL | | | Tier | tinyint(3) unsigned | NO | | 1 | | | Pool | smallint(5) unsigned | NO | | NULL | | | uid | int(11) | NO | MUL | 0 | | | Rank | tinyint(4) | NO | | -100 | | | Flags | tinyint(3) unsigned | NO | | 0 | | ## Fields: * tid : FK to Tournament.ID * Round : round number * Tier : tier number (for league-tournaments) - value: 1..n - NOTE: always 1 for round-robin-tournaments * Pool : pool number - 0 = special pool with unassigned users (Tier=1) - NOTE: can only exist if tournament-games not started yet, used for manual pool-editing - value: 1..n * uid : FK to TournamentParticipant.uid = Players.ID - one uid can only appear once in the same pool -> UNIQ(tid,Round,Pool,uid) * Rank : place of users within one pool - -100 = Rank not set yet, not shown (show only calculated rank) - 0 = no rank, also "withdrawn" TP, shown as NO_VALUE (show only calculated rank) - <0 = Rank, shown as ABS(Rank), user DOES NOT proceed to next round - for ROUND-ROBIN-tournaments: user is no pool-winner, user does not appear in T-results - for LEAGUE-tournaments: NOT-USED - >0 = Rank, normally indicator that pool-user WILL proceed to next round / cycle - for ROUND-ROBIN-tournaments: pool-user is pool-winner and WILL proceed to the next round, pool-user may appear in T-results - for LEAGUE-tournaments: pool-user WILL proceed to the next cycle, but filling ranks is only a pre-step, because at end of tournament a relegation-type MUST BE set (with TournamentPool.Flags) - NOTE: ABS(Rank) = 1 is highest rank * Flags : flags for pool-user - NOTE for LEAGUE-tournaments: pool-user without PROMOTE or DEMOTE flag set but with Rank>0 will stay in same tier in the next cycle - 0x01 = TPOOL_FLAG_PROMOTE = TP is promoted to higher tier in next cycle of league-tournament - 0x02 = TPOOL_FLAG_DEMOTE = TP is demoted to lower tier in next cycle of league-tournament - 0x04 = TPOOL_FLAG_TD_MANUAL = TD set relegation manually, do not overwrite by auto-filling ########################################################################### ## --- TournamentResult --- Description: Table to store final results (ranks/places of players) of tournament. | Field | Type | Null | Key | Default | Extra | +-----------+----------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | uid | int(11) | NO | | NULL | | | rid | int(11) | NO | MUL | 0 | | | Rating | double | NO | | -9999 | | | Type | tinyint(3) unsigned | NO | | 0 | | | Round | tinyint(3) unsigned | NO | | 1 | | | StartTime | datetime | NO | | 0000-00-00 00:00:00 | | | EndTime | datetime | NO | | 0000-00-00 00:00:00 | | | Result | mediumint(9) | NO | | 0 | | | Rank | smallint(5) unsigned | NO | | 0 | | | Comment | varchar(128) | NO | | | | | Note | varchar(255) | NO | | | | ## Fields: * tid : FK to Tournament.ID * uid : FK to TournamentResult.uid = Players.ID * rid : FK to TournamentResult.rid = TournamentParticipant.ID - can be not existing for ladders if user removed * Rating : current rating of player Players.Rating2 at time of storing result * Type : type of tournament-result - 1 = Crowned King (of Ladder), StartTime = rank-changed-date - 2 = Consecutive Wins (for Ladder), Result = count, StartTime = ladder-join-date - 3 = Pool Winner (for Round-Robin), Rank 1..n; Ranks need not to be unique * Round : round number - always 1 for tournament-ladder * StartTime : start-time for entry * EndTime : end-time for entry - normally date result-entry was added or updated * Result : customary result-value dependent on tournament-type and result-type - can be TournamentLadder.SeqWinsBest for Type=2 (for ladder) * Rank : place of users for T-result - ladder: typically the TournamentLadder.Rank of TP - round-robin: typically the TournamentPool.Rank of TP in final round * Comment : public comment about user of result * Note : private note about user, only for tournament-directors ########################################################################### ## --- TournamentNews --- Description: Table to store tournament news (T-news) of tournament. | Field | Type | Null | Key | Default | Extra | +-------------+---------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | uid | int(11) | NO | | NULL | | | Status | enum('NEW','SHOW','ARCHIVE','DELETE') | NO | MUL | NEW | | | Flags | tinyint(3) unsigned | NO | | 0 | | | Published | datetime | NO | | 0000-00-00 00:00:00 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | | Subject | varchar(255) | NO | | NULL | | | Text | text | NO | | NULL | | ## Fields: * tid : FK to Tournament.ID * uid : FK to TournamentResult.uid = Players.ID * Status : current process-state of the T-news - NEW = T-news (preparing for publishing), not shown to users - SHOW = T-news ready for publishing, shown to users on T-info-page - ARCHIVE = older T-news published, shown to users on T-news-list, but not on T-info-page - DELETE = T-news on this status will be deleted after one week by cron * Flags : bitmask with tournament-news-flags - values defined in 'tournaments/include/tournament_globals.php' as TNEWS_FLAG_..., also see descriptions in 'tournaments/include/tournament_news.php' getFlagsText()-func - 0x01 (=HIDDEN): T-news hidden on T-list/T-info-pages for non-TDs - 0x02 (=PRIVATE): T-news only visible to TPs/TDs/T-owner of T * Subject : subject/title of T-news * Text : main-text of T-news (optional) * Lastchanged : date of last-update of T-news (on Status, Flags, Title, Description) * ChangedBy : handle-list of users, that last changed data-row in format '[last-user] [prev-user] ...' ########################################################################### ## --- TournamentLog --- Description: Table to log actions by TD, TA, owner & users on tournaments. | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | tid | int(11) | NO | MUL | NULL | | | uid | int(11) | NO | | NULL | | | Date | datetime | NO | | 0000-00-00 00:00:00 | | | Type | char(2) | NO | | NULL | | | Object | varchar(16) | NO | | T | | | Action | varchar(16) | NO | | NULL | | | actuid | int(11) | NO | | 0 | | | Message | text | NO | | NULL | | ## Fields: * ID : PK * tid : tournament-id, FK to Tournament.ID * uid : user that performed action, FK to Players.ID * Date : time of performed action * Type : type of user that performed action - TA = tournament-admin - TO = tournament-owner - TD = tournament-director - CR = cron (system) - U = user * Object : object action is performed on - format: _ - type: T, TD, TPR, TRULE, TN, TP, TG, TRES; TL, TLP, TPOINT, TRR, TRND, TPOOL - subtype: Data, Lock, Status, Game, Reg, Rank, News, Props, Pool, Round, User, NextRound - Examples with content: - T_Lock : change T-lock with lock-note - T_Status : change T-status with old -> new status - TRND_Status : change T-Round-status with old -> new status - TL_Seed : seed T-ladder * Action : performed action - Create, Change, AddTime; Remove, Add, Set, Clear, Seed, Start * actuid : user on which action was performed, FK to Players.ID - can be 0 (if action is not applied on user) * Message : free-text describing action ########################################################################### ## --- TournamentPoints --- Description: Table to store point-scheme used to determine ranks for pools of round-robin-tournament | Field | Type | Null | Key | Default | Extra | +-------------------+-----------------------+------+-----+---------------------+-------+ | tid | int(11) | NO | PRI | NULL | | | PointsType | enum('SIMPLE','HAHN') | NO | | SIMPLE | | | Flags | tinyint(3) unsigned | NO | | 1 | | | PointsWon | tinyint(4) | NO | | 2 | | | PointsLost | tinyint(4) | NO | | 0 | | | PointsDraw | tinyint(4) | NO | | 1 | | | PointsForfeit | smallint(6) | NO | | 2 | | | PointsNoResult | smallint(6) | NO | | 1 | | | ScoreBlock | tinyint(3) unsigned | NO | | 10 | | | MaxPoints | smallint(5) unsigned | NO | | 10 | | | PointsResignation | smallint(6) | NO | | 10 | | | PointsTimeout | smallint(6) | NO | | 10 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | | ChangedBy | varchar(54) | NO | | | | ## Fields: * tid : tournament-id, FK to Tournament.ID * PointsType : type of point-scheme to apply - SIMPLE = - HAHN = * Flags : flags for point-scheme - 0x01 = share-max-points for HAHN-type, see http://senseis.xmp.net/?HahnPointingSystem - 0x02 = allow negative points for HAHN-type (for loser) * PointsWon : points added for game-winner (SIMPLE-type only) - valid range: -100..100 * PointsLost : points added for game-loser (SIMPLE-type only) - valid range: -100..100 * PointsDraw : points added for both-players on game-draw (SIMPLE-type only) - valid range: -100..100 * PointsForfeit : points added for game-winner on game-forfeit (both types) - valid range for SIMPLE-type: -100..100 - valid range for HAHN-type: -1000..1000 * PointsNoResult : points added for both-players on no-result-game (both types) - valid range for SIMPLE-type: -100..100 - valid range for HAHN-type: -1000..1000 * ScoreBlock : divisor to divide game-score-result into blocks (HAHN-type only) - valid range: 1..1000 * MaxPoints : maximum of awared points for players (HAHN-type only) - valid range: 1..1000 * PointsResignation : points added for winner on game-resignation (HAHN-type only) - valid range: 1..1000 * PointsTimeout : points added for winner on game-resignation (HAHN-type only) - valid range: 1..1000 * Lastchanged : date of last-update of T-points * ChangedBy : handle-list of users, that last changed data-row in format '[last-user] [prev-user] ...' ########################################################################### ## --- TournamentVisit --- Description: Table to store last "view" of tournament-info-page, used for NEW-counter in main-menu | Field | Type | Null | Key | Default | Extra | +-----------+---------------------+------+-----+---------------------+-------+ | uid | int(11) | NO | PRI | NULL | | | tid | int(11) | NO | PRI | NULL | | | VisitTime | datetime | NO | | 0000-00-00 00:00:00 | | | VisitType | tinyint(3) unsigned | NO | | NULL | | ## Fields: * uid, tid : primary key of this table - Existing DB-entry indicates that user [uid] viewed tournament-info page of T [tid] * VisitTime : date of last-visit of T-info-page * VisitType : type of visit (no default) - 1 = visited T-info page - 2 = marked as read (should be overwritten with 1 if visiting T-info-page)