How can one query JIRA for links with confluence


bulb-electricity-energy-glass-45227This morning I had a question from one of our table grid editor customers. They have a nice use case where they wanted to list all comments of a confluence page related to an issue, in the issue itself. The table grid editor allows to query any relational database so the next question was where the links have been stored.

After sniffing around in the database structure, I found out that the confluence links are stored in the remotelink table:

mysql> select applicationtype from remotelink group by applicationtype;
| applicationtype          |
| NULL                     |
| com.atlassian.bamboo     |
| com.atlassian.confluence |
| com.atlassian.jira       |
| com.exalate.jiranode     |
| legacy-trackbacks        |
| net.issuehub.jiranode    |
7 rows in set (0.00 sec)

The next question is of course how to extract the pageid from the confluence page linked to the issue.
Have a look at the content of the URL:

mysql> select id,url from remotelink where applicationtype = 'com.atlassian.confluence' and issueid = 39733;
| id    | url                                                           |
| 15332 | |
1 row in set (0.00 sec)


So the pageid is there – but is it always?
Testing if there are any URL’s without the pageid:

mysql> select * from remotelink where applicationtype = 'com.atlassian.confluence' and url not like '%pageId%';
Empty set (0.00 sec)


Empty set – ok, nice.

So retrieving the pageid – using mysql is something like:

mysql> select substring_index(url,"=",-1) from remotelink where applicationtype = 'com.atlassian.confluence' and url like '%pageId=%';
| substring_index(url,"=",-1) |
| 16779315                    |

Now, I need to join this with the confluence table, but that’s for later.