CMIS-Based Query Language

→ "Try it out - API Component "YADB" ("POST search documents by search query" endpoint)"
→ check GitHub Python calls | Java Script calls | Java calls | Postman collection

The query language based on Content Management Interoperability Services (CMIS) serves to standardize requests using yuuvis^® Ultimate. It consists of a subset of the SQL-92 grammar, augmented by a syntax for requesting fulltext. Thus, the CMIS query language provides a relational view of the data. A query is structured as follows

Query structure SELECT [virtual column list] FROM [virtual table names] WHERE [conditions] ORDER BY [sorting specification]

The virtual columns requested in the SELECT correspond to the CMIS properties (metadata fields).

• Use * as virtual column list to query all metadata fields
• It is possible to use the SCORE() function in the virtual columns list

The virtual tables requested in the FROM clause correspond to object types of the document management system (DMS)
Note that currently only the query of a single object type is supported.

Simple SELECT Queries ↑

Queries on Object Types ↑

SELECT * FROM email;

SELECT custno,date FROM invoice;

Queries on Secondary Object Types (SOT) ↑

SELECT * FROM system:rmRetention;

Queries with Aliases ↑

Properties can be queried with an alias name. The values of these properties are returned in the result with the alias name. The alias can be specified by a space separated behind the property name or with the key word AS between property name and alias name.

SELECT custno c,date d FROM invoice;

SELECT custno AS k,date AS d FROM invoice;

Queries with Conditions ↑

Operators and Predicates ↑

In the WHERE clause, conditions can be formulated and combined with logical operators. The following operators are supported:

• comparison operators: <, >, <=, >=, <>, =
• join operators: AND, OR, NOT
• additional predicates: LIKE, IN, NULL

SELECT * FROM invoice WHERE amount>100;

SELECT * FROM invoice WHERE amount>100 AND lieferant='ACME';

SELECT * FROM invoice WHERE (amount>100 OR amount<10) AND lieferant IN ('ACME','OS','ICH');

Wildcards ↑

Wildcard characters are used with the LIKE operator. There are two wildcards used in conjunction with the LIKE operator:

• % - The percent sign represents zero, one, or multiple characters
• _ - The underscore represents a single character

SELECT * FROM invoice WHERE lieferant LIKE 'ACM%' AND amount IS NOT NULL;

Full-Text Search ↑

At the moment, there is a shortlist of stop words that will NOT be indexed.

Some Examples

Full list of Stop Words

To perform full-text search, use CONTAINS in the WHERE clause.

SELECT * FROM invoice WHERE CONTAINS('reminder discount delay');

SELECT * FROM invoice WHERE amount>1000 AND CONTAINS('reminder discount delay');

The SCORE function returns the score values produced by the CONTAINS function in the SEARCH_SCORE field. The score value is a relative ranking, with values in the range of 0 to 1 and increases with the relevance. The SCORE function can also be queried with an alias name.

SELECT *,SCORE() FROM invoice WHERE amount>1000 AND CONTAINS('reminder discount delay');

SELECT *,SCORE() score FROM invoice WHERE amount>1000 AND CONTAINS('reminder discount delay');

Full-text search only in certain fields

SELECT *,SCORE() FROM invoice WHERE status CONTAINS('reminder discount delay');

Sorting with ORDER BY ↑

To sort the result list, use the ORDER BY clause.

SELECT * FROM invoice WHERE amount>1000 ORDER BY amount DESC;

SELECT * FROM invoice WHERE amount>1000 ORDER BY amount DESC, date ASC;

In ORDER BY clauses, you can use the alias names given in the column definitions.

SELECT amount a FROM invoice WHERE amount>1000 ORDER BY a DESC;

To sort by the score value, you must assign an alias name to the SCORE () function.

SELECT *,SCORE() s FROM invoice WHERE status CONTAINS('reminder discount delay') ORDER BY s ASC;

Aggregations (COUNT(*), GROUP BY) ↑

To determine the number of objects with special properties, queries can be aggregated. All objects with the same entry in the field specified after the GROUP BY clause are summarized. The number of objects is returned to the field 'OBJECT_COUNT' without the assignment of aliases.

SELECT COUNT(*),type FROM system:object GROUP BY type;

It is also possible to aggregate several fields. In this case, all combinations of the fields mentioned are returned.

SELECT COUNT(*),type,title FROM system:object GROUP BY type,title;

The GROUP BY function can be used without the COUNT(*) function. Then all combinations of the mentioned fields are returned without their numbers.

SELECT type FROM system:object GROUP BY type;

To sort the result list of an aggregation, use the ORDER BY clause, with or without alias.

SELECT COUNT(*),type FROM system:object GROUP BY type ORDER BY type ASC;

SELECT COUNT(*),type t FROM system:object GROUP BY type ORDER BY t DESC;

SELECT COUNT(*) c,type FROM system:object GROUP BY type ORDER BY c DESC;

Provided date functions in WHERE clause ↑

The query language provides some frequently used time and time span functions for WHERE clauses.

• now() - a date field contains the current exact time stamp (down to the milisecond)
• today() - a date field contains today's date
• yesterday() - a date field contains yesterday's date
• thisweek() - a date field contains this week's date, a week starts on moday and ends on sunday
• lastweek() - a date field contains last week's date, a week starts on moday and ends on sunday
• thismonth() - a date field contains this month's date
• lastmonth() - a date field contains last month's date
• thisyear() - a date field contains this year's date
• lastyear() - a date field contains last year's date
• dateadd(interval,number,date) - this function offsets an input date value by a specified number value (as a signed integer) of a given interval type, and then returns that modified value.
• date is a valid ISO8601 date value or a function which delivers a valid ISO8601 date value
• number can be a positiv or negative integer value
• valid interval values are:
• year, yyyy, yy = Year
• quarter, qq, q = Quarter
• month, mm, m = month
• dayofyear = Day of the year
• day, dy, y = Day
• week, ww, wk = Week
• weekday, dw, w = Weekday
• hour, hh = hour
• minute, mi, n = Minute
• second, ss, s = Second
• millisecond, ms = Millisecond

SELECT ... FROM ... WHERE system:creationDate IN today();

SELECT contentStreamId, fileName, repositoryId FROM system:object WHERE system:creationDate IN lastmonth();
SELECT * FROM document WHERE system:creationDate in dateadd(day,3,now());
SELECT * FROM document WHERE system:lastModificationDate IN today() AND system:creationDate IN lastyear() ORDER BY system:creationDate DESC;
SELECT COUNT(*) c, system:creationDate FROM system:object WHERE system:creationDate IN thisyear() GROUP BY system:creationDate ORDER BY c DESC;

Queries on contentstream fields ↑

Additional to property fields of a DMS object, all contentstream fields can be queried in SELECT statements as described above. The following queriable contentstream fields exist:

• contentStreamId- the ID of the contentstream
• repositoryId- the ID of the repository the content is stored in
• mimeType - the mime type of the content
• fileName - the file name of the content
• length - the length in bytes of the file
• range - for compound documents the byte range which represents the object content in the file
• digest - the sha256 hash of the content
• archivePath - the file path within an archive

SELECT contentStreamId, fileName, repositoryId FROM system:object;
SELECT * FROM document WHERE mimeType='message/rfc822' ORDER BY title DESC;
SELECT COUNT(*) c, fileName FROM system:object GROUP BY fileName ORDER BY c DESC;

Query - JSON format ↑

A search can be called by HTTP POST Endpunkt /api/dms-core/objects/search by giving the following JSON-structure:

{
  "query" : {
    "statement" : "SELECT * from sysemail WHERE email = @emailAddress AND sysfrom IN @from",
    "skipCount" : 0,         										// optional for Paging
    "maxItems" : 50,         										// optional for Paging
	"useCache" : true,       										// optional
	"handleDeletedDocuments" : "DELETED_DOCUMENTS_EXCLUDE",			// optional DELETED_DOCUMENTS_INCLUDE | DELETED_DOCUMENTS_ONLY | DELETED_DOCUMENTS_EXCLUDE default: DELETED_DOCUMENTS_EXCLUDE 
 	"parameters": {													// optional, only if @ Parameter occurs in statement 
		"emailAddress": "info@optimal-systems.de",
		"from": "('hallo@huhu.de','info@huhu.de')"
	}
 }
}

The field statement contains the SQL query, skipCount (default: 0) defines the number of skipped objects, from with the maxItems (default: 50) number of found objects will be delivered. This defines the paging of the search call. The field useCache (default: true) defines if caching will be used. Within a default search, all objects marked as deleted will be excluded (DELETED_DOCUMENTS_EXCLUDE). By using the optional field handleDeletedDocuments this behaviour can be changed in including this objects (DELETED_DOCUMENTS_INCLUDE) or delivering only deleted objects (DELETED_DOCUMENTS_ONLY).

Usage of parameters ↑

Within a SQL statement two predefined parameters can be referenced. By @userId the ID of the actual user (taken from the user session) and by @userRoles the roles of the actual user (also taken from the user session) can be referenced for replacement at runtime.

{
  "query" : {
    "statement" : "SELECT * from system:object WHERE system:createdBy = @userId AND system:roles IN @userRoles"
 }
}

The JSON field parameters can be used to define parameter (reserved names: userRoles, userId) values which can be referenced by @<parameter> within a preformed sql statement for replacement at runtime. The reserved parameter names userRoles, userId cannot be used for own definition of a parameter. A given definition under this names affects an exception.

{
  "query" : {
    "statement" : "SELECT * from system:object WHERE email = @emailAddress AND email:from IN @from",
 	"parameters": {													// optional, only if @ Parameter occurs in statement 
		"emailAddress": "info@optimal-systems.de",
		"from": "('hallo@huhu.de','info@huhu.de')"
	}
 }
}