From a79312bb341bdfbdf14e7aeb3d1ffa6548604960 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sun, 21 Nov 2010 16:15:26 +0100 Subject: 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. --- lib/VNDB/DB/Releases.pm | 58 ++++++++++++++++-------------- lib/VNDB/Func.pm | 28 ++++++++++++++- lib/VNDB/Handler/Misc.pm | 4 +-- lib/VNDB/Handler/Releases.pm | 84 ++++++++++++++++++++++++-------------------- 4 files changed, 106 insertions(+), 68 deletions(-) (limited to 'lib/VNDB') 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 '▸ '.mt('_rbrowse_filters').''; 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; -- cgit v1.2.3