diff options
author | Yorhel <git@yorhel.nl> | 2019-06-28 15:31:43 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-06-28 15:31:45 +0200 |
commit | a91d48ac617b758a8310c28e91b73e23dae90963 (patch) | |
tree | 3613ca657a3c9e709ff35e0b94606fa9d83f95b1 /lib/VNDB | |
parent | 670c294ff6efeead61e215679224604848229c82 (diff) |
VNBrowse: Add character filters
This is a bit experimental, not sure how the performance is going to be.
Diffstat (limited to 'lib/VNDB')
-rw-r--r-- | lib/VNDB/DB/Chars.pm | 51 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 8 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 14 |
3 files changed, 48 insertions, 25 deletions
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm index 67fe2565..a3ba0df9 100644 --- a/lib/VNDB/DB/Chars.pm +++ b/lib/VNDB/DB/Chars.pm @@ -5,7 +5,35 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|; +our @EXPORT = qw|dbCharFilters dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|; + + +# Character filters shared by dbCharGet and dbVNGet +sub dbCharFilters { + my($self, %o) = @_; + return ( + defined $o{gender} ? ( 'c.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), + defined $o{bloodt} ? ( 'c.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (), + defined $o{bust_min} ? ( 'c.s_bust >= ?' => $o{bust_min} ) : (), + defined $o{bust_max} ? ( 'c.s_bust <= ? AND c.s_bust > 0' => $o{bust_max} ) : (), + defined $o{waist_min} ? ( 'c.s_waist >= ?' => $o{waist_min} ) : (), + defined $o{waist_max} ? ( 'c.s_waist <= ? AND c.s_waist > 0' => $o{waist_max} ) : (), + defined $o{hip_min} ? ( 'c.s_hip >= ?' => $o{hip_min} ) : (), + defined $o{hip_max} ? ( 'c.s_hip <= ? AND c.s_hip > 0' => $o{hip_max} ) : (), + defined $o{height_min} ? ( 'c.height >= ?' => $o{height_min} ) : (), + defined $o{height_max} ? ( 'c.height <= ? AND c.height > 0' => $o{height_max} ) : (), + defined $o{weight_min} ? ( 'c.weight >= ?' => $o{weight_min} ) : (), + defined $o{weight_max} ? ( 'c.weight <= ?' => $o{weight_max} ) : (), + $o{role} ? ( + 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.id = c.id AND cvi.role IN(!l))', + [ ref $o{role} ? $o{role} : [$o{role}] ] ) : (), + $o{trait_inc} ? ( + 'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)', + [ ref $o{trait_inc} ? $o{trait_inc} : [$o{trait_inc}], $o{tagspoil}, ref $o{trait_inc} ? $#{$o{trait_inc}}+1 : 1 ]) : (), + $o{trait_exc} ? ( + 'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (), + ) +} # options: id instance tagspoil trait_inc trait_exc char what results page gender bloodt @@ -29,32 +57,13 @@ sub dbCharGet { $o{notid} ? ( 'c.id <> ?' => $o{notid} ) : (), $o{instance} ? ( 'c.main = ?' => $o{instance} ) : (), $o{vid} ? ( 'c.id IN(SELECT id FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (), - defined $o{gender} ? ( 'c.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (), - defined $o{bloodt} ? ( 'c.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (), - defined $o{bust_min} ? ( 'c.s_bust >= ?' => $o{bust_min} ) : (), - defined $o{bust_max} ? ( 'c.s_bust <= ? AND c.s_bust > 0' => $o{bust_max} ) : (), - defined $o{waist_min} ? ( 'c.s_waist >= ?' => $o{waist_min} ) : (), - defined $o{waist_max} ? ( 'c.s_waist <= ? AND c.s_waist > 0' => $o{waist_max} ) : (), - defined $o{hip_min} ? ( 'c.s_hip >= ?' => $o{hip_min} ) : (), - defined $o{hip_max} ? ( 'c.s_hip <= ? AND c.s_hip > 0' => $o{hip_max} ) : (), - defined $o{height_min} ? ( 'c.height >= ?' => $o{height_min} ) : (), - defined $o{height_max} ? ( 'c.height <= ? AND c.height > 0' => $o{height_max} ) : (), - defined $o{weight_min} ? ( 'c.weight >= ?' => $o{weight_min} ) : (), - defined $o{weight_max} ? ( 'c.weight <= ?' => $o{weight_max} ) : (), $o{search} ? ( "(c.name ILIKE ? OR translate(c.original,' ','') ILIKE translate(?,' ','') OR c.alias ILIKE ?)", [ map '%'.$o{search}.'%', 1..3 ] ) : (), $o{char} ? ( 'LOWER(SUBSTR(c.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( '(ASCII(c.name) < 97 OR ASCII(c.name) > 122) AND (ASCII(c.name) < 65 OR ASCII(c.name) > 90)' => 1 ) : (), - $o{role} ? ( - 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.id = c.id AND cvi.role IN(!l))', - [ ref $o{role} ? $o{role} : [$o{role}] ] ) : (), - $o{trait_inc} ? ( - 'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)', - [ ref $o{trait_inc} ? $o{trait_inc} : [$o{trait_inc}], $o{tagspoil}, ref $o{trait_inc} ? $#{$o{trait_inc}}+1 : 1 ]) : (), - $o{trait_exc} ? ( - 'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (), + $self->dbCharFilters(%o), ); my @select = (qw|c.id c.name c.original c.gender|); diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index a057b166..2933fbfd 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -13,7 +13,7 @@ our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotA # Options: id, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, # hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, -# reverse, inc_hidden, date_before, date_after, released, release +# reverse, inc_hidden, date_before, date_after, released, release, character # What: extended anime staff seiyuu relations screenshots relgraph rating ranking wishlist vnlist # Note: wishlist and vnlist are ignored (no db search) unless a user is logged in # Sort: id rel pop rating title tagscore rand @@ -82,6 +82,12 @@ sub dbVNGet { [ 'NOT r.hidden' => 1, $self->dbReleaseFilters(%{$o{release}}), ]; push @where, $q, \@p; } + if($o{character}) { + my($q, @p) = sqlprint + 'v.id IN(SELECT cv.vid FROM chars c JOIN chars_vns cv ON cv.id = c.id !W)', + [ 'NOT c.hidden' => 1, $self->dbCharFilters(%{$o{character}}) ]; + push @where, $q, \@p; + } my @join = ( $o{what} =~ /relgraph/ ? diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index edc42621..554541a7 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -23,6 +23,7 @@ sub list { { get => 'sq', required => 0, default => '' }, { get => 'fil',required => 0 }, { get => 'rfil', required => 0, default => '' }, + { get => 'cfil', required => 0, default => '' }, { get => 'vnlist', required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref { get => 'wish', required => 0, default => 2, enum => [ '0', '1' ] }, # 2: use pref ); @@ -55,6 +56,9 @@ sub list { $self->filCompat(release => $rfil); $f->{rfil} = fil_serialize $rfil, @{$VNDB::Util::Misc::filfields{release}}; + my $cfil = fil_parse $f->{cfil}, @{$VNDB::Util::Misc::filfields{char}}; + $cfil->{tagspoil} //= $self->authPref('spoilers')||0 if keys %$cfil; + my($list, $np) = $self->filFetchDB(vn => $f->{fil}, { %compat, tagspoil => $self->authPref('spoilers')||0, @@ -65,6 +69,7 @@ sub list { $char ne 'all' ? ( char => $char ) : (), $f->{q} ? ( search => $f->{q} ) : (), keys %$rfil ? ( release => $rfil ) : (), + keys %$cfil ? ( character => $cfil ) : (), results => 50, page => $f->{p}, sort => $f->{s}, reverse => $f->{o} eq 'd', @@ -82,7 +87,7 @@ sub list { my $url = sub { my($char, $toggle) = @_; - return "/v/$char?q=$quri;fil=$f->{fil};rfil=$f->{rfil};s=$f->{s};o=$f->{o}" . + return "/v/$char?q=$quri;fil=$f->{fil};rfil=$f->{rfil};cfil=$f->{cfil};s=$f->{s};o=$f->{o}" . ($toggle ? ";$toggle=".($f->{$toggle}?0:1) : ''); }; @@ -108,13 +113,16 @@ sub list { a id => 'rfilselect', href => '#r'; lit '<i>▸</i> Release filters<i></i>'; end; + a id => 'cfilselect', href => '#c'; + lit '<i>▸</i> Character filters<i></i>'; + end; end; input type => 'hidden', class => 'hidden', name => $_, id => $_, value => $f->{$_} - for (qw{fil rfil s o}); + for (qw{fil rfil cfil s o}); end; end 'form'; - $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$quri;fil=$f->{fil};rfil=$f->{rfil}", $f->{fil} =~ /tag_inc-/); + $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$quri;fil=$f->{fil};rfil=$f->{rfil};cfil=$f->{cfil}", $f->{fil} =~ /tag_inc-/); $self->htmlFooter(pref_code => 1); } |