How to extract build status of all the commits directly from the Bitbucket Data Center database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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.
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;