diff options
| -rw-r--r-- | tools/archive.sql | 2 | ||||
| -rw-r--r-- | tools/purge.sql | 14 |
2 files changed, 10 insertions, 6 deletions
diff --git a/tools/archive.sql b/tools/archive.sql index 149d161..e8b5458 100644 --- a/tools/archive.sql +++ b/tools/archive.sql @@ -1,5 +1,7 @@ -- Make a copy of finished games in a separate archive database. +pragma busy_timeout=10000; + attach database 'db' as live; attach database 'archive.db' as archive; diff --git a/tools/purge.sql b/tools/purge.sql index e48e305..3e0c898 100644 --- a/tools/purge.sql +++ b/tools/purge.sql @@ -1,13 +1,15 @@ -- Prune game snapshot and game state data to save database space. +attach database 'db' as live; + create temporary view prune_snap_list as select distinct game_id from - game_snap + live.game_snap where game_id in ( - select game_id from games + select game_id from live.games where status=2 and date(mtime) < date('now', '-7 days') ) ; @@ -16,10 +18,10 @@ create temporary view prune_all_list as select distinct game_id from - games + live.games where game_id in ( - select game_id from games + select game_id from live.games where status=2 and date(mtime) < date('now', '-28 days') ) ; @@ -27,9 +29,9 @@ create temporary view prune_all_list as 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); +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 games set status = 3 where game_id in (select game_id from prune_all_list); +update live.games set status = 3 where game_id in (select game_id from prune_all_list); commit; |