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
