summaryrefslogtreecommitdiff
path: root/lib
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-12-21 16:14:11 +0100
committerYorhel <git@yorhel.nl>2010-12-21 16:14:11 +0100
commite15869b9c0c124d67f5d8ac85ffefad383642672 (patch)
tree53a3fc52e27480792b2f0ba5ab9b9ffdbbbdac6d /lib
parente77484d81461c322656615759ba77ea25713a8d6 (diff)
Pass VN tag filters by ID rather than name
This makes the UI slightly uglier and less intuitive. I'll see if I can find a way around that. This update is required for the permanent browsing filters to be fast and reliable.
Diffstat (limited to 'lib')
-rw-r--r--lib/VNDB/DB/Tags.pm2
-rw-r--r--lib/VNDB/DB/VN.pm24
-rw-r--r--lib/VNDB/Handler/Tags.pm16
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm40
4 files changed, 39 insertions, 43 deletions
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 4a87713b..b3e16960 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -24,7 +24,7 @@ sub dbTagGet {
my %where = (
$o{id} ? (
- 't.id = ?' => $o{id} ) : (),
+ 't.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
$o{noid} ? (
't.id <> ?' => $o{noid} ) : (),
$o{name} ? (
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index d25a5796..b01d3328 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -10,7 +10,8 @@ use Encode 'decode_utf8';
our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
-# Options: id, rev, char, search, length, lang, olang, plat, tags_include, tags_exclude, hasani, results, page, what, sort, reverse
+# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
+# hasani, results, page, what, sort, reverse
# What: extended anime relations screenshots relgraph rating ranking changes
# Sort: id rel pop rating title tagscore rand
sub dbVNGet {
@@ -19,6 +20,12 @@ sub dbVNGet {
$o{page} ||= 1;
$o{what} ||= '';
$o{sort} ||= 'title';
+ $o{tagspoil} //= 2;
+
+ # user input that is literally added to the query should be checked...
+ die "Invalid input for tagspoil or tag_inc at dbVNGet()\n" if
+ grep !defined($_) || $_!~/^\d+$/, $o{tagspoil},
+ !$o{tag_inc} ? () : (ref($o{tag_inc}) ? @{$o{tag_inc}} : $o{tag_inc});
my @where = (
$o{id} ? (
@@ -39,19 +46,18 @@ sub dbVNGet {
'('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", ref $o{plat} ? @{$o{plat}} : $o{plat}).')' => 1 ) : (),
defined $o{hasani} ? (
'!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
- $o{tags_include} && @{$o{tags_include}} ? (
+ $o{tag_inc} ? (
'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
- [ $o{tags_include}[1], $o{tags_include}[0], $#{$o{tags_include}[1]}+1 ]
- ) : (),
- $o{tags_exclude} && @{$o{tags_exclude}} ? (
- 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (),
+ [ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (),
+ $o{tag_exc} ? (
+ 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ ref $o{tag_exc} ? $o{tag_exc} : [$o{tag_exc}] ] ) : (),
$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 ) : (),
# optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
- $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort)$/, keys %o) ? (
+ $o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort|tagspoil)$/, keys %o) ? (
sprintf 'v.id IN(SELECT floor(random() * last_value)::integer
FROM generate_series(1,20), (SELECT last_value FROM vn_id_seq) s1
LIMIT 20)' ) : (),
@@ -69,7 +75,7 @@ sub dbVNGet {
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
);
- my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]};
+ my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc};
my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages
qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid',
$o{what} =~ /extended/ ? (
@@ -84,7 +90,7 @@ sub dbVNGet {
) : (),
# TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000)
$tag_ids ?
- qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvh.vid) AS tagscore| : (),
+ qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tagspoil} GROUP BY tvh.vid) AS tagscore| : (),
);
my $order = sprintf {
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index 0567bff6..6e373d04 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -44,7 +44,8 @@ sub tagpage {
results => 50,
page => $f->{p},
sort => $f->{s}, reverse => $f->{o} eq 'd',
- tags_include => [ $f->{m}, [$tag ]],
+ tagspoil => $f->{m},
+ tag_inc => $tag,
);
my $title = mt '_tagp_title', $t->{meta}?0:1, $t->{name};
@@ -668,19 +669,22 @@ sub fulltree {
sub tagxml {
my $self = shift;
- my $q = $self->formValidate({ name => 'q', maxlength => 500 });
- return 404 if $q->{_err};
- $q = $q->{q};
+ my $f = $self->formValidate(
+ { name => 'q', required => 0, maxlength => 500 },
+ { name => 'id', required => 0, multi => 1, template => 'int' },
+ );
+ return 404 if $f->{_err} || (!$f->{q} && !$f->{id} && !$f->{id}[0]);
my($list, $np) = $self->dbTagGet(
- $q =~ /^g([1-9]\d*)/ ? (id => $1) : $q =~ /^name:(.+)$/ ? (name => $1) : (search => $q),
+ !$f->{q} ? () : $f->{q} =~ /^g([1-9]\d*)/ ? (id => $1) : $f->{q} =~ /^name:(.+)$/ ? (name => $1) : (search => $f->{q}),
+ $f->{id} && $f->{id}[0] ? (id => $f->{id}) : (),
results => 15,
page => 1,
);
$self->resHeader('Content-type' => 'text/xml; charset=UTF-8');
xml;
- tag 'tags', more => $np ? 'yes' : 'no', query => $q;
+ tag 'tags', more => $np ? 'yes' : 'no', $f->{q} ? (query => $f->{q}) : ();
for(@$list) {
tag 'item', id => $_->{id}, meta => $_->{meta} ? 'yes' : 'no', state => $_->{state}, $_->{name};
}
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index 8e30da99..25166717 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -25,7 +25,7 @@ sub list {
);
return 404 if $f->{_err};
$f->{q} ||= $f->{sq};
- my $fil = fil_parse $f->{fil}, qw|length hasani taginc tagexc tagspoil lang olang plat|;
+ my $fil = fil_parse $f->{fil}, qw|length hasani tag_inc tag_exc taginc tagexc tagspoil lang olang plat|;
_fil_compat($self, $fil);
if($f->{q}) {
@@ -39,20 +39,7 @@ sub list {
}
$f->{fil} = fil_serialize $fil;
- # TODO: this should be moved to dbVNGet() in order for savable VN filters to be useful
- my @ignored;
- my $tagfind = sub {
- return map {
- my $i = $self->dbTagGet(name => $_)->[0];
- push @ignored, [$_, 0] if !$i;
- push @ignored, [$_, 1] if $i && $i->{meta};
- $i && !$i->{meta} ? $i->{id} : ();
- } grep $_, ref $_[0] ? @{$_[0]} : ($_[0]||'')
- };
- my @ti = $tagfind->(delete $fil->{taginc});
- my @te = $tagfind->(delete $fil->{tagexc});
-
- $f->{s} = 'title' if !@ti && $f->{s} eq 'tagscore';
+ $f->{s} = 'title' if !$fil->{tag_inc} && $f->{s} eq 'tagscore';
$f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o};
my($list, $np) = $self->dbVNGet(
@@ -62,8 +49,6 @@ sub list {
results => 50,
page => $f->{p},
sort => $f->{s}, reverse => $f->{o} eq 'd',
- @ti ? (tags_include => [ delete $fil->{tagspoil}, \@ti ]) : (),
- @te ? (tags_exclude => \@te) : (),
%$fil
);
@@ -82,15 +67,6 @@ sub list {
}
end;
- if(@ignored) {
- div class => 'warning';
- h2 mt '_vnbrowse_tagign_title';
- ul;
- li $_->[0].' ('.mt('_vnbrowse_tagign_'.($_->[1]?'meta':'notfound')).')' for @ignored;
- end;
- end;
- }
-
a id => 'filselect', href => '#v';
lit '<i>&#9656;</i> '.mt('_rbrowse_filters').'<i></i>'; # TODO: it's not *r*browse
end;
@@ -98,7 +74,7 @@ sub list {
end;
end; # /form
- $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$f->{q};fil=$f->{fil}", scalar @ti);
+ $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$f->{q};fil=$f->{fil}", $fil->{tag_inc});
$self->htmlFooter;
}
@@ -117,6 +93,16 @@ sub _fil_compat {
$fil->{taginc} //= $f->{ti} if $f->{ti};
$fil->{tagexc} //= $f->{te} if $f->{te};
$fil->{tagspoil} //= $f->{sp};
+
+ # older tag specification (by name rather than ID)
+ my $tagfind = sub {
+ return map {
+ my $i = $self->dbTagGet(name => $_)->[0];
+ $i && !$i->{meta} ? $i->{id} : ();
+ } grep $_, ref $_[0] ? @{$_[0]} : ($_[0]||'')
+ };
+ $fil->{tag_inc} //= [ $tagfind->(delete $fil->{taginc}) ] if $fil->{taginc};
+ $fil->{tag_exc} //= [ $tagfind->(delete $fil->{tagexc}) ] if $fil->{tagexc};
}