In previous part of blog I introduced the way of preparing tables in order to track data using date effective framework. This part is focusing on general operations with data inside date effective tables we prepared last time.
Updating record in date effective table
We can update record in date effective table by using three different modes, which are:
Correction, CreateNewTimePeriod and EffectiveBased. The most commonly used from them is the first one, because basically it allows developer to update record in date affective table as in the any other, ordinary table.
In CreateNewTimePeriod mode, developer can update only a record that is currently effective. It will cause this record to be expired and new record to be created with updated data and set to effective. Value of previous records ValidTo field depends on the ValidTimeStateFieldType. For UtcDateTime it will be current time minus 1 sec. and for Date it will be current day minus 1 day. Therefore ValidFrom field of newly created record will be set with a precision depending on field type as well.
EffectiveBased mode is combination of both Correction and CreateNewTimePeriod. When record being updated will be effective in future – correction mode is used. When record is currently effective it will use CreateNewTimePeriod for update. Important: You can't update records that was effective in the past in this mode.
Any try of updating date effective table without selecting update mode like below will cause error appearing on the screen.
ourTableName.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction);
Updating examples
Important: In all examples below index is set up to NOT allow gaps.
Correction mode
General rule is that every change for records in this mode will depend on chosen gap allowance. For no gaps selected, every date change will affect record that is adjacent to the one being updated.
Example 1: Changing ValidFrom date of the record between two other records.
In the table we have situation like this – no gaps allowed:
As we can see ValidTo of previous period has been automatically adjusted.
Example 2: Changing ValidTo dates on the beginning of the table.
Now after return to previous state we are changing ValidTo date of first period from 15/07 to 18/07.
- As we can notice, ValidFrom date of later period has been adjusted according to new ValidTo date.
CreateNewTimePeriod mode
Example 1: Changing value of field for a current active record.
This is initial state of table for this example:
Trying to update not-current record in CreateNewTimePeriod mode will result in following error:
"Cannot edit a record in MyTestTable (MyTestTable). Changing a record in change tracking mode that is active in the past or future is not allowed. Only current records can be revised."
Now I will try to change ValidFrom date of current period from 13/06 to 15/06 because in this mode effective date can be only changed to future or exact current date (Writing this example on 15/06/2015).
According to our expectations, a new record is inserted.
EffectiveBased mode
Example 1: Changing value of field of a current active record and a record in the future.
Now for EffectiveBased mode I returned to initial status from previous example:
First I will change ValidFrom value as in the example for CreateNewTimePeriod:
New record was inserted, so it works exactly the same way as for the CreateNewTimePeriod mode. Next I will update ValidTo date of the second record from 15/08 to 17/08.
We can see that period after updated one was adjusted the same way as in the Correction mode example.
Inserting into date effective table
It is not possible to insert new record between two existing periods. We can however insert record before the first one in the table or after the last one. We can also insert records that are partially overlapping with first or last record.
Examples of inserting into table
Example 1: Writing record on the beginning of table and on the end.
Table initial state:
Trying to insert a record between existing periods will result in following error:
"Cannot create a record in MyTestTable (MyTestTable). Insert not supported with the values specified for 'Effective' and 'Expiration'. New record overlaps with multiple existing records."
Although we can create record before first existing one ( 16/05 – 15/06):
Or after last one (16/09 – 15/10):
Example 2: Writing record that partially overlaps with existing one.
Using table from previous example we will insert additional two records that are partially overlapping with existing: 16/04 to – 17/05 and 13/10 – 15/11.
As we can see adjacent records are adjusted along with new records creation.
Delete record from date affective table
Deleting record from date effective table will cause adjustment of adjacent date period when the gap is not allowed. Adjustment will not take place when removed record was first or last in the table or when gaps are allowed.
Example 1: Delete record from between two other records.
For following example we will be using table with initial state like this:
Now we are going to remove marked in red above. As we can see, ValidTo date of previous record has been extended.
This is all about basic data operations in date effective framework. In the third part of this series I will describe shortly how to get data from date effective tables by using standard select statements and queries.
