Changed VN score on tags pages to display plain averages

For three reasons:
- Speed
  tag_vn_calc() is now more than 10 times faster (granted, it could
  have been a lot faster even with the bayesian rating, but whatever)
- Consistency with the tag scores displayed on the VN pages (which are
  raw averages as well)
- It didn't always make sense

1 files changed, 38 insertions, 0 deletions

diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
new file mode 100644
index 00000000..dada4889
--- /dev/null
+++ b/util/updates/update_2.10.sql
@@ -0,0 +1,38 @@
+
+-- no more bayesian rating for VN list on tag pages, just plain averages
+DROP TABLE tags_vn_bayesian;
+CREATE TABLE tags_vn_inherit (
+  tag integer NOT NULL,
+  vid integer NOT NULL,
+  users integer NOT NULL,
+  rating real NOT NULL,
+  spoiler smallint NOT NULL
+);
+
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+  -- all votes for all tags
+  CREATE OR REPLACE TEMPORARY VIEW tags_vn_all AS
+    SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
+  -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
+  CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
+    SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+    FROM tags_vn_all GROUP BY tag, vid, uid;
+  -- grouped by (tag, vid) and serialized into a table
+  DROP INDEX IF EXISTS tags_vn_inherit_tag;
+  TRUNCATE tags_vn_inherit;
+  INSERT INTO tags_vn_inherit
+      SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating,
+          (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
+        FROM tags_vn_grouped
+    GROUP BY tag, vid
+      HAVING AVG(vote) > 0;
+  CREATE INDEX tags_vn_inherit_tag ON tags_vn_inherit (tag);
+  -- and update the VN count in the tags table as well
+  UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
+  RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
+
+