diff options
| author | Tor Andersson <tor@ccxvii.net> | 2024-07-23 13:48:07 +0200 |
|---|---|---|
| committer | Tor Andersson <tor@ccxvii.net> | 2024-07-23 13:48:10 +0200 |
| commit | c52ca4b709a8752d870a9123b46cfd3764c608a7 (patch) | |
| tree | e18ffd7959a22711929a4f385c0d702b66a58d5f /public/docs/database.html | |
| parent | 611b9aa100d1135f04cde572115a5ea1f680ad72 (diff) | |
| download | server-c52ca4b709a8752d870a9123b46cfd3764c608a7.tar.gz | |
Start adding developer documentation.
Diffstat (limited to 'public/docs/database.html')
| -rw-r--r-- | public/docs/database.html | 206 |
1 files changed, 206 insertions, 0 deletions
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 @@ +<!doctype html> +<title>Database Overview</title> +<link rel="stylesheet" href="style.css"> +<body> +<article> + +<h1> +Database Overview +</h1> + +<p> +The database uses the following schemas, somewhat simplified and redacted to highlight the important bits. + +<h2>Users</h2> + +<p> +The user table is pretty simple, it just holds the user account information. +Passwords are hashed and salted with SHA-256. + +<pre> +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 +); +</pre> + +<p> +The login session cookie is a 48-bit random number. + +<pre> +create table logins ( + sid integer primary key, + user_id integer, + expires real -- julianday +); +</pre> + +<p> +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. + +<pre> +create table webhooks ( + user_id integer primary key, + url text, + format text, + prefix text, + error text +); +</pre> + +<p> +The contact list keeps track of friends (positive relation) and blacklisted users (negative relation). + +<pre> +create table contacts ( + me integer, + you integer, + relation integer, + primary key (me, you) +); +</pre> + + +<h2>Modules</h2> + +<p> +The game modules to load are registered in the titles table. The title_id must match the directory name for the module. + +<pre> +create table titles ( + title_id text primary key, + title_name text, + bgg integer, + is_symmetric boolean +); +</pre> + +<h2>Games</h2> + +<p> +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. + +<pre> +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 ) +); +</pre> + +<p> +The players table connects users to the games they play. + +<pre> +create table players ( + game_id integer, + role text, + user_id integer, + is_invite integer, + primary key (game_id, role) +); +</pre> + +<p> +The game state is represented by a JSON blob. + +<pre> +create table game_state ( + game_id integer primary key, + state text +); +</pre> + +<p> +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. + +<pre> +create table game_replay ( + game_id integer, + replay_id integer, + role text, + action text, + arguments json, + primary key (game_id, replay_id) +); +</pre> + +<p> +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. + +<pre> +create table game_snap ( + game_id integer, + snap_id integer, + replay_id integer, + state text, + primary key (game_id, snap_id) +); +</pre> + +<p> +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. + +<pre> +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) +); +</pre> + +<h2>Other tables</h2> + +<p> +There are several other tables to deal with password reset tokens, email +notifications, messages, and the forum. + +See the full +<a href="https://git.rally-the-troops.com/common/server/tree/schema.sql">schema.sql</a> +for more details on these. |