From 5b4bd7befcc80cbaf43c07a356acbd45dfdd5dfa Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Thu, 18 Nov 2021 10:51:58 +0100
Subject: 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.
---
 lib/VNWeb/Releases/List.pm | 11 +++++------
 1 file changed, 5 insertions(+), 6 deletions(-)

(limited to 'lib/VNWeb/Releases')

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);
-- 
cgit v1.2.3