diff options
Diffstat (limited to 'lib/VNDB/DB/Users.pm')
-rw-r--r-- | lib/VNDB/DB/Users.pm | 98 |
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; |