Re(2): Queries. Question to all

[Ruslan Zasukhin]

On 6/30/04 1:49 PM, "Peter Knobloch" <peter.knobloch at absus.at> wrote:


>> I am not sure about mySQL
> "LIKE" is case insensitive, "LIKE BINARY" case sensitve
> 
>   http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html#IDX1312

Also note, Valentina 1.x in LIKE use actually REGEX.

In Valentina 1.x you can do case insensitive LIKE using

    WHRE fld LIKE 'something' no_case
                              ^^^^^^
Yes, this is non standard way, but works.
It is not worse of BINARY -- also non standard way.


>> To get case-insensetive search you should create virtual field
>> (BaseObject method)  fldLwr with formula "fld"

> Good to hear that thats the official way: it's what I'm doing. But I
> assume this doubles the size for the fields PLUS the index as well (if I
> have an index on both fields).

But you should to have 2 indexes for a field if you want do both
    case-sensitive and case-insensitive searches or sorting.

Just think about this.

2 indexes will differ in the order of records!!!

And note, you can buld only one index!
You can index or field, or methd, or both. Up to your choice.


>> For several years nobody have complain that Valentina do on default
>> case-sensetive search. What you think guys ? Should we change this in
>> 2.0 ? I think no.
> How about an additional flag to give the developer the option if the
> index should be case sensitive?

YES! Valentina 2.0 will have such flag for string fields.
It simplify work in this area. compare:

----------------------
Valentina 1.x:

    fld                             NOT INDEXED
    fldLwr  method = "lower(fld)"  INDEXED


Now we have one field on disk.
Now we have one virtual field.
And we have One index on disk.

But now developer MUST do one more step.
    when he prepare SQL query, the string constant already should
    be in lower case:

        WHERE fldLwr = 'sgdg'           good
        WHERE fldLwr = 'sGDg'           wrong


----------------------
Valentina 2.0:

    fld                    INDEXED and CASE-INSENSITIVE

Let is all. Valentina will keep index for this field in lower case.
Developer may even give string constant in any case

        WHERE fld = 'sGDg'    okay, Valentina self will make it lower.
    
-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------