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.
Historical SQL query
-
- Posts: 21
- Joined: Wed Apr 17, 2013 12:10 pm
- Location: Böblingen, Baden-Württemberg, Germany
- Contact:
Historical SQL query
Kind regards.
Hagen
Hagen
Re: Historical SQL query
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 to get history information
- Iterate over each change information of corresponding work item
- get date of change and check if it is in the last two weeks
- get a revisioned instance of the work item
- read status of the revisioned work item instance and save it
Regards,
Sandro
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);
- 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
-
- Posts: 3
- Joined: Mon Apr 16, 2018 6:42 am
Re: Historical SQL query
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.
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.
Re: Historical SQL query
dejavu030507 wrote: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:
dejavu030507 wrote: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.
- Attachments
-
- Where to find the Polarion History
- How_to_find_polarion_history.png (40.18 KiB) Viewed 3485 times
Return to “Polarion Application Lifecycle Management (ALM)”
Who is online
Users browsing this forum: No registered users and 11 guests