• Type: Task
    • Status: Closed
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 4.2 Enterprise
    • Fix Version/s: 4.2 Enterprise
    • Security Level: external (External user)
    • Labels:


      Transactional Metadata Query


      To support the execution of a sub-set of CMIS QL and AFTS queries direct against the database.
      Using the database gives transactional consistency as opposed to the eventual consistency provided by SOLR.

      Normally, a query will be executed against the database if possible.
      DB execution of a query depends on the query itself and the application of an optional patch to the database
      which creates the required supporting indexes. If the supporting indexes have been created, each index
      sub-system can be configured to:

      • require transactional execution of queries;
      • to execute queries transactionaly when possible and fall back to eventual consistency; or
      • always to give eventual consistency.

      The SearchParameters and QueryOptions objects can be used to over-ride this behaviour per query.

      Transactional metadata query is supported for all search subsystems (solr, lucene, noindex)

      When transactional queries are enabled a query will parsed to identify if all of its parts are supported by the DB based query engine. If so, the db will used automatically i.e. no query hint is required to use it.

      The following provides details on what queries are supported by DB vs Solr/Lucene.

      CMIS QL

      Supported types:
      1) cmis:document (e.g. select * from cmis:document)
      2) cmis:folder (e.g. select * from cmis:folder)
      3) Alfresco aspects (e.g. select * from cm:dublincore)
      and their sub-types. No other types support DB queries e.g. cm:person, cmis:relationship.

      Note: Join to Alfresco aspects is supported.

      The following cmis property datatypes and comparisons are supported in the WHERE and ORDER BY clauses

      1) string - all properties and comparisons =, <>, <, <=, >=, >, <>, IN, NOT IN, LIKE

      • ordering for single-valued properties
        e.g. select * from cmis:document where cmis:name <> 'fred' order by cmis:name

      2) integer - all properties and comparisons =, <>, <, <=, >=, >, <>, IN, NOT IN

      • ordering for single-valued properties

      3) id (cmis:objectId, cmis:baseTypeId, cmis:objectTypeId, cmis:parentId) =, <>, IN, NOT IN

      • Ordering using a property that is a cmis indentifier is not supported

      4) datetime - all properties and comparisons =, <>, <, <=, >=, >, <>, IN, NOT IN

      • ordering for single-valued properties
        e.g. select * from cmis:document where cmis:lastModificationDate = '2010-04-01T12:15:00.000Z' order by cmis:creationDate ASC

      The CMIS Decimal, Boolean and URI datatypes are not supported.

      Multi-valued properties are supported and will be treated as if ANY of the property values matches the predicate.

      Multi-valued predicates as defined in the CMIS spec are supported.
      e.g. SELECT * FROM ext:doc WHERE 'test' = ANY ext:multiValuedStringProperty

      Supported Predicates
      The following predicates are supported:

      1) Comparison predicate (=, <>, <, <=, >=, >, <>)
      2) IN predicate
      3) LIKE predicate.
      Note: prefixed expressions will perform better and should be used where possible. Use with caution as they could be expensive.
      4) NULL predicate
      Note: the semantics of is null may change to include "property absent"
      5) Quantified comparison predicate (= ANY)
      6) Quantified IN predicate (ANY .... IN (....) )
      7) IN_FOLDER predicate function

      Unsupported Predicates
      The following predicates are NOT supported:
      1) TEXT search predicate (CONTAINS(), SCORE())
      2) IN_TREE() predicate

      Supported Logical operators
      The following logical operators are supported:
      1) AND
      2) NOT

      Unsupported Logical Operators
      Logical operators
      1) OR
      Note: use IN to test for one of a multiple-values for a property/column

      ORDER BY
      Is supported with restrictions on property types as described above and in the notes.


      In the following cases the query will go to the DB but the result may not be as expected.
      (In all other unsupported cases the DB query will fail and fall back to be executed against the sub-system i.e. SOLR/lucene)

      1) IS NOT NULL

      • works as expected
        2) IS NULL
      • as we currently do an inner join this does not include properties that do not exist.
      • this means that it will only find properties that are explicitly NULL as opposed to the property not existing.
        3) Sort
      • multi-valued and mltext properties will sort according to one of the values
      • is not localized and relies on the DB collation
      • sort currently uses an inner join which will also filter null values from the result set
        4) d:mltext
      • ignores locale
      • the localised values behave as a multi-valued string if there is more then one locale
      • << where cm:title = 'fred' >> match if any localised value is fred
      • ordering on mltext will be undefined as it is effectively multi-valued
        5) LIKE
      • We may constrain the form of expression supported in LIKE expressions
      • e.g. always send expressions starting with % to SOLR
        6) UPPRER()& LOWER()
      • There is additional support for SQL UPPER() and LOWER() on comparison predicates


          Issue Links




                • Assignee:
                  closedissues Closed Issues
                  ahind Andrew Hind [X] (Inactive)
                • Votes:
                  0 Vote for this issue
                  4 Start watching this issue


                  • Created:
                    Date of First Response:

                    Structure Helper Panel