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”.

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

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)

• Maintenance Database: postgres
• Username: (if default is set) polarion
• Password: (if default is set) polarion
  

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.

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.

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”.

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:

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