db schema diffs ?
Is it possible to create some sort of database schema diff file, but smarter? So, for instance, I know one can take a diff of the the current db schema in use (some scripts to dump the two schemas in question and compare them manually), but I don’t know of any way one can generate those magical statements like ALTER TABLE …. or ADD/DROP COLUMN, in any type of automatic fashion. It’s either all or nothing on a table, right?
I know, I know, the DB Schema was supposed to be set in stone, months ago during the all-encompassing, formal design docs that figured out every single possible data field we wanted stored forevermore and into the future. Riiiight.
So how do people do it? Besides being really careful about altering data-types, and making sure you hand-code up the same change that you want to do in the dev/test/prod database, right? Or simply writing down the exact steps (or using something like phpMyAdmin, phpPGAdmin, or the Oracle Enterprise Manager Java-based piece of s___ — these tools being able to let you copy-paste and hand-jam manual ALTERs and ADD/DROP COLUMNs)?
I mean, yes, I know there are more problems to be had when altering tables like so (what about all the data that’s in ‘em), but I’m just curious to know if anyone has run into making common changes to a db schema, being able to “check those revisions” into a source-code control system (subversion/cvs/etc) and have your developers upgrade, seemlessly.
I know I’ve seen it in some open-source projects — on an upgrade of one of my fav open-source projects (MythTV, I’ve seen the upgrade go through all the revisions and upgrade the db accordingly (and I’ve also seen that break and I’ve had to go in manually and drop and restore entire tables too, though)…. Something to think about.
