feat(backend): create table `submissions` and `testcase_execution`

1 files changed, 23 insertions, 0 deletions

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);