diff options
Diffstat (limited to 'schema.sql')
| -rw-r--r-- | schema.sql | 280 |
1 files changed, 242 insertions, 38 deletions
@@ -38,24 +38,53 @@ create table if not exists users ( mail text unique collate nocase, notify integer default 0, is_verified boolean default 0, - is_banned boolean default 0, - ctime datetime default current_timestamp, - password text, - salt text, - about text + is_banned boolean default 0 ); insert or ignore into - users (user_id, name, mail, ctime) - values (0, 'Deleted', 'deleted@nowhere', null) + users (user_id, name, mail) + values (0, 'Deleted', 'deleted@nowhere') ; +create table if not exists user_password ( + user_id integer primary key, + password text, + salt text +); + +create table if not exists user_about ( + user_id integer primary key, + about text +); + +create table if not exists user_first_seen ( + user_id integer primary key, + ctime datetime, + ip text +); + create table if not exists user_last_seen ( user_id integer primary key, atime datetime, ip text ); +create table if not exists user_timeout ( + user_id integer, + game_id integer, + time datetime default current_timestamp, + primary key (user_id, game_id) +); + +create index if not exists user_timeout_idx on user_timeout(user_id, time); + +create table if not exists user_move_hist ( + user_id integer, + minutes integer, + frequency integer default 1, + primary key (user_id, minutes) +) without rowid; + create table if not exists tokens ( user_id integer primary key, token text, @@ -84,15 +113,101 @@ create view user_login_view as user_id, name, mail, notify, password, salt from users + left join user_password using(user_id) + ; + +drop view if exists user_move_iqr; +create view user_move_iqr as + with + aa as ( + select + user_id, + sum(frequency) as total + from user_move_hist + group by user_id + ), + bb as ( + select + user_id, + minutes, + 4 * sum(frequency) over (partition by user_id order by minutes) / (total+1) as quartile + from aa join user_move_hist using(user_id) + ), + cc as ( + select + user_id, + quartile, + last_value(minutes) over (partition by user_id order by quartile) as minutes + from bb + where quartile < 3 + group by user_id, quartile + ) + select + user_id, + sum(minutes) filter (where quartile = 0) as q1, + sum(minutes) filter (where quartile = 1) as q2, + sum(minutes) filter (where quartile = 2) as q3 + from cc + group by user_id ; drop view if exists user_profile_view; create view user_profile_view as - select - user_id, name, mail, notify, ctime, atime, about, is_banned - from - users - left join user_last_seen using(user_id) + with + timeout as ( + select + user_id, + count(1) as timeout_total, + max(time) as timeout_last + from + user_timeout + group by + user_id + ), + user_move_mean as ( + select + user_id, + sum(minutes * frequency) / sum(frequency) as move_time_mean + from + user_move_hist + group by + user_id + ), + profile as ( + select + user_id, name, mail, notify, ctime, atime, about, is_banned, + move_time_mean, + coalesce(q1, q2, q3) as move_time_q1, + coalesce(q2, q3) as move_time_q2, + q3 as move_time_q3, + coalesce(timeout_total, 0) as timeout_total, + coalesce(timeout_last, 0) as timeout_last + from + users + left join user_first_seen using(user_id) + left join user_last_seen using(user_id) + left join user_about using(user_id) + left join timeout using(user_id) + left join user_move_mean using(user_id) + left join user_move_iqr using(user_id) + ) + select + profile.*, + ( + select + count(1) + from + players + join games using(game_id) + where + players.user_id = profile.user_id + and games.is_opposed + and games.status > 1 + and games.result != 'None' + and games.mtime > timeout_last + ) as games_since_timeout + from + profile ; drop view if exists user_dynamic_view; @@ -119,16 +234,17 @@ create view user_dynamic_view as join games using(game_id) where status = 1 - and user_count = player_count + and is_opposed + and is_active and players.user_id = users.user_id - and active in ( players.role, 'Both' ) ) + ( select count(*) from players where - players.user_id = users.user_id and players.is_invite + is_invite + and players.user_id = users.user_id ) + ( select count(*) @@ -157,6 +273,7 @@ create view rated_games_view as and moves >= player_count * 3 and user_count = player_count and player_count > 1 + and result != 'None' and not exists ( select 1 from players where players.game_id = games.game_id and user_id = 0 ) @@ -427,6 +544,8 @@ create table if not exists players ( role text, user_id integer, is_invite integer, + is_active boolean, + active_time real, -- julianday clock real, score integer, primary key (game_id, role) @@ -471,19 +590,11 @@ create view player_view as name, role, is_invite, + is_active, ( - case status - when 0 then - owner_id = user_id - when 1 then - active in ( 'Both', role ) - else - 0 - end - ) as is_active, - ( - case when active in ( 'Both', role ) then - clock - (julianday() - julianday(mtime)) + case when is_active + then + clock - (julianday() - julianday(active_time)) else clock end @@ -509,8 +620,8 @@ create view time_control_view as join players using(game_id) where status = 1 - and active in ( 'Both', role ) - and clock - (julianday() - julianday(mtime)) < 0 + and is_active + and clock - (julianday() - julianday(players.active_time)) < 0 ; -- Export game state as JSON @@ -632,6 +743,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 @@ -687,6 +809,7 @@ begin set score = ( case when new.result is null then null + when new.result = 'None' then null when new.result = role then 2 when new.result = 'Draw' then 1 when instr(new.result, role) then 1 @@ -759,7 +882,7 @@ begin with tt as ( select - round_count as threshold + (2 * round_count) / player_count as threshold from tm_seeds where @@ -834,15 +957,91 @@ begin games.game_id = old.game_id; end; --- Trigger to track time spent! +-- Triggers to track is_active and time spent! -drop trigger if exists trigger_time_used_update; -create trigger trigger_time_used_update before update of active on games +drop trigger if exists trigger_game_started; +create trigger trigger_game_started after update of status on games + when old.status = 0 and new.status = 1 begin - update players - set clock = clock - (julianday() - julianday(old.mtime)) + update + players + set + clock = ( + case old.pace + when 1 then 1 + when 2 then 3 + when 3 then 3 + else 21 + end + ) where - players.game_id = old.game_id and players.role in ( 'Both', old.active ); + players.game_id = old.game_id + ; +end; + +drop trigger if exists trigger_active_changed; +create trigger trigger_active_changed after update of active on games +begin + update + players + set + is_active = ( new.active = 'Both' or instr(new.active, players.role) ) + where + players.game_id = old.game_id + ; +end; + +drop trigger if exists trigger_player_to_active; +create trigger trigger_player_to_active after update of is_active on players + when old.is_active is not true and new.is_active +begin + update + players + set + active_time = julianday() + where + players.game_id = old.game_id and players.role = old.role + ; +end; + +drop trigger if exists trigger_player_to_inactive; +create trigger trigger_player_to_inactive after update of is_active on players + when old.is_active and (not new.is_active) +begin + update + players + set + active_time = null, + clock = ( + case (select pace from games where games.game_id = players.game_id) + when 1 then min(clock - (julianday() - julianday(old.active_time)) + 4 / 24.0, 3) + when 2 then min(clock - (julianday() - julianday(old.active_time)) + 12 / 24.0, 5) + when 3 then min(clock - (julianday() - julianday(old.active_time)) + 36 / 24.0, 10) + else 21 + end + ) + where + players.game_id = old.game_id and players.role = old.role + ; + insert into user_move_hist (user_id, minutes) + select + old.user_id, + case + when minutes < 60 then ceil(minutes) + when minutes < 720 then round(minutes / 5) * 5 + when minutes < 4320 then round(minutes / 15) * 15 + when minutes < 7200 then round(minutes / 60) * 60 + else round(minutes / 360) * 360 + end as minutes + from ( + select (julianday() - julianday(old.active_time)) * 1440 as minutes + ) + where ( + select is_opposed from games where games.game_id = old.game_id + ) + on conflict do update + set frequency = frequency + 1 + ; end; -- Trigger to remove game data when filing a game as archived @@ -875,10 +1074,15 @@ end; drop trigger if exists trigger_delete_on_users; create trigger trigger_delete_on_users after delete on users begin + delete from user_password where user_id = old.user_id; + delete from user_first_seen where user_id = old.user_id; + delete from user_last_seen where user_id = old.user_id; + delete from user_about where user_id = old.user_id; + delete from user_move_hist where user_id = old.user_id; + delete from user_timeout where user_id = old.user_id; + delete from webhooks where user_id = old.user_id; delete from logins where user_id = old.user_id; delete from tokens where user_id = old.user_id; - delete from webhooks where user_id = old.user_id; - delete from user_last_seen where user_id = old.user_id; delete from read_threads where user_id = old.user_id; delete from unread_chats where user_id = old.user_id; delete from contacts where me = old.user_id or you = old.user_id; |