diff options
author | Yorhel <git@yorhel.nl> | 2015-10-17 17:05:50 +0200 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2015-10-17 17:20:41 +0200 |
commit | 718f4d0258049aa92f229c08d5ec7204dae3ffa6 (patch) | |
tree | fc45906cbc145e85e7954671da10d7dbbab9dfbc /lib/VNDB/DB/Staff.pm | |
parent | eed1eced579e022135f98f3f733ecab88e3b6b6e (diff) |
SQL: Fix all browsing queries to use the new schema
This basically makes VNDB browsable again, but editing entries is still
broken.
I split off the get-old-revision functionality from the db*Get() methods
into db*GetRev(). This split makes sense even with the old SQL schema:
db*Get() had to special-case some joins/filters when fetching an older
revision, and none of the other filters would work in that case. This
split does cause some code duplication in that all db*GetRev() methods
look very much alike, and that the columns they fetch is almost
identical to the db*Get() methods. Not sure yet how to avoid the
duplication elegantly.
I didn't do a whole lot of query optimization yet (most issues require
extra indices, I'll investigate later which indices will make a big
difference), but I did fix some low hanging fruit whenever I encountered
something.
I don't think I've worsened anything, performance-wise.
Diffstat (limited to 'lib/VNDB/DB/Staff.pm')
-rw-r--r-- | lib/VNDB/DB/Staff.pm | 147 |
1 files changed, 87 insertions, 60 deletions
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; |