Keyword "Jesus Christ" entered in template generator gets changed to Jésus Christ (with accent)

[twinkietoes-on]

• Go to the workflow and launch a new project
• Fill in required information, and add "Jesus Christ" to the key words
• When submitted, it changes to Jésus Christ

Key word "Jesus" does not change.

Low priority.

[peterjdann]

The relevant code is at /application/libraries/Keywords.php, around line 151.

// Keyword already exists
			if (($row = $this->ci->db->where('value', $keyword)->get('keywords')->row()))
			{
				$keyword_id[] = $row->id;
			}

The unusual-looking syntax is actually CodeIgniter db speak (see https://www.codeigniter.com/userguide2/database/active_record.html#select)

It turns out the underlying problem is how collation is set up in our database. To see this in action, run the following query:

select "jesus christ" = "Jésus Christ"

This query returns true.

I can't begin to contemplate what the ramifications of changing the DB collation might be, but they might not be minor, and they might be hard to predict in advance.

For this specific case, which does, after all, concern a rather major cultural figure, I suggest the most viable solution would be to write a migration function that change the keywords value that is currently "Jésus Christ" to "Jesus Christ". Precisely because of the way our collation is set up, there is not ALREADY a keyword whose value is "Jesus Christ", so this will not cause a duplication.

Alternatively (and probably easier still) just change the keyword value directly in the production database. (Eek! Did I just say that???)

[peterjdann]

While I've not attempted this, it should be possible to alter the collation of just the keywords table (https://mariadb.com/kb/en/setting-character-sets-and-collations/). This would solve the so-called "bug" reported here, but could lead to a situation where we have the keywords "jesus christ", "Jesus Christ" "Jésus Christ" and who knows how many more variations all as separate keyword entries and all — presumably — intended to represent the same thing.

If we did this, there would be ramifications for the change we've just accepted into our code base (but not yet made live) that would show hyperlinked keywords at the bottom of each audiobook's catalog page. Without further changes to THAT code, the book twinkitoes-on is working on would show up with the keyword "Jesus Christ" (as intended) with no hyperlinks at all — because even though there are twenty something works in our catalog now with the keyword "Jésus Christ", that code would currently not recognise them as related.

That's all fixable - somehow - but would we really want to go down the path of allowing such variations to exist within our keywords?

Strange as it may seem, I believe the behaviour we're seeing here is not actually a bug, but by design. It just turns out to be a design that has consequences probably unforeseen if the first user to have entered a keyword entered a version of it rather different from most subsequent uses of the "same" keyword.