FWH 19.01: Recycling Deleted Records

[nageswaragunupudi]

We are aware that huge accumulation of deleted records in a dbf greatly reduces performance. FWH 19.01 introduces the feature of recycling of deleted records in DBF opened via "DBFCDX".

This feature may not be required in these cases:

(a) Some standard applications do not permit deletion of records.
(b) The house-keeping routine executed when a user executes the application for the first time in a day includes packing the dbfs.

There could be other cases, where there is a continuous acccumulation of deleted records during execution before the next opportunity to pack or the design does not allow packing. (Whether this is a good programming practice or not is not the point for debate in this post). This feature is useful in such cases.

This feature is available for dbfs opened via "DBFCDX" only and can be availed by using these two functions from FWH:

1. FW_SetRecyleDeleted( nil / .t. / .f. ) --> previous setting // New in 1901
2. DBFAPPEND() --> nAppendedOrRecyledRec // enhanced in 1901 uses this feature depending on the setting

Global setting FW_SetRecyleDeleted( param ) // default NIL

param:

1. Omitted. Does not change the setting. Returns the current setting.
2. .T.. If set to .t., DBFAPPEND() will always try to recycle deleted records.
3. .F.. If set to .t., DBFAPPEND() does not recycle. Same as DBAPPEND()
4. NIL. If set to NIL, DBFAPPEND() adopts default behavior explained later.

Use DBFAPPEND() instead of DBAPPEND() to use this feature. This function's behavour is set by the FW_SetRecycledDeleted() function. (Default NIL)

Present usage of DBAPPEND()

Code: Select all  Expand view

DBAPPEND()
If .not. NetErr()
   // assign values to fields
   DBUNLOCK()
endif
 

Instead, using DBFAPPEND() this way enables using the recycling feature:

Code: Select all  Expand view

if DBFAPPEND() > 0
   // assign values to fields
   DBUNLOCK()
endif
 

Working of DBFAPPEND()

Step-1: Depending on the setting of FW_SetRecycleDeleted(), a deleted record that can be locked is located. If found, the record is locked, data is erased, the record is RECALLed and the record number is returned.

Step-2: If not, attempts to append a new record by trying 4 times at an interval of 0.25 seconds and returns the newly appended record number if successful.

Result of this function is the same as normal DBAPPEND() if the setting is .f.

For the purpose of identifying the deleted records, this feature uses either of two indexes:
a) Any index with for condition "FOR DELETED()"
eg: INDEX ON RECNO() TAG RECYCLE FOR DELETED()
b) Any index with index expression DELETED()
eg: INDEX ON DELETED() TAG DELETED.

Default behaviour (FW_SetRecycleDeleted() is set to NIL):

If the dbf already has index with for condition "FOR DELETED()", this index is used to recycle deleted records. Because an index with this kind of condition is never created in normal course, existance of this index is considered as an indication that recycling is required for this dbf.

Behavior if FW_SetRecycleDeleted() is set to .F.:

The function does not attempt to recycle at all.

Behavior if FW_SetRecycleDeleted() is set to .T.:
Checks if any one of the above indexes exist and then uses that index for recycling.
If no such index exists and if the dbf is opened exclusively, creates an index with "for deleted()" clause and uses it
If the dbf is opened in a shared mode, such an index is created in memory temporarily.

TDataRow() and TDatabase() are compatible with this feature. Depending on the setting, both recycle records in the sameway.

Note: Value of the autoinc field is not changed when recycling the records.

Here are two samples using recycling feature, using DBF directly and with TDatabase. In both cases, the program code is very similar and results are identical.

Sample using DBF directly:

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

static cUser

//----------------------------------------------------------------------------//

