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
|
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(64) NOT NULL UNIQUE,
display_name VARCHAR(64) NOT NULL,
icon_path VARCHAR(255),
is_admin BOOLEAN NOT NULL,
label VARCHAR(16),
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_users_username ON users(username);
CREATE TABLE user_auths (
user_auth_id SERIAL PRIMARY KEY,
user_id INT NOT NULL UNIQUE,
auth_type VARCHAR(16) NOT NULL,
password_hash VARCHAR(256),
CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE INDEX idx_user_auths_user_id ON user_auths(user_id);
CREATE TABLE problems (
problem_id SERIAL PRIMARY KEY,
title VARCHAR(255) NOT NULL,
description TEXT NOT NULL,
language VARCHAR(8) NOT NULL,
sample_code TEXT NOT NULL
);
CREATE TABLE games (
game_id SERIAL PRIMARY KEY,
game_type VARCHAR(16) NOT NULL,
is_public BOOLEAN NOT NULL,
display_name VARCHAR(255) NOT NULL,
duration_seconds INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
started_at TIMESTAMP,
problem_id INT NOT NULL,
CONSTRAINT fk_problem_id FOREIGN KEY(problem_id) REFERENCES problems(problem_id)
);
CREATE INDEX idx_games_problem_id ON games(problem_id);
CREATE TABLE game_main_players (
game_id INT NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY (game_id, user_id),
CONSTRAINT fk_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE TABLE submissions (
submission_id SERIAL PRIMARY KEY,
game_id INT NOT NULL,
user_id INT NOT NULL,
code TEXT NOT NULL,
code_size INT NOT NULL,
status VARCHAR(16) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
);
CREATE INDEX idx_submissions_game_id_user_id ON submissions(game_id, user_id);
CREATE TABLE game_states (
game_id INT NOT NULL,
user_id INT NOT NULL,
code TEXT NOT NULL,
status VARCHAR(16) NOT NULL,
best_score_submission_id INT,
PRIMARY KEY (game_id, user_id),
CONSTRAINT fk_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id),
CONSTRAINT fk_best_score_submission_id FOREIGN KEY(best_score_submission_id) REFERENCES submissions(submission_id)
);
CREATE TABLE testcases (
testcase_id SERIAL PRIMARY KEY,
problem_id INT NOT NULL,
stdin TEXT NOT NULL,
stdout TEXT NOT NULL,
CONSTRAINT fk_problem_id FOREIGN KEY(problem_id) REFERENCES problems(problem_id)
);
CREATE INDEX idx_testcases_problem_id ON testcases(problem_id);
CREATE TABLE testcase_results (
testcase_result_id SERIAL PRIMARY KEY,
submission_id INT NOT NULL,
testcase_id INT NOT NULL,
status VARCHAR(16) NOT NULL,
stdout TEXT NOT NULL,
stderr TEXT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_submission_id FOREIGN KEY(submission_id) REFERENCES submissions(submission_id),
CONSTRAINT fk_testcase_id FOREIGN KEY(testcase_id) REFERENCES testcases(testcase_id),
CONSTRAINT uq_submission_id_testcase_id UNIQUE(submission_id, testcase_id)
);
CREATE INDEX idx_testcase_results_submission_id ON testcase_results(submission_id);
CREATE TABLE sessions (
session_id VARCHAR(64) PRIMARY KEY,
user_id INT NOT NULL,
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_sessions_user_id FOREIGN KEY(user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
CREATE INDEX idx_sessions_user_id ON sessions(user_id);
CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
CREATE TABLE tournaments (
tournament_id SERIAL PRIMARY KEY,
display_name VARCHAR(255) NOT NULL,
bracket_size INT NOT NULL,
num_rounds INT NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE tournament_entries (
tournament_entry_id SERIAL PRIMARY KEY,
tournament_id INT NOT NULL,
user_id INT NOT NULL,
seed INT NOT NULL,
CONSTRAINT fk_tournament_entries_tournament_id FOREIGN KEY(tournament_id) REFERENCES tournaments(tournament_id),
CONSTRAINT fk_tournament_entries_user_id FOREIGN KEY(user_id) REFERENCES users(user_id),
CONSTRAINT uq_tournament_entries_tournament_user UNIQUE(tournament_id, user_id),
CONSTRAINT uq_tournament_entries_tournament_seed UNIQUE(tournament_id, seed)
);
CREATE TABLE tournament_matches (
tournament_match_id SERIAL PRIMARY KEY,
tournament_id INT NOT NULL,
round INT NOT NULL,
position INT NOT NULL,
game_id INT,
CONSTRAINT fk_tournament_matches_tournament_id FOREIGN KEY(tournament_id) REFERENCES tournaments(tournament_id),
CONSTRAINT fk_tournament_matches_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
CONSTRAINT uq_tournament_matches_tournament_round_position UNIQUE(tournament_id, round, position)
);
|
