How to join issues and pull requests in select?

[RacoonRocket]

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!

[Felipe Kraemer · Accepted solution]

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

[RacoonRocket]

@Felipe Kraemer, thank you so much! Your select is very helpful. It really works.