Difference between revisions of "Data Base Tables"
(initial contents) |
m |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
__NOTOC__ | __NOTOC__ {{Design Document Index}} {{ReleaseNotice|1.5}} | ||
{{ReleaseNotice|1.5}} | |||
BOARD18 uses MySQL for its data management. A single database, with a data base name of BOARD18, is used. The database consists of seven tables: | BOARD18 uses MySQL for its data management. A single database, with a data base name of BOARD18, is used. The database consists of seven tables: | ||
| Line 6: | Line 5: | ||
# The Box Table | # The Box Table | ||
# The Game Table | # The Game Table | ||
# The Game | # The Game Snapshot Table | ||
# The Game Link Table | # The Game Link Table | ||
# The Game Player Table | # The Game Player Table | ||
# The Players Table | # The Players Table | ||
Updates to the Game Table in this database occur frequently. Because of this they are protected using [[Optimistic database locking]]. | |||
== The Auth Tokens Table== | == The Auth Tokens Table== | ||
| Line 21: | Line 22: | ||
|token_id||Int (11)||No|| | |token_id||Int (11)||No|| | ||
|- | |- | ||
|selector||Varchar (16)|| | |selector||Varchar (16)||No|| | ||
|- | |- | ||
|hashedValidator||Char (64)||Yes||NULL | |hashedValidator||Char (64)||Yes||NULL | ||
| Line 27: | Line 28: | ||
|player_id||Int (11)||No|| | |player_id||Int (11)||No|| | ||
|- | |- | ||
|expires||Bigint (20)|| | |expires||Bigint (20)||No|| | ||
|} | |} | ||
Latest revision as of 06:13, 30 January 2021
| Design Document edit | |
| This page has been updated to comply with Release 1.5.x of BOARD18. |
BOARD18 uses MySQL for its data management. A single database, with a data base name of BOARD18, is used. The database consists of seven tables:
- The Auth Tokens Table
- The Box Table
- The Game Table
- The Game Snapshot Table
- The Game Link Table
- The Game Player Table
- The Players Table
Updates to the Game Table in this database occur frequently. Because of this they are protected using Optimistic database locking.
The Auth Tokens Table
The “auth_tokens” table contains a row for each unexpired LTPAlocal cookie created during login processing. These rows are deleted when the cookies are deleted, so this is a volatile table.
The table layout is shown below. This table is used to control player access to the application.
| Column | Type | Null | Default |
|---|---|---|---|
| token_id | Int (11) | No | |
| selector | Varchar (16) | No | |
| hashedValidator | Char (64) | Yes | NULL |
| player_id | Int (11) | No | |
| expires | Bigint (20) | No |
The Box Table
Each row in the table named “box” contains a game box. The table layout is shown below. The json_text field contains a stringified JSON game box object as described in section 5.2.1.
| Column | Type | Null | Default |
|---|---|---|---|
| box_id | Int (11) | No | |
| bname | Varchar (25) | No | |
| version | Varchar (25) | Yes | NULL |
| create_date | Timestamp | Yes | NULL |
| activity_date | Timestamp | No | CURRENT_TIMESTAMP |
| author | Varchar (25) | No | |
| status | Varchar (10) | No | 'Active' |
| json_text | MediumText | Yes | NULL |
The Game Table
The “game” table contains a row for each game that is being tracked by BOARD18. The table layout is shown below. The json_text field contains a stringified JSON game session object as described in section 5.2.2.
| Column | Type | Null | Default |
|---|---|---|---|
| game_id | Int (11) | No | |
| gname | Varchar (25) | No | |
| start_date | Timestamp | Yes | NULL |
| activity_date | Timestamp | No | CURRENT_TIMESTAMP |
| update_counter | Int (11) | No | 0 |
| last_updater | Varchar (45) | No | |
| box_id | Int (11) | No | |
| cp_count | Int (11) | No | 0 |
| status | Varchar (10) | No | 'Active' |
| json_text | MediumText | Yes | NULL |
The Game Snapshot Table
The “game_snap” table contains a row for each snapshot taken for each game that is being tracked by BOARD18. The table layout is shown below. The json_text field contains a stringified JSON game session object as described in section 5.2.2.
| Column | Type | Null | Default |
|---|---|---|---|
| game_id | Int (11) | No | |
| cp_id | Int (11) | No | |
| cp_date | Timestamp | No | CURRENT_TIMESTAMP |
| player | Varchar (45) | No | |
| last_updater | Varchar (45) | No | |
| game_round | Varchar (32) | No | |
| json_text | MediumText | Yes | NULL |
The Game Link Table
The “game_link” table contains a row for each link that will be available to each game that is being tracked by BOARD18. The table layout is shown below.
| Column | Type | Null | Default |
|---|---|---|---|
| game_id | Int (11) | No | |
| link_name | Varchar (30) | No | |
| link_url | Varchar (100) | Yes | NULL |
| activity_date | Timestamp | No | CURRENT_TIMESTAMP |
The Game Player Table
The “game_player” table contains a row for each player of each active game that is being tracked by BOARD18. The table layout is shown below. This table is the link between players and the game sessions that they are playing.
| Column | Type | Null | Default |
|---|---|---|---|
| game_id | Int (11) | No | |
| player_id | Int (11) | No | |
| status | Varchar (10) | No | 'P' |
The Players Table
The “players” table contains a row for each player that has a login ID in BOARD18. The table layout is shown below. This table is used to control player access to the application.
| Column | Type | Null | Default |
|---|---|---|---|
| player_id | Int (11) | No | |
| firstname | Varchar (25) | No | |
| lastname | Varchar (25) | Yes | NULL |
| Varchar (254) | No | ||
| login | Varchar (16) | No | |
| passwd | Varchar (64) | No | |
| create_date | Timestamp | No | CURRENT_TIMESTAMP |
| level | Varchar (10) | No | player |
| changeit | Tinyint (1) | No | 0 |