nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

projects and agile boards stored in the database?

HaRsHaS' V

Which table connect the agile boards and projects in jira database?

What connects both these- I need to find the projects and corresponding agile boards

This gives the agile board details

SELECT [CARD_COLOR_STRATEGY]
,[ID]
,[KAN_PLAN_ENABLED]
,[NAME]
,[OWNER_USER_NAME]
,[SAVED_FILTER_ID]
,[SHOW_DAYS_IN_COLUMN]
,[SPRINTS_ENABLED]
,[SPRINT_MARKERS_MIGRATED]
,[SWIMLANE_STRATEGY]
,[OLD_DONE_ISSUES_CUTOFF]
,[SHOW_EPIC_AS_PANEL]
FROM [dbo].[AO_60DB71_RAPIDVIEW]
GO

This is for projects

SELECT [ID]
,[pname]
,[URL]
,[LEAD]
,[DESCRIPTION]
,[pkey]
,[pcounter]
,[ASSIGNEETYPE]
,[AVATAR]
,[ORIGINALKEY]
,[PROJECTTYPE]
FROM [dbo].[project]
GO

Accepted answers

Andy Heinzer

Hi,

I understand that you are taking a closer look at the SQL database for Jira Software server, and you are looking to better understand how these two tables connect to each other.

The short answer is that they do not. These SQL tables do not have a direct connection to each other. Agile Boards are not specifically bound to a single project.

The longer answer: When a board is created in Jira Software, you have to assign it a JQL filter. This is being referenced in the SAVED_FILTER_ID field in the table AO_60DB71_RAPIDVIEW. That JQL filter is what determines which Jira issues are expected to appear on that board. While many of these might default to a format such as

project=xyz order by rank desc

and in turn this will only show you issues from project XYZ, there is no requirement that this board use the project operator when searching JQL. Hence a board could be showing issues from multiple or all the projects potentially.

You can match the id value in "AO_60DB71_RAPIDVIEW"."SAVED_FILTER_ID" with the searchrequest.id field. That way you can at least understand better what JQL filter is being used on the board, and what the JQL search is there. The searchrequest.reqcontent will have the JQL syntax to determine what issues appear on that board.

But again, these projects don't directly connect to a board. Instead the projects contain Jira issues. And those issues are getting selected for display on a board based on the JQL filter. I hope this helps to explain the data structure here of the SQL database a bit better.

Please let me know if this helps, or if you have any followup concerns related to this.

Cheers,

Andy

I was also looking to try to do this to determine which of our users need a software license vs. a JSM license, since currently, they're all lumped together, but we need to separate them out.

From what I can figure, you'd need to somehow parse the project key(s) (or even name(s)) from the filters associated with boards, then, assuming you can get either all keys or all names, you could link the results to the project table.

There really ought to be an easier way of doing this, though.

You can start with this (MySQL) query to get the board related filters:

SELECT sr.ID as filterID, sr.filtername_lower, sr.reqcontent as filter, rv.ID as boardID,rv.name as boardName FROM searchrequest as sr
INNER JOIN AO_60DB71_RAPIDVIEW as rv ON sr.ID = rv.SAVED_FILTER_ID
WHERE sr.reqcontent like "%project%"

Good luck on the parsing part, though, if you have a lot of projects and/or filters!

This will not show any filters that cover all projects. You could possibly do a reverse of the above Where clause for that to get all where not like "%project%". That's if you wanted to separate them out.

I would really hope, though, that a board doesn't cover every single project you have in your instance, unless it's a very small instance. But, in that case, you probably wouldn't need to do a db search.

You could also leave off the Where clause altogether, and use something else to parse whether or not a board filter specifies the project.

It's gonna be tough any way you slice it.

All comments

Andy Heinzer

Hi,

I understand that you are taking a closer look at the SQL database for Jira Software server, and you are looking to better understand how these two tables connect to each other.

The short answer is that they do not. These SQL tables do not have a direct connection to each other. Agile Boards are not specifically bound to a single project.

project=xyz order by rank desc

Please let me know if this helps, or if you have any followup concerns related to this.

Cheers,

Andy

Ahmet Kilic

From DB Table I understood that very well. But which sql statement displays the boards under the proper project?

