"case-insensitive" (and "case-sensitive"?) SQL operations

[Mike Bayer]

this enhancement allows comparison, ordering, and other operations in an expressly "case-insensitive" manner, where the dialect gets to determine how "case-insensitive" should be implemented. (i.e. in postgres or oracle it may be using the lower() function, in mysql it may mean "COLLATE", or just driving off a dialect-level configuration flag).

the instruction to go "case-insensitive" is directed from the Column or maybe its TypeEngine (the initial patch uses TypeEngine).

for the initial patch:

• added visit_case_insensitive and overrode in PGCompiler to use "lower"
• sub-classed ClauseList with _OrderByClause and _GroupByClause.

This was done to add some context as to where in the statement the clauses belong, because we do not want to mess with the case of the results being returned, only when the character data is compared at the server. I need to do some more work to peg HAVING, but this works well otherwise.

• added _CaseInsensitiveCompare like you mentioned.
• modified _compare_self instead of eq, and added case_compare param to the String type

I think this is better then added case_compare at the column level, because this only affects character data. Dates, Int's, etc do not have the same collation issues and the issues with Date that do exist are already addressed.

[Mike Bayer]

initial patch with case-insensitive implemented for postgres

[Mike Bayer]

also, see #418. thats the opposite problem; MySQL needing explicit "case sensitive" flags, i.e. COLLATE. perhaps this ticket should take into account the ability to not just say "ensure this comparison is case insensitive" but also to say "ensure this comparison is case sensitive". but the COLLATE keyword might not be so flexible as to work on individual comparisons.

[Mike Bayer]

this ticket is unlikely to get much traction for the time being since DB support for collation etc. is spotty.

[Mike Bayer]

closing this for these reasons:

1. the patch here is seven years old and the code is entirely unusable.

2. "case insensitive" operations can't reliably be achieved using LOWER() or UPPER(), as these do not accommodate unicode case folding - see http://www.w3.org/International/wiki/Case_folding.

3. the general idea of working an expression like func.lower() into string comparison operations is simple, a simple recipe has been added at UsageRecipes/StringComparisonFilter.