diff options
Diffstat (limited to 'lib')
-rw-r--r-- | lib/VNDB/DB/Misc.pm | 79 | ||||
-rw-r--r-- | lib/VNDB/Handler/Misc.pm | 3 | ||||
-rw-r--r-- | lib/VNDB/Handler/Users.pm | 2 |
3 files changed, 35 insertions, 49 deletions
diff --git a/lib/VNDB/DB/Misc.pm b/lib/VNDB/DB/Misc.pm index 7c3a6e36..d6389376 100644 --- a/lib/VNDB/DB/Misc.pm +++ b/lib/VNDB/DB/Misc.pm @@ -46,8 +46,7 @@ sub dbItemEdit { } -# Options: type, itemid, uid, auto, hidden, edit, page, results, what, releases -# what: item user +# Options: type, itemid, uid, auto, hidden, edit, page, results, releases sub dbRevisionGet { my($self, %o) = @_; $o{results} ||= 10; @@ -55,69 +54,57 @@ sub dbRevisionGet { $o{auto} ||= 0; # 0:show, -1:only, 1:hide $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{itemid}; - my %tables = qw|v vn r releases p producers c chars s staff|; - # what types should we join? - my @types = ( - !$o{type} ? qw(v r p c s) : - ref($o{type}) ? @{$o{type}} : - $o{type} ne 'v' ? $o{type} : - $o{releases} ? ('v', 'r') : 'v' - ); - my %where = ( $o{releases} ? ( # 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}], + q{((c.type = 'v' AND c.itemid = ?) OR (c.type = 'r' AND c.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}] ] ) : (), + 'c.type IN(!l)' => [ ref($o{type})?$o{type}:[$o{type}] ] ) : (), $o{itemid} ? ( - 'h.itemid = ?' => [ $o{itemid} ] ) : (), + 'c.itemid = ?' => [ $o{itemid} ] ) : (), ), $o{uid} ? ( - 'h.requester = ?' => $o{uid} ) : (), + 'c.requester = ?' => $o{uid} ) : (), $o{auto} ? ( - 'h.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (), + 'c.requester !s 1' => $o{auto} < 0 ? '=' : '<>' ) : (), $o{hidden} ? ( - ($o{hidden} == 1 ? 'NOT' : '').' COALESCE('.join(',', map "${_}.hidden", @types).')' => 1 ) : (), + '!s EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.ihid AND'. + ' c2.rev = (SELECT MAX(c3.rev) FROM changes c3 WHERE c3.type = c.type AND c3.itemid = c.itemid))' => $o{hidden} == 1 ? 'NOT' : '') : (), $o{edit} ? ( - 'h.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (), - ); - - my @join = ( - $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 h.itemid = %2$s.id|, $tables{$_}, $_), @types - ) : (), - $o{what} =~ /user/ ? 'JOIN users u ON h.requester = u.id' : (), - ); - 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 vh.title r rh.title p ph.name c ch.name s sah.name); - my @select = ( - 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 $tcolumns{$_}, @types).') AS ititle', - 'COALESCE('.join(', ', map /s/ ? 'sah.original' : "${_}h.original", @types).') AS ioriginal', - ) : (), + 'c.rev !s 1' => $o{edit} < 0 ? '=' : '>' ) : (), ); my($r, $np) = $self->dbPage(\%o, q| - SELECT !s - FROM changes h - !s + SELECT c.id, c.type, c.itemid, c.requester, c.comments, c.rev, extract('epoch' from c.added) as added, u.username + FROM changes c + JOIN users u ON c.requester = u.id !W - ORDER BY h.id DESC|, - join(', ', @select), join(' ', @join), \%where + ORDER BY c.id DESC|, \%where ); + + # I couldn't find a way to fetch the titles the main query above without slowing it down considerably, so let's just do it this way. + if(@$r) { + my %r = map +($_->{id}, $_), @$r; + my $w = join ' OR ', ('(type = ? AND id = ?)') x @$r; + my @w = map +($_->{type}, $_->{id}), @$r; + + $r{ $_->{id} }{ititle} = $_->{title}, $r{ $_->{id} }{ioriginal} = $_->{original} for(@{$self->dbAll(" + SELECT id, title, original FROM ( + SELECT 'v'::dbentry_type, chid, title, original FROM vn_hist + UNION ALL SELECT 'r'::dbentry_type, chid, title, original FROM releases_hist + UNION ALL SELECT 'p'::dbentry_type, chid, name, original FROM producers_hist + UNION ALL SELECT 'c'::dbentry_type, chid, name, original FROM chars_hist + UNION ALL SELECT 's'::dbentry_type, sh.chid, name, original FROM staff_hist sh JOIN staff_alias_hist sah ON sah.chid = sh.chid AND sah.aid = sh.aid + ) x(type, id, title, original) + WHERE $w + ", @w + )}); + } + return wantarray ? ($r, $np) : $r; } diff --git a/lib/VNDB/Handler/Misc.pm b/lib/VNDB/Handler/Misc.pm index c32766b1..cb9a508a 100644 --- a/lib/VNDB/Handler/Misc.pm +++ b/lib/VNDB/Handler/Misc.pm @@ -70,7 +70,7 @@ sub homepage { a href => '/hist', mt '_home_recentchanges'; txt ' '; a href => '/feeds/changes.atom'; cssicon 'feed', mt '_atom_feed'; end; end; - my $changes = $self->dbRevisionGet(what => 'item user', results => 10, auto => 1); + my $changes = $self->dbRevisionGet(results => 10, auto => 1); ul; for (@$changes) { li; @@ -213,7 +213,6 @@ sub history { # get the edit history my($list, $np) = $self->dbRevisionGet( - what => 'item user', $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} ) : (), diff --git a/lib/VNDB/Handler/Users.pm b/lib/VNDB/Handler/Users.pm index 491c573b..c0836f97 100644 --- a/lib/VNDB/Handler/Users.pm +++ b/lib/VNDB/Handler/Users.pm @@ -125,7 +125,7 @@ sub userpage { } if($u->{c_changes}) { - my $list = $self->dbRevisionGet(what => 'item user', uid => $uid, results => 5); + my $list = $self->dbRevisionGet(uid => $uid, results => 5); h1 class => 'boxtitle'; a href => "/u$uid/hist", mt '_userpage_changes'; end; |