----- Forwarded by Ben Carlyle/AU/IRSA/Rail on 02/12/2003 01:55 PM -----
Ben Carlyle
02/12/2003 01:39 PM
To: Darren Duncan <[EMAIL PROTECTED]>@CORP
cc:
Subject: RE: [sqlite] Is case sensitivity a problem for me only?
Darren Duncan <[EMAIL PROTECTED]>
02/12/2003 09:00 AM
To: <[EMAIL PROTECTED]>
cc:
Subject: RE: [sqlite] Is case sensitivity a problem for me only?
> Steve said:
> > Why should case sensitive data comparisons be maintained ?
Just to answer Steve: Well for starters, it would break compatability with
earlier sqlite versions to change the default behaviour :) Such a decision
would have to be made on overwhelming technical merit.
> I wasn't exactly advocating the use of UPPER/LOWER. Rather, I suggest
having smart comparison operators in combination with column indexes that
are customizable to be a case-sensitive index or non-sensitive index.
Comparisons between columns with the same kind of index will do as you
expect; comparisons between a column and a string literal, or a caller
bind variable, would also follow the rule of the column index. Comparisons
between a senstive and a non (not that this would ever happen in a good
design) would be sensitive if the non preserves case.
If a change were in the works, I would advocate a future version of sqlite
supporting indicies that provide arbitrary transformations on their data
before comparison. When sqlite sees the same transformation in a
comparison query the index would be used:
create table foo(bar);
create index foo_bar on foo (bar); -- Case sensitive index
create index foo_bar on foo (lower(bar)); -- Case insensitive index
select * from foo where bar = "baz"; -- Case sensitive select
select * from foo where lower(bar) = "baz"; -- Case insensitive select
This, of course, is just a syntax I made up. Other databases probably
provide hints for better syntax options. This approach would also support
special indicies like:
create index foo_bar on foo (canonical_name(bar)); -- Custom index that
makes "Macdonald" into "McDonald" for sorting purposes.
select * from foo where canonical(bar) = "McDonald"; -- Selects both
Macdonald and McDonald names.
... and could also support custom ordering using an index:
select * from foo order by canonical(bar); -- Use index, no extra sorting
required.
You would still probably only be able to do basic indexing through this
method. As soon as you introduce joins things get difficult... but the
current sqlite doesn't always optimise joins the way we might expect.
You'd at least want to make sure that any such optimisations weren't
prevented from happening by the introduction of this kind of index.
Benjamin
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
