db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "BuiltInLanguageBasedOrderingDERBY-1478" by MamtaSatoor
Date Sat, 31 Mar 2007 23:52:10 GMT
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:

  As per SQL spec, Section 11.1<schema definition>, there is an optional syntax to associate
a character set for a schema at create schema time. Syntax Rule 5 says that if a character
set is not specified by the user, then the character set associated with schema is implementation
defined. In Derby 10.3, system schemas will be associated with SQL_IDENTIFER character set
and all the user schemas will be associated with USER character set. Futher on, General Rule
3 specifies that the character set associated with schema is used as the default character
set for all <column definitions>. Based on this, all the user character columns will
pick up the collation associated with USER character set and all the system character columns
will pick the up the collation associated with SQL_IDENTIFIER character set. 
  The character set specification for string literals in SQL specification is not as well
defined as for <column definitions>. SQL spec Section 5.3<literal>, Syntax Rule
14b says that if the character set is not specified for character string literal, then character
string literal's character set will be the character set of the SQL-client module. Derby does
not implement SQL-client module, but definition of SQL-client module in Section 13.1 says
that SQL-client module definition has mandatory <module name clause> which is defined
in Section 13.2 <module name clause>. The Syntax Rule 4 in this section says that if
a character set is not specified for the SQL-client module, then it's character set is "implementation-defined".
For Derby, this implementation-defined character set for SQL-client module will be USER. And
hence the string literals will have the USER character set associated with them.
+ Some other background information on collation derivation and collation type from SQL spec
: SQL spec talks about character string types having collation type and collation derivation
associated with them (SQL spec Section 4.2.2 Comparison of character strings). If collation
derivation says explicit or implicit, then it means that there is a valid collation type associated
with the charcter string type. If the collation derivation is none, then it means that collation
type can't be established for the character string type. 
+ 1)Collation derivation will be explicit if COLLATE clause has been used for character string
type (this is not a possibility for Derby 10.3, because we are not planning to support SQL
COLLATE clause in this release). 
+ 2)Collation derivation will be implicit if the collation can be determined w/o the COLLATE
clause eg CREATE TABLE t1(c11 char(4)) then c11 will have collation of USER character set.
Another eg, TRIM(c11) then the result character string of TRIM operation will have collation
of the operand, c11. 
+ 3)Collation derivation will be none if the aggregate methods are dealing with character
strings with different collations (Section 9.3 Data types of results of aggregations Syntax
Rule 3aii). 
  == Outstanding items ==
  1)Store column level metadata for collate in Store. Store keeps a version number that describes
the strucutre of column level metadata. For existing pre-10.3 databases which get upgraded
to 10.3 and for new 10.3 databases with default collatoin(UCS_BASIC), the structure of column
level metadata will remain same as 10.2 structure of column level metadata, ie they will not
include collate information in their store metadata. A new version would be used in Store
for structure of column level metadata if the newly created 10.3 database has asked for territory
based collation. In other words, information about collate will be kept in Store column level
metadata only if we are working with a 10.3 newly created database with territory based collation.
This approach will make sure that we do not have to do an on-disk store metadata upgrade when
upgrading a pre-10.3 database to 10.3 version.

View raw message