topic Re: CMIS Search by cmis:name case sensitive? in Alfresco Archive

CMIS Search by cmis:name case sensitive?

nikes — Tue, 30 Mar 2010 08:49:28 GMT

Hello friends,I have document : TestCMISDoc.pdfI tried following query for searching documents by nametring query = "SELECT * FROM cmis:document where cmis:name like '%test%'";‍‍‍Above query doesn't search TestCMISDoc.pdfbut if I execute followingtring query = "SELECT * FROM cmis:document where

Re: CMIS Search by cmis:name case sensitive?

andy — Thu, 01 Apr 2010 15:14:16 GMT

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

Re: CMIS Search by cmis:name case sensitive?

nikes — Thu, 08 Apr 2010 10:20:40 GMT

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.

Re: CMIS Search by cmis:name case sensitive?

southpointtech — Thu, 08 Apr 2010 15:52:10 GMT

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.

Re: CMIS Search by cmis:name case sensitive?

andy — Mon, 12 Apr 2010 18:55:50 GMT

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

Re: CMIS Search by cmis:name case sensitive?

mcs130 — Tue, 25 Sep 2012 19:43:32 GMT

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