How to run an SQL query to get data from JIRA Project ?

[Andrea Queralt]

Hi !

We need to do a query using a Join between two diferents Issuetypes linked then I should use the SQL query on JIRA.

I have generated the database scheme using the JIRA Diagram Scheme Generator plugin. But, I don't undestand which step should I execute to be able to run an SQL query and where can I run it ?

Please, I'll apreciate your help.

Thanks in advance.

[Stefan Arnold]

Iam using a groovy script in a transition postfunction to ask the database for the person that did a specific transition in the issue workflow. For that the might exist an api function too but writing the query and executing it was for me the fastest way.

import com.atlassian.jira.ComponentManager
import com.atlassian.jira.component.ComponentAccessor
import groovy.sql.Sql
import java.sql.Connection
import org.ofbiz.core.entity.ConnectionFactory
import org.ofbiz.core.entity.DelegatorInterface
import com.atlassian.jira.issue.Issue
import com.atlassian.jira.issue.MutableIssue;
import com.atlassian.jira.issue.ModifiedValue
import com.atlassian.jira.issue.util.DefaultIssueChangeHolder
import com.atlassian.jira.user.util.UserManager
import com.atlassian.jira.util.ImportUtils
//import com.atlassian.crowd.embedded.api.User



//Issue issue = issue
//def id = issue.getId()
ComponentManager componentManager = ComponentManager.getInstance()
def delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
String helperName = delegator.getGroupHelperName("default");


def sqlStmt = """
                SELECT a.author as 'doer' FROM changegroup as a JOIN changeitem as b ON b.groupid = a.id
                WHERE b.field = 'status' AND a.issueid = ${issue.id} AND b.oldstring = 'In Progress' AND b.newstring = 'Review'
                ORDER BY a.created DESC LIMIT 1
"""

Connection conn = ConnectionFactory.getConnection(helperName)
Sql sql = new Sql(conn)
try {
        StringBuffer sb = new StringBuffer()
        sql.eachRow(sqlStmt)
    {
        sb << it.doer
    }
    def userManager = (UserManager) ComponentAccessor.getUserManager()
    def user = userManager.getUserByName(sb.toString())
issue.setAssignee(user)


} finally {
sql.close()
}

To execute groovy scripts in transition postfunction you need the ScriptRunner plugin.
Hope you will get an idea how to do what you want to do.

[Nic Brough -Adaptavist-]

That method may not give you the right answer if a change was recent.

[Stefan Arnold]

mmh, so there is a delay between transitioning the issue and writing the change to database?

Isnt a problem for us cause there are a lot of review and stage testing status that take several days before that script is executed.

Do you have experience when things like changes, new issues etc are writing to database?

[Nic Brough -Adaptavist-]

You're probably alright then as you're giving it plenty of time.

People reporting via SQL and getting the wrong answer was the first thing that made me start looking into JIRA database usage, and very quickly lead me to "the database is always the worst option".  There are times when it's the only option, but it's always the worst.

[Andrea Queralt]

Thanks Nic!
For more clarity, we updated our previous question with the necessary details. Please see

https://community.atlassian.com/t5/JIRA-Core-questions/Get-the-EPICS-their-STORIES-and-thier-subtasks-for-these-stories/qaq-p/604703?posted=608122#U608122

Thanks in advance.

[Nic Brough -Adaptavist-]

Generally, running SQL against a JIRA database is the worst way to do any form of reporting.  It is not designed to be reported on.

Of course, the application has no way to get to the database either, as you shouldn't be doing it.

And, never, never, never, write to a JIRA database.

To run your SQL, you will need a database tool to connect to the database.

But I'd strongly recommend that you not bother, as it's very likely that you won't know enough about the database structure to actually get what you want from it.

Could you explain what question you're trying to answer?  Not "I think I want to use SQL", but what are you trying to find out about your JIRA system?

[Andrea Queralt]

Hi Nic,
Thanks for your answer.
I need to do an SQL query to get all the Stories with the Sprint greather than the parent Epics and the Task with their Sprint greather than the parent Stories in a JIRA project.
I found some choices but , based on Atlassian answers, it is not possible , and they have recommended me to use the Database Scheme.
Please, could you guide me to do it ?
Thanks in advance.

[Nic Brough -Adaptavist-]

I'm sorry, I can't work out what that means.

The main problem I have is understanding what "sprint greather than" mean?

For example you say: Stories with the Sprint greather than the parent Epics 

Does that mean

• Stories whose estimates add up to a number larger than the estimate on their parent Epic
• Stories whose planned sprint is planned to be done after the sprint the parent Epic is in
• Something else

There's further problems with all of the interpretations I can make, but I know I do not understand the question