summaryrefslogtreecommitdiff
path: root/lib/VNDB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2010-11-21 16:15:26 +0100
committerYorhel <git@yorhel.nl>2010-11-21 16:15:26 +0100
commita79312bb341bdfbdf14e7aeb3d1ffa6548604960 (patch)
tree9a5830431a5a980295f84d2901306e87f28ece12 /lib/VNDB
parent66e8cbc808775356ea5beeea2f53ab6958ab6c2e (diff)
Implemented the server side of the new release filter selector
There's no validation of the filter string yet, and somehow I don't feel like adding that; it's a lot of code and there's nothing to protect - the values are inserted using parameters into a SELECT query, the worst thing that could happen is the user receiving a 500. Also, I've started using the perl '//=' operator, which was added in 5.10. This removes support for older perls.
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/Releases.pm58
-rw-r--r--lib/VNDB/Func.pm28
-rw-r--r--lib/VNDB/Handler/Misc.pm4
-rw-r--r--lib/VNDB/Handler/Releases.pm84
4 files changed, 106 insertions, 68 deletions
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 01ca6b44..be4fc50e 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -10,8 +10,8 @@ use VNDB::Func 'gtintype';
our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|;
-# Options: id vid pid rev unreleased page results what date media sort reverse
-# platforms languages type minage search resolutions freeware doujin
+# Options: id vid pid rev unreleased page results what med sort reverse
+# date_before date_after plat lang type minage search resolution freeware doujin
# What: extended changes vn producers platforms media
# Sort: title released minage
sub dbReleaseGet {
@@ -21,33 +21,37 @@ sub dbReleaseGet {
$o{what} ||= '';
my @where = (
- !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (),
- $o{id} ? ( 'r.id = ?' => $o{id} ) : (),
- $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (),
- $o{vid} ? ( 'rv.vid = ?' => $o{vid} ) : (),
- $o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (),
- $o{patch} ? ( 'rr.patch = ?' => $o{patch} == 1 ? 1 : 0) : (),
- $o{freeware} ? ( 'rr.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (),
- $o{doujin} ? ( 'rr.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (),
- defined $o{unreleased} ? (
- q|rr.released !s ?| => [ $o{unreleased} ? '>' : '<=', strftime('%Y%m%d', gmtime) ] ) : (),
- $o{date} ? (
- '(rr.released >= ? AND rr.released <= ?)' => [ $o{date}[0], $o{date}[1] ] ) : (),
- $o{languages} ? (
- 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))', => [ $o{languages} ] ) : (),
- $o{platforms} ? (
- #'EXISTS(SELECT 1 FROM releases_platforms rp WHERE rp.rid = rr.id AND rp.platform IN(!l))' => [ $o{platforms} ] ) : (),
- 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' => [ $o{platforms} ] ) : (),
- defined $o{type} ? (
- 'rr.type = ?' => $o{type} ) : (),
- $o{minage} ? (
- 'rr.minage !s ?' => [ $o{minage}[0] ? '<=' : '>=', $o{minage}[1] ] ) : (),
- $o{media} ? (
- 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' => [ $o{media} ] ) : (),
- $o{resolutions} ? (
- 'rr.resolution IN(!l)' => [ $o{resolutions} ] ) : (),
+ !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (),
+ $o{id} ? ( 'r.id = ?' => $o{id} ) : (),
+ $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (),
+ $o{vid} ? ( 'rv.vid = ?' => $o{vid} ) : (),
+ $o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (),
+ defined $o{patch} ? ( 'rr.patch = ?' => $o{patch} == 1 ? 1 : 0) : (),
+ defined $o{freeware} ? ( 'rr.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (),
+ defined $o{doujin} ? ( 'rr.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (),
+ defined $o{type} ? ( 'rr.type = ?' => $o{type} ) : (),
+ defined $o{date_before} ? ( 'rr.released <= ?' => $o{date_before} ) : (),
+ defined $o{date_after} ? ( 'rr.released >= ?' => $o{date_after} ) : (),
+ defined $o{resolution} ? ( 'rr.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (),
+ defined $o{unreleased} ? ( 'rr.released !s ?' => [ $o{unreleased} ? '>' : '<=', strftime('%Y%m%d', gmtime) ] ) : (),
+ $o{lang} ? (
+ 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))', => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
+ $o{plat} ? (
+ 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' => [ ref $o{plat} ? $o{plat} : [ $o{plat} ] ] ) : (),
+ $o{med} ? (
+ 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' => [ ref $o{med} ? $o{med} : [ $o{med} ] ] ) : (),
);
+ # TODO: don't allow NULL for rr.minage after all, since this could be a lot easier...
+ if(exists $o{minage}) {
+ my @m = ref $o{minage} ? @{$o{minage}} : ($o{minage});
+ my @w = (
+ grep(!defined $_ || $_ == -1, @m) ? 'rr.minage IS NULL' : (),
+ grep(defined $_ && $_ != -1, @m) ? 'rr.minage IN(!s)' : ()
+ );
+ push @where, '('.join(' OR ', @w).')', [ grep defined $_ && $_ != -1, @m ];
+ }
+
if($o{search}) {
for (split /[ -,._]/, $o{search}) {
s/%//g;
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index f3f9d009..a693e1de 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -7,7 +7,13 @@ use YAWF ':html';
use Exporter 'import';
use POSIX 'strftime', 'ceil', 'floor';
use VNDBUtil;
-our @EXPORT = (@VNDBUtil::EXPORT, qw| liststat clearfloat cssicon tagscore mt minage |);
+our @EXPORT = (@VNDBUtil::EXPORT, qw| liststat clearfloat cssicon tagscore mt minage fil_parse fil_serialize |);
+
+
+# three ways to represent the same information
+our $fil_escape = '_ !"#$%&\'()*+,-./:;<=>?@[\]^`{}~';
+our @fil_escape = split //, $fil_escape;
+our %fil_escape = map +($fil_escape[$_], $_), 0..$#fil_escape;
# Argument: hashref with rstat and vstat
@@ -89,5 +95,25 @@ sub minage {
}
+sub fil_parse {
+ return { map {
+ my($f, $v) = split /-/, $_, 2;
+ my @v = split /,/, $v;
+ s/_([0-9]{2})/$1 > $#fil_escape ? '' : $fil_escape[$1]/eg for(@v);
+ $f => @v > 1 ? \@v : @v
+ } split /\./, scalar shift };
+}
+
+
+sub fil_serialize {
+ my $fil = shift;
+ my $e = qr/([\Q$fil_escape\E])/;
+ return join '.', map {
+ my @v = ref $fil->{$_} ? @{$fil->{$_}} : ($fil->{$_});
+ s/$e/_$fil_escape{$1}/g for(@v);
+ $_.'-'.join ',', @v
+ } keys %$fil;
+}
+
1;
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 815f308f..1a2df93b 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -138,7 +138,7 @@ sub homepage {
# Upcoming releases
td;
h1;
- a href => strftime('/r?mi=%Y%m%d;o=a;s=released', gmtime), mt '_home_upcoming';
+ a href => strftime('/r?fil=date_after-%Y%m%d;o=a;s=released', gmtime), mt '_home_upcoming';
end;
my $upcoming = $self->dbReleaseGet(results => 10, unreleased => 1, what => 'platforms');
ul;
@@ -158,7 +158,7 @@ sub homepage {
# Just released
td;
h1;
- a href => strftime('/r?ma=%Y%m%d;o=d;s=released', gmtime), mt '_home_justreleased';
+ a href => strftime('/r?fil=date_before-%Y%m%d;o=d;s=released', gmtime), mt '_home_justreleased';
end;
my $justrel = $self->dbReleaseGet(results => 10, sort => 'released', reverse => 1, unreleased => 0, what => 'platforms');
ul;
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 735491f2..63ea0922 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -485,51 +485,27 @@ sub browse {
my $f = $self->formValidate(
{ name => 'p', required => 0, default => 1, template => 'int' },
- { name => 's', required => 0, default => 'title', enum => [qw|released minage title|] },
{ name => 'o', required => 0, default => 'a', enum => ['a', 'd'] },
{ name => 'q', required => 0, default => '', maxlength => 500 },
- { name => 'ln', required => 0, multi => 1, default => '', enum => $self->{languages} },
- { name => 'pl', required => 0, multi => 1, default => '', enum => $self->{platforms} },
- { name => 'me', required => 0, multi => 1, default => '', enum => [ keys %{$self->{media}} ] },
- { name => 'tp', required => 0, default => '', enum => [ '', @{$self->{release_types}} ] },
- { name => 'pa', required => 0, default => 0, enum => [ 0..2 ] },
- { name => 'fw', required => 0, default => 0, enum => [ 0..2 ] },
- { name => 'do', required => 0, default => 0, enum => [ 0..2 ] },
- { name => 'ma_m', required => 0, default => 0, enum => [ 0, 1 ] },
- { name => 'ma_a', required => 0, default => 0, enum => [ grep defined($_), @{$self->{age_ratings}} ] },
- { name => 'mi', required => 0, default => 0, template => 'int' },
- { name => 'ma', required => 0, default => 99999999, template => 'int' },
- { name => 're', required => 0, multi => 1, default => 0, enum => [ 1..$#{$self->{resolutions}} ] },
+ { name => 's', required => 0, default => 'title', enum => [qw|released minage title|] },
+ { name => 'fil',required => 0, default => '' },
);
return 404 if $f->{_err};
- my @filters = (
- $f->{mi} > 0 || $f->{ma} < 99990000 ? (date => [ $f->{mi}, $f->{ma} ]) : (),
- $f->{q} ? (search => $f->{q}) : (),
- $f->{pl}[0] ? (platforms => $f->{pl}) : (),
- $f->{ln}[0] ? (languages => $f->{ln}) : (),
- $f->{me}[0] ? (media => $f->{me}) : (),
- $f->{re}[0] ? (resolutions => $f->{re} ) : (),
- $f->{tp} ? (type => $f->{tp}) : (),
- $f->{ma_a} || $f->{ma_m} ? (minage => [$f->{ma_m}, $f->{ma_a}]) : (),
- $f->{pa} ? (patch => $f->{pa}) : (),
- $f->{fw} ? (freeware => $f->{fw}) : (),
- $f->{do} ? (doujin => $f->{do}) : (),
- );
- my($list, $np) = !@filters ? ([], 0) : $self->dbReleaseGet(
+ # TODO: validate the filter string?
+ my $fil = fil_parse $f->{fil};
+ _fil_compat($self, $fil);
+ $f->{fil} = fil_serialize($fil);
+
+ my($list, $np) = !$f->{q} && !keys %$fil ? ([], 0) : $self->dbReleaseGet(
sort => $f->{s}, reverse => $f->{o} eq 'd',
page => $f->{p},
results => 50,
what => 'platforms',
- @filters,
+ $f->{q} ? ( search => $f->{q} ) : (),
+ %$fil
);
- my $url = "/r?tp=$f->{tp};pa=$f->{pa};ma_m=$f->{ma_m};ma_a=$f->{ma_a};q=$f->{q};mi=$f->{mi};ma=$f->{ma};do=$f->{do};fw=$f->{fw}";
- $_&&($url .= ";ln=$_") for @{$f->{ln}};
- $_&&($url .= ";pl=$_") for @{$f->{pl}};
- $_&&($url .= ";re=$_") for @{$f->{re}};
- $_&&($url .= ";me=$_") for @{$f->{me}};
-
$self->htmlHeader(title => mt('_rbrowse_title'));
form method => 'get', action => '/r', 'accept-charset' => 'UTF-8';
@@ -539,7 +515,7 @@ sub browse {
a id => 'filselect', href => '#';
lit '<i>&#9656;</i> '.mt('_rbrowse_filters').'<i></i>';
end;
- input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => '';
+ input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil};
end;
$self->htmlBrowse(
@@ -547,8 +523,8 @@ sub browse {
items => $list,
options => $f,
nextpage => $np,
- pageurl => "$url;s=$f->{s};o=$f->{o}",
- sorturl => $url,
+ pageurl => "/r?q=$f->{q};fil=$f->{fil};s=$f->{s};o=$f->{o}",
+ sorturl => "/r?q=$f->{q};fil=$f->{fil}",
header => [
[ mt('_rbrowse_col_released'), 'released' ],
[ mt('_rbrowse_col_minage'), 'minage' ],
@@ -574,7 +550,7 @@ sub browse {
end;
},
) if @$list;
- if(@filters && !@$list) {
+ if(($f->{q} || keys %$fil) && !@$list) {
div class => 'mainbox';
h1 mt '_rbrowse_noresults_title';
div class => 'notice';
@@ -586,5 +562,37 @@ sub browse {
}
+# provide compatibility with old filter URLs
+sub _fil_compat {
+ my($self, $fil) = @_;
+ my $f = $self->formValidate(
+ { name => 'ln', required => 0, multi => 1, default => '', enum => $self->{languages} },
+ { name => 'pl', required => 0, multi => 1, default => '', enum => $self->{platforms} },
+ { name => 'me', required => 0, multi => 1, default => '', enum => [ keys %{$self->{media}} ] },
+ { name => 'tp', required => 0, default => '', enum => [ '', @{$self->{release_types}} ] },
+ { name => 'pa', required => 0, default => 0, enum => [ 0..2 ] },
+ { name => 'fw', required => 0, default => 0, enum => [ 0..2 ] },
+ { name => 'do', required => 0, default => 0, enum => [ 0..2 ] },
+ { name => 'ma_m', required => 0, default => 0, enum => [ 0, 1 ] },
+ { name => 'ma_a', required => 0, default => 0, enum => [ grep defined($_), @{$self->{age_ratings}} ] },
+ { name => 'mi', required => 0, default => 0, template => 'int' },
+ { name => 'ma', required => 0, default => 99999999, template => 'int' },
+ { name => 're', required => 0, multi => 1, default => 0, enum => [ 1..$#{$self->{resolutions}} ] },
+ );
+ return if $f->{_err};
+ $fil->{minage} //= [ grep defined($_) && $f->{ma_m} ? $f->{ma_a} >= $_ : $f->{ma_a} <= $_, @{$self->{age_ratings}} ] if $f->{ma_a} || $f->{ma_m};
+ $fil->{date_after} //= $f->{mi} if $f->{mi};
+ $fil->{date_before} //= $f->{ma} if $f->{ma} < 99990000;
+ $fil->{plat} //= $f->{pl} if $f->{pl}[0];
+ $fil->{lang} //= $f->{ln} if $f->{ln}[0];
+ $fil->{med} //= $f->{me} if $f->{me}[0];
+ $fil->{resolution} //= $f->{re} if $f->{re}[0];
+ $fil->{type} //= $f->{tp} if $f->{tp};
+ $fil->{patch} //= $f->{pa} == 2 ? 0 : 1 if $f->{pa};
+ $fil->{freeware} //= $f->{fw} == 2 ? 0 : 1 if $f->{fw};
+ $fil->{doujin} //= $f->{do} == 2 ? 0 : 1 if $f->{do};
+}
+
+
1;