Skip to end of metadata
Go to start of metadata

On this page:

About the JIRA database schema

The PDF below shows the database schema for JIRA 6.1 EAP 3 (m03).

 

The database schema is also described in WEB-INF/classes/entitydefs/entitymodel.xml in 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.

Generating JIRA database schema information

To generate schema information for the JIRA database, e.g. the PDF above, follow the instructions below. You can generate schema information in pdf, txt and dot formats. Note, if you want to generate the schema in PDF format, you need to have Graphviz installed.

  1. Download the attached plugin: jira-schema-diagram-generator-plugin-1.0.jar
  2. Install the plugin in your JIRA instance by following the instructions on Managing JIRA's Plugins.
  3. Go to the JIRA administration console and navigate to System > Troubleshooting and Support > Generate Schema Diagram
    (tick) Keyboard shortcut: g + g + start typing generate
  4. Enter the tables/columns to omit from the generated schema information, if desired.
  5. If you want to generate a pdf, enter the path to the Graphviz executable.
  6. Click Generate Schema.
  7. The 'Database Schema' page will be displayed with links to the schema file in txt, dot and pdf format.

Entity Engine and working with the JIRA database

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.

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:

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.

Notes about working with the JIRA database:

Relationships between tables

Some of the relationships between JIRA's tables in the database are documented below:

Issue Fields

This 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 fields

Most fields in JIRA are kept in the jiraissue table:

mysql> desc jiraissue;
+----------------------+---------------+------+-----+---------+-------+
| Field                | Type          | Null | Key | Default | Extra |
+----------------------+---------------+------+-----+---------+-------+
| ID                   | decimal(18,0) | NO   | PRI | NULL    |       |
| pkey                 | varchar(255)  | YES  | UNI | 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    |       |
| RESOLUTIONDATE       | datetime      | YES  |     | NULL    |       |
| VOTES                | decimal(18,0) | YES  |     | NULL    |       |
| WATCHES              | 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  | MUL | 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 details

Say we wish to find out the email address and other details about our reporter, mvleeuwen.

select user_name, directory_id, display_name, email_address
from cwd_user
where user_name = 'mvleeuwen';

Normally this should return a single row, however JIRA allows you to set up multiple user directories and it is possible that two or more directories contain the same username.

For more information about User and Group Tables see the Users and Groups section.

Components and versions

Since 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 |
+-------+---------+---------------+-------------+------+------+--------------+


JIRA issue links are stored in the issuelink table, which simply links the IDs of two issues together, and records the link type:

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:

Link types are defined in issuelinktype. This query prints all links in the system with their type:

Subtasks

As 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:

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 fields

Custom 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 options

The option sets (1, 2, 3 and A, B, C) are stored in the customfieldoption table:

mysql> select * from customfieldoption where customfieldconfig=10031;

ID

CUSTOMFIELD

CUSTOMFIELDCONFIG

PARENTOPTIONID

SEQUENCE

customvalue

optiontype

disabled

10000

10001

10031

NULL

0

1

NULL

false

10001

10001

10031

NULL

1

2

NULL

false

10002

10001

10031

NULL

2

3

NULL

false

mysql> select * from customfieldoption where customfieldconfig=10032;

ID

CUSTOMFIELD

CUSTOMFIELDCONFIG

PARENTOPTIONID

SEQUENCE

customvalue

optiontype

disabled

10003

10001

10032

NULL

0

A

NULL

false

10004

10001

10032

NULL

1

B

NULL

false

10005

10001

10032

NULL

2

C

NULL

false

Custom field configuration default value

The 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;

ID

DATATYPE

DATAKEY

XMLVALUE

10031

DefaultValue

10030

<string>2</string>

Custom field configuration schemes

JIRA 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 scopes

In the database, these custom field configuration schemes are stored in the fieldconfigscheme table

mysql> select * from fieldconfigscheme where id in (10031,10032);

ID

configname

DESCRIPTION

FIELDID

CUSTOMFIELD

10031

Default Configuration Scheme for SelectCF

Default configuration scheme generated by JIRA

customfield_10001

NULL

10032

NewProj scheme

 

customfield_10001

NULL

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;

ID

PROJECTCATEGORY

PROJECT

