diff options
| -rw-r--r-- | public/join.js | 4 | ||||
| -rw-r--r-- | server.js | 49 | ||||
| -rw-r--r-- | tools/sql/schema.txt | 3 | ||||
| -rw-r--r-- | views/games.ejs | 3 | ||||
| -rw-r--r-- | views/info.ejs | 2 |
5 files changed, 43 insertions, 18 deletions
diff --git a/public/join.js b/public/join.js index 4c2b0a8..a5a2e3a 100644 --- a/public/join.js +++ b/public/join.js @@ -77,9 +77,7 @@ function start_event_source() { } function is_your_turn(player, role) { - if (player.user_id === user_id) - return (game.active_role === role || game.active_role === "Both" || game.active_role === "All"); - return false; + return (game.active_role === role || game.active_role === "Both" || game.active_role === "All"); } function update() { @@ -556,18 +556,37 @@ for (let title_id of db.prepare("SELECT * FROM titles").pluck().all()) { } } -const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?"); const QUERY_LIST_GAMES_OF_TITLE = db.prepare(` - SELECT * FROM game_view - WHERE title_id = ? AND private = 0 + SELECT *, + EXISTS ( + SELECT 1 FROM players + WHERE players.game_id = game_view.game_id + AND user_id = $user_id + AND active_role IN ( 'All', 'Both', role ) + ) AS is_your_turn + FROM game_view + WHERE title_id = $title_id AND private = 0 ORDER BY status ASC, mtime DESC `); + const QUERY_LIST_GAMES_OF_USER = db.prepare(` - SELECT DISTINCT game_view.*, ( active_id = $user_id OR active_role = 'Both' OR active_role = 'All' ) AS is_your_turn FROM game_view - LEFT JOIN players ON game_view.game_id = players.game_id - WHERE game_view.owner_id = $user_id OR players.user_id = $user_id + SELECT *, + EXISTS ( + SELECT 1 FROM players + WHERE players.game_id = game_view.game_id + AND user_id = $user_id + AND active_role IN ( 'All', 'Both', role ) + ) AS is_your_turn + FROM game_view + WHERE owner_id = $user_id + OR EXISTS ( + SELECT 1 FROM players + WHERE players.game_id = game_view.game_id + AND user_id = $user_id + ) ORDER BY status ASC, mtime DESC `); + const QUERY_PLAYERS = db.prepare("SELECT role, user_id, user_name FROM player_view WHERE game_id = ?"); const QUERY_PLAYERS_FULL = db.prepare(` SELECT @@ -581,6 +600,7 @@ const QUERY_PLAYERS_FULL = db.prepare(` WHERE players.game_id = ? `); +const QUERY_GAME = db.prepare("SELECT * FROM game_view WHERE game_id = ?"); const QUERY_TITLE = db.prepare("SELECT * FROM titles WHERE title_id = ?"); const QUERY_ROLES = db.prepare("SELECT role FROM roles WHERE title_id = ? ORDER BY rowid").pluck(); const QUERY_GAME_OWNER = db.prepare("SELECT * FROM games WHERE game_id = ? AND owner_id = ?"); @@ -648,7 +668,7 @@ app.get('/info/:title_id', function (req, res) { return res.redirect('/'); } if (req.isAuthenticated()) { - let games = QUERY_LIST_GAMES_OF_TITLE.all(title_id); + let games = QUERY_LIST_GAMES_OF_TITLE.all({user_id: req.user.user_id, title_id: title_id}); humanize(games); let open_games = games.filter(game => game.status === 0); let active_games = games.filter(game => game.status === 1); @@ -1379,11 +1399,22 @@ app.get('/users', function (req, res) { res.render('users.ejs', { user: req.user, message: req.flash('message'), userList: rows }); }); -const QUERY_LIST_GAMES = db.prepare("SELECT * FROM game_view WHERE private = 0 AND status < 2"); +const QUERY_LIST_GAMES = db.prepare(` + SELECT *, + EXISTS ( + SELECT 1 FROM players + WHERE players.game_id = game_view.game_id + AND user_id = $user_id + AND active_role IN ( 'All', 'Both', role ) + ) AS is_your_turn + FROM game_view + WHERE private = 0 AND status < 2 + ORDER BY status ASC, mtime DESC +`); app.get('/games', must_be_logged_in, function (req, res) { LOG(req, "GET /join"); - let games = QUERY_LIST_GAMES.all(); + let games = QUERY_LIST_GAMES.all({user_id: req.user.user_id}); humanize(games); let open_games = games.filter(game => game.status === 0); let active_games = games.filter(game => game.status === 1); diff --git a/tools/sql/schema.txt b/tools/sql/schema.txt index 71b6f72..e731c8c 100644 --- a/tools/sql/schema.txt +++ b/tools/sql/schema.txt @@ -153,12 +153,9 @@ CREATE VIEW game_view AS , games.status , games.result , games.active AS active_role - , active.user_id AS active_id - , active.user_name AS active_name -- , games.state FROM games JOIN titles USING ( title_id ) JOIN users AS owner ON games.owner_id = owner.user_id LEFT JOIN player_list_view AS players USING ( game_id ) - LEFT JOIN player_view AS active ON games.game_id = active.game_id AND games.active = active.role ; diff --git a/views/games.ejs b/views/games.ejs index a355910..e25df1f 100644 --- a/views/games.ejs +++ b/views/games.ejs @@ -33,10 +33,9 @@ td.nowrap a { color: black; text-decoration: none; } <td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> -<td><%= row.active_role %> +<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %> <td><a href="/join/<%= row.game_id %>">Enter</a> <% }); } else { %> <tr><td colspan="8">No active games. <% } %> </table> - diff --git a/views/info.ejs b/views/info.ejs index fdfc974..778974e 100644 --- a/views/info.ejs +++ b/views/info.ejs @@ -38,7 +38,7 @@ Read more about the game on <td><%= row.player_names %> <td><%= row.description %> <td class="nowrap"><%= row.mtime %> -<td><%= row.active_role %> +<td class="<%= row.is_your_turn ? "is_your_turn" : "" %>"><%= row.active_role %> <td><a href="/join/<%= row.game_id %>">Enter</a> <% }); %> </table> |