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
|
-- name: GetUserByID :one
SELECT * FROM users
WHERE users.user_id = $1
LIMIT 1;
-- name: GetUserIDByUsername :one
SELECT user_id FROM users
WHERE users.username = $1
LIMIT 1;
-- name: CreateUser :one
INSERT INTO users (username, display_name, is_admin)
VALUES ($1, $1, false)
RETURNING user_id;
-- name: UpdateUserIconPath :exec
UPDATE users
SET icon_path = $2
WHERE user_id = $1;
-- name: ListUsers :many
SELECT * FROM users
ORDER BY users.user_id;
-- name: GetUserAuthByUsername :one
SELECT * FROM users
JOIN user_auths ON users.user_id = user_auths.user_id
WHERE users.username = $1
LIMIT 1;
-- name: UpdateUser :exec
UPDATE users
SET
display_name = $2,
icon_path = $3,
is_admin = $4,
label = $5
WHERE user_id = $1;
-- name: CreateUserAuth :exec
INSERT INTO user_auths (user_id, auth_type)
VALUES ($1, $2);
-- name: ListPublicGames :many
SELECT * FROM games
JOIN problems ON games.problem_id = problems.problem_id
WHERE is_public = true
ORDER BY games.game_id;
-- name: ListAllGames :many
SELECT * FROM games
ORDER BY games.game_id;
-- name: UpdateGameStartedAt :exec
UPDATE games
SET started_at = $2
WHERE game_id = $1;
-- name: GetGameByID :one
SELECT * FROM games
JOIN problems ON games.problem_id = problems.problem_id
WHERE games.game_id = $1
LIMIT 1;
-- name: UpdateGame :exec
UPDATE games
SET
game_type = $2,
is_public = $3,
display_name = $4,
duration_seconds = $5,
started_at = $6,
problem_id = $7
WHERE game_id = $1;
-- name: ListMainPlayers :many
SELECT * FROM game_main_players
JOIN users ON game_main_players.user_id = users.user_id
WHERE game_main_players.game_id = ANY($1::INT[])
ORDER BY game_main_players.user_id;
-- name: AddMainPlayer :exec
INSERT INTO game_main_players (game_id, user_id)
VALUES ($1, $2);
-- name: RemoveAllMainPlayers :exec
DELETE FROM game_main_players
WHERE game_id = $1;
-- name: ListTestcasesByGameID :many
SELECT * FROM testcases
WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1)
ORDER BY testcases.testcase_id;
-- name: CreateTestcaseResult :exec
INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr)
VALUES ($1, $2, $3, $4, $5);
-- name: AggregateTestcaseResults :one
SELECT
CASE
WHEN COUNT(CASE WHEN r.status IS NULL THEN 1 END) > 0 THEN 'running'
WHEN COUNT(CASE WHEN r.status = 'internal_error' THEN 1 END) > 0 THEN 'internal_error'
WHEN COUNT(CASE WHEN r.status = 'timeout' THEN 1 END) > 0 THEN 'timeout'
WHEN COUNT(CASE WHEN r.status = 'runtime_error' THEN 1 END) > 0 THEN 'runtime_error'
WHEN COUNT(CASE WHEN r.status = 'wrong_answer' THEN 1 END) > 0 THEN 'wrong_answer'
ELSE 'success'
END AS status
FROM testcases
LEFT JOIN testcase_results AS r ON testcases.testcase_id = r.testcase_id
WHERE r.submission_id = $1;
-- name: GetLatestState :one
SELECT * FROM game_states
LEFT JOIN submissions ON game_states.best_score_submission_id = submissions.submission_id
WHERE game_states.game_id = $1 AND game_states.user_id = $2
LIMIT 1;
-- name: GetLatestStatesOfMainPlayers :many
SELECT * FROM game_main_players
LEFT JOIN game_states ON game_main_players.game_id = game_states.game_id AND game_main_players.user_id = game_states.user_id
LEFT JOIN submissions ON game_states.best_score_submission_id = submissions.submission_id
WHERE game_main_players.game_id = $1;
-- name: GetRanking :many
SELECT
sqlc.embed(submissions),
sqlc.embed(users)
FROM game_states
JOIN users ON game_states.user_id = users.user_id
JOIN submissions ON game_states.best_score_submission_id = submissions.submission_id
WHERE game_states.game_id = $1
ORDER BY submissions.code_size ASC, submissions.created_at ASC;
-- name: GetQualifyingRanking :many
SELECT
u.username AS username,
s1.code_size AS code_size_1,
s2.code_size AS code_size_2,
(s1.code_size + s2.code_size) AS total_code_size,
s1.created_at AS submitted_at_1,
s2.created_at AS submitted_at_2
FROM game_states gs1
JOIN submissions s1 ON gs1.best_score_submission_id = s1.submission_id
JOIN game_states gs2 ON gs1.user_id = gs2.user_id
JOIN submissions s2 ON gs2.best_score_submission_id = s2.submission_id
JOIN users u ON gs1.user_id = u.user_id
WHERE gs1.game_id = $1 AND gs2.game_id = $2
ORDER BY total_code_size ASC;
-- name: UpdateCode :exec
INSERT INTO game_states (game_id, user_id, code, status)
VALUES ($1, $2, $3, $4)
ON CONFLICT (game_id, user_id)
DO UPDATE SET code = EXCLUDED.code;
-- name: CreateSubmission :one
INSERT INTO submissions (game_id, user_id, code, code_size, status)
VALUES ($1, $2, $3, $4, 'running')
RETURNING submission_id;
-- name: UpdateSubmissionStatus :exec
UPDATE submissions
SET status = $2
WHERE submission_id = $1;
-- name: UpdateGameStateStatus :exec
UPDATE game_states
SET status = $3
WHERE game_id = $1 AND user_id = $2;
-- name: SyncGameStateBestScoreSubmission :exec
UPDATE game_states
SET best_score_submission_id = (
SELECT submission_id FROM submissions AS s
WHERE s.game_id = $1 AND s.user_id = $2 AND s.status = 'success'
ORDER BY s.code_size ASC, s.created_at ASC
LIMIT 1
)
WHERE game_id = $1 AND user_id = $2;
|
