SQL Query for linked WorkItems

Description
rpn
Posts: 23
Joined: Thu Oct 26, 2017 4:08 pm

SQL Query for linked WorkItems

Postby rpn » Thu Oct 26, 2017 4:40 pm

I need to trace the WorkItems in a document (CustomerRequirements-Document) down to the tests in different Levels (to ensure coverage).

My Query is like that:

Code: Select all

select * from polarion.WorkItem
where polarion.WorkItem.c_pk in (select * from polarion.lucene_query('WorkItem', 'type:customerrequirement AND document.title:(CustomReqs Platform XY)', 'id'))
AND c_deleted is false AND fk_project = [$project_id];


But the result is missing most of the Items visible via the WebInterface - the result is the same with matching via c_URI instead of c_pk, or with querying STRUCT_WORKITEM_LINKEDWORKITEMS.

STRUCT_WORKITEM_LINKEDWORKITEMSDERIVED is an empty Table, so no luck there, either.

I've read the SQL-Documents in the /sdk/-Folder in our local install, but can't figure it out on my own.

Iam thankful for every hint, on how to get all links from a Workitem out of the database.

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

Re: SQL Query for linked WorkItems

Postby ALMighty » Thu Jan 18, 2018 2:48 pm

Hello,

first at all, sorry that you had to wait so long for a reply but I did not notice your thread until a moment ago. :oops:

rpn wrote:I need to trace the WorkItems in a document (CustomerRequirements-Document) down to the tests in different Levels (to ensure coverage).


With document, you mean the livedocs in Polarion with the blue icons?

If yes, note that they are oft called modules. In the SVN root directory of each Polarion project you will find a modules folder if you have at least one livedoc created (you allso find the work items there which were added to this livedoc) and in the database they are also listed as modules, which will be important in the next step.

rpn wrote:My Query is like that:

Code: Select all

select * from polarion.WorkItem
where polarion.WorkItem.c_pk in (select * from polarion.lucene_query('WorkItem', 'type:customerrequirement AND document.title:(CustomReqs Platform XY)', 'id'))
AND c_deleted is false AND fk_project = [$project_id];



I understand your query but I think there is an easier way to fetch all the items in specific livedoc/module/document with blue icon.

As I said, the livedocs are also labelled as modules in the database, there is a module table and also the table workitems has the columns fk_module and fk_uri_module (both contain actually the same values as far as I see it). This is the key for getting the desired result (see the Full Database Schema of Polarion for checking which columns the module table contains).

Here is a code snippet that gets all work items which were added to the livedoc/module/document
'Testdocument01, tested in pgAdmin 3:

Code: Select all

-- Postgre SQL query tested in pgAdmin3
SELECT
polarion.workitem.c_id, -- the id of the work item
polarion.module.c_id, -- the id of the livedoc
polarion.module.c_modulename -- the title/name of the livedoc
FROM polarion.workitem JOIN polarion.module
ON polarion.workitem.fk_module = polarion.module.c_pk
WHERE polarion.module.c_id LIKE 'Testdocument01'


rpn wrote:But the result is missing most of the Items visible via the WebInterface - the result is the same with matching via c_URI instead of c_pk, or with querying STRUCT_WORKITEM_LINKEDWORKITEMS.


If you would then like to query which work items are linked to the items which a certain module/livedoc contains, beware that they can be either linked ina parent or child relationship, in the following query this problem is considered:

Code: Select all

[code]
-- 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
[/code]


rpn wrote:STRUCT_WORKITEM_LINKEDWORKITEMSDERIVED is an empty Table, so no luck there, either.


I have no clue either what this table is actually for.

rpn wrote:I've read the SQL-Documents in the /sdk/-Folder in our local install, but can't figure it out on my own.


But as far as I see it you can execute Postgre SQL queries on the database (with a client like pgAdmin 3 or from a Java programme) of your Polarion OUTSIDE a wikipage, right?

If not, check out my answer in the thread Way to query physically deleted Work Items still linked, maybe it helps.
---------------------------------------------------------------------------------------------------
Playing a good game is never lame. :D
Image

rpn
Posts: 23
Joined: Thu Oct 26, 2017 4:08 pm

Re: SQL Query for linked WorkItems

Postby rpn » Mon Jul 09, 2018 1:03 pm

Thank you very much for your answer!

I got the lucene-query out of the JS in the wiki-page-dom and queried it via WebServices, that solved my problem. The whole thing was a workaround from the polarion-users (tables got to much width for document-view) and did not use an ordinary polarion-interface to create this type of page (It is a plugin, that triggers the js-query on embed in the document - the query is defined partly in that trigger and partly in the wiki-page itself).
No native speaker, sorry for my bad English.


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: Google [Bot] and 10 guests