diff options
author | morkt <> | 2014-12-28 09:39:21 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2014-12-28 09:39:21 +0100 |
commit | 6399d6d4e21bd4464c7224f821d6ea071ea7a614 (patch) | |
tree | 0eea1eae38e2b98d2f0a235866f6e03749a6b0fa | |
parent | 8719a8e69f5fc46c4ccf44e6e99c33b60c342d76 (diff) |
More progress on the staff + cast DB
-rw-r--r-- | data/lang.txt | 40 | ||||
-rw-r--r-- | data/script.js | 141 | ||||
-rw-r--r-- | data/style.css | 10 | ||||
-rw-r--r-- | lib/VNDB/DB/Chars.pm | 13 | ||||
-rw-r--r-- | lib/VNDB/DB/Staff.pm | 32 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 26 | ||||
-rw-r--r-- | lib/VNDB/Handler/Chars.pm | 10 | ||||
-rw-r--r-- | lib/VNDB/Handler/Staff.pm | 81 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNEdit.pm | 44 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 81 | ||||
-rw-r--r-- | util/sql/func.sql | 7 | ||||
-rw-r--r-- | util/sql/staff.sql | 14 |
12 files changed, 390 insertions, 109 deletions
diff --git a/data/lang.txt b/data/lang.txt index 84e27cdb..07e30ff2 100644 --- a/data/lang.txt +++ b/data/lang.txt @@ -5441,6 +5441,12 @@ en : Credit :_staff_col_note en : Note +:_staff_col_cast +en : Cast + +:_staff_col_seiyuu +en : Seiyuu + # Add/edit staff :_staffe_title_edit @@ -13135,8 +13141,35 @@ 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_credits -en : Credits +:_vnedit_staff +en : Staff + +:_vnedit_staff_msg +en : This message is a placeholder for concise guidelines regarding adding VN staff. + +:_vnedit_cast +en : Cast + +:_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 @@ -13753,6 +13786,9 @@ it : Relazioni :_revfield_v_credits en : Credits +:_revfield_v_seiyuu +en : Seiyuu + :_revfield_v_anime en : Anime ru : Аниме diff --git a/data/script.js b/data/script.js index 8c65a9c9..7d99566c 100644 --- a/data/script.js +++ b/data/script.js @@ -17,6 +17,7 @@ * tvs -> VN page tag spoilers * vnr -> VN relation editor * vns -> VN staff + * vnc -> VN cast * sal -> Staff aliases editor */ @@ -2129,7 +2130,15 @@ if(byId('jt_box_staffe_aliases')) -/* S T A F F < - > V N L I N K I N G (/v+/staff/edit) */ +/* S T A F F < - > V N L I N K I N G (/v+/edit#vn_staff) */ + +function onSubmit(form, handler) { + var prev_handler = form.onsubmit; + form.onsubmit = function(e) { + if (prev_handler) prev_handler(e); + handler(e); + } +} function vnsLoad() { var credits = byId('credits').value.split('|||'); @@ -2156,7 +2165,7 @@ function vnsLoad() { else vnsEmpty(); - byName(byId('maincontent'), 'form')[0].onsubmit = vnsSerialize; + onSubmit(byName(byId('maincontent'), 'form')[0], vnsSerialize); // dropdown search dsInit(byId('credit_input'), '/xml/staff.xml?q=', function(item, tr) { @@ -2217,10 +2226,12 @@ function vnsSerialize() { var l = byName(byId('credits_tbl'), 'tr'); var c = []; for (var i = 0; i < l.length; i++) { - var aid = byName(byName(l[i], 'td')[0], 'input')[0].value; + 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+'-'+role.options[role.selectedIndex].value+'-'+note.value); + c.push (aid.value+'-'+role.value+'-'+note.value); } byId('credits').value = c.join('|||'); return true; @@ -2246,6 +2257,128 @@ if(byId('jt_box_vn_staff')) + +/* V N C H A R A C T E R S C A S T (/v+/edit#vn_cast) */ + +function vncLoad() { + var cast = byId('seiyuu').value.split('|||'); + var s = {}; // seiyuu -> { aid: [ char, note ], .. } + var q = []; // list of a=X parameters + for (var i = 0; i < cast.length && cast[i].length > 1; i++) { + var c = cast[i].split('-', 3); // aid, char, note + if (!s[c[0]]) + q.push('a='+c[0]); + s[c[0]] = [ c[1], c[2] ]; + } + if (q.length > 0) + ajax('/xml/staff.xml?'+q.join(';'), function(hs) { + var seiyuu = hs.responseXML.getElementsByTagName('item'); + for (var i = 0; i < seiyuu.length; i++) { + var aid = seiyuu[i].getAttribute('aid'); + if (s[aid]) + vncAdd(seiyuu[i], s[aid][0], s[aid][1]); + } + vncEmpty(); + }, 1); + else + vncEmpty(); + + 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 vncAdd(seiyuu, chr, note) { + var sid = seiyuu.getAttribute('id'); + var aid = seiyuu.getAttribute('aid'); + var tbl = byId('cast_tbl'); + + var csel = byId('cast_chars').cloneNode(true); + csel.removeAttribute('id'); + csel.value = chr; + var note = tag('input', {type:'text', 'class':'text'}); + + tbl.appendChild(tag('tr', {id:'vnc_a'+aid}, + tag('td', {'class':'tc_char'}, csel), + tag('td', {'class':'tc_name'}, + tag('input', {type:'hidden', value:aid}), + tag('a', {href:'/s'+sid}, seiyuu.firstChild.nodeValue)), + tag('td', {'class':'tc_note'}, 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) + vncAdd(item, 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.value+'-'+role.value+'-'+note.value); + } + byId('seiyuu').value = c.join('|||'); + 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 1e386de1..77f568b8 100644 --- a/data/style.css +++ b/data/style.css @@ -577,22 +577,32 @@ div.staffdesc { margin-bottom: 10px; } /***** Staff edit *****/ +#jt_box_vn_cast table, #jt_box_vn_staff table, #jt_box_staffe_aliases table { margin-bottom: 10px; } +#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 } diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm index 272f9610..a7dfb692 100644 --- a/lib/VNDB/DB/Chars.pm +++ b/lib/VNDB/DB/Chars.pm @@ -111,14 +111,17 @@ sub dbCharGet { if($o{what} =~ /seiyuu/) { push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q| - SELECT cs.cid, sr.sid, sa.name, sa.original, cs.note - FROM chars_seiyuu cs - JOIN staff_alias sa ON sa.id = cs.aid + SELECT cr.id AS cid, sr.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_rev sr ON sr.id = sa.rid JOIN staff s ON sr.id = s.latest + JOIN vn_rev vr ON vr.id = vs.vid + JOIN vn v ON v.latest = vs.vid !W - ORDER BY sa.name|, - { 'cs.cid IN(!l)' => [[ keys %r ]], $o{vid} ? ('vid = ?' => $o{vid}) : () } + ORDER BY v.c_released, sa.name|, + { 'cr.id IN(!l)' => [[ keys %r ]], $o{vid} ? ('v.id = ?' => $o{vid}) : () } )}); } } diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm index b9e8d8c3..1c9438a0 100644 --- a/lib/VNDB/DB/Staff.pm +++ b/lib/VNDB/DB/Staff.pm @@ -7,7 +7,7 @@ use Exporter 'import'; our @EXPORT = qw|dbStaffGet dbStaffRevisionInsert|; -# options: results, page, id, aid, vid, search, rev +# options: results, page, id, aid, search, rev # what: extended changes roles aliases sub dbStaffGet { my $self = shift; @@ -24,7 +24,6 @@ sub dbStaffGet { !$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{vid} ? ( 'vr.vid = ?' => $o{vid}) : (), $o{search} ? ( '(sa.name ILIKE ? OR sa.original ILIKE ?)', [ map '%%'.$o{search}.'%%', 1..2 ] ) : (), $o{char} ? ( 'LOWER(SUBSTR(sa.name, 1, 1)) = ?' => $o{char} ) : (), @@ -38,25 +37,12 @@ sub dbStaffGet { 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/; - push @join, - 'JOIN vn_staff vs ON vs.aid = sa.id', - 'JOIN vn_rev vr ON vs.vid = vr.id', - 'JOIN vn v ON vr.id = v.latest' if $o{vid}; -# fetch both staff and seiyuu in one query -# push @join, q| -# LEFT JOIN vn_staff vs ON vs.aid = sa.id -# LEFT JOIN (chars_seiyuu cs JOIN chars c ON cs.cid = c.latest) -# ON cs.aid = sa.id -# JOIN (vn_rev vr JOIN vn v ON vr.id = v.latest) -# ON vs.vid = vr.id OR cs.vid = v.id -# | if $o{vid}; 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, 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/; - $select .= ', vs.role, vs.note' if $o{vid}; - my $order = $o{vid} ? 'ORDER BY vs.role ASC, sa.name ASC' : 'ORDER BY sa.name ASC'; + my $order = 'ORDER BY sa.name'; my($r, $np) = $self->dbPage(\%o, q| SELECT !s @@ -76,7 +62,7 @@ sub dbStaffGet { } @$r; if ($o{what} =~ /roles/) { push @{$r{ delete $_->{rid} }{roles}}, $_ for (@{$self->dbAll(q| - SELECT sa.rid, vr.vid, sa.name, v.c_released, vr.title, vr.original AS t_original, vs.role, vs.note + 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 @@ -85,12 +71,12 @@ sub dbStaffGet { 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, v.c_released, vr.title, vr.original AS t_original, cr.cid, cr.name AS c_name, cs.note - FROM chars_seiyuu cs - JOIN chars_rev cr ON cr.id = cs.cid - JOIN vn v ON v.id = cs.vid - JOIN vn_rev vr ON v.latest = vr.id - JOIN staff_alias sa ON cs.aid = sa.id + 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 staff_alias sa ON vs.aid = sa.id WHERE sa.rid IN(!l) ORDER BY v.c_released ASC, vr.title ASC|, [ keys %r ] )}); diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm index 1b127e44..97fc3086 100644 --- a/lib/VNDB/DB/VN.pm +++ b/lib/VNDB/DB/VN.pm @@ -146,6 +146,7 @@ sub dbVNGet { my %r = map { $r->[$_]{anime} = []; $r->[$_]{credits} = []; + $r->[$_]{seiyuu} = []; $r->[$_]{relations} = []; $r->[$_]{screenshots} = []; ($r->[$_]{cid}, $_) @@ -153,7 +154,7 @@ sub dbVNGet { 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, sr.id AS cid, vs.role, vs.note + 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 @@ -162,6 +163,20 @@ sub dbVNGet { 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 sr.id = s.latest + JOIN chars c ON c.id = vs.cid + JOIN chars_rev cr ON cr.id = c.latest + JOIN chars_vns cv ON cv.cid = cr.id AND cv.vid = vr.vid + WHERE vs.vid IN(!l) + ORDER BY cv.role, cr.name|, + [ keys %r ] + )}); } if($o{what} =~ /anime/) { @@ -246,6 +261,13 @@ sub dbVNRevisionInsert { 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; + } } @@ -315,7 +337,7 @@ sub dbVNHasChar { sub dbVNHasStaff { my($self, $vid) = @_; return $self->dbRow( - 'SELECT 1 AS exists FROM vn v JOIN vn_staff vs ON v.latest = vs.vid WHERE v.id = ?', $vid + '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}; } diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm index cb829e4c..767e0781 100644 --- a/lib/VNDB/Handler/Chars.pm +++ b/lib/VNDB/Handler/Chars.pm @@ -61,9 +61,6 @@ sub page { $_->{rid}?sprintf('[<a href="/r%d">r%d</a>]', $_->{rid}, $_->{rid}):'', mt("_charrole_$_->{role}"), mt("_spoil_$_->{spoil}")), @{$_[0]}; }], - [ seiyuu => join => '<br />', split => sub { - map sprintf('<a href="/s%d">%s</a>', $_->{sid}, $_->{name}), @{$_[0]} - }], ); } @@ -233,8 +230,11 @@ sub charTable { Tr; td class => 'key', mt '_charp_voice'; td; - for my $s (@{$r->{seiyuu}}) { - a href => "/s$s->{sid}", $s->{name}; br; + 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}; br; + $last_name = $s->{name}; } end; end; diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm index cbe6fd63..45aaa574 100644 --- a/lib/VNDB/Handler/Staff.pm +++ b/lib/VNDB/Handler/Staff.pm @@ -104,44 +104,57 @@ sub page { if (@{$s->{roles}} || @{$s->{cast}}) { div class => 'staffroles'; - table class => 'stripe'; - thead; - Tr; - td class => 'tc1', mt '_staff_col_role'; - td class => 'tc2', mt '_staff_col_title'; - td class => 'tc3', mt '_staff_col_released'; - td class => 'tc4', mt '_staff_col_note'; - end; - end; - foreach my $r (@{$s->{roles}}) { - Tr; - td class => 'tc1', mt '_credit_'.$r->{role}; - td class => 'tc2'; a href => "/v$r->{vid}", title => $r->{t_original}, $r->{title}; end; - td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end; - td class => 'tc4'; - lit '('.mt('_staff_as').$r->{name}.') ' if $r->{name} ne $s->{name}; - lit $r->{note}; - end; - end; - } - if (@{$s->{cast}}) { + if (@{$s->{roles}}) { + table class => 'stripe'; + thead; Tr; - td class => 'tc1', colspan => 4; b mt '_staff_col_cast'; end; + td class => 'tc1', mt '_staff_col_role'; + td class => 'tc2', mt '_staff_col_title'; + td class => 'tc3', mt '_staff_col_released'; + td class => 'tc4', mt '_staff_col_note'; end; - } - foreach my $r (@{$s->{cast}}) { + end; + tbody; + foreach my $r (@{$s->{roles}}) { + Tr; + td class => 'tc1', mt '_credit_'.$r->{role}; + td class => 'tc2'; a href => "/v$r->{vid}", title => $r->{t_original}, $r->{title}; end; + td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end; + td class => 'tc4'; + lit '('.mt('_staff_as', $r->{name}).') ' if $r->{name} ne $s->{name}; + lit $r->{note}; + end; + end; + } + end; + end; + } + if (@{$s->{cast}}) { + table class => 'stripe'; + thead; Tr; - td class => 'tc1'; a href => "/c$r->{cid}", $r->{c_name}; end; - td class => 'tc2'; a href => "/v$r->{vid}", title => $r->{t_original}, $r->{title}; end; - td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end; - td class => 'tc4'; - lit '('.mt('_staff_as', $r->{name}).') ' if $r->{name} ne $s->{name}; - lit $r->{note}; - end; + td class => 'tc1', mt '_staff_col_cast'; + td class => 'tc2', mt '_staff_col_title'; + td class => 'tc3', mt '_staff_col_released'; + td class => 'tc4', mt '_staff_col_note'; end; - } - end 'table'; - end + end; + tbody; + foreach my $r (@{$s->{cast}}) { + Tr; + td class => 'tc1'; a href => "/c$r->{cid}", $r->{c_name}; end; + td class => 'tc2'; a href => "/v$r->{vid}", title => $r->{t_original}, $r->{title}; end; + td class => 'tc3'; lit $self->{l10n}->datestr($r->{c_released}); end; + td class => 'tc4'; + lit '('.mt('_staff_as', $r->{name}).') ' if $r->{name} ne $s->{name}; + lit $r->{note}; + end; + end; + } + end; + end; + } + end; } clearfloat; end; diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm index 1c41e991..bd98b941 100644 --- a/lib/VNDB/Handler/VNEdit.pm +++ b/lib/VNDB/Handler/VNEdit.pm @@ -89,6 +89,7 @@ sub edit { my %b4 = !$vid ? () : ( (map { $_ => $v->{$_} } qw|title original desc alias length l_wp l_encubed l_renai image img_nsfw ihid ilock|), credits => join('|||', map sprintf('%d-%s-%s', $_->{aid}, $_->{role}, $_->{note}), @{$v->{credits}}), + seiyuu => join('|||', map sprintf('%d-%d-%s', $_->{aid}, $_->{cid}, $_->{note}), @{$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}}), @@ -110,6 +111,7 @@ sub edit { { 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 }, @@ -128,6 +130,7 @@ sub edit { my $relations = [ map { /^([a-z]+),([0-9]+),([01]),(.+)$/ && (!$vid || $2 != $vid) ? [ $1, $2, $3, $4 ] : () } split /\|\|\|/, $frm->{vnrelations} ]; my $screenshots = [ map /^[0-9]+,[01],[0-9]+$/ ? [split /,/] : (), split / +/, $frm->{screenshots} ]; my $credits = [ map { /^(\d+)-([^-]+)-(.*)$/ ? [ $1, $2, $3 ]: () } split /\|\|\|/, $frm->{credits} ]; + my $seiyuu = [ map { /^(\d+)-(\d+)-(.*)$/ ? [ $1, $2, $3 ]: () } split /\|\|\|/, $frm->{seiyuu} ]; $frm->{ihid} = $frm->{ihid}?1:0; $frm->{ilock} = $frm->{ilock}?1:0; @@ -147,6 +150,14 @@ sub edit { } $frm->{credits} = join '|||', map sprintf('%d-%s-%s', @$_), @$checked_c; + my($checked_s, $last_s) = ([], []); + for my $s (sort { $a->[0] <=> $b->[0] || $a->[1] <=> $b->[1] } @$seiyuu) { + next if $last_s->[0] == $s->[0] && $last_s->[1] == $s->[1]; + push @$checked_s, $s; + $last_s = $s; + } + $frm->{seiyuu} = join '|||', map sprintf('%d-%d-%s', @$_), @$checked_s; + # weed out duplicate aliases my %alias; $frm->{alias} = join "\n", grep { @@ -163,6 +174,7 @@ sub edit { 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 => $checked_c, + seiyuu => $checked_s, anime => [ keys %$anime ], relations => $relations, screenshots => $screenshots, @@ -227,6 +239,7 @@ sub _uploadimage { sub _form { my($self, $v, $frm, $r) = @_; + my $chars = $v && $self->dbCharGet(vid => $v->{id}, results => 50); $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' ], @@ -272,9 +285,13 @@ sub _form { end 'div'; }]], - vn_staff => [ mt('_vnedit_credits'), + vn_staff => [ mt('_vnedit_staff'), [ hidden => short => 'credits' ], [ static => nolabel => 1, content => sub { + 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; @@ -286,6 +303,31 @@ sub _form { 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 && @{$chars} ? (vn_cast => [ mt('_vnedit_cast'), + [ hidden => short => 'seiyuu' ], + [ static => nolabel => 1, content => sub { + 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 5df2b097..2c1563e1 100644 --- a/lib/VNDB/Handler/VNPage.pm +++ b/lib/VNDB/Handler/VNPage.pm @@ -518,7 +518,7 @@ sub page { my $v = $self->dbVNGet( id => $vid, - what => 'extended anime relations screenshots rating ranking'.($rev ? ' credits changes' : ''), + what => 'extended anime relations screenshots rating ranking'.($staff || $rev ? ' credits' : '').($rev ? ' changes' : ''), $rev ? (rev => $rev) : (), )->[0]; return $self->resNotFound if !$v->{id}; @@ -674,7 +674,7 @@ sub page { if($char) { _chars($self, $haschar, $v); } elsif ($staff) { - _staff($self, $hasstaff, $v); + _staff($self, $v) if $hasstaff; } else { _releases($self, $v, $r); _stats($self, $v); @@ -713,6 +713,14 @@ sub _revision { $_->{id}, xml_escape($_->{original}||$_->{name}), xml_escape($_->{name}), mt("_credit_$_->{role}"), $_->{note} ? ' ['.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} ? ' ['.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'), @@ -1058,7 +1066,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; @@ -1090,30 +1098,55 @@ sub _chars { sub _staff { - my ($self, $has, $v) = @_; - my $l = $has && $self->dbStaffGet(vid => $v->{id}, results => 100); - return if !$has; + my ($self, $v) = @_; div class => 'mainbox'; - 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'; - end; - end; - my $last_role = ''; - for my $s (@$l) { - 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}; + if(@{$v->{credits}}) { + 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; - td class => 'tc3', $s->{note}; end; - $last_role = $s->{role}; - } - end 'table'; + 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'; + br; + } + if(@{$v->{seiyuu}}) { + 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; } diff --git a/util/sql/func.sql b/util/sql/func.sql index 787ae8b8..a1d776d2 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -256,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 @@ -271,8 +272,10 @@ BEGIN 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, edit_vn_staff; + 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 @@ -285,6 +288,7 @@ BEGIN 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; @@ -304,6 +308,7 @@ BEGIN 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; diff --git a/util/sql/staff.sql b/util/sql/staff.sql index a53b8955..c4ff41ba 100644 --- a/util/sql/staff.sql +++ b/util/sql/staff.sql @@ -37,12 +37,12 @@ CREATE TABLE vn_staff ( PRIMARY KEY (vid, aid, role) ); -CREATE TABLE chars_seiyuu ( - cid integer NOT NULL, -- chars_rev reference - vid integer NOT NULL, -- vn reference +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 (cid, vid, aid) + PRIMARY KEY (vid, aid, cid) ); ALTER TABLE staff ADD FOREIGN KEY (latest) REFERENCES staff_rev (id) DEFERRABLE INITIALLY DEFERRED; @@ -51,10 +51,8 @@ ALTER TABLE staff_rev ADD FOREIGN KEY (id) REFERENCES changes 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 chars_seiyuu ADD FOREIGN KEY (cid) REFERENCES chars_rev (id); -ALTER TABLE chars_seiyuu ADD FOREIGN KEY (vid) REFERENCES vn (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 chars_seiyuu_pkey ON chars_seiyuu (cid, vid); -CREATE INDEX chars_seiyuu_aid ON chars_seiyuu (aid); CREATE INDEX vn_staff_vid ON vn_staff (vid); CREATE INDEX vn_staff_aid ON vn_staff (aid); |