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/ULists.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/ULists.pm')
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 48 |
1 files changed, 22 insertions, 26 deletions
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index 6831e579..23bde7cf 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -59,22 +59,21 @@ sub dbVNListList { 'vl.uid = ?' => $o{uid}, defined($o{voted}) ? ('vo.vote !s NULL' => $o{voted} ? 'IS NOT' : 'IS') : (), defined($o{status})? ('vl.status = ?' => $o{status}) : (), - $o{char} ? ('LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{char} ) : (), + $o{char} ? ('LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{char} ) : (), defined $o{char} && !$o{char} ? ( - '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (), ); my $order = sprintf { - title => 'vr.title %s', - vote => 'vo.vote %s NULLS LAST, vr.title ASC', + title => 'v.title %s', + vote => 'vo.vote %s NULLS LAST, v.title ASC', }->{ $o{sort}||'title' }, $o{reverse} ? 'DESC' : 'ASC'; # execute query my($r, $np) = $self->dbPage(\%o, qq| - SELECT vr.vid, vr.title, vr.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote + SELECT vl.vid, v.title, v.original, vl.status, vl.notes, COALESCE(vo.vote, 0) AS vote FROM vnlists vl JOIN vn v ON v.id = vl.vid - JOIN vn_rev vr ON vr.id = v.latest LEFT JOIN votes vo ON vo.vid = vl.vid AND vo.uid = vl.uid !W ORDER BY !s|, @@ -89,27 +88,26 @@ sub dbVNListList { } @$r; my $rel = $self->dbAll(q| - SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.status + SELECT rv.vid, rl.rid, r.title, r.original, r.released, r.type, rl.status FROM rlists rl JOIN releases r ON rl.rid = r.id - JOIN releases_rev rr ON rr.id = r.latest - JOIN releases_vn rv ON rv.rid = r.latest + JOIN releases_vn rv ON rv.id = r.id WHERE rl.uid = ? AND rv.vid IN(!l) - ORDER BY rr.released ASC|, + ORDER BY r.released ASC|, $o{uid}, [ keys %vns ] ); if(@$rel) { - my %rel = map { $_->{latest} => [] } @$rel; - push(@{$rel{$_->{rid}}}, $_->{lang}) for (@{$self->dbAll(q| - SELECT rid, lang + my %rel = map { $_->{rid} => [] } @$rel; + push(@{$rel{$_->{id}}}, $_->{lang}) for (@{$self->dbAll(q| + SELECT id, lang FROM releases_lang - WHERE rid IN(!l)|, + WHERE id IN(!l)|, [ keys %rel ] )}); for(@$rel) { - $_->{languages} = $rel{$_->{latest}}; + $_->{languages} = $rel{$_->{rid}}; push @{$vns{$_->{vid}}}, $_; } } @@ -189,9 +187,9 @@ sub dbVoteGet { $o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (), $o{hide} ? ( 'NOT EXISTS(SELECT 1 FROM users_prefs WHERE uid = n.uid AND key = \'hide_list\')' => 1 ) : (), $o{hide_ign} ? ( '(NOT u.ign_votes OR u.id = ?)' => $self->authInfo->{id}||0 ) : (), - $o{vn_char} ? ( 'LOWER(SUBSTR(vr.title, 1, 1)) = ?' => $o{vn_char} ) : (), + $o{vn_char} ? ( 'LOWER(SUBSTR(v.title, 1, 1)) = ?' => $o{vn_char} ) : (), defined $o{vn_char} && !$o{vn_char} ? ( - '(ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' => 1 ) : (), + '(ASCII(v.title) < 97 OR ASCII(v.title) > 122) AND (ASCII(v.title) < 65 OR ASCII(v.title) > 90)' => 1 ) : (), $o{user_char} ? ( 'LOWER(SUBSTR(u.username, 1, 1)) = ?' => $o{user_char} ) : (), defined $o{user_char} && !$o{user_char} ? ( '(ASCII(u.username) < 97 OR ASCII(u.username) > 122) AND (ASCII(u.username) < 65 OR ASCII(u.username) > 90)' => 1 ) : (), @@ -200,13 +198,12 @@ sub dbVoteGet { my @select = ( qw|n.vid n.vote n.uid|, q|extract('epoch' from n.date) as date|, $o{what} =~ /user/ ? ('u.username') : (), - $o{what} =~ /vn/ ? (qw|vr.title vr.original|) : (), + $o{what} =~ /vn/ ? (qw|v.title v.original|) : (), ); my @join = ( $o{what} =~ /vn/ ? ( 'JOIN vn v ON v.id = n.vid', - 'JOIN vn_rev vr ON vr.id = v.latest' ) : (), $o{what} =~ /user/ || $o{hide} ? ( 'JOIN users u ON u.id = n.uid' @@ -216,8 +213,8 @@ sub dbVoteGet { my $order = sprintf { date => 'n.date %s', username => 'u.username %s', - title => 'vr.title %s', - vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', vr.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''), + title => 'v.title %s', + vote => 'n.vote %s'.($o{what} =~ /vn/ ? ', v.title ASC' : $o{what} =~ /user/ ? ', u.username ASC' : ''), }->{$o{sort}}, $o{reverse} ? 'DESC' : 'ASC'; my($r, $np) = $self->dbPage(\%o, q| @@ -302,15 +299,14 @@ sub dbWishListGet { my $select = q|wl.vid, wl.wstat, extract('epoch' from wl.added) AS added|; my @join; if($o{what} =~ /vn/) { - $select .= ', vr.title, vr.original'; - push @join, 'JOIN vn v ON v.id = wl.vid', - 'JOIN vn_rev vr ON vr.id = v.latest'; + $select .= ', v.title, v.original'; + push @join, 'JOIN vn v ON v.id = wl.vid'; } my $order = sprintf { - title => 'vr.title %s', + title => 'v.title %s', added => 'wl.added %s', - wstat => 'wl.wstat %2$s, vr.title ASC', + wstat => 'wl.wstat %2$s, v.title ASC', }->{ $o{sort}||'added' }, $o{reverse} ? 'DESC' : 'ASC', $o{reverse} ? 'ASC' : 'DESC'; my($r, $np) = $self->dbPage(\%o, q| |