customfield

FIELDCONFIGSCHEME

10053

NULL

NULL

customfield_10001

10031

(Here showing that that the "Default Configuration Scheme for SelectCF" applies to all projects)

mysql> select * from configurationcontext where fieldconfigscheme=10032;

ID

PROJECTCATEGORY

PROJECT

customfield

FIELDCONFIGSCHEME

10054

NULL

10000

customfield_10001

10032

10055

NULL

10010

customfield_10001

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;

ID

ISSUETYPE

FIELDCONFIGSCHEME

FIELDCONFIGURATION

10051

NULL

10031

10031

(Here showing that "Default Configuration Scheme for SelectCF" is not limited to any issue types)

mysql> select * from fieldconfigschemeissuetype where fieldconfigscheme = 10032;

ID

ISSUETYPE

FIELDCONFIGSCHEME

FIELDCONFIGURATION

10052

1

10032

10032

10053

4

10032

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.

Notes:

  • 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. This bug was resolved in JIRA 3.11.

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).

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.

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 jiraissue table:

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

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.

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:

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.

Work logs

Work log entries are kept in the worklog table. For instance, some worklogs in JIRA (from JRA-10393):

are stored in worklog table as:

id

issueid

author

grouplevel

rolelevel

worklogbody

created

updateauthor

updated

timeworked

startdate

83332

38315

mtokar

 

 

Implemented method to calculate number of active users + tests

2008-01-22 19:44:04.867-06

mtokar

2008-01-22 19:44:04.867-06

5400

2008-01-22 19:43:00-06

83333

38315

andreask@atlassian.com

 

 

Implemented a method to check if the user limit of the license has been exceeded.

2008-01-22 21:33:18.23-06

andreask@atlassian.com

2008-01-22 21:33:18.23-06

7200

2008-01-22 21:31:00-06

83334

38315

andreask@atlassian.com

 

 

Added new license types

2008-01-22 23:49:27.794-06

andreask@atlassian.com

2008-01-22 23:51:06.029-06

7200

2008-01-22 23:48:00-06

83335

38315

andreask@atlassian.com

 

 

Integrate new license types in JIRA.

2008-01-22 23:51:23.799-06

andreask@atlassian.com

200

where:

  • issueid maps to jiraissue.id
  • timeworked is in seconds

Whenever a worklog entry is added, the jiraissue.timespent and jiraissue.timeestimate values are incremented and decremented respectively.

Users and Groups

Changed:

JIRA 4.3 and higher uses "Embedded Crowd" as its user management framework.
For the old user and group tables, see Database Schema v4.2.

User and Group Tables

Users

Users are stored in the CWD_USER table:

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

DIRECTORY_ID

NUMBER(18,0)

Links to CWD_DIRECTORY

USER_NAME

VARCHAR(255)

 

LOWER_USER_NAME

VARCHAR(255)

used for case-insensitive search

ACTIVE

NUMBER(9,0)

 

CREATED_DATE

DATE

 

UPDATED_DATE

DATE

 

FIRST_NAME

VARCHAR(255)

Not used

LOWER_FIRST_NAME

VARCHAR(255)

Not used

LAST_NAME

VARCHAR(255)

Not used

LOWER_LAST_NAME

VARCHAR(255)

Not used

DISPLAY_NAME

VARCHAR(255)

 

LOWER_DISPLAY_NAME

VARCHAR(255)

 

EMAIL_ADDRESS

VARCHAR(255)

 

LOWER_EMAIL_ADDRESS

VARCHAR(255)

 

CREDENTIAL

VARCHAR(255)

 

See also CWD_USER_ATTRIBUTES which stores arbitrary "Attributes" against the User.

Group Tables

The groups are stored in the CWD_GROUP table:

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

GROUP_NAME

VARCHAR(255)

 

LOWER_GROUP_NAME

VARCHAR(255)

used for case-insensitive search

ACTIVE

NUMBER(9,0)

 

LOCAL

NUMBER(9,0)

 

CREATED_DATE

DATE

 

UPDATED_DATE

DATE

 

DESCRIPTION

VARCHAR(255)

 

LOWER_DESCRIPTION

VARCHAR(255)

 

GROUP_TYPE

