summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2009-11-16 10:00:16 +0100
committerYorhel <git@yorhel.nl>2009-11-16 10:01:02 +0100
commit445b96631ac06d2fb0c3b6486ccde2a189a58675 (patch)
tree2bdcdbf0912756af65b36e20c7b271b5e2386abf
parent207ecaadc6e746e05d44acc96adbc30e64a87cee (diff)
parenta5e88fd33c3ec018e89be0b5368a85119323554c (diff)
Merge branch 'beta'2.9
+ Changelog update
-rw-r--r--.gitignore2
-rw-r--r--ChangeLog14
-rw-r--r--Makefile102
-rw-r--r--README2
-rw-r--r--data/docs/11891
-rw-r--r--data/docs/881
-rw-r--r--data/docs/94
-rw-r--r--data/docs/9.cs4
-rw-r--r--data/docs/9.ru4
-rw-r--r--data/docs/index1
-rw-r--r--data/docs/index.cs1
-rw-r--r--data/docs/index.ru3
-rw-r--r--data/global.pl19
-rw-r--r--data/lang.txt140
-rw-r--r--data/style.css14
-rw-r--r--lib/Multi/API.pm913
-rw-r--r--lib/Multi/Core.pm63
-rw-r--r--lib/Multi/IRC.pm87
-rw-r--r--lib/Multi/Maintenance.pm18
-rw-r--r--lib/POE/Filter/VNDBAPI.pm312
-rw-r--r--lib/VNDB/DB/Producers.pm6
-rw-r--r--lib/VNDB/DB/Releases.pm2
-rw-r--r--lib/VNDB/DB/ULists.pm2
-rw-r--r--lib/VNDB/DB/Users.pm25
-rw-r--r--lib/VNDB/DB/VN.pm12
-rw-r--r--lib/VNDB/Func.pm40
-rw-r--r--lib/VNDB/Handler/Misc.pm7
-rw-r--r--lib/VNDB/Handler/Producers.pm19
-rw-r--r--lib/VNDB/Handler/Releases.pm25
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm20
-rw-r--r--lib/VNDB/Handler/VNPage.pm12
-rw-r--r--lib/VNDB/Plugin/TransAdmin.pm8
-rw-r--r--lib/VNDB/Util/Auth.pm4
-rw-r--r--lib/VNDB/Util/CommonHTML.pm7
-rw-r--r--util/dump.sql15
-rwxr-xr-xutil/init.pl70
-rw-r--r--util/updates/update_2.9.sql76
37 files changed, 2719 insertions, 306 deletions
diff --git a/.gitignore b/.gitignore
index e2a730fd..de859043 100644
--- a/.gitignore
+++ b/.gitignore
@@ -1,4 +1,6 @@
/data/config.pl
+/data/multi.pid
+/data/docs/8
/data/log/
/static/f/script.js
/static/s/*/style.css
diff --git a/ChangeLog b/ChangeLog
index 299ade41..43f03286 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,17 @@
+2.9 - 2009-11-16
+ - 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
+ - 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 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
- Relation graphs now use the color scheme of selected skin
diff --git a/Makefile b/Makefile
new file mode 100644
index 00000000..df1d05aa
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,102 @@
+# 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
+#
+# 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 multi-start multi-stop multi-restart
+
+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/*\.*
+
+
+# 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)
+
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..e1e49881
--- /dev/null
+++ b/data/docs/11
@@ -0,0 +1,891 @@
+:TITLE:Public Database API
+:INC:index
+
+:SUB:Introduction
+<p>
+ This document describes the public API of VNDB and is intended to be read by
+ programmers. This API allows programs and websites to access (parts of) the
+ VNDB database without actually visiting the website.
+ <br /><br />
+</p>
+
+<b>Design goals</b>
+<ul>
+ <li>
+ 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 shouldn't require huge dependency trees just to use this API.
+ </li>
+ <li>Powerful: Not as powerful as raw SQL, but not as rigid as commonly used REST or RPC protocols.</li>
+ <li>High-level: common applications need to perform only few actions to get what they want.</li>
+ <li>Fast: minimal bandwidth overhead and simple and customizable queries.</li>
+</ul>
+<br />
+
+<b>Design overview</b>
+<ul>
+ <li>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.</li>
+ <li>Request/response, client sends a request and server replies with a response.</li>
+ <li>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.</li>
+ <li><b>Everything</b> sent between the client and the server is encoded in UTF-8.</li>
+</ul>
+<br />
+
+<b>Limits</b>
+<p>The following limits are enforced by the server, in order to limit the
+server resources and prevent abuse of this service.</p>
+<ul>
+ <li>5 connections per IP. All connections that are opened after reaching this limit will be immediately closed.</li>
+ <li>3 sessions per user. The login command will reply with a 'sesslimit' error when this limit is reached.</li>
+ <li>100 commands per 10 minutes per user. Server will reply with a 'throttled' error (type="cmd") when reaching this limit.</li>
+ <li>
+ 1 second of SQL time per minute per user. SQL time is the total time taken to
+ 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.
+ </li>
+ <li>Each command returns at most 10 results.</li>
+</ul>
+
+<br />
+<b>Connection info:</b>
+<dl>
+ <dt>Host</dt><dd>api.vndb.org (primary), beta.vndb.org (available for testing)</dd>
+ <dt>Port (tcp)</dt><dd>19534 ('VN')</dd>
+</dl>
+
+<br />
+<b>Disclaimer</b>
+<p>These notes should be obvious, but I'll point it out anyway.</p>
+<ul>
+ <li>
+ 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.
+ </li>
+ <li>
+ 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.
+ </li>
+ <li>
+ 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 for fetching and
+ publishing more than half of our database. When in doubt, ask.
+ </li>
+ <li>
+ Lastly, this API is not complete. If you have any specific features you'd
+ like to see, don't hesitate to ask.
+ </li>
+</ul>
+
+<br />
+<b>Implementation tips</b>
+<p>Like the disclaimer, these notes should be obvious, but it can't hurt to state them anyway.</p>
+<ul>
+ <li>
+ <b>Cache</b> 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.
+ </li>
+ <li>
+ 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 should be rare).
+ </li>
+ <li>
+ Regularly check this page to see if anything has changed to the API, and
+ update your client when necessary. I'll probably add a changelog to the bottom
+ of this page in the future.
+ </li>
+ <li>
+ 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.
+ </li>
+</ul>
+
+
+
+:SUB:Request/response syntax
+<p>
+ The VNDB API uses the JSON format for data in various places, this document assumes
+ you are familiar with it. See <a href="http://json.org/">JSON.org</a> for a quick
+ overview and <a href="http://www.ietf.org/rfc/rfc4627.txt?number=4627">RFC 4627</a>
+ for the glory details.
+ <br /><br />
+ The words <i>object</i>, <i>array</i>, <i>value</i>, <i>string</i>,
+ <i>number</i> and <i>integer</i> refer to the JSON data types. In addition the following
+ definitions are used in this document:
+</p>
+<dl>
+ <dt><i>request</i> or <i>command</i></dt><dd>
+ Message sent from the client to the server.
+ </dd><dt><i>response</i></dt><dd>
+ Message sent from the server to the client.
+ </dd><dt><i>whitespace</i></dt><dd>
+ 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.
+ </dd><dt><i>date</i></dt><dd>
+ A <i>string</i> 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 <b>null</b> is used.
+ </dd>
+</dl>
+<br />
+
+<b>Message format</b>
+<p>
+ 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.<br />
+ 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.<br />
+ 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.
+</p>
+<pre>
+ login {"protocol":1,"username":"ayo"}<b class="standout">0x04</b>
+</pre><pre>
+ login {
+ "protocol" : 1,
+ "username" : "ayo"
+ }
+ <b class="standout">0x04</b>
+</pre>
+The 0x04 byte will be ommitted in the other examples in this document. It is
+however still required.<br />
+
+<br />
+<b>Filter string syntax</b>
+<p>
+ 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 <i>expressions</i>, 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.
+ <br />
+ An <i>expression</i> consists of a <i>field name</i>, followed by an
+ <i>operator</i> and a <i>value</i>. 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: =, !=, &lt;, &lt;=, &gt;,
+ &gt;= or ~. The <i>value</i> can be any valid JSON value. Whitespace
+ characters are allowed, but not required, between all expressions, field names,
+ operators and values.<br />
+ The following two filters are equivalent:
+</p>
+<pre>
+ (title~"osananajimi"or(id=2))
+</pre><pre>
+ (
+ id = 2
+ or
+ title ~ "osananajimi"
+ )
+</pre>
+<p>More complex filters are also possible:</p>
+<pre>
+ ((platforms = ["win", "ps2"] or languages = "ja") and released > "2009-01-10")
+</pre>
+<p>See the individual commands for more details.</p>
+
+
+:SUB:The 'login' command
+<pre>
+ login {"protocol":1,"client":"test","clientver":0.1,"username":"ayo","password":"hi-mi-tsu!"}
+</pre>
+<p>
+ 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:
+</p>
+<dl>
+ <dt>protocol</dt><dd>An integer that indicates which protocol version the client implements. Must be 1.</dd>
+ <dt>client</dt><dd>
+ 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.
+ </dd><dt>clientver</dt><dd>A positive number indicating the software version of the client.</dd>
+ <dt>username</dt><dd>String containing the username of the person using the client.</dd>
+ <dt>password</dt><dd>String, password of that user in plain text.</dd>
+</dl>
+<p>
+ The server replies with either 'ok' (no arguments), or 'error' (see below).
+</p>
+
+
+:SUB:The 'get' command
+<p>
+ 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), a filter expression, and lastly some options.
+</p>
+<pre>
+ get <b class="standout">type flags filters options</b>
+</pre>
+<p>
+ <i>type</i> and <i>flags</i> are unescaped strings. The accepted values for <i>type</i> are
+ documented below. <i>flags</i> is a comma-separated list of flags indicating what
+ info to fetch. The filters, available flags and their meaning are documented
+ separately for each type. The last <i>options</i> argument is optional, and
+ influences the behaviour of the returned results. When present, <i>options</i>
+ should be a JSON object with the following members (all are optional):
+</p>
+<dl>
+ <dt>page</dt><dd>
+ integer, used for pagination. Page 1 (the default) returns the first 10
+ results (1-10), page 2 returns the following 10 (11-20), etc.
+ </dd><dt>sort</dt><dd>
+ string, the field to order the results by. The accepted field names differ
+ per type, the default sort field is the ID of the database entry.
+ </dd>
+ <dt>reverse</dt><dd>boolean, default false. Set to true to reverse the order of the results.</dd>
+</dl>
+<p>
+ The following example will fetch basic information and information about the
+ related anime of the visual novel with id = 17:
+</p>
+<pre>
+ get vn basic,anime (id = 17)
+</pre>
+<p>
+ The server will reply with a 'results' message, this message is followed by a
+ JSON object describing the results. This object has three members: 'num', which
+ is an integer indicating the number of results returned, 'more', which is true
+ when there are more results available (i.e. increasing the <i>page</i> option
+ described above will give new results) and 'items', which contains the results
+ as an array of objects. For example, the server could reply to the previous
+ command with the following message:
+</p>
+<pre>
+ results {"num":1, "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": []
+ }]}
+</pre>
+<p>
+ 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.
+</p>
+
+:SUBSUB:get vn
+<p>The following members are returned from a 'get vn' command:</p>
+<table style="margin: 5px 2%; width: 95%">
+ <thead><tr>
+ <td style="width: 80px">Member</td>
+ <td style="width: 50px">Flag</td>
+ <td style="width: 90px">Type</td>
+ <td style="width: 40px">null</td>
+ <td>Description</td>
+ </tr></thead>
+ <tr class="odd">
+ <td>id</td>
+ <td>-</td>
+ <td>integer</td>
+ <td>no</td>
+ <td>Visual novel ID</td>
+ </tr>
+ <tr>
+ <td>title</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>no</td>
+ <td>Main title</td>
+ </tr>
+ <tr class="odd">
+ <td>original</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Original/official title.</td>
+ </tr>
+ <tr>
+ <td>released</td>
+ <td>basic</td>
+ <td>date (string)</td>
+ <td>yes</td>
+ <td>Date of the first release.</td>
+ </tr>
+ <tr class="odd">
+ <td>languages</td>
+ <td>basic</td>
+ <td>array of strings</td>
+ <td>no</td>
+ <td>Can be an empty array when nothing has been released yet.</td>
+ </tr>
+ <tr>
+ <td>platforms</td>
+ <td>basic</td>
+ <td>array of strings</td>
+ <td>no</td>
+ <td>Can be an empty array when unknown or nothing has been released yet.</td>
+ </tr>
+ <tr class="odd">
+ <td>aliases</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Comma-separated list of aliases.</td>
+ </tr>
+ <tr>
+ <td>length</td>
+ <td>details</td>
+ <td>integer</td>
+ <td>yes</td>
+ <td>Length of the game, 1-5</td>
+ </tr>
+ <tr class="odd">
+ <td>description</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Description of the VN. Can include formatting codes as described in <a href="/d9.3">d9.3</a>.</td>
+ </tr>
+ <tr>
+ <td>links</td>
+ <td>details</td>
+ <td>object</td>
+ <td>no</td>
+ <td>
+ Contains the following members:<br />
+ "wikipedia", string, name of the related article on the English Wikipedia.<br />
+ "encubed", string, the URL-encoded tag used on <a href="http://novelnews.net/">encubed</a>.<br />
+ "renai", string, the name part of the url on <a href="http://renai.us/">renai.us</a>.<br />
+ All members can be <b>null</b> when no links are available or known to us.
+ </td>
+ </tr>
+ <tr class="odd">
+ <td>anime</td>
+ <td>anime</td>
+ <td>array of objects</td>
+ <td>no</td>
+ <td>
+ (Possibly empty) list of anime related to the VN, each object has the following members:<br />
+ "id", integer, <a href="http://anidb.net/">AniDB</a> ID<br />
+ "ann_id", integer, <a href="http://animenewsnetwork.com/">AnimeNewsNetwork</a> ID<br />
+ "nfo_id", string, <a href="http://animenfo.com/">AnimeNfo</a> ID<br />
+ "title_romaji", string<br />
+ "title_kanji", string<br />
+ "year", integer, year in which the anime was aired<br />
+ "type", string<br />
+ All members except the "id" can be <b>null</b>. Note that this data is courtesy of AniDB,
+ and may not reflect the latest state of their information due to caching.
+ </td>
+ </tr>
+ <tr>
+ <td>relations</td>
+ <td>relations</td>
+ <td>array of objects</td>
+ <td>no</td>
+ <td>
+ (Possibly empty) list of related visual novels, each object has the following members:<br />
+ "id", integer<br />
+ "relation", string, relation to the VN<br />
+ "title", string, (romaji) title<br />
+ "original", string, original/official title, can be <b>null</b>.
+ </td>
+ </tr>
+</table>
+<p>Sorting is possible on the 'id', 'title' and 'released' fields.</p><br />
+
+<p>'get vn' accepts the following filter expressions:</p>
+<table style="margin: 5px 2%; width: 95%">
+ <thead><tr>
+ <td style="width: 80px">Field</td>
+ <td style="width: 90px">Value</td>
+ <td style="width: 90px">Operators</td>
+ <td>&nbsp;</td>
+ </tr></thead>
+ <tr class="odd">
+ <td>id</td>
+ <td>integer<br />array of integers</td>
+ <td>= != &gt; &gt;= &lt; &lt;=<br />= !=</td>
+ <td>
+ 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]).
+ </td>
+ </tr>
+ <tr>
+ <td>title</td>
+ <td>string</td>
+ <td>= != ~</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>original</td>
+ <td>null<br />string</td>
+ <td>= !=<br />= != ~</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>released</td>
+ <td>null<br />date (string)</td>
+ <td>= !=<br />= != &gt; &gt;= &lt; &lt;=</td>
+ <td>
+ Note that matching on partial dates (released = "2009") doesn't do what
+ you want, use ranges instead, e.g. (released &gt; "2008" and released &lt;= "2009").
+ </td>
+ </tr>
+ <tr class="odd">
+ <td>platforms</td>
+ <td>null<br />string<br />array of strings</td>
+ <td><br />= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>languages</td>
+ <td>null<br />string<br />array of strings</td>
+ <td><br />= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>search</td>
+ <td>string</td>
+ <td>~</td>
+ <td>
+ 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.
+ </td>
+ </tr>
+</table>
+
+:SUBSUB:get release
+<p>Returned members:</p>
+<table style="margin: 5px 2%; width: 95%">
+ <thead><tr>
+ <td style="width: 80px">Member</td>
+ <td style="width: 50px">Flag</td>
+ <td style="width: 90px">Type</td>
+ <td style="width: 40px">null</td>
+ <td>Description</td>
+ </tr></thead>
+ <tr class="odd">
+ <td>id</td>
+ <td>-</td>
+ <td>integer</td>
+ <td>no</td>
+ <td>Release ID</td>
+ </tr>
+ <tr>
+ <td>title</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>no</td>
+ <td>Release title (romaji)</td>
+ </tr>
+ <tr class="odd">
+ <td>original</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Original/official title of the release.</td>
+ </tr>
+ <tr>
+ <td>released</td>
+ <td>basic</td>
+ <td>date (string)</td>
+ <td>yes</td>
+ <td>Release date</td>
+ </tr>
+ <tr class="odd">
+ <td>type</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>no</td>
+ <td>"complete", "partial" or "trial"</td>
+ </tr>
+ <tr>
+ <td>patch</td>
+ <td>basic</td>
+ <td>boolean</td>
+ <td>no</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>freeware</td>
+ <td>basic</td>
+ <td>boolean</td>
+ <td>no</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>doujin</td>
+ <td>basic</td>
+ <td>boolean</td>
+ <td>no</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>languages</td>
+ <td>basic</td>
+ <td>array of strings</td>
+ <td>no</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>website</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Official website URL</td>
+ </tr>
+ <tr class="odd">
+ <td>notes</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Random notes, can contain formatting codes as described in <a href="/d9.3">d9.3</a></td>
+ </tr>
+ <tr>
+ <td>minage</td>
+ <td>details</td>
+ <td>integer</td>
+ <td>yes</td>
+ <td>Age rating, 0 = all ages.</td>
+ </tr>
+ <tr class="odd">
+ <td>gtin</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>JAN/UPC/EAN code. This is actually an integer, but formatted as a string to avoid an overflow on 32bit platforms.</td>
+ </tr>
+ <tr>
+ <td>catalog</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Catalog number.</td>
+ </tr>
+ <tr class="odd">
+ <td>platforms</td>
+ <td>details</td>
+ <td>array of strings</td>
+ <td>no</td>
+ <td>Empty array when platform is unknown.</td>
+ </tr>
+ <tr>
+ <td>media</td>
+ <td>details</td>
+ <td>array of objects</td>
+ <td>no</td>
+ <td>
+ Objects have the following two members:<br />
+ "medium", string<br />
+ "qty", integer, the quantity. <b>null</b> when it is not applicable for the medium.<br />
+ An empty array is returned when the media are unknown.
+ </td>
+ </tr>
+ <tr class="odd">
+ <td>vn</td>
+ <td>vn</td>
+ <td>array of objects</td>
+ <td>no</td>
+ <td>
+ 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.
+ </td>
+ </tr>
+ <tr>
+ <td>producers</td>
+ <td>producers</td>
+ <td>array of objects</td>
+ <td>no</td>
+ <td>
+ (Possibly empty) list of producers involved in this release. Objects have the following members:<br />
+ "id", integer<br />
+ "developer", boolean,<br />
+ "publisher", boolean,<br />
+ "name", string, romaji name<br />
+ "original", string, official/original name, can be <b>null</b><br />
+ "type", string, producer type
+ </td>
+ </tr>
+</table>
+<p>Sorting is possible on the 'id', 'title' and 'released' fields.</p><br />
+
+<p>Accepted filters:</p>
+<table style="margin: 5px 2%; width: 95%">
+ <thead><tr>
+ <td style="width: 80px">Field</td>
+ <td style="width: 90px">Value</td>
+ <td style="width: 90px">Operators</td>
+ <td>&nbsp;</td>
+ </tr></thead>
+ <tr class="odd">
+ <td>id</td>
+ <td>integer<br />array of integers</td>
+ <td>= != &gt; &gt;= &lt; &lt;=<br />= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>vn</td>
+ <td>integer</td>
+ <td>=</td>
+ <td>Find releases linked to the given visual novel ID.</td>
+ </tr>
+ <tr class="odd">
+ <td>producer</td>
+ <td>integer</td>
+ <td>=</td>
+ <td>Find releases linked to the given producer ID.</td>
+ </tr>
+ <tr>
+ <td>title</td>
+ <td>string</td>
+ <td>= != ~</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>original</td>
+ <td>null<br />string</td>
+ <td>= !=<br />= != ~</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>released</td>
+ <td>null<br />date (string)</td>
+ <td>= !=<br />= != &gt; &gt;= &lt; &lt;=</td>
+ <td>Note about released filter for the vn type also applies here.</td>
+ </tr>
+ <tr class="odd">
+ <td>patch</td>
+ <td>boolean</td>
+ <td>=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>freeware</td>
+ <td>boolean</td>
+ <td>=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>doujin</td>
+ <td>boolean</td>
+ <td>=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>type</td>
+ <td>string</td>
+ <td>= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>gtin</td>
+ <td>int</td>
+ <td>= !=</td>
+ <td>Value can also be escaped as a string (if you risk an integer overflow otherwise)</td>
+ </tr>
+ <tr>
+ <td>catalog</td>
+ <td>string</td>
+ <td>= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>languages</td>
+ <td>string<br />array of strings</td>
+ <td>= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+</table>
+
+:SUBSUB:get producer
+<p>Returned members:</p>
+<table style="margin: 5px 2%; width: 95%">
+ <thead><tr>
+ <td style="width: 80px">Member</td>
+ <td style="width: 50px">Flag</td>
+ <td style="width: 90px">Type</td>
+ <td style="width: 40px">null</td>
+ <td>Description</td>
+ </tr></thead>
+ <tr class="odd">
+ <td>id</td>
+ <td>-</td>
+ <td>integer</td>
+ <td>no</td>
+ <td>Producer ID</td>
+ </tr>
+ <tr>
+ <td>name</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>no</td>
+ <td>(romaji) producer name</td>
+ </tr>
+ <tr class="odd">
+ <td>original</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Original/official name</td>
+ </tr>
+ <tr>
+ <td>type</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>no</td>
+ <td>Producer type</td>
+ </tr>
+ <tr class="odd">
+ <td>language</td>
+ <td>basic</td>
+ <td>string</td>
+ <td>no</td>
+ <td>Primary language</td>
+ </tr>
+ <tr>
+ <td>links</td>
+ <td>details</td>
+ <td>object</td>
+ <td>no</td>
+ <td>
+ External links, object has the following members:<br />
+ "homepage", official homepage,<br />
+ "wikipedia", title of the related article on the English wikipedia.<br />
+ Both values can be <b>null</b>.
+ </td>
+ </tr>
+ <tr class="odd">
+ <td>aliases</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Comma separated list of alternative names</td>
+ </tr>
+ <tr>
+ <td>description</td>
+ <td>details</td>
+ <td>string</td>
+ <td>yes</td>
+ <td>Description/notes of the producer, can contain formatting codes as described in <a href="/d9.3">d9.3</a></td>
+ </tr>
+ <tr class="odd">
+ <td>relations</td>
+ <td>relations</td>
+ <td>array of objects</td>
+ <td>no</td>
+ <td>
+ (possibly empty) list of related producers, each object has the following members:<br />
+ "id", integer, producer ID,<br />
+ "relation", string, relation to the current producer,<br />
+ "name", string,<br />
+ "original", string, can be <b>null</b>
+ </td>
+ </tr>
+</table>
+<p>Sorting is possible on the 'id' and 'name' fields.</p><br />
+
+<p>The following filters are recognised:</p>
+<table style="margin: 5px 2%; width: 95%">
+ <thead><tr>
+ <td style="width: 80px">Field</td>
+ <td style="width: 90px">Value</td>
+ <td style="width: 90px">Operators</td>
+ <td>&nbsp;</td>
+ </tr></thead>
+ <tr class="odd">
+ <td>id</td>
+ <td>integer<br />array of integers</td>
+ <td>= != &gt; &gt;= &lt; &lt;=<br />= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>name</td>
+ <td>string</td>
+ <td>= != ~</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>original</td>
+ <td>null<br />string</td>
+ <td>= !=<br />= != ~</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>type</td>
+ <td>string</td>
+ <td>= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr class="odd">
+ <td>language</td>
+ <td>string<br />array of strings</td>
+ <td>= !=</td>
+ <td>&nbsp;</td>
+ </tr>
+ <tr>
+ <td>search</td>
+ <td>string</td>
+ <td>~</td>
+ <td>Not an actual field. Performs a search on the title, original and aliases fields.</td>
+ </tr>
+</table>
+
+
+
+:SUB:The 'error' response
+<p>
+ 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:
+</p>
+<pre>
+ error {"id":"parse", "msg":"Invalid command or argument"}
+</pre>
+<p>
+ 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:
+</p>
+<dl>
+ <dt>parse</dt><dd>Syntax error or unknown command.</dd>
+ <dt>missing</dt><dd>A JSON object argument is missing a required member. The name of which is given in the additional "field" member.</dd>
+ <dt>badarg</dt><dd>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.</dd>
+ <dt>needlogin</dt><dd>Need to be logged in to issue this command.</dd>
+ <dt>throttled</dt><dd>
+ 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.
+ </dd>
+ <dt>auth</dt><dd>(login) Incorrect username/password combination.</dd>
+ <dt>loggedin</dt><dd>(login) Already logged in. Only one successful login command can be issues on one connection.</dd>
+ <dt>sesslimit</dt><dd>(login) Too many open sessions for the current user.</dd>
+ <dt>gettype</dt><dd>(get) Unknown type argument to the 'get' command.</dd>
+ <dt>getinfo</dt><dd>(get) Unknown info flag to the 'get' command. The name of the unrecognised flag is given in an additional "flag" member.</dd>
+ <dt>filter</dt><dd>(get) Unknown filter field or invalid combination of field/operator/argument type. Includes three additional members: "field", "op" and "value" of the incorrect expression.</dd>
+</dl>
+
+
+
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.<br />
-<br />
-The full source code of the site is available on a
-<a href="http://git.blicky.net/vndb.git/">git repository</a>, you can use it
-to track changes to the code or even to run your own version of VNDB.<br />
-<br />
-There is a <a href="http://beta.vndb.org/">beta version</a> 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.<br /><br />
-Feel free to comment about the TODO list and to suggest new features on the
-<a href="/t/db">discussion board</a> or on <a href="irc://irc.synirc.net/vndb">
-IRC</a>.
-<br /><br />
-<b>Last update: 2009-02-12</b><br /><br />
-
-
-:SUB:Next version (2.3)
-<ol>
- <li>Catalog numbers for releases (done)</li>
- <li>Changing release statusses from VN page using AJAX (done)</li>
- <li>Random VN quotes on footer of every page (done)</li>
- <li>Platform icons on homepage (done)</li>
- <li>Tagging system (...will take a while)</li>
-</ol>
-
-
-:SUB:(Hopefully) soon
-<ol>
- <li>Advanced notification system</li>
- <li>More VNList filters &amp; stats</li>
- <li>Release calendar</li>
-</ol>
-
-
-:SUB:Later
-<ol>
- <li>Filters on /v/* to exclude blacklisted and/or VNs on a users list from the results</li>
- <li>Producer relations</li>
- <li><b style="font-weight: normal; text-decoration: line-through">VN Staff and Character database</b><br />
- <i>Probably not going to make it into VNDB, unless someone wants to be in charge of this</i></li>
- <li>Soundtrack listing<br />
- <i>Work together with <a href="http://vgmdb.net/">VGMdb</a>.</i></li>
- <li>Manga relations<br />
- <i>Looking for a good and comprehensive manga database fetch info from</i></li>
- <li>Reviews</li>
- <li>Walkthroughs/guides?<br />
- <i>Let each user maintain his own walkthrough, or just one collaboratively edited walkthough per VN?</i></li>
- <li>Image quality/resolution field to releases</li>
- <li>Some kind of minimal API for the hardcore VNDB fans (most likely using the JSON-RPC 1.0 Specification)</li>
- <li>Automatic uploading of cover images from a URL</li>
- <li>Scans of the packaging for releases</li>
- <li>Store which fields have been changed for revisions using one-character-code indicators</li>
- <li>Improved platform for fan translations (separated from releases)</li>
- <li>'currently down/offline' flag for official website links</li>
-</ol>
-
-
-:SUB:Technical things that need improvements
-<p>
- (I'm not expecting anyone to understand this, just random notes for myself)
-</p>
-<ol>
- <li>Set PostgreSQL charset to UTF-8</li>
- <li>Make use of PostgreSQL's fulltext search functionality to power the VN search</li>
- <li>Proper implementation of the 'hidden' flag for v/r/p entries</li>
- <li>Let VNDB and Multi communicate via SQL (and use schemas to separate the tables)</li>
- <li>More secure login system</li>
- <li>Use PostgreSQL stored procedures to insert revisions (using composite types, arrays, etc as arguments)</li>
- <li>Update the users.c_changes column on hiding/unhiding an item</li>
-</ol>
-
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 @@
</dd><dt>[raw]</dt><dd>
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.
+ </dd><dt>[code]</dt><dd>
+ 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.
</dd>
</dl>
<p>
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 @@
</dd><dt>[raw]</dt><dd>
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.
+ </dd><dt>[code]</dt><dd>
+ 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.
</dd>
</dl>
<p>
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]. Любые
вышеперечисленные коды будут проигнорированы.
+ </dd><dt>[code]</dt><dd>
+ Действие этого тега похоже на [raw], с той лишь разницей, что для текста
+ в блоке [code]...[/code] будет использован моноширинный шрифт и код
+ будет помещен в красивую рамочку, чтобы отделить его от остального текста.
</dd>
</dl>
<p>
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 @@
<li><a href="/d9">Discussion board</a></li>
<li><a href="/d6">FAQ</a></li>
<li><a href="/d7">About us</a></li>
+ <li><a href="/d11">Database API</a></li>
<li><a href="/d8">Development</a></li>
</ul>
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 @@
<li><a href="/d9">Diskusní boardy</a></li>
<li><a href="/d6">FAQ</a></li>
<li><a href="/d7">O nás</a></li>
+ <li><a href="/d11">Databáze API</a></li>
<li><a href="/d8">Vývoj</a></li>
</ul>
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 @@
<li><a href="/d9">Форум</a></li>
<li><a href="/d6">ЧаВо</a></li>
<li><a href="/d7">О нас</a></li>
+ <li><a href="/d11">API базы данных</a></li>
<li><a href="/d8">Разработка</a></li>
-</ul> \ No newline at end of file
+</ul>
diff --git a/data/global.pl b/data/global.pl
index 6dae908f..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 => {
@@ -117,6 +101,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/lang.txt b/data/lang.txt
index 1310d8fd..43c8d6f2 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
@@ -997,6 +997,36 @@ cs : Vyřazeno
hu : Lemondva
+# Age ratings
+
+:_minage_null
+en : Unknown
+ru : Неизвестно
+cs : Není známo
+hu : Ismeretlen
+
+:_minage_all
+en : All ages
+ru : Все возраста
+cs : Pro všechny věky
+hu : Nincs korhatár
+
+# "Ages [_1] and up", but shorter
+:_minage_age
+en : [_1]+
+ru : [_1]+
+cs : [_1]+
+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
+:_minage_example
+en : (e.g. [_1])
+ru : (т.е. [_1])
+cs : (např. [_1])
+hu : (p.l. [_1])
+
+
# Form messages
:_formerr_e_login_failed
@@ -2146,6 +2176,18 @@ ru : a.k.a. [_1]
cs : a.k.a. [_1]
hu :
+:_prodpage_homepage
+en : Homepage
+ru : Домашняя страничка
+cs : Domovská stránka
+hu : Weboldal
+
+:_prodpage_wikipedia
+en : Wikipedia
+ru : Википедия
+cs : Wikipedie
+hu :
+
:_prodpage_vnrel
en : Visual Novel Relations
ru : Связи новелл
@@ -2168,7 +2210,7 @@ hu : fejlesztő
en : publisher
ru : издатель
cs : vydavatel
-hu : kiadó
+hu : forgalmazó
# producer diff fields
@@ -2209,6 +2251,12 @@ ru : Веб-сайт
cs : Internetová stránka
hu : Weboldal
+:_revfield_p_l_wp
+en : Wikipedia link
+ru : Ссылка на Википедию
+cs : Odkaz na Wikipedii
+hu :
+
:_revfield_p_desc
en : Description
ru : Описание
@@ -2234,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
@@ -2305,6 +2353,12 @@ ru : Веб-сайт
cs : Internetová stránka
hu : Weboldal
+:_pedit_form_wikipedia
+en : Wikipedia link
+ru : Ссылка на Википедию
+cs : Odkaz na Wikipedii
+hu :
+
:_pedit_form_desc
en : Description
ru : Описание
@@ -2539,7 +2593,7 @@ hu : fejlesztő
en : publisher
ru : издатель
cs : vydavatel
-hu : kiadó
+hu : forgalmazó
# Information table (on every release page)
@@ -2662,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.
@@ -2674,7 +2728,7 @@ hu : Katalógus szám
en : Links
ru : Ссылки
cs : Odkazy
-hu : Linkek
+hu : Hivatkozások
:_relinfo_website
en : Official website
@@ -2945,7 +2999,7 @@ hu : Fejlesztő
en : Publisher
ru : Издатель
cs : Vydavatel
-hu : Kiadó
+hu : Forgalmazó
:_redit_form_prod_both
en : Both
@@ -4573,6 +4627,12 @@ ru : Популярность
cs : Popularita
hu : Népszerűség
+:_vnbrowse_col_rating
+en : Rating
+ru : Рейтинг
+cs : Hodnocení
+hu : Értékelés
+
:_vnbrowse_tagign_title
en : The following tags were ignored:
ru : Следующие теги были пропущены:
@@ -4754,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.
@@ -4858,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
@@ -5091,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+.+)
@@ -5130,7 +5190,7 @@ hu : Hossz
en : ~[no link~]
ru : ~[нет ссылки~]
cs : ~[žádný odkaz~]
-hu : ~[nincs link~]
+hu : ~[nincs hivatkozás~]
:_vndiff_none
en : ~[none~]
@@ -5291,6 +5351,24 @@ ru : Ссылки
cs : Odkazy
hu : Linkek
+:_vnpage_l_wp
+en : Wikipedia
+ru : Википедия
+cs : Wikipedie
+hu :
+
+:_vnpage_l_encubed
+en : Encubed
+ru : Encubed
+cs : Encubed
+hu :
+
+:_vnpage_l_renai
+en : Renai.us
+ru : Renai.us
+cs : Renai.us
+hu :
+
:_vnpage_description
en : Description
ru : Описание
@@ -5337,7 +5415,7 @@ hu : Fejlesztő
en : Publishers
ru : Издатели
cs : Vydavatel
-hu : Kiadó
+hu : Forgalmazók
:_vnpage_relations
en : Relations
@@ -5545,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 : Hodnocení
+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 : Népszerűség: [_1]. hely [_2] pontszámmal
-:_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_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 : Bayes-i ranglista: [_1]. hely [_2]-s értékeléssel
@@ -5749,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.
diff --git a/data/style.css b/data/style.css
index ff0844ad..d6542477 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;
+}
@@ -778,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;
@@ -897,7 +906,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/Multi/API.pm b/lib/Multi/API.pm
new file mode 100644
index 00000000..8014f9be
--- /dev/null
+++ b/lib/Multi/API.pm
@@ -0,0 +1,913 @@
+
+#
+# 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';
+use Time::HiRes 'time'; # important for throttling
+use JSON::XS;
+
+
+# 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 }
+
+
+# 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 ]
+# 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(
+ package_states => [
+ $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
+ get_producer get_producer_res admin
+ |],
+ ],
+ heap => {
+ port => 19534,
+ 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)
+ ipbans => [],
+ @_,
+ c => {}, # open connections
+ s => {conn => 0, cmds => 0, cmd_err => 0}, # stats
+ },
+ );
+}
+
+
+## Non-POE helper functions
+
+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 }]);
+
+ # log
+ $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 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]});
+ my %e = ( field => $field, op => $op, value => $value );
+
+ # get the field that matches
+ $t = (grep $_->[0] eq $field, @$t)[0];
+ return cerr $c, filter => "Unknown field '$field'", %e if !$t;
+ $t = [ @$t[1..$#$t] ];
+
+ # 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' && @$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];
+ 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)
+ my @values = ref($value) eq 'ARRAY' ? @$value : $value;
+ for my $v (!$o{process} ? () : @values) {
+ if(!ref $o{process}) {
+ $v = sprintf $o{process}, $v;
+ } elsif(ref($o{process}) eq 'CODE') {
+ $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=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;
+ }
+
+ # and do some processing for type=stra and type=inta
+ my @parameters;
+ if($o{serialize}) {
+ for (@values) {
+ my $v = $o{serialize};
+ $v =~ s/:op:/$ops->{$op}/g;
+ $v =~ s/:value:/push @parameters, $_; '?'/eg;
+ $_ = $v;
+ }
+ } else {
+ @parameters = @values;
+ $_ = '?' for @values;
+ }
+ my $joined = join defined $o{join} ? $o{join} : '', @values;
+ $sql =~ s/:value:/push @$p, @parameters; $joined/eg;
+ return $sql;
+}
+
+
+# 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 {
+ $_[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];
+
+ $_[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});
+ 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,
+ connected => time,
+ cmds => 0,
+ cmd_err => 0,
+ # username, client, clientver are added after logging in
+ };
+ $_[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};
+
+ # 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
+ # and the arguments are syntactically valid
+
+ # handle login command
+ return $_[KERNEL]->yield(login => $c, @$arg) if $cmd eq 'login';
+ return cerr $c, needlogin => 'Not logged in.' if !$c->{username};
+
+ # 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
+ 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|producer)$/;
+ return $_[KERNEL]->yield("get_$arg->[0]", \%obj);
+ }
+
+ # unknown command
+ return cerr $c, 'parse', "Unkown command '$cmd'" if $cmd ne 'get';
+}
+
+
+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 $_[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 = ?",
+ [ $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});
+
+ # 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->{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});
+}
+
+
+sub get_results {
+ 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, 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 $get = $_[ARG0];
+
+ 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/, @{$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 $get->{filters}, \&filtertosql, $get->{c}, \@placeholders, [
+ [ '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' ]
+ ], [ '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' ],
+ [ 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' ],
+ ],
+ ];
+ 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 $last|,
+ \@placeholders, 'get_vn_res', $get);
+}
+
+
+sub get_vn_res {
+ 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
+ };
+ }
+ }
+ $get->{more} = pop(@$res)&&1 if @$res > $_[HEAP]{results};
+ $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;
+ }
+
+ 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;
+
+ @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' });
+
+ @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' });
+
+ # send results
+ delete $_->{latest} for @{$get->{list}};
+ $_[KERNEL]->yield(get_results => { %$get, type => 'vn' });
+}
+
+
+sub get_release {
+ my $get = $_[ARG0];
+
+ 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/, @{$get->{info}};
+ $select .= ', rr.website, rr.notes, rr.minage, rr.gtin, rr.catalog' if grep /details/, @{$get->{info}};
+
+ my @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 => ',' ],
+ ], [ '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' ],
+ ], [ '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' ],
+ ],
+ ];
+ 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 $last|,
+ \@placeholders, 'get_release_res', $get);
+}
+
+
+sub get_release_res {
+ 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;
+ $_->{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} *= 1 if defined $_->{minage};
+ $_->{gtin} ||= undef;
+ $_->{catalog} ||= undef;
+ }
+ }
+ $get->{more} = pop(@$res)&&1 if @$res > $_[HEAP]{results};
+ $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;
+ }
+ 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}};
+ my $ids = join ',', map '?', @ids;
+
+ @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' });
+
+ @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' });
+
+ @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' });
+
+ @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' });
+
+ @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' });
+
+ # send results
+ delete $_->{latest} for @{$get->{list}};
+ $_[KERNEL]->yield(get_results => { %$get, type => 'release' });
+}
+
+
+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.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, [
+ [ '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}}) {
+ $_->{links} = {
+ homepage => delete($_->{website})||undef,
+ wikipedia => delete $_->{l_wp},
+ };
+ $_->{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..$#_];
+
+ 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;
+
+
+__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)
+ '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)
+ 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 "%$_%" }
+ \'lang' => sub { !grep($_ eq $_[0], @{$VNDB::S{languages}}) ? \'Invalid language' : $_[0] }
+
+ 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 => ','],
+ ]
+
diff --git a/lib/Multi/Core.pm b/lib/Multi/Core.pm
index 830ca657..6388c924 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;
@@ -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,25 +72,40 @@ 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;
+ open STDIN, '/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],
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;
}
@@ -87,8 +121,17 @@ sub shutdown {
$_[KERNEL]->call(core => log => 'Shutting down (%s)', $_[ARG1]);
$_[KERNEL]->post(pg => 'shutdown');
$_[KERNEL]->alias_remove('core');
+ unlink "$VNDB::ROOT/data/multi.pid";
}
+# 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;
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index 359e8e4b..7d74ded7 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,
@@ -51,7 +53,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 +79,7 @@ sub spawn {
eval => 2|8,
die => 2|8,
post => 2|8,
+ api => 2|8,
},
}
);
@@ -89,24 +92,31 @@ 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;
}
+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');
@@ -239,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);
}
@@ -323,23 +333,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));
}
@@ -347,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
@@ -381,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
@@ -403,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]);
}
@@ -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');
+ }
+}
+
+
#
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 4f816e56..0ea7ef29 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(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)
+ ),
+ 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');
+}
+
+
sub cleangraphs {
# should be pretty fast
$_[KERNEL]->post(pg => do => q|
diff --git a/lib/POE/Filter/VNDBAPI.pm b/lib/POE/Filter/VNDBAPI.pm
new file mode 100644
index 00000000..24188a2f
--- /dev/null
+++ b/lib/POE/Filter/VNDBAPI.pm
@@ -0,0 +1,312 @@
+# 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 <json-object>
+# [ 'login', {object} ]
+#
+# C: get <type> <info> <filters> <options>
+# [ 'get', <type>, <info>[ split ',', $2 ], [ filters ], { options } ]
+# <type> must match /[a-z\/_]+/
+# <info> as string: /[a-z_]+(,[a-z_]+)*/, in perl: [ /[a-z_]+/, .. ]
+# <options> is optional, must be JSON-object otherwise
+#
+# S: ok
+# [ 'ok' ]
+#
+# S: results <json-object>
+# [ 'results', {object} ]
+#
+# S: error <json-object>
+# [ 'error', {object} ]
+#
+# <filters>:
+# string: ((<field> <op> <json-value>) <bool-op> (<field> <op> <json-value> ))
+# perl: [ [ 'field', 'op', value ], 'bool-op', [ 'field', 'op', value ] ]
+# <field> must match /[a-z_]+/
+# <op> 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 <type>
+my $GET_INFO = qr/(?:[a-z_]+(?:,[a-z_]+)*)/; # get <info>
+my $FILTER_FIELD = qr/(?:[a-z_]+)/; # <field> in the filters
+my $FILTER_OP = qr/(?:=|!=|<|>|>=|<=|~)/; # <op> in the filters
+my $FILTER_BOOL = qr/(?:and|or)/; # <boolean-op> 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";
+ }
+ 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, $options) = ($1, $2, {});
+ my($filters, $rest) = decode_filters($3);
+ return _err $filters if !ref $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
+ 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 "<field> <op> <value>", and returns the arrayref and the remaining (unparsed) string after <value>
+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;
+ }
+ # <expr>
+ ($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]");
+ }
+}
+
+
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/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/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/DB/VN.pm b/lib/VNDB/DB/VN.pm
index bb2c1275..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;
@@ -82,13 +82,17 @@ sub dbVNGet {
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} =~ /ranking/ ? '(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS ranking' : (),
+ $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 iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking',
+ ) : (),
$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 +102,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/Func.pm b/lib/VNDB/Func.pm
index ad38215e..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...
@@ -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 .= '<b class="spoiler">'; next }
elsif (lc$_ eq '[quote]') {
@@ -59,6 +60,11 @@ sub bb2html {
$result .= '<div class="quote">' if !$maxlength;
$rmnewline = 1;
next
+ } elsif (lc$_ eq '[code]') {
+ push @open, 'code';
+ $result .= '<pre>' if !$maxlength;
+ $rmnewline = 1;
+ next
} elsif (lc$_ eq '[/spoiler]') {
if($open[$#open] eq 'spoiler') {
$result .= '</b>';
@@ -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 .= '</pre>' if !$maxlength;
+ pop @open;
next;
}
@@ -108,8 +118,11 @@ sub bb2html {
$result .= $e->($_);
}
- $result .= $_ eq 'url' ? '</a>' : $_ eq 'quote' ? '</div>' : '</b>'
- while((local $_ = pop @open) ne 'first');
+ # close open tags
+ while((local $_ = pop @open) ne 'first') {
+ $result .= $_ eq 'url' ? '</a>' : $_ eq 'spoiler' ? '</b>' : '';
+ $result .= $_ eq 'quote' ? '</div>' : $_ eq 'code' ? '</pre>' : '' if !$maxlength;
+ }
$result .= '...' if $maxlength && $length > $maxlength;
return $result;
@@ -208,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/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|<h3><a href="#$sec" name="$sec">$sec. $1</a></h3>\n|
}eg;
+ s{^:SUBSUB:(.+)\r?\n$}{
+ $subsec++;
+ qq|<h4><a href="#$sec.$subsec" name="$sec.$subsec">$sec.$subsec. $1</a></h4>\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 $!;
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 '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink' # _vn? hmm...
+ }],
[ desc => diff => 1 ],
[ relations => join => '<br />', split => sub {
my @r = map sprintf('%s: <a href="/p%d" title="%s">%s</a>',
@@ -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').'<br /><b class="standout">'.mt('_inenglish').'</b>', short => 'desc', rows => 6 ],
], 'pedit_rel' => [ mt('_pedit_form_rel'),
[ hidden => short => 'prodrelations' ],
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').'<br /><b class="standout">'.mt('_inenglish').'</b>' ],
[ 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/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index bca21151..dbe80ac1 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 => '' },
@@ -55,12 +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 ]) : (),
@@ -90,6 +99,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) = @_;
@@ -113,7 +123,11 @@ 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}||0;
+ b class => 'grayedout', sprintf ' (%d)', $l->{c_votecount};
+ end;
end;
},
);
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index ec5f4afc..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};
@@ -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;
@@ -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/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm
index ed5ebb36..0d510aa9 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';
@@ -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';
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};
}
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index ad99d32d..dc87d737 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -479,10 +479,11 @@ sub htmlVoteStats {
}
clearfloat;
- if($type eq 'v') {
+ if($type eq 'v' && $obj->{c_votecount}) {
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->{c_rating};
end;
}
end;
diff --git a/util/dump.sql b/util/dump.sql
index 0fdf5696..e0af79e8 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
@@ -129,7 +130,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 '',
@@ -290,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,
+ c_rating real,
+ c_votecount integer NOT NULL DEFAULT 0
);
-- vn_anime
@@ -478,11 +481,11 @@ 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;
- 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;
@@ -575,7 +578,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/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";
-
-
-
-
diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql
new file mode 100644
index 00000000..3b3e147d
--- /dev/null
+++ b/util/updates/update_2.9.sql
@@ -0,0 +1,76 @@
+
+-- 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();
+
+
+
+-- 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;
+
+
+-- wikipedia link for producers
+ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150);
+
+
+-- bayesian rating
+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(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)
+ ),
+ 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
+ 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;
+ 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();
+