summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--data/docs/1644
-rw-r--r--data/docs/234
-rw-r--r--data/docs/2.cs1
-rw-r--r--data/docs/2.de1
-rw-r--r--data/docs/2.hu1
-rw-r--r--data/docs/2.it1
-rw-r--r--data/docs/2.ru1
-rw-r--r--data/docs/index1
-rw-r--r--data/global.pl3
-rw-r--r--data/lang.txt286
-rw-r--r--data/script.js340
-rw-r--r--data/style.css55
-rw-r--r--lib/Multi/IRC.pm15
-rw-r--r--lib/VNDB/DB/Chars.pm23
-rw-r--r--lib/VNDB/DB/Misc.pm13
-rw-r--r--lib/VNDB/DB/Producers.pm2
-rw-r--r--lib/VNDB/DB/Staff.pm133
-rw-r--r--lib/VNDB/DB/VN.pm72
-rw-r--r--lib/VNDB/Func.pm26
-rw-r--r--lib/VNDB/Handler/Chars.pm18
-rw-r--r--lib/VNDB/Handler/Misc.pm6
-rw-r--r--lib/VNDB/Handler/Staff.pm414
-rw-r--r--lib/VNDB/Handler/VNBrowse.pm2
-rw-r--r--lib/VNDB/Handler/VNEdit.pm118
-rw-r--r--lib/VNDB/Handler/VNPage.pm108
-rw-r--r--lib/VNDB/Util/CommonHTML.pm14
-rw-r--r--lib/VNDB/Util/LayoutHTML.pm4
-rw-r--r--lib/VNDBUtil.pm4
-rwxr-xr-xutil/jsgen.pl1
-rw-r--r--util/sql/func.sql69
-rw-r--r--util/sql/staff.sql67
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>&nbsp;</dd>
+ <dt>Character design</dt><dd>&nbsp;</dd>
+ <dt>Composer</dt><dd>&nbsp;</dd>
+ <dt>Director</dt><dd>&nbsp;</dd>
+ <dt>Artist</dt><dd>&nbsp;</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 &amp; 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();