2023:CMISQL Query - Revision history

Dgreenwood at 15:13, 5 February 2026

2026-02-05T15:13:46Z

← Older revision		Revision as of 10:13, 5 February 2026
Line 5:		Line 5:
	The querying language used by '''CMISQL Queries''' is based on a subset of the SQL-92 grammar. Where SQL is used to search and select data from a database, '''CMISQL''' is used to search and select documents (and their metadata properties) from a storage location, represented in Grooper by a '''CMIS Repository'''.		The querying language used by '''CMISQL Queries''' is based on a subset of the SQL-92 grammar. Where SQL is used to search and select data from a database, '''CMISQL''' is used to search and select documents (and their metadata properties) from a storage location, represented in Grooper by a '''CMIS Repository'''.

	'''''Because the property used to configure a CMISQL Query is ~~always~~ named "CMIS Query", CMISQL Queries are often referred to ~~simply~~ as CMIS Queries.'''''		'''''Because the property used to configure a CMISQL Query is named "CMIS Query", "CMISQL Queries" are often referred to as "CMIS Queries".'''''

	== About ==		== About ==

Dgreenwood: /* Import Query Results > CMIS Query */

2026-02-05T15:11:42Z

Import Query Results > CMIS Query

← Older revision		Revision as of 10:11, 5 February 2026
Line 185:		Line 185:
	# Use the property grid to configure the clauses of the query.		# Use the property grid to configure the clauses of the query.
	# Free-type the query directly in the text editor.		# Free-type the query directly in the text editor.
			\| valign=top \|
			[[File:2023_CMIS-Query_02_How-To_02.png]]
	\|}		\|}

Dgreenwood: /* Where Predicates */

2026-02-05T14:54:34Z

Where Predicates

← Older revision		Revision as of 09:54, 5 February 2026
Line 107:		Line 107:
	\|-style="background-color:#ddf5f5"		\|-style="background-color:#ddf5f5"
	\|'''''AT_LEVEL Predicate'''''		\|'''''AT_LEVEL Predicate'''''
	\|Matches ~~objects~~ at a specific level below the base folder.		\|Matches documents/folders at a specific level below the base folder.
	\|<code>AT_LEVEL(2)</code>		\|<code>AT_LEVEL(2)</code>
	\|-style="background-color:#ddf5f5"		\|-style="background-color:#ddf5f5"

Dgreenwood: /* Where Predicates */

2026-02-05T14:52:48Z

Where Predicates

← Older revision		Revision as of 09:52, 5 February 2026
Line 86:		Line 86:
	\|<code>DateTimeCreated < '12/31/2007'</code>		\|<code>DateTimeCreated < '12/31/2007'</code>
	\|-style="background-color:#ddf5f5"		\|-style="background-color:#ddf5f5"
	\|'''''In Predicate'''''\|\|Specifies search criteria for an individual property, using a list of allowed values.\|\|<code>FileExtension IN ('.pdf', '.docx', '.xlsx')</code>		\|'''''IN Predicate'''''
			\|Specifies search criteria for an individual property, using a list of allowed values.
			\|<code>FileExtension IN ('.pdf', '.docx', '.xlsx')</code>
	\|-style="background-color:#ddf5f5"		\|-style="background-color:#ddf5f5"
	\|'''''~~Contains~~ Predicate'''''		\|'''''CONTIAINS Predicate'''''
	\|Performs a full-text search.		\|Performs a full-text search.
	* You can use <code>AND</code>, <code>OR</code> and <code>NOT</code> operators when crafting search parameters.		* You can use <code>AND</code>, <code>OR</code> and <code>NOT</code> operators when crafting search parameters.
