diff options
author | Yorhel <git@yorhel.nl> | 2009-11-13 16:36:04 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-13 16:36:04 +0100 |
commit | 8578b87b8367a2f297a369cb3143627a4b37a4b0 (patch) | |
tree | 2c7e7e5968aaef16e577c903bcf63927c62a9523 | |
parent | 997d1752ff1b1d2b1cf425b2c05dc180abc2243a (diff) |
API: Added sorting and pagination
-rw-r--r-- | data/docs/11 | 43 | ||||
-rw-r--r-- | lib/Multi/API.pm | 95 | ||||
-rw-r--r-- | lib/POE/Filter/VNDBAPI.pm | 21 |
3 files changed, 118 insertions, 41 deletions
diff --git a/data/docs/11 b/data/docs/11 index a2928b7b..6fe7f3ed 100644 --- a/data/docs/11 +++ b/data/docs/11 @@ -52,7 +52,7 @@ server resources and prevent abuse of this service.</p> <ul> <li>5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.</li> <li>3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.</li> - <li>Each command currently returns at most 10 results. <i>TODO: make configurable?</i></li> + <li>Each command returns at most 10 results.</li> <li>100 commands per 10 minutes per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.</li> <li> 1 second of SQL time per minute per user. SQL time is the total time taken to @@ -221,27 +221,44 @@ however still required.<br /> well), and lastly a filter expression. </p> <pre> - get <b class="standout">type flags filters</b> + get <b class="standout">type flags filters options</b> </pre> <p> - Type and flags are unescaped strings. The only type currently accepted is 'vn'. - Flags is a comma-separated list of flags indicating what info to fetch. The - filters, available flags and their meaning are documented separately for each - type. The following example will fetch basic information and information about - the related anime of the visual novel with id = 17: + <i>type</i> and <i>flags</i> are unescaped strings. The accepted values for <i>type</i> are + documented below. <i>flags</i> is a comma-separated list of flags indicating what + info to fetch. The filters, available flags and their meaning are documented + separately for each type. The last <i>options</i> argument is optional, and + influences the behaviour of the returned results. When present, <i>options</i> + should be a JSON object with the following members (all are optional): +</p> +<dl> + <dt>page</dt><dd> + integer, used for pagination. Page 1 (the default) returns the first 10 + results (1-10), page 2 returns the following 10 (11-20), etc. + </dd><dt>sort</dt><dd> + string, the field to order the results by. The accepted field names differ + per type, the default sort field is the ID of the database entry. + </dd> + <dt>reverse</dt><dd>boolean, default false. Set to true to reverse the order of the results.</dd> +</dl> +<p> + The following example will fetch basic information and information about the + related anime of the visual novel with id = 17: </p> <pre> get vn basic,anime (id = 17) </pre> <p> The server will reply with a 'results' message, this message is followed by a - JSON object describing the results. This object has two members: 'num', which - is an integer indicating the number of results returned, and 'items', which - contains the results as an array of objects. For example, the server could - reply to the previous command with the following message: + JSON object describing the results. This object has three members: 'num', which + is an integer indicating the number of results returned, 'more', which is true + when there are more results available (i.e. increasing the <i>page</i> option + described above will give new results) and 'items', which contains the results + as an array of objects. For example, the server could reply to the previous + command with the following message: </p> <pre> - results {"num":1, "items":[{ + results {"num":1, "more":false, "items":[{ "id": 17, "title": "Ever17 -the out of infinity-", "original": null, "released": "2002-08-29", "languages": ["en","ja","ru","zh"], "platforms": ["drc","ps2","psp","win"],"anime": [] @@ -372,6 +389,7 @@ however still required.<br /> </td> </tr> </table> +<p>Sorting is possible on the 'id', 'title' and 'released' fields.</p><br /> <p>'get vn' accepts the following filter expressions:</p> <table style="margin: 5px 2%; width: 95%"> @@ -586,6 +604,7 @@ however still required.<br /> </td> </tr> </table> +<p>Sorting is possible on the 'id', 'title' and 'released' fields.</p><br /> <p>Accepted filters:</p> <table style="margin: 5px 2%; width: 95%"> diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index 4c160f70..f360d231 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -48,6 +48,7 @@ sub spawn { logfile => "$VNDB::M{log_dir}/api.log", conn_per_ip => 5, sess_per_user => 3, + results => 10, tcp_keepalive => [ 120, 60, 3 ], # time, intvl, probes throttle_cmd => [ 6, 100 ], # interval between each command, allowed burst throttle_sql => [ 60, 1 ], # sql time multiplier, allowed burst (in sql time) @@ -177,6 +178,22 @@ sub filtertosql { } +# generates the LIMIT/OFFSET/ORDER BY part of the queries +sub sqllast { # $get, default sort field, hashref with sort fields and SQL variant + my($get, $def, $sort) = @_; + + my $o = $get->{opt}{reverse} ? 'DESC' : 'ASC'; + $get->{opt}{sort} = $def if !defined $get->{opt}{sort}; + my $s = $sort->{$get->{opt}{sort}}; + return cerr $get->{c}, badarg => 'Invalid sort field', field => 'sort' if !$s; + my $q = 'ORDER BY '.sprintf($s, $o); + + my $res = $poe_kernel->get_active_session()->get_heap()->{results}; + $q .= sprintf ' LIMIT %d OFFSET %d', $res+1, $res*($get->{opt}{page}-1); + return $q; +} + + ## POE handlers sub _start { @@ -311,10 +328,28 @@ sub client_input { $c->{throttle}[0] += $_[HEAP]{throttle_cmd}[0]; # handle get command + if($cmd eq 'get') { + my $opt = $arg->[3]; + return cerr $c, badarg => 'Invalid argument for the "page" option', field => 'page' + if defined($opt->{page}) && (ref($opt->{page}) || $opt->{page} !~ /^\d+$/ || $opt->{page} < 1); + return cerr $c, badarg => '"reverse" option must be boolean', field => 'reverse' + if defined($opt->{reverse}) && !JSON::XS::is_bool($opt->{reverse}); + return cerr $c, badarg => '"sort" option must be a string', field => 'sort' + if defined($opt->{sort}) && ref($opt->{sort}); + $opt->{page} = $opt->{page}||1; + $opt->{reverse} = defined($opt->{reverse}) && $opt->{reverse}; + my %obj = ( + c => $c, + info => $arg->[1], + filters => $arg->[2], + opt => $opt, + ); + return cerr $c, 'gettype', "Unknown get type: '$arg->[0]'" if $arg->[0] !~ /^(?:vn|release)$/; + return $_[KERNEL]->yield("get_$arg->[0]", \%obj); + } + + # unknown command return cerr $c, 'parse', "Unkown command '$cmd'" if $cmd ne 'get'; - my $type = shift @$arg; - return cerr $c, 'gettype', "Unknown get type: '$type'" if $type !~ /^(?:vn|release)$/; - $_[KERNEL]->yield("get_$type", $c, @$arg); } @@ -365,30 +400,32 @@ sub login_res { # num, res, [ c, arg ] sub get_results { - my $get = $_[ARG0]; # hashref, must contain: type, c, queries, time, list, info, filters, + my $get = $_[ARG0]; # hashref, must contain: type, c, queries, time, list, info, filters, more, opt # update sql throttle $get->{c}{throttle}[1] += $get->{time}*$_[HEAP]{throttle_sql}[0]; # send and log my $num = @{$get->{list}}; - $get->{c}{wheel}->put([ results => { num => $num, items => $get->{list} }]); - $_[KERNEL]->yield(log => $get->{c}, "T:%4.0fms Q:%d R:%02d get %s %s %s", - $get->{time}*1000, $get->{queries}, $num, $get->{type}, join(',', @{$get->{info}}), encode_filters $get->{filters}); + $get->{c}{wheel}->put([ results => { num => $num, more => $get->{more} ? TRUE : FALSE, items => $get->{list} }]); + $_[KERNEL]->yield(log => $get->{c}, "T:%4.0fms Q:%d R:%02d get %s %s %s {%s %s, page %d}", + $get->{time}*1000, $get->{queries}, $num, $get->{type}, join(',', @{$get->{info}}), encode_filters($get->{filters}), + $get->{opt}{sort}, $get->{opt}{reverse}?'desc':'asc', $get->{opt}{page}); } sub get_vn { - my($c, $info, $filters) = @_[ARG0..$#_]; + my $get = $_[ARG0]; - return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|anime|relations)$/, @$info); + return cerr $get->{c}, getinfo => "Unkown info flag '$_'", flag => $_ + for (grep !/^(basic|details|anime|relations)$/, @{$get->{info}}); my $select = 'v.id, v.latest'; - $select .= ', vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms' if grep /basic/, @$info; - $select .= ', vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai' if grep /details/, @$info; + $select .= ', vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms' if grep /basic/, @{$get->{info}}; + $select .= ', vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai' if grep /details/, @{$get->{info}}; my @placeholders; - my $where = encode_filters $filters, \&filtertosql, $c, \@placeholders, [ + my $where = encode_filters $get->{filters}, \&filtertosql, $get->{c}, \@placeholders, [ [ 'id', [ 'int' => 'v.id :op: :value:', {qw|= = != <> > > < < <= <= >= >=|} ], [ inta => 'v.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, join => ',' ], @@ -419,11 +456,16 @@ sub get_vn { ))', {'~', 1}, process => \'like' ], ], ]; - return if !$where; + my $last = sqllast $get, 'id', { + id => 'v.id %s', + title => 'vr.title %s', + released => 'v.c_released %s', + }; + return if !$last || !$where; $_[KERNEL]->post(pg => query => - qq|SELECT $select FROM vn v JOIN vn_rev vr ON v.latest = vr.id WHERE NOT v.hidden AND $where LIMIT 10|, - \@placeholders, 'get_vn_res', { c => $c, info => $info, filters => $filters }); + qq|SELECT $select FROM vn v JOIN vn_rev vr ON v.latest = vr.id WHERE NOT v.hidden AND $where $last|, + \@placeholders, 'get_vn_res', $get); } @@ -455,6 +497,7 @@ sub get_vn_res { }; } } + $get->{more} = pop(@$res)&&1 if @$res > $_[HEAP]{results}; $get->{list} = $res; } @@ -506,16 +549,16 @@ sub get_vn_res { sub get_release { - my($c, $info, $filters) = @_[ARG0..$#_]; + my $get = $_[ARG0]; - return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|vn|producers)$/, @$info); + return cerr $get->{c}, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|vn|producers)$/, @{$get->{info}}); my $select = 'r.id, r.latest'; - $select .= ', rr.title, rr.original, rr.released, rr.type, rr.patch, rr.freeware, rr.doujin' if grep /basic/, @$info; - $select .= ', rr.website, rr.notes, rr.minage, rr.gtin, rr.catalog' if grep /details/, @$info; + $select .= ', rr.title, rr.original, rr.released, rr.type, rr.patch, rr.freeware, rr.doujin' if grep /basic/, @{$get->{info}}; + $select .= ', rr.website, rr.notes, rr.minage, rr.gtin, rr.catalog' if grep /details/, @{$get->{info}}; my @placeholders; - my $where = encode_filters $filters, \&filtertosql, $c, \@placeholders, [ + my $where = encode_filters $get->{filters}, \&filtertosql, $get->{c}, \@placeholders, [ [ 'id', [ 'int' => 'r.id :op: :value:', {qw|= = != <> > > >= >= < < <= <=|} ], [ inta => 'r.id :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',' ], @@ -548,11 +591,16 @@ sub get_release { [ stra => 'rr.id :op:(SELECT rl.rid FROM releases_lang rl WHERE rl.lang IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ], ], ]; - return if !$where; + my $last = sqllast $get, 'id', { + id => 'r.id %s', + title => 'rr.title %s', + released => 'rr.released %s', + }; + return if !$where || !$last; $_[KERNEL]->post(pg => query => - qq|SELECT $select FROM releases r JOIN releases_rev rr ON rr.id = r.latest WHERE $where AND NOT hidden LIMIT 10|, - \@placeholders, 'get_release_res', { c => $c, info => $info, filters => $filters }); + qq|SELECT $select FROM releases r JOIN releases_rev rr ON rr.id = r.latest WHERE $where AND NOT hidden $last|, + \@placeholders, 'get_release_res', $get); } @@ -581,6 +629,7 @@ sub get_release_res { $_->{catalog} ||= undef; } } + $get->{more} = pop(@$res)&&1 if @$res > $_[HEAP]{results}; $get->{list} = $res; } elsif($get->{type} eq 'languages') { diff --git a/lib/POE/Filter/VNDBAPI.pm b/lib/POE/Filter/VNDBAPI.pm index 0eca3eb2..24188a2f 100644 --- a/lib/POE/Filter/VNDBAPI.pm +++ b/lib/POE/Filter/VNDBAPI.pm @@ -6,10 +6,11 @@ # C: login <json-object> # [ 'login', {object} ] # -# C: get <type> <info> <filters> -# [ 'get', <type>, <info>[ split ',', $2 ], [ filters ] ] +# C: get <type> <info> <filters> <options> +# [ 'get', <type>, <info>[ split ',', $2 ], [ filters ], { options } ] # <type> must match /[a-z\/_]+/ # <info> as string: /[a-z_]+(,[a-z_]+)*/, in perl: [ /[a-z_]+/, .. ] +# <options> is optional, must be JSON-object otherwise # # S: ok # [ 'ok' ] @@ -129,7 +130,7 @@ sub get_one { if(!defined $json) { my $err = $@; $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//; - return _err "JSON-decode: $err" if !defined $json; + return _err "JSON-decode: $err"; } return _err qq|"$cmd" command requires a JSON object| if ref($json) ne 'HASH'; return [[ $cmd, $json ]]; @@ -137,11 +138,19 @@ sub get_one { # C: get if($self->{type} eq 'server' && $str =~ /^$WS*get$WS+($GET_TYPE)$WS+($GET_INFO)$WS+(.+)$/s) { - my($type, $info) = ($1, $2); + my($type, $info, $options) = ($1, $2, {}); my($filters, $rest) = decode_filters($3); return _err $filters if !ref $filters; - return _err 'Leading characters' if length $rest && $rest !~ /^$WS+$/; - return [[ 'get', $type, [ split /,/, $info ], $filters ]]; + if($rest !~ /^$WS*$/) { + $options = eval { JSON::XS->new->decode($rest) }; + if(!defined $options) { + my $err = $@; + $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//; + return _err "JSON-decode: $err"; + } + return _err 'options argument must be a JSON object' if ref($options) ne 'HASH'; + } + return [[ 'get', $type, [ split /,/, $info ], $filters, $options ]]; } # S: ok |