From c7e110801c5de63d570b857f4bb3784f1ca5fbb3 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 25 Oct 2009 09:58:50 +0100
Subject: Replaced util/init.pl with a Makefile
I'm planning to add some more stuff to this makefile later, like
starting/stopping Multi and performing updates.
---
Makefile | 72 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
util/init.pl | 70 ----------------------------------------------------------
2 files changed, 72 insertions(+), 70 deletions(-)
create mode 100644 Makefile
delete mode 100755 util/init.pl
diff --git a/Makefile b/Makefile
new file mode 100644
index 00000000..351a09bf
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,72 @@
+# all (default)
+# Same as $ make staticdirs js skins www robots
+#
+# staticdirs
+# Creates the required directory structures in static/
+#
+# js
+# Generates the Javascript code
+#
+# skins
+# Generates the CSS code
+#
+# robots
+# Ensures that www/robots.txt and static/robots.txt exist. Can be modified to
+# suit your needs.
+#
+# chmod
+# For when the http process is run from a different user than the files are
+# chown'ed to. chmods all files and directories written to from vndb.pl.
+# (including the stylesheets and javascript code, so these can be auto-updated)
+#
+# chmod-tladmin
+# The TransAdmin plugin also needs write access to some files
+#
+#
+# NOTE: This Makefile has only been tested using a recent version of GNU make
+# in a relatively up-to-date Arch Linux environment, and may not work in other
+# environments. Patches to improve the portability are always welcome.
+
+
+.PHONY: all staticdirs js skins robots chmod chmod-tladmin
+
+all: staticdirs js skins robots
+
+
+staticdirs: static/cv static/sf static/st
+
+static/cv static/sf static/st:
+ mkdir $@;
+ for i in $$(seq -w 0 1 99); do mkdir "$@/$$i"; done
+
+
+js: static/f/script.js
+
+static/f/script.js: data/script.js data/lang.txt util/jsgen.pl
+ util/jsgen.pl
+
+
+skins: static/s/*/style.css
+
+static/s/%/style.css: static/s/%/conf util/skingen.pl data/style.css
+ util/skingen.pl $*
+
+
+www:
+ mkdir www
+
+robots: www www/robots.txt static/robots.txt
+
+%/robots.txt:
+ echo 'User-agent: *' > $@
+ echo 'Disallow: /' >> $@
+
+
+chmod: all
+ chmod a-x+rw static/f/script.js
+ chmod -R a-x+rwX static/{cv,sf,st}
+ chmod a-x+rw static/s/*/{style.css,boxbg.png}
+
+chmod-tladmin:
+ chmod a-x+rwX data/lang.txt data/docs data/docs/*\.*
+
diff --git a/util/init.pl b/util/init.pl
deleted file mode 100755
index ac533174..00000000
--- a/util/init.pl
+++ /dev/null
@@ -1,70 +0,0 @@
-#!/usr/bin/perl
-
-
-# This script should be run after you've somehow managed to fetch
-# all the versioned files from the git repo.
-
-
-print "Initializing the files and directories needed to run VNDB...\n";
-
-
-# determine our root directory
-use Cwd 'abs_path';
-our $ROOT;
-BEGIN {
- ($ROOT = abs_path $0) =~ s{/util/init\.pl$}{};
-}
-
-
-print " Using project root: $ROOT\n";
-print "\n";
-
-
-
-print "Creating directory structures...\n";
-for my $d (qw| cv st sf |) {
- print " /static/$d\n";
- mkdir "$ROOT/static/$d" or die "mkdir '$ROOT/static/$d': $!\n";
- for my $i (0..99) {
- my $n = sprintf '%s/static/%s/%02d', $ROOT, $d, $i;
- mkdir $n or die "mkdir '$n': $!\n";
- chmod 0777, $n or die "chmod 777 '$n': $!\n";
- }
-}
-print "\n";
-
-
-print "Creating /www\n";
-print " You can use this directory to store all files you want to\n";
-print " be available from the main domain. A favicon.ico for example.\n";
-mkdir "$ROOT/www" or die $!;
-print "\n";
-
-
-print "Writing robots.txt in /static and /www\n";
-print " You probably don't want your personal copy of VNDB to end up\n";
-print " in the google results, so I'll install a default robots.txt\n";
-print " for you. You're free to modify them as you wish.\n";
-for ('static/robots.txt', 'www/robots.txt') {
- print " $_ exists, skipping...\n", next if -f "$ROOT/$_";
- open my $F, '>', "$ROOT/$_" or die "$_: $!\n";
- print $F "User-agent: *\nDisallow: /\n";
- close $F;
-}
-print "\n";
-
-
-if(!-f "$ROOT/data/config.pl") {
- # TODO: create a template config file
- print "No custom config file found, please write one!\n";
-}
-print "\n";
-
-
-print "Everything is initialized! Now make sure to configure your\n";
-print "webserver and to initialize a postgresql database (using\n";
-print "dump.sql)\n";
-
-
-
-
--
cgit v1.2.3
From 77151c26876f1c9f6619df5c588499740407f1a8 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 25 Oct 2009 11:44:42 +0100
Subject: Multi::Core: Implemented daemonizing and PID file
The location of the PID file isn't configurable, so this change
currently limits running only one Multi for each VNDB installation.
(Not that you'd need more...)
---
lib/Multi/Core.pm | 36 +++++++++++++++++++++++++++++++++---
1 file changed, 33 insertions(+), 3 deletions(-)
diff --git a/lib/Multi/Core.pm b/lib/Multi/Core.pm
index 830ca657..076edf51 100644
--- a/lib/Multi/Core.pm
+++ b/lib/Multi/Core.pm
@@ -10,11 +10,33 @@ use warnings;
use POE;
use POE::Component::Pg;
use DBI;
+use POSIX 'setsid', 'pause', 'SIGUSR1';
sub run {
my $p = shift;
+ die "PID file already exists\n" if -e "$VNDB::ROOT/data/multi.pid";
+
+ # fork
+ my $pid = fork();
+ die "fork(): $!" if !defined $pid or $pid < 0;
+
+ # parent process, log PID and wait for child to initialize
+ if($pid > 0) {
+ $SIG{CHLD} = sub { die "Initialization failed.\n"; };
+ $SIG{ALRM} = sub { kill $pid, 9; die "Initialization timeout.\n"; };
+ $SIG{USR1} = sub {
+ open my $P, '>', "$VNDB::ROOT/data/multi.pid" or kill($pid, 9) && die $!;
+ print $P $pid;
+ close $P;
+ exit;
+ };
+ alarm(10);
+ pause();
+ exit 1;
+ }
+
# spawn our SQL handling session
my @db = @{$VNDB::O{db_login}};
my(@dsn) = DBI->parse_dsn($db[0]);
@@ -29,9 +51,6 @@ sub run {
],
);
- # log warnings
- $SIG{__WARN__} = sub {(local$_=shift)=~s/\r?\n//;$poe_kernel->call(core=>log=>'__WARN__: '.$_)};
-
$poe_kernel->run();
}
@@ -53,6 +72,16 @@ sub _start {
# I'm surprised the strict pagma isn't complaining about this
"Multi::$mod"->spawn(%$args);
}
+
+ # finish daemonizing
+ kill SIGUSR1, getppid();
+ setsid();
+ chdir '/';
+ umask 0022;
+ $SIG{__WARN__} = sub {(local$_=shift)=~s/\r?\n//;$poe_kernel->call(core=>log=>'__WARN__: '.$_)};
+ close STDOUT;
+ close STDERR;
+ close STDIN;
}
@@ -87,6 +116,7 @@ sub shutdown {
$_[KERNEL]->call(core => log => 'Shutting down (%s)', $_[ARG1]);
$_[KERNEL]->post(pg => 'shutdown');
$_[KERNEL]->alias_remove('core');
+ unlink "$VNDB::ROOT/data/multi.pid";
}
--
cgit v1.2.3
From 9d75f5b0e22c1560a048f92c30cd6e572728d6b8 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 25 Oct 2009 16:22:43 +0100
Subject: Makefile: Added rules for controlling Multi
This will come in handy when writing update rules.
---
Makefile | 32 +++++++++++++++++++++++++++++++-
1 file changed, 31 insertions(+), 1 deletion(-)
diff --git a/Makefile b/Makefile
index 351a09bf..df1d05aa 100644
--- a/Makefile
+++ b/Makefile
@@ -22,13 +22,17 @@
# chmod-tladmin
# The TransAdmin plugin also needs write access to some files
#
+# multi-start, multi-stop, multi-restart:
+# Start/stop/restart the Multi daemon. Provided for convenience, a proper initscript
+# probably makes more sense.
+#
#
# NOTE: This Makefile has only been tested using a recent version of GNU make
# in a relatively up-to-date Arch Linux environment, and may not work in other
# environments. Patches to improve the portability are always welcome.
-.PHONY: all staticdirs js skins robots chmod chmod-tladmin
+.PHONY: all staticdirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart
all: staticdirs js skins robots
@@ -70,3 +74,29 @@ chmod: all
chmod-tladmin:
chmod a-x+rwX data/lang.txt data/docs data/docs/*\.*
+
+# may wait indefinitely, ^C and kill -9 in that case
+define multi-stop
+ if [ -s data/multi.pid ]; then\
+ kill `cat data/multi.pid`;\
+ while [ -s data/multi.pid ]; do\
+ if kill -0 `cat data/multi.pid`; then sleep 1;\
+ else rm -f data/multi.pid; fi\
+ done;\
+ fi
+endef
+
+define multi-start
+ util/multi.pl
+endef
+
+multi-stop:
+ $(multi-stop)
+
+multi-start:
+ $(multi-start)
+
+multi-restart:
+ $(multi-stop)
+ $(multi-start)
+
--
cgit v1.2.3
From 4895ea63323b94f0b128d6874be997a24d3a3b0d Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Tue, 27 Oct 2009 22:18:27 +0100
Subject: SQL: Fixed another bug with the tags_vn_bayesian.spoiler calculation
---
ChangeLog | 3 +++
util/dump.sql | 2 +-
util/updates/update_2.9.sql | 32 ++++++++++++++++++++++++++++++++
3 files changed, 36 insertions(+), 1 deletion(-)
create mode 100644 util/updates/update_2.9.sql
diff --git a/ChangeLog b/ChangeLog
index 299ade41..6aa58dce 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,6 @@
+git - ?
+ - Fixed another bug with the calculation of tags_vn_bayesian.spoiler
+
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
- Relation graphs now use the color scheme of selected skin
diff --git a/util/dump.sql b/util/dump.sql
index 0fdf5696..cd6f9434 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -575,7 +575,7 @@ BEGIN
SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
-- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
- SELECT tag, vid, uid, MAX(vote)::real AS vote, AVG(spoiler)::real AS spoiler
+ SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
FROM tags_vn_all GROUP BY tag, vid, uid;
-- grouped by (tag, vid) and serialized into a table
DROP INDEX IF EXISTS tags_vn_bayesian_tag;
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
new file mode 100644
index 00000000..ee2570fb
--- /dev/null
+++ b/util/updates/update_2.9.sql
@@ -0,0 +1,32 @@
+
+-- another fix in the calculation of the tags_vn_bayesian.spoiler column
+
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ -- all votes for all tags
+ CREATE OR REPLACE TEMPORARY VIEW tags_vn_all AS
+ SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs();
+ -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry
+ CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS
+ SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler
+ FROM tags_vn_all GROUP BY tag, vid, uid;
+ -- grouped by (tag, vid) and serialized into a table
+ DROP INDEX IF EXISTS tags_vn_bayesian_tag;
+ TRUNCATE tags_vn_bayesian;
+ INSERT INTO tags_vn_bayesian
+ SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating,
+ (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler
+ FROM tags_vn_grouped
+ GROUP BY tag, vid
+ HAVING AVG(vote) > 0;
+ CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag);
+ -- now perform the bayesian ranking calculation
+ UPDATE tags_vn_bayesian tvs SET rating =
+ ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating)
+ / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real;
+ -- and update the VN count in the tags table as well
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT tag_vn_calc();
--
cgit v1.2.3
From 2a5f8ad226ae798d0d28c5b2259e348255d435fa Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 31 Oct 2009 18:42:42 +0100
Subject: Multi::Core: Fixed bug in daemonizing code
Apparently closing STDOUT/STDIN/STDERR will exit the process... so open
/dev/null instead. (As done in the perlipc manual - of course doesn't
work on windows, but nobody cares about that)
---
lib/Multi/Core.pm | 6 +++---
1 file changed, 3 insertions(+), 3 deletions(-)
diff --git a/lib/Multi/Core.pm b/lib/Multi/Core.pm
index 076edf51..06ade2f1 100644
--- a/lib/Multi/Core.pm
+++ b/lib/Multi/Core.pm
@@ -79,9 +79,9 @@ sub _start {
chdir '/';
umask 0022;
$SIG{__WARN__} = sub {(local$_=shift)=~s/\r?\n//;$poe_kernel->call(core=>log=>'__WARN__: '.$_)};
- close STDOUT;
- close STDERR;
- close STDIN;
+ open STDIN, '/dev/null';
+ open STDOUT, '>/dev/null';
+ open STDERR, '>/dev/null';
}
--
cgit v1.2.3
From 79e15541ff49211663729e597ffa68241b26c171 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Tue, 3 Nov 2009 22:20:04 +0100
Subject: gitignore: Added data/multi.pid
---
.gitignore | 1 +
1 file changed, 1 insertion(+)
diff --git a/.gitignore b/.gitignore
index e2a730fd..13e62a79 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,4 +1,5 @@
/data/config.pl
+/data/multi.pid
/data/log/
/static/f/script.js
/static/s/*/style.css
--
cgit v1.2.3
From e9fb4c410ef5f20f09feed3a5bb3c5f437530745 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Wed, 4 Nov 2009 20:42:57 +0100
Subject: Multi::Core: Improved logging of warnings and errors
Rather than forwarding STDERR and STDOUT to /dev/null, it is now written
to the log file. This also means that exceptions are now finally logged
somewhere, making debugging a lot easier.
I've also looked at how we should handle exceptions, but the default
action of POE is to let the process die() as any normal script (assuming
a session doesn't sig_handled() the DIE signal), and I couldn't think of
anything better than this default - so let's code with this in mind:
die() when something goes horribly wrong, otherwise just warn() and
handle the error.
---
lib/Multi/Core.pm | 31 ++++++++++++++++++++++---------
1 file changed, 22 insertions(+), 9 deletions(-)
diff --git a/lib/Multi/Core.pm b/lib/Multi/Core.pm
index 06ade2f1..5c668b7c 100644
--- a/lib/Multi/Core.pm
+++ b/lib/Multi/Core.pm
@@ -1,6 +1,6 @@
#
-# Multi::Core - handles logging and the main command queue
+# Multi::Core - handles spawning and logging
#
package Multi::Core;
@@ -78,24 +78,29 @@ sub _start {
setsid();
chdir '/';
umask 0022;
- $SIG{__WARN__} = sub {(local$_=shift)=~s/\r?\n//;$poe_kernel->call(core=>log=>'__WARN__: '.$_)};
open STDIN, '/dev/null';
- open STDOUT, '>/dev/null';
- open STDERR, '>/dev/null';
+ tie *STDOUT, 'Multi::Core::STDIO', 'STDOUT';
+ tie *STDERR, 'Multi::Core::STDIO', 'STDERR';
}
-sub log { # level, msg
- (my $p = eval { $_[SENDER][2]{$_[CALLER_STATE]}[0] } || '') =~ s/^Multi:://;
- my $msg = sprintf '%s::%s: %s', $p, $_[CALLER_STATE],
- $_[ARG1] ? sprintf($_[ARG0], @_[ARG1..$#_]) : $_[ARG0];
-
+# subroutine, not supposed to be called as a POE event
+sub log_msg { # msg
+ (my $msg = shift) =~ s/\n+$//;
open(my $F, '>>', $VNDB::M{log_dir}.'/multi.log');
printf $F "[%s] %s\n", scalar localtime, $msg;
close $F;
}
+# the POE event
+sub log { # level, msg
+ (my $p = eval { $_[SENDER][2]{$_[CALLER_STATE]}[0] } || '') =~ s/^Multi:://;
+ log_msg sprintf '%s::%s: %s', $p, $_[CALLER_STATE],
+ $_[ARG1] ? sprintf($_[ARG0], @_[ARG1..$#_]) : $_[ARG0];
+}
+
+
sub pg_error { # ARG: command, errmsg, [ query, params, orig_session, event-args ]
my $s = $_[ARG2] ? sprintf ' (Session: %s, Query: "%s", Params: %s, Args: %s)',
join(', ', $_[KERNEL]->alias_list($_[ARG4])), $_[ARG2],
@@ -120,5 +125,13 @@ sub shutdown {
}
+# Tiny class for forwarding output for STDERR/STDOUT to the log file using tie().
+package Multi::Core::STDIO;
+
+use base 'Tie::Handle';
+sub TIEHANDLE { return bless \"$_[1]", $_[0] }
+sub WRITE { Multi::Core::log_msg(${$_[0]}.': '.$_[1]) }
+
+
1;
--
cgit v1.2.3
From 6576cf8921cbb6e8e50717e071e3f3201c088763 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Wed, 4 Nov 2009 20:55:58 +0100
Subject: Multi::Core: die() on SQL error
One example of what I would consider "horribly wrong".
---
lib/Multi/Core.pm | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/lib/Multi/Core.pm b/lib/Multi/Core.pm
index 5c668b7c..6388c924 100644
--- a/lib/Multi/Core.pm
+++ b/lib/Multi/Core.pm
@@ -105,7 +105,7 @@ sub pg_error { # ARG: command, errmsg, [ query, params, orig_session, event-args
my $s = $_[ARG2] ? sprintf ' (Session: %s, Query: "%s", Params: %s, Args: %s)',
join(', ', $_[KERNEL]->alias_list($_[ARG4])), $_[ARG2],
join(', ', $_[ARG3] ? map qq|"$_"|, @{$_[ARG3]} : '[none]'), $_[ARG5]||'' : '';
- $_[KERNEL]->call(core => log => 'SQL Error for command %s: %s %s', $_[ARG0], $_[ARG1], $s);
+ die sprintf 'SQL Error for command %s: %s%s', $_[ARG0], $_[ARG1], $s;
}
--
cgit v1.2.3
From eb093ac9fe5d9dcbde98c92ba2720749bb814def Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Tue, 3 Nov 2009 22:15:57 +0100
Subject: API: Initial commit of the W.I.P. public API
d11 does not fully reflect what has been implemented, and things are
likely to change.
---
README | 2 +
data/docs/11 | 207 ++++++++++++++++++++++++++++++
data/global.pl | 1 +
data/style.css | 8 ++
lib/Multi/API.pm | 318 ++++++++++++++++++++++++++++++++++++++++++++++
lib/POE/Filter/VNDBAPI.pm | 303 +++++++++++++++++++++++++++++++++++++++++++
6 files changed, 839 insertions(+)
create mode 100644 data/docs/11
create mode 100644 lib/Multi/API.pm
create mode 100644 lib/POE/Filter/VNDBAPI.pm
diff --git a/README b/README
index e62f0e06..c56db3f7 100644
--- a/README
+++ b/README
@@ -28,6 +28,8 @@ Requirements
PerlIO::gzip (optional, for output compression)
util/multi.pl:
+ API:
+ JSON::XS
Core:
DBI
DBD::Pg
diff --git a/data/docs/11 b/data/docs/11
new file mode 100644
index 00000000..7927182b
--- /dev/null
+++ b/data/docs/11
@@ -0,0 +1,207 @@
+:TITLE:Public Database API
+
+
+
IN DEVELOPMENT
+
This API is currently in development, pretty much everything is subject to change without notice.
+
+
+:INC:index
+
+:SUB:Introduction
+Design goals
+
+ -
+ Simple in implementation of both client and server. "Simple" here means that
+ it shouldn't take much code to write a secure and full implementation and that
+ client applications don't require huge dependency trees just to use this API.
+
+ - Powerful: Not as powerful as raw SQL, but not as rigid as commonly used REST or RPC protocols.
+ - Fast: minimal bandwidth overhead
+ -
+ High-level: nobody is interested in the internal database structure of VNDB
+ (ok, maybe you are, but you wouldn't want to write an application using it: it
+ changes quite often)
+
+ - Stateful
+
+
+
+Design overview
+
+ - TCP-based, all communication between the client and the server is done
+ using one TCP connection. This connection stays alive until it is explicitely
+ closed by either the client or the server.
+ - Request/response, client sends a request and server replies with a response.
+ - Session-based: clients are required to login before issuing commands to
+ the server. A session is created by issuing the 'login' command, this session
+ stays valid for the lifetime of the TCP connection.
+ - Everything sent between the client and the server is encoded in UTF-8.
+
+
+
+Limits
+The following limits are enforced by the server, in order to limit the
+server resources and prevent abuse of this service.
+
+ - 5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.
+ - 3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.
+ - more to come...
+
+
+
+Test version:
+
+ - Host (beta)
- beta.vndb.org
+ - Port
- 19534 ('VN')
+
+
+
+:SUB:Request/response syntax
+
+ The VNDB API uses the JSON format for data in various places, this document assumes
+ you are familiar with it. See JSON.org for a quick
+ overview and RFC 4627
+ for the glory details.
+
+ The words object, array, value, string,
+ number and int refer to the JSON data types. In addition the following
+ definitions are used in this document:
+
+
+ - request or command
-
+ Message sent from the client to the server.
+
- response
-
+ Message sent from the server to the client.
+
- whitespace
-
+ Any sequence of the following characters: space, tab, line feed and carriage
+ return. (hexadecimal: 20, 09, 10, 0D, respectively). This is in line with the
+ definition of whitespace in the JSON specification.
+
- date
-
+ A string signifying a date (in particular: release date). The
+ following formats are used: "yyyy" (when day and month are unknown), "yyyy-mm"
+ (when day is unknown) "yyyy-mm-dd", and "tba" (To Be Announced). If the year is
+ not known and the date is not "tba", the special value null is used.
+
+
+
+
+Message format
+
+ A message is formatted as a command or response name, followed by any number of
+ arguments, followed by the End Of Transmission character (04 in hexadecimal).
+ Arguments are separated by one or more whitespace characters, and any sequence
+ of whitespace characters is allowed before and after the message.
+ The command or response name is an unescaped string containing only lowercase
+ alphabetical ASCII characters, and indicates what kind of command or response
+ this message contains.
+ An argument can either be an unescaped string (not containing whitespace), any
+ JSON value, or a filter string. The following two examples demonstrate a
+ 'login' command, with an object as argument. Both messages are equivalent, as
+ the whitespace is ignored. '0x04' is used to indicate the End Of Transmission
+ character.
+
+
+ login {"protocol":1,"username":"ayo"}0x04
+
+ login {
+ "protocol" : 1,
+ "username" : "ayo"
+ }
+ 0x04
+
+The 0x04 byte will be ommitted in the other examples in this document. It is
+however still required.
+
+
+Filter string syntax
+
+ Some commands accept a filter string as argument. This argument is formatted
+ similar to boolean expressions in most programming languages. A filter consists
+ of one or more expressions, separated by the boolean operators "and" and
+ "or" (lowercase). Each filter expression can be surrounded by parentheses to
+ indicate precedence, the filter argument itself must be surrounded by parentheses.
+
+ An expression consists of a field name, followed by an
+ operator and a value. The field name must consist entirely of
+ lowercase alphanumeric characters and can also contain an underscore. The
+ operator must be one of the following characters: =, !=, <, <=, >,
+ >= or ~. The value can be any valid JSON value. Whitespace
+ characters are allowed, but not required, between all expressions, field names,
+ operators and values.
+ The following two filters are equivalent:
+
+
+ (title~"osananajimi"or(id=2))
+
+ (
+ id = 2
+ or
+ title ~ "osananajimi"
+ )
+
+More complex things are also possible:
+
+ ((platforms = ["win", "ps2"] or languages = "ja") and released > "2009-01-10")
+
+See the individual commands for more details.
+
+
+:SUB:The 'login' command
+
+ login {"protocol":1,"client":"test","clientver":0.1,"username":"ayo","password":"hi-mi-tsu!"}
+
+
+ Every client is required to login before issuing other commands. The login
+ command accepts a JSON object as argument. This object must have the following members:
+
+
+ - protocol
- An integer that indicates which protocol version the client implements. Must be 1.
+ - client
-
+ A string identifying the client application. Between the 3 and 50 characters,
+ must contain only alphanumeric ASCII characters, space, underscore and hyphens.
+ When writing a client, think of a funny (unique) name and hardcode it into
+ your application.
+
- clientver
- A positive number indicating the software version of the client.
+ - username
- String containing the username of the person using the client.
+ - password
- String, password of that user in plain text.
+
+
+ The server replies with either 'ok' (no arguments), or 'error' (see below).
+
+
+
+
+:SUB:The 'error' response
+
+ Every command to the server can receive an 'error' response, this response has one
+ argument: a JSON object containing at least a member named "id", which identifies
+ the error, and a "msg", which contains a human readable message explaining what
+ went wrong. Other members are also possible, depending on the value of "id".
+ Example error message:
+
+
+ error {"id":"parse", "msg":"Invalid command or argument"}
+
+
+ Note that the value of "msg" is not directly linked to the error identifier:
+ the message explains what went wrong in more detail, there are several
+ different messages for the same id. The following error identifiers are currently
+ defined:
+
+
+ - parse
- Syntax error or unknown command.
+ - missing
- A JSON object argument is missing a required member. The name of which is given in the additional "field" member.
+ - badarg
- A JSON value is of the wrong type or in the wrong format. The name of the incorrect field is given in a "field" member.
+ - needlogin
- Need to be logged in to issue this command.
+ - auth
- (login) Incorrect username/password combination.
+ - loggedin
- (login) Already logged in. Only one successful login command can be issues on one connection.
+ - sesslimit
- (login) Too many open sessions for the current user.
+ - gettype
- (get) Unknown type argument to the 'get' command.
+ - getinfo
- (get) Unknown info flag to the 'get' command. The name of the unrecognised flag is given in an additional "flag" member.
+ - filterop
- (get) Bad operator in filter expression for the field/value combination. Includes three additional members: "field", "op" and "value" of the incorrect expression.
+ - filterval
- (get) Wrong type or format for the value in a filter expression. Adds same three members as the 'filterop' error.
+ - filterfield
- (get) Unknown field in filter expression. Adds same three members as the 'filterop' error.
+
+
+
+
diff --git a/data/global.pl b/data/global.pl
index 6dae908f..8edd25fa 100644
--- a/data/global.pl
+++ b/data/global.pl
@@ -117,6 +117,7 @@ our %S = (%S,
our %M = (
log_dir => $ROOT.'/data/log',
modules => {
+ #API => {}, # disabled by default, not really needed
RG => {},
Image => {},
Sitemap => {},
diff --git a/data/style.css b/data/style.css
index ff0844ad..5dcd7643 100644
--- a/data/style.css
+++ b/data/style.css
@@ -120,6 +120,14 @@ b.spoiler_shown { font-weight: normal }
text-align: left;
}
.linethrough { text-decoration: line-through }
+ pre {
+ padding:1px 5px;
+ margin: 5px 15px;
+ border: 1px dotted $border$;
+ border-right: none;
+ border-left: 1px solid $border$;
+ background: url($_boxbg$) repeat;
+}
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
new file mode 100644
index 00000000..1a00e565
--- /dev/null
+++ b/lib/Multi/API.pm
@@ -0,0 +1,318 @@
+
+#
+# Multi::API - The public VNDB API
+#
+
+package Multi::API;
+
+use strict;
+use warnings;
+use Socket 'inet_ntoa', 'SO_KEEPALIVE', 'SOL_SOCKET', 'IPPROTO_TCP';
+use Errno 'ECONNABORTED', 'ECONNRESET';
+use POE 'Wheel::SocketFactory', 'Wheel::ReadWrite';
+use POE::Filter::VNDBAPI 'encode_filters';
+use Digest::SHA 'sha256_hex';
+use Encode 'encode_utf8';
+
+
+# not exported by Socket, taken from netinet/tcp.h (specific to Linux, AFAIK)
+sub TCP_KEEPIDLE { 4 }
+sub TCP_KEEPINTVL { 5 }
+sub TCP_KEEPCNT { 6 }
+
+
+sub spawn {
+ my $p = shift;
+ POE::Session->create(
+ package_states => [
+ $p => [qw|
+ _start shutdown log server_error client_connect client_error client_input
+ login login_res
+ get_vn get_vn_res
+ |],
+ ],
+ heap => {
+ port => 19534,
+ logfile => "$VNDB::M{log_dir}/api.log",
+ conn_per_ip => 5,
+ sess_per_user => 3,
+ tcp_keepalive => [ 120, 60, 3 ], # time, intvl, probes
+ @_,
+ c => {},
+ },
+ );
+}
+
+
+## Non-POE helper functions
+
+sub cerr {
+ my($c, $id, $msg, %o) = @_;
+ $c->{wheel}->put([ error => { id => $id, msg => $msg, %o }]);
+ # using $poe_kernel here isn't really a clean solution...
+ $poe_kernel->yield(log => $c, 'error: %s, %s', $id, $msg);
+ return undef;
+}
+
+
+sub formatdate {
+ return undef if $_[0] == 0;
+ (local $_ = sprintf '%08d', $_[0]) =~
+ s/^(\d{4})(\d{2})(\d{2})$/$1 == 9999 ? 'tba' : $2 == 99 ? $1 : $3 == 99 ? "$1-$2" : "$1-$2-$3"/e;
+ return $_;
+}
+
+
+sub filtertosql {
+ my($c, $p, $t, $field, $op, $value) = ($_[1], $_[2], $_[3], @{$_[0]});
+ my %e = ( field => $field, op => $op, value => $value );
+
+ $t = (grep $_->[0] eq $field, @$t)[0];
+ return cerr $c, filterfield => "Unknown field '$field'", %e if !$t;
+ shift @$t; # field name
+ my $type = shift @$t;
+ my %o = @$t;
+
+ # integer, options: dbfield
+ if($type eq 'int') {
+ if($value && ref $value eq 'ARRAY') {
+ return cerr $c, filterop => "Operator for '$field' must be either = or != for array values", %e if $op ne '=' && $op ne '!=';
+ return cerr $c, filterval => "Array elements for '$field' must be integers", %e if grep !defined($_) || !/^\d+$/, @$value;
+ push @$p, @$value;
+ return sprintf '%s %s(%s)', $o{dbfield}, $op eq '=' ? 'IN' : 'NOT IN', join ',', map '?', @$value;
+ } elsif(defined $value && !ref $value && $value =~ /^\d+$/) {
+ my @ops = qw(= != > >= < <=);
+ return cerr $c, filterop => "Operator for '$field' must be one of ".join(', ', @ops), %e if !grep $op eq $_, @ops;
+ push @$p, $value;
+ return sprintf '%s %s ?', $o{dbfield}, $op eq '!=' ? '<>' : $op;
+ }
+ return cerr $c, filterval => "Value for '$field' must be either an integer or an array of integers", %e;
+ }
+
+ # string, options: dbfield, null
+ if($type eq 'str') {
+ if(!defined $value) {
+ return cerr $c, filterval => "null not allowed for '$field'", %e if !exists $o{null};
+ return cerr $c, filterop => "Operator for '$field' must be either = or != for null", %e if $op ne '=' && $op ne '!=';
+ return sprintf '%s %s', $o{dbfield}, $op eq '=' ? 'IS NULL' : 'IS NOT NULL' if !defined $o{null};
+ push @$p, $o{null};
+ return sprintf '%s %s ?', $o{dbfield}, $op eq '=' ? '=' : '<>';
+ } elsif(ref($value) eq 'ARRAY') {
+ return cerr $c, filterop => "Operator for '$field' must be either = or != for array values", %e if $op ne '=' && $op ne '!=';
+ return cerr $c, filterval => "Array elements for '$field' must be scalars", %e if grep !defined($_) || ref($_), @$value;
+ push @$p, @$value;
+ return sprintf '%s %s(%s)', $o{dbfield}, $op eq '=' ? 'IN' : 'NOT IN', join ',', map '?', @$value;
+ } elsif(!ref $value) {
+ my @ops = qw(= != ~);
+ if($op eq '=' || $op eq '!=') {
+ push @$p, $value;
+ return sprintf '%s %s ?', $o{dbfield}, $op eq '!=' ? '<>' : $op;
+ } elsif($op eq '~') {
+ $value =~ s/%//;
+ push @$p, "%$value%";
+ return sprintf '%s ILIKE ?', $o{dbfield};
+ } else {
+ return cerr $c, filterop => "Operator for '$field' must be =, != or ~", %e;
+ }
+ } else {
+ return cerr $c, filterval => "Value for '$field' must be a string or an array of strings.", %e;
+ }
+ }
+
+ die "This shouldn't happen!";
+}
+
+
+## POE handlers
+
+sub _start {
+ $_[KERNEL]->alias_set('api');
+ $_[KERNEL]->sig(shutdown => 'shutdown');
+
+ # create listen socket
+ $_[HEAP]{listen} = POE::Wheel::SocketFactory->new(
+ BindPort => $_[HEAP]{port},
+ Reuse => 1,
+ FailureEvent => 'server_error',
+ SuccessEvent => 'client_connect',
+ );
+ $_[KERNEL]->yield(log => 0, 'API starting up on port %d', $_[HEAP]{port});
+}
+
+
+sub shutdown {
+ $_[KERNEL]->alias_remove('api');
+ $_[KERNEL]->yield(log => 0, 'API shutting down');
+ delete $_[HEAP]{listen};
+ delete $_[HEAP]{c}{$_}{wheel} for (keys %{$_[HEAP]{c}});
+}
+
+
+sub log {
+ my($c, $msg, @args) = @_[ARG0..$#_];
+ if(open(my $F, '>>', $_[HEAP]{logfile})) {
+ printf $F "[%s] %s: %s\n", scalar localtime,
+ $c ? sprintf '%d %s', $c->{wheel}->ID(), $c->{ip} : 'global',
+ @args ? sprintf $msg, @args : $msg;
+ close $F;
+ }
+}
+
+
+sub server_error {
+ return if $_[ARG0] eq 'accept' && $_[ARG1] == ECONNABORTED;
+ $_[KERNEL]->yield(log => 0, 'Server socket failed on %s: (%s) %s', @_[ ARG0..ARG2 ]);
+ $_[KERNEL]->call(core => log => 'API shutting down due to error.');
+ $_[KERNEL]->yield('shutdown');
+}
+
+
+sub client_connect {
+ my $ip = inet_ntoa($_[ARG1]);
+ my $sock = $_[ARG0];
+
+ if($_[HEAP]{conn_per_ip} <= grep $ip eq $_[HEAP]{c}{$_}{ip}, keys %{$_[HEAP]{c}}) {
+ $_[KERNEL]->yield(log => 0,
+ 'Connect from %s denied, limit of %d connections per IP reached', $ip, $_[HEAP]{conn_per_ip});
+ close $sock;
+ return;
+ }
+
+ # set TCP keepalive (silently ignoring errors, it's not really important)
+ my $keep = $_[HEAP]{tcp_keepalive};
+ $keep && eval {
+ setsockopt($sock, SOL_SOCKET, SO_KEEPALIVE, 1);
+ setsockopt($sock, IPPROTO_TCP, TCP_KEEPIDLE, $keep->[0]);
+ setsockopt($sock, IPPROTO_TCP, TCP_KEEPINTVL, $keep->[1]);
+ setsockopt($sock, IPPROTO_TCP, TCP_KEEPCNT, $keep->[2]);
+ };
+
+ # the wheel
+ my $w = POE::Wheel::ReadWrite->new(
+ Handle => $sock,
+ Filter => POE::Filter::VNDBAPI->new(type => 'server'),
+ ErrorEvent => 'client_error',
+ InputEvent => 'client_input',
+ );
+ $_[HEAP]{c}{ $w->ID() } = {
+ wheel => $w,
+ ip => $ip,
+ };
+ $_[KERNEL]->yield(log => $_[HEAP]{c}{ $w->ID() }, 'Connected');
+}
+
+
+sub client_error { # func, errno, errmsg, wheelid
+ my $c = $_[HEAP]{c}{$_[ARG3]};
+ if($_[ARG0] eq 'read' && ($_[ARG1] == 0 || $_[ARG1] == ECONNRESET)) {
+ $_[KERNEL]->yield(log => $c, 'Disconnected');
+ } else {
+ $_[KERNEL]->yield(log => $c, 'SOCKET ERROR on operation %s: (%s) %s', @_[ARG0..ARG2]);
+ }
+ delete $_[HEAP]{c}{$_[ARG3]};
+}
+
+
+sub client_input {
+ my($arg, $id) = @_[ARG0,ARG1];
+ my $cmd = shift @$arg;
+ my $c = $_[HEAP]{c}{$id};
+
+ return cerr $c, $arg->[0]{id}, $arg->[0]{msg} if !defined $cmd;
+
+ # when we're here, we can assume that $cmd contains a valid command
+ # and the arguments are syntactically valid
+
+ # login
+ return $_[KERNEL]->yield(login => $c, @$arg) if $cmd eq 'login';
+
+ return cerr $c, needlogin => 'Not logged in.' if !$c->{username};
+ # TODO: throttling
+
+ # get
+ 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 ne 'vn';
+ $_[KERNEL]->yield("get_$type", $c, @$arg);
+}
+
+
+sub login {
+ my($c, $arg) = @_[ARG0,ARG1];
+
+ # validation (bah)
+ return cerr $c, loggedin => 'Already logged in, please reconnect to start a new session' if $c->{username};
+ for (qw|protocol client clientver username password|) {
+ !exists $arg->{$_} && return cerr $c, missing => "Required field '$_' is missing", field => $_;
+ !defined $arg->{$_} && return cerr $c, badarg => "Field '$_' cannot be null", field => $_;
+ # note that 'true' and 'false' are also refs
+ ref $arg->{$_} && return cerr $c, badarg => "Field '$_' must be a scalar", field => $_;
+ }
+ return cerr $c, badarg => 'Unkonwn protocol version', field => 'protocol' if $arg->{protocol} ne '1';
+ return cerr $c, badarg => 'Invalid client name', field => 'client' if $arg->{client} !~ /^[a-zA-Z0-9 _-]{3,50}$/;
+ return cerr $c, badarg => 'Invalid client version', field => 'clientver' if $arg->{clientver} !~ /^\d+(\.\d+)?$/;
+ return cerr $c, sesslimit => "Too many open sessions for user '$arg->{username}'", max_allowed => $_[HEAP]{sess_per_user}
+ if $_[HEAP]{sess_per_user} <= grep $arg->{username} eq $_[HEAP]{c}{$_}{username}, keys %{$_[HEAP]{c}};
+
+ # fetch user info
+ $_[KERNEL]->post(pg => query => "SELECT rank, salt, encode(passwd, 'hex') as passwd FROM users WHERE username = ?",
+ [ $arg->{username} ], 'login_res', [ $c, $arg ]);
+}
+
+
+sub login_res { # num, res, [ c, arg ]
+ my($num, $res, $c, $arg) = (@_[ARG0, ARG1], $_[ARG2][0], $_[ARG2][1]);
+
+ return cerr $c, auth => "No user with the name '$arg->{username}'" if $num == 0;
+ return cerr $c, auth => "Outdated password format, please relogin on $VNDB::S{url}/ and try again" if $res->[0]{salt} =~ /^ +$/;
+
+ my $encrypted = sha256_hex($VNDB::S{global_salt}.encode_utf8($arg->{password}).encode_utf8($res->[0]{salt}));
+ return cerr $c, auth => "Wrong password for user '$arg->{username}'" if lc($encrypted) ne lc($res->[0]{passwd});
+
+ $c->{wheel}->put(['ok']);
+ $c->{username} = $arg->{username};
+ $_[KERNEL]->yield(log => $c,
+ 'Successful login by %s using client "%s" ver. %s', $arg->{username}, $arg->{client}, $arg->{clientver});
+}
+
+
+sub get_vn {
+ my($c, $info, $filters) = @_[ARG0..$#_];
+
+ return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep $_ ne 'basic', @$info);
+
+ my $select = 'v.id, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms';
+
+ my @placeholders;
+ my $where = encode_filters $filters, \&filtertosql, $c, \@placeholders, [
+ [ id => 'int', dbfield => 'v.id' ],
+ [ title => 'str', dbfield => 'vr.title' ],
+ [ original => 'str', dbfield => 'vr.original', null => '' ],
+ ];
+ warn $where;
+ return if !$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, $info, $filters ]);
+}
+
+
+sub get_vn_res {
+ my($num, $res, $c, $info, $filters, $time) = (@_[ARG0, ARG1], @{$_[ARG2]}, $_[ARG3]);
+
+ for (@$res) {
+ $_->{id}*=1;
+ $_->{original} ||= undef;
+ $_->{platforms} = [ split /\//, delete $_->{c_platforms} ];
+ $_->{languages} = [ split /\//, delete $_->{c_languages} ];
+ $_->{released} = formatdate delete $_->{c_released};
+ }
+
+ $c->{wheel}->put([ results => { num => $#$res+1, items => $res }]);
+ $_[KERNEL]->yield(log => $c, "%4.0fms %2d get vn %s %s", $time*1000, $#$res+1, join (',', @$info), encode_filters $filters);
+}
+
+
+1;
+
diff --git a/lib/POE/Filter/VNDBAPI.pm b/lib/POE/Filter/VNDBAPI.pm
new file mode 100644
index 00000000..0eca3eb2
--- /dev/null
+++ b/lib/POE/Filter/VNDBAPI.pm
@@ -0,0 +1,303 @@
+# Implements a POE::Filter for the VNDB API, and includes basic error checking
+#
+# Currently recognised commands and their mapping between Perl and strings
+# (this is just a simple overview, actual implementation is more advanced)
+#
+# C: login
+# [ 'login', {object} ]
+#
+# C: get
+# [ 'get', , [ split ',', $2 ], [ filters ] ]
+# must match /[a-z\/_]+/
+# as string: /[a-z_]+(,[a-z_]+)*/, in perl: [ /[a-z_]+/, .. ]
+#
+# S: ok
+# [ 'ok' ]
+#
+# S: results
+# [ 'results', {object} ]
+#
+# S: error
+# [ 'error', {object} ]
+#
+# :
+# string: (( ) ( ))
+# perl: [ [ 'field', 'op', value ], 'bool-op', [ 'field', 'op', value ] ]
+# must match /[a-z_]+/
+# must be one of =, !=, <, >, >=, <= or ~
+# whitespace around fields/ops/json-values/bool-ops are ignored.
+#
+# When type='server', put() will accept the objects marked by 'S' and get() will accept the strings marked by 'C'
+# When type='client', put() will accept the objects marked by 'C' and get() will accept the strings marked by 'S'
+#
+# When invalid data is given to put(), ...don't do that, seriously.
+# When invalid data is given to get(), it will return the following arrayref:
+# [ undef, { id => 'parse', msg => 'error message' } ]
+# When type='server', a valid error response can be sent back simply by
+# changing the undef to 'error' and forwarding the arrayref to put()
+#
+# See the POE::Filter documentation for information on how to use this module.
+# This module supports filter switching (which will be required to implement
+# gzip compression or backwards compatibility on API changes)
+# Note that this module is also suitable for use outside of the POE framework.
+
+
+package POE::Filter::VNDBAPI;
+
+use strict;
+use warnings;
+use JSON::XS;
+use Encode 'decode_utf8', 'encode_utf8';
+use Exporter 'import';
+
+our @EXPORT_OK = qw|decode_filters encode_filters|;
+
+
+my $EOT = "\x04"; # End Of Transmission, this string is searched in the binary data using index()
+my $WS = qr/[\x20\x09\x0a\x0d]/; # witespace as defined by RFC4627
+my $GET_TYPE = qr/(?:[a-z\/_]+)/; # get
+my $GET_INFO = qr/(?:[a-z_]+(?:,[a-z_]+)*)/; # get
+my $FILTER_FIELD = qr/(?:[a-z_]+)/; # in the filters
+my $FILTER_OP = qr/(?:=|!=|<|>|>=|<=|~)/; # in the filters
+my $FILTER_BOOL = qr/(?:and|or)/; # in the filters
+
+
+sub new {
+ my($class, %o) = @_;
+ my $type = ($o{type}||'') eq 'server' ? 'server' : 'client';
+ return bless {
+ type => $type,
+ buffer => ''
+ }, $class;
+}
+
+
+sub clone {
+ my $self = shift;
+ return bless {
+ type => $self->{type},
+ }, ref $self;
+}
+
+
+sub get {
+ my ($self, $data) = @_;
+ my @r;
+
+ $self->get_one_start($data);
+ my $d;
+ do {
+ $d = $self->get_one();
+ push @r, @$d if @$d;
+ } while(@$d);
+
+ return \@r;
+}
+
+
+sub get_one_start {
+ my($self, $data) = @_;
+ $self->{buffer} .= join '', @$data;
+}
+
+
+sub get_pending {
+ my $self = shift;
+ return $self->{buffer} ne '' ? [ $self->{buffer} ] : undef;
+}
+
+
+sub _err($) { [ [ undef, { id => 'parse', msg => $_[0] } ] ] };
+
+sub get_one {
+ my $self = shift;
+ # look for EOT
+ my $end = index $self->{buffer}, $EOT;
+ return [] if $end < 0;
+ my $str = substr $self->{buffer}, 0, $end;
+ $self->{buffer} = substr $self->{buffer}, $end+1;
+
+ # $str now contains our request/response encoded in UTF8, time to decode
+ $str = eval { decode_utf8($str, Encode::FB_CROAK); };
+ return _err "Encoding error: $@" if !defined $str;
+
+ # C: login
+ # S: error, results
+ if($str =~ /^$WS*(login|error|results)$WS+(.+)$/s && ($self->{type} eq 'server' && $1 eq 'login' || $self->{type} eq 'client' && $1 ne 'login')) {
+ my($cmd, $json) = ($1, $2);
+ $json = eval { JSON::XS->new->decode($json) };
+ if(!defined $json) {
+ my $err = $@;
+ $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//;
+ return _err "JSON-decode: $err" if !defined $json;
+ }
+ return _err qq|"$cmd" command requires a JSON object| if ref($json) ne 'HASH';
+ return [[ $cmd, $json ]];
+ }
+
+ # C: get
+ if($self->{type} eq 'server' && $str =~ /^$WS*get$WS+($GET_TYPE)$WS+($GET_INFO)$WS+(.+)$/s) {
+ my($type, $info) = ($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 ]];
+ }
+
+ # S: ok
+ if($self->{type} eq 'client' && $str =~ /^$WS*ok$WS*$/) {
+ return [[ 'ok' ]];
+ }
+
+ # if we're here, we've received something strange
+ return _err 'Invalid command or argument';
+}
+
+
+# arguments come from the application and are assumed to be correct,
+# passing incorrect arguments will result in undefined behaviour.
+sub put {
+ my($self, $cmds) = @_;
+ my @r;
+ for my $p (@$cmds) {
+ my $cmd = shift @$p;
+
+ # C: login
+ push @r, 'login '.JSON::XS->new->encode($p->[0])
+ if $self->{type} eq 'client' && $cmd eq 'login';
+
+ # C: get
+ push @r, sprintf 'get %s %s %s', $p->[0], join(',',@{$p->[1]}), encode_filters($p->[2])
+ if $self->{type} eq 'client' && $cmd eq 'get';
+
+ # S: ok
+ push @r, 'ok'
+ if $self->{type} eq 'server' && $cmd eq 'ok';
+
+ # S: error, results
+ push @r, "$cmd ".JSON::XS->new->encode($p->[0])
+ if $self->{type} eq 'server' && ($cmd eq 'error' || $cmd eq 'results');
+ }
+ # the $EOT can also be passed through encode_utf8(), the result is the same.
+ return [ map encode_utf8($_).$EOT, @r ];
+}
+
+
+# decodes " ", and returns the arrayref and the remaining (unparsed) string after
+sub decode_filter_expr {
+ my $str = shift;
+ return ('Invalid filter expression') if $str !~ /^$WS*($FILTER_FIELD)$WS*($FILTER_OP)([^=].*)$/s;
+ my($field, $op, $val) = ($1, $2, $3);
+ my($value, $chars) = eval { JSON::XS->new->allow_nonref->decode_prefix($val) };
+ if(!defined $chars) {
+ my $err = $@;
+ $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//;
+ return ("Invalid JSON value in filter expression: $err");
+ }
+ $str = substr $val, $chars;
+ return ([ $field, $op, $value ], $str);
+}
+
+
+sub decode_filters {
+ my($str, $sub) = @_;
+ $sub ||= 0;
+ my @r;
+ return ('Too many nested filter expressions') if $sub > 10;
+ return ('Filter must start with a (') if !$sub && $str !~ s/^$WS*\(//;
+ while(length $str && $str !~ /^$WS*\)/) {
+ my $ret;
+ $str =~ s/^$WS+//;
+ # AND/OR
+ if(@r%2 == 1 && $str =~ s/^($FILTER_BOOL)//) {
+ push @r, $1;
+ next;
+ }
+ # sub-expression ()
+ if($str =~ s/^\(//) {
+ ($ret, $str) = decode_filters($str, $sub+1);
+ return ($ret) if !ref $ret;
+ return ('Unterminated ( in filter expression') if $str !~ s/^$WS*\)//;
+ push @r, $ret;
+ next;
+ }
+ #
+ ($ret, $str) = decode_filter_expr($str);
+ return ($ret) if !ref $ret;
+ push @r, $ret;
+ }
+ return ('Unterminated ( in filter expression') if !$sub && $str !~ s/^$WS*\)//;
+ # validate what we have parsed
+ return ('Empty filter expression') if !@r;
+ return ('Invalid filter expression') if @r % 2 != 1 || grep ref $r[$_] eq ($_%2 ? 'ARRAY' : ''), 0..$#r;
+ return (@r == 1 ? @r : \@r, $str);
+}
+
+
+# arguments: arrayref returned by decode_filters and an optional serialize function,
+# this function is called for earch filter expression, in the same order the expressions
+# are serialized. Should return the serialized string. This can be used to easily
+# convert the filters into SQL.
+sub encode_filters {
+ my($fil, $func, @extra) = @_;
+ return '('.join('', map {
+ if(!ref $_) { # and/or
+ " $_ "
+ } elsif(ref $_->[0]) { # sub expression
+ my $v = encode_filters($_, $func, @extra);
+ return undef if !defined $v;
+ $v
+ } else { # expression
+ my $v = $func ? $func->($_, @extra) : "$_->[0] $_->[1] ".JSON::XS->new->allow_nonref->encode($_->[2]);
+ return undef if !defined $v;
+ $v
+ }
+ } ref($fil->[0]) ? @$fil : $fil).')';
+}
+
+
+1;
+
+
+__END__
+
+# and here is a relatively comprehensive test suite for the above implementation of decode_filter()
+
+use lib '/home/yorhel/dev/vndb/lib';
+use POE::Filter::VNDBAPI 'decode_filters';
+require Test::More;
+use utf8;
+my @tests = (
+ # these should all parse fine
+ [q|(test = 1)|, ['test', '=', '1'], ''],
+ [q|((vn_name ~ "20") and length > 2)|, [['vn_name', '~', '20'], 'and', ['length', '>', 2]], ''],
+ [q|(padding < ["val1", 4]) padding|, ['padding', '<', ['val1', 4]], ' padding' ],
+ [q|(s=nulland_f<3)()|, [['s', '=', undef], 'and', ['_f', '<', 3]], '()'],
+ [qq|\r(p\r\t=\n \t\r3\n\n)|, ['p', '=', 3], ''],
+ [q|(s=4and((m="3"ort="str")or_g_={}))|, [['s','=',4],'and',[[['m','=','3'],'or',['t','=','str']],'or',['_g_','=',{}]]], ''],
+ [q|(z = ")\"){})")|, ['z', '=', ')"){})'], '' ],
+ [q| (name ~ "月姫") |, ['name', '~', '月姫'], ' ' ],
+ [q| (id >= 2) |, ['id', '>=', 2], ' '],
+ [q|(original = null)|, ['original', '=', undef], ''],
+ # and these should fail
+ [q|(name = true|],
+ [q|(and (f=1) or g=1)|],
+ [q|name = null|],
+ [q|(invalid-field > 6)|],
+ [q|(invalid ~ "JSON)|],
+ [q|()|],
+ [q|(v = 2 and ())|],
+);
+import Test::More tests => ($#tests+1)*2;
+for (@tests) {
+ my @ret = decode_filters($_->[0]);
+ if($_->[1]) {
+ is_deeply($ret[0], $_->[1]);
+ is($ret[1], $_->[2]);
+ } else {
+ ok(ref $ret[0] eq '', "nonref: $_->[0]");
+ is($ret[1], undef, "rest: $_->[0]");
+ }
+}
+
+
--
cgit v1.2.3
From c75e1e48947d12bc224b114f367b97fcb26c68f5 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Wed, 4 Nov 2009 06:52:22 +0100
Subject: API: Fixed perl warning and removed debug info
---
lib/Multi/API.pm | 3 +--
1 file changed, 1 insertion(+), 2 deletions(-)
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 1a00e565..37e68125 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -252,7 +252,7 @@ sub login {
return cerr $c, badarg => 'Invalid client name', field => 'client' if $arg->{client} !~ /^[a-zA-Z0-9 _-]{3,50}$/;
return cerr $c, badarg => 'Invalid client version', field => 'clientver' if $arg->{clientver} !~ /^\d+(\.\d+)?$/;
return cerr $c, sesslimit => "Too many open sessions for user '$arg->{username}'", max_allowed => $_[HEAP]{sess_per_user}
- if $_[HEAP]{sess_per_user} <= grep $arg->{username} eq $_[HEAP]{c}{$_}{username}, keys %{$_[HEAP]{c}};
+ if $_[HEAP]{sess_per_user} <= grep $_[HEAP]{c}{$_}{username} && $arg->{username} eq $_[HEAP]{c}{$_}{username}, keys %{$_[HEAP]{c}};
# fetch user info
$_[KERNEL]->post(pg => query => "SELECT rank, salt, encode(passwd, 'hex') as passwd FROM users WHERE username = ?",
@@ -289,7 +289,6 @@ sub get_vn {
[ title => 'str', dbfield => 'vr.title' ],
[ original => 'str', dbfield => 'vr.original', null => '' ],
];
- warn $where;
return if !$where;
$_[KERNEL]->post(pg => query =>
--
cgit v1.2.3
From 39eec8b430d4cf605dc50035a4d1bdec53634d06 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Wed, 4 Nov 2009 06:54:07 +0100
Subject: API: Removed some leading whitespace
---
data/docs/11 | 2 +-
lib/Multi/API.pm | 4 ++--
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 7927182b..3f1a4615 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -187,7 +187,7 @@ however still required.
the message explains what went wrong in more detail, there are several
different messages for the same id. The following error identifiers are currently
defined:
-
+
- parse
- Syntax error or unknown command.
- missing
- A JSON object argument is missing a required member. The name of which is given in the additional "field" member.
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 37e68125..01e95ac5 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -128,7 +128,7 @@ sub filtertosql {
sub _start {
$_[KERNEL]->alias_set('api');
$_[KERNEL]->sig(shutdown => 'shutdown');
-
+
# create listen socket
$_[HEAP]{listen} = POE::Wheel::SocketFactory->new(
BindPort => $_[HEAP]{port},
@@ -278,7 +278,7 @@ sub login_res { # num, res, [ c, arg ]
sub get_vn {
my($c, $info, $filters) = @_[ARG0..$#_];
-
+
return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep $_ ne 'basic', @$info);
my $select = 'v.id, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms';
--
cgit v1.2.3
From 143925419c36cb3bab0ee772fa32bd9202667ff8 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Wed, 4 Nov 2009 07:36:18 +0100
Subject: API: Added vn search filter
---
lib/Multi/API.pm | 18 ++++++++++++++++++
1 file changed, 18 insertions(+)
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 01e95ac5..5012596d 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -119,6 +119,23 @@ sub filtertosql {
}
}
+ # vn search
+ if($type eq 'vnsearch') {
+ return cerr $c, filterval => "Operator for '$field' must be ~.", %e if $op ne '~';
+ return cerr $c, filterop => "Value for '$field' must be a string.", %e if !defined $value || ref $value;
+ push @$p, "%$value%" for (1..4);
+ return q|(vr.title ILIKE ?
+ OR vr.alias ILIKE ?
+ OR v.id IN(
+ SELECT rv.vid
+ FROM releases r
+ JOIN releases_rev rr ON rr.id = r.latest
+ JOIN releases_vn rv ON rv.rid = rr.id
+ WHERE rr.title ILIKE ?
+ OR rr.original ILIKE ?
+ ))|;
+ }
+
die "This shouldn't happen!";
}
@@ -288,6 +305,7 @@ sub get_vn {
[ id => 'int', dbfield => 'v.id' ],
[ title => 'str', dbfield => 'vr.title' ],
[ original => 'str', dbfield => 'vr.original', null => '' ],
+ [ search => 'vnsearch' ],
];
return if !$where;
--
cgit v1.2.3
From c1d45ea2305cfb428c7378dfe99d930806c0253b Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Thu, 5 Nov 2009 15:27:36 +0100
Subject: Multi::API: Rewrote filtertosql()
The filters are now defined in the arguments rather than the function,
the function simply provides the necessary means to define the filters
in a concise way.
This also means there's a lot less error handling code necessary, and
therefore merged the 'filter*' error messages into one 'filter' error.
This code is far more readable and maintainable than the previous
implementation.
---
data/docs/11 | 4 +-
lib/Multi/API.pm | 215 ++++++++++++++++++++++++++++++++++++++-----------------
2 files changed, 150 insertions(+), 69 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 3f1a4615..f07c1a81 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -198,9 +198,7 @@ however still required.
- sesslimit
- (login) Too many open sessions for the current user.
- gettype
- (get) Unknown type argument to the 'get' command.
- getinfo
- (get) Unknown info flag to the 'get' command. The name of the unrecognised flag is given in an additional "flag" member.
- - filterop
- (get) Bad operator in filter expression for the field/value combination. Includes three additional members: "field", "op" and "value" of the incorrect expression.
- - filterval
- (get) Wrong type or format for the value in a filter expression. Adds same three members as the 'filterop' error.
- - filterfield
- (get) Unknown field in filter expression. Adds same three members as the 'filterop' error.
+ - filter
- (get) Unknown filter field or invalid combination of field/operator/argument type. Includes three additional members: "field", "op" and "value" of the incorrect expression.
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 5012596d..f76585c3 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -63,80 +63,77 @@ sub formatdate {
}
+# see the notes after __END__ for an explanation of what this function does
sub filtertosql {
my($c, $p, $t, $field, $op, $value) = ($_[1], $_[2], $_[3], @{$_[0]});
my %e = ( field => $field, op => $op, value => $value );
+ # get the field that matches
$t = (grep $_->[0] eq $field, @$t)[0];
- return cerr $c, filterfield => "Unknown field '$field'", %e if !$t;
+ return cerr $c, filter => "Unknown field '$field'", %e if !$t;
shift @$t; # field name
- my $type = shift @$t;
- my %o = @$t;
-
- # integer, options: dbfield
- if($type eq 'int') {
- if($value && ref $value eq 'ARRAY') {
- return cerr $c, filterop => "Operator for '$field' must be either = or != for array values", %e if $op ne '=' && $op ne '!=';
- return cerr $c, filterval => "Array elements for '$field' must be integers", %e if grep !defined($_) || !/^\d+$/, @$value;
- push @$p, @$value;
- return sprintf '%s %s(%s)', $o{dbfield}, $op eq '=' ? 'IN' : 'NOT IN', join ',', map '?', @$value;
- } elsif(defined $value && !ref $value && $value =~ /^\d+$/) {
- my @ops = qw(= != > >= < <=);
- return cerr $c, filterop => "Operator for '$field' must be one of ".join(', ', @ops), %e if !grep $op eq $_, @ops;
- push @$p, $value;
- return sprintf '%s %s ?', $o{dbfield}, $op eq '!=' ? '<>' : $op;
- }
- return cerr $c, filterval => "Value for '$field' must be either an integer or an array of integers", %e;
- }
- # string, options: dbfield, null
- if($type eq 'str') {
- if(!defined $value) {
- return cerr $c, filterval => "null not allowed for '$field'", %e if !exists $o{null};
- return cerr $c, filterop => "Operator for '$field' must be either = or != for null", %e if $op ne '=' && $op ne '!=';
- return sprintf '%s %s', $o{dbfield}, $op eq '=' ? 'IS NULL' : 'IS NOT NULL' if !defined $o{null};
- push @$p, $o{null};
- return sprintf '%s %s ?', $o{dbfield}, $op eq '=' ? '=' : '<>';
- } elsif(ref($value) eq 'ARRAY') {
- return cerr $c, filterop => "Operator for '$field' must be either = or != for array values", %e if $op ne '=' && $op ne '!=';
- return cerr $c, filterval => "Array elements for '$field' must be scalars", %e if grep !defined($_) || ref($_), @$value;
- push @$p, @$value;
- return sprintf '%s %s(%s)', $o{dbfield}, $op eq '=' ? 'IN' : 'NOT IN', join ',', map '?', @$value;
- } elsif(!ref $value) {
- my @ops = qw(= != ~);
- if($op eq '=' || $op eq '!=') {
- push @$p, $value;
- return sprintf '%s %s ?', $o{dbfield}, $op eq '!=' ? '<>' : $op;
- } elsif($op eq '~') {
- $value =~ s/%//;
- push @$p, "%$value%";
- return sprintf '%s ILIKE ?', $o{dbfield};
- } else {
- return cerr $c, filterop => "Operator for '$field' must be =, != or ~", %e;
- }
- } else {
- return cerr $c, filterval => "Value for '$field' must be a string or an array of strings.", %e;
+ # get the type that matches
+ $t = (grep +(
+ # wrong operator? don't even look further!
+ !$_->[2]{$op} ? 0
+ # undef
+ : !defined($_->[0]) ? !defined($value)
+ # int
+ : $_->[0] eq 'int' ? (defined($value) && !ref($value) && $value =~ /^-?\d+$/)
+ # str
+ : $_->[0] eq 'str' ? defined($value) && !ref($value)
+ # inta
+ : $_->[0] eq 'inta' ? ref($value) eq 'ARRAY' && !grep(!defined($_) || ref($_) || $_ !~ /^-?\d+$/, @$value)
+ # stra
+ : $_->[0] eq 'stra' ? ref($value) eq 'ARRAY' && !grep(!defined($_) || ref($_), @$value)
+ # oops
+ : die "Invalid filter type $_->[0]"
+ ), @$t)[0];
+ return cerr $c, filter => 'Wrong field/operator/expression type combination', %e if !$t;
+
+ my($type, $sql, $ops, %o) = @$t;
+
+ # substistute :op: in $sql, which is the same for all types
+ $sql =~ s/:op:/$ops->{$op}/g;
+
+ # no further processing required for type=undef
+ return $sql if !defined $type;
+
+ # pre-process the argument(s)
+ for (!$o{process} ? () : ref($value) eq 'ARRAY' ? @$value : $value) {
+ if(!ref $o{process}) {
+ $_ = sprintf $o{process}, $_;
+ } elsif(ref($o{process}) eq 'CODE') {
+ $_ = $o{process}->($_);
+ } elsif(${$o{process}} eq 'like') {
+ y/%//;
+ $_ = "%$_%";
}
}
- # vn search
- if($type eq 'vnsearch') {
- return cerr $c, filterval => "Operator for '$field' must be ~.", %e if $op ne '~';
- return cerr $c, filterop => "Value for '$field' must be a string.", %e if !defined $value || ref $value;
- push @$p, "%$value%" for (1..4);
- return q|(vr.title ILIKE ?
- OR vr.alias ILIKE ?
- OR v.id IN(
- SELECT rv.vid
- FROM releases r
- JOIN releases_rev rr ON rr.id = r.latest
- JOIN releases_vn rv ON rv.rid = rr.id
- WHERE rr.title ILIKE ?
- OR rr.original ILIKE ?
- ))|;
+ # type=str and type=int are now quite simple
+ if(!ref $value) {
+ $sql =~ s/:value:/push @$p, $value; '?'/eg;
+ return $sql;
}
- die "This shouldn't happen!";
+ # and do some processing for type=stra and type=inta
+ my @parameters;
+ if($o{serialize}) {
+ for (@$value) {
+ my $v = $o{serialize};
+ $v =~ s/:op:/$ops->{$op}/g;
+ $v =~ s/:value:/push @parameters, $_; '?'/eg;
+ $_ = $v;
+ }
+ } else {
+ @parameters = @$value;
+ $_ = '?' for @$value;
+ }
+ my $joined = join defined $o{join} ? $o{join} : '', @$value;
+ $sql =~ s/:value:/push @$p, @parameters; $joined/eg;
+ return $sql;
}
@@ -302,10 +299,32 @@ sub get_vn {
my @placeholders;
my $where = encode_filters $filters, \&filtertosql, $c, \@placeholders, [
- [ id => 'int', dbfield => 'v.id' ],
- [ title => 'str', dbfield => 'vr.title' ],
- [ original => 'str', dbfield => 'vr.original', null => '' ],
- [ search => 'vnsearch' ],
+ [ 'id',
+ [ 'int' => 'v.id :op: :value:', {qw|= = != <> > > < < <= <= >= >=|} ],
+ [ inta => 'v.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, join => ',' ],
+ ], [ 'title',
+ [ str => 'vr.title :op: :value:', {qw|= = != <>|} ],
+ [ str => 'vr.title ILIKE :value:', {'~',1}, process => \'like' ],
+ ], [ 'original',
+ [ undef, "vr.original :op: ''", {qw|= = != <>|} ],
+ [ str => 'vr.original :op: :value:', {qw|= = != <>|} ],
+ [ str => 'vr.original ILIKE :value:', {'~',1}, process => \'like' ]
+ ], [ 'platforms',
+ [ undef, "v.c_platforms :op: ''", {qw|= = != <>|} ],
+ [ str => 'v.c_platforms :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ],
+ [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_platforms LIKE :value:', \'like' ],
+ [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_platforms NOT LIKE :value:', \'like' ],
+ ], [ 'languages', # rather similar to platforms
+ [ undef, "v.c_languages :op: ''", {qw|= = != <>|} ],
+ [ str => 'v.c_languages :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ],
+ [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_languages LIKE :value:', process => \'like' ],
+ [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_languages NOT LIKE :value:', process => \'like' ],
+ ], [ 'search',
+ [ str => '(vr.title ILIKE :value: OR vr.alias ILIKE :value: OR v.id IN(
+ SELECT rv.vid FROM releases r JOIN releases_rev rr ON rr.id = r.latest JOIN releases_vn rv ON rv.rid = rr.id
+ WHERE rr.title ILIKE :value: OR rr.original ILIKE :value:
+ ))', {'~', 1}, process => \'like' ],
+ ],
];
return if !$where;
@@ -333,3 +352,67 @@ sub get_vn_res {
1;
+
+__END__
+
+Filter definitions:
+
+ [ 'field name', [ type, 'sql string', { filterop => sqlop, .. }, %options{process serialize join} ] ]
+ type (does not have to be unique, to support multiple operators with different SQL but with the same type):
+ undef (null)
+ 'str' (normal string)
+ 'int' (normal int)
+ 'stra' (array of strings)
+ 'inra' (array of ints)
+ sql string:
+ The relevant SQL string, with :op: and :value: subsistutions. :value: is not available for type=undef
+ join: (only used when type is an array)
+ scalar, join string used when joining multiple values.
+ serialize: (serializes the values before join()'ing, only for arrays)
+ scalar, :op: and :value: subsistution
+ process: (process the value(s) that will be passed to Pg)
+ scalar, %s subsitutes the value
+ sub, argument = value, returns new value
+ scalarref, template:
+ \'like' => sub { (local$_=shift)=~y/%//; lc "%$_%" }
+
+ example for v.id:
+ [ 'id',
+ [ int => 'v.id :op: :value:', {qw|= = != <> > > < < <= <= >= >=|} ],
+ [ inta => 'v.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, join => ',' ]
+ ]
+
+ example for vr.original:
+ [ 'original',
+ [ undef, "vr.original :op: ''", {qw|= = != <>|} ],
+ [ str => 'vr.original :op: :value:', {qw|= = != <>|} ],
+ [ str => 'vr.original :op: :value:', {qw|~ ILIKE|}, process => \'like' ],
+ ]
+
+ example for v.c_platforms:
+ [ 'platforms',
+ [ undef, "v.c_platforms :op: ''", {qw|= = != <>|} ],
+ [ str => 'v.c_platforms :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ],
+ [ stra => '(:value:)', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, join => ' or ', serialize => 'v.c_platforms :op: :value:', process => \'like' ],
+ ]
+
+ example for the VN search:
+ [ 'search', [ '(vr.title ILIKE :value:
+ OR vr.alias ILIKE :value:
+ OR v.id IN(
+ SELECT rv.vid
+ FROM releases r
+ JOIN releases_rev rr ON rr.id = r.latest
+ JOIN releases_vn rv ON rv.rid = rr.id
+ WHERE rr.title ILIKE :value:
+ OR rr.original ILIKE :value:
+ ))', {'~', 1}, process => \'like'
+ ]],
+
+ example for vn_anime (for the sake of the example...)
+ [ 'anime',
+ [ undef, ':op:(SELECT 1 FROM vn_anime va WHERE va.vid = v.id)', {'=' => 'EXISTS', '!=' => 'NOT EXISTS'} ],
+ [ int => 'v.id :op:(SELECT va.vid FROM vn_anime va WHERE va.aid = :value:)', {'=' => 'IN', '!=' => 'NOT IN'} ],
+ [ inta => 'v.id :op:(SELECT va.vid FROM vn_anime va WHERE va.aid IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ','],
+ ]
+
--
cgit v1.2.3
From a155333afd59c5fadee50a5b99f953c8ddc800af Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Thu, 5 Nov 2009 15:59:28 +0100
Subject: Multi::API: Don't modify filter and type arguments in filtertosql()
This fixes two bugs:
- The filter values logged in api.log weren't correct
- When using the same filter field for a second time in the same filter,
it wouldn't be recognised because the type name has been shift()'ed.
---
lib/Multi/API.pm | 15 ++++++++-------
1 file changed, 8 insertions(+), 7 deletions(-)
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index f76585c3..53bb640a 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -71,7 +71,7 @@ sub filtertosql {
# get the field that matches
$t = (grep $_->[0] eq $field, @$t)[0];
return cerr $c, filter => "Unknown field '$field'", %e if !$t;
- shift @$t; # field name
+ $t = [ @$t[1..$#$t] ];
# get the type that matches
$t = (grep +(
@@ -101,7 +101,8 @@ sub filtertosql {
return $sql if !defined $type;
# pre-process the argument(s)
- for (!$o{process} ? () : ref($value) eq 'ARRAY' ? @$value : $value) {
+ my @values = ref($value) eq 'ARRAY' ? @$value : $value;
+ for (!$o{process} ? () : @values) {
if(!ref $o{process}) {
$_ = sprintf $o{process}, $_;
} elsif(ref($o{process}) eq 'CODE') {
@@ -114,24 +115,24 @@ sub filtertosql {
# type=str and type=int are now quite simple
if(!ref $value) {
- $sql =~ s/:value:/push @$p, $value; '?'/eg;
+ $sql =~ s/:value:/push @$p, $values[0]; '?'/eg;
return $sql;
}
# and do some processing for type=stra and type=inta
my @parameters;
if($o{serialize}) {
- for (@$value) {
+ for (@values) {
my $v = $o{serialize};
$v =~ s/:op:/$ops->{$op}/g;
$v =~ s/:value:/push @parameters, $_; '?'/eg;
$_ = $v;
}
} else {
- @parameters = @$value;
- $_ = '?' for @$value;
+ @parameters = @values;
+ $_ = '?' for @values;
}
- my $joined = join defined $o{join} ? $o{join} : '', @$value;
+ my $joined = join defined $o{join} ? $o{join} : '', @values;
$sql =~ s/:value:/push @$p, @parameters; $joined/eg;
return $sql;
}
--
cgit v1.2.3
From 1ff074a56239fbd8e847010ba21e00067103afac Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Thu, 5 Nov 2009 17:58:41 +0100
Subject: API: Updated the documentation to reflect the current implementation
---
data/docs/11 | 96 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 96 insertions(+)
diff --git a/data/docs/11 b/data/docs/11
index f07c1a81..4f735ff6 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -8,6 +8,13 @@
:INC:index
:SUB:Introduction
+
+ This document describes the public API of VNDB and is intended to be read by
+ programmers. The API allows programs and websites to access (parts of) the VNDB
+ database without actually visiting a page on the website.
+
+
+
Design goals
-
@@ -170,6 +177,95 @@ however still required.
+:SUB:The 'get' command
+
+ This command is used to fetch data from the database. It accepts 3 arguments:
+ the type of data to fetch (e.g. visual novels or producers), what part of that
+ data to fetch (e.g. only the VN titles, or the descriptions and relations as
+ well), and lastly a filter expression.
+
+
+ get type flags filters
+
+
+ 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:
+
+
+ get vn basic,anime (id = 17)
+
+
+ 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:
+
+
+ results {"num":1, "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": []
+ }]}
+
+
+ Note that the actual result from the server can (and likely will) be formatted
+ differently and that the order of the members may not be the same. What each
+ member means and what possible values they can have differs per type and is
+ documented below.
+
+
+
+
+:SUB:get vn
+Returned object members:
+(the respective info flag is indicated within the parentheses)
+
+ - id (always present)
-
+ integer, visual novel ID.
+
- title (basic)
-
+ string, main title.
+
- original (basic)
-
+ string, original/official title. Can be null when not known/available.
+
- released (basic)
-
+ string, date of the first release. Can be null.
+
- languages (basic)
-
+ array of strings. Can be an empty array when nothing has been released yet.
+
- platforms (basic)
-
+ array of strings. Can be an empty array when unknown or nothing has been released yet.
+
+
+
+
+
+Filters:
+
+ - id
-
+ value: integer, operators: =, !=, >, >=, < and <=
+ value: array of integers, operators: = and !=
+ - title
-
+ value: string, operators: =, != and ~
+
- original
-
+ value: null, operators: = and !=
+ value: string, operators: =, != and ~
+ - platforms
-
+ values: null, string or array of strings, operators: = and !=
+ TODO: document platform codes.
+ - languages
-
+ values: null, string or array of strings, operators: = and !=
+ TODO: document language codes.
+ - search
-
+ value: string, operator: ~
+ This isn't an actual field, but performs a search on the titles of the visual
+ novel and its releases. Note that the algorithm of this search may change and
+ that it can use a different algorithm than the search function on the website.
+
+
+
+
:SUB:The 'error' response
--
cgit v1.2.3
From ed8192a652bc8c2b18c1cd9a52412faf0234966b Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Thu, 5 Nov 2009 23:01:55 +0100
Subject: API: Added 'details' info flag to the get vn command
---
data/docs/11 | 14 +++++++++++++-
lib/Multi/API.pm | 27 +++++++++++++++++++++------
2 files changed, 34 insertions(+), 7 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 4f735ff6..84a9f97f 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -236,11 +236,23 @@ however still required.
array of strings. Can be an empty array when nothing has been released yet.
- platforms (basic)
-
array of strings. Can be an empty array when unknown or nothing has been released yet.
+
- aliases (details)
-
+ string, comma-separated list of aliases. Can be null.
+
- length (details)
-
+ integer between 1 and 5, length of the game. null when unknown.
+
- description (details)
-
+ string, description of the VN. Note that this description can include formatting codes as described in d9.3.
+ null when whe don't have a description.
+
- links (details)
-
+ object. Contains the following members:
+ "wikipedia", string, name of the related article on the English Wikipedia.
+ "encubed", string, the URL-encoded tag used on encubed.
+ "renai", string, the name part of the url on renai.us.
+ All members can be null when no links are available or known to us.
-
Filters:
- id
-
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 53bb640a..c94c120b 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -294,9 +294,11 @@ sub login_res { # num, res, [ c, arg ]
sub get_vn {
my($c, $info, $filters) = @_[ARG0..$#_];
- return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep $_ ne 'basic', @$info);
+ return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details)$/, @$info);
- my $select = 'v.id, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms';
+ my $select = 'v.id';
+ $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;
my @placeholders;
my $where = encode_filters $filters, \&filtertosql, $c, \@placeholders, [
@@ -340,10 +342,23 @@ sub get_vn_res {
for (@$res) {
$_->{id}*=1;
- $_->{original} ||= undef;
- $_->{platforms} = [ split /\//, delete $_->{c_platforms} ];
- $_->{languages} = [ split /\//, delete $_->{c_languages} ];
- $_->{released} = formatdate delete $_->{c_released};
+ if(grep /basic/, @$info) {
+ $_->{original} ||= undef;
+ $_->{platforms} = [ split /\//, delete $_->{c_platforms} ];
+ $_->{languages} = [ split /\//, delete $_->{c_languages} ];
+ $_->{released} = formatdate delete $_->{c_released};
+ }
+ if(grep /details/, @$info) {
+ $_->{aliases} ||= undef;
+ $_->{length} *= 1;
+ $_->{length} ||= undef;
+ $_->{description} ||= undef;
+ $_->{links} = {
+ wikipedia => delete($_->{l_wp}) ||undef,
+ encubed => delete($_->{l_encubed})||undef,
+ renai => delete($_->{l_renai}) ||undef
+ };
+ }
}
$c->{wheel}->put([ results => { num => $#$res+1, items => $res }]);
--
cgit v1.2.3
From 6f16fef07dd1e964a999d943719dfa07d61351a5 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 6 Nov 2009 00:25:45 +0100
Subject: d11: Formatted the object members and filters as a table
I'd hate to have to maintain that HTML code, but at least those tables
look nicer in the browser.
---
data/docs/11 | 193 +++++++++++++++++++++++++++++++++++++++++------------------
1 file changed, 135 insertions(+), 58 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 84a9f97f..5a222372 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -216,66 +216,143 @@ however still required.
differently and that the order of the members may not be the same. What each
member means and what possible values they can have differs per type and is
documented below.
-
-
-
+
-:SUB:get vn
-Returned object members:
-(the respective info flag is indicated within the parentheses)
-
- - id (always present)
-
- integer, visual novel ID.
-
- title (basic)
-
- string, main title.
-
- original (basic)
-
- string, original/official title. Can be null when not known/available.
-
- released (basic)
-
- string, date of the first release. Can be null.
-
- languages (basic)
-
- array of strings. Can be an empty array when nothing has been released yet.
-
- platforms (basic)
-
- array of strings. Can be an empty array when unknown or nothing has been released yet.
-
- aliases (details)
-
- string, comma-separated list of aliases. Can be null.
-
- length (details)
-
- integer between 1 and 5, length of the game. null when unknown.
-
- description (details)
-
- string, description of the VN. Note that this description can include formatting codes as described in d9.3.
- null when whe don't have a description.
-
- links (details)
-
- object. Contains the following members:
- "wikipedia", string, name of the related article on the English Wikipedia.
- "encubed", string, the URL-encoded tag used on encubed.
- "renai", string, the name part of the url on renai.us.
- All members can be null when no links are available or known to us.
-
-
-
+Returned object members for the 'vn' type:
+
+
+ Member |
+ Flag |
+ Type |
+ null |
+ Description |
+
+
+ id |
+ - |
+ integer |
+ no |
+ Visual novel ID |
+
+
+ title |
+ basic |
+ string |
+ no |
+ Main title |
+
+
+ original |
+ basic |
+ string |
+ yes |
+ Original/official title. |
+
+
+ released |
+ basic |
+ date (string) |
+ yes |
+ Date of the first release. |
+
+
+ languages |
+ basic |
+ array of strings |
+ no |
+ Can be an empty array when nothing has been released yet. |
+
+
+ platforms |
+ basic |
+ array of strings |
+ no |
+ Can be an empty array when unknown or nothing has been released yet. |
+
+
+ aliases |
+ details |
+ string |
+ yes |
+ Comma-separated list of aliases. |
+
+
+ length |
+ details |
+ integer |
+ yes |
+ Length of the game, 1-5 |
+
+
+ description |
+ details |
+ string |
+ yes |
+ Description of the VN. Can include formatting codes as described in d9.3. |
+
+
+ links |
+ details |
+ object |
+ no |
+
+ Contains the following members:
+ "wikipedia", string, name of the related article on the English Wikipedia.
+ "encubed", string, the URL-encoded tag used on encubed.
+ "renai", string, the name part of the url on renai.us.
+ All members can be null when no links are available or known to us.
+ |
+
+
-Filters:
-
- - id
-
- value: integer, operators: =, !=, >, >=, < and <=
- value: array of integers, operators: = and !=
- - title
-
- value: string, operators: =, != and ~
-
- original
-
- value: null, operators: = and !=
- value: string, operators: =, != and ~
- - platforms
-
- values: null, string or array of strings, operators: = and !=
- TODO: document platform codes.
- - languages
-
- values: null, string or array of strings, operators: = and !=
- TODO: document language codes.
- - search
-
- value: string, operator: ~
- This isn't an actual field, but performs a search on the titles of the visual
- novel and its releases. Note that the algorithm of this search may change and
- that it can use a different algorithm than the search function on the website.
-
-
+Accepted filters for the 'vn' type:
+
+
+ Field |
+ Value |
+ Operators |
+ |
+
+
+ id |
+ integer array of integers |
+ = != > >= < <= = != |
+ |
+
+
+ title |
+ string |
+ = != ~ |
+ |
+
+
+ original |
+ null string |
+ = != = != ~ |
+ |
+
+
+ platforms |
+ null string array of strings |
+ = != |
+ |
+
+
+ languages |
+ null string array of strings |
+ = != |
+ |
+
+
+ search |
+ string |
+ ~ |
+
+ This is not an actual field, but performs a search on the titles of the visual
+ novel and its releases. Note that the algorithm of this search may change and
+ that it can use a different algorithm than the search function on the website.
+ |
+
--
cgit v1.2.3
From eb5970f52a3a0bdbd7077d3590a131b2fe604da5 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 6 Nov 2009 00:29:10 +0100
Subject: Multi::API: Fixed bug with accepting empty arrays as filter values
---
lib/Multi/API.pm | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index c94c120b..ddd907f1 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -84,9 +84,9 @@ sub filtertosql {
# str
: $_->[0] eq 'str' ? defined($value) && !ref($value)
# inta
- : $_->[0] eq 'inta' ? ref($value) eq 'ARRAY' && !grep(!defined($_) || ref($_) || $_ !~ /^-?\d+$/, @$value)
+ : $_->[0] eq 'inta' ? ref($value) eq 'ARRAY' && @$value && !grep(!defined($_) || ref($_) || $_ !~ /^-?\d+$/, @$value)
# stra
- : $_->[0] eq 'stra' ? ref($value) eq 'ARRAY' && !grep(!defined($_) || ref($_), @$value)
+ : $_->[0] eq 'stra' ? ref($value) eq 'ARRAY' && @$value && !grep(!defined($_) || ref($_), @$value)
# oops
: die "Invalid filter type $_->[0]"
), @$t)[0];
--
cgit v1.2.3
From f204eacba181acc025354e566a2fded59d0ecaaf Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 6 Nov 2009 11:48:15 +0100
Subject: API: Added release date filter to get vn, and some d11 updates
---
data/docs/11 | 18 +++++++++++++++---
lib/Multi/API.pm | 12 ++++++++++++
2 files changed, 27 insertions(+), 3 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 5a222372..58b86d4c 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -52,6 +52,7 @@ server resources and prevent abuse of this service.
- 5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.
- 3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.
+ - Each command currently returns at most 10 results. TODO: make configurable?
- more to come...
@@ -317,7 +318,10 @@ however still required.
id |
integer array of integers |
= != > >= < <= = != |
- |
+
+ When you need to fetch info about multiple VNs, it is recommended to do so
+ in one command using an array of integers as value. e.g. (id = [7,11,17]).
+ |
title |
@@ -332,18 +336,26 @@ however still required.
|
+ released |
+ null date (string) |
+ = != = != > >= < <= |
+
+ Note that matching on partial dates (released = "2009") doesn't do what
+ you want, use ranges instead, e.g. (released > "2008" and released <= "2009").
+ |
+
platforms |
null string array of strings |
= != |
|
-
+
languages |
null string array of strings |
= != |
|
-
+
search |
string |
~ |
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index ddd907f1..f734da7c 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -63,6 +63,14 @@ sub formatdate {
}
+sub parsedate {
+ return 0 if !defined $_[0];
+ return \'Invalid date value' if $_[0] !~ /^(?:tba|\d{4}(?:-\d{2}(?:-\d{2})?)?)$/;
+ my @v = split /-/, $_[0];
+ return $v[0] eq 'tba' ? 99999999 : @v==1 ? "$v[0]9999" : @v==2 ? "$v[0]$v[1]99" : $v[0].$v[1].$v[2];
+}
+
+
# see the notes after __END__ for an explanation of what this function does
sub filtertosql {
my($c, $p, $t, $field, $op, $value) = ($_[1], $_[2], $_[3], @{$_[0]});
@@ -107,6 +115,7 @@ sub filtertosql {
$_ = sprintf $o{process}, $_;
} elsif(ref($o{process}) eq 'CODE') {
$_ = $o{process}->($_);
+ return cerr $c, filter => $$_, %e if ref($_) eq 'SCALAR';
} elsif(${$o{process}} eq 'like') {
y/%//;
$_ = "%$_%";
@@ -312,6 +321,9 @@ sub get_vn {
[ undef, "vr.original :op: ''", {qw|= = != <>|} ],
[ str => 'vr.original :op: :value:', {qw|= = != <>|} ],
[ str => 'vr.original ILIKE :value:', {'~',1}, process => \'like' ]
+ ], [ 'released',
+ [ undef, 'v.c_released :op: 0', {qw|= = != <>|} ],
+ [ str => 'v.c_released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ],
], [ 'platforms',
[ undef, "v.c_platforms :op: ''", {qw|= = != <>|} ],
[ str => 'v.c_platforms :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ],
--
cgit v1.2.3
From 082be27b03d6b56e114a4b24101d1f5c0d8c3b53 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 6 Nov 2009 13:39:57 +0100
Subject: API: Added anime flag for the get vn command
---
data/docs/11 | 18 ++++++++++++
lib/Multi/API.pm | 84 ++++++++++++++++++++++++++++++++++++++++----------------
2 files changed, 78 insertions(+), 24 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 58b86d4c..d934ab8f 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -304,6 +304,24 @@ however still required.
All members can be null when no links are available or known to us.
+
+ anime |
+ anime |
+ array of objects |
+ no |
+
+ List of anime related to the VN, each object has the following members:
+ "id", integer, AniDB ID
+ "ann_id", integer, AnimeNewsNetwork ID
+ "nfo_id", string, AnimeNfo ID
+ "title_romaji", string
+ "title_kanji", string
+ "year", integer, year in which the anime was aired
+ "type", string
+ All members except the "id" can be null. Note that this data is courtesy of AniDB,
+ and may not reflect the latest state of their information due to caching.
+ |
+
Accepted filters for the 'vn' type:
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index f734da7c..8f99915b 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -303,9 +303,9 @@ sub login_res { # num, res, [ c, arg ]
sub get_vn {
my($c, $info, $filters) = @_[ARG0..$#_];
- return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details)$/, @$info);
+ return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|anime)$/, @$info);
- my $select = 'v.id';
+ 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;
@@ -345,36 +345,72 @@ sub get_vn {
$_[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, $info, $filters ]);
+ \@placeholders, 'get_vn_res', { c => $c, info => $info, filters => $filters });
}
sub get_vn_res {
- my($num, $res, $c, $info, $filters, $time) = (@_[ARG0, ARG1], @{$_[ARG2]}, $_[ARG3]);
-
- for (@$res) {
- $_->{id}*=1;
- if(grep /basic/, @$info) {
- $_->{original} ||= undef;
- $_->{platforms} = [ split /\//, delete $_->{c_platforms} ];
- $_->{languages} = [ split /\//, delete $_->{c_languages} ];
- $_->{released} = formatdate delete $_->{c_released};
+ my($num, $res, $get, $time) = (@_[ARG0..$#_]);
+
+ $get->{time} += $time;
+ $get->{queries}++;
+
+ # process the results
+ if(!$get->{type}) {
+ for (@$res) {
+ $_->{id}*=1;
+ if(grep /basic/, @{$get->{info}}) {
+ $_->{original} ||= undef;
+ $_->{platforms} = [ split /\//, delete $_->{c_platforms} ];
+ $_->{languages} = [ split /\//, delete $_->{c_languages} ];
+ $_->{released} = formatdate delete $_->{c_released};
+ }
+ if(grep /details/, @{$get->{info}}) {
+ $_->{aliases} ||= undef;
+ $_->{length} *= 1;
+ $_->{length} ||= undef;
+ $_->{description} ||= undef;
+ $_->{links} = {
+ wikipedia => delete($_->{l_wp}) ||undef,
+ encubed => delete($_->{l_encubed})||undef,
+ renai => delete($_->{l_renai}) ||undef
+ };
+ }
}
- if(grep /details/, @$info) {
- $_->{aliases} ||= undef;
- $_->{length} *= 1;
- $_->{length} ||= undef;
- $_->{description} ||= undef;
- $_->{links} = {
- wikipedia => delete($_->{l_wp}) ||undef,
- encubed => delete($_->{l_encubed})||undef,
- renai => delete($_->{l_renai}) ||undef
- };
+ $get->{list} = $res;
+ }
+
+ elsif($get->{type} eq 'anime') {
+ # link
+ for my $i (@{$get->{list}}) {
+ $i->{anime} = [ grep $i->{latest} == $_->{vid}, @$res ];
+ }
+ # cleanup
+ for (@$res) {
+ $_->{id} *= 1;
+ $_->{year} *= 1 if defined $_->{year};
+ $_->{ann_id} *= 1 if defined $_->{ann_id};
+ delete $_->{vid};
}
+ $get->{anime} = 1;
+ }
+
+ # fetch more results
+ if(!$get->{anime} && grep /anime/, @{$get->{info}}) {
+ my @ids = map $_->{latest}, @{$get->{list}};
+ my $ids = join ',', map '?', @ids;
+ return $_[KERNEL]->post(pg => query => qq|
+ SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji
+ FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN($ids)|,
+ \@ids, 'get_vn_res', { %$get, type => 'anime' });
}
- $c->{wheel}->put([ results => { num => $#$res+1, items => $res }]);
- $_[KERNEL]->yield(log => $c, "%4.0fms %2d get vn %s %s", $time*1000, $#$res+1, join (',', @$info), encode_filters $filters);
+ # send and log
+ delete $_->{latest} for @{$get->{list}};
+ $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 vn %s %s",
+ $get->{time}*1000, $get->{queries}, $num, join(',', @{$get->{info}}), encode_filters $get->{filters});
}
--
cgit v1.2.3
From da73ec4715f6bdb67833ed482ad09e8735c29977 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 6 Nov 2009 14:28:44 +0100
Subject: API: Added relations flag to get vn command
---
data/docs/11 | 15 ++++++++++++++-
lib/Multi/API.pm | 26 +++++++++++++++++++++++---
2 files changed, 37 insertions(+), 4 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index d934ab8f..202d9024 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -310,7 +310,7 @@ however still required.
array of objects |
no |
- List of anime related to the VN, each object has the following members:
+ (Possibly empty) list of anime related to the VN, each object has the following members:
"id", integer, AniDB ID
"ann_id", integer, AnimeNewsNetwork ID
"nfo_id", string, AnimeNfo ID
@@ -322,6 +322,19 @@ however still required.
and may not reflect the latest state of their information due to caching.
|
+
+ relations |
+ relations |
+ array of objects |
+ no |
+
+ (Possibly empty) list of related visual novels, each object has the following members:
+ "id", integer
+ "relation", string, relation to the VN
+ "title", string, (romaji) title
+ "original", string, original/official title, can be null.
+ |
+
Accepted filters for the 'vn' type:
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 8f99915b..3271e27b 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -303,7 +303,7 @@ sub login_res { # num, res, [ c, arg ]
sub get_vn {
my($c, $info, $filters) = @_[ARG0..$#_];
- return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|anime)$/, @$info);
+ return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|anime|relations)$/, @$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;
@@ -395,16 +395,36 @@ sub get_vn_res {
$get->{anime} = 1;
}
+ elsif($get->{type} eq 'relations') {
+ for my $i (@{$get->{list}}) {
+ $i->{relations} = [ grep $i->{latest} == $_->{vid1}, @$res ];
+ }
+ for (@$res) {
+ $_->{id} *= 1;
+ $_->{original} ||= undef;
+ delete $_->{vid1};
+ }
+ $get->{relations} = 1;
+ }
+
# fetch more results
+ my @ids = map $_->{latest}, @{$get->{list}};
+ my $ids = join ',', map '?', @ids;
+
if(!$get->{anime} && grep /anime/, @{$get->{info}}) {
- my @ids = map $_->{latest}, @{$get->{list}};
- my $ids = join ',', map '?', @ids;
return $_[KERNEL]->post(pg => query => qq|
SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji
FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN($ids)|,
\@ids, 'get_vn_res', { %$get, type => 'anime' });
}
+ if(!$get->{relations} && grep /relations/, @{$get->{info}}) {
+ return $_[KERNEL]->post(pg => query => qq|
+ SELECT vl.vid1, v.id, vl.relation, vr.title, vr.original FROM vn_relations vl
+ JOIN vn v ON v.id = vl.vid2 JOIN vn_rev vr ON vr.id = v.latest WHERE vl.vid1 IN($ids) AND NOT v.hidden|,
+ \@ids, 'get_vn_res', { %$get, type => 'relations' });
+ }
+
# send and log
delete $_->{latest} for @{$get->{list}};
$num = @{$get->{list}};
--
cgit v1.2.3
From 4b73f8b3d33344432f464dc6d8f8258d3dea5295 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 7 Nov 2009 12:33:38 +0100
Subject: API: Added commands/minute and sqltime/minute throttle
This is apparently a token bucket algorithm, though I learned about
that term after I wrote the implementation.
These limits shouldn't be very strict, in a normal situation client
applications won't have to worry about it.
---
data/docs/11 | 17 ++++++++++++++++-
lib/Multi/API.pm | 40 +++++++++++++++++++++++++++++++++++-----
2 files changed, 51 insertions(+), 6 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 202d9024..70c5b2f5 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -53,7 +53,14 @@ server resources and prevent abuse of this service.
- 5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.
- 3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.
- Each command currently returns at most 10 results. TODO: make configurable?
- - more to come...
+ - 30 commands per minute per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.
+ -
+ 1 second of SQL time per minute per user. SQL time is the total time taken to
+ run the database queries for each command. This depends on both the command
+ (filters and get flags) and server load, and is thus not very predictable.
+ Server will reply with a 'throttled' error with type="sql" upon reaching
+ this limit.
+
@@ -421,6 +428,14 @@ however still required.
missingA JSON object argument is missing a required member. The name of which is given in the additional "field" member.
badargA JSON value is of the wrong type or in the wrong format. The name of the incorrect field is given in a "field" member.
needloginNeed to be logged in to issue this command.
+ throttled
+ You have used too many server resources within a short time, and need to wait
+ a bit before sending the next command. The type of throttle is given in the
+ "type" member, and the "minwait" and "fullwait" members tell you how long you
+ need to wait before sending the next command and when you can start bursting
+ again (this is the recommended waiting time), respectively. Both values are in
+ seconds, with one decimal after the point.
+
auth(login) Incorrect username/password combination.
loggedin(login) Already logged in. Only one successful login command can be issues on one connection.
sesslimit(login) Too many open sessions for the current user.
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 3271e27b..7b36755d 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -13,6 +13,7 @@ use POE 'Wheel::SocketFactory', 'Wheel::ReadWrite';
use POE::Filter::VNDBAPI 'encode_filters';
use Digest::SHA 'sha256_hex';
use Encode 'encode_utf8';
+use Time::HiRes 'time'; # important for throttling
# not exported by Socket, taken from netinet/tcp.h (specific to Linux, AFAIK)
@@ -21,6 +22,11 @@ sub TCP_KEEPINTVL { 5 }
sub TCP_KEEPCNT { 6 }
+# Global throttle hash, key = username, value = [ cmd_time, sql_time ]
+# TODO: clean up items in this hash when username isn't connected anymore and throttle times < current time
+my %throttle;
+
+
sub spawn {
my $p = shift;
POE::Session->create(
@@ -37,6 +43,8 @@ sub spawn {
conn_per_ip => 5,
sess_per_user => 3,
tcp_keepalive => [ 120, 60, 3 ], # time, intvl, probes
+ throttle_cmd => [ 2, 30 ], # interval between each command, allowed burst
+ throttle_sql => [ 60, 1 ], # sql time multiplier, allowed burst (in sql time)
@_,
c => {},
},
@@ -247,13 +255,28 @@ sub client_input {
# when we're here, we can assume that $cmd contains a valid command
# and the arguments are syntactically valid
- # login
+ # handle login command
return $_[KERNEL]->yield(login => $c, @$arg) if $cmd eq 'login';
-
return cerr $c, needlogin => 'Not logged in.' if !$c->{username};
- # TODO: throttling
- # get
+ # update throttle array of the current user
+ my $time = time;
+ $_ < $time && ($_ = $time) for @{$c->{throttle}};
+
+ # check for thottle rule violation
+ my @limits = ('cmd', 'sql');
+ for (0..$#limits) {
+ my $threshold = $_[HEAP]{"throttle_$limits[$_]"}[0]*$_[HEAP]{"throttle_$limits[$_]"}[1];
+ return cerr $c, throttled => 'Throttle limit reached.', type => $limits[$_],
+ minwait => int(10*($c->{throttle}[$_]-$time-$threshold))/10+1,
+ fullwait => int(10*($c->{throttle}[$_]-$time))/10+1
+ if $c->{throttle}[$_]-$time > $threshold;
+ }
+
+ # update commands/second throttle
+ $c->{throttle}[0] += $_[HEAP]{throttle_cmd}[0];
+
+ # handle get 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 ne 'vn';
@@ -293,8 +316,12 @@ sub login_res { # num, res, [ c, arg ]
my $encrypted = sha256_hex($VNDB::S{global_salt}.encode_utf8($arg->{password}).encode_utf8($res->[0]{salt}));
return cerr $c, auth => "Wrong password for user '$arg->{username}'" if lc($encrypted) ne lc($res->[0]{passwd});
- $c->{wheel}->put(['ok']);
+ # link this connection to the users' throttle array (create this if necessary)
+ $throttle{$arg->{username}} = [ time, time ] if !$throttle{$arg->{username}};
+ $c->{throttle} = $throttle{$arg->{username}};
+
$c->{username} = $arg->{username};
+ $c->{wheel}->put(['ok']);
$_[KERNEL]->yield(log => $c,
'Successful login by %s using client "%s" ver. %s', $arg->{username}, $arg->{client}, $arg->{clientver});
}
@@ -425,6 +452,9 @@ sub get_vn_res {
\@ids, 'get_vn_res', { %$get, type => 'relations' });
}
+ # update sql throttle
+ $get->{c}{throttle}[1] += $get->{time}*$_[HEAP]{throttle_sql}[0];
+
# send and log
delete $_->{latest} for @{$get->{list}};
$num = @{$get->{list}};
--
cgit v1.2.3
From a418f879fc377c64e2acbbf5d54f97ad524f3302 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 7 Nov 2009 15:41:08 +0100
Subject: API: Added get release command
Still needs more filters, and info flags for producers and vns.
---
data/docs/11 | 144 ++++++++++++++++++++++++++++++++++++++++++++++++++++
lib/Multi/API.pm | 152 ++++++++++++++++++++++++++++++++++++++++++++++---------
2 files changed, 272 insertions(+), 24 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 70c5b2f5..99c727d7 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -402,6 +402,150 @@ however still required.
novel and its releases. Note that the algorithm of this search may change and
that it can use a different algorithm than the search function on the website.
+
+
+Returned object members for the 'release' type:
+
+
+ Member |
+ Flag |
+ Type |
+ null |
+ Description |
+
+
+ id |
+ - |
+ integer |
+ no |
+ Release ID |
+
+
+ title |
+ basic |
+ string |
+ no |
+ Release title (romaji) |
+
+
+ original |
+ basic |
+ string |
+ yes |
+ Original/official title of the release. |
+
+
+ released |
+ basic |
+ date (string) |
+ yes |
+ Release date |
+
+
+ type |
+ basic |
+ string |
+ no |
+ "complete", "partial" or "trial" |
+
+
+ patch |
+ basic |
+ boolean |
+ no |
+ |
+
+
+ freeware |
+ basic |
+ boolean |
+ no |
+ |
+
+
+ doujin |
+ basic |
+ boolean |
+ no |
+ |
+
+
+ languages |
+ basic |
+ array of strings |
+ no |
+ |
+
+
+ website |
+ details |
+ string |
+ yes |
+ Official website URL |
+
+
+ notes |
+ details |
+ string |
+ yes |
+ Random notes, can contain formatting codes as described in d9.3 |
+
+
+ minage |
+ details |
+ integer |
+ yes |
+ Age rating, 0 = all ages. |
+
+
+ gtin |
+ details |
+ string |
+ yes |
+ JAN/UPC/EAN code. This is actually an integer, but formatted as a string to avoid an overflow on 32bit platforms. |
+
+
+ catalog |
+ details |
+ string |
+ yes |
+ Catalog number. |
+
+
+ platforms |
+ details |
+ array of strings |
+ no |
+ Empty array when platform is unknown. |
+
+
+ media |
+ details |
+ array of objects |
+ no |
+
+ Objects have the following two members:
+ "medium", string
+ "qty", integer, the quantity. null when it is not applicable for the medium.
+ An empty array is returned when the media are unknown.
+ |
+
+
+
+Accepted filters for the 'release' type:
+
+
+ Field |
+ Value |
+ Operators |
+ |
+
+
+ id |
+ integer array of integers |
+ = != > >= < <= = != |
+ |
+
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 7b36755d..20b89778 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -17,9 +17,14 @@ use Time::HiRes 'time'; # important for throttling
# not exported by Socket, taken from netinet/tcp.h (specific to Linux, AFAIK)
-sub TCP_KEEPIDLE { 4 }
-sub TCP_KEEPINTVL { 5 }
-sub TCP_KEEPCNT { 6 }
+sub TCP_KEEPIDLE () { 4 }
+sub TCP_KEEPINTVL () { 5 }
+sub TCP_KEEPCNT () { 6 }
+
+
+# what our JSON encoder considers 'true' or 'false'
+sub TRUE () { JSON::XS::true }
+sub FALSE () { JSON::XS::false }
# Global throttle hash, key = username, value = [ cmd_time, sql_time ]
@@ -33,8 +38,7 @@ sub spawn {
package_states => [
$p => [qw|
_start shutdown log server_error client_connect client_error client_input
- login login_res
- get_vn get_vn_res
+ login login_res get_results get_vn get_vn_res get_release get_release_res
|],
],
heap => {
@@ -279,7 +283,7 @@ sub client_input {
# handle get 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 ne 'vn';
+ return cerr $c, 'gettype', "Unknown get type: '$type'" if $type !~ /^(?:vn|release)$/;
$_[KERNEL]->yield("get_$type", $c, @$arg);
}
@@ -327,6 +331,20 @@ sub login_res { # num, res, [ c, arg ]
}
+sub get_results {
+ my $get = $_[ARG0]; # hashref, must contain: type, c, queries, time, list, info, filters,
+
+ # 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});
+}
+
+
sub get_vn {
my($c, $info, $filters) = @_[ARG0..$#_];
@@ -438,29 +456,115 @@ sub get_vn_res {
my @ids = map $_->{latest}, @{$get->{list}};
my $ids = join ',', map '?', @ids;
- if(!$get->{anime} && grep /anime/, @{$get->{info}}) {
- return $_[KERNEL]->post(pg => query => qq|
- SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji
- FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN($ids)|,
- \@ids, 'get_vn_res', { %$get, type => 'anime' });
- }
+ !$get->{anime} && grep(/anime/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji
+ FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN($ids)|,
+ \@ids, 'get_vn_res', { %$get, type => 'anime' });
+
+ !$get->{relations} && grep(/relations/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ SELECT vl.vid1, v.id, vl.relation, vr.title, vr.original FROM vn_relations vl
+ JOIN vn v ON v.id = vl.vid2 JOIN vn_rev vr ON vr.id = v.latest WHERE vl.vid1 IN($ids) AND NOT v.hidden|,
+ \@ids, 'get_vn_res', { %$get, type => 'relations' });
+
+ # send results
+ delete $_->{latest} for @{$get->{list}};
+ $_[KERNEL]->yield(get_results => { %$get, type => 'vn' });
+}
+
+
+sub get_release {
+ my($c, $info, $filters) = @_[ARG0..$#_];
+
+ return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details)$/, @$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;
+
+ my @placeholders;
+ my $where = encode_filters $filters, \&filtertosql, $c, \@placeholders, [
+ [ 'id',
+ [ 'int' => 'r.id :op: :value:', {qw|= = != <> > > >= >= < < <= <=|} ],
+ [ inta => 'r.id :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',' ],
+ ],
+ ];
+ return if !$where;
+
+ $_[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 });
+}
+
+
+sub get_release_res {
+ my($num, $res, $get, $time) = (@_[ARG0..$#_]);
- if(!$get->{relations} && grep /relations/, @{$get->{info}}) {
- return $_[KERNEL]->post(pg => query => qq|
- SELECT vl.vid1, v.id, vl.relation, vr.title, vr.original FROM vn_relations vl
- JOIN vn v ON v.id = vl.vid2 JOIN vn_rev vr ON vr.id = v.latest WHERE vl.vid1 IN($ids) AND NOT v.hidden|,
- \@ids, 'get_vn_res', { %$get, type => 'relations' });
+ $get->{time} += $time;
+ $get->{queries}++;
+
+ # process the results
+ if(!$get->{type}) {
+ for (@$res) {
+ $_->{id}*=1;
+ if(grep /basic/, @{$get->{info}}) {
+ $_->{original} ||= undef;
+ $_->{released} = formatdate($_->{released});
+ $_->{patch} = $_->{patch} ? TRUE : FALSE;
+ $_->{freeware} = $_->{freeware} ? TRUE : FALSE;
+ $_->{doujin} = $_->{doujin} ? TRUE : FALSE;
+ }
+ if(grep /details/, @{$get->{info}}) {
+ $_->{website} ||= undef;
+ $_->{notes} ||= undef;
+ $_->{minage} = $_->{minage} < 0 ? undef : $_->{minage}*1;
+ $_->{gtin} ||= undef;
+ $_->{catalog} ||= undef;
+ }
+ }
+ $get->{list} = $res;
+ }
+ elsif($get->{type} eq 'languages') {
+ for my $i (@{$get->{list}}) {
+ $i->{languages} = [ map $i->{latest} == $_->{rid} ? $_->{lang} : (), @$res ];
+ }
+ $get->{languages} = 1;
+ }
+ elsif($get->{type} eq 'platforms') {
+ for my $i (@{$get->{list}}) {
+ $i->{platforms} = [ map $i->{latest} == $_->{rid} ? $_->{platform} : (), @$res ];
+ }
+ $get->{platforms} = 1;
+ }
+ elsif($get->{type} eq 'media') {
+ for my $i (@{$get->{list}}) {
+ $i->{media} = [ grep $i->{latest} == $_->{rid}, @$res ];
+ }
+ for (@$res) {
+ delete $_->{rid};
+ $_->{qty} = $VNDB::S{media}{$_->{medium}} ? $_->{qty}*1 : undef;
+ }
+ $get->{media} = 1;
}
- # update sql throttle
- $get->{c}{throttle}[1] += $get->{time}*$_[HEAP]{throttle_sql}[0];
+ # get more info
+ my @ids = map $_->{latest}, @{$get->{list}};
+ my $ids = join ',', map '?', @ids;
- # send and log
+ !$get->{languages} && grep(/basic/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
+ qq|SELECT rid, lang FROM releases_lang WHERE rid IN($ids)|,
+ \@ids, 'get_release_res', { %$get, type => 'languages' });
+
+ !$get->{platforms} && grep(/details/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
+ qq|SELECT rid, platform FROM releases_platforms WHERE rid IN($ids)|,
+ \@ids, 'get_release_res', { %$get, type => 'platforms' });
+
+ !$get->{media} && grep(/details/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
+ qq|SELECT rid, medium, qty FROM releases_media WHERE rid IN($ids)|,
+ \@ids, 'get_release_res', { %$get, type => 'media' });
+
+ # send results
delete $_->{latest} for @{$get->{list}};
- $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 vn %s %s",
- $get->{time}*1000, $get->{queries}, $num, join(',', @{$get->{info}}), encode_filters $get->{filters});
+ $_[KERNEL]->yield(get_results => { %$get, type => 'release' });
}
--
cgit v1.2.3
From e745c48e897c580af7c2841832ce0e9786e647c3 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 7 Nov 2009 16:19:09 +0100
Subject: API: Added vn and producers info flags to the get release command
---
data/docs/11 | 25 +++++++++++++++++++++++++
lib/Multi/API.pm | 46 ++++++++++++++++++++++++++++++++++++++++------
2 files changed, 65 insertions(+), 6 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 99c727d7..7a863959 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -530,6 +530,31 @@ however still required.
An empty array is returned when the media are unknown.
+
+ vn |
+ vn |
+ array of objects |
+ no |
+
+ Array of visual novels linked to this release. Objects have the following members:
+ id, title and original. These are the same as the members of the "get vn" command.
+ |
+
+
+ producers |
+ producers |
+ array of objects |
+ no |
+
+ (Possibly empty) list of producers involved in this release. Objects have the following members:
+ "id", integer
+ "developer", boolean,
+ "publisher", boolean,
+ "name", string, romaji name
+ "original", string, official/original name, can be null
+ "type", string, producer type
+ |
+
Accepted filters for the 'release' type:
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 20b89778..741400dc 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -475,7 +475,7 @@ sub get_vn_res {
sub get_release {
my($c, $info, $filters) = @_[ARG0..$#_];
- return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details)$/, @$info);
+ return cerr $c, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|vn|producers)$/, @$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;
@@ -514,11 +514,11 @@ sub get_release_res {
$_->{doujin} = $_->{doujin} ? TRUE : FALSE;
}
if(grep /details/, @{$get->{info}}) {
- $_->{website} ||= undef;
- $_->{notes} ||= undef;
- $_->{minage} = $_->{minage} < 0 ? undef : $_->{minage}*1;
- $_->{gtin} ||= undef;
- $_->{catalog} ||= undef;
+ $_->{website} ||= undef;
+ $_->{notes} ||= undef;
+ $_->{minage} = $_->{minage} < 0 ? undef : $_->{minage}*1;
+ $_->{gtin} ||= undef;
+ $_->{catalog} ||= undef;
}
}
$get->{list} = $res;
@@ -545,6 +545,30 @@ sub get_release_res {
}
$get->{media} = 1;
}
+ elsif($get->{type} eq 'vn') {
+ for my $i (@{$get->{list}}) {
+ $i->{vn} = [ grep $i->{latest} == $_->{rid}, @$res ];
+ }
+ for (@$res) {
+ $_->{id}*=1;
+ $_->{original} ||= undef;
+ delete $_->{rid};
+ }
+ $get->{vn} = 1;
+ }
+ elsif($get->{type} eq 'producers') {
+ for my $i (@{$get->{list}}) {
+ $i->{producers} = [ grep $i->{latest} == $_->{rid}, @$res ];
+ }
+ for (@$res) {
+ $_->{id}*=1;
+ $_->{original} ||= undef;
+ $_->{developer} = $_->{developer} ? TRUE : FALSE;
+ $_->{publisher} = $_->{publisher} ? TRUE : FALSE;
+ delete $_->{rid};
+ }
+ $get->{producers} = 1;
+ }
# get more info
my @ids = map $_->{latest}, @{$get->{list}};
@@ -562,6 +586,16 @@ sub get_release_res {
qq|SELECT rid, medium, qty FROM releases_media WHERE rid IN($ids)|,
\@ids, 'get_release_res', { %$get, type => 'media' });
+ !$get->{vn} && grep(/vn/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ SELECT rv.rid, v.id, vr.title, vr.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid
+ JOIN vn_rev vr ON vr.id = v.latest WHERE NOT v.hidden AND rv.rid IN($ids)|,
+ \@ids, 'get_release_res', { %$get, type => 'vn' });
+
+ !$get->{producers} && grep(/producers/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.type, pr.name, pr.original FROM releases_producers rp
+ JOIN producers p ON p.id = rp.pid JOIN producers_rev pr ON pr.id = p.latest WHERE NOT p.hidden AND rp.rid IN($ids)|,
+ \@ids, 'get_release_res', { %$get, type => 'producers' });
+
# send results
delete $_->{latest} for @{$get->{list}};
$_[KERNEL]->yield(get_results => { %$get, type => 'release' });
--
cgit v1.2.3
From 66a00f986ef00c375187d61e34a7f145410f8f32 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 7 Nov 2009 18:12:24 +0100
Subject: API: Added filters to the get release command and fixed some bugs
---
data/docs/11 | 66 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
lib/Multi/API.pm | 61 ++++++++++++++++++++++++++++++++++++++++-----------
2 files changed, 114 insertions(+), 13 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 7a863959..ba27b2bb 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -571,6 +571,72 @@ however still required.
= != > >= < <= = != |
|
+
+ vn |
+ integer |
+ = |
+ Find releases linked to the given visual novel ID. |
+
+
+ title |
+ string |
+ = != ~ |
+ |
+
+
+ original |
+ null string |
+ = != = != ~ |
+ |
+
+
+ released |
+ null date (string) |
+ = != = != > >= < <= |
+ Note about released filter for the vn type also applies here. |
+
+
+ patch |
+ boolean |
+ = |
+ |
+
+
+ freeware |
+ boolean |
+ = |
+ |
+
+
+ doujin |
+ boolean |
+ = |
+ |
+
+
+ type |
+ string |
+ = != |
+ |
+
+
+ gtin |
+ int |
+ = != |
+ Value can also be escaped as a string (if you risk an integer overflow otherwise) |
+
+
+ catalog |
+ string |
+ = != |
+ |
+
+
+ languages |
+ string array of strings |
+ = != |
+ |
+
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 741400dc..75c5faf3 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -14,6 +14,7 @@ use POE::Filter::VNDBAPI 'encode_filters';
use Digest::SHA 'sha256_hex';
use Encode 'encode_utf8';
use Time::HiRes 'time'; # important for throttling
+use JSON::XS;
# not exported by Socket, taken from netinet/tcp.h (specific to Linux, AFAIK)
@@ -107,6 +108,8 @@ sub filtertosql {
: $_->[0] eq 'inta' ? ref($value) eq 'ARRAY' && @$value && !grep(!defined($_) || ref($_) || $_ !~ /^-?\d+$/, @$value)
# stra
: $_->[0] eq 'stra' ? ref($value) eq 'ARRAY' && @$value && !grep(!defined($_) || ref($_), @$value)
+ # bool
+ : $_->[0] eq 'bool' ? defined($value) && JSON::XS::is_bool($value)
# oops
: die "Invalid filter type $_->[0]"
), @$t)[0];
@@ -122,19 +125,24 @@ sub filtertosql {
# pre-process the argument(s)
my @values = ref($value) eq 'ARRAY' ? @$value : $value;
- for (!$o{process} ? () : @values) {
+ for my $v (!$o{process} ? () : @values) {
if(!ref $o{process}) {
- $_ = sprintf $o{process}, $_;
+ $v = sprintf $o{process}, $v;
} elsif(ref($o{process}) eq 'CODE') {
- $_ = $o{process}->($_);
- return cerr $c, filter => $$_, %e if ref($_) eq 'SCALAR';
+ $v = $o{process}->($v);
+ return cerr $c, filter => $$v, %e if ref($v) eq 'SCALAR';
} elsif(${$o{process}} eq 'like') {
y/%//;
- $_ = "%$_%";
+ $v = "%$v%";
+ } elsif(${$o{process}} eq 'lang') {
+ return cerr $c, filter => 'Invalid language code', %e if !grep $v eq $_, @{$VNDB::S{languages}};
}
}
- # type=str and type=int are now quite simple
+ # type=bool and no processing done? convert bool to what DBD::Pg wants
+ $values[0] = $values[0] ? 1 : 0 if $type eq 'bool' && !$o{process};
+
+ # type=str, int and bool are now quite simple
if(!ref $value) {
$sql =~ s/:value:/push @$p, $values[0]; '?'/eg;
return $sql;
@@ -456,12 +464,12 @@ sub get_vn_res {
my @ids = map $_->{latest}, @{$get->{list}};
my $ids = join ',', map '?', @ids;
- !$get->{anime} && grep(/anime/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ @ids && !$get->{anime} && grep(/anime/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji
FROM anime a JOIN vn_anime va ON va.aid = a.id WHERE va.vid IN($ids)|,
\@ids, 'get_vn_res', { %$get, type => 'anime' });
- !$get->{relations} && grep(/relations/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ @ids && !$get->{relations} && grep(/relations/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
SELECT vl.vid1, v.id, vl.relation, vr.title, vr.original FROM vn_relations vl
JOIN vn v ON v.id = vl.vid2 JOIN vn_rev vr ON vr.id = v.latest WHERE vl.vid1 IN($ids) AND NOT v.hidden|,
\@ids, 'get_vn_res', { %$get, type => 'relations' });
@@ -486,6 +494,31 @@ sub get_release {
[ 'id',
[ 'int' => 'r.id :op: :value:', {qw|= = != <> > > >= >= < < <= <=|} ],
[ inta => 'r.id :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',' ],
+ ], [ 'vn',
+ [ 'int' => 'rr.id IN(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = :value:)', {'=',1} ],
+ ], [ 'title',
+ [ str => 'rr.title :op: :value:', {qw|= = != <>|} ],
+ [ str => 'rr.title ILIKE :value:', {'~',1}, process => \'like' ],
+ ], [ 'original',
+ [ undef, "rr.original :op: ''", {qw|= = != <>|} ],
+ [ str => 'rr.original :op: :value:', {qw|= = != <>|} ],
+ [ str => 'rr.original ILIKE :value:', {'~',1}, process => \'like' ]
+ ], [ 'released',
+ [ undef, 'rr.released :op: 0', {qw|= = != <>|} ],
+ [ str => 'rr.released :op: :value:', {qw|= = != <> > > < < <= <= >= >=|}, process => \&parsedate ],
+ ], [ 'patch', [ bool => 'rr.patch = :value:', {'=',1} ],
+ ], [ 'freeware', [ bool => 'rr.freeware = :value:', {'=',1} ],
+ ], [ 'doujin', [ bool => 'rr.doujin = :value:', {'=',1} ],
+ ], [ 'type',
+ [ str => 'rr.type :op: :value:', {qw|= = != <>|},
+ process => sub { !grep($_ eq $_[0], @{$VNDB::S{release_types}}) ? \'No such release type' : $_[0] } ],
+ ], [ 'gtin',
+ [ 'int' => 'rr.gtin :op: :value:', {qw|= = != <>|} ],
+ ], [ 'catalog',
+ [ str => 'rr.catalog :op: :value:', {qw|= = != <>|} ],
+ ], [ 'languages',
+ [ str => 'rr.id :op:(SELECT rl.rid FROM releases_lang rl WHERE rl.lang = :value:)', {'=' => 'IN', '!=' => 'NOT IN'}, process => \'lang' ],
+ [ 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;
@@ -574,24 +607,24 @@ sub get_release_res {
my @ids = map $_->{latest}, @{$get->{list}};
my $ids = join ',', map '?', @ids;
- !$get->{languages} && grep(/basic/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
+ @ids && !$get->{languages} && grep(/basic/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
qq|SELECT rid, lang FROM releases_lang WHERE rid IN($ids)|,
\@ids, 'get_release_res', { %$get, type => 'languages' });
- !$get->{platforms} && grep(/details/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
+ @ids && !$get->{platforms} && grep(/details/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
qq|SELECT rid, platform FROM releases_platforms WHERE rid IN($ids)|,
\@ids, 'get_release_res', { %$get, type => 'platforms' });
- !$get->{media} && grep(/details/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
+ @ids && !$get->{media} && grep(/details/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query =>
qq|SELECT rid, medium, qty FROM releases_media WHERE rid IN($ids)|,
\@ids, 'get_release_res', { %$get, type => 'media' });
- !$get->{vn} && grep(/vn/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ @ids && !$get->{vn} && grep(/vn/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
SELECT rv.rid, v.id, vr.title, vr.original FROM releases_vn rv JOIN vn v ON v.id = rv.vid
JOIN vn_rev vr ON vr.id = v.latest WHERE NOT v.hidden AND rv.rid IN($ids)|,
\@ids, 'get_release_res', { %$get, type => 'vn' });
- !$get->{producers} && grep(/producers/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ @ids && !$get->{producers} && grep(/producers/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.type, pr.name, pr.original FROM releases_producers rp
JOIN producers p ON p.id = rp.pid JOIN producers_rev pr ON pr.id = p.latest WHERE NOT p.hidden AND rp.rid IN($ids)|,
\@ids, 'get_release_res', { %$get, type => 'producers' });
@@ -616,6 +649,7 @@ Filter definitions:
'int' (normal int)
'stra' (array of strings)
'inra' (array of ints)
+ 'bool'
sql string:
The relevant SQL string, with :op: and :value: subsistutions. :value: is not available for type=undef
join: (only used when type is an array)
@@ -627,6 +661,7 @@ Filter definitions:
sub, argument = value, returns new value
scalarref, template:
\'like' => sub { (local$_=shift)=~y/%//; lc "%$_%" }
+ \'lang' => sub { !grep($_ eq $_[0], @{$VNDB::S{languages}}) ? \'Invalid language' : $_[0] }
example for v.id:
[ 'id',
--
cgit v1.2.3
From edfd4d26db8e8157538fe166def8409fe0ef2345 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 8 Nov 2009 15:00:27 +0100
Subject: Multi::API/IRC: Added runtime API admin/monitoring interface
+ some runtime statistics
+ IP ban list (which is likely unnecessary, but you never know...)
---
lib/Multi/API.pm | 71 ++++++++++++++++++++++++++++++++++++++++++++++++++++----
lib/Multi/IRC.pm | 61 +++++++++++++++++++++++++++++++++++++-----------
2 files changed, 115 insertions(+), 17 deletions(-)
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 75c5faf3..5e9f71d2 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -40,6 +40,7 @@ sub spawn {
$p => [qw|
_start shutdown log server_error client_connect client_error client_input
login login_res get_results get_vn get_vn_res get_release get_release_res
+ admin
|],
],
heap => {
@@ -50,8 +51,10 @@ sub spawn {
tcp_keepalive => [ 120, 60, 3 ], # time, intvl, probes
throttle_cmd => [ 2, 30 ], # interval between each command, allowed burst
throttle_sql => [ 60, 1 ], # sql time multiplier, allowed burst (in sql time)
+ ipbans => [],
@_,
- c => {},
+ c => {}, # open connections
+ s => {conn => 0, cmds => 0, cmd_err => 0}, # stats
},
);
}
@@ -61,8 +64,15 @@ sub spawn {
sub cerr {
my($c, $id, $msg, %o) = @_;
+
+ # update stat counters
+ $c->{cmd_err}++;
+ $poe_kernel->get_active_session()->get_heap()->{s}{cmd_err}++;
+
+ # send error
$c->{wheel}->put([ error => { id => $id, msg => $msg, %o }]);
- # using $poe_kernel here isn't really a clean solution...
+
+ # log
$poe_kernel->yield(log => $c, 'error: %s, %s', $id, $msg);
return undef;
}
@@ -215,6 +225,9 @@ sub client_connect {
my $ip = inet_ntoa($_[ARG1]);
my $sock = $_[ARG0];
+ $_[HEAP]{s}{conn}++;
+
+ return close $sock if grep $ip eq $_, @{$_[HEAP]{ipbans}};
if($_[HEAP]{conn_per_ip} <= grep $ip eq $_[HEAP]{c}{$_}{ip}, keys %{$_[HEAP]{c}}) {
$_[KERNEL]->yield(log => 0,
'Connect from %s denied, limit of %d connections per IP reached', $ip, $_[HEAP]{conn_per_ip});
@@ -239,8 +252,12 @@ sub client_connect {
InputEvent => 'client_input',
);
$_[HEAP]{c}{ $w->ID() } = {
- wheel => $w,
- ip => $ip,
+ wheel => $w,
+ ip => $ip,
+ connected => time,
+ cmds => 0,
+ cmd_err => 0,
+ # username, client, clientver are added after logging in
};
$_[KERNEL]->yield(log => $_[HEAP]{c}{ $w->ID() }, 'Connected');
}
@@ -262,6 +279,11 @@ sub client_input {
my $cmd = shift @$arg;
my $c = $_[HEAP]{c}{$id};
+ # stats
+ $_[HEAP]{s}{cmds}++;
+ $c->{cmds}++;
+
+ # parse error?
return cerr $c, $arg->[0]{id}, $arg->[0]{msg} if !defined $cmd;
# when we're here, we can assume that $cmd contains a valid command
@@ -333,6 +355,9 @@ sub login_res { # num, res, [ c, arg ]
$c->{throttle} = $throttle{$arg->{username}};
$c->{username} = $arg->{username};
+ $c->{client} = $arg->{client};
+ $c->{clientver} = $arg->{clientver};
+
$c->{wheel}->put(['ok']);
$_[KERNEL]->yield(log => $c,
'Successful login by %s using client "%s" ver. %s', $arg->{username}, $arg->{client}, $arg->{clientver});
@@ -635,6 +660,44 @@ sub get_release_res {
}
+# can be call()'ed from other sessions (specifically written for IRC)
+sub admin {
+ my($func, @arg) = @_[ARG0..$#_];
+
+ if($func eq 'stats') {
+ return { %{$_[HEAP]{s}}, online => scalar keys %{$_[HEAP]{c}} };
+ }
+ if($func eq 'list') {
+ return [ map {
+ my $c = $_[HEAP]{c}{$_};
+ my $r = { # make sure not to return our wheel
+ id => $_,
+ (map +($_, $c->{$_}), qw|username ip client clientver connected cmds cmd_err|)
+ };
+ if($c->{username}) {
+ $r->{t_cmd} = ($c->{throttle}[0]-time)/$_[HEAP]{throttle_cmd}[0];
+ $r->{t_sql} = ($c->{throttle}[1]-time)/$_[HEAP]{throttle_sql}[0];
+ $r->{t_cmd} = 0 if $r->{t_cmd} < 0;
+ $r->{t_sql} = 0 if $r->{t_sql} < 0;
+ }
+ $r
+ } keys %{$_[HEAP]{c}} ];
+ }
+ if($func eq 'bans') {
+ return $_[HEAP]{ipbans};
+ }
+ if($func eq 'ban') {
+ my $ip = $_[HEAP]{c}{$arg[0]} ? $_[HEAP]{c}{$arg[0]}{ip} : $arg[0];
+ return undef if !$ip || $ip !~ /^\d{1,3}(?:\.\d{1,3}){3}$/;
+ push @{$_[HEAP]{ipbans}}, $ip;
+ delete $_[HEAP]{c}{$_} for grep $_[HEAP]{c}{$_}{ip} eq $ip, keys %{$_[HEAP]{c}};
+ }
+ if($func eq 'unban') {
+ $_[HEAP]{ipbans} = [ grep $_ ne $arg[0], @{$_[HEAP]{ipbans}} ];
+ }
+}
+
+
1;
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 359e8e4b..9f792d54 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -51,7 +51,7 @@ sub spawn {
_start shutdown throttle_gc irc_001 irc_public irc_ctcp_action irc_msg
command idlequote reply notify_init notify notify_result
cmd_info cmd_list cmd_uptime cmd_vn cmd_vn_results cmd_p cmd_p_results cmd_quote cmd_quote_result
- cmd_say cmd_me cmd_notifications cmd_eval cmd_die cmd_post vndbid formatid
+ cmd_say cmd_me cmd_notifications cmd_eval cmd_die cmd_post cmd_api vndbid formatid
|],
],
heap => {
@@ -77,6 +77,7 @@ sub spawn {
eval => 2|8,
die => 2|8,
post => 2|8,
+ api => 2|8,
},
}
);
@@ -107,6 +108,17 @@ sub throttle {
return 0;
}
+sub age {
+ return '-' if !$_[0];
+ my $d = int $_[0] / 86400;
+ $_[0] %= 86400;
+ my $h = int $_[0] / 3600;
+ $_[0] %= 3600;
+ my $m = int $_[0] / 60;
+ $_[0] %= 60;
+ return sprintf '%s%02d:%02d:%02d', $d ? $d.' day'.($d>1?'s':'').', ' : '', $h, $m, int $_[0];
+}
+
sub _start {
$_[KERNEL]->alias_set('irc');
@@ -323,23 +335,12 @@ sub cmd_list {
sub cmd_uptime {
- my $age = sub {
- return '...down!?' if !$_[0];
- my $d = int $_[0] / 86400;
- $_[0] %= 86400;
- my $h = int $_[0] / 3600;
- $_[0] %= 3600;
- my $m = int $_[0] / 60;
- $_[0] %= 60;
- return sprintf '%s%02d:%02d:%02d', $d ? $d.' day'.($d>1?'s':'').', ' : '', $h, $m, int $_[0];
- };
-
open my $R, '<', '/proc/uptime';
my $server = <$R> =~ /^\s*([0-9]+)/ ? $1 : 0;
close $R;
my $multi = time - $^T;
- $_[KERNEL]->yield(reply => $_[DEST], sprintf 'Server uptime: %s -- mine: %s', $age->($server), $age->($multi));
+ $_[KERNEL]->yield(reply => $_[DEST], sprintf 'Server uptime: %s -- mine: %s', age($server), age($multi));
}
@@ -457,6 +458,40 @@ sub cmd_post {
}
+sub cmd_api {
+ my($cmd, @arg) = split /\s+/, $_[ARG]||'';
+ return $_[KERNEL]->yield(reply => $_[DEST], 'API module not enabled.')
+ if !defined $_[KERNEL]->alias_resolve('api');
+
+ if(!$cmd) {
+ my $stats = $_[KERNEL]->call(api => admin => 'stats');
+ return $_[KERNEL]->yield(reply => $_[DEST], sprintf
+ 'API up %s, %d connects (%d online), %d commands (%d errors).',
+ age(time - $^T), $stats->{conn}, $stats->{online}, $stats->{cmds}, $stats->{cmd_err});
+ }
+ if($cmd eq 'list') {
+ my $lst = $_[KERNEL]->call(api => admin => 'list');
+ return $_[KERNEL]->yield(reply => $_[DEST], 'Nobody connected.') if !@$lst;
+ $_[KERNEL]->yield(reply => $_[DEST], sprintf '%3d %15s %s%s', $_->{id}, $_->{ip}, age(time-$_->{connected}),
+ !$_->{username} ? '' : sprintf ' - %s (%s %s) C/E: %d/%d T: %.2f/%.2f', $_->{username},
+ $_->{client}, $_->{clientver}, $_->{cmds}, $_->{cmd_err}, $_->{t_cmd}, $_->{t_sql})
+ for (sort { $a->{ip} cmp $b->{ip} } @$lst);
+ }
+ if($cmd eq 'bans') {
+ my $lst = $_[KERNEL]->call(api => admin => 'bans');
+ return $_[KERNEL]->yield(reply => $_[DEST], !@$lst ? 'Ban list empty.' : join ' ', sort @$lst);
+ }
+ if($cmd eq 'ban') {
+ $_[KERNEL]->call(api => admin => ban => $arg[0]);
+ return $_[KERNEL]->yield(reply => $_[DEST], 'IP banned');
+ }
+ if($cmd eq 'unban') {
+ $_[KERNEL]->call(api => admin => unban => $arg[0]);
+ return $_[KERNEL]->yield(reply => $_[DEST], 'IP unbanned');
+ }
+}
+
+
#
--
cgit v1.2.3
From 047ea9eedbb84573cc68ccb1320fdb32234ccc39 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 8 Nov 2009 17:17:19 +0100
Subject: API: Changed 30 cmds/min. throttle to 100 cmds/10min.
More bursting, but overall less commands... sounds more fair to me, for
both server resources and API usage.
---
data/docs/11 | 2 +-
lib/Multi/API.pm | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index ba27b2bb..eb7c3aae 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -53,7 +53,7 @@ server resources and prevent abuse of this service.
5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.
3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.
Each command currently returns at most 10 results. TODO: make configurable?
- 30 commands per minute per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.
+ 100 commands per 10 minutes per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.
1 second of SQL time per minute per user. SQL time is the total time taken to
run the database queries for each command. This depends on both the command
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 5e9f71d2..101d8c29 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -49,7 +49,7 @@ sub spawn {
conn_per_ip => 5,
sess_per_user => 3,
tcp_keepalive => [ 120, 60, 3 ], # time, intvl, probes
- throttle_cmd => [ 2, 30 ], # interval between each command, allowed burst
+ throttle_cmd => [ 6, 100 ], # interval between each command, allowed burst
throttle_sql => [ 60, 1 ], # sql time multiplier, allowed burst (in sql time)
ipbans => [],
@_,
--
cgit v1.2.3
From 74497ee46cae8ba800228b8820cc474331c8f27a Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 8 Nov 2009 17:38:46 +0100
Subject: API: Added producer filter to the get release command
LOLHTMLTABLES.
---
data/docs/11 | 24 +++++++++++++++---------
lib/Multi/API.pm | 2 ++
2 files changed, 17 insertions(+), 9 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index eb7c3aae..b7f5ad45 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -578,60 +578,66 @@ however still required.
Find releases linked to the given visual novel ID. |
+ producer |
+ integer |
+ = |
+ Find releases linked to the given producer ID. |
+
+
title |
string |
= != ~ |
|
-
+
original |
null string |
= != = != ~ |
|
-
+
released |
null date (string) |
= != = != > >= < <= |
Note about released filter for the vn type also applies here. |
-
+
patch |
boolean |
= |
|
-
+
freeware |
boolean |
= |
|
-
+
doujin |
boolean |
= |
|
-
+
type |
string |
= != |
|
-
+
gtin |
int |
= != |
Value can also be escaped as a string (if you risk an integer overflow otherwise) |
-
+
catalog |
string |
= != |
|
-
+
languages |
string array of strings |
= != |
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 101d8c29..03d43a6f 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -521,6 +521,8 @@ sub get_release {
[ inta => 'r.id :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',' ],
], [ 'vn',
[ 'int' => 'rr.id IN(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = :value:)', {'=',1} ],
+ ], [ 'producer',
+ [ 'int' => 'rr.id IN(SELECT rp.rid FROM releases_producers rp WHERE rp.pid = :value:)', {'=',1} ],
], [ 'title',
[ str => 'rr.title :op: :value:', {qw|= = != <>|} ],
[ str => 'rr.title ILIKE :value:', {'~',1}, process => \'like' ],
--
cgit v1.2.3
From 70f0408500a37c1e3ce9fc68a5b894babf62b7c8 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Mon, 9 Nov 2009 16:40:40 +0100
Subject: Plugin::TransAdmin: CSS/doc fixes related to the API
+ Changelog updates, considering the API and other changes are now in
the beta branch.
---
ChangeLog | 3 +++
lib/VNDB/Plugin/TransAdmin.pm | 6 +++---
2 files changed, 6 insertions(+), 3 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 6aa58dce..4d8752b8 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,5 +1,8 @@
git - ?
- Fixed another bug with the calculation of tags_vn_bayesian.spoiler
+ - Implemented proper daemonizing and error handling for Multi
+ - Added basic Makefile
+ - Added public database API
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm
index ed5ebb36..f9cf8aed 100644
--- a/lib/VNDB/Plugin/TransAdmin.pm
+++ b/lib/VNDB/Plugin/TransAdmin.pm
@@ -172,7 +172,7 @@ sub _intro {
$f->close;
div class => 'mainbox';
h1 'Introduction to the language file';
- pre $intro;
+ pre style => 'padding: 0; margin: 0; background: none; border: none', $intro;
end;
}
@@ -217,7 +217,7 @@ sub _section {
for my $l (@$page) {
if($l->[0] eq 'comment') {
- pre;
+ pre style => 'padding: 0; margin: 0; background: none; border: none';
b class => 'grayedout', $l->[1]."\n";
end;
next;
@@ -281,7 +281,7 @@ sub _savedoc {
sub _docs {
my($lang, $doc) = @_;
- my @d = map /\.[a-z]{2}$/ || /\/8$/ ? () : s{^.+\/([^/]+)$}{$1} && $_, glob "$VNDB::ROOT/data/docs/*";
+ my @d = map /\.[a-z]{2}$/ || /\/(?:8|11)$/ ? () : s{^.+\/([^/]+)$}{$1} && $_, glob "$VNDB::ROOT/data/docs/*";
h2 class => 'alttitle', '...or a doc page';
div style => 'margin: 0 40px';
--
cgit v1.2.3
From e4b912bc68cebff9c42ef17e7356e9cf5a218272 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Mon, 9 Nov 2009 17:06:45 +0100
Subject: bb2html: Added [code] tag and fixed a minor bug
The previous version also had a problem with closing tags when a
$maxlength was defined. With $maxlength, not all tags actually open a
tag in HTML, after all.
---
ChangeLog | 1 +
data/docs/9 | 4 ++++
lib/VNDB/Func.pm | 23 ++++++++++++++++++-----
3 files changed, 23 insertions(+), 5 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 4d8752b8..76c87e22 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -3,6 +3,7 @@ git - ?
- Implemented proper daemonizing and error handling for Multi
- Added basic Makefile
- Added public database API
+ - Added [code] tag to bb2html()
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/data/docs/9 b/data/docs/9
index f5ee46ab..fedbd2b0 100644
--- a/data/docs/9
+++ b/data/docs/9
@@ -62,6 +62,10 @@
[raw]
Show off your formatting code skills by putting anything you don't want to have formatted in a [raw]
tag. Any of the formatting codes mentioned above are ignored within a [raw] .. [/raw] block.
+ [code]
+ Similar to [raw], except that the text within the [code] .. [/code] block is
+ formatted in a fixed width font and surrounded by a nice box to keep it
+ separate from the rest of your post.
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index ad38215e..49df0121 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -22,6 +22,7 @@ sub shorten {
# [raw] .. [/raw]
# [spoiler] .. [/spoiler]
# [quote] .. [/quote]
+# [code] .. [/code]
# v+, v+.+
# http://../
sub bb2html {
@@ -51,7 +52,7 @@ sub bb2html {
$rmnewline-- && $_ eq "\n" && next if $rmnewline;
my $lit = $_;
- if($open[$#open] ne 'raw') {
+ if($open[$#open] ne 'raw' && $open[$#open] ne 'code') {
if (lc$_ eq '[raw]') { push @open, 'raw'; next }
elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= ''; next }
elsif (lc$_ eq '[quote]') {
@@ -59,6 +60,11 @@ sub bb2html {
$result .= '' if !$maxlength;
$rmnewline = 1;
next
+ } elsif (lc$_ eq '[code]') {
+ push @open, 'code';
+ $result .= '
' if !$maxlength;
+ $rmnewline = 1;
+ next
} elsif (lc$_ eq '[/spoiler]') {
if($open[$#open] eq 'spoiler') {
$result .= '';
@@ -97,8 +103,12 @@ sub bb2html {
$result .= $_;
next;
}
- } elsif(lc$_ eq '[/raw]') {
- pop @open if $open[$#open] eq 'raw';
+ } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') {
+ pop @open;
+ next;
+ } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') {
+ $result .= '
' if !$maxlength;
+ pop @open;
next;
}
@@ -108,8 +118,11 @@ sub bb2html {
$result .= $e->($_);
}
- $result .= $_ eq 'url' ? '' : $_ eq 'quote' ? '
' : ''
- while((local $_ = pop @open) ne 'first');
+ # close open tags
+ while((local $_ = pop @open) ne 'first') {
+ $result .= $_ eq 'url' ? '' : $_ eq 'spoiler' ? '' : '';
+ $result .= $_ eq 'quote' ? '' : $_ eq 'code' ? '' : '' if !$maxlength;
+ }
$result .= '...' if $maxlength && $length > $maxlength;
return $result;
--
cgit v1.2.3
From 05afa16bc5214988fcf6e71736997913afb897d3 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 13 Nov 2009 09:19:56 +0100
Subject: Multi::API: Got rid of compile-time warning
Ambiguous use of -time resolved as -&time() at /lib/Multi/API.pm line 681.
No idea how you could interpret it differently, but oh well.
---
lib/Multi/API.pm | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 03d43a6f..4c160f70 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -677,8 +677,8 @@ sub admin {
(map +($_, $c->{$_}), qw|username ip client clientver connected cmds cmd_err|)
};
if($c->{username}) {
- $r->{t_cmd} = ($c->{throttle}[0]-time)/$_[HEAP]{throttle_cmd}[0];
- $r->{t_sql} = ($c->{throttle}[1]-time)/$_[HEAP]{throttle_sql}[0];
+ $r->{t_cmd} = ($c->{throttle}[0]-time())/$_[HEAP]{throttle_cmd}[0];
+ $r->{t_sql} = ($c->{throttle}[1]-time())/$_[HEAP]{throttle_sql}[0];
$r->{t_cmd} = 0 if $r->{t_cmd} < 0;
$r->{t_sql} = 0 if $r->{t_sql} < 0;
}
--
cgit v1.2.3
From 511c34e3ae8cd9d0f836fc9c745b1011bc731863 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 13 Nov 2009 09:38:46 +0100
Subject: Multi::IRC: More efficient and better throttle algorithm
Same algorithm I used for the API, although slightly more strict.
Also put a throttle on the !quote command.
---
lib/Multi/IRC.pm | 22 +++++++++++-----------
1 file changed, 11 insertions(+), 11 deletions(-)
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 9f792d54..462cd515 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -15,6 +15,8 @@ use POE qw|
|;
use POE::Component::IRC::Common ':ALL';
use URI::Escape 'uri_escape_utf8';
+use Time::HiRes 'time';
+
use constant {
USER => ARG0,
@@ -90,21 +92,17 @@ sub spawn {
# returns false if throttling isn't necessary for that key
sub throttle {
my($heap, $key, $tm, $num) = @_;
+ my $time = time;
# garbage collect
return ($heap->{throttle} = {
- map $heap->{throttle}{$_}[$#{$heap->{throttle}{$_}}] > time-3600 ? ($_, $heap->{throttle}{$_}) : (), keys %{$heap->{throttle}}
+ map $heap->{throttle}{$_} > $time ? ($_, $heap->{throttle}{$_}) : (), keys %{$heap->{throttle}}
}) if !$key;
+ $heap->{throttle}{$key} = $time if !$heap->{throttle}{$key} || $heap->{throttle}{$key} < $time;
$num ||= 1;
- my $dat = $heap->{throttle};
- if(!$dat->{$key}) {
- $dat->{$key} = [ time ];
- return 0;
- }
- $dat->{$key} = [ grep $_ > time-$tm, @{$dat->{$key}} ];
- return 1 if @{$dat->{$key}} >= $num;
- push @{$dat->{$key}}, time;
+ return 1 if $heap->{throttle}{$key}-$time > $tm*($num-1);
+ $heap->{throttle}{$key} += $tm;
return 0;
}
@@ -348,7 +346,7 @@ sub cmd_vn {
(my $q = $_[ARG]||'') =~ s/%//g;
return $_[KERNEL]->yield(reply => $_[DEST], 'You forgot the search query, dummy~~!', $_[USER]) if !$q;
return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER])
- if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 5;
+ if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 3;
$_[KERNEL]->post(pg => query => q|
SELECT 'v'::text AS type, v.id, vr.title
@@ -382,7 +380,7 @@ sub cmd_p {
(my $q = $_[ARG]||'') =~ s/%//g;
return $_[KERNEL]->yield(reply => $_[DEST], 'You forgot the search query, dummy~~!', $_[USER]) if !$q;
return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER])
- if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 5;
+ if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 3;
$_[KERNEL]->post(pg => query => q|
SELECT 'p'::text AS type, p.id, pr.name AS title
@@ -404,6 +402,8 @@ sub cmd_p_results { # num, res, \@_
sub cmd_quote {
+ return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER])
+ if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 3;
$_[KERNEL]->post(pg => query => q|SELECT quote FROM quotes ORDER BY random() LIMIT 1|, undef, 'cmd_quote_result', $_[DEST]);
}
--
cgit v1.2.3
From 8053d00b0c9efa9bf1a7b176dcb8d9c106c1ee0d Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 13 Nov 2009 10:05:22 +0100
Subject: Multi::IRC: Tweaked the idlequote timings
She shouldn't spam a quote more than once every 48 hours per channel now.
Also decreased the actual idle timeout, so that the channel doesn't have
to be idle for that long.
---
ChangeLog | 1 +
lib/Multi/IRC.pm | 4 ++--
2 files changed, 3 insertions(+), 2 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 76c87e22..c759b600 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -4,6 +4,7 @@ git - ?
- Added basic Makefile
- Added public database API
- Added [code] tag to bb2html()
+ - Tweaked Multi's idlequote timings
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 462cd515..7d74ded7 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -249,8 +249,8 @@ sub command { # mask, dest, msg
sub idlequote {
for (keys %{$_[HEAP]{idlequotes}}) {
next if --$_[HEAP]{idlequotes}{$_} > 0;
- $_[KERNEL]->yield(cmd_quote => '', [$_]) if $_[HEAP]{idlequotes}{$_} == 0;
- $_[HEAP]{idlequotes}{$_} = int(120+rand(600));
+ $_[KERNEL]->yield(cmd_quote => '', [$_]) if $_[HEAP]{idlequotes}{$_} == 0 && !throttle $_[HEAP], "idlequote_$_", 48*3600;
+ $_[HEAP]{idlequotes}{$_} = int(60+rand(300));
}
$_[KERNEL]->delay(idlequote => 60);
}
--
cgit v1.2.3
From 997d1752ff1b1d2b1cf425b2c05dc180abc2243a Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 13 Nov 2009 14:08:20 +0100
Subject: docs: Updated d11 and added :SUBSUB: macro
---
ChangeLog | 1 +
data/docs/11 | 42 ++++++++++++++++++++++++++++++++++++------
data/docs/index | 1 +
data/style.css | 3 ++-
lib/VNDB/Handler/Misc.pm | 7 ++++++-
5 files changed, 46 insertions(+), 8 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index c759b600..0cbaee4e 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -5,6 +5,7 @@ git - ?
- Added public database API
- Added [code] tag to bb2html()
- Tweaked Multi's idlequote timings
+ - Added :SUBSUB: macro to the doc pages
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/data/docs/11 b/data/docs/11
index b7f5ad45..a2928b7b 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -70,6 +70,34 @@ server resources and prevent abuse of this service.
Port19534 ('VN')
+
+Disclaimer
+These notes should be obvious, but I'll point it out anyway.
+
+ -
+ This service is still experimental and is provided for free. No guarantees
+ are given about the stability and uptime of this service. It may disappear
+ at any time, and access to it can be restricted without prior notice.
+
+ -
+ Changes to this API can be made without prior notice. Backwards compatibility
+ is provided when feasable, but non-compatible changes will be made at some
+ point. It is the responsibility of the application developers to keep their
+ applications up-to-date.
+
+ -
+ This service does not grant anyone the right to copy or republish the
+ information fetched using the API. VNDB does not have a clear copyright
+ statement, and in general we're not very strict about other people copying our
+ information, but this API is not intended to be used fetching and publishing
+ half of our database. When in doubt, ask.
+
+ -
+ Lastly, this API is not complete. If you have any specific features you'd
+ like to see, don't hesitate to ask.
+
+
+
:SUB:Request/response syntax
@@ -224,9 +252,10 @@ however still required.
differently and that the order of the members may not be the same. What each
member means and what possible values they can have differs per type and is
documented below.
-
+
-Returned object members for the 'vn' type:
+:SUBSUB:get vn
+The following members are returned from a 'get vn' command:
Member |
@@ -344,7 +373,7 @@ however still required.
-Accepted filters for the 'vn' type:
+'get vn' accepts the following filter expressions:
Field |
@@ -402,9 +431,10 @@ however still required.
novel and its releases. Note that the algorithm of this search may change and
that it can use a different algorithm than the search function on the website.
-
+
-Returned object members for the 'release' type:
+:SUBSUB:get release
+Returned members:
Member |
@@ -557,7 +587,7 @@ however still required.
-Accepted filters for the 'release' type:
+Accepted filters:
Field |
diff --git a/data/docs/index b/data/docs/index
index 0c94b71f..02c8f769 100644
--- a/data/docs/index
+++ b/data/docs/index
@@ -9,5 +9,6 @@
Discussion board
FAQ
About us
+ Database API
Development
diff --git a/data/style.css b/data/style.css
index 5dcd7643..7653824e 100644
--- a/data/style.css
+++ b/data/style.css
@@ -905,7 +905,8 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px
/***** Documentation pages *****/
.docs { padding: 0 15% 20px 15%; }
-.docs h3 { margin-top: 25px; }
+.docs h3 { margin-top: 30px; font-size: 14px }
+.docs h4 { margin-top: 15px; font-size: 12px }
.docs dd { padding-bottom: 5px; margin-left: 120px; }
.docs dt { float: left }
.docs ul.index { display: block; float: right; width: 150px; padding: 2px; margin: 0 0 10px 5px; background: url($_boxbg$) repeat; border: 1px solid $border$; }
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 9625372b..d2f5852b 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -281,12 +281,17 @@ sub docpage {
(my $title = shift @c) =~ s/^:TITLE://;
chomp $title;
- my $sec = 0;
+ my($sec, $subsec) = (0,0);
for (@c) {
s{^:SUB:(.+)\r?\n$}{
$sec++;
+ $subsec = 0;
qq|\n|
}eg;
+ s{^:SUBSUB:(.+)\r?\n$}{
+ $subsec++;
+ qq|\n|
+ }eg;
s{^:INC:(.+)\r?\n$}{
$f = sprintf('%s/data/docs/%s', $VNDB::ROOT, $1);
open($F, '<:utf8', $f.$l) or open($F, '<:utf8', $f) or die $!;
--
cgit v1.2.3
From 8578b87b8367a2f297a369cb3143627a4b37a4b0 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 13 Nov 2009 16:36:04 +0100
Subject: API: Added sorting and pagination
---
data/docs/11 | 43 +++++++++++++++------
lib/Multi/API.pm | 95 +++++++++++++++++++++++++++++++++++------------
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.
- 5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.
- 3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.
- - Each command currently returns at most 10 results. TODO: make configurable?
+ - Each command returns at most 10 results.
- 100 commands per 10 minutes per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.
-
1 second of SQL time per minute per user. SQL time is the total time taken to
@@ -221,27 +221,44 @@ however still required.
well), and lastly a filter expression.
- get type flags filters
+ get type flags filters options
- 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:
+ type and flags are unescaped strings. The accepted values for type are
+ documented below. 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 last options argument is optional, and
+ influences the behaviour of the returned results. When present, options
+ should be a JSON object with the following members (all are optional):
+
+
+ - page
-
+ integer, used for pagination. Page 1 (the default) returns the first 10
+ results (1-10), page 2 returns the following 10 (11-20), etc.
+
- sort
-
+ 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.
+
+ - reverse
- boolean, default false. Set to true to reverse the order of the results.
+
+
+ The following example will fetch basic information and information about the
+ related anime of the visual novel with id = 17:
get vn basic,anime (id = 17)
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 page 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:
- 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.
+Sorting is possible on the 'id', 'title' and 'released' fields.
'get vn' accepts the following filter expressions:
@@ -586,6 +604,7 @@ however still required.
+Sorting is possible on the 'id', 'title' and 'released' fields.
Accepted filters:
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
# [ 'login', {object} ]
#
-# C: get
-# [ 'get', , [ split ',', $2 ], [ filters ] ]
+# C: get
+# [ 'get', , [ split ',', $2 ], [ filters ], { options } ]
# must match /[a-z\/_]+/
# as string: /[a-z_]+(,[a-z_]+)*/, in perl: [ /[a-z_]+/, .. ]
+# 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
--
cgit v1.2.3
From 615b7bd42d96566e9cb1db5bb36870b87817af2f Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Fri, 13 Nov 2009 19:20:54 +0100
Subject: API: Added 'get producer' command
---
data/docs/11 | 128 +++++++++++++++++++++++++++++++++++++++++++++++++++++++
lib/Multi/API.pm | 95 ++++++++++++++++++++++++++++++++++++++++-
2 files changed, 221 insertions(+), 2 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 6fe7f3ed..7340319f 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -694,6 +694,134 @@ however still required.
+:SUBSUB:get producer
+Returned members:
+
+
+ Member |
+ Flag |
+ Type |
+ null |
+ Description |
+
+
+ id |
+ - |
+ integer |
+ no |
+ Producer ID |
+
+
+ name |
+ basic |
+ string |
+ no |
+ (romaji) producer name |
+
+
+ original |
+ basic |
+ string |
+ yes |
+ Original/official name |
+
+
+ type |
+ basic |
+ string |
+ no |
+ Producer type |
+
+
+ language |
+ basic |
+ string |
+ no |
+ Primary language |
+
+
+ website |
+ details |
+ string |
+ yes |
+ Official website URL |
+
+
+ aliases |
+ details |
+ string |
+ yes |
+ Comma separated list of alternative names |
+
+
+ description |
+ details |
+ string |
+ yes |
+ Description/notes of the producer, can contain formatting codes as described in d9.3 |
+
+
+ relations |
+ relations |
+ array of objects |
+ no |
+
+ (possibly empty) list of related producers, each object has the following members:
+ "id", integer, producer ID,
+ "relation", string, relation to the current producer,
+ "name", string,
+ "original", string, can be null
+ |
+
+
+Sorting is possible on the 'id' and 'name' fields.
+
+The following filters are recognised:
+
+
+ Field |
+ Value |
+ Operators |
+ |
+
+
+ id |
+ integer array of integers |
+ = != > >= < <= = != |
+ |
+
+
+ name |
+ string |
+ = != ~ |
+ |
+
+
+ original |
+ null string |
+ = != = != ~ |
+ |
+
+
+ type |
+ string |
+ = != |
+ |
+
+
+ language |
+ string array of strings |
+ = != |
+ |
+
+
+ search |
+ string |
+ ~ |
+ Not an actual field. Performs a search on the title, original and aliases fields. |
+
+
+
:SUB:The 'error' response
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index f360d231..47dbafea 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -40,7 +40,7 @@ sub spawn {
$p => [qw|
_start shutdown log server_error client_connect client_error client_input
login login_res get_results get_vn get_vn_res get_release get_release_res
- admin
+ get_producer get_producer_res admin
|],
],
heap => {
@@ -344,7 +344,7 @@ sub client_input {
filters => $arg->[2],
opt => $opt,
);
- return cerr $c, 'gettype', "Unknown get type: '$arg->[0]'" if $arg->[0] !~ /^(?:vn|release)$/;
+ return cerr $c, 'gettype', "Unknown get type: '$arg->[0]'" if $arg->[0] !~ /^(?:vn|release|producer)$/;
return $_[KERNEL]->yield("get_$arg->[0]", \%obj);
}
@@ -711,6 +711,97 @@ sub get_release_res {
}
+sub get_producer {
+ my $get = $_[ARG0];
+
+ return cerr $get->{c}, getinfo => "Unkown info flag '$_'", flag => $_
+ for (grep !/^(basic|details|relations)$/, @{$get->{info}});
+
+ my $select = 'p.id, p.latest';
+ $select .= ', pr.type, pr.name, pr.original, pr.lang AS language' if grep /basic/, @{$get->{info}};
+ $select .= ', pr.website, pr.desc AS description, pr.alias AS aliases' if grep /details/, @{$get->{info}};
+
+ my @placeholders;
+ my $where = encode_filters $get->{filters}, \&filtertosql, $get->{c}, \@placeholders, [
+ [ 'id',
+ [ 'int' => 'p.id :op: :value:', {qw|= = != <> > > < < <= <= >= >=|} ],
+ [ inta => 'p.id :op:(:value:)', {'=' => 'IN', '!= ' => 'NOT IN'}, join => ',' ],
+ ], [ 'name',
+ [ str => 'pr.name :op: :value:', {qw|= = != <>|} ],
+ [ str => 'pr.name ILIKE :value:', {'~',1}, process => \'like' ],
+ ], [ 'original',
+ [ undef, "pr.original :op: ''", {qw|= = != <>|} ],
+ [ str => 'pr.original :op: :value:', {qw|= = != <>|} ],
+ [ str => 'pr.original ILIKE :value:', {'~',1}, process => \'like' ]
+ ], [ 'type',
+ [ str => 'pr.type :op: :value:', {qw|= = != <>|},
+ process => sub { !grep($_ eq $_[0], @{$VNDB::S{producer_types}}) ? \'No such producer type' : $_[0] } ],
+ ], [ 'language',
+ [ str => 'pr.lang :op: :value:', {qw|= = != <>|}, process => \'lang' ],
+ [ stra => 'pr.lang :op:(:value:)', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'lang' ],
+ ], [ 'search',
+ [ str => '(pr.name ILIKE :value: OR pr.original ILIKE :value: OR pr.alias ILIKE :value:)', {'~',1}, process => \'like' ],
+ ],
+ ];
+ my $last = sqllast $get, 'id', {
+ id => 'p.id %s',
+ name => 'pr.name %s',
+ };
+ return if !$where || !$last;
+
+ $_[KERNEL]->post(pg => query =>
+ qq|SELECT $select FROM producers p JOIN producers_rev pr ON pr.id = p.latest WHERE $where AND NOT hidden $last|,
+ \@placeholders, 'get_producer_res', $get);
+}
+
+
+sub get_producer_res {
+ my($num, $res, $get, $time) = (@_[ARG0..$#_]);
+
+ $get->{time} += $time;
+ $get->{queries}++;
+
+ # process the results
+ if(!$get->{type}) {
+ for (@$res) {
+ $_->{id}*=1;
+ $_->{original} ||= undef if grep /basic/, @{$get->{info}};
+ if(grep /details/, @{$get->{info}}) {
+ $_->{website} ||= undef;
+ $_->{description} ||= undef;
+ $_->{aliases} ||= undef;
+ }
+ }
+ $get->{more} = pop(@$res)&&1 if @$res > $_[HEAP]{results};
+ $get->{list} = $res;
+ }
+ elsif($get->{type} eq 'relations') {
+ for my $i (@{$get->{list}}) {
+ $i->{relations} = [ grep $i->{latest} == $_->{pid1}, @$res ];
+ }
+ for (@$res) {
+ $_->{id}*=1;
+ $_->{original} ||= undef;
+ delete $_->{pid1};
+ }
+ $get->{relations} = 1;
+ }
+
+ # get more info
+ my @ids = map $_->{latest}, @{$get->{list}};
+ my $ids = join ',', map '?', @ids;
+
+ @ids && !$get->{relations} && grep(/relations/, @{$get->{info}}) && return $_[KERNEL]->post(pg => query => qq|
+ SELECT pl.pid1, p.id, pl.relation, pr.name, pr.original FROM producers_relations pl
+ JOIN producers p ON p.id = pl.pid2 JOIN producers_rev pr ON pr.id = p.latest WHERE pl.pid1 IN($ids) AND NOT p.hidden|,
+ \@ids, 'get_producer_res', { %$get, type => 'relations' });
+
+ # send results
+ delete $_->{latest} for @{$get->{list}};
+ $_[KERNEL]->yield(get_results => { %$get, type => 'producer' });
+}
+
+
# can be call()'ed from other sessions (specifically written for IRC)
sub admin {
my($func, @arg) = @_[ARG0..$#_];
--
cgit v1.2.3
From 0a4f97f0186d6941a4cab2e3bd05201f1fed1441 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 10:50:22 +0100
Subject: SQL/L10N: Allow NULL for releases_rev.minage and make the values
translatable
---
ChangeLog | 2 ++
data/global.pl | 18 +-----------------
data/lang.txt | 30 ++++++++++++++++++++++++++++++
lib/Multi/API.pm | 2 +-
lib/VNDB/DB/Releases.pm | 2 +-
lib/VNDB/DB/ULists.pm | 2 +-
lib/VNDB/Func.pm | 17 ++++++++++++++++-
lib/VNDB/Handler/Releases.pm | 25 +++++++++++++------------
lib/VNDB/Handler/VNPage.pm | 2 +-
util/dump.sql | 2 +-
util/updates/update_2.9.sql | 8 ++++++++
11 files changed, 75 insertions(+), 35 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 0cbaee4e..0c6dbecf 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -6,6 +6,8 @@ git - ?
- Added [code] tag to bb2html()
- Tweaked Multi's idlequote timings
- Added :SUBSUB: macro to the doc pages
+ - Allow NULL values for releases_rev.minage
+ - Made age ratings translatable
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/data/global.pl b/data/global.pl
index 8edd25fa..b67ae2f7 100644
--- a/data/global.pl
+++ b/data/global.pl
@@ -59,23 +59,7 @@ our %S = (%S,
'spa' => [ 6, 'ori' ],
'ori' => [ 7, 'spa' ],
},
- age_ratings => {
- -1 => [ 'Unknown' ],
- 0 => [ 'All ages' ,'CERO A' ],
- 6 => [ '6+' ],
- 7 => [ '7+' ],
- 8 => [ '8+' ],
- 9 => [ '9+' ],
- 10 => [ '10+' ],
- 11 => [ '11+' ],
- 12 => [ '12+', 'CERO B' ],
- 13 => [ '13+' ],
- 14 => [ '14+' ],
- 15 => [ '15+', 'CERO C' ],
- 16 => [ '16+' ],
- 17 => [ '17+', 'CERO D' ],
- 18 => [ '18+', 'CERO Z' ],
- },
+ age_ratings => [undef, 0, 6..18],
release_types => [qw|complete partial trial|],
platforms => [qw|win dos lin mac dvd gba msx nds nes p98 psp ps1 ps2 ps3 drc sat sfc wii xb3 oth|],
media => {
diff --git a/data/lang.txt b/data/lang.txt
index 935fb1e1..3b4a9ff0 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -997,6 +997,36 @@ cs : Vyřazeno
hu : Lemondva
+# Age ratings
+
+:_minage_null
+en : Unknown
+ru*:
+cs*:
+hu*:
+
+:_minage_all
+en : All ages
+ru*:
+cs*:
+hu*:
+
+# "Ages [_1] and up", but shorter
+:_minage_age
+en : [_1]+
+ru*:
+cs*:
+hu*:
+
+# [_1] is an example of an age rating, like 'CERO A', 'CERO D', etc.
+# this string is appended to the other _minage_* strings
+:_minage_example
+en : (e.g. [_1])
+ru*:
+cs*:
+hu*:
+
+
# Form messages
:_formerr_e_login_failed
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 47dbafea..8da59de6 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -624,7 +624,7 @@ sub get_release_res {
if(grep /details/, @{$get->{info}}) {
$_->{website} ||= undef;
$_->{notes} ||= undef;
- $_->{minage} = $_->{minage} < 0 ? undef : $_->{minage}*1;
+ $_->{minage} *= 1 if defined $_->{minage};
$_->{gtin} ||= undef;
$_->{catalog} ||= undef;
}
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 9260b787..d7bddaab 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -40,7 +40,7 @@ sub dbReleaseGet {
defined $o{type} ? (
'rr.type = ?' => $o{type} ) : (),
$o{minage} ? (
- '(rr.minage !s ? AND rr.minage <> -1)' => [ $o{minage}[0] ? '<=' : '>=', $o{minage}[1] ] ) : (),
+ '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} ? (
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index e9b38e57..0f54686c 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -78,7 +78,7 @@ sub dbVNListList {
} @$r;
my $rel = $self->dbAll(q|
- SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rr.minage, rl.rstat, rl.vstat
+ SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.rstat, rl.vstat
FROM rlists rl
JOIN releases r ON rl.rid = r.id
JOIN releases_rev rr ON rr.id = r.latest
diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm
index 49df0121..1801e3b8 100644
--- a/lib/VNDB/Func.pm
+++ b/lib/VNDB/Func.pm
@@ -6,7 +6,7 @@ use warnings;
use YAWF ':html';
use Exporter 'import';
use POSIX 'strftime', 'ceil', 'floor';
-our @EXPORT = qw| shorten bb2html gtintype liststat clearfloat cssicon tagscore mt |;
+our @EXPORT = qw| shorten bb2html gtintype liststat clearfloat cssicon tagscore mt minage |;
# I would've done this as a #define if this was C...
@@ -221,5 +221,20 @@ sub mt {
}
+sub minage {
+ my($a, $ex) = @_;
+ my $str = !defined($a) ? mt '_minage_null' : !$a ? mt '_minage_all' : mt '_minage_age', $a;
+ $ex = !defined($a) ? '' : {
+ 0 => 'CERO A',
+ 12 => 'CERO B',
+ 15 => 'CERO C',
+ 17 => 'CERO D',
+ 18 => 'CERO Z',
+ }->{$a} if $ex;
+ return $str if !$ex;
+ return $str.' '.mt('_minage_example', $ex);
+}
+
+
1;
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index 7365f235..daeffb9e 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -50,7 +50,7 @@ sub page {
[ languages => join => ', ', split => sub { map mt("_lang_$_"), @{$_[0]} } ],
[ 'website' ],
[ released => htmlize => sub { $self->{l10n}->datestr($_[0]) } ],
- [ minage => serialize => sub { $self->{age_ratings}{$_[0]}[0] } ],
+ [ minage => serialize => \&minage ],
[ notes => diff => 1 ],
[ platforms => join => ', ', split => sub { map mt("_plat_$_"), @{$_[0]} } ],
[ media => join => ', ', split => sub {
@@ -189,10 +189,10 @@ sub _infotable {
end;
end;
- if($r->{minage} >= 0) {
+ if(defined $r->{minage}) {
Tr ++$i % 2 ? (class => 'odd') : ();
td mt '_relinfo_minage';
- td $self->{age_ratings}{$r->{minage}}[0];
+ td minage $r->{minage};
end;
}
@@ -287,7 +287,8 @@ sub edit {
my $vn = $rid ? $r->{vn} : [{ vid => $vid, title => $v->{title} }];
my %b4 = !$rid ? () : (
(map { $_ => $r->{$_} } qw|type title original gtin catalog languages website released
- notes minage platforms patch resolution voiced freeware doujin ani_story ani_ero|),
+ notes platforms patch resolution voiced freeware doujin ani_story ani_ero|),
+ minage => defined($r->{minage}) ? $r->{minage} : -1,
media => join(',', sort map "$_->{medium} $_->{qty}", @{$r->{media}}),
producers => join('|||', map
sprintf('%d,%d,%s', $_->{id}, ($_->{developer}?1:0)+($_->{publisher}?2:0), $_->{name}),
@@ -311,7 +312,7 @@ sub edit {
{ name => 'languages', multi => 1, enum => $self->{languages} },
{ name => 'website', required => 0, default => '', template => 'url' },
{ name => 'released', required => 0, default => 0, template => 'int' },
- { name => 'minage' , required => 0, default => -1, enum => [ keys %{$self->{age_ratings}} ] },
+ { name => 'minage' , required => 0, default => -1, enum => [map !defined($_)?-1:$_, @{$self->{age_ratings}}] },
{ name => 'notes', required => 0, default => '', maxlength => 10240 },
{ name => 'platforms', required => 0, default => '', multi => 1, enum => $self->{platforms} },
{ name => 'media', required => 0, default => '' },
@@ -349,7 +350,8 @@ sub edit {
if(!$frm->{_err}) {
my %opts = (
(map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released
- notes minage platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|),
+ notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|),
+ minage => $frm->{minage} < 0 ? undef : $frm->{minage},
vn => $new_vn,
producers => $producers,
media => $media,
@@ -403,8 +405,7 @@ sub _form {
[ date => short => 'released', name => mt('_redit_form_released') ],
[ static => content => mt('_redit_form_released_note') ],
[ select => short => 'minage', name => mt('_redit_form_minage'),
- options => [ map [ $_, $self->{age_ratings}{$_}[0].($self->{age_ratings}{$_}[1]?" (e.g. $self->{age_ratings}{$_}[1])":'') ],
- sort { $a <=> $b } keys %{$self->{age_ratings}} ] ],
+ options => [ map [ !defined($_)?-1:$_, minage $_, 1 ], @{$self->{age_ratings}} ] ],
[ textarea => short => 'notes', name => mt('_redit_form_notes').'
'.mt('_inenglish').'' ],
[ static => content => mt('_redit_form_notes_note') ],
],
@@ -495,7 +496,7 @@ sub browse {
{ 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 => [ keys %{$self->{age_ratings}} ] },
+ { 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}} ] },
@@ -550,7 +551,7 @@ sub browse {
td class => 'tc1';
lit $self->{l10n}->datestr($l->{released});
end;
- td class => 'tc2', $l->{minage} > -1 ? $self->{age_ratings}{$l->{minage}}[0] : '';
+ td class => 'tc2', !defined($l->{minage}) ? '' : minage $l->{minage};
td class => 'tc3';
$_ ne 'oth' && cssicon $_, mt "_plat_$_" for (@{$l->{platforms}});
cssicon "lang $_", mt "_lang_$_" for (@{$l->{languages}});
@@ -599,8 +600,8 @@ sub _filters {
option value => 1, $f->{ma_m} == 1 ? ('selected' => 'selected') : (), mt '_rbrowse_le';
end;
Select id => 'ma_a', name => 'ma_a', style => 'width: 80px; text-align: center';
- $_>=0 && option value => $_, $f->{ma_a} == $_ ? ('selected' => 'selected') : (), $self->{age_ratings}{$_}[0]
- for (sort { $a <=> $b } keys %{$self->{age_ratings}});
+ defined($_) && option value => $_, $f->{ma_a} == $_ ? ('selected' => 'selected') : (), minage $_
+ for (@{$self->{age_ratings}});
end;
end;
td rowspan => 5, style => 'padding-left: 40px';
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index ec5f4afc..7a258d5a 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -398,7 +398,7 @@ sub _releases {
for my $rel (grep grep($_ eq $l, @{$_->{languages}}), @$r) {
Tr;
td class => 'tc1'; lit $self->{l10n}->datestr($rel->{released}); end;
- td class => 'tc2', $rel->{minage} < 0 ? '' : $self->{age_ratings}{$rel->{minage}}[0];
+ td class => 'tc2', !defined($rel->{minage}) ? '' : minage $rel->{minage};
td class => 'tc3';
for (sort @{$rel->{platforms}}) {
next if $_ eq 'oth';
diff --git a/util/dump.sql b/util/dump.sql
index cd6f9434..8edf56c5 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -129,7 +129,7 @@ CREATE TABLE releases_rev (
website varchar(250) NOT NULL DEFAULT '',
released integer NOT NULL,
notes text NOT NULL DEFAULT '',
- minage smallint NOT NULL DEFAULT -1,
+ minage smallint,
gtin bigint NOT NULL DEFAULT 0,
patch boolean NOT NULL DEFAULT FALSE,
catalog varchar(50) NOT NULL DEFAULT '',
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index ee2570fb..4321f898 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -30,3 +30,11 @@ BEGIN
END;
$$ LANGUAGE plpgsql;
SELECT tag_vn_calc();
+
+
+
+-- releases_rev.minage should accept NULL
+ALTER TABLE releases_rev ALTER COLUMN minage DROP NOT NULL;
+ALTER TABLE releases_rev ALTER COLUMN minage DROP DEFAULT;
+UPDATE releases_rev SET minage = NULL WHERE minage < 0;
+
--
cgit v1.2.3
From aef1e3f073df3494711e949eb2aa31c60cad8460 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 11:51:31 +0100
Subject: Added wikipedia links for producers
---
ChangeLog | 1 +
data/lang.txt | 24 ++++++++++++++++++++++++
lib/VNDB/DB/Producers.pm | 6 +++---
lib/VNDB/Handler/Producers.pm | 19 ++++++++++++++++---
util/dump.sql | 3 ++-
util/updates/update_2.9.sql | 4 ++++
6 files changed, 50 insertions(+), 7 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 0c6dbecf..54379b02 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -8,6 +8,7 @@ git - ?
- Added :SUBSUB: macro to the doc pages
- Allow NULL values for releases_rev.minage
- Made age ratings translatable
+ - Added wikipedia link for producers
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/data/lang.txt b/data/lang.txt
index 3b4a9ff0..06975ddc 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -2176,6 +2176,18 @@ ru : a.k.a. [_1]
cs : a.k.a. [_1]
hu :
+:_prodpage_homepage
+en : Homepage
+ru*:
+cs*:
+hu*:
+
+:_prodpage_wikipedia
+en : Wikipedia
+ru*:
+cs*:
+hu*:
+
:_prodpage_vnrel
en : Visual Novel Relations
ru : Связи новелл
@@ -2239,6 +2251,12 @@ ru : Веб-сайт
cs : Internetová stránka
hu : Weboldal
+:_revfield_p_l_wp
+en : Wikipedia link
+ru*:
+cs*:
+hu*:
+
:_revfield_p_desc
en : Description
ru : Описание
@@ -2335,6 +2353,12 @@ ru : Веб-сайт
cs : Internetová stránka
hu : Weboldal
+:_pedit_form_wikipedia
+en : Wikipedia link
+ru*:
+cs*:
+hu*:
+
:_pedit_form_desc
en : Description
ru : Описание
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index 7c7da3ba..0539634c 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -43,7 +43,7 @@ sub dbProducerGet {
push @join, 'JOIN relgraphs pg ON pg.id = p.rgraph' if $o{what} =~ /relgraph/;
my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph';
- $select .= ', pr.desc, pr.alias, pr.website, p.hidden, p.locked' if $o{what} =~ /extended/;
+ $select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/;
$select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev| if $o{what} =~ /changes/;
$select .= ', pg.svg' if $o{what} =~ /relgraph/;
@@ -125,9 +125,9 @@ sub dbProducerAdd {
sub insert_rev {
my($self, $cid, $pid, $o) = @_;
$self->dbExec(q|
- INSERT INTO producers_rev (id, pid, name, original, website, type, lang, "desc", alias)
+ INSERT INTO producers_rev (id, pid, name, original, website, l_wp, type, lang, "desc", alias)
VALUES (!l)|,
- [ $cid, $pid, @$o{qw| name original website type lang desc alias|} ]
+ [ $cid, $pid, @$o{qw| name original website l_wp type lang desc alias|} ]
);
$self->dbExec(q|
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index 27534e67..e3473c3c 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -62,6 +62,9 @@ sub page {
[ alias => diff => 1 ],
[ lang => serialize => sub { "$_[0] (".mt("_lang_$_[0]").')' } ],
[ website => diff => 1 ],
+ [ l_wp => htmlize => sub {
+ $_[0] ? sprintf '%1$s', xml_escape $_[0] : mt '_vndiff_nolink' # _vn? hmm...
+ }],
[ desc => diff => 1 ],
[ relations => join => '
', split => sub {
my @r = map sprintf('%s: %s',
@@ -79,9 +82,15 @@ sub page {
p class => 'center';
txt mt '_prodpage_langtype', mt("_lang_$p->{lang}"), mt "_ptype_$p->{type}";
txt "\n".mt '_prodpage_aliases', $p->{alias} if $p->{alias};
- if($p->{website}) {
- txt "\n";
- a href => $p->{website}, $p->{website};
+
+ my @links = (
+ $p->{website} ? [ 'homepage', $p->{website} ] : (),
+ $p->{l_wp} ? [ 'wikipedia', "http://en.wikipedia.org/wiki/$p->{l_wp}" ] : (),
+ );
+ txt "\n" if @links;
+ for(@links) {
+ a href => $_->[1], mt "_prodpage_$_->[0]";
+ txt ' - ' if $_ ne $links[$#links];
}
end;
@@ -146,6 +155,7 @@ sub edit {
my %b4 = !$pid ? () : (
(map { $_ => $p->{$_} } qw|type name original lang website desc alias|),
+ l_wp => $p->{l_wp} || '',
prodrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{name}, sort { $a->{id} <=> $b->{id} } @{$p->{relations}}),
);
my $frm;
@@ -158,6 +168,7 @@ sub edit {
{ name => 'alias', required => 0, maxlength => 500, default => '' },
{ name => 'lang', enum => $self->{languages} },
{ name => 'website', required => 0, template => 'url', default => '' },
+ { name => 'l_wp', required => 0, maxlength => 150, default => '' },
{ name => 'desc', required => 0, maxlength => 5000, default => '' },
{ name => 'prodrelations', required => 0, maxlength => 5000, default => '' },
{ name => 'editsum', maxlength => 5000 },
@@ -173,6 +184,7 @@ sub edit {
if $pid && !grep $frm->{$_} ne $b4{$_}, keys %b4;
$frm->{relations} = $relations;
+ $frm->{l_wp} = undef if !$frm->{l_wp};
$rev = 1;
my $npid = $pid;
my $cid;
@@ -210,6 +222,7 @@ sub edit {
[ select => name => mt('_pedit_form_lang'), short => 'lang',
options => [ map [ $_, "$_ (".mt("_lang_$_").')' ], sort @{$self->{languages}} ] ],
[ input => name => mt('_pedit_form_website'), short => 'website' ],
+ [ input => name => mt('_pedit_form_wikipedia'), short => 'l_wp', pre => 'http://en.wikipedia.org/wiki/' ],
[ text => name => mt('_pedit_form_desc').'
'.mt('_inenglish').'', short => 'desc', rows => 6 ],
], 'pedit_rel' => [ mt('_pedit_form_rel'),
[ hidden => short => 'prodrelations' ],
diff --git a/util/dump.sql b/util/dump.sql
index 8edf56c5..dfdeb021 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -69,7 +69,8 @@ CREATE TABLE producers_rev (
website varchar(250) NOT NULL DEFAULT '',
lang varchar NOT NULL DEFAULT 'ja',
"desc" text NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT ''
+ alias varchar(500) NOT NULL DEFAULT '',
+ l_wp varchar(150)
);
-- quotes
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 4321f898..9bb00979 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -38,3 +38,7 @@ ALTER TABLE releases_rev ALTER COLUMN minage DROP NOT NULL;
ALTER TABLE releases_rev ALTER COLUMN minage DROP DEFAULT;
UPDATE releases_rev SET minage = NULL WHERE minage < 0;
+
+-- wikipedia link for producers
+ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150);
+
--
cgit v1.2.3
From 537bba22c0ed0c0fe52ba067f136b30e7f95d968 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 11:51:48 +0100
Subject: Made external links on VN pages translatable
---
ChangeLog | 2 +-
data/lang.txt | 18 ++++++++++++++++++
lib/VNDB/Handler/VNPage.pm | 8 ++++----
3 files changed, 23 insertions(+), 5 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 54379b02..98d46ab0 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -7,7 +7,7 @@ git - ?
- Tweaked Multi's idlequote timings
- Added :SUBSUB: macro to the doc pages
- Allow NULL values for releases_rev.minage
- - Made age ratings translatable
+ - Made age ratings and external VN link titles translatable
- Added wikipedia link for producers
2.8 - 2009-10-24
diff --git a/data/lang.txt b/data/lang.txt
index 06975ddc..e0e7bfce 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -5345,6 +5345,24 @@ ru : Ссылки
cs : Odkazy
hu : Linkek
+:_vnpage_l_wp
+en : Wikipedia
+ru*:
+cs*:
+hu*:
+
+:_vnpage_l_encubed
+en : Encubed
+ru*:
+cs*:
+hu*:
+
+:_vnpage_l_renai
+en : Renai.us
+ru*:
+cs*:
+hu*:
+
:_vnpage_description
en : Description
ru : Описание
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 7a258d5a..b27fa58b 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -111,16 +111,16 @@ sub page {
end;
}
my @links = (
- $v->{l_wp} ? [ 'Wikipedia', 'http://en.wikipedia.org/wiki/%s', $v->{l_wp} ] : (),
- $v->{l_encubed} ? [ 'Encubed', 'http://novelnews.net/tag/%s/', $v->{l_encubed} ] : (),
- $v->{l_renai} ? [ 'Renai.us', 'http://renai.us/game/%s.shtml', $v->{l_renai} ] : (),
+ $v->{l_wp} ? [ 'wp', 'http://en.wikipedia.org/wiki/%s', $v->{l_wp} ] : (),
+ $v->{l_encubed} ? [ 'encubed', 'http://novelnews.net/tag/%s/', $v->{l_encubed} ] : (),
+ $v->{l_renai} ? [ 'renai', 'http://renai.us/game/%s.shtml', $v->{l_renai} ] : (),
);
if(@links) {
Tr ++$i % 2 ? (class => 'odd') : ();
td mt '_vnpage_links';
td;
for(@links) {
- a href => sprintf($_->[1], $_->[2]), $_->[0];
+ a href => sprintf($_->[1], $_->[2]), mt "_vnpage_l_$_->[0]";
txt ', ' if $_ ne $links[$#links];
}
end;
--
cgit v1.2.3
From 848cae8e13d6ab8110caff39acf671822659de08 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 11:58:07 +0100
Subject: API: Changed 'website' member of get producer to a links object
Which also covers the wikipedia link, and possibly more in the future.
---
data/docs/11 | 13 +++++++++----
lib/Multi/API.pm | 7 +++++--
2 files changed, 14 insertions(+), 6 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 7340319f..f1c84dce 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -740,11 +740,16 @@ however still required.
Primary language |
- website |
+ links |
details |
- string |
- yes |
- Official website URL |
+ object |
+ no |
+
+ External links, object has the following members:
+ "homepage", official homepage,
+ "wikipedia", title of the related article on the English wikipedia.
+ Both values can be null.
+ |
aliases |
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 8da59de6..8014f9be 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -719,7 +719,7 @@ sub get_producer {
my $select = 'p.id, p.latest';
$select .= ', pr.type, pr.name, pr.original, pr.lang AS language' if grep /basic/, @{$get->{info}};
- $select .= ', pr.website, pr.desc AS description, pr.alias AS aliases' if grep /details/, @{$get->{info}};
+ $select .= ', pr.website, pr.l_wp, pr.desc AS description, pr.alias AS aliases' if grep /details/, @{$get->{info}};
my @placeholders;
my $where = encode_filters $get->{filters}, \&filtertosql, $get->{c}, \@placeholders, [
@@ -767,7 +767,10 @@ sub get_producer_res {
$_->{id}*=1;
$_->{original} ||= undef if grep /basic/, @{$get->{info}};
if(grep /details/, @{$get->{info}}) {
- $_->{website} ||= undef;
+ $_->{links} = {
+ homepage => delete($_->{website})||undef,
+ wikipedia => delete $_->{l_wp},
+ };
$_->{description} ||= undef;
$_->{aliases} ||= undef;
}
--
cgit v1.2.3
From c7ff7086534cd9de7ce4043eb10c7161401c1b96 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 12:57:14 +0100
Subject: d11: Finalized API documentation
---
data/docs/11 | 47 ++++++++++++++++++++++++++++++++++++-----------
1 file changed, 36 insertions(+), 11 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index f1c84dce..199935fd 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -1,10 +1,4 @@
:TITLE:Public Database API
-
-
-
IN DEVELOPMENT
-
This API is currently in development, pretty much everything is subject to change without notice.
-
-
:INC:index
:SUB:Introduction
@@ -64,10 +58,10 @@ server resources and prevent abuse of this service.
-Test version:
+Connection info:
- - Host (beta)
- beta.vndb.org
- - Port
- 19534 ('VN')
+ - Host
- api.vndb.org (primary), beta.vndb.org (available for testing)
+ - Port (tcp)
- 19534 ('VN')
@@ -98,6 +92,35 @@ server resources and prevent abuse of this service.
+
+Implementation tips
+Like the disclaimer, these notes should be obvious, but it can't hurt to state them anyway.
+
+ -
+ Cache everything you receive, the data on VNDB doesn't change very
+ often. Even if you only refresh the cached information once every month, you
+ can be sure that most of the data hasn't changed within that time.
+
+ -
+ Program defensively. Even when the documentation says that a string will be
+ returned, it may very well happen that this is later changed into an array.
+ Make sure your application doesn't completely crash in such an event. Also keep
+ in mind that for every command you send, the server can reply with an error, or
+ can immediately close the connection (but this is be rare).
+
+ -
+ Regularly check this page to see if anything has changed to the API, and
+ update your client where necessary. I'll probably add a changelog to the bottom
+ of this page to make this easier.
+
+ -
+ Use a JSON library for both encoding and decoding JSON data. While the format
+ is relatively easy to use, you're much more likely to introduce bugs when you're
+ writing your own parser.
+
+
+
+
:SUB:Request/response syntax
@@ -426,8 +449,9 @@ however still required.
= != = != > >= < <= |
Note that matching on partial dates (released = "2009") doesn't do what
- you want, use ranges instead, e.g. (released > "2008" and released <= "2009").
+ you want, use ranges instead, e.g. (released > "2008" and released <= "2009").
|
+
platforms |
null string array of strings |
@@ -449,6 +473,7 @@ however still required.
novel and its releases. Note that the algorithm of this search may change and
that it can use a different algorithm than the search function on the website.
+
:SUBSUB:get release
@@ -851,7 +876,7 @@ however still required.
missingA JSON object argument is missing a required member. The name of which is given in the additional "field" member.
badargA JSON value is of the wrong type or in the wrong format. The name of the incorrect field is given in a "field" member.
needloginNeed to be logged in to issue this command.
- throttled
+ throttled
You have used too many server resources within a short time, and need to wait
a bit before sending the next command. The type of throttle is given in the
"type" member, and the "minwait" and "fullwait" members tell you how long you
--
cgit v1.2.3
From ed15643975e6e05ce5e8b9509901f769c86e06b4 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 16:37:25 +0100
Subject: Added bayesian rating and vote count to the VN list
It's even realtime! To my surprise this calculation isn't very heavy, or
PostgreSQL is just extremely fast. The GetVN query on /v/all takes 100ms
in the worst case (instead of the usual 30-60ms). Can always cache this
later on.
---
ChangeLog | 1 +
data/lang.txt | 6 ++++++
data/style.css | 3 ++-
lib/VNDB/DB/VN.pm | 5 ++++-
lib/VNDB/Handler/VNBrowse.pm | 8 +++++++-
util/dump.sql | 10 ++++++++++
util/updates/update_2.9.sql | 10 ++++++++++
7 files changed, 40 insertions(+), 3 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 98d46ab0..92cfa0e8 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -9,6 +9,7 @@ git - ?
- Allow NULL values for releases_rev.minage
- Made age ratings and external VN link titles translatable
- Added wikipedia link for producers
+ - Added bayesian rating for VNs
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/data/lang.txt b/data/lang.txt
index e0e7bfce..68813be9 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -4627,6 +4627,12 @@ ru : Популярность
cs : Popularita
hu : Népszerűség
+:_vnbrowse_col_rating
+en : Rating
+ru*:
+cs*:
+hu*:
+
:_vnbrowse_tagign_title
en : The following tags were ignored:
ru : Следующие теги были пропущены:
diff --git a/data/style.css b/data/style.css
index 7653824e..d6542477 100644
--- a/data/style.css
+++ b/data/style.css
@@ -786,7 +786,8 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px
.vnbrowse .tc_s { width: 65px }
.vnbrowse .tc2 { text-align: right; padding: 0; }
.vnbrowse .tc3 { padding: 0; }
-.vnbrowse .tc5 { text-align: right; padding-right: 10px; }
+.vnbrowse .tc5 { text-align: right; padding-right: 10px }
+.vnbrowse .tc6 { width: 80px }
#advselect {
text-align: center;
display: block;
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index bb2c1275..b1fb103b 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -78,6 +78,8 @@ sub dbVNGet {
'JOIN users u ON u.id = c.requester' : (),
$o{what} =~ /relgraph/ ?
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
+ $o{what} =~ /rating/ ?
+ 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (),
);
my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]};
@@ -89,6 +91,7 @@ sub dbVNGet {
qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
$o{what} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (),
+ $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (),
$tag_ids ?
qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (),
);
@@ -98,7 +101,7 @@ sub dbVNGet {
FROM vn_rev vr
!s
!W
- ORDER BY !s|,
+ ORDER BY !s NULLS LAST|,
join(', ', @select), join(' ', @join), \%where, $o{order},
);
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index bca21151..87f3371a 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -16,7 +16,7 @@ sub list {
my($self, $char) = @_;
my $f = $self->formValidate(
- { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore| ] },
+ { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] },
{ name => 'o', required => 0, enum => [ 'a','d' ] },
{ name => 'p', required => 0, default => 1, template => 'int' },
{ name => 'q', required => 0, default => '' },
@@ -56,6 +56,7 @@ sub list {
$f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o};
my($list, $np) = $self->dbVNGet(
+ what => 'rating',
$char ne 'all' ? ( char => $char ) : (),
$f->{q} ? ( search => $f->{q} ) : (),
results => 50,
@@ -90,6 +91,7 @@ sub list {
[ '', 0, undef, 'tc3' ],
[ mt('_vnbrowse_col_released'), 'rel', undef, 'tc4' ],
[ mt('_vnbrowse_col_popularity'), 'pop', undef, 'tc5' ],
+ [ mt('_vnbrowse_col_rating'), 'rating', undef, 'tc6' ],
],
row => sub {
my($s, $n, $l) = @_;
@@ -114,6 +116,10 @@ sub list {
lit $self->{l10n}->datestr($l->{c_released});
end;
td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100;
+ td class => 'tc6';
+ txt sprintf '%.2f', $l->{rating}||0;
+ b class => 'grayedout', sprintf ' (%d)', $l->{votecount}||0;
+ end;
end;
},
);
diff --git a/util/dump.sql b/util/dump.sql
index dfdeb021..d64fe12f 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -829,6 +829,16 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PRO
---------------------------------
+-- bayesian rating view
+CREATE OR REPLACE VIEW vn_ratings AS
+ SELECT vid, COUNT(uid) AS votecount, (
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
+ ) AS rating
+ FROM votes
+ GROUP BY vid;
+
+
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 9bb00979..68ea2052 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -42,3 +42,13 @@ UPDATE releases_rev SET minage = NULL WHERE minage < 0;
-- wikipedia link for producers
ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150);
+
+-- bayesian rating
+CREATE OR REPLACE VIEW vn_ratings AS
+ SELECT vid, COUNT(uid) AS votecount, (
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
+ ) AS rating
+ FROM votes
+ GROUP BY vid;
+
--
cgit v1.2.3
From 2394a77e8d503ebcf78109ced76680e46770b617 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 17:08:19 +0100
Subject: Added bayesian rating info to VN pages
This adds about 100ms (sometimes more) to the page generation time of VN
pages... maybe I should cache the ratings after all.
---
data/lang.txt | 26 ++++++++++++++++----------
lib/VNDB/DB/VN.pm | 7 +++++--
lib/VNDB/Handler/VNPage.pm | 2 +-
lib/VNDB/Util/CommonHTML.pm | 5 +++--
4 files changed, 25 insertions(+), 15 deletions(-)
diff --git a/data/lang.txt b/data/lang.txt
index 68813be9..36a2bd40 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -5623,17 +5623,23 @@ ru : Недавно проголосовали
cs : Poslední hlasy
hu : Legútóbbi szavazatok
-:_votestats_pop_title
-en : Popularity
-ru : Популярность
-cs : Popularita
-hu : Népszerűsség
+:_votestats_rank_title
+en : Ranking
+ru*:
+cs*:
+hu*:
-:_votestats_pop_sum
-en : Ranked #[_1] out of [_2] with a score of [_3].
-ru : Рейтинг - #[_1] из [_2], средний балл - [_3].
-cs : Hodnoceno na [_1]. místě z celkem [_2] se skóre [_3].
-hu : [_1]. helyezet a [_2] bejegyzésből [_3] pontszámmal.
+:_votestats_rank_pop
+en : Popularity: ranked #[_1] with a score of [_2]
+ru*:
+cs*:
+hu*:
+
+:_votestats_rank_rat
+en : Bayesian rating: ranked #[_1] with a rating of [_2]
+ru*:
+cs*:
+hu*:
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index b1fb103b..4201181a 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -78,7 +78,7 @@ sub dbVNGet {
'JOIN users u ON u.id = c.requester' : (),
$o{what} =~ /relgraph/ ?
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
- $o{what} =~ /rating/ ?
+ $o{what} =~ /(rating|ranking)/ ?
'LEFT JOIN vn_ratings r ON r.vid = v.id' : (),
);
@@ -90,7 +90,10 @@ sub dbVNGet {
$o{what} =~ /changes/ ? (
qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
- $o{what} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (),
+ $o{what} =~ /ranking/ ? (
+ '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking',
+ '(SELECT COUNT(*)+1 FROM vn_ratings ir WHERE ir.rating > r.rating) AS r_ranking',
+ ) : (),
$o{what} =~ /rating/ ? 'r.rating, r.votecount' : (),
$tag_ids ?
qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (),
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index b27fa58b..2a333337 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -46,7 +46,7 @@ sub page {
my $v = $self->dbVNGet(
id => $vid,
- what => 'extended anime relations screenshots ranking'.($rev ? ' changes' : ''),
+ what => 'extended anime relations screenshots rating ranking'.($rev ? ' changes' : ''),
$rev ? (rev => $rev) : (),
)->[0];
return 404 if !$v->{id};
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index ad99d32d..857cdcef 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -481,8 +481,9 @@ sub htmlVoteStats {
clearfloat;
if($type eq 'v') {
div;
- h3 mt '_votestats_pop_title';
- p mt '_votestats_pop_sum', $obj->{ranking}, $self->{stats}{vn}, sprintf('%0.2f',$obj->{c_popularity}*100);
+ h3 mt '_votestats_rank_title';
+ p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100;
+ p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{rating};
end;
}
end;
--
cgit v1.2.3
From 151a8338c931389c6d67389c56c2d8862e882de6 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 18:46:59 +0100
Subject: SQL: Cached bayesian VN rating and vote counts
Was a good idea after all...
---
lib/Multi/Maintenance.pm | 18 ++++++++++++++++--
lib/VNDB/DB/VN.pm | 10 ++++------
lib/VNDB/Handler/VNBrowse.pm | 14 +++++++++++---
lib/VNDB/Util/CommonHTML.pm | 2 +-
util/dump.sql | 14 +++-----------
util/updates/update_2.9.sql | 12 +++++++-----
6 files changed, 42 insertions(+), 28 deletions(-)
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 4f816e56..422b5970 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -17,12 +17,12 @@ sub spawn {
package_states => [
$p => [qw|
_start shutdown set_daily daily set_monthly monthly log_stats
- vncache tagcache vnpopularity cleangraphs
+ vncache tagcache vnpopularity vnrating cleangraphs
usercache statscache logrotate
|],
],
heap => {
- daily => [qw|vncache tagcache vnpopularity cleangraphs|],
+ daily => [qw|vncache tagcache vnpopularity vnrating cleangraphs|],
monthly => [qw|usercache statscache logrotate|],
@_,
},
@@ -117,6 +117,20 @@ sub vnpopularity {
}
+sub vnrating {
+ # takes less than a second, but can be performed in ranges as well when necessary
+ $_[KERNEL]->post(pg => do => q|
+ UPDATE vn SET
+ c_rating = (SELECT (
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
+ ) FROM votes WHERE vid = id
+ ),
+ c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0)
+ |, undef, 'log_stats', 'vnrating');
+}
+
+
sub cleangraphs {
# should be pretty fast
$_[KERNEL]->post(pg => do => q|
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 4201181a..250f5267 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -11,7 +11,7 @@ our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd
# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, order, what
-# What: extended anime relations screenshots relgraph ranking changes
+# What: extended anime relations screenshots relgraph rating ranking changes
sub dbVNGet {
my($self, %o) = @_;
$o{results} ||= 10;
@@ -78,23 +78,21 @@ sub dbVNGet {
'JOIN users u ON u.id = c.requester' : (),
$o{what} =~ /relgraph/ ?
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
- $o{what} =~ /(rating|ranking)/ ?
- 'LEFT JOIN vn_ratings r ON r.vid = v.id' : (),
);
my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]};
my @select = (
- qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph v.c_popularity|, 'vr.id AS cid',
+ qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid',
$o{what} =~ /extended/ ? (
qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (),
$o{what} =~ /changes/ ? (
qw|c.requester c.comments v.latest u.username c.rev c.causedby|, q|extract('epoch' from c.added) as added|) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
+ $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
$o{what} =~ /ranking/ ? (
'(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking',
- '(SELECT COUNT(*)+1 FROM vn_ratings ir WHERE ir.rating > r.rating) AS r_ranking',
+ '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking',
) : (),
- $o{what} =~ /rating/ ? 'r.rating, r.votecount' : (),
$tag_ids ?
qq|(SELECT AVG(tvb.rating) FROM tags_vn_bayesian tvb WHERE tvb.tag IN($tag_ids) AND tvb.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvb.vid) AS tagscore| : (),
);
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index 87f3371a..3aaa1e25 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -55,13 +55,21 @@ sub list {
$f->{s} = 'title' if !@ti && $f->{s} eq 'tagscore';
$f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o};
+ my $sortcol = {qw|
+ rel c_released
+ pop c_popularity
+ rating c_rating
+ title title
+ tagscore tagscore
+ |}->{$f->{s}};
+
my($list, $np) = $self->dbVNGet(
what => 'rating',
$char ne 'all' ? ( char => $char ) : (),
$f->{q} ? ( search => $f->{q} ) : (),
results => 50,
page => $f->{p},
- order => ($f->{s} eq 'rel' ? 'c_released' : $f->{s} eq 'pop' ? 'c_popularity' : $f->{s}).($f->{o} eq 'a' ? ' ASC' : ' DESC'),
+ order => $sortcol.($f->{o} eq 'a' ? ' ASC' : ' DESC'),
$f->{pl}[0] ? ( platform => $f->{pl} ) : (),
$f->{ln}[0] ? ( lang => $f->{ln} ) : (),
@ti ? (tags_include => [ $f->{sp}, \@ti ]) : (),
@@ -117,8 +125,8 @@ sub list {
end;
td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100;
td class => 'tc6';
- txt sprintf '%.2f', $l->{rating}||0;
- b class => 'grayedout', sprintf ' (%d)', $l->{votecount}||0;
+ txt sprintf '%.2f', $l->{c_rating};
+ b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount};
end;
end;
},
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 857cdcef..bb731a26 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -483,7 +483,7 @@ sub htmlVoteStats {
div;
h3 mt '_votestats_rank_title';
p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100;
- p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{rating};
+ p mt '_votestats_rank_rat', $obj->{r_ranking}, sprintf '%.2f', $obj->{c_rating};
end;
}
end;
diff --git a/util/dump.sql b/util/dump.sql
index d64fe12f..43562da3 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -291,7 +291,9 @@ CREATE TABLE vn (
c_released integer NOT NULL DEFAULT 0,
c_languages varchar(32) NOT NULL DEFAULT '',
c_platforms varchar(32) NOT NULL DEFAULT '',
- c_popularity real NOT NULL DEFAULT 0
+ c_popularity real NOT NULL DEFAULT 0,
+ c_rating real,
+ c_votecount integer NOT NULL DEFAULT 0
);
-- vn_anime
@@ -829,16 +831,6 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PRO
---------------------------------
--- bayesian rating view
-CREATE OR REPLACE VIEW vn_ratings AS
- SELECT vid, COUNT(uid) AS votecount, (
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
- ) AS rating
- FROM votes
- GROUP BY vid;
-
-
-- Sequences used for ID generation of items not in the DB
CREATE SEQUENCE covers_seq;
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 68ea2052..0720681e 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -44,11 +44,13 @@ ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150);
-- bayesian rating
-CREATE OR REPLACE VIEW vn_ratings AS
- SELECT vid, COUNT(uid) AS votecount, (
+ALTER TABLE vn ADD COLUMN c_rating real;
+ALTER TABLE vn ADD COLUMN c_votecount integer NOT NULL DEFAULT 0;
+UPDATE vn SET
+ c_rating = (SELECT (
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
- ) AS rating
- FROM votes
- GROUP BY vid;
+ ) FROM votes WHERE vid = id
+ ),
+ c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0);
--
cgit v1.2.3
From 2c6f62c89c93dbb5ec757c11f2af38534f2c760a Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 18:51:33 +0100
Subject: SQL: Don't count users with ign_votes in bayesian rating
They still have influence on the average number of votes per VN and the
overall average vote, but this isn't significant. (at least, not at the
moment)
---
lib/Multi/Maintenance.pm | 4 ++--
util/updates/update_2.9.sql | 4 ++--
2 files changed, 4 insertions(+), 4 deletions(-)
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 422b5970..836f24ee 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -124,9 +124,9 @@ sub vnrating {
c_rating = (SELECT (
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
- ) FROM votes WHERE vid = id
+ ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
),
- c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0)
+ c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0)
|, undef, 'log_stats', 'vnrating');
}
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 0720681e..cbf6af63 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -50,7 +50,7 @@ UPDATE vn SET
c_rating = (SELECT (
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
- ) FROM votes WHERE vid = id
+ ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
),
- c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id), 0);
+ c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0);
--
cgit v1.2.3
From 7791d0236a9bcd85ea3542360b0c68a8492a909e Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sat, 14 Nov 2009 19:04:44 +0100
Subject: SQL: Allow NULL for vn.c_popularity
Sorting from least to most popular VN make sense now, you won't have to
wade through those entries without any vote at all.
---
ChangeLog | 1 +
lib/VNDB/Handler/VNBrowse.pm | 4 ++--
lib/VNDB/Util/CommonHTML.pm | 2 +-
util/dump.sql | 4 ++--
util/updates/update_2.9.sql | 20 ++++++++++++++++++++
5 files changed, 26 insertions(+), 5 deletions(-)
diff --git a/ChangeLog b/ChangeLog
index 92cfa0e8..bf3620af 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -10,6 +10,7 @@ git - ?
- Made age ratings and external VN link titles translatable
- Added wikipedia link for producers
- Added bayesian rating for VNs
+ - Improved popularity sorting on VN list
2.8 - 2009-10-24
- Converted relation graphs to use inline SVG
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index 3aaa1e25..dbe80ac1 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -123,9 +123,9 @@ sub list {
td class => 'tc4';
lit $self->{l10n}->datestr($l->{c_released});
end;
- td class => 'tc5', sprintf '%.2f', $l->{c_popularity}*100;
+ td class => 'tc5', sprintf '%.2f', ($l->{c_popularity}||0)*100;
td class => 'tc6';
- txt sprintf '%.2f', $l->{c_rating};
+ txt sprintf '%.2f', $l->{c_rating}||0;
b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount};
end;
end;
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index bb731a26..dc87d737 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -479,7 +479,7 @@ sub htmlVoteStats {
}
clearfloat;
- if($type eq 'v') {
+ if($type eq 'v' && $obj->{c_votecount}) {
div;
h3 mt '_votestats_rank_title';
p mt '_votestats_rank_pop', $obj->{p_ranking}, sprintf '%.2f', $obj->{c_popularity}*100;
diff --git a/util/dump.sql b/util/dump.sql
index 43562da3..a5451395 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -291,7 +291,7 @@ CREATE TABLE vn (
c_released integer NOT NULL DEFAULT 0,
c_languages varchar(32) NOT NULL DEFAULT '',
c_platforms varchar(32) NOT NULL DEFAULT '',
- c_popularity real NOT NULL DEFAULT 0,
+ c_popularity real,
c_rating real,
c_votecount integer NOT NULL DEFAULT 0
);
@@ -485,7 +485,7 @@ BEGIN
GROUP BY v.vid, v.uid;
CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
- UPDATE vn SET c_popularity = COALESCE((SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id), 0);
+ UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id);
RETURN;
END;
$$ LANGUAGE plpgsql;
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index cbf6af63..78a8d88a 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -54,3 +54,23 @@ UPDATE vn SET
),
c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0);
+
+-- vn.c_popularity can be NULL
+ALTER TABLE vn ALTER COLUMN c_popularity DROP NOT NULL;
+ALTER TABLE vn ALTER COLUMN c_popularity DROP DEFAULT;
+CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
+BEGIN
+ CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS
+ SELECT v.uid, v.vid, sqrt(count(*))::real
+ FROM votes v
+ JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote
+ WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes)
+ GROUP BY v.vid, v.uid;
+ CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
+ SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
+ UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+SELECT update_vnpopularity();
+
--
cgit v1.2.3
From 0a1b1d255224f690b226fc74ad00ad2d30cf6d25 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 15 Nov 2009 09:06:20 +0100
Subject: SQL: Fixed bug in bayesian rating calculation
avg_rating should be the average rating of all VNs, not the global average
vote.
---
lib/Multi/Maintenance.pm | 2 +-
util/updates/update_2.9.sql | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 836f24ee..0ea7ef29 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -122,7 +122,7 @@ sub vnrating {
$_[KERNEL]->post(pg => do => q|
UPDATE vn SET
c_rating = (SELECT (
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) /
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
),
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index 78a8d88a..d11a5eed 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -48,7 +48,7 @@ ALTER TABLE vn ADD COLUMN c_rating real;
ALTER TABLE vn ADD COLUMN c_votecount integer NOT NULL DEFAULT 0;
UPDATE vn SET
c_rating = (SELECT (
- ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(vote)::real FROM votes) + SUM(vote)::real) /
+ ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) /
((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real)
) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
),
--
cgit v1.2.3
From 42d95bdc14a887c37ec1f849d0f8224736816f11 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 15 Nov 2009 10:06:05 +0100
Subject: d11: Misc. improvements and typo fixes
---
data/docs/11 | 41 ++++++++++++++++++-----------------------
1 file changed, 18 insertions(+), 23 deletions(-)
diff --git a/data/docs/11 b/data/docs/11
index 199935fd..e1e49881 100644
--- a/data/docs/11
+++ b/data/docs/11
@@ -4,8 +4,8 @@
:SUB:Introduction
This document describes the public API of VNDB and is intended to be read by
- programmers. The API allows programs and websites to access (parts of) the VNDB
- database without actually visiting a page on the website.
+ programmers. This API allows programs and websites to access (parts of) the
+ VNDB database without actually visiting the website.
@@ -14,16 +14,11 @@
Simple in implementation of both client and server. "Simple" here means that
it shouldn't take much code to write a secure and full implementation and that
- client applications don't require huge dependency trees just to use this API.
+ client applications shouldn't require huge dependency trees just to use this API.
Powerful: Not as powerful as raw SQL, but not as rigid as commonly used REST or RPC protocols.
- Fast: minimal bandwidth overhead
-
- High-level: nobody is interested in the internal database structure of VNDB
- (ok, maybe you are, but you wouldn't want to write an application using it: it
- changes quite often)
-
- Stateful
+ High-level: common applications need to perform only few actions to get what they want.
+ Fast: minimal bandwidth overhead and simple and customizable queries.
@@ -45,8 +40,7 @@
server resources and prevent abuse of this service.
- 5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.
- - 3 connections per user. The login command will reply with a 'sesslimit' error when reaching this limit.
- - Each command returns at most 10 results.
+ - 3 sessions per user. The login command will reply with a 'sesslimit' error when this limit is reached.
- 100 commands per 10 minutes per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.
-
1 second of SQL time per minute per user. SQL time is the total time taken to
@@ -55,6 +49,7 @@ server resources and prevent abuse of this service.
Server will reply with a 'throttled' error with type="sql" upon reaching
this limit.
+ - Each command returns at most 10 results.
@@ -82,9 +77,9 @@ server resources and prevent abuse of this service.
This service does not grant anyone the right to copy or republish the
information fetched using the API. VNDB does not have a clear copyright
- statement, and in general we're not very strict about other people copying our
- information, but this API is not intended to be used fetching and publishing
- half of our database. When in doubt, ask.
+ statement, and in general we're not very strict about other people copying
+ our information, but this API is not intended to be used for fetching and
+ publishing more than half of our database. When in doubt, ask.
Lastly, this API is not complete. If you have any specific features you'd
@@ -106,12 +101,12 @@ server resources and prevent abuse of this service.
returned, it may very well happen that this is later changed into an array.
Make sure your application doesn't completely crash in such an event. Also keep
in mind that for every command you send, the server can reply with an error, or
- can immediately close the connection (but this is be rare).
+ can immediately close the connection (but this should be rare).
Regularly check this page to see if anything has changed to the API, and
- update your client where necessary. I'll probably add a changelog to the bottom
- of this page to make this easier.
+ update your client when necessary. I'll probably add a changelog to the bottom
+ of this page in the future.
Use a JSON library for both encoding and decoding JSON data. While the format
@@ -130,7 +125,7 @@ server resources and prevent abuse of this service.
for the glory details.
The words object, array, value, string,
- number and int refer to the JSON data types. In addition the following
+ number and integer refer to the JSON data types. In addition the following
definitions are used in this document:
@@ -183,7 +178,7 @@ however still required.
Some commands accept a filter string as argument. This argument is formatted
similar to boolean expressions in most programming languages. A filter consists
- of one or more expressions, separated by the boolean operators "and" and
+ of one or more expressions, separated by the boolean operators "and" or
"or" (lowercase). Each filter expression can be surrounded by parentheses to
indicate precedence, the filter argument itself must be surrounded by parentheses.
@@ -205,7 +200,7 @@ however still required.
title ~ "osananajimi"
)
-
More complex things are also possible:
+More complex filters are also possible:
((platforms = ["win", "ps2"] or languages = "ja") and released > "2009-01-10")
@@ -238,10 +233,10 @@ however still required.
:SUB:The 'get' command
- This command is used to fetch data from the database. It accepts 3 arguments:
+ This command is used to fetch data from the database. It accepts 4 arguments:
the type of data to fetch (e.g. visual novels or producers), what part of that
data to fetch (e.g. only the VN titles, or the descriptions and relations as
- well), and lastly a filter expression.
+ well), a filter expression, and lastly some options.
get type flags filters options
--
cgit v1.2.3
From cd566ad416c19c81e7574491c78b81a7c609c5d2 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 15 Nov 2009 10:06:54 +0100
Subject: docs: Deleted d8 from the git repo
This page is pretty specific to the main VNDB, and each VNDB would probably
have it's own d8.
---
.gitignore | 1 +
data/docs/8 | 81 -------------------------------------------------------------
2 files changed, 1 insertion(+), 81 deletions(-)
delete mode 100644 data/docs/8
diff --git a/.gitignore b/.gitignore
index 13e62a79..de859043 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,5 +1,6 @@
/data/config.pl
/data/multi.pid
+/data/docs/8
/data/log/
/static/f/script.js
/static/s/*/style.css
diff --git a/data/docs/8 b/data/docs/8
deleted file mode 100644
index 701f5f70..00000000
--- a/data/docs/8
+++ /dev/null
@@ -1,81 +0,0 @@
-:TITLE:Development roadmap
-:INC:index
-
-
-:SUB:Some notes
-This TODO list is just a place for Yorhel to write down his plans and ideas
-for the upcoming versions of VNDB. Keep in mind that probably not everything
-written down here will actually see the light of day.
-
-The full source code of the site is available on a
-git repository, you can use it
-to track changes to the code or even to run your own version of VNDB.
-
-There is a beta version of VNDB available
-for testing. New features will be implemented there and tested before getting
-uploaded to the actual website. Everyone is free to play around and use that
-beta version as a sandbox. Keep in mind, however, that all changes you make
-on the beta are not permanent, and will be reverted whenever the beta is
-synchronised with a more recent version of the database.
-Feel free to comment about the TODO list and to suggest new features on the
-discussion board or on
-IRC.
-
-Last update: 2009-02-12
-
-
-:SUB:Next version (2.3)
-
- - Catalog numbers for releases (done)
- - Changing release statusses from VN page using AJAX (done)
- - Random VN quotes on footer of every page (done)
- - Platform icons on homepage (done)
- - Tagging system (...will take a while)
-
-
-
-:SUB:(Hopefully) soon
-
- - Advanced notification system
- - More VNList filters & stats
- - Release calendar
-
-
-
-:SUB:Later
-
- - Filters on /v/* to exclude blacklisted and/or VNs on a users list from the results
- - Producer relations
- - VN Staff and Character database
- Probably not going to make it into VNDB, unless someone wants to be in charge of this
- - Soundtrack listing
- Work together with VGMdb.
- - Manga relations
- Looking for a good and comprehensive manga database fetch info from
- - Reviews
- - Walkthroughs/guides?
- Let each user maintain his own walkthrough, or just one collaboratively edited walkthough per VN?
- - Image quality/resolution field to releases
- - Some kind of minimal API for the hardcore VNDB fans (most likely using the JSON-RPC 1.0 Specification)
- - Automatic uploading of cover images from a URL
- - Scans of the packaging for releases
- - Store which fields have been changed for revisions using one-character-code indicators
- - Improved platform for fan translations (separated from releases)
- - 'currently down/offline' flag for official website links
-
-
-
-:SUB:Technical things that need improvements
-
- (I'm not expecting anyone to understand this, just random notes for myself)
-
-
- - Set PostgreSQL charset to UTF-8
- - Make use of PostgreSQL's fulltext search functionality to power the VN search
- - Proper implementation of the 'hidden' flag for v/r/p entries
- - Let VNDB and Multi communicate via SQL (and use schemas to separate the tables)
- - More secure login system
- - Use PostgreSQL stored procedures to insert revisions (using composite types, arrays, etc as arguments)
- - Update the users.c_changes column on hiding/unhiding an item
-
-
--
cgit v1.2.3
From 7f1b892cb46c8f713a3bb7c5ea94de2f7aa42508 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 15 Nov 2009 10:46:54 +0100
Subject: Auth: Combined dbSessionCheck into dbUserGet
This one query is a bit faster than the two queries executed seperately, and
with a query that is executed on each pageview it does matter.
Ideally, the dbUserMessageCount() is cached and fetched with the same query,
this should save another 1-2ms. But this is probably not worth the extra code
it would require.
---
lib/VNDB/DB/Users.pm | 25 +++++++++++--------------
lib/VNDB/Util/Auth.pm | 4 ++--
2 files changed, 13 insertions(+), 16 deletions(-)
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 593c6415..39429a02 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -5,10 +5,10 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel dbSessionCheck|;
+our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|;
-# %options->{ username passwd mail order uid ip registered search results page what }
+# %options->{ username passwd mail session order uid ip registered search results page what }
# what: stats extended
sub dbUserGet {
my $s = shift;
@@ -40,6 +40,8 @@ sub dbUserGet {
'registered > to_timestamp(?)' => $o{registered} ) : (),
$o{search} ? (
'username ILIKE ?' => "%$o{search}%") : (),
+ $o{session} ? (
+ q|s.token = decode(?, 'hex')| => $o{session} ) : (),
);
my @select = (
@@ -59,12 +61,17 @@ sub dbUserGet {
) : (),
);
+ my @join = (
+ $o{session} ? 'JOIN sessions s ON s.uid = u.id' : (),
+ );
+
my($r, $np) = $s->dbPage(\%o, q|
SELECT !s
FROM users u
+ !s
!W
ORDER BY !s|,
- join(', ', @select), \%where, $o{order}
+ join(', ', @select), join(' ', @join), \%where, $o{order}
);
return wantarray ? ($r, $np) : $r;
}
@@ -147,15 +154,5 @@ sub dbSessionDel {
}
-# Queries the database for the validity of a session
-# Returns 1 if corresponding session found, 0 if not
-# uid, token
-sub dbSessionCheck {
- my($s, @o) = @_;
- return $s->dbRow(
- q|SELECT count(uid) AS count FROM sessions WHERE uid = ? AND token = decode(?, 'hex') LIMIT 1|, @o
- )->{count}||0;
-}
-
-
1;
+
diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm
index a3bf7c29..cef9b905 100644
--- a/lib/VNDB/Util/Auth.pm
+++ b/lib/VNDB/Util/Auth.pm
@@ -25,8 +25,8 @@ sub authInit {
return _rmcookie($self) if length($cookie) < 41;
my $token = substr($cookie, 0, 40);
my $uid = substr($cookie, 40);
- return _rmcookie($self) if $uid !~ /^\d+$/ || !$self->dbSessionCheck($uid, $token);
- $self->{_auth} = $self->dbUserGet(uid => $uid, what => 'extended')->[0];
+ $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended')->[0];
+ return _rmcookie($self) if !$self->{_auth};
}
--
cgit v1.2.3
From 06832f907632574ae41bf96e27e028b224078d39 Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 15 Nov 2009 11:09:34 +0100
Subject: SQL: Improved performance of update_vnpopularity()
The previous statement was optimized for PostgreSQL 8.3 and took only about a
second, but after the update to 8.4 it took about 10 times longer due to a
different execution plan being generated. This slightly reworded statement
generates a more efficient plan on 8.4.
---
util/dump.sql | 2 +-
util/updates/update_2.9.sql | 2 +-
2 files changed, 2 insertions(+), 2 deletions(-)
diff --git a/util/dump.sql b/util/dump.sql
index a5451395..e0af79e8 100644
--- a/util/dump.sql
+++ b/util/dump.sql
@@ -481,7 +481,7 @@ BEGIN
SELECT v.uid, v.vid, sqrt(count(*))::real
FROM votes v
JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote
- WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes)
+ JOIN users u ON u.id = v.uid AND NOT ign_votes
GROUP BY v.vid, v.uid;
CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
index d11a5eed..3b3e147d 100644
--- a/util/updates/update_2.9.sql
+++ b/util/updates/update_2.9.sql
@@ -64,7 +64,7 @@ BEGIN
SELECT v.uid, v.vid, sqrt(count(*))::real
FROM votes v
JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote
- WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes)
+ JOIN users u ON u.id = v.uid AND NOT ign_votes
GROUP BY v.vid, v.uid;
CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
--
cgit v1.2.3
From 2879734d7f5dd805181319dc191a9ee8013497ae Mon Sep 17 00:00:00 2001
From: Yorhel
Date: Sun, 15 Nov 2009 11:23:45 +0100
Subject: TransAdmin: CSS fix for doc page editor
---
lib/VNDB/Plugin/TransAdmin.pm | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm
index f9cf8aed..0d510aa9 100644
--- a/lib/VNDB/Plugin/TransAdmin.pm
+++ b/lib/VNDB/Plugin/TransAdmin.pm
@@ -327,7 +327,7 @@ sub _doc {
}
div style => 'width: 48%; margin-right: 10px; overflow-y: auto; float: left';
- pre style => 'font: 12px Tahoma', $en;
+ pre style => 'font: 12px Tahoma; border: none; background: none; margin: 0', $en;
end;
textarea name => 'tl', id => 'tl', rows => ($en =~ y/\n//),
style => 'border: none; float: left; width: 49%; white-space: nowrap', wrap => 'off';
--
cgit v1.2.3
From 2ca2406969b4b60954618da394f7afa58343bc10 Mon Sep 17 00:00:00 2001
From: Nya-chan Production
Date: Mon, 16 Nov 2009 09:54:18 +0100
Subject: L10N-CS: Synchronised translation
---
data/docs/9.cs | 4 ++++
data/docs/index.cs | 1 +
data/lang.txt | 30 +++++++++++++++---------------
3 files changed, 20 insertions(+), 15 deletions(-)
diff --git a/data/docs/9.cs b/data/docs/9.cs
index f5ceef38..068341ca 100644
--- a/data/docs/9.cs
+++ b/data/docs/9.cs
@@ -63,6 +63,10 @@
[raw]
Předveďte své dovednosti ve formátovacím kódu umístěním čehokoliv, co nechcete aby bylo naformátováno do [raw]
tagu. Jakýkoliv formátovací kód do teď zmíněný bude v [raw] .. [/raw] bloku ignorován.
+ [code]
+ Podobá se tagu [raw], až na to, že text v [code] .. [/code] bloku je formátován
+ do fontu s pevnou šířkou a ohraničen pěkným rámečkem pro oddělení od zbytku
+ vašeho příspěvku.
diff --git a/data/docs/index.cs b/data/docs/index.cs
index 08c8b500..650e2aa6 100644
--- a/data/docs/index.cs
+++ b/data/docs/index.cs
@@ -9,6 +9,7 @@
Diskusní boardy
FAQ
O nás
+ Databáze API
Vývoj
diff --git a/data/lang.txt b/data/lang.txt
index 36a2bd40..f4b356ac 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -1002,20 +1002,20 @@ hu : Lemondva
:_minage_null
en : Unknown
ru*:
-cs*:
+cs : Není známo
hu*:
:_minage_all
en : All ages
ru*:
-cs*:
+cs : Pro všechny věky
hu*:
# "Ages [_1] and up", but shorter
:_minage_age
en : [_1]+
ru*:
-cs*:
+cs : [_1]+
hu*:
# [_1] is an example of an age rating, like 'CERO A', 'CERO D', etc.
@@ -1023,7 +1023,7 @@ hu*:
:_minage_example
en : (e.g. [_1])
ru*:
-cs*:
+cs : (např. [_1])
hu*:
@@ -2179,13 +2179,13 @@ hu :
:_prodpage_homepage
en : Homepage
ru*:
-cs*:
+cs : Domovská stránka
hu*:
:_prodpage_wikipedia
en : Wikipedia
ru*:
-cs*:
+cs : Wikipedie
hu*:
:_prodpage_vnrel
@@ -2254,7 +2254,7 @@ hu : Weboldal
:_revfield_p_l_wp
en : Wikipedia link
ru*:
-cs*:
+cs : Odkaz na Wikipedii
hu*:
:_revfield_p_desc
@@ -2356,7 +2356,7 @@ hu : Weboldal
:_pedit_form_wikipedia
en : Wikipedia link
ru*:
-cs*:
+cs : Odkaz na Wikipedii
hu*:
:_pedit_form_desc
@@ -4630,7 +4630,7 @@ hu : Népszerűség
:_vnbrowse_col_rating
en : Rating
ru*:
-cs*:
+cs : Hodnocení
hu*:
:_vnbrowse_tagign_title
@@ -5354,19 +5354,19 @@ hu : Linkek
:_vnpage_l_wp
en : Wikipedia
ru*:
-cs*:
+cs : Wikipedie
hu*:
:_vnpage_l_encubed
en : Encubed
ru*:
-cs*:
+cs : Encubed
hu*:
:_vnpage_l_renai
en : Renai.us
ru*:
-cs*:
+cs : Renai.us
hu*:
:_vnpage_description
@@ -5626,19 +5626,19 @@ hu : Legútóbbi szavazatok
:_votestats_rank_title
en : Ranking
ru*:
-cs*:
+cs : Hodnocení
hu*:
:_votestats_rank_pop
en : Popularity: ranked #[_1] with a score of [_2]
ru*:
-cs*:
+cs : Popularita: [_1]. místo se skóre [_2]
hu*:
:_votestats_rank_rat
en : Bayesian rating: ranked #[_1] with a rating of [_2]
ru*:
-cs*:
+cs : Bayesovo hodnocení: [_1]. místo se skóre [_2]
hu*:
--
cgit v1.2.3
From eb61189b339011e09ef05799d9a5696ad0cae59f Mon Sep 17 00:00:00 2001
From: Dmitri Poguliayev
Date: Mon, 16 Nov 2009 09:55:43 +0100
Subject: L10N-RU: Synchronised translation
---
data/docs/9.ru | 4 ++++
data/docs/index.ru | 3 ++-
data/lang.txt | 30 +++++++++++++++---------------
3 files changed, 21 insertions(+), 16 deletions(-)
diff --git a/data/docs/9.ru b/data/docs/9.ru
index fde47f1f..34aad612 100644
--- a/data/docs/9.ru
+++ b/data/docs/9.ru
@@ -72,6 +72,10 @@
Покажите ваши уникальные способности по форматированию кода, располагая
всё, что не должно быть отформатировано, внутри блока [raw] .. [/raw]. Любые
вышеперечисленные коды будут проигнорированы.
+ [code]
+ Действие этого тега похоже на [raw], с той лишь разницей, что для текста
+ в блоке [code]...[/code] будет использован моноширинный шрифт и код
+ будет помещен в красивую рамочку, чтобы отделить его от остального текста.
diff --git a/data/docs/index.ru b/data/docs/index.ru
index 28e8620a..c2668c40 100644
--- a/data/docs/index.ru
+++ b/data/docs/index.ru
@@ -9,5 +9,6 @@
Форум
ЧаВо
О нас
+ API базы данных
Разработка
-
\ No newline at end of file
+
diff --git a/data/lang.txt b/data/lang.txt
index f4b356ac..0439ad93 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -1001,20 +1001,20 @@ hu : Lemondva
:_minage_null
en : Unknown
-ru*:
+ru : Неизвестно
cs : Není známo
hu*:
:_minage_all
en : All ages
-ru*:
+ru : Все возраста
cs : Pro všechny věky
hu*:
# "Ages [_1] and up", but shorter
:_minage_age
en : [_1]+
-ru*:
+ru : [_1]+
cs : [_1]+
hu*:
@@ -1022,7 +1022,7 @@ hu*:
# this string is appended to the other _minage_* strings
:_minage_example
en : (e.g. [_1])
-ru*:
+ru : (т.е. [_1])
cs : (např. [_1])
hu*:
@@ -2178,13 +2178,13 @@ hu :
:_prodpage_homepage
en : Homepage
-ru*:
+ru : Домашняя страничка
cs : Domovská stránka
hu*:
:_prodpage_wikipedia
en : Wikipedia
-ru*:
+ru : Википедия
cs : Wikipedie
hu*:
@@ -2253,7 +2253,7 @@ hu : Weboldal
:_revfield_p_l_wp
en : Wikipedia link
-ru*:
+ru : Ссылка на Википедию
cs : Odkaz na Wikipedii
hu*:
@@ -2355,7 +2355,7 @@ hu : Weboldal
:_pedit_form_wikipedia
en : Wikipedia link
-ru*:
+ru : Ссылка на Википедию
cs : Odkaz na Wikipedii
hu*:
@@ -4629,7 +4629,7 @@ hu : Népszerűség
:_vnbrowse_col_rating
en : Rating
-ru*:
+ru : Рейтинг
cs : Hodnocení
hu*:
@@ -5353,19 +5353,19 @@ hu : Linkek
:_vnpage_l_wp
en : Wikipedia
-ru*:
+ru : Википедия
cs : Wikipedie
hu*:
:_vnpage_l_encubed
en : Encubed
-ru*:
+ru : Encubed
cs : Encubed
hu*:
:_vnpage_l_renai
en : Renai.us
-ru*:
+ru : Renai.us
cs : Renai.us
hu*:
@@ -5625,19 +5625,19 @@ hu : Legútóbbi szavazatok
:_votestats_rank_title
en : Ranking
-ru*:
+ru : Место
cs : Hodnocení
hu*:
:_votestats_rank_pop
en : Popularity: ranked #[_1] with a score of [_2]
-ru*:
+ru : Популярность: #[_1] место с количеством баллов, равным [_2]
cs : Popularita: [_1]. místo se skóre [_2]
hu*:
:_votestats_rank_rat
en : Bayesian rating: ranked #[_1] with a rating of [_2]
-ru*:
+ru : Рейтинг методом Байезия: #[_1] место с рейтингом [_2]
cs : Bayesovo hodnocení: [_1]. místo se skóre [_2]
hu*:
--
cgit v1.2.3
From a5e88fd33c3ec018e89be0b5368a85119323554c Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Bikfalvi=20M=C3=A1t=C3=A9?=
Date: Mon, 16 Nov 2009 09:57:37 +0100
Subject: L10N-HU: Synchronised translation + misc. improvements
---
data/lang.txt | 66 +++++++++++++++++++++++++++++------------------------------
1 file changed, 33 insertions(+), 33 deletions(-)
diff --git a/data/lang.txt b/data/lang.txt
index 0439ad93..78fcfaa1 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -517,7 +517,7 @@ hu : Folytatás
en : Prequel
ru : Предыстория
cs : Prequel
-hu : Előzmény
+hu : Előd
:_vnrel_set
en : Same setting
@@ -547,7 +547,7 @@ hu : Mellék történet
en : Parent story
ru : Исходный сюжет
cs : Mateřský příběh
-hu : Szüllő történet
+hu : Fő történet
:_vnrel_ser
en : Same series
@@ -613,7 +613,7 @@ hu : Alválalat
en : Parent producer
ru : Исходная компания
cs : Mateřský producent
-hu : Szüllő készítő
+hu : Fő készítő
:_prodrel_imp
en : Imprint
@@ -712,7 +712,7 @@ hu : Videoklip
en : Announcements
ru : Объявления
cs : Oznámení
-hu : Hirdetések
+hu : Hírek
:_dboard_db
en : VNDB Discussions
@@ -1003,20 +1003,20 @@ hu : Lemondva
en : Unknown
ru : Неизвестно
cs : Není známo
-hu*:
+hu : Ismeretlen
:_minage_all
en : All ages
ru : Все возраста
cs : Pro všechny věky
-hu*:
+hu : Nincs korhatár
# "Ages [_1] and up", but shorter
:_minage_age
en : [_1]+
ru : [_1]+
cs : [_1]+
-hu*:
+hu : [_1]+
# [_1] is an example of an age rating, like 'CERO A', 'CERO D', etc.
# this string is appended to the other _minage_* strings
@@ -1024,7 +1024,7 @@ hu*:
en : (e.g. [_1])
ru : (т.е. [_1])
cs : (např. [_1])
-hu*:
+hu : (p.l. [_1])
# Form messages
@@ -2180,13 +2180,13 @@ hu :
en : Homepage
ru : Домашняя страничка
cs : Domovská stránka
-hu*:
+hu : Weboldal
:_prodpage_wikipedia
en : Wikipedia
ru : Википедия
cs : Wikipedie
-hu*:
+hu :
:_prodpage_vnrel
en : Visual Novel Relations
@@ -2210,7 +2210,7 @@ hu : fejlesztő
en : publisher
ru : издатель
cs : vydavatel
-hu : kiadó
+hu : forgalmazó
# producer diff fields
@@ -2255,7 +2255,7 @@ hu : Weboldal
en : Wikipedia link
ru : Ссылка на Википедию
cs : Odkaz na Wikipedii
-hu*:
+hu :
:_revfield_p_desc
en : Description
@@ -2282,7 +2282,7 @@ hu : semmi
en : Relation graph for [_1]
ru : Схема отношений для [_1]
cs : Graf vztahů pro producenta [_1]
-hu : [_1] összefüggés gráfja
+hu : [_1] összefüggés grafikonja
# Add/Edit producer
@@ -2357,7 +2357,7 @@ hu : Weboldal
en : Wikipedia link
ru : Ссылка на Википедию
cs : Odkaz na Wikipedii
-hu*:
+hu :
:_pedit_form_desc
en : Description
@@ -2593,7 +2593,7 @@ hu : fejlesztő
en : publisher
ru : издатель
cs : vydavatel
-hu : kiadó
+hu : forgalmazó
# Information table (on every release page)
@@ -2716,7 +2716,7 @@ hu : [quant,_1,Fejlesztő,Fejlesztők]
en : [quant,_1,Publisher,Publishers]
ru : [quant,_1,Издатель,Издатели,Издатели]
cs : [quant,_1,Vydavatel,Vydavatelé,Vydavatelů]
-hu : [quant,_1,Kiadó,Kiadók]
+hu : [quant,_1,Forgalmazó,Forgalmazók]
:_relinfo_catalog
en : Catalog no.
@@ -2728,7 +2728,7 @@ hu : Katalógus szám
en : Links
ru : Ссылки
cs : Odkazy
-hu : Linkek
+hu : Hivatkozások
:_relinfo_website
en : Official website
@@ -2999,7 +2999,7 @@ hu : Fejlesztő
en : Publisher
ru : Издатель
cs : Vydavatel
-hu : Kiadó
+hu : Forgalmazó
:_redit_form_prod_both
en : Both
@@ -4631,7 +4631,7 @@ hu : Népszerűség
en : Rating
ru : Рейтинг
cs : Hodnocení
-hu*:
+hu : Értékelés
:_vnbrowse_tagign_title
en : The following tags were ignored:
@@ -4814,13 +4814,13 @@ hu : Hossz
en : External links
ru : Внешние ссылки
cs : Externí odkazy
-hu : Külső linkek
+hu : Külső hivatkozások
:_vnedit_anime
en : Anime
ru : Аниме
cs : Anime
-hu :
+hu : Kapcsolódó anime
:_vnedit_anime_msg
en : Whitespace separated list of [url,http://anidb.net/,AniDB] anime IDs.
@@ -4918,13 +4918,13 @@ hu : Összefüggés hozzáadása
en : is a
ru : это
cs : je
-hu*: -
+hu : -
:_vnedit_rel_of
en : of
ru : для
cs : titulu
-hu*: -
+hu : -
:_vnedit_rel_addbut
en : add
@@ -5151,7 +5151,7 @@ hu : Figyelem: ha ez tovább tart mint 30 másodperc, akkor a mivelünk van a go
en : Relation graph for [_1]
ru : Схема связей для [_1]
cs : Graf vztahů pro vizuální novelu [_1]
-hu : Összefüggés gráf [_1]-hoz
+hu : Összefüggés grafikon [_1]-hoz
# VN Diff viewer (/v+.+)
@@ -5190,7 +5190,7 @@ hu : Hossz
en : ~[no link~]
ru : ~[нет ссылки~]
cs : ~[žádný odkaz~]
-hu : ~[nincs link~]
+hu : ~[nincs hivatkozás~]
:_vndiff_none
en : ~[none~]
@@ -5355,19 +5355,19 @@ hu : Linkek
en : Wikipedia
ru : Википедия
cs : Wikipedie
-hu*:
+hu :
:_vnpage_l_encubed
en : Encubed
ru : Encubed
cs : Encubed
-hu*:
+hu :
:_vnpage_l_renai
en : Renai.us
ru : Renai.us
cs : Renai.us
-hu*:
+hu :
:_vnpage_description
en : Description
@@ -5415,7 +5415,7 @@ hu : Fejlesztő
en : Publishers
ru : Издатели
cs : Vydavatel
-hu : Kiadó
+hu : Forgalmazók
:_vnpage_relations
en : Relations
@@ -5627,19 +5627,19 @@ hu : Legútóbbi szavazatok
en : Ranking
ru : Место
cs : Hodnocení
-hu*:
+hu : Ranglista
:_votestats_rank_pop
en : Popularity: ranked #[_1] with a score of [_2]
ru : Популярность: #[_1] место с количеством баллов, равным [_2]
cs : Popularita: [_1]. místo se skóre [_2]
-hu*:
+hu : Népszerűség: [_1]. hely [_2] pontszámmal
:_votestats_rank_rat
en : Bayesian rating: ranked #[_1] with a rating of [_2]
ru : Рейтинг методом Байезия: #[_1] место с рейтингом [_2]
cs : Bayesovo hodnocení: [_1]. místo se skóre [_2]
-hu*:
+hu : Bayes-i ranglista: [_1]. hely [_2]-s értékeléssel
@@ -5833,5 +5833,5 @@ hu : Nem támogatott
en : Your browser sucks, it doesn't have the functionality to render our nice relation graphs.
ru : Ваш браузер настолько отстоен, что даже не может отрендерить наши красивые графики.
cs : Váš prohlížeč je na nic, nepodporuje vykreslování našich krásných vztahových grafů.
-hu : A böngésződ szar, nem képes megjeleníteni a szép összefüggés gráfunkat.
+hu : A böngésződ szar, nem képes megjeleníteni a szép összefüggés grafikonunkat.
--
cgit v1.2.3