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