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.

Installing Versioned Database Updates

We are in a situation where we have started to handle multiple versions of a platform within an orginization.  Handeling the upgrades of application is easy....if the version currently installed is older than the version being installed...replace it. 

However, handeling the database updates is a bit more challenging.  We currently handle all database updates in a single update script.  But we are going to have to change this model since our current script is not version aware.  For example, if we had 3 existing databases which were version 1.0, 1.1, 1.2, our update script would have all of the changes needed to bring version 1.0 to 1.2 without any problems.  But if we tried to run that same script on a version 1.1 database, obviously there would be a failure as the 1.1 changes already exist.

The only thing I could think of was breaking the each version update into it's own script and then forcing the user to sequentially run all the updates.  But I'm sure there must be a better solution out there, commercial software seems to have tackled this problem a long time ago without any issues.

Any ideas or methods for handling this situation would be appreciated!
Aaron Smith Send private email
Thursday, July 27, 2006

I've got a solution that's working for me, at least in the test lab.  It hasn't spent a lot of time in the real world and it might fall down there.

1. Track the version number of the database somewhere, possibly in the database.

2. On the first run, check to see if the database version is older than the application version.

3. If there is a discrepancy between database version and software version, run all of the scripts that have been identified as being for database versions greater than the current database version.

4. Update the database with the current software version, assuming that all updates took without error.

If you want to see the code in practice drop me a private mail and I'll send you the source.
Clay Dowling Send private email
Thursday, July 27, 2006
Using a database version number stored in the database is exactly how SAP deals with this sort of thing (at least, with the bit of SAP I work with in my day job), so you're in good hands with this approach.
Darren Hague Send private email
Thursday, July 27, 2006
That's what I do.

I have a create script for the database. The last line in the create script sets the database version (this is stored in the system keys table).

The program has a database version constant that has the same value as the version number in the create script.

I have an update file per database version (update-1.sql, update-2.sql and so on) and the last line in each file is a statement that updates the database version. The last line in the most recent file updates the  database version to the same number as the current version number in the create script.

When I want to alter the database I make the change to the create script and add statements to the most recent database update file to effect the change on existing databases. I put the statements just above the line that sets the database version. Also, I manually run the statements on the development databases.

After I release a new version I increment the database version in the program and create script and create a new update file and put the update version statement in it.

When the program runs it checks the database version and if it has the version update files then it runs them in order, starting with the number above the current database version and continuing until the number of the required database version.

There's only about 50 lines of code in the entire application that is related to updating the database.
cja Send private email
Thursday, July 27, 2006
I do the same as some of the previous suggestions: have an automated mechanism to apply updates that bring the database up to the appropriate level.

I actually posted an article on my blog a while back about how I do this:

It's a little more elaborate than some of the other posters have suggested because I actually back up the database, pull it back into a second database and do the migration there, so if something fails the live database hasn't been touched.
Andrew Lighten Send private email
Thursday, July 27, 2006
Hard to argue w/4 logical replies answering in unison!!

I had been hung up thinking that a Setup.exe needed to be the app executing the script and so had not even considered the possibility of the application doing it. Now that you bring it up though, it completely makes sense.

So that brings me to my next question:  How do you execute Update1.SQL, Update2.SQL, etc against the database? ADO.Net won't work for this will it?  I think SQL-DMO would, but the application may not be located on server so SQL-DMO may not be available.

FYI - Your responses so far have gotten me farther in 6 hours then I have been able to get in 2 weeks of worried googling.  Thanks!
Aaron Smith Send private email
Thursday, July 27, 2006
Read the files' contents into a string and just make it hte CommmandText for your SqlCommand object
... Send private email
Thursday, July 27, 2006
I wish a SQLCommand would work as that would make things really easy, but it has all sorts of syntax issues with the structure of a .SQL file.  It doesn't like spaces, the word "go", and the lack of semi-colon's separating each statement.

Obviously I could parse these files into individual statements, but then I would have to have my program in charge of the commits.  My preference would be to pass the whole script to the database and let the database take care of the commit or roll back.  I know it can be done with the likes of OSQL, but I'm sure there is a library that allows this without having to resort to a command line tool.
Aaron Smith Send private email
Friday, July 28, 2006
Aaron, getting your program to handle the commits is pretty trivial.  Just commit once after each string.  If you look at the code I sent you that's what I'm up to.  If you need a larger transaction scope you can put the BEGIN and COMMIT statements into the list of SQL commands to run.
Clay Dowling Send private email
Friday, July 28, 2006

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

Other recent topics Other recent topics
Powered by FogBugz