Keep track of when a session has last been used

If we're going to automatically remove older sessions, it would make
more sense to remove unused sessions, rather than old sessions that are
still in use. But we first need to keep track of when a session has last
been used to do so...

5 files changed, 17 insertions, 1 deletions

diff --git a/ChangeLog b/ChangeLog
index ea2f908a..f2b21f11 100644
--- a/ChangeLog
+++ b/ChangeLog
@@ -10,6 +10,7 @@ git - ?
 	- Only tagmods can create top-level tags
 	- Notification system (still only used for PMs)
 	- Removed the ?l10n= paremeter
+	- Keep track of when a session has last been used
 
 2.10 - 2010-01-10
 	- VN score on tag pages use plain averages instead of bayesian rating
diff --git a/lib/VNDB/DB/Users.pm b/lib/VNDB/DB/Users.pm
index 10c90cf6..8805075f 100644
--- a/lib/VNDB/DB/Users.pm
+++ b/lib/VNDB/DB/Users.pm
@@ -6,7 +6,8 @@ use warnings;
 use Exporter 'import';
 
 our @EXPORT = qw|
-  dbUserGet dbUserEdit dbUserAdd dbUserDel dbSessionAdd dbSessionDel
+  dbUserGet dbUserEdit dbUserAdd dbUserDel
+  dbSessionAdd dbSessionDel dbSessionUpdateLastUsed
   dbNotifyGet dbNotifyMarkRead dbNotifyRemove
 |;
 
@@ -66,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 = (
@@ -155,6 +157,12 @@ 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 {
diff --git a/lib/VNDB/Util/Auth.pm b/lib/VNDB/Util/Auth.pm
index 99434c3f..978b882c 100644
--- a/lib/VNDB/Util/Auth.pm
+++ b/lib/VNDB/Util/Auth.pm
@@ -26,6 +26,8 @@ sub authInit {
   my $token = substr($cookie, 0, 40);
   my $uid  = substr($cookie, 40);
   $self->{_auth} = $uid =~ /^\d+$/ && $self->dbUserGet(uid => $uid, session => $token, what => 'extended notifycount')->[0];
+  # update the sessions.lastused column if lastused < now()'6 hours'
+  $self->dbSessionUpdateLastUsed($uid, $token) if $self->{_auth} && $self->{_auth}{session_lastused} < time()-6*3600;
   return _rmcookie($self) if !$self->{_auth};
 }
 
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index f345cffd..f0091929 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -173,6 +173,7 @@ CREATE TABLE sessions (
   uid integer NOT NULL,
   token bytea NOT NULL,
   expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval),
+  lastused timestamptz NOT NULL DEFAULT NOW(),
   PRIMARY KEY (uid, token)
 );
 
diff --git a/util/updates/update_2.11.sql b/util/updates/update_2.11.sql
index bf999cdc..09efd7d6 100644
--- a/util/updates/update_2.11.sql
+++ b/util/updates/update_2.11.sql
@@ -81,3 +81,7 @@ UNION SELECT 'r', COUNT(*) FROM (SELECT tmp_edit_hidlock('r', id) FROM releases
 UNION SELECT 'p', COUNT(*) FROM (SELECT tmp_edit_hidlock('p', id) FROM producers WHERE hidden OR locked) x;
 DROP FUNCTION tmp_edit_hidlock(text, integer);
 
+
+-- keep track of when a session is last used
+ALTER TABLE sessions ADD COLUMN lastused timestamptz NOT NULL DEFAULT NOW();
+