Step 1: Establish access to the DB of your Polarion
dejavu030507 wrote:Is there anyone to find a way to get the work items' historical data using sql query?
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