summaryrefslogtreecommitdiff
path: root/lib/VNDB/DB/Users.pm
diff options
context:
space:
mode:
Diffstat (limited to 'lib/VNDB/DB/Users.pm')
-rw-r--r--lib/VNDB/DB/Users.pm98
1 files changed, 74 insertions, 24 deletions
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index f8fdfe3f..41f9f25a 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -5,11 +5,15 @@ use strict;
use warnings;
use Exporter 'import';
-our @EXPORT = qw|dbUserGet dbUserEdit dbUserAdd dbUserDel dbUserMessageCount dbSessionAdd dbSessionDel|;
+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: stats extended
+# what: notifycount stats extended
# sort: username registered votes changes tags
sub dbUserGet {
my $s = shift;
@@ -30,8 +34,10 @@ sub dbUserGet {
'ASCII(username) < 97 OR ASCII(username) > 122' => 1 ) : (),
$o{mail} ? (
'mail = ?' => $o{mail} ) : (),
- $o{uid} ? (
+ $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} ? (
@@ -48,9 +54,11 @@ sub dbUserGet {
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|,
+ 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',
@@ -59,6 +67,7 @@ sub dbUserGet {
'(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 = (
@@ -91,7 +100,7 @@ sub dbUserEdit {
my %h;
defined $o{$_} && ($h{$_.' = ?'} = $o{$_})
- for (qw| username mail rank show_nsfw show_list skin customcss salt ign_votes |);
+ 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};
@@ -127,27 +136,10 @@ sub dbUserDel {
}
-# Returns number of unread messages
-sub dbUserMessageCount { # uid
- my($s, $uid) = @_;
- return $s->dbRow(q{
- SELECT SUM(tbi.count) AS cnt FROM (
- SELECT t.count-COALESCE(tb.lastread,0)
- FROM threads_boards tb
- JOIN threads t ON t.id = tb.tid AND NOT t.hidden
- WHERE tb.type = 'u' AND tb.iid = ?
- ) AS tbi (count)
- }, $uid)->{cnt}||0;
-}
-
-
# Adds a session to the database
-# If no expiration is supplied the database default is used
-# uid, 40 character session token, expiration time (timestamp)
+# uid, 40 character session token
sub dbSessionAdd {
- my($s, @o) = @_;
- $s->dbExec(q|INSERT INTO sessions (uid, token, expiration) VALUES(?, decode(?, 'hex'), to_timestamp(?))|,
- @o[0,1], $o[2]||(time+31536000));
+ $_[0]->dbExec(q|INSERT INTO sessions (uid, token) VALUES(?, decode(?, 'hex'))|, @_[1,2]);
}
@@ -162,5 +154,63 @@ sub dbSessionDel {
}
+# 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;