tiOPF

Free, Open Source Object Persistence Framework for Free Pascal & Delphi

3. The Visitor Pattern and SQL Databases

The aims of this chapter

In the previous chapter we investigated how the Visitor pattern can be used to perform a family of related tasks on some elements in a list in a generic way. We used the framework to create a small application that saves names and email addresses to either a comma separated value (CSV) file or fixed length text (TXT) file.

This is useful, but most business applications use a relational database to save their data, so in this chapter we will extend the framework to store our objects in an Interbase database.

Prerequisites

It would be best to have read ‘Chapter #2: Implement the Visitor Framework’ first because the concepts introduced in this chapter are building on the ideas discussed in Chapter #2

The business problem we will work with as an example

We will continue with out simplified version of the contact management system introduced in the previous chapter. We shall save a simple list of TPeople objects, with two properties Name and EMailAdrs. The class diagram of our business objects looks like this:

Implementation

Creating the database

Before we can begin, we will need a database to work with. The following SQL script will create an Interbase database with a single table called People. The table shall have two columns Name and EMailAdrs. This script is called SQLVisitor_Intebase_DDL.sql and can be found in the directory with the source code. (Details for downloading the source code are at the end of the chapter.) Note the Create Database and Connect commands have hard coded database name, user name and password. If you have unzipped the source into another directory, or have changed Interbase’s administrator password, you will have to edit the script.

Create Database "C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb"
  user "SYSDBA" password "masterkey" 
connect "C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb"
  user "SYSDBA" password "masterkey" ;

drop table People ;

create table People
   ( OID Integer not null,
   Name VarChar( 20 ),
   EMailAdrs VarChar( 60 ),
   Primary Key ( OID )) 

create unique index People_uk on People ( name ) ;

insert into People values ( 1, "Peter Hinrichsen", "peter_hinrichsen@dontspamme.com");
insert into People values ( 2, "Don Macrae", "don@dontspamme.com" ) ;
insert into People values ( 3, "Malcolm Groves", "malcolm@dontspamme.com" ) ;

commit ;

Creating a database connection

We are going to perform this demonstration using the Interbase Express (IBX) components that come with Delphi. Now, I have been using Interbase for a while now, but have abstracted the database connection code deep into the hierarchy so very seldom have to create a TIBDatabase or TIBQuery manually in code. There are some tricks in wiring these components up with a TIBTransaction that I forget so we will use Delphi’s IDE to help walk us through the process. We will create a form and add the components in Delphi’s form editor then copy them to the clipboard and paste the code that Delphi generates into a PAS file.

I have dropped a TIBDatabase, TIBQuery, TIBTransaction (that’s the one I forget), TDataSource and TDBGrid on a form, wired them up and set the active property to true. We can connect to the database as expected so I will copy the data access controls to the clipboard and paste them into the pas file where we will be creating our SQL visitor. The form to test the data access components is shown below:

If we copy the components at design time onto the clipboard, and paste them into the pas file, we get some text that looks like this:

object IBDatabase1: TIBDatabase
  Connected = True
  DatabaseName = 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb'
  Params.Strings = (
    'user_name=SYSDBA'
    'password=masterkey')
  LoginPrompt = False
  DefaultTransaction = IBTransaction1
  IdleTimer = 0
  SQLDialect = 1
  TraceFlags = []
  Left = 8
  Top = 16
end
object IBTransaction1: TIBTransaction
  Active = True
  DefaultDatabase = IBDatabase1
  Left = 8
  Top = 48
end
object IBQuery1: TIBQuery
  Database = IBDatabase1
  Transaction = IBTransaction1
  Active = True
  CachedUpdates = False
  SQL.Strings = (
    'select * from people')
  Left = 8
  Top = 80
end
 

This is actually the way Delphi stores the components in the form’s DFM file. This technique of combining Delphi’s IDE with coding components by hand was shown to me by Mark Miller in a presentation he made at BorCon 1999 – a very useful technique.

It only takes a couple of minutes work to edit this code from the DFM format into Pascal source. The finished result looks like this:

var
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
  FQuery : TIBQuery ;
  lData : TPerson ;
begin
  FDB := TIBDatabase.Create( nil ) ;
  FDB.DatabaseName := 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb' ;
  FDB.Params.Add( 'user_name=SYSDBA' ) ;
  FDB.Params.Add( 'password=masterkey' ) ;
  FDB.LoginPrompt := False ;
  FDB.Connected := True ;
  FTransaction := TIBTransaction.Create( nil ) ;
  FTransaction.DefaultDatabase := FDB ;
  FDB.DefaultTransaction := FTransaction ;
  FQuery := TIBQuery.Create( nil ) ;
  FQuery.Database := FDB ;
  FQuery.SQL.Text := 'select * from people' ;
  FQuery.Active := True ;
end;

Our first SQL database visitor

We can wrap this code up as a TVisitor class by implementing the database access in the Visitors Execute method. The Interface of our SQL read Visitor looks like this:

TVisSQLRead = class( TVisitor )
protected
  function AcceptVisitor( pVisited : TVisited ) : boolean ; override ;
public
  procedure Execute( pVisited : TVisited ) ; override ;
end ;

We can add some code to scan the query result set, and convert each row to an object and we end up with a big, fat execute method like this:

procedure TVisSQLRead.Execute(pVisited: TVisited);
var
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
  FQuery : TIBQuery ;
  lData : TPerson ;
begin
  if not AcceptVisitor( pVisited ) then
    Exit ; //==>
  FDB := TIBDatabase.Create( nil ) ;
  FDB.DatabaseName := 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb' ;
  FDB.Params.Add( 'user_name=SYSDBA' ) ;
  FDB.Params.Add( 'password=masterkey' ) ;
  FDB.LoginPrompt := False ;
  FDB.Connected := True ;
  FTransaction := TIBTransaction.Create( nil ) ;
  FTransaction.DefaultDatabase := FDB ;
  FDB.DefaultTransaction := FTransaction ;
  FQuery := TIBQuery.Create( nil ) ;
  FQuery.Database := FDB ;
  FQuery.SQL.Text := 'select * from people' ;
  FQuery.Active := True ;
  while not FQuery.EOF do
  begin
    lData := TPerson.Create ;
    lData.Name := FQuery.FieldByName( 'Name' ).AsString ;
    lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ;
    TPeople( pVisited ).Add( lData ) ;
    FQuery.Next ;
  end ;
  FQuery.Free ;
  FTransaction.Free ;
  FDB.Free ;
end;

We can register this Visitor with the Visitor Manager and test it with our experimental application. The result is shown below:

Now that we have got the SQL read Visitor working, we can refactor the code to thin it down, and improve reuse and maintainability.

