diff options
| -rw-r--r-- | schema.sql | 21 | ||||
| -rw-r--r-- | server.js | 4 |
2 files changed, 23 insertions, 2 deletions
@@ -454,6 +454,7 @@ create table if not exists players ( is_invite integer, time_used real, time_added real, + score integer, primary key (game_id, role) ) without rowid; @@ -648,6 +649,26 @@ begin games.game_id = old.game_id; end; +-- Trigger to update player score when game ends. + +drop trigger if exists trigger_update_score; +create trigger trigger_update_score after update of result on games +begin + update players + set score = ( + case + when new.result is null then null + when new.result = role then 2 + when new.result = 'Draw' then 1 + when instr(new.result, role) then 1 + else 0 + end + ) + where + players.game_id = new.game_id + ; +end; + -- Trigger to track time spent! drop trigger if exists trigger_time_used_update; @@ -3143,7 +3143,7 @@ const SQL_USER_STATS = SQL(` titles.title_name, scenario, role, - sum(role=result) as won, + sum(score) / 2 as won, count(*) as total from players @@ -3163,7 +3163,7 @@ const SQL_USER_STATS = SQL(` titles.title_name, scenario, null as role, - sum(role=result) as won, + sum(score) / 2 as won, count(*) as total from players |