The Data Model

The GNomEx Class Diagram reflects the underlying data model. Some of the table and class names are a bit different than the common term — the data base from which GNomEx first originated used different terms and was primarily an experiment order tracking system. Here are a few of the names that diverge from the concept:Request — This is the Experiment class and table. Request was meant to describe the 'order'.

RequestCategory — Experiment Platform.

Application — Experiment Type.

How to Modify the DB Schema

Adding a column to a table (or add a table) in the database

1. Add the column from the table in the upgrade sql script: gnomex_db_upgrade_to_##.#.#.sql. Remember that when you add a foreign key, you should be adding a foreign key constraint.
2. Modify the CREATE_TABLE statement in gnomex_db_ddl.sql. (Don’t forget the foreign key constraint when you are adding a foreign key column.)
3. If your feature depends on data being populated, add to the upgrade script (#1) and also add to gnomex_db_populate.sql
4. (FOR HCI DEVELOPERS ONLY) Make the schema changes to hci-dbdev-08. Remember that when you are adding a column to add the column to the audit table. If you don’t have schema write permissions, you can email Tarik Courdy ( tarik.courdy@hci.utah.edu) or Andy Hofer ( andy.hofer@hci.utah.edu) if I'm not around and you need the column added to hci-dbdev-08.

When removing or renaming a column or table in the database

1. Follow the steps provided above. If your transformations are more involved that a simple rename or remove, the upgrade script must be able to conserve the data already in production. The usual technique for this is to add new tables and then using a INSERT with SELECT to populate the new table before deleting the old one.
2. These changes must be carefully coordinated with the deployment. So please add a note to the RELEASE_NOTES describing the schema changes that must take place during the deployment.

Syncing the test and production databases (FOR HCI DEVELOPERS ONLY)

When we are ready to deploy to production, I ask our DBA’s to compare the schemas on test and production. They have this cool tool that allows them to apply the changes to production. If these are new columns or tables, they can make this change during the day. If we are removing or renaming columns or tables, the changes have to occur when the server is down and we are ready to deploy. That is where the upgrade (gnomex_db_upgrade_to_##.#.#.sql) script comes in handy. I can look over the changes in the script to make sure that all of the schema changes are performed on the production db.