https://whoek.com/b/jira-to-sqlite-with-scrumdog.html Willem Hoek Why I created Scrumdog - a program to download Jira Issues to a local database Jul 14, 2022 [The_Tower_] 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 [jira-scrumdog] 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 before, 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" libraries do 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 was more familiar with OCaml - having used it before on other projects * I felt OCaml forced me to keep my code structured and clean - which I appreciated * 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. [db-browser] 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 Request for feedback Feel free to test out Scrumdog - which you can download for free from here. References and further reading [1] Scrumdog website https://scrumdog.app/ [2] DB Browser for SQLite https://sqlitebrowser.org/ [3] SQLite website https://www.sqlite.org/ [4] JIRA Cloud REST API documention https://developer.atlassian.com/cloud/jira/platform/rest/ [5] Post: using Python to automate your Jira reports https://whoek.com/b/use-jira-api-to-create-excel-reports [6] TIOBE Programming Community index https://www.tiobe.com/tiobe-index/ Related Posts Jane Street puzzle Feb 2021 SOLVED! OCaml to the rescue Solving the Jane Street puzzle of Dec 2020 - Backtracking with OCaml Automate your Jira reporting with Python and Excel Solving the Jane Street Puzzle of June 2020; Circle Time Solving the Jane Street Puzzle of May 2020; Expelled with OCaml Subscribe to get the latest content by email [ ] [ ] [Subscribe] (c) 2022 - Willem Hoek WebAnalytics