Made Multi update the two new caches every first day of the month

May actually be a better idea to just log a HUGE WARNING in case
the cached values don't match the actual values, as that really
shouldn't happen. But oh well, updating is fine, too. Makes sure
everything stays correct in the long term.

1 files changed, 36 insertions, 3 deletions

diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 3c2cd042..2fb7b2bd 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -17,7 +17,7 @@ sub spawn {
   my $p = shift;
   POE::Session->create(
     package_states => [
-      $p => [qw| _start cmd_maintenance vncache revcache integrity unkanime logrotate |], 
+      $p => [qw| _start cmd_maintenance vncache usercache statscache revcache integrity unkanime logrotate |], 
     ],
   );
 }
@@ -25,12 +25,12 @@ sub spawn {
 
 sub _start {
   $_[KERNEL]->alias_set('maintenance');
-  $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|integrity|unkanime|logrotate))+)$/, 'cmd_maintenance');
+  $_[KERNEL]->call(core => register => qr/^maintenance((?: (?:vncache|revcache|usercache|statscache|integrity|unkanime|logrotate))+)$/, 'cmd_maintenance');
   
  # Perform some maintenance functions every day on 0:00
   $_[KERNEL]->post(core => addcron => '0 0 * * *', 'maintenance vncache integrity unkanime');
  # update caches and rotate logs every 1st day of the month at 0:05
-  $_[KERNEL]->post(core => addcron => '5 0 1 * *' => 'maintenance revcache logrotate');
+  $_[KERNEL]->post(core => addcron => '5 0 1 * *' => 'maintenance usercache statscache revcache logrotate');
 }
 
 
@@ -49,6 +49,39 @@ sub vncache {
 }
 
 
+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)
+  |);
+}
+
+
+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?