### ### Description of mysql table: Players, ConfigBoard, ConfigPages ### Here you'll find some infos about the players fields and players configuration. Players is the table containing ALL information and configuration for a player. The Players-table is always read at start of a page into a global variable $player_row to have user-specific details available in page. - Configuration parameters for the DGS-GUI will be split from the Players table when they are not always needed (to reduce data to load from db). # => split 'Z'-configs into main-Players-table, because used everywhere # => new tables: [Z] into ConfigGlobal, [G] into ConfigBoard, [1/2] into ConfigPages # ConfigXYZ: User_ID, ProfileName VC(32) (Profile later possible) ------------------------------------------------- ## Categorization for Fields of Players-table: # Players: # - O = viewable by others # - u = can be modified by user # - A = can be modified by admin # - S = under system control ("regularly" updated) # - = = set once by system # - Z = GUI-config: used for all pages # - G = GUI-config: used for game-page (board) # - 1 = used for one page, 2 = for two, etc. # Fields for user-management: Cat | Field | Type | Null | Key | Default | Extra | ----+------------------+---------------------------------------------+------+-----+---------------------+----------------+ O= | ID | int(11) | NO | PRI | NULL | auto_increment | O A | Type | smallint(5) unsigned | NO | MUL | 0 | | O A | Handle | varchar(16) | NO | UNI | NULL | | u | Password | varchar(41) | NO | | NULL | | uA | Newpassword | varchar(41) | NO | | | | S | Sessioncode | varchar(41) | NO | | | | S | Sessionexpire | datetime | NO | | 0000-00-00 00:00:00 | | OS | Lastaccess | datetime | NO | MUL | 0000-00-00 00:00:00 | | OS | LastQuickAccess | datetime | NO | | 0000-00-00 00:00:00 | | OS | LastMove | datetime | NO | | 0000-00-00 00:00:00 | | O= | Registerdate | date | NO | | NULL | | S | Hits | int(11) | NO | | 0 | | SA | VaultCnt | smallint(5) unsigned | NO | | 0 | | SA | VaultTime | datetime | NO | | 0000-00-00 00:00:00 | | S | Moves | int(11) | NO | | 0 | | S | Activity | int(11) | NO | MUL | 15000 | | OuA | Name | varchar(40) | NO | MUL | | | u | Email | varchar(80) | NO | | | | Ou | Rank | varchar(40) | NO | | | | u | SendEmail | set('ON','MOVE','BOARD','MESSAGE','BY_LAST_ACCESS','BY_LAST_NOTIFY') | NO | | | | S | Notify | enum('NONE','NEXT','NOW','DONE') | NO | | NONE | | S | NotifyFlags | tinyint(3) unsigned | NO | | 0 | | SZ | CountMsgNew | mediumint(9) | NO | | -1 | | SZ | CountFeatNew | smallint(6) | NO | | -1 | | SZ | CountBulletinNew | smallint(6) | NO | | -1 | | SZ | CountTourneyNew | smallint(6) | NO | | -1 | | A | Adminlevel | smallint(5) unsigned | NO | MUL | 0 | | A | AdminOptions | smallint(5) unsigned | NO | MUL | 0 | | A | AdminNote | varchar(100) | NO | | | | Ou | Timezone | varchar(40) | NO | | GMT | | S | Rating | double | YES | | NULL | | OS | Rating2 | double | YES | MUL | NULL | | S | RatingMin | double | YES | | NULL | | S | RatingMax | double | YES | | NULL | | S | InitialRating | double | NO | | -9999 | | S | RatingStatus | enum('NONE','INIT','RATED') | NO | | NONE | | Ou | Open | varchar(60) | NO | | | | u | Lang | varchar(20) | NO | | C | | OSA | VacationDays | float | NO | MUL | 14 | | OSA | OnVacation | float | NO | MUL | 0 | | OS | Running | smallint(5) unsigned | NO | | 0 | | OS | Finished | mediumint(8) unsigned | NO | | 0 | | OS | RatedGames | mediumint(8) unsigned | NO | | 0 | | OS | Won | mediumint(8) unsigned | NO | | 0 | | OS | Lost | mediumint(8) unsigned | NO | | 0 | | OS | GamesMPG | smallint(5) unsigned | NO | | 0 | | OS | GamesWeaker | mediumint(8) unsigned | NO | | 0 | | A | Translator | varchar(80) | NO | | | | S | IP | varchar(16) | NO | | | | S | Browser | varchar(150) | NO | | | | Ou | Country | char(2) | NO | MUL | | | A | BlockReason | text | NO | | NULL | | S | ForumReadTime | datetime | NO | | 0000-00-00 00:00:00 | | S | ForumReadNew | tinyint(4) | NO | | 0 | | # Fields for (global) GUI-config in Players-table: Zu | UserFlags | int(11) | NO | | 0 | | Zu | SkinName | varchar(32) | NO | | | | Zu | MenuDirection | enum('VERTICAL','HORIZONTAL') | NO | | VERTICAL | | Zu | TableMaxRows | smallint(5) unsigned | NO | | 20 | | Zu | Button | tinyint(4) | NO | | 0 | | Zu | UserPicture | varchar(48) | NO | | | | Zu | NextGameOrder | enum('LASTMOVED_OF','LASTMOVED_NF','MOVES','PRIO','TIMELEFT') | NO | | LASTMOVED_OF | | Zu | SkipBulletin | tinyint(3) unsigned | NO | | 4 | | Zu | RejectTimeoutWin | tinyint(4) | NO | | -1 | | Zu | ThumbnailSize | tinyint(3) unsigned | NO | | 7 | | * CountTourneyNew : number of tournaments that are "new" to user - -1 = need reload for count of tournaments on (REG,PAIR,PLAY status) that are new to user - NOTE: "new" is not further described here as its semantics may be detailed to only include tournaments, that user can actually join (e.g. by rating-range, max participants, register end-time, etc) - NOTE: auto-reload done if last-access was older than 4 weeks # Fields for board-related GUI-config in ConfigBoard-table (category = Gu): | Field | Type | Null | Key | Default | Extra | +------------------+---------------------------------------------+------+-----+---------+-------+ | User_ID | int(11) | NO | PRI | NULL | | | Stonesize | tinyint(3) unsigned | NO | | 25 | | | Woodcolor | tinyint(3) unsigned | NO | | 1 | | | BoardFlags | tinyint(3) unsigned | NO | | 0 | | | Boardcoords | smallint(5) unsigned | NO | | 31 | | | MoveNumbers | smallint(5) unsigned | NO | | 0 | | | MoveModulo | smallint(5) unsigned | NO | | 0 | | | NotesSmallHeight | tinyint(3) unsigned | NO | | 25 | | | NotesSmallWidth | tinyint(3) unsigned | NO | | 30 | | | NotesSmallMode | enum('RIGHT','BELOW','RIGHTOFF','BELOWOFF') | NO | | RIGHT | | | NotesLargeHeight | tinyint(3) unsigned | NO | | 25 | | | NotesLargeWidth | tinyint(3) unsigned | NO | | 30 | | | NotesLargeMode | enum('RIGHT','BELOW','RIGHTOFF','BELOWOFF') | NO | | RIGHT | | | NotesCutoff | tinyint(3) unsigned | NO | | 13 | | * Woodcolor : int - 1..5 = textured wood-images - 11..16 = uni-colored "wood", see $woodbgcolors-array with wood-colors * BoardFlags : int - see consts BOARDFLAG_... # Fields for pages-related GUI-config in ConfigPages-table (category = 1u): | Field | Type | Null | Key | Default | Extra | +---------------------------------+---------------------+------+-----+---------+-------+ | User_ID | int(11) | NO | PRI | NULL | | | StatusFlags | smallint(6) | NO | | 3 | | | StatusFolders | varchar(40) | NO | | | | | ForumFlags | tinyint(3) unsigned | NO | | 8 | | | ColumnsStatusGames | int(11) | NO | | -1 | | | ColumnsStatusTournaments | int(11) | NO | | -1 | | | ColumnsWaitingroom | int(11) | NO | | -1 | | | ColumnsUsers | int(11) | NO | | -1 | | | ColumnsOpponents | int(11) | NO | | -1 | | | ColumnsContacts | int(11) | NO | | -1 | | | ColumnsGamesRunningAll | int(11) | NO | | -1 | | | ColumnsGamesRunningAll2 | int(11) | NO | | -1 | | | ColumnsGamesRunningUser | int(11) | NO | | -1 | | | ColumnsGamesRunningUser2 | int(11) | NO | | -1 | | | ColumnsGamesFinishedAll | int(11) | NO | | -1 | | | ColumnsGamesFinishedAll2 | int(11) | NO | | -1 | | | ColumnsGamesFinishedUser | int(11) | NO | | -1 | | | ColumnsGamesFinishedUser2 | int(11) | NO | | -1 | | | ColumnsGamesObserved | int(11) | NO | | -1 | | | ColumnsGamesObserved2 | int(11) | NO | | -1 | | | ColumnsGamesObservedAll | int(11) | NO | | -1 | | | ColumnsGamesObservedAll2 | int(11) | NO | | -1 | | | ColumnsBulletinList | int(11) | NO | | -1 | | | ColumnsFeatureList | int(11) | NO | | -1 | | | ColumnsTournaments | int(11) | NO | | -1 | | | ColumnsTournamentParticipants | int(11) | NO | | -1 | | | ColumnsTDTournamentParticipants | int(11) | NO | | -1 | | | ColumnsTournamentResults | int(11) | NO | | -1 | | | ColumnsTournamentLadderView | int(11) | NO | | -1 | | | ColumnsTournamentPoolView | int(11) | NO | | -1 | | ------------------------------------------------- # Description of fields: * Lastaccess : timestamp when user was "last on site" or last used quick-suite - field is not set when user uses one of the RSS/WAP/quick-features - field is updated when user access web-site OR accesses the quick-suite (quick_play.php or quick_do.php) To differ web-access and quick-suite-access, there is a separate field 'LastQuickAccess': - if 'Lastaccess' > 'LastQuickAccess', then it was a web-site access - if 'Lastaccess' == 'LastQuickAccess', then it was a quick-suite access - if 'Lastaccess' < 'LastQuickAccess' (should not happen) * LastQuickAccess : timestamp when user last used the quick-suite * Notify : field indicating if there is something to notify - if there's something to notify, user is notified dependent on SendEmail-field - Values: - NONE : nothing to notify; normally set if user is logging in via Web-GUI, so user can see new messages by himself - NEXT : something is there to notify, e.g. new message or game-move - NOW : notify-state that is next picked up to send emails in halfhourly-cron (set to avoid race-condition in cron-script as well) - DONE : stops further notifications till the players next visit; set after last notification per email * NotifyFlags : bit-field indicating categories to check for building mail-notifications - NOTE: see consts NOTIFYFLAG_... - NOTE about mail-notifications: for Players.Notify=NEXT always both, games-to-move and new-messages are checked. Checking for new messages almost always is a slow query (7-12 on average); even games-loading (including board) only last <1 sec normally. Therefore introduced notify-flags that indicate if new-messages (or other new data) must be checked or not on collecting data for mail-notifications. - 0x01 : if bit set, mail-notifications must check for new messages; if not set, no check for new messages needed - 0x02 : if bit set, collect entries to notify since user's last-notified time; otherwise by Players.Lastaccess (=any web-access + quick-suite-access) * SendEmail : user-configuration controlling what notifications to sent per mail - ON : if set, mail-notifications are created at all and being sent out. if none of MOVE,BOARD,MESSAGE is set, notifications will only contain that there have been new message and/or game-moves (without mentioning the entries) - MOVE : mail-notifications contain game-moves since last web-access - BOARD : game-moves contain ASCII-board as well - MESSAGE : mail-notifications contain new messages since last web-access * Running : number of running games (including MP-games) - MP-games in SETUP-mode are not counted with Running, see GamesMPG-field * GamesMPG : number of MP-games for user in SETUP-mode, used to optimize status-loading - if > 0 : load and show MP-games user is joined or has reservation in - +1 if user creates new MP-game as game-master - +1 if invite-reservation as game-player for user is added by game-master - +1 if user joins waiting-room-reservation - -1 if invite-reservation is removed by game-master - -1 if invite-reservation is rejected by invited player - -1 if joined user is removed by game-master - -1 for all joined users if setup-MP-game is started - no change if invite-reservation is accepted by user * GamesWeaker : number of (non-MP) games played with weaker players - badges of honor awarded for hero-ratio in certain ranges: - golden badge: ratio >= 61.8% (= 1/golden_ratio) - silver badge: ratio >= 50% - bronze badge: ratio >= 32.8% (= 100% - 1/golden_ratio) * RejectTimeoutWin : - -1 = no rejection of win-by-timeout (default) - 0 .. 127 = number of days that must pass since last-move-date of opponent to enable rejecting a win-by-timeout by making the game unrated. * UserFlags : user-flags - NOTE: for constants see USERFLAG_... - 0x0001 = set to enable JavaScript-usage on the web-site - 0x0002 = set to indicate, that user has SendEmail>'' (notification on), but has no or an invalid email - 0x0004 = set to indicate, that an email-verification is underway (see Verification-table) - 0x0008 = set to indicate, that an account-activation is underway (see Verification-table) - 0x0010 = set to indicate, that the Players.Email is verified by email-verification, this may be used to later differ accounts with unverified emails from accounts with verified emails * ThumbnailSize : - stone-size to be used for game-thumbnails ------------------------------------------------- TABLE Players: ID int(11) NOT NULL auto_increment, -- numerical user-id 'uid' Handle varchar(16) NOT NULL default '', -- textual user-handle Password varchar(41) default NULL, -- crypted password, created with mysql password('password') Lastaccess datetime default NULL, -- on every viewing of a page on DGS, this is updated to the current-time LastMove datetime default NULL, -- updated to current-time when players moves in a game Registerdate date default NULL, -- datetime when user first created the account on DGS Hits int(11) default '0', -- value increased by one, when visiting or reload a page (is_logged_in-check) Moves int(11) default '0', -- counter for made moves Activity double NOT NULL default '15', -- acitivity points, certain activities on the server adds to this (page-visit, making-move); value is decreased by cron-scripts (half-time) Name varchar(40) NOT NULL default '', -- full name of player Email varchar(80) default NULL, -- email of player Rank varchar(40) default NULL, -- textual rank-information (free text, no meaning for rating) Adminlevel int(11) NOT NULL default '0', -- bit-mask for different admin-tasks, -1=full-fledged-admin Rating2 double default NULL, -- current rating (EGF-rating in range 100 - ), used for calculations to start game RatingMin double default NULL, -- minimal rating (for rating-graph), + ?? RatingMax double default NULL, -- maximum rating (for rating-graph), + ?? RatingStatus enum('INIT','RATED') default NULL, -- INIT=no-rating-set, RATED=rating-set Running int(11) NOT NULL default '0', -- number of running games for player Finished int(11) NOT NULL default '0', -- number of finished games for player RatedGames int(11) NOT NULL default '0', -- number of finished rated games for player Won int(11) NOT NULL default '0', -- number of won rated games for player Lost int(11) NOT NULL default '0', -- number of lost rated games for player Country char(2) default NULL, -- CONF: country for player, for codes see 'include/countries.php' TABLE ConfigBoard: Stonesize tinyint(3) unsigned NOT NULL default '25', -- CONF: stone-size used to select images ?? SkinName varchar(32) NOT NULL default '', -- CONF: used skin for CSS TABLE ConfigPages: ColumnsUsers int(11) signed NOT NULL default '-1', -- bit-mask representing shown columns for page 'users.php' ColumnsStatusGames int(11) signed NOT NULL default '-1', -- bit-mask representing shown columns for page 'status.php' ########################################################################### ## --- IpStats --- Description: Table used for IP and user-id to track too-much-requests. | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------------------+-------+ | uid | int(11) | NO | PRI | 0 | | | Page | char(4) | NO | PRI | | | | IP | varchar(16) | NO | PRI | NULL | | | Counter | int(10) unsigned | NO | | 0 | | | Lastchanged | datetime | NO | | 0000-00-00 00:00:00 | | # Fields: * uid : FK Players.ID - can be 0, if unknown because not logged-in * Page : requested protocol/page - used values: RSS, WAP, WEB, QST (quick-status), QDO (quick-do), QPL (quick-play) * IP : remove ip-address of user * Counter : counter how often uid/IP is used to connect to site * Lastchanged : date of last access with uid/IP ########################################################################### ## --- Contribution --- Description: Table used to store contributions for user-id to be shown on people-page. Multiple entries per user-id allowed. | Field | Type | Null | Key | Default | Extra | +----------+------------------------------------+------+-----+---------------------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | uid | int(11) | NO | | NULL | | | Category | enum('FOUNDER','DEV_MAIN','DEV_RECRUIT','DEV_CLIENT','OTHER') | NO | | NULL | | | Comment | varchar(255) | NO | | | | | Created | datetime | NO | | 0000-00-00 00:00:00 | | | Updated | datetime | NO | | 0000-00-00 00:00:00 | | # Fields: * uid : FK Players.ID * Category : contribution-categories - used values: defined consts CONTRIB_CAT_... in 'include/db/contribution.php' - FOUNDER = founder of Dragon Go Server - DEV_MAIN = main developer (server-code) - DEV_RECRUIT = recruit developer (server-code) - DEV_CLIENT = client developer (client-code, e.g. notifier, mobile-apps, bots) - OTHER = miscellaneous contributions * Comment : comment about a users contribution * Created : creation-date of entry * Updated : date of last update of entry