summaryrefslogtreecommitdiff
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
parent9af811fcde7d9f5c8e9f63390e895fcb8fc07677 (diff)
Added global user votes ignore list
-rw-r--r--ChangeLog1
-rw-r--r--lib/VNDB/DB/ULists.pm8
-rw-r--r--lib/VNDB/DB/Users.pm4
-rw-r--r--lib/VNDB/Handler/Users.pm4
-rw-r--r--lib/VNDB/Handler/VNPage.pm2
-rw-r--r--lib/VNDB/Util/CommonHTML.pm1
-rw-r--r--util/dump.sql9
-rw-r--r--util/updates/update_2.7.sql21
8 files changed, 43 insertions, 7 deletions
diff --git a/ChangeLog b/ChangeLog
index 08fb7a66..84ab9d3f 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,6 @@
git - ?
- Improved styling of the threeboxes layout
+ - Blacklist a users' votes from the VN vote statistics
2.6 - 2009-08-09
- New screen resolutions: 1024x600 and 1600x1200
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 34bdc2aa..f57c5aa1 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -155,6 +155,7 @@ sub dbVoteGet {
$o{uid} ? ( 'n.uid = ?' => $o{uid} ) : (),
$o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (),
$o{hide} ? ( 'u.show_list = TRUE' => 1 ) : (),
+ $o{hide_ign} ? ( '(NOT u.ign_votes OR u.id = ?)' => $self->authInfo->{id}||0 ) : (),
);
my @select = (
@@ -186,16 +187,19 @@ sub dbVoteGet {
}
-# Arguments: (uid|vid), id
+# Arguments: (uid|vid), id, use_ignore_list
# Returns an arrayref with 10 elements containing the number of votes for index+1
sub dbVoteStats {
- my($self, $col, $id) = @_;
+ my($self, $col, $id, $ign) = @_;
+ my $u = $self->authInfo->{id};
my $r = [ qw| 0 0 0 0 0 0 0 0 0 0 | ];
$r->[$_->{vote}-1] = $_->{votes} for (@{$self->dbAll(q|
SELECT vote, COUNT(vote) as votes
FROM votes
+ !s
!W
GROUP BY vote|,
+ $ign ? 'JOIN users ON id = uid AND (NOT ign_votes'.($u?sprintf(' OR id = %d'.$u):'').')' : '',
$col ? { '!s = ?' => [ $col, $id ] } : {},
)});
return $r;
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index e1f4c378..b2cd1a31 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -43,7 +43,7 @@ sub dbUserGet {
);
my @select = (
- qw|id username mail rank salt c_votes c_changes show_nsfw show_list skin customcss ip c_tags|,
+ qw|id username mail rank salt c_votes c_changes show_nsfw show_list skin customcss ip c_tags ign_votes|,
q|encode(passwd, 'hex') AS passwd|, q|extract('epoch' from registered) as registered|,
$o{what} =~ /stats/ ? (
'(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount',
@@ -74,7 +74,7 @@ sub dbUserEdit {
my %h;
defined $o{$_} && ($h{$_.' = ?'} = $o{$_})
- for (qw| username mail rank show_nsfw show_list skin customcss salt |);
+ for (qw| username mail rank show_nsfw show_list skin customcss salt ign_votes |);
$h{'passwd = decode(?, \'hex\')'} = $o{passwd}
if defined $o{passwd};
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index 2e8ac8ee..45d12e35 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -314,6 +314,7 @@ sub edit {
$self->authCan('usermod') ? (
{ name => 'usrname', template => 'pname', minlength => 2, maxlength => 15 },
{ name => 'rank', enum => [ 1..$#{$self->{user_ranks}} ] },
+ { name => 'ign_votes', required => 0, default => 0 },
) : (),
{ name => 'mail', template => 'mail' },
{ name => 'usrpass', required => 0, minlength => 4, maxlength => 64, template => 'asciiprint' },
@@ -335,6 +336,7 @@ sub edit {
($o{passwd}, $o{salt}) = $self->authPreparePass($frm->{usrpass}) if $frm->{usrpass};
$o{show_list} = $frm->{flags_list} ? 1 : 0;
$o{show_nsfw} = $frm->{flags_nsfw} ? 1 : 0;
+ $o{ign_votes} = $frm->{ign_votes} ? 1 : 0 if $self->authCan('usermod');
$self->dbUserEdit($uid, %o);
$self->dbSessionDel($uid) if $frm->{usrpass};
return $self->resRedirect("/u$uid/edit?d=1", 'post') if $uid != $self->authInfo->{id} || !$frm->{usrpass};
@@ -347,6 +349,7 @@ sub edit {
$frm->{$_} ||= $u->{$_} for(qw|rank mail skin customcss|);
$frm->{flags_list} = $u->{show_list} if !defined $frm->{flags_list};
$frm->{flags_nsfw} = $u->{show_nsfw} if !defined $frm->{flags_nsfw};
+ $frm->{ign_votes} = $u->{ign_votes} if !defined $frm->{ign_votes};
# create the page
my $title = $self->authInfo->{id} != $uid ? "Edit $u->{username}'s Account" : 'My Account';
@@ -366,6 +369,7 @@ sub edit {
[ input => short => 'usrname', name => 'Username' ],
[ select => short => 'rank', name => 'Rank', options => [
map [ $_, $self->{user_ranks}[$_][0] ], 1..$#{$self->{user_ranks}} ] ],
+ [ check => short => 'ign_votes', name => 'Ignore votes in VN statistics' ],
) : (
[ static => label => 'Username', content => $frm->{usrname} ],
),
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 0563c1dc..751fc5cc 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -463,7 +463,7 @@ sub _screenshots {
sub _stats {
my($self, $v) = @_;
- my $stats = $self->dbVoteStats(vid => $v->{id});
+ my $stats = $self->dbVoteStats(vid => $v->{id}, 1);
div class => 'mainbox';
h1 'User stats';
if(!grep $_ > 0, @$stats) {
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 981edfe8..2446457d 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -464,6 +464,7 @@ sub htmlVoteStats {
order => 'date DESC',
what => $type eq 'v' ? 'user' : 'vn',
hide => $type eq 'v',
+ hide_ign => $type eq 'v',
);
if(@$recent) {
table class => 'recentvotes';
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;
+