forked from kenkoooo/AtCoderProblems
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathschema.sql
More file actions
229 lines (203 loc) · 7.67 KB
/
Copy pathschema.sql
File metadata and controls
229 lines (203 loc) · 7.67 KB
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
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
-- comment out for now because of the sqlx issue:
-- https://github.com/launchbadge/sqlx/issues/484
-- SET client_encoding = 'UTF8';
DROP TABLE IF EXISTS submissions;
CREATE TABLE submissions (
id BIGINT NOT NULL,
epoch_second BIGINT NOT NULL,
problem_id VARCHAR(255) NOT NULL,
contest_id VARCHAR(255) NOT NULL,
user_id VARCHAR(255) NOT NULL,
language VARCHAR(255) NOT NULL,
point DOUBLE PRECISION NOT NULL,
length INT NOT NULL,
result VARCHAR(255) NOT NULL,
execution_time INT,
PRIMARY KEY (id)
);
CREATE INDEX ON submissions (epoch_second);
CREATE INDEX ON submissions (user_id, epoch_second ASC);
CREATE INDEX ON submissions (LOWER(user_id), epoch_second ASC);
DROP TABLE IF EXISTS problems;
CREATE TABLE problems (
id VARCHAR(255) NOT NULL,
contest_id VARCHAR(255) NOT NULL,
problem_index VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
DROP TABLE IF EXISTS contests;
CREATE TABLE contests (
id VARCHAR(255) NOT NULL,
start_epoch_second BIGINT NOT NULL,
duration_second BIGINT NOT NULL,
title VARCHAR(255) NOT NULL,
rate_change VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
DROP TABLE IF EXISTS solver;
CREATE TABLE solver (
problem_id VARCHAR(255) NOT NULL,
user_count INT NOT NULL,
PRIMARY KEY (problem_id)
);
DROP TABLE IF EXISTS shortest;
CREATE TABLE shortest (
contest_id VARCHAR(255) NOT NULL,
problem_id VARCHAR(255) NOT NULL,
submission_id BIGINT NOT NULL,
PRIMARY KEY (problem_id)
);
DROP TABLE IF EXISTS fastest;
CREATE TABLE fastest (
contest_id VARCHAR(255) NOT NULL,
problem_id VARCHAR(255) NOT NULL,
submission_id BIGINT NOT NULL,
PRIMARY KEY (problem_id)
);
DROP TABLE IF EXISTS first;
CREATE TABLE first (
contest_id VARCHAR(255) NOT NULL,
problem_id VARCHAR(255) NOT NULL,
submission_id BIGINT NOT NULL,
PRIMARY KEY (problem_id)
);
DROP TABLE IF EXISTS accepted_count;
CREATE TABLE accepted_count (
user_id VARCHAR(255) NOT NULL,
problem_count INT NOT NULL,
PRIMARY KEY (user_id)
);
CREATE INDEX ON accepted_count (LOWER(user_id));
CREATE INDEX ON accepted_count (problem_count DESC, user_id);
DROP TABLE IF EXISTS points;
CREATE TABLE points (
problem_id VARCHAR(255) NOT NULL,
point DOUBLE PRECISION,
predict DOUBLE PRECISION,
PRIMARY KEY (problem_id)
);
DROP TABLE IF EXISTS rated_point_sum;
CREATE TABLE rated_point_sum (
user_id VARCHAR(255) NOT NULL,
point_sum BIGINT NOT NULL,
PRIMARY KEY (user_id)
);
CREATE INDEX ON rated_point_sum (LOWER(user_id));
CREATE INDEX ON rated_point_sum (point_sum DESC, user_id);
DROP TABLE IF EXISTS language_count;
CREATE TABLE language_count (
user_id VARCHAR(255) NOT NULL,
simplified_language VARCHAR(255) NOT NULL,
problem_count INT NOT NULL,
PRIMARY KEY (user_id, simplified_language)
);
CREATE INDEX ON language_count (LOWER(user_id));
CREATE INDEX ON language_count (simplified_language, problem_count DESC, user_id);
DROP TABLE IF EXISTS predicted_rating;
CREATE TABLE predicted_rating (
user_id VARCHAR(255) NOT NULL,
rating DOUBLE PRECISION,
PRIMARY KEY (user_id)
);
DROP TABLE IF EXISTS contest_problem;
CREATE TABLE contest_problem (
contest_id VARCHAR(255) NOT NULL,
problem_id VARCHAR(255) NOT NULL,
problem_index VARCHAR(255) NOT NULL,
PRIMARY KEY (contest_id, problem_id)
);
DROP TABLE IF EXISTS max_streaks;
CREATE TABLE max_streaks (
user_id VARCHAR(255) NOT NULL,
streak BIGINT NOT NULL,
PRIMARY KEY (user_id)
);
CREATE INDEX ON max_streaks (LOWER(user_id));
CREATE INDEX ON max_streaks (streak DESC, user_id);
-- For internal services:
DROP TABLE IF EXISTS internal_problem_list_items;
DROP TABLE IF EXISTS internal_problem_lists;
DROP TABLE IF EXISTS internal_virtual_contest_participants;
DROP TABLE IF EXISTS internal_virtual_contest_items;
DROP TABLE IF EXISTS internal_virtual_contests;
DROP TABLE IF EXISTS internal_progress_reset;
DROP TABLE IF EXISTS internal_users;
CREATE TABLE internal_users (
internal_user_id VARCHAR(255) NOT NULL,
atcoder_user_id VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (internal_user_id)
);
CREATE TABLE internal_problem_lists (
internal_list_id VARCHAR(255) NOT NULL,
internal_user_id VARCHAR(255) REFERENCES internal_users ON DELETE CASCADE ON UPDATE CASCADE,
internal_list_name VARCHAR(255) DEFAULT '',
PRIMARY KEY (internal_list_id)
);
CREATE INDEX ON internal_problem_lists (internal_user_id);
CREATE TABLE internal_problem_list_items (
internal_list_id VARCHAR(255) REFERENCES internal_problem_lists ON DELETE CASCADE ON UPDATE CASCADE,
problem_id VARCHAR(255) NOT NULL,
memo VARCHAR(255) DEFAULT '',
PRIMARY KEY (internal_list_id, problem_id)
);
CREATE INDEX ON internal_problem_list_items (internal_list_id);
CREATE TABLE internal_virtual_contests (
id VARCHAR(255) NOT NULL,
title VARCHAR(255) DEFAULT '',
memo VARCHAR(255) DEFAULT '',
internal_user_id VARCHAR(255) REFERENCES internal_users ON DELETE CASCADE ON UPDATE CASCADE,
start_epoch_second BIGINT NOT NULL,
duration_second BIGINT NOT NULL,
mode VARCHAR(255) DEFAULT NULL,
is_public BOOLEAN NOT NULL DEFAULT TRUE,
penalty_second BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
CREATE INDEX ON internal_virtual_contests (internal_user_id);
CREATE INDEX ON internal_virtual_contests (start_epoch_second);
CREATE TABLE internal_virtual_contest_items (
problem_id VARCHAR(255) NOT NULL,
internal_virtual_contest_id VARCHAR(255) REFERENCES internal_virtual_contests(id) ON DELETE CASCADE ON UPDATE CASCADE,
user_defined_point BIGINT DEFAULT NULL,
user_defined_order BIGINT DEFAULT NULL,
PRIMARY KEY (problem_id, internal_virtual_contest_id)
);
CREATE INDEX ON internal_virtual_contest_items (internal_virtual_contest_id);
CREATE TABLE internal_virtual_contest_participants (
internal_virtual_contest_id VARCHAR(255) REFERENCES internal_virtual_contests(id) ON DELETE CASCADE ON UPDATE CASCADE,
internal_user_id VARCHAR(255) REFERENCES internal_users ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (internal_virtual_contest_id, internal_user_id)
);
CREATE INDEX ON internal_virtual_contest_participants (internal_user_id);
CREATE TABLE internal_progress_reset (
internal_user_id VARCHAR(255) REFERENCES internal_users ON DELETE CASCADE ON UPDATE CASCADE,
problem_id VARCHAR(255) NOT NULL,
reset_epoch_second BIGINT NOT NULL,
PRIMARY KEY (internal_user_id, problem_id)
);
CREATE INDEX ON internal_progress_reset (internal_user_id);
-- Merged problems for API export (populated by dbt)
DROP TABLE IF EXISTS merged_problems;
CREATE TABLE merged_problems (
id VARCHAR(255) NOT NULL,
contest_id VARCHAR(255) NOT NULL,
problem_index VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
title VARCHAR(255) NOT NULL,
shortest_submission_id BIGINT,
shortest_contest_id VARCHAR(255),
shortest_user_id VARCHAR(255),
fastest_submission_id BIGINT,
fastest_contest_id VARCHAR(255),
fastest_user_id VARCHAR(255),
first_submission_id BIGINT,
first_contest_id VARCHAR(255),
first_user_id VARCHAR(255),
source_code_length INT,
execution_time INT,
point DOUBLE PRECISION,
solver_count INT,
PRIMARY KEY (id)
);