[MGNLDAM-273] Adapt JCR indexing for DAM mgnl:assets Created: 12/Jun/13  Updated: 07/Oct/13  Resolved: 25/Jun/13

Status: Closed
Project: Magnolia DAM Module
Component/s: None
Affects Version/s: 1.0
Fix Version/s: 1.0.1

Type: Improvement Priority: Neutral
Reporter: Philip Mundt Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
dependency
is depended upon by MGNLSTK-1186 Fix/adapt component stkDownloadList Closed
supersession
is superseded by MGNLDAM-267 Prevent adjustments of JCR index for ... Closed
Template:
Acceptance criteria:
Empty
Task DoD:
[ ]* Doc/release notes changes? Comment present?
[ ]* Downstream builds green?
[ ]* Solution information and context easily available?
[ ]* Tests
[ ]* FixVersion filled and not yet released
[ ]  Architecture Decision Record (ADR)
Date of First Response:
Sprint: RC 1

 Description   

To be able to do flat jcr queries it is necessary to adjust the jcr search index of DAM and mgnl:assets. Most file information are store in the jcr:content subnode of an asset and thus, are not easily queried.

A SQL2 workaround form Greg is

SELECT *
FROM [nt:base]
WHERE extension='jpg'
AND ISDESCENDANTNODE([/demo-project/])

But one has to set the result NodeType to mgnl:asset to only get the assets from the query.

Deprecated XPath query would be

/jcr:root/demo-project[1]//element(*,nt:base)[jcr:content/@extension='jpg']

SQL2 join queries are not supported atm.



 Comments   
Comment by Jan Haderka [ 14/Jun/13 ]

We could indeed configure index to flatten the structure and have child props indexed on the parent already. As for the query itself I think it is wrong. In first case you should search over jcr:content rather then nt:base to get reasonable performance, in second case (xpath) it should be over mgnl:asset.

Once index is flattened both of the queries should be done searching over mgnl:asset.

Comment by Jan Haderka [ 14/Jun/13 ]

for examples on how to change the configuration look at http://wiki.magnolia-cms.com/display/DEV/Indexing+configuration+-+optimizations+for+Magnolia+5

the part you are interested in is the last one explaining how to get metadata subnode props indexed on parent ... you want to do the same for jcr:content node props on mgnl:asset

Comment by Magnolia International [ 17/Jun/13 ]

I'm not too keen on index customization - it's not very portable, and its misconfiguration probably not easy to detect.
Besides, there are a lot of places where we could do that if we go down that route. I'd rather improve our queries and/or query api. Was just working with Philip a minute ago to see if using a join would be doable. (JR claims to have "fixed" the performance issues since JCR-2835)

Comment by Philip Mundt [ 17/Jun/13 ]

So yes, it is doable, the performance still has to be checked though. A sample statement would look like:

SELECT asset.*
FROM [mgnl:asset] AS asset
INNER JOIN [mgnl:resource] AS content ON ISCHILDNODE(content, asset)
WHERE content.extension='jpg'
AND content.size>200000
AND asset.[mgnl:lastModified] > '2012-01-01T12:00:00.000+02:00'
AND ISDESCENDANTNODE(asset, '/demo-project')

However, as this is a join and the query result will contain more than one selector, some code has to be adjusted.

Comment by Magnolia International [ 17/Jun/13 ]

Code change is limited to something along these lines:

 
-            NodeIterator nodeIterator = query.execute().getNodes();
-            while (nodeIterator.hasNext()) {
+            final RowIterator rows = query.execute().getRows();
+            while (rows.hasNext()) {
+                    Row row = rows.nextRow();
                 // Check the result type
-                Node assetNode = nodeIterator.nextNode();
+                Node assetNode = row.getNode("asset");
Comment by Philip Mundt [ 25/Jun/13 ]

Solved by MGNLDAM-267, no more index updates required as we're using SQL2 JOIN statement to access subnode properties.

Generated at Mon Feb 12 04:58:12 CET 2024 using Jira 9.4.2#940002-sha1:46d1a51de284217efdcb32434eab47a99af2938b.