From fc3501382c2aa3ef5b692f4f55c2616f9cade3f5 Mon Sep 17 00:00:00 2001
From: Tor Andersson <tor@ccxvii.net>
Date: Fri, 25 Apr 2025 12:18:00 +0200
Subject: Move toolbox scripts to a "bin" directory.

Add a super "rtt" command to run the scripts.
---
 bin/rtt-archive-prune | 40 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 40 insertions(+)
 create mode 100755 bin/rtt-archive-prune

(limited to 'bin/rtt-archive-prune')

diff --git a/bin/rtt-archive-prune b/bin/rtt-archive-prune
new file mode 100755
index 0000000..fff7f23
--- /dev/null
+++ b/bin/rtt-archive-prune
@@ -0,0 +1,40 @@
+#!/bin/bash
+sqlite3 <<EOF
+
+-- Prune game snapshot and game state data to save database space.
+
+attach database 'db' as live;
+
+pragma live.busy_timeout=10000;
+
+create temporary view prune_snap_list as
+	select
+		distinct game_id
+	from
+		live.game_snap
+	where
+		game_id in (
+			select game_id from live.games
+			where status=2 and date(mtime) < date('now', '-7 days')
+		)
+	;
+
+create temporary view prune_all_list as
+	select
+		distinct game_id
+	from
+		live.games
+	where
+		game_id in (
+			select game_id from live.games
+			where status=2 and date(mtime) < date('now', '-28 days')
+		)
+	;
+
+select 'PURGE SNAPS FROM ' || count(1) from prune_snap_list;
+delete from live.game_snap where game_id in (select game_id from prune_snap_list);
+
+select 'PURGE ALL FROM ' || count(1) from prune_all_list;
+update live.games set status = 3 where game_id in (select game_id from prune_all_list);
+
+EOF
-- 
cgit v1.2.3