From 9af811fcde7d9f5c8e9f63390e895fcb8fc07677 Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Wed, 12 Aug 2009 13:34:19 +0200
Subject: Synchronised a NOT NULL constraint in dump.sql with the actual DB

threads_posts.edited allows NULL values.
---
 util/dump.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index b3baae99..db1ac017 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -234,7 +234,7 @@ CREATE TABLE threads_posts (
   num smallint NOT NULL DEFAULT 0,
   uid integer NOT NULL DEFAULT 0,
   date timestamptz NOT NULL DEFAULT NOW(),
-  edited timestamptz NOT NULL,
+  edited timestamptz,
   msg text NOT NULL DEFAULT '',
   hidden boolean NOT NULL DEFAULT FALSE,
   PRIMARY KEY(tid, num)
-- 
cgit v1.2.3


From e8ed9663331278c6bd555f7a69c45e84fc4bb34e Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Wed, 12 Aug 2009 15:36:37 +0200
Subject: Added global user votes ignore list

---
 util/dump.sql | 9 +++++++--
 1 file changed, 7 insertions(+), 2 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index db1ac017..c8dec7d6 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -264,7 +264,8 @@ CREATE TABLE users (
   customcss text NOT NULL DEFAULT '',
   ip inet NOT NULL DEFAULT '0.0.0.0',
   c_tags integer NOT NULL DEFAULT 0,
-  salt character(9) NOT NULL DEFAULT ''
+  salt character(9) NOT NULL DEFAULT '',
+  ign_votes voolean NOT NULL DEFAULT FALSE
 );
 
 -- vn
@@ -484,7 +485,11 @@ $$ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
 BEGIN
   CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS
-    SELECT v.uid, v.vid, sqrt(count(*))::real FROM votes v JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote GROUP BY v.vid, v.uid;
+      SELECT v.uid, v.vid, sqrt(count(*))::real
+        FROM votes v
+        JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote
+        WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes)
+    GROUP BY v.vid, v.uid;
   CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
     SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
   UPDATE vn SET c_popularity = COALESCE((SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id), 0);
-- 
cgit v1.2.3


From 5658d5178debd0afa2aa10c0840a52eb53c239fd Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Fri, 18 Sep 2009 11:10:14 +0200
Subject: Fixed bug with excluding AVG(vote) < 0 VNs from tag pages

This was *supposed* to be done in
ce6d43aed955effe136f1ce3682828d2d43109cb
---
 util/dump.sql | 5 +++--
 1 file changed, 3 insertions(+), 2 deletions(-)

(limited to 'util/dump.sql')

diff --git a/util/dump.sql b/util/dump.sql
index c8dec7d6..aaa1a5e9 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -586,7 +586,7 @@ BEGIN
   -- 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 WHERE vote > 0 GROUP BY tag, vid, uid;
+    FROM tags_vn_all GROUP BY tag, vid, uid;
   -- grouped by (tag, vid) and serialized into a table
   DROP INDEX IF EXISTS tags_vn_bayesian_tag;
   TRUNCATE tags_vn_bayesian;
@@ -594,7 +594,8 @@ BEGIN
       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;
+    GROUP BY tag, vid
+      HAVING AVG(vote) > 0;
   CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
   -- now perform the bayesian ranking calculation
   UPDATE tags_vn_bayesian tvs SET rating =
-- 
cgit v1.2.3