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/Chars.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/Chars.pm')
-rw-r--r-- | lib/VNDB/DB/Chars.pm | 166 |
1 files changed, 96 insertions, 70 deletions
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; } |