Warning
Please note this is an experimental facility and is subject to a number of caveats (see below). Please always take a backup of your database before trying.
We very much welcome feedback, discussion and additional patches for this. Many thanks to Joe Carlson of the DOE Joint Genome Institute for the idea and implementation!
Traditionally, once the data for a mine has been built, it can only be updated by a complete rebuild. However, sometimes, after a long loading process, you see that something is not right: perhaps a minor issue such as a typo in a name, or perhaps something more major such as errors in an entire dataset. Rather than rebuilding the entire mine from scratch, a process that can take many hours or even many days, you’d like to make changes to your existing data build.
Making such updates requires co-ordinated changes to a number of InterMine tables. For instance, to update a value, one needs to at least:
One way to do this is by installing triggers into the PostgreSQL database that will co-ordinate these updates. InterMine can now generate such triggers if you invoke the ant generate-update-triggers in your mine’s dbmodel/ directory like so:
cd $MINE
./gradlew generateUpdateTriggers
This will generate two SQL files in the build/model/ subdirectory
add-update-triggers.sql
remove-update-triggers.sql
add-update-triggers.sql contains the SQL triggers necessary to co-ordinate table updates. remove-update-triggers.sql contains the removal code. All the triggers have a prefix of im_.
To add the triggers just execute add-update-triggers.sql using psql like so
psql -f add-update-triggers.sql MINE-NAME
You can now do basic create/update/delete operations such as:
The triggers propogate the operations to the superclasses and InterMineObjec tables
Tables have default values supplied for id and class, so you can create new records
The id is supplied from a sequence im_post_build_insert_serial which is initially set to the maximum id of InterMineObject.
Once you’ve completed update operations, you must remove the triggers. Failure to do so may cause interference with InterMine’s run time serial use, though this point needs to be clarified.
You can remove triggers by executing the remove-update-triggers.sql SQL:
psql -f remove-update-triggers.sql MINE-NAME
Please note that there are a number of database changes that the triggers CANNOT handle as of yet: