summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-08-12 15:36:37 +0200
committerYorhel <git@yorhel.nl>2009-08-12 15:36:37 +0200
commite8ed9663331278c6bd555f7a69c45e84fc4bb34e (patch)
tree6c4f6b4539398b48dee64bdd2045713cf5d17013 /util
parent9af811fcde7d9f5c8e9f63390e895fcb8fc07677 (diff)
Added global user votes ignore list
Diffstat (limited to 'util')
-rw-r--r--util/dump.sql9
-rw-r--r--util/updates/update_2.7.sql21
2 files changed, 28 insertions, 2 deletions
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);
diff --git a/util/updates/update_2.7.sql b/util/updates/update_2.7.sql
new file mode 100644
index 00000000..1feea9be
--- /dev/null
+++ b/util/updates/update_2.7.sql
@@ -0,0 +1,21 @@
+
+
+-- add a flag to users whose votes we want to ignore
+ALTER TABLE users ADD COLUMN ign_votes boolean NOT NULL DEFAULT FALSE;
+
+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 users u ON u.id = v.uid AND NOT u.ign_votes -- slow
+ 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) -- faster
+ 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);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+