diff options
-rw-r--r-- | data/docs/16 | 44 | ||||
-rw-r--r-- | data/docs/2 | 34 | ||||
-rw-r--r-- | data/docs/2.cs | 1 | ||||
-rw-r--r-- | data/docs/2.de | 1 | ||||
-rw-r--r-- | data/docs/2.hu | 1 | ||||
-rw-r--r-- | data/docs/2.it | 1 | ||||
-rw-r--r-- | data/docs/2.ru | 1 | ||||
-rw-r--r-- | data/docs/index | 1 | ||||
-rw-r--r-- | data/global.pl | 3 | ||||
-rw-r--r-- | data/lang.txt | 286 | ||||
-rw-r--r-- | data/script.js | 340 | ||||
-rw-r--r-- | data/style.css | 55 | ||||
-rw-r--r-- | lib/Multi/IRC.pm | 15 | ||||
-rw-r--r-- | lib/VNDB/DB/Chars.pm | 23 | ||||
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 13 | ||||
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/DB/Staff.pm | 133 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 72 | ||||
-rw-r--r-- | lib/VNDB/Func.pm | 26 | ||||
-rw-r--r-- | lib/VNDB/Handler/Chars.pm | 18 | ||||
-rw-r--r-- | lib/VNDB/Handler/Misc.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/Handler/Staff.pm | 414 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNBrowse.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNEdit.pm | 118 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 108 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 14 | ||||
-rw-r--r-- | lib/VNDB/Util/LayoutHTML.pm | 4 | ||||
-rw-r--r-- | lib/VNDBUtil.pm | 4 | ||||
-rwxr-xr-x | util/jsgen.pl | 1 | ||||
-rw-r--r-- | util/sql/func.sql | 69 | ||||
-rw-r--r-- | util/sql/staff.sql | 67 |
31 files changed, 1821 insertions, 56 deletions
diff --git a/data/docs/16 b/data/docs/16 new file mode 100644 index 00000000..e70d8b28 --- /dev/null +++ b/data/docs/16 @@ -0,0 +1,44 @@ +:TITLE:Adding/Editing Staff Members +:INC:index + +:SUB:Introduction +<p> + Staff entries represent individuals and groups that play significant roles in visual + novels production, such as script writers, character designers, artists, voice actors, + composers and singers. +</p> + +:SUB:General info +<dl> + <dt>Name (romaji)</dt><dd> + Person's true name, in the latin alphabet. If true name is unknown, fill in the most + widely used alias. Check out the <a href="/d5">general editing guidelines</a> for + information on name order and romanization. <a href="/d5.1">Romanise</a> according to our + guidelines. + </dd><dt>Original name</dt><dd> + The actual name of the person, in the case it is not officially in the latin + alphabet. Leave empty if it's the same as the "Name (romaji)". If possible, + try to include a space between the given name and the surname. + </dd><dt>Gender</dt><dd> + Person's gender, if known. + </dd><dt>Primary language</dt><dd> + Native language, japanese by default. + </dd><dt>Staff note</dt><dd> + Brief note describing biography, occupation and production companies affiliations, if any. + It's fine to leave this field empty if there's nothing much known about the person in question. + </dd><dt>Official page</dt><dd> + Link to the official page or blog. + </dd><dt>Additional links</dt><dd> + You could also provide links to Wikipedia entry, Twitter account and/or AniDB entry. Do + not specify the full URL for additional links, we only need a small part of it! + </dd> +</dl> + +:SUB:Aliases +<p> + People involved in visual novels production are often hide behind multiple aliases. These + aliases could be listed here. Add only aliases that are actually used in VN credits. + Sometimes names with different japanese spelling are romanized equally. You may add such + aliases, but then original spelling should be used when name is being looked up in the VN + staff edit form. +</p> diff --git a/data/docs/2 b/data/docs/2 index b57ab0f1..66ed7cdb 100644 --- a/data/docs/2 +++ b/data/docs/2 @@ -77,12 +77,36 @@ </dl> -:SUB:Categories -<i> - The category system has been replaced with tags. This section is a placeholder to preserve - numbering. -</i> +:SUB:Staff & Cast +<p>It's possible to link VN entries to <a href="/d16">staff entries</a> in the +staff and cast tabs.</p> +:SUBSUB:Staff +<p> + The staff tab lists the people or groups involved in the creation of the + visual novel. The following roles can be used: +</p> +<dl> + <dt>Script writer</dt><dd> </dd> + <dt>Character design</dt><dd> </dd> + <dt>Composer</dt><dd> </dd> + <dt>Director</dt><dd> </dd> + <dt>Artist</dt><dd> </dd> + <dt>Vocals</dt><dd>Vocals used in the music. Not to be used for voice actors of characters, these should be listed under <a href="/d2.3.2">cast</a> instead.</dd> + <dt>Staff</dt><dd>Used for any additional minor roles, use the "notes" field to indicate the staff's involvement.</dd> +</dl> +<p>A few guidelines:</p> +<ul> + <li>Add major staff only.</li> + <li>If someone performed several important jobs, add multiple entries with different major roles.</li> + <li>Don't add a person as "Staff" if he/she is already credited with some of the major roles. Mention this in the notes field instead.</li> +</ul> + +:SUBSUB:Cast +<p> + The cast tab lists the voice actors for each character. This tab is only + available when characters have been linked to the visual novel. +</p> :SUB:Image <p> diff --git a/data/docs/2.cs b/data/docs/2.cs index 6cd92d58..98aa5953 100644 --- a/data/docs/2.cs +++ b/data/docs/2.cs @@ -1,5 +1,6 @@ :TITLE:Přidání/editace vizuální novely :INC:index +:INC:incomplete :SUB:Kdy přidat vizuální novelu <p> diff --git a/data/docs/2.de b/data/docs/2.de index d665566a..8143a5dd 100644 --- a/data/docs/2.de +++ b/data/docs/2.de @@ -1,5 +1,6 @@ :TITLE:Visual Novel hinzufügen/bearbeiten :INC:index +:INC:incomplete :SUB:Wann fügt man eine Visual Novel hinzu <p> diff --git a/data/docs/2.hu b/data/docs/2.hu index 17d9274d..a9b6fdce 100644 --- a/data/docs/2.hu +++ b/data/docs/2.hu @@ -1,5 +1,6 @@ :TITLE:Visual Novel bejegyzése/szerkesztése :INC:index +:INC:incomplete :SUB:Mikor jegyezzünk be egy új visual novelt <p> diff --git a/data/docs/2.it b/data/docs/2.it index 686a706a..97317a8e 100644 --- a/data/docs/2.it +++ b/data/docs/2.it @@ -1,5 +1,6 @@ :TITLE:Aggiungere/Modificare una Visual Novel :INC:index +:INC:incomplete :SUB:Quando aggiungere una visual novel <p> diff --git a/data/docs/2.ru b/data/docs/2.ru index 60ba845e..d48101bb 100644 --- a/data/docs/2.ru +++ b/data/docs/2.ru @@ -1,5 +1,6 @@ :TITLE:Добавление/Редактирование новеллы :INC:index +:INC:incomplete :SUB:Какие игры нужно добавлять? <p> diff --git a/data/docs/index b/data/docs/index index e0d11a8b..a1ca1acd 100644 --- a/data/docs/index +++ b/data/docs/index @@ -5,6 +5,7 @@ <li><a href="/d15">Special Games</a></li> <li><a href="/d3">Releases</a></li> <li><a href="/d4">Producers</a></li> + <li><a href="/d16">Staff</a></li> <li><a href="/d12">Characters</a></li> <li><a href="/d10">Tags & traits</a></li> <li><a href="/d13">Capturing screenshots</a></li> diff --git a/data/global.pl b/data/global.pl index cf9a9fd4..b3fd8264 100644 --- a/data/global.pl +++ b/data/global.pl @@ -35,7 +35,7 @@ our %S = (%S, ch_size => [ 256, 300 ], # max. w*h of char images cv_size => [ 256, 400 ], # max. w*h of cover images # bit flags (Flag 8 was used for charedit, currently free for re-use) - permissions => {qw| board 1 boardmod 2 edit 4 tag 16 dbmod 32 tagmod 64 usermod 128 affiliate 256 |}, + permissions => {qw| board 1 boardmod 2 edit 4 staffedit 8 tag 16 dbmod 32 tagmod 64 usermod 128 affiliate 256 |}, languages => [qw|ar cs da de en es fi fr he hu id it ja ko nl no pl pt-br pt-pt ro ru sk sv tr uk vi zh|], producer_types => [qw|co in ng|], discussion_boards => [qw|an db ge v p u|], # <- note that some properties of these boards are hard-coded @@ -113,6 +113,7 @@ our %S = (%S, changes => [ 25, 'VNDB Recent Changes', '/hist' ], posts => [ 25, 'VNDB Recent Posts', '/t' ], }, + staff_roles => [qw|script chardesign music director art songs staff|], ); diff --git a/data/lang.txt b/data/lang.txt index 48365acf..89f9134c 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -3053,6 +3053,9 @@ tr : Yapımcılar uk : Компанії it : Editori +:_menu_staff +en : Staff + :_menu_characters en : Characters ru : Персонажи @@ -3260,6 +3263,9 @@ tr : Yapımcı ekle uk : Додати компанію it : Aggiungi Editore +:_menu_addstaff +en : Add Staff + :_menu_addcharacter en : Add Character ru : Добавить персонажа @@ -3884,6 +3890,9 @@ tr : Yapımcılar uk : Компанії it : Editori +:_searchbox_staff +en : Staff + :_searchbox_chars en : Characters ru : Персонажи @@ -4218,6 +4227,9 @@ tr : Sadece yapımcılar uk : Лише компанії it : Solo editori +:_hist_filter_onlystaff +en : Only staff + :_hist_filter_onlychars en : Only characters ru : Только персонажи @@ -5328,8 +5340,213 @@ tr : Sonuç bulunamadı uk : Нічого не знайдено it : Nessun risultato +############################################################################# +## Staff pages (/s/*) ## +############################################################################# +# Handler::Staff + +# Built-in credits + +:_credit_script +en : Script writer + +:_credit_chardesign +en : Character design + +:_credit_music +en : Composer + +:_credit_director +en : Director + +:_credit_art +en : Artist + +:_credit_songs +en : Vocals + +:_credit_staff +en : Staff + +# Staff diff fields (/s+.+) + +:_revfield_s_name +en : Name (romaji) + +:_revfield_s_original +en : Original name + +:_revfield_s_gender +en : Gender + +:_revfield_s_lang +en : Language + +:_revfield_s_aliases +en : Aliases + +:_revfield_s_l_wp +en : Wikipedia link + +:_revfield_s_l_site +en : Official page + +:_revfield_s_l_twitter +en : Twitter account + +:_revfield_s_l_anidb +en : AniDB creator ID + +:_revfield_s_desc +en : Description + +# Staff browser + +:_sbrowse_title +en : Browse staff + +:_sbrowse_list +en : Staff list + +:_sbrowse_searchres +en : Search results + +:_sbrowse_noresults +en : No results found + +:_staff_credits +en : Credits + +:_staff_voiced +en : Voiced characters + +:_staff_as +en : as [_1] + +:_staff_gender +en : Gender + +:_staff_language +en : Language + +:_staff_aliases +en : [quant,_1,Alias,Aliases] + +:_staff_links +en : Links + +:_staff_l_wp +en : Wikipedia + +:_staff_l_site +en : Official page + +:_staff_l_twitter +en : Twitter + +:_staff_l_anidb +en : AniDB + +:_staff_bio +en : Info + +:_staff_col_title +en : Title + +:_staff_col_released +en : Released + +:_staff_col_role +en : Role +:_staff_col_credit +en : Credit +:_staff_col_note +en : Note + +:_staff_col_cast +en : Cast + +:_staff_col_seiyuu +en : Seiyuu + +# Add/edit staff + +:_staffe_title_edit +en : Edit [_1] + +:_staffe_title_add +en : Add staff member + +:_staffe_form_generalinfo +en : General info + +:_staffe_form_name +en : Name (romaji) + +:_staffe_form_original +en : Original name + +:_staffe_form_alias +en : Alias (romaji) + +:_staffe_form_original_alias +en : Original alias + +:_staffe_form_original_note +en : The original name of the person, leave blank if it is already in the Latin alphabet. + +:_staffe_form_note +en : Staff note + +:_staffe_form_gender +en : Gender + +:_staffe_form_lang +en : Primary language + +:_staffe_form_wikipedia +en : Wikipedia link + +:_staffe_form_site +en : Official page + +:_staffe_form_twitter +en : Twitter username + +:_staffe_form_tw_err +en : Invalid twitter username + +:_staffe_form_anidb +en : AniDB creator ID + +:_staffe_aliases +en : Aliases + +:_staffe_aliases_add +en : Add alias + +:_staffe_alias_required +en : Specify romanized spelling of the alias. + +:_staffe_aliases_none +en : None + +:_vnstaffe_form_staff +en : Staff + +:_vnstaffe_form_role +en : Credit + +:_vnstaffe_form_note +en : Note + +:_vnstaffe_add +en : Add staff + +:_vnstaffe_none +en : None ############################################################################# ## Release pages (/r/*) ## @@ -8673,6 +8890,9 @@ tr : Diğer tüm yayımlar uk : Усі інші релізи it : Tutte le altre pubblicazioni +:_charp_voice +en : Voiced by + :_charp_description en : Description ru : Описание @@ -12951,6 +13171,44 @@ tr : Eğer resim çıplaklık veya vahşet içeriyorsa, ya da iş ortamında gö uk : Будь ласка, відміть цю опцію, якщо зображення містить оголення, кров або тим чи іншим способом неприйнятне для загального кола користувачів. it : Scegli questa opzione se l'immagine contiene nudi, gore o è generalmente non adatta ad ambienti di lavoro. +:_vnedit_staff +en : Staff + +:_vnedit_staff_msg +en : Please check the [url,/d2.3,staff editing guidelines]. You can + [url,/s/new,create a new staff entry] if it is not in the database yet, + but please [url,/s/all,check for aliasses first]. + +:_vnedit_cast +en : Cast + +:_vnedit_cast_import +en : Import cast + +:_vnedit_cast_import_title +en : Import character cast from related visual novels + +:_vnedit_cast_char +en : Character + +:_vnedit_cast_sel_char +en : Select character + +:_vnedit_cast_nochar +en : Select character first please. + +:_vnedit_cast_seiyuu +en : Seiyuu + +:_vnedit_cast_note +en : Note + +:_vnedit_voiced_by +en : voiced by + +:_vnedit_cast_add +en : Add cast + :_vnedit_rel en : Relations ru : Связи @@ -13563,6 +13821,12 @@ tr : İlişkiler uk : Зв’язки it : Relazioni +:_revfield_v_credits +en : Credits + +:_revfield_v_seiyuu +en : Seiyuu + :_revfield_v_anime en : Anime ru : Аниме @@ -14294,6 +14558,9 @@ tr : karakterler uk : персонажі it : personaggi +:_vnpage_tab_staff +en : staff + :_vnpage_rel en : Releases ru : Выпуски @@ -14405,6 +14672,18 @@ tr : karakter ekle uk : додати персонажа it : aggiungi personaggio +:_vnpage_staff +en : Staff + +:_vnpage_cast +en : Cast of characters + +:_vnpage_staff_add +en : add staff + +:_vnpage_staff_edit +en : edit staff + :_vnpage_rel_add en : add release ru : добавить выпуск @@ -14793,7 +15072,7 @@ uk : Огляньте [url,_1,історію редагувань], можлив it : Controlla le [url,_1,attività] per eventuali modifiche recenti riguardo a ciò che vuoi cambiare. :_editmsg_msg_search -en : [url,_1,Search the database] to see if we already have information about this [index,_2,visual novel,release,producer,character]. +en : [url,_1,Search the database] to see if we already have information about this [index,_2,visual novel,release,producer,character,person]. ru : [url,_1,Воспользуйтесь поиском], ведь вполне возможно, что у нас уже есть информация об [index,_2,этой новелле,этом выпуске,этой компании,этом персонаже]. cs : [url,_1,Prohledejte databázi], zda již nemáme informaci o [index,_2,této vizuální novele,tomto vydání,tomto producentovi,této postavě]. hu : [url,_1,Nézd át az adatbázist], hogy van-e már információ erről a [index,_2,visual novelről, kiadásról, készítőről, szereplőről]. @@ -14817,7 +15096,7 @@ uk : Відновлення it : Ripristina :_editmsg_revert_msg -en : You are editing an old revision of this [index,_1,visual novel,release,producer,character]. +en : You are editing an old revision of this [index,_1,visual novel,release,producer,character,person]. If you save it, all changes made after this revision will be reverted! ru : Вы правите старую редакцию страницы [index,_1,новеллы,выпуска,компании,персонажа]. Если вы сохраните её, все изменения, сделанные после этой правки, будут утеряны! @@ -15282,5 +15561,8 @@ tr : [_1] sadece küçük harfler, rakamlar ve tire içerebilir; ve bir harf ile uk : Поле [_1] може складатися лише з маленьких літер латинки, цифр і рисок, і має починатися з літери it : [_1] può avere solo caratteri minuscoli e trattini, e deve iniziare con una lettera +:_formerr_tpl_json +en : [_1]: Malformed staff data + # vim: set ft=none: diff --git a/data/script.js b/data/script.js index f61805bf..b84316f3 100644 --- a/data/script.js +++ b/data/script.js @@ -16,6 +16,9 @@ * tgl -> VN tag linking * tvs -> VN page tag spoilers * vnr -> VN relation editor + * vns -> VN staff + * vnc -> VN cast + * sal -> Staff aliases editor */ /* Internationalization note: @@ -201,6 +204,9 @@ function mt() { return val; } +function jsonParse(s) { + return s ? JSON.parse(s) : ''; +} @@ -2047,6 +2053,340 @@ if(byId('jt_box_chare_vns')) +/* S T A F F (/s+/edit) */ + +function salLoad () { + var aliases = jsonParse(byId('aliases').value) || []; + for(var i = 0; i < aliases.length; i++) { + salAdd(aliases[i].aid, aliases[i].name, aliases[i].orig); + } + salEmpty(); + + // bind the add-link + byName(byClass(byId('alias_new'), 'td', 'tc_add')[0], 'a')[0].onclick = salFormAdd; + byName(byId('maincontent'), 'form')[0].onsubmit = salSerialize; +} + +function salAdd(aid, name, original) { + byId('alias_tbl').appendChild(tag('tr', {id:'alias_tr_'+aid}, + tag('td', {'class':'tc_name' }, tag('input', {type:'text', 'class':'text', value:name})), + tag('td', {'class':'tc_original' }, tag('input', {type:'text', 'class':'text', value:original})), + tag('td', {'class':'tc_add' }, + tag('input', {type:'hidden', value:aid}), + tag('a', {href:'#', onclick:salDel}, mt('_js_remove'))) + )); + + salEmpty(); +} + +function salEmpty() { + var tbl = byId('alias_tbl'); + if (byName(tbl, 'tr').length < 1) + tbl.appendChild(tag('tr', {id:'alias_tr_none'}, tag('td', {colspan:3}, mt('_staffe_aliases_none')))); + else if (byId('alias_tr_none')) + tbl.removeChild(byId('alias_tr_none')); +} + +function salSerialize() { + var tbl = byName(byId('alias_tbl'), 'tr'); + var a = []; + for (var i = 0; i < tbl.length; ++i) { + if(tbl[i].id == 'alias_tr_none') + continue; + var name = byName(byClass(tbl[i], 'td', 'tc_name')[0], 'input')[0].value; + var orig = byName(byClass(tbl[i], 'td', 'tc_original')[0], 'input')[0].value; + var id = byName(byClass(tbl[i], 'td', 'tc_add')[0], 'input')[0].value; + a.push({ aid:Number(id), name:name, orig:orig }); + } + byId('aliases').value = JSON.stringify(a); + return true; +} + +function salDel() { + var tr = this; + while (tr.nodeName.toLowerCase() != 'tr') + tr = tr.parentNode; + byId('alias_tbl').removeChild(tr); + salSerialize(); + salEmpty(); + return false; +} + +function salFormAdd() { + var alnew = byId('alias_new'); + var name = byName(byClass(alnew, 'td', 'tc_name')[0], 'input')[0]; + var orig = byName(byClass(alnew, 'td', 'tc_original')[0], 'input')[0]; + if(name.value.length < 1) + return alert(mt('_staffe_alias_required')); + + salAdd(0, name.value, orig.value); + salSerialize(); + name.value = ''; + orig.value = ''; + return false; +} + +if(byId('jt_box_staffe_aliases')) + salLoad(); + + + + +/* S T A F F < - > V N L I N K I N G (/v+/edit#vn_staff) */ + +// vnsStaffData maps alias id to staff data { NNN: { id: ..., aid: NNN, name: ...} } +// used to fill form fields instead of ajax queries in vnsLoad() and vncLoad() +var vnsStaffData = {}; + +function onSubmit(form, handler) { + var prev_handler = form.onsubmit; + form.onsubmit = function(e) { + if (prev_handler) prev_handler(e); + return handler(e); + } +} + +function vnsLoad() { + vnsStaffData = jsonParse(getText(byId('staffdata')||{})) || {}; + var credits = jsonParse(byId('credits').value) || []; + for(var i = 0; i < credits.length; i++) { + var aid = credits[i].aid; + if(vnsStaffData[aid]) + vnsAdd(vnsStaffData[aid], credits[i].role, credits[i].note); + } + vnsEmpty(); + + onSubmit(byName(byId('maincontent'), 'form')[0], vnsSerialize); + + // dropdown search + dsInit(byId('credit_input'), '/xml/staff.xml?q=', function(item, tr) { + tr.appendChild(tag('td', { style: 'text-align: right; padding-right: 5px'}, 's'+item.getAttribute('id'))); + tr.appendChild(tag('td', item.firstChild.nodeValue)); + }, vnsFormAdd); +} + +function vnsAdd(staff, role, note) { + var tbl = byId('credits_tbl'); + + var rlist = tag('select', {onchange:vnsSerialize}); + for (var i = 0; i < staff_roles.length; i++) // l10n /^_credit_/ + rlist.appendChild(tag('option', {value:staff_roles[i], selected:staff_roles[i]==role}, + mt('_credit_'+staff_roles[i]))); + + tbl.appendChild(tag('tr', {id:'vns_a'+staff.aid}, + tag('td', {'class':'tc_name'}, + tag('input', {type:'hidden', value:staff.aid}), + tag('a', {href:'/s'+staff.id}, staff.name)), + tag('td', {'class':'tc_role'}, rlist), + tag('td', {'class':'tc_note'}, tag('input', {type:'text', 'class':'text', value:note})), + tag('td', {'class':'tc_del'}, tag('a', {href:'#', onclick:vnsDel}, mt('_js_remove'))) + )); + vnsEmpty(); + vnsSerialize(); +} + +function vnsEmpty() { + var x = byId('credits_loading'); + var tbody = byId('credits_tbl'); + var tbl = tbody.parentNode; + var thead = byName(tbl, 'thead'); + if(x) + tbody.removeChild(x); + if(byName(tbody, 'tr').length < 1) { + tbody.appendChild(tag('tr', {id:'credits_tr_none'}, + tag('td', {colspan:4}, mt('_vnstaffe_none')))); + if (thead.length) + tbl.removeChild(thead[0]); + } else { + if(byId('credits_tr_none')) + tbody.removeChild(byId('credits_tr_none')); + if (thead.length < 1) { + thead = tag('thead', tag('tr', + tag('td', {'class':'tc_name'}, mt('_vnstaffe_form_staff')), + tag('td', {'class':'tc_role'}, mt('_vnstaffe_form_role')), + tag('td', {'class':'tc_note'}, mt('_vnstaffe_form_note')), + tag('td', ''))); + tbl.insertBefore(thead, tbody); + } + } +} + +function vnsSerialize() { + var l = byName(byId('credits_tbl'), 'tr'); + var c = []; + for (var i = 0; i < l.length; i++) { + if(l[i].id == 'credits_tr_none') + continue; + var aid = byName(byClass(l[i], 'tc_name')[0], 'input')[0]; + var role = byName(byClass(l[i], 'tc_role')[0], 'select')[0]; + var note = byName(byClass(l[i], 'tc_note')[0], 'input')[0]; + c.push({ aid:Number(aid.value), role:role.value, note:note.value }); + } + byId('credits').value = JSON.stringify(c); + return true; +} + +function vnsDel() { + var tr = this; + while (tr.nodeName.toLowerCase() != 'tr') + tr = tr.parentNode; + byId('credits_tbl').removeChild(tr); + vnsEmpty(); + vnsSerialize(); + return false; +} + +function vnsFormAdd(item) { + var s = { id:item.getAttribute('id'), aid:item.getAttribute('aid'), name:item.firstChild.nodeValue }; + vnsAdd(s, 'staff', ''); + return ''; +} + +if(byId('jt_box_vn_staff')) + vnsLoad(); + + + + +/* V N C H A R A C T E R S C A S T (/v+/edit#vn_cast) */ + +var vncImportData = []; + +function vncLoad() { + var cast = jsonParse(byId('seiyuu').value) || []; + for(var i = 0; i < cast.length; i++) { + var aid = cast[i].aid; + if(vnsStaffData[aid]) // vnsStaffData is filled by vnsLoad() + vncAdd(vnsStaffData[aid], cast[i].cid, cast[i].note); + } + vncEmpty(); + + var cast_import = byId('cast_import'); + if(cast_import) { + vncImportData = jsonParse(getText(byId('castimpdata')||{})) || []; + if(vncImportData.length) + byName(cast_import, 'a')[0].onclick = vncImport; + else + cast_import.style.display = 'none'; + } + onSubmit(byName(byId('maincontent'), 'form')[0], vncSerialize); + + // dropdown search + dsInit(byId('cast_input'), '/xml/staff.xml?q=', function(item, tr) { + tr.appendChild(tag('td', { style: 'text-align: right; padding-right: 5px'}, 's'+item.getAttribute('id'))); + tr.appendChild(tag('td', item.firstChild.nodeValue)); + }, vncFormAdd); +} + +function vncImport() { + if (!vncImportData.length) + return false; + var c = {}; + for (var i = 0; i < vncImportData.length; i++) { + var s = vncImportData[i]; + c[s.cid] = s; + } + // exclude already credited cast from import list + var l = byName(byId('cast_tbl'), 'tr'); + for (var i = 0; i < l.length; i++) { + if(l[i].id == 'cast_tr_none') + continue; + var role = byName(byClass(l[i], 'tc_char')[0], 'select')[0].value; + if (role in c) + delete c[role]; + } + for (var cid in c) { + if (!c.hasOwnProperty(cid)) + continue; + vncAdd({ id:c[cid].sid, aid:c[cid].aid, name:c[cid].name }, cid, ''); + } + return false; +} + +function vncAdd(seiyuu, chr, note) { + var tbl = byId('cast_tbl'); + + var csel = byId('cast_chars').cloneNode(true); + csel.removeAttribute('id'); + csel.value = chr; + + tbl.appendChild(tag('tr', {id:'vnc_a'+seiyuu.aid}, + tag('td', {'class':'tc_char'}, csel), + tag('td', {'class':'tc_name'}, + tag('input', {type:'hidden', value:seiyuu.aid}), + tag('a', {href:'/s'+seiyuu.id}, seiyuu.name)), + tag('td', {'class':'tc_note'}, tag('input', {type:'text', 'class':'text', value:note})), + tag('td', {'class':'tc_del'}, tag('a', {href:'#', onclick:vncDel}, mt('_js_remove'))) + )); + vncEmpty(); + vncSerialize(); +} + +function vncFormAdd(item) { + var chr = byId('cast_chars').value; + if (chr) { + var s = { id:item.getAttribute('id'), aid:item.getAttribute('aid'), name:item.firstChild.nodeValue }; + vncAdd(s, chr, ''); + } else + alert(mt('_vnedit_cast_nochar')); + return ''; +} + +function vncEmpty() { + var x = byId('cast_loading'); + var tbody = byId('cast_tbl'); + var tbl = tbody.parentNode; + var thead = byName(tbl, 'thead'); + if(x) + tbody.removeChild(x); + if(byName(tbody, 'tr').length < 1) { + tbody.appendChild(tag('tr', {id:'cast_tr_none'}, + tag('td', {colspan:4}, mt('_vnstaffe_none')))); + if (thead.length) + tbl.removeChild(thead[0]); + } else { + if(byId('cast_tr_none')) + tbody.removeChild(byId('cast_tr_none')); + if (thead.length < 1) { + thead = tag('thead', tag('tr', + tag('td', {'class':'tc_char'}, mt('_vnedit_cast_char')), + tag('td', {'class':'tc_name'}, mt('_vnedit_cast_seiyuu')), + tag('td', {'class':'tc_note'}, mt('_vnedit_cast_note')), + tag('td', ''))); + tbl.insertBefore(thead, tbody); + } + } +} + +function vncSerialize() { + var l = byName(byId('cast_tbl'), 'tr'); + var c = []; + for (var i = 0; i < l.length; i++) { + if(l[i].id == 'cast_tr_none') + continue; + var aid = byName(byClass(l[i], 'tc_name')[0], 'input')[0]; + var role = byName(byClass(l[i], 'tc_char')[0], 'select')[0]; + var note = byName(byClass(l[i], 'tc_note')[0], 'input')[0]; + c.push({ aid:Number(aid.value), cid:Number(role.value), note:note.value }); + } + byId('seiyuu').value = JSON.stringify(c); + return true; +} + +function vncDel() { + var tr = this; + while (tr.nodeName.toLowerCase() != 'tr') + tr = tr.parentNode; + byId('cast_tbl').removeChild(tr); + vncEmpty(); + vncSerialize(); + return false; +} + +if(byId('jt_box_vn_cast')) + vncLoad(); + + /* F I L T E R S Y S T E M */ diff --git a/data/style.css b/data/style.css index 9b9ecb9a..10fd8051 100644 --- a/data/style.css +++ b/data/style.css @@ -274,7 +274,7 @@ p#searchtabs a:hover, p#searchtabs a.sel { padding: 1px 5px 2px 5px; background: $secbg$ url($_boxbg$) repeat; } -#q { width: 450px } +#q { width: 600px } @@ -565,6 +565,59 @@ div.charb td.tc2 b { margin-left: 10px } div.charb td.tc2 b a { color: $grayedout$!important } +/***** Staff browse *****/ + +div.staffbrowse { padding-bottom: 10px } +.staffbrowse ul { float: left; margin-top: -5px; margin-left: 3%; width: 28%; } +.staffbrowse ul li { list-style-type: none; margin-bottom: 2px; } +.staffbrowse ul li acronym { margin-right: 5px; margin-top: 1px; } +.staffinfo { position: relative; float: left; margin-right: 3%; margin-bottom: 20px; } +.staffinfo acronym { vertical-align: bottom; } +.staffdesc h2, .staffroles h2 { margin: 0; padding: 5px 0; } +.staffdesc { display: table; margin-bottom: 10px; } +.staffdesc p { padding: 0 5px; } +.staffroles td { padding-left: 20px; padding-right: 20px; } +.staffroles td.tc2 { min-width: 100px; } +table.aliases td { padding: 0 5px; } +table.aliases td.key { padding: 0 5px 0 0; } + + +/***** Staff display *****/ + +div.staff td { white-space: nowrap; padding-left: 20px; padding-right: 20px; } + + +/***** Staff edit *****/ + +#jt_box_vn_cast #cast_import { clear: right; float: right; } +#jt_box_vn_cast table, +#jt_box_vn_staff table, +#jt_box_staffe_aliases table { margin-bottom: 10px; margin-left: 20px } +#jt_box_vn_cast h2, +#jt_box_vn_staff h2, +#jt_box_staffe_aliases h2 { margin: 0 0 3px 0px; } +#jt_box_vn_cast td, +#jt_box_vn_staff td, +#jt_box_staffe_aliases td { padding: 1px 2px; vertical-align: middle; } +#jt_box_vn_cast td.tc_role, +#jt_box_vn_cast td.tc_role select, +#jt_box_vn_staff td.tc_role, +#jt_box_vn_staff td.tc_role select { width: 120px } +#jt_box_vn_cast td.tc_staff, +#jt_box_vn_staff td.tc_staff, +#jt_box_staffe_aliases td.tc_name, +#jt_box_staffe_aliases td.tc_original { width: 200px } +#jt_box_vn_cast td.tc_staff input, +#jt_box_vn_staff td.tc_staff input, +#jt_box_staffe_aliases td.tc_name input, +#jt_box_staffe_aliases td.tc_original input { width: 200px } +#jt_box_vn_cast td.tc_note, +#jt_box_vn_cast td.tc_note input, +#jt_box_vn_staff td.tc_note, +#jt_box_vn_staff td.tc_note input { width: 250px } +#jt_box_vn_cast td.tc_add, +#jt_box_vn_staff td.tc_add, +#jt_box_staffe_aliases td.tc_add { width: 40px; text-align: left } /***** Documentation pages *****/ diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm index cdf52847..4ac45cd9 100644 --- a/lib/Multi/IRC.pm +++ b/lib/Multi/IRC.pm @@ -282,12 +282,14 @@ sub notify { # name, pid, payload my $q = $_[ARG0] eq 'newrevision' ? q|SELECT c.type, c.rev, c.comments, c.id AS lastrev, - COALESCE(vr.vid, rr.rid, pr.pid, cr.cid) AS id, COALESCE(vr.title, rr.title, pr.name, cr.name) AS title, u.username + COALESCE(vr.vid, rr.rid, pr.pid, cr.cid, sr.sid) AS id, COALESCE(vr.title, rr.title, pr.name, cr.name, sa.name) AS title, u.username FROM changes c LEFT JOIN vn_rev vr ON c.type = 'v' AND c.id = vr.id LEFT JOIN releases_rev rr ON c.type = 'r' AND c.id = rr.id LEFT JOIN producers_rev pr ON c.type = 'p' AND c.id = pr.id LEFT JOIN chars_rev cr ON c.type = 'c' AND c.id = cr.id + LEFT JOIN staff_rev sr ON c.type = 's' AND c.id = sr.id + LEFT JOIN staff_alias sa ON c.type = 's' AND sa.id = sr.aid AND sa.rid = c.id JOIN users u ON u.id = c.requester WHERE c.id > ? AND c.requester <> 1 ORDER BY c.added| @@ -540,8 +542,8 @@ sub vndbid { # dest, msg my @id; # [ type, id, ref ] for (split /[, ]/, $msg) { next if length > 15 or m{[a-z]{3,6}://}i; # weed out URLs and too long things - push @id, /^(?:.*[^\w]|)([dvprtc])([1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, $3 ] # x+.+ - : /^(?:.*[^\w]|)([dvprtugic])([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, 0 ] : (); # x+ + push @id, /^(?:.*[^\w]|)([dvprtcs])([1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, $3 ] # x+.+ + : /^(?:.*[^\w]|)([dvprtugics])([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2, 0 ] : (); # x+ } for (@id) { @@ -554,12 +556,13 @@ sub vndbid { # dest, msg $t eq 'u' ? 'u.username AS title FROM users u WHERE u.id = ?' : $t eq 'p' ? 'pr.name AS title FROM producers_rev pr JOIN producers p ON p.latest = pr.id WHERE p.id = ?' : $t eq 'c' ? 'cr.name AS title FROM chars_rev cr JOIN chars c ON c.latest = cr.id WHERE c.id = ?' : + $t eq 's' ? 'sa.name AS title FROM staff_rev sr JOIN staff s ON s.latest = sr.id JOIN staff_alias sa ON sa.id = sr.aid AND sa.rid = s.latest WHERE s.id = ?' : $t eq 't' ? 'title, '.GETBOARDS.' FROM threads t WHERE id = ?' : $t eq 'g' ? 'name AS title FROM tags WHERE id = ?' : $t eq 'i' ? 'name AS title FROM traits WHERE id = ?' : 'rr.title FROM releases_rev rr JOIN releases r ON r.latest = rr.id WHERE r.id = ?'), [ $t, $id, $id ], 'formatid', [$dest] - ) if !$rev && $t =~ /[vprtugic]/; + ) if !$rev && $t =~ /[vprtugics]/; # edit/insert of vn/release/producer or discussion board post $_[KERNEL]->post(pg => query => 'SELECT ?::text AS type, ?::integer AS id, ?::integer AS rev, '.( @@ -567,9 +570,10 @@ 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 eq 'c' ? 'cr.name AS title, u.username, h.comments FROM changes h JOIN chars_rev cr ON h.id = cr.id JOIN users u ON u.id = h.requester WHERE cr.cid = ? AND h.rev = ?' : + $t eq 's' ? 'sa.name AS title, u.username, c.comments FROM changes c JOIN staff_rev sr ON c.id = sr.id JOIN users u ON u.id = c.requester JOIN staff_alias sa ON sa.id = sr.aid AND sa.rid = sr.id WHERE sr.sid = ? 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] - ) if $rev && $t =~ /[vprtc]/; + ) if $rev && $t =~ /[vprtcs]/; # documentation page (need to parse the doc pages manually here) if($t eq 'd') { @@ -609,6 +613,7 @@ sub formatid { p => 'producer', r => 'release', c => 'character', + s => 'staff', g => 'tag', i => 'trait', t => 'thread', diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm index 0f4120fd..2f7de43f 100644 --- a/lib/VNDB/DB/Chars.pm +++ b/lib/VNDB/DB/Chars.pm @@ -44,7 +44,7 @@ sub dbCharGet { defined $o{weight_min} ? ( 'cr.weight >= ?' => $o{weight_min} ) : (), defined $o{weight_max} ? ( 'cr.weight <= ? AND cr.weight > 0' => $o{weight_max} ) : (), $o{search} ? ( - '(cr.name ILIKE ? OR cr.original ILIKE ? OR cr.alias ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..3 ] ) : (), + '(cr.name ILIKE ? OR cr.original ILIKE ? OR cr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (), $o{char} ? ( 'LOWER(SUBSTR(cr.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( @@ -77,10 +77,11 @@ sub dbCharGet { join(', ', @select), join(' ', @join), \%where ); - if(@$r && $o{what} =~ /(vns|traits)/) { + if(@$r && $o{what} =~ /vns|traits|seiyuu/) { my %r = map { $_->{traits} = []; $_->{vns} = []; + $_->{seiyuu} = []; ($_->{cid}, $_) } @$r; @@ -108,6 +109,24 @@ sub dbCharGet { { 'cv.cid IN(!l)' => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () } )}); } + + if($o{what} =~ /seiyuu/) { + push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q| + SELECT cr.id AS cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, vr.title AS vntitle + FROM vn_seiyuu vs + JOIN chars_rev cr ON cr.cid = vs.cid + JOIN staff_alias sa ON sa.id = vs.aid + JOIN staff s ON sa.rid = s.latest + JOIN vn_rev vr ON vr.id = vs.vid + JOIN vn v ON v.latest = vs.vid + !W + ORDER BY v.c_released, sa.name|, { + 's.hidden = FALSE' => 1, + 'cr.id IN(!l)' => [[ keys %r ]], + $o{vid} ? ('v.id = ?' => $o{vid}) : (), + } + )}); + } } return wantarray ? ($r, $np) : $r; } diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 3155ca56..8927e1ec 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -27,7 +27,7 @@ sub dbStats { sub dbItemEdit { my($self, $type, $oid, %o) = @_; - my $fun = {qw|v vn r release p producer c char|}->{$type}; + my $fun = {qw|v vn r release p producer c char s staff|}->{$type}; $self->dbExec('SELECT edit_!s_init(?)', $fun, $oid); $self->dbExec('UPDATE edit_revision !H', { 'requester = ?' => $o{uid}||$self->authInfo->{id}, @@ -41,6 +41,7 @@ sub dbItemEdit { $self->dbProducerRevisionInsert(\%o) if $type eq 'p'; $self->dbReleaseRevisionInsert( \%o) if $type eq 'r'; $self->dbCharRevisionInsert( \%o) if $type eq 'c'; + $self->dbStaffRevisionInsert( \%o) if $type eq 's'; return $self->dbRow('SELECT * FROM edit_!s_commit()', $fun); } @@ -60,10 +61,10 @@ sub dbRevisionGet { $o{what} ||= ''; $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid}; - my %tables = qw|v vn r releases p producers c chars|; + my %tables = qw|v vn r releases p producers c chars s staff|; # what types should we join? my @types = ( - !$o{type} ? ('v', 'r', 'p', 'c') : + !$o{type} ? qw(v r p c s) : ref($o{type}) ? @{$o{type}} : $o{type} ne 'v' ? $o{type} : $o{releases} ? ('v', 'r') : 'v' @@ -97,15 +98,17 @@ sub dbRevisionGet { ) : (), $o{what} =~ /user/ ? 'JOIN users u ON h.requester = u.id' : (), ); + push @join, 'LEFT JOIN staff_alias sa ON sa.rid = sr.id AND sa.id = sr.aid' if grep /s/, @types; + my %tcolumns = qw(v vr.title r rr.title p pr.name c cr.name s sa.name); my @select = ( qw|h.id h.type h.requester h.comments h.rev|, q|extract('epoch' from h.added) as added|, $o{what} =~ /user/ ? 'u.username' : (), $o{what} =~ /item/ ? ( 'COALESCE('.join(', ', map "${_}r.${_}id", @types).') AS iid', - 'COALESCE('.join(', ', map /[pc]/ ? "${_}r.name" : "${_}r.title", @types).') AS ititle', - 'COALESCE('.join(', ', map "${_}r.original", @types).') AS ioriginal', + 'COALESCE('.join(', ', map $tcolumns{$_}, @types).') AS ititle', + 'COALESCE('.join(', ', map /s/ ? 'sa.original' : "${_}r.original", @types).') AS ioriginal', ) : (), ); diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm index 86ed124e..3ff70ae1 100644 --- a/lib/VNDB/DB/Producers.pm +++ b/lib/VNDB/DB/Producers.pm @@ -27,7 +27,7 @@ sub dbProducerGet { $o{id} ? ( 'p.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (), $o{search} ? ( - '(pr.name ILIKE ? OR pr.original ILIKE ? OR pr.alias ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..3 ] ) : (), + '(pr.name ILIKE ? OR pr.original ILIKE ? OR pr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (), $o{char} ? ( 'LOWER(SUBSTR(pr.name, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm new file mode 100644 index 00000000..a1728106 --- /dev/null +++ b/lib/VNDB/DB/Staff.pm @@ -0,0 +1,133 @@ + +package VNDB::DB::Staff; + +use strict; +use warnings; +use Exporter 'import'; + +our @EXPORT = qw|dbStaffGet dbStaffRevisionInsert|; + +# options: results, page, id, aid, search, rev +# what: extended changes roles aliases +sub dbStaffGet { + my $self = shift; + my %o = ( + results => 10, + page => 1, + what => '', + @_ + ); + + $o{search} =~ s/%//g if $o{search}; + + my %where = ( + !$o{id} && !$o{rev} ? ( 's.hidden = FALSE' => 1 ) : (), + $o{id} ? ( ref $o{id} ? ('s.id IN(!l)' => [$o{id}]) : ('s.id = ?' => $o{id}) ) : (), + $o{aid} ? ( ref $o{aid} ? ('sa.id IN(!l)' => [$o{aid}]) : ('sa.id = ?' => $o{aid}) ) : (), + $o{search} ? + $o{search} =~ /[\x{3000}-\x{9fff}\x{ff00}-\x{ff9f}]/ ? + # match against 'original' column only if search string contains any + # japanese character. + # note: more precise regex would be /[\p{Hiragana}\p{Katakana}\p{Han}]/ + ( q|(sa.original LIKE ? OR translate(sa.original,' ','') LIKE ?)| => [ '%'.$o{search}.'%', ($o{search} =~ s/\s+//gr).'%' ] ) : + ( '(sa.name ILIKE ? OR sa.original ILIKE ?)' => [ map '%'.$o{search}.'%', 1..2 ] ) : (), + $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (), + defined $o{char} && !$o{char} ? + ( '(ASCII(sa.name) < 97 OR ASCII(sa.name) > 122) AND (ASCII(sa.name) < 65 OR ASCII(sa.name) > 90)' => 1 ) : (), + $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (), + ); + + my @join; + push @join, 'JOIN staff s ON '.($o{rev} ? 's.id = sr.sid' : 'sr.id = s.latest'); + push @join, 'JOIN staff_alias sa ON sa.rid = sr.id'.($o{id}?' AND sa.id = sr.aid':''); + push @join, 'JOIN changes c ON c.id = sr.id' if $o{what} =~ /changes/ || $o{rev}; + push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/; + + my $select = 's.id, sa.id AS aid, sa.name, sa.original, sr.gender, sr.lang, sr.id AS cid'; + $select .= ', sr.desc, sr.l_wp, sr.l_site, sr.l_twitter, sr.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/; + $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, s.latest, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/; + + my $order = 'ORDER BY sa.name'; + + my($r, $np) = $self->dbPage(\%o, q| + SELECT !s + FROM staff_rev sr + !s + !W + !s|, + $select, join(' ', @join), \%where, $order + ); + + if (@$r && $o{what} =~ /roles|aliases/) { + my %r = map { + $_->{roles} = []; + $_->{cast} = []; + $_->{aliases} = []; + ($_->{cid}, $_); + } @$r; + if ($o{what} =~ /roles/) { + push @{$r{ delete $_->{rid} }{roles}}, $_ for (@{$self->dbAll(q| + SELECT sa.rid, vr.vid, sa.name, sa.original, v.c_released, vr.title, vr.original AS t_original, vs.role, vs.note + FROM vn_staff vs + JOIN vn_rev vr ON vr.id = vs.vid + JOIN vn v ON v.latest = vr.id + JOIN staff_alias sa ON vs.aid = sa.id + WHERE sa.rid IN(!l) + ORDER BY v.c_released ASC, vr.title ASC, vs.role ASC|, [ keys %r ] + )}); + push @{$r{ delete $_->{rid} }{cast}}, $_ for (@{$self->dbAll(q| + SELECT sa.rid, vr.vid, sa.name, sa.original, v.c_released, vr.title, vr.original AS t_original, cr.cid, cr.name AS c_name, cr.original AS c_original, vs.note + FROM vn_seiyuu vs + JOIN vn_rev vr ON vr.id = vs.vid + JOIN vn v ON v.latest = vr.id + JOIN chars_rev cr ON cr.cid = vs.cid + JOIN chars c ON c.latest = cr.id + JOIN staff_alias sa ON vs.aid = sa.id + WHERE sa.rid IN(!l) + ORDER BY v.c_released ASC, vr.title ASC|, [ keys %r ] + )}); + } + if ($o{what} =~ /aliases/) { + push @{$r{ delete $_->{rid} }{aliases}}, $_ for (@{$self->dbAll(q| + SELECT sa.id, sa.rid, sa.name, sa.original + FROM staff_alias sa + JOIN staff_rev sr ON sr.id = sa.rid + WHERE sr.id IN(!l) AND sr.aid <> sa.id + ORDER BY sa.name ASC|, [ keys %r ] + )}); + } + } + + return wantarray ? ($r, $np) : $r; +} + +# Updates the edit_* tables, used from dbItemEdit() +# Arguments: { columns in staff_rev and staff_alias}, +sub dbStaffRevisionInsert { + my($self, $o) = @_; + + $self->dbExec('DELETE FROM edit_staff_aliases'); + if ($o->{aid}) { + $self->dbExec(q| + INSERT INTO edit_staff_aliases (id, name, original) VALUES (?, ?, ?)|, + $o->{aid}, $o->{name}, $o->{original}); + } else { + $o->{aid} = $self->dbRow(q| + INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?) RETURNING id|, + $o->{name}, $o->{original})->{id}; + } + + my %staff = map exists($o->{$_}) ? (qq|"$_" = ?|, $o->{$_}) : (), + qw|aid gender lang desc l_wp l_site l_twitter l_anidb|; + $self->dbExec('UPDATE edit_staff !H', \%staff) if %staff; + for my $alias (@{$o->{aliases}}) { + if ($alias->[0]) { + $self->dbExec('INSERT INTO edit_staff_aliases (id, name, original) VALUES (!l)', $alias); + } else { + $self->dbExec('INSERT INTO edit_staff_aliases (name, original) VALUES (?, ?)', + $alias->[1], $alias->[2]); + } + } +} + +1; diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index aaca4da5..b3b55598 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -7,12 +7,12 @@ use Exporter 'import'; use VNDB::Func 'gtintype', 'normalize_query'; use Encode 'decode_utf8'; -our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar|; +our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff dbVNImportSeiyuu|; # Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil, # hasani, hasshot, ul_notblack, ul_onwish, results, page, what, sort, reverse, inc_hidden -# What: extended anime relations screenshots relgraph rating ranking changes wishlist vnlist +# What: extended anime credits relations screenshots relgraph rating ranking changes wishlist vnlist # Note: wishlist and vnlist are ignored (no db search) unless a user is logged in # Sort: id rel pop rating title tagscore rand sub dbVNGet { @@ -142,14 +142,43 @@ sub dbVNGet { $_->{svg} = decode_utf8($_->{svg}) for @$r; } - if(@$r && $o{what} =~ /(anime|relations|screenshots)/) { + if(@$r && $o{what} =~ /anime|relations|screenshots|credits/) { my %r = map { $r->[$_]{anime} = []; + $r->[$_]{credits} = []; + $r->[$_]{seiyuu} = []; $r->[$_]{relations} = []; $r->[$_]{screenshots} = []; ($r->[$_]{cid}, $_) } 0..$#$r; + if($o{what} =~ /credits/) { + push(@{$r->[$r{ delete $_->{vid} }]{credits}}, $_) for (@{$self->dbAll(q| + SELECT vs.vid, s.id, vs.aid, sa.name, sa.original, sr.gender, sr.lang, vs.role, vs.note + FROM vn_staff vs + JOIN staff_alias sa ON vs.aid = sa.id + JOIN staff_rev sr ON sr.id = sa.rid + JOIN staff s ON sr.id = s.latest + WHERE s.hidden = FALSE AND vs.vid IN(!l) + ORDER BY vs.role ASC, sa.name ASC|, + [ keys %r ] + )}); + push(@{$r->[$r{ delete $_->{vid} }]{seiyuu}}, $_) for (@{$self->dbAll(q| + SELECT vs.vid, s.id, vs.aid, sa.name, sa.original, sr.gender, sr.lang, cr.cid, cr.name AS cname, vs.note + FROM vn_seiyuu vs + JOIN vn_rev vr ON vr.id = vs.vid + JOIN staff_alias sa ON vs.aid = sa.id + JOIN staff_rev sr ON sr.id = sa.rid + JOIN staff s ON s.id = sr.sid AND sr.id = s.latest + JOIN chars c ON c.id = vs.cid + JOIN chars_rev cr ON cr.id = c.latest + WHERE s.hidden = FALSE AND vs.vid IN(!l) + AND cr.id IN(SELECT cv.cid FROM chars_vns cv WHERE cv.vid = vr.vid AND cv.cid = cr.id) + ORDER BY cr.name|, + [ keys %r ] + )}); + } + if($o{what} =~ /anime/) { push(@{$r->[$r{$_->{vid}}]{anime}}, $_) && delete $_->{vid} for (@{$self->dbAll(q| SELECT va.vid, a.id, a.year, a.ann_id, a.nfo_id, a.type, a.title_romaji, a.title_kanji, extract('epoch' from a.lastfetch) AS lastfetch @@ -225,6 +254,20 @@ sub dbVNRevisionInsert { my $q = join ',', map '(?)', @{$o->{anime}}; $self->dbExec("INSERT INTO edit_vn_anime (aid) VALUES $q", @{$o->{anime}}) if @{$o->{anime}}; } + + if($o->{credits}) { + $self->dbExec('DELETE FROM edit_vn_staff'); + my $q = join ',', ('(?, ?, ?)') x @{$o->{credits}}; + my @val = map @{$_}[0..2], @{$o->{credits}}; + $self->dbExec("INSERT INTO edit_vn_staff (aid, role, note) VALUES $q", @val) if @val; + } + + if($o->{seiyuu}) { + $self->dbExec('DELETE FROM edit_vn_seiyuu'); + my $q = join ',', ('(?, ?, ?)') x @{$o->{seiyuu}}; + my @val = map @{$_}[0..2], @{$o->{seiyuu}}; + $self->dbExec("INSERT INTO edit_vn_seiyuu (aid, cid, note) VALUES $q", @val) if @val; + } } @@ -291,5 +334,28 @@ sub dbVNHasChar { } +sub dbVNHasStaff { + my($self, $vid) = @_; + return $self->dbRow( + 'SELECT 1 AS exists FROM vn_staff vs FULL OUTER JOIN vn_seiyuu vsy ON vs.vid = vsy.vid JOIN vn v ON v.latest = vs.vid OR v.latest = vsy.vid WHERE v.id = ?', $vid + )->{exists}; +} + + +# returns seiyuus that voice characters referenced by $cids in VNs other than $vid +sub dbVNImportSeiyuu { + my($self, $vid, $cids) = @_; + return $self->dbAll(q| + SELECT DISTINCT ON(cr.cid) cr.cid, cr.name AS c_name, s.id AS sid, sa.id AS aid, sa.name + FROM vn_seiyuu vs + JOIN vn v ON v.latest = vs.vid + JOIN chars c ON c.id = vs.cid + JOIN chars_rev cr ON cr.id = c.latest + JOIN staff_alias sa ON sa.id = vs.aid + JOIN staff s ON sa.rid = s.latest + WHERE vs.cid IN(!l) AND v.id <> ?|, $cids, $vid); +} + + 1; diff --git a/lib/VNDB/Func.pm b/lib/VNDB/Func.pm index 54acfe4f..ac9fdd58 100644 --- a/lib/VNDB/Func.pm +++ b/lib/VNDB/Func.pm @@ -6,10 +6,12 @@ use warnings; use TUWF ':html'; use Exporter 'import'; use POSIX 'strftime', 'ceil', 'floor'; +use JSON::XS; use VNDBUtil; our @EXPORT = (@VNDBUtil::EXPORT, qw| clearfloat cssicon tagscore mt minage fil_parse fil_serialize parenttags childtags charspoil imgpath imgurl fmtvote + jsonEncode jsonDecode script_json mtvoiced mtani mtvnlen mtrlstat mtvnlstat mtbloodt |); @@ -202,6 +204,30 @@ sub fmtvote { } +# JSON::XS::encode_json converts input to utf8, whereas the below functions +# operate on wide character strings. Canonicalization is enabled to allow for +# proper comparison of serialized objects. +my $JSON = JSON::XS->new; +$JSON->canonical(1); + +sub jsonEncode ($) { + $JSON->encode(@_); +} + +sub jsonDecode ($) { + $JSON->decode(@_); +} + +# Insert JSON-encoded data as script, arguments: id, object +sub script_json { + script id => $_[0], type => 'application/json'; + my $js = jsonEncode $_[1]; + $js =~ s/</\\u003C/g; # escape HTML tags like </script> and <!-- + lit $js; + end; +} + + # mt() wrappers for data-dependent translation strings that have a special # value for 'unknown'. sub mtvoiced { !$_[0] ? mt '_unknown' : mt '_voiced_'.$_[0]; } diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm index 7ae95e43..fcd8203f 100644 --- a/lib/VNDB/Handler/Chars.pm +++ b/lib/VNDB/Handler/Chars.pm @@ -22,7 +22,7 @@ sub page { my $r = $self->dbCharGet( id => $id, - what => 'extended traits vns'.($rev ? ' changes' : ''), + what => 'extended traits vns seiyuu'.($rev ? ' changes' : ''), $rev ? ( rev => $rev ) : () )->[0]; return $self->resNotFound if !$r->{id}; @@ -226,6 +226,22 @@ sub charTable { end; } + if(@{$r->{seiyuu}}) { + Tr; + td class => 'key', mt '_charp_voice'; + td; + my $last_name = ''; + for my $s (sort { $a->{name} cmp $b->{name} } @{$r->{seiyuu}}) { + next if $s->{name} eq $last_name; + a href => "/s$s->{sid}", title => $s->{original}||$s->{name}, $s->{name}; + txt ' ('.$s->{note}.')' if $s->{note}; + br; + $last_name = $s->{name}; + } + end; + end; + } + # description if($r->{desc}) { Tr class => 'nostripe'; diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index 357779bc..7505028a 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -11,7 +11,7 @@ use POSIX 'strftime'; TUWF::register( qr{}, \&homepage, - qr{(?:([upvrc])([1-9]\d*)/)?hist}, \&history, + qr{(?:([upvrcs])([1-9]\d*)/)?hist},\&history, qr{d([1-9]\d*)}, \&docpage, qr{setlang}, \&setlang, qr{nospam}, \&nospam, @@ -195,7 +195,7 @@ sub history { { get => 'p', required => 0, default => 1, template => 'int' }, { get => 'm', required => 0, default => !$type, enum => [ 0, 1 ] }, { get => 'h', required => 0, default => 0, enum => [ -1..1 ] }, - { get => 't', required => 0, default => '', enum => [qw|v r p c a|] }, + { get => 't', required => 0, default => '', enum => [qw|v r p c s a|] }, { get => 'e', required => 0, default => 0, enum => [ -1..1 ] }, { get => 'r', required => 0, default => 0, enum => [ 0, 1 ] }, ); @@ -206,6 +206,7 @@ sub history { $type eq 'p' ? $self->dbProducerGet(id => $id)->[0] : $type eq 'r' ? $self->dbReleaseGet(id => $id)->[0] : $type eq 'c' ? $self->dbCharGet(id => $id)->[0] : + $type eq 's' ? $self->dbStaffGet(id => $id)->[0] : $type eq 'v' ? $self->dbVNGet(id => $id)->[0] : undef; my $title = mt $type ? ('_hist_title_item', $obj->{title} || $obj->{name} || $obj->{username}) : '_hist_title'; return $self->resNotFound if $type && !$obj->{id}; @@ -260,6 +261,7 @@ sub history { a $f->{t} eq 'v' ? (class => 'optselected') : (), href => $u->(t => 'v'), mt '_hist_filter_onlyvn'; a $f->{t} eq 'r' ? (class => 'optselected') : (), href => $u->(t => 'r'), mt '_hist_filter_onlyreleases'; a $f->{t} eq 'p' ? (class => 'optselected') : (), href => $u->(t => 'p'), mt '_hist_filter_onlyproducers'; + a $f->{t} eq 's' ? (class => 'optselected') : (), href => $u->(t => 's'), mt '_hist_filter_onlystaff'; a $f->{t} eq 'c' ? (class => 'optselected') : (), href => $u->(t => 'c'), mt '_hist_filter_onlychars'; a $f->{t} eq 'a' ? (class => 'optselected') : (), href => $u->(t => 'a'), mt '_hist_filter_nochars'; end; diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm new file mode 100644 index 00000000..008dbf96 --- /dev/null +++ b/lib/VNDB/Handler/Staff.pm @@ -0,0 +1,414 @@ + +package VNDB::Handler::Staff; + +use strict; +use warnings; +use TUWF qw(:html :xml xml_escape); +use VNDB::Func; +use List::Util qw(first); + +TUWF::register( + qr{s([1-9]\d*)(?:\.([1-9]\d*))?} => \&page, + qr{s(?:([1-9]\d*)(?:\.([1-9]\d*))?/edit|/new)} + => \&edit, + qr{s/([a-z0]|all)} => \&list, + qr{xml/staff\.xml} => \&staffxml, +); + +sub page { + my($self, $id, $rev) = @_; + + my $s = $self->dbStaffGet( + id => $id, + what => 'extended aliases roles'.($rev ? ' changes' : ''), + $rev ? ( rev => $rev ) : () + )->[0]; + return $self->resNotFound if !$s->{id}; + + $self->htmlHeader(title => $s->{name}, noindex => $rev); + $self->htmlMainTabs('s', $s) if $id; + return if $self->htmlHiddenMessage('s', $s); + + if($rev) { + my $prev = $rev && $rev > 1 && $self->dbStaffGet(id => $id, rev => $rev-1, what => 'changes extended aliases')->[0]; + $self->htmlRevision('s', $prev, $s, + [ name => diff => 1 ], + [ original => diff => 1 ], + [ gender => serialize => sub { mt "_gender_$_[0]" } ], + [ lang => serialize => sub { "$_[0] (".mt("_lang_$_[0]").')' } ], + [ l_site => diff => 1 ], + [ l_wp => htmlize => sub { + $_[0] ? sprintf '<a href="http://en.wikipedia.org/wiki/%s">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink' + }], + [ l_twitter => diff => 1 ], + [ l_anidb => serialize => sub { $_[0] // '' } ], + [ desc => diff => qr/[ ,\n\.]/ ], + [ aliases => join => '<br />', split => sub { + map xml_escape(sprintf('%s%s', $_->{name}, $_->{original} ? ' ('.$_->{original}.')' : '')), @{$_[0]}; + }], + ); + } + + div class => 'mainbox staffpage'; + $self->htmlItemMessage('s', $s); + div class => 'staffinfo'; + h1 $s->{name}; + h2 class => 'alttitle'; + span style => 'margin-right: 10px', $s->{original} if $s->{original}; + cssicon "gen $s->{gender}", mt "_gender_$s->{gender}" if $s->{gender} ne 'unknown'; + end; + + # info table + table class => 'stripe'; + + Tr; + td class => 'key', mt '_staff_language'; + td mt "_lang_$s->{lang}"; + end; + if (@{$s->{aliases}}) { + Tr; + td class => 'key', mt('_staff_aliases', scalar @{$s->{aliases}}); + td; + table class => 'aliases'; + foreach my $alias (@{$s->{aliases}}) { + Tr class => 'nostripe'; + td $alias->{original} ? () : (colspan => 2), class => 'key'; + txt $alias->{name}; + end; + td $alias->{original} if $alias->{original}; + end; + } + end; + end; + end; + } + my @links = ( + $s->{l_site} ? [ 'site', $s->{l_site} ] : (), + $s->{l_wp} ? [ 'wp', "http://en.wikipedia.org/wiki/$s->{l_wp}" ] : (), + $s->{l_twitter} ? [ 'twitter', "https://twitter.com/$s->{l_twitter}" ] : (), + $s->{l_anidb} ? [ 'anidb', "http://anidb.net/cr$s->{l_anidb}" ] : (), + ); + if(@links) { + Tr; + td class => 'key', mt '_staff_links'; + td; + for(@links) { + a href => $_->[1], mt "_staff_l_$_->[0]"; + br if $_ != $links[$#links]; + } + end; + end; + } + end 'table'; + end; + + # description + div class => 'staffdesc'; + if($s->{desc}) { + h2 mt '_staff_bio'; + p; + lit bb2html $s->{desc}, 0, 1; + end; + br; + } + + if (@{$s->{roles}}) { + h2 mt '_staff_credits'; + my $has_notes = first { $_->{note} || $_->{name} ne $s->{name} } @{$s->{roles}}; + table class => 'stripe staffroles'; + thead; + Tr; + td class => 'tc2', mt '_staff_col_title'; + td class => 'tc3', mt '_staff_col_released'; + td class => 'tc1', mt '_staff_col_role'; + td class => 'tc4', mt '_staff_col_note' if $has_notes; + end; + end; + tbody; + my($last_vid, $row_count) = (0); + for my $i (0..$#{$s->{roles}}) { + my $r = $s->{roles}->[$i]; + if($r->{vid} != $last_vid) { + $row_count = 1; + for my $j (1+$i..$#{$s->{roles}}) { + last if $r->{vid} != $s->{roles}->[$j]->{vid}; + ++$row_count; + } + } + Tr; + if($last_vid != $r->{vid}) { + td class => 'tc2', $row_count > 1 ? (rowspan => $row_count) : (); + a href => "/v$r->{vid}", title => $r->{t_original}||$r->{title}, shorten $r->{title}, 100; + end; + td class => 'tc3', $row_count > 1 ? (rowspan => $row_count) : (); + lit $self->{l10n}->datestr($r->{c_released}); + end; + } + td class => 'tc1', mt '_credit_'.$r->{role}; + if($has_notes) { + td class => 'tc4'; + txt '('.mt('_staff_as', $r->{name}).') ' if $r->{name} ne $s->{name}; + txt $r->{note}; + end; + } + end; + $last_vid = $r->{vid}; + } + end; + end; + br; + } + if (@{$s->{cast}}) { + h2 mt '_staff_voiced'; + my $has_notes = first { $_->{note} || $_->{name} ne $s->{name} } @{$s->{cast}}; + table class => 'stripe staffroles'; + thead; + Tr; + td class => 'tc2', mt '_staff_col_title'; + td class => 'tc3', mt '_staff_col_released'; + td class => 'tc1', mt '_staff_col_cast'; + td class => 'tc4', mt '_staff_col_note' if $has_notes; + end; + end; + tbody; + foreach my $r (@{$s->{cast}}) { + Tr; + td class => 'tc2'; + a href => "/v$r->{vid}", title => $r->{t_original}||$r->{title}, shorten $r->{title}, 100; + end; + td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end; + td class => 'tc1'; a href => "/c$r->{cid}", title => $r->{c_original}, $r->{c_name}; end; + if($has_notes) { + td class => 'tc4'; + txt '('.mt('_staff_as', $r->{name}).') ' if $r->{name} ne $s->{name}; + txt $r->{note}; + end; + } + end; + } + end; + end; + } + end; + clearfloat; + end; + + $self->htmlFooter; +} + + +sub edit { + my($self, $sid, $rev) = @_; + + my $s = $sid && $self->dbStaffGet(id => $sid, what => 'changes extended aliases', $rev ? (rev => $rev) : ())->[0]; + return $self->resNotFound if $sid && !$s->{id}; + $rev = undef if !$s || $s->{cid} == $s->{latest}; + + return $self->htmlDenied if !$self->authCan('staffedit') + || $sid && (($s->{locked} || $s->{hidden}) && !$self->authCan('dbmod')); + + my %b4 = !$sid ? () : ( + (map { $_ => $s->{$_} } qw|name original gender lang desc l_wp l_site l_twitter l_anidb ihid ilock|), + aliases => jsonEncode [ + map +{ aid => $_->{id}, name => $_->{name}, orig => $_->{original} }, + sort { $a->{name} cmp $b->{name} } @{$s->{aliases}} + ], + ); + my $frm; + + if ($self->reqMethod eq 'POST') { + return if !$self->authCheckCode; + $frm = $self->formValidate ( + { post => 'name', maxlength => 200 }, + { post => 'original', required => 0, maxlength => 200, default => '' }, + { post => 'desc', required => 0, maxlength => 5000, default => '' }, + { post => 'gender', required => 0, default => 'unknown', enum => [qw|unknown m f|] }, + { post => 'lang', enum => $self->{languages} }, + { post => 'l_wp', required => 0, maxlength => 150, default => '' }, + { post => 'l_site', required => 0, template => 'url', maxlength => 250, default => '' }, + { post => 'l_twitter', required => 0, maxlength => 16, default => '', regex => [ qr/^\S+$/, mt('_staffe_form_tw_err') ] }, + { post => 'l_anidb', required => 0, template => 'int', default => undef }, + { post => 'aliases', required => 0, maxlength => 5000, default => '' }, + { post => 'editsum', required => 0, maxlength => 5000 }, + { post => 'ihid', required => 0 }, + { post => 'ilock', required => 0 }, + ); + push @{$frm->{_err}}, 'badeditsum' if !$frm->{editsum} || lc($frm->{editsum}) eq lc($frm->{desc}); + + my @aliases; + my $raw_a = eval { jsonDecode $frm->{aliases} }; + push @{$frm->{_err}}, [ 'aliases', 'template', 'json' ] if $@ || ref $raw_a ne 'ARRAY'; + if(!$frm->{_err}) { + my %old_aliases = $sid ? ( map +($_->{id} => 1), @{$s->{aliases}} ) : (); + for my $a (sort { $a->{name} cmp $b->{name} } @$raw_a) { + # check for empty aliases + if($a->{name} =~ /^\s*$/) { + push @{$frm->{_err}}, ['alias_name', 'required']; + last; + } + # normalize alias id to a number so that the comparison works + # or reset it to zero for newly added aliases. + $a->{aid} *= $old_aliases{$a->{aid}} ? 1 : 0; + push @aliases, $a; + } + } + if(!$frm->{_err}) { + $frm->{aliases} = jsonEncode \@aliases; + $frm->{ihid} = $frm->{ihid} ?1:0; + $frm->{ilock} = $frm->{ilock}?1:0; + $frm->{aid} = $s->{aid} if $sid; + + return $self->resRedirect("/s$sid", 'post') + if $sid && !first { ($frm->{$_}//'') ne ($b4{$_}//'') } keys %b4; + + $frm->{aliases} = [ map [ @{$_}{qw|aid name orig|} ], @aliases ]; + my $nrev = $self->dbItemEdit ('s' => $sid ? $s->{cid} : undef, %$frm); + return $self->resRedirect("/s$nrev->{iid}.$nrev->{rev}", 'post'); + } + } + + $frm->{$_} //= $b4{$_} for keys %b4; + $frm->{editsum} //= sprintf 'Reverted to revision s%d.%d', $sid, $rev if $rev; + $frm->{lang} = 'ja' if !$sid && !defined $frm->{lang}; + + my $title = mt $s ? ('_staffe_title_edit', $s->{name}) : '_staffe_title_add'; + $self->htmlHeader(title => $title, noindex => 1); + $self->htmlMainTabs('s', $s, 'edit') if $s; + $self->htmlEditMessage('s', $s, $title); + $self->htmlForm({ frm => $frm, action => $s ? "/s$sid/edit" : '/s/new', editsum => 1 }, + staffe_geninfo => [ mt('_staffe_form_generalinfo'), + [ input => name => mt('_staffe_form_name'), short => 'name' ], + [ input => name => mt('_staffe_form_original'), short => 'original' ], + [ static => content => mt('_staffe_form_original_note') ], + [ text => name => mt('_staffe_form_note').'<br /><b class="standout">'.mt('_inenglish').'</b>', short => 'desc', rows => 4 ], + [ select => name => mt('_staffe_form_gender'),short => 'gender', options => [ + map [ $_, mt("_gender_$_") ], qw(unknown m f) ] ], + [ select => name => mt('_staffe_form_lang'), short => 'lang', + options => [ map [ $_, "$_ (".mt("_lang_$_").')' ], sort @{$self->{languages}} ] ], + [ input => name => mt('_staffe_form_site'), short => 'l_site' ], + [ input => name => mt('_staffe_form_wikipedia'), short => 'l_wp', pre => 'http://en.wikipedia.org/wiki/' ], + [ input => name => mt('_staffe_form_twitter'), short => 'l_twitter' ], + [ input => name => mt('_staffe_form_anidb'), short => 'l_anidb' ], + [ static => content => '<br />' ], + ], + + staffe_aliases => [ mt('_staffe_aliases'), + [ hidden => short => 'aliases' ], + [ static => nolabel => 1, content => sub { + table; + thead; Tr; + td class => 'tc_name', mt '_staffe_form_alias'; + td class => 'tc_original', mt '_staffe_form_original_alias'; td; end; + end; end; + tbody id => 'alias_tbl'; + # filled with javascript + end; + end; + h2 mt '_staffe_aliases_add'; + table; Tr id => 'alias_new'; + td class => 'tc_name'; + input id => 'alias_name', type => 'text', class => 'text'; end; + td class => 'tc_original'; + input id => 'alias_original', type => 'text', class => 'text'; end; + td class => 'tc_add'; + a href => '#', mt '_js_add'; end; + end; end; + }], + ]); + + $self->htmlFooter; +} + + +sub list { + my ($self, $char) = @_; + + my $f = $self->formValidate( + { get => 'p', required => 0, default => 1, template => 'int' }, + { get => 'q', required => 0, default => '' }, + ); + return $self->resNotFound if $f->{_err}; + + my ($list, $np) = $self->dbStaffGet( + $char ne 'all' ? ( char => $char ) : (), + $f->{q} ? ( search => $f->{q} ) : (), + results => 150, + page => $f->{p} + ); + + return $self->resRedirect('/s'.$list->[0]{id}, 'temp') + if $f->{q} && @$list == 1 && $f->{p} == 1; + + $self->htmlHeader(title => mt '_sbrowse_title'); + + div class => 'mainbox'; + h1 mt '_sbrowse_title'; + form action => '/s/all', 'accept-charset' => 'UTF-8', method => 'get'; + $self->htmlSearchBox('s', $f->{q}); + end; + p class => 'browseopts'; + for ('all', 'a'..'z', 0) { + a href => "/s/$_", $_ eq $char ? (class => 'optselected') : (), $_ eq 'all' ? mt('_char_all') : $_ ? uc $_ : '#'; + } + end; + end; + + my $pageurl = "/s/$char" . ($f->{q} ? "?q=$f->{q}" : ''); + $self->htmlBrowseNavigate($pageurl, $f->{p}, $np, 't'); + div class => 'mainbox staffbrowse'; + h1 mt $f->{q} ? '_sbrowse_searchres' : '_sbrowse_list'; + if(!@$list) { + p mt '_sbrowse_noresults'; + } else { + # spread the results over 3 equivalent-sized lists + my $perlist = @$list/3 < 1 ? 1 : @$list/3; + for my $c (0..(@$list < 3 ? $#$list : 2)) { + ul; + for ($perlist*$c..($perlist*($c+1))-1) { + li; + my $gender = $list->[$_]{gender}; + cssicon 'lang '.$list->[$_]{lang}, mt "_lang_$list->[$_]{lang}"; + a href => "/s$list->[$_]{id}", + title => $list->[$_]{original}, $list->[$_]{name}; + end; + } + end; + } + } + clearfloat; + end 'div'; + $self->htmlBrowseNavigate($pageurl, $f->{p}, $np, 'b'); + $self->htmlFooter; +} + + +sub staffxml { + my $self = shift; + + my $q = $self->formValidate( + { get => 'a', required => 0, multi => 1, template => 'int' }, + { get => 's', required => 0, multi => 1, template => 'int' }, + { get => 'q', required => 0, maxlength => 500 }, + ); + return $self->resNotFound if $q->{_err} || !(@{$q->{s}} || @{$q->{a}} || $q->{q}); + + my($list, $np) = $self->dbStaffGet( + @{$q->{s}} ? (id => $q->{s}) : + @{$q->{a}} ? (aid => $q->{a}) : + $q->{q} =~ /^s([1-9]\d*)/ ? (id => $1) : + (search => $q->{q}), + results => 10, + page => 1, + ); + + $self->resHeader('Content-type' => 'text/xml; charset=UTF-8'); + xml; + tag 'staff', more => $np ? 'yes' : 'no'; + for(@$list) { + tag 'item', id => $_->{id}, aid => $_->{aid}, $_->{name}; + } + end; +} + +1; +__END__ diff --git a/lib/VNDB/Handler/VNBrowse.pm b/lib/VNDB/Handler/VNBrowse.pm index 7af01048..3323722c 100644 --- a/lib/VNDB/Handler/VNBrowse.pm +++ b/lib/VNDB/Handler/VNBrowse.pm @@ -45,7 +45,7 @@ sub list { $f->{wish} = $read_write_pref->('wish', 'vn_list_wish'); return $self->resRedirect('/'.$1.$2.(!$3 ? '' : $1 eq 'd' ? '#'.$3 : '.'.$3), 'temp') - if $f->{q} && $f->{q} =~ /^([gvrptudci])([0-9]+)(?:\.([0-9]+))?$/; + if $f->{q} && $f->{q} =~ /^([gvrptudcis])([0-9]+)(?:\.([0-9]+))?$/; $f->{s} = 'title' if $f->{fil} !~ /tag_inc-/ && $f->{s} eq 'tagscore'; $f->{o} = $f->{s} eq 'tagscore' ? 'd' : 'a' if !$f->{o}; diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index 13877fa2..3fe76c07 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -77,7 +77,7 @@ sub addform { sub edit { my($self, $vid, $rev, $nosubmit) = @_; - my $v = $vid && $self->dbVNGet(id => $vid, what => 'extended screenshots relations anime changes', $rev ? (rev => $rev) : ())->[0]; + my $v = $vid && $self->dbVNGet(id => $vid, what => 'extended screenshots relations anime credits changes', $rev ? (rev => $rev) : ())->[0]; return $self->resNotFound if $vid && !$v->{id}; $rev = undef if !$vid || $v->{cid} == $v->{latest}; @@ -85,9 +85,18 @@ sub edit { || $vid && (($v->{locked} || $v->{hidden}) && !$self->authCan('dbmod')); my $r = $v ? $self->dbReleaseGet(vid => $v->{id}) : []; + my $chars = $v ? $self->dbCharGet(vid => $v->{id}, results => 100) : []; my %b4 = !$vid ? () : ( (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai image img_nsfw ihid ilock|), + credits => jsonEncode [ + map { my $c = $_; +{ map { $_ => $c->{$_} } qw|aid role note| } } + sort { $a->{aid} <=> $b->{aid} || $a->{role} cmp $b->{role} } @{$v->{credits}} + ], + seiyuu => jsonEncode [ + map { my $c = $_; +{ map { $_ => $c->{$_} } qw|aid cid note| } } + sort { $a->{aid} <=> $b->{aid} || $a->{cid} <=> $b->{cid} } @{$v->{seiyuu}} + ], anime => join(' ', sort { $a <=> $b } map $_->{id}, @{$v->{anime}}), vnrelations => join('|||', map $_->{relation}.','.$_->{id}.','.($_->{official}?1:0).','.$_->{title}, sort { $a->{id} <=> $b->{id} } @{$v->{relations}}), screenshots => join(' ', map sprintf('%d,%d,%d', $_->{id}, $_->{nsfw}?1:0, $_->{rid}), @{$v->{screenshots}}), @@ -108,6 +117,8 @@ sub edit { { post => 'anime', required => 0, default => '' }, { post => 'image', required => 0, default => 0, template => 'int' }, { post => 'img_nsfw', required => 0, default => 0 }, + { post => 'credits', required => 0, default => '', maxlength => 5000 }, + { post => 'seiyuu', required => 0, default => '', maxlength => 5000 }, { post => 'vnrelations', required => 0, default => '', maxlength => 5000 }, { post => 'screenshots', required => 0, default => '', maxlength => 1000 }, { post => 'editsum', required => 0, maxlength => 5000 }, @@ -120,6 +131,44 @@ sub edit { # handle image upload $frm->{image} = _uploadimage($self, $frm) if !$nosubmit; + my (@credits, @seiyuu); + if(!$nosubmit && !$frm->{_err}) { + eval { # catch json decoding errors + my $raw_c = $frm->{credits} ? jsonDecode $frm->{credits} : []; + my $raw_s = $frm->{seiyuu} ? jsonDecode $frm->{seiyuu} : []; + + # ensure submitted alias IDs exist within database + my @alist = map $_->{aid}, @$raw_c, @$raw_s; + my %staff = @alist ? map +($_->{aid} => 1), @{$self->dbStaffGet(aid => \@alist, results => 200)} : (); + return unless %staff; # exit from the eval block if staff list is empty + + # check for duplicate credits + my $last_c = { aid => 0, role => '' }; + for my $c (sort { $a->{aid} <=> $b->{aid} || $a->{role} cmp $b->{role} } @$raw_c) { + next unless exists $staff{$c->{aid}}; + # discard entries with identical name & role + next if $last_c->{aid} == $c->{aid} && $last_c->{role} eq $c->{role}; + $c->{aid} += 0; + push @credits, $c; + $last_c = $c; + } + + # if character list is empty, any seiyuu data will be discarded + if (@$chars && @$raw_s) { + my %vn_chars = map +($_->{id} => 1), @$chars; + my $last_s = { aid => 0, cid => 0 }; + for my $s (sort { $a->{aid} <=> $b->{aid} || $a->{cid} <=> $b->{cid} } @$raw_s) { + next unless $staff{$s->{aid}} && $vn_chars{$s->{cid}}; # weed out odd credits + next if $last_s->{aid} == $s->{aid} && $last_s->{cid} == $s->{cid}; + $s->{cid} += 0; # force numeric conversion + $s->{aid} += 0; + push @seiyuu, $s; + $last_s = $s; + } + } + }; + push @{$frm->{_err}}, [ 'credits', 'template', 'json' ] if $@; + } if(!$nosubmit && !$frm->{_err}) { # parse and re-sort fields that have multiple representations of the same information my $anime = { map +($_=>1), grep /^[0-9]+$/, split /[ ,]+/, $frm->{anime} }; @@ -133,6 +182,8 @@ sub edit { $frm->{vnrelations} = join '|||', map $_->[0].','.$_->[1].','.($_->[2]?1:0).','.$_->[3], sort { $a->[1] <=> $b->[1]} @{$relations}; $frm->{img_nsfw} = $frm->{img_nsfw} ? 1 : 0; $frm->{screenshots} = join ' ', map sprintf('%d,%d,%d', $_->[0], $_->[1]?1:0, $_->[2]), sort { $a->[0] <=> $b->[0] } @$screenshots; + $frm->{credits} = jsonEncode \@credits; + $frm->{seiyuu} = jsonEncode \@seiyuu; # weed out duplicate aliases my %alias; @@ -149,6 +200,8 @@ sub edit { # perform the edit/add my $nrev = $self->dbItemEdit(v => $vid ? $v->{cid} : undef, (map { $_ => $frm->{$_} } qw|title original image alias desc length l_wp l_encubed l_renai editsum img_nsfw ihid ilock|), + credits => [ map [ @{$_}{qw|aid role note|} ], @credits ], + seiyuu => [ map [ @{$_}{qw|aid cid note|} ], @seiyuu ], anime => [ keys %$anime ], relations => $relations, screenshots => $screenshots, @@ -172,7 +225,7 @@ sub edit { $self->htmlHeader(title => $title, noindex => 1); $self->htmlMainTabs('v', $v, 'edit') if $vid; $self->htmlEditMessage('v', $v, $title); - _form($self, $v, $frm, $r); + _form($self, $v, $frm, $r, $chars); $self->htmlFooter; } @@ -212,7 +265,8 @@ sub _uploadimage { sub _form { - my($self, $v, $frm, $r) = @_; + my($self, $v, $frm, $r, $chars) = @_; + my $import = @$chars ? $self->dbVNImportSeiyuu($v->{id}, [ map $_->{id}, @$chars ]) : []; $self->htmlForm({ frm => $frm, action => $v ? "/v$v->{id}/edit" : '/v/new', editsum => 1, upload => 1 }, vn_geninfo => [ mt('_vnedit_geninfo'), [ input => short => 'title', name => mt '_vnedit_frm_title' ], @@ -258,6 +312,64 @@ sub _form { end 'div'; }]], + vn_staff => [ mt('_vnedit_staff'), + [ hidden => short => 'credits' ], + [ static => nolabel => 1, content => sub { + # propagate staff ids and names to javascript + my %staff_data; + for my $c (@{$v->{credits}}, @{$v->{seiyuu}}) { + $staff_data{$c->{aid}} //= { map +($_ => $c->{$_}), qw|id aid name| }; + } + script_json staffdata => \%staff_data if %staff_data; + + div class => 'warning'; + lit mt '_vnedit_staff_msg'; + end; + br; + table; tbody id => 'credits_tbl'; + Tr id => 'credits_loading'; td colspan => '4', mt('_js_loading'); end; + end; end; + h2 mt '_vnstaffe_add'; + table; Tr; + td class => 'tc_staff'; + input id => 'credit_input', type => 'text', class => 'text'; end; + td colspan => 3, ''; + end; end; + }]], + + # Cast tab is only shown for VNs with some characters listed. + # There's no way to add voice actors in new VN edits since character list + # would be empty anyway. + @{$chars} ? (vn_cast => [ mt('_vnedit_cast'), + [ hidden => short => 'seiyuu' ], + [ static => nolabel => 1, content => sub { + if (@$import) { + script_json castimpdata => [ + map { my $c = $_; +{ map { $_ => $c->{$_} } qw|cid sid aid name| } } @$import + ]; + div id => 'cast_import'; + a href => '#', title => mt('_vnedit_cast_import_title'), mt '_vnedit_cast_import'; + end; + } + table; tbody id => 'cast_tbl'; + Tr id => 'cast_loading'; td colspan => '4', mt '_js_loading'; end; + end; end; + h2 mt '_vnedit_cast_add'; + table; Tr; + td class => 'tc_char'; + Select id =>'cast_chars'; + option value => '', mt '_vnedit_cast_sel_char'; + option value => $_->{id}, $_->{name} for @{$chars}; + end; + txt ' '.mt '_vnedit_voiced_by'; + end; + td class => 'tc_staff'; + input id => 'cast_input', type => 'text', class => 'text'; + end; + td colspan => 2, ''; + end; end; + }]]) : (), + vn_rel => [ mt('_vnedit_rel'), [ hidden => short => 'vnrelations' ], [ static => nolabel => 1, content => sub { diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm index 0f8f71ba..f75d02d7 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -13,7 +13,7 @@ TUWF::register( qr{v/rand} => \&rand, qr{v([1-9]\d*)/rg} => \&rg, qr{v([1-9]\d*)/releases} => \&releases, - qr{v([1-9]\d*)/(chars)} => \&page, + qr{v([1-9]\d*)/(chars|staff)} => \&page, qr{v([1-9]\d*)(?:\.([1-9]\d*))?} => \&page, ); @@ -513,11 +513,12 @@ sub page { my($self, $vid, $rev) = @_; my $char = $rev && $rev eq 'chars'; - $rev = undef if $char; + my $staff = $rev && $rev eq 'staff'; + $rev = undef if $char || $staff; my $v = $self->dbVNGet( id => $vid, - what => 'extended anime relations screenshots rating ranking'.($rev ? ' changes' : ''), + what => 'extended anime relations screenshots rating ranking'.($staff || $rev ? ' credits' : '').($rev ? ' changes' : ''), $rev ? (rev => $rev) : (), )->[0]; return $self->resNotFound if !$v->{id}; @@ -646,26 +647,39 @@ sub page { end 'div'; # /mainbox my $haschar = $self->dbVNHasChar($v->{id}); - if($haschar || $self->authCan('edit')) { + my $hasstaff = $self->dbVNHasStaff($v->{id}); + if($haschar || $hasstaff || $self->authCan('edit')) { clearfloat; # fix tabs placement when tags are hidden ul class => 'maintabs notfirst'; - if($haschar) { - li class => 'left '.(!$char ? ' tabselected' : ''); a href => "/v$v->{id}#main", name => 'main', mt '_vnpage_tab_main'; end; - li class => 'left '.( $char ? ' tabselected' : ''); a href => "/v$v->{id}/chars#chars", name => 'chars', mt '_vnpage_tab_chars'; end; + if($haschar || $hasstaff) { + li class => 'left '.(!($char || $staff) && ' tabselected'); a href => "/v$v->{id}#main", name => 'main', mt '_vnpage_tab_main'; end; + if ($haschar) { + li class => 'left '.($char ? ' tabselected' : ''); a href => "/v$v->{id}/chars#chars", name => 'chars', mt '_vnpage_tab_chars'; end; + } + if ($hasstaff) { + li class => 'left '.($staff ? ' tabselected' : ''); a href => "/v$v->{id}/staff#staff", name => 'staff', mt '_vnpage_tab_staff'; end; + } } if($self->authCan('edit')) { li; a href => "/c/new?vid=$v->{id}", mt '_vnpage_char_add'; end; + if(!$v->{locked}) { + li; + a href => "/v$v->{id}/edit#vn_staff", mt $hasstaff ? '_vnpage_staff_edit' : '_vnpage_staff_add'; + end; + } li; a href => "/v$v->{id}/add", mt '_vnpage_rel_add'; end; } end; } - if(!$char) { + if($char) { + _chars($self, $haschar, $v); + } elsif ($staff) { + _staff($self, $v) if $hasstaff; + } else { _releases($self, $v, $r); _stats($self, $v); _screenshots($self, $v, $r) if @{$v->{screenshots}}; - } else { - _chars($self, $haschar, $v); } $self->htmlFooter; @@ -677,7 +691,7 @@ sub _revision { return if !$rev; my $prev = $rev && $rev > 1 && $self->dbVNGet( - id => $v->{id}, rev => $rev-1, what => 'extended anime relations screenshots changes' + id => $v->{id}, rev => $rev-1, what => 'extended anime relations screenshots credits changes' )->[0]; $self->htmlRevision('v', $prev, $v, @@ -695,6 +709,19 @@ sub _revision { [ l_renai => htmlize => sub { $_[0] ? sprintf '<a href="http://renai.us/game/%s.shtml">%1$s</a>', xml_escape $_[0] : mt '_revision_nolink' }], + [ credits => join => '<br />', split => sub { + my @r = map sprintf('<a href="/s%d" title="%s">%s</a> [%s]%s', + $_->{id}, xml_escape($_->{original}||$_->{name}), xml_escape($_->{name}), mt("_credit_$_->{role}"), $_->{note} ? ' ['.xml_escape(shorten($_->{note}, 20)).']' : ''), sort { $a->{id} <=> $b->{id} } @{$_[0]}; + return @r ? @r : (mt '_revision_empty'); + }], + [ seiyuu => join => '<br />', split => sub { + my @r = map sprintf('<a href="/s%d" title="%s">%s</a> %s%s', + $_->{id}, xml_escape($_->{original}||$_->{name}), xml_escape($_->{name}), + mt('_staff_as', xml_escape($_->{cname})), + $_->{note} ? ' ['.xml_escape(shorten($_->{note}, 20)).']' : ''), + sort { $a->{id} <=> $b->{id} } @{$_[0]}; + return @r ? @r : (mt '_revision_empty'); + }], [ relations => join => '<br />', split => sub { my @r = map sprintf('[%s] %s: <a href="/v%d" title="%s">%s</a>', mt($_->{official} ? '_vndiff_rel_official' : '_vndiff_rel_unofficial'), @@ -1040,7 +1067,7 @@ sub _stats { sub _chars { my($self, $has, $v) = @_; - my $l = $has && $self->dbCharGet(vid => $v->{id}, what => "extended vns($v->{id}) traits", results => 100); + my $l = $has && $self->dbCharGet(vid => $v->{id}, what => "extended vns($v->{id}) seiyuu traits", results => 100); return if !$has; # TODO: spoiler handling + hide unimportant roles by default my %done; @@ -1071,5 +1098,62 @@ sub _chars { } +sub _staff { + my ($self, $v) = @_; + if(@{$v->{credits}}) { + div class => 'mainbox staff'; + h1 mt '_vnpage_staff'; + my $has_notes = grep { $_->{note} } @{$v->{credits}}; + table class => 'stripe'; + thead; + Tr; + td class => 'tc1', mt '_staff_col_role'; + td class => 'tc2', mt '_staff_col_credit'; + td class => 'tc3', mt '_staff_col_note' if $has_notes; + end; + end; + my $last_role = ''; + for my $s (@{$v->{credits}}) { + Tr; + td class => 'tc1', $s->{role} ne $last_role ? mt '_credit_'.$s->{role} : ''; + td class => 'tc2'; + a href => "/s$s->{id}", title => $s->{original}||$s->{name}, $s->{name}; + end; + td class => 'tc3', $s->{note} if $has_notes; + end; + $last_role = $s->{role}; + } + end 'table'; + end; + } + if(@{$v->{seiyuu}}) { + div class => 'mainbox staff cast'; + h1 mt '_vnpage_cast'; + my $has_notes = grep { $_->{note} } @{$v->{seiyuu}}; + table class => 'stripe'; + thead; + Tr; + td class => 'tc1', mt '_staff_col_cast'; + td class => 'tc2', mt '_staff_col_seiyuu'; + td class => 'tc3', mt '_staff_col_note' if $has_notes; + end; + end; + for my $s (@{$v->{seiyuu}}) { + Tr; + td class => 'tc1'; + a href => "/c$s->{cid}", $s->{cname}; + end; + td class => 'tc2'; + a href => "/s$s->{id}", title => $s->{original}||$s->{name}, $s->{name}; + end; + td class => 'tc3', $s->{note} if $has_notes; + end; + } + end 'table'; + end; + } +} + + 1; diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm index d4bba99f..7b3fa673 100644 --- a/lib/VNDB/Util/CommonHTML.pm +++ b/lib/VNDB/Util/CommonHTML.pm @@ -27,7 +27,7 @@ sub htmlMainTabs { return if $type eq 'g' && !$self->authCan('tagmod'); ul class => 'maintabs'; - if($type =~ /[uvrpc]/) { + if($type =~ /[uvrpcs]/) { li $sel eq 'hist' ? (class => 'tabselected') : (); a href => "/$id/hist", mt '_mtabs_hist'; end; @@ -73,7 +73,7 @@ sub htmlMainTabs { } if( $type eq 'u' && ($self->authInfo->{id} && $obj->{id} == $self->authInfo->{id} || $self->authCan('usermod')) - || $type =~ /[vrpc]/ && $self->authCan('edit') && ((!$obj->{locked} && !$obj->{hidden}) || $self->authCan('dbmod')) + || $type =~ /[vrpcs]/ && $self->authCan('edit') && ((!$obj->{locked} && !$obj->{hidden}) || $self->authCan('dbmod')) || $type =~ /[gi]/ && $self->authCan('tagmod') ) { li $sel eq 'edit' ? (class => 'tabselected') : (); @@ -132,11 +132,12 @@ sub htmlDenied { sub htmlHiddenMessage { my($self, $type, $obj) = @_; return 0 if !$obj->{hidden}; - my $board = $type eq 'c' ? 'db' : $type eq 'r' ? 'v'.$obj->{vn}[0]{vid} : $type.$obj->{id}; + my $board = $type =~ /[cs]/ ? 'db' : $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} : $type eq 'c' ? $self->dbCharGet(id => $obj->{id}, what => 'changes')->[0]{comments} + : $type eq 's' ? $self->dbStaffGet(id => $obj->{id}, what => 'changes')->[0]{comments} : $self->dbProducerGet(id => $obj->{id}, what => 'changes')->[0]{comments}; div class => 'mainbox'; h1 $obj->{title}||$obj->{name}; @@ -284,8 +285,8 @@ sub revdiff { # Arguments: v/r/p, obj sub htmlEditMessage { my($self, $type, $obj, $title, $copy) = @_; - my $num = {v => 0, r => 1, p => 2, c => 3}->{$type}; - my $guidelines = {v => 2, r => 3, p => 4, c => 12}->{$type}; + my $num = {v => 0, r => 1, p => 2, c => 3, 's' => 4}->{$type}; + my $guidelines = {v => 2, r => 3, p => 4, c => 12, 's' => 16}->{$type}; div class => 'mainbox'; h1 $title; @@ -302,7 +303,7 @@ sub htmlEditMessage { ul; li; lit mt '_editmsg_msg_guidelines', "/d$guidelines"; end; if($obj) { - li; lit mt '_editmsg_msg_discuss', $type eq 'c' ? '/t/db' : $type eq 'r' ? "/t/v$obj->{vn}[0]{vid}" : "/t/$type$obj->{id}"; end; + li; lit mt '_editmsg_msg_discuss', $type =~ /[cs]/ ? '/t/db' : $type eq 'r' ? "/t/v$obj->{vn}[0]{vid}" : "/t/$type$obj->{id}"; end; li; lit mt '_editmsg_msg_history', "/$type$obj->{id}/hist"; end; } elsif($type ne 'r') { li; lit mt '_editmsg_msg_search', "/$type/all", $num; end; @@ -423,6 +424,7 @@ sub htmlSearchBox { 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 => '/s/all', $sel eq 's' ? (class => 'sel') : (), mt '_searchbox_staff'; a href => '/c/all', $sel eq 'c' ? (class => 'sel') : (), mt '_searchbox_chars'; a href => '/g', $sel eq 'g' ? (class => 'sel') : (), mt '_searchbox_tags'; a href => '/i', $sel eq 'i' ? (class => 'sel') : (), mt '_searchbox_traits'; diff --git a/lib/VNDB/Util/LayoutHTML.pm b/lib/VNDB/Util/LayoutHTML.pm index 7de7f709..e51ec998 100644 --- a/lib/VNDB/Util/LayoutHTML.pm +++ b/lib/VNDB/Util/LayoutHTML.pm @@ -66,6 +66,7 @@ sub _menu { b class => 'grayedout', '> '; a href => '/g', mt '_menu_tags'; br; a href => '/r', mt '_menu_releases'; br; a href => '/p/all', mt '_menu_producers'; br; + a href => '/s/all', mt '_menu_staff'; br; a href => '/c/all', mt '_menu_characters'; br; b class => 'grayedout', '> '; a href => '/i', mt '_menu_traits'; br; a href => '/u/all', mt '_menu_users'; br; @@ -102,6 +103,9 @@ sub _menu { if($self->authCan('edit')) { a href => '/v/add', mt '_menu_addvn'; br; a href => '/p/new', mt '_menu_addproducer'; br; + if($self->authCan('staffedit')) { + a href => '/s/new', mt '_menu_addstaff'; br; + } a href => '/c/new', mt '_menu_addcharacter'; br; } br; diff --git a/lib/VNDBUtil.pm b/lib/VNDBUtil.pm index 0c3d5eb3..7bec05b2 100644 --- a/lib/VNDBUtil.pm +++ b/lib/VNDBUtil.pm @@ -59,8 +59,8 @@ sub bb2html { while($raw =~ m{( (d[1-9][0-9]*\.[1-9][0-9]*\.[1-9][0-9]*) | # 2. longid - ([tdvprc][1-9][0-9]*\.[1-9][0-9]*) | # 3. exid - ([tdvprcugi][1-9][0-9]*) | # 4. id + ([tdvprcs][1-9][0-9]*\.[1-9][0-9]*) | # 3. exid + ([tdvprcsugi][1-9][0-9]*) | # 4. id (\[[^\s\]]+\]) | # 5. tag ((?:https?|ftp)://[^><"\n\s\]\[]+[\d\w=/-]) # 6. url )}xg) { diff --git a/util/jsgen.pl b/util/jsgen.pl index 26815423..eb7d2a8a 100755 --- a/util/jsgen.pl +++ b/util/jsgen.pl @@ -114,6 +114,7 @@ sub jsgen { $common .= sprintf "blood_types = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{blood_types}}; $common .= sprintf "genders = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{genders}}; $common .= sprintf "char_roles = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{char_roles}}; + $common .= sprintf "staff_roles = [ %s ];\n", join ', ', map qq{"$_"}, @{$S{staff_roles}}; $common .= sprintf "L10N_LANG = [ %s ];\n", join(', ', map sprintf('["%s","%s"]', $_, $lang{$_}{"_lang_$_"}||$lang{en}{"_lang_$_"}), VNDB::L10N::languages()); diff --git a/util/sql/func.sql b/util/sql/func.sql index 360f52f6..f7125607 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -200,7 +200,8 @@ BEGIN ( SELECT vid FROM vn_rev WHERE id = i UNION SELECT rid FROM releases_rev WHERE id = i UNION SELECT cid FROM chars_rev WHERE id = i - UNION SELECT pid FROM producers_rev WHERE id = i), + UNION SELECT pid FROM producers_rev WHERE id = i + UNION SELECT sid FROM staff_rev WHERE id = i), COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE), COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE) ); @@ -226,6 +227,7 @@ BEGIN UNION SELECT id FROM releases_rev WHERE t = 'r' AND rid = i UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i UNION SELECT id FROM chars_rev WHERE t = 'c' AND cid = i + UNION SELECT id FROM staff_rev WHERE t = 's' AND sid = i ) x(id) ON x.id = c.id ORDER BY c.id DESC LIMIT 1; @@ -242,6 +244,7 @@ BEGIN WHEN 'r' THEN INSERT INTO releases (latest) VALUES (0) RETURNING id INTO r.iid; WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid; WHEN 'c' THEN INSERT INTO chars (latest) VALUES (0) RETURNING id INTO r.iid; + WHEN 's' THEN INSERT INTO staff (latest) VALUES (0) RETURNING id INTO r.iid; END CASE; ELSE r.iid := i; @@ -253,6 +256,7 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION edit_vn_init(cid integer) RETURNS void AS $$ +#variable_conflict use_variable BEGIN -- create tables, based on existing tables (so that the column types are always synchronised) BEGIN @@ -266,8 +270,12 @@ BEGIN 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; + CREATE TEMPORARY TABLE edit_vn_staff (LIKE vn_staff INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_vn_staff DROP COLUMN vid; + CREATE TEMPORARY TABLE edit_vn_seiyuu (LIKE vn_seiyuu INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_vn_seiyuu DROP COLUMN vid; EXCEPTION WHEN duplicate_table THEN - TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots; + TRUNCATE edit_vn, edit_vn_anime, edit_vn_relations, edit_vn_screenshots, edit_vn_staff, edit_vn_seiyuu; END; PERFORM edit_revtable('v', cid); -- new VN, load defaults @@ -279,6 +287,8 @@ BEGIN INSERT INTO edit_vn_anime SELECT aid FROM vn_anime WHERE vid = cid; INSERT INTO edit_vn_relations SELECT vid2, relation, official FROM vn_relations WHERE vid1 = cid; INSERT INTO edit_vn_screenshots SELECT scr, nsfw, rid FROM vn_screenshots WHERE vid = cid; + INSERT INTO edit_vn_staff SELECT aid, role, note FROM vn_staff WHERE vid = cid; + INSERT INTO edit_vn_seiyuu SELECT aid, vs.cid, note FROM vn_seiyuu vs WHERE vid = cid; END IF; END; $$ LANGUAGE plpgsql; @@ -297,6 +307,8 @@ BEGIN INSERT INTO vn_anime SELECT r.cid, aid FROM edit_vn_anime; INSERT INTO vn_relations SELECT r.cid, vid, relation, official FROM edit_vn_relations; INSERT INTO vn_screenshots SELECT r.cid, scr, nsfw, rid FROM edit_vn_screenshots; + INSERT INTO vn_staff SELECT r.cid, aid, role, note FROM edit_vn_staff; + INSERT INTO vn_seiyuu SELECT r.cid, aid, cid, note FROM edit_vn_seiyuu; UPDATE vn SET latest = r.cid WHERE id = r.iid; RETURN r; END; @@ -454,6 +466,49 @@ $$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION edit_staff_init(cid integer) RETURNS void AS $$ +BEGIN + BEGIN + CREATE TEMPORARY TABLE edit_staff (LIKE staff_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_staff DROP COLUMN id; + ALTER TABLE edit_staff DROP COLUMN sid; + CREATE TEMPORARY TABLE edit_staff_aliases (LIKE staff_alias INCLUDING DEFAULTS INCLUDING CONSTRAINTS); + ALTER TABLE edit_staff_aliases DROP COLUMN rid; + EXCEPTION WHEN duplicate_table THEN + TRUNCATE edit_staff, edit_staff_aliases; + END; + PERFORM edit_revtable('s', cid); + -- new staff member + IF cid IS NULL THEN + INSERT INTO edit_staff (aid) VALUES (0); + -- load revision + ELSE + INSERT INTO edit_staff SELECT aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM staff_rev WHERE id = cid; + INSERT INTO edit_staff_aliases SELECT id, name, original FROM staff_alias WHERE rid = cid; + END IF; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION edit_staff_commit() RETURNS edit_rettype AS $$ +DECLARE + r edit_rettype; +BEGIN + IF (SELECT COUNT(*) FROM edit_staff) <> 1 THEN + RAISE 'edit_staff must have exactly one row!'; + END IF; + SELECT INTO r * FROM edit_commit(); + INSERT INTO staff_alias (id, rid, name, original) + SELECT id, r.cid, name, original FROM edit_staff_aliases; + INSERT INTO staff_rev (id, sid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb) + SELECT r.cid, r.iid, aid, gender, lang, "desc", l_wp, l_site, l_twitter, l_anidb FROM edit_staff; + UPDATE staff SET latest = r.cid WHERE id = r.iid; + RETURN r; +END; +$$ LANGUAGE plpgsql; + + + ---------------------------------------------------------- @@ -793,7 +848,7 @@ CREATE OR REPLACE FUNCTION notify_dbdel() RETURNS trigger AS $$ BEGIN 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' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype, h.requester, NEW.id, h2.rev, x.title, h2.requester -- look for changes of the deleted entry -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs @@ -810,6 +865,9 @@ BEGIN UNION SELECT cr.id, cr2.name FROM chars_rev cr JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id + UNION SELECT sr.id, sa.name FROM staff_rev sr + JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id + WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id ) x(id, title) ON h.id = x.id -- join info about the deletion itself JOIN changes h2 ON h2.id = NEW.latest @@ -857,7 +915,7 @@ CREATE OR REPLACE FUNCTION notify_dbedit() RETURNS trigger AS $$ BEGIN 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' WHEN 'producers' THEN 'p' ELSE 'c' END)::notification_ltype, + (CASE TG_TABLE_NAME WHEN 'vn' THEN 'v' WHEN 'releases' THEN 'r' WHEN 'producers' THEN 'p' WHEN 'staff' THEN 's' ELSE 'c' END)::notification_ltype, h.requester, NEW.id, h2.rev, x.title, h2.requester -- look for changes of the edited entry FROM changes h @@ -873,6 +931,9 @@ BEGIN UNION SELECT cr.id, cr2.name FROM chars_rev cr JOIN chars c ON c.id = cr.cid JOIN chars_rev cr2 ON cr2.id = c.latest WHERE TG_TABLE_NAME = 'chars' AND cr.cid = NEW.id + UNION SELECT sr.id, sa.name FROM staff_rev sr + JOIN staff s ON s.id = sr.sid JOIN staff_rev sr2 ON sr2.id = s.latest JOIN staff_alias sa ON sa.id = sr2.aid AND sa.rid = sr2.id + WHERE TG_TABLE_NAME = 'staff' AND sr.sid = NEW.id ) x(id, title) ON h.id = x.id -- join info about the deletion itself JOIN changes h2 ON h2.id = NEW.latest diff --git a/util/sql/staff.sql b/util/sql/staff.sql new file mode 100644 index 00000000..0c91deb4 --- /dev/null +++ b/util/sql/staff.sql @@ -0,0 +1,67 @@ +-- database schema for staff/seiyuu + +ALTER TYPE dbentry_type ADD VALUE 's'; +ALTER TYPE notification_ltype ADD VALUE 's'; +CREATE TYPE credit_type AS ENUM ('script', 'chardesign', 'music', 'director', 'art', 'songs', 'staff'); + +CREATE TABLE staff ( + id SERIAL NOT NULL PRIMARY KEY, + latest integer NOT NULL DEFAULT 0, + locked boolean NOT NULL DEFAULT FALSE, + hidden boolean NOT NULL DEFAULT FALSE +); + +CREATE TABLE staff_rev ( + id integer NOT NULL PRIMARY KEY, + sid integer NOT NULL, -- references staff + aid integer NOT NULL, -- true name, references staff_alias + gender gender NOT NULL DEFAULT 'unknown', + lang language NOT NULL DEFAULT 'ja', + "desc" text NOT NULL DEFAULT '', + l_wp varchar(150) NOT NULL DEFAULT '', + l_site varchar(250) NOT NULL DEFAULT '', + l_twitter varchar(16) NOT NULL DEFAULT '', + l_anidb integer +); + +CREATE TABLE staff_alias ( + id SERIAL NOT NULL, + rid integer, -- references staff_rev + name varchar(200) NOT NULL DEFAULT '', + original varchar(200) NOT NULL DEFAULT '', + PRIMARY KEY (id, rid) +); + +CREATE TABLE vn_staff ( + vid integer NOT NULL, -- vn_rev reference + aid integer NOT NULL, -- staff_alias reference + role credit_type NOT NULL DEFAULT 'staff', + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (vid, aid, role) +); + +CREATE TABLE vn_seiyuu ( + vid integer NOT NULL, -- vn_rev reference + aid integer NOT NULL, -- staff_alias reference + cid integer NOT NULL, -- chars reference + note varchar(250) NOT NULL DEFAULT '', + PRIMARY KEY (vid, aid, cid) +); + +ALTER TABLE staff ADD FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE staff_alias ADD FOREIGN KEY (rid) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE staff_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE staff_rev ADD FOREIGN KEY (sid) REFERENCES staff (id); +ALTER TABLE staff_rev ADD FOREIGN KEY (aid,id) REFERENCES staff_alias (id,rid); +ALTER TABLE vn_staff ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); +ALTER TABLE vn_seiyuu ADD FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE vn_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn_rev (id); + +CREATE INDEX vn_staff_vid ON vn_staff (vid); +CREATE INDEX vn_staff_aid ON vn_staff (aid); +--CREATE INDEX staff_alias_orig ON staff_alias (translate(original,' ','')); + +CREATE TRIGGER hidlock_update BEFORE UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); + +CREATE TRIGGER notify_dbdel AFTER UPDATE ON staff FOR EACH ROW WHEN (NOT OLD.hidden AND NEW.hidden) EXECUTE PROCEDURE notify_dbdel(); +CREATE TRIGGER notify_dbedit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest AND NOT NEW.hidden) EXECUTE PROCEDURE notify_dbedit(); |