The basic form of a CMIS SQL query is as follows:
SELECT <virtual columns> FROM <virtual tables> [WHERE <conditions>] [ORDER BY <sort specification>]
The SELECT clause must either specify a comma-separated list of virtual column names or the '*' token. All column names must be valid queryNames of properties defined for the object types listed in the FROM clause.
In addition to column names, it is also possible to use the SCORE() predicate function as a column name. The SCORE() predicate returns the relevance value of the search made in the CONTAINS predicate. The value is between 0 and 1, with 0 it means that the text was not found in the document and 1 indicates a perfect match. You cannot use the SCORE() function in a query that does not use the CONTAINS predicate.
Note
Content streams are not part of the relational view. Therefore, they cannot be included in the SELECT clauses.
As with SQL-92, aliases can be defined for column names using the AS keyword as follows:
SELECT book.bs:price AS price, book.bs:isbn AS isbn, book.bs:author AS author, book.bs:publisher AS publisher FROM bs:book AS book
The FROM clause lists the queryNames of the virtual tables that are included in the query.
Repositories that support joins provide the capability of performing inner or possibly outer joins in queries, using the standard SQL-92 syntax. Only the equality predicate can be used on join conditions and single-valued properties.
Implicit joins as parts of the WHERE clause are not allowed in CMIS SQL. Only explicit joins as part of the FROM clause can be used, as follows:
SELECT b.cmis:name FROM bs:book AS b JOIN bs:review AS r ON r.cmis:name = b.cmis:name
The WHERE clause defines the constraints that the rows, resulting from the join of all the virtual tables listed in the FROM clause, must satisfy in order to be included in the result set of a query.
All the column names used in the WHERE clause must be valid queryNames of properties, defined for the object types included in the FROM clause, or their aliases, as defined by the SELECT clause.
The comparison operators, shown in the following table, can be used depending on the types of the columns involved in the comparison. These can only be used on single-valued properties.
|
Property Type |
Operators |
|---|---|
|
|
=, <>, [NOT] LIKE, [NOT] IN |
|
|
=, <>, <, <=, >, >=, [NOT] IN |
|
|
=, <>, <, <=, >, >=, [NOT] IN |
|
|
= |
|
|
=, <>, <, <=, >, >=, [NOT] IN |
|
|
=, <>, [NOT] IN |
|
|
=, <>, [NOT] LIKE, [NOT] IN |
The ANY quantified comparison predicate can be used to filter rows where any one of the values of a multi-valued property is equal to a reference value:
SELECT * FROM bs:book AS b WHERE ('Jerome D. Salinger' = ANY b.bs:author)
The IN/ANY predicate is an extension to SQL-92. It evaluates to true when any of the values of a multi-valued property are included in a list of reference values:
SELECT * FROM bs:book WHERE ANY bs:author IN ('Jerome D. Salinger', 'Kurt Vonnegut', 'Joseph Heller')
If a repository provides full-text search, then document text can be queried for using the CONTAINS predicate. Its syntax is as follows:
CONTAINS([<qualifier>,] <text>)
The value of qualifier, if present, must be the name of one of the virtual tables listed in the FROM clause. It indicates which table the search applies to.
If the FROM clause lists more than one virtual table, then the qualifier must be present. Otherwise, it can be omitted, as the search is performed only on the single table named.
The value of text must be a string enclosed in quotes, specifying words and phrases to look for. Search terms can be made up of multiple words and must be enclosed in double quotes.
Terms can be negated by prefixing them with the '-' character. Separate terms are implicitly joined with the AND conjunction (which can be made explicit), unless they are explicitly joined by OR.
At most, one CONTAINS expression can be present in a WHERE clause, and, where it is present together with other predicates, it can only be joined through an AND clause. The return value of the CONTAINS function is a boolean: either true when the content is considered relevant with respect to the search text, or false otherwise. When the CONTAINS predicate is used, it is possible to use the SCORE function in the SELECT clause. Its value is a decimal number between 0 (minimum relevance) and 1 (maximum relevance).
The IN_FOLDER predicate function, whose syntax is
IN_FOLDER([<qualifier>,] <id>)
is used to test whether or not the candidate object is a direct child of the folder identified by id. If it is, the function returns true. The value of qualifier must be the name of one of the virtual tables listed in the FROM clause and must be present if there are more than one.
The ORDER BY clause lists one or more names of virtual columns (or their aliases), according to whose values the objects in the result set are ordered. All the columns listed in the ORDER BY clause must be listed in the SELECT clause and must identify orderable properties of object types listed in the FROM clause. The collation rules used for ordering are repository-specific.