topic Re: Querying the Alfresco repository (~DQL) in Alfresco Archive

Querying the Alfresco repository (~DQL)

bboeri — Wed, 25 Jan 2012 18:33:01 GMT

I understand that Alfresco has no document query language similar to Documentum's DQL. Can anyone suggest a way to generate reports with the following kinds of information (assuming requisite permissions):1) Get a complete count of all documents of a specific type2) Get a complete count of all docu

Re: Querying the Alfresco repository (~DQL)

jpotts — Sat, 28 Jan 2012 00:05:25 GMT

You might be interested in the querying capability that the CMIS Query Language provides. And it is only one letter different from DQL!

Seriously, though, it does give you a query capability closer to what you are probably used to with DQL.

Here's a Python example that uses cmislib to count all documents of a specific type, sc:whitepaper:

>>> query = 'select cmis:objectId from sc:whitepaper'
>>> rs = repo.query(query)
>>> len(rs)
3‍‍‍‍

Date ranges are similarly easy. Here's the query for a date range that also joins on a custom aspect, sc:webable, to find objects with the published date in the given range:

queryString = """select d.cmis:objectId, w.sc:published
                         from sc:whitepaper as d
                         join sc:webable as w on d.cmis:objectId = w.cmis:objectId
                         where w.sc:published > TIMESTAMP '2006-01-01T00:00:00.000-05:00'
                         and w.sc:published < TIMESTAMP '2007-06-02T00:00:00.000-05:00'"""‍‍‍‍‍

If you want to run these on your own using Java instead of Python, check out this custom content types tutorial that includes a section with several sample queries.

Your third request is indeed the trickiest. The easiest thing to do is to set a start date and an end date on the content from within the workflow. Then you can use CMIS Query Language to query for those properties. If you don't like that idea you can instead write data to a "workflow log" object (or whatever you want to call it–it is up to you to define) and then query against those objects.

Activiti may have some built-in reporting that could help you here but I haven't looked into it. This kind of workflow reporting is a requirement we hear a lot.

Hope this has gotten you a little closer to your goal.

Jeff

PS. I recognize your name. Are you the same Bob Boeri that used to edit EContent Mag?

Re: Querying the Alfresco repository (~DQL)

bboeri — Tue, 31 Jan 2012 16:32:08 GMT

Yes, Jeff, I am he (that is, I don't edit eContent mag, but I continue to write columns for them (in my 18th year, if you count the name the magazine originally had, eMedia). I also sit in on the editorial board to arm wrestle with the other editors regarding who gets nominated for the annual eContent 100 issue (Alfresco made the cut last year).

Bob

Re: Querying the Alfresco repository (~DQL)

jpotts — Tue, 31 Jan 2012 16:59:34 GMT

Cool! Nice to meet you, virtually. Did those queries help you out?

Jeff

Re: Querying the Alfresco repository (~DQL)

bboeri — Tue, 31 Jan 2012 18:51:53 GMT

The examples look helpful, but as an ECM business analyst (rather than a developer) I'll need some help from my local developer-colleagues. At least I know that the capabilities are there (and CQL will probably be most useful in a Java environment). I also need more detail about CQL itself but expect I'll find that elsewhere, perhaps @ OASIS. This looks like it is at least a partial answer: http://docs.oasis-open.org/search-ws/searchRetrieve/v1.0/csprd01/part5-cql/searchRetrieve-v1.0-csprd01-part5-cql.pdf.

Thanks again.
Bob

Re: Querying the Alfresco repository (~DQL)

alexr — Tue, 07 Feb 2012 08:41:49 GMT

I was thinking of starting to develop a small admin tool for querying Alfresco through CMIS queries.

However one of the things we often use is a query to retrieve the number of documents of a certain type where metadata has a certain value:

select count(*) from … where …

Then I ran into an old Jira entry:

https://issues.alfresco.com/jira/browse/ALF-4935?page=com.tuncaysenturk.jira.plugins.jira-enhancer-plugin:reopened-issue-tabpanel

I understand that in a programming/scripting language you can do a len(resultset) . However one would expect the CMIS query language to have implemented the count(*) as part of the syntax, which should return a number as a result of the query.

1) Is this possible through CMIS queries in general and against Alfresco specific?
2) If not will the "select count(*)" be added to the CMIS query language?

Kind regards,

Alex

Re: Querying the Alfresco repository (~DQL)

jpotts — Tue, 07 Feb 2012 14:44:28 GMT

There are a number of things you can do in SQL that you cannot do in CMIS Query Language. I do not think COUNT is part of the 1.0 spec. You could check with OASIS to see if support for COUNT is planned for 1.1. If not, you could raise an issue with the CMIS Technical Committee.

Jeff

Re: Querying the Alfresco repository (~DQL)

alexr — Tue, 07 Feb 2012 14:47:39 GMT

Thanks Jeff for the quick response. I will check the cmis 1.1 specs @Oasis.

Kind regards,

Alex

Re: Querying the Alfresco repository (~DQL)

andy — Tue, 17 Apr 2012 19:31:11 GMT

Hi

Some of your use cases will be supported when we expose SOLR faceting.

Andy

Re: Querying the Alfresco repository (~DQL)

paul_price — Thu, 24 Jul 2014 16:37:46 GMT

Mentioned in this comment ("There are a number of things you can do in SQL"), this is what I am looking for. I have a huge collection of docs (~.5 Million) that I would like to get counts by date and State_Code (part of my content model). I desperately want to use SQL rather than SOLR because I can access the production databases much easier than other machines in that environment (plus I'm a DBA, so SQL rules).

So can you (or someone else) point me to some hints/wiki for doing this type of query?

My environment: Alfresco Enterprise 4.1.8 on Linux, using SQL Server 2012 database. I will be moving to 4.2 in the near future, so the ultimate solution needs to run in 4.2.

Thanks for any help.