diff options
author | Yorhel <git@yorhel.nl> | 2021-11-19 13:33:19 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2021-11-19 13:33:19 +0100 |
commit | 0a98d9df12cfc6ea73729b0b3feb3fee52c9f336 (patch) | |
tree | 777d2b1fd12a7f98825c0c2fef6ff87650867e14 /lib | |
parent | 6560748025427a7bca6d074a4858a79a7e7ec48c (diff) |
Search adjustments + use new search for producers,staff,characters too
Diffstat (limited to 'lib')
-rw-r--r-- | lib/Multi/API.pm | 6 | ||||
-rw-r--r-- | lib/Multi/IRC.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Schema.pm | 1 | ||||
-rw-r--r-- | lib/VNWeb/Chars/Elm.pm | 5 | ||||
-rw-r--r-- | lib/VNWeb/Chars/List.pm | 8 | ||||
-rw-r--r-- | lib/VNWeb/Producers/Elm.pm | 13 | ||||
-rw-r--r-- | lib/VNWeb/Producers/List.pm | 3 | ||||
-rw-r--r-- | lib/VNWeb/Staff/Elm.pm | 5 | ||||
-rw-r--r-- | lib/VNWeb/Staff/List.pm | 8 |
9 files changed, 18 insertions, 35 deletions
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index 866de1ce..24a8a0ee 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -817,7 +817,7 @@ my %GET_PRODUCER = ( [ stra => 'p.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], ], search => [ - [ str => '(p.name ILIKE :value: OR p.original ILIKE :value: OR p.alias ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => 'p.c_search LIKE ALL (search_query(:value:))', {'~',1} ], ], }, ); @@ -928,7 +928,7 @@ my %GET_CHARACTER = ( [ str => 'c.original ILIKE :value:', {'~',1}, process => \'like' ] ], search => [ - [ str => '(c.name ILIKE :value: OR c.original ILIKE :value: OR c.alias ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => 'c.c_search LIKE ALL (search_query(:value:))', {'~',1} ], ], vn => [ [ 'int' => 'c.id IN(SELECT cv.id FROM chars_vns cv WHERE cv.vid = :value:)', {'=',1}, process => \'v' ], @@ -1033,7 +1033,7 @@ my %GET_STAFF = ( [ inta => 's.id IN(SELECT sa.id FROM staff_alias sa WHERE sa.aid IN(:value:))', {'=',1}, range => [1,1e6], join => ',' ], ], search => [ - [ str => 's.id IN(SELECT sa.id FROM staff_alias sa WHERE sa.name ILIKE :value: OR sa.original ILIKE :value:)', {'~',1}, process => \'like' ], + [ str => 's.id IN(SELECT sa.id FROM staff_alias sa WHERE sa.c_search LIKE ALL (search_query(:value:)))', {'~',1} ], ], }, ); diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 245603a2..4ef26ec1 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -393,10 +393,10 @@ p => [ 0, 0, sub { pg_cmd q{ SELECT id, name AS title FROM producers p - WHERE hidden = FALSE AND (name ILIKE $1 OR original ILIKE $1 OR alias ILIKE $1) + WHERE hidden = FALSE AND c_search LIKE ALL (search_query($1)) ORDER BY name LIMIT 6 - }, [ "%$q%" ], sub { + }, [ $q ], sub { my $res = shift; return if pg_expect $res, 1; return $irc->send_msg(PRIVMSG => $chan, 'No producers novels found.') if !$res->nRows; diff --git a/lib/VNDB/Schema.pm b/lib/VNDB/Schema.pm index 63c0f258..654a08b9 100644 --- a/lib/VNDB/Schema.pm +++ b/lib/VNDB/Schema.pm @@ -37,6 +37,7 @@ sub schema { next if /^\s*--/ || /^\s*$/; next if /^\s*CREATE\s+TYPE/; next if /^\s*CREATE\s+SEQUENCE/; + next if /^\s*CREATE\s+FUNCTION/; if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) { die "Unexpected 'CREATE TABLE $1'\n" if $table; diff --git a/lib/VNWeb/Chars/Elm.pm b/lib/VNWeb/Chars/Elm.pm index ce14f490..f52ee8f5 100644 --- a/lib/VNWeb/Chars/Elm.pm +++ b/lib/VNWeb/Chars/Elm.pm @@ -4,16 +4,13 @@ use VNWeb::Prelude; elm_api Chars => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = sql_like $q; my $l = tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT c.id, c.name, c.original, c.main, cm.name AS main_name, cm.original AS main_original FROM (SELECT MIN(prio), id FROM (', sql_join('UNION ALL', $q =~ /^$RE{cid}$/ ? sql('SELECT 1, id FROM chars WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(name),' , \$qs, '), id FROM chars WHERE name ILIKE', \"%$qs%"), - sql('SELECT 10+substr_score(lower(original),', \$qs, "), id FROM chars WHERE translate(original,' ','') ILIKE", \("%$qs%" =~ s/ //gr)), - sql('SELECT 100, id FROM chars WHERE alias ILIKE', \"%$qs%"), + sql('SELECT 1+substr_score(lower(name),', \sql_like($q),'), id FROM chars WHERE c_search LIKE ALL (search_query(', \$q, '))'), ), ') x(prio,id) GROUP BY id) x(prio, id) JOIN chars c ON c.id = x.id LEFT JOIN chars cm ON cm.id = c.main diff --git a/lib/VNWeb/Chars/List.pm b/lib/VNWeb/Chars/List.pm index e4956ff7..e44e77ea 100644 --- a/lib/VNWeb/Chars/List.pm +++ b/lib/VNWeb/Chars/List.pm @@ -106,13 +106,9 @@ TUWF::get qr{/c(?:/(?<char>all|[a-z0]))?}, sub { $opt->{f} = advsearch_default 'c' if !$opt->{f}{query} && !defined tuwf->reqGet('f'); - my @search = map { - my $l = '%'.sql_like($_).'%'; - length $_ > 0 ? sql '(c.name ILIKE', \$l, "OR translate(c.original,' ','') ILIKE", \$l, "OR translate(c.alias,' ','') ILIKE", \$l, ')' : (); - } split /[ -,._]/, $opt->{q}||''; - my $where = sql_and - 'NOT c.hidden', $opt->{f}->sql_where(), @search, + 'NOT c.hidden', $opt->{f}->sql_where(), + $opt->{q} ? sql 'c.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(c.name, 1, 1)) =', \$opt->{ch}) : (), defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(c.name) <', \97, 'OR ASCII(c.name) >', \122, ') AND (ASCII(c.name) <', \65, 'OR ASCII(c.name) >', \90, ')') : (); diff --git a/lib/VNWeb/Producers/Elm.pm b/lib/VNWeb/Producers/Elm.pm index dae9709d..2ffea46a 100644 --- a/lib/VNWeb/Producers/Elm.pm +++ b/lib/VNWeb/Producers/Elm.pm @@ -13,15 +13,10 @@ elm_api Producers => undef, { elm_ProducerResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT p.id, p.name, p.original, p.hidden FROM (', - sql_join('UNION ALL', map { - my $qs = sql_like $_; - ( - /^$RE{pid}$/ ? sql('SELECT 1, id FROM producers WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(name),' , \$qs, '), id FROM producers WHERE name ILIKE', \"%$qs%"), - sql('SELECT 10+substr_score(lower(original),', \$qs, "), id FROM producers WHERE translate(original,' ','') ILIKE", \("%$qs%" =~ s/ //gr)), - sql('SELECT 100, id FROM producers WHERE alias ILIKE', \"%$qs%"), - ) - } @q), + sql_join('UNION ALL', map +( + /^$RE{pid}$/ ? sql('SELECT 1, id FROM producers WHERE id =', \"$+{id}") : (), + sql('SELECT 1+substr_score(lower(name),', \sql_like($_), '), id FROM producers WHERE c_search LIKE ALL (search_query(', \"$_", '))'), + ), @q), ') x(prio, id) JOIN producers p ON p.id = x.id WHERE', sql_and($data->{hidden} ? () : 'NOT p.hidden'), ' diff --git a/lib/VNWeb/Producers/List.pm b/lib/VNWeb/Producers/List.pm index 82282a23..741e74f6 100644 --- a/lib/VNWeb/Producers/List.pm +++ b/lib/VNWeb/Producers/List.pm @@ -39,9 +39,8 @@ TUWF::get qr{/p(?:/(?<char>all|[a-z0]))?}, sub { $opt->{f} = advsearch_default 'p' if !$opt->{f}{query} && !defined tuwf->reqGet('f'); - my $qs = length $opt->{q} && '%'.sql_like($opt->{q}).'%'; my $where = sql_and 'NOT p.hidden', $opt->{f}->sql_where(), - $qs ? sql('p.name ILIKE', \$qs, 'OR p.original ILIKE', \$qs, 'OR p.alias ILIKE', \$qs) : (), + $opt->{q} ? sql 'p.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(p.name, 1, 1)) =', \$opt->{ch}) : (), defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(p.name) <', \97, 'OR ASCII(p.name) >', \122, ') AND (ASCII(p.name) <', \65, 'OR ASCII(p.name) >', \90, ')') : (); diff --git a/lib/VNWeb/Staff/Elm.pm b/lib/VNWeb/Staff/Elm.pm index c4db154f..5318f458 100644 --- a/lib/VNWeb/Staff/Elm.pm +++ b/lib/VNWeb/Staff/Elm.pm @@ -4,15 +4,14 @@ use VNWeb::Prelude; elm_api Staff => undef, { search => {} }, sub { my $q = shift->{search}; - my $qs = sql_like $q; elm_StaffResult tuwf->dbPagei({ results => 15, page => 1 }, 'SELECT s.id, sa.aid, sa.name, sa.original FROM (', sql_join('UNION ALL', $q =~ /^$RE{sid}$/ ? sql('SELECT 0, aid FROM staff_alias WHERE id =', \"$+{id}") : (), - sql('SELECT 1+substr_score(lower(name),', \$qs, ')+substr_score(lower(original),', \$qs, '), aid - FROM staff_alias WHERE name ILIKE', \"%$qs%", "OR translate(original,' ','') ILIKE", \("%$qs%" =~ s/ //gr)), + sql('SELECT 1+substr_score(lower(name),', \sql_like($q), ')+substr_score(lower(original),', \sql_like($q), '), aid + FROM staff_alias WHERE c_search LIKE ALL (search_query(', \$q, '))'), ), ') x(prio, aid) JOIN staff_alias sa ON sa.aid = x.aid JOIN staff s ON s.id = sa.id diff --git a/lib/VNWeb/Staff/List.pm b/lib/VNWeb/Staff/List.pm index d83e7827..77f930cd 100644 --- a/lib/VNWeb/Staff/List.pm +++ b/lib/VNWeb/Staff/List.pm @@ -51,14 +51,10 @@ TUWF::get qr{/s(?:/(?<char>all|[a-z0]))?}, sub { $opt->{f} = advsearch_default 's' if !$opt->{f}{query} && !defined tuwf->reqGet('f'); - my @search = map { - my $l = '%'.sql_like($_).'%'; - length $_ > 0 ? sql '(sa.name ILIKE', \$l, "OR translate(sa.original,' ','') ILIKE", \$l, ')' : (); - } split /[ -,._]/, $opt->{q}||''; - my $where = sql_and $opt->{n} ? 's.aid = sa.aid' : (), - 'NOT s.hidden', $opt->{f}->sql_where(), @search, + 'NOT s.hidden', $opt->{f}->sql_where(), + $opt->{q} ? sql 'sa.c_search LIKE ALL (search_query(', \$opt->{q}, '))' : (), defined($opt->{ch}) && $opt->{ch} ? sql('LOWER(SUBSTR(sa.name, 1, 1)) =', \$opt->{ch}) : (), defined($opt->{ch}) && !$opt->{ch} ? sql('(ASCII(sa.name) <', \97, 'OR ASCII(sa.name) >', \122, ') AND (ASCII(sa.name) <', \65, 'OR ASCII(sa.name) >', \90, ')') : (); |