On Mon, 2005-10-24 at 13:14, Dave Johnson wrote: > On Oct 24, 2005, at 3:05 PM, Allen Gilliland wrote: > > i'm still unsure of how to proceed with this. it seems as if we won't > > be able to settle on a column definition that works for all databases, > > so i am fine moving forward with a set of database specific > > definitions for this column if that is agreeable. > > > > another slightly bigger problem is that to make this change for > > upgrading users we will need to alter an existing column, which is > > something that it doesn't seem like we've done before. this is > > problematic because the alter table syntax is different for many > > databases, so we may also need database specific methods for altering > > the column definition. ugh. > > One method that we have used in the past, is recommended for PostgreSQL > and (I believe) should work with any SQL database is to use a temp > table like so: > > -- First you create the temp table full of data from the table you wish > to change: > create table tempfoo as select * from foo; > > -- Then you blow away the old table. > drop table foo; > > -- Recreate the table with the new column type(s) > create table foo ( > id type0, > column1 type1, > column2 type2 > ); > > -- and populate it from the data saved in the tmp table > insert into foo (id, column1, column2) > select tmpfoo.id, tmpfoo.column1, tmpfoo.column2 > from tmpfoo, foo > where tmpfoo.id=foo.id; > >
yikes ... that just makes me nervous :| it would also take quite a bit of time for sites like blogs.sun.com which contain a lot of data. i can think of 2 possible alternatives ... 1. we can do like Anil did with the "condition" column and just create a new column with a slightly different name and then copy the values over and update the hibernate mapping. the drawback here is that it's not backwards compatible. 2. we can create database specific "alter table .." statements. i am willing to do this, but i would need help testing this on the various dbs from other folks. -- Allen > > does anyone have suggestions about the right way to go about this? i > > am almost ready to just de-commit my change from svn and only apply it > > on our Sun instances of Roller since it's turning out to be so much > > trouble. > > Boo! I really like the way you've made PubTime work now. I hope we can > find a way that works for all of our databases. > > - Dave >
