diff options
Diffstat (limited to 'backend/db/query.sql.go')
| -rw-r--r-- | backend/db/query.sql.go | 437 |
1 files changed, 328 insertions, 109 deletions
diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index fdd40dd..980928a 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -1,6 +1,6 @@ // Code generated by sqlc. DO NOT EDIT. // versions: -// sqlc v1.26.0 +// sqlc v1.28.0 // source: query.sql package db @@ -34,8 +34,8 @@ func (q *Queries) AggregateTestcaseResults(ctx context.Context, submissionID int } const createSubmission = `-- name: CreateSubmission :one -INSERT INTO submissions (game_id, user_id, code, code_size, code_hash) -VALUES ($1, $2, $3, $4, $5) +INSERT INTO submissions (game_id, user_id, code, code_size, status) +VALUES ($1, $2, $3, $4, 'running') RETURNING submission_id ` @@ -44,7 +44,6 @@ type CreateSubmissionParams struct { UserID int32 Code string CodeSize int32 - CodeHash string } func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionParams) (int32, error) { @@ -53,35 +52,12 @@ func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionPara arg.UserID, arg.Code, arg.CodeSize, - arg.CodeHash, ) var submission_id int32 err := row.Scan(&submission_id) return submission_id, err } -const createSubmissionResult = `-- name: CreateSubmissionResult :exec -INSERT INTO submission_results (submission_id, status, stdout, stderr) -VALUES ($1, $2, $3, $4) -` - -type CreateSubmissionResultParams struct { - SubmissionID int32 - Status string - Stdout string - Stderr string -} - -func (q *Queries) CreateSubmissionResult(ctx context.Context, arg CreateSubmissionResultParams) error { - _, err := q.db.Exec(ctx, createSubmissionResult, - arg.SubmissionID, - arg.Status, - arg.Stdout, - arg.Stderr, - ) - return err -} - const createTestcaseResult = `-- name: CreateTestcaseResult :exec INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr) VALUES ($1, $2, $3, $4, $5) @@ -135,7 +111,7 @@ func (q *Queries) CreateUserAuth(ctx context.Context, arg CreateUserAuthParams) } const getGameByID = `-- name: GetGameByID :one -SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games +SELECT game_id, game_type, is_public, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, sample_code FROM games JOIN problems ON games.problem_id = problems.problem_id WHERE games.game_id = $1 LIMIT 1 @@ -144,7 +120,7 @@ LIMIT 1 type GetGameByIDRow struct { GameID int32 GameType string - State string + IsPublic bool DisplayName string DurationSeconds int32 CreatedAt pgtype.Timestamp @@ -153,6 +129,7 @@ type GetGameByIDRow struct { ProblemID_2 int32 Title string Description string + SampleCode string } func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow, error) { @@ -161,7 +138,7 @@ func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow err := row.Scan( &i.GameID, &i.GameType, - &i.State, + &i.IsPublic, &i.DisplayName, &i.DurationSeconds, &i.CreatedAt, @@ -170,10 +147,185 @@ func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow &i.ProblemID_2, &i.Title, &i.Description, + &i.SampleCode, + ) + return i, err +} + +const getLatestState = `-- name: GetLatestState :one +SELECT game_states.game_id, game_states.user_id, game_states.code, game_states.status, best_score_submission_id, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, created_at 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 +` + +type GetLatestStateParams struct { + GameID int32 + UserID int32 +} + +type GetLatestStateRow struct { + GameID int32 + UserID int32 + Code string + Status string + BestScoreSubmissionID *int32 + SubmissionID *int32 + GameID_2 *int32 + UserID_2 *int32 + Code_2 *string + CodeSize *int32 + Status_2 *string + CreatedAt pgtype.Timestamp +} + +func (q *Queries) GetLatestState(ctx context.Context, arg GetLatestStateParams) (GetLatestStateRow, error) { + row := q.db.QueryRow(ctx, getLatestState, arg.GameID, arg.UserID) + var i GetLatestStateRow + err := row.Scan( + &i.GameID, + &i.UserID, + &i.Code, + &i.Status, + &i.BestScoreSubmissionID, + &i.SubmissionID, + &i.GameID_2, + &i.UserID_2, + &i.Code_2, + &i.CodeSize, + &i.Status_2, + &i.CreatedAt, ) return i, err } +const getLatestStatesOfMainPlayers = `-- name: GetLatestStatesOfMainPlayers :many +SELECT game_main_players.game_id, game_main_players.user_id, game_states.game_id, game_states.user_id, game_states.code, game_states.status, best_score_submission_id, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, created_at 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 +` + +type GetLatestStatesOfMainPlayersRow struct { + GameID int32 + UserID int32 + GameID_2 *int32 + UserID_2 *int32 + Code *string + Status *string + BestScoreSubmissionID *int32 + SubmissionID *int32 + GameID_3 *int32 + UserID_3 *int32 + Code_2 *string + CodeSize *int32 + Status_2 *string + CreatedAt pgtype.Timestamp +} + +func (q *Queries) GetLatestStatesOfMainPlayers(ctx context.Context, gameID int32) ([]GetLatestStatesOfMainPlayersRow, error) { + rows, err := q.db.Query(ctx, getLatestStatesOfMainPlayers, gameID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetLatestStatesOfMainPlayersRow + for rows.Next() { + var i GetLatestStatesOfMainPlayersRow + if err := rows.Scan( + &i.GameID, + &i.UserID, + &i.GameID_2, + &i.UserID_2, + &i.Code, + &i.Status, + &i.BestScoreSubmissionID, + &i.SubmissionID, + &i.GameID_3, + &i.UserID_3, + &i.Code_2, + &i.CodeSize, + &i.Status_2, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getRanking = `-- name: GetRanking :many +SELECT game_states.game_id, game_states.user_id, game_states.code, game_states.status, best_score_submission_id, users.user_id, username, display_name, icon_path, is_admin, users.created_at, submission_id, submissions.game_id, submissions.user_id, submissions.code, code_size, submissions.status, submissions.created_at 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 +` + +type GetRankingRow struct { + GameID int32 + UserID int32 + Code string + Status string + BestScoreSubmissionID *int32 + UserID_2 int32 + Username string + DisplayName string + IconPath *string + IsAdmin bool + CreatedAt pgtype.Timestamp + SubmissionID int32 + GameID_2 int32 + UserID_3 int32 + Code_2 string + CodeSize int32 + Status_2 string + CreatedAt_2 pgtype.Timestamp +} + +func (q *Queries) GetRanking(ctx context.Context, gameID int32) ([]GetRankingRow, error) { + rows, err := q.db.Query(ctx, getRanking, gameID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetRankingRow + for rows.Next() { + var i GetRankingRow + if err := rows.Scan( + &i.GameID, + &i.UserID, + &i.Code, + &i.Status, + &i.BestScoreSubmissionID, + &i.UserID_2, + &i.Username, + &i.DisplayName, + &i.IconPath, + &i.IsAdmin, + &i.CreatedAt, + &i.SubmissionID, + &i.GameID_2, + &i.UserID_3, + &i.Code_2, + &i.CodeSize, + &i.Status_2, + &i.CreatedAt_2, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + const getSubmissionCodeSizeByID = `-- name: GetSubmissionCodeSizeByID :one SELECT code_size FROM submissions WHERE submission_id = $1 @@ -258,42 +410,29 @@ func (q *Queries) GetUserIDByUsername(ctx context.Context, username string) (int return user_id, err } -const listGamePlayers = `-- name: ListGamePlayers :many -SELECT game_id, game_players.user_id, users.user_id, username, display_name, icon_path, is_admin, created_at FROM game_players -JOIN users ON game_players.user_id = users.user_id -WHERE game_players.game_id = $1 -ORDER BY game_players.user_id +const listAllGames = `-- name: ListAllGames :many +SELECT game_id, game_type, is_public, display_name, duration_seconds, created_at, started_at, problem_id FROM games +ORDER BY games.game_id ` -type ListGamePlayersRow struct { - GameID int32 - UserID int32 - UserID_2 int32 - Username string - DisplayName string - IconPath *string - IsAdmin bool - CreatedAt pgtype.Timestamp -} - -func (q *Queries) ListGamePlayers(ctx context.Context, gameID int32) ([]ListGamePlayersRow, error) { - rows, err := q.db.Query(ctx, listGamePlayers, gameID) +func (q *Queries) ListAllGames(ctx context.Context) ([]Game, error) { + rows, err := q.db.Query(ctx, listAllGames) if err != nil { return nil, err } defer rows.Close() - var items []ListGamePlayersRow + var items []Game for rows.Next() { - var i ListGamePlayersRow + var i Game if err := rows.Scan( &i.GameID, - &i.UserID, - &i.UserID_2, - &i.Username, + &i.GameType, + &i.IsPublic, &i.DisplayName, - &i.IconPath, - &i.IsAdmin, + &i.DurationSeconds, &i.CreatedAt, + &i.StartedAt, + &i.ProblemID, ); err != nil { return nil, err } @@ -305,47 +444,68 @@ func (q *Queries) ListGamePlayers(ctx context.Context, gameID int32) ([]ListGame return items, nil } -const listGames = `-- name: ListGames :many -SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games -JOIN problems ON games.problem_id = problems.problem_id -ORDER BY games.game_id +const listMainPlayerIDs = `-- name: ListMainPlayerIDs :many +SELECT user_id FROM game_main_players +WHERE game_id = $1 +ORDER BY user_id ` -type ListGamesRow struct { - GameID int32 - GameType string - State string - DisplayName string - DurationSeconds int32 - CreatedAt pgtype.Timestamp - StartedAt pgtype.Timestamp - ProblemID int32 - ProblemID_2 int32 - Title string - Description string +func (q *Queries) ListMainPlayerIDs(ctx context.Context, gameID int32) ([]int32, error) { + rows, err := q.db.Query(ctx, listMainPlayerIDs, gameID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []int32 + for rows.Next() { + var user_id int32 + if err := rows.Scan(&user_id); err != nil { + return nil, err + } + items = append(items, user_id) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const listMainPlayers = `-- name: ListMainPlayers :many +SELECT game_id, game_main_players.user_id, users.user_id, username, display_name, icon_path, is_admin, created_at 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 +` + +type ListMainPlayersRow struct { + GameID int32 + UserID int32 + UserID_2 int32 + Username string + DisplayName string + IconPath *string + IsAdmin bool + CreatedAt pgtype.Timestamp } -func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) { - rows, err := q.db.Query(ctx, listGames) +func (q *Queries) ListMainPlayers(ctx context.Context, dollar_1 []int32) ([]ListMainPlayersRow, error) { + rows, err := q.db.Query(ctx, listMainPlayers, dollar_1) if err != nil { return nil, err } defer rows.Close() - var items []ListGamesRow + var items []ListMainPlayersRow for rows.Next() { - var i ListGamesRow + var i ListMainPlayersRow if err := rows.Scan( &i.GameID, - &i.GameType, - &i.State, + &i.UserID, + &i.UserID_2, + &i.Username, &i.DisplayName, - &i.DurationSeconds, + &i.IconPath, + &i.IsAdmin, &i.CreatedAt, - &i.StartedAt, - &i.ProblemID, - &i.ProblemID_2, - &i.Title, - &i.Description, ); err != nil { return nil, err } @@ -357,18 +517,17 @@ func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) { return items, nil } -const listGamesForPlayer = `-- name: ListGamesForPlayer :many -SELECT games.game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, game_players.game_id, user_id FROM games +const listPublicGames = `-- name: ListPublicGames :many +SELECT game_id, game_type, is_public, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, sample_code FROM games JOIN problems ON games.problem_id = problems.problem_id -JOIN game_players ON games.game_id = game_players.game_id -WHERE game_players.user_id = $1 +WHERE is_public = true ORDER BY games.game_id ` -type ListGamesForPlayerRow struct { +type ListPublicGamesRow struct { GameID int32 GameType string - State string + IsPublic bool DisplayName string DurationSeconds int32 CreatedAt pgtype.Timestamp @@ -377,23 +536,22 @@ type ListGamesForPlayerRow struct { ProblemID_2 int32 Title string Description string - GameID_2 int32 - UserID int32 + SampleCode string } -func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListGamesForPlayerRow, error) { - rows, err := q.db.Query(ctx, listGamesForPlayer, userID) +func (q *Queries) ListPublicGames(ctx context.Context) ([]ListPublicGamesRow, error) { + rows, err := q.db.Query(ctx, listPublicGames) if err != nil { return nil, err } defer rows.Close() - var items []ListGamesForPlayerRow + var items []ListPublicGamesRow for rows.Next() { - var i ListGamesForPlayerRow + var i ListPublicGamesRow if err := rows.Scan( &i.GameID, &i.GameType, - &i.State, + &i.IsPublic, &i.DisplayName, &i.DurationSeconds, &i.CreatedAt, @@ -402,8 +560,7 @@ func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListG &i.ProblemID_2, &i.Title, &i.Description, - &i.GameID_2, - &i.UserID, + &i.SampleCode, ); err != nil { return nil, err } @@ -504,11 +661,56 @@ func (q *Queries) ListUsers(ctx context.Context) ([]User, error) { return items, nil } +const syncGameStateBestScoreSubmission = `-- 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 +` + +type SyncGameStateBestScoreSubmissionParams struct { + GameID int32 + UserID int32 +} + +func (q *Queries) SyncGameStateBestScoreSubmission(ctx context.Context, arg SyncGameStateBestScoreSubmissionParams) error { + _, err := q.db.Exec(ctx, syncGameStateBestScoreSubmission, arg.GameID, arg.UserID) + return err +} + +const updateCode = `-- 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 +` + +type UpdateCodeParams struct { + GameID int32 + UserID int32 + Code string + Status string +} + +func (q *Queries) UpdateCode(ctx context.Context, arg UpdateCodeParams) error { + _, err := q.db.Exec(ctx, updateCode, + arg.GameID, + arg.UserID, + arg.Code, + arg.Status, + ) + return err +} + const updateGame = `-- name: UpdateGame :exec UPDATE games SET game_type = $2, - state = $3, + is_public = $3, display_name = $4, duration_seconds = $5, started_at = $6, @@ -519,7 +721,7 @@ WHERE game_id = $1 type UpdateGameParams struct { GameID int32 GameType string - State string + IsPublic bool DisplayName string DurationSeconds int32 StartedAt pgtype.Timestamp @@ -530,7 +732,7 @@ func (q *Queries) UpdateGame(ctx context.Context, arg UpdateGameParams) error { _, err := q.db.Exec(ctx, updateGame, arg.GameID, arg.GameType, - arg.State, + arg.IsPublic, arg.DisplayName, arg.DurationSeconds, arg.StartedAt, @@ -555,19 +757,36 @@ func (q *Queries) UpdateGameStartedAt(ctx context.Context, arg UpdateGameStarted return err } -const updateGameState = `-- name: UpdateGameState :exec -UPDATE games -SET state = $2 -WHERE game_id = $1 +const updateGameStateStatus = `-- name: UpdateGameStateStatus :exec +UPDATE game_states +SET status = $3 +WHERE game_id = $1 AND user_id = $2 ` -type UpdateGameStateParams struct { +type UpdateGameStateStatusParams struct { GameID int32 - State string + UserID int32 + Status string +} + +func (q *Queries) UpdateGameStateStatus(ctx context.Context, arg UpdateGameStateStatusParams) error { + _, err := q.db.Exec(ctx, updateGameStateStatus, arg.GameID, arg.UserID, arg.Status) + return err +} + +const updateSubmissionStatus = `-- name: UpdateSubmissionStatus :exec +UPDATE submissions +SET status = $2 +WHERE submission_id = $1 +` + +type UpdateSubmissionStatusParams struct { + SubmissionID int32 + Status string } -func (q *Queries) UpdateGameState(ctx context.Context, arg UpdateGameStateParams) error { - _, err := q.db.Exec(ctx, updateGameState, arg.GameID, arg.State) +func (q *Queries) UpdateSubmissionStatus(ctx context.Context, arg UpdateSubmissionStatusParams) error { + _, err := q.db.Exec(ctx, updateSubmissionStatus, arg.SubmissionID, arg.Status) return err } |