Remove lower from taxonomy autocomplete

Looking more at http://drupal.org/node/83738 this ought to be fine. New patch has an upgrade path which I've tested, and a slight tidy up for the code so marking to needs review. taxonomy tests still pass as well.

If this is suitable, we could apply the same approach to user.module

New patch fixes whitespace issues, thanks.

On batch API, I don't know to be honest.

We have queries like this in system_update_x though:

db_query('UPDATE {term_node} t SET vid = (SELECT vid FROM {node} n WHERE t.nid = n.nid)');
$ret[] = update_sql("UPDATE {profile_fields} SET category = 'Account settings' WHERE LOWER(category) = 'account'");

Which deal with potentially much larger numbers of records, so I'd rather avoid adding in batch API for now, at least until there's major objections.

Damien, do you have a link to the relevant postgres docs for this? All I can find is:

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

Which suggests otherwise, but I know very little about postgres so...

Additionally, SQLite docs suggest LIKE is case_sensitive there too - although this is mentioned parenthetically as a bug. Since we're hoping to add SQLite this cycle, I'd prefer a solution which works with all three databases reliably. http://www.sqlite.org/lang_expr.html

Having said that, if I'm wrong, and we can just remove LOWER() from these queries, that'd be lovely, and easily back portable to D6, but it'd be nice to know it won't just store up problems for later first.

Will do a quick test on MySQL later, don't have postgres to test on. Back to needs review until we're sure just stripping it out will work across at least the three target databases. edit: cross-posted.

I got bored waiting for devel generate to create 1 million terms, but with 52485 terms it takes 6.85 seconds to run that query, which I think ought to be acceptable - tracker select queries can take 25-30 seconds or more on big sites so for a one off it seems doable.

Spoke to chx and Crell on irc, and they said the new database layer may allow us to use ILIKE in postgres and LIKE in mysql for queries like this - getting rid of the case sensitivity issues without requiring a new column. Obviously that'd be an ideal solution, so marking postponed (rather than won't fix, so I remember to revisit this when that's possible).

Never that easy is it :(

http://archives.postgresql.org/pgsql-sql/2003-09/msg00395.php

If we can use the new DB layer to swap LIKE for ILIKE, and then add the computed index in postgres via schema API, then I agree this would be a more elegant solution (although possibly username/name in user.module is worth doing with the extra column anyway for other reasons).

However, this opens us up to maintaining a different schema between postgres and MySQL - and then we may have to introduce other special cases for SQLite or whatever gets supported in the future (or they'll just be slower). webchick already brought this up on one of the grandfather issues to this one: http://drupal.org/node/83738#comment-447137

Probably we should wait and see what happens with the DB layer, and explore an index on lower in more depth. However I'll leave this at needs work instead of postponed in the hope that others will chime in.

Retitling now dbtng is in.

We're still using a straight LOWER() in taxonomy_get_term_by_name(), which is used in taxonomy_node_save() on each entered free-tagging term amongst other places in contrib.

So I think it's worth having an indexable query for that, which we can do easily with the LIKE to ILIKE mapping. But indeed, autocomplete was the main win here and that's looks un-fixable.

Marked #175421: taxonomy_get_term_by_name uses "wrong" SQL as duplicate - this snippet is interesting about the LIKE indexes - http://pastie.caboo.se/96865

taxonomy_get_term_by_name() now uses LIKE on MySQL and ILIKE on postgresql. So marking this duplicate of #343788: Taxonomy module doesn't use it's own APIs. Like David said, the autocomplete is unfixable with LIKE '%%%$string%%%'.