On Mar 6, 2004, at 11:16 PM, Chris Gahan wrote:
> Hey, that reminds me... you know what I think would be cool?
> Well, probably not, so I'll tell you!
> A function that you can run after you've modified the attributes of an
> SQLObject which will update the database's table to add the new
> and changes WITHOUT requiring you to lose all of your data (or to have
> change the table by hand)!
> It would only work if you've made changes that aren't impossible to
> (i.e. changing a StringCol() to an IntCol()). I think it would be
> handy. :)
> Is there a clever way of implementing this, or is it basically just a
> load of if statements....? (i.e. is there a facility in MySQL or
> which lets you submit a new schema and IT figures out if it's possible
> convert the table to that?)
There's a utility called (I think) mysqldiff which compares table
definitions and comes up with a way to convert one to the other. I
believe it tries to keep values, though obviously it could be difficult
(e.g., it can't tell that you meant to rename a column). There's
something like pgdiff (I'm kind of making up the names here) that does
the same for Postgres, but I don't think it works as well -- Postgres
still has a bunch of restrictions when it comes to altering tables
(though 7.3 seems to remove several of these).
It would be an interesting task to do this with SQLObject, but I think
it would actually be more like a SQLObject application than part of the
library (though maybe not). In many ways it would be easier than with
these diff applications, because you could do it procedurally. E.g.,
add the new column, copy all the values over using whatever kind of
conversion function you want, then drop the old column, and maybe
rename the new column. Way easier than with the diff utilities,
because it doesn't have to divine what changes you intended by looking
at a description of the before and after state.
I know this all gets to be a pain in Postgres because of constraints.
E.g., there's some recipes for deleting columns in 7.2 where you create
the new table, copy all the values, then rename it so its in the old
table's place -- but constraints and views refer to the table ID, not
the table name, so it's a real pain. When I had to do this I ended up
just importing the database into 7.3, doing the operations, then
exporting the entire database back to 7.2. But it's probably doable by
messing with the magic pg_ tables enough, and if it's hard that just
means it's better to encapsulate all that effort into a program.
Anyway, doing it with MySQL would be way easier.
Ian Bicking | ianb@... | http://blog.ianbicking.org