Database - Change history
Change History Database Tables
JIRA stores the Change History records of each issue in the
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).
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
NEWSTRING columns are similar in nature.
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:
The issues are stored in the
And then insert the required number of
changeitem records referencing the inserted
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:
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:
Where 10310 is the nearest 10 above 10303.
SEQ_NAME column refers to the database table name defined in
WEB-INF/classes/entitydefs/entitymodel.xml (eg. "Action" is
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.
If you want to insert records into Active Object (AO) tables, as used by many JIRA add-ons, then SEQUENCE_VALUE_ITEM is not used. Instead AO provides "autoincrement" functionality where each primary key (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's API
The best way to retrieve change history entries is:
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.