From 036babec3e9f93822b808a7a62dcf9485ddf3307 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Sun, 29 Sep 2024 01:36:11 +0200 Subject: Be more robust when deleting accounts. Leave player assignment to not mess with Elo ratings and tournament data. Reset ctime when game is actually started. --- schema.sql | 10 ++++------ 1 file changed, 4 insertions(+), 6 deletions(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index ebe9a66..3ba249b 100644 --- a/schema.sql +++ b/schema.sql @@ -177,7 +177,7 @@ create view rating_view as title_id, name, rating, count, last from ratings - join users using(user_id) + left join users using(user_id) order by title_id, rating desc @@ -499,7 +499,7 @@ create view player_view as from games join players using(game_id) - join users using(user_id) + left join users using(user_id) left join user_last_seen using(user_id) ; @@ -538,7 +538,7 @@ create view game_export_view as json_object('role', role, 'name', name) ) from players - join users using(user_id) + left join users using(user_id) where game_id = outer.game_id ), 'state', @@ -683,9 +683,7 @@ begin delete from posts where author_id = old.user_id; delete from threads where author_id = old.user_id; delete from game_chat where user_id = old.user_id; - delete from ratings where user_id = old.user_id; - delete from players where user_id = old.user_id and game_id in (select game_id from games where status <= 1); - update players set user_id = 0 where user_id = old.user_id; + delete from players where user_id = old.user_id and game_id in (select game_id from games where status = 0); update games set owner_id = 0 where owner_id = old.user_id; end; -- cgit v1.2.3