Case and Width Insensitive Matching for SQL Queries

[yamashita-ki]

Description I propose the addition of a feature to Doma 2 that allows for case and width insensitive matching in SQL queries. This feature would enable developers to write queries that can automatically match strings regardless of their case (uppercase or lowercase) and character width (full-width or half-width characters), simplifying data retrieval in applications dealing with diverse input data. Currently, when performing string matching in SQL queries using Doma 2, developers must explicitly handle variations in character case and width, which can be cumbersome and error-prone. For applications that accept user input in various forms (e.g., search functionalities), ensuring consistent data retrieval regardless of these variations requires additional logic at the application level, leading to more complex and less maintainable code.

Implementation ideas Example:

@Dao
public interface ExampleDao {
    @Select
    List<Item> selectByName(@Insensitive String name);
}

[nakamura-to]

Thank you for your proposal.

Could you tell me which RDBMS you are using and what kind of SQL you would like to execute?

Some RDBMS provide a feature called collation, which might meet your needs. https://learn.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16

[yamashita-ki]

Thank you for your response and for considering my feature request.

Could you tell me which RDBMS you are using and what kind of SQL you would like to execute?

I am currently using Oracle. Here's an example of the kind of SQL I would like to execute:

SELECT * FROM users
WHERE REGEXP_LIKE ( users.name, '[[=t=]][[=a=]][[=r=]][[=o=]]')

[nakamura-to]

Thank you for sharing the information.

I think creating a custom function would be suitable in your case.

1. Create a class that inherits from OracleExpressionFunctions and write a method that generates the string to be passed to the pattern argument of the REGEXP_LIKE function.

   
   package example:

public class MyFunctions extends OracleExpressionFunctions { public String insensitive(String text) { return ...; } }

2. Pass an instance of the above MyFunctions to the constructor of OracleDialect.
```java
OracleDialect dialect = new OracleDialect(new MyFunctions());

3. Specify the fully qualified name of the above MyFunctions in the options for annotation processing (the following example is when writing in build.gradle.kts).

   tasks {
   compileJava {
       options.compilerArgs.addAll(listOf("-Adoma.expr.functions=example.MyFunctions"))
   }
   }

4. Call the method defined in MyFunctions from the SQL template.

   SELECT * FROM users
   WHERE REGEXP_LIKE ( users.name, /* @insensitive(name) */'[[=t=]][[=a=]][[=r=]][[=o=]]')

See also https://doma.readthedocs.io/en/latest/expression/#using-custom-functions.

[yamashita-ki]

@nakamura-to Thank you for your response, despite it being a day off.

Is there any plan to include a custom function named @insensitive in the next update? I understand it might be challenging given the need to accommodate different RDBMS.

[nakamura-to]

No, there are no plans to do so at this time. There are two reasons for this:

1. As you mentioned, it is challenging to accommodate different RDBMS.
2. Even if we focused on a specific RDBMS, we believe it would be difficult to write a generic logic that meets the needs of many users.

If you try out a custom function and it works well, I would be happy if you could share that function with us. If many users agree with that function, we might consider incorporating it into Doma.

[yamashita-ki]

Understood. Since this issue has been resolved, I will go ahead and close it. Thank you very much for your assistance.