In a similar upgrade situation, a solution I saw kept a table of
versions in the database.

Every upgrade was an incremental upgrade script - so if a version was
"missed" the process would start its upgrade incrementally from the last
version the user had and proceed until the database was current.

The test for "upgrade needed" was if the database version number (select
max (version) from versions) was less than the code version (compiled in
the application).

At that point, we would run incremental upgrade scripts starting from
the most recent version in the database, until the database version was
current with the code version.

In pseudocode:

dbVersion = fetchDatabaseVersion ();
while (dbVersion < getCodeVersion())
{
upgradeScriptName = "upgradeFrom"+dbVersion;

invoke (upgradeScriptName);

dbVersion = fetchDatabaseVersion ();
}

On Sun, 2005-09-18 at 08:44 +0200, Adrian Wreyford wrote:
> Thanks David
>
> The software is distributed to farmers. The software relies on the database
> for its functionality, and storage of individual farmer's data. They are not
> linked to a central database.
>
> As the software evolves, new features are added, which inevitably require
> either restructuring of the database, or adding new tables.
>
> Not everybody upgrades to newer versions, as newer features may at the time
> not interest them, but eventually do require upgrades as they are interested
> in newer features.
>
> This is when the scripts become complicated, if there is no way to know what
> version of database the client has, as they obviously don't want to loose
> data already entered.
>
> So my question still stands.
>
> Do I programmatically branch, and run different scripts, depending on the
> value of version field, or can I branch from within the scripts (ie test for
> value version within a script, and then branch accordingly)?
>
> Regards, and thanks for your very informative and thorough answer.
>
> Adrian