I have updated the functional spec based on some feedback from Dan. The changes to the spec in brief are,
a)The collation ordering will apply only to CHAR and VARCHAR types
2)The collation for system table will continue to be code point based no matter what collation user chooses for CHAR and VARCHAR types.
3)I will change the JDBC attribut to accept String values. This way, this feature can be expanded in future to support more variety of collations rather than just the code point based ordering and territory based ordering.
The functional spec now looks as follows
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 collation, at the database create time and that attribute can be set to one of the following 2 values
1) UCS_BASIC (This means codepoint based collation. This will also be the default collation used by Derby if no collation attribute is specified on the JDBC url at the database create time. This collation is what Derby 10.2 and prior have supported) or
2)TERRITORY_BASED_COLLATION (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. Derby 10.3 will continue to use the same mecahnism to determine the territory.)
The collation attribute will apply only for CHAR and VARCHAR datatypes defined in a user-defined table. System tables will continue to use codepoint based collation for it's CHAR and VARCHAR columns.
This collation ordering will impact operations that require returning the order of data on CHAR and VARCHAR columns. 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.
I am not planning to implement any new collation support on any existing database, ie JDBC attribute collation 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.
Other than finding a means of storing the collation attribute from the JDBC url somewhere, I don't anticipate any other disk changes as part of this project.