Wednesday, October 03, 2007

 

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.

Comments:
Can you write a small tutorial on how to setup, develop and deploy small applications using SQLite ?

that the last dragon i must kill before developing some tools for network admin (actually a small web captive portal with per user bandwidth for FreeBSD)

Thank you in advance!
 
Sure, I'll be glad to write it!.
 
Post a Comment

Links to this post:

Create a Link



<< Home

This page is powered by Blogger. Isn't yours?