Data Base Tables

From BOARD18 Project WIKI
Jump to: navigation, search
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:

  1. The Auth Tokens Table
  2. The Box Table
  3. The Game Table
  4. The Game Link Table
  5. The Game Player Table
  6. The Players Table

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
email 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