Visualizing trends in the bug-tracker

At work I manage projects with Trac. It’s great. We have about ten different projects, including websites and internal web apps, with most of the code in a central Subversion repository hooked up to Trac so that it’s easy to see what got changed when (and why!).

But rather than bore you with Trac evangelism I’ll share a goofy hack I built to get a basic visualization of the ticket load in Trac over time.

This is possibly the weirdest little hack I’ve ever shared publicly.

First, the stats-gathering. The data lives in a text file; actually, a YAML file. I chose YAML because at first the idea of graphing was only a “might-do” and I wanted to have a text file I could read. The file is updated by a shell script like this running as a nightly cronjob:

echo '-'
echo '  date  : `date +'%Y-%m-%d`'
echo 'SELECT "  high  : " || count(*) FROM ticket WHERE \
  priority="high" AND status!="closed";' | sqlite3 /var/db/trac.db
echo 'SELECT "  medium: " || count(*) FROM ticket WHERE \
  priority="medium" AND status!="closed";' | sqlite3 /var/db/trac.db
echo 'SELECT "  low   : " || count(*) FROM ticket WHERE \
  priority="low" AND status!="closed";' | sqlite3 /var/db/trac.db

Next, the graphing code. Be thankful I didn’t go all the way to making this a one-liner.

import yaml, urllib
url = ""
data = yaml.load(urllib.urlopen(url).read())
print "\n".join("%s %s%s%s" % 
    (d['date'], "!"*d['high'], "*"*d['medium'], "."*d['low'])
    for d in data)

Finally, here’s what the output looks like – the three different characters in the bars represent high, medium, and low priority tickets respectively.

2008-01-09 !!!!!!!!!!************************...................
2008-01-10 !!!!!!!!************************........................
2008-01-11 !!!**************************.......................

Rendering sparkline graphs via the Google Chart API is left as an exercise for the reader.