Database schema

About the Jira database schema

The following PDFs show the database schemas for different Jira versions:

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 database tables, table columns, and their data types. Some of the relationships between tables also appear in the file.

Entity Engine and working with the Jira database

Jira uses the Entity Engine module of the OfBiz suite to communicate with the database. Learn more about Entity Engine

If you use Jira 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:

1
2
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 for the entity's definition in the WEB-INF/classes/entitydefs/entitymodel.xml file. For the above example, you need to look at the "Project" entity.

Notes about working with the Jira database

Direct database queries are not recommended in Jira. Instead, we recommend adding or modifying data via  Jira's REST APIs. Check the Command Line Interface for existing remote scripting tools. If you absolutely must modify data in your database via direct database queries, always back up your data before performing any modification to the database.

For a great way to watch Jira database queries in action, try adding SQL Logging.

Custom fields have their own set of tables. Learn more about storing custom fields in the database

Work logs

Work log entries are kept in the worklog table. For instance, some work logs in Jira (from JRA-10393) are stored in worklog table in the following way.

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

8333538315andreask@atlassian.com  Integrate new license types in JIRA.2008-01-22 23:51:23.799-06andreask@atlassian.com200  

worklogs

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.

Some of the relationships between Jira's tables in the database are documented in the following sections:

Rate this page: