summaryrefslogtreecommitdiff
path: root/lib/Multi/Maintenance.pm
blob: 51c6cec83d5d2eda6dfa270ee25f8cc1197d432d (plain)
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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197

#
#  Multi::Maintenance  -  General maintenance functions
#

package Multi::Maintenance;

use strict;
use warnings;
use POE;
use PerlIO::gzip;
use Time::HiRes 'gettimeofday', 'tv_interval';


sub spawn {
  # WARNING: these maintenance tasks can block the process for a few seconds

  my $p = shift;
  POE::Session->create(
    package_states => [
      $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate vnpopularity tagcache |],
    ],
  );
}


sub _start {
  $_[KERNEL]->alias_set('maintenance');
  $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate|vnpopularity|tagcache))+)$/, 'cmd_maintenance');

 # recalculate tag<->vn cache each hour (better do this once every 24 hours when the DB grows)
  $_[KERNEL]->post(core => addcron => '0 * * * *', 'maintenance tagcache');
 # Perform some maintenance functions every day on 0:00
  $_[KERNEL]->post(core => addcron => '0 0 * * *', 'maintenance vncache integrity unkanime vnpopularity');
 # update caches and rotate logs every 1st day of the month at 0:05
  $_[KERNEL]->post(core => addcron => '5 0 1 * *' => 'maintenance usercache statscache revcache logrotate');
}


sub cmd_maintenance {
  $_[KERNEL]->yield($_)
    for (split /\s+/, $_[ARG1]);

  $_[KERNEL]->post(core => finish => $_[ARG0]);
}


sub vncache {
  $_[KERNEL]->call(core => log => 3 => 'Updating c_* columns in the vn table...');
 # takes ~5 seconds, better do this in the background...
  $Multi::SQL->do('SELECT update_vncache(0)');
}


sub usercache {
  $_[KERNEL]->call(core => log => 3 => 'Updating c_* columns in the users table...');
  $Multi::SQL->do(q|UPDATE users SET
    c_votes = COALESCE(
      (SELECT COUNT(vid)
      FROM votes
      WHERE uid = users.id
      GROUP BY uid
    ), 0),
    c_changes = COALESCE(
      (SELECT COUNT(id)
      FROM changes
      WHERE requester = users.id
      GROUP BY requester
    ), 0),
    c_tags = COALESCE(
      (SELECT COUNT(tag)
      FROM tags_vn
      WHERE uid = users.id
      GROUP BY uid
    ), 0)
  |);
}


sub statscache {
  $_[KERNEL]->call(core => log => 3 => 'Updating the stats_cache table...');
  $Multi::SQL->do($_) for(
    q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users'|,
    q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn        WHERE hidden = FALSE) WHERE section = 'vn'|,
    q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases  WHERE hidden = FALSE) WHERE section = 'releases'|,
    q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers'|,
    q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads   WHERE hidden = FALSE) WHERE section = 'threads'|,
    q|UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
        AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts'|
  );
}


sub revcache {
  $_[KERNEL]->call(core => log => 3 => 'Updating rev column in the changes table...');
  # this can take a while, maybe split these up in 3 queries?
  # ...or better yet, use asynchronous communication with PgSQL
  $Multi::SQL->do(q|SELECT update_rev('vn', ''), update_rev('releases', ''), update_rev('producers', '')|);
}


sub integrity {
 # checks for database inconsistencies not handled by the foreign key constraints:
 #   - releases without a VN relation
 #   - changes without an entry in the (vn|releases|producers)_rev table
 #   - threads without a tag or post

  my $q = $Multi::SQL->prepare(q|
   SELECT 'r', id FROM releases_rev rr
     WHERE NOT EXISTS(SELECT 1 FROM releases_vn rv WHERE rr.id = rv.rid)
   UNION
   SELECT c.type::varchar, id FROM changes c
     WHERE (c.type = 0 AND NOT EXISTS(SELECT 1 FROM vn_rev vr WHERE vr.id = c.id))
        OR (c.type = 1 AND NOT EXISTS(SELECT 1 FROM releases_rev rr WHERE rr.id = c.id))
        OR (c.type = 2 AND NOT EXISTS(SELECT 1 FROM producers_rev pr WHERE pr.id = c.id))
   UNION
   SELECT 't', id FROM threads t
     WHERE NOT EXISTS(SELECT 1 FROM threads_posts tp WHERE tp.tid = t.id)
        OR NOT EXISTS(SELECT 1 FROM threads_tags tt WHERE tt.tid = t.id)|);
  $q->execute();
  my $r = $q->fetchall_arrayref([]);
  if(@$r) {
    $_[KERNEL]->call(core => log => 1, '!DATABASE INCONSISTENCIES FOUND!: %s',
      join(', ', map { $_->[0].':'.$_->[1] } @$r));
  } else {
    $_[KERNEL]->call(core => log => 3, 'No database inconsistencies found');
  }
}


sub unkanime {
 # warn for VNs with a non-existing anidb id
 # (maybe do an automated edit or something in the future)

  my $q = $Multi::SQL->prepare(q|
    SELECT v.id, va.aid
    FROM vn_anime va
    JOIN vn v ON va.vid = v.latest
    JOIN anime a ON va.aid = a.id
    WHERE a.lastfetch < 0|);
  $q->execute();
  my $r = $q->fetchall_arrayref([]);
  my %aid = map {
    my $a=$_;
    $a->[1] => join(',', map { $a->[1] == $_->[1] ? $_->[0] : () } @$r)
  } @$r;

  if(keys %aid) {
    $_[KERNEL]->call(core => log => 1, '!NON-EXISTING RELATED ANIME FOUND!: %s',
      join('; ', map { 'a'.$_.':v'.$aid{$_} } keys %aid)
    );
  } else {
    $_[KERNEL]->call(core => log => 3, 'No problems found with the related anime');
  }
}


sub logrotate {
  my $dir = sprintf '%s/old', $VNDB::M{log_dir};
  mkdir $dir if !-d $dir;

  for (glob sprintf '%s/*', $VNDB::M{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];
    if(-f $n) {
      $_[KERNEL]->call(core => log => 1, 'Logs already rotated earlier today!');
      return;
    }
    open my $I, '<', sprintf '%s/%s', $VNDB::M{log_dir}, $f;
    open my $O, '>:gzip', $n;
    print $O $_ while <$I>;
    close $O;
    close $I;
    open $I, '>', sprintf '%s/%s', $VNDB::M{log_dir}, $f;
    close $I;
  }
}


sub vnpopularity {
  my $S = [gettimeofday];
  $Multi::SQL->do(q|SELECT update_vnpopularity()|);
  $_[KERNEL]->call(core => log => 3 => '(Re)calculated vn.c_popularity in %.2fs', tv_interval($S));
}


sub tagcache {
  my $S = [gettimeofday];
  $Multi::SQL->do(q|SELECT tag_vn_calc()|);
  $_[KERNEL]->call(core => log => 3 => '(Re)calculated tags_vn_stored in %.2fs', tv_interval($S));
}


1;