Design a site like this with WordPress.com
Get started

Basics: Filter Work Items and configure Widgets by SQL Queries

Most Polarion users use the lucene filter functionality on a daily basis, as it’s pretty handy and easy to use. But there is one downside to lucene queries: as soon as you want to create a filter, based on a complex linking scheme, you will have to switch to using SQL Queries or Velocity Code.

In this topic I want to show you, how you can create SQL Queries, to filter work items and configure widgets by complex logics.

I will go trough the following topics:

  1. Where can I use SQL Queries
  2. Database scheme of Polarion
  3. How to access the SQL database in your offline Polarion installation
  4. Examples of SQL Queries

Note: I won’t explain the basics of SQL (Like SELECT, FROM, WHERE, JOIN etc.). Because there are many sites, which explain the commands way better than I ever could.

Where can I use SQL Queries?

  • In all filter bars, where you can select the filter criteria “SQL”.
  • In the configuration of many widgets.
  • To get work items by using the API.

Database scheme of Polarion

Just for the technical understanding: Although there is a repository, where all the data is saved in the end, there is a PostgreSQL layer to make everything faster. This PostgreSQL database contains almost all the data you find in Polarion. And you can access them via database tools like any other database, if you have the credentials.

Don’t be demotivated by the database scheme on the first look. Although it looks complicated, you won’t need 90% of the tables, which makes it rather simple in the end. You can find the full database scheme here: https://almdemo.polarion.com/polarion/sdk/doc/database/FullDBSchema.pdf

But usually you just need the work items database schema, which you can find here:

https://almdemo.polarion.com/polarion/sdk/doc/database/WorkItemDBSchema.pdf

How to access the SQL database in your offline Polarion installation

To check out the content of the SQL tables and test your queries, it makes sense to have a local Polarion. But it’s not necessary and you can try your SQL queries in Polarion widgets or filter bars.

If you don’t have a local installation yet, I would recommend it. You can find the guide for your local installation here: https://polarion.code.blog/2020/06/12/basics-part-5-get-your-own-free-local-polarion-installation/.

Now you just need a tool to access the PostgreSQL database of your locally hosted Polarion. I prefer the freeware “PGAdmin 4”. This is the one I will show a step by step guide for. Of course this should work with any software that allows you to access databases.

So download PGAdmin 4 under: https://www.pgadmin.org/download/. Install the software – I used default configuration.

If you start it, then you’ll be accessing the pgAdmin tool via your browser. To add the Polarion PostgreSQL Server, select “Add New Server”.

PGAdmin Overview

First you have to enter a name for this Server. This can be anything. I’ll use “Polarion”.

Add Server

Go then to the Tab “Connection”. There you’ll have to add the following data:

  • Host: localhost
  • Port: (default is 5432, but take a look in the Polarion\polarion\configuration\polarion.properties)
polarion.properties – postgre Port
  • Maintenance Database: postgres
  • Username: (if default is set) polarion
  • Password: (if default is set) polarion
PG Admin Connection to Polarion Postgre Server

Click “Save”. Then a connection should be established and visible on the left side of pgAdmin.

If you open the path “Polaron/Databases/polarion/Schemas/polarion/Tables” in pgAdmin, you can take a look at the available tables. You’ll find these tables in the database scheme, that I’ve linked before.

Connection established

To take a look in the data of a table you can right-click for example on the “workitem” table and select “View/Edit Data First 100 Rows”. This will give you an impression of the data in the table.

Access Polarion table data

Examples of SQL Queries

Lets configure a table block widget in a live report page via SQL.

So create a report page, add the table block widget and select query type “SQL”.

Configure Table Block with SQL query

You might have noticed, that a predefined query is pasted in the query field. It looks similar to this:

select WORKITEM.C_URI
--select additional results if you want to use them in the query
from WORKITEM
inner join PROJECT on PROJECT.C_URI = WORKITEM.FK_URI_PROJECT
--specify additional joins between tables
where true
and PROJECT.C_ID = 'drivepilot'
--define your search criteria, 'group by' statements, etc.

Lets talk about the basics:

  • “SELECT WORKITEM.C_URI” – this is the most important thing to be noticed. In the end the widgets and filter bars want a query, that returns a list of WORKITEM.C_URIs. These are the real ids of Work Items Polarion wide. Because IDs of Work Items are just unique project wide.
  • “inner join PROJECT on PROJECT.C_URI = WORKITEM.FK_URI_POJECT” […] WHERE […] PROJECT.C_ID = ‘drivepilot’ – this part sets, that only Work Items of the project with the id “drivepilot” will be listed.

Now lets try this query in our pgAdmin. Click on the database symbol on the upper left in the pgAdmin.

This will open the query editor. Paste the SQL query into the query editor and press “play”.

This will give you the table of C_URIs of all Work Items in the project.

Lets tackle now the following: Lets give me all Unit Test Cases, which have an Issue linked to them.

