diff options
-rw-r--r-- | lib/VNDB/DB/Affiliates.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/DB/Chars.pm | 166 | ||||
-rw-r--r-- | lib/VNDB/DB/Discussions.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 31 | ||||
-rw-r--r-- | lib/VNDB/DB/Producers.pm | 83 | ||||
-rw-r--r-- | lib/VNDB/DB/Releases.pm | 176 | ||||
-rw-r--r-- | lib/VNDB/DB/Staff.pm | 147 | ||||
-rw-r--r-- | lib/VNDB/DB/Tags.pm | 6 | ||||
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 48 | ||||
-rw-r--r-- | lib/VNDB/DB/VN.pm | 224 | ||||
-rw-r--r-- | lib/VNDB/Handler/Chars.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/Handler/Misc.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Handler/Producers.pm | 13 | ||||
-rw-r--r-- | lib/VNDB/Handler/Releases.pm | 11 | ||||
-rw-r--r-- | lib/VNDB/Handler/Staff.pm | 12 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNEdit.pm | 2 | ||||
-rw-r--r-- | lib/VNDB/Handler/VNPage.pm | 12 | ||||
-rw-r--r-- | lib/VNDB/Util/BrowseHTML.pm | 4 | ||||
-rw-r--r-- | lib/VNDB/Util/CommonHTML.pm | 16 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 1 |
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)); |