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
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
^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:
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.
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.
so you really need to define your triggers or have it in common for objects and SQL as in this example:
{
// 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.
Hi Robert,
One thing you might want to include is a warning about using this + DeepSee. DeepSee will remove entries from this global during a synchronize. It looks like you also have some code to clean up entries. It appears that they will negatively interact with each other.
@Peter Steiwer
Thanks for mentioning it. This would be of course a conflicting case.