Querying Alfresco CMIS - How to check Alfresco CMIS queries

During developing a content centric application in CMIS with your favorite language and library (OpenCMIS, cmislib, DotCMIS, PortCMIS), it is usual to check your CMIS queries against repository. You can do it easily in several ways:

Let's give some examples that may be useful in some situation:

SELECT * FROM cmis:document WHERE cmis:createdBy = 'mjackson'
SELECT * FROM qshare:shared WHERE qshare:sharedBy = 'ccl001' ORDER BY cmis:creationDate
SELECT * FROM cm:effectivity ORDER by cm:to DESC
SELECT * FROM cm:lockable WHERE cmis:name = '*(Working Copy)*'
SELECT * FROM zpm:statusable WHERE zpm:mystatus = 'zpm.estado.borrador'

1. CMIS Workbench / Groovy Console

The always helpful CMIS workbench is useful for testing and inspecting CMIS objects in detail. The query console is able to give you a limited set (i.e. 1000), while Groovy scripting may help to iterate a large resultset with pagination.

Besides CMIS Workbench is able to show all details (properties, associations...) for a given object or to inspect Alfresco dictionary of content types and aspects. You may download it from:

• https://chemistry.apache.org/java/developing/tools/dev-tools-workbench.html

2. Alfresco Node Browser

  In Alfresco Node Browser (better the one that is in Alfresco Admin Console than the Alfresco Share one), you can check your CMIS queries either alfresco-cmis, alfresco-strict or even db-cmis (if you are using transactional if possible set in search subssytem). It is able to give you a defined and configurable number of results, and it does not provide a specific details / column on the given objects, only the object name, parent and node reference. Some extra actions are available too for the obtained nodes.
 


If you are not using TMQ (Transactional Metadata Queries), you may obtain messages like the following, when selecting db-cmis query type:

Transactional metadata queries involve the application of additional database indices for working properly.
 
3. Javascript Console

With search.query() we can also run alfresco-cmis querys, and do pagination if necessary in JS console. For example:

JS Script 1 (basic cmis query)

var s = {};

s.page = {};
s.query         = "SELECT * FROM cm:effectivity ORDER by cm:to DESC";
s.language      = 'cmis-strict';
//s.language      = 'cmis-alfresco';
s.page.maxItems = 2000;

var nodes = search.query(s);

logger.log(nodes.length);
for each(var node in nodes) {
    logger.log(node.name + ' (' + node.typeShort + '): ' + node.nodeRef);
}

JS Script 2 (with pagination, for more than 1000 docs)

var pageSize = 100; // no more than 1000!!!
var currentPage = 0;
var currentPageSize = -1;

var q = 'SELECT * FROM cm:effectivity ORDER by cm:to DESC';

var sort1 = { column: 'cm:to', ascending: false };
var paging = { maxItems: pageSize, skipCount: 0 };
var def = { query: q, store: 'workspace://SpacesStore', language: 'cmis-alfresco', sort: [sort1], page: paging };

var i = 0;        

while (currentPageSize != 0) {
  paging.skipCount = currentPage * pageSize; currentPage++;
  var nodes = search.query(def);
  currentPageSize = (null != nodes ? nodes.length : 0);
  if (currentPageSize > 0) {
    nodes.forEach(function(node) {
    
      i++;
          logger.log(node.name + ' (' + node.typeShort + '): ' + node.nodeRef);
                
    });
  }
}

You must install the addon for scripting JS in Alfresco:

• https://github.com/share-extras/js-console

4. Via curl (command line)

Just check the following examples from Angel Borroy's blog. It may be helpful in some situations although the query statement is not probably the more usable without some kind of script:

• https://angelborroy.wordpress.com/2019/06/03/using-cmis-browser-protocol/

Anyway based on this post, I wrote a small cmis-browser.sh script with curl and jq. Let's check the code from my Gist

• https://gist.github.com/CesarCapillas/7a99e3a3e9cb8cde1f4a6f81709be67c

Here some examples,

cesar@lemmy ~ $ cmis-browser.sh -h
Usage: cmis-browser.sh [PATH=] [ACTION=properties|parents|aspects
       cmis-browser.sh
       cmis-browser.sh Sites/swsdp/documentLibrary


cesar@lemmy ~ $ cmis-browser.sh 
a1feeee4-85ab-49cb-944a-c28421bc4e03 [F:st:sites] --> Sitios
717b03c9-82c1-4714-a1a8-4e0d400a93a8 [cmis:folder] --> Imap Attachments
6e4e0382-3276-4e1a-932a-6350efe4e3f8 [cmis:folder] --> Espacio de invitado
92329d5f-f841-4a8f-93d4-25d0eedb0fed [cmis:folder] --> Zylk
20621d28-bf54-4a80-b127-d7883e739353 [cmis:folder] --> Compartido
146ed9e1-aec8-47e8-886c-b7cbb9918386 [cmis:folder] --> Espacios personales de usuario
7dc5c0c5-2617-47d5-95bf-ba4038b89b09 [cmis:folder] --> Diccionario de datos
f11759b6-c2da-4d93-b1e1-0bd143b5c17b [cmis:folder] --> Imap Home
bb81dd7c-23c7-4488-b5d4-f454412be9e1 [cmis:folder] --> Adjuntos IMAP

cesar@lemmy ~ $ cmis-browser.sh 'Zylk'
dc0f4336-254b-4b1a-bf97-af98e37f3246 [cmis:folder] --> Archivo
2856748a-11e9-49bd-9a50-830fc2c13412 [cmis:folder] --> zadmin
9ac2327e-873f-4b7e-9942-ab8a19048804 [cmis:folder] --> Guest
06b89106-684d-49bf-8ddf-b663daf4e15d [cmis:folder] --> Demo

5. Scripting with python or groovy

This is an easy way for checking and processing CMIS queries via script, with cmislib for python ,or java OpenCMIS library for groovy. Some examples are found here:

• https://chemistry.apache.org/python/docs/examples.html
• https://gist.github.com/pmonks/9674126