summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--elm/Reviews/Vote.elm13
-rw-r--r--lib/VNWeb/Reviews/Elm.pm8
-rw-r--r--lib/VNWeb/Reviews/List.pm4
-rw-r--r--lib/VNWeb/Reviews/Page.pm11
-rw-r--r--lib/VNWeb/Reviews/VNTab.pm8
-rw-r--r--lib/VNWeb/VN/Page.pm2
-rw-r--r--sql/func.sql17
-rw-r--r--sql/schema.sql6
-rw-r--r--util/updates/2020-09-03-reviews-flagging.sql5
9 files changed, 51 insertions, 23 deletions
diff --git a/elm/Reviews/Vote.elm b/elm/Reviews/Vote.elm
index c3d8ff9d..490a2b78 100644
--- a/elm/Reviews/Vote.elm
+++ b/elm/Reviews/Vote.elm
@@ -23,6 +23,8 @@ type alias Model =
{ state : Api.State
, id : String
, my : Maybe Bool
+ , overrule : Bool
+ , mod : Bool
}
init : GRV.Recv -> Model
@@ -30,19 +32,23 @@ init d =
{ state = Api.Normal
, id = d.id
, my = d.my
+ , overrule = d.overrule
+ , mod = d.mod
}
type Msg
= Vote Bool
+ | Overrule Bool
| Saved GApi.Response
update : Msg -> Model -> (Model, Cmd Msg)
update msg model =
+ let save m = ({ m | state = Api.Loading }, GRV.send { id = m.id, my = m.my, overrule = m.overrule } Saved)
+ in
case msg of
- Vote b ->
- let my = if model.my == Just b then Nothing else Just b
- in ({ model | my = my, state = Api.Loading }, GRV.send { id = model.id, my = my } Saved)
+ Vote b -> save { model | my = if model.my == Just b then Nothing else Just b }
+ Overrule b -> let nm = { model | overrule = b } in if isJust model.my then save nm else (nm, Cmd.none)
Saved GApi.Success -> ({ model | state = Api.Normal }, Cmd.none)
Saved e -> ({ model | state = Api.Error e }, Cmd.none)
@@ -60,4 +66,5 @@ view model =
, but True "yes"
, text " / "
, but False "no"
+ , if not model.mod then text "" else label [] [ text " / ", inputCheck "" model.overrule Overrule, text " O" ]
]
diff --git a/lib/VNWeb/Reviews/Elm.pm b/lib/VNWeb/Reviews/Elm.pm
index 6bce7973..385c8b0f 100644
--- a/lib/VNWeb/Reviews/Elm.pm
+++ b/lib/VNWeb/Reviews/Elm.pm
@@ -3,8 +3,10 @@ package VNWeb::Reviews::Elm;
use VNWeb::Prelude;
my $VOTE = {
- id => { vndbid => 'w' },
- my => { required => 0, jsonbool => 1 },
+ id => { vndbid => 'w' },
+ my => { required => 0, jsonbool => 1 },
+ overrule => { anybool => 1 },
+ mod => { _when => 'out', anybool => 1 },
};
my $VOTE_IN = form_compile in => $VOTE;
@@ -14,7 +16,7 @@ elm_api ReviewsVote => $VOTE_OUT, $VOTE_IN, sub {
return elm_Unauth if !auth;
my($data) = @_;
my %id = (uid => auth->uid, id => $data->{id});
- my %val = (vote => $data->{my}?1:0, date => sql 'NOW()');
+ 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
diff --git a/lib/VNWeb/Reviews/List.pm b/lib/VNWeb/Reviews/List.pm
index 8d2d0903..94c65625 100644
--- a/lib/VNWeb/Reviews/List.pm
+++ b/lib/VNWeb/Reviews/List.pm
@@ -26,7 +26,7 @@ sub tablebox_ {
td_ class => 'tc2', sub { user_ $_ };
td_ class => 'tc3', fmtvote $_->{vote};
td_ class => 'tc4', $_->{isfull} ? 'Full' : 'Mini';
- td_ class => 'tc5', sub { a_ href => "/$_->{id}", $_->{title} };
+ 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 => 'tc7', $_->{c_count};
td_ class => 'tc8', $_->{c_lastnum} ? sub {
@@ -56,7 +56,7 @@ TUWF::get qr{/w}, sub {
my $where = $u ? sql 'w.uid =', \$u->{id} : '1=1';
my $count = tuwf->dbVali('SELECT COUNT(*) FROM reviews w WHERE', $where);
my $lst = tuwf->dbPagei({results => 50, page => $opt->{p}}, '
- SELECT w.id, w.vid, w.isfull, w.c_up, w.c_down, w.c_count, w.c_lastnum, v.title, uv.vote
+ SELECT w.id, w.vid, w.isfull, w.c_up, w.c_down, w.c_flagged, w.c_count, w.c_lastnum, v.title, uv.vote
, ', sql_user(), ',', sql_totime('w.date'), 'as date
, ', sql_user('wpu','lu_'), ',', sql_totime('wp.date'), 'as ldate
FROM reviews w
diff --git a/lib/VNWeb/Reviews/Page.pm b/lib/VNWeb/Reviews/Page.pm
index 55847bf6..a030b477 100644
--- a/lib/VNWeb/Reviews/Page.pm
+++ b/lib/VNWeb/Reviews/Page.pm
@@ -50,6 +50,11 @@ sub review_ {
my($date, $lastmod) = map $_&&fmtdate($_,'compact'), $w->@{'date', 'lastmod'};
txt_ " on $date";
b_ class => 'grayedout', " last updated on $lastmod" if $lastmod && $date ne $lastmod;
+ if($w->{c_flagged}) {
+ br_;
+ br_;
+ b_ class => 'grayedout', 'Flagged: this review is below the voting threshold and not visible on the VN page.';
+ }
}
};
tr_ class => 'reviewnotspoil', sub {
@@ -65,7 +70,7 @@ sub review_ {
tr_ sub {
td_ '';
td_ style => 'text-align: right', sub {
- elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, $w if auth && $w->{can};
+ elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, {%$w, mod => auth->permBoardmod} if auth && ($w->{can} || auth->permBoardmod);
};
};
}
@@ -75,8 +80,8 @@ sub review_ {
TUWF::get qr{/$RE{wid}(?:(?<sep>[\./])$RE{num})?}, sub {
my($id, $sep, $num) = (tuwf->capture('id'), tuwf->capture('sep')||'', tuwf->capture('num'));
my $w = tuwf->dbRowi(
- 'SELECT r.id, r.vid, r.rid, r.isfull, r.text, r.spoiler, COALESCE(c.count,0) AS count, uv.vote, rm.id IS NULL AS can
- , v.title, rel.title AS rtitle, rel.original AS roriginal, rel.type AS rtype, rv.vote AS my
+ 'SELECT r.id, r.vid, r.rid, r.isfull, r.text, r.spoiler, COALESCE(c.count,0) AS count, r.c_flagged, uv.vote, rm.id IS NULL AS can
+ , v.title, rel.title AS rtitle, rel.original AS roriginal, rel.type AS rtype, rv.vote AS my, COALESCE(rv.overrule,false) AS overrule
, ', sql_user(), ',', sql_totime('r.date'), 'AS date,', sql_totime('r.lastmod'), 'AS lastmod
FROM reviews r
JOIN vn v ON v.id = r.vid
diff --git a/lib/VNWeb/Reviews/VNTab.pm b/lib/VNWeb/Reviews/VNTab.pm
index 9b5427d1..20304315 100644
--- a/lib/VNWeb/Reviews/VNTab.pm
+++ b/lib/VNWeb/Reviews/VNTab.pm
@@ -6,16 +6,16 @@ use VNWeb::Prelude;
sub reviews_ {
my($v, $mini) = @_;
- # TODO: Better order, pagination
+ # TODO: Better order, pagination, option to show flagged reviews
my $lst = tuwf->dbAlli(
- 'SELECT r.id, r.rid, r.text, r.spoiler, r.c_count, uv.vote, rv.vote AS my, NOT r.isfull AND rm.id IS NULL AS can
+ 'SELECT r.id, r.rid, r.text, r.spoiler, r.c_count, uv.vote, rv.vote AS my, COALESCE(rv.overrule,false) AS overrule, NOT r.isfull AND rm.id IS NULL AS can
, ', sql_totime('r.date'), 'AS date, ', sql_user(), '
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 rm ON rm.vid = r.vid AND rm.uid =', \auth->uid, '
- WhERE r.vid =', \$v->{id}, 'AND', ($mini ? 'NOT' : ''), 'r.isfull
+ WhERE NOT r.c_flagged AND r.vid =', \$v->{id}, 'AND', ($mini ? 'NOT' : ''), 'r.isfull
ORDER BY r.c_up-r.c_down DESC'
);
@@ -55,7 +55,7 @@ sub reviews_ {
div_ sub {
a_ href => "/$r->{id}#review", 'Full review ยป' if !$mini;
a_ href => "/$r->{id}#threadstart", $r->{c_count} == 1 ? '1 comment' : "$r->{c_count} comments";
- elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, $r if auth && $r->{can};
+ elm_ 'Reviews.Vote' => $VNWeb::Reviews::Elm::VOTE_OUT, {%$r, mod => auth->permBoardmod} if auth && ($r->{can} || auth->permBoardmod);
};
} for @$lst;
}
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 683e3af0..a8e5ea80 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -390,7 +390,7 @@ sub infobox_ {
sub tabs_ {
my($v, $tab) = @_;
my $chars = tuwf->dbVali('SELECT COUNT(DISTINCT c.id) FROM chars c JOIN chars_vns cv ON cv.id = c.id WHERE NOT c.hidden AND cv.vid =', \$v->{id});
- my $reviews = tuwf->dbRowi('SELECT COUNT(*) FILTER(WHERE isfull) AS full, COUNT(*) FILTER(WHERE NOT isfull) AS mini FROM reviews WHERE vid =', \$v->{id});
+ my $reviews = tuwf->dbRowi('SELECT COUNT(*) FILTER(WHERE isfull) AS full, COUNT(*) FILTER(WHERE NOT isfull) AS mini FROM reviews WHERE NOT c_flagged AND vid =', \$v->{id});
return if !$chars && !$reviews->{full} && !$reviews->{mini} && !auth->permEdit && !auth->permReview;
$tab ||= '';
diff --git a/sql/func.sql b/sql/func.sql
index 215f87c1..59623f3b 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -179,11 +179,18 @@ END; $$ LANGUAGE plpgsql;
--- Update reviews.c_up and c_down.
+-- 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) 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)
+ WITH stats(id,up,down,flag) 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) < -10
FROM reviews r
LEFT JOIN reviews_votes rv ON rv.id = r.id
LEFT JOIN users u ON u.id = rv.uid
@@ -191,8 +198,8 @@ BEGIN
WHERE $1 IS NULL OR r.id = $1
GROUP BY r.id
)
- UPDATE reviews SET c_up = up, c_down = down
- FROM stats WHERE reviews.id = stats.id AND (reviews.c_up,reviews.c_down) <> (stats.up,stats.down);
+ 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);
END; $$ LANGUAGE plpgsql;
diff --git a/sql/schema.sql b/sql/schema.sql
index b195cacb..92f85edc 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -512,7 +512,8 @@ CREATE TABLE reviews (
c_down int NOT NULL DEFAULT 0,
c_count smallint NOT NULL DEFAULT 0,
c_lastnum smallint,
- isfull boolean NOT NULL
+ isfull boolean NOT NULL,
+ c_flagged boolean NOT NULL DEFAULT false
);
-- reviews_posts
@@ -532,7 +533,8 @@ CREATE TABLE reviews_votes (
id vndbid NOT NULL,
uid int,
date timestamptz NOT NULL,
- vote boolean NOT NULL -- true = upvote, false = downvote
+ vote boolean NOT NULL, -- true = upvote, false = downvote
+ overrule boolean NOT NULL DEFAULT false
);
-- rlists
diff --git a/util/updates/2020-09-03-reviews-flagging.sql b/util/updates/2020-09-03-reviews-flagging.sql
new file mode 100644
index 00000000..3cf8ee2c
--- /dev/null
+++ b/util/updates/2020-09-03-reviews-flagging.sql
@@ -0,0 +1,5 @@
+ALTER TABLE reviews ADD COLUMN c_flagged boolean NOT NULL DEFAULT false;
+ALTER TABLE reviews_votes ADD COLUMN overrule boolean NOT NULL DEFAULT false;
+
+\i sql/func.sql
+select update_reviews_votes_cache(null);