From 2705f09f5c52b25af07a1d7ccec546de68ff32e9 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Mon, 5 Aug 2024 02:18:22 +0900 Subject: feat(backend): add `game_type` column to `games` --- backend/db/query.sql.go | 12 +++++++++--- 1 file changed, 9 insertions(+), 3 deletions(-) (limited to 'backend/db/query.sql.go') diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index 074e767..464d7f8 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -12,7 +12,7 @@ import ( ) const getGameByID = `-- name: GetGameByID :one -SELECT game_id, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games +SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games LEFT JOIN problems ON games.problem_id = problems.problem_id WHERE games.game_id = $1 LIMIT 1 @@ -20,6 +20,7 @@ LIMIT 1 type GetGameByIDRow struct { GameID int32 + GameType string State string DisplayName string DurationSeconds int32 @@ -36,6 +37,7 @@ func (q *Queries) GetGameByID(ctx context.Context, gameID int32) (GetGameByIDRow var i GetGameByIDRow err := row.Scan( &i.GameID, + &i.GameType, &i.State, &i.DisplayName, &i.DurationSeconds, @@ -108,12 +110,13 @@ func (q *Queries) GetUserByID(ctx context.Context, userID int32) (User, error) { } const listGames = `-- name: ListGames :many -SELECT game_id, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games +SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games LEFT JOIN problems ON games.problem_id = problems.problem_id ` type ListGamesRow struct { GameID int32 + GameType string State string DisplayName string DurationSeconds int32 @@ -136,6 +139,7 @@ func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) { var i ListGamesRow if err := rows.Scan( &i.GameID, + &i.GameType, &i.State, &i.DisplayName, &i.DurationSeconds, @@ -157,7 +161,7 @@ func (q *Queries) ListGames(ctx context.Context) ([]ListGamesRow, error) { } const listGamesForPlayer = `-- name: ListGamesForPlayer :many -SELECT games.game_id, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, game_players.game_id, user_id FROM games +SELECT games.game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description, game_players.game_id, user_id FROM games LEFT JOIN problems ON games.problem_id = problems.problem_id JOIN game_players ON games.game_id = game_players.game_id WHERE game_players.user_id = $1 @@ -165,6 +169,7 @@ WHERE game_players.user_id = $1 type ListGamesForPlayerRow struct { GameID int32 + GameType string State string DisplayName string DurationSeconds int32 @@ -189,6 +194,7 @@ func (q *Queries) ListGamesForPlayer(ctx context.Context, userID int32) ([]ListG var i ListGamesForPlayerRow if err := rows.Scan( &i.GameID, + &i.GameType, &i.State, &i.DisplayName, &i.DurationSeconds, -- cgit v1.2.3-70-g09d2 From 968b835d0e6fe960c3d7ab915ef6d9cecec4ab7f Mon Sep 17 00:00:00 2001 From: nsfisis Date: Mon, 5 Aug 2024 02:33:54 +0900 Subject: feat(backend): allow to edit `game_type` in admin page --- backend/admin/handlers.go | 4 ++++ backend/admin/templates/game_edit.html | 13 ++++++++++--- backend/admin/templates/games.html | 2 +- backend/db/query.sql.go | 13 ++++++++----- backend/query.sql | 11 ++++++----- 5 files changed, 29 insertions(+), 14 deletions(-) (limited to 'backend/db/query.sql.go') diff --git a/backend/admin/handlers.go b/backend/admin/handlers.go index 14523e6..d9a6977 100644 --- a/backend/admin/handlers.go +++ b/backend/admin/handlers.go @@ -130,6 +130,7 @@ func (h *AdminHandler) getGames(c echo.Context) error { } games[i] = echo.Map{ "GameID": g.GameID, + "GameType": g.GameType, "State": g.State, "DisplayName": g.DisplayName, "DurationSeconds": g.DurationSeconds, @@ -167,6 +168,7 @@ func (h *AdminHandler) getGameEdit(c echo.Context) error { "Title": "Game Edit", "Game": echo.Map{ "GameID": row.GameID, + "GameType": row.GameType, "State": row.State, "DisplayName": row.DisplayName, "DurationSeconds": row.DurationSeconds, @@ -190,6 +192,7 @@ func (h *AdminHandler) postGameEdit(c echo.Context) error { } } + gameType := c.FormValue("game_type") state := c.FormValue("state") displayName := c.FormValue("display_name") durationSeconds, err := strconv.Atoi(c.FormValue("duration_seconds")) @@ -247,6 +250,7 @@ func (h *AdminHandler) postGameEdit(c echo.Context) error { err = h.q.UpdateGame(c.Request().Context(), db.UpdateGameParams{ GameID: int32(gameID), + GameType: gameType, State: state, DisplayName: displayName, DurationSeconds: int32(durationSeconds), diff --git a/backend/admin/templates/game_edit.html b/backend/admin/templates/game_edit.html index 8bc5410..764b577 100644 --- a/backend/admin/templates/game_edit.html +++ b/backend/admin/templates/game_edit.html @@ -14,12 +14,19 @@ +
+ + +
- - - + + diff --git a/backend/admin/templates/games.html b/backend/admin/templates/games.html index 244fc94..47dc4a3 100644 --- a/backend/admin/templates/games.html +++ b/backend/admin/templates/games.html @@ -9,7 +9,7 @@ {{ range .Games }}
  • - {{ .DisplayName }} (id={{ .GameID }}) + {{ .DisplayName }} (id={{ .GameID }} type={{ .GameType }} state={{ .State }})
    • {{ end }} diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index 464d7f8..c574ca8 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -251,16 +251,18 @@ func (q *Queries) ListUsers(ctx context.Context) ([]User, error) { const updateGame = `-- name: UpdateGame :exec UPDATE games SET - state = $2, - display_name = $3, - duration_seconds = $4, - started_at = $5, - problem_id = $6 + game_type = $2, + state = $3, + display_name = $4, + duration_seconds = $5, + started_at = $6, + problem_id = $7 WHERE game_id = $1 ` type UpdateGameParams struct { GameID int32 + GameType string State string DisplayName string DurationSeconds int32 @@ -271,6 +273,7 @@ type UpdateGameParams struct { func (q *Queries) UpdateGame(ctx context.Context, arg UpdateGameParams) error { _, err := q.db.Exec(ctx, updateGame, arg.GameID, + arg.GameType, arg.State, arg.DisplayName, arg.DurationSeconds, diff --git a/backend/query.sql b/backend/query.sql index 886f96c..39b0b21 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -41,9 +41,10 @@ LIMIT 1; -- name: UpdateGame :exec UPDATE games SET - state = $2, - display_name = $3, - duration_seconds = $4, - started_at = $5, - problem_id = $6 + game_type = $2, + state = $3, + display_name = $4, + duration_seconds = $5, + started_at = $6, + problem_id = $7 WHERE game_id = $1; -- cgit v1.2.3-70-g09d2 From 498ad23eaaae1e856c71f44ad0bf3912a6d7e5b7 Mon Sep 17 00:00:00 2001 From: nsfisis Date: Mon, 5 Aug 2024 03:00:48 +0900 Subject: feat(backend): wait for all players to enter --- backend/db/query.sql.go | 46 ++++++++++++++++++++++++++++++++++++++++++++++ backend/game/hub.go | 10 ++++++++-- backend/game/models.go | 1 + backend/query.sql | 5 +++++ 4 files changed, 60 insertions(+), 2 deletions(-) (limited to 'backend/db/query.sql.go') diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go index c574ca8..8df3bf5 100644 --- a/backend/db/query.sql.go +++ b/backend/db/query.sql.go @@ -109,6 +109,52 @@ func (q *Queries) GetUserByID(ctx context.Context, userID int32) (User, error) { return i, err } +const listGamePlayers = `-- name: ListGamePlayers :many +SELECT game_id, game_players.user_id, users.user_id, username, display_name, icon_path, is_admin, created_at FROM game_players +LEFT JOIN users ON game_players.user_id = users.user_id +WHERE game_players.game_id = $1 +` + +type ListGamePlayersRow struct { + GameID int32 + UserID int32 + UserID_2 *int32 + Username *string + DisplayName *string + IconPath *string + IsAdmin *bool + CreatedAt pgtype.Timestamp +} + +func (q *Queries) ListGamePlayers(ctx context.Context, gameID int32) ([]ListGamePlayersRow, error) { + rows, err := q.db.Query(ctx, listGamePlayers, gameID) + if err != nil { + return nil, err + } + defer rows.Close() + var items []ListGamePlayersRow + for rows.Next() { + var i ListGamePlayersRow + if err := rows.Scan( + &i.GameID, + &i.UserID, + &i.UserID_2, + &i.Username, + &i.DisplayName, + &i.IconPath, + &i.IsAdmin, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + const listGames = `-- name: ListGames :many SELECT game_id, game_type, state, display_name, duration_seconds, created_at, started_at, games.problem_id, problems.problem_id, title, description FROM games LEFT JOIN problems ON games.problem_id = problems.problem_id diff --git a/backend/game/hub.go b/backend/game/hub.go index 239f5da..70bf71f 100644 --- a/backend/game/hub.go +++ b/backend/game/hub.go @@ -81,7 +81,7 @@ func (hub *gameHub) run() { entriedPlayerCount++ } } - if entriedPlayerCount == 2 { + if entriedPlayerCount == hub.game.playerCount { err := hub.q.UpdateGameState(hub.ctx, db.UpdateGameStateParams{ GameID: int32(hub.game.gameID), State: string(gameStateWaitingStart), @@ -101,7 +101,7 @@ func (hub *gameHub) run() { readyPlayerCount++ } } - if readyPlayerCount == 2 { + if readyPlayerCount == hub.game.playerCount { startAt := time.Now().Add(11 * time.Second).UTC() for player := range hub.players { player.s2cMessages <- &playerMessageS2CStart{ @@ -300,6 +300,11 @@ func (hubs *GameHubs) RestoreFromDB(ctx context.Context) error { description: *row.Description, } } + // TODO: N+1 + playerRows, err := hubs.q.ListGamePlayers(ctx, int32(row.GameID)) + if err != nil { + return err + } hubs.hubs[int(row.GameID)] = newGameHub(ctx, &game{ gameID: int(row.GameID), durationSeconds: int(row.DurationSeconds), @@ -307,6 +312,7 @@ func (hubs *GameHubs) RestoreFromDB(ctx context.Context) error { displayName: row.DisplayName, startedAt: startedAt, problem: problem_, + playerCount: len(playerRows), }, hubs.q) } return nil diff --git a/backend/game/models.go b/backend/game/models.go index e96184a..4080482 100644 --- a/backend/game/models.go +++ b/backend/game/models.go @@ -30,6 +30,7 @@ type game struct { durationSeconds int startedAt *time.Time problem *problem + playerCount int } type problem struct { diff --git a/backend/query.sql b/backend/query.sql index 39b0b21..245d5cf 100644 --- a/backend/query.sql +++ b/backend/query.sql @@ -38,6 +38,11 @@ LEFT JOIN problems ON games.problem_id = problems.problem_id WHERE games.game_id = $1 LIMIT 1; +-- name: ListGamePlayers :many +SELECT * FROM game_players +LEFT JOIN users ON game_players.user_id = users.user_id +WHERE game_players.game_id = $1; + -- name: UpdateGame :exec UPDATE games SET -- cgit v1.2.3-70-g09d2