Data Base Tables - Revision history

Rich at 14:13, 30 January 2021

2021-01-30T14:13:10Z

← Older revision		Revision as of 14:13, 30 January 2021
Line 5:		Line 5:
	# The Box Table		# The Box Table
	# The Game Table		# The Game Table
			# The Game Snapshot Table
	# The Game Link Table		# The Game Link Table
	# The Game Player Table		# The Game Player Table

Rich: added link to Optimistic database locking

2020-05-16T20:45:17Z

added link to Optimistic database locking

← Older revision		Revision as of 20:45, 16 May 2020
Line 8:		Line 8:
	# 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==

Rich at 17:07, 23 October 2017

2017-10-23T17:07:44Z

← Older revision		Revision as of 17:07, 23 October 2017
Line 5:		Line 5:
	# The Box Table		# The Box Table
	# The Game Table		# The Game Table
	~~# The Game Check Point Table~~
	# The Game Link Table		# The Game Link Table
	# The Game Player Table		# The Game Player Table

Rich: /* The Auth Tokens Table */ fixed

2017-08-04T14:53:53Z

The Auth Tokens Table: fixed

← Older revision		Revision as of 14:53, 4 August 2017
Line 20:		Line 20:
	\|token_id\|\|Int (11)\|\|No\|\|		\|token_id\|\|Int (11)\|\|No\|\|
	\|-		\|-
	\|selector\|\|Varchar (16)\|\|~~Yes~~\|\|~~NULL~~		\|selector\|\|Varchar (16)\|\|No\|\|
	\|-		\|-
	\|hashedValidator\|\|Char (64)\|\|Yes\|\|NULL		\|hashedValidator\|\|Char (64)\|\|Yes\|\|NULL
Line 26:		Line 26:
	\|player_id\|\|Int (11)\|\|No\|\|		\|player_id\|\|Int (11)\|\|No\|\|
	\|-		\|-
	\|expires\|\|Bigint (20)\|\|~~Yes~~\|\|~~NULL~~		\|expires\|\|Bigint (20)\|\|No\|\|
	\|}		\|}

Rich: added Design Document Index

2017-08-01T13:59:01Z

added Design Document Index

← Older revision		Revision as of 13:59, 1 August 2017
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:

Rich: initial contents

2017-08-01T13:54:50Z

initial contents

New page

__NOTOC__
{{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:

# The Auth Tokens Table
# The Box Table
# The Game Table
# The Game Check Point Table
# The Game Link Table
# The Game Player Table
# 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. <br>
The table layout is shown below. This table is used to control player access to the application.

{| class="wikitable"
!Column!!Type!!Null!!Default
|-
|token_id||Int (11)||No||
|-
|selector||Varchar (16)||Yes||NULL
|-
|hashedValidator||Char (64)||Yes||NULL
|-
|player_id||Int (11)||No||
|-
|expires||Bigint (20)||Yes||NULL
|}

== 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.

{| class="wikitable"
!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.

{| class="wikitable"
!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.

{| class="wikitable"
!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.

{| class="wikitable"
!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.

{| class="wikitable"
!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.

{| class="wikitable"
!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
|}

[[Category:Design]]