Hyland Community
Help
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

CMIS Query Language

resplin
Elite Collaborator
Elite Collaborator
‎06-05-2015 07:40 PM

Obsolete Pages{{Obsolete}}

The official documentation is at: http://docs.alfresco.com

Back to CMIS.

Introduction

The CMIS standard defines a query language based on the SQL-92 SELECT statement. To support this, a read-ony
relational view of the CMIS data model is also defined.

This page is based on cmis-spec-v1.0.

It is written as a reference, drawing together the relevant information from different parts of the CMIS specification. There are simple examples for most types of query.

There are two parts to understanding the CMIS query language:

  • the relational model that defines the available tables, their columns, which tables are queryable, and which columns can be used in WHERE and ORDER BY clauses of the query; and
  • the query language, and if you already have experience of SQL-92, how it differs from SQL-92.

The CMIS query language is generally available in 3.3. Earlier releases (3.1 and 3.2) contain earlier versions with partial compliance to early versions of the CMIS specification. For v1.0 of the CMIS specification, use Alfresco 3.3 or higher.

Relational view

For all object types, the CMIS data model defines the attributes queryName, queryable, and includedInSuperType. The queryName of a queryable object type is used to identify the object type in the FROM clause of query statement. Non-queryable object types are excluded from the relational view and can not appear in the FROM clause of a query statement. The attribute includedInSuperType determines if an object sub-type is included in a query for any of its super-types. So it may be that all sub-types are not included in the query for a type. If an object type is not includedInSuperType, a direct query for the type is still supported if it is defined as queryable. For example, Alfresco internally models renditions as a sub-type of cmis:document. Renditions are marked as not includedInSuperType and so will not appear in queries for cmis:document.

SELECT * FROM cmis:document
- selects all properties for all documents but does not include thumbnails (cm:thumbnail). 

SELECT * FROM cm:thumbnail
- includes cm:thumbnail and any sub-types that are includedInSuperType

All object-type property definitions have the attributes queryName, queryable, and orderable. The property queryName is used to identify the related column in the relational table view for the object type. If a property is queryable then it may be identified using its queryName in the WHERE clause of the query statement. Only single valued properties may be orderable. Orderable properties can appear in the ORDER BY clause of the query statement. Any property may be specified in the select list for the query. However, not all properties are mandatory for the base CMIS types.

The Document, Folder, Relationship and Policy object types do not have a common super-type although there are properties that apply to them all. It is not possible to query for one of these common properties, such as cmis:name, across all types; only types descended from one of the four base types. 

The relational view of the CMIS data model is made up of a collection of virtual tables, each named after the queryName, for each queryable object-type definition. Each virtual table has virtual columns, named after the queryName, for each property defined or inherited by the object-type. If an object-type does not inherit a property defined on one of its super-types, the property appears in the virtual table as a column filled with NULLs. Unset properties are treated as null. Multi-valued properties are represented by a column that contains a single list value for each row. The virtual table for an object type includes a row for each object of that type or descended from that type and for which includedInSuperTypeQuery is true.

Content can only be queried via the CONTAINS() predicate function.

cmis:document

M
Indicates there is a mapping within Alfresco for the property and it can be selected.
Most properties are not required according to the specification.

U
Indicates the property is 'unmapped' in Alfresco.
Unmapped properties will be set to null when selected.

Q
Indicates the property is queryable in Alfresco.

O
Indicates the property is orderable in Alfresco.

According to the specification, the virtual table cmis:document has the following columns:

  • cmis:name (MQO)
  • cmisSmiley SurprisedbjectId (MQO)
  • cmis:baseTypeId (MQ)
  • cmisSmiley SurprisedbjectTypeId (MQO)
  • cmis:createdBy (MQO)
  • cmis:creationDate (MQO)
  • cmis:lastModifiedBy (MQO)
  • cmis:lastModificationDate (MQO)
  • cmis:changeToken (U)
  • cmis:isImmutable (M)
  • cmis:isLatestVersion (M)
  • cmis:isMajorVersion (M)
  • cmis:isLatestMajorVersion (M)
  • cmis:versionLabel (M)
  • cmis:versionSeriesId (M)
  • cmis:isVersionSeriesCheckedOut (M)
  • cmis:versionSeriesCheckedOutBy (M)
  • cmis:versionSeriesCheckedOutId (M)
  • cmis:checkinComment (M)
  • cmis:contentStreamLength (MQO)
  • cmis:contentStreamMimeType (MQO)
  • cmis:contentStreamFileName (MQO)
  • cmis:contentStreamId (M)

cmis:folder

  • cmis:name (MQO)
  • cmisSmiley SurprisedbjectId (MQO)
  • cmis:baseTypeId (MQ)
  • cmisSmiley SurprisedbjectTypeId (MQO)
  • cmis:createdBy (MQO)
  • cmis:creationDate (MQO)
  • cmis:lastModifiedBy (MQO)
  • cmis:lastModificationDate (MQO)
  • cmis:changeToken (U)
  • cmisSmiley TonguearentId (MQO)
  • cmisSmiley Tongueath (M)
  • cmis:allowedChildObjectTypeIds (U)

cmis:relationship

Alfresco does not support queries for this type.

cmisSmiley Tongueolicy

Alfresco does not support queries for this type.

Query structure

As with SQL-92, the CMIS Query is made up of:

  • A mandatory SELECT clause
  • A mandatory FROM clause
  • An optional WHERE clause
  • An optional ORDER BY clause

The CMIS query language does not support GROUP BY or HAVING.

SELECT clause

The SELECT clause identifies which virtual columns to return in the result set. It can be either a comma-separated list of one or more queryNames of properties that are defined by queryable object types or * for all virtual columns. The Alfresco relational view includes all single valued and multi-valued properties for *. All columns for an aliased table can be referred to as ALIAS.*.

The function SCORE() may also appear in the select list.

FROM clause

The FROM clause determines which virtual table(s) to use to fullfill the query and implies which virtual columns are available in the SELECT, WHERE and ORDER BY clauses. If more than one table is specified table aliases must be used to avoid any ambiguity. Only the queryName of queryable object-types may appear in the FROM clause.

The Alfresco implementation currently does not support JOIN except as an extension to support aspects. This extension is Alfresco specific.

Simple select examples

To select all properties for all documents:

SELECT * FROM cmis:document

To select all properties for all folders using a tables alias:

SELECT F.* FROM cmis:folder F

To select specific properties for all documents:

SELECT cmis:name, cmis:objectId FROM cmis:document

To select specific properties for all folders using a table alias and assigning aliases for the slected columns:

SELECT F.cmis:name AS name, F.cmis:objectId AS id FROM cmis:folder AS F

WHERE clause

Only rows that satisfy all the constraints specfied in the WHERE clause are returned from the query.

The WHERE clause is made up of a slightly restricted SQL-92 <search condition>. Compared with SQL-92, the CMIS Query <boolean test> maps to SQL-92 <boolean primary>. So OR, AND, NOT and parenthesised expressions work as you would expect. The SQL-92 <boolean test> is not supported. In Alfresco, predicates evaluate to TRUE or FALSE: UNKNOWN is not supported.

CMIS Query supports the following SQL-92 predicates:

  • comparison predicate
  • in predicate
  • like predicate
  • null predicate
  • quantified comparison predicate

It does not support the SQL-92:

  • between predicate
  • exists predicate
  • unique predicate
  • match predicate
  • overlaps predicate

It extends SQL-92 to support additional predicates:

  • quantified in predicate
  • text search predicate
  • folder predicate

Each predicate form is discussed in detail in the following sections.

comparison predicate

All SQL comparisons (equals =, not equals <signed numeric literal> (but implemeted the Java way ...)

decimal integers
0
100
-123
+123

exact decimal floating point literals
10.
10.0
-10.0
+10.0
.3

approximate floating point literals
1E-2
1.E+2
-1E-2
+1.0E+2
.1E2

Note: Alfresco currently accepts 'e' in place of 'E' which does not conform to SQL-92.

Note: String literal may also be used.

datetime literals

Based on SQL-92 <timestamp literal> with 'T' replacing <space> in <timestamp string>, <time value> extended to include milli-seconds, and <time zone interval>  modified to include 'Z'.

TIMESTAMP '2010-04-01T12:15:00.000Z'  
TIMESTAMP '2010-04-01T12:15:00.000+01:00' 
TIMESTAMP '2010-04-01T12:15:00.000-02:00'

In Java SimpleDateFormat terms:
yyyy-MM-dd'T'HH:mm:ss.SSS<time zone> 
with the <time zone> as Z, +hh:mm, or -hh:mm which does not quite match up 🙂
Also, you can use:
org.alfresco.util.CachingDateFormat.getCmisSqlDatetimeFormat()

boolean literals

  • TRUE
  • true
  • FALSE
  • false

Note: String literals may also be used. Alfresco also currently accepts mixed case literals.

Upgrading to 3.2 or 3.3 from previous versions of Alfresco and CMIS query

The cm:name Alfresco property is mapped to cmis:name.
We introduced  dual tokenization for cm:name to support CMIS query exact matching and ordering.
This is an index change compared with previous versions of Alfresco.
If you upgrade from previous versions of Alfresco you will have to fully rebuild your indexes to get the correct behaviour for
ordering and quantified predicates using cmis:name.

Clean 3.3 install will behave correctly.

If the index field to support correct ordering is not present it will order by token which can throw odd errors.
(e.g. 'Search failed due to: java.lang.RuntimeException: there are more terms than documents in field '@{http://www.alfresco.org/model/content/1.0}name'). If you add new stuff, then the new stuff will be ordered and the old stuff will be at one end or the other unordered.

Alfresco does not have a patch to rebuild the index for this support; it is a customer decision if they require ordering then they rebuild.

For the same reason, comparison predicates may fail if the index has not been rebuilt.

Configuring DateTime resolution

To get full DateTime resolution for quantified predicates and ordering the analyzer for date time in Alfresco needs to changed from date to date time. This will also require an index rebuild if the change is not made at installation time.

Note: this was raised as a Jira in https://issues.alfresco.com/jira/browse/ALF-5378

In
classes/alfresco/model/dataTypeAnalyzers.properties

change:

d_dictionary.datatype.d_datetime.analyzer=org.alfresco.repo.search.impl.lucene.analysis.DateAnalyser

into:

 d_dictionary.datatype.d_datetime.analyzer=org.alfresco.repo.search.impl.lucene.analysis.DateTimeAnalyser

CMIS
3.3
Search​

1 Comment
Copyright © 2024 Khoros, LLC