Tech Tips & Tricks: Querying across Documents for unlinked Work Items

By Jiri Jandl

Here’s another in our series of tips and tricks from the Polarion Software Technical Support team, in which we share solutions we provided to customers for real-world use cases that. The same tips might help you too, or others on your team. Let’s look today at how to query to discover unlinked Work Items across Polarion LiveDocs.

Use Case

Two Documents within a project having linked Work Items with related to link role, like this:

Doc1
WI-1, related to WI-3
WI-2, related to WI-7

Doc2
WI-3, related to WI-1, parent of WI-4
WI-4, has parent WI-3, related to Wi-6
WI-5, no link to Doc1, parent of WI-6
WI-6, has parent WI-5, related to WI-4
WI-7, related to WI-2

You want to find all Work Items on the parent level in Doc2 that are NOT linked to a Work Item in Doc1. This means that the query must return WI-5, and NOT WI-4 or WI-6, as they are linked within Doc2 to another item in Doc2.

Solution

Once again we’re transported from the realm of Lucene to the realm of SQL, making use of the background SQL database in Polarion that mirrors the Subversion repository data, and enables speedy complex queries that Lucene either can handle, or would have to be done in such a way as to bog down the system while the query is processed. Here is some example Wiki mark-up and code that provides a solution to our use case.

#set($projectId = $page.getProject())
1 Items from Doc2 without parent requirement in Doc2
#set($my1Query = “SELECT item.C_PK FROM WORKITEM item, PROJECT proj, MODULE doc2 =>

WHERE proj.C_ID = ‘${projectId}’ AND doc2.C_ID = ‘Doc2’ AND doc2.C_MODULEFOLDER = =>

‘My_Space’ AND doc2.FK_URI_PROJECT = proj.C_URI AND item.C_TYPE = ‘requirement’ AND =>

item.FK_URI_MODULE = doc2.C_URI AND NOT EXISTS (SELECT link.* FROM =>

STRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM parent WHERE link.C_ROLE = ‘parent’ =>

AND link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = parent.C_URI =>

AND parent.C_TYPE = ‘requirement’ AND parent.FK_URI_MODULE = doc2.C_URI)”)
{workitems:sqlQuery=$my1Query}
1 Items from Doc2 not linked to Doc1
#set($my2Query = “SELECT item.C_PK FROM WORKITEM item, PROJECT proj, MODULE doc2 =>

WHERE proj.C_ID = ‘${projectId}’ AND doc2.C_ID = ‘Doc2’ AND doc2.C_MODULEFOLDER = =>

‘My_Space’ AND doc2.FK_URI_PROJECT = proj.C_URI AND item.C_TYPE = ‘requirement’ =>

AND item.FK_URI_MODULE = doc2.C_URI AND NOT EXISTS (SELECT link.* FROM =>

STRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM neighbour, MODULE doc1 WHERE =>

link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = neighbour.C_URI AND =>

neighbour.FK_URI_MODULE = doc1.C_URI AND doc1.C_ID = ‘Doc1’ AND =>

doc1.C_MODULEFOLDER = ‘My_Space’ AND doc1.FK_URI_PROJECT = proj.C_URI)”)
{workitems:sqlQuery=$my2Query}
1 The complete solution
#set($my3Query = “SELECT item.C_PK FROM WORKITEM item, PROJECT proj, MODULE doc2 =>

WHERE proj.C_ID = ‘${projectId}’ AND doc2.C_ID = ‘Doc2’ AND doc2.C_MODULEFOLDER = =>

‘My_Space’ AND doc2.FK_URI_PROJECT = proj.C_URI AND item.C_TYPE = ‘requirement’ =>

AND item.FK_URI_MODULE = doc2.C_URI AND NOT EXISTS (SELECT linked.C_PK FROM =>

STRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM linked WHERE link.C_ROLE = ‘parent’ =>

AND link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = linked.C_URI AND =>

linked.C_TYPE = ‘requirement’ AND linked.FK_URI_MODULE = doc2.C_URI UNION SELECT =>

linked.C_PK FROM STRUCT_WORKITEM_LINKEDWORKITEMS link, WORKITEM linked, MODULE doc1 =>

WHERE link.FK_URI_P_WORKITEM = item.C_URI AND link.FK_URI_WORKITEM = linked.C_URI =>

AND linked.FK_URI_MODULE = doc1.C_URI AND doc1.C_ID = ‘Doc1’ AND =>

doc1.C_MODULEFOLDER = ‘My_Space’ AND doc1.FK_URI_PROJECT = proj.C_URI)”)
{workitems:sqlQuery=$my3Query}