summaryrefslogtreecommitdiff
path: root/lib/Multi/Maintenance.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/Multi/Maintenance.pm')
-rw-r--r--lib/Multi/Maintenance.pm113
1 files changed, 46 insertions, 67 deletions
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index a371f29b..728bcd20 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -8,8 +8,7 @@ package Multi::Maintenance;
use strict;
use warnings;
use Multi::Core;
-use PerlIO::gzip;
-use VNDBUtil 'normalize_titles';
+use POSIX 'strftime';
use VNDB::Config;
@@ -17,10 +16,10 @@ my $monthly;
sub run {
+ push_watcher schedule 57*60, 3600, \&hourly; # Every hour at xx:57
push_watcher schedule 7*3600+1800, 24*3600, \&daily; # 7:30 UTC, 30 minutes before the daily DB dumps are created
- push_watcher schedule 0, 3600, \&vnsearch_check;
- push_watcher pg->listen(vnsearch => on_notify => \&vnsearch_check);
set_monthly();
+ logrotate();
}
@@ -48,12 +47,45 @@ sub log_res {
}
+sub hourly {
+ pg_cmd 'SELECT update_vnvotestats()', undef, sub { log_res vnstats => @_ };
+}
+
+
+
#
# D A I L Y J O B S
#
+sub logrotate {
+ my $today = strftime '%Y%m%d', localtime;
+ my $oldest = strftime '%Y%m%d', localtime(time() - 30*24*3600);
+
+ my $dir = config->{Multi}{Core}{log_dir};
+ opendir my $D, $dir or AE::log warn => "Unable to read $dir: $!";
+ while (local $_ = readdir $D) {
+ next if /^\./ || /~$/ || !-f "$dir/$_";
+ if (/-([0-9]{8})$/) {
+ unlink "$dir/$_" or AE::log warn => "Unable to rm $dir/$_: $!" if $1 lt $oldest;
+ } elsif (!-f "$dir/$_-$today") {
+ rename "$dir/$_", "$dir/$_-$today" or AE::log warn => "Unable to move $dir/$_: $!";
+ }
+ }
+ AE::log info => 'Logs rotated.';
+}
+
+
my %dailies = (
+ # Delete tags assigned to Multi that also have (possibly inherited) votes from other users.
+ cleanmultitags => q|
+ WITH RECURSIVE
+ t_votes(tag,vid,uid) AS (SELECT tv.tag, tv.vid, tv.uid FROM tags_vn tv LEFT JOIN users u ON u.id = tv.uid WHERE tv.uid IS DISTINCT FROM 'u1' AND (u.id IS NULL OR u.perm_tag)),
+ t_inherit(tag,vid,uid) AS (SELECT * FROM t_votes UNION SELECT tp.parent, th.vid, th.uid FROM t_inherit th JOIN tags_parents tp ON tp.id = th.tag),
+ t_nonmulti(tag,vid) AS (SELECT DISTINCT tag, vid FROM t_inherit),
+ t_del(tag,vid) AS (SELECT tv.tag, tv.vid FROM tags_vn tv JOIN t_nonmulti tn ON (tn.tag,tn.vid) = (tv.tag,tv.vid) WHERE tv.uid = 'u1')
+ DELETE FROM tags_vn tv WHERE tv.uid = 'u1' AND EXISTS(SELECT 1 FROM t_del td WHERE (td.tag,td.vid) = (tv.tag,tv.vid))|,
+
# takes about 50ms to 500ms to complete, depending on how many releases have been released within the past 5 days
vncache_inc => q|
SELECT update_vncache(id)
@@ -71,18 +103,24 @@ my %dailies = (
# takes about 11 seconds, OK
traitcache => 'SELECT traits_chars_calc(NULL)',
- # takes about 5 seconds, OK
- vnstats => 'SELECT update_vnvotestats()',
+ lengthcache => 'SELECT update_vn_length_cache(NULL)',
# takes about 10 seconds, OK
imagecache => 'SELECT update_images_cache(NULL)',
- cleansessions => q|DELETE FROM sessions WHERE expires < NOW()|,
+ reviewcache => 'SELECT update_reviews_votes_cache(NULL)',
+
+ quotescache => 'SELECT quotes_rand_calc()',
+
+ deleteusers => q|SELECT user_delete()|,
+ cleansessions => q|DELETE FROM sessions WHERE expires < NOW() AND type <> 'api2'|,
cleannotifications => q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|,
cleannotifications2=> q|DELETE FROM notifications WHERE id IN (
SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY uid ORDER BY id DESC) > 500 from notifications) AS x(id,del) WHERE x.del)|,
rmunconfirmusers => q|DELETE FROM users WHERE registered < NOW()-'1 week'::interval AND NOT email_confirmed|,
cleanthrottle => q|DELETE FROM login_throttle WHERE timeout < NOW()|,
+ cleanresthrottle => q|DELETE FROM reset_throttle WHERE timeout < NOW()|,
+ cleanregthrottle => q|DELETE FROM registration_throttle WHERE timeout < NOW()|,
);
@@ -101,6 +139,7 @@ sub daily {
run_daily shift(@l), $s if @l;
};
$s->();
+ logrotate;
}
@@ -121,27 +160,6 @@ my %monthlies = (
);
-sub logrotate {
- my $dir = sprintf '%s/old', config->{Multi}{Core}{log_dir};
- mkdir $dir if !-d $dir;
-
- for (glob sprintf '%s/*', config->{Multi}{Core}{log_dir}) {
- next if /^\./ || /~$/ || !-f;
- my $f = /([^\/]+)$/ ? $1 : $_;
- my $n = sprintf '%s/%s.%04d-%02d-%02d.gz', $dir, $f, (localtime)[5]+1900, (localtime)[4]+1, (localtime)[3];
- return if -f $n;
- open my $I, '<', sprintf '%s/%s', config->{Multi}{Core}{log_dir}, $f;
- open my $O, '>:gzip', $n;
- print $O $_ while <$I>;
- close $O;
- close $I;
- open $I, '>', sprintf '%s/%s', config->{Multi}{Core}{log_dir}, $f;
- close $I;
- }
- AE::log info => 'Logs rotated.';
-}
-
-
sub run_monthly {
my($d, $sub) = @_;
pg_cmd $monthlies{$d}, undef, sub {
@@ -157,47 +175,8 @@ sub monthly {
run_monthly shift(@l), $s if @l;
};
$s->();
-
- logrotate;
set_monthly;
}
-
-#
-# V N S E A R C H C A C H E
-#
-
-
-sub vnsearch_check {
- pg_cmd 'SELECT id FROM vn WHERE c_search IS NULL LIMIT 1', undef, sub {
- my $res = shift;
- return if pg_expect $res, 1 or !$res->rows;
-
- my $id = $res->value(0,0);
- pg_cmd q|SELECT title, original, alias FROM vn WHERE id = $1
- UNION SELECT r.title, r.original, NULL FROM releases r JOIN releases_vn rv ON rv.id = r.id WHERE rv.vid = $1 AND NOT r.hidden|,
- [ $id ], sub { vnsearch_update($id, @_) };
- };
-}
-
-
-sub vnsearch_update { # id, res, time
- my($id, $res, $time) = @_;
- return if pg_expect $res, 1;
-
- my $t = normalize_titles(grep length, map
- +($_->{title}, $_->{original}, split /[\n,]/, $_->{alias}||''),
- $res->rowsAsHashes
- );
-
- pg_cmd 'UPDATE vn SET c_search = $1 WHERE id = $2', [ $t, $id ], sub {
- my($res, $t2) = @_;
- return if pg_expect $res, 0;
- AE::log info => sprintf 'Updated search cache for v%d (%3dms SQL)', $id, ($time+$t2)*1000;
- vnsearch_check;
- };
-}
-
-
1;