Historical SQL query

[hseifert]

Hello.

I heard that it's possible to get historical data by SQL. I didn't find any helpful information in the help or in the forum. Does anybody can give me a little example?

For better understanding: I want to get a list of all workitems in a specified status for every day of the last two weeks.

[Sandro_F]

Hello,

I think this is NOT trivial - also with sql querys. So there is "no little example" unfortunately.

I would solve your problem with a custom plugin:

- Get all relevant work items
- Iterate over each work item and use

Code: Select all

IChange[] change = trackerService.getDataService().getDiffManager().generateHistory(wi, e);

to get history information
- Iterate over each change information of corresponding work item

Code: Select all

for (int i = 0; i < change.length; i++) {
               IChange currentChange = change[i];

- get date of change and check if it is in the last two weeks
- get a revisioned instance of the work item

Code: Select all

IWorkItem wi2 = (IWorkItem) trackerService.getDataService().getVersionedInstance(wi.getObjectId(),
                     currentChange.getRevision());

- read status of the revisioned work item instance and save it


Regards,
Sandro

[dejavu030507]

Hello,

Is there anyone to find a way to get the work items' historical data using sql query?

I think, it will be faster than plugin.

Thanks.

[ALMighty]

Is there anyone to find a way to get the work items' historical data using sql query?

Step 1: Establish access to the DB of your Polarion
First of all, you need to get access to the Postgre SQL database (Postgre that is the kind of SQL Polarion uses).
If this is not done yet, then ask your System Administrator - or whoever is responsible to administer your Polarion installation also on Server level - to go through the tutorial External Database Connection to PostgreSQL, which is listed among the other database documentation of Polarion.

Step 2: Get a Postgre SQL client and connect to your Polarion database
You need a client for executing queries of your Polarion database, especially if you want to query the history, I recommend one that supports Postgre fully, not just partly or has just "Beta Support". I can recommend pgAdmin3, there is also pgAdmin 4 already available but that one was quite slow the last time I used it). With this client you can login to the server which holds the Postgre SQL database.

Step 3: Search for the History tables
Actually Polarion holds two databases, the first is polarion which holds only the current data and the second is polarion_history, which contains the history data. In the picture attached you will find how the structure of your Polarion databases looks like. Search for the polarion_history database, there you will find the same tables like in the polarion database but those tables contain more entries than in polarion db because as mentioned, it contains the history too.

Example: Let's say we have a work item with the id XYZ-1000 and you changed it 4 times. Then you will find 5 rows (1 for the creation of the work item + 4 per each change) for this work item in the workitem table of the polarion_history DB, but only one in the workitem table of the polarion Database because there is no history in that database.

And by the way, because the history can also be fetched with other ways than SQL:

I think, it will be faster than plugin.

I agree because I feel that creating a plugin for Polarion is a pain. However the code in Sandro_F's answer could also be used in a Polarion Wikipage, which is set up way faster than a Plugin. You can check out my answer in the thread Get real starting date of an item if you want to learn more about fetching History in a wikipage.

AND FURTHERMORE: You could also fetch the history in a pure Java App (this is something else than the aforementioned plugin) If you want to learn more about this way of getting the history, see my post in the thread Use compare function in custom wiki content.