CMIS Search by cmis:name case sensitive?

[nikes]

Hello friends,

I have document : TestCMISDoc.pdf

I tried following query for searching documents by name:

String query = "SELECT * FROM cmis:document where cmis:name like '%test%'";
‍‍‍

Above query doesn't search TestCMISDoc.pdf

but if I execute following:

String query = "SELECT * FROM cmis:document where cmis:name like '%Test%'";
‍‍‍

It searches document successfully.

Is there any way to make cmis search case-insensitive?

Thanks for any help.

[andy]

Hi

It is not possible to make your CMIS search example case insensitive.
Collation is up to the repo, just like some databases are case sensitive.

It is arguable that case sensitiveness for identifiers should be exposed for properties on their definition.

In fact, cm:name is indexed both as an identifier and for full text search. CMIS QL uses the identifier bit.
If you depart from strict CMIS SQL and use Alfrecso FTS within the CONTAINS() expression then you culd use the FTS index for cm:name.
However, you will get other matching issues due to tokenisation.

Andy

[nikes]

Thanks Andy,

I don't have expertise in Alfresco CMIS implementation, but what I observed is,

Search files with file names in Alfresco Explorer(JSF) is not case sensitive!

So still my doubt is, using CMIS QL I am passing query parameters, which Alfresco CMIS implementation class uses to search
in Alfresco repository.

Does that mean it depends on Implementation of CMIS spec in repository?

If we make Alfresco CMIS implementation case-insensitive, will it return result matching search parameter irrespective of case?

Thanks for any help.

[southpointtech]

The CMIS spec indicates that the CMIS SQL language is based on SQL 92 specs, if it is then you would expect to be able to use functions such as LOWER() command on a string field or string value.

I would suggest trying
String query = "SELECT * FROM cmis:document where Lower(cmis:name)  like Lower('%Test%') "  ;
Or something close to this.

[andy]

Hi

The CMIS spec only includes the SCORE() function. CMIS QL is a subset of SQL-92 with extensions.  LOWER() and UPPER() functions were actually removed from the spec.
As they were done, they are available in the Alfresco extension to CMIS QL. Again, not ideal.

SQL does exact matching as opposed to token based matching (which is what the UI is doing).

You can do token based matching via CONTAINS() but again you will be using non-standard extensions (in CMIS you can not specify the property name to use)

Andy

[mcs130]

I know this thread is older, but is anyone aware if this is still the case - I believe that would be YES…and if enhancement of the CMIS SQL rules (SQL-92 based) may perhaps address the LOWER() and UPPER() capability in CMIS 1.1. 

If I am not mistaken, these can usually cost in performance in typical direct relational DB SQL queries.  I suppose having the SQL queries return hits regardless of case of the text String values stored would be a problem, in general, for CMIS SQL implementations. 

The example in the thread talks about cmis:name (a standard property), however if you add custom text properties to your model, say acme:category and acme:subCategory, one faces the same restriction regardless, I assume.  It's about the CMIS SQL implementation and not the model properties themselves.

Since the underlying search engine is Solr (and or Lucene), is there any ability to configure for case-neutral search ability? (that might be a bit of reach so just throwing it out there).

Thanks.

Mark