Re: [SQLObject] Re: Changing table structure via SQLOBject

[Ian B. <ia...@co...>]

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 
> attributes
> and changes WITHOUT requiring you to lose all of your data (or to have 
> to
> change the table by hand)!
>
> It would only work if you've made changes that aren't impossible to 
> convert
> (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 
> whole
> load of if statements....? (i.e. is there a facility in MySQL or 
> PostgreSQL
> which lets you submit a new schema and IT figures out if it's possible 
> to
> 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 | ia...@co... | http://blog.ianbicking.org