Hello all,
David, how do I get an oasis Jira login?
Here are the examples I sent in and I have an 3rd one as well below regarding adding SQL functions:
Request for CMIS Query Capability Addition
CMIS query language is based on a subset of the SQL-92 grammar. Section
2.1.1.1 Optional Capabilities
of CMIS 1.1 specification defines some optional capabilities and one of the categories under this is about Query Capabilities. CMIS 1.1 defines following query capabilities.
|
Capability
|
Description
|
|
capabilityQuery
|
Indicates the types of queries that the Repository has the ability to fulfill. Query support levels are:
none
No queries of any kind can be fulfilled.
metadataonly
Only queries that filter based on object properties can be fulfilled. Specifically, the CONTAINS() predicate function is not supported.
fulltextonly
Only queries that filter based on the full-text content of documents can be fulfilled. Specifically, only the CONTAINS() predicate function can be included in the WHERE clause.
bothseparate
The repository can fulfill queries that filter EITHER on the full-text content of documents OR on their properties, but NOT if both types of filters are included in the same query.
bothcombined
The repository can fulfill queries that filter on both the full-text content of documents and their properties in the same query.
|
|
capabilityJoin
|
Indicates the types of JOIN keywords that the Repository can fulfill in queries. Support levels are:
none
The repository cannot fulfill any queries that include any JOIN clauses on two primary types. If the Repository supports secondary types, JOINs on secondary types SHOULD be supported, even if the
support level is none.
inneronly
The repository can fulfill queries that include an INNER JOIN clause, but cannot fulfill queries that include other types of JOIN clauses.
innerandouter
The repository can fulfill queries that include any type of JOIN clause defined by the CMIS query grammar.
|
It would be great if the CMIS specification would also define the following SQL functions possibly as an optional query capability (For examples as
capabilityFunctions).
|
Function
|
Result
|
Description
|
|
AVG
|
Average of the values in the column.
|
Same generic data type as the argument; implementation-defined attributes.
|
|
COUNT
|
Total number of values in (cardinality of) the column.*
|
Exact numeric, scale 0, implementation defined precision and range.
|
|
MAX
|
Largest value in the column.
|
Same data type as the argument
|
|
MIN
|
Smallest value in the column.
|
Same data type as the argument.
|
|
SUM
|
Sum of the values in the column.
|
Exact numeric argument: Exact numeric result; same scale as argument; implementation-defined precision and range.
Approximate numeric argument: Approximate numeric result; implementation-defined precision and range of magnitude.
|
Example CMIS Query:
SELECT COUNT(*) from cmis:document;
Simplify Query Joins for properties/secondary types.
One of our customers has found that managing a large amount of secondary types (in this case alfresco aspects) requires a very large join query. We believe it would be preferable
to simplify the query. Andy Hind I believe had proposed simplifying the queries generally, but in this use case he has suggested adding an optional parameter of some sort like “ALL_PROPERTIES”.
An example long query:
"SELECT CUSTOM_DOC.cmis:objectId, CUSTOM_DOC.cmis:objectTypeId,F.cactus:FILE_EXTENSION, "
+ " T.cactus:DESCRIPTIVE_NAME, CUSTOM_DOC.cmis:isVersionSeriesCheckedOut, CUSTOM_DOC.cactus:DMC_NAME, "
+ " CUSTOM_DOC.cactus:SUB_TITLE, CUSTOM_DOC.cactus:TEACH_ORDER, CUSTOM_DOC.cmis:versionLabel, F.cactus:STATE, "
+ " CUSTOM_DOC.cmis:lastModifiedBy, CUSTOM_DOC.cmis:lastModificationDate, "
+ " F.cactus:REASON_FOR_CHANGE, "
+ " F.cactus:ENG_CHANGE_INFO, F.cactus:IPSS_NUMBER, F.cactus:KEYWORDS, M.cactus:MODEL, "
+ " M.cactus:MODEL_GROUP, F.cactus:DOC_TYPE, E.cactus:ATA_CHAPTER, E.cactus:ATA_SECTION,E.cactus:ATA_SUBJECT, F.cactus:EAR_ITAR, R.cm:title "
+ " FROM cactus:dataModule as CUSTOM_DOC "
+ " join cactus:DescriptiveName as T on T.cmis:objectId = CUSTOM_DOC.cmis:objectId "
+ " join cactus:chapterSectionInfo as E on CUSTOM_DOC.cmis:objectId = E.cmis:objectId "
+ " join cactus:commonPropsForAll as F on F.cmis:objectId = E.cmis:objectId "
+ " join cactus:modelGroupInfo as M on M.cmis:objectId = E.cmis:objectId "
+ " join cm:workingcopy as WC on WC.cmis:objectId = CUSTOM_DOC.cmis:objectId"
+ " join cm:titled as R on R.cmis:objectId = E.cmis:objectId"
SQL Contains() function does not treat special characters as text.
Example Query:
select * from cmis:document WHERE CONTAINS('cmis:name:*test_*')
Expected outcome: Find all documents whose name contain "test_"
Resulting outcome: Finds all documents whose name contains "test"
From: cmis@lists.oasis-open.org [mailto:cmis@lists.oasis-open.org]
On Behalf Of David Choy
Sent: Friday, June 10, 2016 8:39 AM
To: cmis@lists.oasis-open.org
Subject: [cmis] Agenda for 13 June
Agenda for 13 June:
- Query discussion
David Raboy brought up two issues regarding query:
(1) large number of secondary types,
(2) handling of special characters as text in the Contains() function.
David, would you please post your examples to JIRA, or distribute them by email to this list, so that everyone can review? Thanks.
Best regards,
David