326779 - Burndown chart for Bugzilla issues and their dependencies

Status: NEW

(Bugzilla :: Reporting/Charting, enhancement)

Description

[Knut Forkalsrud]

User-Agent:       Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050922 Fedora/1.0.7-1.1.fc4 Firefox/1.0.7
Build Identifier: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050922 Fedora/1.0.7-1.1.fc4 Firefox/1.0.7

I have been working on an implementation of a burndown chart for
the dependencies of one or more bugzilla issues.  Agile development
methodologies often use burndow charts to track (lack of) progress
in projects.  I'll get back to examples further down.  The theory
is that most serious users of Bugzilla use the dependency feature
to link differnet bugs/tasks/features together such that they all
block one meta-bug that serves the purpose of a name tag on the
project/iteration.  The dependeyncy tree feature gives a fairly
good day-to-day overview of what is outstanding.  Given the time
tracking features one can also easily represent the estimated
effort remaining on each bugfix/fature/task.  The information is
already tracked in the bug_activity table.  It is just a matter
of pulling the numbers together to provide reports describing how
the scope/progress has changed over time.

I will attach some code that generates a burndown chart based on
one or more bug_ids.  The calculations are relatively simple:

Each bug has some remaining effort associated with it.  As this
changes we get a remaining effort function (of time) for each bug.
The status of the bug gives the domain of the function, when the
bug is CLOSED there is zero remaining effort.

When bug B blocks bug A the total remaining effort is the sum of
the remaining effort on the two bugs.  When bug B no longer blocks
bug A the total effort no longer includes the effort for bug B.
This applies recursively, as far as the dependency tree goes.

The code attached must be considered pre-beta quality.  I have
very limited command of Perl, and even less insight into the
various bugzilla internals.  Therfore be warned, the code goes
directly to the database tables, witout considering any other
APIs if they exist.  I have no clue on the charting component
mentioned elsewhere in the bugzilla sources, so I do the simplest
thing I know, just write the X/Y values to gnuplot the create a
quick/dirty chart.  I'm sure it can be done better, but I would
have a very hard time even getting started.  Also some of the
Perl constructs are most likely laughable to intermediate or
fluent Perl coders.  All in all it is about 400 lines comments
and all, in the form of a CGI script.

I would be happy to contribute this piece of code if anyone sees
any use for it.  Thanks for your consideration.

Reproducible: Always

Steps to Reproduce:
N/A
Actual Results:  
N/A

Expected Results:  
N/A

N/A

Comment 1

[Knut Forkalsrud]

Attachment: burndown.pl, CGI script to produce the burndown chart

perl -c burndown.pl
burndown.pl syntax OK

Comment 2

[Knut Forkalsrud]

Attachment: Example burndown chart

Here's an example burndown chart from a bug with another bug blocking it.
Of course whether the remaining effort comes from bug A or bug B cannot
be told from the burndown chart.
I reduced the remaining time frequently over about 15 minutes to illustrate
progress.

Comment 3

[Knut Forkalsrud]

Comment on attachment 211467 [details]
burndown.pl, CGI script to produce the burndown chart

I'm sure there is too much wrong with this one to bother pointing out each detail.  But it would be nice if someone could take a quick look and point out if I'm completely off.  I'm going to try my best to make it fit better into the Bugzilla ways, but bear with my rather limited Perl skills.  Thanks in advance.

Comment 4

[Marc Schumann [:Wurblzap]]

See also bug 235059 and bug 223071.

Comment 5

[Vlad Dascalu]

Comment on attachment 211467 [details]
burndown.pl, CGI script to produce the burndown chart

Hi, and welcome to Bugzilla hacking! I recommend reading http://www.bugzilla.org/docs/developer.html to get you up to speed, it's a pretty interesting and useful resource for Bugzilla wanna-be hackers :)

I appologise for the delay, we are still slow nowadays (some patches even wait for months!)

This looks great, although it somehow overlaps with the timetracking feature (you can enable it from the edit params screen).

However the graph looks nice and it would be interesting to have.

Basically at this point there are two pathways: 

1) we can rather have this integrated into the Bugzilla code-base (with the picture link next to the dependency fields), but this will probably require you having a 'tick skin' and bear with us in order to produce version 2 of the patch (and maybe version 3 and so on, until it's ready to commit :) ),

or:

2) we can have it commited in the /contrib/ directory. We have a 'contrib' directory for scripts and stuff that haven't found a way into the code-base, but the code can still be regarded as a stand-alone utility that does a nice thing :)

It would be cool if we could manage 1 -- if you want 2), or at any time you feel like going for it, let us know so we can commit it to /contrib/.

Now, assuming that we want this integrated, it looks very nice! Here are the points that I've noticed:

my $dbh = DBI->connect("DBI:mysql:bugs;host=bugzilla.test.host","bug_user","*****") or die $DBI::errstr;

