Myles Wakeham wrote:

> I'm sure I'm not the only person to be struggling with finding a strategy
> for this, so I was hoping anyone out there who is currently dealing with a
> Firebird SQL database and client applications in production could share
> their strategy for dealing with this, and how they handle updates, etc.

I have a simple table in database called "current_version" with fields
like "database_version" and "last_update". Database version is a simple
integer, and last_update is a timestamp and it's optional.

Each client app. has sql scripts embedded in executable (alternatively,
you can ship separate .sql files) that can update the database to any
version. So, while developing the application, I record each DDL
statement and some important DML statements and increment version number
by one for each of them. This is largely automated by FlameRobin which
has a feature to log incremental statements to files.

So, the client app. holds database version it needs (a simple integer)
and a bunch of scripts that can upgrade any database to that version.

Whenever client app is run, it does

SELECT database_version FROM current_version;

If it is lower than version number hardcoded in app. it starts running
.sql scripts one by one and subsequently runs UPDATE current_version =
:number_of_script.

If it is higher than version number hardcoded in app., the app. refuses
to run, and needs to be upgraded first.

Using this solution, you only get a single check - reading a one-row
table. It works very fine for few years now. The only problem I ran into
is adding foreign keys, but there are workarounds for that.

--
Milan Babuskov
http://fbexport.sourceforge.net
http://www.flamerobin.org