tools/purge.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- 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
		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')
		)
	;

begin;

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);

commit;