db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1478) Add built in language based ordering and like processing to Derby
Date Wed, 20 Dec 2006 17:11:22 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1478?page=comments#action_12459997 ] 
Mamta A. Satoor commented on DERBY-1478:

I would like to propose a way for supporting locale sensitive data in Derby. Currently, upto
Derby 10.2 release, the sorting for CHAR and VARCHAR data types is codepoint based (UNICODE).
For someone looking for any locale specific collation, they can possibly write a couple functions
as suggested by  http://wiki.apache.org/db-derby/LanguageBasedOrdering, but that solution
is not complete and not efficient (since functional indexes can't be defined in Derby). 

My proposal for Derby 10.3 is that a user would be able to specify an optional jdbc url attribute,
called territoryBasedCollation, at the database create time and that attribute can be set
to true or false. If the attribute is not specified or is set to false, then collation will
continue to be codepoint based. But if the user specifies true for territoryBasedCollation,
the collation will be based on language region specified by the exisiting Derby attribute
called territory (territory=ll_CC) http://db.apache.org/derby/docs/10.2/ref/rrefattrib56769.html
If the territory attribute is not specified at the database create time, Derby 10.2 uses java,util.Locale.getDefault
to determine the territory for the newly
created database.

I am not planning to implement any collation support on any existing database, ie collation
enabling will not be supported at the upgrade database time or on a pre-existing database.
Those databases will continue to use codepoint based collation. I am proposing to implement
the collation support only for new databases,

The locale based ordering will impact operations that require returning the order of data.
That includes
1)Comparison using comparison operators (<, >, =, IN, BETWEEN)
2)Statements that involve sorting (ORDER BY, GROUP BY, DISTINCT, MAX, and MIN)
3)Statements that use the LIKE keyword

Derby already has lot of code for locale based ordering for disabled NATIONAL CHAR and NATIONAL
VARCHAR datatypes. I hope to leverage highly on that code and see how it can be used for this
project. Also, I am keeping a goal for myself to implement this in such a way that databases
with codepoint based collation will not get penalized by the code for locale based collation.

Other than finding a means of storing the territoryBasedCollation attribute from the url somewhere,
I don't anticipate any other disk changes as part of this project.

Please share if there are any comments. In the mean time, I will start looking at how to accept
the new jdb url attribute in the create database url and how to store that attribute .

> Add built in language based ordering and like processing to Derby
> -----------------------------------------------------------------
>                 Key: DERBY-1478
>                 URL: http://issues.apache.org/jira/browse/DERBY-1478
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>            Reporter: Kathey Marsden
>         Assigned To: Mamta A. Satoor
> It would be good for Derby to have built in Language based ordering based on locale specific
> Language based ordering is an important feature for international deployment.  DERBY-533
offers one implementation option for this but according to the discussion in that issue National
Character Types carry a fair amount of baggage with them especially in the form of concerns
about conversion   to and from datetime and number types. Rick  mentioned SQL language for
collations as an option for language based ordering. There may be other options too, but I
thought it worthwhile to add an issue for the high level functional concern, so the best choice
can be made for implementation without assuming that National Character Types is the only
> For possible 10.1 workaround and examples see:
> http://wiki.apache.org/db-derby/LanguageBasedOrdering

This message is automatically generated by JIRA.
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira


View raw message