1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
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.
|