Line 94:		Line 96:
	\|<code>CONTAINS('mortgage AND payment AND NOT vehicle')</code>		\|<code>CONTAINS('mortgage AND payment AND NOT vehicle')</code>
	\|-style="background-color:#ddf5f5"		\|-style="background-color:#ddf5f5"
	\|'''''Scope Predicate'''''\|\|Restricts the search scope to the direct children (<code>IN_FOLDER</code>) or all descendants (<code>IN_TREE</code>) of a specific folder.		\|'''''MATCHES Predicate'''''
			\|Defines filtering criteria for a string property, where the property value must match a regular expression.
			\|<code>cmis:name MATCHES '^[A-Z]{3}-\d{4}$'</code>
			\|-style="background-color:#ddf5f5"
			\|'''''Scope Predicate'''''
			\|
			Restricts the search scope to the direct children (<code>IN_FOLDER</code>) or all descendants (<code>IN_TREE</code>) of a specific folder.
	*<li class="attn-bullet"> Note: The <code>NOT</code> operator cannot be used with the Scope Predicate.		*<li class="attn-bullet"> Note: The <code>NOT</code> operator cannot be used with the Scope Predicate.
	\|<code>IN_FOLDER('/Documents/Grooper')</code>		\|<code>IN_FOLDER('/Documents/Grooper')</code>
			\|-style="background-color:#ddf5f5"
			\|'''''AT_LEVEL Predicate'''''
			\|Matches objects at a specific level below the base folder.
			\|<code>AT_LEVEL(2)</code>
	\|-style="background-color:#ddf5f5"		\|-style="background-color:#ddf5f5"
	\|'''''Predicate List'''''\|\|Groups multiple predicates using <code>AND</code> or <code>OR</code>. Allows multiple search conditions in a single CMISQL query.\|\|<code>(Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox/'))</code>		\|'''''Predicate List'''''\|\|Groups multiple predicates using <code>AND</code> or <code>OR</code>. Allows multiple search conditions in a single CMISQL query.\|\|<code>(Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox/'))</code>

Dgreenwood: /* How to Use the CMIS Query Editor */

2026-02-05T14:40:45Z

How to Use the CMIS Query Editor

Show changes

Dgreenwood: /* How to Access the CMIS Query Editor */

2026-02-05T14:22:40Z

How to Access the CMIS Query Editor

← Older revision		Revision as of 09:22, 5 February 2026
Line 173:		Line 173:
	# Use the property grid to configure the clauses of the query.		# Use the property grid to configure the clauses of the query.
	# Free-type the query directly in the text editor.		# Free-type the query directly in the text editor.
			\|}

	''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].		'''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].

	==== CMIS Repository > Search Tab ====		==== CMIS Repository > Search Tab ====

Dgreenwood at 14:20, 5 February 2026

2026-02-05T14:20:10Z

← Older revision		Revision as of 09:20, 5 February 2026
Line 126:		Line 126:
	The editor is available in:		The editor is available in:
	# The '''Import Query Results''' provider		# The '''Import Query Results''' provider
	# A '''CMIS Repository''' via the "Search ~~Repository~~" tab		# A '''CMIS Repository''' via the "Search" tab

	{\|class="attn-box"		{\|class="attn-box"
Line 145:		Line 145:
	There are two locations where the '''CMIS Query Editor''' can be accessed:		There are two locations where the '''CMIS Query Editor''' can be accessed:
	* From the '''Import Query Results''' provider when editing its ''CMIS Query''. This allows users to filter imported documents based on the results of a CMISQL query.		* From the '''Import Query Results''' provider when editing its ''CMIS Query''. This allows users to filter imported documents based on the results of a CMISQL query.
	* From a CMIS ~~Repository’s~~ ''~~Search Repository~~'' tab. This allows users to test CMISQL queries independently of the Import Query Results provider.		* From a '''CMIS Repository's''' "Search" tab. This allows users to test CMISQL queries independently of the Import Query Results provider.

	''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].		''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].
