JIRA 4.0 : Database Schema
This page last changed on Jul 23, 2009 by rosie@atlassian.com.
JIRA uses Entity Engine module of the OfBiz suite to communicate with the database. You can learn more about the Entity Engine by reading its online documentation. The database schema is described in the entitymodel.xml found in the WEB-INF/classes/entitydefs directory under the JIRA web application. The entitymodel.xml file has an XML definition of all JIRA's database tables, table columns and their data type. Some of the relationships between tables also appear in the file. If you are using JIRA's API you will notice that a lot of code deals with GenericValue objects. The GenericValue is an OfBiz entity engine object. Each GenericValue object represents a record in the database. To get a value of a field from a GenericValue you will need to use the relevant getter method for the field's type. For example:
GenericValue project = ... String name = project.getString("name"); Long id = project.getLong("id"); The list of valid fields for each entity can be obtained by looking the entity's definition in the WEB-INF/classes/entitydefs/entitymodel.xml file. For the above example, one needs to look at the "Project" entity. Relationships between tablesSome of the relationships between JIRA's tables in the database are documented below: Issue FieldsThis page shows how to examine each of a JIRA issue's fields via SQL. We will use JRA-3166 as a sample issue in our queries. Simple fieldsMost fields in JIRA are kept in the jiraissue table: mysql> desc jiraissue; +----------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | | | | pkey | varchar(255) | YES | MUL | NULL | | | PROJECT | decimal(18,0) | YES | MUL | NULL | | | REPORTER | varchar(255) | YES | | NULL | | | ASSIGNEE | varchar(255) | YES | MUL | NULL | | | issuetype | varchar(255) | YES | | NULL | | | SUMMARY | varchar(255) | YES | | NULL | | | DESCRIPTION | longtext | YES | | NULL | | | ENVIRONMENT | longtext | YES | | NULL | | | PRIORITY | varchar(255) | YES | | NULL | | | RESOLUTION | varchar(255) | YES | | NULL | | | issuestatus | varchar(255) | YES | | NULL | | | CREATED | datetime | YES | | NULL | | | UPDATED | datetime | YES | | NULL | | | DUEDATE | datetime | YES | | NULL | | | VOTES | decimal(18,0) | YES | | NULL | | | TIMEORIGINALESTIMATE | decimal(18,0) | YES | | NULL | | | TIMEESTIMATE | decimal(18,0) | YES | | NULL | | | TIMESPENT | decimal(18,0) | YES | | NULL | | | WORKFLOW_ID | decimal(18,0) | YES | | NULL | | | SECURITY | decimal(18,0) | YES | | NULL | | | FIXFOR | decimal(18,0) | YES | | NULL | | | COMPONENT | decimal(18,0) | YES | | NULL | | +----------------------+---------------+------+-----+---------+-------+ They can be retrieved with a regular select: mysql> select id, pkey, project, reporter, assignee, issuetype, summary from jiraissue where pkey='JRA-3166'; +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | id | pkey | project | reporter | assignee | issuetype | summary | +-------+----------+---------+-----------+----------+-----------+---------------------------------+ | 16550 | JRA-3166 | 10240 | mvleeuwen | NULL | 2 | Database consistency check tool | +-------+----------+---------+-----------+----------+-----------+---------------------------------+ User detailsSay we wish to find out the email address and other details about our reporter, mvleeuwen. First we find this user's ID: mysql> select id from userbase where username='mvleeuwen'; +-------+ | id | +-------+ | 13841 | +-------+ Then use it to look up 'properties' of this userbase record (stored in propertysets. Each property has a record in the propertyentry table specifying its name and type, and a record in one of propertystring, propertydecimal, propertydate, propertytext, propertydata or propertynumber, depending on the type. mysql> desc propertyentry; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | | | | ENTITY_NAME | varchar(255) | YES | | NULL | | | ENTITY_ID | decimal(18,0) | YES | MUL | NULL | | | PROPERTY_KEY | varchar(255) | YES | | NULL | | | propertytype | decimal(9,0) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ mysql> select * from propertyentry where ENTITY_NAME='OSUser' and ENTITY_ID=(select id from userbase where username='mvleeuwen'); +-------+-------------+-----------+--------------+--------------+ | ID | ENTITY_NAME | ENTITY_ID | PROPERTY_KEY | propertytype | +-------+-------------+-----------+--------------+--------------+ | 18352 | OSUser | 13841 | email | 5 | | 18353 | OSUser | 13841 | fullName | 5 | +-------+-------------+-----------+--------------+--------------+ So email and fullName are of type 5. which means the propertystring table. Here is the list of propertytype to table mappings:
So the email and fullName properties are strings, and so can be found in the propertystring table: mysql> select * from propertystring where id in (18352, 18353); +-------+---------------------+ | ID | propertyvalue | +-------+---------------------+ | 18352 | lemval@zonnet.nl | | 18353 | Michael van Leeuwen | +-------+---------------------+ Components and versionsSince each issue can have multiple components/versions, there is a join table between jiraissue and version/component tables called nodeassociation: mysql> desc nodeassociation; +--------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------------+------+-----+---------+-------+ | SOURCE_NODE_ID | decimal(18,0) | NO | PRI | | | | SOURCE_NODE_ENTITY | varchar(60) | NO | PRI | | | | SINK_NODE_ID | decimal(18,0) | NO | PRI | | | | SINK_NODE_ENTITY | varchar(60) | NO | PRI | | | | ASSOCIATION_TYPE | varchar(60) | NO | PRI | | | | SEQUENCE | decimal(9,0) | YES | | NULL | | +--------------------+---------------+------+-----+---------+-------+ mysql> select distinct SOURCE_NODE_ENTITY from nodeassociation; +--------------------+ | SOURCE_NODE_ENTITY | +--------------------+ | Issue | | Project | +--------------------+ mysql> select distinct SINK_NODE_ENTITY from nodeassociation; +-----------------------+ | SINK_NODE_ENTITY | +-----------------------+ | IssueSecurityScheme | | PermissionScheme | | IssueTypeScreenScheme | | NotificationScheme | | ProjectCategory | | FieldLayoutScheme | | Component | | Version | +-----------------------+ mysql> select distinct ASSOCIATION_TYPE from nodeassociation; +------------------+ | ASSOCIATION_TYPE | +------------------+ | IssueVersion | | IssueFixVersion | | IssueComponent | | ProjectScheme | | ProjectCategory | +------------------+ So to get fix-for versions of an issue, run: mysql> select * from projectversion where id in ( select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueFixVersion' and SOURCE_NODE_ID=( select id from jiraissue where pkey='JRA-5351') ); +-------+---------+-------+-------------+----------+----------+----------+------+-------------+ | ID | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL | RELEASEDATE | +-------+---------+-------+-------------+----------+----------+----------+------+-------------+ | 11614 | 10240 | 3.6 | NULL | 131 | NULL | NULL | NULL | NULL | +-------+---------+-------+-------------+----------+----------+----------+------+-------------+ Similarly with affects versions: mysql> select * from projectversion where id in ( select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueVersion' and SOURCE_NODE_ID=( select id from jiraissue where pkey='JRA-5351') ); +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ | ID | PROJECT | vname | DESCRIPTION | SEQUENCE | RELEASED | ARCHIVED | URL | RELEASEDATE | +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ | 10931 | 10240 | 3.0.3 Professional | NULL | 73 | true | NULL | NULL | 2004-11-19 00:00:00 | | 10930 | 10240 | 3.0.3 Standard | NULL | 72 | true | NULL | NULL | 2004-11-19 00:00:00 | | 10932 | 10240 | 3.0.3 Enterprise | NULL | 74 | true | NULL | NULL | 2004-11-19 00:00:00 | +-------+---------+---------------------+-------------+----------+----------+----------+------+---------------------+ and components: mysql> select * from component where id in ( select SINK_NODE_ID from nodeassociation where ASSOCIATION_TYPE='IssueComponent' and SOURCE_NODE_ID=( select id from jiraissue where pkey='JRA-5351') ); +-------+---------+---------------+-------------+------+------+--------------+ | ID | PROJECT | cname | description | URL | LEAD | ASSIGNEETYPE | +-------+---------+---------------+-------------+------+------+--------------+ | 10126 | 10240 | Web interface | NULL | NULL | NULL | NULL | +-------+---------+---------------+-------------+------+------+--------------+ Issue links
mysql> desc issuelink; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | | | | LINKTYPE | decimal(18,0) | YES | MUL | NULL | | | SOURCE | decimal(18,0) | YES | MUL | NULL | | | DESTINATION | decimal(18,0) | YES | MUL | NULL | | | SEQUENCE | decimal(18,0) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) For instance, to list all links between TP-1 and TP-2: mysql> select * from issuelink where SOURCE=(select id from jiraissue where pkey='TP-1') and DESTINATION=(select id from jiraissue where pkey='TP-2'); +-------+----------+--------+-------------+----------+ | ID | LINKTYPE | SOURCE | DESTINATION | SEQUENCE | +-------+----------+--------+-------------+----------+ | 10020 | 10000 | 10000 | 10010 | NULL | +-------+----------+--------+-------------+----------+ 1 row in set (0.00 sec) Link types are defined in issuelinktype. This query prints all links in the system with their type: mysql> select j1.pkey, issuelinktype.INWARD, j2.pkey from jiraissue j1, issuelink, issuelinktype, jiraissue j2 where j1.id=issuelink.SOURCE and j2.id=issuelink.DESTINATION and issuelinktype.id=issuelink.linktype; +-------+---------------------+-------+ | pkey | INWARD | pkey | +-------+---------------------+-------+ | TP-4 | jira_subtask_inward | TP-5 | | TP-4 | jira_subtask_inward | TP-7 | | TP-4 | jira_subtask_inward | TP-8 | | TP-11 | jira_subtask_inward | TP-12 | | TP-4 | jira_subtask_inward | TP-6 | | TP-1 | is duplicated by | TP-2 | +-------+---------------------+-------+ 6 rows in set (0.00 sec) SubtasksAs shown in the last query, JIRA records the issue-subtask relation as a link. The "subtask" link type is hidden in the user interface (indicated by the 'pstyle' value below), but visible in the database: mysql> select * from issuelinktype; +-------+-------------------+---------------------+----------------------+--------------+ | ID | LINKNAME | INWARD | OUTWARD | pstyle | +-------+-------------------+---------------------+----------------------+--------------+ | 10000 | Duplicate | is duplicated by | duplicates | NULL | | 10001 | jira_subtask_link | jira_subtask_inward | jira_subtask_outward | jira_subtask | +-------+-------------------+---------------------+----------------------+--------------+ 2 rows in set (0.00 sec) This means it is possible to convert an issue to a subtask, or vice-versa, by tweaking issuelink records. Custom fields have their own set of tables. For details, see Custom fields
Custom fieldsCustom fields defined in the system are stored in the customfield table, and instances of custom fields are stored in customfieldvalue: mysql> desc customfieldvalue; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | NO | PRI | | | | ISSUE | decimal(18,0) | YES | MUL | NULL | | | CUSTOMFIELD | decimal(18,0) | YES | | NULL | | | PARENTKEY | varchar(255) | YES | | NULL | | | STRINGVALUE | varchar(255) | YES | | NULL | | | NUMBERVALUE | decimal(18,6) | YES | | NULL | | | TEXTVALUE | longtext | YES | | NULL | | | DATEVALUE | datetime | YES | | NULL | | | VALUETYPE | varchar(255) | YES | | NULL | | +-------------+---------------+------+-----+---------+-------+ We can print all custom field values for an issue with: mysql> select * from customfieldvalue where issue=(select id from jiraissue where pkey='JRA-5448'); +-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+ | ID | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE | VALUETYPE | +-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+ | 23276 | 22160 | 10190 | NULL | NULL | NULL | NULL | 2004-12-07 17:25:58 | NULL | +-------+-------+-------------+-----------+-------------+-------------+-----------+---------------------+-----------+ and we can see what type of custom field this (10190) is with: mysql> select * from customfield where id=10190; +-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+ | ID | CUSTOMFIELDTYPEKEY | CUSTOMFIELDSEARCHERKEY | cfname | DESCRIPTION | defaultvalue | FIELDTYPE | PROJECT | ISSUETYPE | +-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+ | 10190 | com.atlassian.jira.ext.charting:resolutiondate | com.atlassian.jira.ext.charting:resolutiondatesearcher | Resolution Date | NULL | NULL | NULL | NULL | NULL | +-------+------------------------------------------------+--------------------------------------------------------+-----------------+-------------+--------------+-----------+---------+-----------+ (ie. it's a "Resolution Date"). This query identifies a particular custom field value in a particular issue: mysql> select stringvalue from customfieldvalue where customfield=(select id from customfield where cfname='Urgency') and issue=(select id from jiraissue where pkey='FOR-845'); +-------------+ | stringvalue | +-------------+ | Low | +-------------+ 1 row in set (0.33 sec) If the custom field has multiple values (multi-select or multi-user picker), each issue can have multiple customfieldvalue rows: mysql> select * from customfieldvalue where customfield=(select ID from customfield where cfname='MultiUser'); +-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+ | ID | ISSUE | CUSTOMFIELD | PARENTKEY | STRINGVALUE | NUMBERVALUE | TEXTVALUE | DATEVALUE | VALUETYPE | +-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+ | 10002 | 10060 | 10000 | NULL | bob | NULL | NULL | NULL | NULL | | 10003 | 10060 | 10000 | NULL | jeff | NULL | NULL | NULL | NULL | +-------+-------+-------------+-----------+-------------+-------------+-----------+-----------+-----------+ 2 rows in set (0.00 sec) Here issue 10060 has two users, bob and jeff in its MultiUser custom field. Custom field configuration optionsThe option sets (1, 2, 3 and A, B, C) are stored in the customfieldoption table: mysql> select * from customfieldoption where customfieldconfig=10031;
mysql> select * from customfieldoption where customfieldconfig=10032;
Custom field configuration default valueThe custom field default value is stored in the genericconfiguration table. Since this table must store a value for any custom field type (cascading selects, multi-selects, etc) the value is encoded as XML. If we were to set a default value of "2" for our "Default Configuration Scheme for SelectCF", it would be recorded as: mysql> select * from genericconfiguration where ID=10031;
Custom field configuration schemesJIRA custom fields can have different default values and possible values for each project and/or issue type. This is set up by clicking 'Configure' in the custom field definition. For instance, in this screenshot the "SelectCF" select-list field will have values 1, 2, 3 for all projects except bugs and improvements in "NewProj" and"Test Project", which will have values A, B and C: Custom field configuration scopesIn the database, these custom field configuration schemes are stored in the fieldconfigscheme table mysql> select * from fieldconfigscheme where id in (10031,10032);
The projects in scope for each of these schemes is listed as records (one per project) in the configurationcontext table: mysql> select * from configurationcontext where fieldconfigscheme=10031;
(Here showing that that the "Default Configuration Scheme for SelectCF" applies to all projects) mysql> select * from configurationcontext where fieldconfigscheme=10032;
(Here showing that "NewProj scheme" is restricted to projects with ids 10000 and 10010 ("Test Project" and "NewProj")). Finally, the issue types in scope for each scheme is listed as records (one per issue type) in the fieldconfigschemeissuetype table: mysql> select * from fieldconfigschemeissuetype where fieldconfigscheme = 10031;
(Here showing that "Default Configuration Scheme for SelectCF" is not limited to any issue types) mysql> select * from fieldconfigschemeissuetype where fieldconfigscheme = 10032;
(Here showing that "Newproj scheme" is limited to issue types with IDs 1 and 4). Note that there should always be a record in configurationcontext and fieldconfigschemeissuetype for each issue type configuration scheme. If the scheme isn't restricted to any projects or issue types, the project and issuetype columns of the respective tables should be NULL. Incidentally JIRA has/had a bug where it didn't leave an entry when deleting an issue type (JRA-10461), so if you are making changes manually, don't make the same mistake.
Change HistoryChange History Database TablesJIRA 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 recordsWhen 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 tableThe 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 recordsThe 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 APIThe 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 if 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.
Users and GroupsUser and Group TablesUser TablesJIRA uses an open source framework called OSUser to manage its users. The user records are stored in the userbase database table. The table holds little information: +---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | username | varchar(255) | YES | MUL | NULL | | | PASSWORD_HASH | varchar(255) | YES | | NULL | | +---------------+---------------+------+-----+---------+-------+ OSUser also stores some properties for the user. The properties are: full name and e-mail. These properties are stored in the propertyentry and propertystring tables. For each user the propertyentry table: +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | ENTITY_NAME | varchar(255) | YES | MUL | NULL | | | ENTITY_ID | decimal(18,0) | YES | | NULL | | | PROPERTY_KEY | varchar(255) | YES | | NULL | | | propertytype | decimal(9,0) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ stores 'OSUser' in the ENTITY_NAME column, the id of the userbase record in the ENTITY_ID column, and 'fullName' or 'email' in the PROPERTY_KEY column. The propertystring table: +---------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | propertyvalue | text | YES | | NULL | | +---------------+---------------+------+-----+---------+-------+ stores the actual values of the properties. The ID column is the same as the ID of the propertyentry record and the propertyvalue column would store the full name or e-mail of the user. Group TablesThe groups are stored in the groupbase table: +-----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | groupname | varchar(255) | YES | MUL | NULL | | +-----------+---------------+------+-----+---------+-------+ Group MembershipThe membershipbase table records which users belong to which groups: +------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------+------+-----+---------+-------+ | ID | decimal(18,0) | | PRI | 0 | | | USER_NAME | varchar(255) | YES | MUL | NULL | | | GROUP_NAME | varchar(255) | YES | MUL | NULL | | +------------+---------------+------+-----+---------+-------+ The USER_NAME column is set to the username column in the userbase table. The GROUP_NAME is set to the groupname record in the groupbase table. Watches and VotesWatches and votes are recorded in the userassociation table: +------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------+-------+ | SOURCE_NAME | varchar(60) | NO | PRI | | | | SINK_NODE_ID | decimal(18,0) | NO | PRI | | | | SINK_NODE_ENTITY | varchar(60) | NO | PRI | | | | ASSOCIATION_TYPE | varchar(60) | NO | PRI | | | | SEQUENCE | decimal(9,0) | YES | | NULL | | +------------------+---------------+------+-----+---------+-------+ For example: mysql> select * from userassociation; +---------------+--------------+------------------+------------------+----------+ | SOURCE_NAME | SINK_NODE_ID | SINK_NODE_ENTITY | ASSOCIATION_TYPE | SEQUENCE | +---------------+--------------+------------------+------------------+----------+ | asmith | 108433 | Issue | WatchIssue | NULL | | droberts | 100915 | Issue | WatchIssue | NULL | | dfernandez | 106387 | Issue | VoteIssue | NULL | ... For example, here user 'asmith' is watching issue with id 108433.
Issue status and workflowThis page describes the database tables involved in issue workflow. It will be useful for people who wish to insert issues into the database manually, or diagnose/fix corrupted databases.
Issue statusIn the database, the status (Open, Closed etc) is stored on the jiraissue table: mysql> select issuestatus from jiraissue where pkey='TP-1'; +-------------+ | issuestatus | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) mysql> select pname from issuestatus, jiraissue where issuestatus.id=jiraissue.issuestatus and pkey='TP-1'; +-------+ | pname | +-------+ | Open | +-------+ 1 row in set (0.00 sec) Issue workflow stepOriginally JIRA issues only had a status. Then in version 2.0, workflow was added, so that transitions between statuses could be customized. An issue's workflow step is stored in new tables, referenced from jiraissue by the workflow_id: mysql> select * from OS_WFENTRY where ID=(select workflow_id from jiraissue where pkey='TP-1'); +-------+------+-------------+-------+ | ID | NAME | INITIALIZED | STATE | +-------+------+-------------+-------+ | 10000 | jira | 0 | 1 | +-------+------+-------------+-------+ 1 row in set (0.02 sec) The TP-1 issue's OS_WFENTRY row indicates that the issue uses the 'jira' (default, built-in) workflow. mysql> select * from OS_CURRENTSTEP where ENTRY_ID=(select workflow_id from jiraissue where pkey='TP-1'); +-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+ | ID | ENTRY_ID | STEP_ID | ACTION_ID | OWNER | START_DATE | DUE_DATE | FINISH_DATE | STATUS | CALLER | +-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+ | 10000 | 10000 | 1 | 0 | | 2003-11-24 15:17:50 | | | Open | | +-------+----------+---------+-----------+-------+---------------------+----------+-------------+--------+--------+ 1 row in set (0.13 sec) The issue's OS_CURRENTSTEP row specifies the issue's current step. The only field really used is STEP_ID. This references a step definition in the workflow: <step id="1" name="Open">
How status and step relateAn issue's status and workflow step are kept in synch: mysql> select issuestatus.pname status, issuestatus, OS_CURRENTSTEP.STEP_ID, OS_CURRENTSTEP.STATUS from issuestatus, jiraissue, OS_CURRENTSTEP where issuestatus.id=jiraissue.issuestatus and jiraissue.workflow_id=OS_CURRENTSTEP.ENTRY_ID; +-------------+-------------+---------+----------+ | status | issuestatus | STEP_ID | STATUS | +-------------+-------------+---------+----------+ | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | ... | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | | Open | 1 | 1 | Open | | In Progress | 3 | 3 | Underway | | Closed | 6 | 6 | Closed | +-------------+-------------+---------+----------+ 32 rows in set (0.00 sec) mysql> Status and step are kept in synch is with a workflow post-function (UpdateIssueStatusFunction), which updates the status whenever the step changes. If the step gets out of synch with the status, then incorrect (or no) workflow operations appear on the issue page. Eg. if OS_CURRENTSTEP.STEP_ID was 6 ("Closed") when jiraissue.issuestatus was 1 ("Open"), then the issue would have only one transition ("Reopen issue") which would break if anyone clicked on it. Summary
Example SQLsSome examples of SQLs that can be run against the JIRA schema: |
![]() |
Document generated by Confluence on Oct 06, 2009 00:31 |