Move the database connection to an abstract Visitor

The code that creates and frees the TIBDatabase, TIBTransaction and TIBQuery is crying out to be moved to an abstract class. While we are about it, we shall create another pas file to store our abstract SQL visitors and call it tiPtnVisSQL. The interface of our new abstract Visitor called TVisQryAbs looks like this:

TVisSQLAbs = class( TVisitor )
protected
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
  FQuery : TIBQuery ;
  // Implement AcceptVisitor in the concrete class
  // function AcceptVisitor( pVisited : TVisited ) : boolean ; override ;
public
  constructor Create ; override ;
  destructor Destroy ; override ;
  // Implement Execute in the concrete class
  // procedure Execute( pVisited : TVisited ) ; override ;
end ; 

and the constructor and destructors simply contain the code to create and destroy the TIBDatabase, TIBTransaction and TIBQuery as in the previous example.

This means our concrete visitor (the one that maps the SQL result set to objects) has an Execute method that looks like this:

procedure TVisSQLRead.Execute(pVisited: TVisited);
var
  lData : TPerson ;
begin
  if not AcceptVisitor( pVisited ) then
    Exit ; //==>
  FQuery.SQL.Text := 'select * from people' ;
  FQuery.Active := True ;
  while not FQuery.EOF do
  begin
    lData := TPerson.Create ;
    lData.Name := FQuery.FieldByName( 'Name' ).AsString ;
    lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ;
    TPeople( pVisited ).Add( lData ) ;
    FQuery.Next ;
  end ;
end;

Implementing the Template Method pattern

This is starting to look more elegant, however if we have several of these visitors to read different classes from the database, we will be still duplicating lots of code. In the Execute method above, if we where to be reading, say Addresses instead of People, the lines in red and bold would be different, and the lines in blue and italics would be duplicated.

procedure TVisSQLRead.Execute(pVisited: TVisited);
var
  lData : TPerson ; // Different
begin
  if not AcceptVisitor( pVisited ) then // Duplicated
    Exit ; // Duplicated
  FQuery.SQL.Text := 'select * from people' ; // Different
  FQuery.Active := True ; // Duplicated
  while not FQuery.EOF do // Duplicated
  begin // Duplicated
    lData := TPerson.Create ; // Different
    lData.Name := FQuery.FieldByName( 'Name' ).AsString ; // Different
    lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ; // Different
    TPeople( pVisited ).Add( lData ) ; // Different
    FQuery.Next ; // Duplicated
  end ; // Duplicated
end;

What we want to do is find a way of reusing the blue italic code, and only having to retype the red bold code. The solution to this is GoF’s Template Method pattern.

GoF tell us that the intent of the Template Method is:

Define the skeleton of an algorithm in an operation, deferring some steps to client subclasses. Template Method lets subclasses redefine certain steps of an algorithm without changing the algorithm's structure.

This is exactly what we need here. If we look at the red bold code, we can see that the two blocks of code that must be written for each object-database mapping are FQuery.SQL.Text := bla and the code that maps the rows to the SQL data set to an object list. Lets move these to two procedures and implement them in the concrete class. All the code in blue italic will stay in the abstract class.

The interface of the re-factored abstract class, TVisSQLAbs class looks like this:

// Interface of the abstract SQL read visitor
TVisSQLAbs = class( TVisitor )
protected
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
  FQuery : TIBQuery ;
  procedure Init ; virtual ; // Implement in the concrete class
  procedure MapRowToObject ; virtual ; // Implement in the concrete class
  procedure SetupParams ; virtual ; // Implement in the concrete class
public
  constructor Create ; override ;
  destructor Destroy ; override ;
  procedure Execute( pVisited : TVisited ) ; override ;
end ;

And the implementation of the Execute method of TVisSQLAbs looks like this:

// Implementation of the abstract SQL read visitor
procedure TVisSQLAbs.Execute(pVisited: TVisited);
  begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
   
  Init ; // Implemented in the concrete class
   
  SetupParams ; // Implemented in the concrete class
  FQuery.Active := True ;
  while not FQuery.EOF do
  begin
    MapRowToObject ; // Implemented in the concrete class
    FQuery.Next ;
  end ;
end;

The code below shows the interface and implementation of the concrete SQL Visitor TVisSQLReadPeople.

You can see there are four methods to override and implement:

1. AcceptVisitor - is the same as in previous examples – a check to see it the class being visited is of the correct type.
2. Init – is where we set the FQuery.SQL.Text value.
3. SetupParams – we have snuck this method in here and its name says it all. This is where we set any parameters required by the SQL. We can read properties from the object being visited to get the values.
4. MapRowToObject – is where each row of the query result set is turned into an object and added to the list contained in the class being visited.

The UML of the class hierarchy we have implemented so far looks like this:

Implementing the concrete SQL read visitor

Now that we have created an abstract SQL Visitor, we can implement a SQL Visitor to read people. The interface of TVisSQLReadPeople is shown below:

TVisSQLReadPeople = class( TVisSQLAbs )
protected
  function AcceptVisitor : boolean ; override ;
  procedure Init; override ;
  procedure MapRowToObject ; override ;
  procedure SetupParams ; override ;
end ;

And TVisSQLReadPeople’s implementation is shown here. Note that we have overridden the four methods (AcceptVisitor, Init, MapRowToObject and SetupParams) required by the template we setup in the parent class.

// AccetpVisitor is the same as in previous examples
function TVisSQLReadPeople.AcceptVisitor : boolean;
begin
  result := Visited is TPeople ;
end;

// Init is a new method where we can set the Query.SQL.Text value, or 
// perform other setup tasks
procedure TVisSQLReadPeople.Init;
begin
  FQuery.SQL.Text := 'select * from people' ;
  TPeople( Visited ).List.Clear ;
end;

// SetupParams is where we set and parameters on the SQL. The parameter values can 
// be read from the properties of the object being visited
procedure TVisSQLReadPeople.SetupParams;
begin
  // Do nothing yet, we will implement this in a future example
end;

// MapRowToObject is where the SQL result set rows are turned into objects and is called
// once for each row returend by the SQL result set.
procedure TVisSQLReadPeople.MapRowToObject;
var
  lData : TPerson ;
begin
  lData := TPerson.Create ;
  lData.Name := FQuery.FieldByName( 'Name' ).AsString ;
  lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ;
  TPeople( Visited ).Add( lData ) ;
end;

The UML or the class hierarchy we have build so far looks like this:

The Visitor is registered with the Visitor Manager as usual, and then executed by passing a call to the visitor manager in the same way as for the text file visitors we build in the previous chapter. This is shown below:

