diff options
61 files changed, 1286 insertions, 504 deletions
@@ -1,5 +1,24 @@ git - ? - Added Slovak to the language list + - Centered the thumbnails on the screenshots viewer + - Improved date selector + - Made the release date a required field + - Versioned the deleting and locking of database entries + - Multi's announcements are colored in blue + - Abstracted parsing skin config files into a SkinFile module + - Automatically generate the skin credits on d7, by reading the skin files + - Only tagmods can create top-level tags + - Notification system for + - PMs + - Notifying users of a deletion of an entry they contributed to + - Notifying users of a deletion they have in their (wish)list + - Notifying users of an edit of an entry they contributed to + - Notifying users of site announcements + - Removed the ?l10n= paremeter + - Remove sessions that haven't been used for more than a month + - Properly copy over search string on switching with the searchtabs + - Converted language columns in SQL to an ENUM type + - Differentiate between pt-PT and pt-BR 2.10 - 2010-01-10 - VN score on tag pages use plain averages instead of bayesian rating @@ -29,15 +29,15 @@ # sql-import # Imports util/sql/all.sql into your (presumably empty) database # -# update-2.10 -# Updates all non-versioned items to 2.10 +# update-<version> +# Updates all non-versioned items from the version before to <version>. # # NOTE: This Makefile has only been tested using a recent version of GNU make # in a relatively up-to-date Arch Linux environment, and may not work in other # environments. Patches to improve the portability are always welcome. -.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 +.PHONY: all dirs js skins robots chmod chmod-tladmin multi-start multi-stop multi-restart sql-import update-2.10 update-2.11 all: dirs js skins robots data/config.pl @@ -109,7 +109,7 @@ multi-restart: # Small perl script that tries to connect to the PostgreSQL database using 'psql', with the # connection settings from data/config.pl. May not work in all configurations, though... define runpsql - perl -MDBI -e 'package VNDB;\ + @perl -MDBI -e 'package VNDB;\ $$ROOT=".";\ require "data/global.pl";\ $$_=(DBI->parse_dsn($$VNDB::O{db_login}[0]))[4];\ @@ -133,7 +133,11 @@ sql-import: update-2.10: all $(multi-stop) - @${runpsql} < util/updates/update_2.10.sql + ${runpsql} < util/updates/update_2.10.sql $(multi-start) +update-2.11: all + $(multi-stop) + ${runpsql} < util/updates/update_2.11.sql + $(multi-start) diff --git a/data/docs/7 b/data/docs/7 index 66a708f2..7a527a0d 100644 --- a/data/docs/7 +++ b/data/docs/7 @@ -63,30 +63,7 @@ <br /> <b>Skins</b> - <dl> - <dt>applehq</dt><dd> - <a href="?skin=lb">Little Busters! (pink)</a>, - <a href="?skin=term">Neon (black)</a> - </dd><dt>EchoMateria</dt><dd> - <a href="?skin=aselia_01">Eien no Aselia (falu red)</a>, - <a href="?skin=carnevale">Gekkou no Carnevale (black)</a>, - <a href="?skin=eiel">Jingai Makyo (peach-orange)</a>, - <a href="?skin=ever17_01">Ever17 (bondi blue)</a>, - <a href="?skin=fate_01">Fate/stay night (seal brown)</a>, - <a href="?skin=fate_02">Fate/stay night (pale carmine)</a>, - <a href="?skin=higu">Higurashi no Naku Koro ni (orange)</a>, - <a href="?skin=lb_02">Little Busters! (lemon chiffon)</a>, - <a href="?skin=primitive">Primitive Link (pale chestnut)</a>, - <a href="?skin=saya">Saya no Uta (dark scarlet)</a>, - <a href="?skin=seinarukana">Seinarukana (white)</a>, - <a href="?skin=taka">Sora no Iro, Mizu no Iro (turquoise)</a>, - <a href="?skin=tsukihime">Tsukihime (midnight blue)</a>, - <a href="?skin=tsukihime_02">Tsukihime (black)</a> - </dd><dt>Yorhel</dt><dd> - <a href="?skin=angel">Angelic Serenade (dark blue)</a>, - <a href="?skin=grey">Touhou (grey)</a> - </dd> - </dl> +:SKINCONTRIB: <br /> <b>Top 5 Contributors</b> diff --git a/data/docs/7.cs b/data/docs/7.cs index 2c81139c..820772a6 100644 --- a/data/docs/7.cs +++ b/data/docs/7.cs @@ -60,30 +60,7 @@ <br /> <b>Skiny</b> - <dl> - <dt>applehq</dt><dd> - <a href="?skin=lb">Little Busters! (pink)</a>, - <a href="?skin=term">Neon (black)</a> - </dd><dt>EchoMateria</dt><dd> - <a href="?skin=aselia_01">Eien no Aselia (falu red)</a>, - <a href="?skin=carnevale">Gekkou no Carnevale (black)</a>, - <a href="?skin=eiel">Jingai Makyo (peach-orange)</a>, - <a href="?skin=ever17_01">Ever17 (bondi blue)</a>, - <a href="?skin=fate_01">Fate/stay night (seal brown)</a>, - <a href="?skin=fate_02">Fate/stay night (pale carmine)</a>, - <a href="?skin=higu">Higurashi no Naku Koro ni (orange)</a>, - <a href="?skin=lb_02">Little Busters! (lemon chiffon)</a>, - <a href="?skin=primitive">Primitive Link (pale chestnut)</a>, - <a href="?skin=saya">Saya no Uta (dark scarlet)</a>, - <a href="?skin=seinarukana">Seinarukana (white)</a>, - <a href="?skin=taka">Sora no Iro, Mizu no Iro (turquoise)</a>, - <a href="?skin=tsukihime">Tsukihime (midnight blue)</a>, - <a href="?skin=tsukihime_02">Tsukihime (black)</a> - </dd><dt>Yorhel</dt><dd> - <a href="?skin=angel">Angelic Serenade (dark blue)</a>, - <a href="?skin=grey">Touhou (grey)</a> - </dd> - </dl> +:SKINCONTRIB: <br /> <b>5 nejaktivnějších přispívajících</b> diff --git a/data/docs/7.hu b/data/docs/7.hu index 66153c16..db68d515 100644 --- a/data/docs/7.hu +++ b/data/docs/7.hu @@ -62,30 +62,7 @@ is növekszik. <br /> <b>Témák</b> - <dl> - <dt>applehq</dt><dd> - <a href="?skin=lb">Little Busters! (pink)</a>, - <a href="?skin=term">Neon (black)</a> - </dd><dt>EchoMateria</dt><dd> - <a href="?skin=aselia_01">Eien no Aselia (falu red)</a>, - <a href="?skin=carnevale">Gekkou no Carnevale (black)</a>, - <a href="?skin=eiel">Jingai Makyo (peach-orange)</a>, - <a href="?skin=ever17_01">Ever17 (bondi blue)</a>, - <a href="?skin=fate_01">Fate/stay night (seal brown)</a>, - <a href="?skin=fate_02">Fate/stay night (pale carmine)</a>, - <a href="?skin=higu">Higurashi no Naku Koro ni (orange)</a>, - <a href="?skin=lb_02">Little Busters! (lemon chiffon)</a>, - <a href="?skin=primitive">Primitive Link (pale chestnut)</a>, - <a href="?skin=saya">Saya no Uta (dark scarlet)</a>, - <a href="?skin=seinarukana">Seinarukana (white)</a>, - <a href="?skin=taka">Sora no Iro, Mizu no Iro (turquoise)</a>, - <a href="?skin=tsukihime">Tsukihime (midnight blue)</a>, - <a href="?skin=tsukihime_02">Tsukihime (black)</a> - </dd><dt>Yorhel</dt><dd> - <a href="?skin=angel">Angelic Serenade (dark blue)</a>, - <a href="?skin=grey">Touhou (grey)</a> - </dd> - </dl> +:SKINCONTRIB: <br /> <b>Top 5 hozzájáruló</b> diff --git a/data/docs/7.ru b/data/docs/7.ru index dc5309e7..da5bf322 100644 --- a/data/docs/7.ru +++ b/data/docs/7.ru @@ -69,30 +69,7 @@ <br /> <b>Дизайнеры шкурок</b> - <dl> - <dt>applehq</dt><dd> - <a href="?skin=lb">Little Busters! (розовый)</a>, - <a href="?skin=term">Neon (чёрный)</a> - </dd><dt>EchoMateria</dt><dd> - <a href="?skin=aselia_01">Eien no Aselia (бордовый)</a>, - <a href="?skin=carnevale">Gekkou no Carnevale (черный)</a>, - <a href="?skin=eiel">Jingai Makyo (персиковый)</a>, - <a href="?skin=ever17_01">Ever17 (вода пляжа Бонди)</a>, - <a href="?skin=fate_01">Fate/stay night (серо-коричневый)</a>, - <a href="?skin=fate_02">Fate/stay night (бледно-карминный)</a>, - <a href="?skin=higu">Higurashi no Naku Koro ni (оранжевый)</a>, - <a href="?skin=lb_02">Little Busters! (желто-персиковый)</a>, - <a href="?skin=primitive">Primitive Link (бледно-каштановый)</a>, - <a href="?skin=saya">Saya no Uta (алый)</a>, - <a href="?skin=seinarukana">Seinarukana (белый)</a>, - <a href="?skin=taka">Sora no Iro, Mizu no Iro (бирюзовый)</a>, - <a href="?skin=tsukihime">Tsukihime (полуночно-синий)</a>, - <a href="?skin=tsukihime_02">Tsukihime (чёрный)</a> - </dd><dt>Yorhel</dt><dd> - <a href="?skin=angel">Angelic Serenade (тёмно-синий)</a>, - <a href="?skin=grey">Touhou (серый)</a> - </dd> - </dl> +:SKINCONTRIB: <br /> <b>Пятёрка лучших контрибьютеров</b> diff --git a/data/global.pl b/data/global.pl index 94d0aec8..0d58e9b4 100644 --- a/data/global.pl +++ b/data/global.pl @@ -31,7 +31,7 @@ our %S = (%S, [qw| hist board boardmod edit tag mod lock del tagmod |], # 4 [qw| hist board boardmod edit tag mod lock del tagmod usermod |], # 5 ], - languages => [qw|cs da de en es fi fr hu it ja ko nl no pl pt ru sk sv tr vi zh|], + languages => [qw|cs da de en es fi fr hu it ja ko nl no pl pt-br pt-pt ru sk sv tr vi zh|], producer_types => [qw|co in ng|], discussion_boards => [qw|an db v p u|], vn_lengths => [ 0..5 ], diff --git a/data/lang.txt b/data/lang.txt index 61bfc2b5..371ed6d4 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -291,11 +291,17 @@ ru : Польский cs : Polsky hu : Lengyel -:_lang_pt -en : Portuguese -ru : Португальский -cs : Portugalsky -hu : Portugál +:_lang_pt-br +en : Portuguese (Brazil) +ru*: Португальский +cs*: Portugalsky +hu*: Portugál + +:_lang_pt-pt +en : Portuguese (Portugal) +ru*: Португальский +cs*: Portugalsky +hu*: Portugál :_lang_ru en : Russian @@ -1208,6 +1214,24 @@ ru : Отправить cs : Potvrdit hu : Beküldés +:_form_ihid +en : Deleted +ru*: +cs*: +hu*: + +:_form_ilock +en : Locked +ru*: +cs*: +hu*: + +:_form_hidlock_note +en : Note: edit summary of the last edit should indicate the reason for the deletion or lock. +ru*: +cs*: +hu*: + # Common javascript strings @@ -1405,12 +1429,11 @@ ru : Мой список желаемого cs : Můj wishlist hu : A kivánságlistám -# [_1] = number of messages -:_menu_mymessages -en : My Messages ([_1]) -ru : Мои сообщения ([_1]) -cs : Mé zprávy ([_1]) -hu : Az üzenenteim ([_1]) +:_menu_mynotifications +en : My Notifications +ru*: Мои сообщения +cs*: Mé zprávy +hu*: Üzenenteim :_menu_mychanges en : My Recent Changes @@ -1579,32 +1602,6 @@ ru : правка cs : upravit hu : szerkesztés -# hide/unhide a DB item -:_mtabs_hide -en : hide -ru : скрыть -cs : skrýt -hu : elrejtés - -:_mtabs_unhide -en : unhide -ru : показать -cs : odkrýt -hu : megjelenítés - -# lock/unlock for editing -:_mtabs_lock -en : lock -ru : заблокировать -cs : zamknout -hu : bezárás - -:_mtabs_unlock -en : unlock -ru : разблокировать -cs : odemknout -hu : kinyitás - # delete :_mtabs_del en : del @@ -1698,6 +1695,18 @@ ru : Нет cs*: hu : Nem +:_revfield_ihid +en : Deleted +ru*: +cs*: +hu*: + +:_revfield_ilock +en : Locked +ru*: +cs*: +hu*: + # tabs above the search boxes @@ -4613,6 +4622,147 @@ cs : Tagy hu : Címkék +# Notifications (/u+/notifies) + +:_usern_title +en : My notifications +ru*: +cs*: +hu*: + +:_usern_o_unread +en : Unread notifications +ru*: +cs*: +hu*: + +:_usern_o_alsoread +en : All notifications +ru*: +cs*: +hu*: + +:_usern_nonotifies +en : No notifications! +ru*: +cs*: +hu*: + +:_usern_col_type +en : Type +ru*: +cs*: +hu*: + +:_usern_col_age +en : Age +ru*: +cs*: +hu*: + +:_usern_col_id +en : ID +ru*: +cs*: +hu*: + +:_usern_col_act +en : Action +ru*: +cs*: +hu*: + +:_usern_type_pm +en : Private Message +ru*: +cs*: +hu*: + +:_usern_type_dbdel +en : Entry you contributed to has been deleted +ru*: +cs*: +hu*: + +:_usern_type_listdel +en : VN in your (wish)list has been deleted +ru*: +cs*: +hu*: + +:_usern_type_dbedit +en : Entry you contributed to has been edited +ru*: +cs*: +hu*: + +:_usern_type_announce +en : Site announcement +ru*: +cs*: +hu*: + +:_usern_n_t_new +en : New thread [_1] by [_2] +ru*: +cs*: +hu*: + +:_usern_n_t_reply +en : Reply to [_1] by [_2] +ru*: +cs*: +hu*: + +:_usern_n_item_edit +en : Edit of [_1] by [_2] +ru*: +cs*: +hu*: + +:_usern_but_markread +en : mark selected read +ru*: +cs*: +hu*: + +:_usern_but_remove +en : remove selected +ru*: +cs*: +hu*: + +:_usern_set_saved +en : Settings successfully saved. +ru*: +cs*: +hu*: + +:_usern_set_title +en : Settings +ru*: +cs*: +hu*: + +:_usern_set_dbedit +en : Notify me about edits of database entries I contributed to. +ru*: +cs*: +hu*: + +:_usern_set_announce +en : Notify me about site announcements +ru*: +cs*: +hu*: + +:_usern_set_submit +en : Save +ru*: +cs*: +hu*: + + ############################################################################# @@ -5712,7 +5862,7 @@ cs : Položka smazána hu : Tárgy törölve :_hiddenmsg_msg -en : This item has been deleted from the database, File a request on the +en : This item has been deleted from the database. File a request on the [url,_1,discussion board] to undelete this page. ru : Данная запись удалена из базы данных. Пожалуйста, подайте заявку на [url,_1,форуме] для восстановления этой страницы. diff --git a/data/script.js b/data/script.js index d2ed0d6f..07dd1581 100644 --- a/data/script.js +++ b/data/script.js @@ -512,33 +512,82 @@ 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 ]; - var year = tag('select', {style: 'width: 70px', onchange: dateSerialize}, tag('option', {value:0}, mt('_js_date_year'))); - for(var i=1980; i<=(new Date()).getFullYear()+5; i++) - year.appendChild(tag('option', {value: i, selected: i==val[0]}, i)); - year.appendChild(tag('option', {value: 9999, selected: val[0]==9999}, 'TBA')); + /* 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]) + ); + ddInit(year, 'bottom', dateDD); - var month = tag('select', {style: 'width: 70px', onchange: dateSerialize}, tag('option', {value:99}, mt('_js_date_month'))); - for(var i=1; i<=12; i++) - month.appendChild(tag('option', {value: i, selected: i==val[1]}, 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 day = tag('select', {style: 'width: 70px', onchange: dateSerialize}, tag('option', {value:99}, mt('_js_date_day'))); - for(var i=1; i<=31; i++) - day.appendChild(tag('option', {value: i, selected: i==val[2]}, 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); obj.parentNode.insertBefore(tag('div', {date_obj: obj}, year, month, day), obj); } -function dateSerialize() { - var div = this.parentNode; - var sel = byName(div, 'select'); +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 dateSerialize(div) { + var div = div.parentNode; + var sel = byClass(div, 'b', 'datepart'); var val = [ - sel[0].options[sel[0].selectedIndex].value*1, - sel[1].options[sel[1].selectedIndex].value*1, - sel[2].options[sel[2].selectedIndex].value*1 + date_years[sel[0].date_sel][0], + date_months[sel[1].date_sel][0], + date_days[sel[2].date_sel][0], ]; div.date_obj.value = val[0] == 0 ? 0 : val[0] == 9999 ? 99999999 : val[0]*10000+val[1]*100+(val[1]==99?99:val[2]); } @@ -1737,12 +1786,11 @@ if(byId('nsfw_show')) { if(byId('nsfwhide')) { byId('nsfwhide').onclick = function() { var shown = 0; - var l = byName(byId('screenshots'), 'div'); + var l = byClass(byId('screenshots'), 'a', 'scrlnk'); for(var i=0; i<l.length; i++) { if(hasClass(l[i], 'nsfw')) { var hidden = !hasClass(l[i], 'hidden'); setClass(l[i], 'hidden', hidden); - setClass(byName(l[i], 'a')[0], 'hidden', hidden); // for the image viewer if(!hidden) shown++; } else @@ -1903,7 +1951,7 @@ if(byId('lang_select')) { var icon = tag('acronym', {'class':'icons lang '+ln}, ' '); lst.appendChild(tag('li', {'class':'lang_selector'}, mt_curlang == ln ? tag('i', icon, mt('_lang_'+ln)) - : tag('a', {href:'?l10n='+ln}, icon, L10N_STR['_lang_'+ln][ln]||mt('_lang_'+ln)) + : tag('a', {href:'/setlang?lang='+ln}, icon, L10N_STR['_lang_'+ln][ln]||mt('_lang_'+ln)) )); } return lst; @@ -1911,6 +1959,36 @@ if(byId('lang_select')) { d.onclick = function() {return false}; } +// "check all" checkbox +{ + var f = function() { + var l = byName('input'); + for(var i=0; i<l.length; i++) + if(l[i].type == this.type && l[i].name == this.name) + l[i].checked = this.checked; + }; + var l = byClass('input', 'checkall'); + for(var i=0; i<l.length; i++) + if(l[i].type == 'checkbox') + l[i].onclick = f; +} + +// search tabs +if(byId('searchtabs')) { + var f = function() { + var str = byId('q').value; + if(str.length > 1) { + if(this.href.indexOf('/g') >= 0) + this.href += '/list'; + this.href += '?q=' + encodeURIComponent(str); + } + return true; + }; + var l = byName(byId('searchtabs'), 'a'); + for(var i=0; i<l.length; i++) + l[i].onclick = f; +} + // spam protection on all forms setTimeout(function() { for(i=1; i<document.forms.length; i++) diff --git a/data/style.css b/data/style.css index bd8c158a..50f50689 100644 --- a/data/style.css +++ b/data/style.css @@ -315,6 +315,7 @@ b.future, b.standout, a.standout { display: none; } #dd_box acronym { margin: 2px 5px 2px 0!important; } +#menulist .notifyget { display: inline-block; width: 125px; padding: 4px; background: $warnbg$; border: 1px solid $warnborder$; } @@ -457,16 +458,16 @@ fieldset.search { fieldset.search .submit { padding: 0 1px; } -p.searchtabs { +p#searchtabs { height: 12px; padding-right: 70px; } -p.searchtabs a { +p#searchtabs a { padding: 2px 6px 2px 6px; margin: 0 2px; color: $maintext$!important; } -p.searchtabs a:hover, p.searchtabs a.sel { +p#searchtabs a:hover, p#searchtabs a.sel { border: 1px solid $secborder$; border-bottom: none; padding: 1px 5px 2px 5px; @@ -480,7 +481,6 @@ div.mainbox.history td.tc1_1 { padding-right: 0; text-align: right; width: 60px; - *width: auto; /* IE7 sucks */ } div.mainbox.history td.tc1_2 { padding-left: 0; @@ -683,25 +683,22 @@ div#vntags { a.addnew { float: right; } -#screenshots td.scr { - padding-left: 30px; -} -#screenshots td.scr div { - float: left; - width: 142px; - height: 108px; - margin: 2px; - text-align: center; -} -#screenshots td.scr img { border: 3px solid transparent; } -#screenshots td.scr div.nsfw img { border: 3px solid $statnok$; } -#screenshots td.scr a:hover img { border: 3px solid $border$; } -#screenshots td.scr a { border: none; } -#screenshots #nsfwshown { font-style: normal } -#screenshots p.nsfwtoggle { - float: right; + +#screenshots p.rel { + background: url($_boxbg$) repeat; margin: 0; + padding: 2px; + font-weight: bold; + text-align: center; } +#screenshots a.scrlnk { margin: 2px; border: none } +#screenshots div.scr { display: block; padding-left: 30px; text-align: center } +#screenshots img { border: 3px solid transparent; } +#screenshots a.nsfw img { border: 3px solid $statnok$; } +#screenshots a:hover img { border: 3px solid $border$; } +#screenshots #nsfwshown { font-style: normal } +#screenshots p.nsfwtoggle { float: right; margin: 0; } + #dd_box { position: absolute; left: -500px; border: 1px solid $border$; background-color: $secbg$; } #dd_box ul { list-style-type: none; margin: 0; padding: 0 } @@ -713,6 +710,11 @@ 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,7 +756,7 @@ a.addnew { cursor: pointer; } #ds_box b { - padding: 2px 0 0 10px; + padding: 2px 0 0 10px; } #ds_box tr.selected { background: url($_boxbg$) repeat; @@ -942,6 +944,16 @@ div.scr_uploader { visibility: hidden; overflow: hidden; width: 1px; height: 1px .browse.rlist .relhid .tc4 { text-align: right } +/***** User notifications *****/ + +.browse.notifies td.tc1 { width: 14px } +.browse.notifies td.tc3 { width: 90px } +.browse.notifies td.tc4 { width: 60px } +.browse.notifies tbody td.tc5 { color: $grayedout$; cursor: pointer } +.browse.notifies td.tc5 i { font-style: normal; color: $maintext$ } +.browse.notifies .unread td { font-weight: bold } +.browse.notifies tfoot td { padding: 0 0 0 25px } + /***** Userpage *****/ @@ -961,7 +973,6 @@ div.browse.uposts td.tc1 { padding-right: 0; text-align: right; width: 60px; - *width: auto; /* IE7 sucks */ } .uposts td.tc2 { padding-left: 0; @@ -1183,8 +1194,6 @@ a .icons { cursor: pointer } .icons.nes { background-position: -32px -70px; } .icons.dos { background-position: -32px -84px; } -.icons.sk { background-position: -35px -99px; } - .icons.cs { background-position: -48px 0px; } .icons.da { background-position: -48px -11px; } .icons.de { background-position: -48px -22px; } @@ -1195,17 +1204,19 @@ a .icons { cursor: pointer } .icons.it { background-position: -48px -77px; } .icons.ja { background-position: -48px -88px; } .icons.hu { background-position: -48px -99px; } +.icons.pt-br { background-position: -48px -110px; } .icons.nl { background-position: -61px 0px; } .icons.no { background-position: -61px -11px; } .icons.pl { background-position: -61px -22px; } -.icons.pt { background-position: -61px -33px; } +.icons.pt-pt { background-position: -61px -33px; } .icons.ru { background-position: -61px -44px; } .icons.sv { background-position: -61px -55px; } .icons.tr { background-position: -61px -66px; } .icons.zh { background-position: -61px -77px; } .icons.ko { background-position: -61px -88px; } .icons.vi { background-position: -61px -99px; } +.icons.sk { background-position: -61px -110px; } /* Relation graph colors */ diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm index b6100dfa..be1191c6 100644 --- a/lib/Multi/API.pm +++ b/lib/Multi/API.pm @@ -108,7 +108,7 @@ sub filtertosql { # get the type that matches $t = (grep +( # wrong operator? don't even look further! - !$_->[2]{$op} ? 0 + !defined($_->[2]{$op}) ? 0 # undef : !defined($_->[0]) ? !defined($value) # int @@ -423,7 +423,7 @@ sub get_vn { for (grep !/^(basic|details|anime|relations)$/, @{$get->{info}}); my $select = 'v.id, v.latest'; - $select .= ', vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms' if grep /basic/, @{$get->{info}}; + $select .= ', vr.title, vr.original, v.c_released, v.c_languages::text[], v.c_platforms' if grep /basic/, @{$get->{info}}; $select .= ', vr.alias AS aliases, vr.length, vr.desc AS description, vr.l_wp, vr.l_encubed, vr.l_renai' if grep /details/, @{$get->{info}}; my @placeholders; @@ -446,11 +446,10 @@ sub get_vn { [ str => 'v.c_platforms :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ], [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_platforms LIKE :value:', \'like' ], [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_platforms NOT LIKE :value:', \'like' ], - ], [ 'languages', # rather similar to platforms - [ undef, "v.c_languages :op: ''", {qw|= = != <>|} ], - [ str => 'v.c_languages :op: :value:', {'=' => 'LIKE', '!=' => 'NOT LIKE'}, process => \'like' ], - [ stra => '(:value:)', {'=', 1}, join => ' OR ', serialize => 'v.c_languages LIKE :value:', process => \'like' ], - [ stra => '(:value:)', {'!=',1}, join => ' AND ', serialize => 'v.c_languages NOT LIKE :value:', process => \'like' ], + ], [ 'languages', + [ undef, "v.c_languages :op: '{}'", {qw|= = != <>|} ], + [ str => ':op: (v.c_languages && ARRAY[:value:]::language[])', {'=' => '', '!=' => 'NOT'} ], + [ stra => ':op: (v.c_languages && ARRAY[:value:]::language[])', {'=' => '', '!=' => 'NOT'}, join => ',' ], ], [ 'search', [ str => '(vr.title ILIKE :value: OR vr.alias ILIKE :value: OR v.id IN( SELECT rv.vid FROM releases r JOIN releases_rev rr ON rr.id = r.latest JOIN releases_vn rv ON rv.rid = rr.id @@ -484,7 +483,7 @@ sub get_vn_res { if(grep /basic/, @{$get->{info}}) { $_->{original} ||= undef; $_->{platforms} = [ split /\//, delete $_->{c_platforms} ]; - $_->{languages} = [ split /\//, delete $_->{c_languages} ]; + $_->{languages} = delete $_->{c_languages}; $_->{released} = formatdate delete $_->{c_released}; } if(grep /details/, @{$get->{info}}) { diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index 7d74ded7..0b19ed05 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -310,7 +310,7 @@ sub notify_result { # num, res $_[HEAP]{lastpost} = $r->{lastpost} if $r->{lastpost}; $_[HEAP]{lasttag} = $r->{lasttag} if $r->{lasttag}; return if !keys %{$_[HEAP]{notify}}; - $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], [ keys %{$_[HEAP]{notify}} ]); + $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], [ [ keys %{$_[HEAP]{notify}} ], 1 ]); } @@ -372,7 +372,7 @@ sub cmd_vn_results { # num, res, \@_ return $_[KERNEL]->yield(reply => $_[ARG2][DEST], sprintf( 'Too many results found, see %s/v/all?q=%s', $VNDB::S{url}, uri_escape_utf8($_[ARG2][ARG]) ), $_[ARG2][USER]) if $_[ARG0] > 5; - $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], $_[ARG2][DEST]); + $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], [$_[ARG2][DEST]]); } @@ -397,7 +397,7 @@ sub cmd_p_results { # num, res, \@_ return $_[KERNEL]->yield(reply => $_[ARG2][DEST], sprintf( 'Too many results found, see %s/p/all?q=%s', $VNDB::S{url}, uri_escape_utf8($_[ARG2][ARG]) ), $_[ARG2][USER]) if $_[ARG0] > 5; - $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], $_[ARG2][DEST]); + $_[KERNEL]->yield(formatid => $_[ARG0], $_[ARG1], [$_[ARG2][DEST]]); } @@ -521,7 +521,7 @@ sub vndbid { # dest, msg $t eq 't' ? 'title, '.GETBOARDS.' FROM threads t WHERE id = ?' : $t eq 'g' ? 'name AS title FROM tags WHERE id = ?' : 'rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = ?'), - [ $t, $id, $id ], 'formatid', $dest + [ $t, $id, $id ], 'formatid', [$dest] ) if !$rev && $t =~ /[vprtug]/; # edit/insert of vn/release/producer or discussion board post @@ -530,7 +530,7 @@ sub vndbid { # dest, msg $t eq 'r' ? 'rr.title, u.username, c.comments FROM changes c JOIN releases_rev rr ON c.id = rr.id JOIN users u ON u.id = c.requester WHERE rr.rid = ? AND c.rev = ?' : $t eq 'p' ? 'pr.name AS title, u.username, c.comments FROM changes c JOIN producers_rev pr ON c.id = pr.id JOIN users u ON u.id = c.requester WHERE pr.pid = ? AND c.rev = ?' : 't.title, u.username, '.GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE t.id = ? AND tp.num = ?'), - [ $t, $id, $rev, $id, $rev], 'formatid', $dest + [ $t, $id, $rev, $id, $rev], 'formatid', [$dest] ) if $rev && $t =~ /[vprt]/; # documentation page (need to parse the doc pages manually here) @@ -545,7 +545,7 @@ sub vndbid { # dest, msg } close $F; next if $rev && !$sub; - $_[KERNEL]->yield(formatid => 1, [{type => 'd', id => $id, title => $title, rev => $rev, section => $sub}], $dest); + $_[KERNEL]->yield(formatid => 1, [{type => 'd', id => $id, title => $title, rev => $rev, section => $sub}], [$dest]); } } } @@ -561,7 +561,9 @@ sub vndbid { # dest, msg # boards (optional) board titles the thread has been posted in # comments (optional) edit summary sub formatid { - my($num, $res, $dest) = @_[ARG0..$#_]; + my($num, $res, $arg) = @_[ARG0..$#_]; + my($dest, $notify) = @$arg; + my $c = $notify ? LIGHT_BLUE : RED; # only the types for which creation/edit announcements matter my %types = ( @@ -577,11 +579,11 @@ sub formatid { # (always) [x+.+] my @msg = ( - BOLD.RED.'['.NORMAL.BOLD.$id.RED.']'.NORMAL + BOLD.$c.'['.NORMAL.BOLD.$id.$c.']'.NORMAL ); # (only if username key is present) Edit of / New item / reply to / whatever - push @msg, RED.( + push @msg, $c.( ($_->{rev}||1) == 1 ? 'New '.$types{$_->{type}} : $_->{type} eq 't' ? 'Reply to' : 'Edit of' ).NORMAL if $_->{username}; @@ -590,22 +592,22 @@ sub formatid { push @msg, $_->{title}; # (only if boards key is present) Posted in [boards] - push @msg, RED.'Posted in'.NORMAL.' '.$_->{boards} if $_->{boards}; + push @msg, $c.'Posted in'.NORMAL.' '.$_->{boards} if $_->{boards}; # (only if username key is present) By [username] - push @msg, RED.'By'.NORMAL.' '.$_->{username} if $_->{username}; + push @msg, $c.'By'.NORMAL.' '.$_->{username} if $_->{username}; # (only if comments key is present) Summary: $_->{comments} =~ s/\n/ /g if $_->{comments}; - push @msg, RED.'Summary:'.NORMAL.' '.( + push @msg, $c.'Summary:'.NORMAL.' '.( length $_->{comments} > 40 ? substr($_->{comments}, 0, 37).'...' : $_->{comments} ) if defined $_->{comments}; # (for d+.+) -> section title - push @msg, RED.'->'.NORMAL.' '.$_->{section} if $_->{section}; + push @msg, $c.'->'.NORMAL.' '.$_->{section} if $_->{section}; # (always) @ URL - push @msg, RED.'@ '.NORMAL.LIGHT_GREY.$VNDB::S{url}.'/'.$id.NORMAL; + push @msg, $c.'@ '.NORMAL.LIGHT_GREY.$VNDB::S{url}.'/'.$id.NORMAL; # now post it $_[KERNEL]->yield(reply => $dest, join ' ', @msg); diff --git a/lib/Multi/Maintenance.pm b/lib/Multi/Maintenance.pm index 864239dd..be267aba 100644 --- a/lib/Multi/Maintenance.pm +++ b/lib/Multi/Maintenance.pm @@ -17,12 +17,12 @@ sub spawn { package_states => [ $p => [qw| _start shutdown set_daily daily set_monthly monthly log_stats - vncache_inc tagcache vnpopularity vnrating cleangraphs + vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions vncache_full usercache statscache logrotate |], ], heap => { - daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs|], + daily => [qw|vncache_inc tagcache vnpopularity vnrating cleangraphs cleansessions|], monthly => [qw|vncache_full usercache statscache logrotate|], @_, }, @@ -151,6 +151,13 @@ sub cleangraphs { } +sub cleansessions { + $_[KERNEL]->post(pg => do => + q|DELETE FROM sessions WHERE lastused < NOW()-'1 month'::interval|, + undef, 'log_stats', 'cleansessions'); +} + + # # M O N T H L Y J O B S # diff --git a/lib/Multi/RG.pm b/lib/Multi/RG.pm index 76408d92..d72bb516 100644 --- a/lib/Multi/RG.pm +++ b/lib/Multi/RG.pm @@ -97,7 +97,7 @@ sub getrel { # num, res, id if(!grep !$_, values %{$_[HEAP]{nodes}}) { my $ids = join(', ', map '?', keys %{$_[HEAP]{nodes}}); $_[KERNEL]->post(pg => query => $_[HEAP]{type} eq 'v' - ? "SELECT v.id, vr.title, v.c_released AS date, v.c_languages AS lang FROM vn v JOIN vn_rev vr ON vr.id = v.latest WHERE v.id IN($ids) ORDER BY v.c_released" + ? "SELECT v.id, vr.title, v.c_released AS date, v.c_languages::text[] AS lang FROM vn v JOIN vn_rev vr ON vr.id = v.latest WHERE v.id IN($ids) ORDER BY v.c_released" : "SELECT p.id, pr.name, pr.lang, pr.type FROM producers p JOIN producers_rev pr ON pr.id = p.latest WHERE p.id IN($ids) ORDER BY pr.name", [ keys %{$_[HEAP]{nodes}} ], 'builddot'); } @@ -252,7 +252,7 @@ sub _vnnode { q|<TR><TD COLSPAN="2" ALIGN="CENTER" CELLPADDING="2"><FONT POINT-SIZE="%d"> %s </FONT></TD></TR>|. q|<TR><TD> %s </TD><TD> %s </TD></TR>|. qq|</TABLE>> ]\n|, - $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($title), $date, $n->{lang}||'N/A'; + $_->{id}, encode_utf8($tooltip), $heap->{fsize}[2], encode_utf8($title), $date, join('/', @{$n->{lang}})||'N/A'; } diff --git a/lib/SkinFile.pm b/lib/SkinFile.pm new file mode 100644 index 00000000..78608f89 --- /dev/null +++ b/lib/SkinFile.pm @@ -0,0 +1,74 @@ + +package SkinFile; + +use strict; +use warnings; +use Fcntl 'LOCK_SH', 'SEEK_SET'; + + +sub new { + my($class, $root, $open) = @_; + my $self = bless { root => $root }, $class; + $self->open($open) if $open; + return $self; +} + + +sub list { + return map /\/([^\/]+)\/conf/?$1:(), glob "$_[0]{root}/*/conf"; +} + + +sub open { + my($self, $dir, $force) = @_; + return if $self->{"s_$dir"} && !$force; + my %o; + open my $F, '<:utf8', "$self->{root}/$dir/conf" or die $!; + flock $F, LOCK_SH or die $!; + seek $F, 0, SEEK_SET or die $!; + local $_; + while(<$F>) { + chomp; + s/\r//g; + s{[\t\s]*//.+$}{}; + next if !/^([a-z0-9]+)[\t\s]+(.+)$/; + $o{$1} = $2; + } + close $F; + $self->{"s_$dir"} = \%o; + $self->{opened} = $dir; +} + + +sub get { + my($self, $dir, $var) = @_; + $self->open($dir) if defined $var; + $var = $dir if !defined $var; + $var ? $self->{"s_$self->{opened}"}{$var} : keys %{$self->{"s_$self->{opened}"}}; +} + + +1; + + +__END__ + +=pod + +=head1 NAME + +SkinFile - Simple object oriented interface to parsing skin configuration files + +=head1 USAGE + + use SkinFile; + my $s = SkinFile->new($dir); + my @skins = $s->list; + + $s->open($skins[0]); + my $name = $s->get('name'); + + # same as above, but in one function + my $name = $s->get($skins[0], 'name'); + + diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm index 1c27c22e..9da25384 100644 --- a/lib/VNDB/DB/Discussions.pm +++ b/lib/VNDB/DB/Discussions.pm @@ -5,7 +5,7 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount dbPostRead|; +our @EXPORT = qw|dbThreadGet dbThreadEdit dbThreadAdd dbPostGet dbPostEdit dbPostAdd dbThreadCount|; # Options: id, type, iid, results, page, what, notusers, sort, reverse @@ -252,15 +252,5 @@ sub dbPostAdd { } -sub dbPostRead { # thread id, user id, last post number - my($s, $tid, $uid, $num) = @_; - $s->dbExec(q| - UPDATE threads_boards - SET lastread = ? - WHERE tid = ? AND type = 'u' AND iid = ?|, - $num, $tid, $uid); -} - - 1; diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 02df0a2b..e3ef69b1 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -6,7 +6,7 @@ use warnings; use Exporter 'import'; our @EXPORT = qw| - dbStats dbItemEdit dbRevisionGet dbItemMod dbRandomQuote + dbStats dbItemEdit dbRevisionGet dbRandomQuote |; @@ -21,7 +21,7 @@ sub dbStats { # Inserts a new revision into the database -# Arguments: type [vrp], revision id, %options->{ editsum uid + db[item]RevisionInsert } +# Arguments: type [vrp], revision id, %options->{ editsum uid ihid ilock + db[item]RevisionInsert } # revision id = changes.id of the revision this edit is based on, undef to create a new DB item # Returns: { iid, cid, rev } sub dbItemEdit { @@ -29,8 +29,13 @@ sub dbItemEdit { my $fun = {qw|v vn r release p producer|}->{$type}; $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid); - $self->dbExec('UPDATE edit_revision SET requester = ?, ip = ?, comments = ?', - $o{uid}||$self->authInfo->{id}, $self->reqIP, $o{editsum}); + $self->dbExec('UPDATE edit_revision !H', { + 'requester = ?' => $o{uid}||$self->authInfo->{id}, + 'ip = ?' => $self->reqIP, + 'comments = ?' => $o{editsum}, + exists($o{ihid}) ? ('ihid = ?' => $o{ihid} ?1:0) : (), + exists($o{ilock}) ? ('ilock = ?' => $o{ilock}?1:0) : (), + }); $self->dbVNRevisionInsert( \%o) if $type eq 'v'; $self->dbProducerRevisionInsert(\%o) if $type eq 'p'; @@ -112,17 +117,6 @@ sub dbRevisionGet { } -# Lock or hide a DB item -# arguments: v/r/p, id, %options ->( hidden, locked ) -sub dbItemMod { - my($self, $type, $id, %o) = @_; - $self->dbExec('UPDATE !s !H WHERE id = ?', - {qw|v vn r releases p producers|}->{$type}, - { map { ($_.' = ?', int $o{$_}) } keys %o }, $id - ); -} - - # Returns a random quote (hashref with keys = vid, quote) sub dbRandomQuote { return $_[0]->dbRow(q| diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index f32f70db..5ec387cf 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -44,7 +44,7 @@ sub dbProducerGet { my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph'; $select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/; - $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev| if $o{what} =~ /changes/; + $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/; $select .= ', pg.svg' if $o{what} =~ /relgraph/; my($r, $np) = $self->dbPage(\%o, q| diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm index a2f62a63..cb8ad006 100644 --- a/lib/VNDB/DB/Releases.pm +++ b/lib/VNDB/DB/Releases.pm @@ -74,7 +74,7 @@ sub dbReleaseGet { 'rr.id AS cid', $o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin rr.resolution rr.voiced rr.freeware rr.doujin rr.ani_story rr.ani_ero r.hidden r.locked| : (), $o{what} =~ /changes/ ? - (qw|c.requester c.comments r.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (), + (qw|c.requester c.comments r.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (), ); my $order = sprintf { diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 28c4d572..19831593 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -65,7 +65,7 @@ sub dbVNListList { # execute query my($r, $np) = $self->dbPage(\%o, qq| - SELECT vr.vid, vr.title, vr.original, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote + SELECT vr.vid, vr.title, vr.original, COALESCE(vo.vote, 0) AS vote FROM vn v JOIN vn_rev vr ON vr.id = v.latest !s JOIN votes vo ON vo.vid = v.id AND vo.uid = ? diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm index f8fdfe3f..41f9f25a 100644 --- a/lib/VNDB/DB/Users.pm +++ b/lib/VNDB/DB/Users.pm @@ -5,11 +5,15 @@ use strict; use warnings; use Exporter 'import'; -our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|; +our @EXPORT = qw| + dbUserGet dbUserEdit dbUserAdd dbUserDel + dbSessionAdd dbSessionDel dbSessionUpdateLastUsed + dbNotifyGet dbNotifyMarkRead dbNotifyRemove +|; # %options->{ username passwd mail session uid ip registered search results page what sort reverse } -# what: stats extended +# what: notifycount stats extended # sort: username registered votes changes tags sub dbUserGet { my $s = shift; @@ -30,8 +34,10 @@ sub dbUserGet { 'ASCII(username) < 97 OR ASCII(username) > 122' => 1 ) : (), $o{mail} ? ( 'mail = ?' => $o{mail} ) : (), - $o{uid} ? ( + $o{uid} && !ref($o{uid}) ? ( 'id = ?' => $o{uid} ) : (), + $o{uid} && ref($o{uid}) ? ( + 'id IN(!l)' => [ $o{uid} ]) : (), !$o{uid} && !$o{username} ? ( 'id > 0' => 1 ) : (), $o{ip} ? ( @@ -48,9 +54,11 @@ sub dbUserGet { qw|id username c_votes c_changes show_list c_tags|, q|extract('epoch' from registered) as registered|, $o{what} =~ /extended/ ? ( - qw|mail rank salt skin customcss show_nsfw ign_votes|, + qw|mail rank salt skin customcss show_nsfw ign_votes notify_dbedit notify_announce|, q|encode(passwd, 'hex') AS passwd| ) : (), + $o{what} =~ /notifycount/ ? + '(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (), $o{what} =~ /stats/ ? ( '(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount', '(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount', @@ -59,6 +67,7 @@ sub dbUserGet { '(SELECT COUNT(DISTINCT tag) FROM tags_vn WHERE uid = u.id) AS tagcount', '(SELECT COUNT(DISTINCT vid) FROM tags_vn WHERE uid = u.id) AS tagvncount', ) : (), + $o{session} ? q|extract('epoch' from s.lastused) as session_lastused| : (), ); my @join = ( @@ -91,7 +100,7 @@ sub dbUserEdit { my %h; defined $o{$_} && ($h{$_.' = ?'} = $o{$_}) - for (qw| username mail rank show_nsfw show_list skin customcss salt ign_votes |); + for (qw| username mail rank show_nsfw show_list skin customcss salt ign_votes notify_dbedit notify_announce |); $h{'passwd = decode(?, \'hex\')'} = $o{passwd} if defined $o{passwd}; @@ -127,27 +136,10 @@ sub dbUserDel { } -# Returns number of unread messages -sub dbUserMessageCount { # uid - my($s, $uid) = @_; - return $s->dbRow(q{ - SELECT SUM(tbi.count) AS cnt FROM ( - SELECT t.count-COALESCE(tb.lastread,0) - FROM threads_boards tb - JOIN threads t ON t.id = tb.tid AND NOT t.hidden - WHERE tb.type = 'u' AND tb.iid = ? - ) AS tbi (count) - }, $uid)->{cnt}||0; -} - - # Adds a session to the database -# If no expiration is supplied the database default is used -# uid, 40 character session token, expiration time (timestamp) +# uid, 40 character session token sub dbSessionAdd { - my($s, @o) = @_; - $s->dbExec(q|INSERT INTO sessions (uid, token, expiration) VALUES(?, decode(?, 'hex'), to_timestamp(?))|, - @o[0,1], $o[2]||(time+31536000)); + $_[0]->dbExec(q|INSERT INTO sessions (uid, token) VALUES(?, decode(?, 'hex'))|, @_[1,2]); } @@ -162,5 +154,63 @@ sub dbSessionDel { } +# uid, token +sub dbSessionUpdateLastUsed { + $_[0]->dbExec(q|UPDATE sessions SET lastused = NOW() WHERE uid = ? AND token = decode(?, 'hex')|, $_[1], $_[2]); +} + + +# %options->{ uid id what results page } +# what: titles +sub dbNotifyGet { + my($s, %o) = @_; + $o{what} ||= ''; + $o{results} ||= 10; + $o{page} ||= 1; + + my %where = ( + 'n.uid = ?' => $o{uid}, + $o{id} ? ( + 'n.id = ?' => $o{id} ) : (), + defined($o{read}) ? ( + 'n.read !s' => $o{read} ? 'IS NOT NULL' : 'IS NULL' ) : (), + ); + + my @join = ( + $o{what} =~ /titles/ ? 'LEFT JOIN users u ON n.c_byuser = u.id' : (), + ); + + my @select = ( + qw|n.id n.ntype n.ltype n.iid n.subid|, + q|extract('epoch' from n.date) as date|, + q|extract('epoch' from n.read) as read|, + $o{what} =~ /titles/ ? qw|u.username n.c_title| : (), + ); + + my($r, $np) = $s->dbPage(\%o, q| + SELECT !s + FROM notifications n + !s + !W + ORDER BY n.id + |, join(', ', @select), join(' ', @join), \%where); + return wantarray ? ($r, $np) : $r; +} + + +# ids +sub dbNotifyMarkRead { + my $s = shift; + $s->dbExec('UPDATE notifications SET read = NOW() WHERE id IN(!l)', \@_); +} + + +# ids +sub dbNotifyRemove { + my $s = shift; + $s->dbExec('DELETE FROM notifications WHERE id IN(!l)', \@_); +} + + 1; diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 6ff74d1f..cb1a878c 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -29,7 +29,7 @@ sub dbVNGet { 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}} ? ( - '('.join(' OR ', map "v.c_languages ILIKE '%%$_%%'", @{$o{lang}}).')' => 1 ) : (), + 'v.c_languages && ARRAY[!l]::language[]' => [ $o{lang} ]) : (), $o{platform} && @{$o{platform}} ? ( '('.join(' OR ', map "v.c_platforms ILIKE '%%$_%%'", @{$o{platform}}).')' => 1 ) : (), $o{tags_include} && @{$o{tags_include}} ? ( @@ -81,12 +81,12 @@ sub dbVNGet { ); my $tag_ids = $o{tags_include} && join ',', @{$o{tags_include}[1]}; - my @select = ( - qw|v.id v.locked v.hidden v.c_released v.c_languages v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid', + my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages + qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms vr.title vr.original v.rgraph|, 'vr.id AS cid', $o{what} =~ /extended/ ? ( qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai vr.l_vnn| ) : (), $o{what} =~ /changes/ ? ( - qw|c.requester c.comments v.latest u.username c.rev|, q|extract('epoch' from c.added) as added|) : (), + qw|c.requester c.comments v.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (), $o{what} =~ /relgraph/ ? 'vg.svg' : (), $o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (), $o{what} =~ /ranking/ ? ( diff --git a/lib/VNDB/Handler/Discussions.pm b/lib/VNDB/Handler/Discussions.pm index fc477de2..c8431c2c 100644 --- a/lib/VNDB/Handler/Discussions.pm +++ b/lib/VNDB/Handler/Discussions.pm @@ -29,13 +29,7 @@ sub thread { my $p = $self->dbPostGet(tid => $tid, results => 25, page => $page, what => 'user'); return 404 if !$p->[0]; - # mark as read when this thread is posted in the board of the currently logged in user - my $uid = $self->authInfo->{id}; - $self->dbPostRead($t->{id}, $uid, $p->[$#$p]{num}) - if $uid && grep $_->{type} eq 'u' && $_->{iid} == $uid, @{$t->{boards}}; - $self->htmlHeader(title => $t->{title}); - div class => 'mainbox'; h1 $t->{title}; h2 mt '_thread_postedin'; diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 625b0463..de4bdd67 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -13,8 +13,8 @@ YAWF::register( qr{}, \&homepage, qr{(?:([upvr])([1-9]\d*)/)?hist}, \&history, qr{d([1-9]\d*)}, \&docpage, + qr{setlang}, \&setlang, qr{nospam}, \&nospam, - qr{([vrp])([1-9]\d*)/(lock|hide)}, \&itemmod, qr{we-dont-like-ie6}, \&ie6message, qr{opensearch\.xml}, \&opensearch, @@ -63,7 +63,7 @@ sub homepage { h1; a href => '/hist', mt '_home_recentchanges'; end; - my $changes = $self->dbRevisionGet(what => 'item user', results => 10, auto => 1, hidden => 1); + my $changes = $self->dbRevisionGet(what => 'item user', results => 10, auto => 1); ul; for (@$changes) { li; @@ -185,7 +185,7 @@ sub history { my $f = $self->formValidate( { name => 'p', required => 0, default => 1, template => 'int' }, { name => 'm', required => 0, default => !$type, enum => [ 0, 1 ] }, - { name => 'h', required => 0, default => 1, enum => [ -1..1 ] }, + { name => 'h', required => 0, default => 0, enum => [ -1..1 ] }, { name => 't', required => 0, default => '', enum => [ 'v', 'r', 'p' ] }, { name => 'e', required => 0, default => 0, enum => [ -1..1 ] }, { name => 'r', required => 0, default => 0, enum => [ 0, 1 ] }, @@ -289,24 +289,33 @@ sub docpage { $sec++; $subsec = 0; qq|<h3><a href="#$sec" name="$sec">$sec. $1</a></h3>\n| - }eg; + }e; s{^:SUBSUB:(.+)\r?\n$}{ $subsec++; qq|<h4><a href="#$sec.$subsec" name="$sec.$subsec">$sec.$subsec. $1</a></h4>\n| - }eg; + }e; s{^:INC:(.+)\r?\n$}{ $f = sprintf('%s/data/docs/%s', $VNDB::ROOT, $1); open($F, '<:utf8', $f.$l) or open($F, '<:utf8', $f) or die $!; my $ii = join('', <$F>); close $F; $ii; - }eg; + }e; s{^:TOP5CONTRIB:$}{ my $l = $self->dbUserGet(results => 6, sort => 'changes', reverse => 1); '<dl>'.join('', map $_->{id} == 1 ? () : sprintf('<dt><a href="/u%d">%s</a></dt><dd>%d</dd>', $_->{id}, $_->{username}, $_->{c_changes}), @$l).'</dl>'; - }eg; + }e; + s{^:SKINCONTRIB:$}{ + my %users; + push @{$users{ $self->{skins}{$_}[1] }}, [ $_, $self->{skins}{$_}[0] ] + for sort { $self->{skins}{$a}[0] cmp $self->{skins}{$b}[0] } keys %{$self->{skins}}; + my $u = $self->dbUserGet(uid => [ keys %users ]); + '<dl>'.join('', map sprintf('<dt><a href="/u%d">%s</a></dt><dd>%s</dd>', + $_->{id}, $_->{username}, join(', ', map sprintf('<a href="?skin=%s">%s</a>', $_->[0], $_->[1]), @{$users{$_->{id}}}) + ), @$u).'</dl>'; + }e; } $self->htmlHeader(title => $title); @@ -320,6 +329,20 @@ sub docpage { } +sub setlang { + my $self = shift; + + my $lang = $self->formValidate({name => 'lang', required => 1, enum => [ VNDB::L10N::languages ]}); + return 404 if $lang->{_err}; + $lang = $lang->{lang}; + + (my $ref = $self->reqHeader('Referer')||'/') =~ s/^\Q$self->{url}//; + $self->resRedirect($ref, 'post'); + $self->resHeader('Set-Cookie', "l10n=$lang; expires=Sat, 01-Jan-2030 00:00:00 GMT; path=/; domain=$self->{cookie_domain}") + if $lang ne $self->{l10n}->language_tag(); +} + + sub nospam { my $self = shift; $self->htmlHeader(title => mt '_nospam_title', noindex => 1); @@ -336,21 +359,6 @@ sub nospam { } -# /hide and /lock for v/r/p+ pages -sub itemmod { - my($self, $type, $iid, $act) = @_; - return $self->htmlDenied if !$self->authCan($act eq 'hide' ? 'del' : 'lock'); - - my $obj = $type eq 'v' ? $self->dbVNGet(id => $iid)->[0] : - $type eq 'r' ? $self->dbReleaseGet(id => $iid, what => 'extended')->[0] : - $self->dbProducerGet(id => $iid, what => 'extended')->[0]; - return 404 if !$obj->{id}; - - $self->dbItemMod($type, $iid, $act eq 'hide' ? (hidden => !$obj->{hidden}) : (locked => !$obj->{locked})); - $self->resRedirect("/$type$iid", 'temp'); -} - - sub ie6message { my $self = shift; diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm index 064a06bc..b1203dd7 100644 --- a/lib/VNDB/Handler/Producers.pm +++ b/lib/VNDB/Handler/Producers.pm @@ -155,7 +155,7 @@ sub edit { || $pid && ($p->{locked} && !$self->authCan('lock') || $p->{hidden} && !$self->authCan('del')); my %b4 = !$pid ? () : ( - (map { $_ => $p->{$_} } qw|type name original lang website desc alias|), + (map { $_ => $p->{$_} } qw|type name original lang website desc alias ihid ilock|), l_wp => $p->{l_wp} || '', prodrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{name}, sort { $a->{id} <=> $b->{id} } @{$p->{relations}}), ); @@ -173,12 +173,17 @@ sub edit { { name => 'desc', required => 0, maxlength => 5000, default => '' }, { name => 'prodrelations', required => 0, maxlength => 5000, default => '' }, { name => 'editsum', maxlength => 5000 }, + { name => 'ihid', required => 0 }, + { name => 'ilock', required => 0 }, ); if(!$frm->{_err}) { # parse my $relations = [ map { /^([a-z]+),([0-9]+),(.+)$/ && (!$pid || $2 != $pid) ? [ $1, $2, $3 ] : () } split /\|\|\|/, $frm->{prodrelations} ]; # normalize + $frm->{ihid} = $frm->{ihid}?1:0; + $frm->{ilock} = $frm->{ilock}?1:0; + $relations = [] if $frm->{ihid}; $frm->{prodrelations} = join '|||', map $_->[0].','.$_->[1].','.$_->[2], sort { $a->[1] <=> $b->[1]} @{$relations}; return $self->resRedirect("/p$pid", 'post') diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm index 498b9fa9..55bc9778 100644 --- a/lib/VNDB/Handler/Releases.pm +++ b/lib/VNDB/Handler/Releases.pm @@ -287,7 +287,7 @@ sub edit { my $vn = $rid ? $r->{vn} : [{ vid => $vid, title => $v->{title} }]; my %b4 = !$rid ? () : ( (map { $_ => $r->{$_} } qw|type title original gtin catalog languages website released - notes platforms patch resolution voiced freeware doujin ani_story ani_ero|), + notes platforms patch resolution voiced freeware doujin ani_story ani_ero ihid ilock|), minage => defined($r->{minage}) ? $r->{minage} : -1, media => join(',', sort map "$_->{medium} $_->{qty}", @{$r->{media}}), producers => join('|||', map @@ -323,8 +323,12 @@ sub edit { { name => 'producers', required => 0, default => '' }, { name => 'vn', maxlength => 5000 }, { name => 'editsum', maxlength => 5000 }, + { name => 'ihid', required => 0 }, + { name => 'ilock', required => 0 }, ); + push @{$frm->{_err}}, [ 'released', 'required', 1 ] if !$frm->{released}; + my($media, $producers, $new_vn); if(!$frm->{_err}) { # de-serialize @@ -332,7 +336,7 @@ sub edit { $producers = [ map { /^([0-9]+),([1-3])/ ? [ $1, $2&1?1:0, $2&2?1:0] : () } split /\|\|\|/, $frm->{producers} ]; $new_vn = [ map { /^([0-9]+)/ ? $1 : () } split /\|\|\|/, $frm->{vn} ]; $frm->{platforms} = [ grep $_, @{$frm->{platforms}} ]; - $frm->{$_} = $frm->{$_} ? 1 : 0 for (qw|patch freeware doujin|); + $frm->{$_} = $frm->{$_} ? 1 : 0 for (qw|patch freeware doujin ihid ilock|); # reset some fields when the patch flag is set $frm->{doujin} = $frm->{resolution} = $frm->{voiced} = $frm->{ani_story} = $frm->{ani_ero} = 0 if $frm->{patch}; @@ -350,7 +354,7 @@ sub edit { if(!$frm->{_err}) { my $nrev = $self->dbItemEdit(r => !$copy && $rid ? $r->{cid} : undef, (map { $_ => $frm->{$_} } qw| type title original gtin catalog languages website released - notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero|), + notes platforms resolution editsum patch voiced freeware doujin ani_story ani_ero ihid ilock|), minage => $frm->{minage} < 0 ? undef : $frm->{minage}, vn => $new_vn, producers => $producers, diff --git a/lib/VNDB/Handler/Tags.pm b/lib/VNDB/Handler/Tags.pm index 612b603d..aa46f5b9 100644 --- a/lib/VNDB/Handler/Tags.pm +++ b/lib/VNDB/Handler/Tags.pm @@ -191,7 +191,7 @@ sub tagedit { { name => 'meta', required => 0, default => 0 }, { name => 'alias', required => 0, maxlength => 1024, default => '', regex => [ qr/^[^,]+$/s, 'No comma allowed in aliases' ] }, { name => 'description', required => 0, maxlength => 1024, default => '' }, - { name => 'parents', required => 0, default => '' }, + { name => 'parents', required => !$self->authCan('tagmod'), default => '' }, { name => 'merge', required => 0, default => '' }, ); my @aliases = split /[\t\s]*\n[\t\s]*/, $frm->{alias}; diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 2cec8b52..f68f9db9 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -3,7 +3,7 @@ package VNDB::Handler::Users; use strict; use warnings; -use YAWF ':html'; +use YAWF ':html', 'xml_escape'; use VNDB::Func; @@ -18,6 +18,8 @@ YAWF::register( qr{u([1-9]\d*)/posts} => \&posts, qr{u([1-9]\d*)/del(/[od])?} => \&delete, qr{u/(all|[0a-z])} => \&list, + qr{u([1-9]\d*)/notifies} => \¬ifies, + qr{u([1-9]\d*)/notify/([1-9]\d*)} => \&readnotify, ); @@ -113,7 +115,7 @@ sub userpage { } if($u->{c_changes}) { - my $list = $self->dbRevisionGet(what => 'item user', uid => $uid, results => 5, hidden => 1); + my $list = $self->dbRevisionGet(what => 'item user', uid => $uid, results => 5); h1 class => 'boxtitle'; a href => "/u$uid/hist", mt '_userpage_changes'; end; @@ -346,7 +348,7 @@ sub edit { [ check => short => 'flags_list', name => mt '_usere_flist', "/u$uid/list", "/u$uid/wish" ], [ check => short => 'flags_nsfw', name => mt '_usere_fnsfw' ], [ select => short => 'skin', name => mt('_usere_skin'), width => 300, options => [ - map [ $_ eq $self->{skin_default} ? '' : $_, $self->{skins}{$_}.($self->debug?" [$_]":'') ], sort { $self->{skins}{$a} cmp $self->{skins}{$b} } keys %{$self->{skins}} ] ], + map [ $_ eq $self->{skin_default} ? '' : $_, $self->{skins}{$_}[0].($self->debug?" [$_]":'') ], sort { $self->{skins}{$a}[0] cmp $self->{skins}{$b}[0] } keys %{$self->{skins}} ] ], [ textarea => short => 'customcss', name => mt '_usere_css' ], ]); $self->htmlFooter; @@ -519,5 +521,140 @@ sub list { } +sub notifies { + my($self, $uid) = @_; + return $self->htmlDenied if !$self->authInfo->{id} || $uid != $self->authInfo->{id}; + + my $u = $self->dbUserGet(uid => $uid)->[0]; + + my $f = $self->formValidate( + { name => 'p', required => 0, default => 1, template => 'int' }, + { name => 'r', required => 0, default => 0, enum => [0,1] }, + ); + return 404 if $f->{_err}; + + # changing the notification settings + my $saved; + if($self->reqMethod() eq 'POST' && $self->reqParam('set')) { + my $frm = $self->formValidate( + { name => 'notify_dbedit', required => 0 }, + { name => 'notify_announce', required => 0 } + ); + return 404 if $frm->{_err}; + for ('notify_dbedit', 'notify_announce') { + $frm->{$_} = $frm->{$_} ? 1 : 0; + $self->authInfo->{$_} = $frm->{$_}; + } + $self->dbUserEdit($uid, %$frm); + $saved = 1; + + # updating notifications + } elsif($self->reqMethod() eq 'POST') { + my $frm = $self->formValidate( + { name => 'notifysel', multi => 1, required => 0, template => 'int' }, + { name => 'markread', required => 0 }, + { name => 'remove', required => 0 } + ); + return 404 if $frm->{_err}; + my @ids = grep $_, @{$frm->{notifysel}}; + $self->dbNotifyMarkRead(@ids) if @ids && $frm->{markread}; + $self->dbNotifyRemove(@ids) if @ids && $frm->{remove}; + } + + my($list, $np) = $self->dbNotifyGet( + uid => $uid, + page => $f->{p}, + results => 25, + what => 'titles', + read => $f->{r} == 1 ? undef : 0, + ); + + $self->htmlHeader(title => mt('_usern_title'), noindex => 1); + $self->htmlMainTabs(u => $u); + div class => 'mainbox'; + h1 mt '_usern_title'; + p class => 'browseopts'; + a !$f->{r} ? (class => 'optselected') : (), href => "/u$uid/notifies?r=0", mt '_usern_o_unread'; + a $f->{r} ? (class => 'optselected') : (), href => "/u$uid/notifies?r=1", mt '_usern_o_alsoread'; + end; + p mt '_usern_nonotifies' if !@$list; + end; + + if(@$list) { + form action => "/u$uid/notifies?r=$f->{r}", method => 'post'; + $self->htmlBrowse( + items => $list, + options => $f, + nextpage => $np, + class => 'notifies', + pageurl => "/u$uid/notifies?r=$f->{r}", + header => [ + [ '<input type="checkbox" class="checkall" name="notifysel" value="0" />' ], + [ mt '_usern_col_type' ], + [ mt '_usern_col_age' ], + [ mt '_usern_col_id' ], + [ mt '_usern_col_act' ], + ], + row => sub { + my($s, $n, $l) = @_; + Tr class => join ' ', $n%2?'odd':'', $l->{read}?'':'unread'; + td class => 'tc1'; + input type => 'checkbox', name => 'notifysel', value => "$l->{id}"; + end; + td class => 'tc2', mt "_usern_type_$l->{ntype}"; + td class => 'tc3', $self->{l10n}->age($l->{date}); + td class => 'tc4'; + a href => "/u$uid/notify/$l->{id}", "$l->{ltype}$l->{iid}".($l->{subid}?".$l->{subid}":''); + end; + td class => 'tc5', onclick => qq|javascript:location.href="/u$uid/notify/$l->{id}"|; + lit mt '_usern_n_'.( + $l->{ltype} eq 't' ? ($l->{subid} == 1 ? 't_new' : 't_reply') + : 'item_edit'), + sprintf('<i>%s</i>', xml_escape $l->{c_title}), sprintf('<i>%s</i>', xml_escape $l->{username}); + end; + end; + }, + footer => sub { + Tr; + td colspan => 5; + input type => 'submit', name => 'markread', value => mt '_usern_but_markread'; + input type => 'submit', name => 'remove', value => mt '_usern_but_remove'; + end; + end; + } + ); + end; + } + + form method => 'post', action => "/u$uid/notifies"; + div class => 'mainbox'; + h1 mt '_usern_set_title'; + div class => 'notice', mt '_usern_set_saved' if $saved; + p; + for('dbedit', 'announce') { + input type => 'checkbox', name => "notify_$_", id => "notify_$_", value => 1, + $self->authInfo->{"notify_$_"} ? (checked => 'checked') : (); + label for => "notify_$_", ' '.mt("_usern_set_$_"); + br; + } + input type => 'submit', name => 'set', value => mt '_usern_set_submit'; + end; + end; + end; + $self->htmlFooter; +} + + +sub readnotify { + my($self, $uid, $nid) = @_; + return $self->htmlDenied if !$self->authInfo->{id} || $uid != $self->authInfo->{id}; + my $n = $self->dbNotifyGet(uid => $uid, id => $nid)->[0]; + return 404 if !$n->{iid}; + $self->dbNotifyMarkRead($n->{id}) if !$n->{read}; + # NOTE: for t+.+ IDs, this will create a double redirect, which is rather awkward... + $self->resRedirect("/$n->{ltype}$n->{iid}".($n->{subid}?".$n->{subid}":''), 'perm'); +} + + 1; diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index d5dfd878..60b24272 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -26,7 +26,7 @@ sub edit { || $vid && ($v->{locked} && !$self->authCan('lock') || $v->{hidden} && !$self->authCan('del')); my %b4 = !$vid ? () : ( - (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai l_vnn img_nsfw|), + (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai l_vnn img_nsfw ihid ilock|), anime => join(' ', sort { $a <=> $b } map $_->{id}, @{$v->{anime}}), vnrelations => join('|||', map $_->{relation}.','.$_->{id}.','.$_->{title}, sort { $a->{id} <=> $b->{id} } @{$v->{relations}}), screenshots => join(' ', map sprintf('%d,%d,%d', $_->{id}, $_->{nsfw}?1:0, $_->{rid}), @{$v->{screenshots}}), @@ -49,6 +49,8 @@ sub edit { { name => 'vnrelations', required => 0, default => '', maxlength => 5000 }, { name => 'screenshots', required => 0, default => '', maxlength => 1000 }, { name => 'editsum', maxlength => 5000 }, + { name => 'ihid', required => 0 }, + { name => 'ilock', required => 0 }, ); # handle image upload @@ -60,6 +62,9 @@ sub edit { my $relations = [ map { /^([a-z]+),([0-9]+),(.+)$/ && (!$vid || $2 != $vid) ? [ $1, $2, $3 ] : () } split /\|\|\|/, $frm->{vnrelations} ]; my $screenshots = [ map /^[0-9]+,[01],[0-9]+$/ ? [split /,/] : (), split / +/, $frm->{screenshots} ]; + $frm->{ihid} = $frm->{ihid}?1:0; + $frm->{ilock} = $frm->{ilock}?1:0; + $relations = [] if $frm->{ihid}; $frm->{anime} = join ' ', sort { $a <=> $b } keys %$anime; $frm->{vnrelations} = join '|||', map $_->[0].','.$_->[1].','.$_->[2], sort { $a->[1] <=> $b->[1]} @{$relations}; $frm->{img_nsfw} = $frm->{img_nsfw} ? 1 : 0; @@ -71,7 +76,7 @@ sub edit { # perform the edit/add my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef, - (map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw|), + (map { $_ => $frm->{$_} } qw|title original alias desc length l_wp l_encubed l_renai l_vnn editsum img_nsfw ihid ilock|), anime => [ keys %$anime ], relations => $relations, image => $image, diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 6c2e9b79..21655e37 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -455,30 +455,24 @@ sub _screenshots { } h1 mt '_vnpage_scr'; - table; - for my $rel (@$r) { - my @scr = grep $_->{rid} && $rel->{id} == $_->{rid}, @{$v->{screenshots}}; - next if !@scr; - Tr class => 'rel'; - td colspan => 5; - cssicon "lang $_", mt "_lang_$_" for (@{$rel->{languages}}); - txt $rel->{title}; - end; - end; - Tr; - td class => 'scr'; - for (@scr) { - div $_->{nsfw} ? (class => 'nsfw'.(!$self->authInfo->{show_nsfw} ? ' hidden' : '')) : (); - a href => sprintf('%s/sf/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), - rel => "iv:$_->{width}x$_->{height}:scr", $_->{nsfw} && !$self->authInfo->{show_nsfw} ? (class => 'hidden') : (); - img src => sprintf('%s/st/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), alt => mt '_vnpage_scr_num', $_->{id}; - end; - end; - } - end; - end; - } - end; + + for my $rel (@$r) { + my @scr = grep $_->{rid} && $rel->{id} == $_->{rid}, @{$v->{screenshots}}; + next if !@scr; + p class => 'rel'; + cssicon "lang $_", mt "_lang_$_" for (@{$rel->{languages}}); + a href => "/r$rel->{id}", $rel->{title}; + end; + div class => 'scr'; + for (@scr) { + a href => sprintf('%s/sf/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), + class => sprintf('scrlnk%s%s', $_->{nsfw} ? ' nsfw':'', $_->{nsfw}&&!$self->authInfo->{show_nsfw}?' hidden':''), + rel => "iv:$_->{width}x$_->{height}:scr"; + img src => sprintf('%s/st/%02d/%d.jpg', $self->{url_static}, $_->{id}%100, $_->{id}), alt => mt '_vnpage_scr_num', $_->{id}; + end; + } + end; + } end; } diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm index 19a58a0f..2d133476 100644 --- a/lib/VNDB/Util/Auth.pm +++ b/lib/VNDB/Util/Auth.pm @@ -25,7 +25,9 @@ sub authInit { return _rmcookie($self) if length($cookie) < 41; my $token = substr($cookie, 0, 40); my $uid = substr($cookie, 40); - $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended')->[0]; + $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended notifycount')->[0]; + # update the sessions.lastused column if lastused < now()'6 hours' + $self->dbSessionUpdateLastUsed($uid, $token) if $self->{_auth} && $self->{_auth}{session_lastused} < time()-6*3600; return _rmcookie($self) if !$self->{_auth}; } @@ -40,11 +42,10 @@ sub authLogin { if(_authCheck($self, $user, $pass)) { my $token = sha1_hex(join('', Time::HiRes::gettimeofday()) . join('', map chr(rand(93)+33), 1..9)); - my $expiration = time + 31536000; # 1yr my $cookie = $token . $self->{_auth}{id}; - $self->dbSessionAdd($self->{_auth}{id}, $token, $expiration); + $self->dbSessionAdd($self->{_auth}{id}, $token); - my $expstr = strftime("%a, %d %b %Y %H:%M:%S GMT", gmtime($expiration)); + my $expstr = strftime("%a, %d %b %Y %H:%M:%S GMT", gmtime(time + 31536000)); # keep the cookie for 1 year $self->resRedirect($to, 'post'); $self->resHeader('Set-Cookie', "vndb_auth=$cookie; expires=$expstr; path=/; domain=$self->{cookie_domain}"); return 1; @@ -95,7 +96,7 @@ sub _authCheck { return 0 if !$user || length($user) > 15 || length($user) < 2 || !$pass; - my $d = $self->dbUserGet(username => $user, what => 'extended')->[0]; + my $d = $self->dbUserGet(username => $user, what => 'extended notifycount')->[0]; return 0 if !defined $d->{id} || !$d->{rank}; if(_authEncryptPass($self, $pass, $d->{salt}) eq $d->{passwd}) { diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm index e9812f36..cc296cc7 100644 --- a/lib/VNDB/Util/BrowseHTML.pm +++ b/lib/VNDB/Util/BrowseHTML.pm @@ -184,7 +184,7 @@ sub htmlBrowseVN { end; td class => 'tc3'; cssicon "lang $_", mt "_lang_$_" - for (reverse sort split /\//, $l->{c_languages}); + for (reverse sort @{$l->{c_languages}}); end; td class => 'tc4'; lit $self->{l10n}->datestr($l->{c_released}); diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index 79330272..be56e4a8 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -83,18 +83,6 @@ sub htmlMainTabs { end; } - if($type =~ /[vrp]/ && $self->authCan('del')) { - li; - a href => "/$id/hide", mt $obj->{hidden} ? '_mtabs_unhide' : '_mtabs_hide'; - end; - } - - if($type =~ /[vrp]/ && $self->authCan('lock')) { - li; - a href => "/$id/lock", mt $obj->{locked} ? '_mtabs_unlock' : '_mtabs_lock'; - end; - } - if($type eq 'u' && $self->authCan('usermod')) { li $sel eq 'del' ? (class => 'tabselected') : (); a href => "/$id/del", mt '_mtabs_del'; @@ -141,12 +129,18 @@ sub htmlHiddenMessage { my($self, $type, $obj) = @_; return 0 if !$obj->{hidden}; my $board = $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id}; + # fetch edit summary (not present in $obj because the changes aren't fetched) + my $editsum = $type eq 'v' ? $self->dbVNGet(id => $obj->{id}, what => 'changes')->[0]{comments} + : $type eq 'r' ? $self->dbReleaseGet(id => $obj->{id}, what => 'changes')->[0]{comments} + : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments}; div class => 'mainbox'; h1 $obj->{title}||$obj->{name}; div class => 'warning'; h2 mt '_hiddenmsg_title'; p; lit mt '_hiddenmsg_msg', "/t/$board"; + br; br; + lit bb2html $editsum; end; end; end; @@ -209,7 +203,11 @@ sub htmlRevision { end; end; my $i = 1; - revdiff(\$i, $type, $old, $new, @$_) for (@fields); + revdiff(\$i, $type, $old, $new, @$_) for ( + [ ihid => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ], + [ ilock => serialize => sub { mt $_[0] ? '_revision_yes' : '_revision_no' } ], + @fields + ); end; } end; @@ -262,7 +260,7 @@ sub revdiff { $ser2 = mt '_revision_empty' if !$ser2 && $ser2 ne '0'; Tr $$i++ % 2 ? (class => 'odd') : (); - td mt "_revfield_${type}_$short"; + td mt $short eq 'ihid' || $short eq 'ilock' ? "_revfield_$short" : "_revfield_${type}_$short"; td class => 'tcval'; lit $ser1; end; td class => 'tcval'; lit $ser2; end; end; @@ -399,20 +397,13 @@ sub htmlVoteStats { sub htmlSearchBox { my($self, $sel, $v) = @_; - # escape search query for use as a query string value - (my $q = $v||'') =~ s/&/%26/g; - $q =~ s/\?/%3F/g; - $q =~ s/;/%3B/g; - $q =~ s/ /%20/g; - $q = "?q=$q" if $q; - fieldset class => 'search'; - p class => 'searchtabs'; - a href => "/v/all$q", $sel eq 'v' ? (class => 'sel') : (), mt '_searchbox_vn'; - a href => "/r$q", $sel eq 'r' ? (class => 'sel') : (), mt '_searchbox_releases'; - a href => "/p/all$q", $sel eq 'p' ? (class => 'sel') : (), mt '_searchbox_producers'; - a href => '/g'.($q?"/list$q":''), $sel eq 'g' ? (class => 'sel') : (), mt '_searchbox_tags'; - a href => "/u/all$q", $sel eq 'u' ? (class => 'sel') : (), mt '_searchbox_users'; + p id => 'searchtabs'; + a href => '/v/all', $sel eq 'v' ? (class => 'sel') : (), mt '_searchbox_vn'; + a href => '/r', $sel eq 'r' ? (class => 'sel') : (), mt '_searchbox_releases'; + a href => '/p/all', $sel eq 'p' ? (class => 'sel') : (), mt '_searchbox_producers'; + a href => '/g', $sel eq 'g' ? (class => 'sel') : (), mt '_searchbox_tags'; + a href => '/u/all', $sel eq 'u' ? (class => 'sel') : (), mt '_searchbox_users'; end; input type => 'text', name => 'q', id => 'q', class => 'text', value => $v; input type => 'submit', class => 'submit', value => mt '_searchbox_submit'; diff --git a/lib/VNDB/Util/FormHTML.pm b/lib/VNDB/Util/FormHTML.pm index 2882b4c4..cfac77b9 100644 --- a/lib/VNDB/Util/FormHTML.pm +++ b/lib/VNDB/Util/FormHTML.pm @@ -209,11 +209,23 @@ sub htmlForm { end; } - # edit summary / submit button + # db mod / edit summary / submit button if(!$options->{nosubmit}) { div class => 'mainbox'; fieldset class => 'submit'; if($options->{editsum}) { + # hidden / locked checkbox + if($self->authCan('del')) { + input type => 'checkbox', name => 'ihid', id => 'ihid', value => 1, $options->{frm}{ihid} ? (checked => 'checked') : (); + label for => 'ihid', mt '_form_ihid'; + } + if($self->authCan('lock')) { + input type => 'checkbox', name => 'ilock', id => 'ilock', value => 1, $options->{frm}{ilock} ? (checked => 'checked') : (); + label for => 'ilock', mt '_form_ilock'; + } + txt "\n".mt('_form_hidlock_note')."\n" if $self->authCan('lock') || $self->authCan('del'); + + # edit summary (my $txt = $options->{frm}{editsum}||'') =~ s/&/&/; $txt =~ s/</</; $txt =~ s/>/>/; diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index ba330790..eb086bcd 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -77,18 +77,17 @@ sub _menu { div class => 'menubox'; if($self->authInfo->{id}) { - my $msg = $self->dbUserMessageCount($self->authInfo->{id}); my $uid = sprintf '/u%d', $self->authInfo->{id}; + my $nc = $self->authInfo->{notifycount}; h2; a href => $uid, ucfirst $self->authInfo->{username}; - # note: user ranks aren't TL'ed (but might be in the future, hmm) txt ' ('.mt('_urank_'.$self->authInfo->{rank}).')'; end; div; a href => "$uid/edit", mt '_menu_myprofile'; br; a href => "$uid/list", mt '_menu_myvnlist'; br; a href => "$uid/wish", mt '_menu_mywishlist'; br; - a href => "/t$uid", $msg ? (class => 'standout') : (), mt '_menu_mymessages', $msg; br; + a href => "$uid/notifies", $nc ? (class => 'notifyget') : (), mt('_menu_mynotifications').($nc?" ($nc)":''); br; a href => "$uid/hist", mt '_menu_mychanges'; br; a href => "$uid/tags", mt '_menu_mytags'; br; br; diff --git a/static/f/icons.png b/static/f/icons.png Binary files differindex 58a4ab97..4437300b 100644 --- a/static/f/icons.png +++ b/static/f/icons.png diff --git a/static/s/angel/conf b/static/s/angel/conf index 94a9e96b..eb858770 100644 --- a/static/s/angel/conf +++ b/static/s/angel/conf @@ -1,4 +1,5 @@ name Angelic Serenade (dark blue) +userid 2 // text maintext #ddd // primary text color (also used for the menu links) diff --git a/static/s/aselia_01/conf b/static/s/aselia_01/conf index 5b45a0ee..b07657c7 100644 --- a/static/s/aselia_01/conf +++ b/static/s/aselia_01/conf @@ -1,4 +1,5 @@ name Eien no Aselia (falu red) +userid 51 // Eien no Aselia skin made using Minitokyo.Eien.no.Aselia.Scans_373967 // created: 09/27/2009 by echomateria diff --git a/static/s/carnevale/conf b/static/s/carnevale/conf index 5a735ff2..84ba8940 100644 --- a/static/s/carnevale/conf +++ b/static/s/carnevale/conf @@ -1,4 +1,5 @@ name Gekkou no Carnevale (black) +userid 51 // Gekkou no Carnevale skin made using a wallpaper comes with the game // created: 22/01/2009 by echomateria diff --git a/static/s/eiel/conf b/static/s/eiel/conf index a7bf333a..fd00320c 100644 --- a/static/s/eiel/conf +++ b/static/s/eiel/conf @@ -1,4 +1,5 @@ name Jingai Makyo (peach-orange) +userid 51 // A skin made using an image I had for a long time without knowing it's source, // thankfully this skin finally brought out the answer that it was from Jingai Makyo. diff --git a/static/s/ever17_01/conf b/static/s/ever17_01/conf index a82493d0..e7c7ff1c 100644 --- a/static/s/ever17_01/conf +++ b/static/s/ever17_01/conf @@ -1,4 +1,5 @@ name Ever17 (bondi blue) +userid 51 // Ever 17 skin made using the images from the extras section of the game // created: 01/01/2009 by echomateria diff --git a/static/s/fate_01/conf b/static/s/fate_01/conf index f55260ec..f749ed0e 100644 --- a/static/s/fate_01/conf +++ b/static/s/fate_01/conf @@ -1,4 +1,5 @@ name Fate/stay night (seal brown) +userid 51 // FSN skin skin made using a popular fanart // created: 12/31/2008 by echomateria diff --git a/static/s/fate_02/conf b/static/s/fate_02/conf index b58a24b6..0c0ddc96 100644 --- a/static/s/fate_02/conf +++ b/static/s/fate_02/conf @@ -1,4 +1,5 @@ name Fate/stay night (pale carmine) +userid 51 // FSN skin made using a popular fanart // created: 01/01/2009 by echomateria diff --git a/static/s/grey/conf b/static/s/grey/conf index 9de205ad..ef3ca505 100644 --- a/static/s/grey/conf +++ b/static/s/grey/conf @@ -1,4 +1,5 @@ name Touhou (grey) +userid 2 // text maintext #222 diff --git a/static/s/higu/conf b/static/s/higu/conf index 74108144..9c860379 100644 --- a/static/s/higu/conf +++ b/static/s/higu/conf @@ -1,4 +1,5 @@ name Higurashi no Naku Koro ni (orange) +userid 51 // Higurashi no Naku Koro ni skin made using an image I found in MiniTokyo // created: 22/01/2009 by echomateria diff --git a/static/s/lb/conf b/static/s/lb/conf index b56996dc..a1e317a0 100644 --- a/static/s/lb/conf +++ b/static/s/lb/conf @@ -1,4 +1,5 @@ name Little Busters! (pink) +userid 93 // text maintext #408 diff --git a/static/s/lb_02/conf b/static/s/lb_02/conf index 73bd4bb0..e0fd96c2 100644 --- a/static/s/lb_02/conf +++ b/static/s/lb_02/conf @@ -1,4 +1,5 @@ name Little Busters! (lemon chiffon) +userid 51 // Little Busters! skin made using the Minitokyo.Little.Busters.Scans_316439 // created: 09/27/2009 by echomateria diff --git a/static/s/primitive/conf b/static/s/primitive/conf index 11e92be6..6f3b4b8f 100644 --- a/static/s/primitive/conf +++ b/static/s/primitive/conf @@ -1,4 +1,5 @@ name Primitive Link (pale chestnut) +userid 51 // Primitive Link skin made using an image that I liked without knowing what it's based on for a long time // created: 23/01/2009 by echomateria diff --git a/static/s/saya/conf b/static/s/saya/conf index 1f70e590..fd4408d7 100644 --- a/static/s/saya/conf +++ b/static/s/saya/conf @@ -1,4 +1,5 @@ name Saya no Uta (dark scarlet) +userid 51 // Saya no Uta skin made using a criminally cute fanart // created: 22/01/2009 by echomateria diff --git a/static/s/seinarukana/conf b/static/s/seinarukana/conf index bca7746b..1a2a833d 100644 --- a/static/s/seinarukana/conf +++ b/static/s/seinarukana/conf @@ -1,4 +1,5 @@ name Seinarukana (white) +userid 51 // Seinarukana skin made using a callendar image // created: 12/31/2008 by echomateria diff --git a/static/s/taka/conf b/static/s/taka/conf index dba52d26..37f0993e 100644 --- a/static/s/taka/conf +++ b/static/s/taka/conf @@ -1,4 +1,5 @@ name Sora no Iro, Mizu no Iro (turquoise) +userid 51 // A Sora no Iro, Mizu no Iro skin based on a wallpaper named My Perfect Day // created: 23/01/2009 by echomateria diff --git a/static/s/term/conf b/static/s/term/conf index 921e3566..d6fa1d52 100644 --- a/static/s/term/conf +++ b/static/s/term/conf @@ -1,4 +1,5 @@ name Neon (black) +userid 93 // text maintext #0f0 diff --git a/static/s/tsukihime/conf b/static/s/tsukihime/conf index 4dab95bf..c44a063a 100644 --- a/static/s/tsukihime/conf +++ b/static/s/tsukihime/conf @@ -1,4 +1,5 @@ name Tsukihime (midnight blue) +userid 51 // Tsukihime skin made using an image from the Tsukihime Plus+Disc // created: 02/01/2009 by echomateria diff --git a/static/s/tsukihime_02/conf b/static/s/tsukihime_02/conf index a5db55f2..e9866c8d 100644 --- a/static/s/tsukihime_02/conf +++ b/static/s/tsukihime_02/conf @@ -1,4 +1,5 @@ name Tsukihime (black) +userid 51 // Tsukihime skin made with an awesome Akiha artwork from Tsukihime PLUS disc // created: 23/01/2009 by echomateria diff --git a/util/dbgraph.pl b/util/dbgraph.pl index 10297b66..a8f1cbe2 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -23,7 +23,7 @@ my %subgraphs = ( 'Producers' => [qw| FFFFCC producers producers_rev producers_relations |], 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |], 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |], - 'Users' => [qw| CCFFFF users votes rlists wlists sessions |], + 'Users' => [qw| CCFFFF users votes rlists wlists sessions notifications |], 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], 'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |], diff --git a/util/skingen.pl b/util/skingen.pl index 73e4fdc7..37504921 100755 --- a/util/skingen.pl +++ b/util/skingen.pl @@ -1,6 +1,5 @@ #!/usr/bin/perl -package VNDB; use strict; use warnings; @@ -8,81 +7,66 @@ use Cwd 'abs_path'; use Image::Magick; eval { require CSS::Minifier::XS }; - -our($ROOT, %O); +our $ROOT; BEGIN { ($ROOT = abs_path $0) =~ s{/util/skingen\.pl$}{}; } - -if(@ARGV) { - writeskin(readskin($_)) for (@ARGV); -} else { - /([^\/]+)$/ && writeskin(readskin($1)) for (glob($ROOT.'/static/s/*')); -} +use lib "$ROOT/lib"; +use SkinFile; -sub readskin { # skin name +sub writeskin { # $name my $name = shift; - my %o; - open my $F, '<', $ROOT.'/static/s/'.$name.'/conf' or die $!; - while(<$F>) { - chomp; - s/\r//g; - s{[\t\s]*//.+$}{}; - next if !/^([a-z0-9]+)[\t\s]+(.+)$/; - $o{$1} = $2; - } - close $F; - $o{_name} = $name; - return \%o; -} - - -sub writeskin { # $obj - my $o = shift; - - # fix image locations - $o->{$_} && ($o->{$_} = '/s/'.$o->{_name}.'/'.$o->{$_}) for (qw|imglefttop imgrighttop|); + my $skin = SkinFile->new("$ROOT/static/s", $name); + my %o = map +($_ => $skin->get($_)), $skin->get; # get the right top image - if($o->{imgrighttop}) { + if($o{imgrighttop}) { + my $path = "/s/$name/$o{imgrighttop}"; my $img = Image::Magick->new; - $img->Read($ROOT.'/static'.$o->{imgrighttop}); - $o->{_bgright} = sprintf 'background: url(%s) no-repeat; width: %dpx; height: %dpx', - $o->{imgrighttop}, $img->Get('width'), $img->Get('height'); + $img->Read("$ROOT/static$path"); + $o{_bgright} = sprintf 'background: url(%s) no-repeat; width: %dpx; height: %dpx', + $path, $img->Get('width'), $img->Get('height'); } else { - $o->{_bgright} = 'display: none'; + $o{_bgright} = 'display: none'; } # body background - if(!$o->{imglefttop}) { - $o->{_bodybg} = "background-color: $o->{bodybg}"; + if($o{imglefttop}) { + $o{_bodybg} = sprintf 'background: %s url(/s/%s/%s) no-repeat', $o{bodybg}, $name, $o{imglefttop}; } else { - $o->{_bodybg} = "background: $o->{bodybg} url($o->{imglefttop}) no-repeat"; + $o{_bodybg} = sprintf 'background-color: %s', $o{bodybg}; } # main title - $o->{_maintitle} = $o->{maintitle} ? "color: $o->{maintitle}" : 'display: none'; + $o{_maintitle} = $o{maintitle} ? "color: ".$o{maintitle} : 'display: none'; # create boxbg.png my $img = Image::Magick->new(size => '1x1'); - $img->Read('xc:'.$o->{boxbg}); - $img->Write(filename => $ROOT.'/static/s/'.$o->{_name}.'/boxbg.png'); - $o->{_boxbg} = '/s/'.$o->{_name}.'/boxbg.png'; + $img->Read("xc:$o{boxbg}"); + $img->Write(filename => "$ROOT/static/s/$name/boxbg.png"); + $o{_boxbg} = "/s/$name/boxbg.png"; # get the blend color $img = Image::Magick->new(size => '1x1'); - $img->Read('xc:'.$o->{bodybg}, 'xc:'.$o->{boxbg}); + $img->Read("xc:$o{bodybg}", "xc:$o{boxbg}"); $img = $img->Flatten(); - $o->{_blendbg} = '#'.join '', map sprintf('%02x', $_*255), $img->GetPixel(x=>1,y=>1); + $o{_blendbg} = '#'.join '', map sprintf('%02x', $_*255), $img->GetPixel(x=>1,y=>1); # write the CSS open my $CSS, '<', "$ROOT/data/style.css" or die $!; my $css = join '', <$CSS>; close $CSS; - $css =~ s/\$$_\$/$o->{$_}/g for (keys %$o); - open my $SKIN, '>', "$ROOT/static/s/$o->{_name}/style.css" or die $!; + $css =~ s/\$$_\$/$o{$_}/g for (keys %o); + open my $SKIN, '>', "$ROOT/static/s/$name/style.css" or die $!; print $SKIN $CSS::Minifier::XS::VERSION ? CSS::Minifier::XS::minify($css) : $css; close $SKIN; } +if(@ARGV) { + writeskin($_) for (@ARGV); +} else { + writeskin($_) for (SkinFile->new("$ROOT/static/s")->list); +} + + diff --git a/util/sql/all.sql b/util/sql/all.sql index 04e1dbf9..9a9eace0 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -9,7 +9,10 @@ CREATE LANGUAGE plpgsql; CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p'); CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); +CREATE TYPE language AS ENUM('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh'); CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); +CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce'); +CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't'); CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori'); CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial'); CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig'); @@ -27,6 +30,10 @@ 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 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(); @@ -56,14 +63,25 @@ CREATE TRIGGER insert_notify AFTER INSERT ON tags CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW 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(); + -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; -- Rows that are assumed to be available -INSERT INTO users (id, username, mail, rank) VALUES (0, 'deleted', 'del@vndb.org', 0); -INSERT INTO users (username, mail, rank) VALUES ('multi', 'multi@vndb.org', 0); +INSERT INTO users (id, username, mail, rank, notify_dbdel) VALUES (0, 'deleted', 'del@vndb.org', 0, false); +INSERT INTO users (username, mail, rank, notify_dbdel) VALUES ('multi', 'multi@vndb.org', 0, false); INSERT INTO stats_cache (section, count) VALUES ('users', 1), diff --git a/util/sql/func.sql b/util/sql/func.sql index ed054207..f9f0e490 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -1,4 +1,16 @@ +-- A small note on the function naming scheme: +-- edit_* -> revision insertion abstraction functions +-- *_notify -> functions issuing a PgSQL NOTIFY statement +-- notify_* -> functions creating entries in the notifications table +-- update_* -> functions to update a cache +-- *_update ^ (I should probably rename these to +-- *_calc ^ the update_* scheme for consistency) +-- I like to keep the nouns in functions singular, in contrast to the table +-- naming scheme where nouns are always plural. But I'm not very consistent +-- with that, either. + + -- update_vncache(id) - updates the c_* columns in the vn table CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ @@ -14,7 +26,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND rr1.released <> 0 GROUP BY rv1.vid ), 0), - c_languages = COALESCE(ARRAY_TO_STRING(ARRAY( + c_languages = ARRAY( SELECT rl2.lang FROM releases_rev rr2 JOIN releases_lang rl2 ON rl2.rid = rr2.id @@ -26,7 +38,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND r2.hidden = FALSE GROUP BY rl2.lang ORDER BY rl2.lang - ), '/'), ''), + ), c_platforms = COALESCE(ARRAY_TO_STRING(ARRAY( SELECT rp3.platform FROM releases_platforms rp3 @@ -38,8 +50,7 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$ AND rr3.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer AND r3.hidden = FALSE GROUP BY rp3.platform - ORDER BY rp3.platform - ), '/'), '') + ORDER BY rp3.platform), '/'), '') WHERE id = $1; $$ LANGUAGE sql; @@ -119,14 +130,26 @@ $$ LANGUAGE plpgsql; -- create temporary table for generic revision info CREATE OR REPLACE FUNCTION edit_revtable(t dbentry_type, i integer) RETURNS void AS $$ BEGIN - CREATE TEMPORARY TABLE edit_revision ( - type dbentry_type NOT NULL, - iid integer, - requester integer, - ip inet, - comments text + BEGIN + CREATE TEMPORARY TABLE edit_revision ( + type dbentry_type NOT NULL, + iid integer, + requester integer, + ip inet, + comments text, + ihid boolean, + ilock boolean + ); + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_revision; + END; + INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t, + ( SELECT vid FROM vn_rev WHERE id = i + UNION SELECT rid FROM releases_rev WHERE id = i + UNION SELECT pid FROM producers_rev WHERE id = i), + COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE), + COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE) ); - INSERT INTO edit_revision (type, iid) VALUES (t, i); END; $$ LANGUAGE plpgsql; @@ -145,18 +168,16 @@ BEGIN r.rev := 1; ELSE SELECT c.rev+1 INTO r.rev FROM changes c - LEFT JOIN vn_rev vr ON c.id = vr.id - LEFT JOIN releases_rev rr ON c.id = rr.id - LEFT JOIN producers_rev pr ON c.id = pr.id - WHERE (t = 'v' AND vr.vid = i) - OR (t = 'r' AND rr.rid = i) - OR (t = 'p' AND pr.pid = i) + JOIN ( SELECT id FROM vn_rev WHERE t = 'v' AND vid = i + UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i + UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i + ) x(id) ON x.id = c.id ORDER BY c.id DESC LIMIT 1; END IF; -- insert change - INSERT INTO changes (type, requester, ip, comments, rev) - SELECT t, requester, ip, comments, r.rev + INSERT INTO changes (type, requester, ip, comments, ihid, ilock, rev) + SELECT t, requester, ip, comments, ihid, ilock, r.rev FROM edit_revision RETURNING id INTO r.cid; -- insert DB item @@ -178,23 +199,26 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$ BEGIN -- create tables, based on existing tables (so that the column types are always synchronised) - CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn DROP COLUMN id; - ALTER TABLE edit_vn DROP COLUMN vid; - CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_anime DROP COLUMN vid; - CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_relations DROP COLUMN vid1; - ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid; - CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_vn_screenshots DROP COLUMN vid; + BEGIN + CREATE TEMPORARY TABLE edit_vn (LIKE vn_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_vn DROP COLUMN id; + ALTER TABLE edit_vn DROP COLUMN vid; + CREATE TEMPORARY TABLE edit_vn_anime (LIKE vn_anime INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_vn_anime DROP COLUMN vid; + CREATE TEMPORARY TABLE edit_vn_relations (LIKE vn_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_vn_relations DROP COLUMN vid1; + ALTER TABLE edit_vn_relations RENAME COLUMN vid2 TO vid; + CREATE TEMPORARY TABLE edit_vn_screenshots (LIKE vn_screenshots INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_vn_screenshots DROP COLUMN vid; + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; + END; + PERFORM edit_revtable('v', cid); -- new VN, load defaults IF cid IS NULL THEN - PERFORM edit_revtable('v', NULL); INSERT INTO edit_vn DEFAULT VALUES; -- otherwise, load revision ELSE - PERFORM edit_revtable('v', (SELECT vid FROM vn_rev WHERE id = cid)); INSERT INTO edit_vn SELECT title, alias, img_nsfw, length, "desc", l_wp, l_vnn, image, l_encubed, l_renai, original FROM vn_rev WHERE id = cid; INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid; INSERT INTO edit_vn_relations SELECT vid2, relation FROM vn_relations WHERE vid1 = cid; @@ -218,7 +242,6 @@ BEGIN INSERT INTO vn_relations SELECT r.cid, vid, relation FROM edit_vn_relations; INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots; UPDATE vn SET latest = r.cid WHERE id = r.iid; - DROP TABLE edit_revision, edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; RETURN r; END; $$ LANGUAGE plpgsql; @@ -228,26 +251,29 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_release_init(cid integer) RETURNS void AS $$ BEGIN -- temp. tables - CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release DROP COLUMN id; - ALTER TABLE edit_release DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_lang DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_media DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_platforms DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_producers DROP COLUMN rid; - CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_release_vn DROP COLUMN rid; + BEGIN + CREATE TEMPORARY TABLE edit_release (LIKE releases_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_release DROP COLUMN id; + ALTER TABLE edit_release DROP COLUMN rid; + CREATE TEMPORARY TABLE edit_release_lang (LIKE releases_lang INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_release_lang DROP COLUMN rid; + CREATE TEMPORARY TABLE edit_release_media (LIKE releases_media INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_release_media DROP COLUMN rid; + CREATE TEMPORARY TABLE edit_release_platforms (LIKE releases_platforms INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_release_platforms DROP COLUMN rid; + CREATE TEMPORARY TABLE edit_release_producers (LIKE releases_producers INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_release_producers DROP COLUMN rid; + CREATE TEMPORARY TABLE edit_release_vn (LIKE releases_vn INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_release_vn DROP COLUMN rid; + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; + END; + PERFORM edit_revtable('r', cid); -- new release IF cid IS NULL THEN - PERFORM edit_revtable('r', NULL); INSERT INTO edit_release DEFAULT VALUES; -- load revision ELSE - PERFORM edit_revtable('r', (SELECT rid FROM releases_rev WHERE id = cid)); INSERT INTO edit_release SELECT title, original, type, website, released, notes, minage, gtin, patch, catalog, resolution, voiced, freeware, doujin, ani_story, ani_ero FROM releases_rev WHERE id = cid; INSERT INTO edit_release_lang SELECT lang FROM releases_lang WHERE rid = cid; INSERT INTO edit_release_media SELECT medium, qty FROM releases_media WHERE rid = cid; @@ -277,7 +303,6 @@ BEGIN INSERT INTO releases_producers SELECT pid, r.cid, developer, publisher FROM edit_release_producers; INSERT INTO releases_vn SELECT r.cid, vid FROM edit_release_vn; UPDATE releases SET latest = r.cid WHERE id = r.iid; - DROP TABLE edit_revision, edit_release, edit_release_lang, edit_release_media, edit_release_platforms, edit_release_producers, edit_release_vn; RETURN r; END; $$ LANGUAGE plpgsql; @@ -286,19 +311,22 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_producer_init(cid integer) RETURNS void AS $$ BEGIN - CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_producer DROP COLUMN id; - ALTER TABLE edit_producer DROP COLUMN pid; - CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); - ALTER TABLE edit_producer_relations DROP COLUMN pid1; - ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid; + BEGIN + CREATE TEMPORARY TABLE edit_producer (LIKE producers_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_producer DROP COLUMN id; + ALTER TABLE edit_producer DROP COLUMN pid; + CREATE TEMPORARY TABLE edit_producer_relations (LIKE producers_relations INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_producer_relations DROP COLUMN pid1; + ALTER TABLE edit_producer_relations RENAME COLUMN pid2 TO pid; + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_producer, edit_producer_relations; + END; + PERFORM edit_revtable('p', cid); -- new producer IF cid IS NULL THEN - PERFORM edit_revtable('p', NULL); INSERT INTO edit_producer DEFAULT VALUES; -- load revision ELSE - PERFORM edit_revtable('p', (SELECT pid FROM producers_rev WHERE id = cid)); INSERT INTO edit_producer SELECT type, name, original, website, lang, "desc", alias, l_wp FROM producers_rev WHERE id = cid; INSERT INTO edit_producer_relations SELECT pid2, relation FROM producers_relations WHERE pid1 = cid; END IF; @@ -318,7 +346,6 @@ BEGIN INSERT INTO producers_rev SELECT r.cid, r.iid, type, name, original, website, lang, "desc", alias, l_wp FROM edit_producer; INSERT INTO producers_relations SELECT r.cid, pid, relation FROM edit_producer_relations; UPDATE producers SET latest = r.cid WHERE id = r.iid; - DROP TABLE edit_revision, edit_producer, edit_producer_relations; RETURN r; END; $$ LANGUAGE plpgsql; @@ -540,3 +567,174 @@ BEGIN END; $$ LANGUAGE plpgsql; + + +-- update (vn|release|producer).(hidden|locked) on a new revision +-- NOTE: this is a /before/ trigger, it modifies NEW +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; + RETURN NEW; +END; +$$ LANGUAGE plpgsql; + + + + + +---------------------------------------------------------- +-- notification functions -- +-- (these are, in fact, also triggers) -- +---------------------------------------------------------- + + +-- called on INSERT INTO threads_posts +CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$ +BEGIN + INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser) + SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid + FROM threads t + JOIN threads_boards tb ON tb.tid = t.id + WHERE t.id = NEW.tid + AND tb.type = 'u' + AND tb.iid <> NEW.uid -- don't notify when posting in your own board + AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet + SELECT 1 + FROM notifications n + WHERE n.uid = tb.iid + AND n.ntype = 'pm' + AND n.iid = t.id + AND n.read IS NULL + ); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on UPDATE vn / producers / releases +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; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on UPDATE vn / releases +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; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on UPDATE vn / producers / releases +-- 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; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +-- called on INSERT INTO threads_posts +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; + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + diff --git a/util/sql/schema.sql b/util/sql/schema.sql index d5286a94..a9da0cbb 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -20,7 +20,23 @@ CREATE TABLE changes ( added timestamptz NOT NULL DEFAULT NOW(), requester integer NOT NULL DEFAULT 0, ip inet NOT NULL DEFAULT '0.0.0.0', - comments text NOT NULL DEFAULT '' + comments text NOT NULL DEFAULT '', + ihid boolean NOT NULL DEFAULT FALSE, + ilock boolean NOT NULL DEFAULT FALSE +); + +-- notifications +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + uid integer NOT NULL, + date timestamptz NOT NULL DEFAULT NOW(), + read timestamptz, + ntype notification_ntype NOT NULL, + ltype notification_ltype NOT NULL, + iid integer NOT NULL, + subid integer, + c_title text NOT NULL, + c_byuser integer ); -- producers @@ -48,7 +64,7 @@ CREATE TABLE producers_rev ( name varchar(200) NOT NULL DEFAULT '', original varchar(200) NOT NULL DEFAULT '', website varchar(250) NOT NULL DEFAULT '', - lang varchar NOT NULL DEFAULT 'ja', + lang language NOT NULL DEFAULT 'ja', "desc" text NOT NULL DEFAULT '', alias varchar(500) NOT NULL DEFAULT '', l_wp varchar(150) @@ -72,7 +88,7 @@ CREATE TABLE releases ( -- releases_lang CREATE TABLE releases_lang ( rid integer NOT NULL, - lang varchar NOT NULL, + lang language NOT NULL, PRIMARY KEY(rid, lang) ); @@ -158,7 +174,8 @@ CREATE TABLE screenshots ( CREATE TABLE sessions ( uid integer NOT NULL, token bytea NOT NULL, - expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval), + added timestamptz NOT NULL DEFAULT NOW(), + lastused timestamptz NOT NULL DEFAULT NOW(), PRIMARY KEY (uid, token) ); @@ -238,7 +255,6 @@ CREATE TABLE threads_boards ( tid integer NOT NULL DEFAULT 0, type character(2) NOT NULL DEFAULT 0, iid integer NOT NULL DEFAULT 0, - lastread smallint NOT NULL, PRIMARY KEY(tid, type, iid) ); @@ -259,7 +275,9 @@ CREATE TABLE users ( ip inet NOT NULL DEFAULT '0.0.0.0', c_tags integer NOT NULL DEFAULT 0, salt character(9) NOT NULL DEFAULT '', - ign_votes voolean NOT NULL DEFAULT FALSE + ign_votes boolean NOT NULL DEFAULT FALSE, + notify_dbedit boolean NOT NULL DEFAULT TRUE, + notify_announce boolean NOT NULL DEFAULT FALSE ); -- vn @@ -270,7 +288,7 @@ CREATE TABLE vn ( hidden boolean NOT NULL DEFAULT FALSE, rgraph integer, c_released integer NOT NULL DEFAULT 0, - c_languages varchar(32) NOT NULL DEFAULT '', + c_languages language[] NOT NULL DEFAULT '{}', c_platforms varchar(32) NOT NULL DEFAULT '', c_popularity real, c_rating real, @@ -339,10 +357,12 @@ 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 producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); -ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id); +ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id); ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id); ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id); ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id); diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql new file mode 100644 index 00000000..65571fa9 --- /dev/null +++ b/util/updates/update_2.11.sql @@ -0,0 +1,120 @@ + + +CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce'); +CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 't'); + +CREATE TABLE notifications ( + id serial PRIMARY KEY NOT NULL, + uid integer NOT NULL REFERENCES users (id), + date timestamptz NOT NULL DEFAULT NOW(), + read timestamptz, + ntype notification_ntype NOT NULL, + ltype notification_ltype NOT NULL, + iid integer NOT NULL, + subid integer, + c_title text NOT NULL, + c_byuser integer REFERENCES users (id) +); + +-- convert the "unread messages" count into notifications +INSERT INTO notifications (uid, date, ntype, ltype, iid, subid, c_title, c_byuser) + SELECT tb.iid, tp.date, 'pm', 't', t.id, tp.num, t.title, tp.uid + FROM threads_boards tb + JOIN threads t ON t.id = tb.tid + JOIN threads_posts tp ON tp.tid = t.id AND tp.num = COALESCE(tb.lastread, 1) + WHERE tb.type = 'u' AND NOT t.hidden AND (tb.lastread IS NULL OR t.count <> tb.lastread); + +-- ...and drop the now unused lastread column +ALTER TABLE threads_boards DROP COLUMN lastread; + +ALTER TABLE users ADD COLUMN notify_dbedit boolean NOT NULL DEFAULT true; +ALTER TABLE users ADD COLUMN notify_announce boolean NOT NULL DEFAULT false; +UPDATE users SET notify_dbedit = false WHERE id IN(0,1); + + +-- languages -> ENUM +CREATE TYPE language AS ENUM('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh'); +ALTER TABLE producers_rev ALTER COLUMN lang DROP DEFAULT; +ALTER TABLE producers_rev ALTER COLUMN lang TYPE language USING CASE lang WHEN 'pt' THEN 'pt-pt' ELSE lang::language END; +ALTER TABLE producers_rev ALTER COLUMN lang SET DEFAULT 'ja'; +ALTER TABLE releases_lang ALTER COLUMN lang TYPE language USING CASE lang WHEN 'pt' THEN 'pt-pt' ELSE lang::language END; +-- c_languages is an now array of languages, rather than a serialized string +ALTER TABLE vn ALTER COLUMN c_languages DROP DEFAULT; +ALTER TABLE vn ALTER COLUMN c_languages TYPE language[] USING '{}'; +ALTER TABLE vn ALTER COLUMN c_languages SET DEFAULT '{}'; + + + +ALTER TABLE changes ADD COLUMN ihid boolean NOT NULL DEFAULT FALSE; +ALTER TABLE changes ADD COLUMN ilock boolean NOT NULL DEFAULT FALSE; + +\i util/sql/func.sql + +SELECT COUNT(*) FROM (SELECT update_vncache(id) FROM vn) x; + +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 OR REPLACE FUNCTION tmp_edit_hidlock(t text, iid integer) RETURNS void AS $$ +BEGIN + IF t = 'v' THEN + PERFORM edit_vn_init(latest) FROM vn WHERE id = iid; + IF EXISTS(SELECT 1 FROM vn WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This visual novel was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This visual novel was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_vn_commit(); + ELSIF t = 'r' THEN + PERFORM edit_release_init(latest) FROM releases WHERE id = iid; + IF EXISTS(SELECT 1 FROM releases WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This release was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This release was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_release_commit(); + ELSE + PERFORM edit_producer_init(latest) FROM producers WHERE id = iid; + IF EXISTS(SELECT 1 FROM producers WHERE id = iid AND hidden) THEN + UPDATE edit_revision SET ihid = true, ip = '0.0.0.0', requester = 1, + comments = 'This producer was deleted before the update to VNDB 2.11, no reason specified.'; + ELSE + UPDATE edit_revision SET ilock = true, ip = '0.0.0.0', requester = 1, + comments = 'This producer was locked before the update to VNDB 2.11, no reason specified.'; + END IF; + PERFORM edit_producer_commit(); + END IF; +END; +$$ LANGUAGE plpgsql; + + SELECT 'v', COUNT(*) FROM (SELECT tmp_edit_hidlock('v', id) FROM vn WHERE (hidden OR locked)) x +UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases WHERE hidden OR locked) x +UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x; +DROP FUNCTION tmp_edit_hidlock(text, integer); + + +-- keep track of when a session is last used +ALTER TABLE sessions ADD COLUMN lastused timestamptz NOT NULL DEFAULT NOW(); +ALTER TABLE sessions RENAME COLUMN expiration TO added; +UPDATE sessions SET added = added - '1 year'::interval; +ALTER TABLE sessions ALTER COLUMN added SET DEFAULT NOW(); + + +CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm(); +-- make sure to add these triggers AFTER performing the batch edit above +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(); + diff --git a/util/vndb.pl b/util/vndb.pl index 32e1bdb4..29c2648c 100755 --- a/util/vndb.pl +++ b/util/vndb.pl @@ -18,6 +18,7 @@ use lib $ROOT.'/lib'; use YAWF ':html'; use VNDB::L10N; +use SkinFile; our(%O, %S); @@ -53,18 +54,14 @@ sub reqinit { my $self = shift; # Determine language - # if the cookie or parameter "l10n" is set, use that. - # otherwise, interpret the Accept-Language header or fall back to English + # if the cookie is set, use that. Otherwise, interpret the Accept-Language header or fall back to English. # if the cookie is set and is the same as either the Accept-Language header or the fallback, remove it - my $conf = $self->reqParam('l10n') || $self->reqCookie('l10n'); + my $conf = $self->reqCookie('l10n'); $conf = '' if !$conf || !grep $_ eq $conf, VNDB::L10N::languages; $self->{l10n} = VNDB::L10N->get_handle(); # this uses I18N::LangTags::Detect - if($self->{l10n}->language_tag() eq $conf && $self->reqCookie('l10n')) { - $self->resHeader('Set-Cookie', "l10n= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$self->{cookie_domain}"); - } elsif($self->reqParam('l10n') && $conf && $conf ne ($self->reqCookie('l10n')||'') && $self->{l10n}->language_tag() ne $conf) { - $self->resHeader('Set-Cookie', "l10n=$conf; expires=Sat, 01-Jan-2030 00:00:00 GMT; path=/; domain=$self->{cookie_domain}"); - } + $self->resHeader('Set-Cookie', "l10n= ; expires=Sat, 01-Jan-2000 00:00:00 GMT; path=/; domain=$self->{cookie_domain}") + if $conf && $self->{l10n}->language_tag() eq $conf; $self->{l10n} = VNDB::L10N->get_handle($conf) if $conf && $self->{l10n}->language_tag() ne $conf; @@ -104,21 +101,12 @@ sub readskins { my %skins; # dirname => skin name my @regen; my $lasttemplate = [stat "$ROOT/data/style.css"]->[9]; - for my $f (glob "$ROOT/static/s/*") { - next if !-e "$f/conf"; - my $n = $1 if $f =~ m{([^/]+)$}; - open my $F, '<', "$f/conf" or die $!; - while(<$F>) { - chomp; - s/\r//; - s{[\t\s]*//.*$}{}; - next if !/^name[\t\s]+(.+)$/; - $skins{$n} = $1; - last; - } - close $F; - next if !$skins{$n}; + 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; |
