Session class for SQLite

I needed Session management for a web project I made using Delphi with WebBroker, and found a unit called MISessionClass in newsgroups created by Tony Caduto. The problem with that unit was it worked with Interbase and my web hosting provider doesn't inlcude Interbase/Firebird in the package I purchased.

SQLite to the rescue

My hosting provider doesn't allow me to use Interbase/Firebird, but I can use SQLite to store my session data. To access SQLite databases, I use the exelent TLiteDb connector by Renè for DuBaron.com and adapted the unit mentioned in the previous paragraph to this database.

This is the result:

unit SqliteSessionClass;

{**********************************************************************
Open Source WebBroker Session Management Class For SQLite

All database management is done by TLiteDb object by Rene from dubaron.com

Modified version of Tony Caduto's MISessionClass.
Programmed by Leonardo M. Ramé (martinrame at yahoo dot com)
Sep 22th, 2007

Some Code based loosely on the MDweb Components by Mark Brittingham
Programming by Tony Caduto (tcad...@amsoftwaredesign.com)
May 24th, 2001
This class uses a IBexpress database,transaction and query which are created in the
constructor.
To use you must create a table on some interbase server (it could be on the
same pc as the webserver or on a remote server) called SESSIONS which
contains three fields:

SESSIONID      (integer)
SESSTIMESTAMP  (Timestamp)
SESSDATA       (blob)

In the constructor set your database name IE www.myserver.com:c:\yourpath\yourdatabase.gdb
and the SQL dialect

Add a public declaration for the class IE
PUBLIC
 SessionMgr:TMISession;

In your webmodule add the following:
in the oncreate event
SessionMgr:=TMISession.create;
in the ondestroy event add:
SessionMgr.free;

Use is very similar to MDweb without all the extra stuff IE special tags etc
This class works with the standard Webbroker pageproducer.

In the before dispatch add something like this:
 if request.queryFields.Values['islogon'] <> 'true' then
      begin
           SessionMgr.SessionID :=
             StrToIntDef(Request.CookieFields.Values['SID'], 0);
           if (Not SessionMgr.FindSessionRecord) then
           Response.SendRedirect('http://www.yourserver.com/timeout.htm');
      end;

In your logon or other action add code like this:
 SessionMgr.CreateSessionRecord;
     cookiestrings:=tstringlist.create;
     cookiestrings.add('SID='+intTostr(sessionmgr.SessionID));
     response.SetCookieField(cookiestrings,'','',now+1,false);
     SessionMgr.Values['USERID']        := userid;
     SessionMgr.Values['PINNUMBER']     := pinnumber;
     SessionMgr.Values['COMPANYPATH']   := companypath ;
     SessionMgr.Values['TOPATH']        := topath ;
     SessionMgr.Values['FROMPATH']      := frompath ;
     SessionMgr.Values['COMPANYNAME']   := companyname;
     SessionMgr.Values['COMPANYID']     := CompanyID;
     SessionMgr.Values['FIRSTNAME']     := firstname;
     SessionMgr.Values['LASTNAME']      := Lastname;
     SessionMgr.Values['MI']            := MidIntial;
     SessionMgr.Values['EMAIL']         := UserEmail;

     To read or set values do this:

     SET A value
     SessionMgr.values['Firstname'] := 'Tony';

     Get a Value
     thelastname:=  SessionMgr.values['Lastname']

     Because this is not based on a shared btree this class should work on
     linux as well as with
     CGI.  You are not limited to ISAPI

}

interface

uses
sysutils,

classes,
passqlite,
passql;

type
  TLiteSession = class(Tobject)

  private
    SessionDatabase: TLiteDb;
  protected
  public
    ExpireInterval : TDateTime;

    SessionID: Integer;
    SessionTimeStamp: TDateTime;
    SessionData: TStringList;

  procedure SetValue(const Name, Value: string);

  function GetValue(const Name: string): string;

  function  FindSessionRecord:boolean;
  procedure DeleteExpiredSessions(thetime:tdatetime);

  procedure CreateSessionRecord;
  property  Values[const Name: string]: string read GetValue write SetValue;

  constructor Create(ADataBase: TLiteDb);
  destructor Destroy; override;

  published
end;

implementation

{ TLiteSession }

constructor TLiteSession.Create(ADataBase: TLiteDb);

begin
inherited Create;
SessionDatabase := ADataBase;
SessionData := TStringList.Create;

end;

procedure TLiteSession.CreateSessionRecord;
var
lSql: string;

begin
//Delete any expired sessions before createing a new one
DeleteExpiredSessions(Now - ExpireInterval);

//********************create random ID number ***************************************

randomize;
SessionID := Random(2000000000);

//*******************Add new session to the database*********************************

lSql :=
  'INSERT INTO sessions(SESSIONID,SESSIONTIMESTAMP,SESSIONDATA)' +
  'VALUES (%d, ''%s'', ''%s'')';

lSql := Format(lSql, [sessionID, FormatDateTime('yyyy-mm-dd hh:mm:ss', now), '']);

SessionDatabase.Query(lSql);
end;

procedure TLiteSession.DeleteExpiredSessions(thetime: tdatetime);

var
lSql: string;

begin
lSql :=

  'DELETE From SESSIONS WHERE (SESSIONTIMESTAMP < %f)';
lSql := Format(lSql, [thetime]);

SessionDatabase.Query(lSql);
end;

destructor TLiteSession.Destroy;

begin
SessionData.Free;
inherited;
end;

function TLiteSession.FindSessionRecord: boolean;

var
lSql: string;

begin
Result := False;

//**************First Check for Expired Sessions********************************
DeleteExpiredSessions(Now - ExpireInterval);

//******************************************************************************

lSql := Format('select SESSIONID, SESSIONTIMESTAMP, SESSIONDATA from SESSIONS Where SESSIONID = %d', [SessionID]);
if SessionDatabase.Query(lSql) then

  if SessionDatabase.RowCount > 0 then
  begin
    SessionId := StrToInt(SessionDatabase.Results[0].Strings[0]);

    ShortDateFormat := 'yyyy-mm-dd';
    DateSeparator := '-';
    SessionTimeStamp:= StrToDateTime(SessionDatabase.Results[0].Strings[1]);

    SessionData.Text := SessionDatabase.Results[0].Strings[2];

    Result := True;
  end;
end;

function TLiteSession.GetValue(const Name: string): string;

begin
result := SessionData.Values[Name];
end;

procedure TLiteSession.SetValue(const Name, Value: string);

var
lSql: string;

begin
SessionData.Values[Name] := Value;

//Save session string list to the database
lSql :=
  'UPDATE SESSIONS SET SESSIONDATA = ''%s'' WHERE SESSIONID = %d';
lSql := Format(lSql, [sessiondata.text, SessionID]);

SessionDatabase.QueryOne(lSql);
end;

end.