tiofp documentation - the visitor pattern and sql databases.htm

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

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.)</P>
<P>We now have several changes to make to our earlier work:</P>
<OL>
  <LI>The table structure must be extended with the OID column, and the data 
  populated with OID values. 
  <LI>Our TPerson class must be extended with an OID property 
  <LI>The read visitor must be extended to read an object’s OID </LI></OL>
<P>We will change the script to create our test table to look like this:</P><PRE>create table People
( OID Integer not null,
  Name VarChar( 20 ),
  EMailAdrs VarChar( 60 ),
  Primary Key ( OID )) ;</PRE><PRE>insert into People values ( 1, "Peter Hinrichsen", "[e-mail]");
insert into People values ( 2, "Don Macrae", "[e-mail]" ) ;
insert into People values ( 3, "Malcolm Groves", "[e-mail]" ) ;</PRE>
<P>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.</P>
<P>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: </P><PRE>TOID = Int64 ;
TPerObjAbs = class( TVisited )
private
  FOID: TOID;
public
  constructor Create ; virtual ;
  property OID : TOID read FOID write FOID ;
end ;</PRE>
<P>And the changed TVisSQLReadPeople looks like this:</P><PRE>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;</PRE>
<P>I have also refactored the CSV and TXT file visitors, but have not shown the 
code for these here.</P>
<P>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.</P><PRE>update People
set
   Name = :Name
  ,EMailAdrs = :EMailAdrs
where
   OID = :OID</PRE>
<P>It is now possible to finish the TVisSQLUpdatePeople class with the 
implementation looking like this:</P><PRE>function TVisSQLUpdatePeople.AcceptVisitor: boolean;
begin
  result := Visited is TPerson ;
end;</PRE><PRE>procedure TVisSQLUpdatePeople.Init;
begin
  FQuery.SQL.Text :=
    'update People ' +
    'set ' +
    ' Name = :Name ' +
    ' ,EMailAdrs = :EMailAdrs ' +
    'where ' +
    ' OID = :OID' ;
end;</PRE><PRE>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;</PRE>
<P>And of course don’t forget to register TVisSQLUpdatePeople with the Visitor 
Manager in the unit’s implementation section like this:</P><PRE>// Register the SQLSave Visitor
initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLUpdatePeople ) ;
end.</PRE>
<H2>The need for an ObjectState property</H2>
<P>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:</P><PRE>procedure TVisSQLCreatePeople.Init;
begin
   FQuery.SQL.Text :=
   'insert into People ' +
   '( OID, Name, EMailAdrs ) ' +
   'values ' +
   '( :OID, :Name, :EMailAdrs ) ' ;
end;</PRE><PRE>// Register the SQLSave Visitor
initialization
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLUpdatePeople ) ;
  gVisitorMgr.RegisterVisitor( 'SQLSave', TVisSQLCreatePeople ) ;
end.</PRE>
<P>We register the new Visitor with the Visitor Manager, compile and run the 
code then test by inserting an new value and…</P>
<P><IMG height=119 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image002_0000.jpg" 
width=462> </P>
<P>…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.</P>
<P>In the tiOPF, ObjectState is an ordinal type and can have the following 
values:</P>
<TABLE cellSpacing=0 cellPadding=0>
  <TBODY>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P><STRONG>Value </STRONG></P></TD>
    <TD vAlign=top width=481>
      <P><STRONG>Description </STRONG></P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posEmpty </P></TD>
    <TD vAlign=top width=481>
      <P>The object has been created, but not filled with data from the DB 
    </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posPK </P></TD>
    <TD vAlign=top width=481>
      <P>The object has been created, but only it's primary key has been read 
      </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posCreate </P></TD>
    <TD vAlign=top width=481>
      <P>The object has been created and populated with data and must be saved 
      to the DB </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posUpdate </P></TD>
    <TD vAlign=top width=481>
      <P>The object has been changed, the DB must be updated </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posDelete </P></TD>
    <TD vAlign=top width=481>
      <P>The object has been deleted, it must be deleted from the DB </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posDeleted </P></TD>
    <TD vAlign=top width=481>
      <P>The object was marked for deletion, and has been deleted in the 
      database </P></TD></TR>
  <TR class=Normal>
    <TD vAlign=top width=109>
      <P>posClean </P></TD>
    <TD vAlign=top width=481>
      <P>The object is 'Clean' no DB update necessary </P></TD></TR></TBODY></TABLE>
<P>The TPerObjAbsState type is declared like this:</P><PRE>TPerObjectState = (
   posEmpty, posPK, posCreate, posUpdate, posDelete, posDeleted, posClean ) ;</PRE>
<P>What we are aiming to do is to be able to selectively run visitors depending 
on weather 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:</P><PRE>case Visited.ObjectState of
posCreate : // Call CREATE SQL
posUpdate : // Call UPDATE SQL
posDelete : // Call DELETE SQL
else
  // Do nothing
end ;</PRE>
<P>This can be achieved by checking Visited.ObjectState inside the Visitors 
AcceptVisitor method.</P>
<P>Now we have several things to do to finish off the work with ObjectState:</P>
<OL>
  <LI>et ObjectState when we create, update or delete an object in the GUI; 
  <LI>ilter out objects that have been marked as posDelete or posDeleted from 
  the GUI; 
  <LI>heck the ObjectState property in AcceptVisitor; and 
  <LI>et the ObjectState back to posClean after a Visitor has run. </LI></OL>
<P>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:</P><PRE>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 ;</PRE>
<P>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.</P><PRE>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;</PRE>
<P>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.</P><PRE>function TPerObjAbs.GetDirty: boolean;
begin
  result := ObjectState in [posCreate, posUpdate, posDelete] ;
end;</PRE>
<P>And the implementation of the Deleted Get and Set methods looks like 
this:</P><PRE>function TPerObjAbs.GetDeleted: boolean;
begin
  result := ObjectState in [posDelete, posDeleted];
end;</PRE>
<P>As you can see, GetDeleted will return True if ObjectState is either 
posDelete or posDeleted.</P><PRE>procedure TPerObjAbs.SetDeleted(const Value: boolean);
begin
  if ObjectState &lt;&gt; posDeleted then
    ObjectState := posDelete ;
end;</PRE>
<P>We can now use ObjectState, and the two helper properties Deleted and Dirty 
to filter objects by state when saving to the database.</P>
<H2>Insert a new TPerson in the GUI</H2>
<P>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.</P>
<P>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:<BR></P><PRE>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;</PRE>
<P>The call to TformEditPerson.Execute( lData ) executes the following code, and 
shows the dialog box as in the screen shot below:</P><PRE>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;</PRE>
<P><IMG height=141 
src="tiOFP Documentation - The Visitor pattern and SQL databases_files/3_TheVisitorAndSQLDatabases_clip_image001_0004.gif" 
width=294> </P>
<P>Under the OK button of this dialog we set the TPerson’s Dirty property to 
true like this:</P><PRE>procedure TFormEditPerson.btnOKClick(Sender: TObject);
begin
  FData.Dirty := true ;
  ModalResult := mrOK ;
end;</PRE>