DIR : /home/kozerus/public_html/go/dgs0/specs/db/table-Players.txt
/home/kozerus/public_html/go/dgs0/specs/db
###
### 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
//
koh5_pano
Drag mouse to navigate.
Navigation
- Left/Right Mouse drag: Changes camera heading.
- Up/Sown Mouse drag: Changes camera pitch.
- Scroll wheel: Changes camera field of view.
- I-Key: Displays Info panel with canvas size, image size and FPS.
17.Aug.2010, Martin Wengenmayer