Deleted Records

Management of Deleted Records

In the strict sense of Xbase RDDs protocol, DELETED status of records is not maintained in CachéRDD. Once a record is deleted is gone forever. Function DELETED() ever returns FALSE. This implementation was necessary for CachéRDD to co-exist with other external processes.

However, a deleted record is always available to the application but in somewhat different manner. Along with record data it also maintains additional information about the state when the record was deleted.

Well, let us look into how this feature is implemented.

At the time of creating a table CacheRDD creates one more cSchema (package) in the same cNamespace with a suffix "DEL" attached to the cSchema supplied with original table. So, if you are going by default, i.e., are not supplying cSchema with DbCreate(), then "SQLUSER" is the default cSchema and hence cSchema for deleted records info comes out to be "SQLUSERDEL".

So in cSchema for deleted records, CacheRDD creates another table with same name and structure as of original. Indexes are not created for deleted records table. CacheRDD also creates another table with original name suffixed with "INF" (short name for INFormation).

DbCreate( "C:\MyDataFolder\MyTable.dbf", aStruct, "CACHERDD" )

This command will create two cSchema and tables under them as:

1. SQLUSER
MYTABLE with INDEX definitions

2. SQLUSERDEL
MYTABLE without INDEX definitions
MYTABLEINF

Table MYTABLEINF has the following structure

FieldName	Type	Len	Dec	Description
DELRECNO	N	10	0	Record number of the original table.
TABLERECNO	N	10	0	Same as above, please ignore. I had different concept in mind when initiated.
DATE	D	8	0	Server date when the record was deleted.
TIME	C	8	0	Server time of the deleted operation.
USERINFO	C	16	0	Application injected user info set by CacheSetUserInfo() function.
EXECUTABLE	C	20	0	The name of the executable without path, i.e., Vouch.exe.
CLIENTIP	C	20	0	Clients IP address in the notation 255.255.255.255.
CLIENTNODE	C	20	0	Clients Machine's Node Address.
CLIENTUSER	C	20	0	User Name of the Client Machine.
CACHEUSER	C	20	0	User name supplied with CacheSetServerParams() as <cUsername>.

You can access these tables via your application like:

USE "SQLUSERDEL||MYTABLE" VIA "CacheRDD"
and
USE "SQLUSERDEL||MYTABLEINF" VIA "CacheRDD"

Both these tables have no index definition.

NOTE: "SQLUSERDEL||MYTABLE" contains records with their original record number from parent table. So, the function RecNo() will return you the record number of parent table. Once a record is deleted from parent it is transferred to this table with the same record number. The same record number is never used in parent table ever after. New record always fetches the new number. Record numbers are never reused. This is contrary to DBFCDX where record number is calculated via offset of record lengths. This is very important feature and is available only in Cache Database. So, as deleted records table retains the same record number as of parent, you can never judge which record was deleted first. You need to take the help of *INF table whose first field contains the same record number which is returned by RecNo() on deleted table.

This is the reason a record is always present in only one table, either parent or deleted, at a given time. Based on this feature, CacheRDD is capable of placing a deleted record in deleted table back to its parent table exactly at the same location, i.e., record number. It can be achieved via CacheRecallDeleted() function.

Via SQL query you can join both tables like this. You can take advantage of CacheRDD's SQL Interface.

SELECT * FROM sqluserdel.mytable del LEFT OUTER JOIN sqluserdel.mytableinf inf ON del.%ID = inf.delrecno

IMPORTANT: Irrespective of nature of application, be it CacheRDD compiled executable OR any external SQL tool or executable, deleted records are transferred to deleted table with all the information intact. However <USERINFO> field will contain nothing if user info is not set via CacheSetUserInfo(). For external application this field will ever contain blank value.