----- Original Message -----
From: "Myles Wakeham" <myles@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, August 02, 2004 9:33 AM
Subject: [firebird-support] Looking for some suggestions...Vertical market
apps


> We have a vertical market product that we are converting to Firebird. The
> database environment it is being converted from automatically handles
> changes to the schema of the database from version to version, however I
> suspect that we will have to do this ourselves with Firebird.
>
> I would like to know, from other vertical market developers who are using
> Firebird, how they are handling changes in the SQL Schemas when releasing
> updates of their software to their customers? Are there any tools that
make
> this process easy to do, or do you have to write this yourself?
>

We use a database version number in the database as well. In our case, in
the CompanyInfo table and called, well, DatabaseVersion. So the process is
thus:

1. Customer executes a "Software Update Check" from the back office module
of our software.

2. Software downloads an ini file from our website which includes the new
version number for our software update as well as the minimum database
version required to use the updates.

3. If the customer's current db version is less than what is required, we
shell out to a small application that I wrote using D6/IBO that downloads a
script file that looks like so:

#8.9%
// DDL Statements
&DML Statements
#9.0%

// DDL Statements
&DML Statements

The program then stores the strings from the file downloaded into two
stringlists (slDDL & slDML) after parsing through the file and extracting
the lines that customer actually needs based on their database version.
Inside a transaction, we first run the SQL from slDDL and if everything goes
ok, we commit and run the slDML SQL to update the new fields, etc. If there
is a problem (which hasn't happend yet), we roll back the changes and
informa the user. If everything executes correctly, we update the
customer's database version and shell back out to the original program and
close the db_updater through code.

Not sure if this is the best way to do it, but its been working flawlessly
for us and has the following benefits:

1. We give the customer the opportunity to also backup their database before
executing script/s.

2. Our software will not download new updates to the software itself that
depends on the new database changes without first verifying that the
customer has the "minimum database version" required for the new updates.

3. It all happens automatically and so does not require any intervention on
our part (much like symantec's live update).

Lee