You are here

Transactional metadata queries supported by database

This topic provides information on the queries supported by the database.

The Alfresco Full Text Search (FTS) query text can be used standalone or it can be embedded in CMIS-SQL using the contains() predicate function. The CMIS specification supports a subset of Alfresco FTS. For more information on the Alfresco search syntax, see Alfresco Full Text Search Reference.


The following object types and their sub-types are supported:
  • cmis:document
    For example:
    select * from cmis:document
  • cmis:folder
    For example:
    select * from cmis:folder 
  • Alfresco aspects
    For example:
    select * from cm:dublincore 

CMIS property data types

The WHERE and ORDER BY clauses support the following property data types and comparisons:
  • string
    • Supports all properties and comparisons, such as =, <>, <, <=, >=, >, IN, NOT IN, LIKE
    • Supports ordering for single-valued properties
    For example:
    select * from cmis:document where cmis:name <> 'fred' order by cmis:name
  • integer
    • Supports all properties and comparisons, such as =, <>, <, <=, >=, >, IN, NOT IN
    • Supports ordering for single-valued properties
  • id
    • Supports cmis:objectId, cmis:baseTypeId, cmis:objectTypeId, cmis:parentId, =, <>, IN, NOT IN
    • Ordering using a property, which is a CMIS identifier, is not supported.
  • datetime
    • Supports all properties and comparisons =, <>, <, <=, >=, >, IN, NOT IN
    • Supports ordering for single-valued properties
    For example:
    select * from cmis:document where cmis:lastModificationDate = '2010-04-01T12:15:00.000Z' order by
     cmis:creationDate ASC
Note: While the CMIS Decimal, Boolean and URI data types are not supported, the multi-valued properties and the multi-valued predicates as defined in the CMIS specification are supported. For example,
select * from ext:doc where 'test' = ANY ext:multiValuedStringProperty

Supported predicates

A predicate specifies a condition that is true or false about a given row or group. The following predicates are supported:
  • Comparison predicates, such as =, <>, <, <=, >=, >, <>
  • IN predicate
  • LIKE predicate
    Note: Prefixed expressions perform better and should be used where possible.
  • NULL predicate 
  • Quantified comparison predicate (= ANY)
  • Quantified IN predicate (ANY .... IN (....) )
  • IN_FOLDER predicate function

Unsupported predicates

The following predicates are not supported:
  • TEXT search predicate, such as CONTAINS() and SCORE() 
  • IN_TREE() predicate

Supported logical operators

The following logical operators are supported:
  • AND 
  • NOT

Unsupported logical operators

The following logical operator is not supported:
  • OR 

Other operators

In the following cases, the query will go to the database but the result might not be as expected. In all other unsupported cases, the database query will fail and fall back to be executed against the Solr 4 subsystem.
  • IS NULL: Currently, this operator will only find properties that are explicitly NULL as opposed to the property not existing.
  • SORT: The multi-valued and mltext properties will sort according to one of the values. Ordering is not localized and relies on the database collation. It uses an INNER JOIN, which will also filter NULL values from the result set.
  • d:mltext: This data type ignores locale. However, if there is more than one locale, the localised values behave as a multi-valued string. Ordering on mltext will be undefined as it is effectively multi-valued.
  • UPPER() and LOWER(): Comparison predicates provide additional support for SQL UPPER() and LOWER() functions (that were dropped from a draft version of Alfresco CMIS specification but are supported for backward compatibility).