Willem Hoek

Why I created Scrumdog - a program to download Jira Issues to a local database

Jul 14, 2022

The Tower of Babel by Pieter Bruegel the Elder, 1563

Scrumdog is a software program I created to help me automate my Jira Issue reporting. Scrumdog downloads Jira Issues to a local SQLite database for analysis and reporting. It is a really helpful tool if you are managing a complex, multi party project with both Jira and non-Jira users and you report regularly to a Steering Committee.

You can download Scrumdog from here.

Table of Contents:

Why it was created
When to use / not to use Scrumdog
Design considerations
How it was created
Future improvements / enhancements

Getting info from Jira

Jira has many real-time dashboards and reports (“filters”) that can be used to manage Jira Issues. Jira also provides you with the ability to export issues in CSV, XML or Excel/Google Sheets format. However – if you find yourself (like me) downloading thousands of Jira issues regularly (e.g. daily) for reporting and analysis – it might be easier to just automate your steps.

The approach I took to automate Jira reporting consists of two steps:
Step 1 - Get the Jira Issues and save them in a local SQL database
Step 2 - Create Excel report(s) from local SQL database

In a previous post I showed how this can be done with Python. However to simplify the process further, Scrumdog can be used to automate Step 1 without you having to write any code.

When to use Scrumdog?

You have an overwhelming demand, up and down your organisation, for project status data
You want to automate your Jira Issue reporting
You are familiar with SQL e.g. basic SELECT statements
You want to use SQL to do more complex analysis on your Jira Issues, rather than be limited to JQL

When not to use Scrumdog

Existing Jira dashboards and reports (“filters”) give you exactly what you want
No need to automate Jira reporting because reporting demands and issues are manageable in Jira
You don’t know or want to use SQL

Design considerations:

The following was important to me as a user of Scrumdog:

MUST HAVE

No need for the user to code themselves
Local database must be SQLite, because:
- No additional software is required to use
- No maintenance required of database
- It is a file-based database so easy to backup/delete a database. Just copy/delete the database file
- Standard SQL can be used
Use standard Jira APIs to download the Jira Issue
Use JQL (Jira Query Language) to select issues to download
It should be easy run the program on any PC:
- With no installation required
- Just download and run single-file executable
Must be able to run program from the Command Line, so that it can be scheduled in batch mode to automate the download
Basic setup / configuration should be quick and simple to do
- The username, API key, JQL is listed in a simple text-based config file

IF POSSIBLE

Program should be available to use on Windows, Linux or Apple Mac machines
Use the Jira default field names but provide a way to override this is needed. Example for custom fields

Development of Scrumdog

Almost any general purpose programming language could be used to develop Scrumdog. The evaluation criteria I used to pick a suitable programming language:

Should enable fast development time
Must be able to create small standalone executable with no dependancies
Should be able to create executables for Windows, Linus and Mac
Fun factor (totally subjective)

I came up with the following shortlist of possible programming languages to use: Python, C#, Java, D, OCaml and Go. This list is based on my experience and requirements – yours will most definitely be different.

Python

Positives:

I know Python well
Vast user base - #1 in Tiobe index of the popular programming languages
Works on all major operating systems
Lots of libraries available

Negatives:

Not able to create single file executable without specialist tools

C#

Positives:

Big user base - #5 in Tiobe index
Corporate sponsor - Microsoft

Negatives:

Not easy/possible to create single-file executable for Linux and Mac

Java

Positives:

Big user base - #3 in Tiobe index
Works on all major operating systems

Negatives:

Not able to create small single file executables without specialist tools

Go

Positives:

Growing user base - #12 in Tiobe index
Can create single-file executable for Windows, Linux and Mac
Corporate sponsor - Google

Negatives:

I was not very familiar with Go, but it looked easy enough to learn

D

Positives:

Can create a single-file executable for Windows, Linux and Mac
Has the simplicity of Python but with speed of C/C++

Negatives:

Not a mainstream programming language, but great community and corporate sponsors

OCaml

Positives:

Can create a single-file executable for Windows, Linux and Mac
I have used OCaml for some hobby projects, so familiar with syntax, tools and libraries

Negatives:

Similar to D, also not a mainstream programming language
Some of the key libraries, e.g. “Jane Street Core” does not work on Windows

I excluded Python, C# and Java based on the negatives listed above and decided to create simple prototypes using D, OCaml and Go. The prototypes were 20-30 lines of code each to test the following:

Ability of program to perform an API call (https) to Jira
Ability to create, read and update some SQLite tables and records
Test that single-file executable is working on various Windows, Linux and Mac machines

After evaluating the prototypes, Go was excluded based on the following:

At a glance, the syntax of Go and D were similar and between the two I preferred the history and community of D over Go
The Go generated binary (.exe file) was also a lot bigger than the one created by D or OCaml

With OCaml and D left, I decided to proceed further with both. After a few hundred lines of code – I had the basics working for both the OCaml and D programs.

And the winner is …

At this point it was almost a coin-flip on which one to use, but I decided to continue with OCaml based on the following:

I felt OCaml forced me to keep my code structured and clean – which I appreciated
Development was slightly slower on OCaml but with a lot less debugging
Although I picked OCaml to develop Scrumdog, I will definetely re-visit D for future projects

After a few more days of developement and testing I had a working version of Scrumdog and was able to use it in my own automated reporting workflow.

Working with SQLite

You would need a tool to test SQL queries and manage the SQLite database files. I find the SQLite command-line shell program a bit too minamilistic to use. My prefered way to create and test SQL queries is with a (free) program called DB Browser for SQLite. With DB Browser for SQLite it is also possible to display the database table contents and/or edit data, which is very handy during testing.

Creating Excel reports from SQLite

Although not the focus of this post – you may want to create Excel reports from SQLite. Python has some great libraries to get you going very quickly. Here is an example of how to create a simple Excel report using data from SQLite.

import pandas as pd
import sqlite3

# Get data from SQLite db
con = sqlite3.connect("jira.db")
sql = "select * from zz_issues; "
df = pd.read_sql_query(sql, con)
con.close() 

# write to Excel
df.to_excel ("report.xlsx", index=False, header=True)

Future Improvements to Scrumdog

Some enhancements in the pipeline:

Execution speed is OK but can be improved if API calls are done concurrently (not serially)
Better management of database locking during updates
Include some basic Excel reporting options