Line 176:		Line 176:
	''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].		''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].

	==== CMIS Repository > Search ~~Repository~~ Tab ====		==== CMIS Repository > Search Tab ====
	You can also access the '''CMIS Query Editor''' when searching for documents in a '''CMIS Repository''' using the ''Search ~~Repository''~~ tab.		You can also access the '''CMIS Query Editor''' when searching for documents in a '''CMIS Repository''' using the "Search" tab.

	{\| cellpadding=10 cellspacing=5		{\| cellpadding=10 cellspacing=5
Line 183:		Line 183:
	<br>		<br>
	# Select the '''CMIS Repository''' in the Node Tree.		# Select the '''CMIS Repository''' in the Node Tree.
	# Navigate to the ''Search ~~Repository''~~ tab.		# Navigate to the "Search" tab.
	#* '''Note:''' Some '''CMIS Bindings''' are ''not'' CMISQL queryable (for example, ''FTP'' and ''SFTP''). If the CMIS Repository is not queryable, the ''Search ~~Repository''~~ tab will not be available.		#* '''Note:''' Some '''CMIS Bindings''' are ''not'' CMISQL queryable (for example, ''FTP'' and ''SFTP''). If the CMIS Repository is not queryable, the "Search" tab will not be available.
	# Use the property grid to configure the clauses of the query.		# Use the property grid to configure the clauses of the query.
	# Free-type the query directly in the text editor.		# Free-type the query directly in the text editor.

Dgreenwood: /* How to Access the CMIS Query Editor */

2026-02-05T14:16:15Z

How to Access the CMIS Query Editor

← Older revision		Revision as of 09:16, 5 February 2026
Line 142:		Line 142:

	=== How to Access the CMIS Query Editor ===		=== How to Access the CMIS Query Editor ===
	~~<tabs style="margin:20px">~~
	~~<tab name="Option 1: Import Query Results - CMIS Query" style="margin:20px">~~

	=== ~~Option 1:~~ Import Query Results > CMIS Query ===		There are two locations where the '''CMIS Query Editor''' can be accessed:
			* From the '''Import Query Results''' provider when editing its ''CMIS Query''. This allows users to filter imported documents based on the results of a CMISQL query.
			* From a CMIS Repository’s ''Search Repository'' tab. This allows users to test CMISQL queries independently of the Import Query Results provider.

			''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].
			==== Import Query Results > CMIS Query ====
	Most commonly, you will use a CMISQL query to import documents using the '''Import Query Results''' provider.		Most commonly, you will use a CMISQL query to import documents using the '''Import Query Results''' provider.

	{\|class="fyi-box"		{\| class="fyi-box"
	\|		\|
	'''FYI'''		'''FYI'''
	\|		\|
	The '''Import Query Results''' provider is one of two Import Providers that use a '''CMIS Connection''' to bring document content into Grooper. ~~If you need~~ more information ~~on Import Query Results~~, ~~please visit~~ the [[CMIS Import]] article.		The '''Import Query Results''' provider is one of two Import Providers that use a '''CMIS Connection''' to bring document content into Grooper. For more information, see the [[CMIS Import]] article.
	\|}		\|}

	{\|cellpadding=10 cellspacing=5		{\| cellpadding=10 cellspacing=5
	\|valign=top style="width:40%"\|		\| valign=top style="width:40%" \|
	<br>		<br>
	# When configuring the import settings, ~~you will first~~ select ~~which~~ '''CMIS Repository''' ~~you want~~ to query ~~by selecting one~~ using the Repository property.		# When configuring the import settings, select the '''CMIS Repository''' to query using the ''Repository'' property.
	# ~~Then, select~~ the CMIS Query property.		# Select the ''CMIS Query'' property.
	#* Note: Some CMIS Bindings are ''not'' CMISQL queryable (~~For~~ example, ~~the~~ ''FTP'' and ''SFTP'' ~~CMIS Bindings~~). If the CMIS Repository is not queryable, ~~you will~~ ''~~not~~'' ~~see this CMIS Query~~ property.		#* '''Note:''' Some CMIS Bindings are ''not'' CMISQL queryable (for example, ''FTP'' and ''SFTP''). If the CMIS Repository is not queryable, the ''CMIS Query'' property will not be available.
	# ~~Press~~ the ellipsis button at the end of the property to ~~bring up~~ the CMIS Query Editor.		# Click the ellipsis button at the end of the property to open the CMIS Query Editor.
	\|valign=top\|		\| valign=top \|
	[[File:2023_CMIS-Query_02_How-To_01.png]]		[[File:2023_CMIS-Query_02_How-To_01.png]]
	\|-		\|-
	\|valign=top\|		\| valign=top \|
	<br>		<br>
	This is the '''CMIS Query Editor'''. Using this interface you can craft a CMISQL query in one of two ways:		This is the '''CMIS Query Editor'''. Using this interface, you can craft a CMISQL query in one of two ways:
			# Use the property grid to configure the clauses of the query.
	# ~~You can use this~~ property grid to configure the ~~various~~ clauses of the query ~~statement~~.		# Free-type the query directly in the text editor.
	# ~~You can free~~-type the query in ~~this~~ text editor.


	~~We will demonstrate how~~ to use ~~this query editor~~ in the [[#How to Use the CMIS Query Editor\|next section]]		''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].
	~~\|valign=top\|~~
	~~[[File:2023_CMIS-Query_02_How-To_02~~.~~png]]~~
	\|}
	~~</tab>~~
	~~<tab name="Option 2: The Search Repository Tab" style="margin:20px">~~

	=== ~~Option 2: The~~ Search Repository Tab ===		==== CMIS Repository > Search Repository Tab ====
	You can also ~~use~~ the '''CMIS Query Editor''' when searching for documents in a '''CMIS Repository''' using the "Search Repository" tab.		You can also access the '''CMIS Query Editor''' when searching for documents in a '''CMIS Repository''' using the ''Search Repository'' tab.

	{\|cellpadding=10 cellspacing=5		{\| cellpadding=10 cellspacing=5
	\|valign=top style="width:40%"\|		\| valign=top style="width:40%" \|
	<br>		<br>
	# Select the '''CMIS Repository''' ~~you want to query~~ in the Node Tree.		# Select the '''CMIS Repository''' in the Node Tree.
	# Navigate to the "Search Repository" tab.		# Navigate to the ''Search Repository'' tab.
	#* Note: Some '''CMIS Bindings''' are ''not'' CMISQL queryable (~~For~~ example, ~~the~~ ''FTP'' and ''SFTP'' ~~CMIS Bindings~~). If the CMIS Repository is not queryable, ~~you will~~ ''~~not~~'' ~~see this "Search Repository"~~ tab.		#* '''Note:''' Some '''CMIS Bindings''' are ''not'' CMISQL queryable (for example, ''FTP'' and ''SFTP''). If the CMIS Repository is not queryable, the ''Search Repository'' tab will not be available.
	# ~~You can use this~~ property grid to configure the ~~various~~ clauses of the query ~~statement~~.		# Use the property grid to configure the clauses of the query.
	# ~~You can free~~-type the query in ~~this~~ text editor.		# Free-type the query directly in the text editor.
	\|valign=top\|		\| valign=top \|
	[[File:2023_CMIS-Query_02_How-To_03.png]]		[[File:2023_CMIS-Query_02_How-To_03.png]]
	\|}		\|}

			''Note:''' How to use the CMIS Query Editor is demonstrated in the [[#How to Use the CMIS Query Editor\|next section]].
	~~We will demonstrate how~~ to use ~~this query editor~~ in the [[#How to Use the CMIS Query Editor\|next section]]
	~~</tab>~~
	~~</tabs>~~

	=== How to Use the CMIS Query Editor ===		=== How to Use the CMIS Query Editor ===

Dgreenwood: /* CMISQL Query Editor */

2026-02-04T23:04:18Z

CMISQL Query Editor

← Older revision		Revision as of 18:04, 4 February 2026
Line 122:		Line 122:

	== CMISQL Query Editor ==		== CMISQL Query Editor ==
	If you are ~~not familiar~~ with ~~the~~ CMISQL syntax ~~(or even SQL)~~, ~~you may find it easier to use~~ the '''CMIS Query Editor'''~~, instead of writing the full query yourself. This~~ allows you to ~~configure the CMISQL query~~ using a ~~series of~~ property ~~grids and menu selections instead of~~ writing ~~out the query from scratch~~.		If you are unfamiliar with CMISQL syntax, the '''CMIS Query Editor''' allows you to build queries using a property grid rather than writing them manually.

	The ~~'''CMIS Query Editor'''~~ is ~~accessible~~ in ~~two locations~~:		The editor is available in:
	# ~~When configuring the~~ '''Import Query Results''' ~~Import Provider's "CMIS Query" property.~~		# The '''Import Query Results''' provider
	# ~~Using a~~ '''CMIS Repository's''' "Search Repository" tab.		# A '''CMIS Repository''' via the "Search Repository" tab

	{\|class="attn-box"		{\|class="attn-box"
Line 132:		Line 132:
	⚠		⚠
	\|		\|
	~~Only certain external storage platforms are currently queryable with a '''CMIS Query'''.~~ The following ~~'''~~CMIS ~~Binding''' sources '''''~~cannot~~'''''~~ be queried ~~currently.~~		The following CMIS Bindings cannot be queried using CMISQL:

	* '''FTP'''		* '''FTP'''
	* '''SFTP'''		* '''SFTP'''
	* '''NTFS''' (~~If the folder path is not~~ indexed by ~~the~~ Windows Search ~~service and/or Windows Search is not running on the storage server~~)		* '''NTFS''' (unless indexed by Windows Search)

	~~As such, these~~ bindings are ~~'''''~~not~~''''' suitable for~~ the '''Import Query Results''' provider ~~(You should instead use '''Import Descendants''' instead). Furthermore, you will not have access to the "Search Repository" tab for these bindings when selecting a CMIS Repository for these types.~~		These bindings are not compatible with the '''Import Query Results''' provider.
	~~<br>~~
	~~Again, the exception to this is '''NTFS'''. If the folder path IS indexed in Windows and the Windows Search service IS running, then you CAN use the "Search Repository" tab and CMIS Queries~~.
	\|}		\|}


	=== How to Access the CMIS Query Editor ===		=== How to Access the CMIS Query Editor ===

Dgreenwood: /* ORDER BY */

2026-02-04T23:03:29Z

ORDER BY

← Older revision		Revision as of 18:03, 4 February 2026
Line 111:		Line 111:

	==== ORDER BY ====		==== ORDER BY ====
	''SELECT * FROM Message''		Example CMISQL Query:
	''WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox'))''		: <code>SELECT * FROM Message WHERE (Sender LIKE '%user@example.com%' AND IN_FOLDER('/Inbox')) '''ORDER BY DateTimeReceived ASC'''</code>
	'''ORDER BY DateTimeReceived ASC'''

	The optional <code>ORDER BY</code> clause defines the sort order of query results.		The optional <code>ORDER BY</code> clause defines the sort order of query results.