JIRA offers a lot of project reports to keep track of the project progress, analyze trends over the past few months, and make decisions based on various statistics on time estimates, status, and workload. In most cases, these reports are enough to reach conclusions, but there are times when desirable information cannot be fetched from the existing JIRA reports; however, it's possible to generate complex reports directly from the database. In this chapter, we will discuss common databases that JIRA can use and the schemas these databases use. We will take a look at some reports that can only be generated by querying the database directly.
JIRA stores its configuration and data in a database; if you are evaluating JIRA, then it's possible to use the embedded Hyper SQL Database (HSQLDB) written in Java. It's suitable for small applications and JIRA uses it only in its evaluation version. HSQLDB is not recommended for production usage. For that, JIRA recommends MySQL or PostgreSQL.
No matter what type of database is used, the database scheme, that is, the tables and the relationship between them is the same. If you want to take a look at the schema, then you can refer to JIRA_HOME/WEB-INF/classes/entitydefs/entitymodel.xml.
The contents of the file are as displayed in the following screenshot:
This is an XML file that contains the definition of all the tables in JIRA and its relationship with other tables.
Alternatively, you can also check the database schema on the Atlassian website at https://developer.atlassian.com/display/JIRADEV/Database+Schema.
As we just mentioned that HSQLDB is used only for evaluation purpose and it should not be used for production instances; you may however want to run queries to generate reports from the database. Luckily, HSQLDB comes with a built-in console that can be invoked by performing the following steps:
- Shut down your JIRA service.
- Use the following command to start the HSQLDB console:
java -cp JIRA_INSTALL/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:JIRA_HOME/database/jiradb
In the preceding command, replace JIRA_INSTALL and JIRA_HOME with the directory locations as per your installation. If you have installed JIRA using the Windows installer, then the following procedure should work.
Firstly, navigate to C:Program FilesAtlassianApplication DataJIRAdatabase directory.
Then, run the following command:
java -cp ../../../JIRA/lib/hsqldb-1.8.0.5.jar org.hsqldb.util.DatabaseManager -user sa -url jdbc:hsqldb:jiradb
The HSQLDB database manager will be displayed on your screen now:
You can now run SQL queries in the HSQLDB database manager and check the output too in the same window.