summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Users.pm
blob: b9d662000498055c78a0c8a8b1852eadf00c8090 (plain)
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221

package VNDB::DB::Users;

use strict;
use warnings;
use Exporter 'import';

our @EXPORT = qw|
  dbUserGet dbUserEdit dbUserAdd dbUserDel
  dbSessionAdd dbSessionDel dbSessionUpdateLastUsed
  dbNotifyGet dbNotifyMarkRead dbNotifyRemove
|;


# %options->{ username passwd mail session uid ip registered search results page what sort reverse }
# what: notifycount stats extended
# sort: username registered votes changes tags
sub dbUserGet {
  my $s = shift;
  my %o = (
    page => 1,
    results => 10,
    what => '',
    @_
  );

  $o{search} =~ s/%// if $o{search};
  my %where = (
    $o{username} ? (
      'username = ?' => $o{username} ) : (),
    $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} && !ref($o{uid}) ? (
      'id = ?' => $o{uid} ) : (),
    $o{uid} && ref($o{uid}) ? (
      'id IN(!l)' => [ $o{uid} ]) : (),
    !$o{uid} && !$o{username} ? (
      'id > 0' => 1 ) : (),
    $o{ip} ? (
      'ip = ?' => $o{ip} ) : (),
    $o{registered} ? (
      'registered > to_timestamp(?)' => $o{registered} ) : (),
    $o{search} ? (
      'username ILIKE ?' => "%$o{search}%") : (),
    $o{session} ? (
      q|s.token = decode(?, 'hex')| => $o{session} ) : (),
  );

  my @select = (
    qw|id username c_votes c_changes show_list c_tags|,
    q|extract('epoch' from registered) as registered|,
    $o{what} =~ /extended/ ? (
      qw|mail rank salt skin customcss show_nsfw ign_votes notify_dbedit notify_announce|,
      q|encode(passwd, 'hex') AS passwd|
    ) : (),
    $o{what} =~ /notifycount/ ?
      '(SELECT COUNT(*) FROM notifications WHERE uid = u.id AND read IS NULL) AS notifycount' : (),
    $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',
      '(SELECT COUNT(DISTINCT tag) FROM tags_vn WHERE uid = u.id) AS tagcount',
      '(SELECT COUNT(DISTINCT vid) FROM tags_vn WHERE uid = u.id) AS tagvncount',
    ) : (),
    $o{session} ? q|extract('epoch' from s.lastused) as session_lastused| : (),
  );

  my @join = (
    $o{session} ? 'JOIN sessions s ON s.uid = u.id' : (),
  );

  my $order = sprintf {
    username => 'u.username %s',
    registered => 'u.registered %s',
    votes => 'NOT u.show_list, u.c_votes %s',
    changes => 'u.c_changes %s',
    tags => 'u.c_tags %s',
  }->{ $o{sort}||'username' }, $o{reverse} ? 'DESC' : 'ASC';

  my($r, $np) = $s->dbPage(\%o, q|
    SELECT !s
      FROM users u
      !s
      !W
      ORDER BY !s|,
    join(', ', @select), join(' ', @join), \%where, $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 customcss salt ign_votes notify_dbedit notify_announce |);
  $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, pass(ecrypted), salt, mail, [ip]
sub dbUserAdd {
  my($s, @o) = @_;
  $s->dbExec(q|INSERT INTO users (username, passwd, salt, mail, ip) VALUES(?, decode(?, 'hex'), ?, ?, ?)|,
    @o[0..3], $o[4]||$s->reqIP);
}


# uid
sub dbUserDel {
  my($s, $id) = @_;
  # TODO: delete/update all those referenced rows using PgSQL reference actions
  $s->dbExec($_, $id) for (
    q|DELETE FROM rlists WHERE uid = ?|,
    q|DELETE FROM wlists WHERE uid = ?|,
    q|DELETE FROM votes WHERE uid = ?|,
    q|DELETE FROM tags_vn WHERE uid = ?|,
    q|DELETE FROM sessions WHERE uid = ?|,
    q|DELETE FROM notifications WHERE uid = ?|,
    q|UPDATE notifications SET c_byuser = 0 WHERE c_byuser = ?|,
    q|UPDATE changes SET requester = 0 WHERE requester = ?|,
    q|UPDATE tags SET addedby = 0 WHERE addedby = ?|,
    q|UPDATE threads_posts SET uid = 0 WHERE uid = ?|,
    q|DELETE FROM users WHERE id = ?|
  );
}


# Adds a session to the database
# uid, 40 character session token
sub dbSessionAdd {
  $_[0]->dbExec(q|INSERT INTO sessions (uid, token) VALUES(?, decode(?, 'hex'))|, @_[1,2]);
}


# Deletes session(s) from the database
# If no token is supplied, all sessions for the uid are destroyed
# uid, token (optional)
sub dbSessionDel {
  my($s, @o) = @_;
  my %where = ('uid = ?' => $o[0]);
  $where{"token = decode(?, 'hex')"} = $o[1] if $o[1];
  $s->dbExec('DELETE FROM sessions !W', \%where);
}


# uid, token
sub dbSessionUpdateLastUsed {
  $_[0]->dbExec(q|UPDATE sessions SET lastused = NOW() WHERE uid = ? AND token = decode(?, 'hex')|, $_[1], $_[2]);
}


# %options->{ uid id what results page }
# what: titles
sub dbNotifyGet {
  my($s, %o) = @_;
  $o{what} ||= '';
  $o{results} ||= 10;
  $o{page} ||= 1;

  my %where = (
    'n.uid = ?' => $o{uid},
    $o{id} ? (
      'n.id = ?' => $o{id} ) : (),
    defined($o{read}) ? (
      'n.read !s' => $o{read} ? 'IS NOT NULL' : 'IS NULL' ) : (),
  );

  my @join = (
    $o{what} =~ /titles/ ? 'LEFT JOIN users u ON n.c_byuser = u.id' : (),
  );

  my @select = (
    qw|n.id n.ntype n.ltype n.iid n.subid|,
    q|extract('epoch' from n.date) as date|,
    q|extract('epoch' from n.read) as read|,
    $o{what} =~ /titles/ ? qw|u.username n.c_title| : (),
  );

  my($r, $np) = $s->dbPage(\%o, q|
    SELECT !s
      FROM notifications n
      !s
      !W
      ORDER BY n.id
  |, join(', ', @select), join(' ', @join), \%where);
  return wantarray ? ($r, $np) : $r;
}


# ids
sub dbNotifyMarkRead {
  my $s = shift;
  $s->dbExec('UPDATE notifications SET read = NOW() WHERE id IN(!l)', \@_);
}


# ids
sub dbNotifyRemove {
  my $s = shift;
  $s->dbExec('DELETE FROM notifications WHERE id IN(!l)', \@_);
}


1;