BitBucket Database Tables for PullRequests and commits

[Илья Миртов]

Hi everybody!

I need to extract commits grouped by pull requests from our bitbucket database. So, I need name of the table where they  are linked. Can anybody help with that?

Thanks!

[Caterina Curti · Accepted solution]

Hi @Илья Миртов,

A better option would be to use the Bitbucket Server REST API for this purpose.

For example, you could use this one to list all the pull requests in a project/repository:

GET /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/pull-requests

And then the following one to get the commits for each pull request retrieved at the step above by using the pullRequestId:

GET /rest/api/1.0/projects/{projectKey}/repos/{repositorySlug}/pull-requests/{pullRequestId}/commits

The details of these end points are documented on the Core REST API. Note that the details may vary depending on the version currently installed.

Cheers,

Caterina - Atlassian

[Илья Миртов]

Hi @Caterina Curti,

Thanks for quick answer.

We have about 16 000 repositories and about 580 000 pull requests. Would you still suggest REST API over SQL query?

[Caterina Curti]

Hi @Илья Миртов,

Yes, using the REST API is still the recommended and supported way to proceed with this.

These are documented, maintained over time and follow a deprecation cycle to give you enough time to update the tools that use them if needed. In comparison, the database can change between any version (including minor versions) with no notice.

Considering the current numbers, and especially if this would be a recurring task, you can also consider the option to use the JAVA APIs or build a custom add on.

Cheers,

Caterina - Atlassian

[Craig Castle-Mead]

The sta_pull_request table holds the PRs - key fields for your needs are likely: from_repository_id, to_repository_id, from_hash and to_hash.

The below (tested in psql and bb 5.10.1) gives a list of projects + repos + pull requests (to that repo) ordered by the total number of PRs (regardless of state)

SELECT p.name, r.name, count(*) FROM project AS p, repository AS r, sta_pull_request AS pr WHERE p.id = r.project_id AND pr.to_repository_id = r.id  GROUP BY p.name, r.name ORDER BY count(*) DESC;