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/

Friday, February 1, 2008

Remote access to computers

I spent a fair amount of time using one computer to look at/control another across the internet. Over the past 5 years I have evaluated a number of products. The following are some of my favourites.

Remote Desktop
This is the best option across a wan, giving good performance. However it is a pain in the proverbial to set up for use across the internet, as the ports are frequently blocked by firewalls.

LogMeIn is the one I use must frequently, for controlling my personal computers. A free account will let you control a reasonable number of computers (5 I think). The paying version has additional features such as file access, sound and printing. I have used the free version for about 5 years.

Install is straight forward, go to the web site, log in, install software and go. The software takes care of firewall and NAT issues in nearly every case. In 5 years, I only found one location where I couldn't get connectivity.

There is software available for nearly everything, I have even used my windows mobile cellphone to control my pc.

It's account based, so it is good for computers I own. It is not so good for other computers where I don't have physical access beforehand as I obviously don't want to pass my account details around. Once installed, the client pc can be unattended.

GotoMyPc is an alternative, but they don't have a free version.

I use crossloop (no relation) for remote user support. They download and install crossloop and I do the same. They click on the share button, send me the access code, and then I connect using the same access code. Performance is not as good as LogMeIn, but it is usually adequate.

It does require a user on the client machine to run the software, hit the share button and provide the access code. It's free to use.

CoPilot is an alternative which is probably better for Grandma and technically challenged users. It's free on weekends, and $5 a day during the week.