Change History

Change History Database Tables

JIRA stores the Change History records of each issue in the changegroup and changeitem tables.

Each change to the issue triggered by a user inserts one record into the changegroup table. Each changegroup table record describes which issue it refers to, the time of the change and the user who has performed the change (null for a non-logged in user).

mysql> select * from changegroup;
+-------+---------+--------+---------------------+
| ID    | issueid | AUTHOR | CREATED             |
+-------+---------+--------+---------------------+
| 10000 |   10000 | admin  | 2005-06-09 15:16:39 |
| 10751 |   10000 | admin  | 2005-06-10 00:00:00 |
+-------+---------+--------+---------------------+

Each changegroup record refers to one or many changeitem records. Each changeitem record describes the issue field that has been updated and its old and new values. The OLDVALUE column records the id of the changed enity (e.g. status) while OLDSTRING records the name fo the entity, so that if the entity is removed from the system the change history for an issue can still be displayed. The NEWVALUE and NEWSTRING columns are similar in nature.

mysql> select * from changeitem;
+-------+---------+-----------+------------+----------+-----------+----------+-----------+
| ID    | groupid | FIELDTYPE | FIELD      | OLDVALUE | OLDSTRING | NEWVALUE | NEWSTRING |
+-------+---------+-----------+------------+----------+-----------+----------+-----------+
| 10000 |   10000 | jira      | status     | 1        | Open      | 6        | Closed    |
| 10001 |   10000 | jira      | resolution | NULL     | NULL      | 1        | Fixed     |
| 11404 |   10751 | jira      | status     | 1        | Open      | 6        | Closed    |
+-------+---------+-----------+------------+----------+-----------+----------+-----------+

Inserting change history records

When writing tools that import data into JIRA, it is sometimes required to import change history. To do this please first insert a record into the changegroup table with a valid issue id:

insert into changegroup values (20000,10000,'admin','2005-06-12');

The issues are stored in the jiraissue table:

mysql> select id, pkey from jiraissue;
+-------+-------+
| id    | pkey  |
+-------+-------+
| 10000 | TST-1 |
+-------+-------+

And then insert the required number of changeitem records referencing the inserted changegroup record:

insert into changeitem values (11000, 20000, 'jira','status','1','Open','6','Closed');

The SEQUENCE_VALUE_ITEM table

The SEQUENCE_VALUE_ITEM table is used to record, in a database independent way, the maximum ID used in each of JIRA's database tables:

mysql> select * from SEQUENCE_VALUE_ITEM;
+-----------------------------+--------+
| SEQ_NAME                    | SEQ_ID |
+-----------------------------+--------+
| Action                      |  10310 | 
| ChangeGroup                 |  11050 | 
| ChangeItem                  |  11320 | 
| ColumnLayout                |  10040 | 
| ColumnLayoutItem            |  10120 | 
| Component                   |  10110 | 
| ConfigurationContext        |  10170 | 
| SchemeIssueSecurities       |  10040 | 
...

Actually, Ofbiz allocates IDs in batches of 10, so the SEQ_ID is the next available ID rounded up to the nearest 10. So you might have:

mysql> select max(ID) from jiraaction;
+---------+
| max(ID) |
+---------+
|   10303 | 
+---------+
1 row in set (0.04 sec)

mysql> select * from SEQUENCE_VALUE_ITEM where SEQ_NAME='Action';
+----------+--------+
| SEQ_NAME | SEQ_ID |
+----------+--------+
| Action   |  10310 | 
+----------+--------+
1 row in set (0.01 sec)

Where 10310 is the nearest 10 above 10303.

The SEQ_NAME column refers to the database table name defined in WEB-INF/classes/entitydefs/entitymodel.xml (eg. "Action" is jiraaction).

Manually inserting records

The implication of this is that if you want to manually insert records into JIRA database tables, you must update SEQUENCE_VALUE_ITEM yourself. Set the relevant rows' SEQ_ID values to a value greater than the actual maximum ID in the table. You will then need to restart JIRA to ensure all database caches are reset.

Retrieving Change History using JIRA's API

The best way to retrieve change history entries is:

actionManager.getChangeHistory(getIssue(), authenticationContext.getUser());

You can declare dependency on JiraAuthenticationContext and ActionManager in the constructor of your plugin as described in PicoContainer and JIRA.

The getChangeHistory method returns ChangeHistory objects on which you can call the getChangeItems() method. This returns a List of GenericValue objects, each one representing an issue field update. To check the field that was updated do:

String fieldName = changeItem.getString("field")

GenericValues are described in Database Schema.