This one is very easy, you need 'use Bugzilla;' at the beginning of the script, and then you can do 'my $dbh = Bugzilla->dbh'.

open(GP, "|gnuplot") or die "open(gnuplot): $!";

Gnuplot is nice, especially since it's portable on Windows. However we are already having as a Perl package dependency GD. It would be very nice if you could modify it to use GD, since it would avoid adding as a dependency gnuplot.

my $q = new CGI;

We're actually doing 'my $cgi = Bugzilla->cgi;'

my $sth_bug_info= $dbh->prepare("
select
  bug_status,
  creation_ts,
  remaining_time
from bugs
where bug_id = ?
") or die $DBI::errstr;

MySQL caches some queries and stuff, but then it compares them and reuses them only if they have the same case. So we have a coding convension for SQL statements: SELECT, FROM, WHERE must be capitalized, the others don't.

# Connect to the bug database and prepare the two queries we use
my $dbh = DBI->connect("DBI:mysql:bugs;host=bugzilla.test.host","bug_user","*****") or die $DBI::errstr;

You don't need this one since the one returned by Bugzilla->dbh is already connected.

my $sth_bug_status_hist = $dbh->prepare("
select bug_when, fieldid, added, removed
from bugs_activity
where bug_id = ?
and fieldid in (9, 21, 40)
order by bug_when
") or die $DBI::errstr;

Same nit about capitalization. We support both MySQL and PgSQL. You can actually write portable SQL queries using Bugzilla::DB, you can take a look in /Bugzilla/DB/ in order to see if you can take from there some useful methods.

I guess that's it for now, again -- really nice! :) Thanks for writing it.

Comment 6

[Frédéric Buclin]

Note that field IDs and DB names and such must not be hardcoded. This requires extra work to be DB independent.

Comment 7

[Douglas Calvert]

I vote for trying to get it included in the bugzilla, proper (AKA not contrib). I can help test it if you are interested in making the changes and want to work towards gettting it included...

Comment 8

[Knut Forkalsrud]

Attachment: burndown.cgi, version two using GD::Graph

Thanks for all the help.  I have tried to address most of the
comments.

1: I tried not to overlap with the timetracking feature,
   but to complement it.  From what I can tell the
   functionality in summarize_time.cgi is different.

2: Whether to try to get this into Bugzilla proper or to
   submit it to /contrib/.  I have no strong preference,
   but I'll do my best to help get it integrated.

3: DB queries et cetera.  I think I have most of the
   non-portable DB code out now, but I'm unsure how to
   avoid referencing fieldid 9, 21, 39 literally. I haven't
   found the example to go by to replace those numbers with
   symbols.

4: GD instead of gnuplot.  I'm not entirely sure about GD.
   Somehow I got it to work, but the graph output looks
   pretty sad compared to the gnuplot equivalent.  Somehow
   it looks like GD::Graph is dropping most of the samples.
   This may be something I got wrong, but the manual page
   mentions a weakness in GD::Graph and numeric X-axes:

http://crs.ciril.fr/public/docs/perl/GD/Graph.html#options_for_graphs_with_a_numerical_x_axis

       "First of all: GD::Graph does not support numerical x axis
       the way it should. Data for X axes should be equally spaced.
       That understood: There is some support to make the printing
       of graphs with numerical X axis values a bit better, thanks
       to Scott Prahl. If the option x_tick_number is set to a
       defined value, GD::Graph will attempt to treat the X data
       as numerical."

   I'll attach example output to illustrate.

Comment 9

[Knut Forkalsrud]

Attachment: Example burndown chart rendered using gnuplot

This one looks OK.

Comment 10

[Knut Forkalsrud]

Attachment: Example burndown chart rendered using GD::Graph

This is the same data set rendered with GD::Graph.
It doesn't look nearly as nice.

Comment 11

[Knut Forkalsrud]

Douglas (or anyone else for that matter), feel free to
fix/mangle/improve the code as you wish.  I won't feel
offended if you rip it completely apart.

What I like the least about the code as it stands today
is the quasi object oriented stuff in $domain_handler,
$remaining_handler and $deps_handler.  I know there is
better support for this kind of thing in Perl, but I have
a hard time working with even the simplest Perl constructs,
so I will have to spend way too much time to try to fix
it.

Also I'm sure I duplicated the subroutine "IsOpenedState"
from globals.pl in my "status2active" subroutine, but I
could not get the code to run with 'require "globals.pl";'.

I also tried to add 'T' in the first line:
   #!/usr/bin/perl -wT
That also caused errors I couldn't figure out how to resolve.

Comment 12

[Vlad Dascalu]

Comment on attachment 214028 [details]
burndown.cgi, version two using GD::Graph

I've requested review on this in order to keep track on it and prevent it from getting lost in the jungle.

Of course this shouldn't prevent to upload/modify/create new code (while the review? is pending...)

Comment 13

[Vlad Dascalu]

Comment on attachment 214028 [details]
burndown.cgi, version two using GD::Graph

This looks cool and better!

> and fieldid in (9, 21, 39)

AND, IN should be capitalized because they are SQL statements (per our guide).

> order by bug_when

Same for ORDER BY.

+  close GP;
+} else {
+  # Print the resulting step function to the plotting component

All this should get the 4-space identation style (we're using 2-space style for HTML or other formats, but Perl code is 4-space). This is true for other places in the file.

In order to integrate this, we'll need from you to release it under the MPL license - http://www.mozilla.org/MPL/

We need a boiter-plate (a header at the beginning of the file) that looks like http://www.mozilla.org/MPL/boilerplate-1.1/mpl-sh with the blanks filled. You should add yourself as a contributor.

If you've copied code from existing Bugzilla files, you should copy over from there the contributors in those files. Also, if in those files the initial developer appears as been Netscape, that must be copied as well (otherwise  you probably shouldn't mention Netscape).

+           return $status =~ m/NEW|REOPENED|ASSIGNED|UNCONFIRMED/;

This is a suggestion (completely optional/brainstorming stuff): not sure if this is what you want, but I thought it would be probably better/easier if you would just check the bug resolution to be empty.

# Date fu, take a string "1970-01-01 00:00:00" and call mktime
sub datestr2num($) {
  my $str = shift;

Well, this should use 4 space identation, but I was thinking -- maybe you could use here the sub format_time from Bugzilla::Util, in order to reduce code redundancy?

>> I also tried to add 'T' in the first line:
   #!/usr/bin/perl -wT
That also caused errors I couldn't figure out how to resolve. <<

T comes from "Taint". It's a concept in which every variable that you use in order to get results from the database (or access the file-system, etc) must be validated against a regexp.

For example, if you would do:

my $bug = $cgi->param('id');

then $bug would be tainted (since it's an input data that came directly from the user).

You can untaint it by doing something in the lines of:

if ($bug =~ /^([0-9]*)$/)
{
    $bug = $1;
    # Now $bug is ok to be further used
} else
{
    die "Unsuitable format for id parameter";
}

If you run with -T, perl will be quite explicit regarding the usage of tainted variables (that need validation against regexp).

We need to get this to run with -T in order to be commitable. If you're having specific problems with it, maybe we can help.

You could read http://www.google.com/search?q=perl+taint , including http://gunther.web66.com/FAQS/taintmode.html and http://perldoc.perl.org/perlsec.html , but it's probably more than you need/want.

Comment 14

[Knut Forkalsrud]

Attachment: burndown.cgi, version 3. Now works with taint mode

Thanks again for the feedback.

Now I think I have the indentation under control.
Sorry I misunderstood the capitalization stuff for queries,
now I have every SQL keyword uppercase.
I got the Taint mode stuff to work as well, and I added
the license verbiage at the top of the file.

As for the status trickery, I only want the status to
figure out at what times the bug was open.  When I
iterate through the bug history I don't see the resolution
field, only the status field.  I guess I could query
the history table for changes to the resolution field
instead.  When I first wrote the code I was targeting
a 2.16 installation of Bugzilla where I had no "remaining
time" field, so I faked it but translating the different
status values to some reasonable hour values.  Let me
think that one over.

The date trickery was purely to get a numeric x-axsis
value for GD::Chart.  Bugzilla::Util::format_time returns
a string.  I ditched the function and placed the code
with the other GD stuff.

When I looked for it I also found "Field.pm", which
allowed me to get rid of the hard coded field numbers.
I hope that was the right way to do it.

I still have the feeling that there's a lot to improve
in this code.  Since I don't have the "feel" for the
Bugzilla code base this code probably lacks the Bugzilla
"feel" as well.

Comment 15

[Vlad Dascalu]

Comment on attachment 214131 [details]
burndown.cgi, version 3.  Now works with taint mode

I'm not sure if I'll have time this weekend to look into this -- clearing my name since someone else might look into this meanwhile.

Comment 16

[Vlad Dascalu]

Comment on attachment 214131 [details]
burndown.cgi, version 3.  Now works with taint mode

Hello Knut, hope you're still around :)

Looks like nobody picked it up, and since I have some time again, I've looked into this again.

The main reason for not commiting this would be the results it produces when I try it on a standard bug.

I've created a bug, without any kind of dependency, and with 0 hours 0 minutes worked (and 0 remaining). This the default when you enable time tracking, so we can expect to have lots of cases of this. Even more, we're probably lucky if time tracking is enabled :)

When I tried to visit the script, it kept giving out errors. I've played with it for half an hour, then I've come to modify:

    my $gd = $graph->plot($gdata)

in

    my $gd = $graph->plot($gdata) or die $graph->error;

and got the more explicit result result:

No data sets or points at /home/vladd/mozilla/Bugzilla-trunk/chart-test.cgi line 225.


So basically we need to fix that, that is: make sure the script returns a valid image even when there is no time to be "computed". Basically those would be the cases where I've noticed that scalar(@$burndown) remains at 0, despite having fetched a/some bug numbers to the script.


So if you could upload a new version with that fixed, it would be great.

I'm mentioning some other things, if you can look into those it would be great; if they seem complex or run into trouble with them post here and maybe someone can help:

-> we have recently fixed bug 340253, which basically removed "die" from our code-base due to the "security reasons" mentioned in that page. If you can replace it with something more sensible, it would be great. I'm not sure about this since we generate an image and stuff, but just mentioning it.

-> we need HTML wrapping content, that is something which generates a page with the Bugzilla standard header, footer, and maybe an <img src=".."> that points to the generated image. Basically look at showdependencygraph.cgi for a model. I'd suggest to make this script output the required HTML by default, and the generated image if &image=1 is detected in the URL (or something like that).

-> we still need to decide where to link this from the current Bugzilla pages. If you could hack a link (and provide the patch for this change as well), it would be great. This will most likely be a template change (in the /template/en/default directory). You can use there for example IF Param('usetimetracking') (or so) in order to detect if timetracking is enabled (no point in linking to it otherwise). We use template toolkit for the templates - http://www.template-toolkit.org/ , but they're pretty intuitive.

Comment 17

[Knut Forkalsrud]

Pardon my absence on this.  What happened was that I switched jobs and thus is no longer a day-to-day bugzilla user.  As a result this fell by the wayside. Realistically I won't be spending a lot of time on this going forward, but from time to time I need to do something different, so I may come back to this on such rare occasions.  Nevertheless, thanks for the review.

The crash situation is obviously not cool.  I guess a reasonable thing to do would be to crate a fake data set with the data pairs (bug opened date, zero) and (earlier of bug close date or today, zero).  At least that will be renderable.

The changes to make bugzilla run cleanly with mod_perl are understandably desirable, even though they raise the barrier of entry for unsophisticated Perl wranglers like myself.  I suppose most of the error situations need to be handled in such a way that an image is nevertheless produced.  Maybe just include/forward to a static image with an error symbol.

HTML wrapping content and where/how to place the graph is an interesting topic.  However it quickly gets much closer to graphical design and usability than I like to venture.  Doing it by committee inevitably spurs emotional battles.  I'm sure bugzilla has strong graphical designers that could make a mockup much quicker and better than what I could hope to do.  That being said, I see the need to have a starting point for further refinement.  I would personally have a link on the issue detail page right next to "View Issue Activity", or possibly just include the image on the "Issue Activity" page.  (Digression: In the bugzilla installation I used when working on this we had a neat tweak for viewing issue activity inline.  The change records would show up between the comments, chronologically ordered.)

Another (digression) observation regarding the placement/tie-in:  The agile process we eventually settled on ditched the estimation of remaining effort alltogether.  Each "feature" would be divided into "tasks", and either they were done or not.  The remaining effort would simply be the number of tasks left.  If all project members were co-located each task would be represented with a sticky (Post-It) note on a board.  In a distributed project a sticky note wouldn't do, so we had project managers transcribe sticky notes to something viewable online.

The "features" would naturally map to bugzilla issues.  I'm not sure if buzilla is moving in a direction where an issue would be lightweight enough to represent our "task" construct, or if generally there is more ceremony surrounding an issue.  In that light it would make sense to have the option to use some pseudo time tracking behind the scenes even if it isn't directly visible in the UI, or example have the remaining time be 4 hours for any open issue.  Maybe map the severity of an issue to some number for remaining effort.  I guess our installation could have addressed that with a custom bug entry form for this type of "tasks".

The concept I like most about bugzilla compared to other issue tracking systems is that everything is an issue, large or small.  A project is represented as an issue just like the smallest task.  Thanks to the dependency relationships any concept involving a collection of other issues and a state maps easily to a new issue.  No separate conceptual (or programming) model for bug versus new-feature versus project versus inquiry versus ToDo.


OK, back to the topic at hand:

Given my current disconnect with actual bugzilla use I'm probably not the right person to take this further.  In comment #5 Vlad mentioned a contrib/ option.  The time might be right to push it that way, or possibly leave it here for an adventurous soul to pick it up.

Comment 18

[jgreene]

Greetings. I'm a non-programmer, but I just wanted to out in a vote for this (Burndown chart) feature. It'd be a great complement to Bugzilla and helpful feature for all using Bugzilla in a Scrum environment. Thanks.