DIET: DELETE + INSERT instead of UPDATE
When we have to insert, update, or delete data, DIET (Dovetail ArchiveManager) is often the tool of choice.
Updating an object
Typically, when we want to update some data, we just need to define its uniqueness properly.
For example, if I wanted to update a business rule property name, I could define its uniqueness as a combination of its object type + name.
For example:
OBJECT TYPE="prop_name", NAME="commitment case id"
UNIQUE_FIELD=obj_type, prop_name
FIELDS
obj_type=30; /* object type 30 = commit_log */
prop_name="Case ID";
path_name="case_commit2case:id_number";
subtype=0;
val_type=0;
max_len=255;
END_FIELDS
END_OBJECT NAME="commitment case id"
If I used DIET to import this file, and this prop_name record didn’t exist in the database, then it would do an INSERT, which would create this prop_name record.
If I used DIET to import this file, and this prop_name record did exist in the database, then it would do an UPDATE, which would update the existing prop_name record. This would allow me to update the path or max length of that rule property.
This syntax allows us to update the non-unique fields of a record.
Updating the unique fields
Updating a unique field on an object is not always as straight-forward.
A common way to do this is to delete the existing record by using the PURGE option of DIET, and then doing a second DIET IMPORT run to insert the new record. It’s two operations, but it works. This only works if you don’t have any foreign keys that reference the object that you’re deleting.
A second option is to use objid as a unique field, as that doesn’t change. But use of objids can cause problems when using the same file on different databases. So the import file may work in your development database, but not production.
A third option is a seldom-used feature of DIET, which allows a DELETE + an INSERT in one operation. This is a cool little trick that not everyone knows about. Again, this only works if you don’t have any foreign keys that reference the object that you’re deleting.
Update daylight savings time records
A real-world example of this is updating the daylight savings time records. When the start and end dates for daylight savings changed starting in 2007, those daylight_hr records needed to be updated.
The uniqueness for daylight_hr records was the combination of start_time + the relation to the time_zone.
But since we want to change the start_time (and end_time), we can’t use the standard syntax. And since no other tables have foreign keys that point to daylight_hr, we can do this DELETE + INSERT trick in one file.
Here’s an example:
DELETE TYPE="daylight_hr", NAME="delete_2007_Pacific_Standard_Time" UNIQUE_FIELD=start_time UNIQUE_RELATION=daylight_hr2time_zone FIELDS start_time = DATE "04/01/2007 02:00:00"; END_FIELDS RELATIONS TO_NAME="Pacific_Standard_Time" REL="daylight_hr2time_zone"; END_RELATIONS END_DELETE NAME="delete_2007_Pacific_Standard_Time" OBJECT TYPE="daylight_hr", NAME="insert_2007_Pacific_Standard_Time" UNIQUE_FIELD=start_time UNIQUE_RELATION=daylight_hr2time_zone FIELDS start_time = DATE "03/11/2007 02:00:00"; end_time = DATE "11/04/2007 02:00:00"; END_FIELDS RELATIONS TO_NAME="Pacific_Standard_Time" REL="daylight_hr2time_zone"; END_RELATIONS END_OBJECT NAME="insert_2007_Pacific_Standard_Time"
We can have all that information in one DAT file, and use DIET’s import option. DIET will DELETE the old daylight_hr record, INSERT a new one with the correct dates, and RELATE it to the correct time_zone.
And we can do this with just one DAT file, and one execution run of DIET. Pretty cool.
As I mentioned, that DELETE element is rarely used, but can be super useful in certain circumstances. The DIET documentation has more details on the DELETE element.
Just one more tool to add to your bag of tricks.
