ADF supports case-insensitive queries but it relies on use of the UPPER() function to wrap the criteria and column selection. We made design decisions a long time ago, that (A) the business data would not be converted to all upper case, and (B) the application search screens would not convert user criteria to upper case. To make all searches case-insensitive, the application use Oracle’s case-insensitive string comparison feature NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI (where CI means case-insensitive). Furthermore, to support indexed case-insensitive searches, the data modeler added linguist-sort indexes on all indexed character strings. That works fine for the “=” operator but we found out it does not work for the LIKE operator. This has been fixed in the 11g database but if using a 10g database, filtering using the like operator will invalidate the indexes and produce a full table scan. Furthermore, the use of the UPPER() function on the table column will invalidate the linguistic sort index so we can't use the ADF way of making filters be case insensitive.
The reason filters on tables became case sensitive in PS2 was that even though the database was case insensitive by default, ADF would perform an in-memory filter immediately after executing the query and the in-memory filter, being case sensitive would remove all of the non-matching rows from the result set. The fix for this was a little tricky. All of our view objects inherit from a custom base class we created. In this class we have overridden the getViewCriteraClause(boolean forQuery) method. This method is called whenever ADF is about to apply view criteria (i.e. filter) for either a database or a in-memory query. It conveniently lets you know which type (forQuery) it is requesting. So now all we need to do is return null when forQuery==false and no criteria will be applied to in-memory filters. But what if we actually want to perform in-memory filtering? Well, we can check the query mode to determine if the query is a "database only" query and no in-memory filtering should be performed. The problem caused in PS2 was because ADF was performing this in-memory filter even though the query mode had not been set to do so. Therefore we can detect this condition and only allow in-memory filtering when the view's query mode is set to do so.
In the example code below, I'm allowing the in-memory filter to occur but simply calling vc.setUpperColumns(true); to put the UPPER() clause around the in-memory filter making it case insensitive. I want to do just the opposite when the query is bound for the database. There is one section of code added there to support date filters where we skip the in-memory filter all together by returning null. I'll talk about that in a later blog.
public String getViewCriteriaClause(boolean forQuery) {
if (forQuery == false){
// clause is for cache (in-memory)
// WARNING - It is possible to set the criteria mode to cache and the query mode
// to database. That will query for a larger result set, then immediately filter it down in-memory
// queryModeDoesNotUseCache will be true in that case even though it performs a
// in-memory filter after the fact. Doing so is not recommended as it wastes memory unless you
boolean queryModeDoesNotUseCache =
((getQueryMode() & ~QUERY_MODE_SCAN_DATABASE_TABLES) == 0);
ViewCriteria[] vcs =
getApplyViewCriterias(ViewCriteria.CRITERIA_MODE_CACHE);
if (vcs != null && vcs.length > 0) {
boolean criteriaForCacheModeOnlyFound = false; //
for (ViewCriteria vc : vcs) {
criteriaForCacheModeOnlyFound |=
(vc.getCriteriaMode() == ViewCriteria.CRITERIA_MODE_CACHE);
if (vc.isUpperColumns() == false) {
vc.setUpperColumns(true); // puts UPPER(..) around the values for filtering in-memory
}
}
if (queryModeDoesNotUseCache &&
!criteriaForCacheModeOnlyFound) {
// if query mode does not apply to cache and criteria mode
// is not "cache only" mode (i.e not for both cache & database)
// don't apply any in-memory filter.
// This is needed in PS2 to prevent in-memory filtering of dates which causes
// no results because the time component is compared as well.
// see CustomSQLBuilderImpl which has a PS2 workaround to trunc the date
// so the date filters work (without comparing time component)
// trunc will fail if applied to a in-memory filter on PS2
return null;
}
}
} else{
ViewCriteria[] vcs =
getApplyViewCriterias(ViewCriteria.CRITERIA_MODE_QUERY);
if (vcs != null && vcs.length > 0) {
for (ViewCriteria vc : vcs) {
if (vc.isUpperColumns()) {
vc.setUpperColumns(false); // remove UPPER(..) around the values for database queryies
}
}
}
}
String clause = super.getViewCriteriaClause(forQuery);
return clause;
}
