diff options
Diffstat (limited to 'lib/VNWeb/AdvSearch.pm')
| -rw-r--r-- | lib/VNWeb/AdvSearch.pm | 365 |
1 files changed, 267 insertions, 98 deletions
diff --git a/lib/VNWeb/AdvSearch.pm b/lib/VNWeb/AdvSearch.pm index 82030ffd..6f226b7f 100644 --- a/lib/VNWeb/AdvSearch.pm +++ b/lib/VNWeb/AdvSearch.pm @@ -14,12 +14,13 @@ use warnings; use B; use POSIX 'strftime'; use List::Util 'max'; -use TUWF; +use TUWF ':html5_'; use VNWeb::Auth; use VNWeb::DB; use VNWeb::Validation; use VNWeb::HTML (); use VNDB::Types; +use VNDB::ExtLinks (); use Exporter 'import'; our @EXPORT = qw/advsearch_default/; @@ -302,36 +303,40 @@ sub f { $f{vndbid} = ref $v eq 'HASH' && $v->{vndbid} && !ref $v->{vndbid} && $v->{vndbid}; $f{int} = ref $f{value} && ($v->{fuzzyrdate} || $f{value}->analyze->{type} eq 'int' || $f{value}->analyze->{type} eq 'bool'); $FIELDS{$t}{$n} = \%f; + die "Duplicate number $num for $t\n" if $NUMFIELDS{$t}{$num}; $NUMFIELDS{$t}{$num} = $n; } my @TYPE; # stack of query types, $TYPE[0] is the top-level query, $TYPE[$#TYPE] the query currently being processed. +f v => 80 => 'id', { vndbid => 'v' }, sql => sub { sql 'v.id', $_[0], \$_ }; +f v => 81 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('v', 'v.id') }; f v => 2 => 'lang', { enum => \%LANGUAGE }, '=' => sub { sql 'v.c_languages && ARRAY', \$_, '::language[]' }; f v => 3 => 'olang', { enum => \%LANGUAGE }, '=' => sub { sql 'v.olang =', \$_ }; f v => 4 => 'platform', { enum => \%PLATFORM }, '=' => sub { sql 'v.c_platforms && ARRAY', \$_, '::platform[]' }; -f v => 6 => 'developer-id',{ vndbid => 'p' }, '=' => sub { sql 'v.c_developers && ARRAY', \$_, '::vndbid[]' }; -f v => 5 => 'length', { uint => 1, enum => \%VN_LENGTH }, '=' => sub { sql 'v.length =', \$_ }; +f v => 5 => 'length', { uint => 1, enum => \%VN_LENGTH }, + '=' => sub { sql 'COALESCE(v.c_length BETWEEN', \$VN_LENGTH{$_}{low}, 'AND', \$VN_LENGTH{$_}{high}, ', v.length =', \$_, ')' }; f v => 7 => 'released', { fuzzyrdate => 1 }, sql => sub { sql 'v.c_released', $_[0], \($_ == 1 ? strftime('%Y%m%d', gmtime) : $_) }; -f v => 9 => 'popularity',{ uint => 1, range => [ 0, 100] }, sql => sub { sql 'v.c_popularity', $_[0], \($_*100) }; +f v => 9 => 'popularity',{ uint => 1, range => [ 0, 100] }, sql => sub { sql 'v.c_votecount', $_[0], \($_*150) }; # XXX: Deprecated f v => 10 => 'rating', { uint => 1, range => [10, 100] }, sql => sub { sql 'v.c_rating', $_[0], \($_*10) }; -f v => 11 => 'vote-count',{ uint => 1, range => [ 0,1<<30] }, sql => sub { sql 'v.c_votecount', $_[0], \$_ }; -f v => 61 => 'has-description', { uint => 1, range => [1,1] }, '=' => sub { 'v."desc" <> \'\'' }; -f v => 62 => 'has-anime', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' }; -f v => 63 => 'has-screenshot', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.id = v.id)' }; -f v => 64 => 'has-review', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM reviews r WHERE r.vid = v.id AND NOT r.c_flagged)' }; -f v => 65 => 'on-list', { uint => 1, range => [1,1] }, '=' => sub { auth ? sql 'v.id IN(SELECT vid FROM ulist_vns WHERE uid =', \auth->uid, ')' : '1=0' }; - -f v => 8 => 'tag', { type => 'any', func => \&_validate_tag }, - compact => sub { my $id = ($_->[0] =~ s/^g//r)*1; $_->[1] == 0 && $_->[2] == 0 ? $id : [ $id, int($_->[2]*5)*3 + $_->[1] ] }, - sql_list => \&_sql_where_tag; +f v => 11 => 'votecount', { uint => 1, range => [ 0,1<<30] }, sql => sub { sql 'v.c_votecount', $_[0], \$_ }; +f v => 61 => 'has_description', { uint => 1, range => [1,1] }, '=' => sub { 'v.description <> \'\'' }; +f v => 62 => 'has_anime', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' }; +f v => 63 => 'has_screenshot', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.id = v.id)' }; +f v => 64 => 'has_review', { uint => 1, range => [1,1] }, '=' => sub { 'EXISTS(SELECT 1 FROM reviews r WHERE r.vid = v.id AND NOT r.c_flagged)' }; +f v => 65 => 'on_list', { uint => 1, range => [1,1] }, + '=' => sub { auth ? sql 'v.id IN(SELECT vid FROM ulist_vns WHERE uid =', \auth->uid, auth->api2Listread ? () : 'AND NOT c_private', ')' : '1=0' }; +f v => 66 => 'devstatus', { uint => 1, enum => \%DEVSTATUS }, '=' => sub { 'v.devstatus =', \$_ }; + +f v => 8 => 'tag', { type => 'any', func => \&_validate_tag }, compact => \&_compact_tag, sql_list => _sql_where_tag('tags_vn_inherit'); +f v => 14 => 'dtag', { type => 'any', func => \&_validate_tag }, compact => \&_compact_tag, sql_list => _sql_where_tag('tags_vn_direct'); f v => 12 => 'label', { type => 'any', func => \&_validate_label }, compact => sub { [ ($_->[0] =~ s/^u//r)*1, $_->[1]*1 ] }, sql_list => \&_sql_where_label, sql_list_grp => sub { $_->[1] == 0 ? undef : $_->[0] }; -f v => 13 => 'anime-id', { id => 1 }, +f v => 13 => 'anime_id', { id => 1 }, sql_list => sub { my($neg, $all, $val) = @_; sql 'v.id', $neg ? 'NOT' : '', 'IN(SELECT id FROM vn_anime WHERE aid IN', $val, $all && @$val > 1 ? ('GROUP BY id HAVING COUNT(aid) =', \scalar @$val) : (), ')'; @@ -343,19 +348,24 @@ f v => 52 => 'staff', 's', '=' => sub { # The "Staff" filter includes both vn_staff and vn_seiyuu. Union those tables together and filter on that. sql 'v.id IN(SELECT vs.id FROM (SELECT id, aid, role FROM vn_staff UNION ALL SELECT id, aid, NULL FROM vn_seiyuu) vs - JOIN staff_alias sa ON sa.aid = vs.aid - JOIN staff s ON s.id = sa.id + JOIN staff_aliast s ON s.aid = vs.aid WHERE NOT s.hidden AND', $_, ')' }; +f v => 55 => 'developer', 'p', '=' => sub { sql 'EXISTS(SELECT 1 FROM producers p, unnest(v.c_developers) vcd(x) WHERE p.id = vcd.x AND NOT p.hidden AND', $_, ')' }; + +# Deprecated. +f v => 6 => 'developer-id', { vndbid => 'p' }, '=' => sub { sql 'v.c_developers && ARRAY', \$_, '::vndbid[]' }; +f r => 80 => 'id', { vndbid => 'r' }, sql => sub { sql 'r.id', $_[0], \$_ }; +f r => 81 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('r', 'r.id') }; f r => 2 => 'lang', { enum => \%LANGUAGE }, sql_list => sub { my($neg, $all, $val) = @_; - sql 'r.id', $neg ? 'NOT' : '', 'IN(SELECT id FROM releases_lang WHERE NOT mtl AND lang IN', $val, $all && @$val > 1 ? ('GROUP BY id HAVING COUNT(lang) =', \scalar @$val) : (), ')'; + sql 'r.id', $neg ? 'NOT' : '', 'IN(SELECT id FROM releases_titles WHERE NOT mtl AND lang IN', $val, $all && @$val > 1 ? ('GROUP BY id HAVING COUNT(lang) =', \scalar @$val) : (), ')'; }; -f r => 4 => 'platform', { required => 0, default => undef, enum => \%PLATFORM }, +f r => 4 => 'platform', { default => undef, enum => \%PLATFORM }, sql_list_grp => sub { defined $_ }, sql_list => sub { my($neg, $all, $val) = @_; @@ -363,71 +373,77 @@ f r => 4 => 'platform', { required => 0, default => undef, enum => \%PLATFORM } sql 'r.id', $neg ? 'NOT' : '', 'IN(SELECT id FROM releases_platforms WHERE platform IN', $val, $all && @$val > 1 ? ('GROUP BY id HAVING COUNT(platform) =', \scalar @$val) : (), ')'; }; -f r => 6 => 'developer-id',{ vndbid => 'p' }, '=' => sub { sql 'r.id IN(SELECT id FROM releases_producers WHERE developer AND pid =', \$_, ')' }; -f r => 17 => 'producer-id', { vndbid => 'p' }, '=' => sub { sql 'r.id IN(SELECT id FROM releases_producers WHERE pid =', \$_, ')' }; f r => 7 => 'released', { fuzzyrdate => 1 }, sql => sub { sql 'r.released', $_[0], \($_ == 1 ? strftime('%Y%m%d', gmtime) : $_) }; f r => 8 => 'resolution', { type => 'array', length => 2, values => { uint => 1, max => 32767 } }, sql => sub { sql 'NOT r.patch AND r.reso_x', $_[0], \$_->[0], 'AND r.reso_y', $_[0], \$_->[1], $_->[0] ? 'AND r.reso_x > 0' : () }; f r => 9 => 'resolution-aspect', { type => 'array', length => 2, values => { uint => 1, max => 32767 } }, sql => sub { sql 'NOT r.patch AND r.reso_x', $_[0], \$_->[0], 'AND r.reso_y', $_[0], \$_->[1], 'AND r.reso_x*1000/GREATEST(1, r.reso_y) =', \(int ($_->[0]*1000/max(1,$_->[1]))), $_->[0] ? 'AND r.reso_x > 0' : () }; -f r => 10 => 'minage', { required => 0, default => undef, uint => 1, enum => \%AGE_RATING }, +f r => 10 => 'minage', { default => undef, uint => 1, enum => \%AGE_RATING }, sql => sub { defined $_ ? sql 'r.minage', $_[0], \$_ : $_[0] eq '=' ? 'r.minage IS NULL' : 'r.minage IS NOT NULL' }; -f r => 11 => 'medium', { required => 0, default => undef, enum => \%MEDIUM }, +f r => 11 => 'medium', { default => undef, enum => \%MEDIUM }, '=' => sub { !defined $_ ? 'NOT EXISTS(SELECT 1 FROM releases_media rm WHERE rm.id = r.id)' : sql 'EXISTS(SELECT 1 FROM releases_media rm WHERE rm.id = r.id AND rm.medium =', \$_, ')' }; -f r => 12 => 'voiced', { uint => 1, enum => \%VOICED }, '=' => sub { sql 'NOT r.patch AND r.voiced =', \$_ }; +f r => 12 => 'voiced', { default => 0, uint => 1, enum => \%VOICED }, '=' => sub { sql 'NOT r.patch AND r.voiced =', \$_ }; f r => 13 => 'animation-ero', { uint => 1, enum => \%ANIMATED }, '=' => sub { sql 'NOT r.patch AND r.ani_ero =', \$_ }; f r => 14 => 'animation-story', { uint => 1, enum => \%ANIMATED }, '=' => sub { sql 'NOT r.patch AND r.ani_story =', \$_ }; -f r => 15 => 'engine', { required => 0, default => '' }, '=' => sub { sql 'r.engine =', \$_ }; -f r => 16 => 'rtype', { enum => \%RELEASE_TYPE }, '=' => sub { sql 'r.type =', \$_ }; +f r => 15 => 'engine', { default => '' }, '=' => sub { sql 'r.engine =', \$_ }; +f r => 16 => 'rtype', { enum => \%RELEASE_TYPE }, '=' => sub { $#TYPE && $TYPE[$#TYPE-1] eq 'v' ? sql 'rv.rtype =', \$_ : sql 'r.id IN(SELECT id FROM releases_vn WHERE rtype =', \$_, ')' }; f r => 18 => 'rlist', { uint => 1, enum => \%RLIST_STATUS }, sql_list => sub { my($neg, $all, $val) = @_; return '1=0' if !auth; sql 'r.id', $neg ? 'NOT' : '', 'IN(SELECT rid FROM rlists WHERE uid =', \auth->uid, 'AND status IN', $val, $all && @$val > 1 ? ('GROUP BY rid HAVING COUNT(status) =', \scalar @$val) : (), ')'; }; +f r => 19 => 'extlink', _extlink_filter('r'); +f r => 20 => 'drm', { default => '' }, '=' => sub { sql 'EXISTS(SELECT 1 FROM drm JOIN releases_drm rd ON rd.drm = drm.id WHERE drm.name =', \$_, 'AND rd.id = r.id)' }; f r => 61 => 'patch', { uint => 1, range => [1,1] }, '=' => sub { 'r.patch' }; f r => 62 => 'freeware', { uint => 1, range => [1,1] }, '=' => sub { 'r.freeware' }; -# TODO: This flag has been deprecated, replace with a subquery that checks the publisher instead? -f r => 63 => 'doujin', { uint => 1, range => [1,1] }, '=' => sub { 'r.doujin' }; f r => 64 => 'uncensored',{uint => 1, range => [1,1] }, '=' => sub { 'r.uncensored' }; f r => 65 => 'official', { uint => 1, range => [1,1] }, '=' => sub { 'r.official' }; -f r => 53 => 'vn', 'v', '=' => sub { sql 'r.id IN(SELECT rv.id FROM releases_vn rv JOIN vn v ON v.id = rv.vid WHERE NOT v.hidden AND', $_, ')' }; +f r => 66 => 'has_ero', { uint => 1, range => [1,1] }, '=' => sub { 'r.has_ero' }; +f r => 53 => 'vn', 'v', '=' => sub { sql 'r.id IN(SELECT rv.id FROM releases_vn rv JOIN vn v ON v.id = rv.vid WHERE NOT v.hidden AND', $_, ')' }; +f r => 55 => 'producer', 'p', '=' => sub { sql 'r.id IN(SELECT rp.id FROM releases_producers rp JOIN producers p ON p.id = rp.pid WHERE NOT p.hidden AND', $_, ')' }; + +# Deprecated. +f r => 6 => 'developer-id',{ vndbid => 'p' }, '=' => sub { sql 'r.id IN(SELECT id FROM releases_producers WHERE developer AND pid =', \$_, ')' }; # Does not have a new equivalent +f r => 17 => 'producer-id', { vndbid => 'p' }, '=' => sub { sql 'r.id IN(SELECT id FROM releases_producers WHERE pid =', \$_, ')' }; +f r => 63 => 'doujin', { uint => 1, range => [1,1] }, '=' => sub { 'r.doujin' }; # Not recognized by Elm anymore. +f c => 80 => 'id', { vndbid => 'c' }, sql => sub { sql 'c.id', $_[0], \$_ }; +f c => 81 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('c', 'c.id') }; f c => 2 => 'role', { enum => \%CHAR_ROLE }, '=' => sub { $#TYPE && $TYPE[$#TYPE-1] eq 'v' ? sql 'cv.role =', \$_ : sql 'c.id IN(SELECT id FROM chars_vns WHERE role =', \$_, ')' }; -f c => 3 => 'blood-type', { enum => \%BLOOD_TYPE }, '=' => sub { sql 'c.bloodt =', \$_ }; +f c => 3 => 'blood_type', { enum => \%BLOOD_TYPE }, '=' => sub { sql 'c.bloodt =', \$_ }; f c => 4 => 'sex', { enum => \%GENDER }, '=' => sub { sql 'c.gender =', \$_ }; -f c => 5 => 'sex-spoil', { enum => \%GENDER }, '=' => sub { sql '(c.gender =', \$_, 'AND c.spoil_gender IS NULL) OR c.spoil_gender IS NOT DISTINCT FROM', \$_ }; -f c => 6 => 'height', { required => 0, default => undef, uint => 1, max => 32767 }, +f c => 5 => 'sex_spoil', { enum => \%GENDER }, '=' => sub { sql '(c.gender =', \$_, 'AND c.spoil_gender IS NULL) OR c.spoil_gender IS NOT DISTINCT FROM', \$_ }; +f c => 6 => 'height', { default => undef, uint => 1, max => 32767 }, sql => sub { !defined $_ ? sql 'c.height', $_[0], 0 : sql 'c.height <> 0 AND c.height', $_[0], \$_ }; -f c => 7 => 'weight', { required => 0, default => undef, uint => 1, max => 32767 }, +f c => 7 => 'weight', { default => undef, uint => 1, max => 32767 }, sql => sub { !defined $_ ? sql('c.weight IS', $_[0] eq '=' ? '' : 'NOT', 'NULL') : sql 'c.weight', $_[0], \$_ }; -f c => 8 => 'bust', { required => 0, default => undef, uint => 1, max => 32767 }, +f c => 8 => 'bust', { default => undef, uint => 1, max => 32767 }, sql => sub { !defined $_ ? sql 'c.s_bust', $_[0], 0 : sql 'c.s_bust <> 0 AND c.s_bust', $_[0], \$_ }; -f c => 9 => 'waist', { required => 0, default => undef, uint => 1, max => 32767 }, +f c => 9 => 'waist', { default => undef, uint => 1, max => 32767 }, sql => sub { !defined $_ ? sql 'c.s_waist', $_[0], 0 : sql 'c.s_waist <> 0 AND c.s_waist', $_[0], \$_ }; -f c => 10 => 'hips', { required => 0, default => undef, uint => 1, max => 32767 }, +f c => 10 => 'hips', { default => undef, uint => 1, max => 32767 }, sql => sub { !defined $_ ? sql 'c.s_hip', $_[0], 0 : sql 'c.s_hip <> 0 AND c.s_hip', $_[0], \$_ }; -f c => 11 => 'cup', { required => 0, default => undef, enum => \%CUP_SIZE }, +f c => 11 => 'cup', { default => undef, enum => \%CUP_SIZE }, sql => sub { !defined $_ ? sql 'c.cup_size', $_[0], "''" : sql 'c.cup_size <> \'\' AND c.cup_size', $_[0], \$_ }; -f c => 12 => 'age', { required => 0, default => undef, uint => 1, max => 32767 }, +f c => 12 => 'age', { default => undef, uint => 1, max => 32767 }, sql => sub { !defined $_ ? sql('c.age IS', $_[0] eq '=' ? '' : 'NOT', 'NULL') : sql 'c.age', $_[0], \$_ }; -f c => 13 => 'trait', { type => 'any', func => \&_validate_trait }, - compact => sub { my $id = ($_->[0] =~ s/^i//r)*1; $_->[1] == 0 ? $id : [ $id, int $_->[1] ] }, - sql_list => \&_sql_where_trait; -f c => 14 => 'birthday', { type => 'array', length => 2, values => { uint => 1, max => 31 } }, +f c => 13 => 'trait', { type => 'any', func => \&_validate_trait }, compact => \&_compact_trait, sql_list => _sql_where_trait('traits_chars', 'cid'); +f c => 15 => 'dtrait', { type => 'any', func => \&_validate_trait }, compact => \&_compact_trait, sql_list => _sql_where_trait('chars_traits', 'id'); +f c => 14 => 'birthday', { default => [0,0], type => 'array', length => 2, values => { uint => 1, max => 31 } }, '=' => sub { sql 'c.b_month =', \$_->[0], $_->[1] ? ('AND c.b_day =', \$_->[1]) : () }; # XXX: When this field is nested inside a VN query, it may match seiyuu linked to other VNs. # This can be trivially fixed by adding an (AND vs.id = v.id) clause, but that results in extremely slow queries that I've no clue how to optimize. -f c => 52 => 'seiyuu', 's', '=' => sub { sql 'c.id IN(SELECT vs.cid FROM vn_seiyuu vs JOIN staff_alias sa ON sa.aid = vs.aid JOIN staff s ON s.id = sa.id WHERE NOT s.hidden AND', $_, ')' }; +f c => 52 => 'seiyuu', 's', '=' => sub { sql 'c.id IN(SELECT vs.cid FROM vn_seiyuu vs JOIN staff_aliast s ON s.aid = vs.aid WHERE NOT s.hidden AND', $_, ')' }; f c => 53 => 'vn', 'v', '=' => sub { sql 'c.id IN(SELECT cv.id FROM chars_vns cv JOIN vn v ON v.id = cv.vid WHERE NOT v.hidden AND', $_, ')' }; -# Staff filters need both 'staff s' and 'staff_alias sa' - aliases are treated as separate rows. +# Staff filters need 'staff_aliast s', aliases are treated as separate rows. f s => 2 => 'lang', { enum => \%LANGUAGE }, '=' => sub { sql 's.lang =', \$_ }; -f s => 3 => 'id', { vndbid => 's' }, '=' => sub { sql 's.id = ', \$_ }; +f s => 3 => 'id', { vndbid => 's' }, sql => sub { sql 's.id', $_[0], \$_ }; f s => 4 => 'gender', { enum => \%GENDER }, '=' => sub { sql 's.gender =', \$_ }; f s => 5 => 'role', { enum => [ 'seiyuu', keys %CREDIT_TYPE ] }, sql_list_grp => sub { $_ eq 'seiyuu' ? undef : '' }, @@ -437,18 +453,94 @@ f s => 5 => 'role', { enum => [ 'seiyuu', keys %CREDIT_TYPE ] }, if($#TYPE && $TYPE[$#TYPE-1] eq 'v') { # Shortcut referencing the vn_staff table we're already querying return $val->[0] eq 'seiyuu' ? 'vs.role IS NULL' : sql 'vs.role IN', $val if !@grp && !$neg; - return sql $neg ? 'NOT' : '', 'EXISTS(SELECT 1 FROM vn_seiyuu vs WHERE vs.id = v.id AND vs.aid = sa.aid)' if $val->[0] eq 'seiyuu'; - sql 'sa.aid', $neg ? 'NOT' : '', 'IN(SELECT vs.aid FROM vn_staff vs WHERE vs.id = v.id AND vs.role IN', $val, @grp, ')'; + return sql $neg ? 'NOT' : '', 'EXISTS(SELECT 1 FROM vn_seiyuu vs WHERE vs.id = v.id AND vs.aid = s.aid)' if $val->[0] eq 'seiyuu'; + sql 's.aid', $neg ? 'NOT' : '', 'IN(SELECT vs.aid FROM vn_staff vs WHERE vs.id = v.id AND vs.role IN', $val, @grp, ')'; } else { - return sql $neg ? 'NOT' : '', 'EXISTS(SELECT 1 FROM vn_seiyuu vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND vs.aid = sa.aid)' if $val->[0] eq 'seiyuu'; - sql 'sa.aid', $neg ? 'NOT' : '', 'IN(SELECT vs.aid FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND vs.role IN', $val, @grp, ')'; + return sql $neg ? 'NOT' : '', 'EXISTS(SELECT 1 FROM vn_seiyuu vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND vs.aid = s.aid)' if $val->[0] eq 'seiyuu'; + sql 's.aid', $neg ? 'NOT' : '', 'IN(SELECT vs.aid FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden AND vs.role IN', $val, @grp, ')'; } }; +f s => 6 => 'extlink', _extlink_filter('s'); +f s => 61 => 'ismain', { uint => 1, range => [1,1] }, '=' => sub { 's.aid = s.main' }; +f s => 80 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('s', 's.id', 's.aid') }; +f s => 81 => 'aid', { id => 1 }, '=' => sub { sql 's.aid =', \$_ }; + +f p => 2 => 'lang', { enum => \%LANGUAGE }, '=' => sub { sql 'p.lang =', \$_ }; +f p => 3 => 'id', { vndbid => 'p' }, sql => sub { sql 'p.id', $_[0], \$_ }; +f p => 4 => 'type', { enum => \%PRODUCER_TYPE }, '=' => sub { sql 'p.type =', \$_ }; +f p => 80 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('p', 'p.id') }; + + +f g => 2 => 'id', { vndbid => 'g' }, sql => sub { sql 't.id', $_[0], \$_ }; +f g => 3 => 'category', { enum => \%TAG_CATEGORY }, '=' => sub { sql 't.cat =', \$_ }; +f g => 80 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('g', 't.id') }; + + +f i => 2 => 'id', { vndbid => 'i' }, sql => sub { sql 't.id', $_[0], \$_ }; +f i => 80 => 'search', { searchquery => 1 }, '=' => sub { $_->sql_where('i', 't.id') }; + + + +# 'extlink' filter accepts the following values: +# - $name - Whether the entry has a link of site $name +# - [ $name, $val ] - Whether the entry has a link of site $name with the given $val +# - "$name,$val" - Compact version of above (not really *compact* by any means, but this filter isn't common anyway) +# - "http://..." - Auto-detect version of [$name,$val] +# TODO: This only handles links defined in %LINKS, but it would be nice to also support links from Wikidata & PlayAsia. +sub _extlink_filter { + my($type) = @_; + my $schema = (grep +($_->{dbentry_type}||'') eq $type, values VNDB::Schema::schema->%*)[0]; + my %links = map { + my $n = $_; + my $l = $VNDB::ExtLinks::LINKS{$type}{$n}; + my $s = (grep $_->{name} eq $n, $schema->{cols}->@*)[0]; + (s/^l_//r, +{ %$l, + _col => $n, + _schema => $s, + _regex => $l->{regex} && VNDB::ExtLinks::full_regex($l->{regex}), + _hasval => $s->{type} =~ /\[\]/ ? "<> '{}'" : $s->{decl} !~ /not\s+null/i ? 'is not null' : $s->{type} =~ /^(big)?int/i ? '<> 0' : "<> ''" + }) + } keys $VNDB::ExtLinks::LINKS{$type}->%*; + + my sub _val { + return 1 if !ref $_[0] && $links{$_[0]}; # just $name + if(!ref $_[0] && $_[0] =~ /^https?:/) { # URL + for (keys %links) { + if($links{$_}{_regex} && $_[0] =~ $links{$_}{_regex}) { + $_[0] = [ $_, $1 ]; + last; + } + } + return { msg => 'Unrecognized URL format' } if !ref $_[0]; + } + $_[0] = [ split /,/, $_[0], 2 ] if !ref $_[0]; # compact $name,$val form + + # normalized $name,$val form + return 0 if ref $_[0] ne 'ARRAY' || $_[0]->@* != 2 || ref $_[0][0] || ref $_[0][1] || !defined $_[0][1]; + my $l = $links{$_[0][0]}; + return { msg => "Unknown field '$_[0][0]'" } if !$l; + return { msg => "Invalid value '$_[0][1]'" } if $l->{_schema}{type} =~ /^int/i && ($_[0][1] !~ /^-?[0-9]+$/ || $_[0][1] >= (1<<31) || $_[0][1] <= -(1<<31)); + return { msg => "Invalid value '$_[0][1]'" } if $l->{_schema}{type} =~ /^bigint/i && ($_[0][1] !~ /^-?[0-9]+$/ || $_[0][1] >= (1<<63) || $_[0][1] <= -(1<<63)); + $_[0][1] *= 1 if $l->{_schema}{type} =~ /^(big)?int/i; + 1 + } + my sub _sql { + return "$type.$links{$_}{_col} $links{$_}{_hasval}" if !ref; # just name + my($l, $v) = ($links{$_->[0]}, $_->[1]); + sql "$type.$l->{_col}", $l->{_schema}{type} =~ /\[\]/ ? ('&& ARRAY[', \$v, ']::', $l->{_schema}{type}) : ('=', \$v); + } + my sub _comp { ref $_ ? $_->[0].','.(my $x=$_->[1]) : $_ } + ({ type => 'any', func => \&_val }, '=' => \&_sql, compact => \&_comp) +} - -# Accepts either $tag or [$tag, int($minlevel*5)*3+$maxspoil] (for compact form) or [$tag, $maxspoil, $minlevel]. Normalizes to the latter. +# Accepts either: +# - $tag +# - [$tag, $exclude_lies*16*3 + int($minlevel*5)*3 + $maxspoil] (compact form) +# - [$tag, $maxspoil, $minlevel] +# - [$tag, $maxspoil, $minlevel, $exclude_lies] +# Normalizes to the latter two. sub _validate_tag { $_[0] = [$_[0],0,0] if ref $_[0] ne 'ARRAY'; # just a tag id my $v = tuwf->compile({ vndbid => 'g' })->validate($_[0][0]); @@ -456,29 +548,51 @@ sub _validate_tag { $_[0][0] = $v->data; if($_[0]->@* == 2) { # compact form return 0 if !defined $_[0][1] || ref $_[0][1] || $_[0][1] !~ /^[0-9]+$/; - ($_[0][1],$_[0][2]) = ($_[0][1]%3, int($_[0][1]/3)/5); + ($_[0][1],$_[0][2],$_[0][3]) = ($_[0][1]%3, int($_[0][1]%(3*16)/3)/5, int($_[0][1]/3/16) == 1 ? 1 : 0); } # normalized form - return 0 if $_[0]->@* != 3; + return 0 if $_[0]->@* < 3 || $_[0]->@* > 4; return 0 if !defined $_[0][1] || ref $_[0][1] || $_[0][1] !~ /^[0-2]$/; return 0 if !defined $_[0][2] || ref $_[0][2] || $_[0][2] !~ /^(?:[0-2](?:\.[0-9]+)?|3(?:\.0+)?)$/; + $_[0][1] *= 1; + $_[0][2] *= 1; + if ($_[0]->@* == 4) { + return 0 if !defined $_[0][3] || ref $_[0][3] || $_[0][3] !~ /^[0-1]$/; + $_[0][3] *= 1; + pop $_[0]->@* if !$_[0][3]; + } 1 } +sub _compact_tag { my $id = ($_->[0] =~ s/^g//r)*1; $_->[1] == 0 && $_->[2] == 0 && !$_->[3] ? $id : [ $id, ($_->[3]?16*3:0) + int($_->[2]*5)*3 + $_->[1] ] } +sub _compact_trait { my $id = ($_->[0] =~ s/^i//r)*1; $_->[1] == 0 && !$_->[2] ? $id : [ $id, ($_->[2]?3:0) + $_->[1] ] } -# Accepts either $trait or [$trait, $maxspoil]. Normalizes to the latter. +# Accepts either: +# - $trait +# - [$trait, $exclude_lies*3 + $maxspoil] (compact form) +# - [$trait, $maxspoil] +# - [$trait, $maxspoil, $exclude_lies] +# Normalizes to the latter two. sub _validate_trait { $_[0] = [$_[0],0] if ref $_[0] ne 'ARRAY'; # just a trait id my $v = tuwf->compile({ vndbid => 'i' })->validate($_[0][0]); return 0 if $v->err; $_[0][0] = $v->data; - $_[0]->@* == 2 && defined $_[0][1] && !ref $_[0][1] && $_[0][1] =~ /^[0-2]$/ + return 0 if !defined $_[0][1] || ref $_[0][1] || $_[0][1] !~ /^[0-9]+$/; + ($_[0][1], $_[0][2]) = ($_[0][1]%3, int($_[0][1]/3) == 1 ? 1 : 0) if $_[0]->@* == 2; + return 0 if $_[0]->@* != 3; + return 0 if $_[0][1] > 2; + return 0 if !defined $_[0][2] || ref $_[0][2] || $_[0][2] !~ /^[01]$/; + $_[0][1] *= 1; + $_[0][2] *= 1; + pop $_[0]->@* if $_[0]->@* == 3 && !$_[0][2]; + 1 } # Accepts either $label or [$uid, $label]. Normalizes to the latter. $label=0 is used for 'Unlabeled'. sub _validate_label { - $_[0] = [auth->uid(), $_[0]] if ref $_[0] ne 'ARRAY'; + $_[0] = [tuwf->req->{advsearch_uid}||auth->uid(), $_[0]] if ref $_[0] ne 'ARRAY'; my $v = tuwf->compile({ vndbid => 'u' })->validate($_[0][0]); return 0 if $v->err; $_[0][0] = $v->data; @@ -524,25 +638,27 @@ sub _validate_adv { return { msg => 'Invalid compact encoded form', character_index => $i } if !($_[0] = _dec_query($v, \$i)); return { msg => 'Trailing garbage' } if $i != length $v; } + if(ref $_[0] eq 'ARRAY' && $_[0]->@* == 0) { + $_[0] = bless {type=>$t}, __PACKAGE__; + return 1; + } my $v = _validate($t, @_); $_[0] = bless { type => $t, query => $_[0] }, __PACKAGE__ if $v; $v } + # 'advsearch' validation, accepts either a compact encoded string, JSON string or an already decoded array. -TUWF::set('custom_validations')->{advsearch} = sub { my($t) = @_; +{ required => 0, type => 'any', default => bless({type=>$t}, __PACKAGE__), func => sub { _validate_adv $t, @_ } } }; +TUWF::set('custom_validations')->{advsearch} = sub { my($t) = @_; +{ type => 'any', default => bless({type=>$t}, __PACKAGE__), func => sub { _validate_adv $t, @_ } } }; # 'advsearch_err' validation; Same as the 'advsearch' validation except it never throws an error. # If the validation failed, this will log a warning and return an empty query that will cause elm_() to display a warning message. TUWF::set('custom_validations')->{advsearch_err} = sub { my ($t) = @_; - +{ required => 0, type => 'any', default => bless({type=>$t}, __PACKAGE__), func => sub { + +{ type => 'any', default => bless({type=>$t}, __PACKAGE__), func => sub { my $r = _validate_adv $t, @_; - if(!$r || ref $r eq 'HASH') { - warn "advsearch validation failed\n"; - $_[0] = bless {type=>$t,error=>1}, __PACKAGE__; - } + $_[0] = bless {type=>$t,error=>1}, __PACKAGE__ if !$r || ref $r eq 'HASH'; 1 } } }; @@ -573,34 +689,46 @@ sub _canon { } -# sql_list function for tags +# returns an sql_list function for tags sub _sql_where_tag { - my($neg, $all, $val) = @_; - my %f; # spoiler -> rating -> list - my @l; - push $f{$_->[1]}{$_->[2]}->@*, $_->[0] for @$val; - for my $s (keys %f) { - for my $r (keys $f{$s}->%*) { - push @l, sql_and - $s < 2 ? sql('spoiler <=', \$s) : (), - $r > 0 ? sql('rating >=', \$r) : (), - sql('tag IN', $f{$s}{$r}); + my($table) = @_; + sub { + my($neg, $all, $val) = @_; + my %f; # spoiler -> rating -> lie -> list + my @l; + push $f{$_->[1]*1}{$_->[2]*1}{$_->[3]?1:''}->@*, $_->[0] for @$val; + for my $s (keys %f) { + for my $r (keys $f{$s}->%*) { + for my $l (keys $f{$s}{$r}->%*) { + push @l, sql_and + $s < 2 ? sql('spoiler <=', \$s) : (), + $r > 0 ? sql('rating >=', \$r) : (), + $l ? ('NOT lie') : (), + sql('tag IN', $f{$s}{$r}{$l}); + } + } } + sql 'v.id', $neg ? 'NOT' : (), 'IN(SELECT vid FROM', $table, 'WHERE', sql_or(@l), $all && @$val > 1 ? ('GROUP BY vid HAVING COUNT(tag) =', \scalar @$val) : (), ')' } - sql 'v.id', $neg ? 'NOT' : (), 'IN(SELECT vid FROM tags_vn_inherit WHERE', sql_or(@l), $all && @$val > 1 ? ('GROUP BY vid HAVING COUNT(tag) =', \scalar @$val) : (), ')' } sub _sql_where_trait { - my($neg, $all, $val) = @_; - my %f; # spoiler -> list - my @l; - push $f{$_->[1]}->@*, $_->[0] for @$val; - for my $s (keys %f) { - push @l, sql_and - $s < 2 ? sql('spoil <=', \$s) : (), - sql('tid IN', $f{$s}); + my($table, $cid) = @_; + sub { + my($neg, $all, $val) = @_; + my %f; # spoiler -> list + my @l; + push $f{$_->[1]*1}{$_->[2]?1:''}->@*, $_->[0] for @$val; + for my $s (keys %f) { + for my $l (keys $f{$s}->%*) { + push @l, sql_and + $s < 2 ? sql('spoil <=', \$s) : (), + $l ? ('NOT lie') : (), + sql('tid IN', $f{$s}{$l}); + } + } + sql 'c.id', $neg ? 'NOT' : (), 'IN(SELECT', $cid, 'FROM', $table, 'WHERE', sql_or(@l), $all && @$val > 1 ? ('GROUP BY', $cid, 'HAVING COUNT(tid) =', \scalar @$val) : (), ')' } - sql 'c.id', $neg ? 'NOT' : (), 'IN(SELECT cid FROM traits_chars WHERE', sql_or(@l), $all && @$val > 1 ? ('GROUP BY cid HAVING COUNT(tid) =', \scalar @$val) : (), ')' } @@ -608,27 +736,32 @@ sub _sql_where_trait { sub _sql_where_label { my($neg, $all, $val) = @_; my $uid = $val->[0][0]; + require VNWeb::ULists::Lib; my $own = VNWeb::ULists::Lib::ulists_own($uid); my @lbl = map $_->[1], @$val; # Unlabeled if($lbl[0] == 0) { return '1=0' if !$own; - my $onlist = sql 'EXISTS(SELECT 1 FROM ulist_vns WHERE vid = v.id AND uid =', \$uid, ')'; - my $haslbl = sql 'EXISTS(SELECT 1 FROM ulist_vns_labels WHERE vid = v.id AND uid =', \$uid, 'AND lbl <>', \7, ')'; - return $neg ? sql 'NOT', $onlist, 'OR', $haslbl - : sql $onlist,' AND NOT', $haslbl; + return sql $neg ? 'NOT' : (), 'EXISTS(SELECT 1 FROM ulist_vns WHERE vid = v.id AND uid =', \$uid, "AND labels IN('{}','{7}'))"; } - # Simple, stupid and safe: Don't attempt to query anything if there's a private label. - # This can be improved to allow querying/displaying results that *are* visible, but it's more complex and not that often needed. if(!$own) { - tuwf->req->{lblvis}{$uid} ||= { map +($_->{id},1), tuwf->dbAlli('SELECT id FROM ulist_labels WHERE NOT private AND uid =', \$uid)->@* }; + # Label 7 can always be queried, do a lookup for the rest. + tuwf->req->{lblvis}{$uid} ||= { 7, 1, map +($_->{id},1), tuwf->dbAlli('SELECT id FROM ulist_labels WHERE NOT private AND uid =', \$uid)->@* }; my $vis = tuwf->req->{lblvis}{$uid}; - return '1=0' if grep !$vis->{$_}, @lbl; + return $neg ? '1=1' : '1=0' if $all && grep !$vis->{$_}, @lbl; # AND query but one label is private -> no match + @lbl = grep $vis->{$_}, @lbl; + return $neg ? '1=1' : '1=0' if !@lbl; # All requested labels are private -> no match } - sql 'v.id', $neg ? 'NOT' : (), 'IN(SELECT vid FROM ulist_vns_labels WHERE uid =', \$uid, 'AND lbl IN', \@lbl, $all && @lbl > 1 ? ('GROUP BY vid HAVING COUNT(lbl) =', \scalar @lbl) : (), ')' + sql 'v.id', $neg ? 'NOT' : (), 'IN( + SELECT vid + FROM ulist_vns + WHERE uid =', \$uid, + 'AND labels', $all ? '@>' : '&&', sql_array(@lbl), '::smallint[]', + $own ? () : 'AND NOT c_private', + ')' } @@ -714,7 +847,7 @@ sub _extract_ids { } else { my $f = $FIELDS{$t}{$q->[0]}; $ids->{$q->[2]} = 1 if $f->{vndbid}; - $ids->{"anime$q->[2]"} = 1 if $q->[0] eq 'anime-id'; + $ids->{"anime$q->[2]"} = 1 if $q->[0] eq 'anime_id'; $ids->{$q->[2][0]} = 1 if ref $f->{value} && ref $q->[2] eq 'ARRAY'; # Ugly heuristic, may have false positives _extract_ids($f->{value}, $q->[2], $ids) if !ref $f->{value}; } @@ -729,17 +862,17 @@ sub elm_search_query { _extract_ids($self->{type}, $self->{query}, \%ids) if $self->{query}; $o{producers} = [ map +{id => $_}, grep /^p/, keys %ids ]; - enrich_merge id => 'SELECT id, name, original, hidden FROM producers WHERE id IN', $o{producers}; + enrich_merge id => sql('SELECT id, title[1+1] AS name, title[1+1+1+1] AS altname FROM', VNWeb::TitlePrefs::producerst(), 'p WHERE id IN'), $o{producers}; $o{staff} = [ map +{id => $_}, grep /^s/, keys %ids ]; - enrich_merge id => 'SELECT s.id, sa.aid, sa.name, sa.original FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE s.id IN', $o{staff}; + enrich_merge id => sql('SELECT id, lang, aid, title[1+1], title[1+1+1+1] AS alttitle FROM', VNWeb::TitlePrefs::staff_aliast(), 's WHERE aid = main AND id IN'), $o{staff}; $o{tags} = [ map +{id => $_}, grep /^g/, keys %ids ]; enrich_merge id => 'SELECT id, name, searchable, applicable, hidden, locked FROM tags WHERE id IN', $o{tags}; $o{traits} = [ map +{id => $_}, grep /^i/, keys %ids ]; enrich_merge id => 'SELECT t.id, t.name, t.searchable, t.applicable, t.defaultspoil, t.hidden, t.locked, g.id AS group_id, g.name AS group_name - FROM traits t LEFT JOIN traits g ON g.id = t.group WHERE t.id IN', $o{traits}; + FROM traits t LEFT JOIN traits g ON g.id = t.gid WHERE t.id IN', $o{traits}; $o{anime} = [ map +{id => $_=~s/^anime//rg}, grep /^anime/, keys %ids ]; enrich_merge id => 'SELECT id, title_romaji AS title, title_kanji AS original FROM anime WHERE id IN', $o{anime}; @@ -751,11 +884,11 @@ sub elm_search_query { sub elm_ { - my($self) = @_; + my($self, $count, $time) = @_; # TODO: labels can be lazily loaded to reduce page weight state $schema ||= tuwf->compile({ type => 'hash', keys => { - uid => { vndbid => 'u', required => 0 }, + uid => { vndbid => 'u', default => undef }, labels => { aoh => { id => { uint => 1 }, label => {} } }, defaultSpoil => { uint => 1 }, saved => { aoh => { name => {}, query => {} } }, @@ -770,6 +903,21 @@ sub elm_ { error => $self->{error}?1:0, query => $self->elm_search_query(), }; + + if (@_ > 1) { + p_ class => 'center', sub { + input_ type => 'submit', value => 'Search'; + txt_ sprintf ' %d result%s in %.3fs', $count, $count == 1 ? '' : 's', $time if defined $count; + }; + div_ class => 'warning', sub { + h2_ 'ERROR: Query timed out.'; + p_ q{ + This usually happens when your combination of filters is too complex for the server to handle. + This may also happen when the server is overloaded with other work, but that's much less common. + You can adjust your filters or try again later. + }; + } if !defined $count; + } } @@ -781,6 +929,27 @@ sub query_encode { } +sub extract_searchquery { + my($self) = @_; + my $q = $self->{query}; + return ($self) if !$q; + return (bless({type => $self->{type}}, __PACKAGE__), $q->[2]) if @$q == 3 && $q->[1] eq '=' && ref $q->[2] eq 'VNWeb::Validate::SearchQuery'; + if($q->[0] eq 'and') { + my(@newq, $s); + for (@{$q}[1..$#$q]) { + if(@$_ == 3 && $_->[1] eq '=' && ref $_->[2] eq 'VNWeb::Validate::SearchQuery') { + return ($self) if $s; + $s = $_->[2]; + } else { + push @newq, $_; + } + } + return (bless({type => $self->{type}, query => ['and',@newq]}, __PACKAGE__), $s) if $s; + } + return ($self); +} + + # Returns the saved default query for the current user, or an empty query if none has been set. sub advsearch_default { my($t) = @_; |