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
|
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 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;
|
