Getting Things Done – with Dovetail ArchiveManager (DIET)

October 24, 2008

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.