Tilda JSON Syntax: History Pattern

<-- Object Syntax

History Pattern

Most enterprise application require changes to data to be audited, to maintain a history of the values in a record. With Tilda, it's very trivial to define a "sister" table that stays in sync with the master table and where copies of record changes can be kept.

     ,"history":{
         "postfix":"_Hist"
        ,"includedColumns"        :["colA", "colB", "col123*"]
        ,"excludedColumns"        :["col123A"]
        ,"signatureColumns"       :["colA", "colB", "col123A", "col123B", "col123C"]
        ,"signatureColumnsExclude":[]
       ]

// The default values are:
     ,"history":{
         "postfix"         :"_Hist"
        ,"includedColumns" :["*"]
        ,"signatureColumns":["*"]
       ]

// So it's easy to declare a default History simply by doing
     ,"history":{
       ]

Given a Table with the name 'TableA', a history table called 'TableA_Hist' will be created. All columns included, minus those excluded will be added for the new table. When generating a copy of the row, the status of the "signature" columns will be checked to indicate if a copy is needed: is none of the values for the signature columns have changed, then a copy is not needed. If changes are made to the master table, those changes will be reflected automatically to the History table and migrated automatically too.

Rules:

• Only NORMAL tables can have a history. WORM and READ_ONLY tables, by logical definition, cannot change.
• Only OCC tables can have a history. Without core timestamps, a history cannot be kept.
• The OCC columns 'created', 'lastUpdated' and 'deleted' are automatically added to the included columns.
• The OCC columns 'created' and 'lastUpdated' cannot be part of the Signature. If they change and nothing else change, there is no point in versioning the record and create a copy.
• The OCC column 'deleted' must be part of the signature for exactly the opposite reason: if it changes and nothing else changes, it is meaningful to the life-cycle semantics of the row. It will be added automatically.
• The primary key of the source table, and all unique indices, will be changed to regular indices by adding the "lastUpdated" column to their definition.

This feature is currently mostly application-level with a copyForHistory() method generated to handle all the necessary logic:

        TableA_Data A = TableA_Factory.lookupByRefnum(111);
        if (A.read(C) == false)
         throw new Exception("Failed reading TableA record 111.");
        // Make changes to A
        A.setColA("value1");
        A.setColB("value2");
        // Take care of history first
        TableA_Hist_Data A_Hist = A.copyForHistory();
        if (A_Hist != null && A_Hist.write(C) == false)
         throw new Exception("Failed writing a TableA_Hist record to the database");
        if (A.write(C) == false)
         throw new Exception("Failed updating a TableA record in the database");

In the future, such logic may make more sense to be available as a Trigger working all database-side.