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 Mamta Satoor <ms...@gmail.com> on 2007/04/04 21:07:31 UTC
DERBY-1478 : Making the correct collation type available in CreateTableNode for character string type columns
Hi,
I just finished committing a patch (revision 525568) which has added
collation type and collation derivation to DataTypeDescriptor and there are
apis to set and get those values out of DTDs.
As a next step on language based ordering Jira entry, I want to tackle
CREATE TABLE. Create table should pick up the collation requested by the
user at create database time and use that for character string type columns.
I have not though much about it and going to start researching into it. But
what is coming to my mind is that I can write a new api in DataDictionary to
return the right collation type (UCS_BASIC/TERRITORY_BASED) for user defined
tables and CREATE TABLE will use that collation type to set into DTDs.
I am not sure if that will cause a problem with system tables because I
don't know if system table creation goes through CREATE TABLE node. If they
do not, then I think what I am suggesting will work. System table creations
will need to make another api call on DataDictionary which will return the
collation type for character string types associated with system schema,
which is always UCS_BASIC. Another alternative is to hard code UCS_BASIC for
system table columns wherever they get created but I think it will be better
to have one central location to get both user and system collation types.
So, 2 new apis in DataDictionary
public int getCollationTypeForPersistentUserCharacterColumns() - this will
return value based on collation property
public int getCollationTypeForPersistentSystemCharacterColumns() - this will
return hard coded UCS_BASIC.
One improvement could be to have a single api in DataDictionary and pass the
user schema vs system schema flag to it and have DataDictionary check that
flag and return the correct collation.
public int getCollationType(boolean systemSchema) - if systemSchema true
then return UCS_BASIC, if systemSchema false, then return collation type
based on collation property.
Does anyone have an opinion on if I am on the right track?
Thanks,
Mamta
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode for character string type columns
Posted by Mamta Satoor <ms...@gmail.com>.
OK. I am also wondering about the 2 character sets that Derby 10.3 would
have. All the user schemas as per the wiki page would have USER character
set and the system schemas will have SQL_IDENTIFIER character associated
with them. I am wondering if we leave these at conceptual level and just
have the collation type correctly set on the SchemaDescriptor? ie no code
corresponding to character set in SchemaDescriptor?
Mamta
On 4/4/07, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
> > Yes, I thought of going that track, but that would involve saving the
> > collation type in SYSCHEMAS and thus some hard upgrade code. In soft
> > upgrade, we can't upgrade SYSCHEMAS, so some mechanism to have it
> > default to UCS_BASIC.
>
> No, no need to save anything in SYSSCHEMAs. The data dictionary could
> set the schema's default collation default type when loading.
> SchemaDescriptor has a isSystemSchema() method so it's clear which are
> system schemas and which are not.
>
>
> Dan.
>
> >
>
> >
> > The collation you are looking for is a property of the schema, see
> this
> > from your 1478 wiki page:
> >
> > > 2)<column definition> (of character string type) SQL spec Section
> > 11.1 <schema definition>, General Rule 3 specifies that the
> > character set associated with schema is used as the default
> > character set for all <column definitions>.
> >
> > Thus, since it's a property of the schema, the most logical place
> for
> > this is as a method on SchemaDescriptor,
> > e.g.
> >
> > public int getCollationType();
> >
> >
> > Dan.
> >
> >
> >
>
>
>
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode for character string type columns
Posted by Mamta Satoor <ms...@gmail.com>.
I have created Jira entry DERBY-2528 for work that needs to go in for
collation type in SD.
Mamta
On 4/5/07, Mamta Satoor <ms...@gmail.com> wrote:
>
> Just one clarificaiton, the reason I am having SD's constructor call DD to
> get the collation type rather than collationType simply getting passed to
> SD's constructor is that the later approach would require us to change all
> the places where a SD object is created and have them pass the additional
> collationType parameter to it's constructor. I searched through the code and
> there are several places where we create a SD.
>
> With my approach, we have to change code in only one place and that would
> be in SD's constructor (and ofcourse we need changes in DD as explained in
> my mail earlier. But the changes in DD are require no matter how SD gets the
> right value in it's collationType field).
>
> Mamta
>
>
> On 4/5/07, Mamta Satoor <ms...@gmail.com> wrote:
> >
> > Here is what I am thinking of how to make the correct default collation
> > Type available in SchemaDescriptor(SD).
> >
> > DataDictionary(DD) will have two additional fields and two additional
> > apis. The field names will be
> > int collationTypeOfSystemSchemas
> > int collationTypeOfUserSchemas
> > The apis associated with them would be
> > public int getCollationTypeOfSystemSchemas()
> > public int getCollationTypeOfUserSchemas()
> > DD in it's boot method will initialize collationTypeOfSystemSchemas to
> > StringDataValue.COLLATION_TYPE_UCS_BASIC In the same boot method, it
> > will check the collation property supplied through optional JDBC attribute
> > COLLATION and set collationTypeOfUserSchemas to
> > StringDataValue.COLLATION_TYPE_UCS_BASIC or
> > StringDataValue.COLLATION_TYPE_TERRITORY_BASED. This covers the changes
> > required in DD.
> >
> > Now coming to SD. SD will have a new field
> > int collationType
> > It will have a new api called
> > public int getCollationType()
> > This new field will get initialized in SD's constructor. SD's
> > constructor already gets DD and isSystem passed to it. SD can use these 2
> > parameters to initialize collationType. The additional code in SD's
> > constructor would look something like this
> > if (isSystem) //check if I am a system schema
> > collationType = DD.getCollationTypeOfSystemSchemas();
> > else
> > collationType = DD.getCollationTypeOfUserSchemas();
> >
> > I will go ahead and start implementing this. If anyone has any feedback,
> > please let me know.
> > Mamta
> >
> >
> > On 4/4/07, Daniel John Debrunner <djd@apache.org > wrote:
> > >
> > > Mamta Satoor wrote:
> > > > Yes, I thought of going that track, but that would involve saving
> > > the
> > > > collation type in SYSCHEMAS and thus some hard upgrade code. In soft
> > >
> > > > upgrade, we can't upgrade SYSCHEMAS, so some mechanism to have it
> > > > default to UCS_BASIC.
> > >
> > > No, no need to save anything in SYSSCHEMAs. The data dictionary could
> > > set the schema's default collation default type when loading.
> > > SchemaDescriptor has a isSystemSchema() method so it's clear which are
> > > system schemas and which are not.
> > >
> > >
> > > Dan.
> > >
> > > >
> > >
> > > >
> > > > The collation you are looking for is a property of the schema,
> > > see this
> > > > from your 1478 wiki page:
> > > >
> > > > > 2)<column definition> (of character string type) SQL spec
> > > Section
> > > > 11.1 <schema definition>, General Rule 3 specifies that the
> > > > character set associated with schema is used as the default
> > > > character set for all <column definitions>.
> > > >
> > > > Thus, since it's a property of the schema, the most logical
> > > place for
> > > > this is as a method on SchemaDescriptor,
> > > > e.g.
> > > >
> > > > public int getCollationType();
> > > >
> > > >
> > > > Dan.
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
>
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode for character string type columns
Posted by Mamta Satoor <ms...@gmail.com>.
Just one clarificaiton, the reason I am having SD's constructor call DD to
get the collation type rather than collationType simply getting passed to
SD's constructor is that the later approach would require us to change all
the places where a SD object is created and have them pass the additional
collationType parameter to it's constructor. I searched through the code and
there are several places where we create a SD.
With my approach, we have to change code in only one place and that would be
in SD's constructor (and ofcourse we need changes in DD as explained in my
mail earlier. But the changes in DD are require no matter how SD gets the
right value in it's collationType field).
Mamta
On 4/5/07, Mamta Satoor <ms...@gmail.com> wrote:
>
> Here is what I am thinking of how to make the correct default collation
> Type available in SchemaDescriptor(SD).
>
> DataDictionary(DD) will have two additional fields and two additional
> apis. The field names will be
> int collationTypeOfSystemSchemas
> int collationTypeOfUserSchemas
> The apis associated with them would be
> public int getCollationTypeOfSystemSchemas()
> public int getCollationTypeOfUserSchemas()
> DD in it's boot method will initialize collationTypeOfSystemSchemas to
> StringDataValue.COLLATION_TYPE_UCS_BASIC In the same boot method, it will
> check the collation property supplied through optional JDBC attribute
> COLLATION and set collationTypeOfUserSchemas to
> StringDataValue.COLLATION_TYPE_UCS_BASIC or
> StringDataValue.COLLATION_TYPE_TERRITORY_BASED. This covers the changes
> required in DD.
>
> Now coming to SD. SD will have a new field
> int collationType
> It will have a new api called
> public int getCollationType()
> This new field will get initialized in SD's constructor. SD's constructor
> already gets DD and isSystem passed to it. SD can use these 2 parameters to
> initialize collationType. The additional code in SD's constructor would look
> something like this
> if (isSystem) //check if I am a system schema
> collationType = DD.getCollationTypeOfSystemSchemas();
> else
> collationType = DD.getCollationTypeOfUserSchemas();
>
> I will go ahead and start implementing this. If anyone has any feedback,
> please let me know.
> Mamta
>
>
> On 4/4/07, Daniel John Debrunner <dj...@apache.org> wrote:
> >
> > Mamta Satoor wrote:
> > > Yes, I thought of going that track, but that would involve saving the
> > > collation type in SYSCHEMAS and thus some hard upgrade code. In soft
> > > upgrade, we can't upgrade SYSCHEMAS, so some mechanism to have it
> > > default to UCS_BASIC.
> >
> > No, no need to save anything in SYSSCHEMAs. The data dictionary could
> > set the schema's default collation default type when loading.
> > SchemaDescriptor has a isSystemSchema() method so it's clear which are
> > system schemas and which are not.
> >
> >
> > Dan.
> >
> > >
> >
> > >
> > > The collation you are looking for is a property of the schema, see
> > this
> > > from your 1478 wiki page:
> > >
> > > > 2)<column definition> (of character string type) SQL spec
> > Section
> > > 11.1 <schema definition>, General Rule 3 specifies that the
> > > character set associated with schema is used as the default
> > > character set for all <column definitions>.
> > >
> > > Thus, since it's a property of the schema, the most logical place
> > for
> > > this is as a method on SchemaDescriptor,
> > > e.g.
> > >
> > > public int getCollationType();
> > >
> > >
> > > Dan.
> > >
> > >
> > >
> >
> >
> >
>
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode for character string type columns
Posted by Mamta Satoor <ms...@gmail.com>.
Here is what I am thinking of how to make the correct default collation Type
available in SchemaDescriptor(SD).
DataDictionary(DD) will have two additional fields and two additional apis.
The field names will be
int collationTypeOfSystemSchemas
int collationTypeOfUserSchemas
The apis associated with them would be
public int getCollationTypeOfSystemSchemas()
public int getCollationTypeOfUserSchemas()
DD in it's boot method will initialize collationTypeOfSystemSchemas to
StringDataValue.COLLATION_TYPE_UCS_BASIC In the same boot method, it will
check the collation property supplied through optional JDBC attribute
COLLATION and set collationTypeOfUserSchemas to
StringDataValue.COLLATION_TYPE_UCS_BASIC or
StringDataValue.COLLATION_TYPE_TERRITORY_BASED. This covers the changes
required in DD.
Now coming to SD. SD will have a new field
int collationType
It will have a new api called
public int getCollationType()
This new field will get initialized in SD's constructor. SD's constructor
already gets DD and isSystem passed to it. SD can use these 2 parameters to
initialize collationType. The additional code in SD's constructor would look
something like this
if (isSystem) //check if I am a system schema
collationType = DD.getCollationTypeOfSystemSchemas();
else
collationType = DD.getCollationTypeOfUserSchemas();
I will go ahead and start implementing this. If anyone has any feedback,
please let me know.
Mamta
On 4/4/07, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
> > Yes, I thought of going that track, but that would involve saving the
> > collation type in SYSCHEMAS and thus some hard upgrade code. In soft
> > upgrade, we can't upgrade SYSCHEMAS, so some mechanism to have it
> > default to UCS_BASIC.
>
> No, no need to save anything in SYSSCHEMAs. The data dictionary could
> set the schema's default collation default type when loading.
> SchemaDescriptor has a isSystemSchema() method so it's clear which are
> system schemas and which are not.
>
>
> Dan.
>
> >
>
> >
> > The collation you are looking for is a property of the schema, see
> this
> > from your 1478 wiki page:
> >
> > > 2)<column definition> (of character string type) SQL spec Section
> > 11.1 <schema definition>, General Rule 3 specifies that the
> > character set associated with schema is used as the default
> > character set for all <column definitions>.
> >
> > Thus, since it's a property of the schema, the most logical place
> for
> > this is as a method on SchemaDescriptor,
> > e.g.
> >
> > public int getCollationType();
> >
> >
> > Dan.
> >
> >
> >
>
>
>
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode
for character string type columns
Posted by Daniel John Debrunner <dj...@apache.org>.
Mamta Satoor wrote:
> Yes, I thought of going that track, but that would involve saving the
> collation type in SYSCHEMAS and thus some hard upgrade code. In soft
> upgrade, we can't upgrade SYSCHEMAS, so some mechanism to have it
> default to UCS_BASIC.
No, no need to save anything in SYSSCHEMAs. The data dictionary could
set the schema's default collation default type when loading.
SchemaDescriptor has a isSystemSchema() method so it's clear which are
system schemas and which are not.
Dan.
>
>
> The collation you are looking for is a property of the schema, see this
> from your 1478 wiki page:
>
> > 2)<column definition> (of character string type) SQL spec Section
> 11.1 <schema definition>, General Rule 3 specifies that the
> character set associated with schema is used as the default
> character set for all <column definitions>.
>
> Thus, since it's a property of the schema, the most logical place for
> this is as a method on SchemaDescriptor,
> e.g.
>
> public int getCollationType();
>
>
> Dan.
>
>
>
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode for character string type columns
Posted by Mamta Satoor <ms...@gmail.com>.
Yes, I thought of going that track, but that would involve saving the
collation type in SYSCHEMAS and thus some hard upgrade code. In soft
upgrade, we can't upgrade SYSCHEMAS, so some mechanism to have it default to
UCS_BASIC.
Mamta
On 4/4/07, Daniel John Debrunner <dj...@apache.org> wrote:
>
> Mamta Satoor wrote:
> > Hi,
> >
> > I just finished committing a patch (revision 525568) which has added
> > collation type and collation derivation to DataTypeDescriptor and there
> > are apis to set and get those values out of DTDs.
> >
> > As a next step on language based ordering Jira entry, I want to tackle
> > CREATE TABLE. Create table should pick up the collation requested by the
> > user at create database time and use that for character string type
> > columns. I have not though much about it and going to start researching
> > into it. But what is coming to my mind is that I can write a new api in
> > DataDictionary to return the right collation type
> > (UCS_BASIC/TERRITORY_BASED) for user defined tables and CREATE TABLE
> > will use that collation type to set into DTDs.
> >
> > I am not sure if that will cause a problem with system tables because I
> > don't know if system table creation goes through CREATE TABLE node. If
> > they do not, then I think what I am suggesting will work. System table
> > creations will need to make another api call on DataDictionary which
> > will return the collation type for character string types associated
> > with system schema, which is always UCS_BASIC. Another alternative is to
> > hard code UCS_BASIC for system table columns wherever they get created
> > but I think it will be better to have one central location to get both
> > user and system collation types.
> >
> > So, 2 new apis in DataDictionary
> > public int getCollationTypeForPersistentUserCharacterColumns() - this
> > will return value based on collation property
> > public int getCollationTypeForPersistentSystemCharacterColumns() - this
> > will return hard coded UCS_BASIC.
> >
> > One improvement could be to have a single api in DataDictionary and pass
> > the user schema vs system schema flag to it and have DataDictionary
> > check that flag and return the correct collation.
> > public int getCollationType(boolean systemSchema) - if systemSchema true
> > then return UCS_BASIC, if systemSchema false, then return collation type
> > based on collation property.
> >
> > Does anyone have an opinion on if I am on the right track?
>
>
> The collation you are looking for is a property of the schema, see this
> from your 1478 wiki page:
>
> > 2)<column definition> (of character string type) SQL spec Section 11.1<schema definition>, General Rule 3 specifies that the character set
> associated with schema is used as the default character set for all <column
> definitions>.
>
> Thus, since it's a property of the schema, the most logical place for
> this is as a method on SchemaDescriptor,
> e.g.
>
> public int getCollationType();
>
>
> Dan.
>
>
>
Re: DERBY-1478 : Making the correct collation type available in CreateTableNode
for character string type columns
Posted by Daniel John Debrunner <dj...@apache.org>.
Mamta Satoor wrote:
> Hi,
>
> I just finished committing a patch (revision 525568) which has added
> collation type and collation derivation to DataTypeDescriptor and there
> are apis to set and get those values out of DTDs.
>
> As a next step on language based ordering Jira entry, I want to tackle
> CREATE TABLE. Create table should pick up the collation requested by the
> user at create database time and use that for character string type
> columns. I have not though much about it and going to start researching
> into it. But what is coming to my mind is that I can write a new api in
> DataDictionary to return the right collation type
> (UCS_BASIC/TERRITORY_BASED) for user defined tables and CREATE TABLE
> will use that collation type to set into DTDs.
>
> I am not sure if that will cause a problem with system tables because I
> don't know if system table creation goes through CREATE TABLE node. If
> they do not, then I think what I am suggesting will work. System table
> creations will need to make another api call on DataDictionary which
> will return the collation type for character string types associated
> with system schema, which is always UCS_BASIC. Another alternative is to
> hard code UCS_BASIC for system table columns wherever they get created
> but I think it will be better to have one central location to get both
> user and system collation types.
>
> So, 2 new apis in DataDictionary
> public int getCollationTypeForPersistentUserCharacterColumns() - this
> will return value based on collation property
> public int getCollationTypeForPersistentSystemCharacterColumns() - this
> will return hard coded UCS_BASIC.
>
> One improvement could be to have a single api in DataDictionary and pass
> the user schema vs system schema flag to it and have DataDictionary
> check that flag and return the correct collation.
> public int getCollationType(boolean systemSchema) - if systemSchema true
> then return UCS_BASIC, if systemSchema false, then return collation type
> based on collation property.
>
> Does anyone have an opinion on if I am on the right track?
The collation you are looking for is a property of the schema, see this
from your 1478 wiki page:
> 2)<column definition> (of character string type) SQL spec Section 11.1 <schema definition>, General Rule 3 specifies that the character set associated with schema is used as the default character set for all <column definitions>.
Thus, since it's a property of the schema, the most logical place for
this is as a method on SchemaDescriptor,
e.g.
public int getCollationType();
Dan.