diff options
author | Yorhel <git@yorhel.nl> | 2009-11-16 10:00:16 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2009-11-16 10:01:02 +0100 |
commit | 445b96631ac06d2fb0c3b6486ccde2a189a58675 (patch) | |
tree | 2bdcdbf0912756af65b36e20c7b271b5e2386abf | |
parent | 207ecaadc6e746e05d44acc96adbc30e64a87cee (diff) | |
parent | a5e88fd33c3ec018e89be0b5368a85119323554c (diff) |
Merge branch 'beta'2.9
+ Changelog update
37 files changed, 2719 insertions, 306 deletions
@@ -1,4 +1,6 @@ /data/config.pl +/data/multi.pid +/data/docs/8 /data/log/ /static/f/script.js /static/s/*/style.css @@ -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) + @@ -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: =, !=, <, <=, >, + >= 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> </td> + </tr></thead> + <tr class="odd"> + <td>id</td> + <td>integer<br />array of integers</td> + <td>= != > >= < <=<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> </td> + </tr> + <tr class="odd"> + <td>original</td> + <td>null<br />string</td> + <td>= !=<br />= != ~</td> + <td> </td> + </tr> + <tr> + <td>released</td> + <td>null<br />date (string)</td> + <td>= !=<br />= != > >= < <=</td> + <td> + Note that matching on partial dates (released = "2009") doesn't do what + you want, use ranges instead, e.g. (released > "2008" and released <= "2009"). + </td> + </tr> + <tr class="odd"> + <td>platforms</td> + <td>null<br />string<br />array of strings</td> + <td><br />= !=</td> + <td> </td> + </tr> + <tr> + <td>languages</td> + <td>null<br />string<br />array of strings</td> + <td><br />= !=</td> + <td> </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> </td> + </tr> + <tr class="odd"> + <td>freeware</td> + <td>basic</td> + <td>boolean</td> + <td>no</td> + <td> </td> + </tr> + <tr> + <td>doujin</td> + <td>basic</td> + <td>boolean</td> + <td>no</td> + <td> </td> + </tr> + <tr class="odd"> + <td>languages</td> + <td>basic</td> + <td>array of strings</td> + <td>no</td> + <td> </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> </td> + </tr></thead> + <tr class="odd"> + <td>id</td> + <td>integer<br />array of integers</td> + <td>= != > >= < <=<br />= !=</td> + <td> </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> </td> + </tr> + <tr class="odd"> + <td>original</td> + <td>null<br />string</td> + <td>= !=<br />= != ~</td> + <td> </td> + </tr> + <tr> + <td>released</td> + <td>null<br />date (string)</td> + <td>= !=<br />= != > >= < <=</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> </td> + </tr> + <tr> + <td>freeware</td> + <td>boolean</td> + <td>=</td> + <td> </td> + </tr> + <tr class="odd"> + <td>doujin</td> + <td>boolean</td> + <td>=</td> + <td> </td> + </tr> + <tr> + <td>type</td> + <td>string</td> + <td>= !=</td> + <td> </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> </td> + </tr> + <tr class="odd"> + <td>languages</td> + <td>string<br />array of strings</td> + <td>= !=</td> + <td> </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> </td> + </tr></thead> + <tr class="odd"> + <td>id</td> + <td>integer<br />array of integers</td> + <td>= != > >= < <=<br />= !=</td> + <td> </td> + </tr> + <tr> + <td>name</td> + <td>string</td> + <td>= != ~</td> + <td> </td> + </tr> + <tr class="odd"> + <td>original</td> + <td>null<br />string</td> + <td>= !=<br />= != ~</td> + <td> </td> + </tr> + <tr> + <td>type</td> + <td>string</td> + <td>= !=</td> + <td> </td> + </tr> + <tr class="odd"> + <td>language</td> + <td>string<br />array of strings</td> + <td>= !=</td> + <td> </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 & 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(); + |