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
|
CREATE TABLE users (
steam_id TEXT,
user_name TEXT NOT NULL,
avatar_link TEXT NOT NULL,
country_code CHAR(2) NOT NULL,
p2sr TEXT NOT NULL DEFAULT '-',
steam TEXT NOT NULL DEFAULT '-',
youtube TEXT NOT NULL DEFAULT '-',
twitch TEXT NOT NULL DEFAULT '-',
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (steam_id)
);
CREATE TABLE games (
id SERIAL,
name TEXT NOT NULL,
is_coop BOOLEAN NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE chapters (
id SERIAL,
game_id SMALLINT NOT NULL,
name TEXT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (game_id) REFERENCES games(id)
);
CREATE TABLE categories (
id SERIAL,
name TEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE maps (
id SERIAL,
game_id SMALLINT NOT NULL,
chapter_id SMALLINT NOT NULL,
name TEXT NOT NULL,
is_disabled BOOLEAN NOT NULL DEFAULT false,
PRIMARY KEY (id),
FOREIGN KEY (game_id) REFERENCES games(id),
FOREIGN KEY (chapter_id) REFERENCES chapters(id)
);
CREATE TABLE map_routes (
id SERIAL,
map_id SMALLINT NOT NULL,
category_id SMALLINT NOT NULL,
score_count SMALLINT NOT NULL,
description TEXT NOT NULL,
showcase TEXT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (map_id) REFERENCES maps(id),
FOREIGN KEY (category_id) REFERENCES categories(id),
UNIQUE (map_id, category_id, score_count)
);
CREATE TABLE map_history (
id SERIAL,
map_id SMALLINT NOT NULL,
category_id SMALLINT NOT NULL,
user_name TEXT NOT NULL,
score_count SMALLINT NOT NULL,
record_date DATE NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES categories(id),
FOREIGN KEY (map_id) REFERENCES maps(id),
UNIQUE (map_id, category_id, score_count)
);
CREATE TABLE map_ratings (
id SERIAL,
map_id SMALLINT NOT NULL,
category_id SMALLINT NOT NULL,
user_id TEXT NOT NULL,
rating SMALLINT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (map_id) REFERENCES maps(id),
FOREIGN KEY (category_id) REFERENCES categories(id),
FOREIGN KEY (user_id) REFERENCES users(steam_id)
);
CREATE TABLE map_discussions (
id SERIAL,
map_id SMALLINT NOT NULL,
user_id TEXT NOT NULL,
title TEXT NOT NULL,
content TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now(),
updated_at TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (id),
FOREIGN KEY (map_id) REFERENCES maps(id),
FOREIGN KEY (user_id) REFERENCES users(steam_id)
);
CREATE TABLE map_discussions_comments (
id SERIAL,
discussion_id INT NOT NULL,
user_id TEXT NOT NULL,
comment TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (id),
FOREIGN KEY (discussion_id) REFERENCES map_discussions(id),
FOREIGN KEY (user_id) REFERENCES users(steam_id)
);
CREATE TABLE map_discussions_upvotes (
id SERIAL,
discussion_id INT NOT NULL,
user_id TEXT NOT NULL,
upvoted BOOLEAN NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (discussion_id) REFERENCES map_discussions(id),
FOREIGN KEY (user_id) REFERENCES users(steam_id)
);
CREATE TABLE demos (
id UUID,
location_id TEXT NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE records_sp (
id SERIAL,
map_id SMALLINT NOT NULL,
user_id TEXT NOT NULL,
score_count SMALLINT NOT NULL,
score_time INTEGER NOT NULL,
demo_id UUID NOT NULL,
record_date TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (id),
FOREIGN KEY (map_id) REFERENCES maps(id),
FOREIGN KEY (user_id) REFERENCES users(steam_id),
FOREIGN KEY (demo_id) REFERENCES demos(id)
);
CREATE TABLE records_mp (
id SERIAL,
map_id SMALLINT NOT NULL,
host_id TEXT NOT NULL,
partner_id TEXT NOT NULL,
score_count SMALLINT NOT NULL,
score_time INTEGER NOT NULL,
host_demo_id UUID NOT NULL,
partner_demo_id UUID NOT NULL,
record_date TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (id),
FOREIGN KEY (map_id) REFERENCES maps(id),
FOREIGN KEY (host_id) REFERENCES users(steam_id),
FOREIGN KEY (partner_id) REFERENCES users(steam_id),
FOREIGN KEY (host_demo_id) REFERENCES demos(id),
FOREIGN KEY (partner_demo_id) REFERENCES demos(id)
);
CREATE TABLE titles (
id SERIAL,
title_name TEXT NOT NULL,
title_color CHAR(6) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE user_titles (
title_id INT NOT NULL,
user_id TEXT NOT NULL,
FOREIGN KEY (title_id) REFERENCES titles(id),
FOREIGN KEY (user_id) REFERENCES users(steam_id)
);
CREATE TABLE countries (
country_code CHAR(2),
country_name TEXT NOT NULL,
PRIMARY KEY (country_code)
);
CREATE TABLE logs (
id SERIAL,
user_id TEXT NOT NULL,
type TEXT NOT NULL,
description TEXT NOT NULL,
date TIMESTAMP NOT NULL DEFAULT now(),
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(steam_id)
);
|