WebGUI's Thingy is a great tool for building relational databases. It has the ability to import data from external tables into individual Things, but the import capabilities are limited to the data in individual tables; there is no way to re-establish links between tables when importing data from an external relational database (at least as of WebGUI 7.9). This script can be used to re-establish these links within Thingy's internal linking system, allowing for legacy systems to be imported into WebGUI's Thingy asset.

The script works by identifying the key fields that linked data tables in the original database, using the old key values to lookup linked records, and then establishes new links between the records using WebGUI's internal ID fields. The original key fields are maintained for archival purposes, but can be deleted once the import process is complete. Once the historic data are imported, data managers can switch to WebGUI's regular Thingy interface for adding and editing data.

The script works on one link at a time, so if you have tables that link to more than one other table, you must repeat the script once for each link you need to establish. It is important to remember that when establishing links, one of the two tables is the "data table" and the other the "lookup table". During the execution of the script, the lookup table is not changed -- it simply holds values to which the data table will be linked. The data table holds one or more "foreign key fields" which are the values that link its records to individual records in the lookup table(s).

The basic steps for using this script are:

1. Create a Thingy that replicates all the Things in the original database. Each table should have a corresponding Thing and each Thing should have fields that correspond to the columns/fields in the original database. Be sure to include the key fields that are used to link the tables. Links between tables should be simple: one field in each of two linked tables holds a key value that identifies which record in table A is linked to which record in table B.
   
   (This works for 1-to-1 and many-to-1 relationships. Many-to-many relationships must be created with an intermediary table of many-to-1 relationships.)
   
   
2. Export data from the old database into CSV files, formatted for import into Things. (This may involve some data conversion that is beyond the scope of this documentation.)
   
   
3. Import the data into the associated Things.
   
   
4. Add new linking fields that will recreate the old links between tables (Things). It is important that these fields be WebGUI's own Thing-linking fields in order to establish proper data type and to ensure WebGUI recognizes them as Thing links for proper editing and display.
   
   (This step can be performed during the first step, when creating Things, but waiting until afterwards makes the data import stage a little less cluttered and error-prone.)
   
   
5. Copy thingyLinker.pl to /data/WebGUI/sbin (if you are running the WRE).
   
   
6. Don't forget to run setenvironment, if you haven't already done so:
   
   . /data/wre/sbin/setenvironment (space between period and the rest)
   
   
7. On the command line, read the documentation provided in the script for specifics on how to run the scripit. You will need to specify a number of parameters, including the Thingy and Things being linked as well as the fields in each Thing involved in the particular link being established.
   
   perl thingyLinker.pl --man
   
   
8. Test your first link. The script will let you know when you have set all the parameters necessary for it to run. By default, it only analyzes the tables to be linked and reports whether there are any problems with the data. The --verbose flag will report on each and every record in the table being linked, so with large datasets, you may wish to add the --limit flag to preview a subset of the output.
   
   
9. Assess and correct data as necessary. You may find that some records in the data table have no match in the lookup table. Depending on the history of the dataset, those may be OK to leave in place, or you may want to delete them. You may also find some records have already been linked internally; this can happen if you have gone into the web interface for Thingy and viewed, then saved records. You need to decide if you want to leave the current internal links (the default behavior) or if you want to override them and set new links based on the original dataset, in which case you should set the --overwrite flag.
   
   
10. Do it! Once you are confident that you are ready to proceed, add the --doit flag and the script will proceed to update the internal WebGUI links between the tables. If you'd like one last sanity check, you can add the --limit flag to only update a few records and look at the results. You can always --overwrite these tests when you run the script the next time.
    
    
11. Repeat. If you have more than one link between tables to update, repeat steps 7 through 10 until all links are re-established.

Comments

knowmad: "Hey Trex,

Thanks for offering this add-on to the community. It looks like this script is intended to be run manually. I'm curious if you think it'd be safe to run this automate it.

William"

Trex: "I'm sure it could be used to create an automated system. It would be nice to be able to specify links between tables during the import into Thingy.

However, without that capability, I prefer the manual process because it gives me a chance to review the cleanliness of the data and find potential problems prior to committing to the changes."