summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--lib/VNDB/DB/Affiliates.pm6
-rw-r--r--lib/VNDB/DB/Chars.pm166
-rw-r--r--lib/VNDB/DB/Discussions.pm4
-rw-r--r--lib/VNDB/DB/Misc.pm31
-rw-r--r--lib/VNDB/DB/Producers.pm83
-rw-r--r--lib/VNDB/DB/Releases.pm176
-rw-r--r--lib/VNDB/DB/Staff.pm147
-rw-r--r--lib/VNDB/DB/Tags.pm6
-rw-r--r--lib/VNDB/DB/ULists.pm48
-rw-r--r--lib/VNDB/DB/VN.pm224
-rw-r--r--lib/VNDB/Handler/Chars.pm11
-rw-r--r--lib/VNDB/Handler/Misc.pm4
-rw-r--r--lib/VNDB/Handler/Producers.pm13
-rw-r--r--lib/VNDB/Handler/Releases.pm11
-rw-r--r--lib/VNDB/Handler/Staff.pm12
-rw-r--r--lib/VNDB/Handler/VNEdit.pm2
-rw-r--r--lib/VNDB/Handler/VNPage.pm12
-rw-r--r--lib/VNDB/Util/BrowseHTML.pm4
-rw-r--r--lib/VNDB/Util/CommonHTML.pm16
-rw-r--r--util/sql/tableattrs.sql1
20 files changed, 541 insertions, 436 deletions
diff --git a/lib/VNDB/DB/Affiliates.pm b/lib/VNDB/DB/Affiliates.pm
index 51f8c2dc..94dfd198 100644
--- a/lib/VNDB/DB/Affiliates.pm
+++ b/lib/VNDB/DB/Affiliates.pm
@@ -23,12 +23,12 @@ sub dbAffiliateGet {
defined($o{hidden}) ? ('!s af.hidden' => $o{hidden} ? '' : 'NOT') : (),
);
- my $join = $o{what} ? 'JOIN releases r ON r.id = af.rid JOIN releases_rev rr ON rr.id = r.latest' : '';
- my $select = $o{what} ? ', rr.title' : '';
+ my $join = $o{what} ? 'JOIN releases r ON r.id = af.rid' : '';
+ my $select = $o{what} ? ', r.title' : '';
my $order = sprintf {
id => 'af.id %s',
- rel => 'rr.title %s',
+ rel => 'r.title %s',
prio => 'af.priority %s',
url => 'af.url %s',
lastfetch => 'af.lastfetch %s',
diff --git a/lib/VNDB/DB/Chars.pm b/lib/VNDB/DB/Chars.pm
index 3db5f869..1eaa732f 100644
--- a/lib/VNDB/DB/Chars.pm
+++ b/lib/VNDB/DB/Chars.pm
@@ -5,10 +5,10 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbCharGet dbCharRevisionInsert dbCharImageId|;
+our @EXPORT = qw|dbCharGet dbCharGetRev dbCharRevisionInsert dbCharImageId|;
-# options: id rev instance tagspoil trait_inc trait_exc char what results page gender bloodt
+# options: id instance tagspoil trait_inc trait_exc char what results page gender bloodt
# bust_min bust_max waist_min waist_max hip_min hip_max height_min height_max weight_min weight_max role
# what: extended traits vns changes
sub dbCharGet {
@@ -24,33 +24,31 @@ sub dbCharGet {
$o{search} =~ s/%//g if $o{search};
my %where = (
- !$o{id} && !$o{rev} ? ( 'c.hidden = FALSE' => 1 ) : (),
- $o{id} ? (
- 'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
- $o{rev} ? ( 'h.rev = ?' => $o{rev} ) : (),
+ !$o{id} ? ( 'c.hidden = FALSE' => 1 ) : (),
+ $o{id} ? ( 'c.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
$o{notid} ? ( 'c.id <> ?' => $o{notid} ) : (),
- $o{instance} ? ( 'cr.main = ?' => $o{instance} ) : (),
- $o{vid} ? ( 'cr.id IN(SELECT cid FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (),
- defined $o{gender} ? ( 'cr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
- defined $o{bloodt} ? ( 'cr.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (),
- defined $o{bust_min} ? ( 'cr.s_bust >= ?' => $o{bust_min} ) : (),
- defined $o{bust_max} ? ( 'cr.s_bust <= ? AND cr.s_bust > 0' => $o{bust_max} ) : (),
- defined $o{waist_min} ? ( 'cr.s_waist >= ?' => $o{waist_min} ) : (),
- defined $o{waist_max} ? ( 'cr.s_waist <= ? AND cr.s_waist > 0' => $o{waist_max} ) : (),
- defined $o{hip_min} ? ( 'cr.s_hip >= ?' => $o{hip_min} ) : (),
- defined $o{hip_max} ? ( 'cr.s_hip <= ? AND cr.s_hip > 0' => $o{hip_max} ) : (),
- defined $o{height_min} ? ( 'cr.height >= ?' => $o{height_min} ) : (),
- defined $o{height_max} ? ( 'cr.height <= ? AND cr.height > 0' => $o{height_max} ) : (),
- defined $o{weight_min} ? ( 'cr.weight >= ?' => $o{weight_min} ) : (),
- defined $o{weight_max} ? ( 'cr.weight <= ? AND cr.weight > 0' => $o{weight_max} ) : (),
+ $o{instance} ? ( 'c.main = ?' => $o{instance} ) : (),
+ $o{vid} ? ( 'c.id IN(SELECT id FROM chars_vns WHERE vid = ?)' => $o{vid} ) : (),
+ defined $o{gender} ? ( 'c.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
+ defined $o{bloodt} ? ( 'c.bloodt IN(!l)' => [ ref $o{bloodt} ? $o{bloodt} : [$o{bloodt}] ]) : (),
+ defined $o{bust_min} ? ( 'c.s_bust >= ?' => $o{bust_min} ) : (),
+ defined $o{bust_max} ? ( 'c.s_bust <= ? AND c.s_bust > 0' => $o{bust_max} ) : (),
+ defined $o{waist_min} ? ( 'c.s_waist >= ?' => $o{waist_min} ) : (),
+ defined $o{waist_max} ? ( 'c.s_waist <= ? AND c.s_waist > 0' => $o{waist_max} ) : (),
+ defined $o{hip_min} ? ( 'c.s_hip >= ?' => $o{hip_min} ) : (),
+ defined $o{hip_max} ? ( 'c.s_hip <= ? AND c.s_hip > 0' => $o{hip_max} ) : (),
+ defined $o{height_min} ? ( 'c.height >= ?' => $o{height_min} ) : (),
+ defined $o{height_max} ? ( 'c.height <= ? AND c.height > 0' => $o{height_max} ) : (),
+ defined $o{weight_min} ? ( 'c.weight >= ?' => $o{weight_min} ) : (),
+ defined $o{weight_max} ? ( 'c.weight <= ? AND c.weight > 0' => $o{weight_max} ) : (),
$o{search} ? (
- '(cr.name ILIKE ? OR cr.original ILIKE ? OR cr.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
+ '(c.name ILIKE ? OR c.original ILIKE ? OR c.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
$o{char} ? (
- 'LOWER(SUBSTR(cr.name, 1, 1)) = ?' => $o{char} ) : (),
+ 'LOWER(SUBSTR(c.name, 1, 1)) = ?' => $o{char} ) : (),
defined $o{char} && !$o{char} ? (
- '(ASCII(cr.name) < 97 OR ASCII(cr.name) > 122) AND (ASCII(cr.name) < 65 OR ASCII(cr.name) > 90)' => 1 ) : (),
+ '(ASCII(c.name) < 97 OR ASCII(c.name) > 122) AND (ASCII(c.name) < 65 OR ASCII(c.name) > 90)' => 1 ) : (),
$o{role} ? (
- 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.cid = cr.id AND cvi.role IN(!l))',
+ 'EXISTS(SELECT 1 FROM chars_vns cvi WHERE cvi.id = c.id AND cvi.role IN(!l))',
[ ref $o{role} ? $o{role} : [$o{role}] ] ) : (),
$o{trait_inc} ? (
'c.id IN(SELECT cid FROM traits_chars WHERE tid IN(!l) AND spoil <= ? GROUP BY cid HAVING COUNT(tid) = ?)',
@@ -59,75 +57,103 @@ sub dbCharGet {
'c.id NOT IN(SELECT cid FROM traits_chars WHERE tid IN(!l))' => [ ref $o{trait_exc} ? $o{trait_exc} : [$o{trait_exc}] ] ) : (),
);
- my @select = (qw|c.id cr.name cr.original cr.gender|, 'cr.id AS cid');
- push @select, qw|c.hidden c.locked cr.alias cr.desc cr.image cr.b_month cr.b_day cr.s_bust cr.s_waist cr.s_hip cr.height cr.weight cr.bloodt cr.main cr.main_spoil| if $o{what} =~ /extended/;
- push @select, qw|h.requester h.comments c.latest u.username h.rev h.ihid h.ilock|, "extract('epoch' from h.added) as added" if $o{what} =~ /changes/;
-
- my @join;
- push @join, $o{rev} ? 'JOIN chars c ON c.id = cr.cid' : 'JOIN chars c ON cr.id = c.latest';
- push @join, 'JOIN changes h ON h.id = cr.id' if $o{what} =~ /changes/ || $o{rev};
- push @join, 'JOIN users u ON u.id = h.requester' if $o{what} =~ /changes/;
+ my @select = (qw|c.id c.name c.original c.gender|);
+ push @select, qw|c.hidden c.locked c.alias c.desc c.image c.b_month c.b_day c.s_bust c.s_waist c.s_hip c.height c.weight c.bloodt c.main c.main_spoil| if $o{what} =~ /extended/;
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
- FROM chars_rev cr
- !s
+ FROM chars c
!W
- ORDER BY cr.name|,
- join(', ', @select), join(' ', @join), \%where
+ ORDER BY c.name|,
+ join(', ', @select), \%where
);
- if(@$r && $o{what} =~ /vns|traits|seiyuu/) {
+ return _enrich($self, $r, $np, 0, $o{what}, $o{vid});
+}
+
+
+sub dbCharGetRev {
+ my $self = shift;
+ my %o = (what => '', @_);
+
+ $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'c\' AND itemid = ?', $o{id})->{rev};
+
+ my $select = 'c.itemid AS id, ch.name, ch.original, ch.gender';
+ $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+ $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+ $select .= ', ch.alias, ch.desc, ch.image, ch.b_month, ch.b_day, ch.s_bust, ch.s_waist, ch.s_hip, ch.height, ch.weight, ch.bloodt, ch.main, ch.main_spoil, co.hidden, co.locked' if $o{what} =~ /extended/;
+
+ my $r = $self->dbAll(q|
+ SELECT !s
+ FROM changes c
+ JOIN chars co ON co.id = c.itemid
+ JOIN chars_hist ch ON ch.chid = c.id
+ JOIN users u ON u.id = c.requester
+ WHERE c.type = 'c' AND c.itemid = ? AND c.rev = ?|,
+ $select, $o{id}, $o{rev}
+ );
+
+ return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+ my($self, $r, $np, $rev, $what, $vid) = @_;
+
+ if(@$r && $what =~ /vns|traits/) {
+ my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
my %r = map {
$_->{traits} = [];
$_->{vns} = [];
- $_->{seiyuu} = [];
- ($_->{cid}, $_)
+ ($_->{$col}, $_)
} @$r;
- if($o{what} =~ /traits/) {
- push @{$r{ delete $_->{cid} }{traits}}, $_ for (@{$self->dbAll(q|
- SELECT ct.cid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname
- FROM chars_traits ct
+ if($what =~ /traits/) {
+ push @{$r{ delete $_->{xid} }{traits}}, $_ for (@{$self->dbAll(qq|
+ SELECT ct.$colname AS xid, ct.tid, ct.spoil, t.name, t.sexual, t."group", tg.name AS groupname
+ FROM chars_traits$hist ct
JOIN traits t ON t.id = ct.tid
- LEFT JOIN traits tg ON tg.id = t."group"
- WHERE cid IN(!l)
+ JOIN traits tg ON tg.id = t."group"
+ WHERE ct.$colname IN(!l)
ORDER BY tg."order", t.name|, [ keys %r ]
)});
}
- if($o{what} =~ /vns(?:\((\d+)\))?/) {
- push @{$r{ delete $_->{cid} }{vns}}, $_ for (@{$self->dbAll(q|
- SELECT cv.cid, cv.vid, cv.rid, cv.spoil, cv.role, vr.title AS vntitle, rr.title AS rtitle
- FROM chars_vns cv
+ if($what =~ /vns(?:\((\d+)\))?/) {
+ push @{$r{ delete $_->{xid} }{vns}}, $_ for (@{$self->dbAll("
+ SELECT cv.$colname AS xid, cv.vid, cv.rid, cv.spoil, cv.role, v.title AS vntitle, r.title AS rtitle
+ FROM chars_vns$hist cv
JOIN vn v ON cv.vid = v.id
- JOIN vn_rev vr ON vr.id = v.latest
LEFT JOIN releases r ON cv.rid = r.id
- LEFT JOIN releases_rev rr ON rr.id = r.latest
!W
- ORDER BY v.c_released|,
- { 'cv.cid IN(!l)' => [[keys %r]], $1 ? ('cv.vid = ?', $1) : () }
+ ORDER BY v.c_released",
+ { "cv.$colname 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}) : (),
- }
- )});
- }
+ # Depends on the VN revision rather than char revision
+ if(@$r && $what =~ /seiyuu/) {
+ my %r = map {
+ $_->{seiyuu} = [];
+ ($_->{id}, $_)
+ } @$r;
+
+ push @{$r{ delete $_->{cid} }{seiyuu}}, $_ for (@{$self->dbAll(q|
+ SELECT vs.cid, s.id AS sid, sa.name, sa.original, vs.note, v.id AS vid, v.title AS vntitle
+ FROM vn_seiyuu vs
+ JOIN staff_alias sa ON sa.aid = vs.aid
+ JOIN staff s ON s.id = sa.id
+ JOIN vn v ON v.id = vs.id
+ !W
+ ORDER BY v.c_released, sa.name|, {
+ 's.hidden = FALSE' => 1,
+ 'vs.cid IN(!l)' => [[ keys %r ]],
+ $vid ? ('v.id = ?' => $vid) : (),
+ }
+ )});
}
+
return wantarray ? ($r, $np) : $r;
}
diff --git a/lib/VNDB/DB/Discussions.pm b/lib/VNDB/DB/Discussions.pm
index 2be975db..16d33c4a 100644
--- a/lib/VNDB/DB/Discussions.pm
+++ b/lib/VNDB/DB/Discussions.pm
@@ -86,12 +86,10 @@ sub dbThreadGet {
}
if($o{what} =~ /boardtitles/) {
push(@{$r->[$r{$_->{tid}}]{boards}}, $_) for (@{$self->dbAll(q|
- SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, vr.title, pr.name) AS title, COALESCE(u.username, vr.original, pr.original) AS original
+ SELECT tb.tid, tb.type, tb.iid, COALESCE(u.username, v.title, p.name) AS title, COALESCE(u.username, v.original, p.original) AS original
FROM threads_boards tb
LEFT JOIN vn v ON tb.type = 'v' AND v.id = tb.iid
- LEFT JOIN vn_rev vr ON vr.id = v.latest
LEFT JOIN producers p ON tb.type = 'p' AND p.id = tb.iid
- LEFT JOIN producers_rev pr ON pr.id = p.latest
LEFT JOIN users u ON tb.type = 'u' AND u.id = tb.iid
WHERE tb.tid IN(!l)|,
[ keys %r ]
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm
index 8927e1ec..efa0ab34 100644
--- a/lib/VNDB/DB/Misc.pm
+++ b/lib/VNDB/DB/Misc.pm
@@ -47,10 +47,8 @@ sub dbItemEdit {
}
-# Options: type, iid, uid, auto, hidden, edit, page, results, what, releases
+# Options: type, itemid, uid, auto, hidden, edit, page, results, what, releases
# what: item user
-# Not very fast in each situation. Can be further optimized by: putting indexes
-# on *_rev.?id, or by caching iid, ititle and ihidden in the changes table.
sub dbRevisionGet {
my($self, %o) = @_;
$o{results} ||= 10;
@@ -59,7 +57,7 @@ sub dbRevisionGet {
$o{hidden} ||= 0;
$o{edit} ||= 0; # 0:both, -1:new, 1:edits
$o{what} ||= '';
- $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{iid};
+ $o{releases} = 0 if !$o{type} || $o{type} ne 'v' || !$o{itemid};
my %tables = qw|v vn r releases p producers c chars s staff|;
# what types should we join?
@@ -72,43 +70,44 @@ sub dbRevisionGet {
my %where = (
$o{releases} ? (
- q{((h.type = 'v' AND vr.vid = ?) OR (h.type = 'r' AND h.id = ANY(ARRAY(SELECT rv.rid FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{iid}, $o{iid}],
+ # This selects all changes of releases that are currently linked to the VN, not release revisions that are linked to the VN.
+ # The latter seems more useful, but is also a lot more expensive.
+ q{((h.type = 'v' AND h.itemid = ?) OR (h.type = 'r' AND h.itemid = ANY(ARRAY(SELECT rv.id FROM releases_vn rv WHERE rv.vid = ?))))} => [$o{itemid}, $o{itemid}],
) : (
$o{type} ? (
'h.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (),
- $o{iid} ? (
- '!sr.!sid = ?' => [ $o{type}, $o{type}, $o{iid} ] ) : (),
+ $o{itemid} ? (
+ 'h.itemid = ?' => [ $o{itemid} ] ) : (),
),
$o{uid} ? (
'h.requester = ?' => $o{uid} ) : (),
$o{auto} ? (
'h.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (),
$o{hidden} ? (
- '('.join(' OR ', map sprintf('%s.hidden IS NOT NULL AND %s %1$s.hidden', $_, $o{hidden} == 1 ? 'NOT' : ''), @types).')' => 1 ) : (),
+ ($o{hidden} == 1 ? 'NOT' : '').' COALESCE('.join(',', map "${_}.hidden", @types).')' => 1 ) : (),
$o{edit} ? (
'h.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (),
);
my @join = (
- $o{iid} || $o{what} =~ /item/ || $o{hidden} || $o{releases} ? (
- map sprintf(q|LEFT JOIN %s_rev %sr ON h.type = '%2$s' AND h.id = %2$sr.id|, $tables{$_}, $_), @types
+ $o{what} =~ /item/ || $o{hidden} ? (
+ map sprintf(q|LEFT JOIN %s_hist %sh ON h.type = '%2$s' AND h.id = %2$sh.chid|, $tables{$_}, $_), @types
) : (),
$o{hidden} ? (
- map sprintf(q|LEFT JOIN %s %s ON h.type = '%2$s' AND %2$sr.%2$sid = %2$s.id|, $tables{$_}, $_), @types
+ map sprintf(q|LEFT JOIN %s %s ON h.type = '%2$s' AND h.itemid = %2$s.id|, $tables{$_}, $_), @types
) : (),
$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;
+ push @join, 'LEFT JOIN staff_alias_hist sah ON sah.chid = h.id AND sah.aid = sh.aid' if grep /s/, @types;
- my %tcolumns = qw(v vr.title r rr.title p pr.name c cr.name s sa.name);
+ my %tcolumns = qw(v vh.title r rh.title p ph.name c ch.name s sah.name);
my @select = (
- qw|h.id h.type h.requester h.comments h.rev|,
+ qw|h.id h.type h.itemid 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 $tcolumns{$_}, @types).') AS ititle',
- 'COALESCE('.join(', ', map /s/ ? 'sa.original' : "${_}r.original", @types).') AS ioriginal',
+ 'COALESCE('.join(', ', map /s/ ? 'sah.original' : "${_}h.original", @types).') AS ioriginal',
) : (),
);
diff --git a/lib/VNDB/DB/Producers.pm b/lib/VNDB/DB/Producers.pm
index 3ff70ae1..a9636282 100644
--- a/lib/VNDB/DB/Producers.pm
+++ b/lib/VNDB/DB/Producers.pm
@@ -5,11 +5,11 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbProducerGet dbProducerRevisionInsert|;
+our @EXPORT = qw|dbProducerGet dbProducerGetRev dbProducerRevisionInsert|;
-# options: results, page, id, search, char, rev
-# what: extended changes relations relgraph
+# options: results, page, id, search, char
+# what: extended relations relgraph
sub dbProducerGet {
my $self = shift;
my %o = (
@@ -22,52 +22,79 @@ sub dbProducerGet {
$o{search} =~ s/%//g if $o{search};
my %where = (
- !$o{id} && !$o{rev} ? (
+ !$o{id} ? (
'p.hidden = FALSE' => 1 ) : (),
$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 ] ) : (),
+ '(p.name ILIKE ? OR p.original ILIKE ? OR p.alias ILIKE ?)', [ map '%'.$o{search}.'%', 1..3 ] ) : (),
$o{char} ? (
- 'LOWER(SUBSTR(pr.name, 1, 1)) = ?' => $o{char} ) : (),
+ 'LOWER(SUBSTR(p.name, 1, 1)) = ?' => $o{char} ) : (),
defined $o{char} && !$o{char} ? (
- '(ASCII(pr.name) < 97 OR ASCII(pr.name) > 122) AND (ASCII(pr.name) < 65 OR ASCII(pr.name) > 90)' => 1 ) : (),
- $o{rev} ? (
- 'c.rev = ?' => $o{rev} ) : (),
+ '(ASCII(p.name) < 97 OR ASCII(p.name) > 122) AND (ASCII(p.name) < 65 OR ASCII(p.name) > 90)' => 1 ) : (),
);
- my @join;
- push @join, $o{rev} ? 'JOIN producers p ON p.id = pr.pid' : 'JOIN producers p ON pr.id = p.latest';
- push @join, 'JOIN changes c ON c.id = pr.id' if $o{what} =~ /changes/ || $o{rev};
- push @join, 'JOIN users u ON u.id = c.requester' if $o{what} =~ /changes/;
- push @join, 'JOIN relgraphs pg ON pg.id = p.rgraph' if $o{what} =~ /relgraph/;
+ my $join = $o{what} =~ /relgraph/ ? 'JOIN relgraphs pg ON pg.id = p.rgraph' : '';
- my $select = 'p.id, pr.type, pr.name, pr.original, pr.lang, pr.id AS cid, p.rgraph';
- $select .= ', pr.desc, pr.alias, pr.website, pr.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/;
- $select .= q|, extract('epoch' from c.added) as added, c.requester, c.comments, p.latest, pr.id AS cid, u.username, c.rev, c.ihid, c.ilock| if $o{what} =~ /changes/;
+ my $select = 'p.id, p.type, p.name, p.original, p.lang, p.rgraph';
+ $select .= ', p.desc, p.alias, p.website, p.l_wp, p.hidden, p.locked' if $o{what} =~ /extended/;
$select .= ', pg.svg' if $o{what} =~ /relgraph/;
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
- FROM producers_rev pr
+ FROM producers p
!s
!W
- ORDER BY pr.name ASC|,
- $select, join(' ', @join), \%where,
+ ORDER BY p.name ASC|,
+ $select, $join, \%where,
);
- if(@$r && $o{what} =~ /relations/) {
+ return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+# options: id, rev, what
+# what: extended relations
+sub dbProducerGetRev {
+ my $self = shift;
+ my %o = (what => '', @_);
+
+ $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'p\' AND itemid = ?', $o{id})->{rev};
+
+ my $select = 'c.itemid AS id, p.type, p.name, p.original, p.lang, po.rgraph';
+ $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+ $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+ $select .= ', p.desc, p.alias, p.website, p.l_wp, po.hidden, po.locked' if $o{what} =~ /extended/;
+
+ my $r = $self->dbAll(q|
+ SELECT !s
+ FROM changes c
+ JOIN producers po ON po.id = c.itemid
+ JOIN producers_hist p ON p.chid = c.id
+ JOIN users u ON u.id = c.requester
+ WHERE c.type = 'p' AND c.itemid = ? AND c.rev = ?|,
+ $select, $o{id}, $o{rev}
+ );
+
+ return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+ my($self, $r, $np, $rev, $what) = @_;
+
+ if(@$r && $what =~ /relations/) {
+ my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
my %r = map {
$r->[$_]{relations} = [];
- ($r->[$_]{cid}, $_)
+ ($r->[$_]{$col}, $_)
} 0..$#$r;
- push @{$r->[$r{$_->{pid1}}]{relations}}, $_ for(@{$self->dbAll(q|
- SELECT rel.pid1, rel.pid2 AS id, rel.relation, pr.name, pr.original
- FROM producers_relations rel
- JOIN producers p ON rel.pid2 = p.id
- JOIN producers_rev pr ON p.latest = pr.id
- WHERE rel.pid1 IN(!l)|,
+ push @{$r->[$r{$_->{xid}}]{relations}}, $_ for(@{$self->dbAll(qq|
+ SELECT rel.$colname AS xid, rel.pid AS id, rel.relation, p.name, p.original
+ FROM producers_relations$hist rel
+ JOIN producers p ON rel.pid = p.id
+ WHERE rel.$colname IN(!l)|,
[ keys %r ]
)});
}
diff --git a/lib/VNDB/DB/Releases.pm b/lib/VNDB/DB/Releases.pm
index 3c19cfb2..0a8f5363 100644
--- a/lib/VNDB/DB/Releases.pm
+++ b/lib/VNDB/DB/Releases.pm
@@ -7,12 +7,12 @@ use POSIX 'strftime';
use Exporter 'import';
use VNDB::Func 'gtintype';
-our @EXPORT = qw|dbReleaseGet dbReleaseRevisionInsert|;
+our @EXPORT = qw|dbReleaseGet dbReleaseGetRev dbReleaseRevisionInsert|;
-# Options: id vid pid rev released page results what med sort reverse date_before date_after
+# Options: id vid pid released page results what med sort reverse date_before date_after
# plat lang olang type minage search resolution freeware doujin voiced ani_story ani_ero
-# What: extended changes vn producers platforms media affiliates
+# What: extended vn producers platforms media affiliates
# Sort: title released minage
sub dbReleaseGet {
my($self, %o) = @_;
@@ -23,34 +23,33 @@ sub dbReleaseGet {
$o{med} = [ $o{med} ] if $o{med} && !ref $o{med};
my @where = (
- !$o{id} && !$o{rev} ? ( 'r.hidden = FALSE' => 0 ) : (),
+ !$o{id} ? ( 'r.hidden = FALSE' => 0 ) : (),
$o{id} ? ( 'r.id = ?' => $o{id} ) : (),
- $o{rev} ? ( 'c.rev = ?' => $o{rev} ) : (),
$o{vid} ? ( 'rv.vid IN(!l)' => [ ref $o{vid} ? $o{vid} : [$o{vid}] ] ) : (),
$o{pid} ? ( 'rp.pid = ?' => $o{pid} ) : (),
- defined $o{patch} ? ( 'rr.patch = ?' => $o{patch} == 1 ? 1 : 0) : (),
- defined $o{freeware} ? ( 'rr.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (),
- defined $o{doujin} ? ( 'rr.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (),
- defined $o{type} ? ( 'rr.type = ?' => $o{type} ) : (),
- defined $o{date_before} ? ( 'rr.released <= ?' => $o{date_before} ) : (),
- defined $o{date_after} ? ( 'rr.released >= ?' => $o{date_after} ) : (),
- defined $o{minage} ? ( 'rr.minage IN(!l)' => [ ref $o{minage} ? $o{minage} : [$o{minage}] ] ) : (),
- defined $o{resolution} ? ( 'rr.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (),
- defined $o{voiced} ? ( 'rr.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (),
- defined $o{ani_story} ? ( 'rr.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (),
- defined $o{ani_ero} ? ( 'rr.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (),
- defined $o{released} ? ( 'rr.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
+ defined $o{patch} ? ( 'r.patch = ?' => $o{patch} == 1 ? 1 : 0) : (),
+ defined $o{freeware} ? ( 'r.freeware = ?' => $o{freeware} == 1 ? 1 : 0) : (),
+ defined $o{doujin} ? ( 'r.doujin = ?' => $o{doujin} == 1 ? 1 : 0) : (),
+ defined $o{type} ? ( 'r.type = ?' => $o{type} ) : (),
+ defined $o{date_before} ? ( 'r.released <= ?' => $o{date_before} ) : (),
+ defined $o{date_after} ? ( 'r.released >= ?' => $o{date_after} ) : (),
+ defined $o{minage} ? ( 'r.minage IN(!l)' => [ ref $o{minage} ? $o{minage} : [$o{minage}] ] ) : (),
+ defined $o{resolution} ? ( 'r.resolution IN(!l)' => [ ref $o{resolution} ? $o{resolution} : [$o{resolution}] ] ) : (),
+ defined $o{voiced} ? ( 'r.voiced IN(!l)' => [ ref $o{voiced} ? $o{voiced} : [$o{voiced}] ] ) : (),
+ defined $o{ani_story} ? ( 'r.ani_story IN(!l)' => [ ref $o{ani_story} ? $o{ani_story} : [$o{ani_story}] ] ) : (),
+ defined $o{ani_ero} ? ( 'r.ani_ero IN(!l)' => [ ref $o{ani_ero} ? $o{ani_ero} : [$o{ani_ero}] ] ) : (),
+ defined $o{released} ? ( 'r.released !s ?' => [ $o{released} ? '<=' : '>', strftime('%Y%m%d', gmtime) ] ) : (),
$o{lang} ? (
- 'rr.id IN(SELECT irl.rid FROM releases_lang irl JOIN releases ir ON ir.latest = irl.rid WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
+ 'r.id IN(SELECT irl.id FROM releases_lang irl WHERE irl.lang IN(!l))' => [ ref $o{lang} ? $o{lang} : [ $o{lang} ] ] ) : (),
$o{olang} ? (
- 'rr.id IN(SELECT irv.rid FROM releases_vn irv JOIN releases ir ON ir.latest = irv.rid JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (),
+ 'r.id IN(SELECT irv.id FROM releases_vn irv JOIN vn v ON irv.vid = v.id WHERE v.c_olang && ARRAY[!l]::language[])' => [ ref $o{olang} ? $o{olang} : [ $o{olang} ] ] ) : (),
$o{plat} ? ('('.join(' OR ',
- grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.rid = r.latest)' : (),
- grep(!/^unk$/, @{$o{plat}}) ? 'rr.id IN(SELECT irp.rid FROM releases_platforms irp JOIN releases ir ON ir.latest = irp.rid WHERE irp.platform IN(!l))' : (),
+ grep(/^unk$/, @{$o{plat}}) ? 'NOT EXISTS(SELECT 1 FROM releases_platforms irp WHERE irp.id = r.id)' : (),
+ grep(!/^unk$/, @{$o{plat}}) ? 'r.id IN(SELECT irp.id FROM releases_platforms irp WHERE irp.platform IN(!l))' : (),
).')', [ [ grep !/^unk$/, @{$o{plat}} ] ]) : (),
$o{med} ? ('('.join(' OR ',
- grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.rid = r.latest)' : (),
- grep(!/^unk$/, @{$o{med}}) ? 'rr.id IN(SELECT irm.rid FROM releases_media irm JOIN releases ir ON ir.latest = irm.rid WHERE irm.medium IN(!l))' : ()
+ grep(/^unk$/, @{$o{med}}) ? 'NOT EXISTS(SELECT 1 FROM releases_media irm WHERE irm.id = r.id)' : (),
+ grep(!/^unk$/, @{$o{med}}) ? 'r.id IN(SELECT irm.id FROM releases_media irm WHERE irm.medium IN(!l))' : ()
).')', [ [ grep(!/^unk$/, @{$o{med}}) ] ]) : (),
);
@@ -58,110 +57,135 @@ sub dbReleaseGet {
for (split /[ -,._]/, $o{search}) {
s/%//g;
if(/^\d+$/ && gtintype($_)) {
- push @where, 'rr.gtin = ?', $_;
+ push @where, 'r.gtin = ?', $_;
} elsif(length($_) > 0) {
$_ = "%$_%";
- push @where, '(rr.title ILIKE ? OR rr.original ILIKE ? OR rr.catalog = ?)',
+ push @where, '(r.title ILIKE ? OR r.original ILIKE ? OR r.catalog = ?)',
[ $_, $_, $_ ];
}
}
}
my @join = (
- $o{rev} ? 'JOIN releases r ON r.id = rr.rid' : 'JOIN releases r ON rr.id = r.latest',
- $o{vid} ? 'JOIN releases_vn rv ON rv.rid = rr.id' : (),
- $o{pid} ? 'JOIN releases_producers rp ON rp.rid = rr.id' : (),
- $o{what} =~ /changes/ || $o{rev} ? (
- 'JOIN changes c ON c.id = rr.id',
- 'JOIN users u ON u.id = c.requester'
- ) : (),
+ $o{vid} ? 'JOIN releases_vn rv ON rv.id = r.id' : (),
+ $o{pid} ? 'JOIN releases_producers rp ON rp.id = r.id' : (),
);
my @select = (
- qw|r.id rr.title rr.original rr.website rr.released rr.minage rr.type rr.patch|,
- 'rr.id AS cid',
- $o{what} =~ /extended/ ? qw|rr.notes rr.catalog rr.gtin rr.resolution rr.voiced rr.freeware rr.doujin rr.ani_story rr.ani_ero r.hidden r.locked| : (),
- $o{what} =~ /changes/ ?
- (qw|c.requester c.comments r.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (),
+ qw|r.id r.title r.original r.website r.released r.minage r.type r.patch|,
+ $o{what} =~ /extended/ ? qw|r.notes r.catalog r.gtin r.resolution r.voiced r.freeware r.doujin r.ani_story r.ani_ero r.hidden r.locked| : (),
$o{pid} ? ('rp.developer', 'rp.publisher') : (),
);
my $order = sprintf {
- title => 'rr.title %s, rr.released %1$s',
- type => 'rr.patch %s, rr.type %1$s, rr.released %1$s, rr.title %1$s',
- publication => 'rr.doujin %s, rr.freeware %1$s, rr.patch %1$s, rr.released %1$s, rr.title %1$s',
- resolution => 'rr.resolution %s, rr.patch %2$s, rr.released %1$s, rr.title %1$s',
- voiced => 'rr.voiced %s, rr.patch %2$s, rr.released %1$s, rr.title %1$s',
- ani_ero => 'rr.ani_story %s, rr.ani_ero %1$s, rr.patch %2$s, rr.released %1$s, rr.title %1$s',
- released => 'rr.released %s, r.id %1$s',
- minage => 'rr.minage %s, rr.released %1$s, rr.title %1$s',
- notes => 'rr.notes %s, rr.released %1$s, rr.title %1$s',
+ title => 'r.title %s, r.released %1$s',
+ type => 'r.patch %s, r.type %1$s, r.released %1$s, r.title %1$s',
+ publication => 'r.doujin %s, r.freeware %1$s, r.patch %1$s, r.released %1$s, r.title %1$s',
+ resolution => 'r.resolution %s, r.patch %2$s, r.released %1$s, r.title %1$s',
+ voiced => 'r.voiced %s, r.patch %2$s, r.released %1$s, r.title %1$s',
+ ani_ero => 'r.ani_story %s, r.ani_ero %1$s, r.patch %2$s, r.released %1$s, r.title %1$s',
+ released => 'r.released %s, r.id %1$s',
+ minage => 'r.minage %s, r.released %1$s, r.title %1$s',
+ notes => 'r.notes %s, r.released %1$s, r.title %1$s',
}->{ $o{sort}||'released' }, $o{reverse} ? 'DESC' : 'ASC', !$o{reverse} ? 'DESC' : 'ASC';
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
- FROM releases_rev rr
+ FROM releases r
!s
!W
ORDER BY !s|,
join(', ', @select), join(' ', @join), \@where, $order
);
+ return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+# options: id, rev, what
+# what: extended vn producers platforms media affiliates
+sub dbReleaseGetRev {
+ my $self = shift;
+ my %o = (what => '', @_);
+
+ $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'r\' AND itemid = ?', $o{id})->{rev};
+
+ my $select = 'c.itemid AS id, r.title, r.original, r.website, r.released, r.minage, r.type, r.patch';
+ $select .= ', r.notes, r.catalog, r.gtin, r.resolution, r.voiced, r.freeware, r.doujin, r.ani_story, r.ani_ero, ro.hidden, ro.locked' if $o{what} =~ /extended/;
+ $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+ $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+
+ my $r = $self->dbAll(q|
+ SELECT !s
+ FROM changes c
+ JOIN releases ro ON ro.id = c.itemid
+ JOIN releases_hist r ON r.chid = c.id
+ JOIN users u ON u.id = c.requester
+ WHERE c.type = 'r' AND c.itemid = ? AND c.rev = ?|,
+ $select, $o{id}, $o{rev}
+ );
+
+ return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+ my($self, $r, $np, $rev, $what) = @_;
+
if(@$r) {
+ my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
my %r = map {
$r->[$_]{producers} = [];
$r->[$_]{platforms} = [];
$r->[$_]{media} = [];
$r->[$_]{vn} = [];
$r->[$_]{languages} = [];
- ($r->[$_]{cid}, $_)
+ ($r->[$_]{$col}, $_)
} 0..$#$r;
- push(@{$r->[$r{$_->{rid}}]{languages}}, $_->{lang}) for (@{$self->dbAll(q|
- SELECT rid, lang
- FROM releases_lang
- WHERE rid IN(!l)|,
+ push(@{$r->[$r{$_->{xid}}]{languages}}, $_->{lang}) for (@{$self->dbAll("
+ SELECT $colname AS xid, lang
+ FROM releases_lang$hist
+ WHERE $colname IN(!l)",
[ keys %r ]
)});
- if($o{what} =~ /vn/) {
- push(@{$r->[$r{$_->{rid}}]{vn}}, $_) for (@{$self->dbAll(q|
- SELECT rv.rid, vr.vid, vr.title, vr.original
- FROM releases_vn rv
+ if($what =~ /vn/) {
+ push(@{$r->[$r{$_->{xid}}]{vn}}, $_) for (@{$self->dbAll("
+ SELECT rv.$colname AS xid, v.id AS vid, v.title, v.original
+ FROM releases_vn$hist rv
JOIN vn v ON v.id = rv.vid
- JOIN vn_rev vr ON vr.id = v.latest
- WHERE rv.rid IN(!l)
- ORDER BY vr.title|,
+ WHERE rv.$colname IN(!l)
+ ORDER BY v.title",
[ keys %r ]
)});
}
- if($o{what} =~ /producers/) {
- push(@{$r->[$r{$_->{rid}}]{producers}}, $_) for (@{$self->dbAll(q|
- SELECT rp.rid, rp.developer, rp.publisher, p.id, pr.name, pr.original, pr.type
- FROM releases_producers rp
+ if($what =~ /producers/) {
+ push(@{$r->[$r{$_->{xid}}]{producers}}, $_) for (@{$self->dbAll("
+ SELECT rp.$colname AS xid, rp.developer, rp.publisher, p.id, p.name, p.original, p.type
+ FROM releases_producers$hist rp
JOIN producers p ON rp.pid = p.id
- JOIN producers_rev pr ON pr.id = p.latest
- WHERE rp.rid IN(!l)
- ORDER BY pr.name|,
+ WHERE rp.$colname IN(!l)
+ ORDER BY p.name",
[ keys %r ]
)});
}
- if($o{what} =~ /platforms/) {
- push(@{$r->[$r{$_->{rid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll(q|
- SELECT rid, platform
- FROM releases_platforms
- WHERE rid IN(!l)|,
+ if($what =~ /platforms/) {
+ push(@{$r->[$r{$_->{xid}}]{platforms}}, $_->{platform}) for (@{$self->dbAll("
+ SELECT $colname AS xid, platform
+ FROM releases_platforms$hist
+ WHERE $colname IN(!l)",
[ keys %r ]
)});
}
- if($o{what} =~ /media/) {
- push(@{$r->[$r{$_->{rid}}]{media}}, $_) for (@{$self->dbAll(q|
- SELECT rid, medium, qty
- FROM releases_media
- WHERE rid IN(!l)|,
+ if($what =~ /media/) {
+ push(@{$r->[$r{$_->{xid}}]{media}}, $_) for (@{$self->dbAll("
+ SELECT $colname AS xid, medium, qty
+ FROM releases_media$hist
+ WHERE $colname IN(!l)",
[ keys %r ]
)});
}
diff --git a/lib/VNDB/DB/Staff.pm b/lib/VNDB/DB/Staff.pm
index 4dfe0756..8c6badd6 100644
--- a/lib/VNDB/DB/Staff.pm
+++ b/lib/VNDB/DB/Staff.pm
@@ -5,9 +5,9 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbStaffGet dbStaffRevisionInsert dbStaffAliasIds|;
+our @EXPORT = qw|dbStaffGet dbStaffGetRev dbStaffRevisionInsert dbStaffAliasIds|;
-# options: results, page, id, aid, search, exact, rev, truename, role, gender
+# options: results, page, id, aid, search, exact, truename, role, gender
# what: extended changes roles aliases
sub dbStaffGet {
my $self = shift;
@@ -31,15 +31,15 @@ sub dbStaffGet {
$o{search} =~ s/%//g if $o{search};
my %where = (
- !$o{id} && !$o{rev} ? ( 's.hidden = FALSE' => 1 ) : (),
+ !$o{id} ? ( '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{truename} ? ( 'sr.aid = sa.id' => 1 ) : (),
- defined $o{gender} ? ( 'sr.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
+ $o{id} || $o{truename} ? ( 's.aid = sa.aid' => 1 ) : (),
+ defined $o{gender} ? ( 's.gender IN(!l)' => [ ref $o{gender} ? $o{gender} : [$o{gender}] ]) : (),
defined $o{role} ? (
'('.join(' OR ',
- @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.latest = vs.vid WHERE vs.aid = sa.id AND vs.role IN(!l) AND NOT v.hidden)' ) : (),
- $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.latest = vsy.vid WHERE vsy.aid = sa.id AND NOT v.hidden)' ) : ()
+ @roles ? ( 'EXISTS(SELECT 1 FROM vn_staff vs JOIN vn v ON v.id = vs.id WHERE vs.aid = sa.aid AND vs.role IN(!l) AND NOT v.hidden)' ) : (),
+ $seiyuu ? ( 'EXISTS(SELECT 1 FROM vn_seiyuu vsy JOIN vn v ON v.id = vsy.id WHERE vsy.aid = sa.aid AND NOT v.hidden)' ) : ()
).')' => ( @roles ? [ \@roles ] : 1 ),
) : (),
$o{exact} ? ( '(sa.name = ? OR sa.original = ?)' => [ ($o{exact}) x 2 ] ) : (),
@@ -53,73 +53,100 @@ sub dbStaffGet {
$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 $select = 's.id, sa.aid, sa.name, sa.original, s.gender, s.lang';
+ $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, s.hidden, s.locked' if $o{what} =~ /extended/;
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
- FROM staff_rev sr
- !s
+ FROM staff s
+ JOIN staff_alias sa ON sa.id = s.id
!W
- !s|,
- $select, join(' ', @join), \%where, $order
+ ORDER BY sa.name|,
+ $select, \%where
);
- if (@$r && $o{what} =~ /roles|aliases/) {
+ return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+sub dbStaffGetRev {
+ my $self = shift;
+ my %o = (what => '', @_);
+
+ $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'s\' AND itemid = ?', $o{id})->{rev};
+
+ my $select = 'c.itemid AS id, sa.aid, sa.name, sa.original, s.gender, s.lang';
+ $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+ $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+ $select .= ', s.desc, s.l_wp, s.l_site, s.l_twitter, s.l_anidb, so.hidden, so.locked' if $o{what} =~ /extended/;
+
+ my $r = $self->dbAll(q|
+ SELECT !s
+ FROM changes c
+ JOIN staff so ON so.id = c.itemid
+ JOIN staff_hist s ON s.chid = c.id
+ JOIN staff_alias_hist sa ON sa.chid = c.id AND s.aid = sa.aid
+ JOIN users u ON u.id = c.requester
+ WHERE c.type = 's' AND c.itemid = ? AND c.rev = ?|,
+ $select, $o{id}, $o{rev}
+ );
+
+ return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+ my($self, $r, $np, $rev, $what) = @_;
+
+ # Role info is linked to VN revisions, so is independent of the selected staff revision
+ if(@$r && $what =~ /roles/) {
my %r = map {
$_->{roles} = [];
$_->{cast} = [];
+ ($_->{id}, $_);
+ } @$r;
+
+ push @{$r{ delete $_->{id} }{roles}}, $_ for (@{$self->dbAll(q|
+ SELECT sa.id, v.id AS vid, sa.name, sa.original, v.c_released, v.title, v.original AS t_original, vs.role, vs.note
+ FROM vn_staff vs
+ JOIN vn v ON v.id = vs.id
+ JOIN staff_alias sa ON vs.aid = sa.aid
+ WHERE sa.id IN(!l) AND NOT v.hidden
+ ORDER BY v.c_released ASC, v.title ASC, vs.role ASC|, [ keys %r ]
+ )});
+ push @{$r{ delete $_->{id} }{cast}}, $_ for (@{$self->dbAll(q|
+ SELECT sa.id, v.id AS vid, sa.name, sa.original, v.c_released, v.title, v.original AS t_original, c.id AS cid, c.name AS c_name, c.original AS c_original, vs.note
+ FROM vn_seiyuu vs
+ JOIN vn v ON v.id = vs.id
+ JOIN chars c ON c.id = vs.cid
+ JOIN staff_alias sa ON vs.aid = sa.aid
+ WHERE sa.id IN(!l) AND NOT v.hidden
+ ORDER BY v.c_released ASC, v.title ASC|, [ keys %r ]
+ )});
+ }
+
+ if(@$r && $what =~ /aliases/) {
+ my ($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
+ my %r = map {
$_->{aliases} = [];
- ($_->{cid}, $_);
+ ($_->{$col}, $_);
} @$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) AND NOT v.hidden
- 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) AND NOT v.hidden
- 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 ]
- )});
- }
+
+ push @{$r{ delete $_->{xid} }{aliases}}, $_ for (@{$self->dbAll("
+ SELECT s.$colname AS xid, sa.aid, sa.name, sa.original
+ FROM staff_alias$hist sa
+ JOIN staff$hist s ON s.$colname = sa.$colname
+ WHERE s.$colname IN(!l) AND s.aid <> sa.aid
+ 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 {
@@ -153,10 +180,10 @@ sub dbStaffRevisionInsert {
sub dbStaffAliasIds {
my($self, $sid) = @_;
return $self->dbAll(q|
- SELECT DISTINCT sa.id
- FROM staff_alias sa
- JOIN staff_rev sr ON sr.id = sa.rid
- WHERE sr.sid = ?|, $sid);
+ SELECT DISTINCT sa.aid
+ FROM changes c
+ JOIN staff_alias_hist sa ON sa.chid = c.id
+ WHERE c.type = \'s\' AND c.itemid = ?|, $sid);
}
1;
diff --git a/lib/VNDB/DB/Tags.pm b/lib/VNDB/DB/Tags.pm
index 37411a62..f3c901ab 100644
--- a/lib/VNDB/DB/Tags.pm
+++ b/lib/VNDB/DB/Tags.pm
@@ -184,11 +184,11 @@ sub dbTagLinks {
my @select = (
qw|tv.tag tv.vid tv.uid tv.vote tv.spoiler tv.ignore|, "EXTRACT('epoch' from tv.date) AS date",
- $o{what} =~ /details/ ? (qw|vr.title u.username t.name|) : (),
+ $o{what} =~ /details/ ? (qw|v.title u.username t.name|) : (),
);
my @join = $o{what} =~ /details/ ? (
- 'JOIN vn v ON v.id = tv.vid JOIN vn_rev vr ON vr.id = v.latest',
+ 'JOIN vn v ON v.id = tv.vid',
'JOIN users u ON u.id = tv.uid',
'JOIN tags t ON t.id = tv.tag'
) : ();
@@ -196,7 +196,7 @@ sub dbTagLinks {
my $order = !$o{sort} ? '' : 'ORDER BY '.{
username => 'u.username',
date => 'tv.date',
- title => 'vr.title',
+ title => 'v.title',
tag => 't.name',
}->{$o{sort}}.($o{reverse} ? ' DESC' : ' ASC');
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index 6831e579..23bde7cf 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -59,22 +59,21 @@ sub dbVNListList {
'vl.uid = ?' => $o{uid},
defined($o{voted}) ? ('vo.vote !s NULL' => $o{voted} ? 'IS NOT' : 'IS') : (),
defined($o{status})? ('vl.status = ?' => $o{status}) : (),
- $o{char} ? ('LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (),
+ $o{char} ? ('LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (),
defined $o{char} && !$o{char} ? (
- '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
+ '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
);
my $order = sprintf {
- title => 'vr.title %s',
- vote => 'vo.vote %s NULLS LAST, vr.title ASC',
+ title => 'v.title %s',
+ vote => 'vo.vote %s NULLS LAST, v.title ASC',
}->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC';
# execute query
my($r, $np) = $self->dbPage(\%o, qq|
- SELECT vr.vid, vr.title, vr.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote
+ SELECT vl.vid, v.title, v.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote
FROM vnlists vl
JOIN vn v ON v.id = vl.vid
- JOIN vn_rev vr ON vr.id = v.latest
LEFT JOIN votes vo ON vo.vid = vl.vid AND vo.uid = vl.uid
!W
ORDER BY !s|,
@@ -89,27 +88,26 @@ sub dbVNListList {
} @$r;
my $rel = $self->dbAll(q|
- SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.status
+ SELECT rv.vid, rl.rid, r.title, r.original, r.released, r.type, rl.status
FROM rlists rl
JOIN releases r ON rl.rid = r.id
- JOIN releases_rev rr ON rr.id = r.latest
- JOIN releases_vn rv ON rv.rid = r.latest
+ JOIN releases_vn rv ON rv.id = r.id
WHERE rl.uid = ?
AND rv.vid IN(!l)
- ORDER BY rr.released ASC|,
+ ORDER BY r.released ASC|,
$o{uid}, [ keys %vns ]
);
if(@$rel) {
- my %rel = map { $_->{latest} => [] } @$rel;
- push(@{$rel{$_->{rid}}}, $_->{lang}) for (@{$self->dbAll(q|
- SELECT rid, lang
+ my %rel = map { $_->{rid} => [] } @$rel;
+ push(@{$rel{$_->{id}}}, $_->{lang}) for (@{$self->dbAll(q|
+ SELECT id, lang
FROM releases_lang
- WHERE rid IN(!l)|,
+ WHERE id IN(!l)|,
[ keys %rel ]
)});
for(@$rel) {
- $_->{languages} = $rel{$_->{latest}};
+ $_->{languages} = $rel{$_->{rid}};
push @{$vns{$_->{vid}}}, $_;
}
}
@@ -189,9 +187,9 @@ sub dbVoteGet {
$o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (),
$o{hide} ? ( 'NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = n.uid AND key = \'hide_list\')' => 1 ) : (),
$o{hide_ign} ? ( '(NOT u.ign_votes OR u.id = ?)' => $self->authInfo->{id}||0 ) : (),
- $o{vn_char} ? ( 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{vn_char} ) : (),
+ $o{vn_char} ? ( 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{vn_char} ) : (),
defined $o{vn_char} && !$o{vn_char} ? (
- '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
+ '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
$o{user_char} ? ( 'LOWER(SUBSTR(u.username, 1, 1)) = ?' => $o{user_char} ) : (),
defined $o{user_char} && !$o{user_char} ? (
'(ASCII(u.username) < 97 OR ASCII(u.username) > 122) AND (ASCII(u.username) < 65 OR ASCII(u.username) > 90)' => 1 ) : (),
@@ -200,13 +198,12 @@ sub dbVoteGet {
my @select = (
qw|n.vid n.vote n.uid|, q|extract('epoch' from n.date) as date|,
$o{what} =~ /user/ ? ('u.username') : (),
- $o{what} =~ /vn/ ? (qw|vr.title vr.original|) : (),
+ $o{what} =~ /vn/ ? (qw|v.title v.original|) : (),
);
my @join = (
$o{what} =~ /vn/ ? (
'JOIN vn v ON v.id = n.vid',
- 'JOIN vn_rev vr ON vr.id = v.latest'
) : (),
$o{what} =~ /user/ || $o{hide} ? (
'JOIN users u ON u.id = n.uid'
@@ -216,8 +213,8 @@ sub dbVoteGet {
my $order = sprintf {
date => 'n.date %s',
username => 'u.username %s',
- title => 'vr.title %s',
- vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', vr.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''),
+ title => 'v.title %s',
+ vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', v.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''),
}->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
my($r, $np) = $self->dbPage(\%o, q|
@@ -302,15 +299,14 @@ sub dbWishListGet {
my $select = q|wl.vid, wl.wstat, extract('epoch' from wl.added) AS added|;
my @join;
if($o{what} =~ /vn/) {
- $select .= ', vr.title, vr.original';
- push @join, 'JOIN vn v ON v.id = wl.vid',
- 'JOIN vn_rev vr ON vr.id = v.latest';
+ $select .= ', v.title, v.original';
+ push @join, 'JOIN vn v ON v.id = wl.vid';
}
my $order = sprintf {
- title => 'vr.title %s',
+ title => 'v.title %s',
added => 'wl.added %s',
- wstat => 'wl.wstat %2$s, vr.title ASC',
+ wstat => 'wl.wstat %2$s, v.title ASC',
}->{ $o{sort}||'added' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC';
my($r, $np) = $self->dbPage(\%o, q|
diff --git a/lib/VNDB/DB/VN.pm b/lib/VNDB/DB/VN.pm
index 5d404b28..691b0eb3 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 dbVNHasStaff dbVNImportSeiyuu|;
+our @EXPORT = qw|dbVNGet dbVNGetRev dbVNRevisionInsert dbVNImageId dbScreenshotAdd dbScreenshotGet dbScreenshotRandom dbVNHasChar dbVNHasStaff dbVNImportSeiyuu|;
-# Options: id, rev, char, search, length, lang, olang, plat, tag_inc, tag_exc, tagspoil,
+# Options: id, 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 credits relations screenshots relgraph rating ranking changes wishlist vnlist
+# What: extended anime credits relations screenshots relgraph rating ranking 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 {
@@ -33,14 +33,12 @@ sub dbVNGet {
my @where = (
$o{id} ? (
'v.id IN(!l)' => [ ref $o{id} ? $o{id} : [$o{id}] ] ) : (),
- $o{rev} ? (
- 'c.rev = ?' => $o{rev} ) : (),
$o{char} ? (
- 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (),
+ 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (),
defined $o{char} && !$o{char} ? (
- '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (),
+ '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (),
defined $o{length} ? (
- 'vr.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (),
+ 'v.length IN(!l)' => [ ref $o{length} ? $o{length} : [$o{length}] ]) : (),
$o{lang} ? (
'v.c_languages && ARRAY[!l]::language[]' => [ ref $o{lang} ? $o{lang} : [$o{lang}] ]) : (),
$o{olang} ? (
@@ -48,9 +46,9 @@ sub dbVNGet {
$o{plat} ? (
'v.c_platforms && ARRAY[!l]::platform[]' => [ ref $o{plat} ? $o{plat} : [$o{plat}] ]) : (),
defined $o{hasani} ? (
- '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.vid = vr.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
+ '!sEXISTS(SELECT 1 FROM vn_anime va WHERE va.id = v.id)' => [ $o{hasani} ? '' : 'NOT ' ]) : (),
defined $o{hasshot} ? (
- '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.vid = vr.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (),
+ '!sEXISTS(SELECT 1 FROM vn_screenshots vs WHERE vs.id = v.id)' => [ $o{hasshot} ? '' : 'NOT ' ]) : (),
$o{tag_inc} ? (
'v.id IN(SELECT vid FROM tags_vn_inherit WHERE tag IN(!l) AND spoiler <= ? GROUP BY vid HAVING COUNT(tag) = ?)',
[ ref $o{tag_inc} ? $o{tag_inc} : [$o{tag_inc}], $o{tagspoil}, ref $o{tag_inc} ? $#{$o{tag_inc}}+1 : 1 ]) : (),
@@ -66,24 +64,14 @@ sub dbVNGet {
'v.id !s IN(SELECT vid FROM votes WHERE uid = ?)' => [ $o{ul_voted} ? '' : 'NOT', $uid ] ) : (),
$uid && defined $o{ul_onlist} ? (
'v.id !s IN(SELECT vid FROM vnlists WHERE uid = ?)' => [ $o{ul_onlist} ? '' : 'NOT', $uid ] ) : (),
- # don't fetch hidden items unless we ask for an ID
- !$o{id} && !$o{rev} && !$o{inc_hidden} ? (
+ !$o{id} && !$o{inc_hidden} ? (
'v.hidden = FALSE' => 0 ) : (),
- # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
+ # optimize fetching random entries (only when there are no other filters present, otherwise this won't work well)
$o{sort} eq 'rand' && $o{results} <= 10 && !grep(!/^(?:results|page|what|sort|tagspoil)$/, keys %o) ? (
- sprintf 'v.id IN(SELECT floor(random() * last_value)::integer
- FROM generate_series(1,20), (SELECT last_value FROM vn_id_seq) s1
- LIMIT 20)' ) : (),
+ 'v.id IN(SELECT floor(random() * last_value)::integer FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM vn) s1 LIMIT 20)' ) : (),
);
my @join = (
- $o{rev} ?
- 'JOIN vn v ON v.id = vr.vid' :
- 'JOIN vn v ON vr.id = v.latest',
- $o{rev} || $o{what} =~ /changes/ ?
- 'JOIN changes c ON c.id = vr.id' : (),
- $o{what} =~ /changes/ ?
- 'JOIN users u ON u.id = c.requester' : (),
$o{what} =~ /relgraph/ ?
'JOIN relgraphs vg ON vg.id = v.rgraph' : (),
$uid && $o{what} =~ /wishlist/ ?
@@ -92,8 +80,7 @@ sub dbVNGet {
SELECT irv.vid, COUNT(*) AS userlist_all,
SUM(CASE WHEN irl.status = 2 THEN 1 ELSE 0 END) AS userlist_obtained
FROM rlists irl
- JOIN releases ir ON irl.rid = ir.id
- JOIN releases_vn irv ON irv.rid = ir.latest
+ JOIN releases_vn irv ON irv.id = irl.rid
WHERE irl.uid = $uid
GROUP BY irv.vid
) AS vnlist ON vnlist.vid = v.id") : (),
@@ -101,11 +88,9 @@ sub dbVNGet {
my $tag_ids = $o{tag_inc} && join ',', ref $o{tag_inc} ? @{$o{tag_inc}} : $o{tag_inc};
my @select = ( # see https://rt.cpan.org/Ticket/Display.html?id=54224 for the cast on c_languages and c_platforms
- qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] vr.title vr.original v.rgraph|, 'vr.id AS cid',
+ qw|v.id v.locked v.hidden v.c_released v.c_languages::text[] v.c_platforms::text[] v.title v.original v.rgraph|,
$o{what} =~ /extended/ ? (
- qw|vr.alias vr.image vr.img_nsfw vr.length vr.desc vr.l_wp vr.l_encubed vr.l_renai| ) : (),
- $o{what} =~ /changes/ ? (
- qw|c.requester c.comments v.latest u.username c.rev c.ihid c.ilock|, q|extract('epoch' from c.added) as added|) : (),
+ qw|v.alias v.image v.img_nsfw v.length v.desc v.l_wp v.l_encubed v.l_renai| ) : (),
$o{what} =~ /relgraph/ ? 'vg.svg' : (),
$o{what} =~ /rating/ ? (qw|v.c_popularity v.c_rating v.c_votecount|) : (),
$o{what} =~ /ranking/ ? (
@@ -120,100 +105,128 @@ sub dbVNGet {
);
my $order = sprintf {
- id => 'id %s',
- rel => 'c_released %s, title ASC',
- pop => 'c_popularity %s NULLS LAST',
- rating => 'c_rating %s NULLS LAST',
- title => 'title %s',
+ id => 'v.id %s',
+ rel => 'v.c_released %s, v.title ASC',
+ pop => 'v.c_popularity %s NULLS LAST',
+ rating => 'v.c_rating %s NULLS LAST',
+ title => 'v.title %s',
tagscore => 'tagscore %s',
rand => 'RANDOM()',
}->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC';
my($r, $np) = $self->dbPage(\%o, q|
SELECT !s
- FROM vn_rev vr
+ FROM vn v
!s
!W
ORDER BY !s|,
join(', ', @select), join(' ', @join), \@where, $order,
);
- if($o{what} =~ /relgraph/) {
- $_->{svg} = decode_utf8($_->{svg}) for @$r;
- }
+ return _enrich($self, $r, $np, 0, $o{what});
+}
+
+
+sub dbVNGetRev {
+ my $self = shift;
+ my %o = (what => '', @_);
+
+ $o{rev} ||= $self->dbRow('SELECT MAX(rev) AS rev FROM changes WHERE type = \'v\' AND itemid = ?', $o{id})->{rev};
- if(@$r && $o{what} =~ /anime|relations|screenshots|credits/) {
+ # XXX: Too much duplication with code in dbVNGet() here. Can we combine some code here?
+ my $uid = $self->authInfo->{id};
+
+ my $select = 'c.itemid AS id, vo.c_released, vo.c_languages::text[], vo.c_platforms::text[], v.title, v.original, vo.rgraph';
+ $select .= ', extract(\'epoch\' from c.added) as added, c.requester, c.comments, u.username, c.rev, c.ihid, c.ilock';
+ $select .= ', c.id AS cid, NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev = c.rev+1) AS lastrev';
+ $select .= ', v.alias, v.image, v.img_nsfw, v.length, v.desc, v.l_wp, v.l_encubed, v.l_renai, vo.hidden, vo.locked' if $o{what} =~ /extended/;
+ $select .= ', vo.c_popularity, vo.c_rating, vo.c_votecount' if $o{what} =~ /rating/;
+ $select .= ', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_popularity > COALESCE(vo.c_popularity, 0.0)) AS p_ranking'
+ .', (SELECT COUNT(*)+1 FROM vn iv WHERE iv.hidden = false AND iv.c_rating > COALESCE(vo.c_rating, 0.0)) AS r_ranking' if $o{what} =~ /ranking/;
+
+ my $r = $self->dbAll(q|
+ SELECT !s
+ FROM changes c
+ JOIN vn vo ON vo.id = c.itemid
+ JOIN vn_hist v ON v.chid = c.id
+ JOIN users u ON u.id = c.requester
+ WHERE c.type = 'v' AND c.itemid = ? AND c.rev = ?|,
+ $select, $o{id}, $o{rev}
+ );
+
+ return _enrich($self, $r, 0, 1, $o{what});
+}
+
+
+sub _enrich {
+ my($self, $r, $np, $rev, $what) = @_;
+
+ if(@$r && $what =~ /anime|relations|screenshots|credits/) {
+ my($col, $hist, $colname) = $rev ? ('cid', '_hist', 'chid') : ('id', '', 'id');
my %r = map {
$r->[$_]{anime} = [];
$r->[$_]{credits} = [];
$r->[$_]{seiyuu} = [];
$r->[$_]{relations} = [];
$r->[$_]{screenshots} = [];
- ($r->[$_]{cid}, $_)
+ ($r->[$_]{$col}, $_)
} 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|,
+ if($what =~ /credits/) {
+ push(@{$r->[$r{ delete $_->{xid} }]{credits}}, $_) for (@{$self->dbAll("
+ SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, vs.role, vs.note
+ FROM vn_staff$hist vs
+ JOIN staff_alias sa ON vs.aid = sa.aid
+ JOIN staff s ON s.id = sa.id
+ WHERE s.hidden = FALSE AND vs.$colname 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,
- (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = vr.vid AND cv.cid = cr.id) AS spoil
- 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
+
+ # The seiyuu query needs the VN id to get the VN<->Char spoiler level.
+ # Obtaining this ID is different when using the hist table.
+ my($vid, $join) = $rev ? ('h.itemid', 'JOIN changes h ON h.id = vs.chid') : ('vs.id', '');
+ push(@{$r->[$r{ delete $_->{xid} }]{seiyuu}}, $_) for (@{$self->dbAll("
+ SELECT vs.$colname AS xid, s.id, vs.aid, sa.name, sa.original, s.gender, s.lang, c.id AS cid, c.name AS cname, vs.note,
+ (SELECT MAX(spoil) FROM chars_vns cv WHERE cv.vid = $vid AND cv.id = c.id) AS spoil
+ FROM vn_seiyuu$hist vs
+ JOIN staff_alias sa ON vs.aid = sa.aid
+ JOIN staff s ON s.id = sa.id
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|,
+ $join
+ WHERE s.hidden = FALSE AND vs.$colname IN(!l)
+ ORDER BY c.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
- FROM vn_anime va
+ if($what =~ /anime/) {
+ push(@{$r->[$r{ delete $_->{xid} }]{anime}}, $_) for (@{$self->dbAll("
+ SELECT va.$colname AS xid, 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
+ FROM vn_anime$hist va
JOIN anime a ON va.aid = a.id
- WHERE va.vid IN(!l)|,
+ WHERE va.$colname IN(!l)",
[ keys %r ]
)});
}
- if($o{what} =~ /relations/) {
- push(@{$r->[$r{$_->{vid1}}]{relations}}, {
- relation => $_->{relation},
- official => $_->{official},
- id => $_->{vid2},
- title => $_->{title},
- original => $_->{original},
- }) for(@{$self->dbAll(q|
- SELECT rel.vid1, rel.vid2, rel.relation, rel.official, vr.title, vr.original
- FROM vn_relations rel
- JOIN vn v ON rel.vid2 = v.id
- JOIN vn_rev vr ON v.latest = vr.id
- WHERE rel.vid1 IN(!l)|,
+ if($what =~ /relations/) {
+ push(@{$r->[$r{ delete $_->{xid} }]{relations}}, $_) for(@{$self->dbAll("
+ SELECT rel.$colname AS xid, rel.vid AS id, rel.relation, rel.official, v.title, v.original
+ FROM vn_relations$hist rel
+ JOIN vn v ON rel.vid = v.id
+ WHERE rel.$colname IN(!l)",
[ keys %r ]
)});
}
- if($o{what} =~ /screenshots/) {
- push(@{$r->[$r{$_->{vid}}]{screenshots}}, $_) && delete $_->{vid} for (@{$self->dbAll(q|
- SELECT vs.vid, s.id, vs.nsfw, vs.rid, s.width, s.height
- FROM vn_screenshots vs
+ if($what =~ /screenshots/) {
+ push(@{$r->[$r{ delete $_->{xid} }]{screenshots}}, $_) for (@{$self->dbAll("
+ SELECT vs.$colname AS xid, s.id, vs.nsfw, vs.rid, s.width, s.height
+ FROM vn_screenshots$hist vs
JOIN screenshots s ON vs.scr = s.id
- WHERE vs.vid IN(!l)
- ORDER BY vs.scr|,
+ WHERE vs.$colname IN(!l)
+ ORDER BY vs.scr",
[ keys %r ]
)});
}
@@ -296,41 +309,36 @@ sub dbScreenshotGet {
sub dbScreenshotRandom {
my($self, @vids) = @_;
return $self->dbAll(q|
- SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title
+ SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title
FROM screenshots s
JOIN vn_screenshots vs ON vs.scr = s.id
- JOIN vn_rev vr ON vr.id = vs.vid
- JOIN vn v ON v.id = vr.vid AND v.latest = vs.vid
+ JOIN vn v ON v.id = vs.id
WHERE NOT v.hidden AND NOT vs.nsfw
AND s.id IN(
SELECT floor(random() * last_value)::integer
- FROM generate_series(1,20), (SELECT last_value FROM screenshots_id_seq) s1
+ FROM generate_series(1,20), (SELECT MAX(id) AS last_value FROM screenshots) s1
LIMIT 20
)
LIMIT 4|
) if !@vids;
# this query is faster than it looks
return $self->dbAll(join(' UNION ALL ', map
- q|SELECT s.id AS scr, s.width, s.height, vr.vid, vr.title, RANDOM() AS position
- FROM vn v
- JOIN vn_rev vr ON vr.id = v.latest
- JOIN vn_screenshots vs ON vs.vid = v.latest
+ q|SELECT s.id AS scr, s.width, s.height, v.id AS vid, v.title, RANDOM() AS position
+ FROM (
+ SELECT vs2.id, vs2.scr FROM vn_screenshots vs2
+ WHERE vs2.id = ? AND NOT vs2.nsfw
+ ORDER BY RANDOM() LIMIT 1
+ ) vs
+ JOIN vn v ON v.id = vs.id
JOIN screenshots s ON s.id = vs.scr
- WHERE v.id = ? AND s.id = (
- SELECT vs2.scr
- FROM vn_screenshots vs2
- JOIN vn v2 ON v2.latest = vs2.vid
- WHERE v2.id = v.id AND NOT vs2.nsfw
- ORDER BY RANDOM()
- LIMIT 1
- )|, @vids).' ORDER BY position', @vids);
+ |, @vids).' ORDER BY position', @vids);
}
sub dbVNHasChar {
my($self, $vid) = @_;
return $self->dbRow(
- 'SELECT 1 AS exists FROM chars c JOIN chars_vns cv ON c.latest = cv.cid WHERE cv.vid = ? AND NOT c.hidden', $vid
+ 'SELECT 1 AS exists FROM chars c JOIN chars_vns cv ON c.id = cv.id WHERE cv.vid = ? AND NOT c.hidden', $vid
)->{exists};
}
@@ -338,7 +346,7 @@ 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
+ 'SELECT 1 AS exists FROM vn_staff vs FULL OUTER JOIN vn_seiyuu vsy ON vs.id = vsy.id WHERE vs.id = ?', $vid
)->{exists};
}
@@ -347,16 +355,12 @@ sub dbVNHasStaff {
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
+ SELECT DISTINCT ON(c.id) c.id AS cid, c.name AS c_name, sa.id AS sid, sa.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);
+ JOIN staff_alias sa ON sa.aid = vs.aid
+ WHERE vs.cid IN(!l) AND vs.id <> ?|, $cids, $vid);
}
1;
-
diff --git a/lib/VNDB/Handler/Chars.pm b/lib/VNDB/Handler/Chars.pm
index 7171d506..524b5a04 100644
--- a/lib/VNDB/Handler/Chars.pm
+++ b/lib/VNDB/Handler/Chars.pm
@@ -20,9 +20,10 @@ TUWF::register(
sub page {
my($self, $id, $rev) = @_;
- my $r = $self->dbCharGet(
+ my $method = $rev ? 'dbCharGetRev' : 'dbCharGet';
+ my $r = $self->$method(
id => $id,
- what => 'extended traits vns seiyuu'.($rev ? ' changes' : ''),
+ what => 'extended traits vns seiyuu',
$rev ? ( rev => $rev ) : ()
)->[0];
return $self->resNotFound if !$r->{id};
@@ -32,7 +33,7 @@ sub page {
return if $self->htmlHiddenMessage('c', $r);
if($rev) {
- my $prev = $rev && $rev > 1 && $self->dbCharGet(id => $id, rev => $rev-1, what => 'changes extended traits vns')->[0];
+ my $prev = $rev && $rev > 1 && $self->dbCharGetRev(id => $id, rev => $rev-1, what => 'extended traits vns')->[0];
$self->htmlRevision('c', $prev, $r,
[ name => diff => 1 ],
[ original => diff => 1 ],
@@ -265,9 +266,9 @@ sub edit {
$copy = $rev && $rev eq 'copy' || $copy && $copy eq 'copy';
$rev = undef if defined $rev && $rev !~ /^\d+$/;
- my $r = $id && $self->dbCharGet(id => $id, what => 'changes extended vns traits', $rev ? (rev => $rev) : ())->[0];
+ my $r = $id && $self->dbCharGetRev(id => $id, what => 'extended vns traits', $rev ? (rev => $rev) : ())->[0];
return $self->resNotFound if $id && !$r->{id};
- $rev = undef if !$r || $r->{cid} == $r->{latest};
+ $rev = undef if !$r || $r->{lastrev};
return $self->htmlDenied if !$self->authCan('edit')
|| $id && (($r->{locked} || $r->{hidden}) && !$self->authCan('dbmod'));
diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm
index 8176d854..c32766b1 100644
--- a/lib/VNDB/Handler/Misc.pm
+++ b/lib/VNDB/Handler/Misc.pm
@@ -75,7 +75,7 @@ sub homepage {
for (@$changes) {
li;
lit mt '_home_recentchanges_item', $_->{type},
- sprintf('<a href="%s" title="%s">%s</a>', "/$_->{type}$_->{iid}.$_->{rev}",
+ sprintf('<a href="%s" title="%s">%s</a>', "/$_->{type}$_->{itemid}.$_->{rev}",
xml_escape($_->{ioriginal}||$_->{ititle}), xml_escape shorten $_->{ititle}, 33),
$_;
end;
@@ -214,7 +214,7 @@ sub history {
# get the edit history
my($list, $np) = $self->dbRevisionGet(
what => 'item user',
- $type && $type ne 'u' ? ( type => $type, iid => $id ) : (),
+ $type && $type ne 'u' ? ( type => $type, itemid => $id ) : (),
$type eq 'u' ? ( uid => $id ) : (),
$f->{t} ? ( type => $f->{t} eq 'a' ? [qw|v r p|] : $f->{t} ) : (),
page => $f->{p},
diff --git a/lib/VNDB/Handler/Producers.pm b/lib/VNDB/Handler/Producers.pm
index 6369d4d1..628034c2 100644
--- a/lib/VNDB/Handler/Producers.pm
+++ b/lib/VNDB/Handler/Producers.pm
@@ -44,9 +44,10 @@ sub rg {
sub page {
my($self, $pid, $rev) = @_;
- my $p = $self->dbProducerGet(
+ my $method = $rev ? 'dbProducerGetRev' : 'dbProducerGet';
+ my $p = $self->$method(
id => $pid,
- what => 'extended relations'.($rev ? ' changes' : ''),
+ what => 'extended relations',
$rev ? ( rev => $rev ) : ()
)->[0];
return $self->resNotFound if !$p->{id};
@@ -56,7 +57,7 @@ sub page {
return if $self->htmlHiddenMessage('p', $p);
if($rev) {
- my $prev = $rev && $rev > 1 && $self->dbProducerGet(id => $pid, rev => $rev-1, what => 'changes extended relations')->[0];
+ my $prev = $rev && $rev > 1 && $self->dbProducerGetRev(id => $pid, rev => $rev-1, what => 'extended relations')->[0];
$self->htmlRevision('p', $prev, $p,
[ type => serialize => sub { mt "_ptype_$_[0]" } ],
[ name => diff => 1 ],
@@ -200,9 +201,9 @@ sub _releases {
sub edit {
my($self, $pid, $rev) = @_;
- my $p = $pid && $self->dbProducerGet(id => $pid, what => 'changes extended relations', $rev ? (rev => $rev) : ())->[0];
+ my $p = $pid && $self->dbProducerGetRev(id => $pid, what => 'extended relations', rev => $rev)->[0];
return $self->resNotFound if $pid && !$p->{id};
- $rev = undef if !$p || $p->{cid} == $p->{latest};
+ $rev = undef if !$p || $p->{lastrev};
return $self->htmlDenied if !$self->authCan('edit')
|| $pid && (($p->{locked} || $p->{hidden}) && !$self->authCan('dbmod'));
@@ -329,7 +330,7 @@ sub _updreverse {
# edit all related producers
for my $i (keys %upd) {
- my $r = $self->dbProducerGet(id => $i, what => 'relations')->[0];
+ my $r = $self->dbProducerGetRev(id => $i, what => 'relations')->[0];
my @newrel = map $_->{id} != $pid ? [ $_->{relation}, $_->{id} ] : (), @{$r->{relations}};
push @newrel, [ $upd{$i}, $pid ] if $upd{$i};
$self->dbItemEdit(p => $r->{cid},
diff --git a/lib/VNDB/Handler/Releases.pm b/lib/VNDB/Handler/Releases.pm
index b06a62f3..1c97eced 100644
--- a/lib/VNDB/Handler/Releases.pm
+++ b/lib/VNDB/Handler/Releases.pm
@@ -20,9 +20,10 @@ TUWF::register(
sub page {
my($self, $rid, $rev) = @_;
- my $r = $self->dbReleaseGet(
+ my $method = $rev ? 'dbReleaseGetRev' : 'dbReleaseGet';
+ my $r = $self->$method(
id => $rid,
- what => 'vn extended producers platforms media'.($rev ? ' changes' : ''),
+ what => 'vn extended producers platforms media',
$rev ? (rev => $rev) : (),
)->[0];
return $self->resNotFound if !$r->{id};
@@ -32,7 +33,7 @@ sub page {
return if $self->htmlHiddenMessage('r', $r);
if($rev) {
- my $prev = $rev && $rev > 1 && $self->dbReleaseGet(
+ my $prev = $rev && $rev > 1 && $self->dbReleaseGetRev(
id => $rid, rev => $rev-1,
what => 'vn extended producers platforms media changes'
)->[0];
@@ -271,9 +272,9 @@ sub edit {
$rid = 0;
}
- my $r = $rid && $self->dbReleaseGet(id => $rid, what => 'vn extended producers platforms media changes', $rev ? (rev => $rev) : ())->[0];
+ my $r = $rid && $self->dbReleaseGetRev(id => $rid, what => 'vn extended producers platforms media', $rev ? (rev => $rev) : ())->[0];
return $self->resNotFound if $rid && !$r->{id};
- $rev = undef if !$r || $r->{cid} == $r->{latest};
+ $rev = undef if !$r || $r->{lastrev};
my $v = $vid && $self->dbVNGet(id => $vid)->[0];
return $self->resNotFound if $vid && !$v->{id};
diff --git a/lib/VNDB/Handler/Staff.pm b/lib/VNDB/Handler/Staff.pm
index 88e6d7c8..f2e855ba 100644
--- a/lib/VNDB/Handler/Staff.pm
+++ b/lib/VNDB/Handler/Staff.pm
@@ -19,9 +19,10 @@ TUWF::register(
sub page {
my($self, $id, $rev) = @_;
- my $s = $self->dbStaffGet(
+ my $method = $rev ? 'dbStaffGetRev' : 'dbStaffGet';
+ my $s = $self->$method(
id => $id,
- what => 'extended aliases roles'.($rev ? ' changes' : ''),
+ what => 'extended aliases roles',
$rev ? ( rev => $rev ) : ()
)->[0];
return $self->resNotFound if !$s->{id};
@@ -31,7 +32,7 @@ sub page {
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];
+ my $prev = $rev && $rev > 1 && $self->dbStaffGetRev(id => $id, rev => $rev-1, what => 'extended aliases')->[0];
$self->htmlRevision('s', $prev, $s,
[ name => diff => 1 ],
[ original => diff => 1 ],
@@ -179,9 +180,9 @@ sub _cast {
sub edit {
my($self, $sid, $rev) = @_;
- my $s = $sid && $self->dbStaffGet(id => $sid, what => 'changes extended aliases', $rev ? (rev => $rev) : ())->[0];
+ my $s = $sid && $self->dbStaffGetRev(id => $sid, what => 'extended aliases', $rev ? (rev => $rev) : ())->[0];
return $self->resNotFound if $sid && !$s->{id};
- $rev = undef if !$s || $s->{cid} == $s->{latest};
+ $rev = undef if !$s || $s->{lastrev};
return $self->htmlDenied if !$self->authCan('staffedit')
|| $sid && (($s->{locked} || $s->{hidden}) && !$self->authCan('dbmod'));
@@ -384,4 +385,3 @@ sub staffxml {
}
1;
-__END__
diff --git a/lib/VNDB/Handler/VNEdit.pm b/lib/VNDB/Handler/VNEdit.pm
index 8869d1a9..c383be78 100644
--- a/lib/VNDB/Handler/VNEdit.pm
+++ b/lib/VNDB/Handler/VNEdit.pm
@@ -79,7 +79,7 @@ sub edit {
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};
+ $rev = undef if !$vid || $v->{lastrev};
return $self->htmlDenied if !$self->authCan('edit')
|| $vid && (($v->{locked} || $v->{hidden}) && !$self->authCan('dbmod'));
diff --git a/lib/VNDB/Handler/VNPage.pm b/lib/VNDB/Handler/VNPage.pm
index 9c85f922..cf8a50af 100644
--- a/lib/VNDB/Handler/VNPage.pm
+++ b/lib/VNDB/Handler/VNPage.pm
@@ -187,8 +187,7 @@ my @rel_cols = (
sub releases {
my($self, $vid) = @_;
- my $v = $self->dbVNGet(
- id => $vid)->[0];
+ my $v = $self->dbVNGet(id => $vid)->[0];
return $self->resNotFound if !$v->{id};
my $title = mt('_vnpage_rel_title', $v->{title});
@@ -334,9 +333,10 @@ sub page {
my $staff = $rev && $rev eq 'staff';
$rev = undef if $char || $staff;
- my $v = $self->dbVNGet(
+ my $method = $rev ? 'dbVNGetRev' : 'dbVNGet';
+ my $v = $self->$method(
id => $vid,
- what => 'extended anime relations screenshots rating ranking'.($staff || $rev ? ' credits' : '').($rev ? ' changes' : ''),
+ what => 'extended anime relations screenshots rating ranking'.($staff || $rev ? ' credits' : ''),
$rev ? (rev => $rev) : (),
)->[0];
return $self->resNotFound if !$v->{id};
@@ -508,8 +508,8 @@ sub _revision {
my($self, $v, $rev) = @_;
return if !$rev;
- my $prev = $rev && $rev > 1 && $self->dbVNGet(
- id => $v->{id}, rev => $rev-1, what => 'extended anime relations screenshots credits changes'
+ my $prev = $rev && $rev > 1 && $self->dbVNGetRev(
+ id => $v->{id}, rev => $rev-1, what => 'extended anime relations screenshots credits'
)->[0];
$self->htmlRevision('v', $prev, $v,
diff --git a/lib/VNDB/Util/BrowseHTML.pm b/lib/VNDB/Util/BrowseHTML.pm
index b54ad5d9..d57c0546 100644
--- a/lib/VNDB/Util/BrowseHTML.pm
+++ b/lib/VNDB/Util/BrowseHTML.pm
@@ -137,11 +137,11 @@ sub htmlBrowseHist {
],
row => sub {
my($s, $n, $i) = @_;
- my $revurl = "/$i->{type}$i->{iid}.$i->{rev}";
+ my $revurl = "/$i->{type}$i->{itemid}.$i->{rev}";
Tr;
td class => 'tc1_1';
- a href => $revurl, "$i->{type}$i->{iid}";
+ a href => $revurl, "$i->{type}$i->{itemid}";
end;
td class => 'tc1_2';
a href => $revurl, ".$i->{rev}";
diff --git a/lib/VNDB/Util/CommonHTML.pm b/lib/VNDB/Util/CommonHTML.pm
index 7b3fa673..bcc2ea97 100644
--- a/lib/VNDB/Util/CommonHTML.pm
+++ b/lib/VNDB/Util/CommonHTML.pm
@@ -133,12 +133,12 @@ sub htmlHiddenMessage {
my($self, $type, $obj) = @_;
return 0 if !$obj->{hidden};
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};
+ # fetch edit summary (not present in $obj, requires the db*GetRev() methods)
+ my $editsum = $type eq 'v' ? $self->dbVNGetRev(id => $obj->{id})->[0]{comments}
+ : $type eq 'r' ? $self->dbReleaseGetRev(id => $obj->{id})->[0]{comments}
+ : $type eq 'c' ? $self->dbCharGetRev(id => $obj->{id})->[0]{comments}
+ : $type eq 's' ? $self->dbStaffGetRev(id => $obj->{id})->[0]{comments}
+ : $self->dbProducerGetRev(id => $obj->{id})->[0]{comments};
div class => 'mainbox';
h1 $obj->{title}||$obj->{name};
div class => 'warning';
@@ -183,7 +183,7 @@ sub htmlRevision {
a class => 'prev', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}-1), '<- '.mt '_revision_previous'
if $new->{rev} > 1;
a class => 'next', href => sprintf('/%s%d.%d', $type, $new->{id}, $new->{rev}+1), mt('_revision_next').' ->'
- if $new->{cid} != $new->{latest};
+ if !$new->{lastrev};
p class => 'center';
a href => "/$type$new->{id}", "$type$new->{id}";
end;
@@ -310,7 +310,7 @@ sub htmlEditMessage {
}
end;
end;
- if($obj && $obj->{latest} != $obj->{cid}) {
+ if($obj && !$obj->{lastrev}) {
div class => 'warning';
h2 mt '_editmsg_revert_title';
p mt '_editmsg_revert_msg', $num;
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index 05770f83..b3ac137f 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -102,6 +102,7 @@ CREATE INDEX tags_vn_date ON tags_vn (date);
CREATE INDEX tags_vn_vid ON tags_vn (vid);
CREATE INDEX threads_posts_ts ON threads_posts USING gin(to_tsvector('english', strip_bb_tags(msg)));
CREATE INDEX vn_staff_aid ON vn_staff (aid);
+CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid);
CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0));
CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0));