I need to export pull requests with issues from Bitbucket database. Which table contains issue_title or issue_id and how to link it with pr?
Thank you!
Hi @RacoonRocket,
The Jira issue keys mentioned in commit messages get stored in the cs_attribute table, and pull requests are stored in the sta_pull_request table. Very likely, you'll want to know which repository that pull request belongs to, and which project that repository is associated to.
If a pull request is created for a changeset that has a Jira issue key mentioned in its commit message, this SQL query might help you retrieving all this information above:
SELECT
project.name AS "PROJ_NAME",
project.project_key AS "PROJ_KEY",
repository.name AS "REPO_NAME",
repository.slug AS "REPO_SLUG",
sta_pull_request.id AS "PR_ID",
sta_pull_request.title AS "PR_TITLE",
cs_attribute.att_value AS "JIRA_KEY"
FROM
sta_pull_request
INNER JOIN repository ON (sta_pull_request.from_repository_id = repository.id) OR (sta_pull_request.to_repository_id = repository.id)
INNER JOIN project ON (repository.project_id = project.id)
INNER JOIN cs_attribute ON (sta_pull_request.from_hash = cs_attribute.cs_id) OR (sta_pull_request.to_hash = cs_attribute.cs_id)
WHERE cs_attribute.att_name = 'jira-key';
This was the resultset I got in a local instance which has only one pull request:
PROJ_NAME PROJ_KEY REPO_NAME REPO_SLUG PR_ID PR_TITLE JIRA_KEY
--------- -------- --------- --------- ----- ------------------ --------
Project PROJ Git git 1 Pull Request Title TST-999
I hope this helps!
Felipe
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.