> Forgive me for saying this - sounds terribly complex and time
> consuming!! And very prone to error.

On the contrary, based on what you've already done (using Delphi to run SQL
statements, knowledge of DDL) you should be able to get this running very
quickly.

In my opinion your approach is:
a) *More* prone to error. The version boundaries are less clear and the
script would be more difficult to understand.
b) Would have difficulty bringing the database to a consistent state in all
cases. When there are changes to multiple dependent tables the DDL can be
complex and your "no versions" or "one version per table" approach makes the
process more difficult.
c) Makes it difficult to track progressive changes to the database. Can you
clearly see what the changes are between databases?
d) If an error exists in the script (or there is a situation that you have
not catered for) you may get the target database into a state that is more
difficult to recover from.

> I understand what you are saying - I used to use that sort
> of system at a previous job. Did not like it. So much work.

I don't think that you do understand. It isn't much work. It is a much
better way to handle DB versioning.

> The real problem though, is getting anything from the customer.
> It is not going to happen with my particular customers. So, any
> system I device MUST be standalone - no customer interaction
> whatsoever. It must be invisible.

There isn't any customer interaction. I have thousands of customers spread
throughout the world and all they see when installing a new version with a
DB migration is a short-lived progress bar with the message "Upgrading
database. Please wait...". My goal is for hands-off deployment and
maintenance. Additionally on the About window I display the current database
version. This makes tech support much easier.

> I am really trying to get Firebird to do just what DBISAM can
> do so easily.

But that's the point. That is not the best approach to database versioning.
As your database matures your script will be more increasingly complex to
maintain.

> I simply include a modified DDL script with every new update.
> When the program loads, it checks the current database (there
> can be a lot more than one), checks the table versions, and
> updates anything required on the fly. It does this EVERY time
> a database is loaded. Very fast - the customer never knows
> about it and works flawlessly. It will update ANY database the
> customer has - of any version, to the current version.

Likewise, in the Professional edition of my product the database upgrade
occurs at install time (and there can be several databases), and in the
Standard edition the database upgrade occurs at first connect. The check is
simple - read one value from the database to get the current version, read
the last version from the migration script, compare the two to determine if
an upgrade needs to be performed. With a sequenced migration script ONLY the
statements that need to make changes are run.

The difference in the two approaches are that, in my opinion, your script is
more complex to develop and more difficult to understand, makes it more
difficult to recover from if there is an error.

I hope this has helped. I've have stressed my point of view in my posts as I
honestly think that in the long run you'll get into more difficulty going
down your path.

Regards,

Jarrod Hollingworth
Complete Time Tracking
http://www.complete-time-tracking.com/