1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
|
package VNDB::DB::VNList;
use strict;
use warnings;
use Exporter 'import';
our @EXPORT = qw|dbVNListGet dbVNListList dbVNListAdd dbVNListDel|;
# Simpler and more efficient version of dbVNListList below
# %options->{ uid rid }
sub dbVNListGet {
my($self, %o) = @_;
my %where = (
'uid = ?' => $o{uid},
$o{rid} ? (
'rid = ?' => $o{rid} ) : (),
);
return $self->dbAll(q|
SELECT uid, rid, rstat, vstat
FROM rlists
!W|,
\%where
);
}
# %options->{ uid order char voted page results }
# 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{order} ||= 'vr.title ASC';
$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 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 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;
# execute query
my($r, $np) = $self->dbPage(\%o, qq|
SELECT vr.vid, vr.title, vr.original, 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 = ?
WHERE $where
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} : (), $o{order},
);
# fetch releases and link to VNs
if(@$r) {
my %vns = map {
$_->{rels}=[];
$_->{vid}, $_->{rels}
} @$r;
push @{$vns{$_->{vid}}}, $_ for (@{$self->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 = ?
AND rv.vid IN(!l)
ORDER BY rr.released ASC|,
$o{uid}, [ keys %vns ]
)});
}
return wantarray ? ($r, $np) : $r;
}
# %options->{ uid rid rstat vstat }
sub dbVNListAdd {
my($self, %o) = @_;
my %s = (
defined $o{rstat} ? ( 'rstat = ?', $o{rstat} ) : (),
defined $o{vstat} ? ( 'vstat = ?', $o{vstat} ) : (),
);
$o{rstat}||=0;
$o{vstat}||=0;
$self->dbExec(
'UPDATE rlists !H WHERE uid = ? AND rid IN(!l)',
\%s, $o{uid}, ref($o{rid}) eq 'ARRAY' ? $o{rid} : [ $o{rid} ]
)
||
$self->dbExec(
'INSERT INTO rlists (uid, rid, rstat, vstat) VALUES(!l)',
[@o{qw| uid rid rstat vstat |}]
);
}
# Arguments: uid, rid
sub dbVNListDel {
my($self, $uid, $rid) = @_;
$self->dbExec(
'DELETE FROM rlists WHERE uid = ? AND rid IN(!l)',
$uid, ref($rid) eq 'ARRAY' ? $rid : [ $rid ]
);
}
1;
|
