How to I write a SQL select to obtain 'Acceptance criteria' questions and answers?

[Andrew Goodright]

Hello!

I have a requirement to obtain the Acceptance Criteria (questions and answers) from the MySQL db which Jira uses.

I am not in a position to build a Jira system and get my answers that way. And the Jira system itself is not available.

My core problem is identifying the Tables used to support the entity.

I have identified the table 'JiraIssue' as the table to start from, but can not see which table(s) store the Acceptance Criteria values.

With many, many thanks in advance,

Andrew G

DBA

[Nic Brough -Adaptavist- · Accepted solution]

You're not going to enjoy the SQL needed to do this, Jira's database really is not designed for reporting and it is the worst possible way to get anything out of a Jira system.

The table you'll need is "customfieldvalue".  You can join that to jiraissue on jiraissue.id = customfieldvalue.issueid (from memory, field names might not be quite right).

If Acceptance Criteria is a text field, you'll find the content in one of the columns in there.  It is likely to be partly encoded in markup though, unless the field was plain text.  If it's not a text field, it gets more complex.

[Andrew Goodright]

Nic_Brough_Adaptavist_

Much _deep_ thanks for this. This confirms one of the approaches I tried, but rejected, earlier. So, I have the SQL to do this bit.

But, leads me to another question unfortunately (but the last question truly). And that is where are external links stored? My WIP indicates customfields, but weakly.

So:

select P.pname as 'Project:Name',

  JI.issuenum as 'Jira:ID',
  CFV_1.STRINGVALUE as 'Jira:external Jira',
  CFV_2.STRINGVALUE as 'Jira:Acceptance Critera', /* CI.newstring */
  JI.CREATED as 'Jira:Created',
  JI.summary as 'Jira:Summary',
  JI.description as 'Jira:Description',
  JI.REPORTER as 'Jira:Reporter',
  PR.pname as 'Jira:Priority',
  S.pname as 'Jira:Status',
  R.pname as 'Jira:Resolution',

  JA.UPDATED as 'Comment:Date',
  JA.UPDATEAUTHOR as 'Comment:Author',
  JA.actionbody as 'Comment:Text'

from project P
  join jiraissue JI on P.id=JI.project /* Core Joins */
  join jiraaction JA on JI.ID=JA.issueid

  left join priority PR on JI.PRIORITY=PR.ID /* Lookups */
  left join resolution R on JI.RESOLUTION= R.ID
  left join issuestatus S on JI.issuestatus=S.ID

  /* External link joins */
  left join customfieldvalue CFV_1 on JI.ID=CFV_1.issue and CFV_1.CUSTOMFIELD=10220

  /* Acceptance Criteria joins */
  left join customfieldvalue CFV_2 on JI.ID=CFV_2.issue and CFV_2.CUSTOMFIELD=11036
-- left join jiradb2.changegroup CG on CG.issueid=JI.ID
-- left join jiradb2.changeitem CI on CG.id=CI.groupid and CI.field = 'Acceptance Criteria'

Andrew G

DBA

[Nic Brough -Adaptavist-]

They're not fields.   Have a look in remotelink  (same principle in joining back to jiraissue)

[Andrew Goodright]

I think I have resolved the 'external link' requirement. It turns out a external link is not required. And what I really needed was to construct the Jira Issue ID, which appears not be crystallised on the JI table. What I need was construct the ID like so: concat(P.pkey,'-',JI.issuenum).

So, in summary I needed two bits of information:

* where to find the Tables&Columns which implemented the Acceptance Criteria feature

* how to construct the Jira Issue ID from the Tables&Column

I _think_ I have both now, so will press the 'Accept Answer' button. :)

Andrew G
DBA