Way to query physically deleted Work Items still linked

Description
Tzoma
Posts: 3
Joined: Thu Oct 20, 2016 7:23 pm

Way to query physically deleted Work Items still linked

Postby Tzoma » Wed Feb 15, 2017 5:38 pm

Hello,

Does anybody know a way that one could query physically deleted Work Items ( Settings -> Delete ) that are still linked to other Work Items ?

My case :

I have 500 WIs that may still have links to other WIs that have been physically deleted. I need to remove those links but i fail to see any way to do this. Looking in WIs XMLs does not give out any info that it would be linked to a deleted Work Items.

Thanks for your help !

Jürgen
Posts: 28
Joined: Tue Sep 12, 2017 1:02 pm

Re: Way to query physically deleted Work Items still linked

Postby Jürgen » Thu Dec 21, 2017 12:24 pm

I just happened to see this question because I have the exact same problem. I have work items with links to deleted items. I want to find those links to remove them.

it would be a great idea to make search queries possible like this:
- find all work items which are linked to work items of type xyz
- find all work items which are linked to work items in status obsolete
- find all work items which are linked to work items that cannot be resolved (e.g. deleted)
- find all work items which are linked to work items of a specific project

Maybe somebody knows how to write an SQL query that solves this problem?

ALMighty
Posts: 80
Joined: Wed Apr 27, 2016 10:34 am
Location: Austria

Re: Way to query physically deleted Work Items still linked

Postby ALMighty » Wed Jan 17, 2018 2:46 pm

Tzoma wrote:Hello,

Does anybody know a way that one could query physically deleted Work Items ( Settings -> Delete ) that are still linked to other Work Items ?



You mean those kind of 'null ' work items of which the ID has an italic font in Polarion and a white suqre as icon?
If yes, I would try to find them with an SQL query. I think those deleted items have nothing left then their internal primary key and their work item id, so looking for items which have those two values but all the others null (or at least pick a few vital ones like 'created' or 'project') is my first guess.

You have two possibilities how to do that:

1.) SQL query through a wiki page, see the thread SQL query example for workitems in a plan in which I have basically described everything important for executing SQL queries in Polarion, along with the database scheme. This is a convenient way because you do need to set up external access to the Postgre SQL database of your Polarion installation but as far as I know your possibilities are limited their, queries like the one I provide for Jürgen are not possibility in this case as the query result does not represent directly a work item, user, plan or anything else.

2.) Ask your Sysadmin for external access to the Postegre SQL database of your Polarion. If it works you can install then a Postgre SQL client (I recommend pgAdmin 3, not pgAdmin 4 because it is damn slow, at least onmy machine) and then access the database and query whatever you want. The Polarion documentation (scroll down a bit to the section "Database") contains an instruction for how to do establish the external connection.

BEWARE: If you find any instruction for how to access a H2 database of your Polarion installation on your server, then forget this because it is outdated, H2 is not used by Polarion anymore, only PostGre SQL.

Jürgen wrote:- find all work items which are linked to work items of type xyz


I can help you with this one because I have it prepared at my disposal, the other ones you have to find out by yourself. I recommend to run this in a Postgre SQL client like pgAdmin 3. Before you can do that however, the external connection to the Postgre DB of the Polarion installation on your server must be established, like I mentioned in bullet point 2.) before.

The query:

Code: Select all

-- Postgre SQL query
-- Selects all linked items both in parent and child relationship and add the id and type of each
SELECT T1.fk_p_workitem, T1.fk_uri_p_workitem, T2.c_id AS parentWorkItemID, T2.c_type AS parentWorkItemType, T1.c_role, T1.fk_workitem, T1.fk_uri_workitem,
T3.c_id AS childWorkItemID, T3.c_type AS childWorkItemType,
T1.c_revision, T1.c_suspect
FROM polarion.struct_workitem_linkedworkitems T1
JOIN (SELECT c_pk, c_id, c_type FROM polarion.workitem -- fetches the fields for the parent item
-- WHERE c_type LIKE ''%task%'' -- Hint: this type selection is done in the wrapping Query, both ways are feasible
) T2
ON T1.fk_p_workitem = T2.c_pk
JOIN (SELECT c_pk, c_id, c_type FROM polarion.workitem -- fetches the fields for the child item
-- WHERE c_type LIKE ''%task%'' -- Hint: this type selection is done in the wrapping Query, both ways are feasible
) T3
ON T1.fk_workitem = T3.c_pk
ORDER BY T2.c_id
---------------------------------------------------------------------------------------------------
Playing a good game is never lame. :D
Image

