summaryrefslogtreecommitdiff
path: root/lib/VNDB/Util/DB.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNDB/Util/DB.pm')
-rw-r--r--lib/VNDB/Util/DB.pm128
1 files changed, 117 insertions, 11 deletions
diff --git a/lib/VNDB/Util/DB.pm b/lib/VNDB/Util/DB.pm
index 46a17fb1..fdefa6e6 100644
--- a/lib/VNDB/Util/DB.pm
+++ b/lib/VNDB/Util/DB.pm
@@ -16,6 +16,7 @@ $VERSION = $VNDB::VERSION;
DBGetUser DBAddUser DBUpdateUser DBDelUser
DBGetVotes DBVoteStats DBAddVote DBDelVote
DBGetVNList DBVNListStats DBAddVNList DBEditVNList DBDelVNList
+ DBGetRList DBGetRLists DBEditRList DBDelRList
DBGetVN DBAddVN DBEditVN DBHideVN DBUndefRG DBVNCache
DBGetRelease DBAddRelease DBEditRelease DBHideRelease
DBGetProducer DBGetProducerVN DBAddProducer DBEditProducer DBHideProducer
@@ -141,7 +142,7 @@ sub DBTableCount { # table (users, producers, vn, releases, votes)
%s|,
$_[1],
$_[1] =~ /producers|vn|releases/ ? 'WHERE hidden = 0' : '',
- )->{cnt};
+ )->{cnt} - ($_[1] eq 'users' ? 1 : 0);
}
@@ -304,14 +305,6 @@ sub DBGetUser { # %options->{ username mail passwd order firstchar uid results p
[ keys %r ]
)});
- $r->[$r{$_->{uid}}]{vnlist} = $_->{cnt} for (@{$s->DBAll(q|
- SELECT uid, COUNT(vid) AS cnt
- FROM vnlists
- WHERE uid IN(!l)
- GROUP BY uid|,
- [ keys %r ]
- )});
-
$r->[$r{$_->{requester}}]{changes} = $_->{cnt} for (@{$s->DBAll(q|
SELECT requester, COUNT(id) AS cnt
FROM changes
@@ -390,7 +383,7 @@ sub DBGetVotes { # %options->{ uid vid hide order results page }
my %where = (
$o{uid} ? ( 'n.uid = %d' => $o{uid} ) : (),
$o{vid} ? ( 'n.vid = %d' => $o{vid} ) : (),
- $o{hide} ? ( 'u.flags & %d = %1$d' => $VNDB::UFLAGS->{votes} ) : (),
+ $o{hide} ? ( 'u.flags & %d = %1$d' => $VNDB::UFLAGS->{list} ) : (),
);
my $where = scalar keys %where ? 'WHERE !W' : '';
@@ -553,6 +546,119 @@ sub DBDelVNList { # uid, @vid # uid = 0 to delete all
#-----------------------------------------------------------------------------#
+# U S E R R E L E A S E L I S T S #
+#-----------------------------------------------------------------------------#
+
+
+sub DBGetRList { # %options->{ uid rids }
+ my($s, %o) = @_;
+
+ my %where = (
+ 'uid = %d' => $o{uid},
+ $o{rids} ? (
+ 'rid IN(!l)' => $o{rids} ) : (),
+ );
+
+ return $s->DBAll(q|
+ SELECT uid, rid, rstat, vstat
+ FROM rlists
+ WHERE !W|,
+ \%where);
+}
+
+
+# separate function, which also fetches VN info and votes
+sub DBGetRLists { # %options->{ uid order char rstat vstat voted page results }
+ my($s, %o) = @_;
+
+ $o{results} ||= 50;
+ $o{page} ||= 1;
+
+ # bit ugly...
+ my $where = !$o{rstat} && !$o{vstat} ? 'vo.vote IS NOT NULL' : '';
+ $where .= ($where?' OR ':'').q|v.id IN(
+ SELECT irv.vid
+ FROM rlists irl
+ JOIN releases ir ON ir.id = irl.rid
+ JOIN releases_vn irv ON irv.rid = ir.latest
+ WHERE !W
+ )| if !$o{voted};
+ $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 clause for the rlists subquery
+ my %where = (
+ 'uid = %d' => $o{uid},
+ defined $o{rstat} ? ( 'rstat = %d' => $o{rstat} ) : (),
+ defined $o{vstat} ? ( 'vstat = %d' => $o{vstat} ) : (),
+ );
+
+ my $r = $s->DBAll(qq|
+ SELECT vr.vid, vr.title, v.c_released, v.c_languages, v.c_platforms, COALESCE(vo.vote, 0) AS vote
+ FROM vn v
+ JOIN vn_rev vr ON vr.id = v.latest
+ %s JOIN votes vo ON vo.vid = v.id AND vo.uid = %d
+ WHERE $where
+ ORDER BY %s
+ LIMIT %d OFFSET %d|,
+ $o{voted} ? '' : 'LEFT', $o{uid}, # JOIN if we only want votes, LEFT JOIN if we also want rlist items
+ $o{voted} ? () : \%where,
+ $o{order},
+ $o{results}+(wantarray?1:0), $o{results}*($o{page}-1)
+ );
+
+ # now fetch the releases and link them to VNs
+ if(@$r) {
+ my %vns = map { $_->{rels}=[]; $_->{vid}, $_->{rels} } @$r;
+ push @{$vns{$_->{vid}}}, $_ for (@{$s->DBAll(q|
+ SELECT rv.vid, rr.rid, rr.title, rr.original, rr.released, rr.type, rr.language, rr.minage, rl.rstat, rl.vstat
+ 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
+ WHERE rl.uid = %d
+ AND rv.vid IN(!l)
+ ORDER BY rr.released ASC|,
+ $o{uid}, [ keys %vns ]
+ )});
+ }
+
+ return $r if !wantarray;
+ return ($r, 0) if $#$r < $o{results};
+ pop @$r;
+ return ($r, 1);
+}
+
+
+sub DBEditRList { # %options->{ uid rid rstat vstat }
+ # rid can only be a arrayref with UPDATE
+ my($s, %o) = @_;
+ my %s = (
+ defined $o{rstat} ? ( 'rstat = %d', $o{rstat} ) : (),
+ defined $o{vstat} ? ( 'vstat = %d', $o{vstat} ) : (),
+ );
+ $o{rstat}||=0;
+ $o{vstat}||=0;
+
+ $s->DBExec(q|UPDATE rlists SET !H WHERE uid = %d AND rid IN(!l)|,
+ \%s, $o{uid}, ref($o{rid}) eq 'ARRAY' ? $o{rid} : [ $o{rid} ])
+ ||
+ $s->DBExec(q|INSERT INTO rlists (uid, rid, rstat, vstat)
+ VALUES(%d, %d, %d, %d)|,
+ @o{qw| uid rid rstat vstat |});
+}
+
+
+sub DBDelRList { # uid, \@rids
+ my($s, $uid, $rid) = @_;
+ $s->DBExec(q|DELETE FROM rlists WHERE uid = %d AND rid IN(!l)|, $uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ]);
+}
+
+
+
+
+
+#-----------------------------------------------------------------------------#
# V I S U A L N O V E L S #
#-----------------------------------------------------------------------------#
@@ -1374,7 +1480,7 @@ sub sqlhelper { # type, query, @list
$sqlq =~ s/\r?\n/ /g;
$sqlq =~ s/ +/ /g;
$sqlq = sqlprint($sqlq, @_) if exists $_[0];
-# warn "$sqlq\n";
+ #warn "$sqlq\n";
my $q = $s->prepare($sqlq);
$q->execute();