Add case-insensitive database support to views

bump to latest version

Then try this one out.

There was a mistake where LOWER was added in an area where it should not have been added.

Please review this new patch.

I have more recently been wondering which of the following ways is the better way to do this:
1) use the SQL LOWER as done by this patch
2) use the php strtolower function instead

Which one would have better performance?
Which one would have better scalability?

The php strtolower has the advantage of consistency and not having to worry about differences between the different supported databases.
But...the disadvantage, might that be character encoding issues (ascii, utf8, iso8859-1, etc..)?

Here is where I suspect the problem happens.

<?php
    if (empty($this->options['ignorecase'])){
      $this->query->add_where(0, "$field = '%s'", $argument);
    }
    else {
      $this->query->add_where(0, "LOWER($field) = LOWER('%s')", $argument);
    }
?>

Perhaps MySQL does not like the query LOWER('').
I am changing the code to add a php empty() functional call as an additional check before adding the SQL LOWER() function.

If the patch has been applied as mentioned in #7, then should a bug report be opened for the 2.x version?

Also, this patch applies the SQL LOWER() function call to the field name, is this necessary or can the field name be guaranteed lower case?

I have looked all over the drupal 7 database api, I cannot seem to find where to call functions against specific fields.
Either I need to be pointed in the right direction or this may not be doable until the drupal 7 database api fully supports the SQL standard. (namely, sql functions)

I noticed that the date module does the following:

<?php
/**
 *  A helper function to do cross-database concatation of date parts
 *
 *  @param $array - an array of values to be concatonated in sql
 *  @return - correct sql string for database type
 */
function date_sql_concat($array) {
  switch (date_db_type()) {
    case ('mysql'):
    case ('mysqli'):
      return "CONCAT(" . implode(",", $array) . ")";
    case ('pgsql'):
      return implode(" || ", $array);
  }
}
?>

So then does that mean we could do something like this?

<?php
function views_sql_lower($array) {
  switch (date_db_type()) {
    case ('mysql'):
    case ('mysqli'):
    case ('pgsql'):
      return "LOWER(" . implode(",", $array) . ")";
  }
}
?>

I seem to have forgotten about this patch up until I needed it again. Here is the updated coded.

Apparently, views is already adding their own implicit SQL (namely IN()).
I used that as an example on how to properly implement adding LOWER() with the d7 dbapi.

I also noticed that views_plugin_argument_validate_taxonomy_term should support case insensitive searches as well.

Please Review this patch.

I was uncertain of how placeholder was handled in this case.

I know for a fact the following does not work:
IN(LOWER("a", "b", "c"))

Instead the following does work:
IN(LOWER("a"), LOWER("b"), LOWER("c"))

I just don't know how placeholder works in this case.
It seems a little odd for IN to be used with only one argument, so I am going to believe that placeholder is an array.

So then how do we handle properly wrapping the translated placeholder to produce the second example above?

I believe I may have to manually expand that array.

The following urls are of importance:
- http://drupal.org/node/310072#placeholder-arrays
- http://api.drupal.org/api/drupal/includes--database--query.inc/interface...
- http://api.drupal.org/api/drupal/includes--database--select.inc/function...
- http://api.drupal.org/api/drupal/includes--database--select.inc/interfac...

Now the trick is expanding it properly.
If I expand the placeholders, then I assume I need to expand the actual arguments?
Is this necessary, or is it possible to do a db_select with the following snippet:
.. "SELECT * from node where nid = :nid_1 OR nid = :nid_2", array(':nid' => array(501, 502)) ..

The QueryPlaceholderInterface::nextPlaceholder sounds like the class & function to use.