diff options
| -rw-r--r-- | schema.sql | 11 | ||||
| -rw-r--r-- | server.js | 64 | ||||
| -rw-r--r-- | views/tm_seed.pug | 21 |
3 files changed, 64 insertions, 32 deletions
@@ -632,6 +632,17 @@ create table if not exists tm_winners ( create index if not exists tm_winners_pool_idx on tm_winners(pool_id); +drop view if exists tm_queue_view; +create view tm_queue_view as + select + tm_queue.* + from + tm_queue + join user_last_seen using(user_id) + where + julianday() - julianday(atime) < 3 + ; + drop view if exists tm_pool_active_view; create view tm_pool_active_view as select @@ -2593,6 +2593,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. @@ -2662,9 +2663,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 +2685,21 @@ 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() +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) } const TM_SEED_LIST_ALL = SQL(` @@ -2690,8 +2707,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 +2719,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,7 +2732,7 @@ 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 @@ -2768,7 +2788,7 @@ 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 from contacts @@ -2776,8 +2796,8 @@ const TM_SELECT_QUEUE_BLACKLIST = SQL(` 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 (?,?,?)") @@ -2949,9 +2969,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 @@ -2982,11 +3002,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 } @@ -3015,10 +3037,12 @@ app.get("/tm/pool/:pool_name", function (req, res) { app.post("/api/tm/register/:seed_id", must_be_logged_in, function (req, res) { let seed_id = req.params.seed_id | 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)) - return res.status(401).send("You may not join this tournament.") + if (!may_join_seed(seed_id)) + return res.status(401).send("This tournament is closed.") TM_INSERT_QUEUE.run(user_id, seed_id, 1) return res.redirect(req.headers.referer) }) diff --git a/views/tm_seed.pug b/views/tm_seed.pug index 81574ca..9902f4f 100644 --- a/views/tm_seed.pug +++ b/views/tm_seed.pug @@ -48,7 +48,7 @@ html else td #{seed.round_count} sequential - if seed.is_open + if seed.is_open || queues.some(q => q.length > 0) each queue,ix in queues table.half thead @@ -66,21 +66,18 @@ html td Nobody if user - if ix === 0 - if may_register - if !queue.find(p => p.user_id === user.user_id) - form(method="post" action="/api/tm/register/" + seed.seed_id) - button(type="submit") Register - button(disabled) Withdraw - else - div - button(disabled) Register - button(disabled) Withdraw - if queue.find(p => p.user_id === user.user_id) form(method="post" action="/api/tm/withdraw/" + seed.seed_id + "/" + (ix+1)) button(disabled) Register button(type="submit") Withdraw + else if ix === 0 && may_register + form(method="post" action="/api/tm/register/" + seed.seed_id) + button(type="submit") Register + button(disabled) Withdraw + else + div + button(disabled) Register + button(disabled) Withdraw if user.user_id === 1 if queue.length >= seed.pool_size |