On Fri, Feb 11, 2011 at 12:16 AM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
My experience is maybe more like 95% than 99.9%, for what it's worth; they're the exception, but not rare.
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.
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
