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;
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
