1.2.4.1.5. CMIS search

CMIS provides a type-based query service for discovering objects that match specified criteria by defining a read-only projection of the CMIS data model into a Relational View.

CMIS query languages are based on a subset of the SQL-92 grammar. CMIS-specific language extensions to SQL-92 are called out explicitly. The basic structure of a CMIS query is a SQL statement that MUST include the following clauses:

SELECT (virtual columns): This clause identifies the set of virtual columns that will be included in the query results for each row.
FROM (Virtual Table Names): This clause identifies which Virtual Table(s) the query will run against.

Additionally, a CMIS query MAY include the following clauses:

WHERE (conditions): This clause identifies the constraints that rows MUST satisfy to be considered a result for the query.
ORDER BY (sort specification): This clause identifies the order in which the result rows MUST be sorted in the result row set.

Each CMIS ObjectType definition has the following query attributes:

Name	Description
query name (String)	Is used for query operations on object types. In the SQL statement examples, all object types are a queryName. For example, the given queryName matches the specific type of document. For example, in query like "`SELECT FROM cmis:document`", "`cmis:document`*" is queryName pre-configured in Document object type definition.
queryable (Boolean)	Indicates whether or not this object type is queryable. A non-queryable object type is not visible through the relational view that is used for query, and can not appear in the FROM clause of a query statement.
fulltextIndexed (Boolean)	Indicates whether objects of this type are full-text indexed for querying via the `CONTAINS()` query predicate.
includedInSupertypeQuery (Boolean)	Indicates whether this type and its subtypes appear in a query of this type's ancestor types. For example, if Invoice is a sub-type of Document, and its value is TRUE for a query on Document type, the matched instances of Invoice will be returned. If this attribute is FALSE, no instances (including matched ones) of Invoice will be returned.

Property definition also contains queryName and queryable attributes with the same usage.

Query examples

This section gives query examples for each specific case, including:

Simple query

Query: Select all cmis:document.

SELECT * FROM cmis:document

Query result:

All documents from the Apollo program.

Find document by several constraints

Query: Select all documents where apollo:propertyBooster is 'Saturn V' and apollo:propertyCommander is Frank F. Borman, II or James A. Lovell, Jr.

Initial data:

document1: apollo:propertyBooster - Saturn 1B, apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyBooster - Saturn V, apollo:propertyCommander - Frank F. Borman, II
document3: apollo:propertyBooster - Saturn V, apollo:propertyCommander - James A. Lovell, Jr.

SELECT * FROM cmis:document WHERE apollo:propertyBooster = 'Saturn V' AND (apollo:propertyCommander = 'Frank F. Borman, II' OR  apollo:propertyCommander = 'James A. Lovell, Jr.')

Query result:

document2 and document3.

Full-text search

Query: Select all documents that contains the "here" word.

Initial data:

document1: content - "There must be test word"
document2: content - "Test word is not here"

SELECT * FROM cmis:document WHERE CONTAINS('here')

Query result:

document2.

Extended full-text search

Query: Select all documents that contains "There must" phrase and do not contain the "check-word" term.

Initial data:

document1: content - "There must be test word."
document2: content - "Test word is not here. Another check-word."
document3: content - "There must be check-word."

SELECT * FROM cmis:document WHERE CONTAINS("There must" - "check-word")

Query result:

document1.

Date property comparison

Query: Select all documents where cmis:lastModificationDate is more than 2007-01-01.

Initial data:

document1: cmis:lastModificationDate - 2006-08-08
document2: cmis:lastModificationDate - 2009-08-08

SELECT * FROM cmis:document WHERE (cmis:lastModificationDate >= TIMESTAMP '2007-01-01T00:00:00.000Z')"

Query result:

document2.

Boolean property comparison

Query: Select all documents where the apollo:someProperty property equals to false.

Initial data:

document1: apollo:someProperty - true
document2: apollo:someProperty - false

SELECT * FROM cmis:document WHERE (apollo:someProperty = FALSE)

Query result:

document2.

IN Constraint

Query: Select all documents where apollo:propertyCommander is in set {'Virgil I. Grissom', 'Frank F. Borman, II', 'James A. Lovell, Jr.'}.

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander - Frank F. Borman, II
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander - Eugene A. Cernan

SELECT * FROM cmis:document WHERE apollo:propertyCommander IN ('Virgil I. Grissom', 'Frank F. Borman, II', 'James A. Lovell, Jr.')

Query result:

document2, document3.

Select all documents where the longprop property is not in set

Query: Select all documents where the apollo:propertyCommander property is not in set {'Walter M. Schirra', 'James A. Lovell, Jr.'}.

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander - Frank F. Borman, II
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander - Eugene A. Cerna

SELECT * FROM cmis:document WHERE apollo:PropertyCommander NOT IN ('Walter M. Schirra', 'James A. Lovell, Jr.')

Query result:

document2, document4.

Select all documents where the longprop property is in set

Query: Select all documents where the apollo:propertyCommander property is in set {'James A. Lovell, Jr.'}.

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander - Frank F. Borman, II
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander - Eugene A. Cerna

SELECT * FROM cmis:document WHERE  NOT (apollo:propertyCommander NOT IN ('James A. Lovell, Jr.'))

