sqlobject-discuss

[SQLObject] Changing table structure via SQLOBject

[David M. <da...@re...>]

Hi,

Is there any way to change the structure of a table via SQLObject? Or do 
I need to fire off a raw query?

I'm particularly interested in being able to add ForeignKey()s, 
MultipleJoin()s etc.

-- 

Kind regards
David

--

leave this line intact so your email gets through my junk mail filter

Re: [SQLObject] Changing table structure via SQLOBject

[David M. <da...@re...>]

Found it in the manual under 'Runtime Column Changes'.

Sorry for the mailbox noise.

David McNab wrote:
> Hi,
> 
> Is there any way to change the structure of a table via SQLObject? Or do 
> I need to fire off a raw query?
> 
> I'm particularly interested in being able to add ForeignKey()s, 
> MultipleJoin()s etc.
> 

-- 

Kind regards
David

--

leave this line intact so your email gets through my junk mail filter

[SQLObject] Re: Changing table structure via SQLOBject

[Chris G. <ch...@il...>]

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?)

"David McNab" <da...@re...> wrote in message
news:404...@re......
> Found it in the manual under 'Runtime Column Changes'.
>
> Sorry for the mailbox noise.
>
> David McNab wrote:
> > Hi,
> >
> > Is there any way to change the structure of a table via SQLObject? Or do
> > I need to fire off a raw query?
> >
> > I'm particularly interested in being able to add ForeignKey()s,
> > MultipleJoin()s etc.
> >
>
> -- 
>
> Kind regards
> David
>
> --
>
> leave this line intact so your email gets through my junk mail filter
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: IBM Linux Tutorials
> Free Linux tutorial presented by Daniel Robbins, President and CEO of
> GenToo technologies. Learn everything from fundamentals to system
> administration.http://ads.osdn.com/?ad_id=1470&alloc_id=3638&op=click

[SQLObject] Re: Changing table structure via SQLOBject

[Chris G. <ch...@il...>]

Uhm, just read my last post here... It's a little ambiguous.

"Chris Gahan" <ch...@il...> wrote in message
news:c2eb69$emk$1...@se......
> [...] you know what I think would be cool?
> 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)!

By "modified the attributes", I mean "change the definition of an SQLObject
subclass", not "change the values of the attributes" :)

Here's an example... if my original definition was:

def SuperVillain(SQLObject):
  realname = StringCol()
  coolVillainName = StringCol()
  nicknameTauntedByInSchool = StringCol()

...and then I changed it thusly:

def SuperVillain(SQLObject):
  realname = StringCol()
  coolVillainName = StringCol()
  nicknameTauntedByInSchool = StringCol(default='Stupid')
  evilCatchphrase = EnumCol(enumValues=["You will all feel my wrath!", "None
can withstand my awesome power!", "Muahahahahaha!!"])

...and then ran the new (currently fictitious) function
"SuperVillain.updateTableSchema()", it would add an evil_catchphrase column.

Yeah. That's what I mean. :)

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