MySQL adapter:
We use MySQL compatible databases that do not support collations for case insensitive searches. In that case, the where clause may need to be changed while sorting will still be based on UPPER etc if it is for the string field.
Right now, in where clause we are just blindly using UPPER which may not work for some languages. We should enhance the code so that we can reassign/ create/ override a builder of where clauses and define when creating instance. For example, Starrocks uses ILIKE for case insensitive where clause.
For sort, it may be more difficult as we may not be able to easily determine if a field is string or not and needs upper etc for order by.
For that, we may want to create a method that initializes a cache of schem/ table and columns. Then before execute, identify the select columns, order by, where etc. use the map to determine if ILIKE should be used or UPPER for order by. We should also keep a provision for shadow columns. For example, in User table, I can have FullName and FullName_Shadow where FullName_Shadow is already lower and indexed. So, the order by must by on FullName_Shadow.
Similarly, some columns may have full text index or similar and may need to use associated appropriate method to do full text search.
Some databases may not support ILIKE so we may continue to use UPPER.
Another option could be - cache and similar logic can stay in MySQL class and we may want an adapter based on parameter to inject/ change string related methods.
MySQL adapter:
We use MySQL compatible databases that do not support collations for case insensitive searches. In that case, the where clause may need to be changed while sorting will still be based on UPPER etc if it is for the string field.
Right now, in where clause we are just blindly using UPPER which may not work for some languages. We should enhance the code so that we can reassign/ create/ override a builder of where clauses and define when creating instance. For example, Starrocks uses ILIKE for case insensitive where clause.
For sort, it may be more difficult as we may not be able to easily determine if a field is string or not and needs upper etc for order by.
For that, we may want to create a method that initializes a cache of schem/ table and columns. Then before execute, identify the select columns, order by, where etc. use the map to determine if ILIKE should be used or UPPER for order by. We should also keep a provision for shadow columns. For example, in User table, I can have FullName and FullName_Shadow where FullName_Shadow is already lower and indexed. So, the order by must by on FullName_Shadow.
Similarly, some columns may have full text index or similar and may need to use associated appropriate method to do full text search.
Some databases may not support ILIKE so we may continue to use UPPER.
Another option could be - cache and similar logic can stay in MySQL class and we may want an adapter based on parameter to inject/ change string related methods.