diff options
author | Yorhel <git@yorhel.nl> | 2021-08-04 08:47:52 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-08-04 08:47:52 +0200 |
commit | fd8c51b3e47a97ffb06d66140f2458b565c37fe9 (patch) | |
tree | 3084ae08aeac2953181464cd8f863d7bf70ee7da | |
parent | 6baa5582a2ff9015f3bd672e6c62570af6517903 (diff) |
VNLengthVote: Misc code reorg + basic user stats
-rw-r--r-- | lib/VNWeb/User/Page.pm | 9 | ||||
-rw-r--r-- | lib/VNWeb/VN/Elm.pm | 24 | ||||
-rw-r--r-- | lib/VNWeb/VN/Length.pm | 27 | ||||
-rw-r--r-- | lib/VNWeb/VN/Page.pm | 6 | ||||
-rw-r--r-- | sql/tableattrs.sql | 1 | ||||
-rw-r--r-- | util/updates/2021-08-04-vnlength-index.sql | 2 |
6 files changed, 42 insertions, 27 deletions
diff --git a/lib/VNWeb/User/Page.pm b/lib/VNWeb/User/Page.pm index b110a444..ff4361b0 100644 --- a/lib/VNWeb/User/Page.pm +++ b/lib/VNWeb/User/Page.pm @@ -47,6 +47,15 @@ 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}); + 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 »'; + }; + } if $lengthvotes->{count}; tr_ sub { my $vns = tuwf->dbVali( 'SELECT COUNT(DISTINCT uvl.vid) FROM ulist_vns_labels uvl', diff --git a/lib/VNWeb/VN/Elm.pm b/lib/VNWeb/VN/Elm.pm index 5a901e9a..3bf02d59 100644 --- a/lib/VNWeb/VN/Elm.pm +++ b/lib/VNWeb/VN/Elm.pm @@ -30,28 +30,4 @@ elm_api VN => undef, { '); }; - -our $LENGTHVOTE = form_compile any => { - uid => { vndbid => 'u' }, - vid => { vndbid => 'v' }, - vote => { type => 'hash', required => 0, keys => { - rid => { vndbid => 'r' }, - length => { uint => 1, range => [1,32767] }, - speed => { uint => 1, enum => [0,1,2] }, - notes => { required => 0, default => '' }, - } }, -}; - -elm_api VNLengthVote => undef, $LENGTHVOTE, sub { - my($data) = @_; - return elm_Unauth if !auth->permLengthvote || $data->{uid} ne auth->uid; - my %where = ( uid => $data->{uid}, vid => $data->{vid} ); - tuwf->dbExeci('DELETE FROM vn_length_votes WHERE', \%where) if !$data->{vote}; - tuwf->dbExeci( - 'INSERT INTO vn_length_votes', { %where, $data->{vote}->%* }, - 'ON CONFLICT (uid, vid) DO UPDATE SET', $data->{vote} - ) if $data->{vote}; - return elm_Success; -}; - 1; diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm index b5d8d236..05ce509f 100644 --- a/lib/VNWeb/VN/Length.pm +++ b/lib/VNWeb/VN/Length.pm @@ -2,6 +2,9 @@ package VNWeb::VN::Length; use VNWeb::Prelude; +# Also used from VN::Page +sub can_vote { auth->permDbmod || (auth->permLengthvote && !global_settings->{lockdown_edit}) } + sub opts { my($vn) = @_; tableopts @@ -87,4 +90,28 @@ TUWF::get qr{/(?<thing>$RE{vid}|$RE{uid})/lengthvotes}, sub { }; }; + +our $LENGTHVOTE = form_compile any => { + uid => { vndbid => 'u' }, + vid => { vndbid => 'v' }, + vote => { type => 'hash', required => 0, keys => { + rid => { vndbid => 'r' }, + length => { uint => 1, range => [1,32767] }, + speed => { uint => 1, enum => [0,1,2] }, + notes => { required => 0, default => '' }, + } }, +}; + +elm_api VNLengthVote => undef, $LENGTHVOTE, sub { + my($data) = @_; + return elm_Unauth if !can_vote() || $data->{uid} ne auth->uid; + my %where = ( uid => $data->{uid}, vid => $data->{vid} ); + tuwf->dbExeci('DELETE FROM vn_length_votes WHERE', \%where) if !$data->{vote}; + tuwf->dbExeci( + 'INSERT INTO vn_length_votes', { %where, $data->{vote}->%* }, + 'ON CONFLICT (uid, vid) DO UPDATE SET', $data->{vote} + ) if $data->{vote}; + return elm_Success; +}; + 1; diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm index f09634af..1bfd6fa8 100644 --- a/lib/VNWeb/VN/Page.pm +++ b/lib/VNWeb/VN/Page.pm @@ -166,10 +166,10 @@ sub infobox_length_ { WHERE u.perm_lengthvote IS DISTINCT FROM false AND l.vid =', \$v->{id}); return if !$v->{length} && !$stats->{count} && !auth->permLengthvote; - my $my = auth->permLengthvote && tuwf->dbRowi('SELECT rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid); + my $my = VNWeb::VN::Length::can_vote() && tuwf->dbRowi('SELECT rid, length, speed, notes FROM vn_length_votes WHERE vid =', \$v->{id}, 'AND uid =', \auth->uid); tr_ sub { - td_ 'Length'; + td_ 'Play time'; td_ sub { if($stats->{count}) { vnlength_ $stats->{avg}; @@ -186,7 +186,7 @@ sub infobox_length_ { txt_ 'Unknown'; } if (auth->permLengthvote) { - elm_ VNLengthVote => $VNWeb::VN::Elm::LENGTHVOTE, { + elm_ VNLengthVote => $VNWeb::VN::Length::LENGTHVOTE, { uid => auth->uid, vid => $v->{id}, vote => $my->{rid}?$my:undef, }, sub { span_ @_, ''}; diff --git a/sql/tableattrs.sql b/sql/tableattrs.sql index 3842057c..77b5be07 100644 --- a/sql/tableattrs.sql +++ b/sql/tableattrs.sql @@ -156,6 +156,7 @@ CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on / CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+? CREATE INDEX vn_staff_aid ON vn_staff (aid); CREATE UNIQUE INDEX vn_length_votes_pkey ON vn_length_votes (vid, uid); +CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid); CREATE UNIQUE INDEX changes_itemrev ON changes (itemid, rev); CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 'v1')); -- 'v1' is an invalid release id, but works as a 'no release specified' value in the UNIQUE qualifier. CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 'v1')); diff --git a/util/updates/2021-08-04-vnlength-index.sql b/util/updates/2021-08-04-vnlength-index.sql new file mode 100644 index 00000000..f9e93d01 --- /dev/null +++ b/util/updates/2021-08-04-vnlength-index.sql @@ -0,0 +1,2 @@ +ALTER TABLE users ALTER COLUMN perm_lengthvote SET DEFAULT true; +CREATE INDEX vn_length_votes_uid ON vn_length_votes (uid); |