Sort out query operator mapping
Crell - August 24, 2008 - 19:36
| Project: | Drupal |
| Version: | 7.x-dev |
| Component: | database system |
| Category: | task |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
I'm marking this critical because we want to get it worked out before we start converting all of core. :-)
The new DB API includes the ability to "remap" WHERE clause operators in dynamic queries. That is, if the user passes in "LIKE" we can turn that into "ILIKE" on Postgres, which is case-insensitive like MySQL. Neat!
However, that then begs the question of whether or not we should map LIKE to ILIKE like that. Don't we sometimes want case-sensitive LIKE? Frankly I don't know, but we should work out some set of rules for how we want to map certain operators to certain other operators; and remember, it's for dynamic queries only. Static queries (db_query()) don't get remapped.
Discuss. :-)
#1
So there are two things we can do, here. Call the case insensitive operator "LIKE". For MySQL coders this is how the world behaves. For PostgreSQL we can remap to "ILIKE" for consistency. Then we need to invent our own case sensitive version of LIKE -- for example "CSLIKE" which maps to "LIKE ':argument' COLLATE utf8_bin" for MySQL (@Crell, can the code do that now?) and maps to "LIKE" for PostgreSQL.
We also can decide that "LIKE" is case sensitive and "ILIKE" is case insensitive. The PostgreSQL folks will be happy. MySQL folks much less so. Also, because most contrib workers are on MySQL solely, they will, no doubt @#$%^&*( this up and not even their tests will reveal these bugs easily. I dislike this situation.
#2
Hm. No, right now we can't do a full fragment replacement, just an operator replacement. We can probably modify the code to support it, but I'm not sure off hand how difficult it would be.