Database relationship between Project and Issue Type Scheme

[Joe Zangara]

I have been trying to fin this for a few hours and am stumped!

There is a post that was of great help for some of the project relationships, but not this one.

The TABLE that holds the Issue Type Schemes is fieldconfigscheme

But I can not find out how that table relates to the project table.

Thanks in advance for any assistance!

[Andy Heinzer]

Hi Joe,

If you are looking to better understand how the database of Jira is laid out, I would recommend taking a look at the Database schema.   While this does not contain the very latest versions of Jira, it does have a helpful diagram (in the pdf files) that explain the relationships between tables at least for the 7.0 version and earlier versions.  

It is possible that some of these details might have changed between 7.0 and the very latest versions of 7.7.x, but for the most part, I believe that the majority of the information there is still valid in the current versions.

Regards,

Andy

[Joe Zangara]

THANKS Andrew! 

That was one link I just had a hard time with.   I've been to that page a few times but must have downloaded the wrong pdf, because I never saw that VERY HELPFUL relationship diagram.

For Anyone Else looking for it:  The Query to show which projects are using which Issue Type Schemes is as follows:

SELECT 
    configurationcontext.PROJECT AS PROJECT_ID, 
    project.pname AS PROJECT_NAME, 
    fieldconfigscheme.configname AS [ISSUE TYPE SCHEME], 
    project.LEAD AS PROJECT_LEAD
FROM configurationcontext 
    INNER JOIN fieldconfigscheme ON configurationcontext.FIELDCONFIGSCHEME = fieldconfigscheme.ID 
    INNER JOIN project ON configurationcontext.PROJECT = project.ID
ORDER BY 
    PROJECT_NAME, 
    [ISSUE TYPE SCHEME]

[António José Figueiredo]

Hi Joe!

Hard time to figured out here too, but after i read all your posts, u solved my problem!

This last one worked for me. Thanks alot!

Regards,

António

[Damjan Koporec]

Thx Joe.... had the same issue and this post really helped.

Regards

Damjan

[Robin Powell]

Just a heads-up, that query shows all of the schemes associated with a project. Not a bad thing, but if you want only the issue type schemes, you need a where clause that narrows the scope of what's returned. Something like this (works on Postgres):

SELECT cc.project AS project_id, p.pname AS project_name, fcs.configname AS issue_type_scheme, p.lead AS project_lead
FROM configurationcontext cc
  INNER JOIN fieldconfigscheme fcs ON cc.fieldconfigscheme = fcs.id
  INNER JOIN project p ON cc.project = p.id
WHERE fcs.fieldid = 'issuetype'
ORDER BY project_name;

[Nic Brough -Adaptavist-]

Please, stop trying to read the Jira database.  It is a data store, not designed or intended for reporting (and you certainly can't use it for updates)

Can you tell us what you are trying to achieve in terms of what your users will gain?  Then we can tell you how to do it correctly, without SQL