Enhancement: ListGrid case sensitivity / Information about DB case sensitivity

[Blama]

Hi Isomorphic,

it seems that ListGrid's text columns are case insensitive when sorting.

From my test, this is true for the sample's HSQLDB as well, at least it seems to be configured this way.

I found out using this BuiltInDS.java (see especially setDataPageSize(5) and setDrawAllMaxCells(0))

package com.smartgwt.sample.client;

import com.google.gwt.core.client.EntryPoint;
import com.smartgwt.client.core.KeyIdentifier;
import com.smartgwt.client.data.DataSource;
import com.smartgwt.client.data.SortSpecifier;
import com.smartgwt.client.types.SortDirection;
import com.smartgwt.client.util.PageKeyHandler;
import com.smartgwt.client.util.Page;
import com.smartgwt.client.util.SC;
import com.smartgwt.client.widgets.IButton;
import com.smartgwt.client.widgets.events.ClickEvent;
import com.smartgwt.client.widgets.events.ClickHandler;
import com.smartgwt.client.widgets.grid.ListGrid;
import com.smartgwt.client.widgets.grid.ListGridField;
import com.smartgwt.client.widgets.layout.VLayout;

public class BuiltInDS implements EntryPoint {
	private ListGrid boundList;
	private IButton btn;

	public void onModuleLoad() {
		KeyIdentifier debugKey = new KeyIdentifier();
		debugKey.setCtrlKey(true);
		debugKey.setKeyName("D");

		Page.registerKey(debugKey, new PageKeyHandler() {
			public void execute(String keyName) {
				SC.showConsole();
			}
		});

		boundList = new ListGrid(DataSource.get("animals"));
		boundList.setWidth(1200);
		boundList.setHeight(100);
		boundList.setCanMultiSort(true);
		boundList.setCanSort(true);
		boundList.setShowFilterEditor(true);
		boundList.setAutoFetchData(false);
[B]		boundList.setDataPageSize(5);
		boundList.setDrawAllMaxCells(0);
[/B]
		ListGridField commonName = new ListGridField("commonName");
		ListGridField scientificName = new ListGridField("scientificName");
		ListGridField lifeSpan = new ListGridField("lifeSpan");
		lifeSpan.setTitle("Our title");
		lifeSpan.setSortByDisplayField(true);
		ListGridField diet = new ListGridField("diet");
		ListGridField information = new ListGridField("information");

		boundList.setFields(commonName, scientificName, lifeSpan, diet, information);
		boundList.setSort(new SortSpecifier("scientificName", SortDirection.ASCENDING));
		boundList.fetchData();

		btn = new IButton("invalidateCache()");
		btn.addClickHandler(new ClickHandler() {
			@Override
			public void onClick(ClickEvent event) {
				boundList.invalidateCache();
			}
		});

		VLayout vLayout = new VLayout(10);
		vLayout.setMembers(boundList, btn);
		vLayout.draw();
	}
}

and scrolling slowly while watching the DSRequests and their answers go by.
"southern subspecies: Haliaeetus leucocephalus leuc" came before scientificName:"Struthio camelus" and after "Python molurus molurus".

This is not always the case. Oracle for example is case sensitive using the standard AL32UTF8 Character Set.

So by just using a different DB, the content of a sorted ListGrid showing let's say company names (some starting with capital letters, some starting with small letters) is different (as only the first some x sorted entries are transferred to the server).

The suggested enhancement is
a) To introduce a setting telling the framework if the DB is CS or CI (CS sorting is not possible if the system is CI).
b) Introduce a default setting for the framework (e.g. "always sort CI yes/no").
c) Introduce override points at ListGridField-level like setSortCaseInsensitive(Boolean).

As I'm sure this (especially "c") is a pretty big change and ListGrid seems to be always CI by default (when no SortSpecifiers are used) currently (I did not find a CI/CS related setting in the docs), a server.properties setting like alwaysSortCaseInsensitive (point "b") would be useful, that, when activated always adds a LOWER() around the ORDER BY-columns of the generated SQL.

Out of curiosity: Do you re-sort on the client side if you get a sorted (by the requested columns, in requested order, in requested ASC/DESC) DSResponse from the server?

