On 10/25/05, Allen Gilliland <[EMAIL PROTECTED]> wrote: > I have made an attempt at resolving this problem by defining 2 new > variables for the sql scripts ... TIMESTAMP_SQL_TYPE_NULL and > ALTER_PUBTIME_ALLOW_NULL > > the TIMESTAMP_SQL_TYPE_NULL is used for the pubtime column of the > createdb script and it uses Elias' suggestion "timestamp null" for all > dbs except mysql, which uses "datetime null". > > the ALTER_PUBTIME_ALLOW_NULL is meant to allow a database specific > method for altering the pubtime column to allow for null values. i > tried to do this a somewhat database generic method, but i just don't > think that will work. i've filled out this definition and tested it > with mysql and postgres, but it needs to be added for db2, derby, and > hsql. > > it should be pretty easy to follow my changes after seeing the various > *-raw.sql files and db_*.properties files. > > I haven't committed this yet because I wanted to see if anyone is > completely opposed to this approach. If not then i'll commit later > today.
This approach sounds good to me. Matt > > -- Allen > > > On Tue, 2005-10-25 at 10:51, Allen Gilliland wrote: > > 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 > > > > > > >
