From 5a2217f7a9d01b925c3db6491a363e6e4db9ca42 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
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.3.1


From 47d81ffbd3e4fe178d2935325e312cef77276250 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
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.3.1


From a062af23b2e72c38b667e969652aefcf2238dc1b Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
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.3.1

