How to identify all project-scheme associations on the database
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Sometimes it might be helpful to check directly on the database what schemes are associated with a particular project on the database, either to validate for misconfiguration or perhaps to check how much a specific schema is shared among different projects.
In this article, you'll find queries to detect the following project-scheme associations:
- Custom Field contexts
- Field Layout Scheme
- Issue Type Scheme
- Issue Type Screen Scheme
- Notification Scheme
- Priority Scheme
- Workflow Scheme.
Solution
The project-scheme relationships are split into two main database tables, 'configurationcontext' & 'nodeassociation'.
The database queries below were written and tested on a Postgres database. Different databases may require slight syntax adjustments.
Configurationcontext table
The 'configurationcontext' table stores the 'Issue Type' and 'Priority' Schemas and 'Custom Field' Contexts.
To restrict the search to a single project, you may add a "WHERE project.pname = 'ProjectName'" clause to this query. Note that this column is case-sensitive.
SELECT
configurationcontext.PROJECT AS PROJECT_ID,
project.pname AS PROJECT_NAME,
fieldconfigscheme.id AS ISSUE_TYPE_SCHEME_ID,
fieldconfigscheme.configname AS ISSUE_TYPE_SCHEME
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;Nodeassociation table
For the remaining associations, such as 'FieldLayout', 'IssueTypeScreen', 'Notification', 'Permission', and 'Workflow' schemes, we must look into the 'nodeassociation' table.
To restrict the search to a single project, you may add "AND project.pname = 'ProjectName'" to the WHERE clause of this query. Note that this column is case-sensitive.
SELECT source_node_id, project.pname as Project_Name, source_node_entity, sink_node_id, sink_node_entity, association_type,
CASE
WHEN notificationscheme.name != '' THEN notificationscheme.name
WHEN fieldlayoutscheme.name != '' THEN fieldlayoutscheme.name
WHEN issuetypescreenscheme.name != '' THEN issuetypescreenscheme.name
WHEN permissionscheme.name != '' THEN permissionscheme.name
WHEN workflowscheme.name != '' THEN workflowscheme.name
END Scheme_Name
FROM nodeassociation
JOIN project ON nodeassociation.source_node_id = project.id
LEFT OUTER JOIN fieldlayoutscheme ON fieldlayoutscheme.id = nodeassociation.sink_node_id AND nodeassociation.sink_node_entity = 'FieldLayoutScheme'
LEFT OUTER JOIN issuetypescreenscheme ON issuetypescreenscheme.id = nodeassociation.sink_node_id AND nodeassociation.sink_node_entity = 'IssueTypeScreenScheme'
LEFT OUTER JOIN notificationscheme ON notificationscheme.id = nodeassociation.sink_node_id AND nodeassociation.sink_node_entity = 'NotificationScheme'
LEFT OUTER JOIN permissionscheme ON permissionscheme.id = nodeassociation.sink_node_id AND nodeassociation.sink_node_entity = 'PermissionScheme'
LEFT OUTER JOIN workflowscheme ON workflowscheme.id = nodeassociation.sink_node_id AND nodeassociation.sink_node_entity = 'WorkflowScheme'
WHERE source_node_entity='Project'
AND association_type='ProjectScheme'
ORDER BY source_node_id, sink_node_id;