One of the coolest JIRA add-ons you could find in the market is LucidChart. LucidChart is an easy-to-use diagramming software. However, once you start using it in your JIRA, it stores diagrams in it’s own way.
Have you ever come across a requirement to find all issues in your JIRA instance that contains LucidChart diagrams? Well, I’m on JIRA 6.x and currently there’s no JQL function that I know to help with this. At least we have SQL – so JIRA administrator can look up if that’s really important.
So, here’s the fish. All you have to do is to set up a database client to access your JIRA’s database and fire up this query.
select distinct p.pkey || '-' || i.issuenum from propertyentry pe inner join fileattachment fa on pe.entity_id = fa.id inner join jiraissue i on fa.issueid = i.id inner join project p on i.project = p.id where pe.property_key = 'lucidchart.attachment.id';
Simple as that! Then, here’s how to fish.
There was no official documentation telling you how to do this. So how do we find the relation and database tables that contain LucidChart diagram data? Following the guesses is the answer.
First, I did a query like this to find if the word ‘lucidchart’ is mentioned anywhere in the database. In my case, the database was Oracle 11g.
select 'select ' || column_name || ', ''' || table_name || ''', ''' || column_name || ''' from ' || table_name || ' where lower(' || column_name || ') like ''%lucidchart%'' union ' from user_tab_columns where data_type = 'VARCHAR2' order by column_name asc;
It will produce something with many rows like the following:
select ACCESS_LEVEL, 'MANAGEDCONFIGURATIONITEM', 'ACCESS_LEVEL' from MANAGEDCONFIGURATIONITEM where lower(ACCESS_LEVEL) like '%lucidchart%' union
And once you get that, you can remove unwanted texts and build up a BIG SQL query with a lot of UNION statements. You will also have to cleanup it a bit because some SELECT statements contain Oracle reserved words. Just delete those lines.
Finally, execute and there are few interesting lines that read;
com.lucidchart.jira.plugins.oauth.secret PROPERTYENTRY PROPERTY_KEY
That appears to be what we are looking for..!
So just query the PROPERTYENTRY table, and there you will discover the relation between the PROPERTYENTRY.ENTITY_ID and FILEATTACHMENT.ID. There you go and violah!
And that’s how I found it out without any help.
Thanks for reading!