From 75566438c2ba942abf3849b724e318cfa050f1ad Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Fri, 22 Dec 2023 17:32:02 +0100 Subject: Move more player/game info stuff into SQL. Update invite_count with triggers. Add games.is_ready generated column. Add games.is_opposed generated column. Add player_view with is_active column. --- public/join.js | 20 ++++++-------------- schema.sql | 55 ++++++++++++++++++++++++++++++++++++++++++------------- server.js | 45 +++++++++++++-------------------------------- views/head.pug | 15 ++++----------- views/join.pug | 2 +- 5 files changed, 66 insertions(+), 71 deletions(-) diff --git a/public/join.js b/public/join.js index 6ae4deb..e0f6c3c 100644 --- a/public/join.js +++ b/public/join.js @@ -129,20 +129,12 @@ function is_enemy(p) { return blacklist && blacklist.includes(p.user_id) } -function is_active(player, role) { - return (game.active === role || game.active === "Both" || game.active === "All") -} - -function is_solo() { - return players.every(p => p.user_id === players[0].user_id) -} - function user_link(player) { return `${player.name}` } function play_link(player) { - return `\xbb Play` + return `\xbb ${player.name}` } function action_link(player, action, color, text) { @@ -193,7 +185,7 @@ function update_no_login() { if (game.is_match) { if (player) { if (game.status === 1) - element.classList.toggle("is_active", is_active(player, role)) + element.classList.toggle("is_active", player.is_active) element.innerHTML = user_link(player) } else { element.innerHTML = `Empty` @@ -215,7 +207,7 @@ function update_no_login() { element.classList.add("is_invite") element.innerHTML = user_link(player) + " ?" } else { - element.classList.toggle("is_active", is_active(player, role)) + element.classList.toggle("is_active", player.is_active) element.innerHTML = user_link(player) } break @@ -248,7 +240,7 @@ function update_login() { if (game.is_match) { if (player) { if (game.status === 1) - element.classList.toggle("is_active", is_active(player, role)) + element.classList.toggle("is_active", player.is_active) if (player.user_id === user_id && (game.status === 1 || game.status === 2)) element.innerHTML = play_link(player) else @@ -283,7 +275,7 @@ function update_login() { else element.innerHTML = user_link(player) + " ?" } else { - element.classList.toggle("is_active", is_active(player, role)) + element.classList.toggle("is_active", player.is_active) if (player.user_id === user_id) element.innerHTML = play_link(player) + action_link(player, "part", "red", "\u274c") else if (game.owner_id === user_id) @@ -344,7 +336,7 @@ function update_login() { if (game.owner_id === user_id) { window.start_button.disabled = !ready window.start_button.classList = (game.status === 0) ? "" : "hide" - window.delete_button.classList = (game.status === 0 || is_solo()) ? "" : "hide" + window.delete_button.classList = (game.status === 0 || game.user_count <= 1) ? "" : "hide" if (game.status === 0 && ready) start_blinker("READY TO START") else diff --git a/schema.sql b/schema.sql index f780af4..831be32 100644 --- a/schema.sql +++ b/schema.sql @@ -373,7 +373,10 @@ create table if not exists games ( mtime datetime default current_timestamp, moves integer default 0, active text, - result text + result text, + + is_opposed boolean generated as ( user_count = join_count and join_count > 1 ), + is_ready boolean generated as ( player_count = join_count and invite_count = 0 ) ); create index if not exists games_title_idx on games(title_id); @@ -447,8 +450,7 @@ create view game_view as select games.*, titles.title_name, - owner.name as owner_name, - user_count = join_count and join_count > 1 as is_opposed + owner.name as owner_name from games join titles using(title_id) @@ -456,6 +458,27 @@ create view game_view as on owner.user_id = games.owner_id ; +drop view if exists player_view; +create view player_view as + select + game_id, + user_id, + name, + role, + is_invite, + ( + case status + when 0 then owner_id = user_id + when 1 then active in ( 'Both', role ) + else 0 + end + ) as is_active + from + games + join players using(game_id) + join users using(user_id) + ; + drop view if exists ready_to_start_reminder; create view ready_to_start_reminder as select @@ -464,14 +487,7 @@ create view ready_to_start_reminder as games join users on user_id = owner_id where - status = 0 - and join_count = player_count - and not exists ( - select 1 from players - where - players.game_id = games.game_id - and is_invite - ) + status = 0 and is_ready ; drop view if exists your_turn_reminder; @@ -509,7 +525,8 @@ begin games set join_count = ( select count(1) from players where players.game_id = new.game_id ), - user_count = ( select count(distinct user_id) from players where players.game_id = new.game_id ) + user_count = ( select count(distinct user_id) from players where players.game_id = new.game_id ), + invite_count = ( select count(1) from players where players.game_id = new.game_id and players.is_invite ) where games.game_id = new.game_id; end; @@ -521,7 +538,19 @@ begin games set join_count = ( select count(1) from players where players.game_id = old.game_id ), - user_count = ( select count(distinct user_id) from players where players.game_id = old.game_id ) + user_count = ( select count(distinct user_id) from players where players.game_id = old.game_id ), + invite_count = ( select count(1) from players where players.game_id = old.game_id and players.is_invite ) + where + games.game_id = old.game_id; +end; + +drop trigger if exists trigger_accept_invite; +create trigger trigger_accept_invite after update of is_invite on players +begin + update + games + set + invite_count = ( select count(1) from players where players.game_id = new.game_id and players.is_invite ) where games.game_id = old.game_id; end; diff --git a/server.js b/server.js index b30bbfd..278cc6e 100644 --- a/server.js +++ b/server.js @@ -176,6 +176,7 @@ app.locals.ENABLE_MAIL = !!mailer app.locals.ENABLE_WEBHOOKS = !!WEBHOOKS app.locals.ENABLE_FORUM = process.env.FORUM | 0 +app.locals.EMOJI_MATCH = "\u{1f3c6}" app.locals.EMOJI_LIVE = "\u{1f465}" app.locals.EMOJI_FAST = "\u{1f3c1}" app.locals.EMOJI_SLOW = "\u{1f40c}" @@ -1269,6 +1270,8 @@ const SQL_SELECT_PLAYER_NAME = SQL("SELECT name FROM players JOIN users using(us const SQL_INSERT_PLAYER_ROLE = SQL("INSERT OR IGNORE INTO players (game_id,role,user_id,is_invite) VALUES (?,?,?,?)") const SQL_DELETE_PLAYER_ROLE = SQL("DELETE FROM players WHERE game_id=? AND role=?") +const SQL_SELECT_PLAYERS_FULL = SQL("select * from player_view where game_id = ?") + const SQL_COUNT_OPEN_GAMES = SQL(`SELECT COUNT(*) FROM games WHERE owner_id=? AND status=${STATUS_OPEN}`).pluck() const SQL_COUNT_ACTIVE_GAMES = SQL(` select count(*) from games @@ -1408,18 +1411,6 @@ function check_join_game_limit(user) { return null } -function annotate_game_players(game) { - game.players = SQL_SELECT_PLAYERS_JOIN.all(game.game_id) - if (game.player_count === game.join_count) { - game.is_ready = true - for (let p of game.players) - if (p.is_invite) - game.is_ready = false - } else { - game.is_ready = false - } -} - function annotate_game_info(game, user_id, unread) { let options = parse_game_options(game.options) game.human_options = format_options(game.options, options) @@ -1430,24 +1421,18 @@ function annotate_game_info(game, user_id, unread) { let your_role = null let roles = get_game_roles(game.title_id, game.scenario, options) + + game.players = SQL_SELECT_PLAYERS_FULL.all(game.game_id) for (let p of game.players) p.index = roles.indexOf(p.role) game.players.sort((a, b) => a.index - b.index) game.player_names = "" for (let p of game.players) { - let p_is_owner = false - if (game.status === STATUS_OPEN && (game.owner_id === p.user_id)) - p_is_owner = true - - let p_is_active = false - if (game.status === STATUS_ACTIVE && (game.active === p.role || game.active === "Both")) - p_is_active = true - if (p.user_id === user_id) { your_role = p.role your_count++ - if (p_is_active && game.is_ready) + if (p.is_active && game.is_ready && game.status < 2) game.your_turn = true if (p.is_invite) game.your_turn = true @@ -1455,9 +1440,9 @@ function annotate_game_info(game, user_id, unread) { let link if (p.is_invite) - link = `${p.name}?` - else if (p_is_active || p_is_owner) - link = `${p.name}` + link = `${p.name}?` + else if (p.is_active) + link = `${p.name}` else link = `${p.name}` @@ -1466,10 +1451,8 @@ function annotate_game_info(game, user_id, unread) { else game.player_names = link - if (game.active === p.role) - game.active = link if (game.result === p.role) - game.result = `${link} (${game.result})` + game.result = `${p.name} (${game.result})` } if (game.status === STATUS_OPEN && game.is_ready && game.owner_id === user_id) @@ -1486,10 +1469,8 @@ function annotate_game_info(game, user_id, unread) { } function annotate_games(list, user_id, unread) { - for (let game of list) { - annotate_game_players(game) + for (let game of list) annotate_game_info(game, user_id, unread) - } return list } @@ -1810,7 +1791,7 @@ app.get('/join/:game_id', function (req, res) { game.human_options = format_options(game.options, options) let roles = get_game_roles(game.title_id, game.scenario, options) - let players = SQL_SELECT_PLAYERS_JOIN.all(game_id) + let players = SQL_SELECT_PLAYERS_FULL.all(game_id) let whitelist = null let blacklist = null @@ -1835,7 +1816,7 @@ app.get('/join/:game_id', function (req, res) { app.get('/join-events/:game_id', must_be_logged_in, function (req, res) { let game_id = req.params.game_id | 0 let game = SQL_SELECT_GAME_VIEW.get(game_id) - let players = SQL_SELECT_PLAYERS_JOIN.all(game_id) + let players = SQL_SELECT_PLAYERS_FULL.all(game_id) res.setHeader("Content-Type", "text/event-stream") res.setHeader("Connection", "keep-alive") diff --git a/views/head.pug b/views/head.pug index 77ab73b..e47033a 100644 --- a/views/head.pug +++ b/views/head.pug @@ -61,14 +61,10 @@ mixin gamelist(list,hide_title=0) else if (item.status === 2) className += " finished" else if (item.status === 3) className += " archived" if (item.is_unread) chat_icon = "\u{1f4dd}" - if (item.pace === 0) pace_icon = "", pace_text = "" + if (item.is_match) pace_icon = EMOJI_MATCH else if (item.pace === 1) pace_icon = EMOJI_LIVE, pace_text = "Live!" else if (item.pace === 2) pace_icon = EMOJI_FAST, pace_text = "Fast - many moves per day" else if (item.pace === 3) pace_icon = EMOJI_SLOW, pace_text = "Slow - one move per day" - let invite_count = 0 - for (let p of item.players) - if (p.is_invite) - invite_count++ div(class=className) div.game_head @@ -81,12 +77,9 @@ mixin gamelist(list,hide_title=0) case item.status when 0 - if item.join_count === item.player_count - a(class="command" href=`/join/${item.game_id}`) Start - else - a(class="command" href=`/join/${item.game_id}`) Join + a(class="command" href=`/join/${item.game_id}`) Join when 1 - if item.join_count !== item.player_count || invite_count > 0 + if !item.is_ready a(class="command" href="/join/"+item.game_id) Join else if item.is_yours if item.your_role @@ -124,7 +117,7 @@ mixin gamelist(list,hide_title=0) when 0 div Created: #{item.ctime} when 1 - div Changed: #{item.mtime} + div Last move: #{item.mtime} when 2 div Finished: #{item.mtime} div Result: !{item.result} diff --git a/views/join.pug b/views/join.pug index 2fc9287..37c245b 100644 --- a/views/join.pug +++ b/views/join.pug @@ -57,7 +57,7 @@ html if game.status > 1 p Finished #{game.mtime}. else if game.status > 0 - p Last changed #{game.mtime}. + p Last move #{game.mtime}. unless game.human_options === "None" p Options: #{game.human_options}. -- cgit v1.2.3