So lets gather what we need for this: the types of the Work Items and their links.
The types are available in the table WORKITEM.C_TYPE. But the links are available in the table “STRUCT_WORKITEM_LINKEDWORKITEMS”.

The table “STRUCT_WORKITEM_LINKEDWORKITEMS” is structured as follows:

Table “STRUCT_WORKITEM_LINKEDWORKITEMS”

What we can see here is that links have a direction. A Work Item with the “FK_URI_P_WORKITEM” has a link with link role “C_ROLE” to a given Work Item with “FK_URI_WORKITEM”. And we want the following: all C_URIs of type “unittestcase”, which appear in this list with a link to a C_URI of type “issue”.

SQL pros will see instantly, where this goes. I’ll go trough it step by step. So lets develop the table in pgAdmin. At the moment I have one Unit Test Case, which fits to this query.

(As I am not an expert in SQL, you can suggest better solutions in the comments ;))

My first step here is to INNER JOIN the table I’ve got with the STRUCT_WORKITEM_LINKEDWORKITEMS table. One detail here: I joined them on the “FK_URI_WORKITEM” instead of “FK_URI_P_WORKITEM”, because I’m going “backlinked”.

NOTE: Links in Polarion have a direction. Links point “upwards” which means from e.g. software requirement to system requirement. So if I’m trying to get data “backlinked” (downwards linked like from test case to issue) I have to join on the “FK_URI_WORKITEM” column first. As the Unit Tests are the object linked from the Issues here.

And I’ll filter the table by the type “unittestcase”. So now I’ve got all Unit Test Cases, which have any backlinked links.

select WORKITEM.C_URI
from WORKITEM
inner join PROJECT on PROJECT.C_URI = WORKITEM.FK_URI_PROJECT
inner join STRUCT_WORKITEM_LINKEDWORKITEMS as SWL on SWL.FK_URI_WORKITEM = WORKITEM.C_URI
where true
and PROJECT.C_ID = 'drivepilot'
and WORKITEM.C_TYPE = 'unittestcase'

But this is not enough yet. Now I would have to go back trough the linked URI and check, if the one is an issue.

So I’m going basically the same way backwards and add the WORKITEMS table a second time, but name it “WORKITEMS2”.

select WORKITEM.C_URI
from WORKITEM
inner join PROJECT on PROJECT.C_URI = WORKITEM.FK_URI_PROJECT
inner join STRUCT_WORKITEM_LINKEDWORKITEMS as SWL on SWL.FK_URI_WORKITEM = WORKITEM.C_URI
inner join WORKITEM as WORKITEM2 on  WORKITEM2.C_URI = SWL.FK_URI_P_WORKITEM
where true
and PROJECT.C_ID = 'drivepilot'
and WORKITEM.C_TYPE = 'unittestcase'
and WORKITEM2.C_TYPE = 'issue'

Additionally we have to make sure, that no duplicates are counted. (If a Test Case has multiple Issues for example). To do this we use “Group By” instead of “DISTINCT”, as the “SELECT” statement is often hard coded in widgets.

select WORKITEM.C_URI
from WORKITEM
inner join PROJECT on PROJECT.C_URI = WORKITEM.FK_URI_PROJECT
inner join STRUCT_WORKITEM_LINKEDWORKITEMS as SWL on SWL.FK_URI_WORKITEM = WORKITEM.C_URI
inner join WORKITEM as WORKITEM2 on  WORKITEM2.C_URI = SWL.FK_URI_P_WORKITEM
where true
and PROJECT.C_ID = 'drivepilot'
and WORKITEM.C_TYPE = 'unittestcase'
and WORKITEM2.C_TYPE = 'issue'
GROUP BY WORKITEM.C_URI

Now you only have to get this into query form of the table block widget, which is a bit tricky, as some parts are hard coded.

You can use the query in filter bars too:

Find more examples under: https://almdemo.polarion.com/polarion/sdk/doc/database/SQLQueryExamples.pdf



Advertisement

Published by PolarionDude

Dude who does Polarion - if Polarion would be a verb.

5 thoughts on “Basics: Filter Work Items and configure Widgets by SQL Queries

  1. Hallo Florian,
    from your experience: Is it possible to identify deleted WorkItems via SQL? As far as I know, there is no new revision of the WI when deleted. Is there an field, which is written in the database?
    BR and thank you in advance
    Markus

    Like

    1. Hi Markus,

      I don‘t think it‘s possible via SQL. Therefore you have to use the API and Reports. But probably it‘s rather resource intensive. Deleting creates definitely a new revision in Polarion. But as you say, the revision is probably not assigned to the work item. So you would have to go trough each revision and check if an item that existed before isn‘t existing anymore. But I could be wrong, maybe there is some method available to do this more easily.

      Best regards,
      Florian

      Like

      1. Hallo Florian,
        thank you for your fast response! I asume that as well. If I find another posibility, I will reply to this Post.
        BR and thanX
        Markus

        Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: