From 940fc430a5c8fa7a1454cc84586eaca2722ac757 Mon Sep 17 00:00:00 2001
From: nsfisis <nsfisis@gmail.com>
Date: Mon, 5 Aug 2024 04:36:02 +0900
Subject: feat(backend): create table `submissions` and `testcase_execution`

---
 backend/schema.sql | 23 +++++++++++++++++++++++
 1 file changed, 23 insertions(+)

(limited to 'backend/schema.sql')

diff --git a/backend/schema.sql b/backend/schema.sql
index 4a1d4b0..d0b6c40 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -52,3 +52,26 @@ CREATE TABLE testcases (
     CONSTRAINT fk_problem_id FOREIGN KEY(problem_id) REFERENCES problems(problem_id)
 );
 CREATE INDEX idx_testcases_problem_id ON testcases(problem_id);
+
+CREATE TABLE submissions (
+    submission_id SERIAL    PRIMARY KEY,
+    game_id       INT       NOT NULL,
+    user_id       INT       NOT NULL,
+    code          TEXT      NOT NULL,
+    code_size     INT       NOT NULL,
+    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
+    CONSTRAINT fk_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
+    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,
+    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);
-- 
cgit v1.2.3-70-g09d2

