summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--data/style.css2
-rw-r--r--lib/VNWeb/Reviews/Elm.pm5
-rw-r--r--lib/VNWeb/Reviews/Lib.pm4
-rw-r--r--lib/VNWeb/Reviews/List.pm2
-rw-r--r--lib/VNWeb/Reviews/Page.pm2
-rw-r--r--lib/VNWeb/Reviews/VNTab.pm2
-rw-r--r--sql/func.sql15
-rw-r--r--sql/schema.sql3
-rw-r--r--sql/tableattrs.sql1
-rw-r--r--util/updates/2020-10-15-reviews-anonymous-votes.sql4
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;