versioning schema changes was: chosing a database name

On Wed, Jul 13, 2005 at 03:53:26PM -0500, Richard_D_Levine(at)raytheon(dot)com wrote:

> I think a better approach is to handle configuration management with a
> table in each schema. Update the schema, update the table.
We already do that anyways. Our schema scripts have their
CVS version tag embedded in an INSERT statement which
updates the schema revision tracking table with file name
and revision.

From our first release (== CVS release tag) onwards we will
employ the change-script-only technique described in Elein's
latest Tidbits.

I have now added a database revision table which enforces to
have only one single row which holds the "database schema
version". That row holds the md5 hash of the (ordered)
concatenation of the file name/revision rows in the table
described above. Thereby, when the schema changes, the hash
changes, too. We might then employ a lookup table/function
where the client can match its version against the database
version thereby determining whether it can work with that
database. Of course, any user with sufficient access rights
can manually screw up this construct ...

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346