Best regards,
Blama

[Isomorphic]

Different databases have different default behavior for case sensitivity on sort.

It would be a bad idea in general for SmartGWT to enforce a standard across databases, as this could have severe performance consequences. For example, forcing Oracle to be case-insensitive via using lower() on every String value in ORDER BY clauses could cause indices not to be used, when the indices are based on case-sensitive values.

If you're interested in features that would allow additional control here, it could be a valid Feature Sponsorship, although bear in mind that databases have some annoying differences here that would prevent any kind of truly uniform behavior across databases. For example, DB2 requires you to commit to case sensitivity at database creation time.

Yes, we always use a client-side sort whenever we get a complete cache.

[Blama]

Hi Isomorphic,

w.r.t. to the last sentence of your last post:

Yes, we always use a client-side sort whenever we get a complete cache.

Do you also do a client side sort for a non complete cache (e.g. does this result in 2x client side sorting? ListGrid opens, displays rows 0-75 of 1000 rows, after some scrolling loads rows 76-150)?

And w.r.t. to the whole thread: If I can't control SmartGWT client side sorting currently, what sorting does the client side use? If I know this I can lost likely make Oracle use the same sorting with its linguistic sorting.
I also can create NLS-aware indexes so that this sorting is fast.
Reason for this is that I have a complaint about my application being case sensitive which shows when the server returns data for a large list and lower case names do not appear at the top with the upper case names.
So if I change something, I want to do it right once and for all.

Could you therefore answer the following questions?

• Your sorting is case-insensitive and also (?) accent-insensitive, correct? Is there some ISO number (or native browser JavaScript function) you can refer to?
• Does client side sorting depend on the current locale?
• If you did this before in your customer projects: Do you have any recommendations for this w.r.t.
  • Oracle session settings (NLS_SORT, NLS_COMP) and how to apply them (system setting or login trigger)
  • Oracle collation (NLS_CHARACTERSET AL32UTF8?)
  • Oracle index creation (linguistic?)

Thank you & Best regards
Blama

[Blama]

Hi Isomorphic,

with respect to this post of yours I think that you will suggest using some CI setting for NLS_SORT and LINGUISTIC for NLS_COMP.

With respect to this sample (German locale in the link), setting isc_ListGrid_0.setCanEdit(true); in Developer Console and then editing some string to include "ä" (a-Umlaut) and then client side filtering for "a" (normal a) I assume that you will not suggest using an AI (=case insensitive and accent insensitive) setting.

Is this correct?

Next is the DB collation CS/CI setting which now should not matter for Oracle - only problem here was with DB unique constraints, but since you added the feature in the linked thread above some time ago, this should be solved.

If the above is all true I think the only thing left for me is

• Change DB or session settings for NLS_COMP and NLS_SORT (session setting or trigger).
• Recreate indexes with correct NLS_SORT (as it seems automatic in 12.2, but I'm not using that one, yet)

Which NLS_SORT do you suggest? I'd go with GENERIC_M_CI.
It would be great if you could include an advice on this somewhere in the docs, because this is something that is not too easily changed, can cause problems and should be thought though when starting application development.
"Collation" is only mentioned here and in a different context here.

Best regards
Blama

[Isomorphic]

No, we don't sort incomplete data (because you cannot validly sort incomplete data - client and server row numbering would no longer match).

Client-side sorting is via the native localeCompare() JavaScript function, unless you install your own sort normalizer.

In our experience, most teams either do not bother with trying to make DB collation closely match client sort, or must be handling it without asking us / posting here.

We don't think it would be appropriate for SmartGWT to have and maintain official documentation for possible collation settings across all DBs we support, and explain the ins and outs of different settings and performance consequences. However the forums are a good place to share your approach and solicit feedback from other community members.

[Blama]

Hi Isomorphic,

thanks for the fast answer.

No, we don't sort incomplete data (because you cannot validly sort incomplete data - client and server row numbering would no longer match).

Great, that is what my gut feeling said.

Client-side sorting is via the native localeCompare() JavaScript function, unless you install your own sort normalizer.

OK thanks. You call it with the current locale as parameter, don't you? If so, I'll look up if there are any relevant locales for me that do not sort CI.

In our experience, most teams either do not bother with trying to make DB collation closely match client sort, or must be handling it without asking us / posting here.

We don't think it would be appropriate for SmartGWT to have and maintain official documentation for possible collation settings across all DBs we support, and explain the ins and outs of different settings and performance consequences. However the forums are a good place to share your approach and solicit feedback from other community members.

OK, thanks. I think that I will be fine with just CI sorting also on the server. The small subtle differences between client sorting on full cache and server sorting on partial cache w.r.t. to accents should not matter too much, but the case sensitivity sorting-differences are more easily seen.

I think that I'll go with NLS_SORT-indices and session NLS-changes. Once 12.2 gets released and I found time to switch to it, I'll also look into directly defining this for a column.
The only issue (not a problem for me IMHO) is that then exactCase queries for text fields are not possible anymore, as the Database WHERE condition is case insensitive.
Do you filter on the clientside for "ABC" if you get a partial or full result for a fetch with criteria "fieldname contains (not iContains!) ABC"?

Thank you & Best regards
Blama

[Isomorphic]

We currently call localeCompare() with no arguments (which means the current locale is used).

It's not clear what your last question means, but if you are asking whether, after receiving values from the server, we then immediately re-filter on the client, no we do not.

[Blama]

Hi Isomorphic,

We currently call localeCompare() with no arguments (which means the current locale is used).

Using Chrome in Swedish and German, I saw that localeCompare() behaves differently depending on the locale of the Chrome GUI (as expected).

console.log('ä'.localeCompare('z')); // "1" with Swedish GUI, "-1" with German GUI
console.log('ä'.localeCompare('z', 'de')); // both times "-1"
console.log('ä'.localeCompare('z', 'sv')); / both times "1"

So with respect to all the other GWT application locale sensitive things you do (date format, decimal separator, ...) shouldn't this then be also application locale sensitive, meaning you provide the GWT locale to localeCompare() as argument?

It's not clear what your last question means, but if you are asking whether, after receiving values from the server, we then immediately re-filter on the client, no we do not.

Yes, that is what I meant.
Once I finished my transition to NLS_SORT (not very soon) I'll look into this if this creates problems.
I could imagine that this will lead to a small problem:

1. Load 1000 entry ListGrid
2. Filter for iContains "Res", causing a server fetch
3. Because of NLS_COMP=LINGUISTIC this includes "Résumé"
4. Change filter "Res" to "Resu"
5. Local filtering takes place.
6. The "Résumé" entry is no longer displayed.

Currently the ListGrid filtering is Accent sensitive. And as it seems there are no locale-sensitive String functions like startsWith etc. At least I did not find these in MDN and also this SO-link suggests so.
So currently it seems that when using Oracle, one will either have my current sort problem (Server CS, Client CI) or this filter problem (Server AI, Client AS).
@All: Any suggestions for this problem (besides disabling client side filtering) are welcome.


Thank you & Best regards
Blama

[Isomorphic]

We'll check into the first possibility - it's not clear if localeCompare() actually has support for passing the locale on all relevant browsers, it could be just Chrome/Webkit.

On the second, we don't really have a good suggestion. As you say, JavaScript has no variants of startsWith or similar that are accent-insensitive, and implementing such support in JavaScript itself would have a serious performance impact if it were even feasible.

It is definitely not worth turning off client-side sorting or filtering for this issue. If your end users actually notice this problem, you could either:
1. normalize the server data to get rid of accents in cases where it's actually affecting users (like your example of searching on resume - no one will be confused by the lack of accents)
2. transform criteria to cover limited cases, for example transforming all startsWith criteria so that they are a logical OR of multiple startsWith criteria, where key characters are switched to their accented form(s) in the alternate criteria. This would slow things down, of course.

[Blama]

Hi Isomorphic,

thanks for looking into localeCompare().

On the 2nd: Yes, I will do that, at first without any special handling as I don't think that this will cause nearly as much confusion as my current case sensitivity issue. I just wanted to mention it here if someone else finds this issue via search.

Best regards
Blama