Open Exchange App Synchronize Data with DSTIME

 For Data Synchronization inside Caché you have a range of ways to synchronize objects and tables.
At DB level you can use Shadowing  or Mirroring 

This works excellent and if you need just a part of your data to be synchronized you may split your
data into smaller pieces using Global mapping 
Or if you need bi-directional synchronization on Class/Table level you can use the Object Synchronization Feature 


The limit of all these excellent features:
They just work from Caché/IRIS to Caché/IRIS.

If you need to synchronize your data to some external DB you require some other solution.

The solution is available in Caché/IRIS since quite some time and works excellent.
^OBJ.DSTIME does the magic.

It was built to allow data synchronization with Deep See.
It keeps a very simple journal on Object / Table changes by signaling  Modified,New,Deleted
This could be useful not only for DeepSee but for any other type of Data Synchronization.

The Global ^OBJ.DSTIME has 2 additional features

  • It is wrapped in a persistent class %SYSTEM.DSTIME 
     so you can use it also as normal SQL Table to select your changes
     
  • It maintains a version ID (named DSTIME)  that allows control of synchronized junks:
                - you fetch the last version
                - increase the version
                - and then you upload your changes wherever you require them based on the fetched version

And as you do the synchronization by pure SQL your target can be just any DB understanding SQL.

I extended the class %SYSTEM.DSTIME   and placed an example in Open Exchange to try it in SAMPLES
 

  • + 7
  • 1
  • 82
  • 4

Comments

Thank you very much for exposing and extending this capability to the community.

How could a Property like timestamp as $H be added to the DSTIME objects ?

from docs:
Class parameter  DSTIME

• parameter DSTIME;
If the DSTIME parameter is set to AUTO then the most recent filing operation in the current DSTIME value for each object will be recorded in a global, ^OBJ.DSTIME:
^OBJ.DSTIME(ExtentName,DSTIME,objectID) = filing operation
For DSTIME=AUTO the DSTIME value is recorded in ^OBJ.DSTIME and is set by the consumer of DSTIME data.
Refer to %DeepSee documentation for more information on how DSTIME is used by %DeepSee.
The filing operations are:
Code
Operation
0
Update
1
Insert
2
Delete

If the DSTIME parameter is set to MANUAL then the user is responsible for journaling object filing operations.

With MANUAL you are free to add whatever you like to your global ^OBJ.DSTIME
at the price to add your own code manually for any %Save , %Delete,  and also all SQL Triggers.

I didn't mention this. If you do it, there is no advantage to use DSTIME. you could do it with any class or global.
You just use the idea.  

As a simple solution for the same problem I often add these properties to persistent classes:

/// Creation timesptamp (UTC)
Property createdOn As %TimeStamp(POPORDER = -1) [ InitialExpression = {$ZDATETIME($ZTIMESTAMP, 3, 1, 3)}, Required ];

/// Last modification timestamp (UTC)
Property updatedOn As %TimeStamp(POPORDER = -1) [ Required, SqlComputeCode = { set {*} = $ZDATETIME($ZTIMESTAMP, 3, 1, 3)}, SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

They track object creation time and last change time. This works for both SQL and Object access.

That's an excellent approach. But it has its limits.

  • You require the freedom to add properties to the existing class /table 
  • to feed the Global ^OBJ.DSTIME you require the object ID. That's available for  %%UPDATE, not for %%INSERT
    see https://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=ROBJ_property_sqlcomputeonchange
    Any event-triggered computation occurs immediately before validation and normalization (which themselves are followed by writing the value to the database).
    so   You don't have yet the ID at insert time.
  • and there is evidently no chance for handling deletions.

so you really need to define your  triggers or have it in common for objects and SQL as in this example: 

Trigger AfterInsert [ Event = INSERT, Foreach = row/object, Time = AFTER ]
{
 // find id according to object or SQL
 set %ok=1 quit $$$OK
}

Trigger AfterUpdate [ Event = UPDATE, Foreach = row/object, Time = AFTER ]
{
    // find id according to object or SQL
   
if $d(%rowid) set id=%rowid
    else  if $isobject(pNew) set id=pNew.%Id()
  do whatever ....

 set %ok=1 quit $$$OK
}

Trigger AfteDelete [ Event = DELETE, Foreach = row/object, Time = AFTER ]
{
 // find id according to object or SQL
 set %ok=1 quit $$$OK
}

// find id according to object or SQL  
This highlights the fact that the ID is in different variables for object and SQL
and not all SQLTriggers have it in %rowid.
So real debugging is required.