From 6d56af566dc688e3f2af800dbd17950b06297fe7 Mon Sep 17 00:00:00 2001 From: Tor Andersson 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 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