diff options
author | Yorhel <git@yorhel.nl> | 2010-12-19 16:01:58 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2010-12-19 16:01:58 +0100 |
commit | 6cf3b2a6f7cd3fb12282e823f42a64bcdf57d012 (patch) | |
tree | 92ae905399d7ab10e5c3f8ed0106d353749c6e7b /lib/VNDB/DB | |
parent | a129097a782ced2f2c3622f239809a937ecdb7d4 (diff) |
RFC-01: Updated and improved /u+/list
I'm sure I broke all vnlist/rlist-related features on the rest of the
site since I modified the DB abstractions. But these will all have to
be updated/rewritten anyway.
Diffstat (limited to 'lib/VNDB/DB')
-rw-r--r-- | lib/VNDB/DB/ULists.pm | 116 |
1 files changed, 70 insertions, 46 deletions
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm index e52cbf37..1aedfe80 100644 --- a/lib/VNDB/DB/ULists.pm +++ b/lib/VNDB/DB/ULists.pm @@ -7,56 +7,61 @@ use Exporter 'import'; our @EXPORT = qw| - dbVNListGet dbVNListList dbVNListAdd dbVNListDel + dbRListGet dbVNListGet dbVNListList dbVNListAdd dbVNListDel dbRListAdd dbRListDel dbVoteGet dbVoteStats dbVoteAdd dbVoteDel dbWishListGet dbWishListAdd dbWishListDel |; -# Simpler and more efficient version of dbVNListList below -# %options->{ uid rid } -sub dbVNListGet { +# Options: uid rid +sub dbRListGet { my($self, %o) = @_; my %where = ( 'uid = ?' => $o{uid}, - $o{rid} && !ref $o{rid} ? ( - 'rid = ?' => $o{rid} ) : (), - $o{rid} && ref $o{rid} ? ( - 'rid IN(!l)' => [$o{rid}] ) : (), + $o{rid} ? ('rid IN(!l)' => [ ref $o{rid} ? $o{rid} : [$o{rid}] ]) : (), ); return $self->dbAll(q| - SELECT uid, rid, rstat, vstat + SELECT uid, rid, rstat AS status FROM rlists !W|, \%where ); } +# Options: uid vid +sub dbVNListGet { + my($self, %o) = @_; -# %options->{ uid char voted page results sort reverse } + my %where = ( + 'uid = ?' => $o{uid}, + $o{vid} ? ('vid IN(!l)' => [ ref $o{vid} ? $o{vid} : [$o{vid}] ]) : (), + ); + + return $self->dbAll(q| + SELECT uid, vid, status + FROM vnlists + !W|, + \%where + ); +} + + +# Options: uid char voted page results sort reverse # sort: title vote -# NOTE: this function is mostly copied from 1.x, may need some rewriting... sub dbVNListList { my($self, %o) = @_; - $o{results} ||= 50; $o{page} ||= 1; - $o{voted} ||= 0; # -1: only non-voted, 0: all, 1: only voted - - # construct the global WHERE clause - my $where = $o{voted} != -1 ? 'vo.vote IS NOT NULL' : ''; - $where .= ($where?' OR ':'').q|v.id = ANY(ARRAY( - SELECT irv.vid - FROM rlists irl - JOIN releases ir ON ir.id = irl.rid - JOIN releases_vn irv ON irv.rid = ir.latest - WHERE uid = ? - ))| if $o{voted} != 1; - $where = '('.$where.') AND LOWER(SUBSTR(vr.title, 1, 1)) = \''.$o{char}.'\'' if $o{char}; - $where = '('.$where.') AND (ASCII(vr.title) < 97 OR ASCII(vr.title) > 122) AND (ASCII(vr.title) < 65 OR ASCII(vr.title) > 90)' if defined $o{char} && !$o{char}; - $where = '('.$where.') AND vo.vote IS NULL' if $o{voted} == -1; + + my %where = ( + 'vl.uid = ?' => $o{uid}, + defined($o{voted}) ? ('vo.vote !s NULL' => $o{voted} ? 'IS NOT' : 'IS') : (), + $o{char} ? ('LOWER(SUBSTR(vr.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 ) : (), + ); my $order = sprintf { title => 'vr.title %s', @@ -65,14 +70,14 @@ sub dbVNListList { # execute query my($r, $np) = $self->dbPage(\%o, qq| - SELECT vr.vid, vr.title, vr.original, COALESCE(vo.vote, 0) AS vote - FROM vn v + SELECT vr.vid, vr.title, vr.original, vl.status, 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 - !s JOIN votes vo ON vo.vid = v.id AND vo.uid = ? - WHERE $where + LEFT JOIN votes vo ON vo.vid = vl.vid AND vo.uid = vl.uid + !W ORDER BY !s|, - $o{voted} == 1 ? '' : 'LEFT', $o{uid}, # JOIN if we only want votes, LEFT JOIN if we also want rlist items - $o{voted} != 1 ? $o{uid} : (), $order + \%where, $order ); # fetch releases and link to VNs @@ -83,7 +88,7 @@ sub dbVNListList { } @$r; my $rel = $self->dbAll(q| - SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.rstat, rl.vstat + SELECT rv.vid, rr.rid, r.latest, rr.title, rr.original, rr.released, rr.type, rl.rstat AS status FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_rev rr ON rr.id = r.latest @@ -114,35 +119,54 @@ sub dbVNListList { } -# %options->{ uid rid rstat vstat } +# Arguments: uid vid status +# vid can be an arrayref only when the rows are already present, in which case an update is done sub dbVNListAdd { - my($self, %o) = @_; + my($self, $uid, $vid, $stat) = @_; + $self->dbExec( + 'UPDATE vnlists SET status = ? WHERE uid = ? AND vid IN(!l)', + $stat, $uid, ref($vid) ? $vid : [ $vid ] + ) + || + $self->dbExec( + 'INSERT INTO vnlists (uid, vid, status) VALUES(?, ?, ?)', + $uid, $vid, $stat + ); +} - my %s = ( - defined $o{rstat} ? ( 'rstat = ?', $o{rstat} ) : (), - defined $o{vstat} ? ( 'vstat = ?', $o{vstat} ) : (), + +# Arguments: uid, vid +sub dbVNListDel { + my($self, $uid, $vid) = @_; + $self->dbExec( + 'DELETE FROM vnlists WHERE uid = ? AND vid IN(!l)', + $uid, ref($vid) ? $vid : [ $vid ] ); - $o{rstat}||=0; - $o{vstat}||=0; +} + +# Arguments: uid rid status +# rid can be an arrayref only when the rows are already present, in which case an update is done +sub dbRListAdd { + my($self, $uid, $rid, $stat) = @_; $self->dbExec( - 'UPDATE rlists !H WHERE uid = ? AND rid IN(!l)', - \%s, $o{uid}, ref($o{rid}) eq 'ARRAY' ? $o{rid} : [ $o{rid} ] + 'UPDATE rlists SET rstat = ? WHERE uid = ? AND rid IN(!l)', + $stat, $uid, ref($rid) ? $rid : [ $rid ] ) || $self->dbExec( - 'INSERT INTO rlists (uid, rid, rstat, vstat) VALUES(!l)', - [@o{qw| uid rid rstat vstat |}] + 'INSERT INTO rlists (uid, rid, rstat) VALUES(?, ?, ?)', + $uid, $rid, $stat ); } # Arguments: uid, rid -sub dbVNListDel { +sub dbRListDel { my($self, $uid, $rid) = @_; $self->dbExec( 'DELETE FROM rlists WHERE uid = ? AND rid IN(!l)', - $uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ] + $uid, ref($rid) ? $rid : [ $rid ] ); } |