diff options
| -rw-r--r-- | tools/lift-bans.sh | 30 | ||||
| -rw-r--r-- | tools/purge.sql | 4 |
2 files changed, 30 insertions, 4 deletions
diff --git a/tools/lift-bans.sh b/tools/lift-bans.sh new file mode 100644 index 0000000..e76f621 --- /dev/null +++ b/tools/lift-bans.sh @@ -0,0 +1,30 @@ +#!/bin/bash + +sqlite3 db <<EOF + +begin immediate; + +.mode column + +create temporary view tm_lift_ban_view as + select + user_id, + name, + date(timeout_last), + timeout_total, + games_since_timeout, + (games_since_timeout > timeout_total) and (julianday() > julianday(timeout_last)+14) as lift_ban + from + user_profile_view + where + user_id in (select user_id from tm_banned) + order by lift_ban desc, timeout_last asc +; + +select * from tm_lift_ban_view; + +delete from tm_banned where user_id in (select user_id from tm_lift_ban_view where lift_ban) returning user_id; + +commit; + +EOF diff --git a/tools/purge.sql b/tools/purge.sql index 3e3bfc6..2a95f01 100644 --- a/tools/purge.sql +++ b/tools/purge.sql @@ -28,12 +28,8 @@ create temporary view prune_all_list as ) ; -begin immediate; - 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; |