Help Eliminate DBF Corruption with a Posting Engine
Copyright Pinnacle Publishing, Inc. All rights reserved.Help Eliminate DBF Corruption with a Posting Engine
The DBF file has been with us for about 15 years, and while I really like the open architecture of the structure, there's always been an inherent problem with Index and data corruption -- especially in a network environment. The problem seems to be with the amount of data that's transmitted up and down the network wire. Couple this with multiple users, and it doesn't take long to pop an index. On the other hand, single-user systems appear to be much more stable and don't exhibit the same corruption levels as their networked cousins. One solution, often used by developers (myself included), has been to Reindex or recreate the index files when an index-related error rears its head. But this can become unmanageable when the datasets start to approach the four million record mark. A long-term solution might be to force the multi-user application to behave like a single-user system by developing what's become known as a posting engine.
The theory behind the posting engine is actually fairly simple. The
fundamental rule is to never allow the user to post anything directly to the shared table. Instead, the user posts to a shadow-table. After the shadow-table is populated and no errors have occurred during the post, the application passes a directive to the posting engine, which in essence tells the engine what it's supposed to do. In this case, it updates the main table from the data in the shadow-table. So what's actually happening is that the posting engine, or a single workstation, is the only one posting to the main table. This is exactly what a single-user system does.
Anatomy of an engineMost of the posting engines I've seen are able to process a variety of requests by using a table to receive its directives. A simple directive table can have a structure like this:
The engine sits in a loop and looks for records where the Done field is False. When it sees one, it will start to process it using the information stored in the record.
For example, let's say an application needs to update a client's record. The application would get a copy of the record and store it in a shadow-table. After the user makes the desired changes, he or she clicks on the Post or Commit button, and the application writes a record to the directive table. The code might look something like this:
*-- Copy customer record to shadow-table
*-- for editing.
cTempTable = 'Temp'+Right(Sys(3),4)+'.dbf'
Copy To (m.cTempTable) ;
*-- When editing is complete, add record to
*-- Posting Directive Table.
Insert Into Directive ;
When the posting engine sees the record, it starts to process the record something like this:
Replace Directiv.Start_Time With Time()
If ! Empty(Directiv.ShadowTabl)
Use (Directiv.ShadowTabl) Alias Shadow In 0
If ! Empty(Directiv.Program) And ;
If ! Empty(Directiv.Parms)
pParms = Directiv.Parms
Do (Directiv.Program) With pParms
*-- Close the shadow-table.
Use In Shadow
*-- Update the Directive table.
Replace Done With .T.,;
DateDone With Date(),;
End_Time With Time()
The engine can be a non-visible or visible application. I prefer a visible engine so I can monitor the engine's progress if necessary.
The use of a posting engine is obviously not for smaller installations, and it does require a bit reengineering. But for large installations where data integrity must be secured, it might be the way to go.