VARCHAR(60)

 

DIRECTORY_ID

NUMBER(18,0)

Links to CWD_DIRECTORY

See also CWD_GROUP_ATTRIBUTES which stores arbitrary "Attributes" against the Group.

Group Membership

The CWD_MEMBERSHIP table records which users belong to which groups.
Note that it is also used to store parent/child relationships for nested groups.

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

PARENT_ID

NUMBER(18,0)

Parent Group

CHILD_ID

NUMBER(18,0)

User or nested Group ID

MEMBERSHIP_TYPE

VARCHAR(60)

Indicates a Group-User membership or Group-Group membership

GROUP_TYPE

VARCHAR(60)

not used

PARENT_NAME

VARCHAR(255)

Parent Group

LOWER_PARENT_NAME

VARCHAR(255)

used for case-insensitive search

CHILD_NAME

VARCHAR(255)

User or child Group

LOWER_CHILD_NAME

VARCHAR(255)

used for case-insensitive search

DIRECTORY_ID

NUMBER(18,0)

Note that this must match the DirectoryId for the Group and User

User Directories

JIRA can have multiple "User Directories".
The main config is stored in CWD_DIRECTORY

COLUMN_NAME

DATA_TYPE

COMMENTS

ID

NUMBER(18,0)

 

DIRECTORY_NAME

VARCHAR(255)

 

LOWER_DIRECTORY_NAME

VARCHAR(255)

 

CREATED_DATE

DATE

 

UPDATED_DATE

DATE

 

ACTIVE

NUMBER(9,0)

 

DESCRIPTION

VARCHAR(255)

 

IMPL_CLASS

VARCHAR(255)

 

LOWER_IMPL_CLASS

VARCHAR(255)

 

DIRECTORY_TYPE

VARCHAR(60)

Distinguishes Internal, LDAP, Crowd, etc

DIRECTORY_POSITION

VARCHAR(18,0)

Hierarchy of directories

Details and custom settings are stored in CWD_DIRECTORY_ATTRIBUTE.
Available operations (permissions) are stored in CWD_DIRECTORY_OPERATION.

Shadowed Users

Consider a query like:

select user_name, directory_id, display_name, email_address
from cwd_user
where user_name = 'fred'

Normally this should return a single row, however JIRA allows you to set up multiple user directories (eg multiple LDAP directories, or a single LDAP directory mixed with local users).
It is possible that two or more directories contain the same username.
Now the User Directories have a sort hierarchy, and JIRA will only recognise the user in the highest priority directory.
To find which user is in effect, you can change the query to:

select user_name, directory_id, display_name, email_address, dir.directory_position as position
from cwd_user usr
join cwd_directory dir on dir.id = usr.directory_id
where user_name = 'fred'
order by dir.directory_position

The first user in the list is the actual one that JIRA will use.
Any other users are considered as "shadowed" by the first and will be ignored by JIRA.

Watches and Votes

Watches and votes are recorded in the USERASSOCIATION table:

COLUMN_NAME

DATA_TYPE

COMMENTS

SOURCE_NAME

VARCHAR(60)

username

SINK_NODE_ID

NUMBER(18,0)

 

SINK_NODE_ENTITY

VARCHAR(60)

 

ASSOCIATION_TYPE

VARCHAR(60)

 

SEQUENCE

NUMBER(9,0)

 

For example:

For example, here user 'asmith' is watching issue with id 108433.

Issue status and workflow

This 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.


JIRA issues have both:

  • a status (Open, Closed, In Progress etc).
  • a workflow step, which governs which transitions are available

Issue status

In the database, the status (Open, Closed etc) is stored on the jiraissue table:

Issue workflow step

Originally 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:

The TP-1 issue's OS_WFENTRY row indicates that the issue uses the 'jira' (default, built-in) workflow.

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:

Icon

The workflow definition for the built-in 'jira' workflow can be seen in atlassian-jira/WEB-INF/classes/jira-workflow.xml

How status and step relate

An issue's status and workflow step are kept in synch:

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 of issue status and workflow

  • For each jiraissue row, there is a OS_CURRENTSTEP and OS_WFENTRY row.
  • OS_WFENTRY specifies the applicable workflow. OS_CURRENTSTEP specifies the step in that workflow.
  • The relations are:
    • jiraissue.WORKFLOW_ID == OS_WFENTRY.ID
    • jiraissue.WORKFLOW_ID == OS_CURRENTSTEP.ENTRY_ID

 

 

 

