diff options
37 files changed, 739 insertions, 543 deletions
@@ -1,3 +1,26 @@ +2.12 - ? + - !scr command for Multi::IRC + - API: Added 'image' field to get vn + - API: Slightly improved error messages + - Re-added /g/debug (for moderators) + - Improved search + - Display friendly message in the VN edit scr tab when no release is known + - Added 1024x576 and 1280x800 screen resolutions + - Added more comparison VNs for the length field + - Automatically remove read notifications after a month + - Added Apple iProduct platform + - Removed XML sitemap + - Added image dimensions to screenshot thumbail <img> tags + - Prefix all cookies with a configurable cookie_prefix + - Automatically read L10N keys from script.js + - Compressed the thread listing into one row per thread (instead of two) + - Use newlines to separate aliases (except when displayed on VN pages) + - Automatically remove duplicate aliases on /v+/edit + - Bugfix: only redirect VN search to VN page if page=1 + - Bugfix: remove duplicate votes when merging tags (fixes a 500) + - Bugfix: Multi::Anime: don't crash when anidb returns an invalid or empty year + - Bugfix: properly order the relations listed on producer pages + 2.11 - 2010-02-06 - Added Slovak to the language list - Centered the thumbnails on the screenshots viewer @@ -37,7 +37,7 @@ # environments. Patches to improve the portability are always welcome. -.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 update-2.11 +.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 update-2.11 update-2.12 all: dirs js skins robots data/config.pl @@ -141,3 +141,8 @@ update-2.11: all ${runpsql} < util/updates/update_2.11.sql $(multi-start) +update-2.12: all + $(multi-stop) + rm www/sitemap.xml.gz + ${runpsql} < util/updates/update_2.12.sql + $(multi-start) @@ -46,9 +46,6 @@ Requirements XML::Parser XML::Writer graphviz (/usr/bin/dot is used by default) - Sitemap: - XML::Writer - PerlIO::gzip util/skingen.pl Image::Magick diff --git a/data/docs/11 b/data/docs/11 index e1e49881..28d1acc1 100644 --- a/data/docs/11 +++ b/data/docs/11 @@ -376,6 +376,16 @@ however still required.<br /> </td> </tr> <tr class="odd"> + <td>image</td> + <td>details</td> + <td>string</td> + <td>yes</td> + <td> + HTTP link to the VN image. Please note that hotlinking is not allowed, so + make sure to leave the HTTP Referer header empty when fetching the image. + </td> + </tr> + <tr> <td>anime</td> <td>anime</td> <td>array of objects</td> @@ -393,7 +403,7 @@ however still required.<br /> and may not reflect the latest state of their information due to caching. </td> </tr> - <tr> + <tr class="odd"> <td>relations</td> <td>relations</td> <td>array of objects</td> @@ -889,3 +899,15 @@ however still required.<br /> +:SUB:Change Log +<p> + This section lists the changes made in each version of the VNDB code. + Check out the <a href="/t/an">announcements board</a> for more information about updates. +</p> +<b>2.12</b> +<ul> + <li>Added "image" member to "get vn"</li> + <li>A few minor fixes in some error messages</li> + <li>Switched to a different (and faster) search algorithm for "get vn .. (search ~ ..)"</li> +</ul> + diff --git a/data/docs/4 b/data/docs/4 index f2dccba8..fc383e74 100644 --- a/data/docs/4 +++ b/data/docs/4 @@ -64,7 +64,7 @@ The following relations are defined: The difference with the <i>Formerly</i> relation is that the producer where the members came from is still alive. </dd><dt>Originated from</dt><dd> - Reverse of <i>Spawned</i> - current producer spawned the selected producer. + Reverse of <i>Spawned</i> - selected producer spawned the current producer. </dd> </dl> diff --git a/data/docs/5 b/data/docs/5 index ada10755..ee938f3a 100644 --- a/data/docs/5 +++ b/data/docs/5 @@ -24,7 +24,7 @@ :SUB:Capitalization <p> It occasionally happens that official titles or names are either entirely in - uppercase or lowercase characters. If there is no real reason for this choise + uppercase or lowercase characters. If there is no real reason for this choice of capitalization (e.g. it's not an acronym), these titles and names should be properly converted to normal English capitalization (as described <a href="http://en.wikipedia.org/wiki/Capitalization">on Wikipedia</a>) before diff --git a/data/global.pl b/data/global.pl index 0d58e9b4..4046ce10 100644 --- a/data/global.pl +++ b/data/global.pl @@ -19,9 +19,12 @@ our %S = (%S, url_static => 'http://s.vndb.org', skin_default => 'angel', cookie_domain => '.vndb.org', + cookie_prefix => 'vndb_', global_salt => 'any-private-string-here', source_url => 'http://git.blicky.net/vndb.git/?h=master', admin_email => 'contact@vndb.org', + scr_size => [ 136, 102 ], # w*h of screenshot thumbnails + cv_size => [ 256, 400 ], # max. w*h of cover images user_ranks => [ # allowed actions # DB number [qw| hist |], # 0 @@ -52,16 +55,16 @@ our %S = (%S, prod_relations => { 'old' => [ 0, 'new' ], 'new' => [ 1, 'old' ], - 'sub' => [ 2, 'par' ], - 'par' => [ 3, 'sub' ], - 'imp' => [ 4, 'ipa' ], - 'ipa' => [ 5, 'imp' ], - 'spa' => [ 6, 'ori' ], - 'ori' => [ 7, 'spa' ], + 'spa' => [ 2, 'ori' ], + 'ori' => [ 3, 'spa' ], + 'sub' => [ 4, 'par' ], + 'par' => [ 5, 'sub' ], + 'imp' => [ 6, 'ipa' ], + 'ipa' => [ 7, 'imp' ], }, age_ratings => [undef, 0, 6..18], release_types => [qw|complete partial trial|], - platforms => [qw|win dos lin mac dvd gba msx nds nes p98 psp ps1 ps2 ps3 drc sat sfc wii xb3 oth|], + platforms => [qw|win dos lin mac ios dvd gba msx nds nes p98 psp ps1 ps2 ps3 drc sat sfc wii xb3 oth|], media => { #DB qty? cd => 1, @@ -85,9 +88,11 @@ our %S = (%S, [ '1024x768', '4:3' ], [ '1600x1200', '4:3' ], [ '640x400', 'widescreen' ], + [ '1024x576', 'widescreen' ], [ '1024x600', 'widescreen' ], [ '1024x640', 'widescreen' ], [ '1280x720 (720p)', 'widescreen' ], + [ '1280x800', 'widescreen' ], [ '1920x1080 (1080p)', 'widescreen' ], ], voiced => [ 0..4 ], @@ -105,7 +110,6 @@ our %M = ( #API => {}, # disabled by default, not really needed RG => {}, Image => {}, - Sitemap => {}, #Anime => {}, # disabled by default, requires AniDB username/pass Maintenance => {}, #IRC => {}, # disabled by default, no need to run an IRC bot when debugging diff --git a/data/lang.txt b/data/lang.txt index fff7d167..1f8479b9 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -409,6 +409,13 @@ cs : Mac OS hu : nl : +:_plat_ios +en : Apple iProduct +ru*: +cs*: +hu*: +nl : + :_plat_dvd en : DVD Player ru : DVD-плеер @@ -1062,39 +1069,39 @@ hu : Ismeretlen nl : Onbekend :_vnlength_1 -en : Very short[index,_1,, (< 2 hours), (OMGWTHOTL~, A Dream of Summer)] -ru : Крошечная[index,_1,, (< 2 часов), (OMGWTFOTL~, A Dream Of Summer)] -cs : Velmi krátká[index,_1,, (< 2 hours), (OMGWTHOTL~, A Dream of Summer)] -hu : Nagyon rövid[index,_1,, (< 2 óra), (OMGWTHOTL~, A Dream of Summer)] -nl : Erg kort[index,_1,, (< 2 uur), (OMGWTHOTL~, A Dream of Summer)] +en : Very short[index,_1,, (< 2 hours), (OMGWTFOTL~, Jouka no Monshou~, The world to reverse)] +ru : Крошечная[index,_1,, (< 2 часов), (OMGWTFOTL~, Jouka no Monshou~, The world to reverse)] +cs : Velmi krátká[index,_1,, (< 2 hours), (OMGWTFOTL~, Jouka no Monshou~, The world to reverse)] +hu : Nagyon rövid[index,_1,, (< 2 óra), (OMGWTFOTL~, Jouka no Monshou~, The world to reverse)] +nl : Erg kort[index,_1,, (< 2 uur), (OMGWTFOTL~, Jouka no Monshou~, The world to reverse)] :_vnlength_2 -en : Short[index,_1,, (2 - 10 hours), (Narcissu~, Planetarian)] -ru : Малая[index,_1,, (2 - 10 часов), (Narcissu~, Planetarian)] -cs : Krátká[index,_1,, (2 - 10 hours), (Narcissu~, Planetarian)] -hu : Rövid[index,_1,, (2 - 10 óra), (Narcissu~, Planetarian)] -nl : Kort[index,_1,, (2 - 10 uur), (Narcissu~, Planetarian)] +en : Short[index,_1,, (2 - 10 hours), (Narcissu~, Saya no Uta~, Planetarian)] +ru : Малая[index,_1,, (2 - 10 часов), (Narcissu~, Saya no Uta~, Planetarian)] +cs : Krátká[index,_1,, (2 - 10 hours), (Narcissu~, Saya no Uta~, Planetarian)] +hu : Rövid[index,_1,, (2 - 10 óra), (Narcissu~, Saya no Uta~, Planetarian)] +nl : Kort[index,_1,, (2 - 10 uur), (Narcissu~, Saya no Uta~, Planetarian)] :_vnlength_3 -en : Medium[index,_1,, (10 - 30 hours), (Kana: Little Sister)] -ru : Средняя[index,_1,, (10 - 30 часов), (Kana: Little Sister)] -cs : Střední[index,_1,, (10 - 30 hours), (Kana: Little Sister)] -hu : Közepes[index,_1,, (10 - 30 óra), (Kana: Little Sister)] -nl : Gemiddeld[index,_1,, (10 - 30 uur), (Kana: Little Sister)] +en : Medium[index,_1,, (10 - 30 hours), (Yume Miru Kusuri~, Cross†Channel~, Crescendo)] +ru : Средняя[index,_1,, (10 - 30 часов), (Yume Miru Kusuri~, Cross†Channel~, Crescendo)] +cs : Střední[index,_1,, (10 - 30 hours), (Yume Miru Kusuri~, Cross†Channel~, Crescendo)] +hu : Közepes[index,_1,, (10 - 30 óra), (Yume Miru Kusuri~, Cross†Channel~, Crescendo)] +nl : Gemiddeld[index,_1,, (10 - 30 uur), (Yume Miru Kusuri~, Cross†Channel~, Crescendo)] :_vnlength_4 -en : Long[index,_1,, (30 - 50 hours), (Tsukihime)] -ru : Большая[index,_1,, (30 - 50 часов), (Tsukihime)] -cs : Dlouhá[index,_1,, (30 - 50 hours), (Tsukihime)] -hu : Hosszú[index,_1,, (30 - 50 óra), (Tsukihime)] -nl : Lang[index,_1,, (30 - 50 uur), (Tsukihime)] +en : Long[index,_1,, (30 - 50 hours), (Tsukihime~, Ever17~, Utawarerumono)] +ru : Большая[index,_1,, (30 - 50 часов), (Tsukihime~, Ever17~, Utawarerumono)] +cs : Dlouhá[index,_1,, (30 - 50 hours), (Tsukihime~, Ever17~, Utawarerumono)] +hu : Hosszú[index,_1,, (30 - 50 óra), (Tsukihime~, Ever17~, Utawarerumono)] +nl : Lang[index,_1,, (30 - 50 uur), (Tsukihime~, Ever17~, Utawarerumono)] :_vnlength_5 -en : Very long[index,_1,, (> 50 hours), (Clannad)] -ru : Очень большая[index,_1,, (> 50 часов), (Clannad)] -cs : Velmi dlouhá[index,_1,, (> 50 hours), (Clannad)] -hu : Nagyon hosszú[index,_1,, (> 50 óra), (Clannad)] -nl : Erg lang[index,_1,, (> 50 uur), (Clannad)] +en : Very long[index,_1,, (> 50 hours), (Clannad~, Umineko~, Fate/Stay Night)] +ru : Очень большая[index,_1,, (> 50 часов), (Clannad~, Umineko~, Fate/Stay Night)] +cs : Velmi dlouhá[index,_1,, (> 50 hours), (Clannad~, Umineko~, Fate/Stay Night)] +hu : Nagyon hosszú[index,_1,, (> 50 óra), (Clannad~, Umineko~, Fate/Stay Night)] +nl : Erg lang[index,_1,, (> 50 uur), (Clannad~, Umineko~, Fate/Stay Night)] # VN list statuses @@ -5472,6 +5479,13 @@ cs*: hu : bejelöltek eltávolítássa nl : verwijder +:_usern_autodel +en : (Read notifications are automatically removed after one month) +ru*: +cs*: +hu*: +nl : (Gelezen notificaties worden na 1 maand automatisch verwijderd) + :_usern_set_saved en : Settings successfully saved. ru : Параметры успешно сохранены. @@ -5728,19 +5742,19 @@ hu : Más nevek nl : Aliassen :_vnedit_alias_msg -en : Comma separated list of alternative titles or abbreviations. Can include both official - (japanese/english) titles and unofficial titles used around net.[br] +en : List of alternative titles or abbreviations. One line for each alias. + Can include both official (japanese/english) titles and unofficial titles used around net.[br] Titles that are listed in the releases should not be added here! -ru : Список альтернативных названий или аббревиатур, перечисленных через запятую. Может включать как +ru*: Список альтернативных названий или аббревиатур, перечисленных через запятую. Может включать как официальные (японские/английские/русские) названия, так и неофициальные, имеющие хождение в сети.[br] Не следует добавлять сюда названия, указанные в выпусках! -cs : Seznam alternativních názvů nebo zkratek, oddělených čárkou. Může zahrnovat oba +cs*: Seznam alternativních názvů nebo zkratek, oddělených čárkou. Může zahrnovat oba (japonský/anglický) názvy a neoficiální názvy používané na internetu.[br] Názvy, vypsané ve vydáních by se sem přidávat neměly! -hu : Vesszővel elválasztott lista, más címekkel vagy rövidítésekkel. Tartalmazhat hivatalos +hu*: Vesszővel elválasztott lista, más címekkel vagy rövidítésekkel. Tartalmazhat hivatalos (japán/angol) címeket vagy nem hivatalosokat amelyet a netten használnak.[br] Azok a címek amelyek a kiadásokba vannak ne tegyétek bele! -nl : Kommagescheiden lijst van alternatieve titels of afkortingen. Kan zowel officiële +nl : Lijst van alternatieve titels of afkortingen, één titel per regel. Kan zowel officiële (Japanse/Engelse) als officieuze titels bevatten. :_vnedit_desc @@ -5954,6 +5968,13 @@ cs : Screenshoty hu : Pillanatképek nl : +:_vnedit_scrnorel +en : No releases in the database yet. Screenshots can only be uploaded after a release has been added. +ru*: +cs*: +hu*: +nl : Nog geen uitgaven in de database. Screenshots kunnen alleen geupload worden nadat een uitgave is toegevoegd. + :_vnedit_scrmsg en : Please keep the following in mind when uploading screenshots:[br] - Screenshots have to be in the native resolution of the game,[br] @@ -6950,3 +6971,5 @@ cs : Váš prohlížeč je na nic, nepodporuje vykreslování našich krásných hu : A böngésződ szar, nem képes megjeleníteni a szép összefüggés grafikonunkat. nl : Je browser is troep, het heeft niet de benodigde functionaliteit om onze mooie grafiekjes weer te geven. + +# vim: set ft=none: diff --git a/data/script.js b/data/script.js index 521c30df..61f37d66 100644 --- a/data/script.js +++ b/data/script.js @@ -14,6 +14,25 @@ * tvs -> VN page tag spoilers * vnr -> VN relation editor */ + +/* Internationalization note: + * The translation keys to be inserted in the header of this file are parsed + * from the source code. So when using mt(), make sure it is in the following + * format: + * mt('<exact translation key>',<more arguments> + * or + * mt('<exact translation key>') + * The single quotes and (lack of) spaces are significant! + * + * To use non-exact translation keys as argument to mt(), make sure to + * indicate which keys should be inserted in the header by adding a comment + * containing the following format: + * l10n /<perl regex>/ + * any keys matching that regex will be included. + * + * The "_lang_*" keys for all languages for which we have a translation are + * already included automatically. + */ var expanded_icon = '▾'; var collapsed_icon = '▸'; @@ -41,10 +60,11 @@ function ajax(url, func) { function setCookie(n,v) { var date = new Date(); date.setTime(date.getTime()+(365*24*60*60*1000)); - document.cookie = n+'='+v+'; expires='+date.toGMTString()+'; path=/'; + document.cookie = cookie_prefix+n+'='+v+'; expires='+date.toGMTString()+'; path=/'; } function getCookie(n) { var l = document.cookie.split(';'); + n = cookie_prefix+n; for(var i=0; i<l.length; i++) { var c = l[i]; while(c.charAt(0) == ' ') @@ -365,14 +385,14 @@ function rlDropDown(lnk) { var rs = tag('ul', tag('li', tag('b', mt('_vnpage_uopt_relrstat')))); var vs = tag('ul', tag('li', tag('b', mt('_vnpage_uopt_relvstat')))); for(var i=0; i<rlst_rstat.length; i++) { - var val = mt('_rlst_rstat_'+rlst_rstat[i]); + var val = mt('_rlst_rstat_'+rlst_rstat[i]); // l10n /_rlst_rstat_\d+/ if(st[0] && st[0].indexOf(val) >= 0) rs.appendChild(tag('li', tag('i', val))); else rs.appendChild(tag('li', tag('a', {href:'#', rl_rid:relid, rl_act:'r'+rlst_rstat[i], onclick:rlMod}, val))); } for(var i=0; i<rlst_vstat.length; i++) { - var val = mt('_rlst_vstat_'+rlst_vstat[i]); + var val = mt('_rlst_vstat_'+rlst_vstat[i]); // l10n /_rlst_vstat_\d+/ if(st[1] && st[1].indexOf(val) >= 0) vs.appendChild(tag('li', tag('i', val))); else @@ -1040,7 +1060,7 @@ function scrAdd(id, nsfw, rel) { 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')), tag('td', - tag('b', mt(id ? '_vnedit_scr_fetching' : '_vnedit_scr_uploading')), + tag('b', id ? mt('_vnedit_scr_fetching') : mt('_vnedit_scr_uploading')), tag('br', null), id ? null : mt('_vnedit_scr_upl_msg'), tag('br', null), @@ -1189,7 +1209,8 @@ function scrUploadComplete() { 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')); + alert(tr.scr_id == -10 ? mt('_vnedit_scr_oops') : + tr.scr_id == -1 ? mt('_vnedit_scr_errformat') : mt('_vnedit_scr_errempty')); scrDel(tr); } else { tr.id = 'scr_tr_'+tr.scr_id; @@ -1220,7 +1241,7 @@ function scrSerialize() { byId('screenshots').value = r.join(' '); } -if(byId('jt_box_vn_scr')) +if(byId('jt_box_vn_scr') && byId('scr_table')) scrLoad(); @@ -1232,7 +1253,7 @@ var tglSpoilers = []; function tglLoad() { for(var i=0; i<=3; i++) - tglSpoilers[i] = mt('_tagv_spoil'+i); + tglSpoilers[i] = mt('_tagv_spoil'+i); // l10n /_tagv_spoil\d+/ // tag dropdown search dsInit(byId('tagmod_tag'), '/xml/tags.xml?q=', function(item, tr) { @@ -1872,7 +1893,7 @@ if(byId('expandlist')) { var lnk = byId('expandlist'); setexpand = function() { var exp = getCookie('histexpand') == 1; - setText(lnk, mt(exp ? '_js_collapse' : '_js_expand')); + setText(lnk, exp ? mt('_js_collapse') : mt('_js_expand')); var tbl = lnk; while(tbl.nodeName.toLowerCase() != 'table') tbl = tbl.parentNode; diff --git a/data/style.css b/data/style.css index 191ea231..638782cc 100644 --- a/data/style.css +++ b/data/style.css @@ -548,12 +548,12 @@ div.mainbox.discussions td.tc4 { div.mainbox.discussions a.locked { text-decoration: line-through; } -div.mainbox.discussions td.boards { - padding-top: 0; - padding-left: 60px; +div.mainbox.discussions b.boards { + padding-left: 10px; + font-weight: normal; } -div.mainbox.discussions td.boards a { - color: $grayedout$!important +div.mainbox.discussions b.boards a { + color: $grayedout$!important; } div.discussions td.tc2 { width: 50px; } div.discussions td.tc3 { width: 90px; } @@ -1144,7 +1144,7 @@ div#iv_view { /****** Icons *******/ .icons { - background: url(/f/icons.png) no-repeat; + background: url(/f/icons.png?$version$) no-repeat; width: 16px; height: 14px; margin: 0 2px 0 0; @@ -1192,6 +1192,7 @@ a .icons { cursor: pointer } .icons.msx { background-position: -32px -56px; } .icons.nes { background-position: -32px -70px; } .icons.dos { background-position: -32px -84px; } +.icons.ios { background-position: -32px -98px; } .icons.cs { background-position: -48px 0px; } .icons.da { background-position: -48px -11px; } diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index be1191c6..8d146d32 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -14,6 +14,7 @@ use POE::Filter::VNDBAPI 'encode_filters'; use Digest::SHA 'sha256_hex'; use Encode 'encode_utf8'; use Time::HiRes 'time'; # important for throttling +use VNDBUtil 'normalize_query'; use JSON::XS; @@ -134,6 +135,14 @@ sub filtertosql { # no further processing required for type=undef return $sql if !defined $type; + # split a string into an array of strings + if($type eq 'str' && $o{split}) { + $value = [ $o{split}->($value) ]; + # assume that this match failed if the function doesn't return anything useful + return 'false' if !@$value || grep(!defined($_) || ref($_), @$value); + $type = 'stra'; + } + # pre-process the argument(s) my @values = ref($value) eq 'ARRAY' ? @$value : $value; for my $v (!$o{process} ? () : @values) { @@ -349,7 +358,7 @@ sub client_input { } # unknown command - return cerr $c, 'parse', "Unkown command '$cmd'" if $cmd ne 'get'; + return cerr $c, 'parse', "Unknown command '$cmd'" if $cmd ne 'get'; } @@ -366,7 +375,7 @@ sub login { # note that 'true' and 'false' are also refs ref $arg->{$_} && return cerr $c, badarg => "Field '$_' must be a scalar", field => $_; } - return cerr $c, badarg => 'Unkonwn protocol version', field => 'protocol' if $arg->{protocol} ne '1'; + return cerr $c, badarg => 'Unknown protocol version', field => 'protocol' if $arg->{protocol} ne '1'; return cerr $c, badarg => 'Invalid client name', field => 'client' if $arg->{client} !~ /^[a-zA-Z0-9 _-]{3,50}$/; return cerr $c, badarg => 'Invalid client version', field => 'clientver' if $arg->{clientver} !~ /^\d+(\.\d+)?$/; return cerr $c, sesslimit => "Too many open sessions for user '$arg->{username}'", max_allowed => $_[HEAP]{sess_per_user} @@ -419,12 +428,12 @@ sub get_results { sub get_vn { my $get = $_[ARG0]; - return cerr $get->{c}, getinfo => "Unkown info flag '$_'", flag => $_ + return cerr $get->{c}, getinfo => "Unknown info flag '$_'", flag => $_ for (grep !/^(basic|details|anime|relations)$/, @{$get->{info}}); my $select = 'v.id, v.latest'; $select .= ', vr.title, vr.original, v.c_released, v.c_languages::text[], v.c_platforms' if grep /basic/, @{$get->{info}}; - $select .= ', vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai' if grep /details/, @{$get->{info}}; + $select .= ', vr.image, vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai' if grep /details/, @{$get->{info}}; my @placeholders; my $where = encode_filters $get->{filters}, \&filtertosql, $get->{c}, \@placeholders, [ @@ -444,17 +453,15 @@ sub get_vn { ], [ 'platforms', [ undef, "v.c_platforms :op: ''", {qw|= = != <>|} ], [ str => 'v.c_platforms :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ], - [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_platforms LIKE :value:', \'like' ], - [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_platforms NOT LIKE :value:', \'like' ], + [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_platforms LIKE :value:', process => \'like' ], + [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_platforms NOT LIKE :value:', process => \'like' ], ], [ 'languages', [ undef, "v.c_languages :op: '{}'", {qw|= = != <>|} ], [ str => ':op: (v.c_languages && ARRAY[:value:]::language[])', {'=' => '', '!=' => 'NOT'} ], [ stra => ':op: (v.c_languages && ARRAY[:value:]::language[])', {'=' => '', '!=' => 'NOT'}, join => ',' ], ], [ 'search', - [ str => '(vr.title ILIKE :value: OR vr.alias ILIKE :value: OR v.id IN( - SELECT rv.vid FROM releases r JOIN releases_rev rr ON rr.id = r.latest JOIN releases_vn rv ON rv.rid = rr.id - WHERE rr.title ILIKE :value: OR rr.original ILIKE :value: - ))', {'~', 1}, process => \'like' ], + [ str => '(:value:)', {'=',1}, split => \&normalize_query, + join => ' AND ', serialize => 'v.c_search LIKE :value:', process => \'like' ], ], ]; my $last = sqllast $get, 'id', { @@ -496,6 +503,7 @@ sub get_vn_res { encubed => delete($_->{l_encubed})||undef, renai => delete($_->{l_renai}) ||undef }; + $_->{image} = $_->{image} ? sprintf '%s/cv/%02d/%d.jpg', $VNDB::S{url_static}, $_->{image}%100, $_->{image} : undef; } } $get->{more} = pop(@$res)&&1 if @$res > $_[HEAP]{results}; @@ -552,7 +560,7 @@ sub get_vn_res { sub get_release { my $get = $_[ARG0]; - return cerr $get->{c}, getinfo => "Unkown info flag '$_'", flag => $_ for (grep !/^(basic|details|vn|producers)$/, @{$get->{info}}); + return cerr $get->{c}, getinfo => "Unknown info flag '$_'", flag => $_ for (grep !/^(basic|details|vn|producers)$/, @{$get->{info}}); my $select = 'r.id, r.latest'; $select .= ', rr.title, rr.original, rr.released, rr.type, rr.patch, rr.freeware, rr.doujin' if grep /basic/, @{$get->{info}}; @@ -715,7 +723,7 @@ sub get_release_res { sub get_producer { my $get = $_[ARG0]; - return cerr $get->{c}, getinfo => "Unkown info flag '$_'", flag => $_ + return cerr $get->{c}, getinfo => "Unknown info flag '$_'", flag => $_ for (grep !/^(basic|details|relations)$/, @{$get->{info}}); my $select = 'p.id, p.latest'; @@ -861,6 +869,8 @@ Filter definitions: 'bool' sql string: The relevant SQL string, with :op: and :value: subsistutions. :value: is not available for type=undef + split: (only when the type is str) + sub, splits the string into an array and further processes it as if it was of type 'stra' join: (only used when type is an array) scalar, join string used when joining multiple values. serialize: (serializes the values before join()'ing, only for arrays) diff --git a/lib/Multi/Anime.pm b/lib/Multi/Anime.pm index 05852e49..c31e7e8c 100644 --- a/lib/Multi/Anime.pm +++ b/lib/Multi/Anime.pm @@ -230,8 +230,7 @@ sub receivepacket { # input, wheelid } $col[1] = undef if !$col[1]; $col[2] = undef if !$col[2] || $col[2] =~ /^0,/; - $col[3] = $1 if $col[3] =~ /^([0-9]+)/; # remove multi-year stuff - $col[3] = undef if !$col[3]; + $col[3] = $col[3] =~ /^([0-9]+)/ ? $1 : undef; $col[4] = $_[HEAP]{anime_types}{ lc($col[4]) }; $col[5] = undef if !$col[5]; $col[6] = undef if !$col[6]; diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 0b19ed05..bce8f934 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -16,6 +16,7 @@ use POE qw| use POE::Component::IRC::Common ':ALL'; use URI::Escape 'uri_escape_utf8'; use Time::HiRes 'time'; +use VNDBUtil 'normalize_query'; use constant { @@ -53,7 +54,7 @@ sub spawn { _start shutdown throttle_gc irc_001 irc_public irc_ctcp_action irc_msg command idlequote reply notify_init notify notify_result cmd_info cmd_list cmd_uptime cmd_vn cmd_vn_results cmd_p cmd_p_results cmd_quote cmd_quote_result - cmd_say cmd_me cmd_notifications cmd_eval cmd_die cmd_post cmd_api vndbid formatid + cmd_scr cmd_scr_result cmd_say cmd_me cmd_notifications cmd_eval cmd_die cmd_post cmd_api vndbid formatid |], ], heap => { @@ -73,6 +74,7 @@ sub spawn { vn => 0, # 2: only users matching the mask in @masters p => 0, # |8: has to be addressed to the bot (e.g. 'Multi: eval' instead of '!eval') quote => 0, + scr => 0, say => 1|8, me => 1|8, notifications => 1, @@ -343,27 +345,25 @@ sub cmd_uptime { sub cmd_vn { - (my $q = $_[ARG]||'') =~ s/%//g; + my $q = $_[ARG]; return $_[KERNEL]->yield(reply => $_[DEST], 'You forgot the search query, dummy~~!', $_[USER]) if !$q; return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER]) if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 3; - $_[KERNEL]->post(pg => query => q| + my @q = normalize_query($q); + return $_[KERNEL]->yield(reply => $_[DEST], + "Couldn't do anything with that search query, you might want to add quotes or use longer words.", + $_[USER]) if !@q; + + my $w = join ' AND ', map 'v.c_search LIKE ?', @q; + $_[KERNEL]->post(pg => query => qq{ SELECT 'v'::text AS type, v.id, vr.title - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - WHERE v.hidden = FALSE AND (vr.title ILIKE $1 - OR vr.alias ILIKE $1 - OR v.id IN( - SELECT rv.vid - FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = rr.id - WHERE rr.title ILIKE $1 - OR rr.original ILIKE $1 - )) - ORDER BY vr.title - LIMIT 6|, [ "%$q%" ], 'cmd_vn_results', \@_); + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + WHERE NOT v.hidden AND $w + ORDER BY vr.title + LIMIT 6 + }, [ map "%$_%", @q ], 'cmd_vn_results', \@_); } @@ -413,6 +413,31 @@ sub cmd_quote_result { # 1, res, dest } +sub cmd_scr { + my $q = $_[ARG]||''; + $q = $1 if $q =~ /([0-9]+)\.jpg/; + return $_[KERNEL]->yield(reply => $_[DEST], + q|Sorry, I failed to comprehend which screenshot you'd like me to lookup for you,| + .q| please understand that Yorhel was not willing to supply me with mind reading capabilities.|, + $_[USER]) if !$q || $q !~ /^[0-9]+$/; + return $_[KERNEL]->yield(reply => $_[DEST], 'Stop abusing me, it\'s not like I enjoy spamming this channel!', $_[USER]) + if throttle $_[HEAP], "query-$_[USER]-$_[DEST][0]", 60, 3; + $_[KERNEL]->post(pg => query => q| + SELECT 'v'::text AS type, v.id, vr.title + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + JOIN vn_rev vr2 ON vr2.vid = v.id + JOIN vn_screenshots vs ON vs.vid = vr2.id + WHERE vs.scr = ? LIMIT 1|, [ $q ], "cmd_scr_result", \@_); +} + + +sub cmd_scr_result { + return $_[KERNEL]->yield(reply => $_[ARG2][DEST], 'Couldn\'t find VN with that screenshot.', $_[ARG2][USER]) if $_[ARG0] < 1; + $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], [$_[ARG2][DEST]]); +} + + sub cmd_say { my $chan = $_[ARG] =~ s/^(#[a-zA-Z0-9-_.]+) // ? $1 : $_[DEST]; $irc->yield(privmsg => $chan, $_[ARG]); diff --git a/lib/Multi/Image.pm b/lib/Multi/Image.pm index f947bf7d..ad1d436c 100644 --- a/lib/Multi/Image.pm +++ b/lib/Multi/Image.pm @@ -10,6 +10,7 @@ use warnings; use POE; use Image::Magick; use Time::HiRes 'time'; +use VNDBUtil 'imgsize'; sub spawn { @@ -21,8 +22,6 @@ sub spawn { |], ], heap => { - cvsize => [ 256, 400 ], - scrsize => [ 136, 102 ], cvpath => $VNDB::ROOT.'/static/cv', sfpath => $VNDB::ROOT.'/static/sf', stpath => $VNDB::ROOT.'/static/st', @@ -67,13 +66,15 @@ sub cv_process { # num, res my $im = Image::Magick->new; $im->Read($img); $im->Set(magick => 'JPEG'); - my($old, $new) = do_resize($im, $_[HEAP]{cvsize}); + my($ow, $oh) = ($im->Get('width'), $im->Get('height')); + my($nw, $nh) = imgsize($ow, $oh, @{$VNDB::S{cv_size}}); + $im->Thumbnail(width => $nw, height => $nh); $im->Set(quality => 80); $im->Write($img); $_[KERNEL]->post(pg => do => 'UPDATE vn_rev SET image = image*-1 WHERE image = ?', [ -1*$id ]); $_[KERNEL]->call(core => log => 'Processed cover image %d in %.2fs: %.2fkB (%dx%d) -> %.2fkB (%dx%d)', - $id, time-$start, $os/1024, $$old[0], $$old[1], (-s $img)/1024, $$new[0], $$new[1]); + $id, time-$start, $os/1024, $ow, $oh, (-s $img)/1024, $nw, $nh); $_[KERNEL]->yield('cv_check'); } @@ -102,46 +103,24 @@ sub scr_process { # num, res $im->Write($sf); # create thumbnail - my($old, $new) = do_resize($im, $_[HEAP]{scrsize}); + my($ow, $oh) = ($im->Get('width'), $im->Get('height')); + my($nw, $nh) = imgsize($ow, $oh, @{$VNDB::S{scr_size}}); + $im->Thumbnail(width => $nw, height => $nh); $im->Set(quality => 90); $im->Write($st); $_[KERNEL]->post(pg => do => 'UPDATE screenshots SET processed = true, width = ?, height = ? WHERE id = ?', - [ $$old[0], $$old[1], $id ] + [ $ow, $oh, $id ] ); $_[KERNEL]->call(core => log => 'Processed screenshot #%d in %.2fs: %.1fkB -> %.1fkB (%dx%d), thumb: %.1fkB (%dx%d)', - $id, time-$start, $os/1024, (-s $sf)/1024, $$old[0], $$old[1], (-s $st)/1024, $$new[0], $$new[1] + $id, time-$start, $os/1024, (-s $sf)/1024, $ow, $oh, (-s $st)/1024, $nw, $nh ); $_[KERNEL]->yield('scr_check'); } - - -# non-POE helper function -sub do_resize { # im, [ maxwidth, maxheight ] - my($im, $dim) = @_; - - my($w, $h) = ($im->Get('width'), $im->Get('height')); - $dim = [ $w, $h ] if !$dim; - my($ow, $oh) = ($w, $h); - if($w > $$dim[0] || $h > $$dim[1]) { - if($w/$h > $$dim[0]/$$dim[1]) { # width is the limiting factor - $h *= $$dim[0]/$w; - $w = $$dim[0]; - } else { - $w *= $$dim[1]/$h; - $h = $$dim[1]; - } - } - $im->Thumbnail(width => $w, height => $h); - - return ([$ow, $oh], [$w, $h]); -} - - 1; diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index be267aba..5b092d0a 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -9,6 +9,7 @@ use strict; use warnings; use POE; use PerlIO::gzip; +use VNDBUtil 'normalize_titles'; sub spawn { @@ -17,13 +18,15 @@ sub spawn { package_states => [ $p => [qw| _start shutdown set_daily daily set_monthly monthly log_stats - vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions + vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions cleannotifications vncache_full usercache statscache logrotate + vnsearch_check vnsearch_gettitles vnsearch_update |], ], heap => { - daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions|], + daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions cleannotifications|], monthly => [qw|vncache_full usercache statscache logrotate|], + vnsearch_checkdelay => 3600, @_, }, ); @@ -35,6 +38,8 @@ sub _start { $_[KERNEL]->sig(shutdown => 'shutdown'); $_[KERNEL]->yield('set_daily'); $_[KERNEL]->yield('set_monthly'); + $_[KERNEL]->yield('vnsearch_check'); + $_[KERNEL]->post(pg => listen => vnsearch => 'vnsearch_check'); } @@ -158,6 +163,13 @@ sub cleansessions { } +sub cleannotifications { + $_[KERNEL]->post(pg => do => + q|DELETE FROM notifications WHERE read < NOW()-'1 month'::interval|, + undef, 'log_stats', 'cleannotifications'); +} + + # # M O N T H L Y J O B S # @@ -235,5 +247,53 @@ sub logrotate { } +# +# V N S E A R C H C A C H E +# + + +sub vnsearch_check { + $_[KERNEL]->call(pg => query => + 'SELECT id FROM vn WHERE NOT hidden AND c_search IS NULL LIMIT 1', + undef, 'vnsearch_gettitles'); +} + + +sub vnsearch_gettitles { # num, res + return $_[KERNEL]->delay('vnsearch_check', $_[HEAP]{vnsearch_checkdelay}) if $_[ARG0] == 0; + my $id = $_[ARG1][0]{id}; + + # fetch the titles + $_[KERNEL]->call(pg => query => q{ + SELECT vr.title, vr.original, vr.alias + FROM vn v + JOIN vn_rev vr ON vr.id = v.latest + WHERE v.id = ? + UNION + SELECT rr.title, rr.original, NULL + FROM releases r + JOIN releases_rev rr ON rr.id = r.latest + JOIN releases_vn rv ON rv.rid = r.latest + WHERE rv.vid = ? + AND NOT r.hidden + }, [ $id, $id ], 'vnsearch_update', $id); +} + + +sub vnsearch_update { # num, res, vid, time + my($res, $id, $time) = @_[ARG1..ARG3]; + my @t = map +($_->{title}, $_->{original}), @$res; + # alias fields are a bit special + for (@$res) { + push @t, split /,/, $_->{alias} if $_->{alias}; + } + my $t = normalize_titles(@t); + $_[KERNEL]->call(core => log => 'Updated search cache for v%d', $id); + $_[KERNEL]->call(pg => do => + q|UPDATE vn SET c_search = ? WHERE id = ?|, + [ $t, $id ], 'vnsearch_check'); +} + + 1; diff --git a/lib/Multi/Sitemap.pm b/lib/Multi/Sitemap.pm deleted file mode 100644 index 85afae57..00000000 --- a/lib/Multi/Sitemap.pm +++ /dev/null @@ -1,154 +0,0 @@ - -# -# Multi::Sitemap - The sitemap generator -# - -package Multi::Sitemap; - -use strict; -use warnings; -use POE; -use XML::Writer; -use PerlIO::gzip; -use POSIX 'strftime'; -use Time::HiRes 'gettimeofday', 'tv_interval'; - - -sub spawn { - my $p = shift; - POE::Session->create( - package_states => [ - $p => [qw| _start shutdown check_age generate addquery addurl finish |], - ], - heap => { - output => $VNDB::ROOT.'/www/sitemap.xml.gz', - max_age => 24*3600, # seconds - check_delay => 3600, # seconds - @_, - } - ); -} - - -sub _start { - $_[KERNEL]->alias_set('sitemap'); - $_[KERNEL]->yield('check_age'); - $_[KERNEL]->sig(shutdown => 'shutdown'); -} - - -sub shutdown { - $_[KERNEL]->delay('check_age'); - $_[KERNEL]->alias_remove('sitemap'); -} - - -sub check_age { - # check the last modified time of the sitemap, and if it's older than max_age, regenerate it - $_[KERNEL]->yield('generate') if !-f $_[HEAP]{output} || (stat $_[HEAP]{output})[9] < time-$_[HEAP]{max_age}; - - # check sitemap again later - $_[KERNEL]->delay(check_age => $_[HEAP]{check_delay}); -} - - -sub generate { - $_[KERNEL]->call(core => log => '(Re)generating sitemap'); - - $_[HEAP]{urls} = 0; - $_[HEAP]{start} = [ gettimeofday ]; - - open($_[HEAP]{io}, '>:gzip', $_[HEAP]{output}) || die $1; - $_[HEAP]{xml} = new XML::Writer( - OUTPUT => $_[HEAP]{io}, - ENCODING => 'UTF-8', - DATA_MODE => 1, - DATA_INDENT => 1 - ); - $_[HEAP]{xml}->xmlDecl(); - $_[HEAP]{xml}->startTag('urlset', xmlns => 'http://www.sitemaps.org/schemas/sitemap/0.9'); - - # / - $_[KERNEL]->call(sitemap => addurl => '', 'daily'); - - # /d+ - /([0-9]+)$/ && $_[KERNEL]->call(sitemap => addurl => 'd'.$1, 'monthly', [stat $_]->[9]) - for (glob "$VNDB::ROOT/data/docs/*"); - - # /v/[browse] & /p/[browse] - $_[KERNEL]->call(sitemap => addurl => $_, 'weekly') - for (map { 'v/'.$_, 'p/'.$_ } 'a'..'z', 0, 'all'); - - # /v+ - $_[KERNEL]->post(pg => query => q| - SELECT v.id, extract('epoch' from c.added) as added - FROM vn v - JOIN vn_rev vr ON vr.id = v.latest - JOIN changes c ON vr.id = c.id - WHERE v.hidden = FALSE - ORDER BY v.id|, - undef, 'addquery', [ 'v', 0.7 ]); - - # /r+ - $_[KERNEL]->post(pg => query => q| - SELECT r.id, extract('epoch' from c.added) as added - FROM releases r - JOIN releases_rev rr ON rr.id = r.latest - JOIN changes c ON c.id = rr.id - WHERE r.hidden = FALSE - ORDER BY r.id|, - undef, 'addquery', [ 'r', 0.5 ]); - - # /p+ - $_[KERNEL]->post(pg => query => q| - SELECT p.id, extract('epoch' from c.added) as added - FROM producers p - JOIN producers_rev pr ON pr.id = p.latest - JOIN changes c ON c.id = pr.id - WHERE p.hidden = FALSE - ORDER BY p.id|, - undef, 'addquery', [ 'p', 0.3 ]); - - # /g+ - $_[KERNEL]->post(pg => query => q| - SELECT t.id, extract('epoch' from t.added) as added - FROM tags t - WHERE state = 2 - ORDER BY t.id|, - undef, 'addquery', [ 'g', 0.3, 1 ]); -} - - -sub addquery { # num, db-res, [ type, priority, finish ] - $_[KERNEL]->call(sitemap => addurl => $_[ARG2][0].$_->{id}, 'weekly', $_->{added}, $_[ARG2][1]) - for(@{$_[ARG1]}); - $_[KERNEL]->yield('finish') if $_[ARG2][2]; -} - - -sub finish { - $_[HEAP]{xml}->endTag('urlset'); - $_[HEAP]{xml}->end(); - close $_[HEAP]{io}; - - $_[KERNEL]->call(core => log => 'Wrote %d URLs (%.1f kB gzipped) to the sitemap in %.2f seconds', - $_[HEAP]{urls}, (-s $_[HEAP]{output})/1024, tv_interval($_[HEAP]{start})); - - delete @{$_[HEAP]}{qw| xml io start urls |}; -} - - -sub addurl { # loc, changefreq, lastmod, priority - $_[HEAP]{xml}->startTag('url'); - $_[HEAP]{xml}->dataElement(loc => $VNDB::S{url}.'/'.$_[ARG0]); - $_[HEAP]{xml}->dataElement(changefreq => $_[ARG1]) if defined $_[ARG1]; - $_[HEAP]{xml}->dataElement(lastmod => strftime('%Y-%m-%d', gmtime $_[ARG2])) if defined $_[ARG2]; - $_[HEAP]{xml}->dataElement(priority => $_[ARG3]) if defined $_[ARG3]; - $_[HEAP]{xml}->endTag('url'); - $_[HEAP]{urls}++; -} - - -1; - - diff --git a/lib/POE/Filter/VNDBAPI.pm b/lib/POE/Filter/VNDBAPI.pm index 816643d9..ceb9ae2d 100644 --- a/lib/POE/Filter/VNDBAPI.pm +++ b/lib/POE/Filter/VNDBAPI.pm @@ -103,7 +103,11 @@ sub get_one { # $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; + if(!defined $str) { + my $err = $@; + $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//; + return _err "Encoding error: $err" if !defined $str; + } # get command return _err "Invalid command" if !($str =~ s/^$WS*([a-z]+)$WS*//); @@ -121,7 +125,7 @@ sub get_one { $err =~ s/,? at .+ line [0-9]+[\.\r\n ]*$//; return _err "Invalid JSON value in filter expression: $err"; } - $str = substr $str, $chars; + $str = $chars > length($str) ? substr $str, $chars : ''; push @ret, $value; } diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm index 2c1b287f..bfa56260 100644 --- a/lib/VNDB/DB/Tags.pm +++ b/lib/VNDB/DB/Tags.pm @@ -147,6 +147,10 @@ sub dbTagAdd { sub dbTagMerge { my($self, $id, @merge) = @_; + $self->dbExec(q| + DELETE FROM tags_vn tv + WHERE tag IN(!l) + AND EXISTS(SELECT 1 FROM tags_vn ti WHERE ti.tag = ? AND ti.uid = tv.uid AND ti.vid = tv.vid)|, \@merge, $id); $self->dbExec('UPDATE tags_vn SET tag = ? WHERE tag IN(!l)', $id, \@merge); $self->dbExec('UPDATE tags_aliases SET tag = ? WHERE tag IN(!l)', $id, \@merge); $self->dbExec('INSERT INTO tags_aliases (tag, alias) VALUES (?, ?)', $id, $_->{name}) diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index fe4f7475..394e8c06 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -4,7 +4,7 @@ package VNDB::DB::VN; use strict; use warnings; use Exporter 'import'; -use VNDB::Func 'gtintype'; +use VNDB::Func 'gtintype', 'normalize_query'; use Encode 'decode_utf8'; our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; @@ -19,7 +19,7 @@ sub dbVNGet { $o{page} ||= 1; $o{what} ||= ''; - my %where = ( + my @where = ( $o{id} ? ( 'v.id = ?' => $o{id} ) : (), $o{rev} ? ( @@ -38,36 +38,13 @@ sub dbVNGet { ) : (), $o{tags_exclude} && @{$o{tags_exclude}} ? ( 'v.id NOT IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l))' => [ $o{tags_exclude} ] ) : (), + $o{search} ? ( + map +('v.c_search like ?', "%$_%"), normalize_query($o{search})) : (), # don't fetch hidden items unless we ask for an ID !$o{id} && !$o{rev} ? ( 'v.hidden = FALSE' => 0 ) : (), ); - if($o{search}) { - my @w; - for (split /[ -,._]/, $o{search}) { - s/%//g; - if(/^[0-9]{12,13}$/ && gtintype($_)) { - push @w, 'irr.gtin = ?', $_; - } elsif(length($_) > 0) { - $_ = "%$_%"; - push @w, '(ivr.title ILIKE ? OR ivr.original ILIKE ? OR ivr.alias ILIKE ? OR irr.title ILIKE ? OR irr.original ILIKE ?)', - [ $_, $_, $_, $_, $_ ]; - } - } - push @w, '(irr.id IS NULL OR ir.latest = irr.id)' => 1 if @w; - $where{ q| - v.id IN(SELECT iv.id - FROM vn iv - JOIN vn_rev ivr ON iv.latest = ivr.id - LEFT JOIN releases_vn irv ON irv.vid = iv.id - LEFT JOIN releases_rev irr ON irr.id = irv.rid - LEFT JOIN releases ir ON ir.latest = irr.id - !W - GROUP BY iv.id)| - } = [ \@w ] if @w; - } - my @join = ( $o{rev} ? 'JOIN vn v ON v.id = vr.vid' : @@ -114,7 +91,7 @@ sub dbVNGet { !s !W ORDER BY !s|, - join(', ', @select), join(' ', @join), \%where, $order, + join(', ', @select), join(' ', @join), \@where, $order, ); if($o{what} =~ /relgraph/) { @@ -228,10 +205,11 @@ sub dbScreenshotGet { # Fetch random VN + screenshots sub dbScreenshotRandom { return shift->dbAll(q| - SELECT vs.scr, vr.vid, vr.title + SELECT vs.scr, vr.vid, vr.title, s.width, s.height FROM vn_screenshots vs JOIN vn v ON v.latest = vs.vid JOIN vn_rev vr ON vr.id = v.latest + JOIN screenshots s ON vs.scr = s.id WHERE vs.nsfw = FALSE AND v.hidden = FALSE ORDER BY RANDOM() LIMIT 4| diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index e00afe73..f3f9d009 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -6,155 +6,8 @@ use warnings; use YAWF ':html'; use Exporter 'import'; use POSIX 'strftime', 'ceil', 'floor'; -our @EXPORT = qw| shorten bb2html gtintype liststat clearfloat cssicon tagscore mt minage |; - - -# I would've done this as a #define if this was C... -sub shorten { - my($str, $len) = @_; - return length($str) > $len ? substr($str, 0, $len-3).'...' : $str; -} - - -# Arguments: input, and optionally the maximum length -# Parses: -# [url=..] [/url] -# [raw] .. [/raw] -# [spoiler] .. [/spoiler] -# [quote] .. [/quote] -# [code] .. [/code] -# v+, v+.+ -# http://../ -sub bb2html { - my $raw = shift; - my $maxlength = shift; - $raw =~ s/\r//g; - $raw =~ s/\n{5,}/\n\n/g; - return '' if !$raw && $raw ne "0"; - - my($result, $length, $rmnewline, @open) = ('', 0, 0, 'first'); - - my $e = sub { - local $_ = shift; - s/&/&/g; - s/>/>/g; - s/</</g; - s/\n/<br \/>/g if !$maxlength; - s/\n/ /g if $maxlength; - return $_; - }; - - for (split /(\s|\n|\[[^\]]+\])/, $raw) { - next if !defined $_; - next if $_ eq ''; - - # (note to self: stop using unreadable hacks like these!) - $rmnewline-- && $_ eq "\n" && next if $rmnewline; - - my $lit = $_; - if($open[$#open] ne 'raw' && $open[$#open] ne 'code') { - if (lc$_ eq '[raw]') { push @open, 'raw'; next } - elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= '<b class="spoiler">'; next } - elsif (lc$_ eq '[quote]') { - push @open, 'quote'; - $result .= '<div class="quote">' if !$maxlength; - $rmnewline = 1; - next - } elsif (lc$_ eq '[code]') { - push @open, 'code'; - $result .= '<pre>' if !$maxlength; - $rmnewline = 1; - next - } elsif (lc$_ eq '[/spoiler]') { - if($open[$#open] eq 'spoiler') { - $result .= '</b>'; - pop @open; - } - next; - } elsif (lc$_ eq '[/quote]') { - if($open[$#open] eq 'quote') { - $result .= '</div>' if !$maxlength; - $rmnewline = 1; - pop @open; - } - next; - } elsif(lc$_ eq '[/url]') { - if($open[$#open] eq 'url') { - $result .= '</a>'; - pop @open; - } - next; - } elsif(s{\[url=((https?://|/)[^\]>]+)\]}{<a href="$1" rel="nofollow">}i) { - $result .= $_; - push @open, 'url'; - next; - } elsif(!grep(/url/, @open) && - s{(.*)(http|https)://(.+[\d\w=/-])(.*)} - {$e->($1).qq|<a href="$2://|.$e->($3, 1).'" rel="nofollow">'.$e->('link').'</a>'.$e->($4)}e) { - $length += 4; - last if $maxlength && $length > $maxlength; - $result .= $_; - next; - } elsif(!grep(/url/, @open) && ( - s{^(.*[^\w]|)([tdvpr][1-9][0-9]*)\.([1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2.$3">$2.$3</a>|.$e->($4)}e || - s{^(.*[^\w]|)([tdvprug][1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2">$2</a>|.$e->($3)}e)) { - $length += length $lit; - last if $maxlength && $length > $maxlength; - $result .= $_; - next; - } - } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') { - pop @open; - next; - } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') { - $result .= '</pre>' if !$maxlength; - pop @open; - next; - } - - # normal text processing - $length += length $_; - last if $maxlength && $length > $maxlength; - $result .= $e->($_); - } - - # close open tags - while((local $_ = pop @open) ne 'first') { - $result .= $_ eq 'url' ? '</a>' : $_ eq 'spoiler' ? '</b>' : ''; - $result .= $_ eq 'quote' ? '</div>' : $_ eq 'code' ? '</pre>' : '' if !$maxlength; - } - $result .= '...' if $maxlength && $length > $maxlength; - - return $result; -} - - -# GTIN code as argument, -# Returns 'JAN', 'EAN', 'UPC' or undef, -# Also 'normalizes' the first argument in place -sub gtintype { - $_[0] =~ s/[^\d]+//g; - $_[0] = ('0'x(12-length $_[0])) . $_[0] if length($_[0]) < 12; # pad with zeros to GTIN-12 - my $c = shift; - return undef if $c !~ /^[0-9]{12,13}$/; - $c = "0$c" if length($c) == 12; # pad with another zero for GTIN-13 - - # calculate check digit according to - # http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html#how - my @n = reverse split //, $c; - my $n = shift @n; - $n += $n[$_] * ($_ % 2 != 0 ? 1 : 3) for (0..$#n); - return undef if $n % 10 != 0; - - # Do some rough guesses based on: - # http://www.gs1.org/productssolutions/barcodes/support/prefix_list.html - # and http://en.wikipedia.org/wiki/List_of_GS1_country_codes - local $_ = $c; - return 'JAN' if /^4[59]/; # prefix code 450-459 & 490-499 - return 'UPC' if /^(?:0[01]|0[6-9]|13|75[45])/; # prefix code 000-019 & 060-139 & 754-755 - return undef if /^(?:0[2-5]|2|97[789]|9[6-9])/; # some codes we don't want: 020–059 & 200-299 & 977-999 - return 'EAN'; # let's just call everything else EAN :) -} +use VNDBUtil; +our @EXPORT = (@VNDBUtil::EXPORT, qw| liststat clearfloat cssicon tagscore mt minage |); # Argument: hashref with rstat and vstat diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index c8431c2c..9cc83fc7 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -319,7 +319,7 @@ sub board { end; end; - _threadlist($self, $list, $f, $np, "/t/$type$iid") if @$list; + _threadlist($self, $list, $f, $np, "/t/$type$iid", $type.$iid) if @$list; $self->htmlFooter; } @@ -340,7 +340,7 @@ sub index { for (qw|an db v p u|) { my $list = $self->dbThreadGet( type => $_, - results => 5, + results => $_ eq 'db' || $_ eq 'v' ? 10 : 5, page => 1, what => 'firstpost lastpost boardtitles', sort => 'lastpost', reverse => 1, @@ -348,7 +348,7 @@ sub index { h1 class => 'boxtitle'; a href => "/t/$_", mt "_dboard_$_"; end; - _threadlist($self, $list, {p=>1}, 0, "/t"); + _threadlist($self, $list, {p=>1}, 0, "/t", $_); } $self->htmlFooter; @@ -356,7 +356,7 @@ sub index { sub _threadlist { - my($self, $list, $f, $np, $url) = @_; + my($self, $list, $f, $np, $url, $board) = @_; $self->htmlBrowse( items => $list, options => $f, @@ -374,6 +374,18 @@ sub _threadlist { Tr $n % 2 ? ( class => 'odd' ) : (); td class => 'tc1'; a $o->{locked} ? ( class => 'locked' ) : (), href => "/t$o->{id}", shorten $o->{title}, 50; + b class => 'boards'; + my $i = 1; + my @boards = sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } grep $_->{type}.($_->{iid}||'') ne $board, @{$o->{boards}}; + for(@boards) { + last if $i++ > 4; + txt ', ' if $i > 2; + a href => "/t/$_->{type}".($_->{iid}||''), + title => $_->{original}||mt("_dboard_$_->{type}"), + shorten $_->{title}||mt("_dboard_$_->{type}"), 30; + } + txt ', ...' if @boards > 4; + end; end; td class => 'tc2', $o->{count}-1; td class => 'tc3'; @@ -387,20 +399,6 @@ sub _threadlist { end; end; end; - Tr $n % 2 ? ( class => 'odd' ) : (); - td colspan => 4, class => 'boards'; - txt ' > '; - my $i = 1; - for(sort { $a->{type}.$a->{iid} cmp $b->{type}.$b->{iid} } @{$o->{boards}}) { - last if $i++ > 5; - txt ', ' if $i > 2; - a href => "/t/$_->{type}".($_->{iid}||''), - title => $_->{original}||mt("_dboard_$_->{type}"), - shorten $_->{title}||mt("_dboard_$_->{type}"), 30; - } - txt ', ...' if @{$o->{boards}} > 5; - end; - end; } ); } diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index de4bdd67..87839730 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -48,8 +48,10 @@ sub homepage { my $scr = $self->dbScreenshotRandom; p class => 'screenshots'; for (@$scr) { + my($w, $h) = imgsize($_->{width}, $_->{height}, @{$self->{scr_size}}); a href => "/v$_->{vid}", title => $_->{title}; - img src => sprintf("%s/st/%02d/%d.jpg", $self->{url_static}, $_->{scr}%100, $_->{scr}), alt => $_->{title}; + img src => sprintf("%s/st/%02d/%d.jpg", $self->{url_static}, $_->{scr}%100, $_->{scr}), + alt => $_->{title}, width => $w, height => $h; end; } end; diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index b1203dd7..bc68b88c 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -101,7 +101,7 @@ sub page { for (sort { $a->{name} cmp $b->{name} } @{$p->{relations}}); p class => 'center'; txt "\n"; - for my $r (sort keys %rel) { + for my $r (sort { $self->{prod_relations}{$a}[0] <=> $self->{prod_relations}{$b}[0] } keys %rel) { txt mt("_prodrel_$r").': '; for (@{$rel{$r}}) { a href => "/p$_->{id}", title => $_->{original}||$_->{name}, shorten $_->{name}, 40; diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index 521d4fc8..a8558575 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -17,6 +17,7 @@ YAWF::register( qr{v([1-9]\d*)/tagmod}, \&vntagmod, qr{u([1-9]\d*)/tags}, \&usertags, qr{g}, \&tagindex, + qr{g/debug}, \&fulltree, qr{xml/tags\.xml}, \&tagxml, ); @@ -34,7 +35,7 @@ sub tagpage { { name => 'm', required => 0, default => -1, enum => [qw|0 1 2|] }, ); return 404 if $f->{_err}; - my $tagspoil = $self->reqCookie('tagspoil'); + my $tagspoil = $self->reqCookie($self->{cookie_prefix}.'tagspoil'); $f->{m} = $tagspoil =~ /^[0-2]$/ ? $tagspoil : 0 if $f->{m} == -1; my($list, $np) = $t->{meta} || $t->{state} != 2 ? ([],0) : $self->dbVNGet( @@ -591,6 +592,36 @@ sub tagindex { } +# non-translatable debug page +sub fulltree { + my $self = shift; + return $self->htmlDenied if !$self->authCan('tagmod'); + + my $e; + $e = sub { + my $lst = shift; + ul style => 'list-style-type: none; margin-left: 15px'; + for (@$lst) { + li; + txt '> '; + a href => "/g$_->{id}", $_->{name}; + b class => 'grayedout', " ($_->{c_vns})" if $_->{c_vns}; + end; + $e->($_->{sub}) if $_->{sub}; + } + end; + }; + + my $tags = $self->dbTagTree(0, 25); + $self->htmlHeader(title => '[DEBUG] Tag tree', noindex => 1); + div class => 'mainbox'; + h1 '[DEBUG] Tag tree'; + $e->($tags); + end; + $self->htmlFooter; +} + + sub tagxml { my $self = shift; diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 4a3ca71b..05be95d4 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -32,7 +32,7 @@ sub userpage { my $votes = $u->{c_votes} && $self->dbVoteStats(uid => $uid); my $title = mt '_userpage_title', $u->{username}; - $self->htmlHeader(title => $title); + $self->htmlHeader(title => $title, noindex => 1); $self->htmlMainTabs('u', $u); div class => 'mainbox userpage'; h1 $title; @@ -620,6 +620,7 @@ sub notifies { td colspan => 5; input type => 'submit', name => 'markread', value => mt '_usern_but_markread'; input type => 'submit', name => 'remove', value => mt '_usern_but_remove'; + b class => 'grayedout', ' '.mt '_usern_autodel'; end; end; } diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index 43e8b3cb..f3193b57 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -25,7 +25,7 @@ sub list { { name => 'pl', required => 0, multi => 1, enum => $self->{platforms}, default => '' }, { name => 'ti', required => 0, default => '', maxlength => 200 }, { name => 'te', required => 0, default => '', maxlength => 200 }, - { name => 'sp', required => 0, default => $self->reqCookie('tagspoil') =~ /^([0-2])$/ ? $1 : 0, enum => [0..2] }, + { name => 'sp', required => 0, default => $self->reqCookie($self->{cookie_prefix}.'tagspoil') =~ /^([0-2])$/ ? $1 : 0, enum => [0..2] }, ); return 404 if $f->{_err}; $f->{q} ||= $f->{sq}; @@ -69,7 +69,7 @@ sub list { ); $self->resRedirect('/v'.$list->[0]{id}, 'temp') - if $f->{q} && @$list == 1; + if $f->{q} && @$list == 1 && $f->{p} == 1; $self->htmlHeader(title => mt('_vnbrowse_title'), search => $f->{q}); _filters($self, $f, $char, \@ignored); diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index d9c69f23..44c5ab08 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -25,6 +25,8 @@ sub edit { return $self->htmlDenied if !$self->authCan('edit') || $vid && ($v->{locked} && !$self->authCan('lock') || $v->{hidden} && !$self->authCan('del')); + my $r = $v ? $self->dbReleaseGet(vid => $v->{id}) : []; + my %b4 = !$vid ? () : ( (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai l_vnn img_nsfw ihid ilock|), anime => join(' ', sort { $a <=> $b } map $_->{id}, @{$v->{anime}}), @@ -70,6 +72,14 @@ sub edit { $frm->{img_nsfw} = $frm->{img_nsfw} ? 1 : 0; $frm->{screenshots} = join ' ', map sprintf('%d,%d,%d', $_->[0], $_->[1]?1:0, $_->[2]), sort { $a->[0] <=> $b->[0] } @$screenshots; + # weed out duplicate aliases + my %alias; + $frm->{alias} = join "\n", grep { + my $a = lc $_; + $a && !$alias{$a}++ && $a ne lc($frm->{title}) && $a ne lc($frm->{original}) + && !grep $a eq lc($_->{title}) || $a eq lc($_->{original}), @$r; + } map { s/^ +//g; s/ +$//g; $_ } split /\n/, $frm->{alias}; + # nothing changed? just redirect return $self->resRedirect("/v$vid", 'post') if $vid && !$self->reqUploadFileName('img') && !grep $frm->{$_} ne $b4{$_}, keys %b4; @@ -101,7 +111,7 @@ sub edit { $self->htmlHeader(title => $title, noindex => 1); $self->htmlMainTabs('v', $v, 'edit') if $vid; $self->htmlEditMessage('v', $v, $title); - _form($self, $v, $frm); + _form($self, $v, $frm, $r); $self->htmlFooter; } @@ -139,8 +149,7 @@ sub _uploadimage { sub _form { - my($self, $v, $frm) = @_; - my $r = $v ? $self->dbReleaseGet(vid => $v->{id}) : []; + my($self, $v, $frm, $r) = @_; $self->htmlForm({ frm => $frm, action => $v ? "/v$v->{id}/edit" : '/v/new', editsum => 1, upload => 1 }, vn_geninfo => [ mt('_vnedit_geninfo'), [ input => short => 'title', name => mt '_vnedit_frm_title' ], @@ -150,7 +159,7 @@ sub _form { [ static => content => mt '_vnedit_alias_msg' ], [ textarea => short => 'desc', name => mt('_vnedit_desc').'<br /><b class="standout">'.mt('_inenglish').'</b>', rows => 10 ], [ static => content => mt '_vnedit_desc_msg' ], - [ select => short => 'length', name => mt('_vnedit_length'), width => 300, options => + [ select => short => 'length', name => mt('_vnedit_length'), width => 450, options => [ map [ $_ => mt '_vnlength_'.$_, 2 ], @{$self->{vn_lengths}} ] ], [ input => short => 'l_wp', name => mt('_vnedit_links'), pre => 'http://en.wikipedia.org/wiki/' ], @@ -216,7 +225,9 @@ sub _form { }], ], - !@$r ? () : ( vn_scr => [ mt('_vnedit_scr'), + vn_scr => [ mt('_vnedit_scr'), !@$r ? ( + [ static => nolabel => 1, content => mt '_vnedit_scrnorel' ], + ) : ( [ hidden => short => 'screenshots' ], [ static => nolabel => 1, content => sub { div class => 'warning'; @@ -230,7 +241,7 @@ sub _form { option value => $_->{id}, sprintf '[%s] %s (r%d)', join(',', @{$_->{languages}}), $_->{title}, $_->{id} for (@$r); end; }], - ]) + )] ); } diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index f47aa8c4..b6b39483 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -102,6 +102,7 @@ sub page { end; } if($v->{alias}) { + $v->{alias} =~ s/\n/, /g; Tr ++$i % 2 ? (class => 'odd') : (); td mt '_vnpage_alias'; td $v->{alias}; @@ -465,10 +466,12 @@ sub _screenshots { end; div class => 'scr'; for (@scr) { + my($w, $h) = imgsize($_->{width}, $_->{height}, @{$self->{scr_size}}); a href => sprintf('%s/sf/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), class => sprintf('scrlnk%s%s', $_->{nsfw} ? ' nsfw':'', $_->{nsfw}&&!$self->authInfo->{show_nsfw}?' hidden':''), rel => "iv:$_->{width}x$_->{height}:scr"; - img src => sprintf('%s/st/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), alt => mt '_vnpage_scr_num', $_->{id}; + img src => sprintf('%s/st/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), + width => $w, height => $h, alt => mt '_vnpage_scr_num', $_->{id}; end; } end; diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index 2d133476..45b39249 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -20,7 +20,7 @@ sub authInit { my $self = shift; $self->{_auth} = undef; - my $cookie = $self->reqCookie('vndb_auth'); + my $cookie = $self->reqCookie($self->{cookie_prefix}.'auth'); return 0 if !$cookie; return _rmcookie($self) if length($cookie) < 41; my $token = substr($cookie, 0, 40); @@ -47,7 +47,7 @@ sub authLogin { my $expstr = strftime("%a, %d %b %Y %H:%M:%S GMT", gmtime(time + 31536000)); # keep the cookie for 1 year $self->resRedirect($to, 'post'); - $self->resHeader('Set-Cookie', "vndb_auth=$cookie; expires=$expstr; path=/; domain=$self->{cookie_domain}"); + $self->resHeader('Set-Cookie', "$self->{cookie_prefix}auth=$cookie; expires=$expstr; path=/; domain=$self->{cookie_domain}"); return 1; } @@ -59,7 +59,7 @@ sub authLogin { sub authLogout { my $self = shift; - my $cookie = $self->reqCookie('vndb_auth'); + my $cookie = $self->reqCookie($self->{cookie_prefix}.'auth'); if ($cookie && length($cookie) >= 41) { my $token = substr($cookie, 0, 40); my $uid = substr($cookie, 40); @@ -138,7 +138,7 @@ sub authPreparePass{ # removes the vndb_auth cookie sub _rmcookie { $_[0]->resHeader('Set-Cookie', - "vndb_auth= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$_[0]->{cookie_domain}"); + "$_[0]->{cookie_prefix}auth= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$_[0]->{cookie_domain}"); } diff --git a/lib/VNDBUtil.pm b/lib/VNDBUtil.pm new file mode 100644 index 00000000..bda54383 --- /dev/null +++ b/lib/VNDBUtil.pm @@ -0,0 +1,221 @@ +# Misc. utility functions, do not rely on YAWF or POE and can be used from any script + +package VNDBUtil; + +use strict; +use warnings; +use Exporter 'import'; +use Unicode::Normalize 'NFKD'; + +our @EXPORT = qw|shorten bb2html gtintype normalize normalize_titles normalize_query imgsize|; + + +sub shorten { + my($str, $len) = @_; + return length($str) > $len ? substr($str, 0, $len-3).'...' : $str; +} + + +# Arguments: input, and optionally the maximum length +# Parses: +# [url=..] [/url] +# [raw] .. [/raw] +# [spoiler] .. [/spoiler] +# [quote] .. [/quote] +# [code] .. [/code] +# v+, v+.+ +# http://../ +sub bb2html { + my $raw = shift; + my $maxlength = shift; + $raw =~ s/\r//g; + $raw =~ s/\n{5,}/\n\n/g; + return '' if !$raw && $raw ne "0"; + + my($result, $length, $rmnewline, @open) = ('', 0, 0, 'first'); + + my $e = sub { + local $_ = shift; + s/&/&/g; + s/>/>/g; + s/</</g; + s/\n/<br \/>/g if !$maxlength; + s/\n/ /g if $maxlength; + return $_; + }; + + for (split /(\s|\n|\[[^\]]+\])/, $raw) { + next if !defined $_; + next if $_ eq ''; + + # (note to self: stop using unreadable hacks like these!) + $rmnewline-- && $_ eq "\n" && next if $rmnewline; + + my $lit = $_; + if($open[$#open] ne 'raw' && $open[$#open] ne 'code') { + if (lc$_ eq '[raw]') { push @open, 'raw'; next } + elsif (lc$_ eq '[spoiler]') { push @open, 'spoiler'; $result .= '<b class="spoiler">'; next } + elsif (lc$_ eq '[quote]') { + push @open, 'quote'; + $result .= '<div class="quote">' if !$maxlength; + $rmnewline = 1; + next + } elsif (lc$_ eq '[code]') { + push @open, 'code'; + $result .= '<pre>' if !$maxlength; + $rmnewline = 1; + next + } elsif (lc$_ eq '[/spoiler]') { + if($open[$#open] eq 'spoiler') { + $result .= '</b>'; + pop @open; + } + next; + } elsif (lc$_ eq '[/quote]') { + if($open[$#open] eq 'quote') { + $result .= '</div>' if !$maxlength; + $rmnewline = 1; + pop @open; + } + next; + } elsif(lc$_ eq '[/url]') { + if($open[$#open] eq 'url') { + $result .= '</a>'; + pop @open; + } + next; + } elsif(s{\[url=((https?://|/)[^\]>]+)\]}{<a href="$1" rel="nofollow">}i) { + $result .= $_; + push @open, 'url'; + next; + } elsif(!grep(/url/, @open) && + s{(.*)(http|https)://(.+[\d\w=/-])(.*)} + {$e->($1).qq|<a href="$2://|.$e->($3, 1).'" rel="nofollow">'.$e->('link').'</a>'.$e->($4)}e) { + $length += 4; + last if $maxlength && $length > $maxlength; + $result .= $_; + next; + } elsif(!grep(/url/, @open) && ( + s{^(.*[^\w]|)([tdvpr][1-9][0-9]*)\.([1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2.$3">$2.$3</a>|.$e->($4)}e || + s{^(.*[^\w]|)([tdvprug][1-9][0-9]*)([^\w].*|)$}{$e->($1).qq|<a href="/$2">$2</a>|.$e->($3)}e)) { + $length += length $lit; + last if $maxlength && $length > $maxlength; + $result .= $_; + next; + } + } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') { + pop @open; + next; + } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') { + $result .= '</pre>' if !$maxlength; + pop @open; + next; + } + + # normal text processing + $length += length $_; + last if $maxlength && $length > $maxlength; + $result .= $e->($_); + } + + # close open tags + while((local $_ = pop @open) ne 'first') { + $result .= $_ eq 'url' ? '</a>' : $_ eq 'spoiler' ? '</b>' : ''; + $result .= $_ eq 'quote' ? '</div>' : $_ eq 'code' ? '</pre>' : '' if !$maxlength; + } + $result .= '...' if $maxlength && $length > $maxlength; + + return $result; +} + + +# GTIN code as argument, +# Returns 'JAN', 'EAN', 'UPC' or undef, +# Also 'normalizes' the first argument in place +sub gtintype { + $_[0] =~ s/[^\d]+//g; + $_[0] = ('0'x(12-length $_[0])) . $_[0] if length($_[0]) < 12; # pad with zeros to GTIN-12 + my $c = shift; + return undef if $c !~ /^[0-9]{12,13}$/; + $c = "0$c" if length($c) == 12; # pad with another zero for GTIN-13 + + # calculate check digit according to + # http://www.gs1.org/productssolutions/barcodes/support/check_digit_calculator.html#how + my @n = reverse split //, $c; + my $n = shift @n; + $n += $n[$_] * ($_ % 2 != 0 ? 1 : 3) for (0..$#n); + return undef if $n % 10 != 0; + + # Do some rough guesses based on: + # http://www.gs1.org/productssolutions/barcodes/support/prefix_list.html + # and http://en.wikipedia.org/wiki/List_of_GS1_country_codes + local $_ = $c; + return 'JAN' if /^4[59]/; # prefix code 450-459 & 490-499 + return 'UPC' if /^(?:0[01]|0[6-9]|13|75[45])/; # prefix code 000-019 & 060-139 & 754-755 + return undef if /^(?:0[2-5]|2|97[789]|9[6-9])/; # some codes we don't want: 020–059 & 200-299 & 977-999 + return 'EAN'; # let's just call everything else EAN :) +} + + +# a rather aggressive normalization +sub normalize { + local $_ = lc shift; + # remove combining markings. assuming the string is in NFD or NFKD, + # this effectively removes all accents from the characters (e.g. é -> e) + s/\pM//g; + # remove some characters that have no significance when searching + use utf8; + tr/\r\n\t ,_\-.~:[]()%+!?&#$"'`♥★☆♪†「」『』【】・”//d; + tr/@/a/; + # remove commonly used release titles ("x Edition" and "x Version") + # this saves some space and speeds up the search + s/(?: + first|firstpress|firstpresslimited|limited|regular|standard + |package|boxed|download|complete|popular + |lowprice|best|cheap|budget + |special|trial|allages|fullvoice + |cd|cdr|cdrom|dvdrom|dvd|dvdpack|dvdpg|windows + |初回限定|初回|限定|通常|廉価|パッケージ|ダウンロード + )(?:edition|version|版|生産)//xg; + # other common things + s/fandisk/fandisc/g; + no utf8; + return $_; +} + + +# normalizes each title and returns a concatenated string of unique titles +sub normalize_titles { + my %t = map +(normalize(NFKD($_)), 1), @_; + return join ' ', grep $_, keys %t; +} + + +sub normalize_query { + my $q = NFKD shift; + # remove spaces within quotes, so that it's considered as one search word + $q =~ s/"([^"]+)"/(my $s=$1)=~y{ }{}d;$s/ge; + # split into search words, normalize, and remove too short words + return map length($_)>(/^[\x01-\x7F]+$/?2:0) ? quotemeta($_) : (), map normalize($_), split / /, $q; +} + + +# arguments: <image size>, <max dimensions> +# returns the size of the thumbnail with the same aspect ratio as the full-size +# image, but fits within the specified maximum dimensions +sub imgsize { + my($ow, $oh, $sw, $sh) = @_; + return ($ow, $oh) if $ow <= $sw && $oh <= $sh; + if($ow/$oh > $sw/$sh) { # width is the limiting factor + $oh *= $sw/$ow; + $ow = $sw; + } else { + $ow *= $sh/$oh; + $oh = $sh; + } + return (int $ow, int $oh); +} + + +1; + diff --git a/static/f/icons.png b/static/f/icons.png Binary files differindex 4437300b..3b761c85 100644 --- a/static/f/icons.png +++ b/static/f/icons.png diff --git a/util/jsgen.pl b/util/jsgen.pl index 1ad8fb53..27cbc6da 100755 --- a/util/jsgen.pl +++ b/util/jsgen.pl @@ -22,74 +22,73 @@ use LangFile; use VNDB::L10N; -my $jskeys_lang = join '|', VNDB::L10N::languages(); -my $jskeys = qr{^(?: - _lang_(?:$jskeys_lang)| - _js_.+| - _menu_emptysearch| - _vnpage_uopt_(?:10?vote|rel.+)| - _rlst_[vr]stat_.+| - _vnedit_rel_(?:isa|of|addbut|del|none|findformat|novn|double)| - _redit_form_med_.+| - _vnedit_scr_.+| - _tagv_(?:add|spoil\d|notfound|nometa|double)| - _redit_form_vn_(?:addbut|remove|none|vnformat|notfound|double)| - _redit_form_prod_(?:addbut|remove|none|pformat|notfound|double)| - _pedit_rel_(?:addbut|del|none|findformat|notfound|double) - )$}x; - sub l10n { - # Using JSON::XS or something may be shorter and less error prone, - # although I would have less power over the output (mostly the quoting of the keys) + # parse the .js code to find the l10n keys to use + my $js = shift; + my @keys; + push @keys, $1 ? quotemeta($1) : qr/$2/ while($js =~ m{(?:mt\('([a-z0-9_]+)'[,\)]|l10n /([^/]+)/)}g); + # also add the _lang_* for all languages for which we have a translation + my $jskeys_lang = join '|', VNDB::L10N::languages(); + push @keys, qr/_lang_(?:$jskeys_lang)/; + # fetch the corresponding text from lang.txt + my %lang; # key1 => { lang1 => .., lang2 => .. }, key2 => { .. } my $lang = LangFile->new(read => "$ROOT/data/lang.txt"); - my @r; - push @r, 'L10N_STR = {'; - my $cur; # undef = none/excluded, 1 = awaiting first TL line, 2 = after first TL line - my %lang; + my $cur; # 0 = none/excluded, 1 = TL lines + my $key; while((my $l = $lang->read())) { my $type = shift @$l; if($type eq 'key') { - my $key = shift @$l; - push @r, ' }' if $cur; - $cur = $key =~ $jskeys ? 1 : undef; - if($cur) { - $r[$#r] .= ',' if $r[$#r] =~ /}$/; - # let's assume key names don't trigger a reserved word in JS - $key = qq{"$key"} if $key !~ /^[a-z_][a-z0-9_]*$/i; - push @r, qq| $key: {|; - } + my $k = shift @$l; + $cur = grep $k =~ /$_/, @keys; + $key = $k; } - $lang{$l->[0]} = 1 if $type eq 'tl'; if($type eq 'tl' && $cur) { my($lang, $sync, $val) = @$l; next if !$val; + $lang{$key}{$lang} = $val; + } + } + + # generate JS code + my $r = "L10N_STR = {\n"; + my $first = 1; + for my $key (sort keys %lang) { + $r .= ",\n" if !$first; + $first = 0; + # let's assume all L10N keys are valid JS variable names + $r .= sprintf qq| "%s": {\n|, $key; + my $firstk = 1; + for (sort keys %{$lang{$key}}) { + $r .= ",\n" if !$firstk; + $firstk = 0; + my $lang = $_; + $lang = qq{"$lang"} if $lang =~ /^(?:as|do|if|in|is)$/; # reserved two-char words + my $val = $lang{$key}{$_}; $val =~ s/"/\\"/g; $val =~ s/\n/\\n/g; - $r[$#r] .= ',' if $cur == 2; - $lang = qq{"$l->[0]"} if $lang =~ /^(?:as|do|if|in|is)$/; # reserved two-char words - push @r, qq| $lang: "$val"|; - $cur = 2; + $r .= sprintf qq| %s: "%s"|, $lang, $val; } + $r .= "\n }"; } - push @r, ' }' if $cur; - push @r, '};'; - push @r, 'L10N_LANG = [ '.join(', ', map qq{"$_"}, VNDB::L10N::languages()).' ];'; - return join "\n", @r; + $r .= "\n};\n"; + $r .= 'L10N_LANG = [ '.join(', ', map qq{"$_"}, VNDB::L10N::languages()).' ];'; + return "$r\n"; } sub jsgen { # JavaScript::Minifier::XS doesn't correctly handle perl's unicode, # so just do everything in raw bytes instead. - my $js = encode_utf8(l10n()) . "\n"; - $js .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}}; - $js .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}}; open my $JS, '<', "$ROOT/data/script.js" or die $!; - $js .= join '', <$JS>; + my $js .= join '', <$JS>; close $JS; + my $head = encode_utf8(l10n($js)) . "\n"; + $head .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}}; + $head .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}}; + $head .= sprintf "cookie_prefix = '%s';\n", $S{cookie_prefix}; open my $NEWJS, '>', "$ROOT/static/f/script.js" or die $!; - print $NEWJS $JavaScript::Minifier::XS::VERSION ? JavaScript::Minifier::XS::minify($js) : $js; + print $NEWJS $JavaScript::Minifier::XS::VERSION ? JavaScript::Minifier::XS::minify($head.$js) : $head.$js; close $NEWJS; } diff --git a/util/skingen.pl b/util/skingen.pl index 37504921..343c5922 100755 --- a/util/skingen.pl +++ b/util/skingen.pl @@ -1,18 +1,22 @@ #!/usr/bin/perl +package VNDB; + use strict; use warnings; use Cwd 'abs_path'; use Image::Magick; eval { require CSS::Minifier::XS }; -our $ROOT; +our($ROOT, %S); BEGIN { ($ROOT = abs_path $0) =~ s{/util/skingen\.pl$}{}; } use lib "$ROOT/lib"; use SkinFile; +require $ROOT.'/data/global.pl'; + sub writeskin { # $name my $name = shift; @@ -24,15 +28,15 @@ sub writeskin { # $name my $path = "/s/$name/$o{imgrighttop}"; my $img = Image::Magick->new; $img->Read("$ROOT/static$path"); - $o{_bgright} = sprintf 'background: url(%s) no-repeat; width: %dpx; height: %dpx', - $path, $img->Get('width'), $img->Get('height'); + $o{_bgright} = sprintf 'background: url(%s?%s) no-repeat; width: %dpx; height: %dpx', + $path, $S{version}, $img->Get('width'), $img->Get('height'); } else { $o{_bgright} = 'display: none'; } # body background if($o{imglefttop}) { - $o{_bodybg} = sprintf 'background: %s url(/s/%s/%s) no-repeat', $o{bodybg}, $name, $o{imglefttop}; + $o{_bodybg} = sprintf 'background: %s url(/s/%s/%s?%s) no-repeat', $o{bodybg}, $name, $o{imglefttop}, $S{version}; } else { $o{_bodybg} = sprintf 'background-color: %s', $o{bodybg}; } @@ -44,7 +48,7 @@ sub writeskin { # $name my $img = Image::Magick->new(size => '1x1'); $img->Read("xc:$o{boxbg}"); $img->Write(filename => "$ROOT/static/s/$name/boxbg.png"); - $o{_boxbg} = "/s/$name/boxbg.png"; + $o{_boxbg} = "/s/$name/boxbg.png?$S{version}"; # get the blend color $img = Image::Magick->new(size => '1x1'); @@ -52,6 +56,9 @@ sub writeskin { # $name $img = $img->Flatten(); $o{_blendbg} = '#'.join '', map sprintf('%02x', $_*255), $img->GetPixel(x=>1,y=>1); + # version + $o{version} = $S{version}; + # write the CSS open my $CSS, '<', "$ROOT/data/style.css" or die $!; my $css = join '', <$CSS>; diff --git a/util/sql/all.sql b/util/sql/all.sql index 9a9eace0..fd1f0a73 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -74,6 +74,9 @@ CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_announce(); +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify(); +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify(); + -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; diff --git a/util/sql/func.sql b/util/sql/func.sql index f9f0e490..7b8b9302 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -586,6 +586,52 @@ $$ LANGUAGE plpgsql; +-- Check for updates to vn.c_search +-- 1. NOTIFY is sent when vn.c_search goes from non-NULL to NULL +-- vn.c_search is set to NULL when: +-- 2. UPDATE on VN with the hidden field going from TRUE to FALSE +-- 3. VN add/edit of which the title/original/alias fields differ from previous revision +-- 4. Release gets hidden or unhidden +-- 5. Release add/edit of which the title/original/vn fields differ from the previous revision +CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$ +BEGIN + IF TG_TABLE_NAME = 'vn' THEN + -- 1. + IF NEW.c_search IS NULL AND NOT NEW.hidden THEN + NOTIFY vnsearch; + -- 2. + ELSIF NEW.hidden IS DISTINCT FROM OLD.hidden THEN + UPDATE vn SET c_search = NULL WHERE id = NEW.id; + -- 3. + ELSIF NEW.latest IS DISTINCT FROM OLD.latest THEN + IF EXISTS(SELECT 1 FROM vn_rev v1, vn_rev v2 + WHERE v1.id = OLD.latest AND v2.id = NEW.latest + AND (v1.title IS DISTINCT FROM v2.title OR v1.original IS DISTINCT FROM v2.original OR v1.alias IS DISTINCT FROM v2.alias) + ) THEN + UPDATE vn SET c_search = NULL WHERE id = NEW.id; + END IF; + END IF; + ELSIF TG_TABLE_NAME = 'releases' THEN + -- 4. & 5. + IF NEW.hidden IS DISTINCT FROM OLD.hidden OR ( + NEW.latest IS DISTINCT FROM OLD.latest AND ( + EXISTS( + SELECT 1 FROM releases_rev r1, releases_rev r2 + WHERE r1.id = OLD.latest AND r2.id = NEW.latest + AND (r1.title IS DISTINCT FROM r2.title OR r1.original IS DISTINCT FROM r2.original) + ) + OR EXISTS(SELECT vid FROM releases_vn WHERE rid = OLD.latest EXCEPT SELECT vid FROM releases_vn WHERE rid = NEW.latest) + OR (SELECT COUNT(*) FROM releases_vn WHERE rid = OLD.latest) <> (SELECT COUNT(*) FROM releases_vn WHERE rid = NEW.latest) + )) THEN + UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest); + END IF; + END IF; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + + ---------------------------------------------------------- diff --git a/util/updates/update_2.12.sql b/util/updates/update_2.12.sql new file mode 100644 index 00000000..eea0ac5c --- /dev/null +++ b/util/updates/update_2.12.sql @@ -0,0 +1,20 @@ + +-- use newlines to separate aliases +-- (note: this will go wrong with titles that contain a comma. Those have to be fixed manually) +UPDATE vn_rev SET alias = trim(both ' ' from regexp_replace(alias, ' *, *', E'\n', 'g')); + + +-- cache for search +ALTER TABLE vn ADD COLUMN c_search text; + +\i util/sql/func.sql + +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify(); +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE vn_vnsearch_notify(); + + +-- two new resolutions have been added, array indexes have changed +UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 7; +UPDATE releases_rev SET resolution = resolution + 1 WHERE resolution >= 11; + + diff --git a/yawf b/yawf -Subproject a4988cf068fa52dae3f0ff88d73564136e54f2d +Subproject d7f850931451bf11538fea70b0dac212be346fc |
