summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--ChangeLog21
-rw-r--r--Makefile59
-rw-r--r--README2
-rw-r--r--data/global.pl3
-rw-r--r--data/lang.txt55
-rw-r--r--data/script.js78
-rw-r--r--data/style.css6
-rw-r--r--lib/Multi/API.pm16
-rw-r--r--lib/Multi/Anime.pm17
-rw-r--r--lib/Multi/Maintenance.pm37
-rw-r--r--lib/Multi/RG.pm11
-rw-r--r--lib/POE/Filter/VNDBAPI.pm135
-rw-r--r--lib/VNDB/DB/Discussions.pm18
-rw-r--r--lib/VNDB/DB/Misc.pm69
-rw-r--r--lib/VNDB/DB/Producers.pm49
-rw-r--r--lib/VNDB/DB/Releases.pm108
-rw-r--r--lib/VNDB/DB/Tags.pm89
-rw-r--r--lib/VNDB/DB/ULists.pm30
-rw-r--r--lib/VNDB/DB/Users.pm14
-rw-r--r--lib/VNDB/DB/VN.pm113
-rw-r--r--lib/VNDB/Handler/Discussions.pm4
-rw-r--r--lib/VNDB/Handler/Misc.pm21
-rw-r--r--lib/VNDB/Handler/Producers.pm30
-rw-r--r--lib/VNDB/Handler/Releases.pm24
-rw-r--r--lib/VNDB/Handler/Tags.pm162
-rw-r--r--lib/VNDB/Handler/ULists.pm4
-rw-r--r--lib/VNDB/Handler/Users.pm4
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm57
-rw-r--r--lib/VNDB/Handler/VNEdit.pm35
-rw-r--r--lib/VNDB/Handler/VNPage.pm46
-rw-r--r--lib/VNDB/L10N.pm6
-rw-r--r--lib/VNDB/Plugin/TransAdmin.pm2
-rw-r--r--lib/VNDB/Util/BrowseHTML.pm204
-rw-r--r--lib/VNDB/Util/CommonHTML.pm153
-rwxr-xr-xutil/dbgraph.pl8
-rw-r--r--util/dump.sql851
-rw-r--r--util/sql/all.sql75
-rw-r--r--util/sql/func.sql542
-rw-r--r--util/sql/schema.sql388
-rw-r--r--util/updates/update_2.10.sql63
-rwxr-xr-xutil/vndb.pl20
41 files changed, 1902 insertions, 1727 deletions
diff --git a/ChangeLog b/ChangeLog
index 43f03286..01780243 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,3 +1,24 @@
+2.10 - 2010-01-10
+ - VN score on tag pages use plain averages instead of bayesian rating
+ - Display VN ratings on tag pages as well
+ - Split browse functions from CommonHTML.pm into BrowseHTML.pm
+ - Abstracted all ORDER BY clauses in the DB abstraction layer
+ - Show language flags on release lists on the homepage
+ - Allow hiding of NSFW cover even if NSFW warning is disabled
+ - Removed /g/debug
+ - Replaced recursive stored procedures with WITH .. SELECT queries
+ - Merged db[VN|Producer|Release][Edit|Add] into dbItemEdit and dbItemAdd
+ - Removed the use of CONSTRAINT TRIGGERs
+ - Added maxlength check on the website fields for releases and producers
+ - Removed changes.causedby
+ - Fixed minor JS dropdown issue when trigger objects are close to each other
+ - Allow earlier selecting of release on screenshot upload
+ - Fixed bug with zero strings ("0") in the diff viewer
+ - Rewrote POE::Filter::VNDBAPI to be more generic
+ - Highlight opened VN/producer in relation graphs
+ - Added revision insertion abstraction functions in SQL
+ - Determine interface language from Accept-Language header
+
2.9 - 2009-11-16
- Fixed another bug with the calculation of tags_vn_bayesian.spoiler
- Implemented proper daemonizing and error handling for Multi
diff --git a/Makefile b/Makefile
index df1d05aa..873ae1f5 100644
--- a/Makefile
+++ b/Makefile
@@ -1,8 +1,8 @@
# all (default)
-# Same as $ make staticdirs js skins www robots
+# Same as $ make dirs js skins www robots
#
-# staticdirs
-# Creates the required directory structures in static/
+# dirs
+# Creates the required directories not present in git
#
# js
# Generates the Javascript code
@@ -26,27 +26,35 @@
# Start/stop/restart the Multi daemon. Provided for convenience, a proper initscript
# probably makes more sense.
#
+# sql-import
+# Imports util/sql/all.sql into your (presumably empty) database
+#
+# update-2.10
+# Updates all non-versioned items to 2.10
#
# 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
+.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10
-all: staticdirs js skins robots
+all: dirs js skins robots data/config.pl
-staticdirs: static/cv static/sf static/st
+dirs: static/cv static/sf static/st data/log www
static/cv static/sf static/st:
mkdir $@;
for i in $$(seq -w 0 1 99); do mkdir "$@/$$i"; done
+data/log www:
+ mkdir $@
+
js: static/f/script.js
-static/f/script.js: data/script.js data/lang.txt util/jsgen.pl
+static/f/script.js: data/script.js data/lang.txt util/jsgen.pl data/config.pl
util/jsgen.pl
@@ -56,10 +64,7 @@ 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: dirs www/robots.txt static/robots.txt
%/robots.txt:
echo 'User-agent: *' > $@
@@ -100,3 +105,35 @@ multi-restart:
$(multi-stop)
$(multi-start)
+
+# Small perl script that tries to connect to the PostgreSQL database using 'psql', with the
+# connection settings from data/config.pl. May not work in all configurations, though...
+define runpsql
+ perl -MDBI -e 'package VNDB;\
+ $$ROOT=".";\
+ require "data/global.pl";\
+ $$_=(DBI->parse_dsn($$VNDB::O{db_login}[0]))[4];\
+ $$ENV{PGPASSWORD} = $$VNDB::O{db_login}[2];\
+ $$ENV{PGUSER} = $$VNDB::O{db_login}[1];\
+ $$ENV{PGDATABASE} = $$2 if /(dbname|db|database)=([^;]+)/;\
+ $$ENV{PGHOST} = $$1 if /host=([^;]+)/;\
+ $$ENV{PGHOSTADDR} = $$1 if /hostaddr=([^;]+)/;\
+ $$ENV{PGPORT} = $$1 if /port=([^;]+)/;\
+ $$ENV{PGSERVICE} = $$1 if /service=([^;]+)/;\
+ $$ENV{PGSSLMODE} = $$1 if /sslmode=([^;]+)/;\
+ open F, "|psql" or die $$!;\
+ print F while(<>);\
+ close F or exit 1'
+endef
+
+
+sql-import:
+ ${runpsql} < util/sql/all.sql
+
+
+update-2.10: all
+ $(multi-stop)
+ @${runpsql} < util/updates/update_2.10.sql
+ $(multi-start)
+
+
diff --git a/README b/README
index c56db3f7..21b9389b 100644
--- a/README
+++ b/README
@@ -12,7 +12,7 @@ Requirements
global requirements:
Linux, or an OS that resembles Linux. Chances are VNDB won't run on Windows.
- PostgreSQL 8.3+
+ PostgreSQL 8.4+
perl 5.10 recommended, 5.8 may also work
A webserver that works with YAWF (lighttpd and Apache are known to work)
diff --git a/data/global.pl b/data/global.pl
index b67ae2f7..a160ab15 100644
--- a/data/global.pl
+++ b/data/global.pl
@@ -77,8 +77,9 @@ our %S = (%S,
otc => 0
},
resolutions => [
+ # TODO: Make translatable!
[ 'Unknown / console / handheld', '' ],
- [ 'Custom', '' ],
+ [ 'Non-standard', '' ],
[ '640x480 (480p)', '4:3' ],
[ '800x600', '4:3' ],
[ '1024x768', '4:3' ],
diff --git a/data/lang.txt b/data/lang.txt
index 1da99ef0..0f655dc5 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -104,6 +104,23 @@ ends with ']'. The following options are supported:
# data/global.pl - used in many places
+# Date/time format
+# See http://www.opengroup.org/onlinepubs/007908799/xsh/strftime.html for the formatting codes
+# (I recommend to keep the yyyy-mm-dd format, though, see http://www.cl.cam.ac.uk/~mgk25/iso-time.html)
+
+:_datetime_compact
+en : %Y-%m-%d
+ru*:
+cs*:
+hu*:
+
+:_datetime_full
+en : %Y-%m-%d at %R
+ru*:
+cs*:
+hu*:
+
+
# Age display
:_age_years
@@ -1657,12 +1674,30 @@ ru : [userstr,_1], [date,_2,full]
cs : Změnil [userstr,_1] [date,_2,full]
hu : [userstr,_1] által, [date,_2,full]
-:_revision_emptyfield
+:_revision_empty
en : ~[empty~]
ru : ~[пусто~]
cs : ~[prázdná~]
hu : ~[üres~]
+:_revision_nolink
+en : ~[no link~]
+ru : ~[нет ссылки~]
+cs : ~[žádný odkaz~]
+hu : ~[nincs hivatkozás~]
+
+:_revision_yes
+en : Yes
+ru*:
+cs*:
+hu*:
+
+:_revision_no
+en : No
+ru*:
+cs*:
+hu*:
+
# tabs above the search boxes
@@ -2269,12 +2304,6 @@ ru : Отношения
cs : Vztahy
hu : Összefüggések
-:_proddiff_none
-en : none
-ru : нет
-cs : žádné
-hu : semmi
-
# Producer relation graph page (/p+/rg)
@@ -5186,18 +5215,6 @@ ru : Продолжительность
cs : Délka
hu : Hossz
-:_vndiff_nolink
-en : ~[no link~]
-ru : ~[нет ссылки~]
-cs : ~[žádný odkaz~]
-hu : ~[nincs hivatkozás~]
-
-:_vndiff_none
-en : ~[none~]
-ru : ~[пусто~]
-cs : ~[nic~]
-hu : ~[nincs~]
-
:_revfield_v_l_wp
en : Wikipedia link
ru : Ссылка Википедии
diff --git a/data/script.js b/data/script.js
index c2eee7f4..8f725b12 100644
--- a/data/script.js
+++ b/data/script.js
@@ -316,7 +316,7 @@ function ddMouseMove(e) {
ddHide();
}
- if(!box.dd_used && lnk) {
+ if(!box.dd_used && lnk || box.dd_used && lnk && box.dd_lnk != lnk) {
box.dd_lnk = lnk;
box.dd_used = true;
if(!ddRefresh())
@@ -566,7 +566,7 @@ function dsInit(obj, url, trfunc, serfunc, retfunc, parfunc) {
obj.ds_selectedId = 0;
obj.ds_dosearch = null;
if(!byId('ds_box'))
- addBody(tag('div', {id: 'ds_box', style: 'position: absolute; top: -500px'}, tag('b', mt('_js_loading'))));
+ addBody(tag('div', {id: 'ds_box'}, tag('b', mt('_js_loading'))));
}
function dsKeyDown(ev) {
@@ -927,6 +927,7 @@ if(byId('jt_box_rel_format'))
var scrRel = [ [ 0, mt('_vnedit_scr_selrel') ] ];
var scrStaticURL;
var scrUplNr = 0;
+var scrDefRel;
function scrLoad() {
// get scrRel and scrStaticURL
@@ -935,6 +936,9 @@ function scrLoad() {
for(var i=0; i<rel.options.length; i++)
scrRel[scrRel.length] = [ rel.options[i].value, getText(rel.options[i]) ];
rel.parentNode.removeChild(rel);
+ if(scrRel.length <= 2)
+ scrRel.shift();
+ scrDefRel = scrRel[0][0];
// load the current screenshots
var scr = byId('screenshots').value.split(' ');
@@ -958,9 +962,10 @@ function scrSetSubmit() {
var norelease = 0;
var l = byName(byId('scr_table'), 'tr');
for(var i=0; i<l.length-1; i++) {
+ var rel = byName(l[i], 'select')[0];
if(l[i].scr_status > 0)
loading = 1;
- else if(byName(l[i], 'select')[0].selectedIndex == 0)
+ else if(rel.options[rel.selectedIndex].value == 0)
norelease = 1;
}
if(loading) {
@@ -979,7 +984,7 @@ function scrURL(id, t) {
}
function scrAdd(id, nsfw, rel) {
- // tr.scr_status = 0: done, 1: uploading, 2: waiting for thumbnail, 3: deleted
+ // tr.scr_status = 0: done, 1: uploading, 2: waiting for thumbnail
var tr = tag('tr', { id:'scr_tr_'+id, scr_id: id, scr_status: id?2:1, scr_rel: rel, scr_nsfw: nsfw},
tag('td', { 'class': 'thumb'}, mt('_js_loading')),
@@ -1001,6 +1006,11 @@ function scrLast() {
byId('scr_table').removeChild(byId('scr_last'));
var full = byName(byId('scr_table'), 'tr').length >= 10;
+
+ var rel = tag('select', {onchange: function(){scrDefRel=this.options[this.selectedIndex].value}, 'class':'scr_relsel', 'id':'scradd_relsel'});
+ for(var j=0; j<scrRel.length; j++)
+ rel.appendChild(tag('option', {value: scrRel[j][0], selected: scrDefRel == scrRel[j][0]}, scrRel[j][1]));
+
byId('scr_table').appendChild(tag('tr', {id:'scr_last'},
tag('td', {'class': 'thumb'}),
full ? tag('td',
@@ -1014,6 +1024,8 @@ function scrLast() {
tag('br', null),
tag('input', {name:'scr_upload', id:'scr_upload', type:'file', 'class':'text'}),
tag('br', null),
+ rel,
+ tag('br', null),
tag('input', {type:'button', value:mt('_vnedit_scr_addbut'), 'class':'submit', onclick:scrUpload})
)
));
@@ -1081,7 +1093,9 @@ function scrDel(what) {
var tr = what && what.scr_status != null ? what : this;
while(tr.nodeName.toLowerCase() != 'tr')
tr = tr.parentNode;
- tr.scr_status = 3;
+ tr.scr_status = null;
+ if(tr.scr_upl && byId(tr.scr_upl))
+ byId(tr.scr_upl).parentNode.removeChild(byId(tr.scr_upl));
byId('scr_table').removeChild(tr);
scrSerialize();
scrLast();
@@ -1093,15 +1107,19 @@ function scrUpload() {
// create temporary form
var ifid = 'scr_upl_'+scrUplNr;
- var frm = tag('form', {method: 'post', action:'/xml/screenshots.xml', target: ifid, enctype:'multipart/form-data'});
+ var frm = tag('form', {method: 'post', action:'/xml/screenshots.xml?upload='+scrUplNr,
+ target: ifid, enctype:'multipart/form-data'});
var ifr = tag('iframe', {id:ifid, name:ifid, src:'about:blank', onload:scrUploadComplete});
addBody(tag('div', {'class':'scr_uploader'}, ifr, frm));
- // submit form and delete it
- frm.appendChild(byId('scr_upload'));
+ // submit form
+ var upl = byId('scr_upload');
+ upl.id = upl.name = 'scr_upl_file_'+scrUplNr;
+ frm.appendChild(upl);
frm.submit();
- frm.parentNode.removeChild(frm);
ifr.scr_tr = scrAdd(0, 0, 0);
+ ifr.scr_upl = ifid;
+ ifr.scr_tr.scr_rel = byId('scradd_relsel').options[byId('scradd_relsel').selectedIndex].value;
scrLast();
return false;
}
@@ -1113,29 +1131,29 @@ function scrUploadComplete() {
return;
var tr = ifr.scr_tr;
- if(!tr || tr.scr_status == 3)
- return;
-
- try {
- tr.scr_id = fr.window.document.getElementsByTagName('image')[0].getAttribute('id');
- } catch(e) {
- tr.scr_id = -10;
- }
- if(tr.scr_id < 0) {
- alert(mt(tr.scr_id == -10 ? '_vnedit_scr_oops' : tr.scr_id == -1 ? '_vnedit_scr_errformat' : '_vnedit_scr_errempty'));
- return scrDel(tr);
+ if(tr && tr.scr_status == 1) {
+ try {
+ tr.scr_id = fr.window.document.getElementsByTagName('image')[0].getAttribute('id');
+ } catch(e) {
+ tr.scr_id = -10;
+ }
+ if(tr.scr_id < 0) {
+ alert(mt(tr.scr_id == -10 ? '_vnedit_scr_oops' : tr.scr_id == -1 ? '_vnedit_scr_errformat' : '_vnedit_scr_errempty'));
+ scrDel(tr);
+ } else {
+ tr.id = 'scr_tr_'+tr.scr_id;
+ tr.scr_status = 2;
+ setContent(byName(tr, 'td')[1],
+ tag('b', mt('_vnedit_scr_genthumb')),
+ tag('br', null),
+ mt('_vnedit_scr_genthumb_msg')
+ );
+ }
}
- tr.id = 'scr_tr_'+tr.scr_id;
- tr.scr_status = 2;
- setContent(byName(tr, 'td')[1],
- tag('b', mt('_vnedit_scr_genthumb')),
- tag('br', null),
- mt('_vnedit_scr_genthumb_msg')
- );
-
- // remove the <div> in a timeout, otherwise some browsers think the page is still loading
- setTimeout(function() { ifr.parentNode.parentNode.removeChild(ifr.parentNode) }, 100);
+ tr.scr_upl = null;
+ /* remove the <div> in a timeout, otherwise some browsers think the page is still loading */
+ setTimeout(function() { ifr.parentNode.parentNode.removeChild(ifr.parentNode) }, 1000);
}
function scrSerialize() {
diff --git a/data/style.css b/data/style.css
index d6542477..7221459f 100644
--- a/data/style.css
+++ b/data/style.css
@@ -599,7 +599,7 @@ div.vnimg p {
.vndesc p {
padding: 0 0 0 5px;
}
-img#nsfw_hid {
+p#nsfw_hid {
display: none;
cursor: pointer;
}
@@ -746,6 +746,8 @@ a.addnew {
#jt_box_vn_rel td.tc_rel select { width: 130px; }
#ds_box {
+ position: absolute;
+ top: -500px;
border: 1px solid $border$;
border-top: none;
background-color: $secbg$;
@@ -1210,4 +1212,6 @@ svg .edge polygon.border { fill: $border$ }
svg .nodebg { fill: $tabbg$; stroke: $tabbg$ }
svg text { fill: $maintext$ }
svg .edge text { font: 8px "Tahoma" }
+#graph_current .border { stroke: $warnborder$ }
+#graph_current .nodebg { stroke: $warnborder$; fill: $warnbg$ }
diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index 8014f9be..b6100dfa 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -264,7 +264,7 @@ sub client_connect {
# the wheel
my $w = POE::Wheel::ReadWrite->new(
Handle => $sock,
- Filter => POE::Filter::VNDBAPI->new(type => 'server'),
+ Filter => POE::Filter::VNDBAPI->new(),
ErrorEvent => 'client_error',
InputEvent => 'client_input',
);
@@ -303,11 +303,8 @@ sub client_input {
# 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 $_[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
@@ -329,7 +326,10 @@ sub client_input {
# handle get command
if($cmd eq 'get') {
- my $opt = $arg->[3];
+ return cerr $c, parse => 'Invalid arguments to get command' if @$arg < 3 || @$arg > 4
+ || ref($arg->[0]) || ref($arg->[1]) || ref($arg->[2]) ne 'POE::Filter::VNDBAPI::filter'
+ || exists($arg->[3]) && ref($arg->[3]) ne 'HASH';
+ 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'
@@ -340,7 +340,7 @@ sub client_input {
$opt->{reverse} = defined($opt->{reverse}) && $opt->{reverse};
my %obj = (
c => $c,
- info => $arg->[1],
+ info => [ split /,/, $arg->[1] ],
filters => $arg->[2],
opt => $opt,
);
@@ -357,6 +357,8 @@ sub login {
my($c, $arg) = @_[ARG0,ARG1];
# validation (bah)
+ return cerr $c, parse => 'Argument to login must be a single JSON object' if @$arg != 1 || ref($arg->[0]) ne 'HASH';
+ $arg = $arg->[0];
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 => $_;
diff --git a/lib/Multi/Anime.pm b/lib/Multi/Anime.pm
index df2f6424..793cbe07 100644
--- a/lib/Multi/Anime.pm
+++ b/lib/Multi/Anime.pm
@@ -147,7 +147,6 @@ sub nextcmd {
clientver => $_[HEAP]{clientver},
enc => 'UTF-8',
);
- $_[KERNEL]->call(core => log => 'Authenticating with AniDB...');
}
# logged in, get anime
else {
@@ -156,7 +155,6 @@ sub nextcmd {
aid => $_[HEAP]{aid},
acode => 3973121, # aid, ANN id, NFO id, year, type, romaji, kanji
);
- $_[KERNEL]->call(core => log => 'Fetching info for a%d', $_[HEAP]{aid});
}
# send command
@@ -179,17 +177,14 @@ sub receivepacket { # input, wheelid
# parse message
my @r = split /\n/, $_[ARG0]{payload}[0];
my($tag, $code, $msg) = ($1, $2, $3) if $r[0] =~ /^([0-9]+) ([0-9]+) (.+)$/;
-
- # log
- $_[KERNEL]->call(core => log => 'Received from AniDB after %.2fs: %d %s',
- time-$_[HEAP]{lm}, $code, $msg);
+ my $time = time-$_[HEAP]{lm};
# tag incorrect, ignore message
- return $_[KERNEL]->call(core => log => 'Ignoring incorrect tag')
+ return $_[KERNEL]->call(core => log => 'Ignoring incorrect tag of message: %d %s', $code, $msg)
if $tag != $_[HEAP]{tag};
# unhandled code, ignore as well
- return $_[KERNEL]->call(core => log => 'Ignoring unhandled code')
+ return $_[KERNEL]->call(core => log => 'Ignoring unhandled code %d (%s)', $code, $msg)
if !grep $_ == $code, @handled_codes;
# at this point, we have a message we can handle, so disable the timeout
@@ -201,7 +196,7 @@ sub receivepacket { # input, wheelid
$_[HEAP]{tm}++;
my $delay = $_[HEAP]{msgdelay}**(1 + $_[HEAP]{tm}*$_[HEAP]{timeoutdelay});
$delay = $_[HEAP]{maxtimeoutdelay} if $delay > $_[HEAP]{maxtimeoutdelay};
- $_[KERNEL]->call(core => log => 'Delaying %.0fs.', $delay);
+ $_[KERNEL]->call(core => log => 'Reply timed out, delaying %.0fs.', $delay);
return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay});
}
@@ -211,12 +206,14 @@ sub receivepacket { # input, wheelid
# our session isn't valid, discard it and call nextcmd to get a new one
if($code == NOT_LOGGED_IN || $code == LOGIN_FIRST || $code == INVALID_SESSION) {
$_[HEAP]{s} = '';
+ $_[KERNEL]->call(core => log => 'Our session was invalid, logging in again...');
return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay});
}
# we received a session ID, call nextcmd again to fetch anime info
if($code == LOGIN_ACCEPTED || $code == LOGIN_ACCEPTED_NEW_VER) {
$_[HEAP]{s} = $1 if $msg =~ /^\s*([a-zA-Z0-9]{4,8}) /;
+ $_[KERNEL]->call(core => log => 'Successfully logged in to AniDB in %.2fs.', $time);
return $_[KERNEL]->delay(nextcmd => $_[HEAP]{msgdelay});
}
@@ -244,7 +241,7 @@ sub receivepacket { # input, wheelid
WHERE id = ?',
[ @col, $_[HEAP]{aid} ]
);
- $_[KERNEL]->call(core => log => 'Updated anime info for a%d', $_[HEAP]{aid});
+ $_[KERNEL]->call(core => log => 'Fetched anime info for a%d in %.2fs', $_[HEAP]{aid}, $time);
$_[KERNEL]->call(core => log => 'ERROR: a%d doesn\'t have a title or year!', $_[HEAP]{aid})
if !$col[3] || !$col[5];
}
diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm
index 0ea7ef29..864239dd 100644
--- a/lib/Multi/Maintenance.pm
+++ b/lib/Multi/Maintenance.pm
@@ -17,13 +17,13 @@ sub spawn {
package_states => [
$p => [qw|
_start shutdown set_daily daily set_monthly monthly log_stats
- vncache tagcache vnpopularity vnrating cleangraphs
- usercache statscache logrotate
+ vncache_inc tagcache vnpopularity vnrating cleangraphs
+ vncache_full usercache statscache logrotate
|],
],
heap => {
- daily => [qw|vncache tagcache vnpopularity vnrating cleangraphs|],
- monthly => [qw|usercache statscache logrotate|],
+ daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs|],
+ monthly => [qw|vncache_full usercache statscache logrotate|],
@_,
},
);
@@ -98,21 +98,31 @@ sub log_stats { # num, res, action, time
#
-sub vncache {
- # this takes about 40s to complete. We really need to search for an alternative
- # method of keeping the c_* columns in the vn table up-to-date.
- $_[KERNEL]->post(pg => do => 'SELECT update_vncache(0)', undef, 'log_stats', 'vncache');
+sub vncache_inc {
+ # takes about 50ms to 1s to complete, depending on how many
+ # releases have been released within the past 5 days
+ $_[KERNEL]->post(pg => do => q|
+ SELECT update_vncache(id)
+ FROM (
+ SELECT DISTINCT rv.vid
+ FROM releases r
+ JOIN releases_rev rr ON rr.id = r.latest
+ JOIN releases_vn rv ON rv.rid = r.latest
+ WHERE rr.released > TO_CHAR(NOW() - '5 days'::interval, 'YYYYMMDD')::integer
+ AND rr.released <= TO_CHAR(NOW(), 'YYYYMMDD')::integer
+ ) AS r(id)
+ |, undef, 'log_stats', 'vncache_inc');
}
sub tagcache {
- # takes about 18 seconds max. ouch, but still kind-of acceptable
+ # takes about 2 seconds max, still OK
$_[KERNEL]->post(pg => do => 'SELECT tag_vn_calc()', undef, 'log_stats', 'tagcache');
}
sub vnpopularity {
- # still takes at most 2 seconds. let's hope that doesn't increase...
+ # still takes at most 3 seconds. let's hope that doesn't increase...
$_[KERNEL]->post(pg => do => 'SELECT update_vnpopularity()', undef, 'log_stats', 'vnpopularity');
}
@@ -146,6 +156,13 @@ sub cleangraphs {
#
+sub vncache_full {
+ # this takes more than a minute to complete, and should only be necessary in the
+ # event that the daily vncache_inc cron hasn't been running for 5 subsequent days.
+ $_[KERNEL]->post(pg => do => 'SELECT update_vncache(id) FROM vn', undef, 'log_stats', 'vncache_full');
+}
+
+
sub usercache {
# Shouldn't really be necessary, except c_changes could be slightly off when hiding/unhiding DB items
# Currently takes about 25 seconds to complete.
diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm
index b3f9bb46..76408d92 100644
--- a/lib/Multi/RG.pm
+++ b/lib/Multi/RG.pm
@@ -158,7 +158,8 @@ sub savegraph {
$attr{class} = 'border' if $attr{stroke} && $attr{stroke} eq '#111111';
$attr{class} = 'nodebg' if $attr{fill} && $attr{fill} eq '#222222';
- delete @attr{qw|stroke fill id xmlns xmlns:xlink|};
+ delete @attr{qw|stroke fill xmlns xmlns:xlink|};
+ delete $attr{id} if $attr{id} && $attr{id} !~ /^node_[vp]\d+$/;
$el eq 'path' || $el eq 'polygon'
? $w->emptyTag("svg:$el", %attr)
: $w->startTag("svg:$el", %attr);
@@ -246,12 +247,12 @@ sub _vnnode {
$tooltip =~ s/"/\\"/g;
return sprintf
- qq|\tv%d [ URL = "/v%d", tooltip = "%s", label=<|.
+ qq|\tv%d [ id = "node_v%1\$d", URL = "/v%1\$d", tooltip = "%s", label=<|.
q|<TABLE CELLSPACING="0" CELLPADDING="1" BORDER="0" CELLBORDER="1" BGCOLOR="#222222">|.
q|<TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="2"><FONT POINT-SIZE="%d"> %s </FONT></TD></TR>|.
q|<TR><TD> %s </TD><TD> %s </TD></TR>|.
qq|</TABLE>> ]\n|,
- $_->{id}, $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($title), $date, $n->{lang}||'N/A';
+ $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($title), $date, $n->{lang}||'N/A';
}
@@ -298,12 +299,12 @@ sub _prodnode {
$tooltip =~ s/"/\\"/g;
return sprintf
- qq|\tp%d [ URL = "/p%d", tooltip = "%s", label=<|.
+ qq|\tp%d [ id = "node_p%1\$d", URL = "/p%1\$d", tooltip = "%s", label=<|.
q|<TABLE CELLSPACING="0" CELLPADDING="1" BORDER="0" CELLBORDER="1" BGCOLOR="#222222">|.
q|<TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="2"><FONT POINT-SIZE="%d"> %s </FONT></TD></TR>|.
q|<TR><TD ALIGN="CENTER"> $_lang_%s_$ </TD><TD ALIGN="CENTER"> $_ptype_%s_$ </TD></TR>|.
qq|</TABLE>> ]\n|,
- $_->{id}, $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($name), $n->{lang}, $n->{type};
+ $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($name), $n->{lang}, $n->{type};
}
diff --git a/lib/POE/Filter/VNDBAPI.pm b/lib/POE/Filter/VNDBAPI.pm
index 24188a2f..816643d9 100644
--- a/lib/POE/Filter/VNDBAPI.pm
+++ b/lib/POE/Filter/VNDBAPI.pm
@@ -1,35 +1,24 @@
# 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)
+# Mapping between the request/response data and perl data structure:
#
-# C: login <json-object>
-# [ 'login', {object} ]
+# <command> -> [ '<command>' ]
#
-# 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
+# <command> <arg1> <arg2> .. -> [ 'command', <arg1>, <arg2>, .. ]
#
-# S: ok
-# [ 'ok' ]
+# <arg>: <JSON-text> | <filter> | <unescaped-string>
#
-# S: results <json-object>
-# [ 'results', {object} ]
+# <JSON-text>: JSON object or array -> perl object or array
#
-# S: error <json-object>
-# [ 'error', {object} ]
-#
-# <filters>:
+# <filter>:
# string: ((<field> <op> <json-value>) <bool-op> (<field> <op> <json-value> ))
-# perl: [ [ 'field', 'op', value ], 'bool-op', [ 'field', 'op', value ] ]
+# perl: bless [ [ 'field', 'op', value ], 'bool-op', [ 'field', 'op', value ] ], 'POE::Filter::VNDBAPI::filter'
# <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'
+# <unescaped-string>: Any string not starting with (, [ or { and not containing
+# whitespace. In perl represented as a normal string.
#
# 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:
@@ -56,8 +45,6 @@ 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
@@ -65,19 +52,15 @@ 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;
+ my $b = '';
+ return bless \$b, $class;
}
sub clone {
my $self = shift;
- return bless {
- type => $self->{type},
- }, ref $self;
+ my $b = '';
+ return bless \$b, ref $self;
}
@@ -98,13 +81,13 @@ sub get {
sub get_one_start {
my($self, $data) = @_;
- $self->{buffer} .= join '', @$data;
+ $$self .= join '', @$data;
}
sub get_pending {
my $self = shift;
- return $self->{buffer} ne '' ? [ $self->{buffer} ] : undef;
+ return $$self ne '' ? [ $$self ] : undef;
}
@@ -113,53 +96,52 @@ sub _err($) { [ [ undef, { id => 'parse', msg => $_[0] } ] ] };
sub get_one {
my $self = shift;
# look for EOT
- my $end = index $self->{buffer}, $EOT;
+ my $end = index $$self, $EOT;
return [] if $end < 0;
- my $str = substr $self->{buffer}, 0, $end;
- $self->{buffer} = substr $self->{buffer}, $end+1;
+ my $str = substr $$self, 0, $end;
+ $$self = substr $$self, $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 ]];
- }
+ # get command
+ return _err "Invalid command" if !($str =~ s/^$WS*([a-z]+)$WS*//);
+ my @ret = ($1);
+
+ # parse arguments
+ while($str) {
+ $str =~ s/^$WS*//;
- # 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) {
+ # JSON text, starts with { or [
+ if($str =~ /^[\[{]/) {
+ my($value, $chars) = eval { JSON::XS->new->decode_prefix($str) };
+ if(!defined $chars) {
my $err = $@;
$err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//;
- return _err "JSON-decode: $err";
+ return _err "Invalid JSON value in filter expression: $err";
}
- return _err 'options argument must be a JSON object' if ref($options) ne 'HASH';
+ $str = substr $str, $chars;
+ push @ret, $value;
+ }
+
+ # filter expression, starts with (
+ elsif($str =~ /^\(/) {
+ my($value, $rest) = decode_filters($str);
+ return _err $value if !ref $value;
+ $str = $rest;
+ push @ret, bless $value, 'POE::Filter::VNDBAPI::filter';
}
- return [[ 'get', $type, [ split /,/, $info ], $filters, $options ]];
- }
- # S: ok
- if($self->{type} eq 'client' && $str =~ /^$WS*ok$WS*$/) {
- return [[ 'ok' ]];
+ # otherwise it's an unescaped string
+ else {
+ my ($value, $rest) = split /$WS+/, $str, 2;
+ $str = $rest;
+ push @ret, $value;
+ }
}
- # if we're here, we've received something strange
- return _err 'Invalid command or argument';
+ return [ \@ret ];
}
@@ -170,22 +152,13 @@ sub put {
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');
+ for (@$p) {
+ $cmd .= ' '.(
+ ref($_) eq 'POE::Filter::VNDBAPI::filter' ? encode_filters $_ :
+ ref($_) eq 'ARRAY' || ref($_) eq 'HASH' ? JSON::XS->new->encode($_) : $_
+ );
+ }
+ push @r, $cmd;
}
# the $EOT can also be passed through encode_utf8(), the result is the same.
return [ map encode_utf8($_).$EOT, @r ];
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 60487098..deeb7480 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -8,14 +8,14 @@ use Exporter 'import';
our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPostRead|;
-# Options: id, type, iid, results, page, what, notusers
+# Options: id, type, iid, results, page, what, notusers, sort, reverse
# What: boards, boardtitles, firstpost, lastpost
+# Sort: id lastpost
sub dbThreadGet {
my($self, %o) = @_;
$o{results} ||= 50;
$o{page} ||= 1;
$o{what} ||= '';
- $o{order} ||= 't.id DESC';
my %where = (
$o{id} ? (
@@ -49,13 +49,18 @@ sub dbThreadGet {
'JOIN threads_boards tb ON tb.tid = t.id' : (),
);
+ my $order = sprintf {
+ id => 't.id %s',
+ lastpost => 'tpl.date %s',
+ }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
+
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
FROM threads t
!s
!W
ORDER BY !s|,
- join(', ', @select), join(' ', @join), \%where, $o{order}
+ join(', ', @select), join(' ', @join), \%where, $order
);
if($o{what} =~ /(boards|boardtitles)/ && $#$r >= 0) {
@@ -153,14 +158,13 @@ sub dbThreadCount {
}
-# Options: tid, num, what, order, uid, mindate, hide, page, results
+# Options: tid, num, what, uid, mindate, hide, page, results
# what: user thread
sub dbPostGet {
my($self, %o) = @_;
$o{results} ||= 50;
$o{page} ||= 1;
$o{what} ||= '';
- $o{order} ||= 'tp.num ASC';
my %where = (
$o{tid} ? (
@@ -192,8 +196,8 @@ sub dbPostGet {
FROM threads_posts tp
!s
!W
- ORDER BY !s|,
- join(', ', @select), join(' ', @join), \%where, $o{order}
+ ORDER BY tp.num ASC|,
+ join(', ', @select), join(' ', @join), \%where
);
return wantarray ? ($r, $np) : $r;
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index e819a1a3..02df0a2b 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -6,7 +6,7 @@ use warnings;
use Exporter 'import';
our @EXPORT = qw|
- dbStats dbRevisionInsert dbItemInsert dbRevisionGet dbItemMod dbRandomQuote
+ dbStats dbItemEdit dbRevisionGet dbItemMod dbRandomQuote
|;
@@ -20,60 +20,23 @@ sub dbStats {
}
-# Inserts a new revision and updates the item to point to this revision
-# This function leaves the DB in an inconsistent state, the actual revision
-# will have to be inserted directly after calling this function, otherwise
-# the commit will fail.
-# Arguments: type [vrp], item ID, edit summary
-# Returns: local revision, global revision
-sub dbRevisionInsert {
- my($self, $type, $iid, $editsum, $uid) = @_;
-
- my $table = {qw|v vn r releases p producers|}->{$type};
-
- my $c = $self->dbRow(q|
- INSERT INTO changes (type, requester, ip, comments, rev)
- VALUES (?, ?, ?, ?, (
- SELECT c.rev+1
- FROM changes c
- JOIN !s_rev ir ON ir.id = c.id
- WHERE ir.!sid = ?
- ORDER BY c.id DESC
- LIMIT 1
- ))
- RETURNING id, rev|,
- $type, $uid||$self->authInfo->{id}, $self->reqIP, $editsum,
- $table, $type, $iid
- );
-
- $self->dbExec(q|UPDATE !s SET latest = ? WHERE id = ?|, $table, $c->{id}, $iid);
+# Inserts a new revision into the database
+# Arguments: type [vrp], revision id, %options->{ editsum uid + db[item]RevisionInsert }
+# revision id = changes.id of the revision this edit is based on, undef to create a new DB item
+# Returns: { iid, cid, rev }
+sub dbItemEdit {
+ my($self, $type, $oid, %o) = @_;
- return ($c->{rev}, $c->{id});
-}
+ my $fun = {qw|v vn r release p producer|}->{$type};
+ $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid);
+ $self->dbExec('UPDATE edit_revision SET requester = ?, ip = ?, comments = ?',
+ $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum});
+ $self->dbVNRevisionInsert( \%o) if $type eq 'v';
+ $self->dbProducerRevisionInsert(\%o) if $type eq 'p';
+ $self->dbReleaseRevisionInsert( \%o) if $type eq 'r';
-# Comparable to RevisionInsert, but creates a new item with a corresponding
-# change. Same things about inconsistent state, etc.
-# Argumments: type [vrp], edit summary, [uid]
-# Returns: item id, global revision
-sub dbItemInsert {
- my($self, $type, $editsum, $uid) = @_;
-
- my $cid = $self->dbRow(q|
- INSERT INTO changes (type, requester, ip, comments)
- VALUES (?, ?, ?, ?)
- RETURNING id|,
- $type, $uid||$self->authInfo->{id}, $self->reqIP, $editsum
- )->{id};
-
- my $iid = $self->dbRow(q|
- INSERT INTO !s (latest)
- VALUES (?)
- RETURNING id|,
- {qw|v vn r releases p producers|}->{$type}, $cid
- )->{id};
-
- return ($iid, $cid);
+ return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun);
}
@@ -127,7 +90,7 @@ sub dbRevisionGet {
);
my @select = (
- qw|c.id c.type c.requester c.comments c.rev c.causedby|,
+ qw|c.id c.type c.requester c.comments c.rev|,
q|extract('epoch' from c.added) as added|,
$o{what} =~ /user/ ? 'u.username' : (),
$o{what} =~ /item/ ? (
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index 0539634c..f32f70db 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -5,7 +5,7 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbProducerGet dbProducerEdit dbProducerAdd|;
+our @EXPORT = qw|dbProducerGet dbProducerRevisionInsert|;
# options: results, page, id, search, char, rev
@@ -100,42 +100,23 @@ sub dbProducerGet {
}
-# arguments: id, %options ->( editsum uid + insert_rev )
-# returns: ( local revision, global revision )
-sub dbProducerEdit {
- my($self, $pid, %o) = @_;
- my($rev, $cid) = $self->dbRevisionInsert('p', $pid, $o{editsum}, $o{uid});
- insert_rev($self, $cid, $pid, \%o);
- return ($rev, $cid);
-}
-
-
-# arguments: %options ->( editsum uid + insert_rev )
-# returns: ( item id, global revision )
-sub dbProducerAdd {
- my($self, %o) = @_;
- my($pid, $cid) = $self->dbItemInsert('p', $o{editsum}, $o{uid});
- insert_rev($self, $cid, $pid, \%o);
- return ($pid, $cid);
-}
+# Updates the edit_* tables, used from dbItemEdit()
+# Arguments: { columns in producers_rev + relations },
+sub dbProducerRevisionInsert {
+ my($self, $o) = @_;
+ my %set = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (),
+ qw|name original website l_wp type lang desc alias|;
+ $self->dbExec('UPDATE edit_producer !H', \%set) if keys %set;
-# helper function, inserts a producer revision
-# Arguments: global revision, item id, { columns in producers_rev }, relations
-sub insert_rev {
- my($self, $cid, $pid, $o) = @_;
- $self->dbExec(q|
- INSERT INTO producers_rev (id, pid, name, original, website, l_wp, type, lang, "desc", alias)
- VALUES (!l)|,
- [ $cid, $pid, @$o{qw| name original website l_wp type lang desc alias|} ]
- );
-
- $self->dbExec(q|
- INSERT INTO producers_relations (pid1, pid2, relation)
- VALUES (?, ?, ?)|,
- $cid, $_->[1], $_->[0]
- ) for (@{$o->{relations}});
+ if($o->{relations}) {
+ $self->dbExec('DELETE FROM edit_producer_relations');
+ my $q = join ',', map '(?,?)', @{$o->{relations}};
+ my @q = map +($_->[1], $_->[0]), @{$o->{relations}};
+ $self->dbExec("INSERT INTO edit_producer_relations (pid, relation) VALUES $q", @q) if @q;
+ }
}
1;
+
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index d7bddaab..a2f62a63 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -7,18 +7,18 @@ use POSIX 'strftime';
use Exporter 'import';
use VNDB::Func 'gtintype';
-our @EXPORT = qw|dbReleaseGet dbReleaseAdd dbReleaseEdit|;
+our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|;
-# Options: id vid rev order unreleased page results what date media
+# Options: id vid rev unreleased page results what date media sort reverse
# platforms languages type minage search resolutions freeware doujin
# What: extended changes vn producers platforms media
+# Sort: title released minage
sub dbReleaseGet {
my($self, %o) = @_;
$o{results} ||= 50;
$o{page} ||= 1;
$o{what} ||= '';
- $o{order} ||= 'rr.released ASC';
my @where = (
!$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (),
@@ -77,13 +77,19 @@ sub dbReleaseGet {
(qw|c.requester c.comments r.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (),
);
+ my $order = sprintf {
+ title => 'rr.title %s',
+ minage => 'rr.minage %s',
+ released => 'rr.released %s',
+ }->{ $o{sort}||'released' }, $o{reverse} ? 'DESC' : 'ASC';
+
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
FROM releases_rev rr
!s
!W
ORDER BY !s|,
- join(', ', @select), join(' ', @join), \@where, $o{order}
+ join(', ', @select), join(' ', @join), \@where, $order
);
if(@$r) {
@@ -150,67 +156,47 @@ sub dbReleaseGet {
}
-# arguments: id, %options ->( editsum uid + insert_rev )
-# returns: ( local revision, global revision )
-sub dbReleaseEdit {
- my($self, $rid, %o) = @_;
- my($rev, $cid) = $self->dbRevisionInsert('r', $rid, $o{editsum}, $o{uid});
- insert_rev($self, $cid, $rid, \%o);
- return ($rev, $cid);
-}
+# Updates the edit_* tables, used from dbItemEdit()
+# Arguments: { columns in releases_rev + languages + vn + producers + media + platforms }
+sub dbReleaseRevisionInsert {
+ my($self, $o) = @_;
+ my %set = map exists($o->{$_}) ? ("$_ = ?", $o->{$_}) : (),
+ qw|title original gtin catalog website released notes minage type
+ patch resolution voiced freeware doujin ani_story ani_ero|;
+ $self->dbExec('UPDATE edit_release !H', \%set) if keys %set;
-# arguments: %options ->( editsum uid + insert_rev )
-# returns: ( item id, global revision )
-sub dbReleaseAdd {
- my($self, %o) = @_;
- my($rid, $cid) = $self->dbItemInsert('r', $o{editsum}, $o{uid});
- insert_rev($self, $cid, $rid, \%o);
- return ($rid, $cid);
-}
+ if($o->{languages}) {
+ $self->dbExec('DELETE FROM edit_release_lang');
+ my $q = join ',', map '(?)', @{$o->{languages}};
+ $self->dbExec("INSERT INTO edit_release_lang (lang) VALUES $q", @{$o->{languages}}) if @{$o->{languages}};
+ }
+ if($o->{producers}) {
+ $self->dbExec('DELETE FROM edit_release_producers');
+ my $q = join ',', map '(?,?,?)', @{$o->{producers}};
+ my @q = map +($_->[0], $_->[1]?1:0, $_->[2]?1:0), @{$o->{producers}};
+ $self->dbExec("INSERT INTO edit_release_producers (pid, developer, publisher) VALUES $q", @q) if @q;
+ }
-# helper function, inserts a producer revision
-# Arguments: global revision, item id, { columns in releases_rev + languages + vn + producers + media + platforms }
-sub insert_rev {
- my($self, $cid, $rid, $o) = @_;
-
- $self->dbExec(q|
- INSERT INTO releases_rev (id, rid, title, original, gtin, catalog, website, released,
- notes, minage, type, patch, resolution, voiced, freeware, doujin, ani_story, ani_ero)
- VALUES (!l)|,
- [ $cid, $rid, @$o{qw| title original gtin catalog website released
- notes minage type patch resolution voiced freeware doujin ani_story ani_ero|} ]);
-
- $self->dbExec(q|
- INSERT INTO releases_lang (rid, lang)
- VALUES (?, ?)|,
- $cid, $_
- ) for (@{$o->{languages}});
-
- $self->dbExec(q|
- INSERT INTO releases_producers (rid, pid, developer, publisher)
- VALUES (?, ?, ?, ?)|,
- $cid, $_->[0], $_->[1]?1:0, $_->[2]?1:0
- ) for (@{$o->{producers}});
-
- $self->dbExec(q|
- INSERT INTO releases_platforms (rid, platform)
- VALUES (?, ?)|,
- $cid, $_
- ) for (@{$o->{platforms}});
-
- $self->dbExec(q|
- INSERT INTO releases_vn (rid, vid)
- VALUES (?, ?)|,
- $cid, $_
- ) for (@{$o->{vn}});
-
- $self->dbExec(q|
- INSERT INTO releases_media (rid, medium, qty)
- VALUES (?, ?, ?)|,
- $cid, $_->[0], $_->[1]
- ) for (@{$o->{media}});
+ if($o->{platforms}) {
+ $self->dbExec('DELETE FROM edit_release_platforms');
+ my $q = join ',', map '(?)', @{$o->{platforms}};
+ $self->dbExec("INSERT INTO edit_release_platforms (platform) VALUES $q", @{$o->{platforms}}) if @{$o->{platforms}};
+ }
+
+ if($o->{vn}) {
+ $self->dbExec('DELETE FROM edit_release_vn');
+ my $q = join ',', map '(?)', @{$o->{vn}};
+ $self->dbExec("INSERT INTO edit_release_vn (vid) VALUES $q", @{$o->{vn}}) if @{$o->{vn}};
+ }
+
+ if($o->{media}) {
+ $self->dbExec('DELETE FROM edit_release_media');
+ my $q = join ',', map '(?,?)', @{$o->{media}};
+ my @q = map +($_->[0], $_->[1]), @{$o->{media}};
+ $self->dbExec("INSERT INTO edit_release_media (medium, qty) VALUES $q", @q) if @q;
+ }
}
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 8cf4ee29..478cd91c 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -5,15 +5,15 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats dbTagVNs|;
+our @EXPORT = qw|dbTagGet dbTagTree dbTagEdit dbTagAdd dbTagMerge dbTagLinks dbTagLinkEdit dbTagStats|;
-# %options->{ id noid name search state meta page results order what }
+# %options->{ id noid name search state meta page results what sort reverse }
# what: parents childs(n) aliases addedby
+# sort: id name added vns
sub dbTagGet {
my $self = shift;
my %o = (
- order => 't.id ASC',
page => 1,
results => 10,
what => '',
@@ -45,13 +45,20 @@ sub dbTagGet {
);
my @join = $o{what} =~ /addedby/ ? 'JOIN users u ON u.id = t.addedby' : ();
+ my $order = sprintf {
+ id => 't.id %s',
+ name => 't.name %s',
+ added => 't.added %s',
+ vns => 't.c_vns %s',
+ }->{ $o{sort}||'id' }, $o{reverse} ? 'DESC' : 'ASC';
+
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
FROM tags t
!s
!W
ORDER BY !s|,
- join(', ', @select), join(' ', @join), \%where, $o{order}
+ join(', ', @select), join(' ', @join), \%where, $order
);
if(@$r && $o{what} =~ /aliases/) {
@@ -66,21 +73,46 @@ sub dbTagGet {
}
if($o{what} =~ /parents\((\d+)\)/) {
- $_->{parents} = $self->dbTagTree($_->{id}, $1, 0) for(@$r);
+ $_->{parents} = $self->dbTagTree($_->{id}, $1, 1) for(@$r);
}
if($o{what} =~ /childs\((\d+)\)/) {
- $_->{childs} = $self->dbTagTree($_->{id}, $1, 1) for(@$r);
+ $_->{childs} = $self->dbTagTree($_->{id}, $1) for(@$r);
}
return wantarray ? ($r, $np) : $r;
}
-# plain interface to the tag_tree() stored procedure in pgsql
+# Walks the tag tree
+# id = tag to start with, or 0 to start with top-level tags
+# lvl = max. recursion level
+# back = false for parent->child, true for child->parent
+# Returns: [ { id, name, c_vns, sub => [ { id, name, c_vns, sub => [..] }, .. ] }, .. ]
sub dbTagTree {
- my($self, $id, $lvl, $dir) = @_;
- return $self->dbAll('SELECT * FROM tag_tree(?, ?, ?)', $id, $lvl||0, $dir?1:0);
+ my($self, $id, $lvl, $back) = @_;
+ $lvl ||= 15;
+ my $r = $self->dbAll(q|
+ WITH RECURSIVE tagtree(lvl, id, parent, name, c_vns) AS (
+ SELECT ?::integer, id, 0, name, c_vns
+ FROM tags
+ !W
+ UNION ALL
+ SELECT tt.lvl-1, t.id, tt.id, t.name, t.c_vns
+ FROM tagtree tt
+ JOIN tags_parents tp ON !s
+ JOIN tags t ON !s
+ WHERE tt.lvl > 0
+ AND t.state = 2
+ ) SELECT id, parent, name, c_vns FROM tagtree ORDER BY name|, $lvl,
+ $id ? {'id = ?' => $id} : {'NOT EXISTS(SELECT 1 FROM tags_parents WHERE tag = id)' => 1, 'state = 2' => 1},
+ !$back ? ('tp.parent = tt.id', 't.id = tp.tag') : ('tp.tag = tt.id', 't.id = tp.parent')
+ );
+ for my $i (@$r) {
+ $i->{'sub'} = [ grep $_->{parent} == $i->{id}, @$r ];
+ }
+ my @r = grep !delete($_->{parent}), @$r;
+ return $id ? $r[0]{'sub'} : \@r;
}
@@ -147,13 +179,13 @@ sub dbTagLinkEdit {
# Fetch all tags related to a VN or User
-# Argument: %options->{ uid vid minrating results what page order }
+# Argument: %options->{ uid vid minrating results what page sort reverse }
# what: vns
+# sort: name, count, rating
sub dbTagStats {
my($self, %o) = @_;
$o{results} ||= 10;
$o{page} ||= 1;
- $o{order} ||= 't.name ASC';
$o{what} ||= '';
my %where = (
@@ -162,6 +194,13 @@ sub dbTagStats {
$o{vid} ? (
'tv.vid = ?' => $o{vid} ) : (),
);
+
+ my $order = sprintf {
+ name => 't.name %s',
+ count => 'count(*) %s',
+ rating => 'avg(tv.vote) %s',
+ }->{ $o{sort}||'name' }, $o{reverse} ? 'DESC' : 'ASC';
+
my($r, $np) = $self->dbPage(\%o, q|
SELECT t.id, t.name, count(*) as cnt, avg(tv.vote) as rating, COALESCE(avg(tv.spoiler), 0) as spoiler
FROM tags t
@@ -171,7 +210,7 @@ sub dbTagStats {
!s
ORDER BY !s|,
\%where, defined $o{minrating} ? "HAVING avg(tv.vote) > $o{minrating}" : '',
- $o{order}
+ $order
);
if(@$r && $o{what} =~ /vns/ && $o{uid}) {
@@ -196,31 +235,5 @@ sub dbTagStats {
}
-# Fetch all VNs from a tag, including VNs from child tags, and provide ratings for them.
-# Argument: %options->{ tag order page results maxspoil }
-sub dbTagVNs {
- my($self, %o) = @_;
- $o{order} ||= 'tb.rating DESC';
- $o{page} ||= 1;
- $o{results} ||= 10;
-
- my %where = (
- 'tag = ?' => $o{tag},
- defined $o{maxspoil} ? (
- 'tb.spoiler <= ?' => $o{maxspoil} ) : (),
- 'v.hidden = FALSE' => 1,
- );
-
- my($r, $np) = $self->dbPage(\%o, q|
- SELECT tb.tag, tb.vid, tb.users, tb.rating, tb.spoiler, vr.title, vr.original, v.c_languages, v.c_released, v.c_platforms, v.c_popularity
- FROM tags_vn_bayesian tb
- JOIN vn v ON v.id = tb.vid
- JOIN vn_rev vr ON vr.id = v.latest
- !W
- ORDER BY !s|,
- \%where, $o{order});
- return wantarray ? ($r, $np) : $r;
-}
-
1;
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 0f54686c..28c4d572 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -35,14 +35,14 @@ sub dbVNListGet {
}
-# %options->{ uid order char voted page results }
+# %options->{ uid char voted page results sort reverse }
+# sort: title vote
# NOTE: this function is mostly copied from 1.x, may need some rewriting...
sub dbVNListList {
my($self, %o) = @_;
$o{results} ||= 50;
$o{page} ||= 1;
- $o{order} ||= 'vr.title ASC';
$o{voted} ||= 0; # -1: only non-voted, 0: all, 1: only voted
# construct the global WHERE clause
@@ -58,6 +58,11 @@ sub dbVNListList {
$where = '('.$where.') AND (ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' if defined $o{char} && !$o{char};
$where = '('.$where.') AND vo.vote IS NULL' if $o{voted} == -1;
+ my $order = sprintf {
+ title => 'vr.title %s',
+ vote => 'vo.vote %s NULLS LAST, vr.title ASC',
+ }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC';
+
# execute query
my($r, $np) = $self->dbPage(\%o, qq|
SELECT vr.vid, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote
@@ -67,7 +72,7 @@ sub dbVNListList {
WHERE $where
ORDER BY !s|,
$o{voted} == 1 ? '' : 'LEFT', $o{uid}, # JOIN if we only want votes, LEFT JOIN if we also want rlist items
- $o{voted} != 1 ? $o{uid} : (), $o{order},
+ $o{voted} != 1 ? $o{uid} : (), $order
);
# fetch releases and link to VNs
@@ -142,11 +147,10 @@ sub dbVNListDel {
}
-# %options->{ uid vid hide order results page what }
+# %options->{ uid vid hide results page what }
# what: user, vn
sub dbVoteGet {
my($self, %o) = @_;
- $o{order} ||= 'n.date DESC';
$o{results} ||= 50;
$o{page} ||= 1;
$o{what} ||= '';
@@ -179,8 +183,8 @@ sub dbVoteGet {
FROM votes n
!s
!W
- ORDER BY !s|,
- join(',', @select), join(' ', @join), \%where, $o{order}
+ ORDER BY n.date DESC|,
+ join(',', @select), join(' ', @join), \%where
);
return wantarray ? ($r, $np) : $r;
@@ -234,12 +238,12 @@ sub dbVoteDel {
}
-# %options->{ uid vid wstat what order page results }
+# %options->{ uid vid wstat what page results sort reverse }
# what: vn
+# sort: title added wstat
sub dbWishListGet {
my($self, %o) = @_;
- $o{order} ||= 'wl.wstat ASC';
$o{page} ||= 1;
$o{results} ||= 50;
$o{what} ||= '';
@@ -258,13 +262,19 @@ sub dbWishListGet {
'JOIN vn_rev vr ON vr.id = v.latest';
}
+ my $order = sprintf {
+ title => 'vr.title %s',
+ added => 'wl.added %s',
+ wstat => 'wl.wstat %2$s, vr.title ASC',
+ }->{ $o{sort}||'added' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC';
+
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
FROM wlists wl
!s
!W
ORDER BY !s|,
- $select, join(' ', @join), \%where, $o{order},
+ $select, join(' ', @join), \%where, $order,
);
return wantarray ? ($r, $np) : $r;
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 39429a02..f8fdfe3f 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -8,12 +8,12 @@ use Exporter 'import';
our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|;
-# %options->{ username passwd mail session order uid ip registered search results page what }
+# %options->{ username passwd mail session uid ip registered search results page what sort reverse }
# what: stats extended
+# sort: username registered votes changes tags
sub dbUserGet {
my $s = shift;
my %o = (
- order => 'username ASC',
page => 1,
results => 10,
what => '',
@@ -65,13 +65,21 @@ sub dbUserGet {
$o{session} ? 'JOIN sessions s ON s.uid = u.id' : (),
);
+ my $order = sprintf {
+ username => 'u.username %s',
+ registered => 'u.registered %s',
+ votes => 'NOT u.show_list, u.c_votes %s',
+ changes => 'u.c_changes %s',
+ tags => 'u.c_tags %s',
+ }->{ $o{sort}||'username' }, $o{reverse} ? 'DESC' : 'ASC';
+
my($r, $np) = $s->dbPage(\%o, q|
SELECT !s
FROM users u
!s
!W
ORDER BY !s|,
- join(', ', @select), join(' ', @join), \%where, $o{order}
+ join(', ', @select), join(' ', @join), \%where, $order
);
return wantarray ? ($r, $np) : $r;
}
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 250f5267..b8cc0c42 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -7,16 +7,16 @@ use Exporter 'import';
use VNDB::Func 'gtintype';
use Encode 'decode_utf8';
-our @EXPORT = qw|dbVNGet dbVNAdd dbVNEdit dbVNImageId dbVNCache dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
+our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|;
-# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, order, what
+# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, what, sort, reverse
# What: extended anime relations screenshots relgraph rating ranking changes
+# Sort: id rel pop rating title tagscore rand
sub dbVNGet {
my($self, %o) = @_;
$o{results} ||= 10;
$o{page} ||= 1;
- $o{order} ||= 'vr.title ASC';
$o{what} ||= '';
my %where = (
@@ -33,11 +33,11 @@ sub dbVNGet {
$o{platform} && @{$o{platform}} ? (
'('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (),
$o{tags_include} && @{$o{tags_include}} ? (
- 'v.id IN(SELECT vid FROM tags_vn_bayesian WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
+ 'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
[ $o{tags_include}[1], $o{tags_include}[0], $#{$o{tags_include}[1]}+1 ]
) : (),
$o{tags_exclude} && @{$o{tags_exclude}} ? (
- 'v.id NOT IN(SELECT vid FROM tags_vn_bayesian WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (),
+ 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (),
# don't fetch hidden items unless we ask for an ID
!$o{id} && !$o{rev} ? (
'v.hidden = FALSE' => 0 ) : (),
@@ -86,24 +86,35 @@ sub dbVNGet {
$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|) : (),
+ qw|c.requester c.comments v.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
$o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
$o{what} =~ /ranking/ ? (
'(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > v.c_popularity) AS p_ranking',
'(SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > v.c_rating) AS r_ranking',
) : (),
+ # TODO: optimize this, as it will be very slow when the selected tags match a lot of VNs (>1000)
$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| : (),
+ qq|(SELECT AVG(tvh.rating) FROM tags_vn_inherit tvh WHERE tvh.tag IN($tag_ids) AND tvh.vid = v.id AND spoiler <= $o{tags_include}[0] GROUP BY tvh.vid) AS tagscore| : (),
);
+ my $order = sprintf {
+ id => 'id %s',
+ rel => 'c_released %s',
+ pop => 'c_popularity %s NULLS LAST',
+ rating => 'c_rating %s NULLS LAST',
+ title => 'title %s',
+ tagscore => 'tagscore %s',
+ rand => 'RANDOM()',
+ }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC';
+
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
FROM vn_rev vr
!s
!W
- ORDER BY !s NULLS LAST|,
- join(', ', @select), join(' ', @join), \%where, $o{order},
+ ORDER BY !s|,
+ join(', ', @select), join(' ', @join), \%where, $order,
);
if($o{what} =~ /relgraph/) {
@@ -160,57 +171,38 @@ sub dbVNGet {
}
-# arguments: id, %options ->( editsum uid + insert_rev )
-# returns: ( local revision, global revision )
-sub dbVNEdit {
- my($self, $id, %o) = @_;
- my($rev, $cid) = $self->dbRevisionInsert('v', $id, $o{editsum}, $o{uid});
- insert_rev($self, $cid, $id, \%o);
- return ($rev, $cid);
-}
-
-
-# arguments: %options ->( editsum uid + insert_rev )
-# returns: ( item id, global revision )
-sub dbVNAdd {
- my($self, %o) = @_;
- my($id, $cid) = $self->dbItemInsert('v', $o{editsum}, $o{uid});
- insert_rev($self, $cid, $id, \%o);
- return ($id, $cid);
-}
-
-
-# helper function, inserts a producer revision
-# Arguments: global revision, item id, { columns in producers_rev + anime + relations + screenshots }
+# Updates the edit_* tables, used from dbItemEdit()
+# Arguments: { columns in producers_rev + anime + relations + screenshots }
# screenshots = [ [ scrid, nsfw, rid ], .. ]
# relations = [ [ rel, vid ], .. ]
# anime = [ aid, .. ]
-sub insert_rev {
- my($self, $cid, $vid, $o) = @_;
-
- $o->{img_nsfw} = $o->{img_nsfw}?1:0;
- $self->dbExec(q|
- INSERT INTO vn_rev (id, vid, title, original, "desc", alias, image, img_nsfw, length, l_wp, l_encubed, l_renai, l_vnn)
- VALUES (!l)|,
- [ $cid, $vid, @$o{qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|} ]);
-
- $self->dbExec(q|
- INSERT INTO vn_screenshots (vid, scr, nsfw, rid)
- VALUES (?, ?, ?, ?)|,
- $cid, $_->[0], $_->[1]?1:0, $_->[2]
- ) for (@{$o->{screenshots}});
-
- $self->dbExec(q|
- INSERT INTO vn_relations (vid1, vid2, relation)
- VALUES (?, ?, ?)|,
- $cid, $_->[1], $_->[0]
- ) for (@{$o->{relations}});
-
- $self->dbExec(q|
- INSERT INTO vn_anime (vid, aid)
- VALUES (?, ?)|,
- $cid, $_
- ) for (@{$o->{anime}});
+sub dbVNRevisionInsert {
+ my($self, $o) = @_;
+
+ $o->{img_nsfw} = $o->{img_nsfw}?1:0 if exists $o->{img_nsfw};
+ my %set = map exists($o->{$_}) ? (qq|"$_" = ?| => $o->{$_}) : (),
+ qw|title original desc alias image img_nsfw length l_wp l_encubed l_renai l_vnn|;
+ $self->dbExec('UPDATE edit_vn !H', \%set) if keys %set;
+
+ if($o->{screenshots}) {
+ $self->dbExec('DELETE FROM edit_vn_screenshots');
+ my $q = join ',', map '(?, ?, ?)', @{$o->{screenshots}};
+ my @val = map +($_->[0], $_->[1]?1:0, $_->[2]), @{$o->{screenshots}};
+ $self->dbExec("INSERT INTO edit_vn_screenshots (scr, nsfw, rid) VALUES $q", @val) if @val;
+ }
+
+ if($o->{relations}) {
+ $self->dbExec('DELETE FROM edit_vn_relations');
+ my $q = join ',', map '(?, ?)', @{$o->{relations}};
+ my @val = map +($_->[1], $_->[0]), @{$o->{relations}};
+ $self->dbExec("INSERT INTO edit_vn_relations (vid, relation) VALUES $q", @val) if @val;
+ }
+
+ if($o->{anime}) {
+ $self->dbExec('DELETE FROM edit_vn_anime');
+ my $q = join ',', map '(?)', @{$o->{anime}};
+ $self->dbExec("INSERT INTO edit_vn_anime (aid) VALUES $q", @{$o->{anime}}) if @{$o->{anime}};
+ }
}
@@ -220,13 +212,6 @@ sub dbVNImageId {
}
-# Updates the vn.c_ columns
-sub dbVNCache {
- my($self, @vn) = @_;
- $self->dbExec('SELECT update_vncache(?)', $_) for (@vn);
-}
-
-
# insert a new screenshot and return it's ID
# (no arguments required, as Multi is responsible for filling the entry with information)
sub dbScreenshotAdd {
diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm
index 12b55029..fc477de2 100644
--- a/lib/VNDB/Handler/Discussions.pm
+++ b/lib/VNDB/Handler/Discussions.pm
@@ -295,7 +295,7 @@ sub board {
results => 50,
page => $f->{p},
what => 'firstpost lastpost boardtitles',
- order => $type eq 'an' ? 't.id DESC' : 'tpl.date DESC',
+ sort => $type eq 'an' ? 'id' : 'lastpost', reverse => 1,
);
$self->htmlHeader(title => $title, noindex => !@$list || $type eq 'u');
@@ -349,7 +349,7 @@ sub index {
results => 5,
page => 1,
what => 'firstpost lastpost boardtitles',
- order => 'tpl.date DESC',
+ sort => 'lastpost', reverse => 1,
);
h1 class => 'boxtitle';
a href => "/t/$_", mt "_dboard_$_";
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index d2f5852b..625b0463 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -78,7 +78,7 @@ sub homepage {
# Announcements
td;
- my $an = $self->dbThreadGet(type => 'an', order => 't.id DESC', results => 2);
+ my $an = $self->dbThreadGet(type => 'an', sort => 'id', reverse => 1, results => 2);
h1;
a href => '/t/an', mt '_home_announcements';
end;
@@ -98,7 +98,7 @@ sub homepage {
h1;
a href => '/t', mt '_home_recentposts';
end;
- my $posts = $self->dbThreadGet(what => 'lastpost boardtitles', results => 10, order => 'tpl.date DESC', notusers => 1);
+ my $posts = $self->dbThreadGet(what => 'lastpost boardtitles', results => 10, sort => 'lastpost', reverse => 1, notusers => 1);
ul;
for (@$posts) {
my $boards = join ', ', map mt("_dboard_$_->{type}").($_->{iid}?' > '.$_->{title}:''), @{$_->{boards}};
@@ -120,7 +120,7 @@ sub homepage {
h1;
a href => '/v/rand', mt '_home_randomvn';
end;
- my $random = $self->dbVNGet(results => 10, order => 'RANDOM()');
+ my $random = $self->dbVNGet(results => 10, sort => 'rand');
ul;
for (@$random) {
li;
@@ -142,6 +142,7 @@ sub homepage {
lit $self->{l10n}->datestr($_->{released});
txt ' ';
cssicon $_, mt "_plat_$_" for (@{$_->{platforms}});
+ cssicon "lang $_", mt "_lang_$_" for (@{$_->{languages}});
txt ' ';
a href => "/r$_->{id}", title => $_->{original}||$_->{title}, shorten $_->{title}, 30;
end;
@@ -154,13 +155,14 @@ sub homepage {
h1;
a href => strftime('/r?ma=%Y%m%d;o=d;s=released', gmtime), mt '_home_justreleased';
end;
- my $justrel = $self->dbReleaseGet(results => 10, order => 'rr.released DESC', unreleased => 0, what => 'platforms');
+ my $justrel = $self->dbReleaseGet(results => 10, sort => 'released', reverse => 1, unreleased => 0, what => 'platforms');
ul;
for (@$justrel) {
li;
lit $self->{l10n}->datestr($_->{released});
txt ' ';
cssicon $_, mt "_plat_$_" for (@{$_->{platforms}});
+ cssicon "lang $_", mt "_lang_$_" for (@{$_->{languages}});
txt ' ';
a href => "/r$_->{id}", title => $_->{original}||$_->{title}, shorten $_->{title}, 30;
end;
@@ -263,7 +265,7 @@ sub history {
}
end;
- $self->htmlHistory($list, $f, $np, $u->());
+ $self->htmlBrowseHist($list, $f, $np, $u->());
$self->htmlFooter;
}
@@ -300,7 +302,7 @@ sub docpage {
$ii;
}eg;
s{^:TOP5CONTRIB:$}{
- my $l = $self->dbUserGet(results => 6, order => 'c_changes DESC');
+ my $l = $self->dbUserGet(results => 6, sort => 'changes', reverse => 1);
'<dl>'.join('', map $_->{id} == 1 ? () :
sprintf('<dt><a href="/u%d">%s</a></dt><dd>%d</dd>', $_->{id}, $_->{username}, $_->{c_changes}),
@$l).'</dl>';
@@ -340,16 +342,11 @@ sub itemmod {
return $self->htmlDenied if !$self->authCan($act eq 'hide' ? 'del' : 'lock');
my $obj = $type eq 'v' ? $self->dbVNGet(id => $iid)->[0] :
- $type eq 'r' ? $self->dbReleaseGet(id => $iid, what => 'vn extended')->[0] :
+ $type eq 'r' ? $self->dbReleaseGet(id => $iid, what => 'extended')->[0] :
$self->dbProducerGet(id => $iid, what => 'extended')->[0];
return 404 if !$obj->{id};
$self->dbItemMod($type, $iid, $act eq 'hide' ? (hidden => !$obj->{hidden}) : (locked => !$obj->{locked}));
-
- # update cached vn info when hiding an r+ page
- $self->dbVNCache(map $_->{vid}, @{$obj->{vn}})
- if $type eq 'r' && $act eq 'hide';
-
$self->resRedirect("/$type$iid", 'temp');
}
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index e3473c3c..064a06bc 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -26,6 +26,7 @@ sub rg {
my $title = mt '_prodrg_title', $p->{name};
return if $self->htmlRGHeader($title, 'p', $p);
+ $p->{svg} =~ s/id="node_p$pid"/id="graph_current"/;
$p->{svg} =~ s/\$___(_prodrel_[a-z]+)____\$/mt $1/eg;
$p->{svg} =~ s/\$(_lang_[a-z]+)_\$/mt $1/eg;
$p->{svg} =~ s/\$(_ptype_[a-z]+)_\$/mt $1/eg;
@@ -63,14 +64,14 @@ sub page {
[ 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...
+ $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink'
}],
[ desc => diff => 1 ],
[ relations => join => '<br />', split => sub {
my @r = map sprintf('%s: <a href="/p%d" title="%s">%s</a>',
mt("_prodrel_$_->{relation}"), $_->{id}, xml_escape($_->{original}||$_->{name}), xml_escape shorten $_->{name}, 40
), sort { $a->{id} <=> $b->{id} } @{$_[0]};
- return @r ? @r : (mt '_proddiff_none');
+ return @r ? @r : (mt '_revision_empty');
}],
);
}
@@ -167,7 +168,7 @@ sub edit {
{ name => 'original', required => 0, maxlength => 200, default => '' },
{ name => 'alias', required => 0, maxlength => 500, default => '' },
{ name => 'lang', enum => $self->{languages} },
- { name => 'website', required => 0, template => 'url', default => '' },
+ { name => 'website', required => 0, maxlength => 250, default => '', template => 'url' },
{ name => 'l_wp', required => 0, maxlength => 150, default => '' },
{ name => 'desc', required => 0, maxlength => 5000, default => '' },
{ name => 'prodrelations', required => 0, maxlength => 5000, default => '' },
@@ -185,20 +186,16 @@ sub edit {
$frm->{relations} = $relations;
$frm->{l_wp} = undef if !$frm->{l_wp};
- $rev = 1;
- my $npid = $pid;
- my $cid;
- ($rev, $cid) = $self->dbProducerEdit($pid, %$frm) if $pid;
- ($npid, $cid) = $self->dbProducerAdd(%$frm) if !$pid;
+ my $nrev = $self->dbItemEdit(p => $pid ? $p->{cid} : undef, %$frm);
# update reverse relations
if(!$pid && $#$relations >= 0 || $pid && $frm->{prodrelations} ne $b4{prodrelations}) {
my %old = $pid ? (map { $_->{id} => $_->{relation} } @{$p->{relations}}) : ();
my %new = map { $_->[1] => $_->[0] } @$relations;
- _updreverse($self, \%old, \%new, $npid, $cid, $rev);
+ _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev});
}
- return $self->resRedirect("/p$npid.$rev", 'post');
+ return $self->resRedirect("/p$nrev->{iid}.$nrev->{rev}", 'post');
}
}
@@ -256,10 +253,8 @@ sub edit {
$self->htmlFooter;
}
-# !IMPORTANT!: Don't forget to update this function when
-# adding/removing fields to/from producer entries!
sub _updreverse {
- my($self, $old, $new, $pid, $cid, $rev) = @_;
+ my($self, $old, $new, $pid, $rev) = @_;
my %upd;
# compare %old and %new
@@ -270,20 +265,17 @@ sub _updreverse {
$upd{$_} = $self->{prod_relations}{$$new{$_}}[1];
}
}
-
return if !keys %upd;
# edit all related producers
for my $i (keys %upd) {
- my $r = $self->dbProducerGet(id => $i, what => 'extended relations')->[0];
+ my $r = $self->dbProducerGet(id => $i, what => 'relations')->[0];
my @newrel = map $_->{id} != $pid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}};
push @newrel, [ $upd{$i}, $pid ] if $upd{$i};
- $self->dbProducerEdit($i,
+ $self->dbItemEdit(p => $r->{cid},
relations => \@newrel,
editsum => "Reverse relation update caused by revision p$pid.$rev",
- causedby => $cid,
- uid => 1, # Multi - hardcoded
- ( map { $_ => $r->{$_} } qw|type name original lang website desc alias| )
+ uid => 1,
);
}
}
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index daeffb9e..498b9fa9 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -40,13 +40,13 @@ sub page {
map sprintf('<a href="/v%d" title="%s">%s</a>', $_->{vid}, $_->{original}||$_->{title}, shorten $_->{title}, 50), @{$_[0]};
} ],
[ type => serialize => sub { mt "_rtype_$_[0]" } ],
- [ patch => serialize => sub { $_[0] ? 'Patch' : 'Not a patch' } ],
- [ freeware => serialize => sub { $_[0] ? 'yes' : 'nope' } ],
- [ doujin => serialize => sub { $_[0] ? 'yups' : 'nope' } ],
+ [ patch => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ],
+ [ freeware => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ],
+ [ doujin => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ],
[ title => diff => 1 ],
[ original => diff => 1 ],
- [ gtin => serialize => sub { $_[0]||'[none]' } ],
- [ catalog => serialize => sub { $_[0]||'[none]' } ],
+ [ gtin => serialize => sub { $_[0]||mt '_revision_empty' } ],
+ [ catalog => serialize => sub { $_[0]||mt '_revision_empty' } ],
[ languages => join => ', ', split => sub { map mt("_lang_$_"), @{$_[0]} } ],
[ 'website' ],
[ released => htmlize => sub { $self->{l10n}->datestr($_[0]) } ],
@@ -310,7 +310,7 @@ sub edit {
func => [ \&gtintype, 'Not a valid JAN/UPC/EAN code' ] },
{ name => 'catalog', required => 0, default => '', maxlength => 50 },
{ name => 'languages', multi => 1, enum => $self->{languages} },
- { name => 'website', required => 0, default => '', template => 'url' },
+ { name => 'website', required => 0, default => '', maxlength => 250, template => 'url' },
{ name => 'released', required => 0, default => 0, template => 'int' },
{ name => 'minage' , required => 0, default => -1, enum => [map !defined($_)?-1:$_, @{$self->{age_ratings}}] },
{ name => 'notes', required => 0, default => '', maxlength => 10240 },
@@ -348,7 +348,7 @@ sub edit {
}
if(!$frm->{_err}) {
- my %opts = (
+ my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef,
(map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released
notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|),
minage => $frm->{minage} < 0 ? undef : $frm->{minage},
@@ -357,13 +357,7 @@ sub edit {
media => $media,
);
- $rev = 1;
- ($rev) = $self->dbReleaseEdit($rid, %opts) if !$copy && $rid;
- ($rid) = $self->dbReleaseAdd(%opts) if $copy || !$rid;
-
- $self->dbVNCache(@$new_vn, map $_->{vid}, @$vn);
-
- return $self->resRedirect("/r$rid.$rev", 'post');
+ return $self->resRedirect("/r$nrev->{iid}.$nrev->{rev}", 'post');
}
}
@@ -517,7 +511,7 @@ sub browse {
$f->{do} ? (doujin => $f->{do}) : (),
);
my($list, $np) = !@filters ? ([], 0) : $self->dbReleaseGet(
- order => $f->{s}.($f->{o}eq'd'?' DESC':' ASC'),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
page => $f->{p},
results => 50,
what => 'platforms',
diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm
index b28ba97a..df91a1c3 100644
--- a/lib/VNDB/Handler/Tags.pm
+++ b/lib/VNDB/Handler/Tags.pm
@@ -18,7 +18,6 @@ YAWF::register(
qr{u([1-9]\d*)/tags}, \&usertags,
qr{g}, \&tagindex,
qr{xml/tags\.xml}, \&tagxml,
- qr{g/debug}, \&tagtree,
);
@@ -29,7 +28,7 @@ sub tagpage {
return 404 if !$t;
my $f = $self->formValidate(
- { name => 's', required => 0, default => 'score', enum => [ qw|score title rel pop| ] },
+ { name => 's', required => 0, default => 'tagscore', enum => [ qw|title rel pop tagscore rating| ] },
{ name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] },
{ name => 'p', required => 0, default => 1, template => 'int' },
{ name => 'm', required => 0, default => -1, enum => [qw|0 1 2|] },
@@ -38,12 +37,12 @@ sub tagpage {
my $tagspoil = $self->reqCookie('tagspoil');
$f->{m} = $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0 if $f->{m} == -1;
- my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbTagVNs(
- tag => $tag,
- order => {score=>'tb.rating',title=>'vr.title',rel=>'v.c_released',pop=>'v.c_popularity'}->{$f->{s}}.($f->{o}eq'a'?' ASC':' DESC'),
- page => $f->{p},
+ my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbVNGet(
+ what => 'rating',
results => 50,
- maxspoil => $f->{m},
+ page => $f->{p},
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
+ tags_include => [ $f->{m}, [$tag ]],
);
my $title = mt '_tagp_title', $t->{meta}?0:1, $t->{name};
@@ -86,7 +85,7 @@ sub tagpage {
a href => '/g', mt '_tagp_indexlink';
for ($p[$_], reverse @r) {
txt ' > ';
- a href => "/g$_->{tag}", $_->{name};
+ a href => "/g$_->{id}", $_->{name};
}
txt " > $t->{name}\n";
}
@@ -111,7 +110,20 @@ sub tagpage {
end;
_childtags($self, $t) if @{$t->{childs}};
- _vnlist($self, $t, $f, $list, $np) if !$t->{meta} && $t->{state} == 2;
+
+ if(!$t->{meta} && $t->{state} == 2) {
+ div class => 'mainbox';
+ h1 mt '_tagp_vnlist';
+ p class => 'browseopts';
+ a href => "/g$t->{id}?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0';
+ a href => "/g$t->{id}?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1';
+ a href => "/g$t->{id}?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2';
+ end;
+ p "\n\n".mt '_tagp_novn' if !@$list;
+ p "\n".mt '_tagp_cached';
+ end;
+ $self->htmlBrowseVN($list, $f, $np, "/g$t->{id}?m=$f->{m}", 1) if @$list;
+ }
$self->htmlFooter;
}
@@ -120,38 +132,27 @@ sub tagpage {
sub _childtags {
my($self, $t, $index) = @_;
- my @l = @{$t->{childs}};
- my @tags;
- for (0..$#l) {
- if($l[$_]{lvl} == $l[0]{lvl}) {
- $l[$_]{childs} = [];
- push @tags, $l[$_];
- } else {
- push @{$tags[$#tags]{childs}}, $l[$_];
- }
- }
-
div class => 'mainbox';
h1 mt $index ? '_tagp_tree' : '_tagp_childs';
ul class => 'tagtree';
- for my $p (sort { @{$b->{childs}} <=> @{$a->{childs}} } @tags) {
+ for my $p (sort { @{$b->{'sub'}} <=> @{$a->{'sub'}} } @{$t->{childs}}) {
li;
- a href => "/g$p->{tag}", $p->{name};
+ a href => "/g$p->{id}", $p->{name};
b class => 'grayedout', " ($p->{c_vns})" if $p->{c_vns};
- end, next if !@{$p->{childs}};
+ end, next if !@{$p->{'sub'}};
ul;
- for (0..$#{$p->{childs}}) {
- last if $_ >= 5 && @{$p->{childs}} > 6;
+ for (0..$#{$p->{'sub'}}) {
+ last if $_ >= 5 && @{$p->{'sub'}} > 6;
li;
txt '> ';
- a href => "/g$p->{childs}[$_]{tag}", $p->{childs}[$_]{name};
- b class => 'grayedout', " ($p->{childs}[$_]{c_vns})" if $p->{childs}[$_]{c_vns};
+ a href => "/g$p->{sub}[$_]{id}", $p->{'sub'}[$_]{name};
+ b class => 'grayedout', " ($p->{sub}[$_]{c_vns})" if $p->{'sub'}[$_]{c_vns};
end;
}
- if(@{$p->{childs}} > 6) {
+ if(@{$p->{'sub'}} > 6) {
li;
txt '> ';
- a href => "/g$p->{tag}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{childs}}-5;
+ a href => "/g$p->{id}", style => 'font-style: italic', mt '_tagp_moretags', @{$p->{'sub'}}-5;
end;
}
end;
@@ -163,63 +164,6 @@ sub _childtags {
end;
}
-sub _vnlist {
- my($self, $t, $f, $list, $np) = @_;
- div class => 'mainbox';
- h1 mt '_tagp_vnlist';
- p class => 'browseopts';
- a href => "/g$t->{id}?m=0", $f->{m} == 0 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 0);return true;", mt '_tagp_spoil0';
- a href => "/g$t->{id}?m=1", $f->{m} == 1 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 1);return true;", mt '_tagp_spoil1';
- a href => "/g$t->{id}?m=2", $f->{m} == 2 ? (class => 'optselected') : (), onclick => "setCookie('tagspoil', 2);return true;", mt '_tagp_spoil2';
- end;
- if(!@$list) {
- p "\n\n".mt '_tagp_novn';
- }
- p "\n".mt '_tagp_cached';
- end;
- return if !@$list;
- $self->htmlBrowse(
- class => 'tagvnlist',
- items => $list,
- options => $f,
- nextpage => $np,
- pageurl => "/g$t->{id}?m=$f->{m};o=$f->{o};s=$f->{s}",
- sorturl => "/g$t->{id}?m=$f->{m}",
- header => [
- [ mt('_tagp_vncol_score'), 'score' ],
- [ mt('_tagp_vncol_title'), 'title' ],
- [ '', 0 ],
- [ '', 0 ],
- [ mt('_tagp_vncol_rel'), 'rel' ],
- [ mt('_tagp_vncol_pop'), 'pop' ],
- ],
- row => sub {
- my($s, $n, $l) = @_;
- Tr $n % 2 ? (class => 'odd') : ();
- td class => 'tc1';
- tagscore $l->{rating};
- i sprintf '(%d)', $l->{users};
- end;
- td class => 'tc2';
- a href => '/v'.$l->{vid}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100;
- end;
- td class => 'tc3';
- $_ ne 'oth' && cssicon $_, mt "_plat_$_"
- for (sort split /\//, $l->{c_platforms});
- end;
- td class => 'tc4';
- cssicon "lang $_", mt "_lang_$_"
- for (reverse sort split /\//, $l->{c_languages});
- end;
- td class => 'tc5';
- lit $self->{l10n}->datestr($l->{c_released});
- end;
- td class => 'tc6', sprintf '%.2f', ($l->{c_popularity}||0)*100;
- end;
- }
- );
-}
-
sub tagedit {
my($self, $tag, $act) = @_;
@@ -345,7 +289,7 @@ sub taglist {
return 404 if $f->{_err};
my($t, $np) = $self->dbTagGet(
- order => $f->{s}.($f->{o}eq'd'?' DESC':' ASC'),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
page => $f->{p},
results => 50,
state => $f->{t},
@@ -493,7 +437,7 @@ sub usertags {
return 404 if !$u;
my $f = $self->formValidate(
- { name => 's', required => 0, default => 'cnt', enum => [ qw|cnt name| ] },
+ { name => 's', required => 0, default => 'count', enum => [ qw|count name| ] },
{ name => 'o', required => 0, default => 'd', enum => [ 'a','d' ] },
{ name => 'p', required => 0, default => 1, template => 'int' },
);
@@ -503,7 +447,7 @@ sub usertags {
my($list, $np) = $self->dbTagStats(
uid => $uid,
page => $f->{p},
- order => ($f->{s}eq'cnt'?'COUNT(*)':'name').($f->{o}eq'a'?' ASC':' DESC'),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
what => 'vns',
);
@@ -533,8 +477,8 @@ sub usertags {
b id => 'expandall';
lit '<i>&#9656;</i> '.mt('_tagu_col_num').' ';
end;
- lit $f->{s} eq 'cnt' && $f->{o} eq 'a' ? "\x{25B4}" : qq|<a href="/u$u->{id}/tags?o=a;s=cnt">\x{25B4}</a>|;
- lit $f->{s} eq 'cnt' && $f->{o} eq 'd' ? "\x{25BE}" : qq|<a href="/u$u->{id}/tags?o=d;s=cnt">\x{25BE}</a>|;
+ lit $f->{s} eq 'count' && $f->{o} eq 'a' ? "\x{25B4}" : qq|<a href="/u$u->{id}/tags?o=a;s=count">\x{25B4}</a>|;
+ lit $f->{s} eq 'count' && $f->{o} eq 'd' ? "\x{25BE}" : qq|<a href="/u$u->{id}/tags?o=d;s=count">\x{25BE}</a>|;
end;
},
[ mt('_tagu_col_name'), 'name' ],
@@ -580,7 +524,7 @@ sub tagindex {
end;
end;
- my $t = $self->dbTagTree(0, 2, 1);
+ my $t = $self->dbTagTree(0, 2);
_childtags($self, {childs => $t}, 1);
table class => 'mainbox threelayout';
@@ -589,7 +533,7 @@ sub tagindex {
# Recently added
td;
a class => 'right', href => '/g/list', mt '_tagidx_browseall';
- my $r = $self->dbTagGet(order => 'added DESC', results => 10, state => 2);
+ my $r = $self->dbTagGet(sort => 'added', reverse => 1, results => 10, state => 2);
h1 mt '_tagidx_recent';
ul;
for (@$r) {
@@ -604,7 +548,7 @@ sub tagindex {
# Popular
td;
- $r = $self->dbTagGet(order => 'c_vns DESC', meta => 0, results => 10);
+ $r = $self->dbTagGet(sort => 'vns', reverse => 1, meta => 0, results => 10);
h1 mt '_tagidx_popular';
ul;
for (@$r) {
@@ -619,7 +563,7 @@ sub tagindex {
# Moderation queue
td;
h1 mt '_tagidx_queue';
- $r = $self->dbTagGet(state => 0, order => 'added DESC', results => 10);
+ $r = $self->dbTagGet(state => 0, sort => 'added', reverse => 1, results => 10);
ul;
li mt '_tagidx_queue_empty' if !@$r;
for (@$r) {
@@ -667,32 +611,4 @@ sub tagxml {
}
-sub tagtree {
- my $self = shift;
-
- return 404 if !$self->authCan('tagmod');
-
- $self->htmlHeader(title => '[DEBUG] The complete tag tree');
- div class => 'mainbox';
- h1 '[DEBUG] The complete tag tree';
-
- div style => 'margin-left: 10px';
- my $t = $self->dbTagTree(0, -1, 1);
- my $lvl = $t->[0]{lvl} + 1;
- for (@$t) {
- map ul(style => 'margin-left: 15px; list-style-type: none'), 1..($lvl-$_->{lvl}) if $lvl > $_->{lvl};
- map end, 1..($_->{lvl}-$lvl) if $lvl < $_->{lvl};
- $lvl = $_->{lvl};
- li;
- txt '> ';
- a href => "/g$_->{tag}", $_->{name};
- end;
- }
- map end, 0..($t->[0]{lvl}-$lvl);
- end;
- end;
- $self->htmlFooter;
-}
-
-
1;
diff --git a/lib/VNDB/Handler/ULists.pm b/lib/VNDB/Handler/ULists.pm
index 6a0d6c9e..9d6885ad 100644
--- a/lib/VNDB/Handler/ULists.pm
+++ b/lib/VNDB/Handler/ULists.pm
@@ -122,7 +122,7 @@ sub wishlist {
my($list, $np) = $self->dbWishListGet(
uid => $uid,
- order => $f->{s}.' '.($f->{o} eq 'a' ? ($f->{s} eq 'wstat' ? 'DESC' : 'ASC' ) : ($f->{s} eq 'wstat' ? 'ASC' : 'DESC')).($f->{s} eq 'wstat' ? ', title ASC' : ''),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
$f->{f} != -1 ? (wstat => $f->{f}) : (),
what => 'vn',
results => 50,
@@ -229,7 +229,7 @@ sub vnlist {
uid => $uid,
results => 50,
page => $f->{p},
- order => $f->{s}.' '.($f->{o} eq 'd' ? 'DESC' : 'ASC').($f->{s} eq 'vote' ? ', title ASC' : ''),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
voted => $f->{v},
$f->{c} ne 'all' ? (char => $f->{c}) : (),
);
diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm
index bf89b32d..e9c97198 100644
--- a/lib/VNDB/Handler/Users.pm
+++ b/lib/VNDB/Handler/Users.pm
@@ -117,7 +117,7 @@ sub userpage {
h1 class => 'boxtitle';
a href => "/u$uid/hist", mt '_userpage_changes';
end;
- $self->htmlHistory($list, { p => 1 }, 0, "/u$uid/hist");
+ $self->htmlBrowseHist($list, { p => 1 }, 0, "/u$uid/hist");
}
$self->htmlFooter;
}
@@ -474,7 +474,7 @@ sub list {
end;
my($list, $np) = $self->dbUserGet(
- order => ($f->{s} eq 'changes' || $f->{s} eq 'tags' ? 'c_' : $f->{s} eq 'votes' ? 'NOT show_list, c_' : '').$f->{s}.($f->{o} eq 'a' ? ' ASC' : ' DESC'),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
$char ne 'all' ? (
firstchar => $char ) : (),
results => 50,
diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm
index dbe80ac1..43e8b3cb 100644
--- a/lib/VNDB/Handler/VNBrowse.pm
+++ b/lib/VNDB/Handler/VNBrowse.pm
@@ -55,21 +55,13 @@ 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 => $sortcol.($f->{o} eq 'a' ? ' ASC' : ' DESC'),
+ sort => $f->{s}, reverse => $f->{o} eq 'd',
$f->{pl}[0] ? ( platform => $f->{pl} ) : (),
$f->{ln}[0] ? ( lang => $f->{ln} ) : (),
@ti ? (tags_include => [ $f->{sp}, \@ti ]) : (),
@@ -85,52 +77,7 @@ sub list {
my $url = "/v/$char?q=$f->{q};ti=$f->{ti};te=$f->{te}";
$_ and $url .= ";pl=$_" for @{$f->{pl}};
$_ and $url .= ";ln=$_" for @{$f->{ln}};
- $self->htmlBrowse(
- class => 'vnbrowse',
- items => $list,
- options => $f,
- nextpage => $np,
- pageurl => "$url;o=$f->{o};s=$f->{s}",
- sorturl => $url,
- header => [
- @ti ? [ mt('_vnbrowse_col_score'), 'tagscore', undef, 'tc_s' ] : (),
- [ mt('_vnbrowse_col_title'), 'title', undef, @ti ? 'tc_t' : 'tc1' ],
- [ '', 0, undef, 'tc2' ],
- [ '', 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) = @_;
- Tr $n % 2 ? (class => 'odd') : ();
- if(@ti) {
- td class => 'tc_s';
- tagscore $l->{tagscore}, 0;
- end;
- }
- td class => @ti ? 'tc_t' : 'tc1';
- a href => '/v'.$l->{id}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100;
- end;
- td class => 'tc2';
- $_ ne 'oth' && cssicon $_, mt "_plat_$_"
- for (sort split /\//, $l->{c_platforms});
- end;
- td class => 'tc3';
- cssicon "lang $_", mt "_lang_$_"
- for (reverse sort split /\//, $l->{c_languages});
- end;
- td class => 'tc4';
- lit $self->{l10n}->datestr($l->{c_released});
- end;
- 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;
- },
- );
+ $self->htmlBrowseVN($list, $f, $np, $url, scalar @ti);
$self->htmlFooter;
}
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index ed7068dc..d5dfd878 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -69,8 +69,8 @@ sub edit {
return $self->resRedirect("/v$vid", 'post')
if $vid && !$self->reqUploadFileName('img') && !grep $frm->{$_} ne $b4{$_}, keys %b4;
- # execute the edit/add
- my %args = (
+ # perform the edit/add
+ my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef,
(map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw|),
anime => [ keys %$anime ],
relations => $relations,
@@ -78,18 +78,14 @@ sub edit {
screenshots => $screenshots,
);
- my($nvid, $nrev, $cid) = ($vid, 1);
- ($nrev, $cid) = $self->dbVNEdit($vid, %args) if $vid;
- ($nvid, $cid) = $self->dbVNAdd(%args) if !$vid;
-
# update reverse relations & relation graph
if(!$vid && $#$relations >= 0 || $vid && $frm->{vnrelations} ne $b4{vnrelations}) {
my %old = $vid ? (map { $_->{id} => $_->{relation} } @{$v->{relations}}) : ();
my %new = map { $_->[1] => $_->[0] } @$relations;
- _updreverse($self, \%old, \%new, $nvid, $cid, $nrev);
+ _updreverse($self, \%old, \%new, $nrev->{iid}, $nrev->{rev});
}
- return $self->resRedirect("/v$nvid.$nrev", 'post');
+ return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post');
}
}
@@ -236,14 +232,12 @@ sub _form {
# Update reverse relations and regenerate relation graph
-# Arguments: %old. %new, vid, cid, rev
+# Arguments: %old. %new, vid, rev
# %old,%new -> { vid2 => relation, .. }
# from the perspective of vid
-# cid, rev are of the related edit
-# !IMPORTANT!: Don't forget to update this function when
-# adding/removing fields to/from VN entries!
+# rev is of the related edit
sub _updreverse {
- my($self, $old, $new, $vid, $cid, $rev) = @_;
+ my($self, $old, $new, $vid, $rev) = @_;
my %upd;
# compare %old and %new
@@ -254,22 +248,17 @@ sub _updreverse {
$upd{$_} = $self->{vn_relations}{$$new{$_}}[1];
}
}
-
return if !keys %upd;
# edit all related VNs
for my $i (keys %upd) {
- my $r = $self->dbVNGet(id => $i, what => 'extended relations anime screenshots')->[0];
+ my $r = $self->dbVNGet(id => $i, what => 'relations')->[0];
my @newrel = map $_->{id} != $vid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}};
push @newrel, [ $upd{$i}, $vid ] if $upd{$i};
- $self->dbVNEdit($i,
+ $self->dbItemEdit(v => $r->{cid},
relations => \@newrel,
editsum => "Reverse relation update caused by revision v$vid.$rev",
- causedby => $cid,
- uid => 1, # Multi - hardcoded
- anime => [ map $_->{id}, @{$r->{anime}} ],
- screenshots => [ map [ $_->{id}, $_->{nsfw}, $_->{rid} ], @{$r->{screenshots}} ],
- ( map { $_ => $r->{$_} } qw| title original desc alias img_nsfw length l_wp l_encubed l_renai l_vnn image | )
+ uid => 1, # Multi
);
}
}
@@ -321,8 +310,10 @@ sub scrxml {
}
# upload new screenshot
+ my $num = $self->formValidate({name => 'upload', template => 'int'});
+ return 404 if $num->{_err};
my $tmp = sprintf '%s/static/sf/00/tmp.%d.jpg', $VNDB::ROOT, $$*int(rand(1000)+1);
- $self->reqSaveUpload('scr_upload', $tmp);
+ $self->reqSaveUpload("scr_upl_file_$num->{upload}", $tmp);
my $id = 0;
$id = -2 if !-s $tmp;
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 2a333337..6c2e9b79 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -16,7 +16,7 @@ YAWF::register(
sub rand {
my $self = shift;
- $self->resRedirect('/v'.$self->dbVNGet(results => 1, order => 'RANDOM()')->[0]{id}, 'temp');
+ $self->resRedirect('/v'.$self->dbVNGet(results => 1, sort => 'rand')->[0]{id}, 'temp');
}
@@ -29,6 +29,7 @@ sub rg {
my $title = mt '_vnrg_title', $v->{title};
return if $self->htmlRGHeader($title, 'v', $v);
+ $v->{svg} =~ s/id="node_v$vid"/id="graph_current"/;
$v->{svg} =~ s/\$___(_vnrel_[a-z]+)____\$/mt $1/eg;
div class => 'mainbox';
@@ -72,16 +73,18 @@ sub page {
p mt '_vnpage_noimg';
} elsif($v->{image} < 0) {
p mt '_vnpage_imgproc';
- } elsif($v->{img_nsfw} && !$self->authInfo->{show_nsfw}) {
- img id => 'nsfw_hid', src => sprintf("%s/cv/%02d/%d.jpg", $self->{url_static}, $v->{image}%100, $v->{image}), alt => $v->{title};
- p id => 'nsfw_show';
- txt mt('_vnpage_imgnsfw_msg')."\n\n";
- a href => '#', mt '_vnpage_imgnsfw_show';
- txt "\n\n".mt '_vnpage_imgnsfw_note';
- end;
} else {
- img src => sprintf("%s/cv/%02d/%d.jpg", $self->{url_static}, $v->{image}%100, $v->{image}), alt => $v->{title};
- i mt '_vnpage_imgnsfw_foot' if $v->{img_nsfw} && $self->authInfo->{show_nsfw};
+ p $v->{img_nsfw} ? (id => 'nsfw_hid', style => $self->authInfo->{show_nsfw} ? 'display: block' : '') : ();
+ img src => sprintf("%s/cv/%02d/%d.jpg", $self->{url_static}, $v->{image}%100, $v->{image}), alt => $v->{title};
+ i mt '_vnpage_imgnsfw_foot' if $v->{img_nsfw};
+ end;
+ if($v->{img_nsfw}) {
+ p id => 'nsfw_show', $self->authInfo->{show_nsfw} ? (style => 'display: none') : ();
+ txt mt('_vnpage_imgnsfw_msg')."\n\n";
+ a href => '#', mt '_vnpage_imgnsfw_show';
+ txt "\n\n".mt '_vnpage_imgnsfw_note';
+ end;
+ }
}
end;
@@ -146,7 +149,7 @@ sub page {
clearfloat;
# tags
- my $t = $self->dbTagStats(vid => $v->{id}, order => 'avg(tv.vote) DESC', minrating => 0, results => 999);
+ my $t = $self->dbTagStats(vid => $v->{id}, sort => 'rating', reverse => 1, minrating => 0, results => 999);
if(@$t) {
div id => 'tagops';
# NOTE: order of these links is hardcoded in JS
@@ -191,40 +194,41 @@ sub _revision {
[ desc => diff => 1 ],
[ length => serialize => sub { mt '_vnlength_'.$_[0] } ],
[ l_wp => htmlize => sub {
- $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink'
+ $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink'
}],
[ l_encubed => htmlize => sub {
- $_[0] ? sprintf '<a href="http://novelnews.net/tag/%s/">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink'
+ $_[0] ? sprintf '<a href="http://novelnews.net/tag/%s/">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink'
}],
[ l_renai => htmlize => sub {
- $_[0] ? sprintf '<a href="http://renai.us/game/%s.shtml">%1$s</a>', xml_escape $_[0] : mt '_vndiff_nolink'
+ $_[0] ? sprintf '<a href="http://renai.us/game/%s.shtml">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink'
}],
[ relations => join => '<br />', split => sub {
my @r = map sprintf('%s: <a href="/v%d" title="%s">%s</a>',
mt("_vnrel_$_->{relation}"), $_->{id}, xml_escape($_->{original}||$_->{title}), xml_escape shorten $_->{title}, 40
), sort { $a->{id} <=> $b->{id} } @{$_[0]};
- return @r ? @r : (mt '_vndiff_none');
+ return @r ? @r : (mt '_revision_empty');
}],
[ anime => join => ', ', split => sub {
my @r = map sprintf('<a href="http://anidb.net/a%d">a%1$d</a>', $_->{id}), sort { $a->{id} <=> $b->{id} } @{$_[0]};
- return @r ? @r : (mt '_vndiff_none');
+ return @r ? @r : (mt '_revision_empty');
}],
[ screenshots => join => '<br />', split => sub {
my @r = map sprintf('[%s] <a href="%s/sf/%02d/%d.jpg" rel="iv:%dx%d">%4$d</a> (%s)',
$_->{rid} ? qq|<a href="/r$_->{rid}">r$_->{rid}</a>| : 'no release',
- $self->{url_static}, $_->{id}%100, $_->{id}, $_->{width}, $_->{height}, $_->{nsfw} ? 'NSFW' : 'Safe'
+ $self->{url_static}, $_->{id}%100, $_->{id}, $_->{width}, $_->{height},
+ mt($_->{nsfw} ? '_vndiff_nsfw_notsafe' : '_vndiff_nsfw_safe')
), @{$_[0]};
- return @r ? @r : (mt '_vndiff_none');
+ return @r ? @r : (mt '_revision_empty');
}],
[ image => htmlize => sub {
my $url = sprintf "%s/cv/%02d/%d.jpg", $self->{url_static}, $_[0]%100, $_[0];
if($_[0] > 0) {
- return $_[1]->{img_nsfw} && !$self->authInfo->{show_nsfw} ? "<a href=\"$url\">(NSFW)</a>" : "<img src=\"$url\" />";
+ return $_[1]->{img_nsfw} && !$self->authInfo->{show_nsfw} ? "<a href=\"$url\">".mt('_vndiff_image_nsfw').'</a>' : "<img src=\"$url\" />";
} else {
- return $_[0] < 0 ? '[processing]' : 'No image';
+ return mt $_[0] < 0 ? '_vndiff_image_proc' : '_vndiff_image_none';
}
}],
- [ img_nsfw => serialize => sub { $_[0] ? 'Not safe' : 'Safe' } ],
+ [ img_nsfw => serialize => sub { mt $_[0] ? '_vndiff_nsfw_notsafe' : '_vndiff_nsfw_safe' } ],
);
}
diff --git a/lib/VNDB/L10N.pm b/lib/VNDB/L10N.pm
index 8698cb74..9b9d2a82 100644
--- a/lib/VNDB/L10N.pm
+++ b/lib/VNDB/L10N.pm
@@ -73,12 +73,10 @@ use warnings;
}
# argument: unix timestamp and optional format (compact/full)
- # return value: yyyy-mm-dd
- # (maybe an idea to use cgit-style ages for recent timestamps)
sub date {
my($s, $t, $f) = @_;
- return strftime '%Y-%m-%d', gmtime $t if !$f || $f eq 'compact';
- return strftime '%Y-%m-%d at %R', gmtime $t;
+ return strftime $s->maketext('_datetime_compact'), gmtime $t if !$f || $f eq 'compact';
+ return strftime $s->maketext('_datetime_full'), gmtime $t;
}
# argument: database release date format (yyyymmdd)
diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm
index 0d510aa9..4859ea67 100644
--- a/lib/VNDB/Plugin/TransAdmin.pm
+++ b/lib/VNDB/Plugin/TransAdmin.pm
@@ -137,7 +137,7 @@ sub _readlang {
my $t = shift @$l;
if($t eq 'space') {
- if(join("\n", @$l) =~ /((#{30,90}\n)## +(.+) +##\n\2.+)^/ms) {
+ if(join("\n", @$l) =~ /((#{30,90}\n)## +(.+) +##\n\2.+)/ms) {
my $header = $1;
(my $title = $3) =~ s/\s+$//;
$title =~ s/\s+\([^)]+\)$//;
diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm
new file mode 100644
index 00000000..e9812f36
--- /dev/null
+++ b/lib/VNDB/Util/BrowseHTML.pm
@@ -0,0 +1,204 @@
+
+package VNDB::Util::BrowseHTML;
+
+use strict;
+use warnings;
+use YAWF ':html', 'xml_escape';
+use Exporter 'import';
+use VNDB::Func;
+
+
+our @EXPORT = qw| htmlBrowse htmlBrowseNavigate htmlBrowseHist htmlBrowseVN |;
+
+
+# generates a browse box, arguments:
+# items => arrayref with the list items
+# options => hashref containing at least the keys s (sort key), o (order) and p (page)
+# nextpage => whether there's a next page or not
+# sorturl => base URL to append the sort options to (if there are any sortable columns)
+# pageurl => base URL to append the page option to
+# class => classname of the mainbox
+# header =>
+# can be either an arrayref or subroutine reference,
+# in the case of a subroutine, it will be called when the header should be written,
+# in the case of an arrayref, the array should contain the header items. Each item
+# can again be either an arrayref or subroutine ref. The arrayref would consist of
+# two elements: the name of the header, and the name of the sorting column if it can
+# be sorted
+# row => subroutine ref, which is called for each item in $list, arguments will be
+# $self, $item_number (starting from 0), $item_value
+# footer => subroutine ref, called after all rows have been processed
+sub htmlBrowse {
+ my($self, %opt) = @_;
+
+ $opt{sorturl} .= $opt{sorturl} =~ /\?/ ? ';' : '?' if $opt{sorturl};
+
+ # top navigation
+ $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 't');
+
+ div class => 'mainbox browse'.($opt{class} ? ' '.$opt{class} : '');
+ table;
+
+ # header
+ thead;
+ Tr;
+ if(ref $opt{header} eq 'CODE') {
+ $opt{header}->($self);
+ } else {
+ for(0..$#{$opt{header}}) {
+ if(ref $opt{header}[$_] eq 'CODE') {
+ $opt{header}[$_]->($self, $_+1);
+ } else {
+ td class => $opt{header}[$_][3]||'tc'.($_+1), $opt{header}[$_][2] ? (colspan => $opt{header}[$_][2]) : ();
+ lit $opt{header}[$_][0];
+ if($opt{header}[$_][1]) {
+ lit ' ';
+ lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'a' ? "\x{25B4}" : qq|<a href="$opt{sorturl}o=a;s=$opt{header}[$_][1]">\x{25B4}</a>|;
+ lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'd' ? "\x{25BE}" : qq|<a href="$opt{sorturl}o=d;s=$opt{header}[$_][1]">\x{25BE}</a>|;
+ }
+ end;
+ }
+ }
+ }
+ end;
+ end;
+
+ # footer
+ if($opt{footer}) {
+ tfoot;
+ $opt{footer}->($self);
+ end;
+ }
+
+ # rows
+ $opt{row}->($self, $_+1, $opt{items}[$_])
+ for 0..$#{$opt{items}};
+
+ end;
+ end;
+
+ # bottom navigation
+ $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 'b');
+}
+
+
+# creates next/previous buttons (tabs), if needed
+# Arguments: page url, current page (1..n), nextpage (0/1), alignment (t/b), noappend (0/1)
+sub htmlBrowseNavigate {
+ my($self, $url, $p, $np, $al, $na) = @_;
+ return if $p == 1 && !$np;
+
+ $url .= $url =~ /\?/ ? ';p=' : '?p=' unless $na;
+ ul class => 'maintabs ' . ($al eq 't' ? 'notfirst' : 'bottom');
+ if($p > 1) {
+ li class => 'left';
+ a href => $url.($p-1), '<- '.mt '_browse_previous';
+ end;
+ }
+ if($np) {
+ li;
+ a href => $url.($p+1), mt('_browse_next').' ->';
+ end;
+ }
+ end;
+}
+
+
+sub htmlBrowseHist {
+ my($self, $list, $f, $np, $url) = @_;
+ $self->htmlBrowse(
+ items => $list,
+ options => $f,
+ nextpage => $np,
+ pageurl => $url,
+ class => 'history',
+ header => [
+ sub { td colspan => 2, class => 'tc1', mt '_hist_col_rev' },
+ [ mt '_hist_col_date' ],
+ [ mt '_hist_col_user' ],
+ sub { td; a href => '#', id => 'expandlist', mt '_js_expand'; txt mt '_hist_col_page'; end; }
+ ],
+ row => sub {
+ my($s, $n, $i) = @_;
+ my $revurl = "/$i->{type}$i->{iid}.$i->{rev}";
+
+ Tr $n % 2 ? ( class => 'odd' ) : ();
+ td class => 'tc1_1';
+ a href => $revurl, "$i->{type}$i->{iid}";
+ end;
+ td class => 'tc1_2';
+ a href => $revurl, ".$i->{rev}";
+ end;
+ td class => 'tc2', $self->{l10n}->date($i->{added});
+ td class => 'tc3';
+ lit $self->{l10n}->userstr($i);
+ end;
+ td;
+ a href => $revurl, title => $i->{ioriginal}, shorten $i->{ititle}, 80;
+ end;
+ end;
+ if($i->{comments}) {
+ Tr class => $n % 2 ? 'collapse msgsum odd hidden' : 'collapse msgsum hidden';
+ td colspan => 5;
+ lit bb2html $i->{comments}, 150;
+ end;
+ end;
+ }
+ },
+ );
+}
+
+
+sub htmlBrowseVN {
+ my($self, $list, $f, $np, $url, $tagscore) = @_;
+ $self->htmlBrowse(
+ class => 'vnbrowse',
+ items => $list,
+ options => $f,
+ nextpage => $np,
+ pageurl => "$url;o=$f->{o};s=$f->{s}",
+ sorturl => $url,
+ header => [
+ $tagscore ? [ mt('_vnbrowse_col_score'), 'tagscore', undef, 'tc_s' ] : (),
+ [ mt('_vnbrowse_col_title'), 'title', undef, $tagscore ? 'tc_t' : 'tc1' ],
+ [ '', 0, undef, 'tc2' ],
+ [ '', 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) = @_;
+ Tr $n % 2 ? (class => 'odd') : ();
+ if($tagscore) {
+ td class => 'tc_s';
+ tagscore $l->{tagscore}, 0;
+ end;
+ }
+ td class => $tagscore ? 'tc_t' : 'tc1';
+ a href => '/v'.$l->{id}, title => $l->{original}||$l->{title}, shorten $l->{title}, 100;
+ end;
+ td class => 'tc2';
+ $_ ne 'oth' && cssicon $_, mt "_plat_$_"
+ for (sort split /\//, $l->{c_platforms});
+ end;
+ td class => 'tc3';
+ cssicon "lang $_", mt "_lang_$_"
+ for (reverse sort split /\//, $l->{c_languages});
+ end;
+ td class => 'tc4';
+ lit $self->{l10n}->datestr($l->{c_released});
+ end;
+ 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;
+ },
+ );
+}
+
+
+1;
+
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index f39ffaaa..79330272 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -11,8 +11,8 @@ use Encode 'encode_utf8', 'decode_utf8';
use POSIX 'ceil';
our @EXPORT = qw|
- htmlMainTabs htmlDenied htmlHiddenMessage htmlBrowse htmlBrowseNavigate
- htmlRevision htmlEditMessage htmlItemMessage htmlVoteStats htmlHistory htmlSearchBox htmlRGHeader
+ htmlMainTabs htmlDenied htmlHiddenMessage htmlRevision
+ htmlEditMessage htmlItemMessage htmlVoteStats htmlSearchBox htmlRGHeader
|;
@@ -155,99 +155,6 @@ sub htmlHiddenMessage {
}
-# generates a browse box, arguments:
-# items => arrayref with the list items
-# options => hashref containing at least the keys s (sort key), o (order) and p (page)
-# nextpage => whether there's a next page or not
-# sorturl => base URL to append the sort options to (if there are any sortable columns)
-# pageurl => base URL to append the page option to
-# class => classname of the mainbox
-# header =>
-# can be either an arrayref or subroutine reference,
-# in the case of a subroutine, it will be called when the header should be written,
-# in the case of an arrayref, the array should contain the header items. Each item
-# can again be either an arrayref or subroutine ref. The arrayref would consist of
-# two elements: the name of the header, and the name of the sorting column if it can
-# be sorted
-# row => subroutine ref, which is called for each item in $list, arguments will be
-# $self, $item_number (starting from 0), $item_value
-# footer => subroutine ref, called after all rows have been processed
-sub htmlBrowse {
- my($self, %opt) = @_;
-
- $opt{sorturl} .= $opt{sorturl} =~ /\?/ ? ';' : '?' if $opt{sorturl};
-
- # top navigation
- $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 't');
-
- div class => 'mainbox browse'.($opt{class} ? ' '.$opt{class} : '');
- table;
-
- # header
- thead;
- Tr;
- if(ref $opt{header} eq 'CODE') {
- $opt{header}->($self);
- } else {
- for(0..$#{$opt{header}}) {
- if(ref $opt{header}[$_] eq 'CODE') {
- $opt{header}[$_]->($self, $_+1);
- } else {
- td class => $opt{header}[$_][3]||'tc'.($_+1), $opt{header}[$_][2] ? (colspan => $opt{header}[$_][2]) : ();
- lit $opt{header}[$_][0];
- if($opt{header}[$_][1]) {
- lit ' ';
- lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'a' ? "\x{25B4}" : qq|<a href="$opt{sorturl}o=a;s=$opt{header}[$_][1]">\x{25B4}</a>|;
- lit $opt{options}{s} eq $opt{header}[$_][1] && $opt{options}{o} eq 'd' ? "\x{25BE}" : qq|<a href="$opt{sorturl}o=d;s=$opt{header}[$_][1]">\x{25BE}</a>|;
- }
- end;
- }
- }
- }
- end;
- end;
-
- # footer
- if($opt{footer}) {
- tfoot;
- $opt{footer}->($self);
- end;
- }
-
- # rows
- $opt{row}->($self, $_+1, $opt{items}[$_])
- for 0..$#{$opt{items}};
-
- end;
- end;
-
- # bottom navigation
- $self->htmlBrowseNavigate($opt{pageurl}, $opt{options}{p}, $opt{nextpage}, 'b');
-}
-
-
-# creates next/previous buttons (tabs), if needed
-# Arguments: page url, current page (1..n), nextpage (0/1), alignment (t/b), noappend (0/1)
-sub htmlBrowseNavigate {
- my($self, $url, $p, $np, $al, $na) = @_;
- return if $p == 1 && !$np;
-
- $url .= $url =~ /\?/ ? ';p=' : '?p=' unless $na;
- ul class => 'maintabs ' . ($al eq 't' ? 'notfirst' : 'bottom');
- if($p > 1) {
- li class => 'left';
- a href => $url.($p-1), '<- '.mt '_browse_previous';
- end;
- }
- if($np) {
- li;
- a href => $url.($p+1), mt('_browse_next').' ->';
- end;
- }
- end;
-}
-
-
# Shows a revision, including diff if there is a previous revision.
# Arguments: v|p|r, old revision, new revision, @fields
# Where @fields is a list of fields as arrayrefs with:
@@ -341,8 +248,8 @@ sub revdiff {
# $i % 2 == 0 -> equal, otherwise it's different
my $a = join($o{join}, @ser1[ $d[$i*2] .. $d[$i*2+2]-1 ]);
my $b = join($o{join}, @ser2[ $d[$i*2+1] .. $d[$i*2+3]-1 ]);
- $ser1 .= ($ser1?$o{join}:'').($i % 2 ? qq|<b class="diff_del">$a</b>| : $a) if $a;
- $ser2 .= ($ser2?$o{join}:'').($i % 2 ? qq|<b class="diff_add">$b</b>| : $b) if $b;
+ $ser1 .= ($ser1?$o{join}:'').($i % 2 ? qq|<b class="diff_del">$a</b>| : $a) if $a ne '';
+ $ser2 .= ($ser2?$o{join}:'').($i % 2 ? qq|<b class="diff_add">$b</b>| : $b) if $b ne '';
}
$ser1 = decode_utf8($ser1);
$ser2 = decode_utf8($ser2);
@@ -351,8 +258,8 @@ sub revdiff {
$ser2 = xml_escape $ser2;
}
- $ser1 = mt '_revision_emptyfield' if !$ser1 && $ser1 ne '0';
- $ser2 = mt '_revision_emptyfield' if !$ser2 && $ser2 ne '0';
+ $ser1 = mt '_revision_empty' if !$ser1 && $ser1 ne '0';
+ $ser2 = mt '_revision_empty' if !$ser2 && $ser2 ne '0';
Tr $$i++ % 2 ? (class => 'odd') : ();
td mt "_revfield_${type}_$short";
@@ -375,7 +282,7 @@ sub htmlEditMessage {
div class => 'warning';
h2 mt '_editmsg_copy_title';
p;
- lit mt '_editmsg_copy_msg', sprintf '<a href="/%s%d">%s</a>', $type, $obj->{id}, xml_escape $obj->{title},
+ lit mt '_editmsg_copy_msg', sprintf '<a href="/%s%d">%s</a>', $type, $obj->{id}, xml_escape $obj->{title};
end;
end;
}
@@ -452,7 +359,6 @@ sub htmlVoteStats {
my $recent = $self->dbVoteGet(
$type.'id' => $obj->{id},
results => 8,
- order => 'date DESC',
what => $type eq 'v' ? 'user' : 'vn',
hide => $type eq 'v',
hide_ign => $type eq 'v',
@@ -490,51 +396,6 @@ sub htmlVoteStats {
}
-sub htmlHistory {
- my($self, $list, $f, $np, $url) = @_;
- $self->htmlBrowse(
- items => $list,
- options => $f,
- nextpage => $np,
- pageurl => $url,
- class => 'history',
- header => [
- sub { td colspan => 2, class => 'tc1', mt '_hist_col_rev' },
- [ mt '_hist_col_date' ],
- [ mt '_hist_col_user' ],
- sub { td; a href => '#', id => 'expandlist', mt '_js_expand'; txt mt '_hist_col_page'; end; }
- ],
- row => sub {
- my($s, $n, $i) = @_;
- my $revurl = "/$i->{type}$i->{iid}.$i->{rev}";
-
- Tr $n % 2 ? ( class => 'odd' ) : ();
- td class => 'tc1_1';
- a href => $revurl, "$i->{type}$i->{iid}";
- end;
- td class => 'tc1_2';
- a href => $revurl, ".$i->{rev}";
- end;
- td class => 'tc2', $self->{l10n}->date($i->{added});
- td class => 'tc3';
- lit $self->{l10n}->userstr($i);
- end;
- td;
- a href => $revurl, title => $i->{ioriginal}, shorten $i->{ititle}, 80;
- end;
- end;
- if($i->{comments}) {
- Tr class => $n % 2 ? 'collapse msgsum odd hidden' : 'collapse msgsum hidden';
- td colspan => 5;
- lit bb2html $i->{comments}, 150;
- end;
- end;
- }
- },
- );
-}
-
-
sub htmlSearchBox {
my($self, $sel, $v) = @_;
diff --git a/util/dbgraph.pl b/util/dbgraph.pl
index 0b72ceba..10297b66 100755
--- a/util/dbgraph.pl
+++ b/util/dbgraph.pl
@@ -2,7 +2,7 @@
# Generates a graphviz relation graph of the complete SQL database,
-# information is parsed from dump.sql (has to be in the 'current directory').
+# information is parsed from sql/schema.sql
# outputs the graph in dot format, usable as input to graphviz.
#
# Usage:
@@ -15,6 +15,10 @@ use strict;
use warnings;
+use Cwd 'abs_path';
+(my $ROOT = abs_path $0) =~ s{/util/dbgraph\.pl$}{};
+
+
my %subgraphs = (
'Producers' => [qw| FFFFCC producers producers_rev producers_relations |],
'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |],
@@ -29,7 +33,7 @@ my %tables; # table_name => [ [ col1, pri ], ... ]
my @rel; # 'table:col -- table:col', ...
sub parse_dump {
- open my $R, '<', 'dump.sql' or die $!;
+ open my $R, '<', "$ROOT/util/sql/schema.sql" or die $!;
my $in='';
while (<$R>) {
chomp;
diff --git a/util/dump.sql b/util/dump.sql
deleted file mode 100644
index e0af79e8..00000000
--- a/util/dump.sql
+++ /dev/null
@@ -1,851 +0,0 @@
-
--- plpgsql is required for our (trigger) functions
-CREATE LANGUAGE plpgsql;
-
-
--- data types
-
-CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
-CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
-CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
-CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
-CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
-CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
-
-
------------------------------------------
--- T A B L E D E F I N I T I O N S --
------------------------------------------
-
-
--- anime
-CREATE TABLE anime (
- id integer NOT NULL PRIMARY KEY,
- year smallint,
- ann_id integer,
- nfo_id varchar(200),
- type anime_type,
- title_romaji,
- title_kanji,
- lastfetch timestamptz
-);
-
--- changes
-CREATE TABLE changes (
- id SERIAL NOT NULL PRIMARY KEY,
- type dbentry_type NOT NULL,
- rev integer NOT NULL DEFAULT 1,
- added timestamptz NOT NULL DEFAULT NOW(),
- requester integer NOT NULL DEFAULT 0,
- ip inet NOT NULL DEFAULT '0.0.0.0',
- comments text NOT NULL DEFAULT '',
- causedby integer
-);
-
--- producers
-CREATE TABLE producers (
- id SERIAL NOT NULL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- rgraph integer
-);
-
--- producers_relations
-CREATE TABLE producers_relations (
- pid1 integer NOT NULL,
- pid2 integer NOT NULL,
- relation producer_relation NOT NULL,
- PRIMARY KEY(pid1, pid2)
-);
-
--- producers_rev
-CREATE TABLE producers_rev (
- id integer NOT NULL PRIMARY KEY,
- pid integer NOT NULL DEFAULT 0,
- type character(2) NOT NULL DEFAULT 'co',
- name varchar(200) NOT NULL DEFAULT '',
- original varchar(200) NOT NULL DEFAULT '',
- website varchar(250) NOT NULL DEFAULT '',
- lang varchar NOT NULL DEFAULT 'ja',
- "desc" text NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- l_wp varchar(150)
-);
-
--- quotes
-CREATE TABLE quotes (
- vid integer NOT NULL,
- quote varchar(250) NOT NULL,
- PRIMARY KEY(vid, quote)
-);
-
--- releases
-CREATE TABLE releases (
- id SERIAL NOT NULL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE
-);
-
--- releases_lang
-CREATE TABLE releases_lang (
- rid integer NOT NULL,
- lang varchar NOT NULL,
- PRIMARY KEY(rid, lang)
-);
-
--- releases_media
-CREATE TABLE releases_media (
- rid integer NOT NULL DEFAULT 0,
- medium medium NOT NULL,
- qty smallint NOT NULL DEFAULT 1,
- PRIMARY KEY(rid, medium, qty)
-);
-
--- releases_platforms
-CREATE TABLE releases_platforms (
- rid integer NOT NULL DEFAULT 0,
- platform character(3) NOT NULL DEFAULT 0,
- PRIMARY KEY(rid, platform)
-);
-
--- releases_producers
-CREATE TABLE releases_producers (
- rid integer NOT NULL,
- pid integer NOT NULL,
- developer boolean NOT NULL DEFAULT FALSE,
- publisher boolean NOT NULL DEFAULT TRUE,
- CHECK(developer OR publisher),
- PRIMARY KEY(pid, rid)
-);
-
--- releases_rev
-CREATE TABLE releases_rev (
- id integer NOT NULL PRIMARY KEY,
- rid integer NOT NULL DEFAULT 0,
- title varchar(250) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
- type release_type NOT NULL DEFAULT 'complete',
- website varchar(250) NOT NULL DEFAULT '',
- released integer NOT NULL,
- notes text NOT NULL DEFAULT '',
- minage smallint,
- gtin bigint NOT NULL DEFAULT 0,
- patch boolean NOT NULL DEFAULT FALSE,
- catalog varchar(50) NOT NULL DEFAULT '',
- resolution smallint NOT NULL DEFAULT 0,
- voiced smallint NOT NULL DEFAULT 0,
- freeware boolean NOT NULL DEFAULT FALSE,
- doujin boolean NOT NULL DEFAULT FALSE,
- ani_story smallint NOT NULL DEFAULT 0,
- ani_ero smallint NOT NULL DEFAULT 0
-);
-
--- releases_vn
-CREATE TABLE releases_vn (
- rid integer NOT NULL DEFAULT 0,
- vid integer NOT NULL DEFAULT 0,
- PRIMARY KEY(rid, vid)
-);
-
--- relgraphs
-CREATE TABLE relgraphs (
- id SERIAL PRIMARY KEY,
- svg xml NOT NULL
-);
-
--- rlists
-CREATE TABLE rlists (
- uid integer NOT NULL DEFAULT 0,
- rid integer NOT NULL DEFAULT 0,
- vstat smallint NOT NULL DEFAULT 0,
- rstat smallint NOT NULL DEFAULT 0,
- added timestamptz NOT NULL DEFAULT NOW(),
- PRIMARY KEY(uid, rid)
-);
-
--- screenshots
-CREATE TABLE screenshots (
- id SERIAL NOT NULL PRIMARY KEY,
- processed boolean NOT NULL DEFAULT FALSE,
- width smallint NOT NULL DEFAULT 0,
- height smallint NOT NULL DEFAULT 0
-);
-
--- sessions
-CREATE TABLE sessions (
- uid integer NOT NULL,
- token bytea NOT NULL,
- expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval),
- PRIMARY KEY (uid, token)
-);
-
--- stats_cache
-CREATE TABLE stats_cache (
- section varchar(25) NOT NULL PRIMARY KEY,
- count integer NOT NULL DEFAULT 0
-);
-
--- tags
-CREATE TABLE tags (
- id SERIAL NOT NULL PRIMARY KEY,
- name varchar(250) NOT NULL UNIQUE,
- description text NOT NULL DEFAULT '',
- meta boolean NOT NULL DEFAULT FALSE,
- added timestamptz NOT NULL DEFAULT NOW(),
- state smallint NOT NULL DEFAULT 0,
- c_vns integer NOT NULL DEFAULT 0,
- addedby integer NOT NULL DEFAULT 1
-);
-
--- tags_aliases
-CREATE TABLE tags_aliases (
- alias varchar(250) NOT NULL PRIMARY KEY,
- tag integer NOT NULL,
-);
-
--- tags_parents
-CREATE TABLE tags_parents (
- tag integer NOT NULL,
- parent integer NOT NULL,
- PRIMARY KEY(tag, parent)
-);
-
--- tags_vn
-CREATE TABLE tags_vn (
- tag integer NOT NULL,
- vid integer NOT NULL,
- uid integer NOT NULL,
- vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
- spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
- PRIMARY KEY(tag, vid, uid)
-);
-
--- tags_vn_bayesian
-CREATE TABLE tags_vn_bayesian (
- tag integer NOT NULL,
- vid integer NOT NULL,
- users integer NOT NULL,
- rating real NOT NULL,
- spoiler smallint NOT NULL
-);
-
--- threads
-CREATE TABLE threads (
- id SERIAL NOT NULL PRIMARY KEY,
- title varchar(50) NOT NULL DEFAULT '',
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- count smallint NOT NULL DEFAULT 0
-);
-
--- threads_posts
-CREATE TABLE threads_posts (
- tid integer NOT NULL DEFAULT 0,
- num smallint NOT NULL DEFAULT 0,
- uid integer NOT NULL DEFAULT 0,
- date timestamptz NOT NULL DEFAULT NOW(),
- edited timestamptz,
- msg text NOT NULL DEFAULT '',
- hidden boolean NOT NULL DEFAULT FALSE,
- PRIMARY KEY(tid, num)
-);
-
--- threads_boards
-CREATE TABLE threads_boards (
- tid integer NOT NULL DEFAULT 0,
- type character(2) NOT NULL DEFAULT 0,
- iid integer NOT NULL DEFAULT 0,
- lastread smallint NOT NULL,
- PRIMARY KEY(tid, type, iid)
-);
-
--- users
-CREATE TABLE users (
- id SERIAL NOT NULL PRIMARY KEY,
- username varchar(20) NOT NULL UNIQUE,
- mail varchar(100) NOT NULL,
- rank smallint NOT NULL DEFAULT 3,
- passwd bytea NOT NULL DEFAULT '',
- registered timestamptz NOT NULL DEFAULT NOW(),
- show_nsfw boolean NOT NULL DEFAULT FALSE,
- show_list boolean NOT NULL DEFAULT TRUE,
- c_votes integer NOT NULL DEFAULT 0,
- c_changes integer NOT NULL DEFAULT 0,
- skin varchar(128) NOT NULL DEFAULT '',
- customcss text NOT NULL DEFAULT '',
- ip inet NOT NULL DEFAULT '0.0.0.0',
- c_tags integer NOT NULL DEFAULT 0,
- salt character(9) NOT NULL DEFAULT '',
- ign_votes voolean NOT NULL DEFAULT FALSE
-);
-
--- vn
-CREATE TABLE vn (
- id SERIAL NOT NULL PRIMARY KEY,
- latest integer NOT NULL DEFAULT 0,
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- rgraph integer,
- c_released integer NOT NULL DEFAULT 0,
- c_languages varchar(32) NOT NULL DEFAULT '',
- c_platforms varchar(32) NOT NULL DEFAULT '',
- c_popularity real,
- c_rating real,
- c_votecount integer NOT NULL DEFAULT 0
-);
-
--- vn_anime
-CREATE TABLE vn_anime (
- vid integer NOT NULL,
- aid integer NOT NULL,
- PRIMARY KEY(vid, aid)
-);
-
--- vn_relations
-CREATE TABLE vn_relations (
- vid1 integer NOT NULL DEFAULT 0,
- vid2 integer NOT NULL DEFAULT 0,
- relation vn_relation NOT NULL,
- PRIMARY KEY(vid1, vid2)
-);
-
--- vn_rev
-CREATE TABLE vn_rev (
- id integer NOT NULL PRIMARY KEY,
- vid integer NOT NULL DEFAULT 0,
- title varchar(250) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- img_nsfw boolean NOT NULL DEFAULT FALSE,
- length smallint NOT NULL DEFAULT 0,
- "desc" text NOT NULL DEFAULT '',
- l_wp varchar(150) NOT NULL DEFAULT '',
- l_vnn integer NOT NULL DEFAULT 0,
- image integer NOT NULL DEFAULT 0,
- l_encubed varchar(100) NOT NULL DEFAULT '',
- l_renai varchar(100) NOT NULL DEFAULT ''
-);
-
--- vn_screenshots
-CREATE TABLE vn_screenshots (
- vid integer NOT NULL DEFAULT 0,
- scr integer NOT NULL DEFAULT 0,
- nsfw boolean NOT NULL DEFAULT FALSE,
- rid integer DEFAULT NULL,
- PRIMARY KEY(vid, scr)
-);
-
--- votes
-CREATE TABLE votes (
- vid integer NOT NULL DEFAULT 0,
- uid integer NOT NULL DEFAULT 0,
- vote integer NOT NULL DEFAULT 0,
- date timestamptz NOT NULL DEFAULT NOW(),
- PRIMARY KEY(vid, uid)
-);
-
--- wlists
-CREATE TABLE wlists (
- uid integer NOT NULL DEFAULT 0,
- vid integer NOT NULL DEFAULT 0,
- wstat smallint NOT NULL DEFAULT 0,
- added timestamptz NOT NULL DEFAULT NOW(),
- PRIMARY KEY(uid, vid)
-);
-
-
-
-
-
------------------------------------------------
--- F O R E I G N K E Y C H E C K I N G --
------------------------------------------------
-
-
-ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id);
-ALTER TABLE changes ADD FOREIGN KEY (causedby) REFERENCES changes (id);
-ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id);
-ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id);
-ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
-ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id);
-ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id);
-ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id);
-ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id);
-ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id);
-ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
-ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);
-ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id);
-ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
-ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
-ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id);
-ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id);
-ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
-
-
-
-
-
-
--------------------------
--- F U N C T I O N S --
--------------------------
-
-
--- update_vncache(id) - updates the c_* columns in the vn table
-CREATE FUNCTION update_vncache(id integer) RETURNS void AS $$
-DECLARE
- w text := '';
-BEGIN
- IF id > 0 THEN
- w := ' WHERE id = '||id;
- END IF;
- EXECUTE 'UPDATE vn SET
- c_released = COALESCE((SELECT
- MIN(rr1.released)
- FROM releases_rev rr1
- JOIN releases r1 ON rr1.id = r1.latest
- JOIN releases_vn rv1 ON rr1.id = rv1.rid
- WHERE rv1.vid = vn.id
- AND rr1.type <> ''trial''
- AND r1.hidden = FALSE
- AND rr1.released <> 0
- GROUP BY rv1.vid
- ), 0),
- c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
- SELECT rl2.lang
- FROM releases_rev rr2
- JOIN releases_lang rl2 ON rl2.rid = rr2.id
- JOIN releases r2 ON rr2.id = r2.latest
- JOIN releases_vn rv2 ON rr2.id = rv2.rid
- WHERE rv2.vid = vn.id
- AND rr2.type <> ''trial''
- AND rr2.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
- AND r2.hidden = FALSE
- GROUP BY rl2.lang
- ORDER BY rl2.lang
- ), ''/''), ''''),
- c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
- SELECT rp3.platform
- FROM releases_platforms rp3
- JOIN releases_rev rr3 ON rp3.rid = rr3.id
- JOIN releases r3 ON rp3.rid = r3.latest
- JOIN releases_vn rv3 ON rp3.rid = rv3.rid
- WHERE rv3.vid = vn.id
- AND rr3.type <> ''trial''
- AND rr3.released <= TO_CHAR(''today''::timestamp, ''YYYYMMDD'')::integer
- AND r3.hidden = FALSE
- GROUP BY rp3.platform
- ORDER BY rp3.platform
- ), ''/''), '''')
- '||w;
-END;
-$$ LANGUAGE plpgsql;
-
-
--- recalculate vn.c_popularity
-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;
-
-
--- tag: tag to start with,
--- lvl: recursion level
--- dir: direction, true = parent->child, false = child->parent
-CREATE TYPE tag_tree_item AS (lvl smallint, tag integer, name text, c_vns integer);
-CREATE OR REPLACE FUNCTION tag_tree(tag integer, lvl integer, dir boolean) RETURNS SETOF tag_tree_item AS $$
-DECLARE
- r tag_tree_item%rowtype;
- r2 tag_tree_item%rowtype;
-BEGIN
- IF dir AND tag = 0 THEN
- FOR r IN
- SELECT lvl, t.id, t.name, t.c_vns
- FROM tags t
- WHERE state = 2 AND NOT EXISTS(SELECT 1 FROM tags_parents tp WHERE tp.tag = t.id)
- ORDER BY t.name
- LOOP
- RETURN NEXT r;
- IF lvl-1 <> 0 THEN
- FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
- RETURN NEXT r2;
- END LOOP;
- END IF;
- END LOOP;
- ELSIF dir THEN
- FOR r IN
- SELECT lvl, tp.tag, t.name, t.c_vns
- FROM tags_parents tp
- JOIN tags t ON t.id = tp.tag
- WHERE tp.parent = tag
- AND state = 2
- ORDER BY t.name
- LOOP
- RETURN NEXT r;
- IF lvl-1 <> 0 THEN
- FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
- RETURN NEXT r2;
- END LOOP;
- END IF;
- END LOOP;
- ELSE
- FOR r IN
- SELECT lvl, tp.parent, t.name, t.c_vns
- FROM tags_parents tp
- JOIN tags t ON t.id = tp.parent
- WHERE tp.tag = tag
- AND state = 2
- ORDER BY t.name
- LOOP
- RETURN NEXT r;
- IF lvl-1 <> 0 THEN
- FOR r2 IN SELECT * FROM tag_tree(r.tag, lvl-1, dir) LOOP
- RETURN NEXT r2;
- END LOOP;
- END IF;
- END LOOP;
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-
-
--- returns all votes inherited by childs
--- UNION this with tags_vn and you have all votes for all tags
-CREATE OR REPLACE FUNCTION tag_vn_childs() RETURNS SETOF tags_vn AS $$
-DECLARE
- r tags_vn%rowtype;
- i RECORD;
- l RECORD;
-BEGIN
- FOR l IN SElECT id FROM tags WHERE meta = FALSE AND state = 2 AND EXISTS(SELECT 1 FROM tags_parents WHERE parent = id) LOOP
- FOR i IN SELECT tag FROM tag_tree(l.id, 0, true) LOOP
- FOR r IN SELECT l.id, vid, uid, vote, spoiler FROM tags_vn WHERE tag = i.tag LOOP
- RETURN NEXT r;
- END LOOP;
- END LOOP;
- END LOOP;
-END;
-$$ LANGUAGE plpgsql;
-
-
--- recalculate tags_vn_bayesian
-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();
-
-
-
-
-
------------------------
--- T R I G G E R S --
------------------------
-
-
--- keep the c_* columns in the users table up to date
-CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
-BEGIN
- IF TG_TABLE_NAME = 'votes' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
- ELSE
- UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
- END IF;
- ELSIF TG_TABLE_NAME = 'changes' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
- ELSE
- UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
- END IF;
- ELSIF TG_TABLE_NAME = 'tags_vn' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
- ELSE
- UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
- END IF;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-
-
--- the stats_cache table
-CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
-BEGIN
- IF TG_OP = 'INSERT' THEN
- IF TG_TABLE_NAME = 'users' THEN
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- ELSIF NEW.hidden = FALSE THEN
- IF TG_TABLE_NAME = 'threads_posts' THEN
- IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- END IF;
- ELSE
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- END IF;
- END IF;
-
- ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN
- IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN
- IF TG_TABLE_NAME = 'threads' THEN
- UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
- END IF;
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN
- IF TG_TABLE_NAME = 'threads' THEN
- UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
- END IF;
- UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
- END IF;
-
- ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN
- UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-
-
--- insert rows into anime for new vn_anime.aid items
-CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$
-BEGIN
- IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN
- INSERT INTO anime (id) VALUES (NEW.aid);
- END IF;
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
-
-
--- Send a notify whenever anime info should be fetched
-CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
-BEGIN
- IF NEW.lastfetch IS NULL THEN
- NOTIFY anime;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify();
-
-
--- Send a notify when a new cover image is uploaded
-CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$
-BEGIN
- IF NEW.image < 0 THEN
- NOTIFY coverimage;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify();
-
-
--- Send a notify when a screenshot needs to be processed
-CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$
-BEGIN
- IF NEW.processed = FALSE THEN
- NOTIFY screenshot;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify();
-
-
--- Update vn.rgraph column and send notify when a relation graph needs to be regenerated
--- 1. NOTIFY is sent on an UPDATE or INSERT on vn with rgraph = NULL and with entries in vn_relations (deferred)
--- vn.rgraph is set to NULL when:
--- 2. UPDATE on vn where c_released or c_languages has changed (deferred, but doesn't have to be)
--- 3. New VN revision of which the title differs from previous revision (deferred)
--- 4. New VN revision with items in vn_relations that differ from previous revision (deferred)
-CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
-BEGIN
- -- 1.
- IF TG_TABLE_NAME = 'vn' THEN
- IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN
- NOTIFY relgraph;
- END IF;
- END IF;
- IF TG_TABLE_NAME = 'vn' AND TG_OP = 'UPDATE' THEN
- IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
- -- 2.
- IF OLD.c_released <> NEW.c_released OR OLD.c_languages <> NEW.c_languages THEN
- UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
- END IF;
- -- 3 & 4
- IF OLD.latest <> NEW.latest AND (
- EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 WHERE v2.title <> v1.title AND v1.id = OLD.latest AND v2.id = NEW.latest)
- OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = OLD.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = NEW.latest)
- OR EXISTS(SELECT v1.vid2, v1.relation FROM vn_relations v1 WHERE v1.vid1 = NEW.latest EXCEPT SELECT v2.vid2, v2.relation FROM vn_relations v2 WHERE v2.vid1 = OLD.latest)
- ) THEN
- UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
- END IF;
- END IF;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON vn DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
-
-
--- Same as above for producers, with slight differences in the steps:
--- There is no 2, and
--- 3 = New producer revision of which the name, language or type differs from the previous revision (deferred)
-CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
-BEGIN
- -- 1.
- IF TG_TABLE_NAME = 'producers' THEN
- IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
- NOTIFY relgraph;
- END IF;
- END IF;
- IF TG_TABLE_NAME = 'producers' AND TG_OP = 'UPDATE' THEN
- IF NEW.rgraph IS NOT NULL AND OLD.latest > 0 THEN
- -- 3 & 4
- IF OLD.latest <> NEW.latest AND (
- EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
- OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
- OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = NEW.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = OLD.latest)
- ) THEN
- UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
- END IF;
- END IF;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE CONSTRAINT TRIGGER vn_relgraph_notify AFTER INSERT OR UPDATE ON producers DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
-
-
--- NOTIFY on insert into changes/posts/tags
-CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
-BEGIN
- IF TG_TABLE_NAME = 'changes' THEN
- NOTIFY newrevision;
- ELSIF TG_TABLE_NAME = 'threads_posts' THEN
- NOTIFY newpost;
- ELSIF TG_TABLE_NAME = 'tags' THEN
- NOTIFY newtag;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
-
-
-
-
-
----------------------------------
--- M I S C E L L A N E O U S --
----------------------------------
-
-
--- Sequences used for ID generation of items not in the DB
-CREATE SEQUENCE covers_seq;
-
-
--- Rows that are assumed to be available
-INSERT INTO users (id, username, mail, rank)
- VALUES (0, 'deleted', 'del@vndb.org', 0);
-INSERT INTO users (username, mail, rank)
- VALUES ('multi', 'multi@vndb.org', 0);
-
-INSERT INTO stats_cache (section, count) VALUES
- ('users', 1),
- ('vn', 0),
- ('producers', 0),
- ('releases', 0),
- ('threads', 0),
- ('threads_posts', 0);
-
diff --git a/util/sql/all.sql b/util/sql/all.sql
new file mode 100644
index 00000000..04e1dbf9
--- /dev/null
+++ b/util/sql/all.sql
@@ -0,0 +1,75 @@
+-- NOTE: Make sure you're cd'ed in the vndb root directory before running this script
+
+-- plpgsql is required for our (trigger) functions
+CREATE LANGUAGE plpgsql;
+
+
+-- data types
+
+CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
+CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p');
+CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer);
+CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc');
+CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
+CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
+CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
+
+
+-- schema
+
+\i util/sql/schema.sql
+
+
+-- functions
+
+\i util/sql/func.sql
+
+
+-- triggers
+
+CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
+
+CREATE TRIGGER vn_stats_update AFTER INSERT OR UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER producers_stats_update AFTER INSERT OR UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER releases_stats_update AFTER INSERT OR UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER threads_stats_update AFTER INSERT OR UPDATE ON threads FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER threads_posts_stats_update AFTER INSERT OR UPDATE ON threads_posts FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+CREATE TRIGGER users_stats_update AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
+
+CREATE TRIGGER vn_anime_aid BEFORE INSERT OR UPDATE ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
+
+CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW EXECUTE PROCEDURE anime_fetch_notify();
+
+CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW EXECUTE PROCEDURE vn_rev_image_notify();
+
+CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW EXECUTE PROCEDURE screenshot_process_notify();
+
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+
+CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
+
+
+-- Sequences used for ID generation of items not in the DB
+CREATE SEQUENCE covers_seq;
+
+
+-- Rows that are assumed to be available
+INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0);
+INSERT INTO users (username, mail, rank) VALUES ('multi', 'multi@vndb.org', 0);
+
+INSERT INTO stats_cache (section, count) VALUES
+ ('users', 1),
+ ('vn', 0),
+ ('producers', 0),
+ ('releases', 0),
+ ('threads', 0),
+ ('threads_posts', 0);
+
diff --git a/util/sql/func.sql b/util/sql/func.sql
new file mode 100644
index 00000000..ff38f02d
--- /dev/null
+++ b/util/sql/func.sql
@@ -0,0 +1,542 @@
+
+
+-- update_vncache(id) - updates the c_* columns in the vn table
+CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
+ UPDATE vn SET
+ c_released = COALESCE((SELECT
+ MIN(rr1.released)
+ FROM releases_rev rr1
+ JOIN releases r1 ON rr1.id = r1.latest
+ JOIN releases_vn rv1 ON rr1.id = rv1.rid
+ WHERE rv1.vid = vn.id
+ AND rr1.type <> 'trial'
+ AND r1.hidden = FALSE
+ AND rr1.released <> 0
+ GROUP BY rv1.vid
+ ), 0),
+ c_languages = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT rl2.lang
+ FROM releases_rev rr2
+ JOIN releases_lang rl2 ON rl2.rid = rr2.id
+ JOIN releases r2 ON rr2.id = r2.latest
+ JOIN releases_vn rv2 ON rr2.id = rv2.rid
+ WHERE rv2.vid = vn.id
+ AND rr2.type <> 'trial'
+ AND rr2.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
+ AND r2.hidden = FALSE
+ GROUP BY rl2.lang
+ ORDER BY rl2.lang
+ ), '/'), ''),
+ c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY(
+ SELECT rp3.platform
+ FROM releases_platforms rp3
+ JOIN releases_rev rr3 ON rp3.rid = rr3.id
+ JOIN releases r3 ON rp3.rid = r3.latest
+ JOIN releases_vn rv3 ON rp3.rid = rv3.rid
+ WHERE rv3.vid = vn.id
+ AND rr3.type <> 'trial'
+ AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
+ AND r3.hidden = FALSE
+ GROUP BY rp3.platform
+ ORDER BY rp3.platform
+ ), '/'), '')
+ WHERE id = $1;
+$$ LANGUAGE sql;
+
+
+
+-- recalculate vn.c_popularity
+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;
+
+
+
+-- recalculate tags_vn_inherit
+CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
+BEGIN
+ DROP INDEX IF EXISTS tags_vn_inherit_tag_vid;
+ TRUNCATE tags_vn_inherit;
+ -- populate tags_vn_inherit
+ INSERT INTO tags_vn_inherit
+ -- all votes for all tags, including votes inherited by child tags
+ -- (also includes meta tags, because they could have a normal tag as parent)
+ WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS (
+ SELECT 15, tag, vid, uid, vote, spoiler, false
+ FROM tags_vn
+ UNION ALL
+ SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta
+ FROM tags_vn_all ta
+ JOIN tags_parents tp ON tp.tag = ta.tag
+ JOIN tags t ON t.id = tp.parent
+ WHERE t.state = 2
+ AND ta.lvl > 0
+ )
+ -- grouped by (tag, vid)
+ 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 (
+ -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags)
+ SELECT tag, vid, uid, MAX(vote)::real, COALESCE(AVG(spoiler), 0)::real
+ FROM tags_vn_all
+ WHERE NOT meta
+ GROUP BY tag, vid, uid
+ ) AS t(tag, vid, uid, vote, spoiler)
+ GROUP BY tag, vid
+ HAVING AVG(vote) > 0;
+ -- recreate index
+ CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
+ -- and update the VN count in the tags table
+ UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
+ RETURN;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
+
+----------------------------------------------------------
+-- revision insertion abstraction --
+----------------------------------------------------------
+
+
+-- IMPORTANT: these functions will need to be updated on each change in the DB structure
+-- of the relevant tables
+
+
+-- create temporary table for generic revision info
+CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$
+BEGIN
+ CREATE TEMPORARY TABLE edit_revision (
+ type dbentry_type NOT NULL,
+ iid integer,
+ requester integer,
+ ip inet,
+ comments text
+ );
+ INSERT INTO edit_revision (type, iid) VALUES (t, i);
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+ t dbentry_type;
+ i integer;
+BEGIN
+ SELECT type INTO t FROM edit_revision;
+ SELECT iid INTO i FROM edit_revision;
+ -- figure out revision number
+ IF i IS NULL THEN
+ r.rev := 1;
+ ELSE
+ SELECT c.rev+1 INTO r.rev FROM changes c
+ LEFT JOIN vn_rev vr ON c.id = vr.id
+ LEFT JOIN releases_rev rr ON c.id = rr.id
+ LEFT JOIN producers_rev pr ON c.id = pr.id
+ WHERE (t = 'v' AND vr.vid = i)
+ OR (t = 'r' AND rr.rid = i)
+ OR (t = 'p' AND pr.pid = i)
+ ORDER BY c.id DESC
+ LIMIT 1;
+ END IF;
+ -- insert change
+ INSERT INTO changes (type, requester, ip, comments, rev)
+ SELECT t, requester, ip, comments, r.rev
+ FROM edit_revision
+ RETURNING id INTO r.cid;
+ -- insert DB item
+ IF i IS NULL THEN
+ CASE t
+ WHEN 'v' THEN INSERT INTO vn (latest) VALUES (0) RETURNING id INTO r.iid;
+ WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid;
+ WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid;
+ END CASE;
+ ELSE
+ r.iid := i;
+ END IF;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$
+BEGIN
+ -- create tables, based on existing tables (so that the column types are always synchronised)
+ CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn DROP COLUMN id;
+ ALTER TABLE edit_vn DROP COLUMN vid;
+ CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_anime DROP COLUMN vid;
+ CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_relations DROP COLUMN vid1;
+ ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid;
+ CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_vn_screenshots DROP COLUMN vid;
+ -- new VN, load defaults
+ IF cid IS NULL THEN
+ PERFORM edit_revtable('v', NULL);
+ INSERT INTO edit_vn DEFAULT VALUES;
+ -- otherwise, load revision
+ ELSE
+ PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid));
+ INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM vn_rev WHERE id = cid;
+ INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid;
+ INSERT INTO edit_vn_relations SELECT vid2, relation FROM vn_relations WHERE vid1 = cid;
+ INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_vn_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ IF (SELECT COUNT(*) FROM edit_vn) <> 1 THEN
+ RAISE 'edit_vn must have exactly one row!';
+ END IF;
+ SELECT INTO r * FROM edit_commit();
+ INSERT INTO vn_rev SELECT r.cid, r.iid, title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM edit_vn;
+ INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime;
+ INSERT INTO vn_relations SELECT r.cid, vid, relation FROM edit_vn_relations;
+ INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots;
+ UPDATE vn SET latest = r.cid WHERE id = r.iid;
+ DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$
+BEGIN
+ -- temp. tables
+ CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_release DROP COLUMN id;
+ ALTER TABLE edit_release DROP COLUMN rid;
+ CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_release_lang DROP COLUMN rid;
+ CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_release_media DROP COLUMN rid;
+ CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_release_platforms DROP COLUMN rid;
+ CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_release_producers DROP COLUMN rid;
+ CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_release_vn DROP COLUMN rid;
+ -- new release
+ IF cid IS NULL THEN
+ PERFORM edit_revtable('r', NULL);
+ INSERT INTO edit_release DEFAULT VALUES;
+ -- load revision
+ ELSE
+ PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid));
+ INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid;
+ INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid;
+ INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid;
+ INSERT INTO edit_release_platforms SELECT platform FROM releases_platforms WHERE rid = cid;
+ INSERT INTO edit_release_producers SELECT pid, developer, publisher FROM releases_producers WHERE rid = cid;
+ INSERT INTO edit_release_vn SELECT vid FROM releases_vn WHERE rid = cid;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_release_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ IF (SELECT COUNT(*) FROM edit_release) <> 1 THEN
+ RAISE 'edit_release must have exactly one row!';
+ ELSIF NOT EXISTS(SELECT 1 FROM edit_release_vn) THEN
+ RAISE 'edit_release_vn must have at least one row!';
+ END IF;
+ SELECT INTO r * FROM edit_commit();
+ INSERT INTO releases_rev SELECT r.cid, r.iid, title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM edit_release;
+ INSERT INTO releases_lang SELECT r.cid, lang FROM edit_release_lang;
+ INSERT INTO releases_media SELECT r.cid, medium, qty FROM edit_release_media;
+ INSERT INTO releases_platforms SELECT r.cid, platform FROM edit_release_platforms;
+ INSERT INTO releases_producers SELECT pid, r.cid, developer, publisher FROM edit_release_producers;
+ INSERT INTO releases_vn SELECT r.cid, vid FROM edit_release_vn;
+ UPDATE releases SET latest = r.cid WHERE id = r.iid;
+ DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$
+BEGIN
+ CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_producer DROP COLUMN id;
+ ALTER TABLE edit_producer DROP COLUMN pid;
+ CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+ ALTER TABLE edit_producer_relations DROP COLUMN pid1;
+ ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid;
+ -- new producer
+ IF cid IS NULL THEN
+ PERFORM edit_revtable('p', NULL);
+ INSERT INTO edit_producer DEFAULT VALUES;
+ -- load revision
+ ELSE
+ PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid));
+ INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid;
+ INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid;
+ END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_producer_commit() RETURNS edit_rettype AS $$
+DECLARE
+ r edit_rettype;
+BEGIN
+ IF (SELECT COUNT(*) FROM edit_producer) <> 1 THEN
+ RAISE 'edit_producer must have exactly one row!';
+ END IF;
+ SELECT INTO r * FROM edit_commit();
+ INSERT INTO producers_rev SELECT r.cid, r.iid, type, name, original, website, lang, "desc", alias, l_wp FROM edit_producer;
+ INSERT INTO producers_relations SELECT r.cid, pid, relation FROM edit_producer_relations;
+ UPDATE producers SET latest = r.cid WHERE id = r.iid;
+ DROP TABLE edit_revision, edit_producer, edit_producer_relations;
+ RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+
+
+----------------------------------------------------------
+-- trigger functions --
+----------------------------------------------------------
+
+
+-- keep the c_* columns in the users table up to date
+CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
+BEGIN
+ IF TG_TABLE_NAME = 'votes' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'changes' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
+ ELSE
+ UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
+ END IF;
+ ELSIF TG_TABLE_NAME = 'tags_vn' THEN
+ IF TG_OP = 'INSERT' THEN
+ UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
+ ELSE
+ UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE 'plpgsql';
+
+
+
+-- the stats_cache table
+CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
+BEGIN
+ IF TG_OP = 'INSERT' THEN
+ IF TG_TABLE_NAME = 'users' THEN
+ UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+ ELSIF NEW.hidden = FALSE THEN
+ IF TG_TABLE_NAME = 'threads_posts' THEN
+ IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND hidden = FALSE) THEN
+ UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+ END IF;
+ ELSE
+ UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+ END IF;
+ END IF;
+
+ ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN
+ IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN
+ IF TG_TABLE_NAME = 'threads' THEN
+ UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
+ END IF;
+ UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
+ ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN
+ IF TG_TABLE_NAME = 'threads' THEN
+ UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
+ END IF;
+ UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
+ END IF;
+
+ ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN
+ UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE 'plpgsql';
+
+
+
+-- insert rows into anime for new vn_anime.aid items
+CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$
+BEGIN
+ IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN
+ INSERT INTO anime (id) VALUES (NEW.aid);
+ END IF;
+ RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- Send a notify whenever anime info should be fetched
+CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
+BEGIN
+ IF NEW.lastfetch IS NULL THEN
+ NOTIFY anime;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- Send a notify when a new cover image is uploaded
+CREATE OR REPLACE FUNCTION vn_rev_image_notify() RETURNS trigger AS $$
+BEGIN
+ IF NEW.image < 0 THEN
+ NOTIFY coverimage;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- Send a notify when a screenshot needs to be processed
+CREATE OR REPLACE FUNCTION screenshot_process_notify() RETURNS trigger AS $$
+BEGIN
+ IF NEW.processed = FALSE THEN
+ NOTIFY screenshot;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- Update vn.rgraph column and send notify when a relation graph needs to be regenerated
+-- 1. NOTIFY is sent on VN edit or insert or change in vn.rgraph, when rgraph = NULL and entries in vn_relations
+-- vn.rgraph is set to NULL when:
+-- 2. UPDATE on vn where c_released or c_languages has changed
+-- 3. VN edit of which the title differs from previous revision
+-- 4. VN edit with items in vn_relations that differ from previous
+CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM vn_relations WHERE vid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF NEW.rgraph IS NOT NULL THEN
+ IF
+ -- 2.
+ OLD.c_released IS DISTINCT FROM NEW.c_released
+ OR OLD.c_languages IS DISTINCT FROM NEW.c_languages
+ OR OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
+ -- 3.
+ EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 WHERE v2.title <> v1.title AND v1.id = OLD.latest AND v2.id = NEW.latest)
+ -- 4. (not-really-readable method of comparing two query results)
+ OR EXISTS(SELECT vid2, relation FROM vn_relations WHERE vid1 = OLD.latest EXCEPT SELECT vid2, relation FROM vn_relations WHERE vid1 = NEW.latest)
+ OR (SELECT COUNT(*) FROM vn_relations WHERE vid1 = OLD.latest) <> (SELECT COUNT(*) FROM vn_relations WHERE vid1 = NEW.latest)
+ )
+ THEN
+ UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- Same as above for producers, with slight differences in the steps:
+-- There is no 2, and
+-- 3 = Producer edit of which the name, language or type differs from the previous revision
+CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
+BEGIN
+ -- 1.
+ IF NEW.rgraph IS DISTINCT FROM OLD.rgraph OR NEW.latest IS DISTINCT FROM OLD.latest THEN
+ IF NEW.rgraph IS NULL AND EXISTS(SELECT 1 FROM producers_relations WHERE pid1 = NEW.latest) THEN
+ NOTIFY relgraph;
+ END IF;
+ END IF;
+ IF NEW.rgraph IS NOT NULL THEN
+ -- 2.
+ IF OLD.latest <> 0 AND OLD.latest IS DISTINCT FROM NEW.latest AND (
+ -- 3.
+ EXISTS(SELECT 1 FROM producers_rev p1, producers_rev p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.id = OLD.latest AND p2.id = NEW.latest)
+ -- 4. (not-really-readable method of comparing two query results)
+ OR EXISTS(SELECT p1.pid2, p1.relation FROM producers_relations p1 WHERE p1.pid1 = OLD.latest EXCEPT SELECT p2.pid2, p2.relation FROM producers_relations p2 WHERE p2.pid1 = NEW.latest)
+ OR (SELECT COUNT(*) FROM producers_relations WHERE pid1 = OLD.latest) <> (SELECT COUNT(*) FROM producers_relations WHERE pid1 = NEW.latest)
+ )
+ THEN
+ UPDATE producers SET rgraph = NULL WHERE id = NEW.id;
+ END IF;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- NOTIFY on insert into changes/posts/tags
+CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
+BEGIN
+ IF TG_TABLE_NAME = 'changes' THEN
+ NOTIFY newrevision;
+ ELSIF TG_TABLE_NAME = 'threads_posts' THEN
+ NOTIFY newpost;
+ ELSIF TG_TABLE_NAME = 'tags' THEN
+ NOTIFY newtag;
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+-- call update_vncache() when a release is added, edited, hidden or unhidden
+CREATE OR REPLACE FUNCTION release_vncache_update() RETURNS trigger AS $$
+BEGIN
+ IF OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden THEN
+ PERFORM update_vncache(vid) FROM (
+ SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest
+ ) AS v(vid);
+ END IF;
+ RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
new file mode 100644
index 00000000..d5286a94
--- /dev/null
+++ b/util/sql/schema.sql
@@ -0,0 +1,388 @@
+
+
+-- anime
+CREATE TABLE anime (
+ id integer NOT NULL PRIMARY KEY,
+ year smallint,
+ ann_id integer,
+ nfo_id varchar(200),
+ type anime_type,
+ title_romaji,
+ title_kanji,
+ lastfetch timestamptz
+);
+
+-- changes
+CREATE TABLE changes (
+ id SERIAL NOT NULL PRIMARY KEY,
+ type dbentry_type NOT NULL,
+ rev integer NOT NULL DEFAULT 1,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ requester integer NOT NULL DEFAULT 0,
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ comments text NOT NULL DEFAULT ''
+);
+
+-- producers
+CREATE TABLE producers (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ rgraph integer
+);
+
+-- producers_relations
+CREATE TABLE producers_relations (
+ pid1 integer NOT NULL,
+ pid2 integer NOT NULL,
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(pid1, pid2)
+);
+
+-- producers_rev
+CREATE TABLE producers_rev (
+ id integer NOT NULL PRIMARY KEY,
+ pid integer NOT NULL DEFAULT 0,
+ type character(2) NOT NULL DEFAULT 'co',
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ website varchar(250) NOT NULL DEFAULT '',
+ lang varchar NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ l_wp varchar(150)
+);
+
+-- quotes
+CREATE TABLE quotes (
+ vid integer NOT NULL,
+ quote varchar(250) NOT NULL,
+ PRIMARY KEY(vid, quote)
+);
+
+-- releases
+CREATE TABLE releases (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE
+);
+
+-- releases_lang
+CREATE TABLE releases_lang (
+ rid integer NOT NULL,
+ lang varchar NOT NULL,
+ PRIMARY KEY(rid, lang)
+);
+
+-- releases_media
+CREATE TABLE releases_media (
+ rid integer NOT NULL DEFAULT 0,
+ medium medium NOT NULL,
+ qty smallint NOT NULL DEFAULT 1,
+ PRIMARY KEY(rid, medium, qty)
+);
+
+-- releases_platforms
+CREATE TABLE releases_platforms (
+ rid integer NOT NULL DEFAULT 0,
+ platform character(3) NOT NULL DEFAULT 0,
+ PRIMARY KEY(rid, platform)
+);
+
+-- releases_producers
+CREATE TABLE releases_producers (
+ rid integer NOT NULL,
+ pid integer NOT NULL,
+ developer boolean NOT NULL DEFAULT FALSE,
+ publisher boolean NOT NULL DEFAULT TRUE,
+ CHECK(developer OR publisher),
+ PRIMARY KEY(pid, rid)
+);
+
+-- releases_rev
+CREATE TABLE releases_rev (
+ id integer NOT NULL PRIMARY KEY,
+ rid integer NOT NULL DEFAULT 0,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ type release_type NOT NULL DEFAULT 'complete',
+ website varchar(250) NOT NULL DEFAULT '',
+ released integer NOT NULL DEFAULT 0,
+ notes text NOT NULL DEFAULT '',
+ minage smallint,
+ gtin bigint NOT NULL DEFAULT 0,
+ patch boolean NOT NULL DEFAULT FALSE,
+ catalog varchar(50) NOT NULL DEFAULT '',
+ resolution smallint NOT NULL DEFAULT 0,
+ voiced smallint NOT NULL DEFAULT 0,
+ freeware boolean NOT NULL DEFAULT FALSE,
+ doujin boolean NOT NULL DEFAULT FALSE,
+ ani_story smallint NOT NULL DEFAULT 0,
+ ani_ero smallint NOT NULL DEFAULT 0
+);
+
+-- releases_vn
+CREATE TABLE releases_vn (
+ rid integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0,
+ PRIMARY KEY(rid, vid)
+);
+
+-- relgraphs
+CREATE TABLE relgraphs (
+ id SERIAL PRIMARY KEY,
+ svg xml NOT NULL
+);
+
+-- rlists
+CREATE TABLE rlists (
+ uid integer NOT NULL DEFAULT 0,
+ rid integer NOT NULL DEFAULT 0,
+ vstat smallint NOT NULL DEFAULT 0,
+ rstat smallint NOT NULL DEFAULT 0,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ PRIMARY KEY(uid, rid)
+);
+
+-- screenshots
+CREATE TABLE screenshots (
+ id SERIAL NOT NULL PRIMARY KEY,
+ processed boolean NOT NULL DEFAULT FALSE,
+ width smallint NOT NULL DEFAULT 0,
+ height smallint NOT NULL DEFAULT 0
+);
+
+-- sessions
+CREATE TABLE sessions (
+ uid integer NOT NULL,
+ token bytea NOT NULL,
+ expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval),
+ PRIMARY KEY (uid, token)
+);
+
+-- stats_cache
+CREATE TABLE stats_cache (
+ section varchar(25) NOT NULL PRIMARY KEY,
+ count integer NOT NULL DEFAULT 0
+);
+
+-- tags
+CREATE TABLE tags (
+ id SERIAL NOT NULL PRIMARY KEY,
+ name varchar(250) NOT NULL UNIQUE,
+ description text NOT NULL DEFAULT '',
+ meta boolean NOT NULL DEFAULT FALSE,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ state smallint NOT NULL DEFAULT 0,
+ c_vns integer NOT NULL DEFAULT 0,
+ addedby integer NOT NULL DEFAULT 1
+);
+
+-- tags_aliases
+CREATE TABLE tags_aliases (
+ alias varchar(250) NOT NULL PRIMARY KEY,
+ tag integer NOT NULL,
+);
+
+-- tags_parents
+CREATE TABLE tags_parents (
+ tag integer NOT NULL,
+ parent integer NOT NULL,
+ PRIMARY KEY(tag, parent)
+);
+
+-- tags_vn
+CREATE TABLE tags_vn (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ uid integer NOT NULL,
+ vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
+ spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
+ PRIMARY KEY(tag, vid, uid)
+);
+
+-- tags_vn_inherit
+CREATE TABLE tags_vn_inherit (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
+-- threads
+CREATE TABLE threads (
+ id SERIAL NOT NULL PRIMARY KEY,
+ title varchar(50) NOT NULL DEFAULT '',
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ count smallint NOT NULL DEFAULT 0
+);
+
+-- threads_posts
+CREATE TABLE threads_posts (
+ tid integer NOT NULL DEFAULT 0,
+ num smallint NOT NULL DEFAULT 0,
+ uid integer NOT NULL DEFAULT 0,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ edited timestamptz,
+ msg text NOT NULL DEFAULT '',
+ hidden boolean NOT NULL DEFAULT FALSE,
+ PRIMARY KEY(tid, num)
+);
+
+-- threads_boards
+CREATE TABLE threads_boards (
+ tid integer NOT NULL DEFAULT 0,
+ type character(2) NOT NULL DEFAULT 0,
+ iid integer NOT NULL DEFAULT 0,
+ lastread smallint NOT NULL,
+ PRIMARY KEY(tid, type, iid)
+);
+
+-- users
+CREATE TABLE users (
+ id SERIAL NOT NULL PRIMARY KEY,
+ username varchar(20) NOT NULL UNIQUE,
+ mail varchar(100) NOT NULL,
+ rank smallint NOT NULL DEFAULT 3,
+ passwd bytea NOT NULL DEFAULT '',
+ registered timestamptz NOT NULL DEFAULT NOW(),
+ show_nsfw boolean NOT NULL DEFAULT FALSE,
+ show_list boolean NOT NULL DEFAULT TRUE,
+ c_votes integer NOT NULL DEFAULT 0,
+ c_changes integer NOT NULL DEFAULT 0,
+ skin varchar(128) NOT NULL DEFAULT '',
+ customcss text NOT NULL DEFAULT '',
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ c_tags integer NOT NULL DEFAULT 0,
+ salt character(9) NOT NULL DEFAULT '',
+ ign_votes voolean NOT NULL DEFAULT FALSE
+);
+
+-- vn
+CREATE TABLE vn (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ rgraph integer,
+ c_released integer NOT NULL DEFAULT 0,
+ c_languages varchar(32) NOT NULL DEFAULT '',
+ c_platforms varchar(32) NOT NULL DEFAULT '',
+ c_popularity real,
+ c_rating real,
+ c_votecount integer NOT NULL DEFAULT 0
+);
+
+-- vn_anime
+CREATE TABLE vn_anime (
+ vid integer NOT NULL,
+ aid integer NOT NULL,
+ PRIMARY KEY(vid, aid)
+);
+
+-- vn_relations
+CREATE TABLE vn_relations (
+ vid1 integer NOT NULL DEFAULT 0,
+ vid2 integer NOT NULL DEFAULT 0,
+ relation vn_relation NOT NULL,
+ PRIMARY KEY(vid1, vid2)
+);
+
+-- vn_rev
+CREATE TABLE vn_rev (
+ id integer NOT NULL PRIMARY KEY,
+ vid integer NOT NULL DEFAULT 0,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ img_nsfw boolean NOT NULL DEFAULT FALSE,
+ length smallint NOT NULL DEFAULT 0,
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT '',
+ l_vnn integer NOT NULL DEFAULT 0,
+ image integer NOT NULL DEFAULT 0,
+ l_encubed varchar(100) NOT NULL DEFAULT '',
+ l_renai varchar(100) NOT NULL DEFAULT ''
+);
+
+-- vn_screenshots
+CREATE TABLE vn_screenshots (
+ vid integer NOT NULL DEFAULT 0,
+ scr integer NOT NULL DEFAULT 0,
+ nsfw boolean NOT NULL DEFAULT FALSE,
+ rid integer DEFAULT NULL,
+ PRIMARY KEY(vid, scr)
+);
+
+-- votes
+CREATE TABLE votes (
+ vid integer NOT NULL DEFAULT 0,
+ uid integer NOT NULL DEFAULT 0,
+ vote integer NOT NULL DEFAULT 0,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ PRIMARY KEY(vid, uid)
+);
+
+-- wlists
+CREATE TABLE wlists (
+ uid integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0,
+ wstat smallint NOT NULL DEFAULT 0,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ PRIMARY KEY(uid, vid)
+);
+
+
+
+ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id);
+ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id);
+ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id);
+ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
+ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id);
+ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
+
diff --git a/util/updates/update_2.10.sql b/util/updates/update_2.10.sql
new file mode 100644
index 00000000..e54e028c
--- /dev/null
+++ b/util/updates/update_2.10.sql
@@ -0,0 +1,63 @@
+
+-- no more bayesian rating for VN list on tag pages, just plain averages
+DROP TABLE tags_vn_bayesian;
+CREATE TABLE tags_vn_inherit (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
+
+-- remove unused functions
+DROP FUNCTION tag_vn_childs() CASCADE;
+DROP FUNCTION tag_tree(integer, integer, boolean);
+DROP TYPE tag_tree_item;
+
+
+-- remove changes.causedby and give the affected changes to Multi
+UPDATE changes SET requester = 1 WHERE causedby IS NOT NULL;
+ALTER TABLE changes DROP COLUMN causedby;
+UPDATE users SET
+ c_changes = COALESCE((
+ SELECT COUNT(id)
+ FROM changes
+ WHERE requester = users.id
+ GROUP BY requester
+ ), 0);
+
+
+-- set default on releases_rev.released, required for the revision insertion abstraction
+ALTER TABLE releases_rev ALTER COLUMN released SET DEFAULT 0;
+
+
+-- type used for the revision inserting functions
+CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer);
+
+
+-- import the new and updated functions
+\i util/sql/func.sql
+
+
+-- call update_vncache() when a release is added, edited, hidden or unhidden
+CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE release_vncache_update();
+
+
+-- improved relgraph notify triggers
+DROP TRIGGER vn_relgraph_notify ON vn;
+CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify();
+DROP TRIGGER vn_relgraph_notify ON producers;
+CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify();
+
+
+-- more efficient version of tag_vn_calc()
+SELECT tag_vn_calc();
+
+
+-- regenerate the relation graphs so that they contain IDs for highlighting
+UPDATE vn SET rgraph = NULL;
+UPDATE producers SET rgraph = NULL;
+DELETE FROM relgraphs;
+
+
diff --git a/util/vndb.pl b/util/vndb.pl
index 4752c274..32e1bdb4 100755
--- a/util/vndb.pl
+++ b/util/vndb.pl
@@ -52,11 +52,23 @@ YAWF::init(
sub reqinit {
my $self = shift;
- $self->{l10n} = VNDB::L10N->get_handle($self->reqParam('l10n') || $self->reqCookie('l10n') || ());
- my $lang = $self->{l10n}->language_tag();
- $self->resHeader('Set-Cookie', "l10n=$lang; expires=Sat, 01-Jan-2030 00:00:00 GMT; path=/; domain=$self->{cookie_domain}")
- if $lang ne ($self->reqCookie('l10n')||'');
+ # Determine language
+ # if the cookie or parameter "l10n" is set, use that.
+ # otherwise, interpret the Accept-Language header or fall back to English
+ # if the cookie is set and is the same as either the Accept-Language header or the fallback, remove it
+ my $conf = $self->reqParam('l10n') || $self->reqCookie('l10n');
+ $conf = '' if !$conf || !grep $_ eq $conf, VNDB::L10N::languages;
+
+ $self->{l10n} = VNDB::L10N->get_handle(); # this uses I18N::LangTags::Detect
+ if($self->{l10n}->language_tag() eq $conf && $self->reqCookie('l10n')) {
+ $self->resHeader('Set-Cookie', "l10n= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$self->{cookie_domain}");
+ } elsif($self->reqParam('l10n') && $conf && $conf ne ($self->reqCookie('l10n')||'') && $self->{l10n}->language_tag() ne $conf) {
+ $self->resHeader('Set-Cookie', "l10n=$conf; expires=Sat, 01-Jan-2030 00:00:00 GMT; path=/; domain=$self->{cookie_domain}");
+ }
+ $self->{l10n} = VNDB::L10N->get_handle($conf) if $conf && $self->{l10n}->language_tag() ne $conf;
+
+ # check authentication cookies
$self->authInit;
# check for IE6