// Registering TVisSQSLReadPeople is the same as usual
initialization
  gVisitorMgr.RegisterVisitor( 'SQLRead', TVisSQLReadPeople ) ;
end.

// And calling TVisSQLReadPeople is the same as usual
begin
  gVisitorMgr.Execute( 'SQLRead', FPeople ) ;
end;

We now have the beginnings of a system that will let us read data from one format, and save it to another. For example, the code below will read TPerson(s) from a SQL database, and save them to a CSV file:

// Register the SQLRead Visitor and CSVSave Visitors
initialization
  gVisitorMgr.RegisterVisitor( 'CSVSave', TVisCSVSave ) ;
  gVisitorMgr.RegisterVisitor( 'SQLRead', TVisSQLReadPeople ) ;
end.

// Read from the SQL database, and save to a CSV file is now easy
var
  lPeople : TPeople ;
begin
  lPeople := TPeople.Create ;
  try
    gVisitorMgr.Execute( 'SQLRead', lPeople ) ;
    gVisitorMgr.Execute( 'CSVSave', lPeople ) ;
  finally
    lPeople.Free;
  end ;
end ;

As you can imagine, it is possible to register any number of Visitors to read and write from different file formats, or database types. In the next section, we will expand the Visitor framework to save objects (as well as read) to a SQL database.

Refactor the SQLVisitor hierarchy for read and update (create, update, delete) SQL

This is where things become a little trickier. When we where creating our CSV file save visitor, it was easy because we just wrote our all the objects in the list. We can’t do this with an SQL database because some of the object may already exist in the database, and attempting to save them again would cause duplicate data and possibly database key violations. We could delete all the records before saving them all back like we do when saving to the CSV file, but writing to a text files is very quick, while interacting with an SQL database can be quite slow. We want to cause as little communication with the database as possible and will write an update Visitor to achieve this goal.

We will start by writing a Visitor to save changed objects (calling UPDATE SQL), then introduce the concept of an ObjectState property which will let the framework determine whether it has to run CREATE, UPDATE or DELETE SQL.

The steps we found are necessary when reading objects from a SQL database are:

Task	Method name
Should we be visiting this object?	AcceptVisitor
Setup the Query, assign the SQL	Init
Set any parameters required by the SQL	SetupParams
Turn the SQL result set into objects	MapRowToObject

Calling a SQL UPDATE statement requires pretty much the same steps, except MapRowToObject is not necessary. We really need a Map-Object-To-Row method, however this can be taken care of in SetupParams. We can now refactor our abstract SQL Visitor so the database connection and query remain in the abstract class, and we introduce two more classes at the next level down; one for reading objects by calling SELECT SQL (TVisSQLReadAbs). And one for saving them (TVisSQLUpdateAbs) by running UPDATE, CREATE or DELETE SQL. The execute methods for each will have to be slightly different too. The UML of the modified SQL Visitor hierarchy is shown below:

The interface of the abstract SQL visitor is shown below. We have introduced the Database connection, Query as well as the Init and SetupParams methods.

// The abstract SQL visitor contains the database connection, query
// Init and SetupParams methods
TVisSQLAbs = class( TVisitor )
protected
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
  FQuery : TIBQuery ;
  procedure Init ; virtual ;
  procedure SetupParams ; virtual ;
public
  constructor Create ; override ;
  destructor Destroy ; override ;
end ;

The interface of the abstract SQL read visitor is shown next. We have added the MapRowToObject method an have implemented Execute as shown beloe:

// The abstract SQL read visitor adds the MapRowToObject method, and
// implements the calls to Init, SetupParams and MapRowToObject
// (and FQuery.Open)in the correct order
TVisSQLReadAbs = class( TVisSQLAbs )
  protected
    procedure MapRowToObject ; virtual ;
  public
    procedure Execute( pVisited : TVisited ) ; override ;
  end ;

procedure TVisSQLReadAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.Active := True ;
  while not FQuery.EOF do
  begin
    MapRowToObject ; // Map a query row to an object. Implemented in the concrete class
    FQuery.Next ;
  end ;
end;

The interface of the SQL update visitor has no additional methods and only contains the overridden Execute procedure.

// The abstract SQL update visitor just has an execute method and implements
// calls to Init and SetupParams (and FQueyr.SQLExec)
TVisSQLUpdateAbs = class( TVisSQLAbs )
public
  procedure Execute( pVisited : TVisited ) ; override ;
end ;

And the implementation of the abstract SQL update Visitor’s Execute method looks like this:

procedure TVisSQLUpdateAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.ExecSQL ;
end;

Now that we have refactored the abstract SQL read and update visitors, it is a simple matter to write our first concrete Visitor to update objects to a SQL database.

Write an UPDATE Visitor

We now have a stub of an object that will walk us through the steps we have to take to update a changed object in the database. We have to implement the Init method, where we will write some SQL, and the SetupParams method where we will map the object’s properties to the query’s parameters. We will start by writing some update SQL like this:

update People
set
    Name = :Name
   ,EMailAdrs = :EMailAdrs
where
    Name = :Old_Name
and EMailAdrs = :Old_EMailAdrs

This SQL is the same as we get when we drop a TQuery on a form and hook it up to a TUpdateSQL. Now if we use a TQuery TClientDataSet on a for with a TDBGrid, it will internally maintain a list of changed rows, keeping a copy of the both the old and new values. This is where the Old_Name and Old_EmailAdrs values where derived from. Our framework as it stands has no knowledge of how an object looked before it was edited. We have taken over this responsibility from the TQuery and It’s about now in our implementation of an OPF that we realise how cleaver the TQuery and TClientDataset components really are. This is the point when we can quite rightly reassess whether or not it is worth our while developing a custom persistence framework. (I still maintain that it’s well worth the effort.)

The solution to our problem is contained in a paper by Scott Ambler found at http://www.ambysoft.com/mappingObjects.pdf. The discussion starts off on page one with the heading ‘The importance of OIDs’. In a relational database, a row in a table can be uniquely identified by its primary key fields. In our People table, this field would probably be Name. That’s fine, except when we want to join the People table to another table with a foreign key relationship. We can use the name field as the link between the two tables that will work well until we want to change a value in Name. How do we do that? The databases reverential integrity will make this very difficult. The solution (and Ambler spends considerable time driving this point home) is to make sure your primary key fields have no business meaning. Integer is the perfect candidate data type for OID’s and is what we will use here. (Ambler describes several strategies to generating OIDs including High/Low integers, which is what is used in the tiOPF, GUIDS and others – his paper is well worth a read.)

We now have several changes to make to our earlier work:

1. The table structure must be extended with the OID column, and the data populated with OID values.
2. Our TPerson class must be extended with an OID property
3. The read visitor must be extended to read an object’s OID

