You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@roller.apache.org by Elias Torres <el...@gmail.com> on 2005/08/22 04:23:57 UTC
createdb-raw.sql
Hi Guys,
I was trying to use your cross-database tools (properties file) to add
support for DB2 in Roller and I found a couple of things you might
want to know. BTW, I'm no DB expert.
- DB2 has a (seems-to-me) hard limit on identifier names for
constraints and indexes to 18 chars. I had to shorten them.
- It's illegal for column definitions to contain simply a "null" after
the coltype (it's the default).
- It's a column definition is of type varchar, the default value cannot be 0.
- If I use db2 -tvf createdb.sql, it seems to fail when comments are
embedded in a create table definition.
In addition to that I had to add extra keywords to the foreign key
constraints, so an extra property at the end of the contraints would
do me a great deal of good. I'll keep you posted on the rest of the
progress to actually connect the WAR to DB2.
To achieve this, I wrote a python script that did all of this for me,
but I'd hope we could add this support to Roller right out of the
box.
Regards,
Elias
Re: createdb-raw.sql
Posted by Elias Torres <el...@gmail.com>.
Dave,
Attached are three files. -- I could not send attachments.
- A database SVN generated patch (inside it points to my workspace
path in the fs, but I'm sure you can workaround that) [1]
- The tar containing all of the files touched. [2]
- A build.xml SVN generated patch so it includes DB2 in the scripts. [3]
The patch for createdb-raw.sql did the following things:
- Remove all unnecessary nulls from column definitions
- changed one column def from default 0 to default '' (I tested this
on mySQL and it seem to cause no problems)
- moved all in-statements comments to pre-statements comments
- added a new field for additional foreign key params statements and
properly updated all db*.property files
The only thing I did not do was shorten the index names because I'm
not sure how much that could break in upgrade scripts, etc. After all
these changes, I have tested on DB2 and all my python script has to do
is shorten the index/constraints identifiers, so this is a lot of
progress.
Thanks,
Elias
[1] http://torrez.us/archives/2005/08/23/roller/patches/db2_database.patch
[2] http://torrez.us/archives/2005/08/23/roller/patches/db2_database_patch.tar
[3] http://torrez.us/archives/2005/08/23/roller/patches/db2_build.patch
On 8/23/05, Dave Johnson <da...@rollerweblogger.org> wrote:
>
> On Aug 22, 2005, at 8:48 AM, Lance Lavandowska wrote:
> > On 8/21/05, Elias Torres <el...@gmail.com> wrote:
> >> - DB2 has a (seems-to-me) hard limit on identifier names for
> >> constraints and indexes to 18 chars. I had to shorten them.
> >> - It's illegal for column definitions to contain simply a "null" after
> >> the coltype (it's the default).
>
> I knew there were still database with crazy column name limitations.
>
> I guess the proposed "roller_" prefix is too much.
>
> Elias: do you have a patch that goes against the 2.0 version of
> createdb.sql?
>
> - Dave
>
>
> >
> > It seems to me that is the default for most DBs, so this change
> > shouldn't be an issue (removing the 'allows null' declarations).
> >
> >> - It's a column definition is of type varchar, the default value
> >> cannot be 0.
> >
> > Example please.
> >
> >> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> >> embedded in a create table definition.
> >
> > Ugh. I suppose we could move comments to be before the table
> > definition.
> >
> >> In addition to that I had to add extra keywords to the foreign key
> >> constraints, so an extra property at the end of the contraints would
> >
> > We could add a generic transform (keyword substitution) at the end of
> > each foreign key declaration, but that seems rather hackish.
> >
> > Anyone else have comments?
> >
> > Lance
> >
>
>
Re: createdb-raw.sql
Posted by Elias Torres <el...@torrez.us>.
Dave,
Any thoughts on what do you think could be done regarding the index
names? Are you guys willing to shorten them a bit for us :-) ?
Elias
On 8/29/05, Dave Johnson <da...@rollerweblogger.org> wrote:
> No I haven't had a chance to take a look yet. I hope to get to it this
> week when I work on the 2.0 database migration stuff.
>
> - Dave
>
>
>
> On Aug 28, 2005, at 7:54 PM, Elias Torres wrote:
>
> > Dave,
> >
> > I'm not sure if you missed my message or have not had time to look at
> > the patch. Just in case, here's the link to my earlier message [1]
> >
> > Elias
> >
> > [1] http://tinyurl.com/8nklz
> >
> > On 8/23/05, Dave Johnson <da...@rollerweblogger.org> wrote:
> >>
> >> On Aug 22, 2005, at 8:48 AM, Lance Lavandowska wrote:
> >>> On 8/21/05, Elias Torres <el...@gmail.com> wrote:
> >>>> - DB2 has a (seems-to-me) hard limit on identifier names for
> >>>> constraints and indexes to 18 chars. I had to shorten them.
> >>>> - It's illegal for column definitions to contain simply a "null"
> >>>> after
> >>>> the coltype (it's the default).
> >>
> >> I knew there were still database with crazy column name limitations.
> >>
> >> I guess the proposed "roller_" prefix is too much.
> >>
> >> Elias: do you have a patch that goes against the 2.0 version of
> >> createdb.sql?
> >>
> >> - Dave
> >>
> >>
> >>>
> >>> It seems to me that is the default for most DBs, so this change
> >>> shouldn't be an issue (removing the 'allows null' declarations).
> >>>
> >>>> - It's a column definition is of type varchar, the default value
> >>>> cannot be 0.
> >>>
> >>> Example please.
> >>>
> >>>> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> >>>> embedded in a create table definition.
> >>>
> >>> Ugh. I suppose we could move comments to be before the table
> >>> definition.
> >>>
> >>>> In addition to that I had to add extra keywords to the foreign key
> >>>> constraints, so an extra property at the end of the contraints would
> >>>
> >>> We could add a generic transform (keyword substitution) at the end of
> >>> each foreign key declaration, but that seems rather hackish.
> >>>
> >>> Anyone else have comments?
> >>>
> >>> Lance
> >>>
> >>
> >>
> >
>
>
Re: createdb-raw.sql
Posted by Dave Johnson <da...@rollerweblogger.org>.
No I haven't had a chance to take a look yet. I hope to get to it this
week when I work on the 2.0 database migration stuff.
- Dave
On Aug 28, 2005, at 7:54 PM, Elias Torres wrote:
> Dave,
>
> I'm not sure if you missed my message or have not had time to look at
> the patch. Just in case, here's the link to my earlier message [1]
>
> Elias
>
> [1] http://tinyurl.com/8nklz
>
> On 8/23/05, Dave Johnson <da...@rollerweblogger.org> wrote:
>>
>> On Aug 22, 2005, at 8:48 AM, Lance Lavandowska wrote:
>>> On 8/21/05, Elias Torres <el...@gmail.com> wrote:
>>>> - DB2 has a (seems-to-me) hard limit on identifier names for
>>>> constraints and indexes to 18 chars. I had to shorten them.
>>>> - It's illegal for column definitions to contain simply a "null"
>>>> after
>>>> the coltype (it's the default).
>>
>> I knew there were still database with crazy column name limitations.
>>
>> I guess the proposed "roller_" prefix is too much.
>>
>> Elias: do you have a patch that goes against the 2.0 version of
>> createdb.sql?
>>
>> - Dave
>>
>>
>>>
>>> It seems to me that is the default for most DBs, so this change
>>> shouldn't be an issue (removing the 'allows null' declarations).
>>>
>>>> - It's a column definition is of type varchar, the default value
>>>> cannot be 0.
>>>
>>> Example please.
>>>
>>>> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
>>>> embedded in a create table definition.
>>>
>>> Ugh. I suppose we could move comments to be before the table
>>> definition.
>>>
>>>> In addition to that I had to add extra keywords to the foreign key
>>>> constraints, so an extra property at the end of the contraints would
>>>
>>> We could add a generic transform (keyword substitution) at the end of
>>> each foreign key declaration, but that seems rather hackish.
>>>
>>> Anyone else have comments?
>>>
>>> Lance
>>>
>>
>>
>
Re: createdb-raw.sql
Posted by Elias Torres <el...@torrez.us>.
Dave,
I'm not sure if you missed my message or have not had time to look at
the patch. Just in case, here's the link to my earlier message [1]
Elias
[1] http://tinyurl.com/8nklz
On 8/23/05, Dave Johnson <da...@rollerweblogger.org> wrote:
>
> On Aug 22, 2005, at 8:48 AM, Lance Lavandowska wrote:
> > On 8/21/05, Elias Torres <el...@gmail.com> wrote:
> >> - DB2 has a (seems-to-me) hard limit on identifier names for
> >> constraints and indexes to 18 chars. I had to shorten them.
> >> - It's illegal for column definitions to contain simply a "null" after
> >> the coltype (it's the default).
>
> I knew there were still database with crazy column name limitations.
>
> I guess the proposed "roller_" prefix is too much.
>
> Elias: do you have a patch that goes against the 2.0 version of
> createdb.sql?
>
> - Dave
>
>
> >
> > It seems to me that is the default for most DBs, so this change
> > shouldn't be an issue (removing the 'allows null' declarations).
> >
> >> - It's a column definition is of type varchar, the default value
> >> cannot be 0.
> >
> > Example please.
> >
> >> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> >> embedded in a create table definition.
> >
> > Ugh. I suppose we could move comments to be before the table
> > definition.
> >
> >> In addition to that I had to add extra keywords to the foreign key
> >> constraints, so an extra property at the end of the contraints would
> >
> > We could add a generic transform (keyword substitution) at the end of
> > each foreign key declaration, but that seems rather hackish.
> >
> > Anyone else have comments?
> >
> > Lance
> >
>
>
Re: createdb-raw.sql
Posted by Dave Johnson <da...@rollerweblogger.org>.
On Aug 22, 2005, at 8:48 AM, Lance Lavandowska wrote:
> On 8/21/05, Elias Torres <el...@gmail.com> wrote:
>> - DB2 has a (seems-to-me) hard limit on identifier names for
>> constraints and indexes to 18 chars. I had to shorten them.
>> - It's illegal for column definitions to contain simply a "null" after
>> the coltype (it's the default).
I knew there were still database with crazy column name limitations.
I guess the proposed "roller_" prefix is too much.
Elias: do you have a patch that goes against the 2.0 version of
createdb.sql?
- Dave
>
> It seems to me that is the default for most DBs, so this change
> shouldn't be an issue (removing the 'allows null' declarations).
>
>> - It's a column definition is of type varchar, the default value
>> cannot be 0.
>
> Example please.
>
>> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
>> embedded in a create table definition.
>
> Ugh. I suppose we could move comments to be before the table
> definition.
>
>> In addition to that I had to add extra keywords to the foreign key
>> constraints, so an extra property at the end of the contraints would
>
> We could add a generic transform (keyword substitution) at the end of
> each foreign key declaration, but that seems rather hackish.
>
> Anyone else have comments?
>
> Lance
>
Re: createdb-raw.sql
Posted by Lance Lavandowska <la...@gmail.com>.
On 8/21/05, Elias Torres <el...@gmail.com> wrote:
> Hi Guys,
>
> I was trying to use your cross-database tools (properties file) to add
> support for DB2 in Roller and I found a couple of things you might
> want to know. BTW, I'm no DB expert.
>
> - DB2 has a (seems-to-me) hard limit on identifier names for
> constraints and indexes to 18 chars. I had to shorten them.
> - It's illegal for column definitions to contain simply a "null" after
> the coltype (it's the default).
It seems to me that is the default for most DBs, so this change
shouldn't be an issue (removing the 'allows null' declarations).
> - It's a column definition is of type varchar, the default value cannot be 0.
Example please.
> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> embedded in a create table definition.
Ugh. I suppose we could move comments to be before the table definition.
> In addition to that I had to add extra keywords to the foreign key
> constraints, so an extra property at the end of the contraints would
We could add a generic transform (keyword substitution) at the end of
each foreign key declaration, but that seems rather hackish.
Anyone else have comments?
Lance
Re: createdb-raw.sql
Posted by Allen Gilliland <Al...@Sun.COM>.
ahh ... i did misinterpret. sorry, i thought table names were included.
indexes and constraints probably aren't as big a deal as table names if you ask me, so if we can clean those up and get those to work with db2 out of the box then i say we do that as well.
-- Allen
On Tue, 2005-08-23 at 09:33, Elias Torres wrote:
> I hope you did not misunderstand me. The limitation is not on table
> names, it's on constraint and index names. The table/column names are
> all fine.
>
> stuff like this:
>
> rag_group_subscription_gid
>
> from
>
> create index rag_group_subscription_gid on
> rag_group_subscription(group_id@INDEXSIZE@);
>
> Elias
>
> PS. I wouldn't call DB2 obscure, but maybe I'm biased.
>
> On 8/23/05, Allen Gilliland <Al...@sun.com> wrote:
> > On Tue, 2005-08-23 at 09:18, Elias Torres wrote:
> > > On 8/23/05, Anil Gangolli <an...@busybuddha.org> wrote:
> > > >
> > > > ----- Original Message -----
> > > > From: "Elias Torres" <el...@gmail.com>
> > > > To: <ro...@incubator.apache.org>
> > > > Sent: Sunday, August 21, 2005 7:23 PM
> > > > Subject: createdb-raw.sql
> > > >
> > > >
> > > > > Hi Guys,
> > > > >
> > > > > I was trying to use your cross-database tools (properties file) to add
> > > > > support for DB2 in Roller and I found a couple of things you might
> > > > > want to know. BTW, I'm no DB expert.
> > > > >
> > > > > - DB2 has a (seems-to-me) hard limit on identifier names for
> > > > > constraints and indexes to 18 chars. I had to shorten them.
> > > >
> > > > Ouch. This is a really painful one to force everything else into compliance with.
> > >
> >
> > yeah ... 18 characters seems ridiculously low :/
> >
> > i am all for portablility, but as with most situations a line has to be drawn somewhere so we don't bend over backwards to support the obscure. i still prefer a "roller_" prefix because it's the most descriptive, but "rol_" is fine if that's all we can manage.
> >
> > what i would be most opposed to is cryptic abbreviations just to make the table names short enough. so if we had to do "rol_cmts" instead of "roller_comments" then that's just no good.
> >
> > we had talked about doing a full renaming of the tables to fit a nice scheme, how about we try and do that for the 2.0 release? anyone want to put together a list of new table names and send it out to the dev list for a quick review?
> >
> > -- Allen
> >
> >
> > > Agree. That's why I did not include this in my patch.
> > >
> > > >
> > > > > - It's illegal for column definitions to contain simply a "null" after
> > > > > the coltype (it's the default).
> > > >
> > > > Easy to accomodate; I think this is generally the default so we can eliminate the ones that are superfluous.
> > > >
> > >
> > > Done. See patch email.
> > >
> > > > > - It's a column definition is of type varchar, the default value cannot be 0.
> > > >
> > > > this is probably a typo, and should be corrected
> > > >
> > >
> > > Done. See patch email.
> > >
> > > > > - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> > > > > embedded in a create table definition.
> > > >
> > > > Mostly my fault; I like to use per-column comments. We should remove them or filter these out of the -raw files during processing.
> > > >
> > >
> > > Done. See patch email.
> > >
> > > > > In addition to that I had to add extra keywords to the foreign key
> > > > > constraints, so an extra property at the end of the contraints would
> > > > > do me a great deal of good. I'll keep you posted on the rest of the
> > > > > progress to actually connect the WAR to DB2.
> > > >
> > > > Easy.
> > >
> > > Done. See patch email.
> > >
> > > >
> > > > > To achieve this, I wrote a python script that did all of this for me,
> > > > > but I'd hope we could add this support to Roller right out of the
> > > > > box.
> > > >
> > > > I'm curious about the name transformations you applied; perhaps this is the approach we should use in general for dbs that support
> > > > only very short names.
> > > >
> > > >
> > >
> > > Well, I tried several approaches. At first I was adding all table
> > > names to a list and replacing the table names from the identifier with
> > > an index. This however was not enough, so I replace index with idx and
> > > other little things, but the naming overall is not completely
> > > consistent so there were still some long identifiers. In the end, I
> > > ended up just keeping a counter and now I end up with index_n and
> > > constraint_n. That definitely worked :-)
> > >
> > > I'm not really sure, but I think that a 3 character abbreviation for
> > > tables and fields should be enough the rest is just either idx or fk.
> > > However, this is a major change to get right for the upgrade scripts,
> > > etc. I also did get some warnings from DB2 that some of the indexes
> > > and unique constraints are unnecessary because those columns are
> > > already primary keys. It might be good to get rid of and double-check
> > > their effects on other dbs.
> > >
> > > Elias
> >
> >
Re: createdb-raw.sql
Posted by Elias Torres <el...@torrez.us>.
I can make sure it works for Derby as well. However, I did notice that
the DB2Dialect in Hibernate2.1 is not really correct. Maybe this is
another reason for moving to Hibernate3.
Elias
On 8/29/05, Brent Schneeman <sc...@gmail.com> wrote:
> On 8/23/05, Elias Torres <el...@gmail.com> wrote:
> >
> > PS. I wouldn't call DB2 obscure, but maybe I'm biased.
> >
>
> I'm pretty sure that the Apache Derby DB has the same restrictions at
> DB2. Derby derives from Cloudscape.
>
> IIRC, the Hibernate dialect for DB2 and Derby are the same (or really,
> really close). The work that Elias is doing will have benefit for
> Derby users.
>
Re: createdb-raw.sql
Posted by Brent Schneeman <sc...@gmail.com>.
On 8/23/05, Elias Torres <el...@gmail.com> wrote:
>
> PS. I wouldn't call DB2 obscure, but maybe I'm biased.
>
I'm pretty sure that the Apache Derby DB has the same restrictions at
DB2. Derby derives from Cloudscape.
IIRC, the Hibernate dialect for DB2 and Derby are the same (or really,
really close). The work that Elias is doing will have benefit for
Derby users.
Re: createdb-raw.sql
Posted by Elias Torres <el...@gmail.com>.
I hope you did not misunderstand me. The limitation is not on table
names, it's on constraint and index names. The table/column names are
all fine.
stuff like this:
rag_group_subscription_gid
from
create index rag_group_subscription_gid on
rag_group_subscription(group_id@INDEXSIZE@);
Elias
PS. I wouldn't call DB2 obscure, but maybe I'm biased.
On 8/23/05, Allen Gilliland <Al...@sun.com> wrote:
> On Tue, 2005-08-23 at 09:18, Elias Torres wrote:
> > On 8/23/05, Anil Gangolli <an...@busybuddha.org> wrote:
> > >
> > > ----- Original Message -----
> > > From: "Elias Torres" <el...@gmail.com>
> > > To: <ro...@incubator.apache.org>
> > > Sent: Sunday, August 21, 2005 7:23 PM
> > > Subject: createdb-raw.sql
> > >
> > >
> > > > Hi Guys,
> > > >
> > > > I was trying to use your cross-database tools (properties file) to add
> > > > support for DB2 in Roller and I found a couple of things you might
> > > > want to know. BTW, I'm no DB expert.
> > > >
> > > > - DB2 has a (seems-to-me) hard limit on identifier names for
> > > > constraints and indexes to 18 chars. I had to shorten them.
> > >
> > > Ouch. This is a really painful one to force everything else into compliance with.
> >
>
> yeah ... 18 characters seems ridiculously low :/
>
> i am all for portablility, but as with most situations a line has to be drawn somewhere so we don't bend over backwards to support the obscure. i still prefer a "roller_" prefix because it's the most descriptive, but "rol_" is fine if that's all we can manage.
>
> what i would be most opposed to is cryptic abbreviations just to make the table names short enough. so if we had to do "rol_cmts" instead of "roller_comments" then that's just no good.
>
> we had talked about doing a full renaming of the tables to fit a nice scheme, how about we try and do that for the 2.0 release? anyone want to put together a list of new table names and send it out to the dev list for a quick review?
>
> -- Allen
>
>
> > Agree. That's why I did not include this in my patch.
> >
> > >
> > > > - It's illegal for column definitions to contain simply a "null" after
> > > > the coltype (it's the default).
> > >
> > > Easy to accomodate; I think this is generally the default so we can eliminate the ones that are superfluous.
> > >
> >
> > Done. See patch email.
> >
> > > > - It's a column definition is of type varchar, the default value cannot be 0.
> > >
> > > this is probably a typo, and should be corrected
> > >
> >
> > Done. See patch email.
> >
> > > > - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> > > > embedded in a create table definition.
> > >
> > > Mostly my fault; I like to use per-column comments. We should remove them or filter these out of the -raw files during processing.
> > >
> >
> > Done. See patch email.
> >
> > > > In addition to that I had to add extra keywords to the foreign key
> > > > constraints, so an extra property at the end of the contraints would
> > > > do me a great deal of good. I'll keep you posted on the rest of the
> > > > progress to actually connect the WAR to DB2.
> > >
> > > Easy.
> >
> > Done. See patch email.
> >
> > >
> > > > To achieve this, I wrote a python script that did all of this for me,
> > > > but I'd hope we could add this support to Roller right out of the
> > > > box.
> > >
> > > I'm curious about the name transformations you applied; perhaps this is the approach we should use in general for dbs that support
> > > only very short names.
> > >
> > >
> >
> > Well, I tried several approaches. At first I was adding all table
> > names to a list and replacing the table names from the identifier with
> > an index. This however was not enough, so I replace index with idx and
> > other little things, but the naming overall is not completely
> > consistent so there were still some long identifiers. In the end, I
> > ended up just keeping a counter and now I end up with index_n and
> > constraint_n. That definitely worked :-)
> >
> > I'm not really sure, but I think that a 3 character abbreviation for
> > tables and fields should be enough the rest is just either idx or fk.
> > However, this is a major change to get right for the upgrade scripts,
> > etc. I also did get some warnings from DB2 that some of the indexes
> > and unique constraints are unnecessary because those columns are
> > already primary keys. It might be good to get rid of and double-check
> > their effects on other dbs.
> >
> > Elias
>
>
Re: createdb-raw.sql
Posted by Allen Gilliland <Al...@Sun.COM>.
On Tue, 2005-08-23 at 09:18, Elias Torres wrote:
> On 8/23/05, Anil Gangolli <an...@busybuddha.org> wrote:
> >
> > ----- Original Message -----
> > From: "Elias Torres" <el...@gmail.com>
> > To: <ro...@incubator.apache.org>
> > Sent: Sunday, August 21, 2005 7:23 PM
> > Subject: createdb-raw.sql
> >
> >
> > > Hi Guys,
> > >
> > > I was trying to use your cross-database tools (properties file) to add
> > > support for DB2 in Roller and I found a couple of things you might
> > > want to know. BTW, I'm no DB expert.
> > >
> > > - DB2 has a (seems-to-me) hard limit on identifier names for
> > > constraints and indexes to 18 chars. I had to shorten them.
> >
> > Ouch. This is a really painful one to force everything else into compliance with.
>
yeah ... 18 characters seems ridiculously low :/
i am all for portablility, but as with most situations a line has to be drawn somewhere so we don't bend over backwards to support the obscure. i still prefer a "roller_" prefix because it's the most descriptive, but "rol_" is fine if that's all we can manage.
what i would be most opposed to is cryptic abbreviations just to make the table names short enough. so if we had to do "rol_cmts" instead of "roller_comments" then that's just no good.
we had talked about doing a full renaming of the tables to fit a nice scheme, how about we try and do that for the 2.0 release? anyone want to put together a list of new table names and send it out to the dev list for a quick review?
-- Allen
> Agree. That's why I did not include this in my patch.
>
> >
> > > - It's illegal for column definitions to contain simply a "null" after
> > > the coltype (it's the default).
> >
> > Easy to accomodate; I think this is generally the default so we can eliminate the ones that are superfluous.
> >
>
> Done. See patch email.
>
> > > - It's a column definition is of type varchar, the default value cannot be 0.
> >
> > this is probably a typo, and should be corrected
> >
>
> Done. See patch email.
>
> > > - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> > > embedded in a create table definition.
> >
> > Mostly my fault; I like to use per-column comments. We should remove them or filter these out of the -raw files during processing.
> >
>
> Done. See patch email.
>
> > > In addition to that I had to add extra keywords to the foreign key
> > > constraints, so an extra property at the end of the contraints would
> > > do me a great deal of good. I'll keep you posted on the rest of the
> > > progress to actually connect the WAR to DB2.
> >
> > Easy.
>
> Done. See patch email.
>
> >
> > > To achieve this, I wrote a python script that did all of this for me,
> > > but I'd hope we could add this support to Roller right out of the
> > > box.
> >
> > I'm curious about the name transformations you applied; perhaps this is the approach we should use in general for dbs that support
> > only very short names.
> >
> >
>
> Well, I tried several approaches. At first I was adding all table
> names to a list and replacing the table names from the identifier with
> an index. This however was not enough, so I replace index with idx and
> other little things, but the naming overall is not completely
> consistent so there were still some long identifiers. In the end, I
> ended up just keeping a counter and now I end up with index_n and
> constraint_n. That definitely worked :-)
>
> I'm not really sure, but I think that a 3 character abbreviation for
> tables and fields should be enough the rest is just either idx or fk.
> However, this is a major change to get right for the upgrade scripts,
> etc. I also did get some warnings from DB2 that some of the indexes
> and unique constraints are unnecessary because those columns are
> already primary keys. It might be good to get rid of and double-check
> their effects on other dbs.
>
> Elias
Re: createdb-raw.sql
Posted by Elias Torres <el...@gmail.com>.
On 8/23/05, Anil Gangolli <an...@busybuddha.org> wrote:
>
> ----- Original Message -----
> From: "Elias Torres" <el...@gmail.com>
> To: <ro...@incubator.apache.org>
> Sent: Sunday, August 21, 2005 7:23 PM
> Subject: createdb-raw.sql
>
>
> > Hi Guys,
> >
> > I was trying to use your cross-database tools (properties file) to add
> > support for DB2 in Roller and I found a couple of things you might
> > want to know. BTW, I'm no DB expert.
> >
> > - DB2 has a (seems-to-me) hard limit on identifier names for
> > constraints and indexes to 18 chars. I had to shorten them.
>
> Ouch. This is a really painful one to force everything else into compliance with.
Agree. That's why I did not include this in my patch.
>
> > - It's illegal for column definitions to contain simply a "null" after
> > the coltype (it's the default).
>
> Easy to accomodate; I think this is generally the default so we can eliminate the ones that are superfluous.
>
Done. See patch email.
> > - It's a column definition is of type varchar, the default value cannot be 0.
>
> this is probably a typo, and should be corrected
>
Done. See patch email.
> > - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> > embedded in a create table definition.
>
> Mostly my fault; I like to use per-column comments. We should remove them or filter these out of the -raw files during processing.
>
Done. See patch email.
> > In addition to that I had to add extra keywords to the foreign key
> > constraints, so an extra property at the end of the contraints would
> > do me a great deal of good. I'll keep you posted on the rest of the
> > progress to actually connect the WAR to DB2.
>
> Easy.
Done. See patch email.
>
> > To achieve this, I wrote a python script that did all of this for me,
> > but I'd hope we could add this support to Roller right out of the
> > box.
>
> I'm curious about the name transformations you applied; perhaps this is the approach we should use in general for dbs that support
> only very short names.
>
>
Well, I tried several approaches. At first I was adding all table
names to a list and replacing the table names from the identifier with
an index. This however was not enough, so I replace index with idx and
other little things, but the naming overall is not completely
consistent so there were still some long identifiers. In the end, I
ended up just keeping a counter and now I end up with index_n and
constraint_n. That definitely worked :-)
I'm not really sure, but I think that a 3 character abbreviation for
tables and fields should be enough the rest is just either idx or fk.
However, this is a major change to get right for the upgrade scripts,
etc. I also did get some warnings from DB2 that some of the indexes
and unique constraints are unnecessary because those columns are
already primary keys. It might be good to get rid of and double-check
their effects on other dbs.
Elias
Re: createdb-raw.sql
Posted by Anil Gangolli <an...@busybuddha.org>.
----- Original Message -----
From: "Elias Torres" <el...@gmail.com>
To: <ro...@incubator.apache.org>
Sent: Sunday, August 21, 2005 7:23 PM
Subject: createdb-raw.sql
> Hi Guys,
>
> I was trying to use your cross-database tools (properties file) to add
> support for DB2 in Roller and I found a couple of things you might
> want to know. BTW, I'm no DB expert.
>
> - DB2 has a (seems-to-me) hard limit on identifier names for
> constraints and indexes to 18 chars. I had to shorten them.
Ouch. This is a really painful one to force everything else into compliance with.
> - It's illegal for column definitions to contain simply a "null" after
> the coltype (it's the default).
Easy to accomodate; I think this is generally the default so we can eliminate the ones that are superfluous.
> - It's a column definition is of type varchar, the default value cannot be 0.
this is probably a typo, and should be corrected
> - If I use db2 -tvf createdb.sql, it seems to fail when comments are
> embedded in a create table definition.
Mostly my fault; I like to use per-column comments. We should remove them or filter these out of the -raw files during processing.
> In addition to that I had to add extra keywords to the foreign key
> constraints, so an extra property at the end of the contraints would
> do me a great deal of good. I'll keep you posted on the rest of the
> progress to actually connect the WAR to DB2.
Easy.
> To achieve this, I wrote a python script that did all of this for me,
> but I'd hope we could add this support to Roller right out of the
> box.
I'm curious about the name transformations you applied; perhaps this is the approach we should use in general for dbs that support
only very short names.