topic Re: NXQL query for locked documents in Nuxeo Forum

NXQL query for locked documents

Terry_A — Thu, 28 Oct 2021 18:01:49 GMT

Looking for a way to query for locked documents of a particular type.

I've tried variations on this:

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND ecm:lockOwner IS NOT NULL

including

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND (ecm:lockOwner IS NOT NULL or ecm:lockOwner <> ' ')

To test the query syntax I am working in a pre-production environment where I know that some but not all of the documents of the desired types are locked. Regardless of how I word the query, I either get back all of the documents or none of the documents, not the desired subset.

Any suggestions or help is appreciated.

Re: NXQL query for locked documents

Gregory_Carlin — Tue, 02 Nov 2021 17:26:33 GMT

Hello,

Try with ecm:isCheckedIn = 0 to find locked documents 🙂

More on https://doc.nuxeo.com/nxdoc/nxql/#special-nxql-properties

Regards

Re: NXQL query for locked documents

Phil_Ludlow — Wed, 03 Nov 2021 16:31:43 GMT

I tried your query and the first option worked correctly for me:

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND ecm:lockOwner IS NOT NULL

That query returned the 1 document I had locked of the specified doctype in the NQXL.

If I use the option for isCheckedIn then it returns all the documents of the specified doctype, not sure why but it doesn't seem to acknowledge that a locked file is checkedOut.

SELECT * FROM Document WHERE ecm:primaryType = 'DocumentType' AND ecm:isCheckedIn = 0

I tested this in my local environment which is LTS 2021 (hotfix 10). If your first query is not working, then maybe it has to do with the database you are using?

Re: NXQL query for locked documents

Terry_A — Wed, 03 Nov 2021 16:35:42 GMT

TBH I'm not sure if it was a database issue or something else, but whatever the case, some of these attributes such as ecm

Re: NXQL query for locked documents

opage_ — Wed, 02 Nov 2022 14:34:40 GMT

Hi ! I've got exactly the same problem. What solution did you use ?

The second question will be : how to unlock ALL locked documents (yes ... I have to do this because another software has locked many of them ...)

Re: NXQL query for locked documents

opage_ — Thu, 03 Nov 2022 12:41:29 GMT

It doesn't work: It gives me locked and unlocked documents. For example, It returns me this one which is locked

root@gedgfct1# curl http://localhost:8080/nuxeo/api/v1/id/1d9d53df-ba30-443b-9216-6a83854a2c2b -u xxxx:xxxxxx -H 'fetch-document: lock'

{"entity-type":"document","repository":"default","uid":"1d9d53df-ba30-443b-9216-6a83854a2c2b","path":"/default-domain/workspaces/Cocktail/flux/numero:FLX-2022-001516/Bon de commande/Deployments.csv","type":"BonDeCommande","state":"project","parentRef":"0f365e91-ff21-435c-be32-d2ae39b0620d","isCheckedOut":true,"isRecord":false,"retainUntil":null,"hasLegalHold":false,"isUnderRetentionOrLegalHold":false,"isVersion":false,"isProxy":false,"changeToken":"3-0","isTrashed":false,"title":"Deployments.csv","lockOwner":"criecm","lockCreated":"2022-09-19T10:51:09.246Z","lastModified":"2022-09-19T10:51:09.220Z","facets":["Versionable","NXTag","Publishable","Commentable","HasRelatedText","Thumbnail"],"schemas":[{"name":"file_schema","prefix":"file_schema"},{"name":"uid","prefix":"uid"},{"name":"thumbnail","prefix":"thumb"},{"name":"file","prefix":"file"},{"name":"common","prefix":"common"},{"name":"bondecommande","prefix":"bondecommande"},{"name":"files","prefix":"files"},{"name":"dublincore","prefix":"dc"},{"name":"relatedtext","prefix":"relatedtext"},{"name":"facetedTag","prefix":"nxtag"}]}**#**

but also this one which is unlocked

root@gedgfct1# curl http://localhost:8080/nuxeo/api/v1/id/899fac1c-4d23-4971-8885-1dc298ac41e3 -u xxxxx:xxxxx -H 'fetch-document: lock'


  

{"entity-type":"document","repository":"default","uid":"899fac1c-4d23-4971-8885-1dc298ac41e3","path":"/default-domain/workspaces/Cocktail/flux/numero:FLX-2022-000647/Bon de commande/DEP_COMMANDE_LIGNE.png","type":"BonDeCommande","state":"project","parentRef":"74a4feb4-ccf2-4fcb-8533-9dfd95f76d8a","isCheckedOut":true,"isRecord":false,"retainUntil":null,"hasLegalHold":false,"isUnderRetentionOrLegalHold":false,"isVersion":false,"isProxy":false,"changeToken":"3-0","isTrashed":false,"title":"DEP_COMMANDE_LIGNE.png","lastModified":"2022-06-03T08:00:10.747Z","facets":["Versionable","NXTag","Publishable","Commentable","HasRelatedText","Thumbnail"],"schemas":[{"name":"file_schema","prefix":"file_schema"},{"name":"uid","prefix":"uid"},{"name":"thumbnail","prefix":"thumb"},{"name":"file","prefix":"file"},{"name":"common","prefix":"common"},{"name":"bondecommande","prefix":"bondecommande"},{"name":"files","prefix":"files"},{"name":"dublincore","prefix":"dc"},{"name":"relatedtext","prefix":"relatedtext"},{"name":"facetedTag","prefix":"nxtag"}]}**#**

As Terry said "some of these attributes such as ecm:lockOwner and ecm:isCheckedIn were not updating to the database properly for locked documents"

I've opened a case with Nuxeo support

Re: NXQL query for locked documents

opage_ — Fri, 04 Nov 2022 08:00:26 GMT

I've found part of the problem: As the "redis" template was in use, the locks are managed by RedisLockManager, hence stored in redis.

The solution is to delete all the keys whose prefix is "nuxeo:lock:"