RE: [stellation-res] SQL name length question

["Jonathan Gossage" <jgossage@xxxxxxxx>]

> >-----Original Message-----
> >From: stellation-res-admin@xxxxxxxxxxxxxxx
> >[mailto:stellation-res-admin@xxxxxxxxxxxxxxx]On Behalf Of Mark C.
> >Chu-Carroll
> >Sent: September 1, 2002 9:06 AM
> >To: stellation-res@xxxxxxxxxxxxxxx
> >Subject: RE: [stellation-res] SQL name length question
> >
> >
> >On Sun, 2002-09-01 at 16:48, Jonathan Gossage wrote:
> >>
> >>
> >> > >-----Original Message-----
> >> > >From: stellation-res-admin@xxxxxxxxxxxxxxx
> >> > >[mailto:stellation-res-admin@xxxxxxxxxxxxxxx]On Behalf Of Mark C.
> >> > >Chu-Carroll
> >> > >Sent: September 1, 2002 8:35 AM
> >> > >To: stellation-res@xxxxxxxxxxxxxxx
> >> > >Subject: [stellation-res] SQL name length question
> >> > >
> >> > >
> >> > >
> >> > >I did a quick look to check how long names can be in SQL, since
> >> > >prefixing is increasing the name length.  What I found was that
> >> > >officially, SQL names can be up to 30 characters, but that some DB2
> >> > >utilities will fail on column names that are not unique
> >within the first
> >> > >10 characters.
> >> > >
> >> > >I'm tentatively making the assumption that table names can be up to
> >> > >30 characters, and column names can be up to 10 characters.
> >Does anyone
> >> > >know if any of the databases out there are more restrictive
> >than this?
> >>
> >> MySQL allows up to 64 characters for both table and column
> >names. It also
> >> allows the
> >> use of restricted names as column names by quoting the name.
> >Opens up the
> >> possibility of using quoted names with databases that support them and
> >> prefixes otherwise. The down-side is an extra level of
> >indirection that will
> >> generate column names appropriately for each database type.
> >
> >For now, we're still generating the queries manually... So the column
> >names can't vary by database.
> >
> >Also, we had a summer student, named Annie Ying working on Stellation.
> >Annie had the very interesting observation that by using a database, we
> >enable extenders to write code to do things like data mining directly
> >against the database. It's a really interesting idea, which she's using
> >to try some really neat stuff. The basic idea behind what she's up to is
> >that there is some very interesting information in the development, but
> >that the standard repository interface is not well suited to doing
> >things like mining that information.
> >
> >I think that tools that bypass the standard repository interface can
> >be very interesting and valuable. (I also think that they should be
> >read-only with respect to the repository tables, but that's a different
> >issue.) So I'd like to make sure that we keep things in a form where
> >other tools can access the repository tables directly.
> >
> >This isn't an argument against adding an abstraction layer for SQL
> >DML... But it is an argument for making sure that any such layer
> >doesn't hinder the ability to access the database from outside of
> >the normal repository code. (Annie's code uses an access point to get
> >a database object, and then uses the database object to access the
> >tables.)
> >
> >	-Mark
> >

Are you going to make the assumption that other external tools will go
through an access point? If so the existing code could easily handle the
differences in column names by simply asking the access point for the name.
Incidentally, not only researchers, but administrators may be interested in
this ability which would allow the development of custom reporting to meet
individual organization requirements.

Are you looking at providing other access for languages besides Java? In
this case you have another interesting set of land mines to negotiate. In my
organization we have just completed a C/C++ interface to part of our Java
based product and the exercise had its' moments.

Jonathan