How to extract build status of all the commits directly from the Bitbucket Data Center database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Bitbucket provides a mechanism for a build server to store the status of the build related to a commit using a REST endpoint. Whilst the REST API allows the developer to store and retrieve the build status related to single or multiple specified commits there is no API to extract the build status related to all commits. This knowledge-base article describes one method you can use to retrieve the build status of all commits via running a SQL query on the Bitbucket database.
Solution
Bitbucket will store an entry of the build status, that has been posted to it by the build engine in the bb_build_status table. The following SQL will extract the entries from the table and list the commit, build status, build plan, date and the corresponding repository.
The below query has been developed on PostgreSQL and may require some tweaking depending on the database engine that Bitbucket is using.
1
2
3
4
5
6
7
select bs.commit_id commit, bs.state state, bs.build_key build_key, bs.build_number build_number , bs.updated_date build_date, p.name project, r.name repository from bb_build_status bs
join (
select commit_id, max(updated_date) latest
from bb_build_status group by commit_id
) l on bs.commit_id =l.commit_id and bs.updated_date = l.latest
join repository r on r.id = bs.repository_id
join project p on p.id= r.project_id;Was this helpful?