Add support for case sensitive validation to UniqueFieldValuesConstraint

This is very particularly like #3415582: Unhandled exception when trying to register a duplicate username with different case.

Was expecting this would also impact username, as it also uses that constraint - but we have a unique key on name for the users table, so instead if causes a fatal (SQL) exception - so still an issue.

Added tests and fix for both cases.

I am new here, can I work on this issue?

I verified for the use cases given above. The fix is working - Error message are displayed 'The email address EMAIL@TEST.COM is already taken.'

Testing Steps :
1. Creating user with Email@test.com
2. Verify when used different cases such as eMail@test.com , EMAIL@test.com - error message must be displayed
3. Verify when used same case Email@test.com with different usernames - - error message must be displayed

Can be moved to RTBC++

Keeping this in need review for further reviews.

Opened an MR and appears to have test failures.

Added a question to the MR.

#3415582: Unhandled exception when trying to register a duplicate username with different case is in so let's repurpose this

This appears to have been added in the latest core security update:

https://www.drupal.org/sa-core-2024-004

https://git.drupalcode.org/project/drupal/-/commit/f1d3470b057e84fd5483b...

Is this fixed in total by the security release?

A comment here that may save others time.

Today, I added the UniqueField constraint onto a base field on a custom entity type, setting caseSensitive to TRUE, and found that it did not work. I created a new entity and entered a value for this field that matched an existing value, except for a change in the case of a single letter (e.g. "bar" vs "Bar"). This validator rejected the new entity. but I would have expected it to accept it.

As it happens, my field is a string type, and I was able to fix this by setting the case_sensitive field option to force the SQL field storage to store it in a format that always behaves case-sensitively in queries. All in all, this is the sort of base field definition I now have:

    $fields['foo'] = BaseFieldDefinition::create('string')
      ->setTranslatable(FALSE)
      ->setLabel(t('Foo'))
      ->setRequired(TRUE)
      ->addConstraint('UniqueField', ['caseSensitive' => TRUE])
      ->setSetting('case_sensitive', TRUE)
      ->setSetting('max_length', 255);

It may be relevant that I'm using MariaDB (version 10.4) rather than MySQL.

On balance this feels to me like unexpected behaviour in the UniqueField Constraint that should be improved. There's nothing in the documentation of the caseSensitive option that indicates extra steps are necessary in the SQL storage to make it work.

The UniqueFieldValueValidator uses an entity query, and entity query has supported case-sensitive comparisons since #2068655: Entity fields do not support case sensitive queries. Perhaps a better implementation here would be to keep the OR condition when caseSensitive, but use an operator like LIKE BINARY?

Wanted to bump this 1 more time if still relevant.