FoxPro DBF Corruption

May 1999

Help Eliminate DBF Corruption with a Posting Engine

Steve Zimmelman

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 engine
Most 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:

Field  Field Name  Type       Width
    1  ID          Character     10
    2  PROGRAM     Character     50
    3  PARMS       Character     60
    4  SHADOWTABL  Character     50
    5  DATEREQEST  Date           8
    6  DATEDONE    Date           8
    7  START_TIME  Character      8
    8  END_TIME    Character      8
    9  DONE        Logical        1
   10  USERID      Character      5

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.
*
Select Customer
If Seek(m.CustNo)
   cTempTable = 'Temp'+Right(Sys(3),4)+'.dbf'
   Copy To (m.cTempTable) ;
      While Customer.CustNo=m.CustNo
Endif 
*
*-- When editing is complete, add record to
*-- Posting Directive Table.
*
Insert Into Directive ;
   (ID,Program,Parms,ShadowTabl,;
    DateReqest,UserID,Done) ;
   Values (SYS(2015),'CustUpDt.fxp',;
           m.CustNo,m.cTempTable,Date(),m.UserID,.F.)

When the posting engine sees the record, it starts to process the record something like this:

If Seek(.F.,'Directiv')

   Replace Directiv.Start_Time With Time()

   If ! Empty(Directiv.ShadowTabl)
      If File(Directiv.ShadowTabl)
         Use (Directiv.ShadowTabl) Alias Shadow In 0
      Endif
   Endif
   If ! Empty(Directiv.Program) And ;
              File(AllTrim(Directiv.Program))
      If ! Empty(Directiv.Parms)
         pParms = Directiv.Parms
         Do (Directiv.Program) With pParms
      Else
         Do (Directiv.Program)
      Endif   
   Endif
   *
   *-- Close the shadow-table.
   *
   If Used('Shadow')
      Use In Shadow
   Endif
   *
   *-- Update the Directive table.
   *
   Select Directiv
   Replace Done     With .T.,;
           DateDone With Date(),;
           End_Time With Time()
   
Endif

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.