diff options
-rw-r--r-- | elm/VNLengthVote.elm | 18 | ||||
-rw-r--r-- | lib/VNWeb/User/Page.pm | 4 | ||||
-rw-r--r-- | lib/VNWeb/VN/Length.pm | 19 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 2 | ||||
-rw-r--r-- | sql/func.sql | 2 | ||||
-rw-r--r-- | sql/schema.sql | 3 | ||||
-rwxr-xr-x | util/dbdump.pl | 4 | ||||
-rwxr-xr-x | util/devdump.pl | 2 | ||||
-rw-r--r-- | util/updates/2022-03-29-lengthvotes-private.sql | 3 |
9 files changed, 39 insertions, 18 deletions
diff --git a/elm/VNLengthVote.elm b/elm/VNLengthVote.elm index 8ee576d2..3d2354a8 100644 --- a/elm/VNLengthVote.elm +++ b/elm/VNLengthVote.elm @@ -51,7 +51,7 @@ init f = , defrid = "" , hours = Maybe.map (\v -> v.length // 60 ) f.vote , minutes = Maybe.andThen (\v -> let n = modBy 60 v.length in if n == 0 then Nothing else Just n) f.vote - , speed = Maybe.map (\v -> v.speed) f.vote |> Maybe.withDefault (Just 9) + , speed = Maybe.map (\v -> if v.private then Just 8 else v.speed) f.vote |> Maybe.withDefault (Just 9) , length = Maybe.map (\v -> v.length) f.vote |> Maybe.withDefault 0 , notes = Maybe.map (\v -> v.notes) f.vote |> Maybe.withDefault "" , rels = Nothing @@ -64,7 +64,13 @@ encode : Model -> GV.Send encode m = { uid = m.uid , vid = m.vid - , vote = if enclen m == 0 then Nothing else Just { rid = m.rid, notes = m.notes, speed = m.speed, length = enclen m } + , vote = if enclen m == 0 then Nothing else Just + { rid = m.rid + , notes = m.notes + , speed = if m.speed == Just 8 then Nothing else m.speed + , length = enclen m + , private = m.speed == Just 8 + } } type Msg @@ -151,10 +157,16 @@ view model = div [class "lengthvotefrm"] <| , (Just 0, "Slow (e.g. low language proficiency or extra time spent on gameplay)") , (Just 1, "Normal (no content skipped, all voices listened to end)") , (Just 2, "Fast (e.g. fast reader or skipping through voices and gameplay)") - , (Nothing, "Don't count my play time") + , (Nothing, "Don't count my play time (public)") + , (Just 8, "Don't count my play time (private)") ] , case model.speed of Just 9 -> span [] [] + Just 8 -> span [] + [ text "Your play time is not counted towards the VN's average and is not visible in the listings." + , text " It is only saved for your own administration and counted towards the personal play time displayed on your profile." + , br [] [] + ] Nothing -> span [] [ text "Your play time is not counted towards the VN's average, but is still visible in the listings and saved for your own administration." , br [] [] diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm index 43d4dd8e..7d418a27 100644 --- a/lib/VNWeb/User/Page.pm +++ b/lib/VNWeb/User/Page.pm @@ -53,13 +53,13 @@ sub _info_table_ { a_ href => "/$u->{id}/ulist?votes=1", 'Browse votes »'; } }; - my $lengthvotes = tuwf->dbRowi('SELECT count(*) AS count, sum(length) AS sum FROM vn_length_votes WHERE uid =', \$u->{id}); + my $lengthvotes = tuwf->dbRowi('SELECT count(*) AS count, sum(length) AS sum, bool_or(not private) as haspub FROM vn_length_votes WHERE uid =', \$u->{id}); tr_ sub { td_ 'Play times'; td_ sub { vnlength_ $lengthvotes->{sum}; txt_ sprintf ' from %d submitted play times. ', $lengthvotes->{count}; - a_ href => "/$u->{id}/lengthvotes", 'Browse votes »'; + a_ href => "/$u->{id}/lengthvotes", 'Browse votes »' if $own || $lengthvotes->{haspub}; }; } if $lengthvotes->{count}; tr_ sub { diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm index 362ae2b5..bf39018e 100644 --- a/lib/VNWeb/VN/Length.pm +++ b/lib/VNWeb/VN/Length.pm @@ -55,7 +55,10 @@ sub listing_ { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' for sort keys %l; join_ ',', sub { a_ href => "/$_->{id}", $_->{id} }, sort { idcmp $a->{id}, $b->{id} } $_->{rel}->@*; }; - td_ class => 'tc6'.($_->{ignore}?' grayedout':''), sub { lit_ bb_format $_->{notes}, inline => 1 }; + td_ class => 'tc6'.($_->{ignore}?' grayedout':''), sub { + b_ class => 'grayedout', '(private) ' if $_->{private}; + lit_ bb_format $_->{notes}, inline => 1; + }; td_ class => 'tc7', sub { select_ name => "lv$_->{id}", sub { option_ value => '', '--'; @@ -82,7 +85,7 @@ sub stats_ { , percentile_cont(', \0.5, ') WITHIN GROUP (ORDER BY l.length) AS median FROM vn_length_votes l LEFT JOIN users u ON u.id = l.uid - WHERE u.perm_lengthvote IS DISTINCT FROM false AND l.speed IS NOT NULL AND l.vid =', \$o->{id}, ' + WHERE u.perm_lengthvote IS DISTINCT FROM false AND l.speed IS NOT NULL AND NOT l.private AND l.vid =', \$o->{id}, ' GROUP BY GROUPING SETS ((speed),()) ORDER BY speed' ); return if !$stats->[0]{count}; @@ -122,11 +125,12 @@ TUWF::get qr{/(?:(?<thing>$RE{vid}|$RE{uid})/)?lengthvotes}, sub { my $where = sql_and $mode ? sql($mode eq 'v' ? 'l.vid =' : 'l.uid =', \$o->{id}) : (), + $mode eq 'u' && auth && $o->{id} eq auth->uid ? () : 'NOT l.private', defined $opt->{ign} ? sql('l.speed IS', $opt->{ign} ? 'NULL' : 'NOT NULL') : (); my $count = tuwf->dbVali('SELECT COUNT(*) FROM vn_length_votes l WHERE', $where); my $lst = tuwf->dbPagei({results => $opt->{s}->results, page => $opt->{p}}, - 'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.rid::text[] AS rel, ' + 'SELECT l.id, l.uid, l.vid, l.length, l.speed, l.notes, l.private, l.rid::text[] AS rel, ' , sql_totime('l.date'), 'AS date, u.perm_lengthvote IS NOT DISTINCT FROM false AS ignore', $mode ne 'u' ? (', ', sql_user()) : (), $mode ne 'v' ? ', v.title, v.alttitle' : (), ' @@ -184,10 +188,11 @@ our $LENGTHVOTE = form_compile any => { uid => { vndbid => 'u' }, vid => { vndbid => 'v' }, vote => { type => 'hash', required => 0, keys => { - rid => { type => 'array', minlength => 1, values => { vndbid => 'r' } }, - length => { uint => 1, range => [1,26159] }, # 435h59m, largest round-ish number where the 'fast' speed adjustment doesn't overflow a smallint - speed => { required => 0, uint => 1, enum => [0,1,2] }, - notes => { required => 0, default => '' }, + rid => { type => 'array', minlength => 1, values => { vndbid => 'r' } }, + length => { uint => 1, range => [1,26159] }, # 435h59m, largest round-ish number where the 'fast' speed adjustment doesn't overflow a smallint + speed => { required => 0, uint => 1, enum => [0,1,2] }, + private => { anybool => 1 }, + notes => { required => 0, default => '' }, } }, }; diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm index 9b3f8137..ee53b079 100644 --- a/lib/VNWeb/VN/Page.pm +++ b/lib/VNWeb/VN/Page.pm @@ -167,7 +167,7 @@ sub infobox_length_ { return if !$v->{c_length} && !$v->{c_lengthnum} && !VNWeb::VN::Length::can_vote(); my $my = VNWeb::VN::Length::can_vote() - && tuwf->dbRowi('SELECT rid::text[] AS rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid); + && tuwf->dbRowi('SELECT rid::text[] AS rid, length, speed, private, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid); tr_ sub { td_ 'Play time'; diff --git a/sql/func.sql b/sql/func.sql index 53d8ce8f..8f9a1c1b 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -239,7 +239,7 @@ CREATE OR REPLACE FUNCTION update_vn_length_cache(vndbid) RETURNS void AS $$ SELECT v.id, count(l.vid) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL) , percentile_cont(0.5) WITHIN GROUP (ORDER BY l.length + (l.length/4 * (l.speed-1))) FILTER (WHERE u.id IS NOT NULL AND l.vid IS NOT NULL) FROM vn v - LEFT JOIN vn_length_votes l ON l.vid = v.id AND l.speed IS NOT NULL + LEFT JOIN vn_length_votes l ON l.vid = v.id AND l.speed IS NOT NULL AND NOT l.private LEFT JOIN users u ON u.id = l.uid AND u.perm_lengthvote WHERE ($1 IS NULL OR v.id = $1) GROUP BY v.id diff --git a/sql/schema.sql b/sql/schema.sql index c6019b72..5719d6e7 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -1206,7 +1206,8 @@ CREATE TABLE vn_length_votes ( speed smallint, -- [pub] NULL=uncounted/ignored, 0=slow, 1=normal, 2=fast uid vndbid, -- [pub] rid vndbid[] NOT NULL, -- [pub] - notes text NOT NULL DEFAULT '' -- [pub] + notes text NOT NULL DEFAULT '', -- [pub] + private boolean NOT NULL ); -- wikidata diff --git a/util/dbdump.pl b/util/dbdump.pl index 5488d6e4..5adf35de 100755 --- a/util/dbdump.pl +++ b/util/dbdump.pl @@ -98,7 +98,7 @@ my %tables = ( users => { where => 'id IN(SELECT DISTINCT uvl.uid FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE NOT ul.private)' .' OR id IN(SELECT DISTINCT uid FROM tags_vn)' .' OR id IN(SELECT DISTINCT uid FROM image_votes)' - .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes)' }, + .' OR id IN(SELECT DISTINCT uid FROM vn_length_votes WHERE NOT private)' }, vn => { where => 'NOT hidden' }, vn_anime => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, vn_relations => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, @@ -108,7 +108,7 @@ my %tables = ( .' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' }, vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' }, vn_titles => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' }, - vn_length_votes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)' + vn_length_votes => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden) AND NOT private' , order => 'vid, uid' }, wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden UNION SELECT l_wikidata FROM staff WHERE NOT hidden diff --git a/util/devdump.pl b/util/devdump.pl index 9d801912..a36d0184 100755 --- a/util/devdump.pl +++ b/util/devdump.pl @@ -160,7 +160,7 @@ sub copy_entry { copy_entry [qw/vn vn_anime vn_seiyuu vn_staff vn_relations vn_screenshots vn_titles/], \@vids; # VN-related niceties - copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE vid IN($vids)", {uid => 'user'}; + copy vn_length_votes => "SELECT DISTINCT ON (vid,vndbid_num(uid)%10) * FROM vn_length_votes WHERE NOT private AND vid IN($vids)", {uid => 'user'}; copy tags_vn => "SELECT DISTINCT ON (tag,vid,vndbid_num(uid)%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'}; copy quotes => "SELECT * FROM quotes WHERE vid IN($vids)"; my $votes = "SELECT vid, vndbid('u', vndbid_num(uid)%8+2) AS uid, (percentile_cont((vndbid_num(uid)%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(vote_date) AS vote_date" diff --git a/util/updates/2022-03-29-lengthvotes-private.sql b/util/updates/2022-03-29-lengthvotes-private.sql new file mode 100644 index 00000000..5c721818 --- /dev/null +++ b/util/updates/2022-03-29-lengthvotes-private.sql @@ -0,0 +1,3 @@ +ALTER TABLE vn_length_votes ADD COLUMN private boolean NOT NULL DEFAULT FALSE; +ALTER TABLE vn_length_votes ALTER COLUMN private DROP DEFAULT; +\i sql/func.sql |