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 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 6d35f66e3502b61c0386f719d3a5f3caf0e109b6 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Mon, 29 Jul 2024 23:37:54 +0900 Subject: feat: separate Makefile and compose.yaml for local/prod envs --- Makefile | 52 +++++++++++++++++------------- Makefile.prod | 39 +++++++++++++++++++++++ compose.local.yaml | 54 ++++++++++++++++++++++++++++++++ compose.prod.yaml | 63 +++++++++++++++++++++++++++++++++++++ compose.yaml | 92 ------------------------------------------------------ 5 files changed, 187 insertions(+), 113 deletions(-) create mode 100644 Makefile.prod create mode 100644 compose.local.yaml create mode 100644 compose.prod.yaml delete mode 100644 compose.yaml diff --git a/Makefile b/Makefile index 07f682f..b15a2bb 100644 --- a/Makefile +++ b/Makefile @@ -1,42 +1,52 @@ +DOCKER_COMPOSE := docker compose -f compose.local.yaml + .PHONY: build build: - docker compose build + ${DOCKER_COMPOSE} build + cd frontend; npm install .PHONY: up up: - docker compose up -d + ${DOCKER_COMPOSE} up -d + cd frontend; npm run dev .PHONY: down down: - docker compose down - -.PHONY: api-server-only-build -api-server-only-build: - docker compose build api-server-only - -.PHONY: api-server-only-up -api-server-only-up: - docker compose up -d api-server-only + ${DOCKER_COMPOSE} down -.PHONY: api-server-only-down -api-server-only-down: - docker compose down api-server-only db +.PHONY: logs +logs: + ${DOCKER_COMPOSE} logs .PHONY: psql psql: - docker compose exec db psql --user=postgres albatross + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} exec db psql --user=postgres albatross + +.PHONY: psql-query +psql-query: + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} exec --no-TTY db psql --user=postgres albatross .PHONY: sqldef-dryrun sqldef-dryrun: down - docker compose build db - docker compose up -d db - docker compose run --no-TTY tools psqldef --dry-run < ./backend/schema.sql + ${DOCKER_COMPOSE} build db + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} run --no-TTY tools psqldef --dry-run < ./backend/schema.sql .PHONY: sqldef sqldef: down - docker compose build db - docker compose up -d db - docker compose run --no-TTY tools psqldef < ./backend/schema.sql + ${DOCKER_COMPOSE} build db + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} run --no-TTY tools psqldef < ./backend/schema.sql + +.PHONY: init +init: build initdb + +.PHONY: initdb +initdb: + make psql-query < ./backend/schema.sql + make psql-query < ./backend/fixtures/dev.sql .PHONY: oapi-codegen oapi-codegen: diff --git a/Makefile.prod b/Makefile.prod new file mode 100644 index 0000000..05901f0 --- /dev/null +++ b/Makefile.prod @@ -0,0 +1,39 @@ +DOCKER_COMPOSE := docker compose -f compose.prod.yaml + +.PHONY: build +build: + ${DOCKER_COMPOSE} build + +.PHONY: up +up: + ${DOCKER_COMPOSE} up -d + +.PHONY: down +down: + ${DOCKER_COMPOSE} down + +.PHONY: logs +logs: + ${DOCKER_COMPOSE} logs + +.PHONY: psql +psql: + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} exec db psql --user=postgres albatross + +.PHONY: psql-query +psql-query: + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} exec --no-TTY db psql --user=postgres albatross + +.PHONY: sqldef-dryrun +sqldef-dryrun: down + ${DOCKER_COMPOSE} build db + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} run --no-TTY tools psqldef --dry-run < ./backend/schema.sql + +.PHONY: sqldef +sqldef: down + ${DOCKER_COMPOSE} build db + ${DOCKER_COMPOSE} up --wait db + ${DOCKER_COMPOSE} run --no-TTY tools psqldef < ./backend/schema.sql diff --git a/compose.local.yaml b/compose.local.yaml new file mode 100644 index 0000000..6e5604f --- /dev/null +++ b/compose.local.yaml @@ -0,0 +1,54 @@ +services: + api-server: + build: + context: ./backend + ports: + - '127.0.0.1:8002:80' + depends_on: + db: + condition: service_healthy + environment: + ALBATROSS_DB_HOST: db + ALBATROSS_DB_PORT: 5432 + ALBATROSS_DB_USER: postgres + ALBATROSS_DB_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo + ALBATROSS_DB_NAME: albatross + restart: always + + db: + image: postgres:16.3 + environment: + POSTGRES_USER: postgres + POSTGRES_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo + POSTGRES_DB: albatross + expose: + - 5432 + healthcheck: + test: ["CMD-SHELL", "pg_isready -U postgres"] + interval: 10s + timeout: 5s + retries: 5 + volumes: + - db-data:/var/lib/postgresql/data + restart: always + + worker: + build: + context: ./worker + expose: + - 80 + restart: always + + tools: + build: + context: ./backend + dockerfile: ./Dockerfile.tools + environment: + POSTGRES_USER: postgres + POSTGRES_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo + POSTGRES_DB: albatross + profiles: + - tools + +volumes: + db-data: diff --git a/compose.prod.yaml b/compose.prod.yaml new file mode 100644 index 0000000..a7c64ab --- /dev/null +++ b/compose.prod.yaml @@ -0,0 +1,63 @@ +services: + reverse-proxy: + image: nginx:1.27 + ports: + - '127.0.0.1:8002:80' + volumes: + - ./nginx.conf:/etc/nginx/nginx.conf:ro + depends_on: + - api-server + - app-server + restart: always + + api-server: + build: + context: ./backend + expose: + - 80 + depends_on: + db: + condition: service_healthy + environment: + ALBATROSS_DB_HOST: db + ALBATROSS_DB_PORT: 5432 + ALBATROSS_DB_USER: postgres + ALBATROSS_DB_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo + ALBATROSS_DB_NAME: albatross + restart: always + + app-server: + build: + context: ./frontend + args: + ALBATROSS_HOST: localhost + expose: + - 80 + restart: always + + db: + image: postgres:16.3 + environment: + POSTGRES_USER: postgres + POSTGRES_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo + POSTGRES_DB: albatross + expose: + - 5432 + healthcheck: + test: ["CMD-SHELL", "pg_isready -U postgres"] + interval: 10s + timeout: 5s + retries: 5 + volumes: + - db-data:/var/lib/postgresql/data + restart: always + + worker: + build: + context: ./worker + expose: + - 80 + restart: always + +volumes: + db-data: diff --git a/compose.yaml b/compose.yaml deleted file mode 100644 index a98b918..0000000 --- a/compose.yaml +++ /dev/null @@ -1,92 +0,0 @@ -services: - reverse-proxy: - image: nginx:1.27 - ports: - - '127.0.0.1:8002:80' - volumes: - - ./nginx.conf:/etc/nginx/nginx.conf:ro - depends_on: - - api-server - - app-server - restart: always - - api-server: - build: - context: ./backend - expose: - - 80 - depends_on: - db: - condition: service_healthy - environment: - ALBATROSS_DB_HOST: db - ALBATROSS_DB_PORT: 5432 - ALBATROSS_DB_USER: postgres - ALBATROSS_DB_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo - ALBATROSS_DB_NAME: albatross - restart: always - - api-server-only: - build: - context: ./backend - ports: - - '127.0.0.1:8002:80' - depends_on: - db: - condition: service_healthy - environment: - ALBATROSS_DB_HOST: db - ALBATROSS_DB_PORT: 5432 - ALBATROSS_DB_USER: postgres - ALBATROSS_DB_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo - ALBATROSS_DB_NAME: albatross - restart: always - profiles: - - api-server-only - - app-server: - build: - context: ./frontend - args: - ALBATROSS_HOST: localhost - expose: - - 80 - restart: always - - db: - image: postgres:16.3 - environment: - POSTGRES_USER: postgres - POSTGRES_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo - POSTGRES_DB: albatross - expose: - - 5432 - healthcheck: - test: ["CMD-SHELL", "pg_isready -U postgres"] - interval: 10s - timeout: 5s - retries: 5 - volumes: - - db-data:/var/lib/postgresql/data - restart: always - - worker: - build: - context: ./worker - expose: - - 80 - restart: always - - tools: - build: - context: ./backend - dockerfile: ./Dockerfile.tools - environment: - POSTGRES_USER: postgres - POSTGRES_PASSWORD: eepei5reesoo0ov2ceelahd4Emi0au8ahJa6oochohheiquahweihoovahsee1oo - POSTGRES_DB: albatross - profiles: - - tools - -volumes: - db-data: -- 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(-) 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 From 0908e5d2041ded22969da7d66ead59d5037d40dd Mon Sep 17 00:00:00 2001 From: nsfisis Date: Tue, 30 Jul 2024 00:05:42 +0900 Subject: doc: add docs/DEV.md --- docs/DEV.md | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) create mode 100644 docs/DEV.md diff --git a/docs/DEV.md b/docs/DEV.md new file mode 100644 index 0000000..7e8bcd6 --- /dev/null +++ b/docs/DEV.md @@ -0,0 +1,18 @@ +# Dependencies + +* Docker +* Docker Compose +* Node.js 20.0.0 or later +* Npm +* Go 1.22.3 or later + +# Run + +1. Clone the repository. +1. `cd path/to/the/repo` +1. `make init` +1. `make up` +1. Access to http://localhost:5173. + * User `a`, `b` and `c` can log in with any password. + * User `a` and `b` are players. + * User `c` is an administrator. -- cgit v1.2.3-70-g09d2