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 scarter@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
managedobjectproperties.propvaluecolumn toutf8_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 thepropertiesTableentry in yourrepo.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, refer to Mappings with a JDBC repository.
-
For an Oracle DB repository, refer to the corresponding Oracle documentation.
-
For a SQL Server repository, refer to the corresponding Windows documentation.
-
For a DB2 repository, refer to the corresponding DB2 documentation.