diff options
Diffstat (limited to 'server.js')
| -rw-r--r-- | server.js | 624 |
1 files changed, 433 insertions, 191 deletions
@@ -12,7 +12,9 @@ const sqlite3 = require("better-sqlite3") require("dotenv").config() -const DEBUG = process.env.DEBUG || 0 +const DEBUG = process.env.DEBUG | 0 +const TIMEOUT = process.env.TIMEOUT | 0 +const ALTCHA = process.env.ALTCHA | 0 const HTTP_HOST = process.env.HTTP_HOST || "localhost" const HTTP_PORT = process.env.HTTP_PORT || 8080 @@ -64,7 +66,13 @@ var game_cookies = {} let db = new sqlite3(process.env.DATABASE || "./db") db.pragma("synchronous = NORMAL") -const SQL_BEGIN = db.prepare("begin") +let ENABLE_ARCHIVE = process.env.ARCHIVE | 0 +if (ENABLE_ARCHIVE) { + console.log("Attached to archive database.") + db.exec("attach database 'archive.db' as archive") +} + +const SQL_BEGIN = db.prepare("begin immediate") const SQL_COMMIT = db.prepare("commit") const SQL_ROLLBACK = db.prepare("rollback") @@ -186,6 +194,7 @@ function set_static_headers(res, path) { let app = express() app.locals.DEBUG = DEBUG +app.locals.ALTCHA = ALTCHA app.locals.SITE_NAME = SITE_NAME app.locals.SITE_NAME_P = SITE_NAME.endsWith("!") ? SITE_NAME : SITE_NAME + "." @@ -197,6 +206,7 @@ app.locals.ENABLE_MAIL = !!mailer app.locals.ENABLE_WEBHOOKS = !!WEBHOOKS app.locals.ENABLE_FORUM = process.env.FORUM | 0 app.locals.ENABLE_TOURNAMENTS = process.env.TOURNAMENTS | 0 +app.locals.ENABLE_ARCHIVE = ENABLE_ARCHIVE app.locals.EMOJI_PRIVATE = "\u{1F512}" // or 512 app.locals.EMOJI_MATCH = "\u{1f3c6}" @@ -222,6 +232,9 @@ app.locals.PACE_TEXT = [ app.locals.human_date = human_date app.locals.format_options = format_options +app.locals.format_minutes = format_minutes + +app.locals.may_join_seed_level = may_join_seed_level app.set("x-powered-by", false) app.set("etag", false) @@ -310,6 +323,15 @@ function human_date(date) { return new Date(epoch_from_julianday(date)).toISOString().substring(0,10) } +function format_minutes(mins) { + if (mins > 59) { + var hh = mins / 60 | 0 + var mm = mins % 60 + return `${hh} hours ${mm} minutes` + } + return mins + " minutes" +} + function is_valid_email(email) { return REGEX_MAIL.test(email) } @@ -343,6 +365,72 @@ function hash_password(password, salt) { } /* + * ALTCHA ANTI-BOT SIGNUP + */ + +const ALTCHA_HMAC_KEY = crypto.randomBytes(16).toString("hex") + +function sha2_hex(salty_secret) { + var hash = crypto.createHash("sha256") + hash.update(salty_secret) + return hash.digest("hex") +} + +function hmac_sha2_hex(challenge, key) { + var hmac = crypto.createHmac("sha256", key) + hmac.update(challenge) + return hmac.digest("hex") +} + +function altcha_create_challenge() { + var maxnumber = ALTCHA + var secret = crypto.randomInt(maxnumber) + var salt = crypto.randomBytes(16).toString("hex") + var challenge = sha2_hex(salt + secret) + var signature = hmac_sha2_hex(challenge, ALTCHA_HMAC_KEY) + return { + algorithm: "SHA-256", + challenge, + maxnumber, + salt, + signature + } +} + +function altcha_verify_solution(payload) { + var data + if (!payload) + return "missing altcha payload" + try { + data = JSON.parse(atob(payload)) + } catch (error) { + return "invalid altcha payload" + } + if (data.algorithm !== "SHA-256") + return "invalid altcha algorithm" + if (data.challenge !== sha2_hex(data.salt + data.number)) + return "invalid altcha challenge" + if (data.signature !== hmac_sha2_hex(data.challenge, ALTCHA_HMAC_KEY)) + return "invalid altcha signature" + return null +} + +function must_pass_altcha(req, res, next) { + if (ALTCHA) { + var altcha_error = altcha_verify_solution(req.body.altcha) + if (altcha_error) { + setTimeout(() => res.status(500).send(altcha_error), 3000) + return + } + } + return next() +} + +app.get("/altcha-challenge", function (_req, res) { + return res.json(altcha_create_challenge()) +}) + +/* * USER AUTHENTICATION */ @@ -352,14 +440,15 @@ const SQL_BLACKLIST_NAME = SQL("select exists ( select 1 from blacklist_name whe const SQL_EXISTS_USER_NAME = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE name=? )").pluck() const SQL_EXISTS_USER_MAIL = SQL("SELECT EXISTS ( SELECT 1 FROM users WHERE mail=? )").pluck() -const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail,password,salt,notify) VALUES (?,?,?,?,?) RETURNING user_id,name,mail,notify") +const SQL_INSERT_USER = SQL("INSERT INTO users (name,mail) VALUES (?,?) RETURNING user_id,name,mail") const SQL_DELETE_USER = SQL("DELETE FROM users WHERE user_id = ?") const SQL_SELECT_LOGIN = SQL("SELECT * FROM user_login_view WHERE user_id=?") -const SQL_SELECT_USER_VIEW = SQL("SELECT * FROM user_view WHERE user_id=?") -const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?") const SQL_SELECT_LOGIN_BY_MAIL = SQL("SELECT * FROM user_login_view WHERE mail=?") const SQL_SELECT_LOGIN_BY_NAME = SQL("SELECT * FROM user_login_view WHERE name=?") + +const SQL_SELECT_USER_VIEW = SQL("SELECT * FROM user_view WHERE user_id=?") +const SQL_SELECT_USER_BY_NAME = SQL("SELECT * FROM user_view WHERE name=?") const SQL_SELECT_USER_PROFILE = SQL("SELECT * FROM user_profile_view WHERE name=?") const SQL_SELECT_USER_DYNAMIC = SQL("select * from user_dynamic_view where user_id=?") const SQL_SELECT_USER_ID = SQL("SELECT user_id FROM users WHERE name=?").pluck() @@ -371,9 +460,11 @@ const SQL_UPDATE_USER_NOTIFY = SQL("UPDATE users SET notify=? WHERE user_id=?") const SQL_UPDATE_USER_NAME = SQL("UPDATE users SET name=? WHERE user_id=?") const SQL_UPDATE_USER_MAIL = SQL("UPDATE users SET mail=? WHERE user_id=?") const SQL_UPDATE_USER_VERIFIED = SQL("UPDATE users SET is_verified=? WHERE user_id=?") -const SQL_UPDATE_USER_ABOUT = SQL("UPDATE users SET about=? WHERE user_id=?") -const SQL_UPDATE_USER_PASSWORD = SQL("UPDATE users SET password=?, salt=? WHERE user_id=?") -const SQL_UPDATE_USER_LAST_SEEN = SQL("INSERT OR REPLACE INTO user_last_seen (user_id,atime,ip) VALUES (?,datetime(),?)") + +const SQL_UPDATE_USER_ABOUT = SQL("insert or replace into user_about (user_id,about) values (?,?)") +const SQL_UPDATE_USER_PASSWORD = SQL("insert or replace into user_password (user_id,password,salt) values (?,?,?)") +const SQL_UPDATE_USER_FIRST_SEEN = SQL("insert or replace into user_first_seen (user_id,ctime,ip) values (?,datetime(),?)") +const SQL_UPDATE_USER_LAST_SEEN = SQL("insert or replace into user_last_seen (user_id,atime,ip) values (?,datetime(),?)") const SQL_UPDATE_USER_IS_BANNED = SQL("update users set is_banned=? where name=?") const SQL_SELECT_WEBHOOK = SQL("SELECT * FROM webhooks WHERE user_id=?") @@ -449,7 +540,7 @@ app.get("/login", function (req, res) { res.render("login.pug", { redirect: req.query.redirect }) }) -app.post("/login", function (req, res) { +app.post("/login", must_pass_altcha, function (req, res) { let name_or_mail = req.body.username let password = req.body.password let redirect = req.body.redirect @@ -470,14 +561,14 @@ app.get("/signup", function (req, res) { res.render("signup.pug") }) -app.post("/signup", function (req, res) { +app.post("/signup", must_pass_altcha, function (req, res) { function err(msg) { res.render("signup.pug", { flash: msg }) } + let ip = req.headers["x-real-ip"] || req.ip || req.connection.remoteAddress || "0.0.0.0" let name = req.body.username let mail = req.body.mail let password = req.body.password - let notify = req.body.notify === "true" name = clean_user_name(name) if (!is_valid_user_name(name)) return err("Invalid user name!") @@ -493,7 +584,9 @@ app.post("/signup", function (req, res) { return err("Password is too long!") let salt = crypto.randomBytes(32).toString("hex") let hash = hash_password(password, salt) - let user = SQL_INSERT_USER.get(name, mail, hash, salt, notify ? 1 : 0) + let user = SQL_INSERT_USER.get(name, mail) + SQL_UPDATE_USER_FIRST_SEEN.run(user.user_id, ip) + SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt) login_insert(res, user.user_id) res.redirect("/profile") }) @@ -532,7 +625,7 @@ app.get("/forgot-password", function (req, res) { res.render("forgot_password.pug") }) -app.post("/forgot-password", function (req, res) { +app.post("/forgot-password", must_pass_altcha, function (req, res) { let mail = req.body.mail let user = SQL_SELECT_LOGIN_BY_MAIL.get(mail) if (user) { @@ -585,7 +678,7 @@ app.post("/reset-password", function (req, res) { return err("Invalid or expired token!") let salt = crypto.randomBytes(32).toString("hex") let hash = hash_password(password, salt) - SQL_UPDATE_USER_PASSWORD.run(hash, salt, user.user_id) + SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt) SQL_UPDATE_USER_VERIFIED.run(1, user.user_id) login_insert(res, user.user_id) return res.redirect("/profile") @@ -609,7 +702,7 @@ app.post("/change-password", must_be_logged_in, function (req, res) { return res.render("change_password.pug", { user: req.user, flash: "Wrong password!" }) let salt = crypto.randomBytes(32).toString("hex") let hash = hash_password(newpass, salt) - SQL_UPDATE_USER_PASSWORD.run(hash, salt, user.user_id) + SQL_UPDATE_USER_PASSWORD.run(user.user_id, hash, salt) return res.redirect("/profile") }) @@ -681,7 +774,6 @@ app.get("/unsubscribe", must_be_logged_in, function (req, res) { }) app.get("/webhook", must_be_logged_in, function (req, res) { - req.user.notify = SQL_SELECT_USER_NOTIFY.get(req.user.user_id) let webhook = SQL_SELECT_WEBHOOK.get(req.user.user_id) res.render("webhook.pug", { user: req.user, webhook: webhook }) }) @@ -738,7 +830,7 @@ app.get("/change-about", must_be_logged_in, function (req, res) { }) app.post("/change-about", must_be_logged_in, function (req, res) { - SQL_UPDATE_USER_ABOUT.run(req.body.about, req.user.user_id) + SQL_UPDATE_USER_ABOUT.run(req.user.user_id, req.body.about) return res.redirect("/profile") }) @@ -746,13 +838,22 @@ app.get("/user/:who_name", function (req, res) { let who = SQL_SELECT_USER_PROFILE.get(req.params.who_name) if (who) { let games = QUERY_LIST_PUBLIC_GAMES_OF_USER.all({ user_id: who.user_id }) + let ratings = SQL_USER_RATINGS.all(who.user_id) annotate_games(games, 0, null) let active_pools = TM_POOL_LIST_USER_ACTIVE.all(who.user_id) let finished_pools = TM_POOL_LIST_USER_RECENT_FINISHED.all(who.user_id) let relation = 0 if (req.user) relation = SQL_SELECT_RELATION.get(req.user.user_id, who.user_id) | 0 - res.render("user.pug", { user: req.user, who, relation, games, active_pools, finished_pools }) + res.render("user.pug", { + user: req.user, + who, + relation, + games, + active_pools, + finished_pools, + ratings, + }) } else { return res.status(404).send("User not found.") } @@ -1203,10 +1304,11 @@ function format_options(options_json) { function get_game_roles(title_id, scenario, options) { let roles = RULES[title_id].roles - if (typeof options === "string") - options = parse_game_options(options) - if (typeof roles === "function") + if (typeof roles === "function") { + if (typeof options === "string") + options = parse_game_options(options) return roles(scenario, options) + } return roles } @@ -1312,6 +1414,8 @@ const SQL_SELECT_REWIND = SQL("select snap_id, state->>'$.active' as active, sta const SQL_UPDATE_GAME_ACTIVE = SQL("update games set active=?,mtime=datetime(),moves=moves+1 where game_id=?") const SQL_UPDATE_GAME_SCENARIO = SQL("update games set scenario=? where game_id=?") +const ARCHIVE_SELECT_GAME_STATE = ENABLE_ARCHIVE ? SQL("select state from archive.game_state where game_id=?").pluck() : null + const SQL_SELECT_GAME_STATE = SQL("select state from game_state where game_id=?").pluck() const SQL_INSERT_GAME_STATE = SQL("insert or replace into game_state (game_id,state) values (?,?)") @@ -1369,6 +1473,78 @@ const SQL_SELECT_REPLAY = SQL(` where game_id = ? `).pluck() +const ARCHIVE_SELECT_REPLAY = ENABLE_ARCHIVE ? SQL(` + select + json_object( + 'players', + (select json_group_array( + json_object('role', role, 'name', name) + ) + from players + left join users using(user_id) + where game_id = outer.game_id + ), + 'state', + (select json(state) + from archive.game_state + where game_id = outer.game_id + ), + 'replay', + (select json_group_array( + case when arguments is null then + json_array(role, action) + else + json_array(role, action, json(arguments)) + end + ) + from archive.game_replay + where game_id = outer.game_id + ) + ) as export + from games as outer + where game_id = ? +`).pluck() : null + +const ARCHIVE_SELECT_EXPORT = ENABLE_ARCHIVE ? SQL(` + select + game_id, + json_object( + 'setup', json_object( + 'game_id', game_id, + 'title_id', title_id, + 'scenario', scenario, + 'options', json(options), + 'player_count', player_count, + 'notice', notice + ), + 'players', + (select json_group_array( + json_object('role', role, 'name', name) + ) + from players + left join users using(user_id) + where game_id = outer.game_id + ), + 'state', + (select json(state) + from archive.game_state + where game_id = outer.game_id + ), + 'replay', + (select json_group_array( + case when arguments is null then + json_array(role, action) + else + json_array(role, action, json(arguments)) + end + ) + from archive.game_replay + where game_id = outer.game_id + ) + ) as export + from games as outer +`).pluck() : null + const SQL_SELECT_EXPORT = SQL("select export from game_export_view where game_id=?").pluck() const SQL_SELECT_GAME = SQL("SELECT * FROM games WHERE game_id=?") @@ -1464,8 +1640,10 @@ const QUERY_NEXT_GAME_OF_USER = SQL(` join players using(game_id) where status = ${STATUS_ACTIVE} - and active in (role, 'Both') + -- and active in (role, 'Both') + and ( active = 'Both' or instr(active, role) > 0 ) and user_id = ? + and is_opposed order by mtime limit 1 `) @@ -1477,7 +1655,7 @@ const QUERY_LIST_PUBLIC_GAMES_OF_USER = SQL(` and ( status <= ${STATUS_FINISHED} ) and - ( not is_private or status = ${STATUS_ACTIVE} ) + ( not is_private or status >= ${STATUS_ACTIVE} ) order by status asc, mtime desc `) @@ -1733,7 +1911,7 @@ app.get("/create/:title_id", function (req, res) { user: req.user, title: title, limit: req.user ? check_create_game_limit(req.user) : null, - scenarios: RULES[title_id].scenarios, + rules: RULES[title_id], }) }) @@ -1919,6 +2097,11 @@ app.get("/join/:game_id", function (req, res) { if (!game) return res.status(404).send("Invalid game ID.") + if (ENABLE_ARCHIVE) { + if (game.status === STATUS_ARCHIVED && game.moves >= game.player_count * 3) + game.status = STATUS_FINISHED + } + let roles = get_game_roles(game.title_id, game.scenario, game.options) let players = SQL_SELECT_PLAYER_VIEW.all(game_id) @@ -2085,7 +2268,7 @@ function assign_random_roles(game, options, players) { app.post("/api/start/:game_id", must_be_logged_in, function (req, res) { let game_id = req.params.game_id | 0 let game = SQL_SELECT_GAME.get(game_id) - if (game.owner_id !== req.user.user_id) + if (req.user.user_id !== game.owner_id && req.user.user_id !== 1) return res.send("Not authorized to start that game ID.") if (game.status !== STATUS_OPEN) return res.send("The game is already started.") @@ -2121,13 +2304,11 @@ function start_game(game) { state = RULES[game.title_id].setup(seed, game.scenario, options) - SQL_START_GAME.run(state.active, game.game_id) + SQL_START_GAME.run(String(state.active), game.game_id) let replay_id = put_replay(game.game_id, null, ".setup", [ seed, game.scenario, options ]) put_snap(game.game_id, replay_id, state) SQL_INSERT_GAME_STATE.run(game.game_id, JSON.stringify(state)) - SQL_UPDATE_PLAYERS_INIT_TIME.run(game.game_id) - SQL_COMMIT.run() } finally { if (db.inTransaction) @@ -2146,6 +2327,10 @@ app.get("/api/replay/:game_id", function (req, res) { return res.status(404).send("Invalid game ID.") if (game.status < STATUS_FINISHED && (!req.user || req.user.user_id !== 1)) return res.status(401).send("Not authorized to debug.") + if (ENABLE_ARCHIVE) { + if (game.status === STATUS_ARCHIVED) + return res.type("application/json").send(ARCHIVE_SELECT_REPLAY.get(game_id)) + } return res.type("application/json").send(SQL_SELECT_REPLAY.get(game_id)) }) @@ -2156,6 +2341,10 @@ app.get("/api/export/:game_id", function (req, res) { return res.status(404).send("Invalid game ID.") if (game.status < STATUS_FINISHED && (!req.user || req.user.user_id !== 1)) return res.status(401).send("Not authorized to debug.") + if (ENABLE_ARCHIVE) { + if (game.status === STATUS_ARCHIVED) + return res.type("application/json").send(ARCHIVE_SELECT_EXPORT.get(game_id)) + } return res.type("application/json").send(SQL_SELECT_EXPORT.get(game_id)) }) @@ -2172,7 +2361,7 @@ function rewind_game_to_snap(game_id, snap_id) { SQL_DELETE_GAME_REPLAY.run(game_id, snap.replay_id) SQL_INSERT_GAME_STATE.run(game_id, JSON.stringify(snap_state)) - SQL_REWIND_GAME.run(snap_id - 1, snap_state.active, game_id) + SQL_REWIND_GAME.run(snap_id - 1, String(snap_state.active), game_id) SQL_REWIND_GAME_CLOCK.run(game_id) update_join_clients(game_id) @@ -2224,7 +2413,7 @@ const SQL_CLONE_1 = SQL(` `).pluck() const SQL_CLONE_2 = [ - SQL(`insert into players(game_id,role,user_id) select $new_game_id,role,user_id from players where game_id=$old_game_id`), + SQL(`insert into players(game_id,role,user_id,is_active) select $new_game_id,role,user_id,is_active from players where game_id=$old_game_id`), SQL(`insert into game_state(game_id,state) select $new_game_id,state from game_state where game_id=$old_game_id`), SQL(`insert into game_replay(game_id,replay_id,role,action,arguments) select $new_game_id,replay_id,role,action,arguments from game_replay where game_id=$old_game_id`), SQL(`insert into game_snap(game_id,snap_id,replay_id,state) select $new_game_id,snap_id,replay_id,state from game_snap where game_id=$old_game_id`), @@ -2268,8 +2457,8 @@ function is_winner(role, result) { return (result === "Draw" || result === role || result.includes(role)) } -function elo_k(a) { - return a.count < 10 ? 60 : 30 +function elo_k(_) { + return 30 } function elo_ev(a, players) { @@ -2424,6 +2613,10 @@ function message_link(msg_id) { return SITE_URL + "/message/read/" + msg_id } +function tour_pool_link(pool_id) { + return SITE_URL + "/tm/pool/" + pool_id +} + function send_notification(user, link, message) { if (WEBHOOKS) { let webhook = SQL_SELECT_WEBHOOK_SEND.get(user.user_id) @@ -2458,6 +2651,10 @@ function send_play_notification(user, game_id, message) { send_notification(user, game_play_link(game_id, title_id, user), `${title_name} #${game_id} (${user.role}) - ${message}`) } +function send_tour_notification(user, pool_name, message) { + send_notification(user, tour_pool_link(pool_name), `${pool_name} - ${message}`) +} + function send_chat_activity_notification(game_id, p) { send_play_notification(p, game_id, "Chat activity") } @@ -2465,7 +2662,7 @@ function send_chat_activity_notification(game_id, p) { function send_game_started_notification(game_id, active) { let players = SQL_SELECT_PLAYERS.all(game_id) for (let p of players) { - let p_is_active = active === p.role || active === "Both" + let p_is_active = is_role_active(active, p.role) if (p_is_active) send_play_notification(p, game_id, "Started - Your turn") else @@ -2473,15 +2670,15 @@ function send_game_started_notification(game_id, active) { } } -function send_your_turn_notification_to_offline_users(game_id, old_active, active) { +function send_your_turn_notification_to_offline_users(game_id, old_active, new_active) { // Only send notifications when the active player changes. - if (old_active === active) + if (!is_changed_active(old_active, new_active)) return let players = SQL_SELECT_PLAYERS.all(game_id) for (let p of players) { - let p_was_active = old_active === p.role || old_active === "Both" - let p_is_active = active === p.role || active === "Both" + let p_was_active = is_role_active(old_active, p.role) + let p_is_active = is_role_active(new_active, p.role) if (!p_was_active && p_is_active) { if (!is_player_online(game_id, p.user_id)) send_play_notification(p, game_id, "Your turn") @@ -2593,6 +2790,7 @@ function purge_game_ticker() { QUERY_PURGE_ACTIVE_GAMES.run() QUERY_PURGE_FINISHED_GAMES.run() QUERY_PURGE_MESSAGES.run() + TM_DELETE_QUEUE_INACTIVE.run() } // Purge abandoned games every 31 minutes. @@ -2603,43 +2801,20 @@ setTimeout(purge_game_ticker, 89 * 1000) * TIME CONTROL */ -const SQL_UPDATE_PLAYERS_INIT_TIME = SQL(` - update players - set clock = ( - case (select pace from games where games.game_id = players.game_id) - when 1 then 1 - when 2 then 3 - when 3 then 3 - else 21 - end - ) - where - players.game_id = ? -`) - -const SQL_UPDATE_PLAYERS_ADD_TIME = SQL(` - update players - set clock = ( - case (select pace from games where games.game_id = players.game_id) - when 1 then min(clock + ${4 / 24}, 3) - when 2 then min(clock + ${12 / 24}, 5) - when 3 then min(clock + ${36 / 24}, 10) - else 21 - end - ) - where - players.game_id = ? and players.role = ? -`) - +// SQL_UPDATE_PLAYERS_INIT_TIME is handled by trigger +// SQL_UPDATE_PLAYERS_ADD_TIME is handled by trigger // SQL_UPDATE_PLAYERS_USE_TIME is handled by trigger const SQL_SELECT_TIME_CONTROL = SQL("select * from time_control_view") +const SQL_INSERT_TIMEOUT = SQL("insert into user_timeout (user_id, game_id) values (?, ?)") + function time_control_ticker() { for (let item of SQL_SELECT_TIME_CONTROL.all()) { if (item.is_opposed) { console.log("TIMED OUT GAME:", item.game_id, item.role) - do_resign(item.game_id, item.role, "timed out") + do_timeout(item.game_id, item.role, item.role + " timed out.") + SQL_INSERT_TIMEOUT.run(item.user_id, item.game_id) if (item.is_match) { console.log("BANNED FROM TOURNAMENTS:", item.user_id) TM_INSERT_BANNED.run(item.user_id) @@ -2653,8 +2828,10 @@ function time_control_ticker() { } // Run time control checks every 13 minutes. -setInterval(time_control_ticker, 13 * 60 * 1000) -setTimeout(time_control_ticker, 13 * 1000) +if (TIMEOUT) { + setInterval(time_control_ticker, 13 * 60 * 1000) + setTimeout(time_control_ticker, 13 * 1000) +} /* * TOURNAMENTS @@ -2662,9 +2839,20 @@ setTimeout(time_control_ticker, 13 * 1000) const designs = require("./designs.js") +const TM_SELECT_BANNED = SQL("select exists ( select 1 from tm_banned where user_id=? )").pluck() const TM_INSERT_BANNED = SQL("insert or ignore into tm_banned (user_id, time) values (?, datetime())") + const TM_DELETE_QUEUE_ALL = SQL("delete from tm_queue where user_id=?") +const TM_DELETE_QUEUE_INACTIVE = SQL(` + delete from tm_queue where exists ( + select 1 + from user_last_seen + where user_last_seen.user_id = tm_queue.user_id + and julianday() - julianday(atime) > 14 + ) +`) + const TM_MAY_JOIN_ANY_SEED = SQL(` select ( select notify and is_verified from users where user_id=@user_id ) or ( select exists ( select 1 from webhooks where user_id=@user_id and error is null ) ) @@ -2673,16 +2861,56 @@ const TM_MAY_JOIN_ANY_SEED = SQL(` `).pluck() const TM_MAY_JOIN_SEED = SQL(` - select ( select not exists ( select 1 from tm_banned where user_id=@user_id ) ) - and ( select coalesce(is_open, 0) as may_join from tm_seeds where seed_id=@seed_id ) + select is_open or exists ( select 1 from tm_queue_view where tm_queue_view.seed_id = tm_seeds.seed_id ) + from tm_seeds + where seed_id=? `).pluck() +const TM_MAY_JOIN_SEED_LEVEL = SQL(` + with + win_cte as ( + select + count(1) as n_win + from + tm_winners + join tm_pools using(pool_id) + where + level = @level - 1 and user_id = @user_id and seed_id = @seed_id + ), + play_cte as ( + select + count(distinct pool_id) as n_play + from + tm_rounds + join tm_pools using(pool_id) + join players using(game_id) + where + level = @level and user_id = @user_id and seed_id = @seed_id + ) + select + coalesce(n_win, 0) > coalesce(n_play, 0) as may_join + from + win_cte, play_cte +`).pluck() + +function is_banned_from_tournaments(user_id) { + return TM_SELECT_BANNED.get(user_id) +} + function may_join_any_seed(user_id) { return DEBUG || TM_MAY_JOIN_ANY_SEED.get({user_id}) } -function may_join_seed(user_id, seed_id) { - return TM_MAY_JOIN_SEED.get({user_id,seed_id}) +function may_join_seed(seed_id) { + return TM_MAY_JOIN_SEED.get(seed_id) +} + +function may_join_seed_level(user_id, seed_id, level) { + if (level === 1) + return true + if (level >= 2) + return TM_MAY_JOIN_SEED_LEVEL.get({ level, user_id, seed_id }) + return false } const TM_SEED_LIST_ALL = SQL(` @@ -2690,8 +2918,9 @@ const TM_SEED_LIST_ALL = SQL(` tm_seeds.*, sum(level is 1) as queue_size, sum(user_id is ?) as is_queued - from tm_seeds left join tm_queue using(seed_id) - where is_open + from tm_seeds left join tm_queue_view using(seed_id) + where + is_open or exists ( select 1 from tm_queue_view where tm_queue_view.seed_id = tm_seeds.seed_id ) group by seed_id order by seed_name `) @@ -2701,8 +2930,10 @@ const TM_SEED_LIST_TITLE = SQL(` tm_seeds.*, sum(level is 1) as queue_size, sum(user_id is ?) as is_queued - from tm_seeds left join tm_queue using(seed_id) - where title_id = ? and is_open + from tm_seeds left join tm_queue_view using(seed_id) + where title_id = ? and ( + is_open or exists ( select 1 from tm_queue_view where tm_queue_view.seed_id = tm_seeds.seed_id ) + ) group by seed_id order by seed_name `) @@ -2712,14 +2943,12 @@ const TM_SEED_LIST_USER = SQL(` tm_seeds.*, sum(level is 1) as queue_size, sum(user_id is ?) as is_queued - from tm_seeds left join tm_queue using(seed_id) + from tm_seeds left join tm_queue_view using(seed_id) group by seed_id having is_queued order by seed_name `) -const TM_POOL_LIST_ACTIVE = SQL("select * from tm_pool_active_view") - const TM_POOL_LIST_USER_ACTIVE = SQL(` select * from tm_pool_active_view where not is_finished and pool_id in ( @@ -2768,16 +2997,18 @@ const TM_POOL_LIST_SEED_FINISHED = SQL("select * from tm_pool_finished_view wher const TM_SELECT_QUEUE_BLACKLIST = SQL(` with qq as ( - select user_id from tm_queue where seed_id=? and level=? + select user_id from tm_queue_view where seed_id=? and level=? ) - select me, you + select me, you, u_me.name as me_name, u_you.name as you_name from contacts join qq on qq.user_id = me + join users u_me on u_me.user_id=me + join users u_you on u_you.user_id=you where relation < 0 and exists (select 1 from qq where user_id = you) `) -const TM_SELECT_QUEUE_NAMES = SQL("select user_id, name, level from tm_queue join users using(user_id) where seed_id=? and level=? order by time") -const TM_SELECT_QUEUE = SQL("select user_id from tm_queue where seed_id=? and level=? order by time desc").pluck() +const TM_SELECT_QUEUE_NAMES = SQL("select user_id, name, level from tm_queue_view join users using(user_id) where seed_id=? and level=? order by time") +const TM_SELECT_QUEUE = SQL("select user_id from tm_queue_view where seed_id=? and level=? order by time desc").pluck() const TM_DELETE_QUEUE = SQL("delete from tm_queue where user_id=? and seed_id=? and level=?") const TM_INSERT_QUEUE = SQL("insert or ignore into tm_queue (user_id, seed_id, level) values (?,?,?)") @@ -2798,6 +3029,7 @@ const TM_SELECT_GAMES = SQL(` tm_rounds.*, games.status, games.moves, + games.status > 1 and games.result = 'None' as is_abandoned, json_group_object(role, coalesce(name, 'null')) as role_names, json_group_object(role, score) as role_scores from @@ -2811,7 +3043,16 @@ const TM_SELECT_GAMES = SQL(` game_id `) -const TM_SELECT_WINNERS = SQL("select user_id from tm_winners where pool_id = ?").pluck() +const TM_SELECT_PLAYERS_IN_POOL = SQL(` + select + user_view.* + from + tm_rounds + join players using(game_id) + join user_view using(user_id) + group by + user_id +`) const TM_SELECT_PLAYERS_2P = SQL(` with @@ -2930,10 +3171,9 @@ const TM_FIND_NEXT_GAME_TO_START = SQL(` const TM_SELECT_ENDED_POOLS = SQL(` select - pool_id, seed_id, level, pool_name, level_count + pool_id, pool_name from tm_pools - join tm_seeds using(seed_id) join tm_rounds using(pool_id) join games using(game_id) where @@ -2949,9 +3189,9 @@ const TM_SELECT_SEED_READY_MINI_CUP = SQL(` seed_id, level from tm_seeds - join tm_queue using(seed_id) + join tm_queue_view using(seed_id) where - is_open and seed_name like 'mc.%' + seed_name like 'mc.%' and julianday(time) < julianday('now', '-30 seconds') group by seed_id, level @@ -2961,10 +3201,7 @@ const TM_SELECT_SEED_READY_MINI_CUP = SQL(` app.get("/tm/list", function (req, res) { let seeds = TM_SEED_LIST_ALL.all(req.user ? req.user.user_id : 0) - let seeds_by_title = object_group_by(seeds, "title_id") - let active_pools = TM_POOL_LIST_ACTIVE.all() - let pools_by_seed = object_group_by(active_pools, "seed_name") - res.render("tm_list.pug", { user: req.user, seeds, seeds_by_title, active_pools, pools_by_seed }) + res.render("tm_list.pug", { user: req.user, seeds }) }) app.get("/tm/seed/:seed_name", function (req, res) { @@ -2982,11 +3219,13 @@ app.get("/tm/seed/:seed_name", function (req, res) { let error = null let may_register = false - if (req.user && seed.is_open) { - if (!may_join_any_seed(req.user.user_id)) + if (req.user) { + if (is_banned_from_tournaments(req.user.user_id)) + error = "You may not join any tournaments." + else if (!may_join_any_seed(req.user.user_id)) error = "Please verify your mail address and enable notifications to join tournaments." - else if (!may_join_seed(req.user.user_id, seed_id)) - error = "You may not register for this tournament." + else if (!may_join_seed(seed_id)) + error = "This tournament is closed." else may_register = true } @@ -3009,17 +3248,22 @@ app.get("/tm/pool/:pool_name", function (req, res) { players = TM_SELECT_PLAYERS_MP.all(pool_id) let games = TM_SELECT_GAMES.all(pool_id) let games_by_round = object_group_by(games, "round") - res.render("tm_pool.pug", { user: req.user, seed, pool, roles, players, games_by_round }) + res.render("tm_pool.pug", { user: req.user, seed, pool, roles, players, games, games_by_round }) }) -app.post("/api/tm/register/:seed_id", must_be_logged_in, function (req, res) { +app.post("/api/tm/register/:seed_id/:level", must_be_logged_in, function (req, res) { let seed_id = req.params.seed_id | 0 + let level = req.params.level | 0 let user_id = req.user.user_id + if (is_banned_from_tournaments(req.user.user_id)) + return res.status(401).send("You may not join any tournaments.") if (!may_join_any_seed(user_id)) return res.status(401).send("You may not join any tournaments right now.") - if (!may_join_seed(user_id, seed_id)) + if (!may_join_seed(seed_id)) + return res.status(401).send("This tournament is closed.") + if (!may_join_seed_level(req.user.user_id, seed_id, level)) return res.status(401).send("You may not join this tournament.") - TM_INSERT_QUEUE.run(user_id, seed_id, 1) + TM_INSERT_QUEUE.run(user_id, seed_id, level) return res.redirect(req.headers.referer) }) @@ -3165,7 +3409,7 @@ function make_concurrent_rounds(v, k, n) { let rbibd = designs.resolvable_bibd(v, k) if (rbibd) - return rbibd.slice(0, n).flat() + return [ rbibd.slice(0, n).flat() ] throw new Error("cannot create rounds for this configuration") } @@ -3278,6 +3522,7 @@ function start_tournament_seed_mc(seed_id, level) { let blacklist = TM_SELECT_QUEUE_BLACKLIST.all(seed_id, level) console.log("TM SPAWN SEED (MC)", seed.seed_name, level, queue.length) + console.log("TM BLACKLIST", blacklist) let players = filter_queue_through_blacklist(queue, seed.pool_size, blacklist) if (!players) { @@ -3320,23 +3565,15 @@ function start_tournament_seed(seed_id, level) { } function tm_reap_pools() { - // reap pools that are finished (and promote winners) + // reap pools that are finished (and notify players) let ended = TM_SELECT_ENDED_POOLS.all() for (let item of ended) { - console.log("TM POOL - END", item.pool_name) - SQL_BEGIN.run() - try { - TM_UPDATE_POOL_FINISHED.run(item.pool_id) - if (item.level < item.level_count) { - let winners = TM_SELECT_WINNERS.all(item.pool_id) - for (let user_id of winners) - TM_INSERT_QUEUE.run(user_id, item.seed_id, item.level + 1) - } - SQL_COMMIT.run() - } finally { - if (db.inTransaction) - SQL_ROLLBACK.run() - } + console.log("TM POOL FINISHED", item.pool_name) + TM_UPDATE_POOL_FINISHED.run(item.pool_id) + + let players = TM_SELECT_PLAYERS_IN_POOL.all(item.pool_id) + for (let user of players) + send_tour_notification(user, item.pool_name, "Finished") } } @@ -3376,6 +3613,26 @@ if (app.locals.ENABLE_TOURNAMENTS) { * GAME SERVER */ +function is_role_active(active, role) { + return active === role || active === "Both" || active.includes(role) +} + +function is_nobody_active(active) { + return !active || active === "None" +} + +function is_multi_active(active) { + if (!active) + return false + if (Array.isArray(active)) + return true + return active === "Both" || active.includes(",") +} + +function is_changed_active(old_active, new_active) { + return String(old_active) !== String(new_active) +} + function is_player_online(game_id, user_id) { if (game_clients[game_id]) for (let other of game_clients[game_id]) @@ -3397,13 +3654,14 @@ function send_state(socket, state) { view.log_start = view.log.length socket.seen = view.log.length view.log = view.log.slice(view.log_start) - if (state.state === "game_over") - view.game_over = 1 let this_view = JSON.stringify(view) if (view.actions || socket.last_view !== this_view) { socket.send('["state",' + this_view + "," + game_cookies[socket.game_id] + "]") socket.last_view = this_view } + if (is_nobody_active(state.active)) { + socket.send('["finished"]') + } } catch (err) { console.log(err) return send_message(socket, "error", err.toString()) @@ -3412,6 +3670,10 @@ function send_state(socket, state) { function get_game_state(game_id) { let game_state = SQL_SELECT_GAME_STATE.get(game_id) + if (ENABLE_ARCHIVE) { + if (!game_state) + game_state = ARCHIVE_SELECT_GAME_STATE.get(game_id) + } if (!game_state) throw new Error("No game with that ID") return JSON.parse(game_state) @@ -3442,6 +3704,18 @@ function put_replay(game_id, role, action, args) { return SQL_INSERT_REPLAY.get(game_id, game_id, role, action, args) } +function dont_snap(rules, state, old_active) { + if (is_nobody_active(state.active)) + return true + if (is_multi_active(old_active) && is_multi_active(state.active)) + return true + if (!is_changed_active(old_active, state.active)) + return true + if (rules.dont_snap && rules.dont_snap(state)) + return true + return false +} + function put_snap(game_id, replay_id, state) { let snap_id = SQL_INSERT_SNAP.get(game_id, game_id, replay_id, snap_from_state(state)) if (game_clients[game_id]) @@ -3449,42 +3723,38 @@ function put_snap(game_id, replay_id, state) { send_message(other, "snapsize", snap_id) } -function put_game_state(game_id, state, old_active, current_role) { +function put_game_state(game_id, state, old_active) { // TODO: separate state, undo, and log entries (and reuse "snap" json stringifaction?) - SQL_INSERT_GAME_STATE.run(game_id, JSON.stringify(state)) - if (state.active !== old_active) { - SQL_UPDATE_GAME_ACTIVE.run(state.active, game_id) + if (is_changed_active(old_active, state.active)) + SQL_UPDATE_GAME_ACTIVE.run(String(state.active), game_id) - // add time for the player who took the current action - SQL_UPDATE_PLAYERS_ADD_TIME.run(game_id, current_role) - } - - if (state.state === "game_over") { + if (is_nobody_active(state.active)) { SQL_FINISH_GAME.run(state.result, game_id) if (state.result && state.result !== "None") update_elo_ratings(game_id) } } -function put_new_state(game_id, state, old_active, role, action, args) { +function put_new_state(title_id, game_id, state, old_active, role, action, args) { SQL_BEGIN.run() try { let replay_id = put_replay(game_id, role, action, args) - if (state.active !== old_active) + if (!dont_snap(RULES[title_id], state, old_active)) put_snap(game_id, replay_id, state) - put_game_state(game_id, state, old_active, role) + put_game_state(game_id, state, old_active) - if (state.active !== old_active) + if (is_changed_active(old_active, state.active)) update_join_clients(game_id) + if (game_clients[game_id]) for (let other of game_clients[game_id]) send_state(other, state) - if (state.state === "game_over") + if (is_nobody_active(state.active)) send_game_finished_notification_to_offline_users(game_id, state.result) else send_your_turn_notification_to_offline_users(game_id, old_active, state.active) @@ -3510,15 +3780,15 @@ function on_action(socket, action, args, cookie) { try { let state = get_game_state(socket.game_id) - let old_active = state.active + let old_active = String(state.active) // Don't update cookie during simultaneous turns, as it results // in many in-flight collisions. - if (old_active !== "Both") + if (!is_multi_active(old_active)) game_cookies[socket.game_id] ++ state = RULES[socket.title_id].action(state, socket.role, action, args) - put_new_state(socket.game_id, state, old_active, socket.role, action, args) + put_new_state(socket.title_id, socket.game_id, state, old_active, socket.role, action, args) } catch (err) { console.log(err) return send_message(socket, "error", err.toString()) @@ -3528,73 +3798,51 @@ function on_action(socket, action, args, cookie) { function on_resign(socket) { SLOG(socket, "RESIGN") try { - do_resign(socket.game_id, socket.role, "resigned") + do_resign(socket.game_id, socket.role) } catch (err) { console.log(err) return send_message(socket, "error", err.toString()) } } -function do_resign(game_id, role, how) { +function do_timeout(game_id, role) { let game = SQL_SELECT_GAME.get(game_id) let state = get_game_state(game_id) - let old_active = state.active + let old_active = String(state.active) + state = finish_game_state(game.title_id, state, "None", role + " timed out.") + put_new_state(game.title_id, game_id, state, old_active, role, ".timeout", null) +} - let result = "None" +function do_resign(game_id, role) { + let game = SQL_SELECT_GAME.get(game_id) + let state = get_game_state(game_id) + let old_active = String(state.active) - let roles = get_game_roles(game.title_id, game.scenario, game.options) + let result = "None" if (game.player_count === 2) { + let roles = get_game_roles(game.title_id, game.scenario, game.options) for (let r of roles) if (r !== role) result = r - } else { - result = roles.filter(r => r !== role).join(", ") } - state.state = "game_over" - state.active = "None" - state.result = result - state.victory = role + " " + how + "." - state.log.push("") - state.log.push(state.victory) - - put_new_state(game_id, state, old_active, role, ".resign", null) -} - -function on_restore(socket, state_text) { - if (!DEBUG) - send_message(socket, "error", "Debugging is not enabled on this server.") - SLOG(socket, "RESTORE") - try { - let state = JSON.parse(state_text) - - // reseed! - state.seed = random_seed() - - // resend full log! - for (let other of game_clients[socket.game_id]) - other.seen = 0 + state = finish_game_state(game.title_id, state, result, role + " resigned.") - put_new_state(socket.game_id, state, null, null, "$restore", state) - } catch (err) { - console.log(err) - return send_message(socket, "error", err.toString()) - } + put_new_state(game.title_id, game_id, state, old_active, role, ".resign", result) } -function on_save(socket) { - if (!DEBUG) - send_message(socket, "error", "Debugging is not enabled on this server.") - SLOG(socket, "SAVE") - try { - let game_state = SQL_SELECT_GAME_STATE.get(socket.game_id) - if (!game_state) - return send_message(socket, "error", "No game with that ID.") - send_message(socket, "save", game_state) - } catch (err) { - console.log(err) - return send_message(socket, "error", err.toString()) +function finish_game_state(title_id, state, result, message) { + if (typeof RULES[title_id].finish === "function") { + state = RULES[title_id].finish(state, result, message) + } else { + state.state = "game_over" + state.active = "None" + state.result = result + state.victory = message + state.log.push("") + state.log.push(message) } + return state } function on_query(socket, q, params) { @@ -3755,12 +4003,6 @@ function handle_player_message(socket, cmd, arg) { case "querysnap": on_query_snap(socket, arg[0], arg[1], arg[2]) break - case "save": - on_save(socket) - break - case "restore": - on_restore(socket, arg) - break default: send_message(socket, "error", "Invalid server command: " + cmd) break @@ -3974,12 +4216,12 @@ const SQL_USER_STATS = SQL(` `) const SQL_USER_RATINGS = SQL(` - select title_name, rating, count, date(last) as last + select title_id, title_name, rating, count, date(last) as last from ratings join titles using(title_id) where user_id = ? - and count >= 5 - order by rating desc + and count >= 3 + order by count desc `) const SQL_GAME_RATINGS = SQL(` |