From 66f983b8c9bfe547835741fd93d003d56ddea186 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Mon, 10 Jul 2023 14:41:29 +0200
Subject: Add trigger to prune game data when archiving a game.

---
 schema.sql | 14 ++++++++++++++
 1 file changed, 14 insertions(+)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 8e5ff4d..ff99076 100644
--- a/schema.sql
+++ b/schema.sql
@@ -439,6 +439,20 @@ create view your_turn as
 		and active in ('All', 'Both', role)
 	;
 
+-- Trigger to remove game data when filing a game as archived
+
+drop trigger if exists trigger_archive_game;
+create trigger trigger_archive_game after update on games when new.status = 3
+begin
+	delete from game_state where game_id = old.game_id;
+	delete from game_chat where game_id = old.game_id;
+	delete from game_replay where game_id = old.game_id;
+	delete from game_snap where game_id = old.game_id;
+	delete from game_notes where game_id = old.game_id;
+	delete from last_notified where game_id = old.game_id;
+	delete from unread_chats where game_id = old.game_id;
+end;
+
 -- Triggers to clean up without relying on foreign key cascades
 
 drop trigger if exists trigger_delete_on_games;
-- 
cgit v1.2.3

