diff options
author | Yorhel <git@yorhel.nl> | 2015-10-21 16:55:52 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2015-10-21 16:59:18 +0200 |
commit | d5456b8ed5952b9fa061f0de536fab572c95016a (patch) | |
tree | bf70e1739ea47b0cd364fdf1fa60d4867795ff5a | |
parent | 08a0d819efd244c8b3dacfd810b7322ad8df73fb (diff) |
Improve SQL in dbRevisionGet()
Two main improvements:
- Filtering on (non)hidden items now doesn't join any of the item
tables, instead it looks up the latest revision from the changes table
itself, using the index on (type,itemid,rev). It's still not super
fast, but a pretty large improvement nonetheless.
- The item titles/names are obtained in a separate query. I tried to
modify the main query in various ways, but couldn't make it as fast as
I'd have liked.
I also removed the 'what' flag while I was at it, all uses of the method
request all information anyway.
-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; |