You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@roller.apache.org by Allen Gilliland <Al...@Sun.COM> on 2005/10/24 21:05:13 UTC
resolving the timestamp/datetime issue
guys,
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.
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.
-- Allen
Re: resolving the timestamp/datetime issue
Posted by Matt Raible <mr...@gmail.com>.
On 10/25/05, Allen Gilliland <Al...@sun.com> 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
> > >
> >
>
>
Re: resolving the timestamp/datetime issue
Posted by Allen Gilliland <Al...@Sun.COM>.
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.
-- 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
> >
>
Re: resolving the timestamp/datetime issue
Posted by Allen Gilliland <Al...@Sun.COM>.
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
>
Re: resolving the timestamp/datetime issue
Posted by Dave Johnson <da...@rollerweblogger.org>.
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