How do I make SQL queries in JIRA?

[Matt H]

I am new to developing in JIRA and do not know where to start on making SQL queries to the database in JIRA. I'm trying to query all Issues that have a particular custom field, and then see if each of those issues contains a number of different strings for those Issues that do have the custom field. Can anyone point me in the right direction? 

[Nic Brough -Adaptavist-]

Really, don't bother.  The JIRA database is built for data storage, not reporting or querying. 

As an example, take a simple custom field - the SQL for answering "What components are on ABC-123" involves joining at least six tables together.  Or for "what colour are the issues in that project? (colour being a custom field)" - you're looking at 7 to 10 depending on the type of field.

The database is abstracted from the application and it's far better to ignore it and use the API to make queries, especially as the API provides access to JQL - that gives you a list of issues, and then you can run that through a display of some sort.

[crf]

+1.

Add to this the fact that the Java API Policy for JIRA does not specify anything at all about the datamodel.  We do not consider the database layout to be part of the API, and even bugfix releases could break anything that you did.

When you're talking about a tiny custom plugin whose original author is still with the company, this might be something you can live with, but such things tend to grow out of control and become fragile over time.  Life is easier for all of us when you stick with what we formally support.

[Petar Petrov (Appfire)]

+1.

All issue data is indexed using Apache Lucene so a JQL search will be much faster than querying the database.

[Deleted user]

This might help you get started;

https://confluence.atlassian.com/display/JIRA041/Example+SQL+queries+for+JIRA

[crf]

Honestly, from what you've expressed in this question, searching with JQL would seem to make a lot more sense. Here are a couple write-ups about it:

• Advanced Searching
• JQL: The Most Flexible Way to Search JIRA (1/4)

And you can run JQL from code, as well:

• Over HTTP[S] using the REST API
• Or directly in Java from a P2 plugin

An example that I think is similar to what you were asking for might be this query on jira.atlassian.com:

resolution IS EMPTY AND "Bugmaster Rank" IS NOT EMPTY AND (text ~ "test" OR text ~ "hello") ORDER BY key

https://jira.atlassian.com/issues?jql=resolution IS EMPTY AND "Bugmaster Rank" IS NOT EMPTY AND (text ~ "test" OR text ~ "hello") ORDER BY key

[Joe Pitt]

Just don't update the database outside of JIRA or the API. You'll break JIRA

[Matt H]

Yeah I've come across that a few places but still haven't been able to find a likely quite basic explanation of how to get started with SQL queries in JIRA, do you know of any tutorials or resources?