summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-13 16:36:04 +0100
committerYorhel <git@yorhel.nl>2009-11-13 16:36:04 +0100
commit8578b87b8367a2f297a369cb3143627a4b37a4b0 (patch)
tree2c7e7e5968aaef16e577c903bcf63927c62a9523
parent997d1752ff1b1d2b1cf425b2c05dc180abc2243a (diff)
API: Added sorting and pagination
-rw-r--r--data/docs/1143
-rw-r--r--lib/Multi/API.pm95
-rw-r--r--lib/POE/Filter/VNDBAPI.pm21
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