diff options
-rw-r--r-- | data/style.css | 2 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Elm.pm | 5 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Lib.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/List.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Page.pm | 2 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/VNTab.pm | 2 | ||||
-rw-r--r-- | sql/func.sql | 15 | ||||
-rw-r--r-- | sql/schema.sql | 3 | ||||
-rw-r--r-- | sql/tableattrs.sql | 1 | ||||
-rw-r--r-- | util/updates/2020-10-15-reviews-anonymous-votes.sql | 4 |
10 files changed, 20 insertions, 20 deletions
diff --git a/data/style.css b/data/style.css index 240981e3..a9c673b6 100644 --- a/data/style.css +++ b/data/style.css @@ -633,7 +633,7 @@ div.producerbrowse { padding-bottom: 10px } .reviewlist td.tc2 { width: 110px; } .reviewlist td.tc3 { width: 50px; text-align: right } .reviewlist td.tc4 { width: 50px } -.reviewlist td.tc6 { width: 80px } +.reviewlist td.tc6 { width: 140px } .reviewlist td.tc7 { width: 30px; text-align: right } .reviewlist td.tc8 { width: 250px; text-align: right } diff --git a/lib/VNWeb/Reviews/Elm.pm b/lib/VNWeb/Reviews/Elm.pm index 385c8b0f..9b520501 100644 --- a/lib/VNWeb/Reviews/Elm.pm +++ b/lib/VNWeb/Reviews/Elm.pm @@ -13,13 +13,12 @@ my $VOTE_IN = form_compile in => $VOTE; our $VOTE_OUT = form_compile out => $VOTE; elm_api ReviewsVote => $VOTE_OUT, $VOTE_IN, sub { - return elm_Unauth if !auth; my($data) = @_; - my %id = (uid => auth->uid, id => $data->{id}); + my %id = (auth ? (uid => auth->uid) : (ip => tuwf->reqIP()), id => $data->{id}); my %val = (vote => $data->{my}?1:0, overrule => auth->permBoardmod ? $data->{overrule}?1:0 : 0, date => sql 'NOW()'); tuwf->dbExeci( defined $data->{my} - ? sql 'INSERT INTO reviews_votes', {%id,%val}, 'ON CONFLICT (id,uid) DO UPDATE SET', \%val + ? sql 'INSERT INTO reviews_votes', {%id,%val}, 'ON CONFLICT (id,', auth ? 'uid' : 'ip', ') DO UPDATE SET', \%val : sql 'DELETE FROM reviews_votes WHERE', \%id ); elm_Success diff --git a/lib/VNWeb/Reviews/Lib.pm b/lib/VNWeb/Reviews/Lib.pm index 2872966c..1f7c6e4e 100644 --- a/lib/VNWeb/Reviews/Lib.pm +++ b/lib/VNWeb/Reviews/Lib.pm @@ -7,8 +7,8 @@ our @EXPORT = qw/reviews_vote_ reviews_format/; sub reviews_vote_ { my($w) = @_; span_ sub { - elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, {%$w, mod => auth->permBoardmod} if auth && ($w->{can} || auth->permBoardmod); - b_ class => 'grayedout', sprintf ' %d/%d', $w->{c_up}, $w->{c_down} if auth->permBoardmod; + elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, {%$w, mod => auth->permBoardmod||0} if $w->{can} || auth->permBoardmod; + b_ class => 'grayedout', sprintf ' %.2f/%.2f', $w->{c_up}/100, $w->{c_down}/100 if auth->permBoardmod; } } diff --git a/lib/VNWeb/Reviews/List.pm b/lib/VNWeb/Reviews/List.pm index 94c65625..eed0abf9 100644 --- a/lib/VNWeb/Reviews/List.pm +++ b/lib/VNWeb/Reviews/List.pm @@ -27,7 +27,7 @@ sub tablebox_ { td_ class => 'tc3', fmtvote $_->{vote}; td_ class => 'tc4', $_->{isfull} ? 'Full' : 'Mini'; td_ class => 'tc5', sub { a_ href => "/$_->{id}", $_->{title}; b_ class => 'grayedout', ' (flagged)' if $_->{c_flagged} }; - td_ class => 'tc6', sprintf '👍 %d 👎 %d', $_->{c_up}, $_->{c_down} if auth->isMod; + td_ class => 'tc6', sprintf '👍 %.2f 👎 %.2f', $_->{c_up}/100, $_->{c_down}/100 if auth->isMod; td_ class => 'tc7', $_->{c_count}; td_ class => 'tc8', $_->{c_lastnum} ? sub { user_ $_, 'lu_'; diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm index 7673996a..d70a612f 100644 --- a/lib/VNWeb/Reviews/Page.pm +++ b/lib/VNWeb/Reviews/Page.pm @@ -94,7 +94,7 @@ TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub { LEFT JOIN users u ON u.id = r.uid LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid LEFT JOIN (SELECT id, COUNT(*) FROM reviews_posts GROUP BY id) AS c(id,count) ON c.id = r.id - LEFT JOIN reviews_votes rv ON rv.id = r.id AND rv.uid =', \auth->uid, ' + LEFT JOIN reviews_votes rv ON rv.id = r.id AND', auth ? ('rv.uid =', \auth->uid) : ('rv.ip =', \tuwf->reqIP()), ' LEFT JOIN reviews rm ON rm.vid = r.vid AND rm.uid =', \auth->uid, ' WHERE r.id =', \$id ); diff --git a/lib/VNWeb/Reviews/VNTab.pm b/lib/VNWeb/Reviews/VNTab.pm index 8b372e5a..30408299 100644 --- a/lib/VNWeb/Reviews/VNTab.pm +++ b/lib/VNWeb/Reviews/VNTab.pm @@ -14,7 +14,7 @@ sub reviews_ { FROM reviews r LEFT JOIN users u ON r.uid = u.id LEFT JOIN ulist_vns uv ON uv.uid = r.uid AND uv.vid = r.vid - LEFT JOIN reviews_votes rv ON rv.uid =', \auth->uid, ' AND rv.id = r.id + LEFT JOIN reviews_votes rv ON rv.id = r.id AND', auth ? ('rv.uid =', \auth->uid) : ('rv.ip =', \tuwf->reqIP()), ' LEFT JOIN reviews rm ON rm.vid = r.vid AND rm.uid =', \auth->uid, ' WhERE NOT r.c_flagged AND r.vid =', \$v->{id}, 'AND', ($mini ? 'NOT' : ''), 'r.isfull ORDER BY r.c_up-r.c_down DESC' diff --git a/sql/func.sql b/sql/func.sql index 30e9be4d..0d86dcd9 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -188,15 +188,10 @@ END; $$ LANGUAGE plpgsql; -- Update reviews.c_up, c_down and c_flagged CREATE OR REPLACE FUNCTION update_reviews_votes_cache(vndbid) RETURNS void AS $$ BEGIN - WITH stats(id,up,down,flag) AS ( + WITH stats(id,up,down) AS ( SELECT r.id - , COUNT(*) FILTER(WHERE rv.vote AND NOT u.ign_votes AND r2.id IS NULL) - , COUNT(*) FILTER(WHERE NOT rv.vote AND NOT u.ign_votes AND r2.id IS NULL) - -- flag score = up-down < -10, overrule votes count for 10000 (this algorithm is subject to tuning) - , COALESCE( - SUM((CASE WHEN rv.vote THEN 1 ELSE -1 END)*(CASE WHEN rv.overrule THEN 10000 ELSE 1 END)) - FILTER(WHERE NOT u.ign_votes AND (r2.id IS NULL OR rv.overrule)), - 0) < -1000 + , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE rv.vote AND NOT u.ign_votes AND r2.id IS NULL), 0) + , COALESCE(SUM(CASE WHEN rv.overrule THEN 100000 WHEN rv.ip IS NULL THEN 100 ELSE 1 END) FILTER(WHERE NOT rv.vote AND NOT u.ign_votes AND r2.id IS NULL), 0) FROM reviews r LEFT JOIN reviews_votes rv ON rv.id = r.id LEFT JOIN users u ON u.id = rv.uid @@ -204,8 +199,8 @@ BEGIN WHERE $1 IS NULL OR r.id = $1 GROUP BY r.id ) - UPDATE reviews SET c_up = up, c_down = down, c_flagged = flag - FROM stats WHERE reviews.id = stats.id AND (reviews.c_up,reviews.c_down,reviews.c_flagged) <> (stats.up,stats.down,stats.flag); + UPDATE reviews SET c_up = up, c_down = down, c_flagged = up-down<-10000 + FROM stats WHERE reviews.id = stats.id AND (c_up,c_down,c_flagged) <> (up,down,up-down<10000); END; $$ LANGUAGE plpgsql; diff --git a/sql/schema.sql b/sql/schema.sql index 934a3de9..046bb833 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -546,7 +546,8 @@ CREATE TABLE reviews_votes ( uid int, date timestamptz NOT NULL, vote boolean NOT NULL, -- true = upvote, false = downvote - overrule boolean NOT NULL DEFAULT false + overrule boolean NOT NULL DEFAULT false, + ip inet -- Only for anonymous votes ); -- rlists diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 8e408b92..829640d8 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -128,6 +128,7 @@ CREATE UNIQUE INDEX reviews_vid_uid ON reviews (vid,uid); CREATE INDEX reviews_uid ON reviews (uid); CREATE INDEX reviews_posts_uid ON reviews_posts (uid); CREATE UNIQUE INDEX reviews_votes_id_uid ON reviews_votes (id,uid); +CREATE UNIQUE INDEX reviews_votes_id_ip ON reviews_votes (id,ip); CREATE INDEX staff_alias_id ON staff_alias (id); CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid); CREATE INDEX tags_vn_date ON tags_vn (date); diff --git a/util/updates/2020-10-15-reviews-anonymous-votes.sql b/util/updates/2020-10-15-reviews-anonymous-votes.sql new file mode 100644 index 00000000..721543f6 --- /dev/null +++ b/util/updates/2020-10-15-reviews-anonymous-votes.sql @@ -0,0 +1,4 @@ +ALTER TABLE reviews_votes ADD COLUMN ip inet; +CREATE UNIQUE INDEX reviews_votes_id_ip ON reviews_votes (id,ip); +\i sql/func.sql +SELECT update_reviews_votes_cache(id) FROM reviews; |