Upgrading database application data

Question

How does the data in a database in the field get upgraded because a new version of the software is being released? For an example, consider look up data. Usually the look up data associates an ID (number) to a name (string) and then this ID is referenced in other tables instead of the the ID directly (i.e. normalized). There are other types data that can be upgraded too.

In my experience the answer to this question is "with difficulty". Are there any tools that can assist in creating data upgrade scripts? For example, I use Toad's schema compare tool to generate schema upgrade scripts but it does not do the data. Would an ORM tool help here? What other ways are there to achieve this?

Answer 1

I'm currently evaluating Schema Compare for Oracle by Red Gate although it's not free and it's still in early development.

If data is your only concern, I normally work with normalized SQL dumps (i.e., one line per row, sorted by primary key). I store them in my version control repository and I can use any standard file compare tool to track changes. It's probably an ungly method but it has the advantage of its extreme simplicity :)

Answer 2

You would update data by writing update statments in a script. However, why you would want to update lookup data and then change existing records is beyond me. If you want the value to chance, then you should properly use a PK/FK design where the id value is stored not the text. Then you only need to update the lookup table for all the queries to reflect this. If you are storing the text, that is normally for historical reasons and you would wan to keep the old value. For instance syuppose we are talking about a parts list. A part called a J2890 was ordered in 2008. In 2010 the part number for that part changed to JS390, you would not want to change the order detail table because that isn't the part that was actually shipped. That is why you stored the name and not the id inteh first place.

Answer 3

I use XSql. It compare both schema and data and produce scripts to synchronize your customer database. There also a free lite edition with some limitation (it compare no more than 25 table, 40 views etc.) that for me is sufficient. Anyway if you have more objects to compare it's not to expensive. Also I use XPO from DevExpress, witch can upgrade database schema from older version db, but I prefer to see the scripts I run on my customer db, just to be sure that I don't accidentally delete important data.

Answer 4

Hi there,

Red Gate now has a product for comparing and deploying data changes. You can download and use the EAP for free from - http://www.red-gate.com/products/data_compare_for_oracle_eap/index.htm

Product will be released Q4 2010

Regards,

Tom Harris, Red Gate