Today I was thinking that I'd like to have a way to include my list of Jira issues in my Debian packages so users can see that list if they'd like to know where we're at. (what's left to do)

Looking around, I found nothing of interest that said:

Extract Jira issues from the Command Line

So I tried with other types of searches and found a few things, but no real command line tool. Well... it looks like there is one from the Jira people but I'm afraid that's Java and I did not understand what the command could be (i.e. all I could see was a list of what looks like a long list of command line options, one of which was --extract-issues or something like that.

So... no real solution. Plus when you look how complicated it is to start a Java command when it's not setup by your installer (read... you don't have a Debian package you can just install) then it looks complicated.

Investigating a little more, I found a page that mentioned the idea of querying the database for something or other. That reminded me that indeed, Jira issues are in my PostgreSQL database. All I have to do is write a command which spits out the list of issues I'd like to share and voilà! I'm done.

I looked at the existing tables and the one I was interested is called jiraissues. Now all I had left to do is make sure only the Snap! Websites project issues would be output, only issues that aren't yet closed, and only issues that aren't marked as a security issue. (although really most of our security issues are not so high at this point because often the security part is already implemented.

Anyway, that still lives us with many issues to work on! Ooooh... And I can just use the psql command line to extract that information in the format I choose (i.e. it can be HTML, PDF, CSV, etc.)

Here is an example of SELECT that gives you the issue number, status, and summary (a.k.a. title). I order the results by increasing issue number.

SELECT issuenum, issuestatus.pname, summary
   FROM jiraissue, issuestatus
   WHERE project = 10000
     AND CAST(issuestatus AS integer) NOT IN (5, 6)
     AND issuestatus.id = jiraissue.issuestatus 
   ORDER BY issuenum;

The issuestatus is checked to avoid closed and resolved issues.

The project is checked because I only want issues that are for Snap! Websites.

The issuestatus table is used to get the name of each status instead of just a number. It makes it easier to read the results.