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([EMAIL PROTECTED]@); Elias PS. I wouldn't call DB2 obscure, but maybe I'm biased. On 8/23/05, Allen Gilliland <[EMAIL PROTECTED]> wrote: > On Tue, 2005-08-23 at 09:18, Elias Torres wrote: > > On 8/23/05, Anil Gangolli <[EMAIL PROTECTED]> wrote: > > > > > > ----- Original Message ----- > > > From: "Elias Torres" <[EMAIL PROTECTED]> > > > To: <[email protected]> > > > 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 > >
