7.5 Importing data from conventional relational databases

This section describes how to write common Tcl applications for importing data from standard relational tables. You must be able to produce an Ascii table with columns separated by some character in order to take advantage of these methods.

7.5.1 Single tables

Single tables are the easiest to translate; typically they will translate into Qddb format on a row-by-row basis. Each field in the table should have a corresponding attribute in the Qddb Schema.

Suppose you have the schema

and you are translating from a table of unique rows of the following format:

Further suppose that you can export the data into a form that is separated by the character ``@'' and ``@'' cannot occur in any attribute's value. Some sample data might look like:

The first step is to build a file, say ExportFile, containing the table in the above form. Next, you must map the exported data into the Qddb Schema that you have created. In our example, the Name and Address attributes map directly. The Phone attribute, however, must have two instances in the Qddb tuple rather than two explicit attributes as is in the data. The Phone.Desc attribute must be provided because our Qddb schema does not distinquish between ``Home Phone'' and ``Work Phone'' explicitly. Here is a Qddb Tcl script that does the translation.

    #!/usr/local/qddb/bin/qtclsh
    set s [qddb_schema open Relation]
    set fd [open ExportFile r]
    while {[gets $fd var] != -1} {
        set var [split $var @]
        set t [qddb_tuple new $s]
        set v [qddb_view define $t {
            { Name arr(Name) }
            { Address arr(Address) }
            { Phone.Desc arr(Phone.Desc) }
            { Phone.Number arr(Phone.Number) }
        }]
        set arr(Name) [lindex $var 0]
        set arr(Address) [lindex $var 1]
        set arr(Phone.Desc) Home
        set arr(Phone.Number) [lindex $var 2]
        qddb_instance switch $v Phone \
            [qddb_instance new $v Phone]
        set arr(Phone.Desc) Work
        set arr(Phone.Number) [lindex $var 3]
        qddb_tuple write $t  ;# saves tuple to disk
        qddb_tuple delete $t ;# frees up storage
    }

First, we open the ExportFile and the Qddb schema. Next, we read each line in the ExportFile and split the row into its components with the Tcl command split. A new Qddb tuple is created with qddb_tuple new and a view is created into the tuple. For each row, there are two phone numbers. We must first set the Tcl variables in the view to the corresponding data in the row, and then create a new instance of the Phone attribute. Finally, we (1) set the new instance to be the current instance in the view, (2) set the new instance's Tcl variables, and (3) write the tuple.

7.5.2 Multiple tables with a unique key

Multiple tables must have a unique key on which to join the tables before translating them into Qddb format. Typically, what you want to do is read lines from each exported file (much like in a single table translation) but keep them in memory. Use the unique key as an index into an array for each table, then after all tables have been read, merge them into the Qddb database as appropriate.

Example:

Suppose we have two relational tables, Client and Address:

The equivalent Qddb Schema might look like:

    # Qddb Schema for the "Client" relation
    Use reduced attribute identifiers
    Use cached hashing
    CacheSize = 1000
    HashSize = 10000

    Name (First Last)
    Address (Street City State Zip) *

To perform the translation into Qddb tuples, we must first join the tables based on the link field Id. Assuming the Client table and the Address table are in the files Client.table and Addr.table respectively, the following Tcl code accomplishes this task:

    set fd_c [open Client.table r]
    while {[gets $fd_c str] != -1} {
        set l [split $str ,] ;# ',' separates fields
        set gv_client([lindex $l 2]) $str
    }
    close $fd_c
    set fd_a [open Addr.table r]
    while {[gets $fd_a str] != -1} {
        set l [split $str ,] ;# ',' separates fields
        lappend gv_addrs([lindex $l 5]) $str
    }
    foreach i [qddb_schema leaves $s] {
        lappend attr_pairs [list $i gv_attr($i)]
    } ;# build a list of {attr,var} pairs

We first read the entire contents of the Client.table and Addr.table files. Each line of the Client.table file will eventually become a unique Qddb tuple, so we build a Tcl array (gv_client) to temporarily hold the values and use the unique Id as an index. Each line of the Addr.table file is then read and appended to another Tcl array gv_addrs, where each element is a list of rows (from Addr.table) for a unique Id. Finally, we iterate through the indices of the gv_client array, building a new tuple for each element. To build the addresses for a particular client, we look at the gv_addrs array element with the same index. If a client has more than one address, we must build and initialize a new instance of Address in the current Qddb tuple. To build all the tuples from our Tcl arrays, we do the following:

    set s [qddb_schema open Client]
    foreach i [array names gv_client] {
        set t [qddb_tuple new $s] ;# new tuple
        set v [qddb_view define $t $attr_pairs]
        initClientProc $i
        if [info exists gv_addrs($i)] {
            set addrs [array names gv_addrs($i)]
            initAddrProc [lindex $addrs 0]
            foreach j [lrange $addrs 1 end] {
                qddb_instance switch $v Address \
                    [qddb_instance new $v Address]
                initAddrProc $j
            }
        }
        qddb_tuple write $t
        qddb_tuple delete $t ;# delete in-memory copy
    }
    qddb_schema close $s

For clarity, we have separated the Tcl procedures responsible for initializing values. Notice the view's Tcl variable (gv_attr) must be declared global within any Tcl procedure:

    proc initClientProc {idx} {
        global gv_client gv_attr
        set gv_attr(Name.First) [lindex $gv_client($idx) 0]
        set gv_attr(Name.Last) [lindex $gv_client($idx) 1]
    }
    proc initAddrProc {idx} {
        global gv_client gv_attr
        set gv_attr(Address.Street) [lindex $gv_addrs($idx) 0]
        append gv_attr(Address.Street) "\n[lindex $gv_addrs($idx) 1]"
        set gv_attr(Address.City) [lindex $gv_addrs($idx) 2]
        set gv_attr(Address.State) [lindex $gv_addrs($idx) 3]
        set gv_attr(Address.Zip) [lindex $gv_addrs($idx) 4]
    }