diff options
author | Yorhel <git@yorhel.nl> | 2010-03-13 15:34:24 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-03-13 15:34:24 +0100 |
commit | b8fbb458cdbf6635c45675a3797292110a739f31 (patch) | |
tree | af39f8ae60ba4fa49c92d41a1dc3484d990914b9 /lib/VNDB/DB | |
parent | d5bb5bfc9fd4a043be20bb0e7ba68dbb39157862 (diff) |
Improved VN search
This adds a new column to the vn table: c_search, which holds the
normalized titles for speedy search results using LIKE.
Also split some functions from VNDB::Func that didn't require YAWF into
a VNDBUtil module, so Multi can also make use of them. The normalization
functions are the same for Multi and VNDB, after all.
The API and Multi::IRC still use the old search, these should be updated
as well.
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/VN.pm | 33 |
1 files changed, 5 insertions, 28 deletions
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index cb1a878c..da479a9a 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -4,7 +4,7 @@ package VNDB::DB::VN; use strict; use warnings; use Exporter 'import'; -use VNDB::Func 'gtintype'; +use VNDB::Func 'gtintype', 'normalize_query'; use Encode 'decode_utf8'; our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; @@ -19,7 +19,7 @@ sub dbVNGet { $o{page} ||= 1; $o{what} ||= ''; - my %where = ( + my @where = ( $o{id} ? ( 'v.id = ?' => $o{id} ) : (), $o{rev} ? ( @@ -38,36 +38,13 @@ sub dbVNGet { ) : (), $o{tags_exclude} && @{$o{tags_exclude}} ? ( 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), + $o{search} ? ( + map +('v.c_search like ?', "%$_%"), normalize_query($o{search})) : (), # don't fetch hidden items unless we ask for an ID !$o{id} && !$o{rev} ? ( 'v.hidden = FALSE' => 0 ) : (), ); - if($o{search}) { - my @w; - for (split /[ -,._]/, $o{search}) { - s/%//g; - if(/^\d+$/ && gtintype($_)) { - push @w, 'irr.gtin = ?', $_; - } elsif(length($_) > 0) { - $_ = "%$_%"; - push @w, '(ivr.title ILIKE ? OR ivr.original ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)', - [ $_, $_, $_, $_, $_ ]; - } - } - push @w, '(irr.id IS NULL OR ir.latest = irr.id)' => 1 if @w; - $where{ q| - v.id IN(SELECT iv.id - FROM vn iv - JOIN vn_rev ivr ON iv.latest = ivr.id - LEFT JOIN releases_vn irv ON irv.vid = iv.id - LEFT JOIN releases_rev irr ON irr.id = irv.rid - LEFT JOIN releases ir ON ir.latest = irr.id - !W - GROUP BY iv.id)| - } = [ \@w ] if @w; - } - my @join = ( $o{rev} ? 'JOIN vn v ON v.id = vr.vid' : @@ -114,7 +91,7 @@ sub dbVNGet { !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $order, + join(', ', @select), join(' ', @join), \@where, $order, ); if($o{what} =~ /relgraph/) { |