Disconnected Apps - Purge Records.

[dherron]

In Bruces video (339) and in a post to the Clarion10 NG(dd 5/1/16) re SQL Bruce speaks of purging records marked as deleted.  In the latter he says, "...both sides can purge deleted records. (I'm assuming edits can happen on both sides here.)"

The principle that purging can only occur "…..once the (deleted) mark has propagated" is clear.  It is the method that escapes me.

In the Disconnected app the 'server' does not necessarily have an edit function, so the SQL example would (logically) not apply.

Presuming that the server controls the 'master records' it would seem that the purging would be effected from there.  Try as I might I have not succeeded. Please can you advise how this might be achieved?

(As I read the manual, a record's DeletedTimeStamp is the only thing that marks that record as deleted.  This implies that both the server and each desktop app periodically have to run through the entire database looking for anywhere the DeletedTimeStamp is set and using Access:<file>.DeleteRecord to really delete the record, finishing off with a PACK.  Is there a more elegant way to do this? )

Thanks

[Bruce]

Hi Dermot,

You don't mention what database you are using, but I'll assume TPS.

(for SQL a simple prop:sql, or a stored procedure will work fine.)

So, first thing to do is determine the date range you want to delete.
to do that we want to know the current time, and subtract some fixed amount from that.
Lets say you want to keep records for 30 days after they are deleted.

currentTime  real
net               _NetAll  ! any NetTalk class will work.
keepfor         Equate(30 * 24 * 60 * 60 * 1000)

  currentTime = net.GetElapsedTimeUTC()

  ! so delete all records which have a dts >0 and dts < currentTime-KeepFor.

Smart play is to have a key on the dts field. but with, or without that, simply scan through the table looking for records with dts >0 and dts < currentTime-KeepFor and delete them. (While it depends on the table, there's probably not a lot of them.)

You can call PACK at the end if you like, but as far as I know it doesn't do anything to a TPS file.

cheers
Bruce

[dherron]

Thanks Bruce.

Yes - it is TPS and yes PACK does get rid of empty space in a TPS file. (Being compressed records in a TPS, the length of a record is not fixed so deletion has to leave a hole and the file gets 'defragmented' and needs sorting out.)

I get from your comments that the deletions aren't that important, provided you filter them out from any browse or form.  In the end of course you have to clean up, but that can be left for a while (min 1 week?) so you can be certain that the sync process has happened. 

And BOTH ends have to delete or the deleted records get replaced by the sync!

Dermot

[Bruce]

Hi Dermot,

yes you've got it right, with one small tweak,

>> And BOTH ends have to delete or the deleted records get replaced by the sync!

It's only the server that needs to keep them around.
The client can delete the record immediately when servertimestamp = timestamp.
In other words, in the client-side of the sync, records that come from the server with deletedtimestamp >0 and servertimestamp=timestamp can be deleted / not written.

But even if you don't do this;

>> And BOTH ends have to delete or the deleted records get replaced by the sync!

no, this is untrue. Deleted records are not replaced by a sync because;
a) the client only sends records where timestamp <> servertimestamp and
b) the server only sends records with servertimestamp > everythingafter.

So physically deleting a record (on either end) will not cause a resync, unless the record (where it still exists) gets edited. (like, maybe, undeleted.)

Cheers
Bruce