2021:CMISQL Query
CMIS Queries are utilized to search documents in CMIS Repositories and to filter documents upon import when using the Import Query Results provider.
Also called a "CMISQL query", the querying language CMIS Queries use is based on a subset of the SQL-92 grammar. Where SQL is a querying language to search and select data in a database, "CMISQL" is a querying language to search and select documents (and their metadata properties) in a storage location, represented by a CMIS Repository in Grooper.
About
CMIS Queries use clauses to retrieve content from a CMIS Repositories based on certain metadata property values, much like a SQL query uses clauses to retrieve data from a database based on column values. In both cases, a SELECT clause is used to select content based on certain conditions determined by a WHERE clause
In general the CMISQL statement takes the following form:
SELECT * FROM <Content Type>
WHERE <Conditions>
ORDER BY <Sort Criteria>
For example, if you wanted to retrieve all email messages in your Inbox folder over an Exchange CMIS Connection from a particular sender and order results by the date you received the email (oldest first), you could use the following query:
SELECT * FROM Message
WHERE (Sender LIKE '%user@example.com' AND IN_FOLDER('/Inbox'))
ORDER BY DateTimeReceived ASC
Next, let's break down this query further and look at what each clause is doing.
Clause Information
SELECT
SELECT * FROM Message
WHERE (Sender LIKE '%user@example.com' AND IN_FOLDER('/Inbox'))
ORDER BY DateTimeReceived ASC
The SELECT clause defines a set of property values to be returned with each query result. "Property values" refers to the available metadata properties for the object type selected for the From statement. For example, when querying email messages using in an Exchange CMIS Repository, there are metadata properties for the sender, the subject line, the date the email was sent, and more. All these metadata properties are part and parcel to the email message file itself.
From a practical standpoint, your CMIS Query is ultimately returning documents. Files in a digital storage location. You aren't retrieving individual metadata values, rather the whole file with all its various metadata included. To that end you will use the * character to select all property values, when crafting a CMISQL query.
You will always start your CMIS Query with SELECT * when using a CMIS Query to import documents.
FROM
SELECT * FROM Message
WHERE (Sender LIKE '%user@example.com' AND IN_FOLDER('/Inbox'))
ORDER BY DateTimeReceived ASC
The FROM clause determines what type of content you're searching for, such as a document or a folder. What type of object you select determines what metadata properties you have access to when building the conditional logic in your query's WHERE clause.
Depending on the CMIS Connection type, you will have different options to select from. Some do have simple "Document" and "Folder" object types, representing documents and folders respectively. Some have metadata properties specific to the CMIS Binding.
- For example, the Exchange binding's Message content type has property values relating to email messages, such as Subject, Sender and DateTimeSent.
- For example, The SharePoint binding's content types can be Document Libraries, giving you access to custom metadata properties you've created on the SharePoint site.
In our example, we wanted to query email messages in an Exchange repository. So, we selected the Message type by using FROM Message in our query.
WHERE
SELECT * FROM Message
WHERE (Sender LIKE '%user@example.com' AND IN_FOLDER('/Inbox'))
ORDER BY DateTimeReceived ASC
The WHERE clause defines what search conditions must be met to be included in your set of returns. Each condition is defined by a "predicate". You can use any metadata available to the content type (as determined by the FROM clause) in your query conditions. Multiple conditions can be joined with the AND or OR or NOT operators. You can change the order of operations by using nested parenthesis.
The following is a list of predicates:
| Predicate | Description | Example |
| Comparison Predicate |
Specifies a condition for an individual property using comparisons, such as "equals to"
|
invoice_date<'12/31/2007'
|
| In Predicate | Specifies a list of allowed values for a property. This list is separated by commas. | FileExtension IN ('.pdf', '.docx', '.xlsx')
|
| Contains Predicate | Specifies a full-text query.
|
CONTAINS('mortgage AND payment AND NOT vehicle')
|
| Scope Predicate | Restricts the search scope to a specific folder (IN_FOLDER) or a specific folder including its subfolders(IN_TREE) |
IN_FOLDER(/Inbox)
|
- Note: Not every property type may be able to utilize every predicate/operators within a predicate. For example, the Subject property on the Exchange binding cannot use the
=operator when crafting a Comparison Predicate. - Note: The
NOToperator cannot be used with theIN_FOLDERorIN_TREEScope Predicate.
In our example, we used a combination of the Comparison Predicate and Scope Predicate to only retrieve email messages from certain sender (Sender LIKE '%user@example.com') and in our inbox folder ((IN_FOLDER('/Inbox/')) Since we wanted to filter content by multiple criteria, the two conditions were joined with the AND operator.
ORDER BY
SELECT * FROM Message
WHERE (Sender LIKE '%user@example.com' AND IN_FOLDER('/Inbox'))
ORDER BY DateTimeReceived ASC
The ORDER BY clause is an optional clause which allows you to specify the order in which results are returned. You can sort by multiple properties using a comma separated list. Each property name may be followed by ASC or DESC to indicate ascending or descending sort direction.
- The default sort direction is ascending. However, it is still considered best practice to include the
ASCtag to properties you wish to sort in ascending order.
In our example, we wanted to sort our incoming messages by oldest received first.
CMIS Query Editor
If you are not familiar with the CMISQL syntax (or even SQL), you may find it easier to use the CMIS Query Editor, instead of writing the full query yourself. This allows you to configure the CMISQL query using a series of property grids and menu selections instead of writing out the query from scratch.
The CMIS Query Editor is accessible in two locations:
- When configuring the Import Query Results Import Provider's CMIS Query property.
- Using a CMIS Repository's "Search Repository" tab.
How to Access the CMIS Query Editor
Import Query Results > CMIS Query
Most commonly, you will use a CMISQL query to import documents using the Import Query Results provider.
| FYI | The Import Query Results provider is one of two Import Providers that use a CMIS Connection to bring document content into Grooper. If you need more information on Import Query Results, please visit the CMIS Import article. |
|
|
|
|
|
|
The Search Repository Tab
You can also use the CMIS Query Editor when searching for documents in a CMIS Repository using the "Search Repository" tab.
|
|
|
We will demonstrate how to use this query editor in the next section