diff options
41 files changed, 1902 insertions, 1727 deletions
@@ -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 @@ -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) + + @@ -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 => [ \>intype, '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>▸</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 |