Re: Schema version control

On Fri, Feb 11, 2011 at 12:16 AM, Bill Moran <wmoran(at)potentialtech(dot)com>wrote:

> The big caveat is that 99.9% of the database changes don't fall into those
> "nontrivial" categories, and dbsteward makes those 99.9% of the changes
> easy to do, reliable to reproduce, and easy to track.
>

My experience is maybe more like 95% than 99.9%, for what it's worth;
they're the exception, but not rare.

We've added some stuff to handle the other .1% as well, like
> <beforeUpdateSQL>
> and <afterUpdateSQL> where you can put an arbitrary SQL strings to be run
> before or after the remainder of the automatic stuff is done. We probably
> haven't seen every circumstance that needs a special handling, but we've
> already struggled through a bunch.
>

Here's a fairly common example, in the abstract:

version 1 has two columns, i and j;
version 2 has one column, k, where k = i + j; and
version 3 has one column, x, where x = k * 2

Not only is updating from 1 to 2 tricky ("k = i + j" lies between the adding
of "k" but before the removal of i and j; it's neither a "before" nor an
"after"), but updating directly from 1 to 3 without first migrating to 2 is
extremely hard. I suspect you'd need to snapshot the schema at each version
where these are needed to update incrementally, rather than always trying to
convert directly to the current version--maybe you already do that.

Anyhow, just some thoughts based on my own experience with database
updates--good luck.

--
Glenn Maynard