From 1520f8c87a4ab9ff4bd2e8af6debf8f361e0582e Mon Sep 17 00:00:00 2001 From: nsfisis Date: Sun, 28 Jul 2024 13:03:17 +0900 Subject: feat(backend): change games table and create problems table --- backend/schema.sql | 18 ++++++++++++++---- 1 file changed, 14 insertions(+), 4 deletions(-) (limited to 'backend/schema.sql') diff --git a/backend/schema.sql b/backend/schema.sql index 6696242..1cd0700 100644 --- a/backend/schema.sql +++ b/backend/schema.sql @@ -16,8 +16,18 @@ CREATE TABLE user_auths ( ); CREATE TABLE games ( - game_id SERIAL PRIMARY KEY, - type VARCHAR(255) NOT NULL, - created_at TIMESTAMP NOT NULL DEFAULT NOW(), - state VARCHAR(255) NOT NULL + game_id SERIAL PRIMARY KEY, + state VARCHAR(32) NOT NULL, + display_name VARCHAR(255) NOT NULL, + duration_seconds INT NOT NULL, + created_at TIMESTAMP NOT NULL DEFAULT NOW(), + started_at TIMESTAMP, + problem_id INT, + CONSTRAINT fk_problem_id FOREIGN KEY(problem_id) REFERENCES problems(problem_id) +); + +CREATE TABLE problems ( + problem_id SERIAL PRIMARY KEY, + title VARCHAR(255) NOT NULL, + description TEXT NOT NULL ); -- cgit v1.2.3-70-g09d2 From d85cf0be57dd4394c588c340fbfa8483d981da02 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Sun, 28 Jul 2024 15:58:34 +0900 Subject: feat(backend): add game_players table --- backend/db/models.go | 5 +++++ backend/schema.sql | 8 ++++++++ 2 files changed, 13 insertions(+) (limited to 'backend/schema.sql') diff --git a/backend/db/models.go b/backend/db/models.go index e60cb8a..cc08817 100644 --- a/backend/db/models.go +++ b/backend/db/models.go @@ -18,6 +18,11 @@ type Game struct { ProblemID pgtype.Int4 } +type GamePlayer struct { + GameID int32 + UserID int32 +} + type Problem struct { ProblemID int32 Title string diff --git a/backend/schema.sql b/backend/schema.sql index 1cd0700..f0b5b37 100644 --- a/backend/schema.sql +++ b/backend/schema.sql @@ -26,6 +26,14 @@ CREATE TABLE games ( CONSTRAINT fk_problem_id FOREIGN KEY(problem_id) REFERENCES problems(problem_id) ); +CREATE TABLE game_players ( + game_id INT NOT NULL, + user_id INT NOT NULL, + PRIMARY KEY (game_id, user_id), + 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 problems ( problem_id SERIAL PRIMARY KEY, title VARCHAR(255) NOT NULL, -- cgit v1.2.3-70-g09d2