add in support for lower function indexes
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),
Comments (12)
-
-
repo owner It is kind of a dupe of
#487. the patch on#487is 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)
-
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.
-
repo owner 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()
? -
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.
-
repo owner 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. -
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, ...
-
repo owner 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 ?
-
repo owner - removed milestone
need advice from jek on how to continue with this ticket.
-
repo owner - changed milestone to 0.5.xx
-
repo owner - changed milestone to blue sky
well those other two tix are blue sky so this one is too. its a manual thing for now.
-
repo owner - changed status to duplicate
- Log in to comment
dupe of
#487and possibly#695?