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
|
CREATE TABLE IF NOT EXISTS users (
user_id INTEGER PRIMARY KEY,
name TEXT UNIQUE COLLATE NOCASE,
mail TEXT UNIQUE COLLATE NOCASE,
password TEXT,
salt TEXT,
ctime TIMESTAMP,
cip TEXT,
atime TIMESTAMP,
aip TEXT,
notifications INTEGER
);
CREATE TABLE IF NOT EXISTS notifications (
user_id INTEGER,
game_id INTEGER,
time TIMESTAMP,
UNIQUE ( user_id, game_id )
);
CREATE TABLE IF NOT EXISTS tokens (
user_id INTEGER PRIMARY KEY,
token TEXT,
time TIMESTAMP
);
CREATE TABLE IF NOT EXISTS blacklist_ip ( ip TEXT PRIMARY KEY );
CREATE TABLE IF NOT EXISTS blacklist_mail ( mail TEXT PRIMARY KEY );
CREATE TABLE IF NOT EXISTS titles (
title_id TEXT UNIQUE,
title_name TEXT,
bgg INTEGER
);
CREATE TABLE IF NOT EXISTS roles (
title_id TEXT,
role TEXT,
UNIQUE ( title_id, role )
);
CREATE TABLE IF NOT EXISTS games (
game_id INTEGER PRIMARY KEY,
title_id TEXT,
scenario TEXT,
owner_id INTEGER,
private BOOLEAN,
random BOOLEAN,
description TEXT,
ctime TIMESTAMP,
mtime TIMESTAMP,
status INTEGER,
result TEXT,
active TEXT,
state TEXT
);
CREATE TABLE IF NOT EXISTS chats (
game_id INTEGER PRIMARY KEY,
time TIMESTAMP,
chat TEXT
);
CREATE TABLE IF NOT EXISTS players (
user_id INTEGER,
game_id INTEGER,
role TEXT,
UNIQUE ( game_id, role )
);
DROP TRIGGER IF EXISTS no_part_on_active_game;
CREATE TRIGGER no_part_on_active_game BEFORE DELETE ON players
BEGIN
SELECT CASE
WHEN ( SELECT status FROM games WHERE game_id = old.game_id ) > 0
THEN RAISE(ABORT, "Cannot remove players from started games.")
END;
END;
DROP TRIGGER IF EXISTS no_join_on_active_game;
CREATE TRIGGER no_join_on_active_game BEFORE INSERT ON players
BEGIN
SELECT CASE
WHEN ( SELECT status FROM games WHERE game_id = new.game_id ) > 0
THEN RAISE(ABORT, "Cannot add players to started games.")
END;
END;
DROP TRIGGER IF EXISTS must_be_valid_role;
CREATE TRIGGER must_be_valid_role BEFORE INSERT ON players
BEGIN
SELECT CASE
WHEN ( SELECT COUNT(*) FROM roles, games WHERE
roles.title_id = games.title_id AND
games.game_id = new.game_id AND
roles.role = new.role ) <> 1
AND new.role <> 'Random 1'
AND new.role <> 'Random 2'
AND new.role <> 'Random 3'
AND new.role <> 'Random 4'
AND new.role <> 'Random 5'
AND new.role <> 'Random 6'
THEN RAISE(ABORT, "Invalid role for that title.")
END;
END;
DROP TRIGGER IF EXISTS purge_players;
CREATE TRIGGER purge_players AFTER DELETE ON games
BEGIN
DELETE FROM players WHERE game_id = old.game_id;
DELETE FROM notifications WHERE game_id = old.game_id;
DELETE FROM chats WHERE game_id = old.game_id;
END;
DROP VIEW IF EXISTS player_view;
DROP VIEW IF EXISTS player_list_view;
DROP VIEW IF EXISTS game_view;
CREATE VIEW player_view AS
SELECT players.game_id
, players.role AS role
, players.user_id AS user_id
, users.name AS user_name
FROM players
JOIN users ON players.user_id = users.user_id
;
CREATE VIEW player_list_view AS
SELECT game_id
, group_concat(name, ', ') AS player_names
, COUNT(DISTINCT user_id) AS user_count
, COUNT(user_id) AS role_count
FROM players
JOIN users USING ( user_id )
GROUP BY game_id
;
CREATE VIEW game_view AS
SELECT games.game_id
, games.title_id
, titles.title_name
, games.scenario
, games.owner_id
, owner.name AS owner_name
, players.player_names
, players.user_count = 1 AS is_solo
, players.user_count <> players.role_count AS is_shared
, games.private
, games.random
, games.description
, games.ctime
, games.mtime
, games.status
, games.result
, games.active AS active_role
, active.user_id AS active_id
, active.user_name AS active_name
-- , games.state
FROM games
JOIN titles USING ( title_id )
JOIN users AS owner ON games.owner_id = owner.user_id
LEFT JOIN player_list_view AS players USING ( game_id )
LEFT JOIN player_view AS active ON games.game_id = active.game_id AND games.active = active.role
;
|
