summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2021-01-06 15:51:52 +0100
committerYorhel <git@yorhel.nl>2021-01-06 16:01:46 +0100
commit38c25d78f5705312d7a6e1815d5eec251284e675 (patch)
treecf577796753c39d42daff5608d55e6fb59b3441b
parentd87dca35890706ee156645cb9979caa7c3a357f8 (diff)
Releases::List: Add experimental new release browser with advanced search
Uses the same release_row_() function used on VN and producer pages, so this adds a lot more information and user list management functionality to the release browser. Removed compat with *very* old URLs, I'm monitoring the logs to see if those are still used. Also removes a friendly message when the filters don't match anything. Main reason for implementing this before going live with the VN advanced search is to make it possible to also switch the default saved release filters to the new system in one go.
-rw-r--r--lib/VNWeb/DB.pm21
-rw-r--r--lib/VNWeb/HTML.pm16
-rw-r--r--lib/VNWeb/Producers/Page.pm3
-rw-r--r--lib/VNWeb/Releases/Lib.pm12
-rw-r--r--lib/VNWeb/Releases/List.pm107
-rw-r--r--lib/VNWeb/VN/List.pm29
-rw-r--r--lib/VNWeb/VN/Page.pm3
7 files changed, 162 insertions, 29 deletions
diff --git a/lib/VNWeb/DB.pm b/lib/VNWeb/DB.pm
index d3128b1c..ecfdae8b 100644
--- a/lib/VNWeb/DB.pm
+++ b/lib/VNWeb/DB.pm
@@ -12,7 +12,7 @@ our @EXPORT = qw/
sql
sql_identifier sql_join sql_comma sql_and sql_or sql_array sql_func sql_fromhex sql_tohex sql_fromtime sql_totime sql_like sql_user
enrich enrich_merge enrich_flatten enrich_obj
- db_entry db_edit
+ db_maytimeout db_entry db_edit
/;
@@ -220,6 +220,25 @@ sub enrich_obj {
+# Run the given subroutine inside a savepoint and capture an SQL timeout.
+# Returns false and logs a warning on timeout.
+sub db_maytimeout(&) {
+ my($f) = @_;
+ tuwf->dbh->pg_savepoint('maytimeout');
+ my $r = eval { $f->(); 1 };
+
+ if(!$r && $@ =~ /canceling statement due to statement timeout/) {
+ tuwf->dbh->pg_rollback_to('maytimeout');
+ warn "Query timed out\n";
+ return 0;
+ }
+ carp $@ if !$r;
+ tuwf->dbh->pg_release('maytimeout');
+ 1;
+}
+
+
+
# Database entry API: Intended to provide a low-level read/write interface for
# versioned database entires. The same data structure is used for reading and
# updating entries, and should support easy diffing/comparison.
diff --git a/lib/VNWeb/HTML.pm b/lib/VNWeb/HTML.pm
index 10aac6f7..cee47b7a 100644
--- a/lib/VNWeb/HTML.pm
+++ b/lib/VNWeb/HTML.pm
@@ -34,6 +34,7 @@ our @EXPORT = qw/
searchbox_
itemmsg_
editmsg_
+ advsearch_msg_
/;
@@ -899,4 +900,19 @@ sub editmsg_ {
}
}
+
+# Display the number of results and time it took. If the query timed out ($count is undef), an error message is displayed instead.
+sub advsearch_msg_ {
+ my($count, $time) = @_;
+ p_ class => 'center', sprintf '%d results in %.3fs', $count, $time if defined $count;
+ div_ class => 'warning', sub {
+ h2_ 'ERROR: Query timed out.';
+ p_ q{
+ This usually happens when your combination of filters is too complex for the server to handle.
+ This may also happen when the server is overloaded with other work, but that's much less common.
+ You can adjust your filters or try again later.
+ };
+ } if !defined $count;
+}
+
1;
diff --git a/lib/VNWeb/Producers/Page.pm b/lib/VNWeb/Producers/Page.pm
index eac6f3e4..ee494518 100644
--- a/lib/VNWeb/Producers/Page.pm
+++ b/lib/VNWeb/Producers/Page.pm
@@ -100,7 +100,8 @@ sub rel_ {
td_ colspan => 8, sub {
a_ href => "/v$v->{id}", title => $v->{original}||$v->{title}, $v->{title};
};
- release_row_ $_, $v->{id}, 1 for $vn{$v->{id}}->@*;
+ my $ropt = { id => $v->{id}, prod => 1, lang => 1 };
+ release_row_ $_, $ropt for $vn{$v->{id}}->@*;
};
}
} if @$r;
diff --git a/lib/VNWeb/Releases/Lib.pm b/lib/VNWeb/Releases/Lib.pm
index 207faba8..07bb6878 100644
--- a/lib/VNWeb/Releases/Lib.pm
+++ b/lib/VNWeb/Releases/Lib.pm
@@ -68,8 +68,12 @@ sub release_extlinks_ {
}
+# Options
+# id: unique identifier if the same release may be listed on a page twice.
+# lang: 0/1 whether to display language icons
+# prod: 0/1 whether to display Pub/Dev indication
sub release_row_ {
- my($r, $id, $prodpage) = @_;
+ my($r, $opt) = @_;
my sub icon_ {
my($img, $label, $class) = @_;
@@ -103,7 +107,7 @@ sub release_row_ {
td_ class => 'tc2', $r->{minage} < 0 ? '' : minage $r->{minage};
td_ class => 'tc3', sub {
abbr_ class => "icons $_", title => $PLATFORM{$_}, '' for grep $_ ne 'oth', $r->{platforms}->@*;
- if($prodpage) {
+ if($opt->{lang}) {
abbr_ class => "icons lang $_", title => $LANGUAGE{$_}, '' for $r->{lang}->@*;
}
abbr_ class => "icons rt$r->{type}", title => $r->{type}, '';
@@ -114,11 +118,11 @@ sub release_row_ {
b_ class => 'grayedout', " ($note)" if $note;
};
td_ class => 'tc_icons', sub { icons_ $r };
- td_ class => 'tc_prod', join ' & ', $r->{publisher} ? 'Pub' : (), $r->{developer} ? 'Dev' : () if $prodpage;
+ td_ class => 'tc_prod', join ' & ', $r->{publisher} ? 'Pub' : (), $r->{developer} ? 'Dev' : () if $opt->{prod};
td_ class => 'tc5 elm_dd_left', sub {
elm_ 'UList.ReleaseEdit', $VNWeb::ULists::Elm::RLIST_STATUS, { rid => $r->{id}, uid => auth->uid, status => $r->{rlist_status}, empty => '--' } if auth;
};
- td_ class => 'tc6', sub { release_extlinks_ $r, "${id}_$r->{id}" };
+ td_ class => 'tc6', sub { release_extlinks_ $r, "$opt->{id}_$r->{id}" };
}
}
diff --git a/lib/VNWeb/Releases/List.pm b/lib/VNWeb/Releases/List.pm
new file mode 100644
index 00000000..89332200
--- /dev/null
+++ b/lib/VNWeb/Releases/List.pm
@@ -0,0 +1,107 @@
+package VNWeb::Releases::List;
+
+use VNDB::Func 'gtintype';
+use VNWeb::Prelude;
+use VNWeb::AdvSearch;
+use VNWeb::Filters;
+use VNWeb::Releases::Lib;
+
+
+sub listing_ {
+ my($opt, $list, $count) = @_;
+ my sub url { '?'.query_encode %$opt, @_ }
+ paginate_ \&url, $opt->{p}, [$count, 50], 't';
+ div_ class => 'mainbox browse', sub {
+ table_ class => 'stripe releases', sub {
+ thead_ sub { tr_ sub {
+ td_ class => 'tc1', sub { txt_ 'Date'; sortable_ 'released',$opt, \&url; debug_ $list; };
+ td_ class => 'tc2', sub { txt_ 'Rating'; sortable_ 'minage', $opt, \&url };
+ td_ class => 'tc3', '';
+ td_ class => 'tc4', sub { txt_ 'Title'; sortable_ 'title', $opt, \&url };
+ td_ class => 'tc_icons', '';
+ td_ class => 'tc5', '';
+ td_ class => 'tc6', '';
+ } };
+ my $ropt = { id => '', lang => 1 };
+ release_row_ $_, $ropt for @$list;
+ }
+ };
+ paginate_ \&url, $opt->{p}, [$count, 50], 'b';
+}
+
+
+TUWF::get qr{/experimental/r}, sub {
+ my $opt = tuwf->validate(get =>
+ q => { onerror => undef },
+ p => { upage => 1 },
+ f => { advsearch => 'r' },
+ s => { onerror => 'title', enum => [qw/released minage title/] },
+ o => { onerror => 'a', enum => ['a','d'] },
+ fil => { required => 0 },
+ )->data;
+
+ # URL compatibility with old filters
+ if(!$opt->{f}->{query} && $opt->{fil}) {
+ my $q = eval {
+ tuwf->compile({ advsearch => 'r' })->validate(filter_release_adv filter_parse r => $opt->{fil})->data;
+ };
+ if(!$q) {
+ warn "Filter compatibility conversion failed\n$@";
+ } else {
+ return tuwf->resRedirect(tuwf->reqPath().'?'.query_encode(%$opt, fil => undef, f => $q), 'temp');
+ }
+ }
+
+ if(auth && !$opt->{f}{query} && !defined tuwf->reqGet('f')) {
+ my $def = tuwf->dbVali('SELECT query FROM saved_queries WHERE qtype = \'r\' AND name = \'\' AND uid =', \auth->uid);
+ $opt->{f} = tuwf->compile({ advsearch => 'r' })->validate($def)->data if $def;
+ }
+
+ my @search = map {
+ my $l = '%'.sql_like($_).'%';
+ /^\d+$/ && gtintype($_) ? sql 'r.gtin =', \"$_" :
+ length $_ > 0 ? sql '(r.title ILIKE', \$l, 'OR r.original ILIKE', \$l, 'OR r.catalog =', \"$_", ')' : ();
+ } split /[ -,._]/, $opt->{q}||'';
+ my $where = sql_and 'NOT r.hidden', $opt->{f}->sql_where(), @search;
+
+ my $time = time;
+ my($count, $list);
+ db_maytimeout {
+ $count = tuwf->dbVali('SELECT count(*) FROM releases r WHERE', $where);
+ $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, '
+ SELECT r.id, r.type, r.patch, r.released, r.gtin, ', sql_extlinks(r => 'r.'), '
+ FROM releases r
+ WHERE', $where, '
+ ORDER BY', sprintf {
+ title => 'r.title %s, r.released %1$s',
+ minage => 'r.minage %s, r.title %1$s, r.released %1$s',
+ released => 'r.released %s, r.id %1$s',
+ }->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC'
+ ) : [];
+ } || (($count, $list) = (undef, []));
+
+ enrich_extlinks r => $list;
+ enrich_release $list;
+ $time = time - $time;
+
+ framework_ title => 'Browse releases', sub {
+ div_ class => 'mainbox', sub {
+ h1_ 'Browse releases';
+ div_ class => 'warning', sub {
+ h2_ 'EXPERIMENTAL';
+ p_ "This is Yorhel's playground. Lots of functionality is missing, lots of stuff is or will be broken. Here be dragons. Etc.";
+ };
+ br_;
+ form_ action => '/experimental/r', method => 'get', sub {
+ searchbox_ r => $opt->{q}//'';
+ input_ type => 'hidden', name => 'o', value => $opt->{o};
+ input_ type => 'hidden', name => 's', value => $opt->{s};
+ $opt->{f}->elm_;
+ advsearch_msg_ $count, $time;
+ };
+ };
+ listing_ $opt, $list, $count if $count;
+ };
+};
+
+1;
diff --git a/lib/VNWeb/VN/List.pm b/lib/VNWeb/VN/List.pm
index cb05e27f..712a222c 100644
--- a/lib/VNWeb/VN/List.pm
+++ b/lib/VNWeb/VN/List.pm
@@ -97,10 +97,9 @@ TUWF::get qr{/experimental/v(?:/(?<char>all|[a-z0]))?}, sub {
my $time = time;
my($count, $list);
- tuwf->dbh->pg_savepoint('filter'); # Savepoint to reset our transaction in case the query timed out.
- eval {
+ db_maytimeout {
$count = tuwf->dbVali('SELECT count(*) FROM vn v WHERE', $where);
- $list = $count && tuwf->dbPagei({results => 50, page => $opt->{p}}, '
+ $list = $count ? tuwf->dbPagei({results => 50, page => $opt->{p}}, '
SELECT v.id, v.title, v.original, v.c_released, v.c_popularity, v.c_votecount, v.c_rating, v.c_platforms::text[] AS platforms, v.c_languages::text[] AS lang
, vl.userlist_all, vl.userlist_obtained
FROM vn v
@@ -119,16 +118,10 @@ TUWF::get qr{/experimental/v(?:/(?<char>all|[a-z0]))?}, sub {
pop => 'v.c_popularity %s NULLS LAST, v.title',
rating => 'v.c_rating %s NULLS LAST, v.title'
}->{$opt->{s}}, $opt->{o} eq 'a' ? 'ASC' : 'DESC'
- );
- };
- tuwf->dbh->pg_rollback_to('filter');
- if(!defined $list && $@ =~ /canceling statement due to statement timeout/) {
- warn "Query timed out\n";
- ($count, $list) = (undef, []);
- }
- die $@ if !defined $list;
+ ) : [];
+ } || (($count, $list) = (undef, []));
- return tuwf->resRedirect("/v$list->[0]{id}") if $count == 1 && $opt->{q} && !defined $opt->{ch};
+ return tuwf->resRedirect("/v$list->[0]{id}") if $count && $count == 1 && $opt->{q} && !defined $opt->{ch};
enrich_flatten vnlist_labels => id => vid => sub { sql '
SELECT uvl.vid, ul.label
@@ -136,7 +129,7 @@ TUWF::get qr{/experimental/v(?:/(?<char>all|[a-z0]))?}, sub {
JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl
WHERE uvl.uid =', \auth->uid, 'AND uvl.vid IN', $_[0], '
ORDER BY CASE WHEN ul.id < 10 THEN ul.id ELSE 10 END, ul.label'
- }, $list if $count && auth;
+ }, $list if auth;
$time = time - $time;
framework_ title => 'Browse visual novels', sub {
@@ -157,16 +150,8 @@ TUWF::get qr{/experimental/v(?:/(?<char>all|[a-z0]))?}, sub {
input_ type => 'hidden', name => 's', value => $opt->{s};
input_ type => 'hidden', name => 'ch', value => $opt->{ch}//'';
$opt->{f}->elm_;
+ advsearch_msg_ $count, $time;
};
- p_ class => 'center', sprintf '%d results in %.3fs', $count, $time if defined $count;
- div_ class => 'warning', sub {
- h2_ 'ERROR: Query timed out.';
- p_ q{
- This usually happens when your combination of filters is too complex for the server to handle.
- This may also happen when the server is overloaded with other work, but that's much less common.
- You can adjust your filters or try again later.
- };
- } if !defined $count;
};
listing_ $opt, $list, $count if $count;
};
diff --git a/lib/VNWeb/VN/Page.pm b/lib/VNWeb/VN/Page.pm
index f2f0fc8d..3a98bedb 100644
--- a/lib/VNWeb/VN/Page.pm
+++ b/lib/VNWeb/VN/Page.pm
@@ -443,7 +443,8 @@ sub releases_ {
txt_ $LANGUAGE{$lang};
}
};
- release_row_ $_, $lang for grep grep($_ eq $lang, $_->{lang}->@*), $v->{releases}->@*;
+ my $ropt = { id => $lang };
+ release_row_ $_, $ropt for grep grep($_ eq $lang, $_->{lang}->@*), $v->{releases}->@*;
}
div_ class => 'mainbox', sub {