DB issues and their solutions

Issues

• What should the database look like?
• How do you handle upgrades of the database structure?
• How do you handle upgrades when both MySQL, PostgreSQL, SQL server and Oracle is supposed to be able to be the database backend? (I'd say DB2 as well, only I haven't got any experience with it, or test installations)
• How do you test upgrades of the database structure?
• How do you test upgrades of the database structure against different backends?

Upgrading

For Unified BPM, the solution to the upgrade problem is two-fold:

1. A generalizing layer. A Database Abstraction Layer(DAL).
   


2. A step-based upgrade solution that uses that layer to apply upgrades.

In my experience, and as long as you stay away from stored procedures, functions and stuff like that, and only have a simple database structure(which Unified BPM is supposed to), most updates only need standard SQL syntax, like CREATE TABLE, ALTER TABLE, INSERT and such. And a "custom" which takes whatever needs to be done, one SQL for each backend. Hopefully, "custom" should be rarely used.
So it should not be very difficult to write a layer that takes parameters and transforms them into the mentioned flavours of SQL. Yeah I know. Famous last words. :-)
I also now that there have been some attempts made, however, I will have a somewhat narrower approach. No bells and whistles what so ever.
I have also made an XML schema that should contain steps, parameters and such so now I am writing a utility uses the database abstraction layer to apply those steps on the database. No GUI yet, but I know that it will be needed later on.

Testing

A *full* Unified BPM integration test run will start with "CREATE database", each time building and populating the database from scratch using the update utility and many of the access objects' unit tests. Possibly, in a few years, one can start with a later version.

Structure

The database structure will be an old favourite, a *really* basic tree. nodeid and parentnodeid

1. All objects will have a node in the tree. This makes the security layer easy to implement.
2. All other data will be in sub tables.
   
3. For auditing, the first, and hopefully last(fuzzy, see "custom" above, stuff to maintain on different backends) triggers of the database will be created and will.

Oh, well. Bye then!