Dear Wiki user,
You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.
The following page has been changed by MamtaSatoor:
http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478
------------------------------------------------------------------------------
http://www.nabble.com/Collation-feature-discussion-tf3418026.html#a9559634
== Outstanding items ==
- 1)Add jdbc url attribute COLLATION into services.properties as derby.database.collation
property. If no COLLATION is specified at database create time, then have UCS_BASIC as the
value for derby.database.collation We need the property in the services.properties rather
than properties conglomerate because DataValueFactory needs this property before store has
been booted completely.
+ 1)At the time of upgrade of pre-10.3 database, we should make sure that derby.database.collation
property with value UCS_BASIC in added to properties conglomerate. This is because we do not
plan on supporting collation change for existing databases. All of the pre-10.3 databases
will continue to use their old collation (UCS_BASIC) after upgrade to 10.3 release.
- '''Question''' Why does DVF need this property before the store has booted?
+ 2)Store column level metadata for collate in Store.
- 2)At the time of upgrade of pre-10.3 database, we should make sure that derby.database.collation
property with value UCS_BASIC in added to services.properties. This is because we do not plan
on supporting collation change for existing databases.
+ 3)Store column level metadata for collate in Language Layer as well. This will happen in
DataTypeDescriptor(DTD) with the addition of int collateType field. It will be set to 0(UCS_BASIC)/1(TERRITORY_BASED)/-1(UNKNOWN).
There will be get and set methods on DTD for this new field.
- 3)Store column level metadata for collate in Store.
+ 4)The TypeDescriptor for character columns always has 0 for scale because scale does not
apply to character datatypes. Starting Derby 10.3, this scale field in TypeDescriptor will
be overloaded to indicate the collate type of the character. So, if user has requested for
TERRITORY_BASED collation, then the scale in TypeDescriptor for user columns(character) will
be 1(TERRITORY_BASED). The scale will be always 0(UCS_BASIC) for SYS schema character columns
and for databases with collation set to UCS_BASIC.
- 4)Store column level metadata for collate in Language Layer as well. This will happen in
DataTypeDescriptor(DTD) with the addition of int collateType field. It will be set to 0(UCS_BASIC)/1(TERRITORY_BASED)/-1(UNKNOWN).
There will be get and set methods on DTD for this new field.
+ 5)When a character column is added using CREATE TABLE/ALTER TABLE, make sure that the correct
collate type is populated in the TypeDescriptor's scale field in the SYS.SYSCOLUMNS table.
- 5)The TypeDescriptor for character columns always has 0 for scale because scale does not
apply to character datatypes. Starting Derby 10.3, this scale field in TypeDescriptor will
be overloaded to indicate the collate type of the character. So, if user has requested for
TERRITORY_BASED collation, then the scale in TypeDescriptor for user columns(character) will
be 1(TERRITORY_BASED). The scale will be always 0(UCS_BASIC) for SYS schema character columns
and for databases with collation set to UCS_BASIC.
+ 6)For both a newly created 10.3 database and an upgraded 10.3 database, make sure that the
scale for character datatypes continue to be 0 (rather than the collation type value) through
the metadata. The overloading of scale in TypeDescriptor as collation for character datatypes
should be transparent to the end user. We should include test for the scale of character datatype.
- 6)When a character column is added using CREATE TABLE/ALTER TABLE, make sure that the correct
collate type is populated in the TypeDescriptor's scale field in the SYS.SYSCOLUMNS table.
+ 7)Override all the collation related methods in the CollatorSQLChar. CollatorSQLChar is
a subclass of SQLChar.
- 7)For both a newly created 10.3 database and an upgraded 10.3 database, make sure that the
scale for character datatypes continue to be 0 (rather than the collation type value) through
the metadata. The overloading of scale in TypeDescriptor as collation for character datatypes
should be transparent to the end user. We should include test for the scale of character datatype.
+ 8)Add subclasses for SQLVarchar, SQLLongvarchar, SQLClob. These subclasses will override
the collation related methods in their superclasses.
- 8)Override all the collation related methods in the CollatorSQLChar. CollatorSQLChar is
a subclass of SQLChar.
-
- 9)Add subclasses for SQLVarchar, SQLLongvarchar, SQLClob. These subclasses will override
the collation related methods in their superclasses.
-
- 10)CollatorSQLChar has a method called getCollationElementsForString which currently gets
called by like method. getCollationElementsForString gets the collation elements for the value
of CollatorSQLChar class. But say like method is looking for pattern 'A%' and the value of
CollatorSQLChar is 'BXXXXXXXXXXXXXXXXXXXXXXX'. This is eg of one case where it would have
been better to get collation element one character of CollatorSQLChar value at a time so we
don't go through the process of getting collation elements for the entire string when we don't
really need. This is a performance issue and could be taken up at the end of the implementation.
Comments on this from Dan and Dag can be found in DERBY-2416.
+ 9)CollatorSQLChar has a method called getCollationElementsForString which currently gets
called by like method. getCollationElementsForString gets the collation elements for the value
of CollatorSQLChar class. But say like method is looking for pattern 'A%' and the value of
CollatorSQLChar is 'BXXXXXXXXXXXXXXXXXXXXXXX'. This is eg of one case where it would have
been better to get collation element one character of CollatorSQLChar value at a time so we
don't go through the process of getting collation elements for the entire string when we don't
really need. This is a performance issue and could be taken up at the end of the implementation.
Comments on this from Dan and Dag can be found in DERBY-2416.
== Implemented items ==
- 1)A shell for subclass of SQLChar has been implemented and it is called CollatorSQLChar.
It resides in derby.iapi.types package.
+ 1)A shell for subclass of SQLChar has been implemented and it is called CollatorSQLChar.
It resides in derby.iapi.types package. This work was done by revisions 516864, 516869 and
518479.
+
+ 2)At the time of database create time, optional JDBC url attribute COLLATION is validated
by the boot code in data dictionary and the validated value of COLLATION(if none specified
by user, then it will default to UCS_BASIC which is also the only collation available on pre-10.3
databases) attribute is saved as derby.database.collation property in the properties conglomerate.
This work was done by revision 511283
== Related Pages ==