Case insensitivity (was Re: browse keys (@bk))

[Fairlight]

On Wed, Jul 21, 2004 at 12:39:42AM -0400, Kenneth Brody may or may not have
proven themselves an utter git by pronouncing:
> "Jay R. Ashworth" wrote:
> [...]
> > Ok, *now* I'm just disgusted.
> > 
> > WARNING, to anyone remaining who might expect sane behavior: indexes on
> > * format fields are case insensitive, too.
> [...]
> 
> If "SMITH"="Smith", then I would certainly hope that they appear together
> in indexes.

<devils_advocate>

Assume you had...I dunno, customer service plans for long distance, or any
kind of service in any service industry.

Say you had a system where they interface with some other system and
-cannot- (at least easily, or -will- not, which is more likely) re-allocate
the field lengths.  Say they made the field for their plans 4 digits long,
and in the format:  ###A

Now, you have 999 * 26 possible combinations there.  But assume that over
time they actually run out of space for new plan types, but for
recordkeeping purposes they can't actually remove old ones.  

If they can differentiate between 'A' and 'a', they just made their
possible pool size 999 * 52.  Their other application may be able to
handle it, but fP could not -if what I've seen presented, as well as how I
interpreted it is correct-.  It would apparently not differentiate between
'456M' and '456m'.  That could be Very Bad in a business transaction,
especially if the difference in pricing is a significant amount.  I know
I'd be ticked if I got bitten by that and this was someone's excuse.
Actually, the other day Cingular quoted me an upgraded plan citing I would
not lose my -unlimited- nights and weekends, and they'd changed the rate
plan on him under the same number, and it actually went from unlimited to
5000 minutes.  I was peeved on general principle (having verified it with
the sales rep FOUR times before signing)--and got the central office to fix
it after they rummaged for ten minutes to find the right codes to do so.

But it could have just as easily been a code situation as I outlined above,
and I'd be no more tolerant as a customer if they mixed up different codes
as opposed to having changed what fell under the same code.

</devils_advocate>

I -don't- claim that's good design in the first place.  But I wouldn't put it
past some places to do something very similar, if not exactly that way.
DBA's are a dime a dozen.  Actually they're more like $65K/yr+ per dozen,
but you get the idea.  :)  They're like MCSE's these days.  Many of them
land nice cushy jobs where they don't need to know anything, and design
things very poorly (sometimes that's an understatement).

The point is, bad design or not (and it -is-, I have -no- hesitation in
calling that BAD database design), it should be possible to accomodate.
And everything I've read today implies that fP indexes will not do so.

That said, MySQL apparently won't do it -by default-, but you can tell it
to do so explicitly.  I just tried it on one of my databases.  My login
is 'Fairlight'.  I issued the command: 

     select * from users where login = 'fairLIGHT';

It gave me my record.  That actually surprised me until I read further in
the docs.

If I issue:

     select * from users where login = binary 'fairLIGHT';

It will not give me the record.  

BINARY is apparently an alias for CAST(str AS BINARY) in MySQL.  I don't
know enough about the generic SQL 99 specification to say if that's
standard or something they added.  I personally think it's bound to be
handy in some instances.

Jay uses PgSQL elsewhere, as I recall.  Maybe he's had need of this
functionality and he's used to it being there as a matter of course.  I
personally didn't know about the binary mode in MySQL until just now when I
looked it up.  

Point is, they apparently considered it worth adding, and I can see
situations where it could be necessary to do so through no fault of the
-current- implementor--where someone made a costly decision a long time ago
and it "just needs to work" in very short order, possibly interfaced to
something that nobody knows anything about any longer and which is running
on fumes, as it were--or designed and implemented by someone who locked it
down so it's unchangeable on the other end.  The goal, IMHO, is always to
make something robust enough to cover every possible contingency possible.
That's not always attainable, unfortunately, but one tries.

And before anyone starts, yes, I'm -fully- aware that MySQL is not fP and
vice versa.  I'm -not- expecting it to be.  I'm -not- raising the point
that it should be.  I'm simply citing some examples where one might need to
do this particular sort of thing, and where it's possible elsewhere and one
might be used to the level of robustness that would let you achieve such a
feat, if necessary.

mark->
-- 
Bring the web-enabling power of OneGate to -your- filePro applications today!

Try the live filePro-based, OneGate-enabled demo at the following URL:
               http://www2.onnik.com/~fairlite/flfssindex.html