We will change the script to create our test table to look like this:

create table People
( OID Integer not null,
  Name VarChar( 20 ),
  EMailAdrs VarChar( 60 ),
  Primary Key ( OID )) ;

insert into People values ( 1, "Peter Hinrichsen", "peter_hinrichsen@dontspamme.com");
insert into People values ( 2, "Don Macrae", "don@dontspamme.com" ) ;
insert into People values ( 3, "Malcolm Groves", "malcolm@dontspamme.com" ) ;

While we are adding the OID column, we will take the opportunity to make a NOT NULL field, and set it as the table’s primary key.

Next, our TPerson class must be extended with an OID property. At this stage in the development of the framework I decided that all objects would have a property called OID of type Int64. That’s has been a good rule to enforce when building a system from scratch, with complete control over the database schema. However if we where building an OO front end to a legacy system, we would have to come up with a more versatile OID/Primary key strategy. As all objects will have an OID, lets add it to the abstract business object class. We will define a type called TOID that will be an Int64. This will make it easier to change the implementation of TOID down the track. Our re-factored TPerObjAbs class looks like this:

TOID = Int64 ;
TPerObjAbs = class( TVisited )
private
  FOID: TOID;
public
  constructor Create ; virtual ;
  property OID : TOID read FOID write FOID ;
end;

And the changed TVisSQLReadPeople looks like this:

procedure TVisSQLReadPeople.MapRowToObject;
var
  lData : TPerson ;
begin
  lData := TPerson.Create ;
  lData.OID := FQuery.FieldByName( 'OID' ).AsInteger ;
  lData.Name := FQuery.FieldByName( 'Name' ).AsString ;
  lData.EMailAdrs := FQuery.FieldByName( 'EMailAdrs' ).AsString ;
  TPeople( Visited ).Add( lData ) ;
end;

I have also refactored the CSV and TXT file visitors, but have not shown the code for these here.

Now, back to where we where 20 minutes ago, we can write our UPDATE SQL using the OID value to find the record to update.

update People
set
   Name = :Name
  ,EMailAdrs = :EMailAdrs
where
   OID = :OID

It is now possible to finish the TVisSQLUpdatePeople class with the implementation looking like this:

function TVisSQLUpdatePeople.AcceptVisitor: boolean;
begin
  result := Visited is TPerson ;
end;

procedure TVisSQLUpdatePeople.Init;
begin
  FQuery.SQL.Text :=
    'update People ' +
    'set ' +
    ' Name = :Name ' +
    ' ,EMailAdrs = :EMailAdrs ' +
    'where ' +
    ' OID = :OID' ;
end;

procedure TVisSQLUpdatePeople.SetupParams;
var
  lData : TPerson ;
begin
  lData := TPerson( Visited ) ;
  FQuery.Params.ParamByName( 'OID' ).AsInteger := lData.OID ;
  FQuery.Params.ParamByName( 'Name' ).AsString := lData.Name ;
  FQuery.Params.ParamByName( 'EMailAdrs' ).AsString := lData.EMailAdrs ;
end;

And of course don’t forget to register TVisSQLUpdatePeople with the Visitor Manager in the unit’s implementation section like this:

// Register the SQLSave Visitor
initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLUpdatePeople ) ;
end.

The need for an ObjectState property

It should now be easy to write a CREATE visitor. It can potentially share the same SetupParams code as the UPDATE visitor, with modified SQL. The implementation of the new Init method is shown below:

procedure TVisSQLCreatePeople.Init;
begin
   FQuery.SQL.Text :=
   'insert into People ' +
   '( OID, Name, EMailAdrs ) ' +
   'values ' +
   '( :OID, :Name, :EMailAdrs ) ' ;
end;

// Register the SQLSave Visitor
initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLUpdatePeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLCreatePeople ) ;
end.

We register the new Visitor with the Visitor Manager, compile and run the code then test by inserting an new value and…

…another problem. Primary key violation on table People. Well, we only have one primary key field and that’s OID. This problem is being caused because we are calling our CREATE Visitor for every object in the list, not just those that that must be newly created. We need some way of identifying an object’s state: Is it clean, does it need creating, updating or deleting? This can be done by introducing an ObjectState property at the level of TPerObjAbs.

In the tiOPF, ObjectState is an ordinal type and can have the following values:

Value	Description
posEmpty	The object has been created, but not filled with data from the DB
posPK	The object has been created, but only it's primary key has been read
posCreate	The object has been created and populated with data and must be saved to the DB
posUpdate	The object has been changed, the DB must be updated
posDelete	The object has been deleted, it must be deleted from the DB
posDeleted	The object was marked for deletion, and has been deleted in the database
posClean	The object is 'Clean' no DB update necessary

The TPerObjAbsState type is declared like this:

TPerObjectState = (
   posEmpty, posPK, posCreate, posUpdate, posDelete, posDeleted, posClean ) ;

What we are aiming to do is to be able to selectively run visitors depending on whether an object needs to be newly created, updated or deleted from the database. For example, we would like to achieve the same as this case statement, only within the Visitor framework:

case Visited.ObjectState of
posCreate : // Call CREATE SQL
posUpdate : // Call UPDATE SQL
posDelete : // Call DELETE SQL
else
  // Do nothing
end ;

This can be achieved by checking Visited.ObjectState inside the Visitors AcceptVisitor method.

Now we have several things to do to finish off the work with ObjectState:

1. et ObjectState when we create, update or delete an object in the GUI;
2. ilter out objects that have been marked as posDelete or posDeleted from the GUI;
3. heck the ObjectState property in AcceptVisitor; and
4. et the ObjectState back to posClean after a Visitor has run.

Setting ObjectState when we create, update or delete an object in the GUI is a chore but can be made easier by introducing two new properties on TPerObjAbs: Deleted and Dirty. The interface of TPerObjAbs is extended as shown below:

TPerObjAbs = class( TVisited )
private
  FObjectState: TPerObjectState;
protected
  function GetDirty: boolean; virtual ;
  procedure SetDirty(const Value: boolean);virtual ;
  function GetDeleted: boolean;virtual ;
  procedure SetDeleted(const Value: boolean);virtual ;
public
  property ObjectState : TPerObjectState read FObjectState write FObjectState ;
  property Dirty : boolean read GetDirty write SetDirty ;
  property Deleted : boolean read GetDeleted write SetDeleted ;
end ;

The implementation of SetDirty and GetDirty is shown below. Set dirty will change the object state to posUpdate from posEmpty, posPK or posClean, and will not change the objec state if the object is already in one of the ‘Dirty’ states.

