add in support for lower function indexes

[Former user]

it is a PITA to get a function index to work right using sqlalchemy.

for those that aren't familiar... postgres , oracle and a few other dbs all allow for indexes to be created as a function

the idiomatic example is:

create unique index user_email_address_uidx on user(lower(email_address));

which allows for case-sensitive storing of the email address, and creates a lower version to speed searching and preserve uniqueness

the planners automagically use the index on queries like: select * from user where lower(email_address) = %s

i'm not sure what a long-term solution would be, but in the short term... for the two most common operations, my suggestion is to add two new operators to sql.compiler

operators.lower_eq : lambda x, y: "lower(%s) = lower(%s)" % (x, y),

operators.upper_eq : lambda x, y: "upper(%s) = upper(%s)" % (x, y),

this should work too :

operators.lower_eq : lambda x, y: "lower(%s) = %s" % (x),

operators.upper_eq : lambda x, y: "upper(%s) = %s" % (x),

[jek]

dupe of #487 and possibly #695?

[Mike Bayer]

It is kind of a dupe of #487. the patch on #487 is waaay out of date. the basic idea is that you can configure a String type to be "case_insensitive":

Column("foo", String(50, case_sensitive=False))

then compare operations would interpret the flag when doing comparisons to roll out the lower() or upper() functions:

>>> print table.c.foo == "test"
lower(foo) == lower(:foo_1)

[jek]

and dialects that support SQL collations would do what? ignore and compare as normal, hoping the column collation is case insensitive? or do a lower() and hope that the lower() ends up in a indexed collation?

perhaps this should be solved for collations first with a lower() fallback for older databases.

[Mike Bayer]

couldn't the Dialect be configured with a "supports_collations" flag (or do the whole "check the collations set up on the connection" thing) so that compilation in those cases doesn't produce the lower() ?

[jek]

i guess i'm saying that case_sensitive=False may not be enough information to do this properly in ansi sql going forward, so why start the thinking with the degenerate legacy case.

[Mike Bayer]

so, database-managed collation is available across all the db's in question here, like PG, oracle, etc. ? is the lower() scenario legacy across the board (if so, is that the answer to the ticket poster)? I'm not that familiar with collate rules, my impression was that it was primarily a MySQL feature.

[jek]

nope, collate is SQL. mysql, mssql and sqlite have collations. pg still has collation as a todo, not sure if it's on the radar for 8.4 or not. i don't know about oracle, db2, sybase, ...

[Mike Bayer]

it sounds like "information schema", in the SQL spec but not really a viable reality for a large part of the database world. so....whats the proposal ?

[Mike Bayer]

need advice from jek on how to continue with this ticket.

[Mike Bayer]

well those other two tix are blue sky so this one is too. its a manual thing for now.

[Mike Bayer]

this is definitely asking for what #487 and #695 ask for, #695 being the more doable one.