Tuesday, February 26, 2008

TIOPF - New persistance layer walkthrough


I use tiOPF for most new Delphi database applications.
tiOPF is a Object Persistence Framework. That is, it is a framework based around saving your objects to, and loading them from, databases and/or flat files.

See overview for more details on tiOPF.

Persistence Layers (PL)
TIOPF uses persistence layers to save/load objects. If you want to use an unsupported database, you just write another PL, and the required unit tests. The following is a walkthrough the process of creating and testing a new PL.

Corelab - SDAC
I use Corelab's Sql Server Data Access Components for talking to MS Sql Server databases. This is faster than the BDE and ADO components I have used previously. However there is currently no PL for this...

Step 1 - Base point
Writing a PL involves the oldest form of code reuse - copy/paste.
Find an existing PL similar to what you want. In this case, I will use ADOSQLServer. The ado sql server layer is slightly more complicated than most as it uses an abstract class shared with ado access. However this shouldn't be a problem.

Because I am using a very similar persistance layer, the required changes are quite limited. In many cases, I would need to alter the Connection string handling as well, amd perhaps the multithreading support.

I need to copy the *ADOSQLServer files to *CrLabSDAC files, and then make the following substitutions:

  • TMSConnection replaces TADOConnection
  • TMSQuery replaces TADOQuery
  • CrSDAC replaces ADOSQLServer
  • CrAbs replaces ADOAbs
+ the various uses replacements

Using delphi's 'Find it Files', I searched for ADOSQLServer in the ..\tiOPF2\Trunk\ directory and subdirectories. It pulls up the following files:

  • tiQueryADOSQLServer (which uses tiQueryADOAbs)
  • TTestTIPersistenceLayersADOSQLServer
  • tiTestDependencies
  • tiConstants
  • tiOPFManager

tiQueryADOSQLServer.pas is saved as tiQueryCrSdac.pas
tiQueryADOAbs.pas is saved as tiQueryCrAbs.pas
Both of these are saved in the \Options\ directory

tiOPFADOSQLServer_TST.pas is saved as tiOPFCrSdac_TST.pas
This is saved in the \UnitTests\Tests directory.

Step 2 Persistence Layer Changes
In tiQueryCrAbs, I made the following changes:

  • In the uses I replace ,ADODb with ,DBAccess, MSAccess
  • replace TADOConnection with TMSConnection
  • replace FADOConnection with FMSConnection
  • replace TADOQuery with TMSQuery
  • replace FADOQuery with FMSQuery
  • replace TtiQueryADO with TtiQueryCrSdac
  • replace TtiDatabaseADOAbs with TtiDatabaseCrAbs
  • replace cErrorADOCoInitialize with cErrorCrCoInitialize
  • replace EADOError with EDAError
  • replace cTIPersistADOSQLServer with cTIPersistCrSdac
  • delete cDelphi5ADOErrorString = '...';

In tiQueryCrSdac, I made the following changes:

  • In the uses replace ,ADODb with ,DBAccess, MSAccess and tiQueryADOAbs with tiQueryCrAbs
  • replace ADOSQLServer with CrSdac
  • replace TtiDatabaseADOAbs with TtiDatabaseCrAbs
  • replace TADOTable with TMSTable
  • Delete the line TtiQueryCrSdac = class(TtiQueryADO);

In tiConstants, I added the line
cTIPersistCrSdac = 'CrSdac';

In tiOPFManager, I added the line

This means that the CR PL can be compiled in to applications by using LINK_CRSDAC in the conditional defines instead of adding the unit tiQueryCrSdac to the application. This makes running the unit tests much easier as my local copy of the standard tests just need the define added

Finally, I added the LINK_CRSDAC define to the DUNINTTIOPFGui appliciation and did a build.

Some errors turned up in constructor TtiQueryCrSdac.Create; so I commented them out for now. I found a few more compile errors as well due to the differences between SDAC and Ado components

  • replace ExecSQL with Execute
  • replace Parameters with Params
  • replace TParameter with TMSParam
  • replace CommitTrans with Commit
  • replace RollbackTrans with Rollback
  • replace BeginTrans with StartTransaction
  • replace ConnectionString with ConnectString

Finally it all compiles. I ran the unit tests to make sure I haven't broken anything yet. I shouldn't have (yet) but ...

8 minutes later, 1729 tests are run and passed.

Step 3 Unit Test changes

In tiOPFCrSdac_TST, I made the following changes:

  • replace ADOSQLServer with CrSdac

In tiTestDependencies, I made the following changes:
  • added ,tiOPFCrSdac_TST after ,tiOPFADOSQLServer_TST
  • added tiOPFCrSdac_TST.RegisterTests; after tiOPFADOSQLServer_TST.RegisterTests;

Update: Add the following define to the unit test properties: LINK_CRSDAC

This now adds unit tests for the CrSdac PL. It will run the same tests as the AdoSqlServer layer. If necessary, I could override and alter the tests to accommodate database changes.

Compile and run again.

This time there are numerous errors. In part, this is because this layer doesn't implement CreateDatabase and no default database has been defined.

I ran the tests again. In the Setup dialog, I clicked on the [Local Settings] button. I added the following lines to the ini.

This resolves most errors, leaving only 5. I won't step through the process of fixing them. They came down to:
  • SDAC truncates long strings to 8000. This needs further investigation
  • SDAC GetTableNames returns the owner as part of the name (eg 'dbo.MyTable') which needs to be removed.
Step 4 Real data test
In the unit tests for my real application, I added then tiQueryCrSdac unit. I can now swap persistence layers by altering the connection string used in my application.

Running the tests raises a couple more errors which are resolved by changing some of the SDAC query options. Once that is done, and the tests all pass, I ran my application against some test data, and real data. All works well.

Step 5 Build a patch file

TIOPF http://tiopf.sourceforge.net/
Overview http://tiopf.sourceforge.net/Doc/overview/index.shtml
Corelabs http://crlab.com/


Everybyte said...

How much faster in your experience is SDAC-based PL compared to ADO-based PL ?

Sean said...

I haven't done a back to back comparision yet. However sdac runs the tiopf unit tests about 10% faster than ado.

Everybyte said...

Thanks for a quick reply.

Check the comment timestamps, time is moving backwards. That also could give a performance advantage