CMISQL Query

Defines a CMISQL query which selects documents or folders from a CMIS Repository.

Remarks

CMISQL is a query language based on a subset of the SQL-92 grammar, with some extensions specific to CMIS. A CMISQL statement takes the following general form:

SELECT <Properties> FROM <Type> [WHERE <Conditions>] [ORDER BY <Sort>]

A CMISQL query specifies the type of item being searched for, the criteria for matching items, the order in which results should be returned, and what properties should be returned with each result. All properties and content types used in a CMIS Query must be referenced using their "query name".

SELECT Clause

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.

FROM Clause

The FROM clause indicates the type of object to search for, and should specify the name of a queryable content type 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.

The content type specified in the FROM clause serves 2 primary purposes. Firstly, it defines what properties are available in the SELECT, WHERE, and ORDER BY clauses. Secondly, it limits the scope of the search to objects of the specified type. (This includes objects with a type inheriting from the specified type.)

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. Each condition may be one of the following predicates:

Predicate Type	Description	Example
Comparison Predicate	Specifies a condition for an individual property.	invoice_date < '12/31/2007'
IN Predicate	Specifies a list of allowed values for a property.	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 children or descendants of a folder.	IN_FOLDER(/Inbox) IN_TREE(/Inbox)

The NOT operator may be used to invert the logic of individual predicates or a group of predicates enclosed by (). Note that some CMIS Bindings have limited support for the NOT operator.

ORDER BY Clause

The optional ORDER BY clause specifies the order in which results should be returned. 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. See ORDER BY Element for more details.

CMISQL Query Examples

SELECT * FROM INVOICES WHERE (invoice_amount>5000) AND (freight<100) ORDER BY invoice_amount DESC

SELECT po_number, invoice_no, invoice_date, freight, sales_tax, invoice_amount FROM INVOICES WHERE (invoice_no='123456789')

SELECT * FROM File WHERE FileExtension IN ('.pdf', '.docx') AND cmis:creationDate > '1/1/2018' AND CONTAINS('grooper AND cmis')

SELECT * FROM Message WHERE Subject LIKE '%grooper%' AND DateTimeSent > '1/1/2018' AND CONTAINS('training')

Properties

No properties are defined