How to get all Bitbucket Projects/Repos linked to Jira Projects/Issues

[Mehmet Sari]

I needed to know which bitbucket projects are linked to which Jira projects.

i could not find any answer so i am writing this question and also answering it myself and maybe it helps someone.

In Jira the infos are capsuled in the database table AO_<HEX>_DEV_SUMMARY in the column "json". extracting the infos on jira issues with bitbucket links is really hard this way.

if you have a look into bitbucket database there is the table "AO_777666_JIRA_INDEX" with all the infos you need:

1. ISSUE -> contains all Jira issue-keys with links in bitbucket

2. Repository -> contains repository-id for bitbucket repo

-> now join with table repository and one more time with project and you can extract jira tickets to bitbucket projects.q

Just use following SQL if you have Postgresql:

SELECT distinct split_part(JI."ISSUE",'-', 1) AS BITBUCKETPROJECT, PRO.PROJECT_KEY AS JIRAPROJECT FROM "AO_777666_JIRA_INDEX" AS JI

INNER JOIN repository REP ON JI."REPOSITORY" = REP.ID

INNER JOIN project PRO ON REP.PROJECT_ID = PRO.id

or you can use following SQL for any other database and afterwards you have to do a splitstring for the jira issueskey to extract the project keys:

SELECT JI."ISSUE" AS BITBUCKETPROJECT, PRO.PROJECT_KEY AS JIRAPROJECT FROM "AO_777666_JIRA_INDEX" AS JI

INNER JOIN repository REP ON JI."REPOSITORY" = REP.ID

INNER JOIN project PRO ON REP.PROJECT_ID = PRO.id