From 2f0ba65a7b21fe6ef0c6131f73c28a823bcb0b0d Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Fri, 30 Sep 2022 18:36:19 +0200 Subject: Add "delete account" screen. --- schema.sql | 29 ++++++++++++++++++++++++++++- 1 file changed, 28 insertions(+), 1 deletion(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index 28f32e3..c8bb93c 100644 --- a/schema.sql +++ b/schema.sql @@ -44,6 +44,11 @@ create table if not exists users ( about text ); +insert or ignore into + users (user_id, name, mail, ctime) + values (0, 'Deleted', 'deleted@rally-the-troops.com', datetime('1970-01-01')) +; + create table if not exists user_last_seen ( user_id integer primary key @@ -396,7 +401,8 @@ create view your_turn as and active in ('All', 'Both', role) ; --- Manual key management if pragma foreign_keys = off +-- Triggers to clean up without relying on foreign key cascades + drop trigger if exists trigger_delete_on_games; create trigger trigger_delete_on_games after delete on games begin @@ -406,3 +412,24 @@ begin delete from last_notified where game_id = old.game_id; delete from players where game_id = old.game_id; end; + +drop trigger if exists trigger_delete_on_users; +create trigger trigger_delete_on_users after delete on users +begin + delete from logins where user_id = old.user_id; + delete from tokens where user_id = old.user_id; + delete from user_last_seen where user_id = old.user_id; + delete from last_notified where user_id = old.user_id; + delete from messages where from_id = old.user_id or to_id = old.user_id; + 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 players where user_id = old.user_id; + update games set owner_id = 0 where owner_id = old.user_id; +end; + +drop trigger if exists trigger_delete_on_threads; +create trigger trigger_delete_on_threads after delete on threads +begin + delete from posts where thread_id = old.thread_id; +end -- cgit v1.2.3