pitzalis
Posts: 15
Joined: Mon Jun 04, 2007 8:58 am
Location: Italy

Re: Way to query physically deleted Work Items still linked

Postby pitzalis » Tue Jan 23, 2018 9:59 am

Hello,
I solved using the following utility wiki page:

Code: Select all

{parameter:querysrc|type=string|value=}
{parameter:obsolete|type=boolean|value=}
{parameter:simulation|type=boolean|value=true}

1 Purge Ghost Links

Remove ghost links to deleted Work Items. Optionally also to Work Items in "Obsolete" status.

Define the query for the selection of source Work Items. All links starting from a source Work Item and ending to a deleted inexistent, or in "Obsolete" status, destination Work Item, are removed.\\ \\
\\


{parameter-form}
<table>
  <tr>
    <td>*Delete also links to Obsolete Work Items:*&nbsp;</td>
    <td>{parameter-editor:obsolete}</td>
  <tr/>
  <tr>
    <td>*Links source Work Items query:*&nbsp;</td>
    <td>{parameter-editor:querysrc|width=500} <br/><br/>You can build the query in the <a href='/polarion/#/workitems' target='_blank'>Work Items</a> table, use the 'Convert To Text' feature, and copy the query string here.<br/><br/><br/><br/></td>
  <tr/>
  <tr>
    <td>{parameter-form-submit}</td>
    <td>*Simulation mode:*&nbsp;{parameter-editor:simulation}</td>
  <tr/>
</table>
{parameter-form}

#if($pageParameters.querysrc!="")

1.1 Results

#if($pageParameters.simulation)
  #warning("Simulation mode, no changes will be saved.")
  \\
#end

#set($wisSrc= $trackerService.queryWorkItems("HAS_VALUE:linkedWorkItems AND ($pageParameters.querysrc)","id"))

$transactionService.beginTx()
#set($err=false)
#foreach($wiSrc in $wisSrc)
  #set($linksList=[])
  #set($linkedWIsDirect = $wiSrc.getLinkedWorkItemsStructsDirect())
  #foreach($link in $linkedWIsDirect)
    ## Is The linked Item real and exists?
    #if($link.getLinkedItem().isUnresolvable())
      ## Enqueue the links to be removed
      #set($dummy=$linksList.add($link))
    #elseif($pageParameters.obsolete && $link.getLinkedItem().status.id == "obsolete")
      ## Enqueue the links to be removed
      #set($dummy=$linksList.add($link))
    #end
  #end
  ## Remove the links
  #foreach($link in $linksList)
    #set($linkedItem = $link.getLinkedItem())
    #set($linkedRole = $link.getLinkRole())
    #set($linkedItemId = $linkedItem.id)
    #if(!$wiSrc.removeLinkedItem($linkedItem, $linkedRole))
      #set($err=true)
      #error("Failure on removing link from $wiSrc.id to $linkedItemId")
    #else
* Link from &nbsp;&nbsp; {workitem:$wiSrc.project.id/$wiSrc.id} &nbsp;&nbsp; to &nbsp;&nbsp; <img src="/polarion/ria/images/enums/type_generic.gif"/>&nbsp;~~{pre} $linkedItemId {/pre}~~ &nbsp;&nbsp; removed
    #end
  #end
  $wiSrc.save()
#end
#if($err || $pageParameters.simulation)
  $transactionService.rollbackTx()
#else
  $transactionService.commitTx()
  #info("Done.")
#end

#else
  #info("Enter the parameters first")
#end


I hope this will help you.
GP


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: No registered users and 2 guests