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

[Ian S. <Ian...@et...>]

Ian Sparks wrote :
>>
It occured to me that it would be nice to have a dbDump() capability to =
dump all the data from the database into CSV in one file-per-table.
<<

Before anyone else has a chance to reply he then adds :

For this to work with Referential Integrity SO needs to know which =
tables are the start of the RI chain, load them first and then all the =
FK tables in order. I've been away from the SO code for a while so I =
can't remember if that information is already in there or can be easily =
extracted/extrapolated?


-----Original Message-----
From: Ian Sparks=20
Sent: Monday, March 08, 2004 12:32 PM
To: Ian Bicking; Chris Gahan
Cc: sql...@li...
Subject: RE: [SQLObject] Re: Changing table structure via SQLOBject


Ian Bicking Wrote :
>>
In many ways it would be easier than with=20
these diff applications, because you could do it procedurally.  E.g.,=20
add the new column, copy all the values over using whatever kind of=20
conversion function you want, then drop the old column, and maybe=20
rename the new column.
<<

It occured to me that it would be nice to have a dbDump() capability to =
dump all the data from the database into CSV in one file-per-table.

Then you could have a dbLoad() capability to re-load all that data to an =
empty schema.

Being able to change a database in place would be useful but often =
metadata changes cut-across transactions so you can't allow anyone else =
access to the database while you're doing it anyway. That being the case =
a dump, modify, regenerate schema, reload cycle is not too hard to bear. =
Sure, it could take some time on a large database but it'd be pretty =
safe and useful for rapid prototyping too.

My $0.02. I wish I had the time to contribute and not just cheer from =
the sidelines.


-----Original Message-----
From: Ian Bicking [mailto:ia...@co...]
Sent: Sunday, March 07, 2004 3:39 PM
To: Chris Gahan
Cc: sql...@li...
Subject: Re: [SQLObject] Re: Changing table structure via SQLOBject


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=20
> 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=20
> convert
> (i.e. changing a StringCol() to an IntCol()). I think it would be=20
> handy. :)
>
> Is there a clever way of implementing this, or is it basically just a=20
> whole
> load of if statements....? (i.e. is there a facility in MySQL or=20
> 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=20
definitions and comes up with a way to convert one to the other.  I=20
believe it tries to keep values, though obviously it could be difficult=20
(e.g., it can't tell that you meant to rename a column).  There's=20
something like pgdiff (I'm kind of making up the names here) that does=20
the same for Postgres, but I don't think it works as well -- Postgres=20
still has a bunch of restrictions when it comes to altering tables=20
(though 7.3 seems to remove several of these).

It would be an interesting task to do this with SQLObject, but I think=20
it would actually be more like a SQLObject application than part of the=20
library (though maybe not).  In many ways it would be easier than with=20
these diff applications, because you could do it procedurally.  E.g.,=20
add the new column, copy all the values over using whatever kind of=20
conversion function you want, then drop the old column, and maybe=20
rename the new column.  Way easier than with the diff utilities,=20
because it doesn't have to divine what changes you intended by looking=20
at a description of the before and after state.

I know this all gets to be a pain in Postgres because of constraints. =20
E.g., there's some recipes for deleting columns in 7.2 where you create=20
the new table, copy all the values, then rename it so its in the old=20
table's place -- but constraints and views refer to the table ID, not=20
the table name, so it's a real pain.  When I had to do this I ended up=20
just importing the database into 7.3, doing the operations, then=20
exporting the entire database back to 7.2.  But it's probably doable by=20
messing with the magic pg_ tables enough, and if it's hard that just=20
means it's better to encapsulate all that effort into a program. =20
Anyway, doing it with MySQL would be way easier.

--
Ian Bicking | ia...@co... | http://blog.ianbicking.org



-------------------------------------------------------
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=3D1470&alloc_id=3D3638&op=3Dcli=
ck
_______________________________________________
sqlobject-discuss mailing list
sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss


-------------------------------------------------------
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_id638&op=3Dick
_______________________________________________
sqlobject-discuss mailing list
sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss