2009/2/26 inoffensive_2009 <larryl_hoo@...>:

> 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.

Hi Larry,

we've been using the following approach for years and it has proofed
stable and very practical.
The database has a table db_version with the fields major, minor and
build (but actually a simple "version" field would do the trick, too).
The application checks whether the program's internal version matches
the database version and, if not, executes appropriate scripts to
update the database. The script-files are shipped with each update and
correspond to a certain naming scheme, i.e. upd_MMmmmbbb.sql (where MM
= Major version, mmm = Minor version and bbb = Build, with each field
padded with zeros). This way we keep a complete history of the
database-changes and can, for example, find out which field was added
in which version, which came in handy several times already. Of course
that way we can update every database to the latest version without
any hassles, but this should be a requirement anyways.

There's even a more advanced solution to this in Ruby on Rails. Ruby
on Rails has some kind of conversion-entities (don't remember how they
call them) that can up- AND downgrade a certain database change. So
you can even downgrade a database to a state older than it currently
is. To us this is both not needed and would be impractical because we
do certain conversions that are simply not reversable.

Regards,

Daniel Albuschat
--
eat(this); // delicious suicide