> OK totally different issue. MySQL does have a case-sensitivity > setting using COLLATE. not sure what MS-SQL has and we dont yet have > sybase support. we've had people report the whole lower() issue in > the past and im not sure that should be "automatic" within SA. You > can just explicitly say lower() across the board for case-insensitive > comparisons. if SA implemented some operator for this (which could > be doable, such as table.c.compare_insensitive(foo)), it would still > have to use lower() for every dialect since in particular for MySQL > we have no idea what the COLLATE rules are on the given table.
Even though MySQL allows the setting of COLLATE, it does not support functional indexes, so if your code explicitly calls lower you technically now have code that will work for both MySQL and Postgres, but MySQL is going to take a big performance hit and perform table scans regardless of any created indexes. Realistically, I can't see anyone setting MySQL collate rules to case-sensitive without the support of functional indexes, which MySQL does not have. If Postgres allowed case-insensitive collation all would be good. But it does not, and I've never seen anyone make MySQL case-sensitive and deal with the headaches (nor should they, I think case-sensitivity in a database server is a throw-back), for this reason I think it should somehow be approachable from the dialect to deal with how different db servers handle collation support. In simple terms, if the Postgres dialect supported compare_insensitive=True|False|[upper|lower]? then apps written in sqlalchemy easily support Postgres and MySQL with the same code in out- of-the-box configurations. But even if it didn't, if there was a way to override the default postgres dialect I'd be a happy camper. Infact, that is exactly what I have done. I added a dialect called "lowergres", but I'm stuck because I can not seem to find the appropriate hook to alter the column to a func.lower when the column is not part of the selected column list. format_column looks to me like the right place to do it without converting a column to a function, but the column object in that function has no context as to where it lies in the sql statement. I'm curious as to others experiences with writing an app that supports both Postgres and MySQL with sqlalchemy, because if someone else is doing this then I must be missing something, or maybe not. At first, our unit tests all passed, then when we added real world data with mixed case, tests started to fail on everything doing sorts and where's on character data. How about a Pepsi (at PyCon)? Troy --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
