Getting Things Done - with Dovetail ArchiveManager (DIET)
I am currently working on a project where I need to take a database from empty to usable with a script file. I am at the point where I need to import data files, and the data files I need to import don't exist. The customer has the data, and luckily they have a tool to create nice, tidy data files for me - Dovetail Software's Data Import Export Tool (DIET).
The challenge for me is to make it easy for them to use DIET, so I can get the data files that I need.
DIET is the perfect tool for the job, because it is designed to do exactly what I need. I need data from three related tables in the database for this file, so I created a directives file to specifically control what information to extract.
User-Defined Lists are one of the most challenging data structures within the database, but the directives file was pretty easy to build (with a little help from Gary Sherman). Here is the end result, which I will explain below:
EXPORT OBJECT hgbst_lst
UNIQUE_FIELD = "title"
ACTIONS = EXPORT, DELETE
TO hgbst_show THROUGH hgbst_lst2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
TO hgbst_show THROUGH prnt_chld2hgbst_show
ACTIONS = EXPORT, DELETE
TO hgbst_elm THROUGH hgbst_show2hgbst_elm
ACTIONS = EXPORT, DELETE
END_TO
END_TO
END_TO
END_TO
END_TO
END_TO;
At the top level, I want to export data from the hgbst_lst table, and then also export any related data from the hgbst_show and hgbst_elm tables at the lower levels. Since I want to be able to extract this data from one database and import it into a different database, I avoided using any unique record identifiers (objid's). Using "title" as the unique identifier for the hgbst_lst table prevents multiple copies from being imported into the database.
Each lower level is reached by the "TO <table> THROUGH <relation>" phrases, and that traverses through the parent-child relations that make up the user-defined list structure.
The batch file that I set up and used for this data extraction is the other key piece of the puzzle. Here it is, and again the explanation follows:
diet -user_name <USERNAME> -password <PASSWORD> -db_name <DATABASE> -license <LICENSE> -directive hgbst.dir -archive -export hgbst.dat -where "title IN ('WORKGROUP', 'Recipient')"
This batch file is built for an Oracle database. The command line options that were key were:
- -directive hgbst.dir - contains the directives file described above
- -archive - copies the data from the database but does not remove it
- -export hgbst.dat - specifies the file where the output data is written
- -where "title IN ('Msg Reason', 'Recipient', 'Reimbursement_addr')" - controls which lists are exported
The other command line options control the database connection. Each one has to have a valid value, but otherwise they are self-explanatory.
Here is a sample of the data that gets created:
OBJECT TYPE="hgbst_lst", NAME="hgbst_lst_268435457"
UNIQUE_FIELD=title
FIELDS
title = "WORKGROUP";
description = "Employee's work group designation";
deletable = 1;
END_FIELDS
RELATIONS
TO_NAME="hgbst_show_268435457" REL="hgbst_lst2hgbst_show";
END_RELATIONS
END_OBJECT NAME="hgbst_lst_268435457"
OBJECT TYPE="hgbst_show", NAME="hgbst_show_268435457"
FIELDS
last_mod_time = DATE "?/?/? ?:?:?";
title = "Level 1";
END_FIELDS
RELATIONS
TO_NAME="hgbst_elm_268435462" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435461" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435460" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435459" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435458" REL="hgbst_show2hgbst_elm";
TO_NAME="hgbst_elm_268435457" REL="hgbst_show2hgbst_elm";
END_RELATIONS
END_OBJECT NAME="hgbst_show_268435457"
OBJECT TYPE="hgbst_elm", NAME="hgbst_elm_268435457"
FIELDS
title = "Call Administrator";
state = "Default";
END_FIELDS
RELATIONS
TO_NAME="hgbst_show_268435457" REL="hgbst_elm2hgbst_show";
END_RELATIONS
END_OBJECT NAME="hgbst_elm_268435457"
Hopefully this will help get the data that you need out of your database - DIET can definitely do the job.