Once again, if you click on a project that have 5 boards. How do you get or with which relation do you achieve this?

My solution:

Connect board table with filter table

"AO_60DB71_RAPIDVIEW"."SAVED_FILTER_ID"=searchrequest.id

Connect filter table with project table

project.id=searchrequest.projectid

Put it all together

select
project.pname,
boards."NAME",
searchrequest.filtername,
searchrequest.projectid
from "AO_60DB71_RAPIDVIEW" as boards
join searchrequest on searchrequest.id=boards."SAVED_FILTER_ID"
left join project on project.id=searchrequest.projectid
where searchrequest.projectid is not null;

but no result

Rodolfo Romero - Adaptavist

Hi @Andy Heinzer,

Following up with the question from @Ahmet Kilic, how is determined the list of boards showing for a particular project. Is it base on the JQL? This list of boards is the one that shows under the name of the project in the Agile boards.

Thanks,

Rodolfo

Andy Heinzer

I have tried to explain before, boards are not specifically bound to a project. However in more recent versions of Jira Software, when you first visit a project, frequently you are being directed to an agile board that shows issues in that project.

Hovering over the top icon in the left navbar will show you a list of "Boards in this project". If this is where you are expecting to see a board, then only if the JQL filter of the board explicitly searches that project will the board using that filter appear here. So for example if the JQL filter does not contain a project such as something like

issuetype=bugs ORDER BY RANK ASC

Then there might be issues from that project that would appear in that board. But viewing a default board from a project landing page would not display this board as being "Boards in this project". So yes, the contents of the JQL filter do matter here. I do not know if there is a specific SQL query that can be used here to easily determine what appears there since projects can be referred to in JQL by either their project name, project key, or even project id.

Furthermore, the JQL filter itself has to be shared with other users or groups or project roles in order for other users to be able to see the board at all. By default new filters tend to only be visible by the user that created them. If the creator has not shared out the filter to be visible by other users, then no one else will be able to see that board anywhere within Jira.

I was also looking to try to do this to determine which of our users need a software license vs. a JSM license, since currently, they're all lumped together, but we need to separate them out.

There really ought to be an easier way of doing this, though.

You can start with this (MySQL) query to get the board related filters:

SELECT sr.ID as filterID, sr.filtername_lower, sr.reqcontent as filter, rv.ID as boardID,rv.name as boardName FROM searchrequest as sr
INNER JOIN AO_60DB71_RAPIDVIEW as rv ON sr.ID = rv.SAVED_FILTER_ID
WHERE sr.reqcontent like "%project%"

Good luck on the parsing part, though, if you have a lot of projects and/or filters!

You could also leave off the Where clause altogether, and use something else to parse whether or not a board filter specifies the project.

It's gonna be tough any way you slice it.

Chamdarig Dall

This solution works for dev jira H2 database:

SELECT
p.PKEY AS project_key,
rv.ID AS board_id,
rv.NAME AS board_name,
c.NAME AS column_name,
s.PNAME AS status_name
FROM AO_60DB71_RAPIDVIEW rv
JOIN SEARCHREQUEST sr ON rv.SAVED_FILTER_ID = sr.ID
JOIN PROJECT p ON LOWER(sr.REQCONTENT) LIKE CONCAT('%project = ', LOWER(p.PKEY), '%')
JOIN AO_60DB71_COLUMN c ON c.RAPID_VIEW_ID = rv.ID
JOIN AO_60DB71_COLUMNSTATUS cs ON cs.COLUMN_ID = c.ID
JOIN ISSUESTATUS s ON s.ID = cs.STATUS_ID
WHERE LOWER(p.PKEY) IN ('tbp', 'tes')
ORDER BY p.PKEY, rv.ID, c.POS, s.PNAME;

PROJECT_KEY BOARD_ID BOARD_NAME COLUMN_NAME STATUS_NAME
TBP 2 Next Board gh.boards.todo To Do
TBP 2 Next Board gh.boards.inprog In Progress
TBP 2 Next Board gh.boards.done Done
TES 1 TES board To Do On Hold
TES 1 TES board To Do To Do
TES 1 TES board In Progress In Progress
TES 1 TES board Done Done
(7 rows, 8 ms)

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

sql

jira

project

jira-software

jira-software-server

database