From d799993a4fe0d05aa72c9a48d4a76c6db2240038 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 7 Aug 2024 22:44:08 +0900
Subject: feat(backend): make testcase_executions.testcase_id non-null

---
 backend/schema.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'backend/schema.sql')

diff --git a/backend/schema.sql b/backend/schema.sql
index d0b6c40..27a63d1 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -67,7 +67,7 @@ CREATE TABLE submissions (
 CREATE TABLE testcase_executions (
     testcase_execution_id SERIAL      PRIMARY KEY,
     submission_id         INT         NOT NULL,
-    testcase_id           INT,
+    testcase_id           INT         NOT NULL,
     status                VARCHAR(16) NOT NULL,
     stdout                TEXT        NOT NULL,
     stderr                TEXT        NOT NULL,
-- 
cgit v1.3.1


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/db/models.go           | 14 +++++++-------
 backend/db/query.sql.go        | 10 +++++-----
 backend/game/hub.go            | 12 ++++++------
 backend/query.sql              |  4 ++--
 backend/schema.sql             | 16 ++++++++--------
 backend/taskqueue/processor.go | 20 ++++++++++----------
 6 files changed, 38 insertions(+), 38 deletions(-)

(limited to 'backend/schema.sql')

diff --git a/backend/db/models.go b/backend/db/models.go
index 126b5f2..a8729e4 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -46,13 +46,13 @@ type Testcase struct {
 	Stdout     string
 }
 
-type TestcaseExecution struct {
-	TestcaseExecutionID int32
-	SubmissionID        int32
-	TestcaseID          int32
-	Status              string
-	Stdout              string
-	Stderr              string
+type TestcaseResult struct {
+	TestcaseResultID int32
+	SubmissionID     int32
+	TestcaseID       int32
+	Status           string
+	Stdout           string
+	Stderr           string
 }
 
 type User struct {
diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index eefa8a0..6c78267 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -36,12 +36,12 @@ func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionPara
 	return submission_id, err
 }
 
-const createTestcaseExecution = `-- name: CreateTestcaseExecution :exec
-INSERT INTO testcase_executions (submission_id, testcase_id, status, stdout, stderr)
+const createTestcaseResult = `-- name: CreateTestcaseResult :exec
+INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr)
 VALUES ($1, $2, $3, $4, $5)
 `
 
-type CreateTestcaseExecutionParams struct {
+type CreateTestcaseResultParams struct {
 	SubmissionID int32
 	TestcaseID   int32
 	Status       string
@@ -49,8 +49,8 @@ type CreateTestcaseExecutionParams struct {
 	Stderr       string
 }
 
-func (q *Queries) CreateTestcaseExecution(ctx context.Context, arg CreateTestcaseExecutionParams) error {
-	_, err := q.db.Exec(ctx, createTestcaseExecution,
+func (q *Queries) CreateTestcaseResult(ctx context.Context, arg CreateTestcaseResultParams) error {
+	_, err := q.db.Exec(ctx, createTestcaseResult,
 		arg.SubmissionID,
 		arg.TestcaseID,
 		arg.Status,
diff --git a/backend/game/hub.go b/backend/game/hub.go
index 605edf5..ef69ddb 100644
--- a/backend/game/hub.go
+++ b/backend/game/hub.go
@@ -33,7 +33,7 @@ type gameHub struct {
 	watchers          map[*watcherClient]bool
 	registerWatcher   chan *watcherClient
 	unregisterWatcher chan *watcherClient
-	testcaseExecution chan taskqueue.TaskExecResult
+	taskResults       chan taskqueue.TaskExecResult
 }
 
 func newGameHub(ctx context.Context, game *game, q *db.Queries, taskQueue *taskqueue.Queue) *gameHub {
@@ -49,7 +49,7 @@ func newGameHub(ctx context.Context, game *game, q *db.Queries, taskQueue *taskq
 		watchers:          make(map[*watcherClient]bool),
 		registerWatcher:   make(chan *watcherClient),
 		unregisterWatcher: make(chan *watcherClient),
-		testcaseExecution: make(chan taskqueue.TaskExecResult),
+		taskResults:       make(chan taskqueue.TaskExecResult),
 	}
 }
 
@@ -200,16 +200,16 @@ func (hub *gameHub) run() {
 }
 
 func (hub *gameHub) processTaskResults() {
-	for executionResult := range hub.testcaseExecution {
+	for taskResult := range hub.taskResults {
 		for player := range hub.players {
-			if player.playerID != executionResult.Task.UserID {
+			if player.playerID != taskResult.Task.UserID {
 				continue
 			}
 			player.s2cMessages <- &playerMessageS2CExecResult{
 				Type: playerMessageTypeS2CExecResult,
 				Data: playerMessageS2CExecResultPayload{
 					Score:  nil,
-					Status: api.GamePlayerMessageS2CExecResultPayloadStatus(executionResult.Result),
+					Status: api.GamePlayerMessageS2CExecResultPayloadStatus(taskResult.Result),
 				},
 			}
 		}
@@ -344,7 +344,7 @@ func (hubs *GameHubs) Run() {
 			log.Printf("no such game: %d", taskResult.Task.GameID)
 			continue
 		}
-		hub.testcaseExecution <- taskResult
+		hub.taskResults <- taskResult
 	}
 }
 
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);
diff --git a/backend/schema.sql b/backend/schema.sql
index 27a63d1..3cc7ed9 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -64,14 +64,14 @@ CREATE TABLE submissions (
     CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
 );
 
-CREATE TABLE testcase_executions (
-    testcase_execution_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,
+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,
     CONSTRAINT fk_submission_id FOREIGN KEY(submission_id) REFERENCES submissions(submission_id),
     CONSTRAINT fk_testcase_id FOREIGN KEY(testcase_id) REFERENCES testcases(testcase_id)
 );
-CREATE INDEX idx_testcase_executions_submission_id ON testcase_executions(submission_id);
+CREATE INDEX idx_testcase_results_submission_id ON testcase_results(submission_id);
diff --git a/backend/taskqueue/processor.go b/backend/taskqueue/processor.go
index 098b565..f789e2e 100644
--- a/backend/taskqueue/processor.go
+++ b/backend/taskqueue/processor.go
@@ -70,7 +70,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 			return fmt.Errorf("json.Decode failed: %v", err)
 		}
 		if resData.Result != "success" {
-			err := p.q.CreateTestcaseExecution(ctx, db.CreateTestcaseExecutionParams{
+			err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{
 				SubmissionID: submissionID,
 				TestcaseID:   nil,
 				Status:       "compile_error",
@@ -78,7 +78,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 				Stderr:       resData.Stderr,
 			})
 			if err != nil {
-				return fmt.Errorf("CreateTestcaseExecution failed: %v", err)
+				return fmt.Errorf("CreateTestcaseResult failed: %v", err)
 			}
 			p.results <- TaskExecResult{
 				Task:   &payload,
@@ -114,7 +114,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 			return fmt.Errorf("json.Decode failed: %v", err)
 		}
 		if resData.Result != "success" {
-			err := p.q.CreateTestcaseExecution(ctx, db.CreateTestcaseExecutionParams{
+			err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{
 				SubmissionID: submissionID,
 				TestcaseID:   nil,
 				Status:       "compile_error",
@@ -122,7 +122,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 				Stderr:       resData.Stderr,
 			})
 			if err != nil {
-				return fmt.Errorf("CreateTestcaseExecution failed: %v", err)
+				return fmt.Errorf("CreateTestcaseResult failed: %v", err)
 			}
 			p.results <- TaskExecResult{
 				Task:   &payload,
@@ -166,7 +166,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 			return fmt.Errorf("json.Decode failed: %v", err)
 		}
 		if resData.Result != "success" {
-			err := p.q.CreateTestcaseExecution(ctx, db.CreateTestcaseExecutionParams{
+			err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{
 				SubmissionID: submissionID,
 				TestcaseID:   testcase.TestcaseID,
 				Status:       resData.Result,
@@ -174,7 +174,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 				Stderr:       resData.Stderr,
 			})
 			if err != nil {
-				return fmt.Errorf("CreateTestcaseExecution failed: %v", err)
+				return fmt.Errorf("CreateTestcaseResult failed: %v", err)
 			}
 			p.results <- TaskExecResult{
 				Task:   &payload,
@@ -182,8 +182,8 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 			}
 			return fmt.Errorf("testrun failed: %v", resData.Stderr)
 		}
-		if !isTestcaseExecutionCorrect(testcase.Stdout, resData.Stdout) {
-			err := p.q.CreateTestcaseExecution(ctx, db.CreateTestcaseExecutionParams{
+		if !isTestcaseResultCorrect(testcase.Stdout, resData.Stdout) {
+			err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{
 				SubmissionID: submissionID,
 				TestcaseID:   testcase.TestcaseID,
 				Status:       "wrong_answer",
@@ -191,7 +191,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 				Stderr:       resData.Stderr,
 			})
 			if err != nil {
-				return fmt.Errorf("CreateTestcaseExecution failed: %v", err)
+				return fmt.Errorf("CreateTestcaseResult failed: %v", err)
 			}
 			p.results <- TaskExecResult{
 				Task:   &payload,
@@ -208,7 +208,7 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 	return nil
 }
 
-func isTestcaseExecutionCorrect(expectedStdout, actualStdout string) bool {
+func isTestcaseResultCorrect(expectedStdout, actualStdout string) bool {
 	expectedStdout = strings.TrimSpace(expectedStdout)
 	actualStdout = strings.TrimSpace(actualStdout)
 	return actualStdout == expectedStdout
-- 
cgit v1.3.1


From 0e8410f39e91b3b11063cb940926107639a2bc89 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 7 Aug 2024 23:21:16 +0900
Subject: feat(backend): add created_at column to testcase_results

---
 backend/db/models.go | 1 +
 backend/schema.sql   | 1 +
 2 files changed, 2 insertions(+)

(limited to 'backend/schema.sql')

diff --git a/backend/db/models.go b/backend/db/models.go
index a8729e4..49e4b52 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -53,6 +53,7 @@ type TestcaseResult struct {
 	Status           string
 	Stdout           string
 	Stderr           string
+	CreatedAt        pgtype.Timestamp
 }
 
 type User struct {
diff --git a/backend/schema.sql b/backend/schema.sql
index 3cc7ed9..53c68ec 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -71,6 +71,7 @@ CREATE TABLE testcase_results (
     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)
 );
-- 
cgit v1.3.1


From 14f48b4e4f7463e0972c2a5b39e94ee674ec93ba Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Wed, 7 Aug 2024 23:21:47 +0900
Subject: feat(backend): add unique constraint to testcase_results

---
 backend/schema.sql | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'backend/schema.sql')

diff --git a/backend/schema.sql b/backend/schema.sql
index 53c68ec..7188d72 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -73,6 +73,7 @@ CREATE TABLE testcase_results (
     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 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);
-- 
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/schema.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

