From 5a2217f7a9d01b925c3db6491a363e6e4db9ca42 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Wed, 7 Aug 2024 22:49:33 +0900 Subject: feat(backend): rename testcase_executions to testcase_results --- backend/query.sql | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'backend/query.sql') diff --git a/backend/query.sql b/backend/query.sql index ea04c08..493fdb8 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -68,6 +68,6 @@ SELECT * FROM testcases WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1) ORDER BY testcases.testcase_id; --- name: CreateTestcaseExecution :exec -INSERT INTO testcase_executions (submission_id, testcase_id, status, stdout, stderr) +-- name: CreateTestcaseResult :exec +INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr) VALUES ($1, $2, $3, $4, $5); -- cgit v1.2.3-70-g09d2 From 47d81ffbd3e4fe178d2935325e312cef77276250 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Wed, 7 Aug 2024 23:22:07 +0900 Subject: feat(backend): create submission_results table --- backend/db/models.go | 9 +++++++++ backend/db/query.sql.go | 22 ++++++++++++++++++++++ backend/query.sql | 4 ++++ backend/schema.sql | 11 +++++++++++ backend/taskqueue/processor.go | 6 ++---- 5 files changed, 48 insertions(+), 4 deletions(-) (limited to 'backend/query.sql') diff --git a/backend/db/models.go b/backend/db/models.go index 49e4b52..d4cf98b 100644 --- a/backend/db/models.go +++ b/backend/db/models.go @@ -39,6 +39,15 @@ type Submission struct { CreatedAt pgtype.Timestamp } +type SubmissionResult struct { + SubmissionResultID int32 + SubmissionID int32 + Status string + Stdout string + Stderr string + CreatedAt pgtype.Timestamp +} + type Testcase struct { TestcaseID int32 ProblemID int32 diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index 6c78267..cfb97fd 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -36,6 +36,28 @@ func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionPara 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) diff --git a/backend/query.sql b/backend/query.sql index 493fdb8..0d32944 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -68,6 +68,10 @@ SELECT * FROM testcases WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1) ORDER BY testcases.testcase_id; +-- name: CreateSubmissionResult :exec +INSERT INTO submission_results (submission_id, status, stdout, stderr) +VALUES ($1, $2, $3, $4); + -- name: CreateTestcaseResult :exec INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr) VALUES ($1, $2, $3, $4, $5); diff --git a/backend/schema.sql b/backend/schema.sql index 7188d72..74d1202 100644 --- a/backend/schema.sql +++ b/backend/schema.sql @@ -64,6 +64,17 @@ CREATE TABLE submissions ( CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id) ); +CREATE TABLE submission_results ( + submission_result_id SERIAL PRIMARY KEY, + submission_id INT NOT NULL UNIQUE, + 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) +); +CREATE INDEX idx_submission_results_submission_id ON submission_results(submission_id); + CREATE TABLE testcase_results ( testcase_result_id SERIAL PRIMARY KEY, submission_id INT NOT NULL, diff --git a/backend/taskqueue/processor.go b/backend/taskqueue/processor.go index 135a7d2..b080c46 100644 --- a/backend/taskqueue/processor.go +++ b/backend/taskqueue/processor.go @@ -70,9 +70,8 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error { return fmt.Errorf("json.Decode failed: %v", err) } if resData.Status != "success" { - err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{ + err := p.q.CreateSubmissionResult(ctx, db.CreateSubmissionResultParams{ SubmissionID: submissionID, - TestcaseID: nil, Status: "compile_error", Stdout: resData.Stdout, Stderr: resData.Stderr, @@ -114,9 +113,8 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error { return fmt.Errorf("json.Decode failed: %v", err) } if resData.Status != "success" { - err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{ + err := p.q.CreateSubmissionResult(ctx, db.CreateSubmissionResultParams{ SubmissionID: submissionID, - TestcaseID: nil, Status: "compile_error", Stdout: resData.Stdout, Stderr: resData.Stderr, -- cgit v1.2.3-70-g09d2 From a062af23b2e72c38b667e969652aefcf2238dc1b Mon Sep 17 00:00:00 2001 From: nsfisis Date: Thu, 8 Aug 2024 03:57:16 +0900 Subject: feat(backend): aggregate testcase results --- backend/db/query.sql.go | 22 ++++++++++++++++++++++ backend/game/hub.go | 29 ++++++++++++++++++++++++++--- backend/query.sql | 14 ++++++++++++++ worker/exec.go | 2 +- worker/models.go | 2 +- 5 files changed, 64 insertions(+), 5 deletions(-) (limited to 'backend/query.sql') diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index cfb97fd..5475067 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -11,6 +11,28 @@ import ( "github.com/jackc/pgx/v5/pgtype" ) +const aggregateTestcaseResults = `-- 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 +` + +func (q *Queries) AggregateTestcaseResults(ctx context.Context, submissionID int32) (string, error) { + row := q.db.QueryRow(ctx, aggregateTestcaseResults, submissionID) + var status string + err := row.Scan(&status) + return status, err +} + const createSubmission = `-- name: CreateSubmission :one INSERT INTO submissions (game_id, user_id, code, code_size) VALUES ($1, $2, $3, $4) diff --git a/backend/game/hub.go b/backend/game/hub.go index d17ff7c..11a466b 100644 --- a/backend/game/hub.go +++ b/backend/game/hub.go @@ -270,7 +270,17 @@ func (hub *gameHub) processTaskResults() { // TODO: broadcast to watchers } case *taskqueue.TaskResultRunTestcase: - err := hub.processTaskResultRunTestcase(taskResult) + var err error + err = hub.processTaskResultRunTestcase(taskResult) + _ = err // TODO: handle err? + aggregatedStatus, err := hub.q.AggregateTestcaseResults(hub.ctx, int32(taskResult.TaskPayload.SubmissionID)) + _ = err // TODO: handle err? + err = hub.q.CreateSubmissionResult(hub.ctx, db.CreateSubmissionResultParams{ + SubmissionID: int32(taskResult.TaskPayload.SubmissionID), + Status: aggregatedStatus, + Stdout: "", + Stderr: "", + }) if err != nil { for player := range hub.players { if player.playerID != taskResult.TaskPayload.UserID() { @@ -280,13 +290,26 @@ func (hub *gameHub) processTaskResults() { Type: playerMessageTypeS2CExecResult, Data: playerMessageS2CExecResultPayload{ Score: nil, - Status: api.GamePlayerMessageS2CExecResultPayloadStatus(err.Status), + Status: api.GamePlayerMessageS2CExecResultPayloadStatus("internal_error"), }, } } // TODO: broadcast to watchers + continue + } + for player := range hub.players { + if player.playerID != taskResult.TaskPayload.UserID() { + continue + } + player.s2cMessages <- &playerMessageS2CExecResult{ + Type: playerMessageTypeS2CExecResult, + Data: playerMessageS2CExecResultPayload{ + Score: nil, + Status: api.GamePlayerMessageS2CExecResultPayloadStatus(aggregatedStatus), + }, + } + // TODO: broadcast to watchers } - // TODO: aggregate results of testcases default: panic("unexpected task result type") } diff --git a/backend/query.sql b/backend/query.sql index 0d32944..e767746 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -75,3 +75,17 @@ VALUES ($1, $2, $3, $4); -- 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; diff --git a/worker/exec.go b/worker/exec.go index 10bc99a..fb238c3 100644 --- a/worker/exec.go +++ b/worker/exec.go @@ -77,7 +77,7 @@ func convertCommandErrorToResultType(err error) string { if err == context.DeadlineExceeded { return resultTimeout } else { - return resultFailure + return resultRuntimeError } } else { return resultSuccess diff --git a/worker/models.go b/worker/models.go index a7310bd..c60002c 100644 --- a/worker/models.go +++ b/worker/models.go @@ -7,7 +7,7 @@ import ( const ( resultSuccess = "success" - resultFailure = "failure" + resultRuntimeError = "runtime_error" resultTimeout = "timeout" resultInternalError = "internal_error" ) -- cgit v1.2.3-70-g09d2