Re: Schema version control

On Thu, Feb 10, 2011 at 6:44 PM, Bill Moran <wmoran(at)potentialtech(dot)com>wrote:

> dbsteward can do downgrades ... you just feed it the old schema and
> the new schema in reverse of how you'd do an upgrade ;)
>
> Oh, also, it allows us to do installation-specific overrides. We use
> this ONLY for DML for lookup lists where some clients have slightly
> different names for things than others. In theory, it could do DDL
> overrides as well, but we decided on a policy of not utilizing that
> because we wanted the schemas to be consistent on all our installs.
>

What about upgrades that can't be derived directly from an inspection of the
schema? Some examples:

- Adding a NOT NULL constraint (without adding a DEFAULT). You often want
to precede this with filling in any existing NULL values, so the new
constraint doesn't fail.
- Updating triggers, functions and their effects. For example, when I have
an FTS index with a trigger to update an index column, and I change the
underlying trigger, I often do something like "UPDATE table SET column =
column", to cause all of the update triggers to fire and recalculate the
index columns.
- Creating a new column based on an old one, and removing the old one; eg.
add a column "n", run "UPDATE ... SET n = i*j * 2", and then drop the old
columns "i" and "j".
- Updating data from an external source, such as ORM model code; for
example, if you have a table representing external files, an update may want
to calculate and update the SHA-1 of each file.
- For efficiency, dropping a specific index while making a large update, and
then recreating the index.

In my experience, while generating schema updates automatically is handy, it
tends to make nontrivial database updates more complicated. These sorts of
things happen often and are an integral part of a database update, so I'm
just curious how/if you deal with them.

I've used Ruby's migrations, and for my Django databases I use my own
migration system which is based in principle off of it: create scripts to
migrate the database from version X to X+1 and X-1, and upgrade or downgrade
by running the appropriate scripts in sequence.

It's not ideal, since it can't generate a database at a specific version
directly; it always has to run through the entire sequence of migrations to
the version you want, and the migrations accumulate. However, it can handle
whatever arbitrary steps are needed to update a database, and I don't need
to test updates from every version to every other version.

--
Glenn Maynard