Bitbucket REST API to SQL Query conversion - SQL Table Details required

[Ramakrishnan Srinivasan]

Hi,

 I need help in getting SQL query matching a REST API call and its output. We have Tag Maker add-on installed in out Bitbucket Server instance (v5.15.0 version)

My Bitbucket REST API is

https://domain/bb/rest/api/1.0/projects/MYPROJECT/repos/xyz/tags/XYZ-C_3_5_17_0

The output is 

{"id":"refs/tags/XYZ-C_3_5_17_0","displayId":"XYZ-C_3_5_17_0","type":"TAG","latestCommit":"71f5cc3eb287bad159abb68bd695b7972f706f8a","latestChangeset":"71f5cc3eb287bad159abb68bd695b7972f706f8a","hash":"e784e7a9483594b72d200e15b77b56db61414526"}

In the output there is a hash value and latestCommit/latestChangeset, 

Can you please let me know how to get the SQL query which will map this hash to latestCommit/latestChangeset in the database? I could not find the table details which gives the above REST output.

Our requirement is to use Bob Swift SQL Pro add-on in confluence and pull some Bitbucket data as wiki reports. In specific, I am trying to get the Tags from any given Branch or vice versa.

Thank you in advance

with warm regards

ramki

[Amulya Akula {Appfire}]

Hi Ramakrishnan S,

With the help of Advanced table JSON macro from Advanced Tables for Confluence app you will be able to configure your requirement..

In this below scenario, we have tried to pull the Bitbucket report using the Advance table-Json macro and able to display the result as below.

Please refer to the steps:

1. In the Confluence page, add the Advance Table-Json macro and enter the details. Please refer to this link for more information.

URL of JSON data: Your Bitbucket repository url.
Username / Password: Bitbucket credential.

Under the Data Format tab, parse the values as per the screenshot.

2. Bitbucket data will be display in confluence page as shown below:

3. Please find below storage format of our test attached.

<p>
<br/>
</p>
<p>
<br/>
</p>
<ac:structured-macro ac:macro-id="ce5e0392-32ec-4e3c-846e-c11424ec8a80" ac:name="json-table" ac:schema-version="1">
<ac:parameter ac:name="password">automation</ac:parameter>
<ac:parameter ac:name="paths">values["0"],values["1"],values["2"],values["3"]</ac:parameter>
<ac:parameter ac:name="user">automation</ac:parameter>
<ac:parameter ac:name="url">http://Confluence-URL/projects/TRAIN/repos/repository_manisha1/branches</ac:parameter>
<ac:parameter ac:name="atlassian-macro-output-type">INLINE</ac:parameter>
</ac:structured-macro>
<p>
<br/>
</p>
<p>
<br/>
</p>

Let us know if you face any issues or have any questions.

Regards,
Amulya Akula

[Ramakrishnan Srinivasan]

Hi Amulya Akula,

 Thank you, I am familiar getting the json data using the REST API, the request was for equivalent SQL query so that we can use either Script Runner or SQL Pro add-ons to prepare the report we wanted. So really looking for relation between lastestCommit and hash for a tag through the REST API mentioned in my post

with warm regards

ramki