• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
TSB Alfresco Cobrand White tagline

Technology Services Group

  • Home
  • Products
    • Alfresco Enterprise Viewer
    • OpenContent Search
    • OpenContent Case
    • OpenContent Forms
    • OpenMigrate
    • OpenContent Web Services
    • OpenCapture
    • OpenOverlay
  • Solutions
    • Alfresco Content Accelerator for Claims Management
      • Claims Demo Series
    • Alfresco Content Accelerator for Policy & Procedure Management
      • Compliance Demo Series
    • OpenContent Accounts Payable
    • OpenContent Contract Management
    • OpenContent Batch Records
    • OpenContent Government
    • OpenContent Corporate Forms
    • OpenContent Construction Management
    • OpenContent Digital Archive
    • OpenContent Human Resources
    • OpenContent Patient Records
  • Platforms
    • Alfresco Consulting
      • Alfresco Case Study – Canadian Museum of Human Rights
      • Alfresco Case Study – New York Philharmonic
      • Alfresco Case Study – New York Property Insurance Underwriting Association
      • Alfresco Case Study – American Society for Clinical Pathology
      • Alfresco Case Study – American Association of Insurance Services
      • Alfresco Case Study – United Cerebral Palsy
    • HBase
    • DynamoDB
    • OpenText & Documentum Consulting
      • Upgrades – A Well Documented Approach
      • Life Science Solutions
        • Life Sciences Project Sampling
    • Veeva Consulting
    • Ephesoft
    • Workshare
  • Case Studies
    • White Papers
    • 11 Billion Document Migration
    • Learning Zone
    • Digital Asset Collection – Canadian Museum of Human Rights
    • Digital Archive and Retrieval – ASCP
    • Digital Archives – New York Philharmonic
    • Insurance Claim Processing – New York Property Insurance
    • Policy Forms Management with Machine Learning – AAIS
    • Liferay and Alfresco Portal – United Cerebral Palsy of Greater Chicago
  • About
    • Contact Us
  • Blog

Alfresco’s Transactional Metadata Query System – Mystery Results

You are here: Home / Alfresco / Alfresco’s Transactional Metadata Query System – Mystery Results

November 8, 2016

With the release of version 5.0, Alfresco officially stopped supporting the Lucene indexing subsystem, leaving Solr as Alfresco’s lone indexing solution. A major benefit of leveraging Lucene was its in-transaction indexing, making content searchable as soon as it was created. Solr, however, utilizes asynchronous indexing which leads to “eventual consistency”, meaning newly created content is not searchable right away. This poses problems when building solutions for clients that need a high-velocity system capable of ingesting and then quickly presenting newly created content from Alfresco to end users. Luckily, Alfresco has a solution: Enter Transactional Metadata Query.

What is a Transactional Metadata Query?

With the release of version 4.2, Alfresco began supporting a system called Transactional Metadata Query. This system allows particular CMIS and FTS language queries to be run directly against database indexes instead of the Solr index. Having CMIS and FTS queries run directly against the database allows content to be retrieved as soon as it is committed, as opposed to searching against the “eventually consistent” Solr indexes. By default, the Transactional Metadata Query system takes a CMIS or FTS query and parses the query to determine if each part is supported by the database query engine (Please see What Queries are Supported with Transactional Metadata Query? below). If the entire query is supported, it is run directly against the database. If the entire query is not supported by the database query engine, it is run against Solr instead.

What Queries are Supported with Transactional Metadata Query?

Earlier this year one of our clients was experiencing confusing results when performing FTS queries in HPI. In their situation, querying for a document with a particular status and category returned the proper results. However, when the same status and category were searched for within a particular time period, documents with the incorrect status or incorrect category would appear in the results. What made the issue so difficult to understand was that the queries themselves and the metadata in Alfresco all appeared to be correct, but the values being returned by the queries using a specific date range were not matching what was in Alfresco. How could one query return the correct documents with the correct data, but simply adding date parameter to our search cause such odd data to return? The issue was two-fold: there was an issue with our Solr index, and FTS queries using date searches are not supported by the Transactional Metadata Query system.

