From c52ca4b709a8752d870a9123b46cfd3764c608a7 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Tue, 23 Jul 2024 13:48:07 +0200 Subject: Start adding developer documentation. --- public/docs/database.html | 206 ++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 206 insertions(+) create mode 100644 public/docs/database.html (limited to 'public/docs/database.html') diff --git a/public/docs/database.html b/public/docs/database.html new file mode 100644 index 0000000..f4ec4c2 --- /dev/null +++ b/public/docs/database.html @@ -0,0 +1,206 @@ + +Database Overview + + +
+ +

+Database Overview +

+ +

+The database uses the following schemas, somewhat simplified and redacted to highlight the important bits. + +

Users

+ +

+The user table is pretty simple, it just holds the user account information. +Passwords are hashed and salted with SHA-256. + +

+create table users (
+	user_id integer primary key,
+	name text unique collate nocase,
+	mail text unique collate nocase,
+	notify integer, -- email notifications
+	password text, -- hashed & salted
+	salt text
+);
+
+ +

+The login session cookie is a 48-bit random number. + +

+create table logins (
+	sid integer primary key,
+	user_id integer,
+	expires real -- julianday
+);
+
+ +

+Webhook notification settings are kept in the webhooks table. +The error column keeps any error message such as timeouts or HTTP failure statuses. +It is null if the hook is operational. + +

+create table webhooks (
+	user_id integer primary key,
+	url text,
+	format text,
+	prefix text,
+	error text
+);
+
+ +

+The contact list keeps track of friends (positive relation) and blacklisted users (negative relation). + +

+create table contacts (
+	me integer,
+	you integer,
+	relation integer,
+	primary key (me, you)
+);
+
+ + +

Modules

+ +

+The game modules to load are registered in the titles table. The title_id must match the directory name for the module. + +

+create table titles (
+	title_id text primary key,
+	title_name text,
+	bgg integer,
+	is_symmetric boolean
+);
+
+ +

Games

+ +

+Each game session uses a handful of tables. +The main table holds the status (open/active/finished), setup information (scenario, options), whose turn it is (active), and the final result. + +

+create table games (
+	game_id integer primary key,
+	status integer,
+
+	title_id text,
+	scenario text,
+	options text,
+
+	player_count integer,
+	join_count integer,
+	invite_count integer,
+	user_count integer,
+
+	owner_id integer,
+	notice text,
+	pace integer,
+	is_private boolean,
+	is_random boolean,
+	is_match boolean,
+
+	ctime datetime,
+	mtime datetime,
+	moves integer,
+	active text,
+	result text,
+
+	is_opposed boolean generated as ( user_count = join_count and join_count > 1 ),
+	is_ready boolean generated as ( player_count = join_count and invite_count = 0 )
+);
+
+ +

+The players table connects users to the games they play. + +

+create table players (
+	game_id integer,
+	role text,
+	user_id integer,
+	is_invite integer,
+	primary key (game_id, role)
+);
+
+ +

+The game state is represented by a JSON blob. + +

+create table game_state (
+	game_id integer primary key,
+	state text
+);
+
+ +

+Each action taken in stored in the game_replay log. This is primarily used for +the detailed "Sherlock" replay view, but is also used to patch running games when +fixing bugs. + +

+create table game_replay (
+	game_id integer,
+	replay_id integer,
+	role text,
+	action text,
+	arguments json,
+	primary key (game_id, replay_id)
+);
+
+ +

+Whenever who is active changes, we take a snapshot of the game state +so we can provide the coarse turn-by-turn rewind view that is available when +playing. This table is also used when rewinding games. The replay_id syncs each +snapshot with the corresponding action in the game_replay table. + +

+create table game_snap (
+	game_id integer,
+	snap_id integer,
+	replay_id integer,
+	state text,
+	primary key (game_id, snap_id)
+);
+
+ +

+Game chat is kept in another table, and there's also a table to track whether a +user has any unread in-game chat messages. + +

+create table game_chat (
+	game_id integer,
+	chat_id integer,
+	user_id integer,
+	time datetime,
+	message text,
+	primary key (game_id, chat_id)
+);
+
+create table unread_chats (
+	user_id integer,
+	game_id integer,
+	primary key (user_id, game_id)
+);
+
+ +

Other tables

+ +

+There are several other tables to deal with password reset tokens, email +notifications, messages, and the forum. + +See the full +schema.sql +for more details on these. -- cgit v1.2.3