From 036babec3e9f93822b808a7a62dcf9485ddf3307 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Sun, 29 Sep 2024 01:36:11 +0200 Subject: Be more robust when deleting accounts. Leave player assignment to not mess with Elo ratings and tournament data. Reset ctime when game is actually started. --- public/join.js | 21 ++++++++++++++++----- schema.sql | 10 ++++------ server.js | 28 ++++++++++++++++++++++++---- 3 files changed, 44 insertions(+), 15 deletions(-) diff --git a/public/join.js b/public/join.js index 20a95e7..75e7aca 100644 --- a/public/join.js +++ b/public/join.js @@ -275,6 +275,8 @@ function user_link(user_name) { } function player_link(player) { + if (!player.name) + return "null" let link = user_link(player.name) if (player.is_invite) link = "" + link + " ?" @@ -332,11 +334,18 @@ function create_game_list() { create_game_list_item(list, "Pace", PACE_TEXT[game.pace]) create_game_list_item(list, "Notice", game.notice) - if (game.owner_id) - create_game_list_item(list, "Created", human_date(game.ctime) + " by " + user_link(game.owner_name)) - else - create_game_list_item(list, "Created", human_date(game.ctime)) + if (game.status === 0) { + if (game.owner_id) + create_game_list_item(list, "Created", human_date(game.ctime) + " by " + user_link(game.owner_name)) + else + create_game_list_item(list, "Created", human_date(game.ctime)) + } else { + if (game.owner_id) + create_game_list_item(list, "Started", human_date(game.ctime) + " by " + user_link(game.owner_name)) + else + create_game_list_item(list, "Started", human_date(game.ctime)) + } create_game_list_item(list, "Moves", game.moves) @@ -426,7 +435,9 @@ function update() { } if (game.status === 0) { - if (user_id) + if (game.is_ready) + window.game_enter.textContent = "Waiting to start." + else if (user_id) window.game_enter.textContent = "Waiting for players to join." else window.game_enter.innerHTML = `Login or sign up to join.` diff --git a/schema.sql b/schema.sql index ebe9a66..3ba249b 100644 --- a/schema.sql +++ b/schema.sql @@ -177,7 +177,7 @@ create view rating_view as title_id, name, rating, count, last from ratings - join users using(user_id) + left join users using(user_id) order by title_id, rating desc @@ -499,7 +499,7 @@ create view player_view as from games join players using(game_id) - join users using(user_id) + left join users using(user_id) left join user_last_seen using(user_id) ; @@ -538,7 +538,7 @@ create view game_export_view as json_object('role', role, 'name', name) ) from players - join users using(user_id) + left join users using(user_id) where game_id = outer.game_id ), 'state', @@ -683,9 +683,7 @@ begin delete from posts where author_id = old.user_id; delete from threads where author_id = old.user_id; delete from game_chat where user_id = old.user_id; - delete from ratings where user_id = old.user_id; - delete from players where user_id = old.user_id and game_id in (select game_id from games where status <= 1); - update players set user_id = 0 where user_id = old.user_id; + delete from players where user_id = old.user_id and game_id in (select game_id from games where status = 0); update games set owner_id = 0 where owner_id = old.user_id; end; diff --git a/server.js b/server.js index 5d26114..f500db7 100644 --- a/server.js +++ b/server.js @@ -583,15 +583,32 @@ app.post("/change-password", must_be_logged_in, function (req, res) { return res.redirect("/profile") }) +const SQL_SELECT_MAY_DELETE_ACCOUNT = SQL(` + select exists ( + select 1 from games join players using(game_id) where status <= 1 and user_id=? + ) +`).pluck() + +function may_delete_account(user_id) { + if (SQL_SELECT_MAY_DELETE_ACCOUNT.get(user_id)) + return false + return true +} + app.get("/delete-account", must_be_logged_in, function (req, res) { - res.render("delete_account.pug", { user: req.user }) + if (!may_delete_account(req.user.user_id)) + return res.status(401).send("You may not delete your account while you have unfinished games.") + res.render("delete_account.pug", { user: req.user, flash }) }) const SQL_SELECT_GAME_ROLE_FOR_DELETED_USER = SQL(` select game_id, role from players where user_id = ? and game_id in (select game_id from games where status <= 1) - `) +`) app.post("/delete-account", must_be_logged_in, function (req, res) { + if (!may_delete_account(req.user.user_id)) + res.status(401).send("You may not delete your account while you have unfinished games.") + let password = req.body.password // Get full user record including password and salt let user = SQL_SELECT_LOGIN.get(req.user.user_id) @@ -1280,6 +1297,7 @@ const SQL_START_GAME = SQL(` update games set status = 1, is_private = (is_private or user_count = 1 or user_count < player_count), + ctime = datetime(), mtime = datetime(), active = ? where @@ -1551,7 +1569,9 @@ function annotate_game_info(game, user_id, unread) { time_left = Math.min(time_left, p.time_left) let link - if (p.is_invite) + if (!p.name) + link = "null" + else if (p.is_invite) link = `${p.name}?` else if (p.is_active) link = `${p.name}` @@ -2803,7 +2823,7 @@ function on_resign(socket) { } function do_resign(game_id, role, how) { - let game = SQL_SELECT_GAME_VIEW.get(game_id) + let game = SQL_SELECT_GAME.get(game_id) let state = get_game_state(game_id) let old_active = state.active -- cgit v1.2.3