Managing static data

Question

Hi,

I'm looking for a Java (or Groovy) solution for managing static data i.e. data in infrequently changing tables such as a list of countries.

I would like to keep the list of countries in a text file and have the tables automatically updated when the server starts. I can't simply delete all the tables, then re-insert the data in the text files because this would violate key constraints.

DbUnit is a library which supports loading data into a DB from an XML file. It has a refresh operation which almost does what I need. This will insert any rows that are in the file but not in the DB, update any rows in the DB which are also in the file, but it doesn't delete any rows that are in the DB but not in the file.

Has anyone found a good solution for this problem?

Thanks, Don

Answer 1

There are several open source dbms systems, and dbunit looks reasonable.

I'm puzzled as to why you want to keep your master copy as a text file - why not use the database as a master?

Answer 2

If you're using Hibernate, you can use Castor to load an XML file of countries (for instance) into your Hibernate model objects, then persist those objects.

You may have to manage the insert/update/delete logic on your own...which can be summarized with this pseudocode:

• Load XML into Hibernate objects via Castor
• For each object:
  • If object is on the DB, invoke an update
  • If object is not on the DB, invoke save
• then for each record on the DB
  • Compare each record to your list of objects and delete if not found in your list

I hope that's clear...we have a similar process where a user will upload an XML file to refresh the DB.

Answer 3

I've tackled this problem before by deferring contraints.

Normally, constraints are checked as data is inserted or updated in the row. When a row is inserted, all the constraints are checked and the row either accepted or rejected. You can, however, defer the constraint checking until the transaction commits. see:

http://www.remote-dba.cc/t_garmany_easysql_deferred_constraints.htm

This is vendor specific behaviour. It works in oracle but to the best of my knowledge is not supported in mysql.