From 6d56af566dc688e3f2af800dbd17950b06297fe7 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Sun, 24 Mar 2024 16:17:58 +0100
Subject: Add script to purge game snapshots and archive old games.

---
 tools/purge.sql | 35 +++++++++++++++++++++++++++++++++++
 1 file changed, 35 insertions(+)
 create mode 100644 tools/purge.sql

(limited to 'tools/purge.sql')

diff --git a/tools/purge.sql b/tools/purge.sql
new file mode 100644
index 0000000..e48e305
--- /dev/null
+++ b/tools/purge.sql
@@ -0,0 +1,35 @@
+-- Prune game snapshot and game state data to save database space.
+
+create temporary view prune_snap_list as
+	select
+		distinct game_id
+	from
+		game_snap
+	where
+		game_id in (
+			select game_id from games
+			where status=2 and date(mtime) < date('now', '-7 days')
+		)
+	;
+
+create temporary view prune_all_list as
+	select
+		distinct game_id
+	from
+		games
+	where
+		game_id in (
+			select game_id from games
+			where status=2 and date(mtime) < date('now', '-28 days')
+		)
+	;
+
+begin;
+
+select 'PURGE SNAPS FROM ' || count(1) from prune_snap_list;
+delete from game_snap where game_id in (select game_id from prune_snap_list);
+
+select 'PURGE ALL FROM ' || count(1) from prune_all_list;
+update games set status = 3 where game_id in (select game_id from prune_all_list);
+
+commit;
-- 
cgit v1.2.3

