summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Chars.pm
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2015-10-17 17:05:50 +0200
committerYorhel <git@yorhel.nl>2015-10-17 17:20:41 +0200
commit718f4d0258049aa92f229c08d5ec7204dae3ffa6 (patch)
treefc45906cbc145e85e7954671da10d7dbbab9dfbc /lib/VNDB/DB/Chars.pm
parenteed1eced579e022135f98f3f733ecab88e3b6b6e (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.pm166
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;
}