You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Brian McCallister <br...@apache.org> on 2004/09/18 21:47:19 UTC
SQL/DDL Limitations (and DB2)
There are a few artificial constraints (1) imposed on Derby, possibly
in the interest of making the DDL/SQL/etc exactly compatible with DB2
compared to prior versions of Cloudscape. In at least some cases (2)
these constraints are different from the SQL spec, and older versions
of Cloudscape (3)
While I understand that the IBM developers may not be in a position to
undo these changes, is there any *technical* reason to limit Derby in
this way? If not, will patches submitted to undo these limitations be
accepted?
-Brian
(1)
http://www-306.ibm.com/software/data/cloudscape/pubs/cloudscape10-
migrate.html#Header_15
(2)
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-
dev@db.apache.org&msgNo=411
(3)
http://cermics.enpc.fr/~rl/cloudscape/doc/html/coredocs/
sqlj3.htm#1013025
Re: SQL/DDL Limitations (and DB2)
Posted by Jan Hlavaty <hl...@code.cz>.
I think it is responsibility of the database application authors to
account for any such limits of databse they intend to later upgrade to.
They don't need to be forced to limit the names - they should know what
they are doing. We should not limit others that don't intend to upgrade
to these databases at all because of someone else might want to upgrade
to database with severe limitations - otherwise we would end up
implementing only the least common denominator of functionality. And
thats not a way to bring progress ;-)
It is not our fault that database vendors sabotaged the standards in
order to remain exclusive and keep customers that would otherwise
happily migrate to other databases that suit them better.
Extending the allowed names length to SQL limits of 128 wont break any
existing applications that use Derby/Cloudscape.
We should do it.
Re: SQL/DDL Limitations (and DB2)
Posted by Joel Rosi-Schwartz <Jo...@Etish.org>.
On Monday 20 September 2004 21:40, Satheesh Bandaram wrote:
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> <title></title>
> </head>
> <body bgcolor="#ffffff" text="#000000">
> I did some searching on the internet. I found this table about some of
> the DDL maximum lenghts. (Haven't verified) If Derby increases
> constraint name length to 128, looks like it would break application
> migration to both Oracle and DB2, the top two enterprise databases.<br>
> <br>
> While increasing the limit makes life easier to migrate applications to
> Derby, wouldn't it make it harder to migrate out of Derby to enterprise
> databases? Being a free Apache product, Derby might actually have to
> pay attention to both migration routes ...<br>
> <br>
> Satheesh<br>
> <br>
> <table border="1" cellpadding="2" cellspacing="2" height="191"
> width="536">
> <tbody>
> <tr>
> <td valign="top"><b>Identifier maximum length (characters)</b><br>
> </td>
> <td valign="top"><b>Oracle 9</b><br>
> </td>
> <td valign="top"><b>DB2 8.1</b><br>
> </td>
> <td valign="top"><b>SQL Server 2000</b><br>
> </td>
> </tr>
> <tr>
> <td valign="top">Table name length </td>
> <td valign="top">30<br>
> </td>
> <td valign="top">128<br>
> </td>
> <td valign="top">128<br>
> </td>
> </tr>
> <tr>
> <td valign="top">Column name length</td>
> <td valign="top">30<br>
> </td>
> <td valign="top">30<br>
> </td>
> <td valign="top">128<br>
> </td>
> </tr>
> <tr>
> <td valign="top">Constraint name length<br>
> </td>
> <td valign="top">30<br>
> </td>
> <td valign="top">18<br>
> </td>
> <td valign="top">128<br>
> </td>
> </tr>
> <tr>
> <td valign="top">Index name length </td>
> <td valign="top">30<br>
> </td>
> <td valign="top">128<br>
> </td>
> <td valign="top">128<br>
> </td>
> </tr>
> <tr>
> <td valign="top">Number of table columns<br>
> </td>
> <td valign="top">1000<br>
> </td>
> <td valign="top">255<br>
> </td>
> <td valign="top">1023<br>
> </td>
> </tr>
> </tbody>
> </table>
> <br>
> Jason Rimmer wrote:<br>
> <blockquote cite="mid414DC322.2000102@irth.net"
> type="cite"> While a reasonable suggestion on its
> face adoption puts Derby on a slippery slope. Why favor DB2?
> Why not add transition flags for any 'enterprise-class' database such as P
> determine a destiny of its own. (Though I understand that such a
> determination could lead to the
> maintenance of these DB2 compatibility flags).
> <br>
This is a good point. We should also be taking into account, however, of how
this issue effects prospective legacy user applications. For example, I think
that Derby would make the perfect default database for Jira. I run Jira in
house and I was planning on replacing my Mckoi database with Derby. A quick
look at the entity defs for Jira changed my mind. They do not have support
for DB2 and it would be bit painful to go through all of the constraint names
and redo. I any case more than I was willing to bite off at the moment. How
many other existing applications are there that would otherwise find it
attractive to use Derby. Possibly the answers is to use Oracle limits as the
high side. I think most packaged applications could live with that.
> <br>
> Joel Rosi-Schwartz wrote:
> <br>
> <blockquote type="cite">I am just guessing that IBM would be less
> than overjoyed if Derby lost its ability to be an easy migration path
> to DB2. Would it not be fairly reasonable, however, to fulfil both
> requirements. At database creation time a flag could be set to dictate
> DB2 mode or extended mode. The database could then set an immutable
> database level property and behave accordingly. True this would
> introduce some complexity into the system, but it would be politically
> sensitive while still achieving better functionality.
> <br>
> <br>
> - joel
> <br>
> </blockquote>
> <br>
> </blockquote>
> </body>
> </html>
Re: SQL/DDL Limitations (and DB2)
Posted by Kathy Saunders <ka...@Source-Zone.com>.
In my opinion Derby should make decisions based on what is the best
thing for the Derby project, not just how it works with DB2. The
proposal for submitting Derby to Apache at
http://incubator.apache.org/derby/derby_proposal.html talks about
promoting a standards-based, database-agnostic approach to application
development. I believe that we should be sure changes we make adhere to
standards. So, I believe that you should go ahead and submit the
"patch". As it changes functionality, I see it more of an enhancement
which may require a vote, but I'm still learning about the process.
Having said that, you should note that the decision was made to make
Cloudscape DB2 compatible prior to IBM's decision to contribute the code
to Apache. Much of this work was done before the Apache decision was
made. There may be places where reasonable adjustments can be made
within standards guidelines, assuming the Derby community agrees. I
personally believe that we should maintain general compatibility with
enterprise databases, including Oracle, Sybase, Informix, DB2 etc,.
where possible. The standards position should help. I agree with Jason
that transition flags would be a painful way to go.
Kathy
Jason Rimmer wrote:
> While a reasonable suggestion on its face adoption puts Derby on a
> slippery slope. Why favor DB2? Why not add transition flags for any
> 'enterprise-class' database such as PostgreSQL, Oracle, Sybase,
> Informix, and what the heck SQL Server, MySQL, and Firebird as well?
> The tracking of versions and capabilities alone introduces maintenance
> issues of the most enjoyable variety.
> No, Derby should be its own database. Better yet, if the
> developer base so determines Derby could be an 'enterprise-class'
> database rivaling any previously listed. I don't see where political
> sensitivity even enters the equation. While I certainly appreciate
> and am grateful for IBM's contribution of Derby's original source, in
> order for the project to flourish it will have to determine a destiny
> of its own. (Though I understand that such a determination could lead
> to the maintenance of these DB2 compatibility flags).
> If you love it, set it free.
>
> Joel Rosi-Schwartz wrote:
>
>> I am just guessing that IBM would be less than overjoyed if Derby
>> lost its ability to be an easy migration path to DB2. Would it not be
>> fairly reasonable, however, to fulfil both requirements. At database
>> creation time a flag could be set to dictate DB2 mode or extended
>> mode. The database could then set an immutable database level
>> property and behave accordingly. True this would introduce some
>> complexity into the system, but it would be politically sensitive
>> while still achieving better functionality.
>>
>> - joel
>
>
Re: SQL/DDL Limitations (and DB2)
Posted by Thomas Hawtin <ta...@claranet.com>.
Satheesh Bandaram wrote:
> I did some searching on the internet. I found this table about some of
> the DDL maximum lenghts. (Haven't verified) If Derby increases
> constraint name length to 128, looks like it would break application
> migration to both Oracle and DB2, the top two enterprise databases.
My 2p: A couple years back I had to work with an Oracle schema which had
a consistent set of naming conventions, including for foreign key
constraints. That was great because I was, as a side effect, attempting
to generate their DDL. One of the problems I ran into was that a few of
the names were too big. The human had then abbreviated arbitrarily.
I don't like the idea of introducing random constraints which will
inconvenience.
Tom Hawtin
Re: SQL/DDL Limitations (and DB2)
Posted by Jason Rimmer <jr...@irth.net>.
While a reasonable suggestion on its face adoption puts Derby on a
slippery slope. Why favor DB2? Why not add transition flags for any
'enterprise-class' database such as PostgreSQL, Oracle, Sybase,
Informix, and what the heck SQL Server, MySQL, and Firebird as well?
The tracking of versions and capabilities alone introduces maintenance
issues of the most enjoyable variety.
No, Derby should be its own database. Better yet, if the developer
base so determines Derby could be an 'enterprise-class' database
rivaling any previously listed. I don't see where political sensitivity
even enters the equation. While I certainly appreciate and am grateful
for IBM's contribution of Derby's original source, in order for the
project to flourish it will have to determine a destiny of its own.
(Though I understand that such a determination could lead to the
maintenance of these DB2 compatibility flags).
If you love it, set it free.
Joel Rosi-Schwartz wrote:
> I am just guessing that IBM would be less than overjoyed if Derby lost its
> ability to be an easy migration path to DB2. Would it not be fairly
> reasonable, however, to fulfil both requirements. At database creation time a
> flag could be set to dictate DB2 mode or extended mode. The database could
> then set an immutable database level property and behave accordingly. True
> this would introduce some complexity into the system, but it would be
> politically sensitive while still achieving better functionality.
>
> - joel
--
Jason Rimmer
jrimmer at irth dot net
Re: SQL/DDL Limitations (and DB2)
Posted by Joel Rosi-Schwartz <Jo...@Etish.org>.
I am just guessing that IBM would be less than overjoyed if Derby lost its
ability to be an easy migration path to DB2. Would it not be fairly
reasonable, however, to fulfil both requirements. At database creation time a
flag could be set to dictate DB2 mode or extended mode. The database could
then set an immutable database level property and behave accordingly. True
this would introduce some complexity into the system, but it would be
politically sensitive while still achieving better functionality.
- joel
On Saturday 18 September 2004 20:47, Brian McCallister wrote:
> There are a few artificial constraints (1) imposed on Derby, possibly
> in the interest of making the DDL/SQL/etc exactly compatible with DB2
> compared to prior versions of Cloudscape. In at least some cases (2)
> these constraints are different from the SQL spec, and older versions
> of Cloudscape (3)
>
> While I understand that the IBM developers may not be in a position to
> undo these changes, is there any *technical* reason to limit Derby in
> this way? If not, will patches submitted to undo these limitations be
> accepted?
>
> -Brian
>
> (1)
> http://www-306.ibm.com/software/data/cloudscape/pubs/cloudscape10-
> migrate.html#Header_15
> (2)
> http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-
> dev@db.apache.org&msgNo=411
> (3)
> http://cermics.enpc.fr/~rl/cloudscape/doc/html/coredocs/
> sqlj3.htm#1013025
Re: SQL/DDL Limitations (and DB2)
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Brian McCallister wrote:
> There are a few artificial constraints (1) imposed on Derby, possibly
> in the interest of making the DDL/SQL/etc exactly compatible with DB2
> compared to prior versions of Cloudscape. In at least some cases (2)
> these constraints are different from the SQL spec, and older versions
> of Cloudscape (3)
>
> While I understand that the IBM developers may not be in a position to
> undo these changes, is there any *technical* reason to limit Derby in
> this way? If not, will patches submitted to undo these limitations be
> accepted?
Such patches would be voted on by the community, as they are changes in
Derby's api. There may be justifible reasons to veto (-1 vote) a patch,
based on its (lack of) benefit to Derby, not to reasons associated with
IBM and/or DB2.
I believe that Derby stands as its own database as well as being a great
development database for later deployment on Derby or other databases
(see the scope section in the Derby proposal). I don't think Derby has
to go in only one direction or the other on those issues. So any change
should be made with the defined scope in mind.
Most likely increased limits would be in line with Derby's scope, but,
as an example, say if every other database had a constraint name limit
of 18 there would seem to be little benefit of Derby having a higher limit.
Dan.