diff options
author | Yorhel <git@yorhel.nl> | 2009-08-12 15:36:37 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-08-12 15:36:37 +0200 |
commit | e8ed9663331278c6bd555f7a69c45e84fc4bb34e (patch) | |
tree | 6c4f6b4539398b48dee64bdd2045713cf5d17013 | |
parent | 9af811fcde7d9f5c8e9f63390e895fcb8fc07677 (diff) |
Added global user votes ignore list
-rw-r--r-- | ChangeLog | 1 | ||||
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 8 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 1 | ||||
-rw-r--r-- | util/dump.sql | 9 | ||||
-rw-r--r-- | util/updates/update_2.7.sql | 21 |
8 files changed, 43 insertions, 7 deletions
@@ -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; + |