function Main()

   RDDSETDEFAULT( "DBFCDX" )
   SET DELETED ON
   SET TIME FORMAT TO "HH:MM:SS"

   cUser    := wNetGetUser()

   FW_SetRecycleDeleted( .T. )

   CreateTestDbf()

   USE RECYCLE NEW SHARED VIA "DBFCDX"
   SET FILTER TO !DELETED()
   GO TOP

   XBROWSER "RECYCLE" FASTEDIT SHOW RECID SETUP ;
      ( oBrw:bTrigger := { || FIELD->USER := cUser } ) ;
      TITLE "FWH 1901: DBF: Recycling of deleted records"

   CLOSE RECYCLE

return nil

//----------------------------------------------------------------------------//

function CreateTestDbf()

   local n
   local lCreated := .f.

   local aCols := {  {  "ID",       "+",   4, 0 }, ;
                     {  "NAME1",    "C",  20, 0 }, ;
                     {  "NAME2",    "C",  30, 0 }, ;
                     {  "USER",     "C",  10, 0 }, ;
                     {  "UPDT",     "=",   8, 0 }  }

   TRY
      DBCREATE( "RECYCLE", aCols, "DBFCDX", .T., "RCL" )
      lCreated := .t.
   CATCH
   END

   if lCreated

      FW_CdxCreate() // Create Indexes

      FW_NUMTOWORDS( 0, "", "" )
      for n := 1 to 20
         DBAPPEND()
         FIELD->NAME1   := LTRIM( FW_NUMTOWORDS( n ) )
         FIELD->NAME2   := LTRIM( FW_NUMTOWORDS( n * 10 ) )
         FIELD->USER    := cUser
      next

      CLOSE RCL

   endif

return nil

//----------------------------------------------------------------------------//
 

Sample using TDatabase class:

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

static cUser

//----------------------------------------------------------------------------//

function Main()

   local oDbf

   RDDSETDEFAULT( "DBFCDX" )
   SET DELETED ON
   SET TIME FORMAT TO "HH:MM:SS"

   cUser    := wNetGetUser()

   FW_SetRecycleDeleted( .T. )

   CreateTestDbf()

   oDbf  := TDatabase():Open( , "RECYCLE", "DBFCDX", .T. )
   oDbf:SetFilter( "!DELETED()" )
   oDbf:GoTop()

   oDbf:bTrigger  := { || FIELD->USER := wNetGetUser() }

   XBROWSER oDbf FASTEDIT SHOW RECID ;
      TITLE "FWH 1901: TDatabase: Recycling Deleted Records"

   oDbf:Close()

return nil

//----------------------------------------------------------------------------//

function CreateTestDbf()

   local n
   local lCreated := .f.
   local oDbf
   local aCols := {  {  "ID",       "+",   4, 0 }, ;
                     {  "NAME1",    "C",  20, 0 }, ;
                     {  "NAME2",    "C",  30, 0 }, ;
                     {  "USER",     "C",  10, 0 }, ;
                     {  "UPDT",     "=",   8, 0 }  }


   oDbf  := TDatabase():Create( "RECYCLE.DBF", aCols, "DBFCDX", "*" )
            // 4th parameter "*" indicates to create indexes on all fields

   oDbf:bTrigger  := { || FIELD->USER := wNetGetUser() }

   if oDbf:Used()

      FW_NUMTOWORDS( 0, "", "" )
      for n := 1 to 20
         oDbf:Append( "NAME1,NAME2", { LTRIM( FW_NUMTOWORDS( n ) ), LTRIM( FW_NUMTOWORDS( n * 10 ) ) } )
      next

   endif

   oDbf  := nil // this closes the dbf

return nil

//----------------------------------------------------------------------------//
 

You may build these samples and test by keeping on editing, deleting and adding new records. You may also try two instances of the application to check multi-user behavior.

[nageswaragunupudi]

Using FWH functions and classes to append records using recycling feature.

To use the feature for specific dbfs only without setting FW_SetRecycleDeleted() globally, first create one index tag with "FOR DELETED()" on the required dbfs. Easy way to create:

Code: Select all  Expand view

USE CUSTOMER NEW EXCLUSIVE VIA "DBFCDX"
if Used()
   FW_CdxCreate( "RECYCLE" )
   CLOSE CUSTOMER
