summaryrefslogtreecommitdiff
path: root/lib/VNDB
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-21 16:55:52 +0200
committerYorhel <git@yorhel.nl>2015-10-21 16:59:18 +0200
commitd5456b8ed5952b9fa061f0de536fab572c95016a (patch)
treebf70e1739ea47b0cd364fdf1fa60d4867795ff5a /lib/VNDB
parent08a0d819efd244c8b3dacfd810b7322ad8df73fb (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.
Diffstat (limited to 'lib/VNDB')
-rw-r--r--lib/VNDB/DB/Misc.pm79
-rw-r--r--lib/VNDB/Handler/Misc.pm3
-rw-r--r--lib/VNDB/Handler/Users.pm2
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;