Query

CMIS query was made available in Technology One’s implementation of CMIS during the 2016A release. It provides a means for users to dynamically retrieve document sets and index entries based on property values.

Queryable properties can be checked via CMIS Workbench:

Queryable properties using workbench

Workbench example

Open workbench and press the Query button on the main menu. This will open a new query window that allows for a cmis sql statement to be entered. The cmis spec defines how a query must be structured, however there are several additional constraints that the ECM implementation applies:

Restricted Indexes:
 

Only indexes that have been whitelisted in CMIS configuration are searchable.
Operators:

CMIS will support the same types of queries that ECM Search supports. This includes equals =, not equals <>, greater than >, less than <, greater than or equal to >=, less than or equal to <=, between between 'from' and 'to', like like 'report%', is empty, is not empty, starts with, ends with, contains, and sounds like. The use of these terms will depend on the type of field you are searching on, for example you can not use contains on a numeric field.

It also supports boolean operators such as AND, NOT, OR; and the use of brackets to group query terms.
Searchable tables:
 

T1ActiveUserType provides access to all users and groups that are currently active within ECM.

T1DocumentType allows for individual documents to be queried. Note that this will return all documents and versions. This differs from T1FolderType which only returns the latest version.

T1FieldDetailType shows field details which can be used to label document and index entry fields.

T1FolderType provides access to document sets.

T1IndexEntryType exposes index entries. Note that user fields can be mapped to their labels via T1FieldDetailType.
Querying via Workbench

In order to retrieve all document sets authored by TechOne that belong to the TechOne QAP the following query would be used:

SELECT *
FROM T1FolderType
WHERE cmis:author = 'TechOne'
           AND cmis:templateId = '8'

The T1FolderType table indicates that the search should be performed across document sets. The author property definition id specifies that results should be restricted to those that have a value of TechOne.

The templates Id field works in a similar manner. To determine which id to use, a getChildren() call can be made to the QuickAddProfiles folder.

Once executed, the query will return any matching document sets. Note that the result set is pageable and may contain zero to one hundred matches per response.

To retrieve documents that have been created within a date range the following query would be used:

SELECT *
FROM T1FolderType
WHERE cmis:creationDate between '2016-09-12 08:30:00' and '2016-09-12 15:45:00'

To retrieve customers with a description containing a search term you can use:

SELECT *
FROM T1FolderType
WHERE cmis:author = 'John' AND cmis:description like '%John%'

In order to retrieve document sets contained with a list the following query may be used:

SELECT *
FROM T1FolderType
WHERE cmis:documentSetid Is one of 578453,578452

String fields can be used in a similar manner however special characters and punctuation should be avoided:

SELECT *
FROM t1indexentrytype
WHERE cmis:usertext1 is one of 'Funny,fruit,test test'

More complex queries can be performed using a combination of brackets and boolean operators:

SELECT *
FROM T1FolderType
WHERE cmis:author = 'John' OR (cmis:author contains 'Smith' AND cmis:creationDate > '2016-09-12 08:30:00')

An example of how to retrieve all users where the description starts with Chris:

SELECT *
FROM t1activeusertype
WHERE cmis:description LIKE 'CHRIS%'

Request Samples

In order to retrieve all level one subjects that have a description of ‘Aged Services’ the following request would be used.

Method: GET
Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/

<?xml version='1.0' encoding='UTF-8'?>
<cmis:query xmlns:cmis="http://docs.oasis-open.org/ns/cmis/core/200908/">
        <cmis:statement>SELECT * FROM t1indexentrytype WHERE cmis:indexnumber = 40 AND cmis:levelNumber = 1 AND cmis:userText1 = 'Aged Services'</cmis:statement>
        <cmis:searchAllVersions>false</cmis:searchAllVersions>
        <cmis:includeAllowableActions>false</cmis:includeAllowableActions>
        <cmis:includeRelationships>none</cmis:includeRelationships>
        <cmis:renditionFilter>cmis:none</cmis:renditionFilter>
        <cmis:maxItems>100</cmis:maxItems>
        <cmis:skipCount>0</cmis:skipCount>
</cmis:query>

Requests can also be generated using the ‘q’ url parameter.

Method: GET
Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/?q=SELECT * FROM t1indexentrytype WHERE cmis:indexnumber = 40 AND cmis:levelNumber = 1 AND cmis:userText1 = 'Aged Services'

Querying for Activer Users and Groups:

To retrieve an active group requires an additional parameter. Either filter=searchallversions or searchallversions=true can be used.

Method: GET
Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/?q=select * from t1activeusertype WHERE cmis:name = 'Additional Test Group'&filter=searchallversions

Method: GET
Url: {base url}/{environment id}/Api/CMIS/{repository id}/query/?q=select * from t1activeusertype WHERE cmis:name = 'Additional Test Group'&searchallversions=true

DotCMIS Samples

// Index Entries
var queryResults = session.Query("SELECT * FROM t1indexentrytype WHERE cmis:userText1 = 'test name' AND cmis:indexNumber = 40", true);

// Document sets
var queryResults = session.Query("SELECT * FROM t1foldertype WHERE cmis:documentSetId = 123", true);

// Field details
var queryResults = session.Query("SELECT * FROM t1fielddetailtype WHERE cmis:fieldName = 'Description' AND cmis:indexNumber = 183", true);

// Active Users only
var queryResults = session.Query("SELECT * FROM t1activeusertype WHERE cmis:name = 'John Smith'", false);

// Active Users and Groups
var queryResults = session.Query("SELECT * FROM t1activeusertype WHERE cmis:name = 'John Smith'", true);