From 8719a8e69f5fc46c4ccf44e6e99c33b60c342d76 Mon Sep 17 00:00:00 2001
From: morkt <>
Date: Wed, 24 Dec 2014 09:24:58 +0100
Subject: Further progress on the staff database
- Moves staff<->vn linking form to the main VN edit form
- Fixes a bug with linking staff aliases to VNs
- Adds staff changes to the VN revisions
- And some misc. improvements
---
data/lang.txt | 14 +++++-----
data/script.js | 8 +++---
data/style.css | 26 +++++++++---------
lib/VNDB/DB/Staff.pm | 2 +-
lib/VNDB/DB/VN.pm | 18 +++++++++++--
lib/VNDB/Handler/Staff.pm | 67 +---------------------------------------------
lib/VNDB/Handler/VNEdit.pm | 30 ++++++++++++++++++++-
lib/VNDB/Handler/VNPage.pm | 15 +++++++----
util/sql/staff.sql | 3 ++-
9 files changed, 84 insertions(+), 99 deletions(-)
diff --git a/data/lang.txt b/data/lang.txt
index d64c8e92..84e27cdb 100644
--- a/data/lang.txt
+++ b/data/lang.txt
@@ -5409,7 +5409,7 @@ en : No results found
en : Credits
:_staff_as
-en : as
+en : as [_1]
:_staff_gender
en : Gender
@@ -5494,12 +5494,6 @@ en : Visual novels
:_sedit_form_alias_none
en : None
-:_vnstaff_edit_title
-en : Edit staff of [_1]
-
-:_vnstaff_edit_credits
-en : Credits
-
:_vnstaffe_form_staff
en : Staff
@@ -13141,6 +13135,9 @@ 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_rel
en : Relations
ru : Связи
@@ -13753,6 +13750,9 @@ tr : İlişkiler
uk : Зв’язки
it : Relazioni
+:_revfield_v_credits
+en : Credits
+
:_revfield_v_anime
en : Anime
ru : Аниме
diff --git a/data/script.js b/data/script.js
index 6a3bd25e..8c65a9c9 100644
--- a/data/script.js
+++ b/data/script.js
@@ -2096,6 +2096,7 @@ function salSerialize() {
a[a.length] = [ id, name, orig ].join(',');
}
byId('aliases').value = a.join('|||');
+ return true;
}
function salDel() {
@@ -2146,9 +2147,9 @@ function vnsLoad() {
for (var i = 0; i < staff.length; i++) {
var aid = staff[i].getAttribute('aid');
if (s[aid])
- vnsAdd (staff[i], s[aid][0], s[aid][1]);
+ vnsAdd (staff[i], s[aid][0], s[aid][1]);
else
- vnsAdd (staff[i], 'staff', '');
+ vnsAdd (staff[i], 'staff', '');
}
vnsEmpty();
}, 1);
@@ -2222,6 +2223,7 @@ function vnsSerialize() {
c.push (aid+'-'+role.options[role.selectedIndex].value+'-'+note.value);
}
byId('credits').value = c.join('|||');
+ return true;
}
function vnsDel() {
@@ -2239,7 +2241,7 @@ function vnsFormAdd(item) {
return '';
}
-if(byId('jt_box_vnstaffe_credits'))
+if(byId('jt_box_vn_staff'))
vnsLoad();
diff --git a/data/style.css b/data/style.css
index 3c565700..1e386de1 100644
--- a/data/style.css
+++ b/data/style.css
@@ -577,24 +577,24 @@ div.staffdesc { margin-bottom: 10px; }
/***** Staff edit *****/
-#jt_box_vnstaffe_credits table,
+#jt_box_vn_staff table,
#jt_box_staffe_aliases table { margin-bottom: 10px; }
-#jt_box_vnstaffe_credits h2,
+#jt_box_vn_staff h2,
#jt_box_staffe_aliases h2 { margin: 0 0 3px 0px; }
-#jt_box_vnstaffe_credits td,
+#jt_box_vn_staff td,
#jt_box_staffe_aliases td { padding: 1px 2px; vertical-align: middle; }
-#jt_box_vnstaffe_credits td.tc_role,
-#jt_box_vnstaffe_credits td.tc_role select { width: 120px }
-#jt_box_vnstaffe_credits td.tc_staff,
+#jt_box_vn_staff td.tc_role,
+#jt_box_vn_staff td.tc_role select { width: 120px }
+#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_vnstaffe_credits td.tc_staff input,
-#jt_box_staffe_aliases td.tc_name input,
-#jt_box_staffe_aliases td.tc_original input { width: 200px }
-#jt_box_vnstaffe_credits td.tc_note,
-#jt_box_vnstaffe_credits td.tc_note input { width: 250px }
-#jt_box_vnstaffe_credits td.tc_add,
-#jt_box_staffe_aliases td.tc_add { width: 40px; text-align: left }
+#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_staff td.tc_note,
+#jt_box_vn_staff td.tc_note input { width: 250px }
+#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/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm
index 7a732c4d..b9e8d8c3 100644
--- a/lib/VNDB/DB/Staff.pm
+++ b/lib/VNDB/DB/Staff.pm
@@ -51,7 +51,7 @@ sub dbStaffGet {
# ON vs.vid = vr.id OR cs.vid = v.id
# | if $o{vid};
- my $select = 's.id, sr.aid, sa.name, sa.original, sr.gender, sr.lang, sr.id AS cid';
+ 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};
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index f6635c07..1b127e44 100644
--- a/lib/VNDB/DB/VN.pm
+++ b/lib/VNDB/DB/VN.pm
@@ -12,7 +12,7 @@ our @EXPORT = qw|dbVNGet dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreen
# 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,28 @@ 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->[$_]{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, sr.id AS cid, 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 vs.vid IN(!l)
+ ORDER BY vs.role ASC, sa.name ASC|,
+ [ 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
diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm
index 14f0b53d..cbe6fd63 100644
--- a/lib/VNDB/Handler/Staff.pm
+++ b/lib/VNDB/Handler/Staff.pm
@@ -11,7 +11,6 @@ TUWF::register(
qr{s(?:([1-9]\d*)(?:\.([1-9]\d*))?/edit|/new)}
=> \&edit,
qr{s/([a-z0]|all)} => \&list,
- qr{v([1-9]\d*)/staff/edit} => \&vn_edit,
qr{xml/staff.xml} => \&staffxml,
);
@@ -136,7 +135,7 @@ sub page {
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 '('.mt('_staff_as', $r->{name}).') ' if $r->{name} ne $s->{name};
lit $r->{note};
end;
end;
@@ -253,70 +252,6 @@ sub edit {
}
-sub vn_edit {
- my($self, $vid) = @_;
-
- my $v = $self->dbVNGet(id => $vid, what => 'changes')->[0];
- return $self->resNotFound if !$v->{id};
-
- return $self->htmlDenied if !$self->authCan('edit')
- || (($v->{locked} || $v->{hidden}) && !$self->authCan('dbmod'));
-
- my $s = $self->dbStaffGet(vid => $vid);
-
- my %b4 = (
- vid => $vid,
- credits => join('|||', map sprintf('%d-%s-%s', $_->{aid}, $_->{role}, $_->{note}), @$s),
- );
- my $frm;
- if ($self->reqMethod eq 'POST') {
- return if !$self->authCheckCode;
- $frm = $self->formValidate (
- { post => 'vid', required => 1, template => 'int' },
- { post => 'credits', required => 0, maxlength => 5000, default => '' },
- );
- my @credits = map { /^(\d+)-([^-]+)-(.*)$/ ? [ $1, $2, $3 ]: () } split /\|\|\|/, $frm->{credits};
- if (!$frm->{_err}) {
- # parse and normalize
- $frm->{credits} = join('|||', map sprintf('%d-%s-%s', @$_), @credits);
-
- return $self->resRedirect("/v$vid/staff#staff", 'post')
- if !grep $frm->{$_} ne $b4{$_}, keys %b4;
-
- $frm->{credits} = \@credits;
- $frm->{editsum} = 'Edit staff';
- my $nrev = $self->dbItemEdit ('v' => $v->{cid}, %$frm);
- return $self->resRedirect("/v$nrev->{iid}.$nrev->{rev}", 'post');
- }
- }
- $frm->{$_} //= $b4{$_} for keys %b4;
-
- my $title = mt ('_vnstaff_edit_title', $v->{title});
- $self->htmlHeader(title => $title, noindex => 1);
- $self->htmlMainTabs('v', $v, 'edit');
- $self->htmlEditMessage('v', $v, $title);
-
- $self->htmlForm({ frm => $frm, action => "/v$vid/staff/edit" },
- vnstaffe_credits => [ mt('_vnstaff_edit_credits'),
- [ hidden => short => 'vid' ],
- [ hidden => short => 'credits' ],
- [ static => nolabel => 1, content => sub {
- 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;
- }],
- ]);
-
- $self->htmlFooter;
-}
-
-
sub list {
my ($self, $char) = @_;
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index 13877fa2..1c41e991 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};
@@ -88,6 +88,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}}),
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 +109,7 @@ 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 => 'vnrelations', required => 0, default => '', maxlength => 5000 },
{ post => 'screenshots', required => 0, default => '', maxlength => 1000 },
{ post => 'editsum', required => 0, maxlength => 5000 },
@@ -125,6 +127,7 @@ sub edit {
my $anime = { map +($_=>1), grep /^[0-9]+$/, split /[ ,]+/, $frm->{anime} };
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} ];
$frm->{ihid} = $frm->{ihid}?1:0;
$frm->{ilock} = $frm->{ilock}?1:0;
@@ -134,6 +137,16 @@ sub edit {
$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;
+ # check for duplicate credits
+ my($checked_c, $last_c) = ([], []);
+ for my $c (sort { $a->[0] <=> $b->[0] || $a->[1] cmp $b->[1] } @$credits) {
+ # discard entries with identical name & role
+ next if $last_c->[0] == $c->[0] && $last_c->[1] eq $c->[1];
+ push @$checked_c, $c;
+ $last_c = $c;
+ }
+ $frm->{credits} = join '|||', map sprintf('%d-%s-%s', @$_), @$checked_c;
+
# weed out duplicate aliases
my %alias;
$frm->{alias} = join "\n", grep {
@@ -149,6 +162,7 @@ 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 => $checked_c,
anime => [ keys %$anime ],
relations => $relations,
screenshots => $screenshots,
@@ -258,6 +272,20 @@ sub _form {
end 'div';
}]],
+ vn_staff => [ mt('_vnedit_credits'),
+ [ hidden => short => 'credits' ],
+ [ static => nolabel => 1, content => sub {
+ 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;
+ }]],
+
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 be3f8a4c..5df2b097 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 ? ' changes' : ''),
+ what => 'extended anime relations screenshots rating ranking'.($rev ? ' credits changes' : ''),
$rev ? (rev => $rev) : (),
)->[0];
return $self->resNotFound if !$v->{id};
@@ -653,17 +653,17 @@ sub page {
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;
+ 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;
+ 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}/staff/edit", mt $hasstaff ? '_vnpage_staff_edit' : '_vnpage_staff_add';
+ 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;
@@ -690,7 +690,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,
@@ -708,6 +708,11 @@ sub _revision {
[ l_renai => htmlize => sub {
$_[0] ? sprintf '%1$s', xml_escape $_[0] : mt '_revision_nolink'
}],
+ [ credits => join => '
', split => sub {
+ my @r = map sprintf('%s [%s]%s',
+ $_->{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');
+ }],
[ relations => join => '
', split => sub {
my @r = map sprintf('[%s] %s: %s',
mt($_->{official} ? '_vndiff_rel_official' : '_vndiff_rel_unofficial'),
diff --git a/util/sql/staff.sql b/util/sql/staff.sql
index 074aacae..a53b8955 100644
--- a/util/sql/staff.sql
+++ b/util/sql/staff.sql
@@ -33,7 +33,8 @@ 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 ''
+ note varchar(250) NOT NULL DEFAULT '',
+ PRIMARY KEY (vid, aid, role)
);
CREATE TABLE chars_seiyuu (
--
cgit v1.2.3