diff options
| -rw-r--r-- | schema.sql | 99 | ||||
| -rw-r--r-- | server.js | 50 | ||||
| -rw-r--r-- | views/stats.pug | 35 |
3 files changed, 63 insertions, 121 deletions
@@ -3,7 +3,7 @@ create table if not exists blacklist_ip ( ip text primary key ) without rowid; create table if not exists blacklist_mail ( mail text primary key ) without rowid; --- Titles and roles -- +-- Titles -- create table if not exists titles ( title_id text @@ -14,13 +14,6 @@ create table if not exists titles ( default 0 ) without rowid; -create table if not exists roles ( - title_id text - references titles, - role text, - unique (title_id, role) -); - -- Users -- create table if not exists logins ( @@ -293,6 +286,8 @@ create table if not exists game_replay ( arguments text ); +create index if not exists game_replay_idx on game_replay(game_id); + create table if not exists players ( game_id integer references games @@ -416,38 +411,6 @@ begin ; end; -drop trigger if exists must_be_valid_role; -create trigger must_be_valid_role before insert on players -begin - select - raise(abort, 'Invalid role.') - where - not exists ( - select - 1 - from - roles - where - roles.title_id = ( - select - title_id - from - games - where - games.game_id = new.game_id - ) - and roles.role = new.role - ) - and new.role != 'Random 1' - and new.role != 'Random 2' - and new.role != 'Random 3' - and new.role != 'Random 4' - and new.role != 'Random 5' - and new.role != 'Random 6' - and new.role != 'Random 7' - ; -end; - -- Manual key management if pragma foreign_keys = off drop trigger if exists trigger_delete_on_games; create trigger trigger_delete_on_games after delete on games @@ -458,59 +421,3 @@ begin delete from last_notified where game_id = old.game_id; delete from players where game_id = old.game_id; end; - --- Game stats - -drop view if exists role_index_view; -create view role_index_view (t, i, r) as - select - title_id, - row_number() over (partition by title_id), - role - from - roles - ; - -drop view if exists game_stat_view; -create view game_stat_view as - select - title_name, - null as scenario, - null as total, - (select r from role_index_view where t=title_id and i=1) as r1, - (select r from role_index_view where t=title_id and i=2) as r2, - (select r from role_index_view where t=title_id and i=3) as r3, - (select r from role_index_view where t=title_id and i=4) as r4, - (select r from role_index_view where t=title_id and i=5) as r5, - (select r from role_index_view where t=title_id and i=6) as r6, - (select r from role_index_view where t=title_id and i=7) as r7 - from - titles - where - is_hidden = 0 - union - select - title_name, - scenario, - count(*) as total, - sum((select i from role_index_view where t=title_id and r=result) = 1) as r1, - sum((select i from role_index_view where t=title_id and r=result) = 2) as r2, - sum((select i from role_index_view where t=title_id and r=result) = 3) as r3, - sum((select i from role_index_view where t=title_id and r=result) = 4) as r4, - sum((select i from role_index_view where t=title_id and r=result) = 5) as r5, - sum((select i from role_index_view where t=title_id and r=result) = 6) as r6, - sum((select i from role_index_view where t=title_id and r=result) = 7) as r7 - from - games - natural join titles - where - is_hidden = 0 - and status = 2 - and game_id in (select game_id from opposed_games) - group by - title_id, - scenario - order by - title_name, - total desc nulls first - ; @@ -843,13 +843,11 @@ app.post('/forum/reply/:thread_id', must_be_logged_in, function (req, res) { let TITLES = {}; let RULES = {}; -let ROLES = {}; let HTML_ABOUT = {}; let HTML_CREATE = {}; function load_rules() { const SQL_SELECT_TITLES = SQL("SELECT * FROM titles"); - const SQL_SELECT_TITLE_ROLES = SQL("SELECT role FROM roles WHERE title_id=?").pluck(); for (let title of SQL_SELECT_TITLES.all()) { let title_id = title.title_id; if (fs.existsSync(__dirname + "/public/" + title_id + "/rules.js")) { @@ -857,7 +855,6 @@ function load_rules() { try { TITLES[title_id] = title; RULES[title_id] = require("./public/" + title_id + "/rules.js"); - ROLES[title_id] = SQL_SELECT_TITLE_ROLES.all(title_id); HTML_ABOUT[title_id] = fs.readFileSync("./public/" + title_id + "/about.html"); HTML_CREATE[title_id] = fs.readFileSync("./public/" + title_id + "/create.html"); } catch (err) { @@ -869,6 +866,13 @@ function load_rules() { } } +function get_game_roles(title_id, scenario, options) { + let roles = RULES[title_id].roles; + if (typeof roles === 'function') + return roles(scenario, options); + return roles; +} + load_rules(); const SQL_INSERT_GAME = SQL("INSERT INTO games (owner_id,title_id,scenario,options,is_private,is_random,description) VALUES (?,?,?,?,?,?,?)"); @@ -1216,7 +1220,7 @@ app.get('/join/:game_id', must_be_logged_in, function (req, res) { if (!game) return res.status(404).send("Invalid game ID."); annotate_game(game, req.user.user_id); - let roles = ROLES[game.title_id]; + let roles = get_game_roles(game.title_id, game.scenario, game.options); let players = SQL_SELECT_PLAYERS_JOIN.all(game_id); let ready = (game.status === 0) && RULES[game.title_id].ready(game.scenario, game.options, players); res.render('join.pug', { @@ -1289,7 +1293,7 @@ function assign_random_roles(game, players) { list.splice(k, 1); return r; } - let roles = ROLES[game.title_id].slice(); + let roles = get_game_roles(game.title_id, game.scenario, game.options).slice(); for (let p of players) { let old_role = p.role; p.role = pick_random_item(roles); @@ -1813,14 +1817,46 @@ wss.on('connection', (socket, req, client) => { * HIDDEN EXTRAS */ -const SQL_GAME_STATS = SQL("SELECT * FROM game_stat_view"); +const SQL_GAME_STATS = SQL(` + select + title_id, scenario, options, + group_concat(result) as result_role, + group_concat(n) as result_count, + sum(n) as total + from + ( + select + title_id, scenario, options, + result, + count(1) as n + from + opposed_games + natural join game_state + where + status=2 + group by + title_id, + scenario, + options, + result + ) + group by + title_id, scenario, options + having + total > 12 + `); app.get('/stats', function (req, res) { let stats = SQL_GAME_STATS.all(); + stats.forEach(row => { + row.title_name = TITLES[row.title_id].title_name; + row.options = format_options(row.options); + row.result_role = row.result_role.split(","); + row.result_count = row.result_count.split(",").map(Number); + }); res.render('stats.pug', { user: req.user, stats: stats, - titles: TITLES, }); }); diff --git a/views/stats.pug b/views/stats.pug index 9e625b8..65649b3 100644 --- a/views/stats.pug +++ b/views/stats.pug @@ -5,32 +5,31 @@ html include head title Game Statistics style. - table { table-layout: fixed; min-width: auto; margin-bottom: 30px; } + table { min-width: auto; margin-bottom: 30px; } td:not(:first-child) { text-align: right; } th:not(:first-child) { text-align: right; } - td { width: 100px; } - td:first-child { width: 240px; } + td:not(:first-child) { width: 50px; } + td:first-child { width: 400px; } body include header article h1 Game Statistics - function p(t,r) { return r > 0 ? Math.round(r*100/t) + "%" : "" } - - function drawn(x) { return x.total-(x.r1+x.r2+x.r3+x.r4+x.r5+x.r6+x.r7) } - each title in titles + each row in stats table thead tr - th: a(href="/"+title.title_id)= title.title_name - each row in stats - unless row.scenario || row.title_name !== title.title_name - th= row.r1 - th= row.r2 - th Draw + th + a(href="/"+row.title_id)= row.title_name + unless row.scenario === "Standard" + | , #{row.scenario} + unless row.options === "None" + | , #{row.options} + th= row.total tbody - each row in stats - if row.scenario && row.title_name === title.title_name - tr - td #{row.scenario} (#{row.total}) - td= p(row.total, row.r1) - td= p(row.total, row.r2) - td= p(row.total, drawn(row)) + - for (let i=0; i<row.result_role.length; ++i) + - let role = row.result_role[i] + - let count = row.result_count[i] + tr + td= role + td= p(row.total,count) |