tiofp documentation - the visitor pattern and sql databases.htm

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

    FQuery.Next ; // Duplicated
  end ; // Duplicated
end;</PRE>
<P>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.</P>
<P>GoF tell us that the intent of the Template Method is:</P>
<P>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.</P>
<P>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.</P>
<P>The interface of the re-factored abstract class, TVisSQLAbs class looks like 
this: </P><PRE>// 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 ;</PRE>
<P>And the implementation of the Execute method of TVisSQLAbs looks like this: 
</P><PRE>// Implementation of the abstract SQL read visitor
procedure TVisSQLAbs.Execute(pVisited: TVisited);
  begin
  inherited Execute( pVisited ) ;
  if not AcceptVisitor then
    Exit ; //==&gt;
   
  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;</PRE>
<P>The code below shows the interface and implementation of the concrete SQL 
Visitor TVisSQLReadPeople.</P>
<P>You can see there are four methods to override and implement:</P>
<P>1. AcceptVisitor - is the same as in previous examples – a check to see it 
the class being visited is of the correct type.<BR>2. Init – is where we set the 
FQuery.SQL.Text value.<BR>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.<BR>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.</P>
<P>The UML of the class hierarchy we have implemented so far looks like 
this:</P>
<P><IMG height=243 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0001.gif" 
width=144> </P>
<H2>Implementing the concrete SQL read visitor</H2>
<P>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: </P><PRE>TVisSQLReadPeople = class( TVisSQLAbs )
protected
  function AcceptVisitor : boolean ; override ;
  procedure Init; override ;
  procedure MapRowToObject ; override ;
  procedure SetupParams ; override ;
end ;</PRE>
<P>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.<BR></P><PRE>// AccetpVisitor is the same as in previous examples
function TVisSQLReadPeople.AcceptVisitor : boolean;
begin
  result := Visited is TPeople ;
end;</PRE><PRE>// 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;</PRE><PRE>// 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;</PRE><PRE>// 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;</PRE>
<P>The UML or the class hierarchy we have build so far looks like this:</P>
<P><IMG height=331 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0002.gif" 
width=395> </P>
<P>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:</P><PRE>// Registering TVisSQSLReadPeople is the same as usual
initialization
  gVisitorMgr.RegisterVisitor( 'SQLRead', TVisSQLReadPeople ) ;
end.</PRE><PRE>// And calling TVisSQLReadPeople is the same as usual
begin
  gVisitorMgr.Execute( 'SQLRead', FPeople ) ;
end;</PRE>
<P>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: </P><PRE>// Register the SQLRead Visitor and CSVSave Visitors
initialization
  gVisitorMgr.RegisterVisitor( 'CSVSave', TVisCSVSave ) ;
  gVisitorMgr.RegisterVisitor( 'SQLRead', TVisSQLReadPeople ) ;
end.</PRE><PRE>// 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 ;</PRE>
<P>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.</P>
<H2>Refactor the SQLVisitor hierarchy for read and update (create, update, 
delete) SQL</H2>
<P>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.</P>
<P>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 weather it has to run CREATE, UPDATE or DELETE SQL.</P>
<P>The steps we found are necessary when reading objects from a SQL database 
are:</P>
<TABLE cellSpacing=0 cellPadding=0>
  <TBODY>
  <TR class=Normal>
    <TD vAlign=top width=295>Task</TD>
    <TD vAlign=top width=295>Method name</TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=295>
      <P>Should we be visiting this object? </P></TD>
    <TD vAlign=top width=295>
      <P>AcceptVisitor </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=295>
      <P>Setup the Query, assign the SQL </P></TD>
    <TD vAlign=top width=295>
      <P>Init </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=295>
      <P>Set any parameters required by the SQL </P></TD>
    <TD vAlign=top width=295>
      <P>SetupParams </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=295>
      <P>Turn the SQL result set into objects </P></TD>
    <TD vAlign=top width=295>
      <P>MapRowToObject </P></TD></TR></TBODY></TABLE>
<P>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:</P>
<P><IMG height=356 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0003.gif" 
width=576> </P>
<P>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.<BR></P><PRE>// 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 ;</PRE>
<P>The interface of the abstract SQL read visitor is shown next. We have added 
the MapRowToObject method an have implemented Execute as shown beloe: </P><PRE>// 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 ;</PRE>end ;<PRE></PRE><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 ;
end;</PRE>
<P>The interface of the SQL update visitor has no additional methods and only 
contains the overridden Execute procedure. </P><PRE>// 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 ;</PRE>
<P>And the implementation of the abstract SQL update Visitor’s Execute method 
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 ;
end;</PRE>
<P>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.</P>
<H2>Write an UPDATE Visitor</H2>
<P>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: </P><PRE>update People
set
    Name = :Name
   ,EMailAdrs = :EMailAdrs
where
    Name = :Old_Name
and EMailAdrs = :Old_EMailAdrs</PRE>
<P>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 weather or not it is worth our while 
developing a custom persistence framework. (I still maintain that it’s well 
worth the effort.)</P>
<P>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