[OASIS Issue Tracker] (CMIS-768) Simplify CMIS query

[OASIS Issues Tracker <workgroup_mailer@lists.oasis-open.org>]

    [ https://tools.oasis-open.org/issues/browse/CMIS-768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=36993#comment-36993 ] 

Andy Hind commented on CMIS-768:
--------------------------------

To define a dynamic folder:

1) We want to return all documents defined by some query:
   For example, using cmis rm related secondary types - "All documents I can see that are on hold and have expired"
{code}
   SELECT * 
      FROM cmis:document              doc 
      JOIN cmis:rm_hold               hold ON d.cmis:objectId = h.cmis:objectId
      JOIN cmis:rm_clientMgtRetention retn ON d.cmis:objectId = r.cmis:objectId
      WHERE retn.cmis:rm_expirationDate < 'TODAY' 
{code}
   
2) We want to display the documents in the same detail as we would for listing the children of a folder.

   With CMIS 1.1 we would have to fetch each document.
   It is not possible to pull back all the document properties on the query as we would need to know all the aspects and types we may find and left outer join to them 
   
{code}
   SELECT doc.*, type1.*, type2.*, ..., hold.*, retn.*, secondaryType1.*, secondaryType2.*
      FROM cmis:document                           doc 
      JOIN cmis:rm_hold                            hold ON doc.cmis:objectId = h.cmis:objectId
      JOIN cmis:rm_clientMgtRetention              retn ON doc.cmis:objectId = r.cmis:objectId
      LEFT OUTER JOIN some:type1                   type1 ON type1.cmis:objectId = doc.cmis:objectId
      ...
      LEFT OUTER JOIN some:secondaryType1          secondaryType1  on secondaryType1.cmis:objectId = doc.cmis:objectId
      ...
      WHERE retn.cmis:rm_expirationDate < 'TODAY' 
{code}


With bulk fetch, we could pull back only object ids in the query and make a second call to get all the document details.


However, it would be more covenient to use the proposed batch fetch syntax to specify the properties required and drop all the self joins.

{code}
   SELECT doc.cmis:createdDate, type1.some:prop2, type2.some:prop2, ..., hold.cmis:rm_holdIds, retn.cmis:rm_expirationDate, secondaryType1.some:property1, secondaryType2.some:property2
      FROM cmis:document                           doc 
      JOIN cmis:rm_hold                            hold ON doc.cmis:objectId = h.cmis:objectId
      JOIN cmis:rm_clientMgtRetention              retn ON doc.cmis:objectId = r.cmis:objectId
      WHERE retn.cmis:rm_expirationDate < 'TODAY'
{code}


Further  
{code}
    SELECT *
      FROM cmis:document                           doc 
      JOIN cmis:rm_hold                            hold ON doc.cmis:objectId = h.cmis:objectId
      JOIN cmis:rm_clientMgtRetention              retn ON doc.cmis:objectId = r.cmis:objectId
      WHERE retn.cmis:rm_expirationDate < 'TODAY'
{code}
could return all properties on the document without all the self joins. 
We could use some other keyword for this alternative relational view projection, 




... and we could consider a syntax like:

{code}
SELECT cmis:docuement.*, some:type1.*, some:secondaryType1.*, ... 
      FROM cmis:document                           doc 
      JOIN cmis:rm_hold                            hold ON doc.cmis:objectId = h.cmis:objectId
      JOIN cmis:rm_clientMgtRetention              retn ON doc.cmis:objectId = r.cmis:objectId
      WHERE retn.cmis:rm_expirationDate < 'TODAY'
{code}
to give better control over what is returned.
This syntax could also help with bulk fetch - "all properties for this type", "all properties for this secondard type" 



I think it is more difficult to drop the self joins required for predicates or ordering.
If we define a new keyword that implies an alternative relational view projection, where the properties of all subtypes are treated as optional properties, it is clear.
The projection would be "as if we had left outer joined onto the tables for all sub-types" for the current projection. 

> Simplify CMIS query
> -------------------
>
>                 Key: CMIS-768
>                 URL: https://tools.oasis-open.org/issues/browse/CMIS-768
>             Project: OASIS Content Management Interoperability Services (CMIS) TC
>          Issue Type: Improvement
>          Components: Domain Model
>    Affects Versions: Proposals for 2.0
>            Reporter: Andy Hind
>
> This follows on from CMIS-758 where some discussion took place related to query and over lap with bulk get.
> There are 3 main issues with CMIS query that I see:
> 1) Now that we have secondary types, joining to all the secondary types you may wish to use is cumbersome
>      You also have to left outer join to any specific sub-types for which you may want properties.
> 2) To batch fetch all properties as part of a query requires that you know before hand what secondary types are applied to build the complex query above
>      While it may be possible to get the ids and use the new batch operations it would be good to get all you want in one shot.
> 3)  To get heterogeneous objects may require several calls for folders, documents, items
>      (I am not sure if we have considered selecting a secondary type and then outer joining to the base types - I would like to make that easy too) 
> An example use case for both would be to generate a dynamic folder/view of documents and folders defined by a query.
> To support this the query would have similar requirements to listing children of a folder. 



--
This message was sent by Atlassian JIRA
(v6.1.1#6155)