### ### 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') | 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 | | 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 | | Ou | Nightstart | smallint(6) | NO | | 22 | | S | ClockUsed | smallint(6) | NO | | 22 | | S | ClockChanged | enum('N','Y') | NO | | Y | | 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 | | 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','MOVES','PRIO','TIMELEFT') | NO | | LASTMOVED | | Zu | SkipBulletin | tinyint(3) unsigned | NO | | 4 | | Zu | RejectTimeoutWin | tinyint(4) | NO | | -1 | | Zu | ThumbnailSize | tinyint(3) unsigned | NO | | 7 | | # 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 | | # 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 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 * SendEmail : user-configuration controlling what notifications to sent per mail - ON : if set, mail-notifications are created at all and being sent out - 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 * 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 games for player Lost int(11) NOT NULL default '0', -- number of lost 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