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
|
package VNDB::DB::Users;
use strict;
use warnings;
use Exporter 'import';
our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel|;
# %options->{ username passwd mail order uid results page what }
# what: stats
sub dbUserGet {
my $s = shift;
my %o = (
order => 'username ASC',
page => 1,
results => 10,
what => '',
@_
);
my %where = (
$o{username} ? (
'username = ?' => $o{username} ) : (),
$o{passwd} ? (
'passwd = decode(?, \'hex\')' => $o{passwd} ) : (),
$o{firstchar} ? (
'SUBSTRING(username from 1 for 1) = ?' => $o{firstchar} ) : (),
!$o{firstchar} && defined $o{firstchar} ? (
'ASCII(username) < 97 OR ASCII(username) > 122' => 1 ) : (),
$o{mail} ? (
'mail = ?' => $o{mail} ) : (),
$o{uid} ? (
'id = ?' => $o{uid} ) : (),
!$o{uid} && !$o{username} ? (
'id > 0' => 1 ) : (),
);
my @select = (
'u.*',
$o{what} =~ /stats/ ? (
'(SELECT COUNT(*) FROM rlists WHERE uid = u.id) AS releasecount',
'(SELECT COUNT(DISTINCT rv.vid) FROM rlists rl JOIN releases r ON rl.rid = r.id JOIN releases_vn rv ON rv.rid = r.latest WHERE uid = u.id) AS vncount',
'(SELECT COUNT(*) FROM threads_posts WHERE uid = u.id) AS postcount',
'(SELECT COUNT(*) FROM threads_posts WHERE uid = u.id AND num = 1) AS threadcount',
) : (),
);
my($r, $np) = $s->dbPage(\%o, q|
SELECT !s
FROM users u
!W
ORDER BY !s|,
join(', ', @select), \%where, $o{order}
);
return wantarray ? ($r, $np) : $r;
}
# uid, %options->{ columns in users table }
sub dbUserEdit {
my($s, $uid, %o) = @_;
my %h;
defined $o{$_} && ($h{$_.' = ?'} = $o{$_})
for (qw| username mail rank show_nsfw show_list skin |);
$h{'passwd = decode(?, \'hex\')'} = $o{passwd}
if defined $o{passwd};
return if scalar keys %h <= 0;
return $s->dbExec(q|
UPDATE users
!H
WHERE id = ?|,
\%h, $uid);
}
# username, md5(pass), mail
sub dbUserAdd {
my($s, @o) = @_;
$s->dbExec(q|INSERT INTO users (username, passwd, mail, registered) VALUES(?, decode(?, 'hex'), ?, ?)|, @o, time);
}
# uid
sub dbUserDel {
my($s, $id) = @_;
$s->dbExec($_, $id) for (
q|DELETE FROM vnlists WHERE uid = ?|,
q|DELETE FROM rlists WHERE uid = ?|,
q|DELETE FROM wlists WHERE uid = ?|,
q|DELETE FROM votes WHERE uid = ?|,
q|UPDATE changes SET requester = 0 WHERE requester = ?|,
q|UPDATE threads_posts SET uid = 0 WHERE uid = ?|,
q|DELETE FROM users WHERE id = ?|
);
}
1;
|