Subject Re: [firebird-support] Re: Disable / Enable Triggers
Author Woody
From: "Geoff Worboys" <geoff@...>
> > I'm with Alan here. I have never used such a complex
> > procedure and upgrades are not a problem for us.
> ...
> > 3) Run your upgrade DDL.
> ...
> While I can understand the attraction of this approach, and
> I certainly do use it for relatively minor updates, for most
> significant upgrades I find a full database upgrade - similar
> to what I described in my response to t.s. - is much simpler.
> This is especially true when there may be multiple databases
> each of which may be in a slightly different state. I have
> found it easier to (in some cases automatically) adapt the
> data transfer process, than to adapt an upgrade DLL script
> for each situation.

I don't have to adapt anything no matter what state the database is coming
from. I wrote my own little database version program that keeps track of all
my projects. I simply add each update, it assigns it a version number and
generates a data file of the SQL needed to update the database. The routine
runs this data file and does all updates sequentially to bring the database
up to date no matter how far behind it's coming from. It uses a table in the
database to track all version changes so it knows exactly what updates were
done and when. That way, it will never run the same update more than once.
If the version table doesn't exist, it creates it.

The whole thing is in a unit file that I can include in any program, not a
separate program itself that has to be run independently. That way, I can
provide an option in the administration section of my applications so the
admin can perform the updates automatically. I am in the process of adding
the ability for it to execute a procedure call for those instances where
additional processing may be needed that isn't possible with straight SQL.
Since the unit is compiled with the program, it should be a fairly simple
process. I just need the time to finish it. :)

Of course, this only works if you don't have to have different database
structures for each of your clients. In that case, you still have different
versions of the program to compensate anyway so I don't see much difference
in maintaining separate updates at the same time.

Woody (TMW)