diff options
-rw-r--r-- | elm/Reviews/Vote.elm | 13 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Elm.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/List.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/Page.pm | 11 | ||||
-rw-r--r-- | lib/VNWeb/Reviews/VNTab.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 2 | ||||
-rw-r--r-- | sql/func.sql | 17 | ||||
-rw-r--r-- | sql/schema.sql | 6 | ||||
-rw-r--r-- | util/updates/2020-09-03-reviews-flagging.sql | 5 |
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); |