diff options
author | Yorhel <git@yorhel.nl> | 2010-11-28 10:45:43 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-11-28 10:45:43 +0100 |
commit | e27071f4110c6a93ef140b2d3dde331194917616 (patch) | |
tree | 269a8479ebe7a1b56480fae45b97ff5f30b728ad | |
parent | afa8f6cb619fd59d164d15d12249560706878639 (diff) | |
parent | 5bfd8f4559f2ce54332d28ac767619c020b151dc (diff) |
Merge branch 'beta'
Conflicts:
lib/VNDB/Handler/Discussions.pm
-rw-r--r-- | .gitignore | 3 | ||||
-rw-r--r-- | ChangeLog | 18 | ||||
-rw-r--r-- | Makefile | 36 | ||||
-rw-r--r-- | README | 8 | ||||
-rw-r--r-- | data/global.pl | 7 | ||||
-rw-r--r-- | data/lang.txt | 298 | ||||
-rw-r--r-- | data/script.js | 518 | ||||
-rw-r--r-- | data/style.css | 83 | ||||
-rw-r--r-- | lib/Multi/Feed.pm | 170 | ||||
-rw-r--r-- | lib/VNDB/DB/Releases.pm | 63 | ||||
-rw-r--r-- | lib/VNDB/DB/Users.pm | 16 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 16 | ||||
-rw-r--r-- | lib/VNDB/Func.pm | 34 | ||||
-rw-r--r-- | lib/VNDB/Handler/Discussions.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Misc.pm | 29 | ||||
-rw-r--r-- | lib/VNDB/Handler/Producers.pm | 8 | ||||
-rw-r--r-- | lib/VNDB/Handler/Releases.pm | 195 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 114 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Plugin/TransAdmin.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 9 | ||||
-rw-r--r-- | lib/VNDB/Util/LayoutHTML.pm | 6 | ||||
-rw-r--r-- | lib/VNDBUtil.pm | 127 | ||||
-rw-r--r-- | static/f/icons.png | bin | 10352 -> 10825 bytes | |||
-rwxr-xr-x | util/jsgen.pl | 135 | ||||
-rw-r--r-- | util/sql/all.sql | 81 | ||||
-rw-r--r-- | util/sql/func.sql | 237 | ||||
-rw-r--r-- | util/sql/schema.sql | 27 | ||||
-rw-r--r-- | util/updates/update_2.14.sql | 145 | ||||
-rwxr-xr-x | util/vndb.pl | 55 |
30 files changed, 1556 insertions, 892 deletions
@@ -2,7 +2,8 @@ /data/multi.pid /data/docs/8 /data/log/ -/static/f/script.js +/static/f/js/ +/static/feeds/ /static/s/*/style.css /static/s/*/boxbg.png /static/cv/ @@ -1,3 +1,21 @@ +2.14 - 2010-11-28 + - Improved filter selection interface for the release and VN browser + - New release filters: voiced, animation and original language + - New VN filters: length, "has anime" and original language + - Apply search query and filters when changing first char + - Added Atom feeds for the recent announcements, changes and posts + (located in /www/feeds and updated every 15 min. by Multi::Feed) + - Re-added producer role to collapsed view on producer pages + - JS: Reverted to the old selection box date selector + - JS: Split script.js into a separate file for each language + - Improved performance of update_vnpopularity() on PostgreSQL 9.0 + - Faster and improved bb2html() + - Added WHEN clause to all SQL TRIGGERs for which it was useful + (this *requires* PostgreSQL 9.0 or up!) + - Added ON DELETE clause to all foreign keys referencing users (id) + - Use word-level (instead of character-level) diff for large fields + - Extended IE6 warning message to show up for IE7 as well + 2.13 - 2010-11-11 - Added 'formcode' parameter to all modification requests to fix all cross-site request forgery vulnerabilities @@ -1,5 +1,5 @@ # all (default) -# Same as $ make dirs js skins www robots +# Same as `make dirs js skins robots` # # dirs # Creates the required directories not present in git @@ -37,42 +37,38 @@ # 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 update-2.12 update-2.13 +.PHONY: all dirs js skins robots chmod chmod-tladmin multi-stop multi-start multi-restart\ + sql-import update-2.10 update-2.11 update-2.12 update-2.13 update-2.14 all: dirs js skins robots data/config.pl +dirs: static/f/js static/cv static/sf static/st data/log www www/feeds -dirs: static/cv static/sf static/st data/log www +js: static/f/js/en.js + +skins: static/s/*/style.css + +robots: dirs www/robots.txt static/robots.txt static/cv static/sf static/st: mkdir $@; for i in $$(seq -w 0 1 99); do mkdir "$@/$$i"; done -data/log www: +static/f/js data/log www www/feeds: mkdir $@ - -js: static/f/script.js - -static/f/script.js: data/script.js data/lang.txt util/jsgen.pl data/config.pl +static/f/js/en.js: data/script.js data/lang.txt util/jsgen.pl data/config.pl data/global.pl util/jsgen.pl - -skins: static/s/*/style.css - static/s/%/style.css: static/s/%/conf util/skingen.pl data/style.css util/skingen.pl $* - -robots: dirs www/robots.txt static/robots.txt - %/robots.txt: echo 'User-agent: *' > $@ echo 'Disallow: /' >> $@ - chmod: all - chmod a-x+rw static/f/script.js + chmod a+xrw static/f/js chmod -R a-x+rwX static/{cv,sf,st} chmod a-x+rw static/s/*/{style.css,boxbg.png} @@ -127,7 +123,7 @@ define runpsql endef -sql-import: +sql-import): ${runpsql} < util/sql/all.sql @@ -152,3 +148,9 @@ update-2.13: all ${runpsql} < util/updates/update_2.13.sql $(multi-start) +update-2.14: all + rm -f static/f/script.js + $(multi-stop) + ${runpsql} < util/updates/update_2.14.sql + $(multi-start) + @@ -12,11 +12,11 @@ Requirements global requirements: Linux, or an OS that resembles Linux. Chances are VNDB won't run on Windows. - PostgreSQL 8.4+ - perl 5.10 recommended, 5.8 may also work + PostgreSQL 9.0+ (don't try older versions or other SQL databases, it won't work) + perl 5.12 recommended, 5.10 may also work A webserver that works with YAWF (lighttpd and Apache are known to work) - (perl 5.10 core modules are not listed.) + (perl 5.12 core modules are not listed.) util/vndb.pl: Algorithm::Diff::Fast @@ -35,6 +35,8 @@ Requirements DBD::Pg POE POE::Component::Pg (get it from http://g.blicky.net/poco-pg.git/) + Feed: + XML::Writer IRC: POE::Component::IRC URI::Escape diff --git a/data/global.pl b/data/global.pl index 0e7b6d8c..c64eeac7 100644 --- a/data/global.pl +++ b/data/global.pl @@ -22,6 +22,7 @@ our %S = (%S, cookie_prefix => 'vndb_', global_salt => 'any-private-string-here', form_salt => 'a-different-private-string-here', + regen_static => 0, source_url => 'http://git.blicky.net/vndb.git/?h=master', admin_email => 'contact@vndb.org', scr_size => [ 136, 102 ], # w*h of screenshot thumbnails @@ -101,6 +102,11 @@ our %S = (%S, wishlist_status => [ 0..3 ], rlst_rstat => [ 0..4 ], # 2 = hardcoded 'OK', < 2 = hardcoded 'NOK' rlst_vstat => [ 0..4 ], # 2 = hardcoded 'OK', 0 || 4 = hardcoded 'NOK' + atom_feeds => { # num_entries, title, id + announcements => [ 10, 'VNDB Site Announcements', '/t/an' ], + changes => [ 25, 'VNDB Recent Changes', '/hist' ], + posts => [ 25, 'VNDB Recent Posts', '/t' ], + }, ); @@ -109,6 +115,7 @@ our %M = ( log_dir => $ROOT.'/data/log', modules => { #API => {}, # disabled by default, not really needed + Feed => {}, RG => {}, Image => {}, #Anime => {}, # disabled by default, requires AniDB username/pass diff --git a/data/lang.txt b/data/lang.txt index 8ddd96d6..588ecc63 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -1511,6 +1511,14 @@ hu : moderálásra vár nl : ongemodereerd +# title of the syndication icon (this thing -> http://en.wikipedia.org/wiki/Atom_feed) + +:_atom_feed +en : Atom Feed +ru*: +cs*: +hu*: +nl : ############################################################################# @@ -3687,33 +3695,26 @@ cs : Filtry hu : Szűrők nl : -:_rbrowse_minage -en : Age rating -ru : Возрастной рейтинг -cs : Věková přístupnost -hu : Korhatár -nl : Minimum leeftijd - -:_rbrowse_ge -en : Greater than or equal to -ru : Больше или равно -cs : Větší než nebo rovno -hu : Nagyobb vagy egyenlő -nl : Hoger dan of gelijk aan +:_rbrowse_fil_title +en : Release filters +ru*: +cs*: +hu*: +nl : Uitgavenfilters -:_rbrowse_le -en : Less than or equal to -ru : Меньше или равно -cs : Menší než nebo rovno -hu : Kisebb vagy egyenlő -nl : Lager dan of gelijk aan +:_rbrowse_general +en : General +ru*: +cs*: +hu*: +nl : Algemeen -:_rbrowse_resolution -en : Screen resolution -ru : Разрешение экрана -cs : Rozlišení obrazovky -hu : Felbontás -nl : Schermresolutie +:_rbrowse_close +en : close +ru*: +cs*: +hu*: +nl : sluiten :_rbrowse_type en : Release type @@ -3722,13 +3723,6 @@ cs : Typ vydání hu : Kiadás típus nl : Type -:_rbrowse_all -en : All -ru : Все -cs : Vše -hu : Minden -nl : Alle - :_rbrowse_patch en : Patch status ru : Статус патча @@ -3736,19 +3730,19 @@ cs : Status patche hu : Patch állapot nl : -:_rbrowse_patchonly -en : Only patches -ru : Только патчи -cs : Pouze patche -hu : Csak patchek -nl : Alleen patches +:_rbrowse_patch_yes +en : Patch +ru*: Только патчи +cs*: Pouze patche +hu*: Csak patchek +nl : -:_rbrowse_patchnone -en : Only standalone releases -ru : Только самостоятельные выпуски -cs : Pouze samostatná vydání -hu : Csak különálló kiadások -nl : Geen patches +:_rbrowse_patch_no +en : Standalone +ru*: Только самостоятельные выпуски +cs*: Pouze samostatná vydání +hu*: Csak különálló kiadások +nl : Geen patch :_rbrowse_freeware en : Freeware @@ -3757,14 +3751,14 @@ cs : Freeware hu : nl : -:_rbrowse_freewareonly -en : Freeware only +:_rbrowse_freeware_yes +en : Only freeware ru : Только Freeware cs : Pouze freeware hu : Csak Freeware nl : Alleen freeware -:_rbrowse_freewarenone +:_rbrowse_freeware_no en : Only non-free releases ru : Только несвободные cs : Pouze placená vydání @@ -3778,14 +3772,14 @@ cs : Doujin hu : nl : -:_rbrowse_doujinonly +:_rbrowse_doujin_yes en : Only doujin releases ru : Только додзинси cs : Pouze doujin vydání hu : Csak doujin kiadások nl : Alleen doujin uitgaven -:_rbrowse_doujinnone +:_rbrowse_doujin_no en : Only commercial releases ru : Только коммерческие cs : Pouze komerční vydání @@ -3806,34 +3800,83 @@ cs : Vydáno před hu : Kiadva előtt nl : Uitgegeven voor -:_rbrowse_languages -en : Languages -ru : Языки -cs : Jazyky -hu : Nyelvek -nl : Talen +:_rbrowse_minage +en : Age rating +ru : Возрастной рейтинг +cs : Věková přístupnost +hu : Korhatár +nl : Minimum leeftijd + +:_rbrowse_language +en : Language +ru*: Языки +cs*: Jazyky +hu*: Nyelvek +nl : Taal + +:_rbrowse_olang +en : Original language +ru*: +cs*: +hu*: +nl : Originele taal + +:_rbrowse_resolution +en : Screen resolution +ru : Разрешение экрана +cs : Rozlišení obrazovky +hu : Felbontás +nl : Schermresolutie :_rbrowse_boolor -en : boolean or, selecting more gives more results +en : Boolean or, selecting more gives more results ru : логическое 'или', чем больше выбрано, тем больше даёт результатов cs : boolean nebo, výběr více dá více výsledků hu : Boole féle értékhalmaz(igaz/hamis), ha többet jelölsz be, több eredményt ad ki -nl : booleaanse 'of', meerdere selecties geven meer resultaten +nl : Booleaanse 'of', meerdere selecties geven meer resultaten -:_rbrowse_platforms -en : Platforms -ru : Платформы -cs : Platformy -hu : Platformok -nl : Platformen +:_rbrowse_platform +en : Platform +ru*: Платформы +cs*: Platformy +hu*: Platformok +nl : Platform -:_rbrowse_media -en : Media -ru : Носители -cs : Média -hu : Médium +:_rbrowse_medium +en : Medium +ru*: Носители +cs*: Média +hu*: Médium nl : +:_rbrowse_voiced +en : Voiced +ru*: +cs*: +hu*: +nl : Ingesproken + +:_rbrowse_animation +en : Animation +ru*: +cs*: +hu*: +nl : Animatie + +:_rbrowse_ani_story +en : Story +ru*: +cs*: +hu*: +nl : Verhaal + +:_rbrowse_ani_ero +en : Ero scenes +ru*: +cs*: +hu*: +nl : Erotische scenes + :_rbrowse_apply en : Apply ru : Применить @@ -3841,12 +3884,12 @@ cs : Použít hu : Alkalmaz nl : Zoek -:_rbrowse_clear -en : Clear -ru : Очистить -cs : Začít znovu -hu : Törlés -nl : Reset +:_rbrowse_reset +en : Reset +ru*: Очистить +cs*: Začít znovu +hu*: Törlés +nl : @@ -5620,19 +5663,54 @@ cs : nenalezeny takové tagy hu : nem található ilyen címke nl : tag niet gevonden -:_vnbrowse_advsearch -en : advanced search -ru : расширенный поиск -cs : pokročilé hledání -hu : bővített keresés -nl : geavanceerd zoeken +:_vnbrowse_fil_title +en : Visual Novel Filters +ru*: +cs*: +hu*: +nl : + +:_vnbrowse_general +en : General +ru*: +cs*: +hu*: +nl : Algemeen + +:_vnbrowse_length +en : Length +ru*: +cs*: +hu*: +nl : Lengte + +:_vnbrowse_anime +en : Anime +ru*: +cs*: +hu*: +nl : + +:_vnbrowse_anime_yes +en : Has anime +ru*: +cs*: +hu*: +nl : Heeft anime + +:_vnbrowse_anime_no +en : Does not have anime +ru*: +cs*: +hu*: +nl : Heeft geen anime :_vnbrowse_tags -en : Tag filters -ru : Фильтры тегов -cs : Filtry tagů -hu : címke szűrők -nl : Tagfilters +en : Tags +ru*: +cs*: +hu*: +nl : :_vnbrowse_booland en : boolean and, selecting more gives less results @@ -5676,40 +5754,26 @@ cs : Ukázat všechny spoilery hu : Nagyobb spoilerek megjelenítése nl : Toon alle spoilers -:_vnbrowse_lang -en : Languages -ru : Языки -cs : Jazyky -hu : Nyelvek -nl : Talen - -:_vnbrowse_boolor -en : boolean or, selecting more gives more results -ru : логическое 'или', чем больше выбрано, тем больше даёт результатов -cs : boolean nebo, výběr více dá více výsledků -hu : Boole féle értékhalmaz(igaz/hamis), ha többet választasz ki akkor több találatot ad ki -nl : booleaanse 'of', meerdere selecties geven meer resultaten +:_vnbrowse_language +en : Language +ru*: Языки +cs*: Jazyky +hu*: Nyelvek +nl : Taal -:_vnbrowse_plat -en : Platforms -ru : Платформы -cs : Platformy -hu : Platformok -nl : Platformen +:_vnbrowse_olang +en : Original language +ru*: +cs*: +hu*: +nl : Originele taal -:_vnbrowse_apply -en : Apply -ru : Применить -cs : Použít -hu : Alkalmazás -nl : Toepassen - -:_vnbrowse_clear -en : Clear -ru : Очистить -cs : Začít znovu -hu : Törlés -nl : Wissen +:_vnbrowse_platform +en : Platform +ru*: Платформы +cs*: Platformy +hu*: Platformok +nl : # VN add/edit form (/v+/edit) diff --git a/data/script.js b/data/script.js index cdbac941..27f20f6b 100644 --- a/data/script.js +++ b/data/script.js @@ -2,6 +2,7 @@ * date -> Date selector * dd -> dropdown * ds -> dropdown search + * fil -> Filter selector * iv -> image viewer * jt -> Javascript Tabs * med -> Release media selector @@ -30,6 +31,10 @@ * l10n /<perl regex>/ * any keys matching that regex will be included. * + * In the case of an mt('<key>') without any extra arguments, the entire + * function call may be replaced by the TL string. In such a case mt() + * behaves similar to a preprocessor macro in C. + * * The "_lang_*" keys for all languages for which we have a translation are * already included automatically. */ @@ -180,10 +185,9 @@ function shorten(v, l) { * - Only supports [_n], ~[, ~] * - When it finds [quant,_n,..], it will only return the first argument (and doesn't support ~ in an argument) * assumes that a TL structure called 'L10N_STR' is defined in the header of this file */ -var mt_curlang = byName(byId('lang_select'), 'acronym')[0].className.substr(11, 2); function mt() { var key = arguments[0]; - var val = L10N_STR[key] ? L10N_STR[key][mt_curlang] || L10N_STR[key].en : key; + var val = L10N_STR[key] ? L10N_STR[key] : key; for(var i=1; i<arguments.length; i++) { var expr = '[_'+i+']'; while(val.indexOf(expr) >= 0) @@ -534,84 +538,53 @@ tvsInit(); /* D A T E I N P U T */ - -var date_years = []; -var date_months = []; -var date_days = []; - -function dateLoad(obj) { - /* load the arrays */ - var i; - date_years = [ [ 0, mt('_js_date_year') ], [ 9999, 'TBA' ] ]; - for(i=(new Date()).getFullYear()+5; i>=1980; i--) - date_years[date_years.length] = [ i, i ]; - - date_months = [ [ 99, mt('_js_date_month') ] ]; - for(i=1; i<=12; i++) - date_months[date_months.length] = [ i, i ]; - - date_days = [ [ 99, mt('_js_date_day') ] ]; - for(var i=1; i<=31; i++) - date_days[date_days.length] = [ i, i ]; - - /* get current value */ - var val = Math.floor(obj.value) || 0; - val = [ Math.floor(val/10000), Math.floor(val/100)%100, val%100 ]; - - /* create elements */ - for(var i=0; i<date_years.length; i++) - if(val[0] == date_years[i][0]) { val[0] = i; break }; - var year = tag('b', {'class':'datepart', date_sel: val[0], date_lst:date_years, date_cnt:10}, - tag('i', expanded_icon), - tag('b', date_years[val[0]][1]) +function dateLoad(obj, serfunc) { + var year = tag('select', {style: 'width: 70px', onfocus:serfunc, onchange: dateSerialize}, + tag('option', {value:0}, mt('_js_date_year')), + tag('option', {value: 9999}, 'TBA') ); - ddInit(year, 'bottom', dateDD); + for(var i=(new Date()).getFullYear()+5; i>=1980; i--) + year.appendChild(tag('option', {value: i}, i)); - val[1] = val[1] == 99 ? 0 : val[1]; - var month = tag('b', {'class':'datepart', date_sel: val[1], date_lst:date_months, date_cnt:7}, - tag('i', expanded_icon), - tag('b', date_months[val[1]][1]) - ); - ddInit(month, 'bottom', dateDD); + var month = tag('select', {style: 'width: 70px', onfocus:serfunc, onchange: dateSerialize}, tag('option', {value:99}, mt('_js_date_month'))); + for(var i=1; i<=12; i++) + month.appendChild(tag('option', {value: i}, i)); - val[2] = val[2] == 99 ? 0 : val[2]; - var day = tag('b', {'class':'datepart', date_sel: val[2], date_lst:date_days, date_cnt:10}, - tag('i', expanded_icon), - tag('b', date_days[val[2]][1]) - ); - ddInit(day, 'bottom', dateDD); + var day = tag('select', {style: 'width: 70px', onfocus:serfunc, onchange: dateSerialize}, tag('option', {value:99}, mt('_js_date_day'))); + for(var i=1; i<=31; i++) + day.appendChild(tag('option', {value: i}, i)); - obj.parentNode.insertBefore(tag('div', {date_obj: obj}, year, month, day), obj); + var div = tag('div', {date_obj: obj, date_serfunc: serfunc, date_val: obj ? obj.value : 0}, year, month, day); + dateSet(div, obj ? obj.value : 0); + return obj ? obj.parentNode.insertBefore(div, obj) : div; } -function dateDD(lnk) { - var d = tag('div', {'class':'date'}); - var clk = function () { - lnk.date_sel = this.date_val; - setText(byName(lnk, 'b')[0], lnk.date_lst[lnk.date_sel][1]); - ddHide(); - dateSerialize(lnk); - return false; - }; - for(var i=0; i<lnk.date_lst.length; i++) { - var txt = lnk.date_lst[i][1]; - if(txt == mt('_js_date_year') || txt == mt('_js_date_month') || txt == mt('_js_date_day')) txt = '--'; - if((i % lnk.date_cnt) == 0) d.appendChild(tag('ul', null)); - d.childNodes[d.childNodes.length-1].appendChild(tag('li', lnk.date_sel == i ? tag('i', txt) : - tag('a', {href:'#', date_val: i, onclick: clk}, txt))); - } - return d; +function dateSet(div, val) { + val = typeof val == 'object' ? val[0] : val; + val = Math.floor(val) || 0; + val = [ Math.floor(val/10000), Math.floor(val/100)%100, val%100 ]; + if(val[1] == 0) val[1] = 99; + if(val[2] == 0) val[2] = 99; + var l = byName(div, 'select'); + for(var i=0; i<l.length; i++) + for(var j=0; j<l[i].options.length; j++) + l[i].options[j].selected = l[i].options[j].value == val[i]; + dateSerialize(div.childNodes[0], true); } -function dateSerialize(div) { - var div = div.parentNode; - var sel = byClass(div, 'b', 'datepart'); +function dateSerialize(div, nonotify) { + var div = div && div.parentNode ? div.parentNode : this.parentNode; + var sel = byName(div, 'select'); var val = [ - date_years[sel[0].date_sel][0], - date_months[sel[1].date_sel][0], - date_days[sel[2].date_sel][0], + sel[0].options[sel[0].selectedIndex].value*1, + sel[1].options[sel[1].selectedIndex].value*1, + sel[2].options[sel[2].selectedIndex].value*1 ]; - div.date_obj.value = val[0] == 0 ? 0 : val[0] == 9999 ? 99999999 : val[0]*10000+val[1]*100+(val[1]==99?99:val[2]); + div.date_val = val[0] == 0 ? 0 : val[0] == 9999 ? 99999999 : val[0]*10000+val[1]*100+(val[1]==99?99:val[2]); + if(div.date_obj) + div.date_obj.value = div.date_val; + if(!nonotify && div.date_serfunc) + div.date_serfunc(div); } { @@ -661,7 +634,7 @@ function dsKeyDown(ev) { if(obj.ds_selectedId != 0) obj.value = obj.ds_serFunc(byId('ds_box_'+obj.ds_selectedId).ds_itemData, obj); if(obj.ds_returnFunc) - obj.ds_returnFunc(); + obj.ds_returnFunc(obj); setClass(byId('ds_box'), 'hidden', true); setContent(byId('ds_box'), tag('b', mt('_js_loading'))); @@ -1745,6 +1718,365 @@ if(byId('prodrelations')) +/* F I L T E R S Y S T E M */ + + +/* Filter box definition: + * [ <title>, + * [ <category_name>, + * [ <fieldcode>, <fieldname>, <fieldcontents>, <fieldreadfunc>, <fieldwritefunc> ], .. + * ], .. + * ] + * Where: + * <title> human-readable title of the filter box + * <category_name> human-readable name of the category. ignored if there's only one category + * <fieldcode> code of this field, refers to the <field> in the filter format. Empty string for just a <tr> + * <fieldname> human-readanle name of the field. Empty to not display a label. Space for always-enabled items (without checkbox) + * <fieldcontents> tag() object, or an array of tag() objects + * <fieldreadfunc> function reference. argument: <fieldcontents>; must return data to be used in the filter format + * <fieldwritefunc> function reference, argument: <fieldcontents>, data from filter format; must update the contents with the passed data + * + * Filter string format: + * <field>-<value1>~<value2>.<field2>-<value>.<field3>-<value1>~<value2> + * Where: + * <field> = [a-z0-9]+ + * <value> = [a-zA-Z0-9_]+ and any UTF-8 characters not in the ASCII range + * Escaping of the <value>: + * "_<two-number-code>" + * Where <two-number-code> is the decimal index to the following array: + * _ <space> ! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ ` { | } ~ + * For boolean fields, the <value> is either 0 or 1. + */ + +var fil_cats; // [ <object with field->tr mapping>, <category-link1>, .. ] +var fil_escape = "_ !\"#$%&'()*+,-./:;<=>?@[\\]^`{|}~".split(''); +function filLoad() { + var l = byId('filselect').href.match(/#r$/) ? filReleases() : filVN(); + fil_cats = [ new Object ]; + + var p = tag('p', {'class':'browseopts'}); + var c = tag('div', null); + for(var i=1; i<l.length; i++) { + // category link + var a = tag('a', { href: '#', onclick: filSelectCat, fil_num: i }, l[i][0]); + p.appendChild(a); + p.appendChild(tag(' ')); + + // category contents + var t = tag('table', {'class':'formtable', fil_num: i}, null); + setClass(t, 'hidden', true); + a.fil_t = t; + for(var j=1; j<l[i].length; j++) { + var fd = l[i][j]; + var lab = typeof fd[1] == 'object' ? fd[1][0] : fd[1]; + var f = tag('tr', {'class':'newfield', fil_code: fd[0], fil_contents: fd[2], fil_readfunc: fd[3], fil_writefunc: fd[4]}, + fd[0] ? tag('td', {'class':'check'}, tag('input', {type:'checkbox', id:'fil_check_'+fd[0], 'class':fd[1]==' '?'hidden':'', name:'fil_check_'+fd[0], onclick: filSelectField })) : tag('td', null), + fd[1] ? tag('td', {'class':'label'}, + tag('label', {'for':'fil_check_'+fd[0]}, lab), + typeof fd[1] == 'object' ? tag('b', fd[1][1]) : null + ) : null, + tag('td', {'class':'cont' }, fd[2])); + if(fd[0]) + fil_cats[0][fd[0]] = f; + t.appendChild(f); + } + c.appendChild(t); + + fil_cats[i] = a; + } + + // TODO: _rbrowse_ -> generalize (this isn't specific to the release browser) + addBody(tag('div', { id: 'fil_div', 'class':'hidden' }, + tag('a', {href:'#', onclick:filShow, 'class':'close'}, mt('_rbrowse_close')), + tag('h3', l[0]), + p, + tag('b', {'class':'ruler'}, null), + c, + tag('b', {'class':'ruler'}, null), + tag('input', {type:'button', 'class':'submit', value: mt('_rbrowse_apply'), onclick:function () { + var f = byId('fil'); + while(f.nodeName.toLowerCase() != 'form') + f = f.parentNode; + f.submit(); + }}), + tag('input', {type:'button', 'class':'submit', value: mt('_rbrowse_reset'), onclick:function () { byId('fil').value = ''; filDeSerialize()} }) + )); + filSelectCat(1); + byId('filselect').onclick = filShow; + filDeSerialize(); +} + +function filSelectCat(n) { + n = this.fil_num ? this.fil_num : n; + for(var i=1; i<fil_cats.length; i++) { + setClass(fil_cats[i], 'optselected', i == n); + setClass(fil_cats[i].fil_t, 'hidden', i != n); + } + return false +} + +function filSelectField(obj) { + var t = obj && obj.parentNode ? obj : this; + // update checkbox and label + var o = t; + while(o.nodeName.toLowerCase() != 'tr') + o = o.parentNode; + var c = byId('fil_check_'+o.fil_code); + if(c != t) + c.checked = true; + if(hasClass(c, 'hidden')) + c.checked = true; + setClass(byName(o, 'label')[0], 'active', c.checked); + + // update category link + while(o.nodeName.toLowerCase() != 'table') + o = o.parentNode; + var l = byName(o, 'input'); + var n=0; + for(var i=0; i<l.length; i++) + if(l[i].type == 'checkbox' && l[i].id.substr(0, 10) == 'fil_check_' && !hasClass(l[i], 'hidden') && l[i].checked) + n++; + setClass(fil_cats[o.fil_num], 'active', n>0); + + // serialize + filSerialize(); + return true; +} + +function filSerialize() { + var l = []; + var num = 0; + for(var f in fil_cats[0]) { + if(!byId('fil_check_'+f).checked) + continue; + if(!hasClass(byId('fil_check_'+f), 'hidden')) + num++; + var v = fil_cats[0][f].fil_readfunc(fil_cats[0][f].fil_contents); + var r = []; + for(var h=0; h<v.length; h++) { + v[h] = (''+v[h]).split(''); + r[h] = ''; + // this isn't a very fast escaping method, blame JavaScript for inflexible search/replace support + for(var i=0; i<v[h].length; i++) { + for(var j=0; j<fil_escape.length; j++) + if(v[h][i] == fil_escape[j]) + break; + r[h] += j == fil_escape.length ? v[h][i] : '_'+(j<10?'0'+j:j); + } + } + if(r.length > 0 && r[0] != '') + l.push(fil_cats[0][f].fil_code+'-'+r.join('~')); + } + byId('fil').value = l.join('.'); + setText(byName(byId('filselect'), 'i')[1], num > 0 ? ' ('+num+')' : ''); +} + +function filDeSerialize() { + var d = byId('fil').value; + var fs = d.split('.'); + var f = new Object; + for(var i=0; i<fs.length; i++) { + var v = fs[i].split('-'); + if(fil_cats[0][v[0]]) + f[v[0]] = v[1]; + } + for(var fn in fil_cats[0]) + if(!f[fn]) + f[fn] = ''; + for(var fn in f) { + var c = byId('fil_check_'+fn); + c.checked = f[fn] == '' ? false : true; + var v = f[fn].split('~'); + for(var i=0; i<v.length; i++) + v[i] = v[i].replace(/_([0-9]{2})/g, function (a, e) { return fil_escape[Math.floor(e)] }); + fil_cats[0][fn].fil_writefunc(fil_cats[0][fn].fil_contents, v); + // not very efficient: filSelectField() does a lot of things that can be + // batched after all fields have been updated, and in some cases the + // writefunc() triggers the same filSelectField() as well + filSelectField(c); + } +} + +function filShow() { + var div = byId('fil_div'); + var hid = !hasClass(div, 'hidden'); + setClass(div, 'hidden', hid); + setText(byName(byId('filselect'), 'i')[0], hid ? collapsed_icon : expanded_icon); + + var o = this; + ddx = ddy = 0; + do { + ddx += o.offsetLeft; + ddy += o.offsetTop; + } while(o = o.offsetParent); + ddy += this.offsetHeight+2; + ddx += (this.offsetWidth-div.offsetWidth)/2; + div.style.left = ddx+'px'; + div.style.top = ddy+'px'; + + return false; +} + +function filFSelect(c, n, lines, opts) { + var s = tag('select', {onfocus: filSelectField, onchange: filSerialize, multiple: lines > 1, size: lines}); + for(var i=0; i<opts.length; i++) { + if(typeof opts[i][1] != 'object') + s.appendChild(tag('option', {name: opts[i][0]}, opts[i][1])); + else { + var g = tag('optgroup', {label: opts[i][0]}); + for(var j=1; j<opts[i].length; j++) + g.appendChild(tag('option', {name: opts[i][j][0]}, opts[i][j][1])); + s.appendChild(g); + } + } + return [ c, lines > 1 ? [ n, mt('_rbrowse_boolor') ] : n, s, + function (c) { + var l = []; + for(var i=0; i<c.options.length; i++) + if(c.options[i].selected) + l.push(c.options[i].name); + return l; + }, + function (c, f) { + for(var i=0; i<c.options.length; i++) { + for(var j=0; j<f.length; j++) + if(c.options[i].name+'' == f[j]+'') // beware of JS logic: 0 == '', but '0' != '' + break; + c.options[i].selected = j != f.length; + } + } + ]; +} + +function filFOptions(c, n, opts, setfunc) { + var p = tag('p', {'class':'opts', fil_val:opts[0][0]}); + var sel = function (e) { + var o = typeof e == 'string' ? e : this.fil_n; + if(setfunc) + o = setfunc(o); + var l = byName(p, 'a'); + for(var i=0; i<l.length; i++) + setClass(l[i], 'tsel', l[i].fil_n+'' == o+''); + p.fil_val = o; + if(typeof e != 'string') + filSelectField(p); + return false + }; + for(var i=0; i<opts.length; i++) { + p.appendChild(tag('a', {href:'#', fil_n: opts[i][0], onclick:sel}, opts[i][1])); + if(i<opts.length-1) + p.appendChild(tag('b', '|')); + } + return [ c, n, p, + function (c) { return [ c.fil_val ] }, + function (c, v) { sel(v[0]) } + ]; +} + +function filReleases() { + var types = release_types; + for(var i=0; i<types.length; i++) // l10n /_rtype_.+/ + types[i] = [ types[i], mt('_rtype_'+types[i]) ]; + var ages = age_ratings; + for(var i=0; i<ages.length; i++) + ages[i] = [ ages[i], ages[i] == -1 ? mt('_minage_null') : ages[i] == 0 ? mt('_minage_all') : mt('_minage_age', ages[i]) ]; + var lang = languages; + for(var i=0; i<lang.length; i++) // l10n /_lang_.+/ + lang[i] = [ lang[i], mt('_lang_'+lang[i]) ]; + var plat = platforms; + for(var i=0; i<plat.length; i++) // l10n /_plat_.+/ + plat[i] = [ plat[i], mt('_plat_'+plat[i]) ]; + var med = media; + for(var i=0; i<med.length; i++) // l10n /_med_.+/ + med[i] = [ med[i], mt('_med_'+med[i]) ]; + var voi = voiced; + for(var i=0; i<voi.length; i++) // l10n /_voiced_.+/ + voi[i] = [ voi[i], mt('_voiced_'+voi[i]) ]; + var ani = animated; + for(var i=0; i<ani.length; i++) // l10n /_animated_.+/ + ani[i] = [ ani[i], mt('_animated_'+ani[i]) ]; + return [ + mt('_rbrowse_fil_title'), + [ mt('_rbrowse_general'), + filFOptions('type', mt('_rbrowse_type'), types), + filFOptions('patch', mt('_rbrowse_patch'), [ [1, mt('_rbrowse_patch_yes')], [0, mt('_rbrowse_patch_no')] ]), + filFOptions('freeware', mt('_rbrowse_freeware'),[ [1, mt('_rbrowse_freeware_yes')], [0, mt('_rbrowse_freeware_no')] ]), + filFOptions('doujin', mt('_rbrowse_doujin'), [ [1, mt('_rbrowse_doujin_yes')], [0, mt('_rbrowse_doujin_no')] ]), + [ 'date_after', mt('_rbrowse_dateafter'), dateLoad(null, filSelectField), function (c) { return [c.date_val] }, dateSet ], + [ 'date_before', mt('_rbrowse_datebefore'), dateLoad(null, filSelectField), function (c) { return [c.date_val] }, dateSet ] + ], + [ mt('_rbrowse_minage'), filFSelect('minage', mt('_rbrowse_minage'), 15, ages) ], + [ mt('_rbrowse_language'), filFSelect('lang', mt('_rbrowse_language'), 20, lang) ], + [ mt('_rbrowse_olang'), filFSelect('olang', mt('_rbrowse_olang'), 20, lang) ], + [ mt('_rbrowse_resolution'), filFSelect('resolution', mt('_rbrowse_resolution'), 15, resolutions) ], + [ mt('_rbrowse_platform'), filFSelect('plat', mt('_rbrowse_platform'), 20, plat) ], + [ mt('_rbrowse_medium'), filFSelect('med', mt('_rbrowse_medium'), 10, med) ], + [ mt('_rbrowse_voiced'), filFSelect('voiced', mt('_rbrowse_voiced'), 5, voi) ], + [ mt('_rbrowse_animation'), + filFSelect('ani_story', mt('_rbrowse_ani_story'), 5, ani), + filFSelect('ani_ero', mt('_rbrowse_ani_ero'), 5, ani) + ] + ]; +} + +function filVN() { + var lang = languages; + for(var i=0; i<lang.length; i++) // l10n /_lang_.+/ + lang[i] = [ lang[i], mt('_lang_'+lang[i]) ]; + var plat = platforms; + for(var i=0; i<plat.length; i++) // l10n /_plat_.+/ + plat[i] = [ plat[i], mt('_plat_'+plat[i]) ]; + var len = vn_lengths; + for(var i=0; i<len.length; i++) // l10n /_vnlength_.+/ + len[i] = [ len[i], mt('_vnlength_'+len[i]) ]; + + // tag include/exclude dropdown search + var taginc = tag('input', {type:'text', 'class':'text', style:'width:350px', onfocus:filSelectField}); + var tagexc = tag('input', {type:'text', 'class':'text', style:'width:350px', onfocus:filSelectField}); + var trfunc = function(item, tr) { + tr.appendChild(tag('td', shorten(item.firstChild.nodeValue, 40), + item.getAttribute('meta') == 'yes' ? tag('b', {'class': 'grayedout'}, ' '+mt('_js_ds_tag_meta')) : null, + item.getAttribute('state') == 0 ? tag('b', {'class': 'grayedout'}, ' '+mt('_js_ds_tag_mod')) : null + )); + }; + var serfunc = function(item, obj) { + var tags = obj.value.split(/ *, */); + tags[tags.length-1] = item.firstChild.nodeValue; + filSelectField(obj); + return tags.join(', '); + }; + var retfunc = function(o) { filSelectField(o); false }; + var parfunc = function(val) { return (val.split(/, */))[val.split(/, */).length-1]; }; + var readfunc = function(c) { return c.value.split(/, */) }; + var writefunc = function(c,v) { c.value = v.join(', ') }; + dsInit(taginc, '/xml/tags.xml?q=', trfunc, serfunc, retfunc, parfunc); + dsInit(tagexc, '/xml/tags.xml?q=', trfunc, serfunc, retfunc, parfunc); + + return [ + mt('_vnbrowse_fil_title'), + [ mt('_vnbrowse_general'), + filFSelect( 'length', mt('_vnbrowse_length'), 6, len), + filFOptions('hasani', mt('_vnbrowse_anime'), [[1, mt('_vnbrowse_anime_yes')],[0, mt('_vnbrowse_anime_no')]]) + ], + [ mt('_vnbrowse_tags'), + [ '', ' ', tag('('+mt('_vnbrowse_booland')+')') ], + [ 'taginc', mt('_vnbrowse_taginc'), taginc, readfunc, writefunc ], + [ 'tagexc', mt('_vnbrowse_tagexc'), tagexc, readfunc, writefunc ], + filFOptions('tagspoil', ' ', [[0, mt('_vnbrowse_spoil0')],[1, mt('_vnbrowse_spoil1')],[2, mt('_vnbrowse_spoil2')]], + function (o) { var s = getCookie('tagspoil'); if(o+'' == '') return s == null ? 0 : s; setCookie('tagspoil', o); return o}) + ], + [ mt('_vnbrowse_language'), filFSelect('lang', mt('_vnbrowse_language'), 20, lang) ], + [ mt('_vnbrowse_olang'), filFSelect('olang',mt('_vnbrowse_olang'), 20, lang) ], + [ mt('_vnbrowse_platform'), filFSelect('plat', mt('_vnbrowse_platform'), 20, plat) ] + ]; +} + +if(byId('filselect')) + filLoad(); + + + + /* M I S C S T U F F */ // search box @@ -1777,7 +2109,7 @@ if(byId('votesel')) { }; } -// Advanced search (/v/*, /r) +// Advanced search (/v/*) if(byId('advselect')) { byId('advselect').onclick = function() { var box = byId('advoptions'); @@ -1788,15 +2120,6 @@ if(byId('advselect')) { }; } -// Spoiler filters -> cookie (/v/*) -if(byId('sp_0')) { - byId('sp_0').onclick = function() { setCookie('tagspoil', 0) }; - byId('sp_1').onclick = function() { setCookie('tagspoil', 1) }; - byId('sp_2').onclick = function() { setCookie('tagspoil', 2) }; - var spoil = getCookie('tagspoil'); - byId('sp_'+(spoil == null ? 0 : spoil)).checked = true; -} - // NSFW VN image toggle (/v+) if(byId('nsfw_show')) { var msg = byId('nsfw_show'); @@ -1956,14 +2279,7 @@ if(byId('expandprodrel')) { setexpand = function() { var exp = !(getCookie('prodrelexpand') == 1); setText(lnk, exp ? mt('_js_collapse') : mt('_js_expand')); - var l = byClass(byId('prodrel'), 'tr', 'rel'); - for(var i=0; i<l.length; i++) - setClass(l[i], 'hidden', !exp); - var l = byClass(byId('prodrel'), 'tr', exp ? 'vn_lst' : 'vn'); - for(var i=0; i<l.length; i++) { - setClass(l[i], 'vn', exp); - setClass(l[i], 'vn_lst', !exp); - } + setClass(byId('prodrel'), 'collapse', !exp); }; setexpand(); lnk.onclick = function () { @@ -1973,36 +2289,16 @@ if(byId('expandprodrel')) { }; } -// auto-complete tag search (/v/*) -if(byId('advselect') && byId('ti')) { - var trfunc = function(item, tr) { - tr.appendChild(tag('td', shorten(item.firstChild.nodeValue, 40), - item.getAttribute('meta') == 'yes' ? tag('b', {'class': 'grayedout'}, ' '+mt('_js_ds_tag_meta')) : null, - item.getAttribute('state') == 0 ? tag('b', {'class': 'grayedout'}, ' '+mt('_js_ds_tag_mod')) : null - )); - }; - var serfunc = function(item, obj) { - var tags = obj.value.split(/ *, */); - tags[tags.length-1] = item.firstChild.nodeValue; - return tags.join(', '); - }; - var retfunc = function() { false; }; - var parfunc = function(val) { - return (val.split(/, */))[val.split(/, */).length-1]; - }; - dsInit(byId('ti'), '/xml/tags.xml?q=', trfunc, serfunc, retfunc, parfunc); - dsInit(byId('te'), '/xml/tags.xml?q=', trfunc, serfunc, retfunc, parfunc); -} - // Language selector if(byId('lang_select')) { var d = byId('lang_select'); + var curlang = byName(d, 'acronym')[0].className.substr(11, 2); ddInit(d, 'bottom', function(lnk) { var lst = tag('ul', null); for(var i=0; i<L10N_LANG.length; i++) { var ln = L10N_LANG[i]; var icon = tag('acronym', {'class':'icons lang '+ln}, ' '); - lst.appendChild(tag('li', {'class':'lang_selector'}, mt_curlang == ln + lst.appendChild(tag('li', {'class':'lang_selector'}, curlang == ln ? tag('i', icon, mt('_lang_'+ln)) : tag('a', {href:'/setlang?lang='+ln}, icon, L10N_STR['_lang_'+ln][ln]||mt('_lang_'+ln)) )); diff --git a/data/style.css b/data/style.css index f7b0ab72..c1c70a08 100644 --- a/data/style.css +++ b/data/style.css @@ -414,6 +414,7 @@ p.screenshots img { #maincontent .mainbox.threelayout h1 a { color: $boxtitle$; } +a.feed { float: right } @@ -425,14 +426,15 @@ p.browseopts { text-align: center; padding: 2px; } -#maincontent p.browseopts a { +p.browseopts a { padding: 1px 3px; - color: $maintext$; + color: $maintext$!important; border: 1px solid $border$; margin: 0 2px; + white-space: nowrap; } -#maincontent p.browseopts a.optselected, -#maincontent p.browseopts a:hover { +p.browseopts a.optselected, +p.browseopts a:hover { border: 0; padding: 2px 4px; } @@ -700,7 +702,7 @@ a.addnew { #screenshots p.nsfwtoggle { float: right; margin: 0; } -#dd_box { position: absolute; left: 0px; border: 1px solid $border$; background-color: $secbg$; } +#dd_box { position: absolute; left: 0px; border: 1px solid $border$; background-color: $secbg$; z-index: 2 } #dd_box ul { list-style-type: none; margin: 0; padding: 0 } #dd_box li b { display: block; font-weight: normal; padding-left: 5px; } #dd_box li i { display: block; font-style: normal; padding-left: 10px; padding-right: 5px } @@ -710,11 +712,6 @@ a.addnew { #dd_box .vrdd ul { float: left; width: 90px; } #dd_box .vrdd ul.full { width: 180px; text-align: center; } #dd_box .vrdd ul.full li a { padding: 0 } -#dd_box .date ul { float: left } - -b.datepart { display: inline-block; width: 60px; margin-right: 5px; background-color: $secbg$; padding: 1px 5px; border: 1px solid $secborder$; font-weight: normal } -b.datepart i { float: right } -b.datepart b { font-weight: normal } @@ -754,6 +751,7 @@ b.datepart b { font-weight: normal } border-top: none; background-color: $secbg$; cursor: pointer; + z-index: 2 } #ds_box b { padding: 2px 0 0 10px; @@ -792,35 +790,13 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px .vnbrowse .tc3 { padding: 0; } .vnbrowse .tc5 { text-align: right; padding-right: 10px } .vnbrowse .tc6 { width: 80px } -#advselect { +#filselect { text-align: center; display: block; margin: 10px auto 3px auto; border: none; outline: none; } -#advoptions { - width: 90%; - padding: 0 30px 5px 30px; - margin: 0 auto; - border-top: 1px solid $border$; -} -#advoptions h2 { - clear: left; - padding-top: 10px; - margin-left: -20px; - margin-top: 0; - margin-bottom: 3px; -} -#advoptions span { - display: block; - float: left; - width: 170px; -} -#advoptions span input { - margin-right: 3px; -} - @@ -828,9 +804,12 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px #prodrel { width: 100%; } #prodrel tr.vn td { background: url($_boxbg$) repeat; font-weight: bold; } -#prodrel tr.vn i { display: none } -#prodrel tr.vn_lst td { padding: 1px } -#prodrel tr.vn_lst i { font-style: normal; display: block; float: left; width: 80px; padding: 0 0 0 40px; } +#prodrel tr.vn i, +#prodrel tr.vn span { display: none } +#prodrel.collapse tr.vn td { padding: 1px; background: none; font-weight: normal } +#prodrel.collapse tr.vn i { font-style: normal; display: block; float: left; width: 80px; padding: 0 0 0 40px; } +#prodrel.collapse tr.vn span { display: inline; font-weight: normal; color: $grayedout$; padding: 0 0 0 5px } +#prodrel.collapse tr.rel { display: none } #prodrel td.tc1 { width: 80px; padding-left: 30px; } #prodrel td.tc2 { width: 50px; text-align: center; } #prodrel td.tc3 { width: 120px; text-align: right; padding: 0; } @@ -1139,6 +1118,36 @@ div#iv_view { +/****** filter selector *****/ + +div#fil_div { + position: absolute; + top: 0px; + left: 0px; + background: $tabbg$; + border: 1px solid $border$; + padding: 5px; + width: 600px; + text-align: center; +} +#fil_div a.close { float: right; color: $link$; border: 0; font-weight: bold } +#fil_div p.browseopts { padding: 2px 50px; line-height: 23px } +#fil_div .browseopts a { outline: none } +#fil_div .browseopts a.active { font-weight: bold } +#fil_div b.ruler { display: block; margin: auto; width: 93%; height: 1px; border-bottom: 1px solid $border$; margin-bottom: 5px } +#fil_div h3 { width: 100%; text-align: center; font-size: 11px } +#fil_div table { width: 93%; text-align: left; margin: 0 auto 5px auto } +#fil_div table td.label label { width: 120px } +#fil_div table td.label b { display: block; font-weight: normal; padding: 10px 5px 0 0 } +#fil_div table td.check { width: 15px } +#fil_div label.active { font-weight: bold } +#fil_div .opts a { border: 0; outline: none; color: $link$ } +#fil_div .opts b { margin: 0 7px; font-weight: normal } +#fil_div .opts a.tsel { color: $maintext$; } +#filselect i { font-style: normal } + + + /****** Icons *******/ .icons { @@ -1161,6 +1170,7 @@ div#iv_view { height: 11px; opacity: 0.5; } +.icons.feed { width: 12px; height: 12px } .icons.rtcomplete, .icons.rtpartial, .icons.rttrial { width: 11px; } acronym.icons, acronym.uicons { cursor: default; } a .icons { cursor: pointer } @@ -1173,6 +1183,7 @@ a .icons { cursor: pointer } .icons.gba { background-position: 0px -70px; } .icons.ps3 { background-position: 0px -84px; } .icons.p98 { background-position: 0px -98px; } +.icons.feed { background-position: 0px -112px; } .icons.dvd { background-position: -16px 0px; } .icons.mac { background-position: -16px -14px; } diff --git a/lib/Multi/Feed.pm b/lib/Multi/Feed.pm new file mode 100644 index 00000000..c97d87d8 --- /dev/null +++ b/lib/Multi/Feed.pm @@ -0,0 +1,170 @@ + +# +# Multi::Feed - Generates and updates Atom feeds +# + +package Multi::Feed; + +use strict; +use warnings; +use POE; +use XML::Writer; +use POSIX 'strftime'; +use Time::HiRes 'time'; +use VNDBUtil 'bb2html'; + + +sub spawn { + my $p = shift; + POE::Session->create( + package_states => [ + $p => [qw| _start shutdown generate write_atom log_stats |], + ], + heap => { + regenerate_interval => 900, # 15 min. + stats_interval => 86400, # daily + debug => 0, + @_, + stats => {}, # key = feed, value = [ count, total, max ] + }, + ); +} + + +sub _start { + $_[KERNEL]->alias_set('feed'); + $_[KERNEL]->yield('generate'); + $_[KERNEL]->alarm(log_stats => int((time+3)/$_[HEAP]{stats_interval}+1)*$_[HEAP]{stats_interval}); + $_[KERNEL]->sig(shutdown => 'shutdown'); +} + + +sub shutdown { + $_[KERNEL]->delay('generate'); + $_[KERNEL]->delay('log_stats'); + $_[KERNEL]->alias_remove('feed'); +} + + +sub generate { + $_[KERNEL]->alarm(generate => int((time+3)/$_[HEAP]{regenerate_interval}+1)*$_[HEAP]{regenerate_interval}); + + # announcements + $_[KERNEL]->post(pg => query => q{ + SELECT '/t'||t.id AS id, t.title, extract('epoch' from tp.date) AS published, + extract('epoch' from tp.edited) AS updated, u.username, u.id AS uid, tp.msg AS summary + FROM threads t + JOIN threads_posts tp ON tp.tid = t.id AND tp.num = 1 + JOIN threads_boards tb ON tb.tid = t.id AND tb.type = 'an' + JOIN users u ON u.id = tp.uid + WHERE NOT t.hidden + ORDER BY t.id DESC + LIMIT ?}, [ $VNDB::S{atom_feeds}{announcements}[0] ], 'write_atom', 'announcements' + ); + + # changes + $_[KERNEL]->post(pg => query => q{ + SELECT '/'||c.type||COALESCE(vr.vid, rr.rid, pr.pid)||'.'||c.rev AS id, + COALESCE(vr.title, rr.title, pr.name) AS title, extract('epoch' from c.added) AS updated, + u.username, u.id AS uid, c.comments AS summary + FROM changes c + LEFT JOIN vn_rev vr ON c.type = 'v' AND c.id = vr.id + LEFT JOIN releases_rev rr ON c.type = 'r' AND c.id = rr.id + LEFT JOIN producers_rev pr ON c.type = 'p' AND c.id = pr.id + JOIN users u ON u.id = c.requester + WHERE c.requester <> 1 + ORDER BY c.id DESC + LIMIT ?}, [ $VNDB::S{atom_feeds}{changes}[0] ], 'write_atom', 'changes' + ); + + # posts (this query isn't all that fast) + $_[KERNEL]->post(pg => query => q{ + SELECT '/t'||t.id||'.'||tp.num AS id, t.title||' (#'||tp.num||')' AS title, extract('epoch' from tp.date) AS published, + extract('epoch' from tp.edited) AS updated, u.username, u.id AS uid, tp.msg AS summary + FROM threads_posts tp + JOIN threads t ON t.id = tp.tid + JOIN users u ON u.id = tp.uid + WHERE NOT tp.hidden AND NOT t.hidden + ORDER BY tp.date DESC + LIMIT ?}, [ $VNDB::S{atom_feeds}{posts}[0] ], 'write_atom', 'posts' + ); +} + + +sub write_atom { # num, res, feed, time + my $r = $_[ARG1]; + my $feed = $_[ARG2]; + + my $start = time; + + my $updated = 0; + for(@$r) { + $updated = $_->{published} if $_->{published} && $_->{published} > $updated; + $updated = $_->{updated} if $_->{updated} && $_->{updated} > $updated; + } + + my $data; + my $x = XML::Writer->new(OUTPUT => \$data, DATA_MODE => 1, DATA_INDENT => 2); + $x->xmlDecl('UTF-8'); + $x->startTag(feed => xmlns => 'http://www.w3.org/2005/Atom', 'xml:lang' => 'en', 'xml:base' => $VNDB::S{url}.'/'); + $x->dataElement(title => $VNDB::S{atom_feeds}{$feed}[1]); + $x->dataElement(updated => datetime($updated)); + $x->dataElement(id => $VNDB::S{url}.$VNDB::S{atom_feeds}{$feed}[2]); + $x->emptyTag(link => rel => 'self', type => 'application/atom+xml', href => "$VNDB::S{url}/feeds/$feed.atom"); + $x->emptyTag(link => rel => 'alternate', type => 'text/html', href => $VNDB::S{atom_feeds}{$feed}[2]); + + for(@$r) { + $x->startTag('entry'); + $x->dataElement(id => $VNDB::S{url}.$_->{id}); + $x->dataElement(title => $_->{title}); + $x->dataElement(updated => $_->{updated}?datetime($_->{updated}):datetime($_->{published})); + $x->dataElement(published => datetime($_->{published})) if $_->{published}; + if($_->{username}) { + $x->startTag('author'); + $x->dataElement(name => $_->{username}); + $x->dataElement(uri => '/u'.$_->{uid}) if $_->{uid}; + $x->endTag('author'); + } + $x->emptyTag(link => rel => 'alternate', type => 'text/html', href => $_->{id}); + $x->dataElement(summary => bb2html($_->{summary}, 200), type => 'html') if $_->{summary}; + $x->endTag('entry'); + } + + $x->endTag('feed'); + + open my $f, '>:utf8', "$VNDB::ROOT/www/feeds/$feed.atom" || die $!; + print $f $data; + close $f; + + $_[HEAP]{debug} && $_[KERNEL]->call(core => log => 'Wrote %s.atom (%d entries, sql:%4dms, perl:%4dms)', + $feed, scalar(@$r), $_[ARG3]*1000, (time-$start)*1000); + + $_[HEAP]{stats}{$feed} = [ 0, 0, 0 ] if !$_[HEAP]{stats}{$feed}; + my $time = ((time-$start)+$_[ARG3])*1000; + $_[HEAP]{stats}{$feed}[0]++; + $_[HEAP]{stats}{$feed}[1] += $time; + $_[HEAP]{stats}{$feed}[2] = $time if $_[HEAP]{stats}{$feed}[2] < $time; +} + + +sub log_stats { + $_[KERNEL]->alarm(log_stats => int((time+3)/$_[HEAP]{stats_interval}+1)*$_[HEAP]{stats_interval}); + + for (keys %{$_[HEAP]{stats}}) { + my $v = $_[HEAP]{stats}{$_}; + next if !$v->[0]; + $_[KERNEL]->call(core => log => 'Stats summary for %s.atom: total:%5dms, avg:%4dms, max:%4dms, size: %.1fkB', + $_, $v->[1], $v->[1]/$v->[0], $v->[2], (-s "$VNDB::ROOT/www/feeds/$_.atom")/1024); + } + $_[HEAP]{stats} = {}; +} + + +# non-POE helper function +sub datetime { + strftime('%Y-%m-%dT%H:%M:%SZ', gmtime shift); +} + + +1; + diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index 01ca6b44..b0fb9a89 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -10,8 +10,8 @@ use VNDB::Func 'gtintype'; our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|; -# Options: id vid pid rev unreleased page results what date media sort reverse -# platforms languages type minage search resolutions freeware doujin +# Options: id vid pid rev unreleased page results what med sort reverse date_before date_after +# plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero # What: extended changes vn producers platforms media # Sort: title released minage sub dbReleaseGet { @@ -21,33 +21,42 @@ sub dbReleaseGet { $o{what} ||= ''; my @where = ( - !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (), - $o{id} ? ( 'r.id = ?' => $o{id} ) : (), - $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (), - $o{vid} ? ( 'rv.vid = ?' => $o{vid} ) : (), - $o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (), - $o{patch} ? ( 'rr.patch = ?' => $o{patch} == 1 ? 1 : 0) : (), - $o{freeware} ? ( 'rr.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (), - $o{doujin} ? ( 'rr.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (), - defined $o{unreleased} ? ( - q|rr.released !s ?| => [ $o{unreleased} ? '>' : '<=', strftime('%Y%m%d', gmtime) ] ) : (), - $o{date} ? ( - '(rr.released >= ? AND rr.released <= ?)' => [ $o{date}[0], $o{date}[1] ] ) : (), - $o{languages} ? ( - 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))', => [ $o{languages} ] ) : (), - $o{platforms} ? ( - #'EXISTS(SELECT 1 FROM releases_platforms rp WHERE rp.rid = rr.id AND rp.platform IN(!l))' => [ $o{platforms} ] ) : (), - 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' => [ $o{platforms} ] ) : (), - defined $o{type} ? ( - 'rr.type = ?' => $o{type} ) : (), - $o{minage} ? ( - 'rr.minage !s ?' => [ $o{minage}[0] ? '<=' : '>=', $o{minage}[1] ] ) : (), - $o{media} ? ( - 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' => [ $o{media} ] ) : (), - $o{resolutions} ? ( - 'rr.resolution IN(!l)' => [ $o{resolutions} ] ) : (), + !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (), + $o{id} ? ( 'r.id = ?' => $o{id} ) : (), + $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (), + $o{vid} ? ( 'rv.vid = ?' => $o{vid} ) : (), + $o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (), + defined $o{patch} ? ( 'rr.patch = ?' => $o{patch} == 1 ? 1 : 0) : (), + defined $o{freeware} ? ( 'rr.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (), + defined $o{doujin} ? ( 'rr.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (), + defined $o{type} ? ( 'rr.type = ?' => $o{type} ) : (), + defined $o{date_before} ? ( 'rr.released <= ?' => $o{date_before} ) : (), + defined $o{date_after} ? ( 'rr.released >= ?' => $o{date_after} ) : (), + defined $o{resolution} ? ( 'rr.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (), + defined $o{voiced} ? ( 'rr.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (), + defined $o{ani_story} ? ( 'rr.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (), + defined $o{ani_ero} ? ( 'rr.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (), + defined $o{unreleased} ? ( 'rr.released !s ?' => [ $o{unreleased} ? '>' : '<=', strftime('%Y%m%d', gmtime) ] ) : (), + $o{lang} ? ( + 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (), + $o{olang} ? ( + 'rr.id IN(SELECT irv.rid FROM releases_vn irv JOIN releases ir ON ir.latest = irv.rid JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (), + $o{plat} ? ( + 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' => [ ref $o{plat} ? $o{plat} : [ $o{plat} ] ] ) : (), + $o{med} ? ( + 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' => [ ref $o{med} ? $o{med} : [ $o{med} ] ] ) : (), ); + # TODO: don't allow NULL for rr.minage after all, since this could be a lot easier... + if(exists $o{minage}) { + my @m = ref $o{minage} ? @{$o{minage}} : ($o{minage}); + my @w = ( + grep(!defined $_ || $_ == -1, @m) ? 'rr.minage IS NULL' : (), + grep(defined $_ && $_ != -1, @m) ? 'rr.minage IN(!s)' : () + ); + push @where, '('.join(' OR ', @w).')', [ grep defined $_ && $_ != -1, @m ]; + } + if($o{search}) { for (split /[ -,._]/, $o{search}) { s/%//g; diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index b9d66200..7440f495 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -123,21 +123,7 @@ sub dbUserAdd { # uid sub dbUserDel { - my($s, $id) = @_; - # TODO: delete/update all those referenced rows using PgSQL reference actions - $s->dbExec($_, $id) for ( - q|DELETE FROM rlists WHERE uid = ?|, - q|DELETE FROM wlists WHERE uid = ?|, - q|DELETE FROM votes WHERE uid = ?|, - q|DELETE FROM tags_vn WHERE uid = ?|, - q|DELETE FROM sessions WHERE uid = ?|, - q|DELETE FROM notifications WHERE uid = ?|, - q|UPDATE notifications SET c_byuser = 0 WHERE c_byuser = ?|, - q|UPDATE changes SET requester = 0 WHERE requester = ?|, - q|UPDATE tags SET addedby = 0 WHERE addedby = ?|, - q|UPDATE threads_posts SET uid = 0 WHERE uid = ?|, - q|DELETE FROM users WHERE id = ?| - ); + $_[0]->dbExec(q|DELETE FROM users WHERE id = ?|, $_[1]); } diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 6d99ba8a..d25a5796 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -10,7 +10,7 @@ use Encode 'decode_utf8'; our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom|; -# Options: id, rev, char, search, lang, platform, tags_include, tags_exclude, results, page, what, sort, reverse +# Options: id, rev, char, search, length, lang, olang, plat, tags_include, tags_exclude, hasani, results, page, what, sort, reverse # What: extended anime relations screenshots relgraph rating ranking changes # Sort: id rel pop rating title tagscore rand sub dbVNGet { @@ -29,10 +29,16 @@ sub dbVNGet { 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), - $o{lang} && @{$o{lang}} ? ( - 'v.c_languages && ARRAY[!l]::language[]' => [ $o{lang} ]) : (), - $o{platform} && @{$o{platform}} ? ( - '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (), + defined $o{length} ? ( + 'vr.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (), + $o{lang} ? ( + 'v.c_languages && ARRAY[!l]::language[]' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (), + $o{olang} ? ( + 'v.c_olang && ARRAY[!l]::language[]' => [ ref $o{olang} ? $o{olang} : [$o{olang}] ]) : (), + $o{plat} ? ( + '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", ref $o{plat} ? @{$o{plat}} : $o{plat}).')' => 1 ) : (), + defined $o{hasani} ? ( + '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (), $o{tags_include} && @{$o{tags_include}} ? ( '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 ] diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index f3f9d009..65b66f9e 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -7,7 +7,13 @@ use YAWF ':html'; use Exporter 'import'; use POSIX 'strftime', 'ceil', 'floor'; use VNDBUtil; -our @EXPORT = (@VNDBUtil::EXPORT, qw| liststat clearfloat cssicon tagscore mt minage |); +our @EXPORT = (@VNDBUtil::EXPORT, qw| liststat clearfloat cssicon tagscore mt minage fil_parse fil_serialize |); + + +# three ways to represent the same information +our $fil_escape = '_ !"#$%&\'()*+,-./:;<=>?@[\]^`{}~'; +our @fil_escape = split //, $fil_escape; +our %fil_escape = map +($fil_escape[$_], sprintf '%02d', $_), 0..$#fil_escape; # Argument: hashref with rstat and vstat @@ -89,5 +95,31 @@ sub minage { } +# arguments: $filter_string, @allowed_keys +sub fil_parse { + my $str = shift; + my %keys = map +($_,1), @_; + my %r; + for (split /\./, $str) { + next if !/^([a-z0-9_]+)-([a-zA-Z0-9_~]+)$/ || !$keys{$1}; + my($f, $v) = ($1, $2); + my @v = split /~/, $v; + s/_([0-9]{2})/$1 > $#fil_escape ? '' : $fil_escape[$1]/eg for(@v); + $r{$f} = @v > 1 ? \@v : $v[0] + } + return \%r; +} + + +sub fil_serialize { + my $fil = shift; + my $e = qr/([\Q$fil_escape\E])/; + return join '.', map { + my @v = ref $fil->{$_} ? @{$fil->{$_}} : ($fil->{$_}); + s/$e/_$fil_escape{$1}/g for(@v); + $_.'-'.join '~', @v + } keys %$fil; +} + 1; diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index 8f7ae76c..4cd9f04d 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -294,7 +294,7 @@ sub board { sort => $type eq 'an' ? 'id' : 'lastpost', reverse => 1, ); - $self->htmlHeader(title => $title, noindex => 1); + $self->htmlHeader(title => $title, noindex => 1, feeds => [ $type eq 'an' ? 'announcements' : 'posts' ]); $self->htmlMainTabs($type, $obj, 'disc') if $iid; div class => 'mainbox'; @@ -330,7 +330,7 @@ sub board { sub index { my $self = shift; - $self->htmlHeader(title => mt('_disindex_title'), noindex => 1); + $self->htmlHeader(title => mt('_disindex_title'), noindex => 1, feeds => [ 'posts', 'announcements' ]); div class => 'mainbox'; h1 mt '_disindex_title'; p class => 'browseopts'; diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 87839730..b26868d5 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -15,7 +15,7 @@ YAWF::register( qr{d([1-9]\d*)}, \&docpage, qr{setlang}, \&setlang, qr{nospam}, \&nospam, - qr{we-dont-like-ie6}, \&ie6message, + qr{we-dont-like-ie}, \&iemessage, qr{opensearch\.xml}, \&opensearch, # redirects for old URLs @@ -37,7 +37,7 @@ YAWF::register( sub homepage { my $self = shift; - $self->htmlHeader(title => mt '_site_title'); + $self->htmlHeader(title => mt('_site_title'), feeds => [ keys %{$self->{atom_feeds}} ]); div class => 'mainbox'; h1 mt '_site_title'; @@ -63,7 +63,8 @@ sub homepage { # Recent changes td; h1; - a href => '/hist', mt '_home_recentchanges'; + a href => '/hist', mt '_home_recentchanges'; txt ' '; + a href => '/feeds/changes.atom'; cssicon 'feed', mt '_atom_feed'; end; end; my $changes = $self->dbRevisionGet(what => 'item user', results => 10, auto => 1); ul; @@ -82,7 +83,8 @@ sub homepage { td; my $an = $self->dbThreadGet(type => 'an', sort => 'id', reverse => 1, results => 2); h1; - a href => '/t/an', mt '_home_announcements'; + a href => '/t/an', mt '_home_announcements'; txt ' '; + a href => '/feeds/announcements.atom'; cssicon 'feed', mt '_atom_feed'; end; end; for (@$an) { my $post = $self->dbPostGet(tid => $_->{id}, num => 1)->[0]; @@ -98,7 +100,8 @@ sub homepage { # Recent posts td; h1; - a href => '/t', mt '_home_recentposts'; + a href => '/t', mt '_home_recentposts'; txt ' '; + a href => '/feeds/posts.atom'; cssicon 'feed', mt '_atom_feed'; end; end; my $posts = $self->dbThreadGet(what => 'lastpost boardtitles', results => 10, sort => 'lastpost', reverse => 1, notusers => 1); ul; @@ -135,7 +138,7 @@ sub homepage { # Upcoming releases td; h1; - a href => strftime('/r?mi=%Y%m%d;o=a;s=released', gmtime), mt '_home_upcoming'; + a href => strftime('/r?fil=date_after-%Y%m%d;o=a;s=released', gmtime), mt '_home_upcoming'; end; my $upcoming = $self->dbReleaseGet(results => 10, unreleased => 1, what => 'platforms'); ul; @@ -155,7 +158,7 @@ sub homepage { # Just released td; h1; - a href => strftime('/r?ma=%Y%m%d;o=d;s=released', gmtime), mt '_home_justreleased'; + a href => strftime('/r?fil=date_before-%Y%m%d;o=d;s=released', gmtime), mt '_home_justreleased'; end; my $justrel = $self->dbReleaseGet(results => 10, sort => 'released', reverse => 1, unreleased => 0, what => 'platforms'); ul; @@ -216,7 +219,7 @@ sub history { releases => $f->{r}, ); - $self->htmlHeader(title => $title, noindex => 1); + $self->htmlHeader(title => $title, noindex => 1, feeds => [ 'changes' ]); $self->htmlMainTabs($type, $obj, 'hist') if $type; # url generator @@ -361,12 +364,12 @@ sub nospam { } -sub ie6message { +sub iemessage { my $self = shift; if($self->reqParam('i-still-want-access')) { (my $ref = $self->reqHeader('Referer') || '/') =~ s/^\Q$self->{url}//; - $ref = '/' if $ref eq '/we-dont-like-ie6'; + $ref = '/' if $ref eq '/we-dont-like-ie'; $self->resRedirect($ref, 'temp'); $self->resHeader('Set-Cookie', "ie-sucks=1; path=/; domain=$self->{cookie_domain}"); return; @@ -386,16 +389,16 @@ sub ie6message { div; h1 'Oops, we were too lazy to support your browser!'; p; - lit qq|We decided to stop supporting Internet Explorer 6, as it's a royal pain in | + lit qq|We decided to stop supporting Internet Explorer 6 and 7, as it's a royal pain in | .qq|the ass to make our site look good in a browser that doesn't want to cooperate with us.<br />| .qq|You can try one of the following free alternatives: | .qq|<a href="http://www.mozilla.com/firefox/">Firefox</a>, | .qq|<a href="http://www.opera.com/">Opera</a>, | .qq|<a href="http://www.apple.com/safari/">Safari</a>, or | .qq|<a href="http://www.google.com/chrome">Chrome</a>.<br /><br />| - .qq|If you're really stubborn about using Internet Explorer, upgrading to version 7 will also work.<br /><br />| + .qq|If you're really stubborn about using Internet Explorer, upgrading to version 8 will also work.<br /><br />| .qq|...and if you're mad, you can also choose to ignore this warning and | - .qq|<a href="/we-dont-like-ie6?i-still-want-access=1">open the site anyway</a>.|; + .qq|<a href="/we-dont-like-ie?i-still-want-access=1">open the site anyway</a>.|; end; end; end; diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 2931e25f..6e11e829 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -60,13 +60,13 @@ sub page { [ type => serialize => sub { mt "_ptype_$_[0]" } ], [ name => diff => 1 ], [ original => diff => 1 ], - [ alias => diff => 1 ], + [ alias => diff => qr/[ ,\n\.]/ ], [ 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 '_revision_nolink' }], - [ desc => diff => 1 ], + [ desc => diff => qr/[ ,\n\.]/ ], [ 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 @@ -153,6 +153,10 @@ sub _releases { td colspan => 6; i; lit $self->{l10n}->datestr($vn{$v->{vid}}[0]{released}); end; a href => "/v$v->{vid}", title => $v->{original}, $v->{title}; + span '('.join(', ', + (grep($_->{developer}, @{$vn{$v->{vid}}}) ? mt '_prodpage_dev' : ()), + (grep($_->{publisher}, @{$vn{$v->{vid}}}) ? mt '_prodpage_pub' : ()) + ).')'; end; end; for my $rel (@{$vn{$v->{vid}}}) { diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index f9c600bd..e2a18e1a 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -51,7 +51,7 @@ sub page { [ 'website' ], [ released => htmlize => sub { $self->{l10n}->datestr($_[0]) } ], [ minage => serialize => \&minage ], - [ notes => diff => 1 ], + [ notes => diff => qr/[ ,\n\.]/ ], [ platforms => join => ', ', split => sub { map mt("_plat_$_"), @{$_[0]} } ], [ media => join => ', ', split => sub { map $self->{media}{$_->{medium}} ? $_->{qty}.' '.mt("_med_$_->{medium}", $_->{qty}) : mt("_med_$_->{medium}",1), @{$_[0]} @@ -485,60 +485,46 @@ sub browse { my $f = $self->formValidate( { name => 'p', required => 0, default => 1, template => 'int' }, - { name => 's', required => 0, default => 'title', enum => [qw|released minage title|] }, { name => 'o', required => 0, default => 'a', enum => ['a', 'd'] }, { name => 'q', required => 0, default => '', maxlength => 500 }, - { name => 'ln', required => 0, multi => 1, default => '', enum => $self->{languages} }, - { name => 'pl', required => 0, multi => 1, default => '', enum => $self->{platforms} }, - { name => 'me', required => 0, multi => 1, default => '', enum => [ keys %{$self->{media}} ] }, - { name => 'tp', required => 0, default => '', enum => [ '', @{$self->{release_types}} ] }, - { name => 'pa', required => 0, default => 0, enum => [ 0..2 ] }, - { name => 'fw', required => 0, default => 0, enum => [ 0..2 ] }, - { name => 'do', required => 0, default => 0, enum => [ 0..2 ] }, - { name => 'ma_m', required => 0, default => 0, enum => [ 0, 1 ] }, - { name => 'ma_a', required => 0, default => 0, enum => [ grep defined($_), @{$self->{age_ratings}} ] }, - { name => 'mi', required => 0, default => 0, template => 'int' }, - { name => 'ma', required => 0, default => 99999999, template => 'int' }, - { name => 're', required => 0, multi => 1, default => 0, enum => [ 1..$#{$self->{resolutions}} ] }, + { name => 's', required => 0, default => 'title', enum => [qw|released minage title|] }, + { name => 'fil',required => 0, default => '' }, ); return 404 if $f->{_err}; - my @filters = ( - $f->{mi} > 0 || $f->{ma} < 99990000 ? (date => [ $f->{mi}, $f->{ma} ]) : (), - $f->{q} ? (search => $f->{q}) : (), - $f->{pl}[0] ? (platforms => $f->{pl}) : (), - $f->{ln}[0] ? (languages => $f->{ln}) : (), - $f->{me}[0] ? (media => $f->{me}) : (), - $f->{re}[0] ? (resolutions => $f->{re} ) : (), - $f->{tp} ? (type => $f->{tp}) : (), - $f->{ma_a} || $f->{ma_m} ? (minage => [$f->{ma_m}, $f->{ma_a}]) : (), - $f->{pa} ? (patch => $f->{pa}) : (), - $f->{fw} ? (freeware => $f->{fw}) : (), - $f->{do} ? (doujin => $f->{do}) : (), - ); - my($list, $np) = !@filters ? ([], 0) : $self->dbReleaseGet( + my $fil = fil_parse $f->{fil}, qw|type patch freeware doujin date_before date_after minage lang olang resolution plat med voiced ani_story ani_ero|; + _fil_compat($self, $fil); + $f->{fil} = fil_serialize($fil); + + my($list, $np) = !$f->{q} && !keys %$fil ? ([], 0) : $self->dbReleaseGet( sort => $f->{s}, reverse => $f->{o} eq 'd', page => $f->{p}, results => 50, what => 'platforms', - @filters, + $f->{q} ? ( search => $f->{q} ) : (), + %$fil ); - my $url = "/r?tp=$f->{tp};pa=$f->{pa};ma_m=$f->{ma_m};ma_a=$f->{ma_a};q=$f->{q};mi=$f->{mi};ma=$f->{ma};do=$f->{do};fw=$f->{fw}"; - $_&&($url .= ";ln=$_") for @{$f->{ln}}; - $_&&($url .= ";pl=$_") for @{$f->{pl}}; - $_&&($url .= ";re=$_") for @{$f->{re}}; - $_&&($url .= ";me=$_") for @{$f->{me}}; - $self->htmlHeader(title => mt('_rbrowse_title')); - _filters($self, $f, !@filters || !@$list); + + form method => 'get', action => '/r', 'accept-charset' => 'UTF-8'; + div class => 'mainbox'; + h1 mt '_rbrowse_title'; + $self->htmlSearchBox('r', $f->{q}); + a id => 'filselect', href => '#r'; + lit '<i>▸</i> '.mt('_rbrowse_filters').'<i></i>'; + end; + input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil}; + end; + end; + $self->htmlBrowse( class => 'relbrowse', items => $list, options => $f, nextpage => $np, - pageurl => "$url;s=$f->{s};o=$f->{o}", - sorturl => $url, + pageurl => "/r?q=$f->{q};fil=$f->{fil};s=$f->{s};o=$f->{o}", + sorturl => "/r?q=$f->{q};fil=$f->{fil}", header => [ [ mt('_rbrowse_col_released'), 'released' ], [ mt('_rbrowse_col_minage'), 'minage' ], @@ -564,7 +550,7 @@ sub browse { end; }, ) if @$list; - if(@filters && !@$list) { + if(($f->{q} || keys %$fil) && !@$list) { div class => 'mainbox'; h1 mt '_rbrowse_noresults_title'; div class => 'notice'; @@ -576,108 +562,35 @@ sub browse { } -sub _filters { - my($self, $f, $shown) = @_; - - form method => 'get', action => '/r', 'accept-charset' => 'UTF-8'; - div class => 'mainbox'; - h1 mt '_rbrowse_title'; - - $self->htmlSearchBox('r', $f->{q}); - - a id => 'advselect', href => '#'; - lit '<i>'.($shown?'▾':'▸').'</i> '.mt('_rbrowse_filters'); - end; - div id => 'advoptions', !$shown ? (class => 'hidden') : (); - - h2 mt '_rbrowse_filters'; - table class => 'formtable', style => 'margin-left: 0'; - Tr class => 'newfield'; - td class => 'label'; label for => 'ma_m', mt '_rbrowse_minage'; end; - td class => 'field'; - Select id => 'ma_m', name => 'ma_m', style => 'width: 160px'; - option value => 0, $f->{ma_m} == 0 ? ('selected' => 'selected') : (), mt '_rbrowse_ge'; - option value => 1, $f->{ma_m} == 1 ? ('selected' => 'selected') : (), mt '_rbrowse_le'; - end; - Select id => 'ma_a', name => 'ma_a', style => 'width: 80px; text-align: center'; - defined($_) && option value => $_, $f->{ma_a} == $_ ? ('selected' => 'selected') : (), minage $_ - for (@{$self->{age_ratings}}); - end; - end; - td rowspan => 5, style => 'padding-left: 40px'; - label for => 're', mt '_rbrowse_resolution'; br; - Select id => 're', name => 're', multiple => 'multiple', size => 8; - my $l=''; - for my $i (1..$#{$self->{resolutions}}) { - if($l ne $self->{resolutions}[$i][1]) { - end if $l; - $l = $self->{resolutions}[$i][1]; - optgroup label => $l; - } - option value => $i, scalar grep($i==$_, @{$f->{re}}) ? (selected => 'selected') : (), $self->{resolutions}[$i][0]; - } - end if $l; - end; - end; - end; - $self->htmlFormPart($f, [ select => short => 'tp', name => mt('_rbrowse_type'), - options => [ ['', mt '_rbrowse_all'], map [ $_, mt "_rtype_$_" ], @{$self->{release_types}} ]]); - $self->htmlFormPart($f, [ select => short => 'pa', name => mt('_rbrowse_patch'), - options => [ [0, mt '_rbrowse_all' ], [1, mt '_rbrowse_patchonly'], [2, mt '_rbrowse_patchnone']]]); - $self->htmlFormPart($f, [ select => short => 'fw', name => mt('_rbrowse_freeware'), - options => [ [0, mt '_rbrowse_all' ], [1, mt '_rbrowse_freewareonly'], [2, mt '_rbrowse_freewarenone']]]); - $self->htmlFormPart($f, [ select => short => 'do', name => mt('_rbrowse_doujin'), - options => [ [0, mt '_rbrowse_all' ], [1, mt '_rbrowse_doujinonly'], [2, mt '_rbrowse_doujinnone']]]); - $self->htmlFormPart($f, [ date => short => 'mi', name => mt '_rbrowse_dateafter' ]); - $self->htmlFormPart($f, [ date => short => 'ma', name => mt '_rbrowse_datebefore' ]); - end; - - h2; - txt mt '_rbrowse_languages'; - b ' ('.mt('_rbrowse_boolor').')'; - end; - for my $i (@{$self->{languages}}) { - span; - input type => 'checkbox', name => 'ln', value => $i, id => "lang_$i", grep($_ eq $i, @{$f->{ln}}) ? (checked => 'checked') : (); - label for => "lang_$i"; - cssicon "lang $i", mt "_lang_$i"; - txt mt "_lang_$i"; - end; - end; - } - - h2; - txt mt '_rbrowse_platforms'; - b ' ('.mt('_rbrowse_boolor').')'; - end; - for my $i (sort @{$self->{platforms}}) { - span; - input type => 'checkbox', name => 'pl', value => $i, id => "plat_$i", grep($_ eq $i, @{$f->{pl}}) ? (checked => 'checked') : (); - label for => "plat_$i"; - cssicon $i, mt "_plat_$i"; - txt mt "_plat_$i"; - end; - end; - } - - h2; - txt mt '_rbrowse_media'; - b ' ('.mt('_rbrowse_boolor').')'; - end; - for my $i (sort keys %{$self->{media}}) { - span; - input type => 'checkbox', name => 'me', value => $i, id => "med_$i", grep($_ eq $i, @{$f->{me}}) ? (checked => 'checked') : (); - label for => "med_$i", mt "_med_$i", 1; - end; - } - - div style => 'text-align: center; clear: left;'; - input type => 'submit', value => mt('_rbrowse_apply'), class => 'submit'; - input type => 'reset', value => mt('_rbrowse_clear'), class => 'submit', onclick => 'location.href="/r"'; - end; - end; - end; - end; +# provide compatibility with old filter URLs +sub _fil_compat { + my($self, $fil) = @_; + my $f = $self->formValidate( + { name => 'ln', required => 0, multi => 1, default => '', enum => $self->{languages} }, + { name => 'pl', required => 0, multi => 1, default => '', enum => $self->{platforms} }, + { name => 'me', required => 0, multi => 1, default => '', enum => [ keys %{$self->{media}} ] }, + { name => 'tp', required => 0, default => '', enum => [ '', @{$self->{release_types}} ] }, + { name => 'pa', required => 0, default => 0, enum => [ 0..2 ] }, + { name => 'fw', required => 0, default => 0, enum => [ 0..2 ] }, + { name => 'do', required => 0, default => 0, enum => [ 0..2 ] }, + { name => 'ma_m', required => 0, default => 0, enum => [ 0, 1 ] }, + { name => 'ma_a', required => 0, default => 0, enum => [ grep defined($_), @{$self->{age_ratings}} ] }, + { name => 'mi', required => 0, default => 0, template => 'int' }, + { name => 'ma', required => 0, default => 99999999, template => 'int' }, + { name => 're', required => 0, multi => 1, default => 0, enum => [ 1..$#{$self->{resolutions}} ] }, + ); + return if $f->{_err}; + $fil->{minage} //= [ grep defined($_) && $f->{ma_m} ? $f->{ma_a} >= $_ : $f->{ma_a} <= $_, @{$self->{age_ratings}} ] if $f->{ma_a} || $f->{ma_m}; + $fil->{date_after} //= $f->{mi} if $f->{mi}; + $fil->{date_before} //= $f->{ma} if $f->{ma} < 99990000; + $fil->{plat} //= $f->{pl} if $f->{pl}[0]; + $fil->{lang} //= $f->{ln} if $f->{ln}[0]; + $fil->{med} //= $f->{me} if $f->{me}[0]; + $fil->{resolution} //= $f->{re} if $f->{re}[0]; + $fil->{type} //= $f->{tp} if $f->{tp}; + $fil->{patch} //= $f->{pa} == 2 ? 0 : 1 if $f->{pa}; + $fil->{freeware} //= $f->{fw} == 2 ? 0 : 1 if $f->{fw}; + $fil->{doujin} //= $f->{do} == 2 ? 0 : 1 if $f->{do}; } diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index f3193b57..8e30da99 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -21,14 +21,12 @@ sub list { { name => 'p', required => 0, default => 1, template => 'int' }, { name => 'q', required => 0, default => '' }, { name => 'sq', required => 0, default => '' }, - { name => 'ln', required => 0, multi => 1, enum => $self->{languages}, default => '' }, - { 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($self->{cookie_prefix}.'tagspoil') =~ /^([0-2])$/ ? $1 : 0, enum => [0..2] }, + { name => 'fil',required => 0, default => '' }, ); return 404 if $f->{_err}; $f->{q} ||= $f->{sq}; + my $fil = fil_parse $f->{fil}, qw|length hasani taginc tagexc tagspoil lang olang plat|; + _fil_compat($self, $fil); if($f->{q}) { return $self->resRedirect('/'.$1.$2.(!$3 ? '' : $1 eq 'd' ? '#'.$3 : '.'.$3), 'temp') @@ -37,9 +35,11 @@ sub list { # for URL compatibilty with older versions (ugly hack to get English strings) my @lang; $f->{q} =~ s/\s*$VNDB::L10N::en::Lexicon{"_lang_$_"}\s*//&&push @lang, $_ for (@{$self->{languages}}); - $f->{ln} = $f->{ln}[0] ? [ @{$f->{ln}}, @lang ] : \@lang; + $fil->{lang} = $fil->{lang} ? [ ref($fil->{lang}) ? @{$fil->{lang}} : $fil->{lang}, @lang ] : \@lang if @lang; } + $f->{fil} = fil_serialize $fil; + # TODO: this should be moved to dbVNGet() in order for savable VN filters to be useful my @ignored; my $tagfind = sub { return map { @@ -47,10 +47,10 @@ sub list { push @ignored, [$_, 0] if !$i; push @ignored, [$_, 1] if $i && $i->{meta}; $i && !$i->{meta} ? $i->{id} : (); - } grep $_, split /\s*,\s*/, $_[0]; + } grep $_, ref $_[0] ? @{$_[0]} : ($_[0]||'') }; - my @ti = $tagfind->($f->{ti}); - my @te = $tagfind->($f->{te}); + my @ti = $tagfind->(delete $fil->{taginc}); + my @te = $tagfind->(delete $fil->{tagexc}); $f->{s} = 'title' if !@ti && $f->{s} eq 'tagscore'; $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o}; @@ -62,28 +62,15 @@ sub list { results => 50, page => $f->{p}, 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 ]) : (), + @ti ? (tags_include => [ delete $fil->{tagspoil}, \@ti ]) : (), @te ? (tags_exclude => \@te) : (), + %$fil ); $self->resRedirect('/v'.$list->[0]{id}, 'temp') if $f->{q} && @$list == 1 && $f->{p} == 1; $self->htmlHeader(title => mt('_vnbrowse_title'), search => $f->{q}); - _filters($self, $f, $char, \@ignored); - - 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->htmlBrowseVN($list, $f, $np, $url, scalar @ti); - $self->htmlFooter; -} - - -sub _filters { - my($self, $f, $char, $ign) = @_; form action => '/v/all', 'accept-charset' => 'UTF-8', method => 'get'; div class => 'mainbox'; @@ -91,72 +78,45 @@ sub _filters { $self->htmlSearchBox('v', $f->{q}); p class => 'browseopts'; for ('all', 'a'..'z', 0) { - a href => "/v/$_", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? mt('_char_all') : $_ ? uc $_ : '#'; + a href => "/v/$_?q=$f->{q};fil=$f->{fil}", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? mt('_char_all') : $_ ? uc $_ : '#'; } end; - if(@$ign) { + if(@ignored) { div class => 'warning'; h2 mt '_vnbrowse_tagign_title'; ul; - li $_->[0].' ('.mt('_vnbrowse_tagign_'.($_->[1]?'meta':'notfound')).')' for @$ign; + li $_->[0].' ('.mt('_vnbrowse_tagign_'.($_->[1]?'meta':'notfound')).')' for @ignored; end; end; } - a id => 'advselect', href => '#'; - lit '<i>▸</i> '.mt('_vnbrowse_advsearch'); + a id => 'filselect', href => '#v'; + lit '<i>▸</i> '.mt('_rbrowse_filters').'<i></i>'; # TODO: it's not *r*browse end; - div id => 'advoptions', class => 'hidden vnoptions'; - - h2; - txt mt '_vnbrowse_tags'; - b ' ('.mt('_vnbrowse_booland').')'; - end; - table class => 'formtable', style => 'margin-left: 0'; - $self->htmlFormPart($f, [ input => short => 'ti', name => mt('_vnbrowse_taginc'), width => 350 ]); - $self->htmlFormPart($f, [ radio => short => 'sp', name => '', options => [map [$_, mt '_vnbrowse_spoil'.$_], 0..2]]); - $self->htmlFormPart($f, [ input => short => 'te', name => mt('_vnbrowse_tagexc'), width => 350 ]); - end; - - h2; - txt mt '_vnbrowse_lang'; - b ' ('.mt('_vnbrowse_boolor').')'; - end; - for my $i (@{$self->{languages}}) { - span; - input type => 'checkbox', name => 'ln', value => $i, id => "lang_$i", - (scalar grep $_ eq $i, @{$f->{ln}}) ? (checked => 'checked') : (); - label for => "lang_$i"; - cssicon "lang $i", mt "_lang_$i"; - txt mt "_lang_$i"; - end; - end; - } + input type => 'hidden', class => 'hidden', name => 'fil', id => 'fil', value => $f->{fil}; + end; + end; # /form - h2; - txt mt '_vnbrowse_plat'; - b ' ('.mt('_vnbrowse_boolor').')'; - end; - for my $i (sort @{$self->{platforms}}) { - next if $i eq 'oth'; - span; - input type => 'checkbox', id => "plat_$i", name => 'pl', value => $i, - (scalar grep $_ eq $i, @{$f->{pl}}) ? (checked => 'checked') : (); - label for => "plat_$i"; - cssicon $i, mt "_plat_$i"; - txt mt "_plat_$i"; - end; - end; - } + $self->htmlBrowseVN($list, $f, $np, "/v/$char?q=$f->{q};fil=$f->{fil}", scalar @ti); + $self->htmlFooter; +} - div style => 'text-align: center; clear: left;'; - input type => 'submit', value => mt('_vnbrowse_apply'), class => 'submit'; - input type => 'reset', value => mt('_vnbrowse_clear'), class => 'submit', onclick => 'location.href="/v/all"'; - end; - end; - end; - end; + +sub _fil_compat { + my($self, $fil) = @_; + my $f = $self->formValidate( + { name => 'ln', required => 0, multi => 1, enum => $self->{languages}, default => '' }, + { 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($self->{cookie_prefix}.'tagspoil') =~ /^([0-2])$/ ? $1 : 0, enum => [0..2] }, + ); + $fil->{lang} //= $f->{ln} if $f->{ln}[0]; + $fil->{plat} //= $f->{pl} if $f->{pl}[0]; + $fil->{taginc} //= $f->{ti} if $f->{ti}; + $fil->{tagexc} //= $f->{te} if $f->{te}; + $fil->{tagspoil} //= $f->{sp}; } diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 4a4b3f4f..1a3eb514 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -191,8 +191,8 @@ sub _revision { $self->htmlRevision('v', $prev, $v, [ title => diff => 1 ], [ original => diff => 1 ], - [ alias => diff => 1 ], - [ desc => diff => 1 ], + [ alias => diff => qr/[ ,\n\.]/ ], + [ desc => diff => qr/[ ,\n\.]/ ], [ 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 '_revision_nolink' diff --git a/lib/VNDB/Plugin/TransAdmin.pm b/lib/VNDB/Plugin/TransAdmin.pm index 4859ea67..6d4de67e 100644 --- a/lib/VNDB/Plugin/TransAdmin.pm +++ b/lib/VNDB/Plugin/TransAdmin.pm @@ -121,7 +121,7 @@ sub _savelang { # re-read the file and regenerate the JS in case we're not running as CGI if($INC{"FCGI.pm"}) { VNDB::L10N::loadfile(); - VNDB::checkjs(); + system "make -sC $VNDB::ROOT js" if $self->{regen_static}; } } diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index a862911d..280ceddb 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -153,7 +153,7 @@ sub htmlHiddenMessage { # Where @fields is a list of fields as arrayrefs with: # [ shortname, displayname, %options ], # Where %options: -# diff => 1/0, whether do show a diff on this field +# diff => 1/0/regex, whether to show a diff on this field, and what to split it with (1 = character-level diff) # serialize => coderef, should convert the field into a readable string, no HTML allowed # htmlize => same as serialize, but HTML is allowed and this can't be diff'ed # split => coderef, should return an array of HTML strings that can be diff'ed. (implies diff => 1) @@ -226,7 +226,7 @@ sub revdiff { my($i, $type, $old, $new, $short, %o) = @_; $o{serialize} ||= $o{htmlize}; - $o{diff}++ if $o{split}; + $o{diff} = 1 if $o{split}; $o{join} ||= ''; my $ser1 = $o{serialize} ? $o{serialize}->($old->{$short}, $old) : $old->{$short}; @@ -234,8 +234,9 @@ sub revdiff { return if $ser1 eq $ser2; if($o{diff} && $ser1 && $ser2) { - my @ser1 = $o{split} ? $o{split}->($ser1) : map xml_escape($_), split //, $ser1; - my @ser2 = $o{split} ? $o{split}->($ser2) : map xml_escape($_), split //, $ser2; + my $sep = ref $o{diff} ? qr/($o{diff})/ : qr//; + my @ser1 = $o{split} ? $o{split}->($ser1) : map xml_escape($_), split $sep, $ser1; + my @ser2 = $o{split} ? $o{split}->($ser2) : map xml_escape($_), split $sep, $ser2; return if $o{split} && $#ser1 == $#ser2 && !grep $ser1[$_] ne $ser2[$_], 0..$#ser1; $ser1 = $ser2 = ''; diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index 93a35e0e..b5dc3e9c 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -10,7 +10,7 @@ use VNDB::Func; our @EXPORT = qw|htmlHeader htmlFooter|; -sub htmlHeader { # %options->{ title, noindex, search } +sub htmlHeader { # %options->{ title, noindex, search, feeds } my($self, %o) = @_; my $skin = $self->reqParam('skin') || $self->authInfo->{skin} || $self->{skin_default}; $skin = $self->{skin_default} if !$self->{skins}{$skin} || !-d "$VNDB::ROOT/static/s/$skin"; @@ -26,6 +26,8 @@ sub htmlHeader { # %options->{ title, noindex, search } (my $css = $self->authInfo->{customcss}) =~ s/\n/ /g; style type => 'text/css', $css; } + Link rel => 'alternate', type => 'application/atom+xml', href => "/feeds/$_.atom", title => $self->{atom_feeds}{$_}[1] + for ($o{feeds} ? @{$o{feeds}} : ()); meta name => 'robots', content => 'noindex, follow', undef if $o{noindex}; end; body; @@ -153,7 +155,7 @@ sub htmlFooter { a href => $self->{source_url}, mt '_footer_source'; end; end; # /div maincontent - script type => 'text/javascript', src => $self->{url_static}.'/f/script.js?'.$self->{version}, ''; + script type => 'text/javascript', src => $self->{url_static}.'/f/js/'.$self->{l10n}->language_tag().'.js?'.$self->{version}, ''; end; # /body end; # /html diff --git a/lib/VNDBUtil.pm b/lib/VNDBUtil.pm index f23811a9..b0213d07 100644 --- a/lib/VNDBUtil.pm +++ b/lib/VNDBUtil.pm @@ -29,95 +29,118 @@ 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($result, $last, $length, $rmnewline, @open) = ('', 0, 0, 0, 'first'); + # escapes, returns string, and takes care of $length and $maxlength; also + # takes care to remove newlines and double spaces when necessary my $e = sub { local $_ = shift; + s/^\n// if $rmnewline && $rmnewline--; + s/\n{5,}/\n\n/g if $open[$#open] ne 'code'; + s/ +/ /g if $open[$#open] ne 'code'; + $length += length $_; + if($maxlength && $length > $maxlength) { + $_ = substr($_, 0, $maxlength-$length); + s/[ \.,:;]+[^ \.,:;]*$//; # cleanly cut off on word boundary + } s/&/&/g; s/>/>/g; s/</</g; s/\n/<br \/>/g if !$maxlength; - s/\n/ /g if $maxlength; + s/\n/ /g if $maxlength; return $_; }; - for (split /(\s|\n|\[[^\]]+\])/, $raw) { - next if !defined $_; - next if $_ eq ''; + while($raw =~ m{( + ([tdvpr][1-9][0-9]*\.[1-9][0-9]*) | # 2. exid + ([tdvprug][1-9][0-9]*) | # 3. id + (\[[^\s\]]+\]) | # 4. tag + ((?:https?|ftp)://[^><"\n\s\]\[]+[\d\w=/-]) # 5. url + )}xg) { + my($match, $exid, $id, $tag, $url) = ($1, $2, $3, $4, $5); - # (note to self: stop using unreadable hacks like these!) - $rmnewline-- && $_ eq "\n" && next if $rmnewline; + # add string before the match + $result .= $e->(substr $raw, $last, (pos($raw)-length($match))-$last); + last if $maxlength && $length > $maxlength; + $last = pos $raw; - 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') { + # handle tags + if($tag) { + $tag = lc $tag; + if($tag eq '[raw]') { + push @open, 'raw'; + next; + } elsif($tag eq '[spoiler]') { + push @open, 'spoiler'; + $result .= '<b class="spoiler">'; + next; + } elsif($tag eq '[quote]') { + push @open, 'quote'; + $result .= '<div class="quote">' if !$maxlength; + $rmnewline = 1; + next; + } elsif($tag eq '[code]') { + push @open, 'code'; + $result .= '<pre>' if !$maxlength; + $rmnewline = 1; + next; + } elsif($tag eq '[/spoiler]' && $open[$#open] eq 'spoiler') { $result .= '</b>'; pop @open; - } - next; - } elsif (lc$_ eq '[/quote]') { - if($open[$#open] eq 'quote') { + next; + } elsif($tag eq '[/quote]' && $open[$#open] eq 'quote') { $result .= '</div>' if !$maxlength; $rmnewline = 1; - pop @open; - } - next; - } elsif(lc$_ eq '[/url]') { - if($open[$#open] eq 'url') { + next; + } elsif($tag eq '[/url]' && $open[$#open] eq 'url') { $result .= '</a>'; pop @open; + next; + } elsif($tag =~ s{\[url=((https?://|/)[^\]>]+)\]}{<a href="$1" rel="nofollow">}i) { + $result .= $tag; + push @open, 'url'; + next; } - 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) { + } + # handle URLs + if($url && !grep(/url/, @open)) { $length += 4; last if $maxlength && $length > $maxlength; - $result .= $_; + $result .= sprintf '<a href="%s" rel="nofollow">link</a>', $url; 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; + } + # id + if(($id || $exid) && substr($raw, $last-1-length($match), 1) !~ /[\w]/ && substr($raw, $last, 1) !~ /[\w]/) { + $length += length $match; last if $maxlength && $length > $maxlength; - $result .= $_; - next; + $result .= sprintf '<a href="/%s">%1$s</a>', $match; + next } - } elsif($open[$#open] eq 'raw' && lc$_ eq '[/raw]') { + } + + if($tag && $open[$#open] eq 'raw' && lc$tag eq '[/raw]') { pop @open; next; - } elsif($open[$#open] eq 'code' && lc$_ eq '[/code]') { + } + + if($tag && $open[$#open] eq 'code' && lc$tag eq '[/code]') { $result .= '</pre>' if !$maxlength; pop @open; next; } - # normal text processing - $length += length $_; + # We'll only get here when the bbcode input isn't correct or something else + # didn't work out. In that case, just output whatever we've matched. + $result .= $e->($match); last if $maxlength && $length > $maxlength; - $result .= $e->($_); } + # the last unmatched part, just escape and output + $result .= $e->(substr $raw, $last); + # close open tags while((local $_ = pop @open) ne 'first') { $result .= $_ eq 'url' ? '</a>' : $_ eq 'spoiler' ? '</b>' : ''; diff --git a/static/f/icons.png b/static/f/icons.png Binary files differindex 3b761c85..e8d9f1f3 100644 --- a/static/f/icons.png +++ b/static/f/icons.png diff --git a/util/jsgen.pl b/util/jsgen.pl index 27cbc6da..03b79ab5 100755 --- a/util/jsgen.pl +++ b/util/jsgen.pl @@ -22,74 +22,111 @@ use LangFile; use VNDB::L10N; -sub l10n { - # 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; # lang1 => { key1 => .., key22 => .. }, lang2 => { .. } + +sub l10n_load { + # fetch all text from lang.txt my $lang = LangFile->new(read => "$ROOT/data/lang.txt"); - my $cur; # 0 = none/excluded, 1 = TL lines my $key; while((my $l = $lang->read())) { my $type = shift @$l; - if($type eq 'key') { - my $k = shift @$l; - $cur = grep $k =~ /$_/, @keys; - $key = $k; - } - if($type eq 'tl' && $cur) { - my($lang, $sync, $val) = @$l; - next if !$val; - $lang{$key}{$lang} = $val; - } + $key = shift @$l if $type eq 'key'; + $lang{$l->[0]}{$key} = $l->[2] if $type eq 'tl'; } +} + - # generate JS code +sub l10n { + my($lang, $js) = @_; + + # parse the .js code and replace mt()'s that can be modified in-place, otherwise add to the @keys + my @keys; + $js =~ s{(?:mt\('([a-z0-9_]+)'([,\)])|l10n /([^/]+)/)}# + my($k, $s, $q) = ($1, $2, $3); + my $v = $k ? $lang{$lang}{$k} || $lang{'en'}{$k} : ''; + if($q) { $q ne '<perl regex>' && push @keys, qr/$q/; '' } + elsif($s eq ')' && $v && $v !~ /[\~\[\]]/) { + $v =~ s/"/\\"/g; + $v =~ s/\n/\\n/g; + qq{"$v"} + } else { + push @keys, quotemeta($k); + "mt('$k'$s" + } + #eg; + # 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)/; + + # generate header my $r = "L10N_STR = {\n"; my $first = 1; - for my $key (sort keys %lang) { + for my $key (sort keys %{$lang{$lang}}) { + next if !grep $key =~ /$_/, @keys; $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 .= sprintf qq| %s: "%s"|, $lang, $val; + my $val = $lang{$lang}{$key} || $lang{'en'}{$key}; + $val =~ s/"/\\"/g; + $val =~ s/\n/\\n/g; + $val =~ s/\[index,.+$// if $key =~ /^_vnlength_/; # special casing the VN lengths, since the JS mt() doesn't handle [index] + $r .= sprintf qq| %s: "%s"|, $key !~ /^[a-z0-9_]+$/ ? "'$key'" : $key, $val; + } + $r .= "\n};"; + return ("$r\n", $js); +} + + +# screen resolution information, suitable for usage in filFSelect() +sub resolutions { + my $res_cat = ''; + my $resolutions = ''; + my $comma = 0; + for my $i (0..$#{$S{resolutions}}) { + my $r = $S{resolutions}[$i]; + if($res_cat ne $r->[1]) { + $resolutions .= ']' if $res_cat; + $resolutions .= ",['$r->[1]',"; + $res_cat = $r->[1]; + $comma = 0; } - $r .= "\n }"; + $resolutions .= ($comma ? ',' : '')."[$i,'$r->[0]']"; + $comma = 1; } - $r .= "\n};\n"; - $r .= 'L10N_LANG = [ '.join(', ', map qq{"$_"}, VNDB::L10N::languages()).' ];'; - return "$r\n"; + $resolutions .= ']' if $res_cat; + return "resolutions = [ $resolutions ];\n"; } sub jsgen { - # JavaScript::Minifier::XS doesn't correctly handle perl's unicode, - # so just do everything in raw bytes instead. - open my $JS, '<', "$ROOT/data/script.js" or die $!; + l10n_load(); + my $common = ''; + $common .= resolutions(); + $common .= sprintf "rlst_rstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_rstat}}; + $common .= sprintf "rlst_vstat = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{rlst_vstat}}; + $common .= sprintf "cookie_prefix = '%s';\n", $S{cookie_prefix}; + $common .= sprintf "age_ratings = [ %s ];\n", join ',', map !defined $_ ? -1 : $_, @{$S{age_ratings}}; + $common .= sprintf "languages = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{languages}}; + $common .= sprintf "platforms = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{platforms}}; + $common .= sprintf "media = [ %s ];\n", join ', ', map qq{"$_"}, sort keys %{$S{media}}; + $common .= sprintf "release_types = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{release_types}}; + $common .= sprintf "animated = [ %s ];\n", join ', ', @{$S{animated}}; + $common .= sprintf "voiced = [ %s ];\n", join ', ', @{$S{voiced}}; + $common .= sprintf "vn_lengths = [ %s ];\n", join ', ', @{$S{vn_lengths}}; + $common .= sprintf "L10N_LANG = [ %s ];\n", join(', ', map qq{"$_"}, VNDB::L10N::languages()); + + open my $JS, '<:utf8', "$ROOT/data/script.js" or die $!; 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($head.$js) : $head.$js; - close $NEWJS; + + for my $l (VNDB::L10N::languages()) { + my($head, $body) = l10n($l, $js); + # JavaScript::Minifier::XS doesn't correctly handle perl's unicode, so manually encode + my $content = encode_utf8($head . $common . $body); + open my $NEWJS, '>', "$ROOT/static/f/js/$l.js" or die $!; + print $NEWJS $JavaScript::Minifier::XS::VERSION ? JavaScript::Minifier::XS::minify($content) : $content; + close $NEWJS; + } } jsgen; diff --git a/util/sql/all.sql b/util/sql/all.sql index fd1f0a73..4408c6df 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -1,8 +1,5 @@ -- 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 @@ -30,52 +27,74 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid -- triggers -CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); -CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); 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 stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache 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 vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); +CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) 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 anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) 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 vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW WHEN (NEW.image < 0) 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 screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify(); -CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE vn_relgraph_notify(); +CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW + WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph + OR OLD.latest IS DISTINCT FROM NEW.latest + OR OLD.c_released IS DISTINCT FROM NEW.c_released + OR OLD.c_languages IS DISTINCT FROM NEW.c_languages + ) EXECUTE PROCEDURE vn_relgraph_notify(); -CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE producer_relgraph_notify(); +CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW + WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph + OR OLD.latest IS DISTINCT FROM NEW.latest + ) 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(); +CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW + WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden) + EXECUTE PROCEDURE release_vncache_update(); CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_dbdel(); -CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_listdel(); -CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW EXECUTE PROCEDURE notify_listdel(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); -CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW EXECUTE PROCEDURE notify_dbedit(); -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(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce(); + +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW + WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL AND NOT NEW.hidden + OR NEW.hidden IS DISTINCT FROM OLD.hidden + OR NEW.latest IS DISTINCT FROM OLD.latest + ) EXECUTE PROCEDURE vn_vnsearch_notify(); +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW + WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest) + EXECUTE PROCEDURE vn_vnsearch_notify(); -- Sequences used for ID generation of items not in the DB diff --git a/util/sql/func.sql b/util/sql/func.sql index 371e3ed0..53af121c 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -26,6 +26,21 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND rr1.released <> 0 GROUP BY rv1.vid ), 0), + c_olang = ARRAY( + SELECT lang + FROM releases_lang + WHERE rid = ( + SELECT r.latest + FROM releases_vn rv + JOIN releases r ON rv.rid = r.latest + JOIN releases_rev rr ON rr.id = r.latest AND rr.rid = r.id + WHERE rr.released > 0 + AND NOT r.hidden + AND rv.vid = $1 + ORDER BY rr.released + LIMIT 1 + ) + ), c_languages = ARRAY( SELECT rl2.lang FROM releases_rev rr2 @@ -59,6 +74,8 @@ $$ LANGUAGE sql; -- recalculate vn.c_popularity CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$ BEGIN + -- the following queries only update rows with popularity > 0, so make sure to reset all rows first + UPDATE vn SET c_popularity = NULL; CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS SELECT v.uid, v.vid, sqrt(count(*))::real FROM votes v @@ -67,7 +84,7 @@ BEGIN 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); + UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 s1 WHERE s1.vid = vn.id; RETURN; END; $$ LANGUAGE plpgsql; @@ -393,7 +410,7 @@ 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 + ELSE 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; @@ -404,12 +421,12 @@ BEGIN END IF; ELSIF TG_OP = 'UPDATE' AND TG_TABLE_NAME <> 'users' THEN - IF OLD.hidden = TRUE AND NEW.hidden = FALSE THEN + IF OLD.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; - ELSIF OLD.hidden = FALSE AND NEW.hidden = TRUE THEN + ELSIF OLD.hidden = FALSE THEN IF TG_TABLE_NAME = 'threads' THEN UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts'; END IF; @@ -426,6 +443,7 @@ $$ LANGUAGE 'plpgsql'; -- insert rows into anime for new vn_anime.aid items +-- (this is a BEFORE trigger) CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$ BEGIN IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN @@ -439,36 +457,21 @@ $$ 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; + BEGIN NOTIFY anime; 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; + BEGIN NOTIFY coverimage; 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; + BEGIN NOTIFY screenshot; RETURN NULL; END; $$ LANGUAGE plpgsql; @@ -558,11 +561,9 @@ $$ 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; + PERFORM update_vncache(vid) FROM ( + SELECT DISTINCT vid FROM releases_vn WHERE rid = OLD.latest OR rid = NEW.latest + ) AS v(vid); RETURN NULL; END; $$ LANGUAGE plpgsql; @@ -575,11 +576,9 @@ CREATE OR REPLACE FUNCTION update_hidlock() RETURNS trigger AS $$ DECLARE r record; BEGIN - IF OLD.latest IS DISTINCT FROM NEW.latest THEN - SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest; - NEW.hidden := r.ihid; - NEW.locked := r.ilock; - END IF; + SELECT INTO r ihid, ilock FROM changes WHERE id = NEW.latest; + NEW.hidden := r.ihid; + NEW.locked := r.ilock; RETURN NEW; END; $$ LANGUAGE plpgsql; @@ -663,123 +662,111 @@ END; $$ LANGUAGE plpgsql; --- called on UPDATE vn / producers / releases +-- called on UPDATE vn / producers / releases when (NOT OLD.hidden AND NEW.hidden) CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$ BEGIN - -- item is deleted? - IF NOT OLD.hidden AND NEW.hidden THEN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT DISTINCT 'dbdel'::notification_ntype, - (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype, - c.requester, NEW.id, c2.rev, x.title, c2.requester - -- look for changes of the deleted entry - -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs - FROM changes c - JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr - JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest - WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id - UNION SELECT rr.id, rr2.title FROM releases_rev rr - JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest - WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id - UNION SELECT pr.id, pr2.name FROM producers_rev pr - JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest - WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id - ) x(id, title) ON c.id = x.id - -- join info about the deletion itself - JOIN changes c2 ON c2.id = NEW.latest - WHERE c.requester <> 1 -- exclude Multi - -- exclude the user who deleted the entry - AND c.requester <> c2.requester; - END IF; + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT DISTINCT 'dbdel'::notification_ntype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype, + c.requester, NEW.id, c2.rev, x.title, c2.requester + -- look for changes of the deleted entry + -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs + FROM changes c + JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr + JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest + WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id + UNION SELECT rr.id, rr2.title FROM releases_rev rr + JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest + WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id + UNION SELECT pr.id, pr2.name FROM producers_rev pr + JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest + WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id + ) x(id, title) ON c.id = x.id + -- join info about the deletion itself + JOIN changes c2 ON c2.id = NEW.latest + WHERE c.requester <> 1 -- exclude Multi + -- exclude the user who deleted the entry + AND c.requester <> c2.requester; RETURN NULL; END; $$ LANGUAGE plpgsql; --- called on UPDATE vn / releases +-- called on UPDATE vn / releases when (NOT OLD.hidden AND NEW.hidden) CREATE OR REPLACE FUNCTION notify_listdel() RETURNS trigger AS $$ BEGIN - -- item is deleted? - IF NOT OLD.hidden AND NEW.hidden THEN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT DISTINCT 'listdel'::notification_ntype, - (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' ELSE 'r' END)::notification_ltype, - u.uid, NEW.id, c.rev, x.title, c.requester - -- look for users who should get this notify - FROM ( - -- voted on the VN - SELECT uid FROM votes WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - -- VN in wishlist - UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - -- release in release list - UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id - -- there's also a special case which we're ignoring here: - -- when a VN linked to a release in a user's release list is deleted - -- normally, the releases are also deleted, so a notify is generated anyway - ) u - -- fetch info about this edit - JOIN changes c ON c.id = NEW.latest - JOIN ( - SELECT id, title FROM vn_rev WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id - UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id - ) x ON c.id = x.id; - END IF; + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT DISTINCT 'listdel'::notification_ntype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' ELSE 'r' END)::notification_ltype, + u.uid, NEW.id, c.rev, x.title, c.requester + -- look for users who should get this notify + FROM ( + -- voted on the VN + SELECT uid FROM votes WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id + -- VN in wishlist + UNION SELECT uid FROM wlists WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id + -- release in release list + UNION SELECT uid FROM rlists WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id + -- there's also a special case which we're ignoring here: + -- when a VN linked to a release in a user's release list is deleted + -- normally, the releases are also deleted, so a notify is generated anyway + ) u + -- fetch info about this edit + JOIN changes c ON c.id = NEW.latest + JOIN ( + SELECT id, title FROM vn_rev WHERE TG_TABLE_NAME = 'vn' AND vid = NEW.id + UNION SELECT id, title FROM releases_rev WHERE TG_TABLE_NAME = 'releases' AND rid = NEW.id + ) x ON c.id = x.id; RETURN NULL; END; $$ LANGUAGE plpgsql; --- called on UPDATE vn / producers / releases +-- called on UPDATE vn / producers / releases when (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) -- this trigger is very similar to notify_dbdel() CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$ BEGIN - -- item is edited but not deleted? (deleted items are handled by the dbdel notify) - IF OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden THEN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT DISTINCT 'dbedit'::notification_ntype, - (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype, - c.requester, NEW.id, c2.rev, x.title, c2.requester - -- look for changes of the edited entry - FROM changes c - JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr - JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest - WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id - UNION SELECT rr.id, rr2.title FROM releases_rev rr - JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest - WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id - UNION SELECT pr.id, pr2.name FROM producers_rev pr - JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest - WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id - ) x(id, title) ON c.id = x.id - -- join info about the deletion itself - JOIN changes c2 ON c2.id = NEW.latest - -- join info about the user who should get this notification - JOIN users u ON u.id = c.requester - -- exclude the user who edited the entry - WHERE c.requester <> c2.requester - -- exclude users who don't want this notify - AND u.notify_dbedit; - END IF; + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT DISTINCT 'dbedit'::notification_ntype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' ELSE 'p' END)::notification_ltype, + c.requester, NEW.id, c2.rev, x.title, c2.requester + -- look for changes of the edited entry + FROM changes c + JOIN ( SELECT vr.id, vr2.title FROM vn_rev vr + JOIN vn v ON v.id = vr.vid JOIN vn_rev vr2 ON vr2.id = v.latest + WHERE TG_TABLE_NAME = 'vn' AND vr.vid = NEW.id + UNION SELECT rr.id, rr2.title FROM releases_rev rr + JOIN releases r ON r.id = rr.rid JOIN releases_rev rr2 ON rr2.id = r.latest + WHERE TG_TABLE_NAME = 'releases' AND rr.rid = NEW.id + UNION SELECT pr.id, pr2.name FROM producers_rev pr + JOIN producers p ON p.id = pr.pid JOIN producers_rev pr2 ON pr2.id = p.latest + WHERE TG_TABLE_NAME = 'producers' AND pr.pid = NEW.id + ) x(id, title) ON c.id = x.id + -- join info about the deletion itself + JOIN changes c2 ON c2.id = NEW.latest + -- join info about the user who should get this notification + JOIN users u ON u.id = c.requester + -- exclude the user who edited the entry + WHERE c.requester <> c2.requester + -- exclude users who don't want this notify + AND u.notify_dbedit; RETURN NULL; END; $$ LANGUAGE plpgsql; --- called on INSERT INTO threads_posts +-- called on INSERT INTO threads_posts when (NEW.num = 1) CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$ BEGIN - -- new thread? - IF NEW.num = 1 THEN - INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) - SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid - FROM threads t - JOIN threads_boards tb ON tb.tid = t.id - -- get the users who want this announcement - JOIN users u ON u.notify_announce - WHERE t.id = NEW.tid - AND tb.type = 'an' -- announcement board - AND NOT t.hidden; - END IF; + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT 'announce', 't', u.id, t.id, 1, t.title, NEw.uid + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + -- get the users who want this announcement + JOIN users u ON u.notify_announce + WHERE t.id = NEW.tid + AND tb.type = 'an' -- announcement board + AND NOT t.hidden; RETURN NULL; END; $$ LANGUAGE plpgsql; diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 72ecd9cc..6911a40b 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -36,7 +36,7 @@ CREATE TABLE notifications ( iid integer NOT NULL, subid integer, c_title text NOT NULL, - c_byuser integer + c_byuser integer NOT NULL DEFAULT 0 ); -- producers @@ -194,7 +194,7 @@ CREATE TABLE tags ( 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 + addedby integer NOT NULL DEFAULT 0 ); -- tags_aliases @@ -293,7 +293,8 @@ CREATE TABLE vn ( c_popularity real, c_rating real, c_votecount integer NOT NULL DEFAULT 0, - c_search text + c_search text, + c_olang language[] NOT NULL DEFAULT '{}' ); -- vn_anime @@ -358,9 +359,9 @@ CREATE TABLE wlists ( -ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id); -ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id); -ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id); +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT; 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); @@ -378,19 +379,19 @@ ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes 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 (uid) REFERENCES users (id) ON DELETE CASCADE; 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 sessions ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; 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 tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; 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_posts ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; 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); @@ -403,8 +404,8 @@ ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn 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 (uid) REFERENCES users (id) ON DELETE CASCADE; 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 (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/util/updates/update_2.14.sql b/util/updates/update_2.14.sql new file mode 100644 index 00000000..ea06e689 --- /dev/null +++ b/util/updates/update_2.14.sql @@ -0,0 +1,145 @@ + +-- add vn.c_olang +ALTER TABLE vn ADD COLUMN c_olang language[] NOT NULL DEFAULT '{}'; + + +-- reload functions +\i util/sql/func.sql + + +-- regenerate vn.c_* columns +SELECT COUNT(*) FROM (SELECT update_vncache(id) FROM vn WHERE NOT hidden) s; + + +-- redefine the triggers to use the new conditional triggers in PostgreSQL 9.0 + +DROP TRIGGER hidlock_update ON vn; +DROP TRIGGER hidlock_update ON producers; +DROP TRIGGER hidlock_update ON releases; +CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); +CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); + + +DROP TRIGGER vn_stats_update ON vn; +DROP TRIGGER producers_stats_update ON producers; +DROP TRIGGER releases_stats_update ON releases; +DROP TRIGGER threads_stats_update ON threads; +DROP TRIGGER threads_posts_stats_update ON threads_posts; +DROP TRIGGER users_stats_update ON users; +CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache(); +CREATE TRIGGER stats_cache AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache(); + +DROP TRIGGER vn_anime_aid ON vn_anime; +CREATE TRIGGER vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid(); +CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid(); + +DROP TRIGGER anime_fetch_notify ON anime; +CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); + +DROP TRIGGER vn_rev_image_notify ON vn_rev; +CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE vn_rev_image_notify(); + +DROP TRIGGER screenshot_process_notify ON screenshots; +CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify(); + +DROP TRIGGER vn_relgraph_notify ON vn; +CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW + WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph + OR OLD.latest IS DISTINCT FROM NEW.latest + OR OLD.c_released IS DISTINCT FROM NEW.c_released + OR OLD.c_languages IS DISTINCT FROM NEW.c_languages + ) EXECUTE PROCEDURE vn_relgraph_notify(); + +DROP TRIGGER producer_relgraph_notify ON producers; +CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW + WHEN (OLD.rgraph IS DISTINCT FROM NEW.rgraph + OR OLD.latest IS DISTINCT FROM NEW.latest + ) EXECUTE PROCEDURE producer_relgraph_notify(); + +DROP TRIGGER release_vncache_update ON releases; +CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW + WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden) + EXECUTE PROCEDURE release_vncache_update(); + +DROP TRIGGER notify_dbdel ON vn; +DROP TRIGGER notify_dbdel ON producers; +DROP TRIGGER notify_dbdel ON releases; +CREATE TRIGGER notify_dbdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON producers FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); + +DROP TRIGGER notify_listdel ON vn; +DROP TRIGGER notify_listdel ON releases; +CREATE TRIGGER notify_listdel AFTER UPDATE ON vn FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel(); +CREATE TRIGGER notify_listdel AFTER UPDATE ON releases FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_listdel(); + +DROP TRIGGER notify_dbedit ON vn; +DROP TRIGGER notify_dbedit ON producers; +DROP TRIGGER notify_dbedit ON releases; +CREATE TRIGGER notify_dbedit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); + +DROP TRIGGER notify_announce ON threads_posts; +CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce(); + +DROP TRIGGER vn_vnsearch_notify ON vn; +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW + WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL AND NOT NEW.hidden + OR NEW.hidden IS DISTINCT FROM OLD.hidden + OR NEW.latest IS DISTINCT FROM OLD.latest + ) EXECUTE PROCEDURE vn_vnsearch_notify(); + +DROP TRIGGER vn_vnsearch_notify ON releases; +CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON releases FOR EACH ROW + WHEN (NEW.hidden IS DISTINCT FROM OLD.hidden OR NEW.latest IS DISTINCT FROM OLD.latest) + EXECUTE PROCEDURE vn_vnsearch_notify(); + + + +-- add ON DELETE clause to all foreign keys referencing users (id) +-- and change some defaults/constraints to make sure it'll actually work + +ALTER TABLE changes DROP CONSTRAINT changes_requester_fkey; +ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; + +UPDATE notifications SET c_byuser = 0 WHERE c_byuser IS NULL; +ALTER TABLE notifications ALTER COLUMN c_byuser SET DEFAULT 0; +ALTER TABLE notifications ALTER COLUMN c_byuser SET NOT NULL; +ALTER TABLE notifications DROP CONSTRAINT notifications_uid_fkey; +ALTER TABLE notifications DROP CONSTRAINT notifications_c_byuser_fkey; +ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; +ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT; + +ALTER TABLE rlists DROP CONSTRAINT rlists_uid_fkey; +ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE sessions DROP CONSTRAINT sessions_uid_fkey; +ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE tags ALTER COLUMN addedby SET DEFAULT 0; +ALTER TABLE tags DROP CONSTRAINT tags_addedby_fkey; +ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT; + +ALTER TABLE tags_vn DROP CONSTRAINT tags_vn_uid_fkey; +ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE threads_posts DROP CONSTRAINT threads_posts_uid_fkey; +ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; + +ALTER TABLE votes DROP CONSTRAINT votes_uid_fkey; +ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + +ALTER TABLE wlists DROP CONSTRAINT wlists_uid_fkey; +ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; + diff --git a/util/vndb.pl b/util/vndb.pl index 29c2648c..183cea4d 100755 --- a/util/vndb.pl +++ b/util/vndb.pl @@ -24,13 +24,10 @@ use SkinFile; our(%O, %S); -# load and (if required) regenerate the skins +# load the skins # NOTE: $S{skins} can be modified in data/config.pl, allowing deletion of skins or forcing only one skin -$S{skins} = readskins(); - - -# automatically regenerate script.js when required and possible -checkjs(); +my $skin = SkinFile->new("$ROOT/static/s"); +$S{skins} = { map +($_ => [ $skin->get($_, 'name'), $skin->get($_, 'userid') ]), $skin->list }; # load lang.dat @@ -41,6 +38,10 @@ VNDB::L10N::loadfile(); require $ROOT.'/data/global.pl'; +# automatically regenerate the skins and script.js and whatever else should be done +system "make -sC $ROOT" if $S{regen_static}; + + YAWF::init( %O, namespace => 'VNDB', @@ -69,11 +70,11 @@ sub reqinit { $self->authInit; # check for IE6 - if($self->reqHeader('User-Agent') && $self->reqHeader('User-Agent') =~ /MSIE 6/ - && !$self->reqCookie('ie-sucks') && $self->reqPath ne 'we-dont-like-ie6') { + if($self->reqHeader('User-Agent') && $self->reqHeader('User-Agent') =~ /MSIE [67]/ + && !$self->reqCookie('ie-sucks') && $self->reqPath ne 'we-dont-like-ie') { # act as if we're opening /we-dont-like-ie6 (ugly hack, until YAWF supports preventing URL handlers from firing) $ENV{HTTP_REFERER} = $ENV{REQUEST_URI}; - $ENV{REQUEST_URI} = '/we-dont-like-ie6'; + $ENV{REQUEST_URI} = '/we-dont-like-ie'; } # load some stats (used for about all pageviews, anyway) @@ -96,39 +97,3 @@ sub handle404 { $self->htmlFooter; } - -sub readskins { - my %skins; # dirname => skin name - my @regen; - my $lasttemplate = [stat "$ROOT/data/style.css"]->[9]; - my $skin = SkinFile->new("$ROOT/static/s"); - for my $n ($skin->list) { - $skins{$n} = [ $skin->get($n, 'name'), $skin->get($n, 'userid') ]; - next if !$skins{$n}[0]; - - my $f = "$ROOT/static/s/$n"; - my $css = -f "$f/style.css" && [stat "$f/style.css"]->[9] || 0; - my $boxbg = -f "$f/boxbg.png" && [stat "$f/boxbg.png"]->[9] || 0; - my $lastgen = $css < $boxbg ? $css : $boxbg; - push @regen, $n if (!$lastgen && -x $f && (!$css && !$boxbg || $css && -w "$f/style.css" || $boxbg && -w "$f/boxbg.png")) - || ([stat "$f/conf"]->[9] > $lastgen || $lasttemplate > $lastgen) && -w "$f/style.css" && -w "$f/boxbg.png"; - } - system "$ROOT/util/skingen.pl", @regen if @regen; - return \%skins; -} - - -sub checkjs { - my $script = "$ROOT/static/f/script.js"; - my $lastmod = [stat $script]->[9]; - system "$ROOT/util/jsgen.pl" if - (!-e $script && -x "$ROOT/static/f") - || (-e $script && -w $script && ( - $lastmod < [stat "$ROOT/data/script.js"]->[9] - || $lastmod < [stat "$ROOT/data/lang.txt"]->[9] - || (-e "$ROOT/data/config.pl" && $lastmod < [stat "$ROOT/data/config.pl"]->[9]) - || $lastmod < [stat "$ROOT/data/global.pl"]->[9] - || $lastmod < [stat "$ROOT/util/jsgen.pl"]->[9] - )); -} - |