The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Automatic updating database schemas

Hi folks,

maybe someone can enlighten me with some wisdom on this problem, i'm clueless...

So I have this Java application that can update itself by downloading a new configuration file and the corresponding jars from the internet. This is well tested and works like a charm.

But, there is a weak point: The application (a GUI client app) uses an in-process SQL database to store its data. The database schema is rather simple but huge. Therefore we use code generation to construct
a) The sql script building the database and
b) the persistence layer in Java.
This is done by specifying the db schema in XML which is then transformed to SQL rsp. Java with the help of two XSLT stylesheet.
This also works like a charm.

Here comes the problem: I need to find a safe(!!!) way to automatically update the database schema in a running live installation containing live data. The data is all on the local machine where the app is installed, there is no central DB server.

At the moment, you have to handcraft a "update SQL script" from looking at the different version of the XML file that specifyies the database. This has some disadvantages.
1) It's error-prone, because the information is usually only available in the XML file through diffs and the schema is huge.
2) If someone misses an update or two, you have to find a way, how to incorparate all DB updates done since his last update.
3) There are things you can't do with CREATE/DROP/ALTER TABLE commands and their friends (e.g. dropping a no longer needed column in a table that was once a NOT NULL column..). Well, this is _not_ the biggest problem. I could live with that.

I'm stuck here. The ideal solution would be an automatic diff between the different schema versions and an automatic creation of said SQL script, but I do not see any way to do this.

Any ideas on that?
Holger D. Send private email
Wednesday, February 09, 2005
 
 
If you change the schema at all, there will always be some sort of risk involved.

If you do it in an automated fashion and have tested it and confirmed the operation of it in advance, this should simplify things a bit, but it won't be foolproof.

I would do something as simple as creating the complete sql string, giving it an id, and sending that to the production system.  Then have the production system keep all previous actions in the database along with their id's.

If you're ever missing an id, you know which commands have to be run.
KC Send private email
Wednesday, February 09, 2005
 
 
Instead of updating the schema, create a new one everytime.
Copy all the data from the previous schema to the new one.
Assert that the operation is successful.
Delete old schema.
load new classes (update code)
if the assert fails, dont delete the old schema and dont update the code to the new version. maybe just show some message to the user saying update failed or something.
Developer Send private email
Wednesday, February 09, 2005
 
 
---- Developer wrote -----
Instead of updating the schema, create a new one everytime.
Copy all the data from the previous schema to the new one.
Assert that the operation is successful.
---------------------------

Yes, i evaluated this solution as well, in fact, this seems to be the most likely solution to be implemented. There are still scenarios in which this won't work, but I don't expect them to happen anyway.

Thanks for the reply,

Holger
Holger D. Send private email
Wednesday, February 09, 2005
 
 
------------- KC wrote------------------
I would do something as simple as creating the complete sql string, giving it an id, and sending that to the production system.  Then have the production system keep all previous actions in the database along with their id's.

If you're ever missing an id, you know which commands have to be run.
-----------------------------------------

This addresses point 2) of the original posting. Sounds more than reasonable to have some kind of versioning in the "update SQL".
Still it requires handcrafting the update sql....

I talked to some other people and no one came up with a solution that _really_ satisfies me. Sigh. Guess I have to live with it.
The thing is, the people that will do the updates in the future are perfectly able to manipulate the XML file, but SQL isn't on their "strong knowledge list".... Seems like they should learn it fast :-)

Thanks for the reply,

Holger
Holger D. Send private email
Wednesday, February 09, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz