diff options
| author | Tor Andersson <tor@ccxvii.net> | 2025-04-24 12:48:51 +0200 |
|---|---|---|
| committer | Tor Andersson <tor@ccxvii.net> | 2025-04-25 16:06:05 +0200 |
| commit | ed2361980b455d1825d811670f329cbcf5624927 (patch) | |
| tree | a1e620490bf305635c3a0f22a6f695e22c14ff2f /public/docs/database.html | |
| parent | 71a98cbbe0657880cfb1d81962786188b009fc5c (diff) | |
| download | server-ed2361980b455d1825d811670f329cbcf5624927.tar.gz | |
Move docs to Markdown and use markdown renderer in server.
Diffstat (limited to 'public/docs/database.html')
| -rw-r--r-- | public/docs/database.html | 207 |
1 files changed, 0 insertions, 207 deletions
diff --git a/public/docs/database.html b/public/docs/database.html deleted file mode 100644 index 27ae6d9..0000000 --- a/public/docs/database.html +++ /dev/null @@ -1,207 +0,0 @@ -<!doctype html> -<meta name="viewport" content="width=device-width"> -<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. |