How to find the commit revision details from Bamboo 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
The steps outlined on this article are provided AS-IS. This means we've had reports of them working for some customers — under certain circumstances — yet are not officially supported, nor can we guarantee they'll work for your specific scenario.
You may follow through and validate them on your own non-prod environments prior to production or fall back to supported alternatives if they don't work out.
We also invite you to reach out to our Community for matters that fall beyond Atlassian's scope of support!
This article provides Database SQL queries to help extract the commit revision details from Bamboo Database.
Environment
This has been tested on Bamboo 9.6.4 and PostgreSQL but should work on other supported Bamboo version and other DBMS types as well.
Solution
SQL query to extract the Author and commit revision details for a particular buildresult
1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT A.AUTHOR_NAME,
UC.*,
BRS.BUILD_KEY,
BRS.BUILD_NUMBER
FROM AUTHOR A,
USER_COMMIT UC,
REPOSITORY_CHANGESET RC,
BUILDRESULTSUMMARY BRS
WHERE A.AUTHOR_ID = UC.AUTHOR_ID
AND UC.REPOSITORY_CHANGESET_ID = RC.REPOSITORY_CHANGESET_ID
AND RC.BUILDRESULTSUMMARY_ID = BRS.BUILDRESULTSUMMARY_ID
AND BRS.BUILD_KEY = 'PLAN_KEY'
AND BRS.BUILD_NUMBER = BUILD_NUMBER; ℹ️ Please replace PLAN_KEY and BUILD_NUMBER with appropriate values
SQL query to identify the builds which were triggered for a particular commit revision
1
2
3
4
5
6
7
8
9
SELECT BRS.BUILD_KEY,
BRS.BUILD_NUMBER,
UC.*
FROM BUILDRESULTSUMMARY BRS,
REPOSITORY_CHANGESET RPC,
USER_COMMIT UC
WHERE UC.COMMIT_REVISION = 'b437a08a0a9e4ceae91edabbc195efefee09e3be'
AND BRS.BUILDRESULTSUMMARY_ID = RPC.BUILDRESULTSUMMARY_ID
AND UC.REPOSITORY_CHANGESET_ID = RPC.REPOSITORY_CHANGESET_ID ℹ️ Please replace the value of COMMIT_REVISION with appropriate values
SQL query to find all the commit history for a repository and the corresponding build details in Bamboo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT VC.NAME,
BRS.BUILD_KEY,
BRS.BUILD_NUMBER,
UC.*
FROM USER_COMMIT UC,
REPOSITORY_CHANGESET RC,
VCS_LOCATION VC,
BUILDRESULTSUMMARY BRS
WHERE RC.REPOSITORY_CHANGESET_ID = UC.REPOSITORY_CHANGESET_ID
AND RC.VCS_LOCATION_ID = VC. VCS_LOCATION_ID
AND RC.BUILDRESULTSUMMARY_ID = BRS.BUILDRESULTSUMMARY_ID
AND VC.NAME = 'REPOSITORY_NAME'
ORDER BY BRS.BUILD_KEY,
UC.COMMIT_DATE ℹ️Please replace REPOSITORY_NAME with appropriate values
Was this helpful?