Releases: Experiment with new SQL search implementation

Goal is to use the improved search normalisation mechanism that VN
search has had for ages on other database types as well (releases with
this commit; producers, staff and characters to come later).

Another goal is to do the search cache updates within SQL, removing the
Perl search normalization and the round-trip through Multi every time an
entry needs to be updated.

Requires the 'unaccent' Postgres extension. I considered writing my own
unaccent rules file, but decided against it for now. Current approach
works pretty well.

1 files changed, 5 insertions, 6 deletions

diff --git a/lib/VNWeb/Releases/List.pm b/lib/VNWeb/Releases/List.pm
index 3f5717a2..a55a7a88 100644
--- a/lib/VNWeb/Releases/List.pm
+++ b/lib/VNWeb/Releases/List.pm
@@ -50,12 +50,11 @@ TUWF::get qr{/r}, sub {
 
     $opt->{f} = advsearch_default 'r' if !$opt->{f}{query} && !defined tuwf->reqGet('f');
 
-    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 $where = sql_and 'NOT r.hidden', $opt->{f}->sql_where(),
+        !$opt->{q} ? () : sql_or
+            sql('r.c_search LIKE ALL (search_query(', \$opt->{q}, '))'),
+            $opt->{q} =~ /^\d+$/ && gtintype($opt->{q}) ? sql 'r.gtin =', \$opt->{q} : (),
+            $opt->{q} =~ /^[a-zA-Z0-9-]+$/ ? sql 'r.catalog =', \$opt->{q} : ();
 
     my $time = time;
     my($count, $list);