procedure TPerObjAbs.SetDirty(const Value: boolean);
begin
  case ObjectState of
  posEmpty : FObjectState := posCreate ;
  posPK : FObjectState := posUpdate ;
  posCreate : FObjectState := FObjectState ; // Do nothing
  posUpdate : FObjectState := FObjectState ; // Do nothing
  posDelete : FObjectState := FObjectState ; // Do nothing
  posDeleted : FObjectState := FObjectState ; // Do nothing
  posClean : FObjectState := posUpdate ;
  end ;
end;

GetDirty checks for an ObjectState of posCreate, posUpdate or posDelete. PosEmpty, posPK, posClean and posDeleted are not regarded as dirty state that mean the object has changed and must be saved back to the database.

function TPerObjAbs.GetDirty: boolean;
begin
  result := ObjectState in [posCreate, posUpdate, posDelete] ;
end;

And the implementation of the Deleted Get and Set methods looks like this:

function TPerObjAbs.GetDeleted: boolean;
begin
  result := ObjectState in [posDelete, posDeleted];
end;

As you can see, GetDeleted will return True if ObjectState is either posDelete or posDeleted.

procedure TPerObjAbs.SetDeleted(const Value: boolean);
begin
  if ObjectState <> posDeleted then
    ObjectState := posDelete ;
end;

We can now use ObjectState, and the two helper properties Deleted and Dirty to filter objects by state when saving to the database.

Insert a new TPerson in the GUI

Our ultimate aim in the next few pages is to write a SQL Create Visitor, but before we can do that, we must insert some new objects into the list for saving to the database. We shall do this by writing extending the edit dialog we developed in the previous chapter.

The TtiListView on the main form has three event handlers: OnItemInsert, OnItemEdit and OnItemDelete. These are fired when the list view is double clicked, or right clicked and Insert, Edit or Delete are selected from the popup context menu. The code under OnItemInsert looks like this:

procedure TFormMain_VisitorManager.LVItemInsert(
  pLV: TtiCustomListView;
  pData: TPersistent;
  pItem: TListItem);
var
  lData : TPerson ;
begin
  lData := TPerson.Create ;
  lData.ObjectState := posCreate ; // Set the ObjectState property here
  lData.OID := GetNewOID ; // A helper function that generates a new OID
  FPeople.Add( lData ) ;
  TFormEditPerson.Execute( lData ) ;
end;

The call to TformEditPerson.Execute( lData ) executes the following code, and shows the dialog box as in the screen shot below:

class procedure TFormEditPerson.Execute(pData: TPerObjAbs);
var
  lForm : TFormEditPerson ;
begin
  lForm := TFormEditPerson.Create( nil ) ;
  try
    lForm.Data := pData ;
    lForm.ShowModal ;
  finally
    lForm.Free ;
  end ;
end;

Under the OK button of this dialog we set the TPerson’s Dirty property to true like this:

procedure TFormEditPerson.btnOKClick(Sender: TObject);
begin
  FData.Dirty := true ;
  ModalResult := mrOK ;
end;

Having to remember to set Dirty := true after an edit is a chore, and is error prone. The need to do this could be removed by setting Dirty := true in each properties Set method, but there would need to be some code to turn this feature off while populating an object from the persistent store.

Another solution is to use visual form inheritance and create an abstract edit dialog that takes care of setting pData.Dirty := True under the OK button. This is how we implement edit dialogs in the tiOPF and shall be discussed later.

Now that we have set the ObjectState property after inserting a new object, we can write the Create Visitor.

Write a CREATE Visitor

The first thing we must do in the Create Visitor is check the ObjectState of the object being processed in the AcceptVisitor method. This will make it possible to run CREATE SQL against only the objects that have been newly created, and UPDATE SQL against objects that already exist in the database, and must be updated.

An example of this is shown below:

function TVisSQLCreatePeople.AcceptVisitor: boolean;
begin
  result := ( Visited is TPerson ) and
    ( TPerson( Visited ).ObjectState = posCreate ) ;
end;

The full interface of the Create Visitor is shown below:

Interface
TVisSQLCreatePeople = class( TVisSQLUpdateAbs )
protected
  function AcceptVisitor : boolean ; override ;
  procedure Init ; override ;
  procedure SetupParams ; override ;
end ;

And the implementation of the Create Visitor is shown next:

implementation
function TVisSQLCreatePeople.AcceptVisitor: boolean;
begin
  result := ( Visited is TPerson ) and
    ( TPerson( Visited ).ObjectState = posCreate ) ;
end;

procedure TVisSQLCreatePeople.Init;
begin
  FQuery.SQL.Text :=
    'insert into People ' +
    '( OID, Name, EMailAdrs ) ' +
    'values ' +
    '( :OID, :Name, :EMailAdrs ) ' ;
end;

procedure TVisSQLCreatePeople.SetupParams;
var
  lData : TPerson ;
begin
  lData := TPerson( Visited ) ;
  FQuery.Params.ParamByName( 'OID' ).AsInteger := lData.OID ;
  FQuery.Params.ParamByName( 'Name' ).AsString := lData.Name ;
  FQuery.Params.ParamByName( 'EMailAdrs' ).AsString := lData.EMailAdrs ;
end;

This Visitor is registered with the Visitor Manager in the usual way:

initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLCreatePeople ) ;
end.

Write a DELETE Visitor

Now that we have worked out how to determine which Visitors to call when, we can write our DELETE visitors. Their interface will be the same as the CREATE and UPDATE Visitors because they descend from the same parent (and use the Template Method pattern) and looks like this:

Interface
TVisSQLUpdatePeople = class( TVisSQLUpdateAbs )
protected
  function AcceptVisitor : boolean ; override ;
  procedure Init ; override ;
  procedure SetupParams ; override ;
end ;

The implementation of TVisSQLDeletePeople looks like this:

implementation
function TVisSQLDeletePeople.AcceptVisitor: boolean;
begin
  result := ( Visited is TPerson ) and
  ( TPerson( Visited ).ObjectState = posDelete ) ;
end;

procedure TVisSQLDeletePeople.Init;
begin
  FQuery.SQL.Text :=
  'delete from People ' +
  'where ' +
  'OID = :OID' ;
end;

procedure TVisSQLDeletePeople.SetupParams;
var
  lData : TPerson ;
begin
  lData := TPerson( Visited ) ;
  FQuery.Params.ParamByName( 'OID' ).AsInteger := lData.OID ;
end;

And once again, the Visitor is registered with the Visitor Manager in the usual way:

initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLDeletePeople ) ;
end.

Registering Visitors in the correct order

It is important to register the Visitors with the Visitor Manager in the correct order. This is especially important if a tree hierarchy has been modeled and is represented in the database by a one to many relationship with database referential integrity. The order that visitors are registered is the order that the SQL is called and I find that the most reliable is to register them in the order of Read, Delete, Update then Create. This is shown below:

initialization
  gVisitorMgr.RegisterVisitor( 'SQLRead', TVisSQLReadPeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLDeletePeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLUpdatePeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLCreatePeople ) ;
end.

Setting ObjectState back to posClean

Now that we have checked the ObjectState property for posCreate, posDelete or posUpdate in the Visitor’s AcceptVisitor method and run the SQL in the Visitor we must set ObjectState back to posClean. To do this we will add an extra class between TVisitor and TVisSQLAbs called TVisPerObjectAwareAbs. This will let us descend our text file visitors from the same parent as the SQL visitor giving them both access to the new method called Final. The interface and implementation of TVisPerObjectAwareAbs looks like this:

interface
  TVisPerObjAwareAbs = class( TVisitor )
  protected
    procedure Final ; virtual ;
  end ;

implementation
procedure TVisPerObjAwareAbs.Final;
begin
  if TPerObjAbs( Visited ).ObjectState = posDelete then
    TPerObjAbs( Visited ).ObjectState := posDeleted
  else
    TPerObjAbs( Visited ).ObjectState := posClean ;
end;

We can now call Final in the Execute method of both the Read and Update Visitors. The Execute method of TVisSQLReadAbs now looks like this:

procedure TVisSQLReadAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.Active := True ;
  while not FQuery.EOF do
  begin
    MapRowToObject ; // Map a query row to an object. Implemented in the concrete class
    FQuery.Next ;
  end ;
  Final ;
end;

And the execute method of TVisSQLUpdateAbs looks like this:

procedure TVisSQLUpdateAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.ExecSQL ;
  Final ;
end;

The UML of the SQL visitor class hierarchy now looks like this:

Filtering in the GUI

Our objects can now have two states that should prevent them from being displayed in the GUI: posDelete (meaning they have been marked for deletion, but have not yet been deleted from the database) and posDeleted (meaning they have been marked for deletion, and removed from the database). If either of these conditions is true, the Deleted property will return true. If we check the deleted property while painting the TtiListView, we can filter the records we don’t want to display. The TtiListView has an OnFilterRecord event that can be programmed like this:

procedure TFormMain_VisitorManager.LVFilterData(
   const pData: TPersistent;
   var   pbInclude: Boolean);
begin
  pbInclude := not TPerObjAbs( pData ).Deleted ;
end;

When the TtiListView’s ApplyFilter property is set to True, the objects that have an ObjectState of posDelete or posDeleted will be filtered out and not displayed.

Add some logging to help debugging

It does not take much time debugging this code before you will realise how difficult it can be to keep track of what is going on. The business object model is decoupled from the persistence layer, but with the continual looping within the TVisited.Iterate method, tracking errors, especially in the SQL can be quite a torturous process. The solution is to add some logging and to help with this and have developed the TtiLog family of classes.

To add logging to the application, add the unit tiLog.pas to the tiPtnVisSQL.pas uses clause, then add the Log( ) command in the Visitor’s execute method like this:

procedure TVisSQLReadAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
  Log( 'Calling ' + ClassName + '.Execute' ) ;
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.Active := True ;
  while not FQuery.EOF do
  begin
    MapRowToObject ; // Map a query row to an object. Implemented in the concrete class
    FQuery.Next ;
  end ;
  Final ;
end;

and this...

procedure TVisSQLUpdateAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==>
  Log( 'Calling ' + ClassName + '.Execute' ) ;
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.ExecSQL ;
  Final ;
end;

It is also a good idea to add logging to AcceptVisitor, SetupParams, MapRowToObject and Final with each call that is likely to call an exception being surrounded by a try except block. This makes it easier to locate the source of an error by reading the log trace, which is much quicker than having to step through the code in the IDE.

For example, the TVisSQLUpdateAbs.Execute method is refactored like this in its implementation in the tiOPF:

procedure TVisQryUpdate.Execute( pData: TVisitedAbs);
  procedure DoExecuteQuery ;
  begin
    try
      Query.ExecSQL ;
    except
      on e:exception do
        tiFmtException( e, ClassName, '_ExecuteQuery',
          DBExceptionMessage( 'Error opening query', e )) ;
    end ;
 end ;
begin
  Inherited Execute( pData ) ;
  if not DoAcceptVisitor then
    exit ; //==>
  DoInit ;
  DoGetQuery ;
  DoSetupParams ;
  DoExecuteQuery ;
end ;

To get logging working you also have to call SetupLogForClient somewhere in the application and the DPR file is as good a place as any. To turn visual logging on, you must pass the –lv parameter on the command line. An example of how to use the tiLog classes can be found in the DemoTILog directory. An application running with visual logging turned on will typically look like this:

Change the CSV and TXT visitors to ignore deleted objects

When we first wrote the TVisCSVSave and TVisTXTSave classes, we assumed we wanted to save all the objects in the list that was passed to the visitor manager. This was a good strategy for persisting to a text file, but as we discussed above, when saving to a SQL database, we must maintain a list of objects that are marked for deletion so they can be removed from the database as chapter of the save.

In the main form, under the delete button we had the following code that removes then frees the object from the list:

procedure TFormMain_VisitorManager.LVItemDelete(
   pLV: TtiCustomListView; pData: TPersistent; pItem: TListItem);
begin
  FPeople.List.Remove( pData ) ;
end;

For saving to a SQL database, this has been changed to marking the object for deletion, rather than removing it from the list.

procedure TFormMain_VisitorManager.LVItemDelete(
   pLV: TtiCustomListView; pData: TPersistent; pItem: TListItem);
begin
  TPerObjAbs( pData ).Deleted := true ;
end;

The AcceptVisitor method in TVisCSVSave and TVisTXTSave must be extended to skip over records that have been deleted, or marked for deletion:

function TVisCSVSave.AcceptVisitor : boolean;
begin
  result := ( Visited is TPerson ) and 
    ( not TPerson( Visited ).Deleted ) ;
end;

Enable the save button in the GUI only when the object hierarchy is dirty

The way we have designed the application’s main form has the save buttons enabled all the time. It would be nice if we could disable the save button when the data in the hierarchy is clean and enable the buttons only when a CREATE, UPDATE or DELETE must be made.

We have added a Dirty property to the TPerObjAbs class, but this only checks the one classes ObjectState. What we need is a way of iterating over all owned objects and to check if any of them are dirty. This is easily achieved by writing an IsDirty Visitor and calling it inside the object’s GetDirty method. The interface of TVisPerObjIsDirty looks like this:

TVisPerObjIsDirty = class( TVisitorAbs )
private
 FbDirty: boolean;
protected
  function AcceptVisitor : boolean ; override ;
