How to go about catching database and software version mismatch on deployed application?

Question

I'm developing a standalone application that accesses a common database on a server. It will be used by a few dozen people in my organization.

I know that I will need to update the software and the design of the database. This creates the possibility that someone will perform a query on the database using old software.

I already have a system in place that will catch if the user tries to start up an out-of-date version of the application. However, this does not guard against someone who keeps the application up most of the time.

My idea was to put a version entry in the database (in tblVersion or something like that) and check it each time any record in any table in the DB (except tblVersion) is added, updated, or deleted, but not merely read.

This way I could catch things that (I think) could corrupt the DB even if the user has an out-of-date version of the SW: check the version in the DB against what's in the code, and disallow the add, update, or delete operation if there's a mismatch. At the same time, I wouldn't be adding the overhead of a lot of checks to tblVersion for DB reads.

My questions: Is this sound? Is there a better way to go about it? If so, what?

Thanks!

Answer 1

I think the tblVersion idea is probably fairly sound, but I'd consider checking it at start-up instead and immediately failing, rather than waiting for the user to attempt a write on the database.

There's a couple reasons this is beneficial:

1. You avoid the need to repeatedly check the version. You can do this once at startup and save quite a few queries, especially if lots of users are using this system.
2. You don't have to litter your data access layer with these checks.
3. Checking on write probably isn't the best user experience anyways. Users would be able to load a record, spend a few minutes performing some edits, and then have to abandon all of those because the database is out-of-date. If they aren't going to be able to do anything useful with the database, let them know ahead of time rather than after they finish their edits.

At a minimum, you should check at app start-up and warn the user that they won't be able to save anything as soon as they load the application.

Answer 2

The use case of a person having the app open for so long that a new, incompatible rollout of the app occurs and the database schema (or interpretation) is modified in a structurally compatible, but semantically incompatible way seems pretty narrow.

Having a schemaInfo table with and having two tuples, one with the current schema version and another with the last compatible version is going to catch the vast majority of cases while not overly complicating your application.

Checking on every write would be a horrible mess, if you're really concerned about the scenario, have a timer go off every hour or two that checks the table version and pops up a dialog warning the user to get a new version.

Even better you could simply bounce the DB forcing all existing sessions to disconnect once you've finished a schema change.

Answer 3

I think that could work.

However, I'd suggest something slightly different.

There are two parts to it:

1. Consider two types of updates -- required and optional. The idea would be that the software would more forcefully update itself when the update is required. And you as the developer would only flag an update as required if you think it needs to be. I work on a similar type of application and many updates that I see don't actually have anything to do with the database at all... and those are the type of updates that should be optional.
2. Add code to your application that queries your database every minute for the current version number. When the current running version is out-of-date and the latest update is required, then pop up a dialog box that requires the user to get the update. Depending on your application, you may also need to add code that allows the user to save their work.

I think if you followed this approach then you could update the application automatically during start-up no matter if the update is required or optional. But then while the user is actually using the application, I'd only pester the user about the required updates.