endif
 

Using when opening dbf directly. (without TDatabase)

During XBrowse use oBrw:EditSource( .t. ). When the dialog is edited and saved the recycling feature is automatically used. You can use your custom dialog by setting
oBrw:bEdit := { |oRec| MyEditDlg( oRec ) }
When you save the edit with oRec:Save(), the feature is automatically saved.

For appending known values, without editing;

Code: Select all  Expand view

caFields := "CODE,NAME"
aValues  := { "TN", "Tennese" }
lAppend  := .t.
FW_FieldsPut( caFields, aValues, lAppend )
 

Editing and appending without using XBrowse:

Code: Select all  Expand view

USE STATES NEW SHARED/EXCLUSIVE
oRec := TDataRow():New( "STATES", , .T. )
// Edit values of oRec and then
oRec:Save()
 

Using the recycling feature with TDatabase:
1. Using Blank() and Save() methods

Code: Select all  Expand view

oDbf:Blank()
// Edit or assign values
oDbf:Save()
 

2. Using record object. Recommended.

Code: Select all  Expand view

oDbf := TDatabase():Open( , "SATES", "DBFCDX", lShared )
oDbf:bEdit := { |oRec| MyEditDialog( oRec ) } // optional
// for direct editing and appending
oDbf:Edit( cFieldList, .t. )
// for appending values without editing
oDbf:Append( caFields, aValues )
// if aValues is a 2 dim array multipe records are appended

// if using XBrowse, then use
oBrw:EditSource( .t. )
 

[Silvio.Falconi]

This is a beautiful and usefull invention!!!!!

[Silvio.Falconi]

I not understood..I tried to make it on my test but I not understood how make

I have all record delete



and now I wish recycling delete record and save a new record

Wich are the step for make it ?
my source

Code: Select all  Expand view

//delete all records
oSpiaggia:Gotop()
Do While .not. oSpiaggia:eof()
oSpiaggia:delete()
oSpiaggia:skip()
EndDo


oSpiaggia:Gotop()

// save on temp array

For nY := 1 to TOTY
For nX := 1 To TOTX
nRow:=nX
nCol:=nY
cElemento:=aData[nY,nX]
cLibero:=strzero(recno(),5)


//----------------------------------------------------------------------------------//
Now I save all data into a temporaney array aTempDbf I have info data in different other arrays
//-------------------------------------------------------------------------------------//

For n= 1 to Len(aElementi)
IF aData[nY,nX] == aElementi[n][3]
IF aElementi[n][6] == TRUE //attivo
nCamera:= Iif(!empty(aNumeri[nY,nX]),aNumeri[nY,nX],0)
cSettore:=Iif(!empty( aSettori[nY,nX]),aSettori[nY,nX],"")
cDesc:=aElementi[n][4]
lactive:= TRUE
else
cDesc:=aElementi[n][4]
ENDIF
elseif aData[nY,nX] =="X"
cDesc:="spazio vuoto"
Endif
next n

AaDD( aTempDbf,{nCamera,nRow,nCol,cElemento,cDesc,,strzero(recno(),5),cSettore,lActive})

next nX
next nY
 

Now if I save from array I make

oSpiaggia:SetOrder( 0 )
oSpiaggia:GoTop()
oSpiaggia:ArrayToDBF( atempDbf, cFieldList, nil, .t., .t. )

If I wish recall delete record How I must make ?

[nageswaragunupudi]

oSpiaggia:SetOrder( 0 )
oSpiaggia:GoTop()
oSpiaggia:ArrayToDBF( atempDbf, cFieldList, nil, .t., .t. )

If I wish recall delete record How I must make ?

Code: Select all  Expand view

oSpiaggia:SetOrder( 0 )
oSpiaggia:GoTo( 1 )
oSpiaggia:ArrayToDBF( atempDbf, cFieldList, nil, .t., .t. )
 

[Silvio.Falconi]

thanks

[driessen]

Mr. Rao,

Thanks a lot for all your help.