Case insensitivity for a JDBC repo
The following topic only applies if you have set up a JDBC repository, as described in Select a repository
A DS repository is case-insensitive by default. The supported JDBC repositories are generally case-sensitive by default. Case-sensitivity can cause issues if queries expect results to be returned, regardless of case.
For example, with the default configuration of a MySQL database, a search for an email address of
email@example.com might return a result, while a search for
scarter@EXAMPLE.COM might return an
Unable to find account error.
If you need to support case-insensitive queries, you must configure a case-insensitive collation in your JDBC repository, on the specific columns that require it. For example:
For a generic managed object mapping in MySQL or MariaDB, change the default collation of the
utf8_general_ci. Note that this changes case-sensitivity for all managed object properties. To change case-sensitivity for all the properties of a specific object, specify a different table for the
propertiesTableentry in your
repo.jdbc.jsonfor that object, and adjust the collation on that table. To change case-sensitivity only for certain properties of an object, use an explicit mapping.
For a PostgreSQL repository, use an explicit table structure if you require case-insensitivity. Managing case-insensitivity at scale with generic tables in PostgreSQL is not supported. For more information about object mappings, see Mappings with a JDBC repository.
For an Oracle DB repository, see the corresponding Oracle documentation.
For a SQL Server repository, see the corresponding Windows documentation.
For a DB2 repository, see the corresponding DB2 documentation.