summaryrefslogtreecommitdiff
path: root/util/sql/func.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/sql/func.sql')
-rw-r--r--util/sql/func.sql16
1 files changed, 16 insertions, 0 deletions
diff --git a/util/sql/func.sql b/util/sql/func.sql
index af01e0fe..8d81c440 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -206,6 +206,22 @@ END;
$$ LANGUAGE plpgsql;
+-- Fully recalculate all rows in stats_cache
+CREATE OR REPLACE FUNCTION update_stats_cache_full() RETURNS void AS $$
+BEGIN
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits';
+ UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads';
+ 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';
+END;
+$$ LANGUAGE plpgsql;