Query result:

document3.

IN_FOLDER constraint

Query: Select all folders that are in folder1.

Initial data:

folder1: id - 123456789
- document1: Title - node1
folder3:
- folder4:
folder2:
- document2: Title - node2

SELECT * FROM cmis:folder WHERE IN_FOLDER('123456789')

Query result:

folder3.

Select all documents that are in a specified folder

Query: Select all documents that are in folder1.

Initial data:

folder1: id - 123456789
- document1: Title - node1
folder2:
- document2: Title - node2

SELECT * FROM cmis:document WHERE IN_FOLDER('123456789')

Query result:

document1.

Select all documents where query supertype is cmis:article

Initial data:

testRoot: id - 123456789
document1: Title - node1 typeID - cmis:article-sports
document2: Title - node2 typeID - cmis:article-animals

SELECT * FROM cmis:article WHERE IN_FOLDER('123456789')

Query result:

document1, document2.

IN_TREE constraint

Query: Select all documents that are in the tree of folder1.

Initial data:

folder1: id - 123456789
- document1
folder2:
- document2

SELECT * FROM cmis:document WHERE IN_TREE('123456789')

Query result:

document1, document2.

LIKE Comparison

Query: Select all documents where apollo:propertyCommander begins with "James".

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander - Frank F. James, II
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander - Eugene A. James

SELECT * FROM cmis:document AS doc WHERE apollo:PropertyCommander LIKE 'James%'

Query result:

document3.

LIKE constraint with escape symbols

Query: Select all documents where apollo:someProperty like 'ad%min%'.

Initial data:

document1: Title - node1, apollo:someProperty - ad%min master
document2: Title - node2, apollo:someProperty - admin operator
document3: Title - node2, apollo:someProperty - radmin

SELECT * FROM cmis:document AS doc WHERE apollo:someProperty  LIKE 'ad%min%'

Query result:

document1.

NOT constraint

Query: Select all documents that do not contain the "world" word.

Initial data:

document1: Title - node1, content - hello world
document2: Title - node2, content - hello

SELECT * FROM cmis:document WHERE NOT CONTAINS('world')

Query result:

document2.

Property existence

Query: Select all documents that has the apollo:propertyCommander property is NOT NULL.

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander -
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander -

SELECT * FROM cmis:document WHERE apollo:propertyCommander is NOT NULL

Query result:

document1, document3.

ORDER BY

Query: Select all documents in default order (by document name).

Initial data:

document1: Title - Apollo 7
document2: Title - Apollo 8
document3: Title - Apollo 13
document4: Title - Apollo 17

SELECT cmis:lastModifiedBy, cmis:objectId, cmis:lastModificationDate FROM cmis:document

Query result:

document3, document4, document1, document2.

ORDER BY ASC

Query: Order by the apollo:propertyCommander property value (in ascending order).

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander - Frank F. Borman, II
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander - Eugene A. Cerna

SELECT cmis:lastModifiedBy, cmis:objectId, cmis:lastModificationDate FROM cmis:document ORDER BY apollo:propertyCommander

Query result:

document4, document2, document3, document1.

ORDER BY DESC

Query: Order by the apollo:propertyCommander property value (in descending order).

Initial data:

document1: apollo:propertyCommander - Walter M. Schirra
document2: apollo:propertyCommander - Frank F. James, II
document3: apollo:propertyCommander - James A. Lovell, Jr.
document4: apollo:propertyCommander - Eugene A. James

SELECT cmis:lastModifiedBy, cmis:objectId, cmis:lastModificationDate FROM cmis:document ORDER BY cmis:propertyCommander DESC

Query result:

document1, document3, document2, document4.

ORDER BY SCORE (as columns)

Query: Select all documents which contains word "moon" ordered by score.

Initial data:

document1: content - "Earth-orbital mission, the first manned launch"
document2: content - "from another celestial body - Earth's Moon"
document3: content - "NASA intended to land on the Moon, but a mid-mission technical"
document4: content - "It was the first night launch of a U.S. human"

SELECT cmis:lastModifiedBy, cmis:objectId, cmis:lastModificationDate FROM cmis:document WHERE CONTAINS('moon') ORDER BY SCORE()

Query result:

document2, document3.

Not equal comparison (decimal)

Query: Select all documents which have the apollo:propertyBooster property that does not equal to 3.

Initial data:

document1: Title - node1, apollo:propertyBooster - 3
document2: Title - node2, apollo:propertyBooster - 15

SELECT * FROM cmis:document WHERE apollo:propertyBooster <> 3

Query result:

document2.

Not equal comparison (string)

Query: Select all documents with the apollo:someProperty property that does not equal to "test word second".

Initial data:

document1: apollo:someProperty - "test word first"
document2: apollo:someProperty - "test word second"

SELECT * FROM cmis:document WHERE apollo:someProperty <> 'test word second'

Query result:

document1.

More than comparison (>)

Query: Select all documents with the apollo:propertyBooster property which is more than 5.

Initial data:

document1: apollo:propertyBooster - 3
document2: apollo:propertyBooster - 15

SELECT * FROM cmis:document WHERE apollo:propertyBooster > 5

Query result:

document2.

The CMIS specification prescribes:

Domain model
Services