>Not all databases support the enormous range of
>character sets that Firebird does, though, do they?

I'm selfish. I just need one character set and I want it to behave
the way I want it to.

>Firebird DOES have an easy solution to implement
>case-insensitive indexes: you define the index
>with a case-insensitive collation order.
>The catch is...

...significant if the platform you're developing on doesn't have it.
The solution starts losing its "easyness" if your platform doesn't
have any case-insensitive collations.

>So you can go with the DBMSs that let you blindly
>define case-insensitive searches for everything
>and cop the resulting slow searches;

Is this a quantifiable value? 2x slower? 5x slower? 10x slower? (I'm
not trying to be argumentative. I really want to know.)

I wasn't asking the developers to make Firebird slower for my
convenience. I was asking the developers to give ME the option to
make Firebird slower for my convenience. If they have to change the
internal guts of Firebird to everyone's detriment just to give me a
feature I want, then that would be too high of a price to pay. I
don't want to hurt any else's application.

>The argument that Firebird should bend its
>implementation of case-insensitive indexing to align
>with inferior and less flexible implementations
>in competitor products appears specious to me.

All of my data is in proper case. I FIND DATA THAT IS IN ALL UPPER
CASE TO BE HARD TO READ AND UNPROFESSIONAL LOOKING. I'm not sure I
understand how writing triggers to duplicate data in all upper case
and changing the number of columns in a table each time you want to
add an index is a superior and more flexible implementation. The data
from my inferior DB2, MSSQL and Oracle databases comes back plenty
fast for my needs. I just don't like the $$$ associated with them. I
would rather user FB. I like Linux. I like open source.


For the sake of argument, let me ask you FB gurus a question. If I
use your suggestion to get case-insensitive indexing, I get duplicate
data in my primary table AND an index on the duplicate data. Is there
any advantage or disadvantage to using the trigger to insert the
upper case data into a secondary table, setting the primary key of
that table to the fields I want to index and then doing a join on it.
For example:

Employee table
-------------------------------
ID | LastName | FirstName | ...
-------------------------------
1 | Caillouet | Darryl
2 | CAILLOUET | DARRYL
3 | caillouet | darryl
4 | CaIlLoUeT | DaRrYl
PrimaryKey (ID)

Emp_Index table (inserted by trigger)
---------------
LastName | ID
---------------
CAILLOUET | 1
CAILLOUET | 2
CAILLOUET | 3
CAILLOUET | 4
PrimaryKey (LastName, ID)

SELECT Employee.*
FROM Employee, Emp_Index
WHERE Employee.ID = Emp_Index.ID
AND Emp_Index.LastName = 'CAILLOUET';

My question is, does the primary key on the Emp_Index table
physically sort the data in that order and not create an additional
index. If this is true, then I wouldn't have duplicate data in my
primary table and my secondary index table would take up the same
physical space as the index did in the method you suggested. I could
actually save space by not having the duplicate data in my primary
table.

If I wanted to port this to another database, I would just port the
primary tables. I wouldn't port the *_Index tables or the triggers.
Instead of a trigger I would just add an (inferior) case-insensitive
index to my primary table and tweak the WHERE clause of each of my
SELECT statements to remove the join. This would be a fairly straight-
forward conversion since I wouldn't have to change my primary tables
or write triggers. Indexes are easy to write. On my SELECT statement
I would just delete the middle of the WHERE clause:

SELECT Employee.*
FROM Employee
WHERE Employee.LastName = 'CAILLOUET';

Is this a viable option or a train wreck waiting to happen? Remember,
I'm making this up on-the-fly, so be kind...

Thanks,
Darryl