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 Kathey Marsden <km...@sbcglobal.net> on 2006/07/07 21:01:26 UTC
Language based matching
Does anyone know of an easy built in Java mechanism for Locale
sensitive matching?
I continue to work with a user trying to develop a strategy for language
based string type handling in Derby 10.1.
The ordering seems doable with the approach in
http://wiki.apache.org/db-derby/LanguageBasedOrdering
For <, =. > comparisons I was able to implement a LOCALE_COMPARE
function pretty easily using Collators as well,
but matching (LIKE replacement) seems harder. For example in
Norwegian we need to have "aa" be treated as one character and in the US
have it treated as two. So given the values acorn, aacorn, and aass (
a Norwegian brewery) , and matching "a.*", we should see three rows in
english and just one in Norwegian.
e.g.
With function LOCALE_MATCHES that returns 1 for a match and method
public static int localeMatches(String language, String country,
String pattern, String value)
{
....
}
we should see:
SELECT ID, NAME FROM CUSTOMER WHERE LOCALE_MATCHES('no','NO', 'a.*',
NAME) = 1
7 , acorn
SELECT ID, NAME FROM CUSTOMER WHERE LOCALE_MATCHES('en','US', 'a.*',
NAME) = 1
6 , aacorn
7 , acorn
8 , aass
I looked at java.util.regex to see if there were Locale related API's
but I don't see them and tried to see if the results of
java.lang.String.matches() vary at all if I change my default Locale.
They don't. I also looked at some of the server Like code in the hopes
of finding something easy I could pull out into a function. What I
found was a world of RuleBasedCollators, CollationElementIterators etc
and thought I better ask if there is an easier way before heading off
to try to understand that.
Any help appreciated. I'll summarize any knowledge gained on the Wiki.
Kathey
Re: Language based matching
Posted by Daniel John Debrunner <dj...@apache.org>.
Bernt M. Johnsen wrote:
> "aa" as one letter was removed from the Norwegian language in 1938 ("å"
> had been optional since 1917). It is only used in names today and it is
> true what Anders says about the phonebook (also about the foreign names
> where "aa" is treated like two letters). I don't think it would be wise
> to not let "a.*" match "Aasen" (wich in modern writing would be Åsen).
I think Kathey was just using this as an example of how the LIKE
operator can be different when using locale based collation.
I would hope that Derby or a workaround would use the locale collation
alrady provided by Java and not set up a home-grown scheme. Thus the
rules for aa in Norwegian would come from the JDK.
Dan.
Re: Language based matching
Posted by Bryan Pendleton <bp...@amberpoint.com>.
>> "Is there some easy Java regular expression matching function like
>> String.matches(Collator collator, String pattern, String value)? "
Here's an article from 1999 in which some person apparently decided
that they needed to write such a thing themselves.
Warning: the code described in this article appears to be patented,
and thus probably isn't acceptable for an Open Source project such as ours.
http://www-128.ibm.com/developerworks/java/library/j-text-searching.html
thanks,
bryan
Re: Language based matching
Posted by Rick Hillegas <Ri...@Sun.COM>.
Kathey Marsden wrote:
> Rick Hillegas wrote:
>
> [ some interesting stuff about performance]
>
>> LIKE is going to be a pile of work. I think your LOCALE_MATCHES
>> function will have to duplicate a lot of the code in Derby. At the
>> end of the day, you will replace LIKE with LOCALE_MATCHES and so lose
>> the performance-enhancing query pre-processing which DERBY does for
>> %. Here the weeds have become too thick for me.
>>
> So are you saying the answer to the question:
> "Is there some easy Java regular expression matching function like
> String.matches(Collator collator, String pattern, String value)? "
>
> is "No"
>
> Kathey
>
I don't know. I haven't looked deeply into this. Cursory googling didn't
find anything.
Re: Language based matching
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
[ some interesting stuff about performance]
> LIKE is going to be a pile of work. I think your LOCALE_MATCHES
> function will have to duplicate a lot of the code in Derby. At the end
> of the day, you will replace LIKE with LOCALE_MATCHES and so lose the
> performance-enhancing query pre-processing which DERBY does for %.
> Here the weeds have become too thick for me.
>
So are you saying the answer to the question:
"Is there some easy Java regular expression matching function like
String.matches(Collator collator, String pattern, String value)? "
is "No"
Kathey
Re: Language based matching
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Kathey,
My gut feeling is that you are headed off into the tall weeds here. That
said, let me walk with you part way into the swamp. Another feature you
might want would be DERBY-481, computed columns. This would help you get
better performance. So, for instance, you could declare your data like this:
create table foo
(
name varchar(20)
nameKey varchar(60) for bit data generated always as ( locale_order(
'pl', 'PL', name )
);
create index foo_idx on foo( nameKey );
Lacking DERBY-481, you might get away with
create table foo
(
name varchar(20)
nameKey varchar(60) for bit data
);
create index foo_idx on foo( nameKey );
and then use triggers or procedures to populate the nameKey column based
on the value in name.
Then you could get decent performance if you did ORDER BY and GROUP BY
on foo.nameKey. Other operations might look like this:
select * from foo
where nameKey in ( locale_order( 'pl', 'PL', 'dsfaf' ), ... );
select * from foo
where nameKey between locale_order( 'pl', 'PL', 'lkjh' ) and
locale_order( 'pl', 'PL', 'mnbv' );
select * from foo
where nameKey < locale_order( 'pl', 'PL', 'asdfgf'' )
LIKE is going to be a pile of work. I think your LOCALE_MATCHES function
will have to duplicate a lot of the code in Derby. At the end of the
day, you will replace LIKE with LOCALE_MATCHES and so lose the
performance-enhancing query pre-processing which DERBY does for %. Here
the weeds have become too thick for me.
Kathey Marsden wrote:
> Rick Hillegas wrote:
>
>>
>> 3) The locale-sensitive meaning of <, =, and > affected the operation
>> of all orderings of national strings, including sorts, indexes,
>> unions, group-by's, like's, between's, and in's.
>>
>> At one point I was keen on re-enabling the national string types. Now
>> I am leaning toward implementing the ANSI collation language. I think
>> this is more powerful. In particular, it lets you support more than
>> one language-sensitive ordering in the same database.
>>
>> You and your customer face a hard problem trying to migrate national
>> strings from Cloudscape 5.1.60 into Derby 10.1.3 or 10.2. I'm at a
>> loss how to do this in a way that preserves Cloudscape's performance.
>>
>
> Thank you so much Rick for helping me understand this stuff. For now
> lets just assume this is just a small dataset and set performance
> aside I am interested to know
> 1) When might Locale specific matching be different in the
> context WHERE value LIKE '%< >%' (or whatever language we use)
> besides the deprecated Norwegian 'aa' and when might this be useful?
> Is it somehow related to bidirectional data like Hebrew and Arabic?
I'm afraid I don't understand the question. I think you are going to
have to duplicate the LIKE processing code, splicing special characters
into subkeys created by LOCALE_ORDER. I don't understand the issues with
Semitic languages but I suspect that Arabic orthography creates some
interesting cases.
> 2) Is there some easy java code that can be used to accomplish
> writing a LOCALE_MATCHES(pattern,value) function?
I'm afraid I can't point you at anything easier than Derby's code.
> For the other functionality I have these equivalent functions to
> offer as a workaround (see
> http://wiki.apache.org/db-derby/LanguageBasedOrdering)
>
> ORDER BY - Use ORDER BY expression with LOCALE_ORDER function
> implemented with Collator.getCollationKey()
> <, =, > , BETWEEN - Use LOCALE_COMPARE function implemented with
> Collator.compare()
> IN - Since this is an exact match, would the non-locale specific
> matching work ok here?
I'm not sure I understand the question. I don't think you can get around
wrapping local_order around the left and right expressions:
select * from bar where locale_order( 'pl', 'PL', name ) in ( select
locale_order( 'pl', 'PL', name ) from wibble );
> GROUP-BY - No solution yet but GROUP BY expression in progress will
> allow LOCALE_ORDER to be used.
> *LIKE - ???????? * Is there some easy Java regular expression
> matching function like String.matches(Collator collator, String
> pattern, String value)? I can't find it. The code in
> org.apache.derby.iapi.types.Like looks pretty involved, but perhaps
> that is what is needed. I just want to confirm before I go down
> that path and try to figure it out.f
I agree that this looks pretty involved.
>
> Thanks
>
> Kathey
>
> P.S. I once came very close to getting a cash register meant to
> interface to a gas pump working in a Deli with a scale until Mother
> Nature stepped in and raised the Russian River to the point that it
> swallowed the whole thing up, so I have been known to try too hard for
> a workaround. If trying to workaround Locale specific processing
> with Derby with FUNCTIONS is a doomed enterprise, I welcome that
> perspective as historically I sometimes don't know when to give up.
>
Re: Language based matching
Posted by Kathey Marsden <km...@sbcglobal.net>.
Rick Hillegas wrote:
>
> 3) The locale-sensitive meaning of <, =, and > affected the operation
> of all orderings of national strings, including sorts, indexes,
> unions, group-by's, like's, between's, and in's.
>
> At one point I was keen on re-enabling the national string types. Now
> I am leaning toward implementing the ANSI collation language. I think
> this is more powerful. In particular, it lets you support more than
> one language-sensitive ordering in the same database.
>
> You and your customer face a hard problem trying to migrate national
> strings from Cloudscape 5.1.60 into Derby 10.1.3 or 10.2. I'm at a
> loss how to do this in a way that preserves Cloudscape's performance.
>
Thank you so much Rick for helping me understand this stuff. For now
lets just assume this is just a small dataset and set performance
aside I am interested to know
1) When might Locale specific matching be different in the context
WHERE value LIKE '%< >%' (or whatever language we use) besides the
deprecated Norwegian 'aa' and when might this be useful? Is it somehow
related to bidirectional data like Hebrew and Arabic?
2) Is there some easy java code that can be used to accomplish writing
a LOCALE_MATCHES(pattern,value) function?
For the other functionality I have these equivalent functions to offer
as a workaround (see http://wiki.apache.org/db-derby/LanguageBasedOrdering)
ORDER BY - Use ORDER BY expression with LOCALE_ORDER function
implemented with Collator.getCollationKey()
<, =, > , BETWEEN - Use LOCALE_COMPARE function implemented with
Collator.compare()
IN - Since this is an exact match, would the non-locale specific
matching work ok here?
GROUP-BY - No solution yet but GROUP BY expression in progress will
allow LOCALE_ORDER to be used.
*LIKE - ???????? * Is there some easy Java regular expression matching
function like String.matches(Collator collator, String pattern, String
value)? I can't find it. The code in
org.apache.derby.iapi.types.Like looks pretty involved, but perhaps that
is what is needed. I just want to confirm before I go down that path
and try to figure it out.
Thanks
Kathey
P.S. I once came very close to getting a cash register meant to
interface to a gas pump working in a Deli with a scale until Mother
Nature stepped in and raised the Russian River to the point that it
swallowed the whole thing up, so I have been known to try too hard for a
workaround. If trying to workaround Locale specific processing with
Derby with FUNCTIONS is a doomed enterprise, I welcome that
perspective as historically I sometimes don't know when to give up.
Re: Language based matching
Posted by Satheesh Bandaram <ba...@gmail.com>.
Rick Hillegas wrote:
> At one point I was keen on re-enabling the national string types. Now
> I am leaning toward implementing the ANSI collation language. I think
> this is more powerful. In particular, it lets you support more than
> one language-sensitive ordering in the same database.
I also had interest in enabling national characters for 10.2, but after
dates for 10.2 became clearer, I decided it wouldn't be feasible to
research and implement this functionality in the remaining time. I will
add a comment to JIRA entry stating this. I also don't have itch to look
into this issue for 10.3, though I think this is very useful functionality.
Satheesh
Re: Language based matching
Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Kathey,
Here is my understanding of how the disabled national string types worked:
1) A national string type used the collation ordering appropriate to the
locale of the database. That collation ordering, in turn, was specified
by the jdk and could not be overriden.
2) The collation ordering determined the meaning of <, =, and > for
national strings. For a given locale, the rules can be quite tricky. If
you're not familiar with a locale, you are likely to be surprised by the
visibly different strings which nevertheless turn out to be = to one
another.
3) The locale-sensitive meaning of <, =, and > affected the operation of
all orderings of national strings, including sorts, indexes, unions,
group-by's, like's, between's, and in's.
At one point I was keen on re-enabling the national string types. Now I
am leaning toward implementing the ANSI collation language. I think this
is more powerful. In particular, it lets you support more than one
language-sensitive ordering in the same database.
You and your customer face a hard problem trying to migrate national
strings from Cloudscape 5.1.60 into Derby 10.1.3 or 10.2. I'm at a loss
how to do this in a way that preserves Cloudscape's performance.
Regards,
-Rick
Kathey Marsden wrote:
> Bernt M. Johnsen wrote:
>
>> "aa" as one letter was removed from the Norwegian language in 1938 ("å"
>> had been optional since 1917). It is only used in names today and it is
>> true what Anders says about the phonebook (also about the foreign names
>> where "aa" is treated like two letters). I don't think it would be wise
>> to not let "a.*" match "Aasen" (wich in modern writing would be Åsen).
>>
>>
>>
> Thank you so much Knut Anders and Bernt for the clarification on
> "aa". I guess now I need a new example and need to understand how
> Locale specific LIKE processing is functionally different than
> regular like behavior and when it is required.
> The user I have been working with is actually migrating from
> Cloudscape 5.1.60 National Character types and the goal was to get a
> workaround to achieve the same behavior in Derby. The example came
> from the doc:
>
> http://publibfi.boulder.ibm.com/epubs/html/cloud51/doc/html/coredocs/sqlj105.htm#1178996
>
>
> Clearly the Derby code still has the code path for the National Type
> special processing.
> In org.apache.derby.iapi.types.SQLChar We have a separate code path
> for National Character types that passes the Collator.
> How is this functionally different than LIKE processing for regular
> character types? Can anyone think of another example where this
> special processing might be needed?
> Thanks
>
> Kathey
>
> Below is a SQLChar code snippet for reference.
> public BooleanDataValue like(DataValueDescriptor pattern)
> throws StandardException
> {
> Boolean likeResult;
>
> if (! isNationalString())
> {
> // note that we call getLength() because the length
> // of the char array may be different than the
> // length we should be using (i.e. getLength()).
> // see getCharArray() for more info
> char[] evalCharArray = getCharArray();
> char[] patternCharArray = ((SQLChar)pattern).getCharArray();
> likeResult = Like.like(evalCharArray,
> getLength(),
> patternCharArray,
> pattern.getLength());
> }
> else
> {
> SQLChar patternSQLChar = (SQLChar) pattern;
> likeResult = Like.like(getIntArray(),
> getIntLength(),
> patternSQLChar.getIntArray(),
> patternSQLChar.getIntLength(),
> getLocaleFinder().getCollator());
> }
>
>
>
Re: Language based matching
Posted by Kathey Marsden <km...@sbcglobal.net>.
Bernt M. Johnsen wrote:
>"aa" as one letter was removed from the Norwegian language in 1938 ("å"
>had been optional since 1917). It is only used in names today and it is
>true what Anders says about the phonebook (also about the foreign names
>where "aa" is treated like two letters). I don't think it would be wise
>to not let "a.*" match "Aasen" (wich in modern writing would be Åsen).
>
>
>
Thank you so much Knut Anders and Bernt for the clarification on "aa".
I guess now I need a new example and need to understand how Locale
specific LIKE processing is functionally different than regular like
behavior and when it is required.
The user I have been working with is actually migrating from Cloudscape
5.1.60 National Character types and the goal was to get a workaround to
achieve the same behavior in Derby. The example came from the doc:
http://publibfi.boulder.ibm.com/epubs/html/cloud51/doc/html/coredocs/sqlj105.htm#1178996
Clearly the Derby code still has the code path for the National Type
special processing.
In org.apache.derby.iapi.types.SQLChar We have a separate code path for
National Character types that passes the Collator.
How is this functionally different than LIKE processing for regular
character types? Can anyone think of another example where this special
processing might be needed?
Thanks
Kathey
Below is a SQLChar code snippet for reference.
public BooleanDataValue like(DataValueDescriptor pattern)
throws StandardException
{
Boolean likeResult;
if (! isNationalString())
{
// note that we call getLength() because the length
// of the char array may be different than the
// length we should be using (i.e. getLength()).
// see getCharArray() for more info
char[] evalCharArray = getCharArray();
char[] patternCharArray = ((SQLChar)pattern).getCharArray();
likeResult = Like.like(evalCharArray,
getLength(),
patternCharArray,
pattern.getLength());
}
else
{
SQLChar patternSQLChar = (SQLChar) pattern;
likeResult = Like.like(getIntArray(),
getIntLength(),
patternSQLChar.getIntArray(),
patternSQLChar.getIntLength(),
getLocaleFinder().getCollator());
}
Re: Language based matching
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
"aa" as one letter was removed from the Norwegian language in 1938 ("å"
had been optional since 1917). It is only used in names today and it is
true what Anders says about the phonebook (also about the foreign names
where "aa" is treated like two letters). I don't think it would be wise
to not let "a.*" match "Aasen" (wich in modern writing would be Åsen).
Knut Anders Hatlen wrote:
> Kathey Marsden <km...@sbcglobal.net> writes:
>
>
>>Does anyone know of an easy built in Java mechanism for Locale
>>sensitive matching?
>>
>>I continue to work with a user trying to develop a strategy for
>>language based string type handling in Derby 10.1.
>>The ordering seems doable with the approach in
>>http://wiki.apache.org/db-derby/LanguageBasedOrdering
>>For <, =. > comparisons I was able to implement a LOCALE_COMPARE
>>function pretty easily using Collators as well,
>>but matching (LIKE replacement) seems harder. For example in
>>Norwegian we need to have "aa" be treated as one character and in the
>>US have it treated as two. So given the values acorn, aacorn, and
>>aass ( a Norwegian brewery) , and matching "a.*", we should see three
>>rows in english and just one in Norwegian.
>
> [snip]
>
> Hi Kathey,
>
> It is true that in a Norwegian phone book, Wanvik is listed before
> Waagan. However, Haas (which is not a Norwegian name) would be listed
> before Hatlen. Likewise, geographical names from other countries could
> have "aa" which should be treated as two characters in Norwegian
> (Saarland, Saarbrücken, Haag). Also, you could have composite words
> like "pizzaauksjon" (pizza auction - whatever that is) which would be
> listed before "pizzabakar" (pizza baker) in a dictionary. You could
> also have words where the stem ends with an a and the ending starts
> with an a, like "dataa" which consists of "data" (same word as in
> English) and "a" (definite article, plural, neuter).
>
> It is not possible to decide how "aa" should be treated without
> knowing the context, so in general I think it is best if Derby just
> treats "aa" as two characters and lets the application do the magic if
> magic is required.
>
> But, as many others have said, IANAL... (I Am Not A Linguist)
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Language based matching
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Kathey Marsden <km...@sbcglobal.net> writes:
> Does anyone know of an easy built in Java mechanism for Locale
> sensitive matching?
>
> I continue to work with a user trying to develop a strategy for
> language based string type handling in Derby 10.1.
> The ordering seems doable with the approach in
> http://wiki.apache.org/db-derby/LanguageBasedOrdering
> For <, =. > comparisons I was able to implement a LOCALE_COMPARE
> function pretty easily using Collators as well,
> but matching (LIKE replacement) seems harder. For example in
> Norwegian we need to have "aa" be treated as one character and in the
> US have it treated as two. So given the values acorn, aacorn, and
> aass ( a Norwegian brewery) , and matching "a.*", we should see three
> rows in english and just one in Norwegian.
[snip]
Hi Kathey,
It is true that in a Norwegian phone book, Wanvik is listed before
Waagan. However, Haas (which is not a Norwegian name) would be listed
before Hatlen. Likewise, geographical names from other countries could
have "aa" which should be treated as two characters in Norwegian
(Saarland, Saarbrücken, Haag). Also, you could have composite words
like "pizzaauksjon" (pizza auction - whatever that is) which would be
listed before "pizzabakar" (pizza baker) in a dictionary. You could
also have words where the stem ends with an a and the ending starts
with an a, like "dataa" which consists of "data" (same word as in
English) and "a" (definite article, plural, neuter).
It is not possible to decide how "aa" should be treated without
knowing the context, so in general I think it is best if Derby just
treats "aa" as two characters and lets the application do the magic if
magic is required.
But, as many others have said, IANAL... (I Am Not A Linguist)
--
Knut Anders