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