SQL query example for workitems in a plan

Description
steffenw
Posts: 9
Joined: Mon Jun 25, 2012 8:37 am

SQL query example for workitems in a plan

Postby steffenw » Mon Jan 15, 2018 4:30 pm

Hello,

I'm looking for an example of a query which gives me all Tasks assigned to a given userid which are assigned to a Story which itself is planned in a Polarion plan (plan feature).

- Story1 assigned to plan "plan1"
- Task1.1 => user 1
- Task1.2

- Story2 assigned to plan "plan1"
- Task2.1
- Task2.2 => user 1

- Story 3 assigned to plan "plan2"
- Task3.1
- Task3.2 => user 1

So the result of the query for Tasks of "plan1" would be Task1.1 and Task2.2.

Because I intend to build up a Wiki page with a table for a list of users and there work in dedicated plans it's probably better to use SQL queries rather than several Lucene queries for performacne reasons.

Any ideas? Thanks a lot.

Steffen

ALMighty
Posts: 33
Joined: Wed Apr 27, 2016 10:34 am

Re: SQL query example for workitems in a plan

Postby ALMighty » Tue Jan 16, 2018 10:10 am

I cannot provide you with a out of the box query, I however can provide you some equipment for making your live a bit easier.

1.) I have here a code snippet that shows how you can execute SQL queries in a wiki page, the example queries work items but you can query basically everything with it that can be represented ina PObject (work items, timepoints, users and for sure plans). Doing it via the Dataservice is the only way I know. You can use this snippet out of the box in a wiki page, just you just have to adjust the query with a work item ID that exists in one of your Polarion projects. Check also the comments, which start with ##-

Code: Select all

## Not really needed in this case, but a nice way to get the project id of the project in which you use the wikipage
#set($projectID = "$page.space.projectId")

## Note: $trackerService does not be defined, you can use right away like below
#set($dataService = $trackerService.getDataService())

## sqlSearch method can be used for query anything, not just work items but also timepoints, Test runs, plans and users.
#set($sampleObjectList = $dataService.sqlSearch("SELECT * FROM polarion.workitem where c_id LIKE 'IM1-%'"))
## $sampleObjectList

#foreach($tpObject in $sampleObjectList)

## Note: We have an object of the PObject type in our loop, which can represent anything (user, timepoint work item and so on) but in this case a work item.
## Each PObject contains however a URI and if the URI belongs to a work item, we can get a work item with it (recognizable by the {WorkItem} part of the URI)
## With the URI, we can get a WorkItem object, with which can further access all variables.

URI of the object: $tpObject.getUri() <br />

#set($workItemRepresentedBySampleOBject = $trackerService.getWorkItem($tpObject.getUri()))

<H1>$workItemRepresentedBySampleOBject.getId()</H1> ## printzing the ID of the work item

## a more beautified way to show a work items based on the wiki syntax help. For detailed instruction, see "Using Wiki Syntax Help" in https://almdemo.polarion.com/polarion/help/?topic=/com.polarion.xray.doc.user/ugchAppendix.html
{workitem: $workItemRepresentedBySampleOBject.getId()}

#end ## end foreach


2.) You probably want to know how the database structure looks like in Polarion, so that you know what you can query from which table. Check out the database documentation of Polarion, especially the Full DB Schema will help you: https://almdemo.polarion.com/polarion/s ... Schema.pdf

3.) Furthermore I want to provide you with the Javadoc showing you methods to some types you will use uin your wiki page:

TrackerService,
DataService,
PObject,
WorkItem,
Plan,
User

You can also fetch plan via the searchPlans method (I guess it requires a Lucene query) of the PlanningManager, which can be invoked with the following command:

Code: Select all

$trackerService.getPlanningManager()


4.) Also, here is the Velocity guide (Velocity is the language which is primarily used in wiki pages along with HTML) for Version 1.4, which is currently used by Polarion 2016 as far as I know:
https://velocity.apache.org/engine/1.4/user-guide.html

steffenw wrote:Because I intend to build up a Wiki page with a table for a list of users and there work in dedicated plans it's probably better to use SQL queries rather than several Lucene queries for performacne reasons.


To be honest I am not sure about this. For applying SQL queries in a wiki page, POlarion has to crawl its object list, which - I can imagine - will also consume some resources. Also, sometimes writing a query for Lucene is less cumbersome than for SQL.
---------------------------------------------------------------------------------------------------
Playing a good game is never lame. :D
Image


Return to “Polarion Application Lifecycle Management (ALM)”

Who is online

Users browsing this forum: No registered users and 2 guests