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>
>
>