Re: Listing Active CGI::Sessions

[antirice]

If you're using CGI::Session::MySQL, you can alter it to put the expiration time in a column.

sub store {
    my ($self, $sid, $options, $data) = @_;   

    my $dbh = $self->MySQL_dbh($options);
    my $lck_status = $dbh->selectrow_array(qq|SELECT GET_LOCK("$sid", 
+10)|);
    unless ( $lck_status == 1 ) {
        $self->error("Couldn't acquire lock on id '$sid'. Lock status:
+ $lck_status");
        return undef;
    }

    $dbh->do(qq|REPLACE INTO $TABLE_NAME (id, expires, a_session) VALU
+ES(?,FROM_UNIXTIME(?),?)|, 
                undef, $sid, $self->expire(), $self->freeze($data));
    
    return $dbh->selectrow_array(qq|SELECT RELEASE_LOCK("$sid")|);
}

And the schema just needs to be updated:

CREATE TABLE sessions (
        id CHAR(32) NOT NULL UNIQUE,
        expires datetime,
        a_session TEXT NOT NULL
    );

Now in order to find all active sessions, run the query:

select id from sessions where expires <= NOW() or expires is null

...or something like that ;-P