Database – 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 not logged in user).

1
2
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 entity (for example, status) while OLDSTRING records the name for 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.

1
2
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.

  1. Insert a record into the changegroup table with a valid issue ID.

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

    The issues are stored in the jiraissue table:

    1
2
    mysql> select jiraissue.id, issuenum, project.pkey, project.pname from jiraissue join project on jiraissue.project=project.id;
+-------+---------+-------+-------+
| id    | issuenum| pkey  | pname |
+-------+---------+-------+-------+
| 10000 | 1       | TST   | TEST  |
+-------+---------+-------+-------+

  2. Insert the required number of changeitem records referencing the inserted changegroup record.

    1
2
    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 database tables:

1
2
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:

1
2
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 (for example, "Action" is jiraaction).

Manually inserting records

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. To ensure all database caches are reset, you will then need to restart Jira.

If you want to insert records into AO (that is, Active Object) tables, as used by many Jira apps, then SEQUENCE\_VALUE\_ITEM is not used. Instead AO provides autoincrement functionality where each primary key (that is, ID) is automatically created. Different databases support this feature in different ways (MySQL has the feature, Oracle 11g uses triggers and sequences).

Retrieving Change History using Jira API

The best way to retrieve change history entries is:

1
2
changeHistoryManager.getChangeHistoriesForUser(getIssue(), authenticationContext.getUser());

You can declare dependency on JiraAuthenticationContext and ChangeHistoryManager in the constructor of your app using Atlassian Spring Scanner.

The getChangeHistoriesForUser method returns list of 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 the following:

1
2
String fieldName = changeItem.getString("field")

For more info on GenericValues, see the section about Entity Engine.

Rate this page: