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.
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
The following was important to me as a user of Scrumdog:
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
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.
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
Not able to create single file executable without specialist tools
Can create single-file executable for Windows, Linux and Mac
Corporate sponsor - Google
I was not very familiar with Go, but it looked easy enough to learn
Can create a single-file executable for Windows, Linux and Mac
Has the simplicity of Python but with speed of C/C++
Not a mainstream programming language, but great community and corporate sponsors
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
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.
importpandasaspdimportsqlite3# 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
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
Request for feedback
Feel free to test out Scrumdog – which you can download for free from here.