summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/ULists.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNDB/DB/ULists.pm')
-rw-r--r--lib/VNDB/DB/ULists.pm138
1 files changed, 87 insertions, 51 deletions
diff --git a/lib/VNDB/DB/ULists.pm b/lib/VNDB/DB/ULists.pm
index e52cbf37..e6b6419a 100644
--- a/lib/VNDB/DB/ULists.pm
+++ b/lib/VNDB/DB/ULists.pm
@@ -7,56 +7,62 @@ 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, 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') : (),
+ defined($o{status})? ('vl.status = ?' => $o{status}) : (),
+ $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 +71,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, 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
- !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 +89,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.status
FROM rlists rl
JOIN releases r ON rl.rid = r.id
JOIN releases_rev rr ON rr.id = r.latest
@@ -114,35 +120,57 @@ sub dbVNListList {
}
-# %options->{ uid rid rstat vstat }
+# Arguments: uid vid status notes
+# vid can be an arrayref only when the rows are already present, in which case an update is done
+# status and notes can be undef when an update is done, in which case these fields aren't updated
sub dbVNListAdd {
- my($self, %o) = @_;
+ my($self, $uid, $vid, $stat, $notes) = @_;
+ $self->dbExec(
+ 'UPDATE vnlists !H WHERE uid = ? AND vid IN(!l)',
+ {defined($stat) ? ('status = ?' => $stat ):(),
+ defined($notes)? ('notes = ?' => $notes):()},
+ $uid, ref($vid) ? $vid : [ $vid ]
+ )
+ ||
+ $self->dbExec(
+ 'INSERT INTO vnlists (uid, vid, status, notes) VALUES(?, ?, ?, ?)',
+ $uid, $vid, $stat||0, $notes||''
+ );
+}
- 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 status = ? 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, status) 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 ]
);
}
@@ -160,8 +188,14 @@ sub dbVoteGet {
my %where = (
$o{uid} ? ( 'n.uid = ?' => $o{uid} ) : (),
$o{vid} ? ( 'n.vid = ?' => $o{vid} ) : (),
- $o{hide} ? ( 'u.show_list = TRUE' => 1 ) : (),
+ $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} ) : (),
+ 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 ) : (),
+ $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 ) : (),
);
my @select = (
@@ -221,14 +255,15 @@ sub dbVoteStats {
# Adds a new vote or updates an existing one
# Arguments: vid, uid, vote
+# vid can be an arrayref only when the rows are already present, in which case an update is done
sub dbVoteAdd {
my($self, $vid, $uid, $vote) = @_;
$self->dbExec(q|
UPDATE votes
- SET vote = ?
- WHERE vid = ?
+ SET vote = ?, date = NOW()
+ WHERE vid IN(!l)
AND uid = ?|,
- $vote, $vid, $uid
+ $vote, ref($vid) ? $vid : [$vid], $uid
) || $self->dbExec(q|
INSERT INTO votes
(vid, uid, vote)
@@ -239,10 +274,11 @@ sub dbVoteAdd {
# Arguments: uid, vid
+# vid can be an arrayref
sub dbVoteDel {
my($self, $uid, $vid) = @_;
$self->dbExec('DELETE FROM votes !W',
- { 'vid = ?' => $vid, 'uid = ?' => $uid }
+ { 'vid IN(!l)' => [ref($vid)?$vid:[$vid]], 'uid = ?' => $uid }
);
}