Versioned data storage in Caché Persistent classes

In the standard Caché stored classes, when modifying a record, the previous property values ​​disappear irrevocably. But there are cases when it is undesirable when "all moves must be recorded." First of all, of course, such a requirement arises when developing applications for materially responsible persons, for whom the possibility is critical, for example, to cancel an erroneous action and restore the document state for a specified time, or, more importantly, to investigate an attacker’s attempt to “cover up "In the database.
This article demonstrates how to implement version storage and recovery for Caché objects.

This functionality can be added to Persistent-classes using the% OnBeforeSave () method and SQL trigger on the Update event. Since in the overwhelming majority of cases of using Persistent-classes, the entire record, including the values ​​of properties - arrays and lists, is stored in the global storage node of the form ^ ClassData (id), before overwriting an object, it is possible to save the previous version of the record in some secluded place - like a "basket" in Windows.
Attached to this article is an example of using the above described feature, consisting of the abstract class PersHist - the heir of% Persistent, to which the necessary methods are added, and the demonstration class TestPersHist, in which there is absolutely nothing unusual except that he is the heir of PersHist, and not directly% Persistent. In exactly the same way, it is possible to create and modify records in it, both by object and by SQL access, but the overwritten data does not disappear there without a trace and can be restored.



I will briefly comment on the methods added to the PersHist class.
Class method GetDataLocation () - reads the description of a class-successor and returns the name of the storage global.
The class method SaveLastRevision (id) is the central figure of our class. In an amicable way, it should be made private, but in this case it cannot be called from the SQL trigger. This method copies the record from the global node ^ ClassData (id) to ^ ClassData ("History", id, saved_version_id, $ ZTIMESTAMP). Naturally, instead of ^ ClassData, the real name of the storage global returned by the GetDataLocation () method is used.
The private method% OnBeforeSave () - calls SaveLastRevision () only when the% Save () method performs the actual modification of the record already stored in the database.
The OnBeforeSaveForSQL () SQL trigger does the same.
And, finally, to restore data to a state that is current at a given point in time in the $ ZTIMESTAMP format (more precisely, $ ZU (188) - local time $ ZTIMESTAMP), the MakeActual (timestamp) method is used. This method looks at whether the record was updated exactly at the specified time or after it, and, if overwritten, saves the current version of the record and restores the previously saved one. For example,

do oref . MakeActual (( $ h -7) _ "," _ (12.5 * 3600))

will return the record to the state in which it was a week ago at 12:30, and, of course, it will save a copy of the state of this record at the time of its appeal.
If you need a complete list of saves of this object, then the following sequence of calls is proposed.

s ids = "" f s ids = oref . OrderIdSave ( ids ) q : ids = "" w ids,!

will give all identifiers of the saved versions. And the method

write oref . GetTimeStampByIdSave ( ids )

will return the exact time saving the version of interest. However, you can restore it directly by the identifier:

do oref . MakeActualByIdSave ( ids )

But it would be illogical to save all changes when modifying a record, and at the same time allow it to permanently lose this record in case of its deletion. The% OnDelete () private method and the OnBeforeDeleteForSQL SQL trigger insure against this trouble, which will allow you to restore a deleted record using the class method UndeleteId (id) if necessary.
How to calculate the identifier of the record to be restored is a non-trivial question, and it should be decided, apparently, at the discretion of the developer of the final application. But in any case, you need the ability to navigate through remote records. And this opportunity is provided by the OrderDeletedId (id, dir) method, which, similarly to the well-known $ ORDER () function, returns the nearest next or previous (with dir = -1) record identifier deleted but kept in history.

The TestPersHist demonstration class inherited from PersHist contains nothing but properties in the format of a regular field, list, and array. It is proposed to randomly create and modify entries of this class (for example, in the class comments), then direct viewing the storage global ^ User.TestPersHistD using Caché regular tools to monitor changes in the content of the storage global and see for yourself that all editions of all properties are stored in the ^ User branch .TestPersHistD ("History").

The code for the classes PersHist and TestPersHist in the form ready for import into Caché is available for download at this link : in CDL format for Caché version 5.0 and earlier, in XML format for modern ones.