1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
|
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} ?
( '(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, 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 image gender lang desc l_wp|;
$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;
|