Add script to purge game snapshots and archive old games.

1 files changed, 35 insertions, 0 deletions

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;