public
  procedure Execute( pVisited : TVisitedAbs ) ; override ;
  property Dirty : boolean read FbDirty write FbDirty ;
end ;

And the implementation looks like this:

function TVisPerObjIsDirty.AcceptVisitor : boolean;
begin
  result := ( Visited is TPerObjAbs ) and ( not Dirty ) ;
end;

procedure TVisPerObjIsDirty.Execute(pVisited: TVisitedAbs);
begin
  Inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    exit ; //==>
  Dirty := TPerObjAbs( pVisited ).ObjectState in
   [ posCreate,
   posUpdate,
   posDelete
   ]
end;

The call to this Visitor is wrapped up in the TPerObjAbs.GetDirty method like this:

function TPerObjAbs.GetDirty: boolean;
var
  lVis : TVisPerObjIsDirty ;
begin
  lVis := TVisPerObjIsDirty.Create ;
  try
    self.Iterate( lVis ) ;
    result := lVis.Dirty ;
  finally
    lVis.Free ;
  end ;
end;

This lets us extend the application's main form by adding an ActionList. Double click the ActionList and add three actions as shown below. Move the code from the three save buttons OnClick methods to the actions then hook the save buttons up to the actions.

In the ActionList's OnUpdate method add the following code to check the Dirty state of the data hierarchy and enable or disable the save buttons as necessary:

procedure TFormMain_VisitorManager.ALUpdate(Action: TBasicAction;
  var Handled: Boolean);
var
  lDirty : boolean ;
begin
  lDirty := FPeople.Dirty ;
  aSaveToInterbase.Enabled := lDirty ;
  aSaveToCSV.Enabled := lDirty ;
  aSaveToTXT.Enabled := lDirty ;
  Handled := true ;
end;

Now the save buttons are disabled when the object hierarchy is clean, and disabled when the object hierarchy is dirty like this:

The object Hierarchy is clean	The Object Hierarchy is dirty

Adding more database constraints

Now let’s create a slightly more realistic database schema by adding a unique key on the People table. This can be done by modifying the create SQL as shown below:

create table People
   ( OID Integer not null,
   Name VarChar( 20 ),
   EMailAdrs VarChar( 60 ),
   Primary Key ( OID )) ;

create unique index People_uk on People ( name, EMailAdrs ) ;

insert into People values ( 1, "Peter Hinrichsen", "peter_hinrichsen@dontspamme.com");
insert into People values ( 2, "Don Macrae", "don@dontspamme.com" ) ;
insert into People values ( 3, "Malcolm Groves", "malcolm@dontspamme.com" ) ;

Run the application and deliberately try to insert duplicate name records to see how the framework handles a database error. Add two duplicate records then click ‘Save to Interbase’. You will get unique key error like the one shown below:

Click the ‘Read from Interbase’ button and you will find that one record was saved, while the other was not. The record that was not saved is still in the client with an ObjectState of posCreate. What we clearly want here is some transaction management so either all objects are saved, or none are saved. Before we can setup transaction management, we must modify the framework so all the visitors share the same database connection.

Share the database connection between Visitors

The First step towards providing transaction support is to change the framework so all calls to the database are funneled through the same database connection and transaction object. You will remember that the TVisSQLAbs class owned an instance of a TIBDatabase and TIBTransaction. This meant that every visitor was processed within its own database connection and transaction. Transactions would be implicitly started and committed by Delphi around each SQL statement. What we want is for all visitors to share the same database connection, and for the Visitor Manager to have control over the database transaction.

To achieve this, we will do four things:

1. Move the database and transaction objects out of the TVisSQLAbs class and wrap them up in an object we will call TtiDatabase. (This will become especially useful when we start work on building a swappable persistence layer. Also, one of the slowest things you can do to a database is connect to it, so sharing a database connection between visitor will improve the application’s performance.)
2. Modify the TVisSQLAbs class with a Database property. The SetDatabase method will assign a field variable for later reference, and hook the Query object up to the database connection at the same time.
3. Create a single instance (Singleton pattern) of the database object that has application wide visibility.
4. Modify the Visitor Manager so it sets each Visitors Database property before executing, and then clears it when done.

These four steps are detailed below.

Firstly, we will move the database and transaction objects into their own class. The interface of TtiDatabase is shown here:

TtiDatabase = class( TObject )
private
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
public
  constructor Create ;
  destructor Destroy ; override ;
  property DB : TIBDatabase read FDB ;
end ;

And the implementation of TtiDatabase is shown here:

constructor TtiDatabase.Create;
begin
  inherited ;
  FDB := TIBDatabase.Create( nil ) ;
  FDB.DatabaseName := 'C:\TechInsite\OPFPresentation\Source\3_SQLVisitors\Test.gdb' ;
  FDB.Params.Add( 'user_name=SYSDBA' ) ;
  FDB.Params.Add( 'password=masterkey' ) ;
  FDB.LoginPrompt := False ;
  FDB.Connected := True ;
  FTransaction := TIBTransaction.Create( nil ) ;
  FTransaction.DefaultDatabase := FDB ;
  FDB.DefaultTransaction := FTransaction ;
end;

destructor TtiDatabase.Destroy;
begin
  FTransaction.Free ;
  FDB.Free ;
  inherited;
end;

As well as moving the database connection out of the Visitor class, and allowing it to be shared between all visitors in the application, we have wrapped the TIBDatabase component in our own code. This is an important step towards using the Adaptor pattern to make our framework independent of any one-database vendor’s API.

Next, we modify the TVisSQLAbs class. The owned database and transaction objects are removed and a field variable is added to hold a pointer to the shared database object. A database property with a SetDatabase method is added and SetDatabase is responsible for hooking the query object up to the database connection. The interface of TVisSQLAbs is shown below:

TVisSQLAbs = class( TVisPerObjAwareAbs )
private
  FDatabase: TtiDatabase;
  procedure SetDatabase(const Value: TtiDatabase);
public
  constructor Create ; override ;
  destructor Destroy ; override ;
  property Database : TtiDatabase read FDatabase write SetDatabase ;
end ;

The implementation of TVisSQLAbs.SetDatabase is shown below. Notice how there is protection against Value being passed as nil.

procedure TVisSQLAbs.SetDatabase(const Value: TtiDatabase);
begin
  FDatabase := Value;
  if FDatabase <> nil then
    FQuery.Database := FDatabase.DB
  else
   FQuery.Database := nil ;
end;

Thirdly we require a globally visible, single instance of the database connection. (This is not how we will ultimately implement a database connection – we will use a thread safe database connection pool, but this is sufficient to get us started.) I use something I call a poor man’s Singleton, a unit wide variable hiding behind a globally visible function. This implementation does not come close to providing what a GoF singleton requires, but it does the job and is quick to implement. The code for the single instance database connection is shown below:

interface
function gDBConnection : TtiDatabase ;
implementation
var
  uDBConnection : TtiDatabase ;
function gDBConnection : TtiDatabase ;
begin
  if uDBConnection = nil then
    uDBConnection := TtiDatabase.Create ;
  result := uDBConnection ;
end ;

The final changes we must make are to extend TVisitorMgr.Execute with some code to set the database property on the visitor before calling TVisitor.Execute. There is a complication here because not all Visitors will have a database property, only those that descend from TVisSQLAbs. As a work around we will check the type of the Visitor, and if it descends from TVisSQLAbs, assume it has a database property and hook it up to the default application database object. We also clear the Visitors database property after the visitor has executed. (This is a bit of a hack and is not how we solve the problem in the framework. We actually have another class called a TVisitorController that is responsible for performing tasks before and after visitors execute. Much more elegant, but rather more complex too.) The modified TVisitorMgr.Execute method is shown below:

procedure TVisitorMgr.Execute(const pCommand: string; const pData: TVisited);
var
  i : integer ;
  lVisitor : TVisitor ;
begin
  for i := 0 to FList.Count - 1 do
    if SameText( pCommand, TVisitorMapping( FList.Items[i] ).Command ) then
    begin
      lVisitor := TVisitorMapping( FList.Items[i] ).VisitorClass.Create ;
      try
        if ( lVisitor is TVisSQLAbs ) then
          TVisSQLAbs( lVisitor ).Database := gDBConnection ;
        pData.Iterate( lVisitor ) ;
        if ( lVisitor is TVisSQLAbs ) then
          TVisSQLAbs( lVisitor ).Database := nil ;
      finally
        lVisitor.Free ;
      end ;
  end ;
end;

The changes we have made to this section ensure that all database activity is channeled through a single database connection. The next step is to modify TVisitorMgr.Execute so all SQL statements are called within a single database transaction.

Manage transactions

To add transaction support, we must to two things:

1. 1. Expose transaction support through the procedures StartTransaction, Commit and RollBack on the database wrapper class TtiDatabase
2. 2. Extend the Visitor Manager’s Execute method with the ability to start a transaction when a group of objects is passed to Execute and commit or roll back the transaction when processing has either finished successfully, or failed.

Firstly, we extended interface of TtiDatabase, with the additional methods StartTransaction, Commit and RollBack as in the code below:

TtiDatabase = class( TObject )
private
  FDB : TIBDatabase ;
  FTransaction : TIBTransaction ;
public
  constructor Create ;
  destructor Destroy ; override ;
  property DB : TIBDatabase read FDB ;
  procedure StartTransaction ;
  procedure Commit ;
  procedure Rollback ;
end ;
 

The implementation of TtiDatabase is shown next. You can see that the StartTransaction, Commit and RollBack methods simply delegate the call to the owned TIBTransaction object. The reasons for this shall be looked at in more detail when we study swappable persistence layers and the Adaptor pattern.

procedure TtiDatabase.StartTransaction;
begin
  FTransaction.StartTransaction ;
end;

procedure TtiDatabase.Commit;
begin
  FTransaction.Commit ;
end;

procedure TtiDatabase.Rollback;
begin
  FTransaction.RollBack ;
end;

Secondly, we extend the TVisitorManager.Execute method transaction support. This means changes in three places. Firstly we start the transaction after assigning the database connection to the visitor’s Database property. Next we wrap the call to pData. Iterate(lVisitor) up in a try except block and call RollBack if an exception is raised. We re-raise the exception after calling RollBack so it will bubble to the top of any exception handling code we have added to our application. The final change is to add the code to call commit that is executed when the call to pData. Iterate( lVisitor ) is completed successfully. The modified TVisitorMgr.Execute method is shown next:

procedure TVisitorMgr.Execute(const pCommand: string; const pData: TVisited);
var
  i: integer;
  lVisitor: TVisitor;
begin
  for i := 0 to FList.Count - 1 do
  if SameText(pCommand, TVisitorMapping(FList.Items[i]).Command) then
  begin
    lVisitor := TVisitorMapping(FList.Items[i]).VisitorClass.Create;
    try
      if (lVisitor is TVisSQLAbs) then
      begin
        TVisSQLAbs(lVisitor).Database := gDBConnection;
        gDBConnection.StartTransaction;
      end 
      try
        pData.Iterate(lVisitor);
      except
        on E: Exception do
        begin
          if (lVisitor is TVisSQLAbs) then
          begin
            gDBConnection.Rollback;
            TVisSQLAbs(lVisitor).Database := nil;
          end;
          raise;
        end;
      end;
      if (lVisitor is TVisSQLAbs) then
      begin
        gDBConnection.Commit;
        TVisSQLAbs(lVisitor).Database := nil;
      end;
    finally
      lVisitor.Free;
    end;
  end;
end;

This transaction strategy will ensure that either all of the objects are saved to the database, or none are saved to the database and will guarantee that the database is never left in an unstable state. There is one further feature to add which will prevent a Visitor’s Final method from being called until the database transaction is successfully committed. At the moment, Final is called after the query has executed. This means that the following sequence of events is possible:

Object_1 Run SaveSQL
Object_1 Call Final and set ObjectState to posClean
Object_2 Run SaveSQL Raise an exception and roll back database.

This will leave the Object_1.ObjectState property incorrectly set to posClean. This is a little tricky to implement and is managed by the TvisitorController class which is a feature of the framework code you can download. We shall discuss the theory of how this was done in a future chapter.

Summary

We have covered a lot of material in this chapter with the main emphasis on extending the Visitor framework to support persistence to a SQL database. We achieved this by creating an application wide database object (which will be moved to a database connection pool in a future chapter), and adding a TQuery object that is owned by the Visitor. We implemented a structured sequence of tasks in the abstract SQL Visitor’s Execute method that was inspired by GoF’s Template Method Pattern. We created abstract SQLRead and SQLUpdate Visitors, then implemented concrete classes to manage the READ, CREATE, UPDATE and DELETE SQL calls that are necessary to persist our TPerson class. We added a debug log trace to the Visitor framework to help the programmer keep track of what was going on inside the tight looping caused by the TVisited’s Iterate method. We explored the idea of adding a Dirty property to the TPerObjAbs class which can be used to enable or disable a save button on a form, then finally implemented transaction support within the TVisitorMgr.Execute method so a group of objects is either all saved, or not saved at all.

The next section

In the next session we shall extend the abstract business object model with additional methods and properties as well as modify the Iterate method so it will allow us to iterate over more complex hierarchies of objects. The next section can be read here.