Alfresco lists a number of supported and unsupported CMIS and FTS  queries on their Alfresco Docs site, but these do not appear to be a comprehensive lists. Listed below are currently known supported and unsupported predicates and logical operators. (For more information on what is and isn’t supported, visit Alfresco’s community pages: http://docs.alfresco.com/4.2/concepts/intrans-metadata-query.html, http://docs.alfresco.com/5.0/concepts/intrans-metadata-query.html

logical-operator-support table

predicate-support table

Other operators

In the following cases, the query will go to the database but the result might not be as expected. In all other unsupported cases, the database query will fail and fall back to be executed against the Solr 4 subsystem.

  • IS NOT NULL
  • IS NULL: Currently, this operator will only find properties that are explicitly NULL as opposed to the property not existing.
  • SORT: The multi-valued and mltext properties will sort according to one of the values. Ordering is not localized and relies on the database collation. It uses an INNER JOIN, which will also filter NULL values from the result set.
  • d:mltext: This data type ignores locale. However, if there is more than one locale, the localized values behave as a multi-valued string. Ordering on mltext will be undefined as it is effectively multi-valued.
  • UPPER() and LOWER(): Comparison predicates provide additional support for SQL UPPER() and LOWER() functions (that were dropped from a draft version of Alfresco CMIS specification but are supported for backward compatibility).

Other Situations

  • Performing DATE searches will automatically go against Solr
  • Queries against Boolean properties will automatically go against Solr

As previously stated, Alfresco’s list of supported and unsupported queries does not appear to be comprehensive. If you want to know definitively if your query is running against the database or Solr, increase the logging level to DEBUG on the DbOrIndexSwitchingQueryLanguage class. This class will print a log stating whether your query was supported to run against the database or if it ran against Solr as a fall back.

log4j.logger.org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage=debug

We suggest running all queries using the CMIS and FTS languages in order to take advantage of Transactional Metadata Query’s ability to retrieve information immediately from the database instead of waiting for “Eventually Consistent”
Solr indexes. To avoid the possibility of having unexpected or inconsistent search results, please refer the the information listed above (as well as Alfresco’s own documentation) in order to craft a Transactional Metadata Query.

Filed Under: Alfresco, Tech Tip

Reader Interactions

Trackbacks

  1. Alfresco – Adding the ability to query on very large data sets says:
    June 13, 2018 at 2:24 pm

    […] database compliant query (check out our previous posts on utilizing the Alfresco database and Alfresco TMQ Queries for more information) that runs against the database does not perform well and can time out, […]

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Search

Related Posts

  • Alfresco Performance – Making Property Queries Faster
  • Alfresco and Angular 2.0: Technical First Look
  • Avoiding Alfresco Performance Problems from Day 1 – Keeping My ECM Healthy
  • Upgrading to Alfresco 5 – Keys to Success
  • Harnessing the Power of Alfresco Data Lists for Cascading Value Assistance
  • Auto-Numbering Content in Alfresco
  • Auto-Filing Content in Alfresco
  • Alfresco Post-Installation Configuration
  • Content Deletion in Alfresco – More than Meets the Eye
  • Alfresco Data List-Driven Value Assistance

Recent Posts

  • Alfresco Content Accelerator and Alfresco Enterprise Viewer – Improving User Collaboration Efficiency
  • Alfresco Content Accelerator – Document Notification Distribution Lists
  • Alfresco Webinar – Productivity Anywhere: How modern claim and policy document processing can help the new work-from-home normal succeed
  • Alfresco – Viewing Annotations on Versions
  • Alfresco Content Accelerator – Collaboration Enhancements
stacks-of-paper

11 BILLION DOCUMENT
BENCHMARK
OVERVIEW

Learn how TSG was able to leverage DynamoDB, S3, ElasticSearch & AWS to successfully migrate 11 Billion documents.

Download White Paper

Footer

Search

Contact

22 West Washington St
5th Floor
Chicago, IL 60602

inquiry@tsgrp.com

312.372.7777

Copyright © 2023 · Technology Services Group, Inc. · Log in

This website uses cookies to improve your experience. Please accept this site's cookies, but you can opt-out if you wish. Privacy Policy ACCEPT | Cookie settings
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT