tiofp documentation - the visitor pattern and sql databases.htm

tiOPF 面向对象的数据库持久层持久层开发的框架

<P>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.</P>
<P>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.</P>
<P>Now that we have set the ObjectState property after inserting a new object, 
we can write the Create Visitor.</P>
<H2>Write a CREATE Visitor</H2>
<P>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.</P>
<P>An example of this is shown below:</P><PRE>function TVisSQLCreatePeople.AcceptVisitor: boolean;
begin
  result := ( Visited is TPerson ) and
    ( TPerson( Visited ).ObjectState = posCreate ) ;
end;</PRE>
<P>The full interface of the Create Visitor is shown below:</P><PRE>Interface
TVisSQLCreatePeople = class( TVisSQLUpdateAbs )
protected
  function AcceptVisitor : boolean ; override ;
  procedure Init ; override ;
  procedure SetupParams ; override ;
end ;</PRE>
<P>And the implementation of the Create Visitor is shown next:</P><PRE>implementation
function TVisSQLCreatePeople.AcceptVisitor: boolean;
begin
  result := ( Visited is TPerson ) and
    ( TPerson( Visited ).ObjectState = posCreate ) ;
end;</PRE><PRE>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;</PRE>
<P>This Visitor is registered with the Visitor Manager in the usual way:</P><PRE>initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLCreatePeople ) ;
end.</PRE>
<H2>Write a DELETE Visitor</H2>
<P>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:</P><PRE>Interface
TVisSQLUpdatePeople = class( TVisSQLUpdateAbs )
protected
  function AcceptVisitor : boolean ; override ;
  procedure Init ; override ;
  procedure SetupParams ; override ;
end ;</PRE>
<P>The implementation of TVisSQLDeletePeople looks like this:</P><PRE>implementation
function TVisSQLDeletePeople.AcceptVisitor: boolean;
begin
  result := ( Visited is TPerson ) and
  ( TPerson( Visited ).ObjectState = posDelete ) ;
end;</PRE><PRE>procedure TVisSQLDeletePeople.Init;
begin
  FQuery.SQL.Text :=
  'delete from People ' +
  'where ' +
  'OID = :OID' ;
end;</PRE><PRE>procedure TVisSQLDeletePeople.SetupParams;
var
  lData : TPerson ;
begin
  lData := TPerson( Visited ) ;
  FQuery.Params.ParamByName( 'OID' ).AsInteger := lData.OID ;
end;</PRE>
<P>And once again, the Visitor is registered with the Visitor Manager in the 
usual way:</P><PRE>initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLDeletePeople ) ;
end.</PRE>
<H2>Registering Visitors in the correct order</H2>
<P>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:<BR></P><PRE>initialization
  gVisitorMgr.RegisterVisitor( 'SQLRead', TVisSQLReadPeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLDeletePeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLUpdatePeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLCreatePeople ) ;</PRE>end.<PRE></PRE>
<H2>Setting ObjectState back to posClean</H2>
<P>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:</P><PRE>interface
  TVisPerObjAwareAbs = class( TVisitor )
  protected
    procedure Final ; virtual ;
  end ;</PRE><PRE>implementation
procedure TVisPerObjAwareAbs.Final;
begin
  if TPerObjAbs( Visited ).ObjectState = posDelete then
    TPerObjAbs( Visited ).ObjectState := posDeleted
  else
    TPerObjAbs( Visited ).ObjectState := posClean ;
end;</PRE>
<P>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:</P><PRE>procedure TVisSQLReadAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==&gt;
  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;</PRE>
<P>And the execute method of TVisSQLUpdateAbs looks like this:</P><PRE>procedure TVisSQLUpdateAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==&gt;
  Init ; // Set the SQL. Implemented in the concrete class
  SetupParams ; // Set the Queries parameters. Implemented in the concrete class
  FQuery.ExecSQL ;
  Final ;
end;</PRE>
<P>The UML of the SQL visitor class hierarchy now looks like this:</P>
<P><IMG height=428 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0005.gif" 
width=546> </P>
<H2>Filtering in the GUI</H2>
<P>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:</P><PRE>procedure TFormMain_VisitorManager.LVFilterData(
   const pData: TPersistent;
   var   pbInclude: Boolean);
begin
  pbInclude := not TPerObjAbs( pData ).Deleted ;
end;</PRE>
<P>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.</P>
<P>Add some logging to help debugging</P>
<P>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.</P>
<P>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:</P><PRE>procedure TVisSQLReadAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==&gt;
  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;</PRE>
<P>and this...</P><PRE>procedure TVisSQLUpdateAbs.Execute(pVisited: TVisited);
begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==&gt;
  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;</PRE>
<P>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.</P>
<P>For example, the TVisSQLUpdateAbs.Execute method is refactored like this in 
its implementation in the tiOPF:</P><PRE>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 ; //==&gt;
  DoInit ;
  DoGetQuery ;
  DoSetupParams ;
  DoExecuteQuery ;
end ;</PRE>
<P>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:</P>
<P align=left><IMG height=116 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0006.gif" 
width=576></P>
<P align=left><IMG height=196 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0007.gif" 
width=342> </P>
<H2>Change the CSV and TXT visitors to ignore deleted objects </H2>
<P>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.</P>
<P>In the main form, under the delete button we had the following code that 
removes then frees the object from the list: </P><PRE>procedure TFormMain_VisitorManager.LVItemDelete(
   pLV: TtiCustomListView; pData: TPersistent; pItem: TListItem);
begin
  FPeople.List.Remove( pData ) ;
end;</PRE>
<P>For saving to a SQL database, this has been changed to marking the object for 
deletion, rather than removing it from the list.</P><PRE>procedure TFormMain_VisitorManager.LVItemDelete(
   pLV: TtiCustomListView; pData: TPersistent; pItem: TListItem);
begin
  TPerObjAbs( pData ).Deleted := true ;
end;</PRE>
<P>The AcceptVisitor method in TVisCSVSave and TVisTXTSave must be extended to 
skip over records that have been deleted, or marked for deletion:</P><PRE>function TVisCSVSave.AcceptVisitor : boolean;
begin
  result := ( Visited is TPerson ) and 
    ( not TPerson( Visited ).Deleted ) ;
end;</PRE>
<P>Enable the save button in the GUI only when the object hierarchy is dirty</P>
<P>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.</P>
<P>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:</P><PRE>TVisPerObjIsDirty = class( TVisitorAbs )
private