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
|
package VNDB::DB::Users;
use strict;
use warnings;
use Exporter 'import';
our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel|;
# %options->{ username passwd mail order uid what results page }
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($r, $np) = $s->dbPage(\%o, q|
SELECT *
FROM users u
!W
ORDER BY !s|,
\%where,
$o{order}
);
# XXX: cache please...
if($o{what} =~ /list/ && $#$r >= 0) {
my %r = map {
$r->[$_]{votes} = 0;
$r->[$_]{changes} = 0;
($r->[$_]{id}, $_)
} 0..$#$r;
$r->[$r{$_->{uid}}]{votes} = $_->{cnt} for (@{$s->dbAll(q|
SELECT uid, COUNT(vid) AS cnt
FROM votes
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
WHERE requester IN(!l)
GROUP BY requester|,
[ keys %r ]
)});
}
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 flags |);
$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) VALUES(?, decode(?, 'hex'), ?)|, @o);
}
# 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;
|