sorry, im not keen on the magical approach....at least something that is cross-referencing reflected database metadata like indexes and stuff with in-application behaviors (we *never* rely upon anything reflected..reflection to me is like a luxury feature). the most id ever consider, even though i dont like this idea either, is to do is some kind of "comparison_function" argument on Column which tries to shove its way into order by's and comparison operations.
i think folks should be aware of what kind of casing rules they want to apply to their orderings, comparisons, etc., and its also a little hairy between databases (like on MySQL, you actually need to express COLLATE directives on table creates/selects to deal with these things). On Feb 13, 8:43 am, "Troy" <[EMAIL PROTECTED]> wrote: > A common use of functional indexes for Postgres and Oracle is to apply > a "upper" or "lower" to a character column for case insensitive > searching and ordering. For example, with the following people table: > > id name > -- ---------------- > 1 adam > 2 Troy > > select name from people where people='troy' # returns 0 records > > select name from people order by people asc # returns Troy,adam > > So what usually happens to fix this is a query like: > > select name from people where upper(people)='TROY' # returns Troy > > But this can kill performance, as your not using an index. No > problem, add a functional index: > > CREATE INDEX upper_name_idx ON people ((upper(name))); > > Ok, great. The reason for my verbosity is I would like to see (at > least as an option) that if a upper or lower functional index exists > on a char field then to automagically invoke the function on the where > clause or order by. I would be interested to know how others writing > applications using SA targeting multiple databases are dealing with > Oracle and Postgres returning different data and different ordered > results than MySQL and MS-SQL depending on the case of the data in the > column. From what I've concluded, there are 3 options: > > 1) In the domain logic, lower or upper the data before it gets to the > database: > a) Ugly single case for users > b) Still breaks oracle and postgres for where/like without a > function clause > > 2) Manually apply func.upper/lower to conditions/order-by's to > specified columns: > a) Not confirmed, but MySQL probably would not use an index > b) Hard for developers to remember which columns have a functional > index > > 3) SA Automagically applies func.upper/lower to char columns with a > upper/lower function: > a) Makes life easy > > What do you think Mike? And where is the best place to do the > automagic, in the dialect or the compiler? > > Troy > > On Feb 12, 3:28 pm, "Michael Bayer" <[EMAIL PROTECTED]> wrote: > > > by "function index", we're talking expressions stuck into Index() ? > > shouldnt be too hard to implement, if you submit a patch that does it > > similarly to how column level defaults are done. kind of a 5-liner id > > think. > > > On Feb 12, 5:24 pm, "Troy" <[EMAIL PROTECTED]> wrote: > > > > Is there a time frame, or an intention, for supporting function > > > indexes? I would like to help out if there is no one on this. I've > > > spent a few hours pouring over the sqlalchemy code and have a few > > > ideas and questions if anyone is interested. > > > > Thanks, > > > > Troy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
