| Subject | RE: [IB-Architect] Syntax for case insensitive sort |
|---|---|
| Author | David Schnepper |
| Post date | 2000-03-29T22:08:51Z |
Does Case-insensitive Thai make any sense? Or Kanji? Or Case-insensitive
GUID sorting?
(Recall that character set OCTETS can also have collation drivers).
(And, yes, I'm ignoring the fact that Thai and Japanese do use latin
characters)
How about US Telephone Book sort order? (The definition of the sort order
specifies
how to handle case) - Case / No Case options are an extension to the
definition.
And once you add Case vs No Case,
well, might as well add Accent vs No Accent
and digits before letters vs letters before digits
and, heck, not that much work to require a driver to have an option for
putting digits
into text form (Eighteen-Twelve Overature)
and it would be cool to have an option to merge McDonald and MacDonald, just
in case I
wanted to use this driver to sort names.
Where do you draw the line between what's "an option in a driver" and a
"separate driver" ?
8 years ago I made the decision to keep "close to" the SQL 92 specification
in how to handle this -
and if I had won the arguments to provide case & no-case drivers back then
we would not now be
having a discussion to add a SQL language feature to get around the fact
that InterBase didn't
provide the right set of drivers.
(Incidently, the reason I lost the argument back then was the 2 weeks dev
time & 2-3 weeks test time
would slip the 4.0 schedule by 5 weeks - which wound up slipping a few
months anyway...
-- but again, where do you draw the line on product features as well?)
SQL makes no allowance for specifing "CASE" or "NO CASE" when you use a
collation -- and every comparision
operator uses a collation in SQL (default one if not specifically
mentioned). SQL, as I recall from
my fuzzy memory, did make an allowance for Creating a collation based on an
existing collation with options,
but that was mainly designed for using a "French collation designed for
character set 850" on data defined
with character set latin-1 (for example).
(something like
CREATE COLLATION LATIN1_FR_FR FOR CHARACTER SET LATIN1 AS
TRANSLATE USING LATIN1_TO_850
THEN COLLATE 850_FR_FR;
(lots of liberties taken above...)
Dave
-----Original Message-----
From: Olivier Mascia [mailto:om@...]
Sent: Wednesday, March 29, 2000 10:28 AM
To: IB-Architect@onelist.com
Subject: RE: [IB-Architect] Syntax for case insensitive sort
From: "Olivier Mascia" <om@...>
I quite like that solution based on collation drivers.
The problem is that you can't make an insensitive query on data which you
do not have decided upfront it would be case insensitive (by assigning it
a specific special collation driver).
Why not revise the internals of collation drivers so that any collation
driver *must* include both provision for case-sensitive collation and
case-insensitive. Then some keyword(s) (like were discussed today) would
allow to trigger the use of the sensitive or insensitive mode of the
collation driver.
Indexes could be defined sensitive or not as earlier discussed, but this
would be implemented by relying on the right rules on *the* collation
driver of the columns.
Relying on the collation drivers allows for easy and correct cross-
platform lowercasing and uppercasing. The danger would be to implement
transformations from any case to another inside the engine, separately
from the collation drivers. Intricaties of some language could then be
badly handled.
---------------------------------------------------------------------
Olivier Mascia T.I.P. Group SA
om@... www.tipgroup.com
Director, Chief Software Architect +32 65 401111
-----Original Message-----
From: David Schnepper <dschnepper@...>
To: "'IB-Architect@onelist.com'" <IB-Architect@onelist.com>
Date: Wed, 29 Mar 2000 10:21:36 -0800
Subject: RE: [IB-Architect] Syntax for case insensitive sort
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954355223/
------------------------------------------------------------------------
To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com
GUID sorting?
(Recall that character set OCTETS can also have collation drivers).
(And, yes, I'm ignoring the fact that Thai and Japanese do use latin
characters)
How about US Telephone Book sort order? (The definition of the sort order
specifies
how to handle case) - Case / No Case options are an extension to the
definition.
And once you add Case vs No Case,
well, might as well add Accent vs No Accent
and digits before letters vs letters before digits
and, heck, not that much work to require a driver to have an option for
putting digits
into text form (Eighteen-Twelve Overature)
and it would be cool to have an option to merge McDonald and MacDonald, just
in case I
wanted to use this driver to sort names.
Where do you draw the line between what's "an option in a driver" and a
"separate driver" ?
8 years ago I made the decision to keep "close to" the SQL 92 specification
in how to handle this -
and if I had won the arguments to provide case & no-case drivers back then
we would not now be
having a discussion to add a SQL language feature to get around the fact
that InterBase didn't
provide the right set of drivers.
(Incidently, the reason I lost the argument back then was the 2 weeks dev
time & 2-3 weeks test time
would slip the 4.0 schedule by 5 weeks - which wound up slipping a few
months anyway...
-- but again, where do you draw the line on product features as well?)
SQL makes no allowance for specifing "CASE" or "NO CASE" when you use a
collation -- and every comparision
operator uses a collation in SQL (default one if not specifically
mentioned). SQL, as I recall from
my fuzzy memory, did make an allowance for Creating a collation based on an
existing collation with options,
but that was mainly designed for using a "French collation designed for
character set 850" on data defined
with character set latin-1 (for example).
(something like
CREATE COLLATION LATIN1_FR_FR FOR CHARACTER SET LATIN1 AS
TRANSLATE USING LATIN1_TO_850
THEN COLLATE 850_FR_FR;
(lots of liberties taken above...)
Dave
-----Original Message-----
From: Olivier Mascia [mailto:om@...]
Sent: Wednesday, March 29, 2000 10:28 AM
To: IB-Architect@onelist.com
Subject: RE: [IB-Architect] Syntax for case insensitive sort
From: "Olivier Mascia" <om@...>
I quite like that solution based on collation drivers.
The problem is that you can't make an insensitive query on data which you
do not have decided upfront it would be case insensitive (by assigning it
a specific special collation driver).
Why not revise the internals of collation drivers so that any collation
driver *must* include both provision for case-sensitive collation and
case-insensitive. Then some keyword(s) (like were discussed today) would
allow to trigger the use of the sensitive or insensitive mode of the
collation driver.
Indexes could be defined sensitive or not as earlier discussed, but this
would be implemented by relying on the right rules on *the* collation
driver of the columns.
Relying on the collation drivers allows for easy and correct cross-
platform lowercasing and uppercasing. The danger would be to implement
transformations from any case to another inside the engine, separately
from the collation drivers. Intricaties of some language could then be
badly handled.
---------------------------------------------------------------------
Olivier Mascia T.I.P. Group SA
om@... www.tipgroup.com
Director, Chief Software Architect +32 65 401111
-----Original Message-----
From: David Schnepper <dschnepper@...>
To: "'IB-Architect@onelist.com'" <IB-Architect@onelist.com>
Date: Wed, 29 Mar 2000 10:21:36 -0800
Subject: RE: [IB-Architect] Syntax for case insensitive sort
> <html><body>------------------------------------------------------------------------
> <tt>From:</tt> <tt>
> David Schnepper <dschnepper@...></tt>
> <br><br>
> <tt>
> OK, I freely admit I've been too busy today to read *all* the
> discussion on<BR>
> case-insensitive sorting.<BR>
> <BR>
> Let me outline my solution to the problem. This uses the SQL
> "COLLATE"<BR>
> clause, and InterBase's ability to add collation drivers to an
> installed<BR>
> server. I've created a set of drivers that mimic each of the
> existing<BR>
> InterBase European collations, EXCEPT they make no distinction based
> on<BR>
> case. To use them:<BR>
> <BR>
> CREATE TABLE example (someText CHARACTER(30) COLLATE
> FR_FR_NOCASE, /*<BR>
> Case insensitive FRench used in FRance driver */<BR>
>
> ; someMore
> CHARACTER(30) COLLATE DE_DE_NOCASE) /*<BR>
> Case insensitive German used in Germany driver */<BR>
> <BR>
> Indices created on these columns are automatically case
> insensitive.<BR>
> Comparisions using these columns automatically use the rules for the
> column<BR>
> <BR>
> eg:<BR>
> ... WHERE someText = 'Des
> Jardins' &nb
> sp; /*
> Uses<BR>
> FR_FR_NOCASE */<BR>
> AND
> someMore <
> 'Schwartz' &
> nbsp;
> /* Uses<BR>
> DE_DE_NOCASE */<BR>
>
> OR /* if you don't like the column default collation, you can<BR>
> specify one */<BR>
>
> ; someText = 'Garden' COLLATE
> EN_UK_NOCASE /* Overrides<BR>
> default of FR_FR_NOCASE */<BR>
>
> ; AND<BR>
>
> ; someText = someMore COLLATE
> SJIS_NOCASE /* Silly in<BR>
> practice, but, yes, you can even specify<BR>
>
> ; &nbs
> p; &nb
> sp; &n
> bsp; &
> nbsp; Japanese<BR>
> character comparision */<BR>
> <BR>
> Performance: Slightly faster than existing InterBase collation
> drivers.<BR>
> Memory: Small, about 2K per additional driver<BR>
> <BR>
> I was driving myself to complete this product last December.
> Around<BR>
> December 18th I suddenly lost motovation (as I'm sure most of you
> understand<BR>
> why...)<BR>
> <BR>
> Current state: I have plenty of motovation to finish this, I need
> about 2<BR>
> solid days to spend on it, which I will have in April, since a lot
> of<BR>
> projects are finished or in automatic mode (finally!).<BR>
> <BR>
> And, yes, it will work on InterBase 4.x, 5.x, and 6.x. <BR>
> <BR>
> (Now, your job (IB-Architect list) is to give me encouragement to
> finish<BR>
> this up and post it!)<BR>
> <BR>
> Dave Schnepper<BR>
> <BR>
> <BR>
> <BR>
> -----Original Message-----<BR>
> From: Jim Starkey [mailto:jas@...]<BR>
> Sent: Wednesday, March 29, 2000 8:22 AM<BR>
> To: IB-Architect@onelist.com<BR>
> Subject: [IB-Architect] Syntax for case insensitive sort<BR>
> <BR>
> <BR>
> From: Jim Starkey <jas@...><BR>
> <BR>
> The current SQL "order by" clause is<BR>
> <BR>
> { <expr> | <position> } [
> ASC | DESC ]<BR>
> <BR>
> We could make it<BR>
> <BR>
> { <expr> | <position> } [
> ASC | DESC ] [CASE SENSITIVE | CASE<BR>
> INSENSITIVE]<BR>
> <BR>
> Obviously the default would "CASE SENSITIVE".<BR>
> <BR>
> I'm not at all keen on two word "keywords", but
> CASEINSENSITIVE is too<BR>
> ughly to consider. Just sensitive makes the whole things a
> little<BR>
> too touchy feely for my taste, though INSENSITIVE is a pretty good<BR>
> description for computer code. In any case (ho ho), once we
> open<BR>
> the gates on collation options many more will try to follow, so
> perhaps<BR>
> a common qualifier CASE is justified. That also leaves the
> unadorned<BR>
> keyword SENSITIVE for the day when the database system knows to put<BR>
> the poetically inclined first and fiftyish iconoclastic programmers<BR>
> last.<BR>
> <BR>
> Jim Starkey<BR>
> <BR>
> -----------------------------------------------------------------------
> -<BR>
> Special Offer-Earn 300 Points from MyPoints.com for trying @Backup<BR>
> Get automatic protection and access to your important computer
> files.<BR>
> Install today:<BR>
> <a
> href="http://click.egroups.com/1/2344/3/_/_/_/954347030/">http://click.
> egroups.com/1/2344/3/_/_/_/954347030/</a><BR>
> -----------------------------------------------------------------------
> -<BR>
> <BR>
> To unsubscribe from this group, send an email to:<BR>
> IB-Architect-unsubscribe@onelist.com<BR>
> <BR>
> <BR>
> <BR>
> </tt>
>
> <hr>
> <!-- begin banner runid: 2571 crid: 1281 -->
> <a target="_blank"
> href="http://click.egroups.com/1/2571/3/_/_/_/954354084/"><center>
> <img width="468" height="60"
> border="0"
> alt=""
>
> src="http://adimg.egroups.com/img/2571/3/_/_/_/954354084/468x60_98degre
> es_yel_L.gif"></center><center><font color="white"></font></center></a>
> <!-- end banner -->
> <hr>
> <tt>
> To unsubscribe from this group, send an email to:<BR>
> IB-Architect-unsubscribe@onelist.com<BR>
> <BR>
> </tt>
> <br>
>
> </body></html>
>
>
Special Offer-Earn 300 Points from MyPoints.com for trying @Backup
Get automatic protection and access to your important computer files.
Install today:
http://click.egroups.com/1/2344/3/_/_/_/954355223/
------------------------------------------------------------------------
To unsubscribe from this group, send an email to:
IB-Architect-unsubscribe@onelist.com