From: "inoffensive_2009" <larryl_hoo@...>

> Hi Folks:
>
> In a thread the other day, Adam told me he keeps a table that
> tracks patches to the database, so he knows what's been applied.
>
> I've been testing the database to see if a new table, or column
> exists, or a column has the correct width, and applying a particular
> create a table, or alter a table, if the query indicates that the DB
> isn't current.
>
> I don't know how expensive it is to perform these queries, but I'm
> interested in how you folks are logging your patches.
>
> Do you have a table that describes has a version number, and know
> what patches need to be applied to get that current? Or do you have a
> column for each change to the database like "CONTACT_ZIP_EXPANDED",
> "WEB_ADDRESS_TABLE_ADDED".
>
> Or is there a whole different approach that makes more sense.

I keep a version update table in each database. It stores the version number
of the update, the SQL text and the date it was done. A management function
in most of my apps checks an update file in a known location against the
database to see if any updates are needed. If any are needed, they are done,
one at a time, and the information stored in the version table.

The unit that performs updates, can also be called to do automatic version
updates when an administrator logs in through the application. If the user
is an administrator, the program checks for an update file. If it finds one,
it checks to see if there are any updates needed. If there are, it displays
a message to the user informing them that updates are needed and no one else
should be in the database while they are being done. It is up to the
administrator at that point to get everyone out of the system for the update
to take place. If they choose to put off the updates, it will prompt them
each time they log in to the application until they do them. It also warns
them that updating the application without updating the database could
result in problems or errors and continuing would be at their own risk, not
mine. :-)

The update file information is simply streamed text so the files are
relatively small (1-15k) even when including many previous updates. I
created a list class that can read/write it's information to a stream. A
simple GUI management program let's me track various projects and databases
so I can add updates visually and then create a new update file at any time.
These files are small enough to email to a client. All they have to do is
save the file in the proper directory.

HTH
Woody (TMW)