Maximizing Efficiency: Querying JIRA for Confluence Links Made Easy

I had a question from one of our table grid editor customers that I wanted to share, as it’s valuable. 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 of 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.

Here’s how the content of the URL looks like:

mysql> select id,url from remotelink where applicationtype = 'com.atlassian.confluence' and issueid = 39733;
+-------+---------------------------------------------------------------+
| id    | url                                                           |
+-------+---------------------------------------------------------------+
| 15332 | https://wiki.idalko.com/pages/viewpage.action?pageId=46008203 |
+-------+---------------------------------------------------------------+
1 row in set (0.00 sec)

In this case, 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                    |
...

All good!

Next will be to join this with the confluence table, but that’s for later.

If you need any questions, feel free to contact us.