With five different (major) versions of Windows out there plus Service Packs, Personal Editions and Professional Editions, and three major versions of Firebird to consider (not to mention the old InterBase versions), upgrading an existing installation has now become something of a black art. In this article, I have attempted to summarise the contortions that my Firebird Server Merge Module goes through in order to provide a smooth upgrade under whatever conditions it finds.

When seamless Upgrade is not Possible

Over-writing an old installation without understanding how it was installed is usually a good way to go about breaking an existing system. For that basic reason you should not upgrade someone else's installation. It's better to just tell the user to uninstall the older application and let it uninstall itself - an activity it knows best. This also gives the user a chance to recognise a perhaps unexpected implication of installing your application and resolve any problems before they trash their system - and at least you gave them due warning.

However, that still leaves open the question as to how do you detect an existing installation.

An installation of the Firebird Server must try and detect: older installations of Firebird done by another installer, any installation of InterBase, as well as later installations of Firebird. We need to check for the latter because sometime in the future, some user may unwittingly try and install an old version of your application on a system on which an up-to-date version of the Firebird Server has already been installed. If you don't trap this then the resulting stealth downgrade will most likely not be appreciated.

The Windows Registry is usually the best place to detect an existing installation. InterBase and Firebird have used a series of well known registry keys that they have used to record their installation folders and we can use these to search for fbserver.exe, ibserver.exe or even fb_inet_server.exe. If we find anyone of these then we know that there is an existing InterBase or Firebird Server present.

It is also possible to search for a file that was installed by a specific Windows Installer component. We can use this to search for an existing installation that was done by our installation script. All components are identified by a globally unique GUID and searching on this for the Firebird Server allows us to identify an earlier installation that we can upgrade.

The Firebird Server Merge Modules thus start with a number of targetted searches. If an InterBase installation or a Firebird installation that we can't upgrade is found, a suitable error message is emitted and the install is aborted.

The one case that isn't trapped is when a later version of InterBase or Firebird installed under a new registry key and not one that we know about. This is a real possibility. InterBase has at least three times changed its registry key and Firebird twice. (from Firebird 1 to 1.5). The only way of avoiding this risk is a whole disk search for fbserver.exe, ibserver.exe or  fb_inet_server.exe. This is a lengthy process and still does not avoid all the risks, as it is still possible for someone to change the name of the server executable - and then we will never find it. My judgement call is that it is not worth forcing every installation of Firebird to be a marathon just in case some bright spark thinks it's a good idea to change the registry key yet again.  I appeal to the good sense of the Firebird and InterBase developers not to change the registry keys again and, if they do, to do it in a backwards compatible manner.

Upgrading From Firebird 1

Firebird 1 was really a re-branding of InterBase 6 and most of the installation file names reflect this fact. The server is still ibserver.exe and the security database is isc4.gdb, but it did have its own registry key "SOFTWARE\FirebirdSQL\Firebird\CurrentVersion". Upgrading to Firebird 1.5 or Firebird 2 iis largely a matter of uninstalling Firebird 1 and then copying the contents of the security database to the Firebird 1.5 security.fdb or the Firebird 2 security2.fdb. (This is also true if a real InterBase 6 installation is found).

If the upgrade is to Firebird 1.5 then this is as simple as copying the file and renaming it security.fdb. However, the upgrade to Firebird 2 is more complex as the database scheme and ODS have changed. We need to backup and restore the security database and then upgrade the schema.

Upgrading from Firebird 1.5 to Firebird 2

Firebird 1.5 introduces two new user configurable files: firebird.conf and aliases.conf. These should be preserved on upgrade as they may contain important user options - and losing them could cause difficult to diagnose problems. The security database - security.fdb - also needs to be upgraded and moved to security2.fdb. As discussed above, it has to be backed up, restored and then its schema upgraded.
In addition to all this, the default installation folder has changed from \Program Files\Firebird\Firebird_1_5 to \Program Files\Firebird\Firebird_2_0 so the files have to be physically moved or copied. It is not simply a matter of not over-writing critical files.

The Firebird Server Merge Module Upgrade Approach

There are two separate problems to solve. One is moving existing firebird.conf and aliases.conf files from Firebird 1.5 to a Firebird 2.0 installation and the other is upgrading the security database.

Moving existing firebird.conf and aliases.conf files

This problem only applies to a Firebird 2 installation and it is relatively easily solved. The installer first searches for these files using the Firebird registry key and, if they are found, they are copied to the Firebird 2 installation folder. Otherwise new copies are installed. A backup copy of the installation version is always installed with a suffix '.orig' as a reference version. The files are also marked and "permanent" and "never overwrite" so user edits are not lost in future ugprades.

Upgrading the Security Database

Ideally a single strategy would be available for all cases. However, I have not been able to find a single reliable approach that works across all versions of Windows and which also allows file permissions to be correctly set in the lock down versions. Instead, different stategies are used under Windows 9X compared with later versions of Windows.

Under Windows NT4 and later, a vanilla security database is always installed by the installer. However, it does also check for an existing isc4.gdb security database, or, when Firebird 2 is being installed, for a Firebird 1.5 security.fdb security database. If one of these is found then an upgrade script is run.

The upgrade script is embedded in the Merge Module and a self-extracting executable. The Open Source 7-zip is used for this purpose and this creates a self-extractor that extracts its files into a temporary folder, runs a nominated application, and then deletes the temporary folder before terminating. The self-extracting executable contains the embedded Firebird Server, gbak, isql (Firebird 2 only) , a script (.bat) file and the upgrade SQL script (Firebird 2 only). The self-extracting executable is run after the initial installation is complete and the upgrade script will run gbak to backup and restore the database and, if necessary,  run isql to upgrade the schema.

Using the embedded server frees us from the problem of persuading Firebird to update its own security database and avoids a need to know the SYDBA password. gbak restores the database directory as security.fdb or security2.fdb using the -REP switch and this ensures that it takes on the correct user access permissions i.e. those set by the installer.

The application run by the self-extractor is "cmd.exe" i.e. the Windows command line interpreter and this executes the batch script. This is better than trying to run the .bat file directly as Windows will use shellex to do this and this has been found to be problematic under NT4 with long file names escaped by double quotes.

This same issues of  long file names escaped by double quotes is why the strategy does not work for Windows 9X. command.com, the old MSDOS command line interpreter seems to limit long file name to 32 characters when used as .bat file parameters and this is too short for reliable operation of the backup script. On the other hand, because Firebird always runs as an application under Windows 9X and hence the script can easily stop and start the server, a different approach is possible.

This is to directly script the upgrade process as Windows Installer Custom Actions, running gbak (and isql) as separate steps. This works, however, it uses the newly installed server to perform the operations and hence it is not possible to restore the database to its correct location in one step. Instead, we have to restore it to a temporary location, stop the server, move the database to its correct location, and then restart the server.

This strategy works fine under Windows 9X, but does not easily translate to the other versions of Windows which run the Firebird Server as a Windows Service. Moving the database  rather than replacing the existing database also does not allow a easy strategy for setting correct user access permissions in the lock down version. Thus the two strategy approach seems to be the best compromise.