From b7e2d4571b61f20a9f4a288f41ba0a30e488eda4 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Mon, 29 Jul 2024 23:52:37 +0900 Subject: feat(backend): add fixture file for local dev --- backend/fixtures/dev.sql | 37 +++++++++++++++++++++++++++++++++++++ backend/schema.sql | 12 ++++++------ 2 files changed, 43 insertions(+), 6 deletions(-) create mode 100644 backend/fixtures/dev.sql (limited to 'backend') diff --git a/backend/fixtures/dev.sql b/backend/fixtures/dev.sql new file mode 100644 index 0000000..3549d86 --- /dev/null +++ b/backend/fixtures/dev.sql @@ -0,0 +1,37 @@ +INSERT INTO users +(username, display_name, icon_path, is_admin) +VALUES + ('a', 'TEST A', NULL, FALSE), + ('b', 'TEST B', NULL, FALSE), + ('c', 'TEST C', NULL, TRUE); + +INSERT INTO user_auths +(user_id, auth_type) +VALUES + (1, 'bypass'), + (2, 'bypass'), + (3, 'bypass'); + +INSERT INTO problems +(title, description) +VALUES + ('TEST problem 1', 'This is TEST problem 1'), + ('TEST problem 2', 'This is TEST problem 2'), + ('TEST problem 3', 'This is TEST problem 3'); + +INSERT INTO games +(state, display_name, duration_seconds, problem_id) +VALUES + ('waiting_entries', 'TEST game 1', 180, 1), + ('closed', 'TEST game 2', 180, 2), + ('finished', 'TEST game 3', 180, 3); + +INSERT INTO game_players +(game_id, user_id) +VALUES + (1, 1), + (1, 2), + (2, 1), + (2, 2), + (3, 1), + (3, 2); diff --git a/backend/schema.sql b/backend/schema.sql index 7f98d91..2a92271 100644 --- a/backend/schema.sql +++ b/backend/schema.sql @@ -15,6 +15,12 @@ CREATE TABLE user_auths ( 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, + description TEXT NOT NULL +); + CREATE TABLE games ( game_id SERIAL PRIMARY KEY, state VARCHAR(32) NOT NULL, @@ -34,12 +40,6 @@ CREATE TABLE game_players ( 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, - description TEXT NOT NULL -); - CREATE TABLE testcases ( testcase_id SERIAL PRIMARY KEY, problem_id INT NOT NULL, -- cgit v1.2.3-70-g09d2 From b6d9ec85c0247251c25c5f1a1f94114e412d196b Mon Sep 17 00:00:00 2001 From: nsfisis Date: Tue, 30 Jul 2024 00:10:40 +0900 Subject: fix(backend): add unique constraint to user_auths.user_id --- backend/schema.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'backend') diff --git a/backend/schema.sql b/backend/schema.sql index 2a92271..3a4422f 100644 --- a/backend/schema.sql +++ b/backend/schema.sql @@ -9,7 +9,7 @@ CREATE TABLE users ( CREATE TABLE user_auths ( user_auth_id SERIAL PRIMARY KEY, - user_id INT NOT NULL, + user_id INT NOT NULL UNIQUE, auth_type VARCHAR(16) NOT NULL, password_hash VARCHAR(256), CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id) -- cgit v1.2.3-70-g09d2