Database schema

About the Jira database schema

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

• Jira_7.9.2_schema.pdf
• Jira_7.0_schema.pdf
• Jira_6.1_schema.pdf
• Jira_5.12_chema.pdf
• Jira_8.13_schema.pdf
• Jira_8.20_schema.pdf
• Jira_9.0_schema.pdf

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 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, for example, like the previous PDFs, follow the instructions below. You can generate schema information in PDF, TXT, and DOT formats. Note that if you want to generate the schema in PDF format, you need to have Graphviz installed.

1. Download the attached app: 
   • For Jira 5: jira-schema-diagram-generator-plugin-1.0.jar
   • For Jira 6: jira-schema-diagram-generator-plugin-1.0.1.jar
   • For Jira 7: jira-schema-diagram-generator-plugin-1.1.0.jar
   • For Jira 8: jira-schema-diagram-generator-plugin-1.2.0.jar
   • For Jira 9: jira-schema-diagram-generator-plugin-1.2.1.jar
2. Install the app in your Jira instance by following the instructions on Managing Jira apps.
3. Go to the Jira administration console.
   1. Go to System > Troubleshooting and Support > Generate Schema Diagram
      Keyboard shortcut: g + g, and then start typing generate.
   2. Enter the tables or columns to omit from the generated schema information (optional).
   3. If you want to generate a PDF, enter the path to the Graphviz executable.
   4. Click Generate Schema.
   5. 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. To learn more, read the online documentation.

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

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

Custom fields have their own set of tables. For details, see Database – Custom fields.

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 in the following way.

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.

Related topics

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

• Change history
• Custom fields
• Issue fields
• Issue status and workflow
• User and Group tables