Obsolete Pages{{Obsolete}}
The official documentation is at: http://docs.alfresco.com
Back to CMIS.
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 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.
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.
According to the specification, the virtual table cmis:document has the following columns:
Alfresco does not support queries for this type.
Alfresco does not support queries for this type.
As with SQL-92, the CMIS Query is made up of:
The CMIS query language does not support GROUP BY or HAVING.
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.
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.
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
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:
It does not support the SQL-92:
It extends SQL-92 to support additional predicates:
Each predicate form is discussed in detail in the following sections.
All SQL comparisons (equals =, not equals <signed numeric literal> (but implemeted the Java way ...)
Note: Alfresco currently accepts 'e' in place of 'E' which does not conform to SQL-92.
Note: String literal may also be used.
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()
Note: String literals may also be used. Alfresco also currently accepts mixed case literals.
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.
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
Ask for and offer help to other Alfresco Content Services Users and members of the Alfresco team.
Related links:
By using this site, you are agreeing to allow us to collect and use cookies as outlined in Alfresco’s Cookie Statement and Terms of Use (and you have a legitimate interest in Alfresco and our products, authorizing us to contact you in such methods). If you are not ok with these terms, please do not use this website.