summaryrefslogtreecommitdiff
path: root/lib/VNWeb
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-09-26 09:22:11 +0200
committerYorhel <git@yorhel.nl>2021-09-26 09:22:11 +0200
commitcee8da8d20540393c1a4e9c92c08ddc288244afd (patch)
treef20cd046d02881c1ac97913e540c4cf720c3058e /lib/VNWeb
parente0db70fd64a0524eaba01666e59a0febd0cd9ee5 (diff)
Cache VN length + add to browser UI + reduce max length a bit
Diffstat (limited to 'lib/VNWeb')
-rw-r--r--lib/VNWeb/VN/Length.pm2
-rw-r--r--lib/VNWeb/VN/List.pm23
-rw-r--r--lib/VNWeb/VN/Page.pm16
3 files changed, 28 insertions, 13 deletions
diff --git a/lib/VNWeb/VN/Length.pm b/lib/VNWeb/VN/Length.pm
index 75c135b8..f1a2635e 100644
--- a/lib/VNWeb/VN/Length.pm
+++ b/lib/VNWeb/VN/Length.pm
@@ -186,7 +186,7 @@ our $LENGTHVOTE = form_compile any => {
vid => { vndbid => 'v' },
vote => { type => 'hash', required => 0, keys => {
rid => { type => 'array', minlength => 1, values => { vndbid => 'r' } },
- length => { uint => 1, range => [1,32767] },
+ length => { uint => 1, range => [1,26159] }, # 435h59m, largest round-ish number where the 'fast' speed adjustment doesn't overflow a smallint
speed => { uint => 1, enum => [0,1,2] },
notes => { required => 0, default => '' },
} },
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index 8bbfe858..4dcb1785 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -32,6 +32,10 @@ sub TABLEOPTS {
sort_id => 2,
sort_sql => 'v.c_released ?o, v.title',
},
+ length => {
+ name => 'Length',
+ vis_id => 4,
+ },
developer => {
name => 'Developer',
vis_id => 2,
@@ -75,6 +79,16 @@ sub listing_ {
paginate_ \&url, $opt->{p}, [$count, $opt->{s}->results], 't', sub { $opt->{s}->elm_ };
+ my sub len_ {
+ my($v) = @_;
+ if ($v->{c_lengthnum}) {
+ vnlength_ $v->{c_length};
+ b_ class => 'grayedout', " ($v->{c_lengthnum})";
+ } elsif($_->{length}) {
+ txt_ $VN_LENGTH{$v->{length}}{txt};
+ }
+ }
+
div_ class => 'mainbox browse vnbrowse', sub {
table_ class => 'stripe', sub {
thead_ sub { tr_ sub {
@@ -85,6 +99,7 @@ sub listing_ {
td_ class => 'tc_plat', '';
td_ class => 'tc_lang', '';
td_ class => 'tc_rel', sub { txt_ 'Released'; sortable_ 'released', $opt, \&url };
+ td_ class => 'tc_length',sub { txt_ 'Length'; } if $opt->{s}->vis('length');
td_ class => 'tc_pop', sub { txt_ 'Popularity'; sortable_ 'popularity', $opt, \&url } if $opt->{s}->vis('popularity');
td_ class => 'tc_rating',sub { txt_ 'Rating'; sortable_ 'rating', $opt, \&url } if $opt->{s}->vis('rating');
td_ class => 'tc_average',sub{ txt_ 'Average'; sortable_ 'average', $opt, \&url } if $opt->{s}->vis('average');
@@ -101,6 +116,7 @@ sub listing_ {
td_ class => 'tc_plat', sub { join_ '', sub { platform_ $_ if $_ ne 'unk' }, sort $_->{platforms}->@* };
td_ class => 'tc_lang', sub { join_ '', sub { abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' }, reverse sort $_->{lang}->@* };
td_ class => 'tc_rel', sub { rdate_ $_->{c_released} };
+ td_ class => 'tc_length',sub { len_ $_ } if $opt->{s}->vis('length');
td_ class => 'tc_pop', sprintf '%.2f', ($_->{c_popularity}||0)/100 if $opt->{s}->vis('popularity');
td_ class => 'tc_rating',sub {
txt_ sprintf '%.2f', ($_->{c_rating}||0)/100;
@@ -139,6 +155,10 @@ sub listing_ {
td_ sub { tagscore_ $_->{tagscore} };
} if $tagscore;
tr_ sub {
+ td_ 'Length';
+ td_ sub { len_ $_ };
+ } if $opt->{s}->vis('length');
+ tr_ sub {
td_ 'Popularity:';
td_ sprintf '%.2f', ($_->{c_popularity}||0)/100;
} if $opt->{s}->vis('popularity');
@@ -253,7 +273,8 @@ TUWF::get qr{/v(?:/(?<char>all|[a-z0]))?}, sub {
$count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where);
$list = $count ? tuwf->dbPagei({results => $opt->{s}->results(), page => $opt->{p}}, '
SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_average
- , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
+ , v.image, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang',
+ $opt->{s}->vis('length') ? ', v.length, v.c_length, v.c_lengthnum' : (), '
FROM vn v
WHERE', $where, '
ORDER BY', $opt->{s}->sql_order(),
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index 60876e1e..31a49c6c 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -11,7 +11,7 @@ use VNDB::Func 'fmtrating';
# Also used by Chars::VNTab & Reviews::VNTab
sub enrich_vn {
my($v, $revonly) = @_;
- enrich_merge id => 'SELECT id, c_votecount FROM vn WHERE id IN', $v;
+ enrich_merge id => 'SELECT id, c_votecount, c_length, c_lengthnum FROM vn WHERE id IN', $v;
enrich_merge vid => 'SELECT id AS vid, title, original, c_released FROM vn WHERE id IN', $v->{relations};
enrich_merge aid => 'SELECT id AS aid, title_romaji, title_kanji, year, type, ann_id, lastfetch FROM anime WHERE id IN', $v->{anime};
enrich_extlinks v => $v;
@@ -159,13 +159,7 @@ sub infobox_length_ {
my $today = strftime('%Y%m%d', gmtime);
return if !grep $_->{type} ne 'trial' && $_->{released} <= $today, $v->{releases}->@*;
- my $stats = tuwf->dbRowi('
- SELECT count(*) as count
- , percentile_cont(', \0.5, ') WITHIN GROUP (ORDER BY l.length + (l.length/(1+1+1+1) * (l.speed-1))) 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 NOT l.ignore AND l.vid =', \$v->{id});
- return if !$v->{length} && !$stats->{count} && !VNWeb::VN::Length::can_vote();
+ 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);
@@ -173,8 +167,8 @@ sub infobox_length_ {
tr_ sub {
td_ 'Play time';
td_ sub {
- if($stats->{count}) {
- my $m = $stats->{median};
+ if($v->{c_lengthnum}) {
+ my $m = $v->{c_length};
my $len = $m < 2*60 ? 1
: $m < 10*60 ? 2
: $m < 30*60 ? 3
@@ -182,7 +176,7 @@ sub infobox_length_ {
txt_ $VN_LENGTH{$len}{txt}.' (';
vnlength_ $m;
txt_ ' from ';
- a_ href => "/$v->{id}/lengthvotes", sprintf '%d vote%s', $stats->{count}, $stats->{count}==1?'':'s';
+ a_ href => "/$v->{id}/lengthvotes", sprintf '%d vote%s', $v->{c_lengthnum}, $v->{c_length}==1?'':'s';
txt_ ')';
} elsif($v->{length}) {
txt_ "$VN_LENGTH{$v->{length}}{txt} ($VN_LENGTH{$v->{length}}{time})";