36 Comments

  1. So... how do you get a list of issue types for a given project? It seems to be independently configured from the screen scheme.

      1. I should have added a "using sql" to my question. This is the Database Schema document, so that is how I would like to list the types.

        I see the api function IssueTypeSchemeManager.getIssueTypesForConfigScheme which seems to do what I want. I am attempting to translate that into sql queries, however it is not easy.

        1. Ok I figured it out:

  2. Anonymous

    QUERY to get usernames, emails, and full names.

    --

    select username, property_key, propertyvalue
    from userbase t1, propertyentry t2, propertystring t3
    where t1.id = t2.entity_id
    and t2.id = t3.id
    and t2.entity_name = 'OSUser'
    order by username

  3. Anonymous

    From the examples I see how easy is was to find out that the customfield in question is a 'resolutiondate' and it seems quite obvious that the cutomfieldvalue table should have the datevalue field populated, but how, in general, can one determine what value field is going to be populated for a given customfield (especially before there are any values recorded in the customfieldvalue table)?

    1. The column that is used is chosen by the Custom Field depending on the type of data they want to store and retrieve.
      If you have access to the source for the given Custom Field, then you would know.
      Alternatively you should normally be able to guess from the type of data that the field accepts.

      However, by far the easiest and most reliable way is to run a test instance and put some data in the field.

      Also note that some custom fields may store multiple "cutomfieldvalue" rows for a single Issue.
      This could be because it is a "multi-select" type field, or the "Cascading Select" field.

  4. Anonymous

    Query (revised): unique rows with the following columns <username, name, email>

    -----------------------

    select t1.username, t1.propertyvalue as name, t2.propertyvalue as email
    from (
        select username, property_key, propertyvalue
        from userbase t1, propertyentry t2, propertystring t3
        where t1.id = t2.entity_id
        and t2.id = t3.id
        and t2.entity_name = 'OSUser'
        and property_key = 'fullName'
        order by username
    ) t1,
    (
        select username, property_key, propertyvalue
        from userbase t1, propertyentry t2, propertystring t3
        where t1.id = t2.entity_id
        and t2.id = t3.id
        and t2.entity_name = 'OSUser'
        and property_key = 'email'
        order by username
    ) t2
    where t1.username = t2.username

    -MP927

    1. Under v4.3 this will be:

  5. Having spent a considerable amount of time muddling through the above and various other resources to figure out how I can create a crystal report, I've created a time saving list to make it easier for people to accomplish the same.

    If anyone needs a crystal report which displays Stories and Tasks and their respective subtasks, points totals and assignees, message me and I'll send it over. I originally designed it to work as a daily report for the Agile methodology.

    Required Tables
    jiraissue
    issuetype
    issuestatus
    issuelink
    issuelinktype
    nodeassociation
    project
    projectversion
    component
    customfieldvalue
    customfield

    How to join them
    project.ID --> jiraissue.PROJECT
    issuestatus.ID --> jiraissue.issuestatus
    customfield.ID --> customfieldvalue.CUSTOMFIELD
    customfieldvalue.ISSUE --> jiraissue.ID
    projectversion.ID --> nodeassociation.SINK_NODE_ID
    component.ID --> nodeassociation.SINK_NODE_ID
    component.project --> project.id
    nodeassociation.SOURCE_NODE_ID --> jiraissue.ID
    issuelinktype.ID --> issuelink.LINKTYPE
    issuelink.SOURCE --> jiraiisue.ID
    issuetype.ID --> jiraissue.issuetype

    1. Hello,

      As you propose I would be interested in your queries, but I do not know where to contact you. Could you may be post the sql ?

      Thanks

      Christophe

      1. I'm afraid I can't find the sql proper for you. However, I'll send over the file itself if you want. My email is now on my profile.

    2. Hello I'am interested by your proposal, in matter of fact you have helped me to figure some relation

      between tables  ,thank you very much

      The tables that I can't understand them until now are customfield ,customfieldvalue ,configurationcontext

      and the fields of nodeassociation : SINK_NODE_ID ,SINK_NODE_ENTITY


    3. nodeassociation.SOURCE_NODE_ID --> jiraissue.ID

      This is true only when nodeassociation.SOURCE_NODE_ENTITY = "Issue"

      The "entity" tells you what table the "ID" is for (for both sources and sinks).

  6. Anonymous

    Can anyone tell how to list all the fields system and custom from any particular workflow by sql query.

    Thanks 

    Nitin 

    1. Nitin,

      Workflows, as people think of them, are complex objects made up of multiple schemas as well as the jira "workflow" xml object which defines, among other things, the transitions and actions between states.

      Fields can be manipulated in the workflow as part of a transition, or they might not appear in the workflow at all if they're just part of the screen and/or field configuration schemas.

      If all you want to know is what fields are touched by the transitions of a workflow, then your best bet is to download the workflow as an xml file and read through that to find all the references to fields.

  7. Anonymous

    Hi Alex,

    I'm looking for data stored in the customfieldvalue table for all cutom fields which are present in my

    workflow.

    I have listed down the all the fields id's from screens.

    How to get the data ?

    Thanks,

    Nitin

    1. You may be better off to ask your question on the JIRA Development Forum

  8. Any way to get this kind of report :

    PROJECT

    PermissionScheme

    IssueTypeScreenScheme

    etc ...

    I was looking into the nodeassociation table, but couldn't find a way to get the whole project config on one line...

    the goal is to get a report on all my projects to verify that all projects of the same nature are configured the same.

    1. Good question. This is not simple.

      You could start with:

      You will need to add more joins for your "etc ..." (wink)

      Also not sure, but the configurationcontext and fieldconfigscheme may want more conditions on them like fieldid = 'issuetype' ?

      1. I studied the tables a little bit and ended with this query which gives me the result I wanted

        your query seems much cleaner, I will try it tommorrow.

        1. For SQL Server, this seems to do the trick:

  9. Anonymous

    Hi,

    Where can we get the Jira database schema with sample data for testing?

    -- Pradeep 

    1. Did you try http://confluence.atlassian.com/display/CONFEVAL/2.+Use+Cases ?

      This lists a number of different JIRA use cases with sample set up for each, including an XML/zip file for import.
      I haven't actually used these myself, so I am not sure how much of this is "data" vs "config".

  10. Hi,

    I guess section "User Details" is not up to date: table "userbase" in the database in empty...

    It may be usefull to have more documentation and examples about the new user management with tables "cwd_xxx"

    Alice

    1. Thanks Alice,

      I updated the "User details" subsection within Issue Fields.
      Please also see the "Users and Groups" section for more details on the new "cwd_xxx" tables.

  11. Anonymous

    Please post here or some there in Documentation DB Diagram.

    Indeed I was wonder when I was trying to find foreign keys in jiradb.... 

    1. JIRA doesn't use foreign keys in the database.
      Where it has referential integrity, this is enforced in the Application.

  12. Anonymous

    How do we get list of all users - username , email (project wise)

  13. Anonymous

    Dear Atlassians,

    is there a newer version of the Database schema?

    E.g. looking at jiraissue, the columns "fixfor" and "Component" are not included in the schema pdf (we have version 6.x) 

    Thanks in advance
    Dominik 

    1. You should be able to use a tool like http://schemaspy.sourceforge.net/ to create a diagram.  I'll see about getting updates to this page.

  14. Anonymous

    We recently upgraded to JIRA 6.1 and PKEY field is NULL in JiraIssues table. Was there a change made in 6.1?

    1. Yes indeed, see Preparing for JIRA 6.1. Did you come across this because you're using an SQL query on that table, or some other way?

  15. Anonymous

     

    Yes we are using SQL and have some custome reports.

    I already changed the queries to drive pkey from projectspkey and jiraissue.issunum.

    Thanks for the Info. I will take a look.

  16. Anonymous

    Jira developers that make small schema changes can save hundreds of people an hour each if you simply say you can replace

    select pkey from jiraissue

    with something like

    select project.pkey||'-'||case(jiraissue.issuenum as varchar) from jiraissue join project on project.id = jiraissue.project