Filtered value comparisons

As with case-sensitivity, it seems that treatment of accented characters is server-specific (at least with MySQL). With my server locale set to en_AU, MySQL considers 'cafe' equivalent to 'café'. Presumably if my server locale was set to French, this would not be the case, so although in my case the behaviour is as desired, it would be best to be explicit.

You can do a strict comparison on a per-query basis by adding 'collate utf8_bin' to the end of your SELECT query, but I don't know whether this works the same in PostgreSQL. If somebody can check this out for me, I'd be much obliged. In the meantime I'll make this functionality dependent on MySQL.

Okay, so this has turned out to be a hairier yak than I originally thought.

It occurred to me that the most flexible way to tackle this would be to leverage filter formats. So we associate each unique field with a filter format (a default one is provided), which will - usually - be different to the one (if any) used to display the value of the field. This is configured in the CCK global field settings form fieldset (admin/content/node-type/[content_type]/fields/[field_type] - I just couldn't figure out the best place to put this for the node title field, so for the moment title values are stuck on the default unique field filter format). When the test for matching values is done we compare the new value as run through the filter format against our own stored values which have already been filtered.

I do not know how PostgreSQL works with unicode, so for the moment I've set this to only work with MySQL. I'd really appreciate input from PostgreSQL people.

This works far less efficiently than it might, often just dumping and recreating whole tables on configuration changes where it might in theory be possible to update some subset of the records. This may cause unnecessary problems with really big datasets; I don't know. I wanted to get some feedback as to whether this is a useful direction to pursue before putting a lot of effort into performance optimisation.

The default unique field filter provided converts all values to lower case, to in effect retain the existing (depending on your database collation configuration) behaviour. You can remove that filter from the default format (or create another format without that filter and set your desired field to use that) to get case-sensitivity. You could also install the Filter Transliteration module and enable that filter if you want 'cafe' to match 'café'. Go crazy. There are lots of modules that provide filters out there, and writing your own is pretty trivial.

Please test away. The patch below is against the latest commit on the 6.x-1.x branch.

Damn, needs some code in the update hook to generate the db tables. In the meantime, disabling and re-enabling the module will have the same effect.

And it spits the dummy when trying to create tables for email fields.

This patch appears to fix the issues above. Review appreciated.

Rerolled to fix a minor bug and add needed database table creation code in upgrade function.

Yet another minor modification to refresh stored values when field filter configuration is changed.

Node titles are 255 bytes long, not 12. D'oh!