On Thu, Feb 10, 2011 at 6:44 PM, Bill Moran <wmoran@xxxxxxxxxxxxxxxxx> wrote:
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.
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
