CMISQL Queries

jongoesboomjongoesboom Posts: 26 admin
In Grooper 2.7 we have implemented two major overhauls to our CMIS capabilities: CMIS+ Architecture and CMISQL Queries. At a high level, CMIS+ allows you to connect all of your I/O content under a single framework. What this means for you, is that you can now have dedicated connections or "bindings" to non-CMIS platforms. On platforms like file systems and email servers, this will give you greater flexibility than just importing and exporting, including full text search and improved query capabilities.

In previous versions of Grooper, querying CMIS repositories was not a unified process. Each CMIS provider had its own way of handling queries, and did not always follow CMIS standards. With CMISQL you have one standard query language that works for all your CMIS repository connections.

The CMISQL query syntax will be familiar to anyone who is familiar with SQL queries. A CMISQL statement takes the following general form:
SELECT <Properties> FROM <Type> [WHERE <Conditions>] [ORDER BY  <Sort>]

The SELECT clause specifies which properties are to be returned with query results. It should contain a comma-separated list of property names, or * to indicate that all properties should be returned. Example:
Will return all properties for the selected repository

SELECT cmis:name, cmis:createdBy
Will only return the Name and Created By property in the selected repository

If you are unsure of what properties are available to you, simply type SELECT into the query box and Intellisense will give you a list of your options

FROM Clause 

The FROM clause indicates the type of object to search for. It should specify the name a queryable content defined in the CMIS repository. If the content type is document-based, then each query result will be a CMIS Document. If the content type is folder-based, then each query result will be a CMIS Folder. Again, Intellisense is your friend here and can help if you are not sure what content types are available. The exhibit below shows the content types associated with an email account.

WHERE Clause

The optional WHERE clause specifies one or more conditions items must match to be included in the result set. Multiple conditions are joined with the AND/OR operators, and may be nested with parenthesis () to indicate order of operations. Examples below of operators used with the WHERE clause.
  • =  Equal to
  • <>  Not Equal to
  • <  Less than
  • >  Greater than
  • <=  Less than or equal to
  • >=  Greater than or equal to
  • LIKE  Contains a substring
  • IS  IS NULL or IS NOT NULL, evaluates to a boolean
  • IN  Search a list of values
  • CONTAINS  Used for full-text search criteria
  • IN_FOLDER  only matches items which are direct children of the base folder
  • IN_TREE  matches all descendants of the base folder at any level
The WHERE clause is essential if you are doing anything other than selecting the entire data set. The WHERE clause can be as basic as locating a specific file type:
WHERE FileExtension = '.jpg'
They can also get more complicated. The clause below is looking for any pdf, jpg or tif in the Datasets folder and subfolders.
WHERE IN_TREE(\\server\path\name\Datasets) AND FileExtension IN ('.pdf', '.tif', '.jpg')
A note on CONTAINS

When searching for full-text result you can search by words or phrases. If you are searching for single words you will need to put them in single quotes see the following examples

WHERE CONTAINS ('word AND word2')

If you are searching for a phrase you will need to use single quotes to surround the entire search string, and double quotes to surround the phrase. 
WHERE CONTAINS ('"test phrase"')

WHERE CONTAINS ('"test phrase" and "other phrase"')

Another optional clause that specifies the order of the results. It should include a comma-separated list of one or more property names on which the result set should be sorted. Optionally, each property name may be followed by ASC or DESC to indicate ascending or descending sort direction. If a property is specified without a sort direction, the default is ascending.

The following statement sorts the results ascending by the name property.
ORDER BY cmis:name 

The next statement sorts the results by modification date newest to oldest and then by name a to z.

ORDER BY cmis:lastModificationDate DESC, cmis:name

Putting It All Together

Let's look at some examples of full CMISQL query examples and break down what exactly is going on.

SELECT * FROM INVOICES WHERE (invoice_amount>5000) AND (freight<100) ORDER BY invoice_amount DESC
We are returning all columns where the invoice amount is greater than 5000 and the freight is less than 100. We are sorting the results by invoice amount highest to lowest.

SELECT po_number, invoice_no, invoice_date, freight, sales_tax, invoice_amount FROM INVOICES WHERE (invoice_no='123456789')\
We are returning only the PO Number, Invoice Number, Invoice Date, Freight, Sales Tax, and Invoice Amount from the Invoices table where the Invoice Number is 123456789

SELECT * FROM File WHERE FileExtension IN ('.pdf', '.docx') AND cmis:creationDate > '1/1/2018' AND CONTAINS('grooper AND cmis')
We are returning all columns in the fileshare with a pdf or docx extension, the Creation Date is after 1/1/2018, and has either the words grooper or cmis in the full-text search results.

SELECT * FROM Message WHERE Subject LIKE '%grooper%' AND DateTimeSent > '1/1/2018' AND CONTAINS('training')
We are returning all columns that have the word grooper in the subject, the date sent was after 1/1/2018, and has the word training in the body of the email.
Sign In or Register to comment.