Bugzilla enhancements

Sean McAfee etzwane at schwag.org
Tue Feb 1 22:31:46 UTC 2005

Besides an implementation of custom fields, I've made lots of local
customizations to Transmeta's Bugzilla installation of greater or lesser
usefulness.  I'd like to make them available to the Bugzilla community.  This
message describes several of the more significant ones.  Comments are welcome.

As an aside, if you haven't been following the financial news, in all
probability Transmeta will be having a mass layoff on March 31 as it adjusts
for a new business model.  I strongly suspect that I'll be a victim of this
layoff, and if so, I won't have a large production Bugzilla database to work
with anymore (unless my next employer happens to have one).

In the following text which mentions CGI form elements, [Submit] indicates a
submit form element, {foo,bar,baz} indicates a selection form element, and
<input> indicates a single-line text input element.

Database utility routines

These are already present in my last custom fields patch, but they're so
useful it might be appropriate to put them in Bugzilla.pm or Bugzilla/DB.pm or

process_query($sql, @bind [, $subref])

Prepares a statement handle from $sql, executes it with @bind as bind
variables, and passes each returned row to the subroutine referred to by
$subref (if given).  Returns the number of rows processed.

simple_query($sql, @bind)

Prepares a statement handle from $sql, executes it with @bind as bind
variables, and returns a list of the first column of each returned row.

single_row_query($sql, @bind)

Prepares a statement handle from $sql, executes it with @bind as bind
variables, and returns the first returned row as a list.

All three of these routines accept a slightly enhanced type of SQL.  I got
tired of writing

my $placeholder = join ',', ('?') x @array;

...all over the place, so these routines will accept arrayrefs as bind
variables as long as the corresponding placeholder is "???".  The routines
will expand the "???" to a comma-separated list of questions marks of the
appropriate length, and expand the corresponding array reference argument to a
list of the elements of the array before calling execute() on the statement

Typical example:

    SELECT field_id, value
    FROM cf_shortstring
    WHERE bug_id = ? AND field_id IN (???)
}, $bug_id, \@field_ids, sub {
    $field{ $_[0] } = $_[1];

Often the subroutine is so short that it's not at all unclear to use $_[0],
$_[1], etc. in the body, as one can just glance a little ways up and see
immediately what columns they refer to.

Auto Fields

Our prior incident-tracking system, Teamshare, numbered incidents on a
per-product (or "project" in Teamshare parlance) basis.  When we converted
to Bugzilla, various departments wanted to continue their old numbering
system, rather than use Bugzilla IDs which would end up being discontinuous
from their point of view.  My solution was auto fields, which are fields set
programmatically at bug creation.  Products that correspond to older Teamshare
projects have an integer custom field which at bug creation is automatically
set to one greater than the current highest value of that field.

To provide extra protection to these fields, which should always remain
constant, I have an extra boolean column READONLY in the CUSTOM_FIELDS table,
which if set indicates that that field may not be changed via the standard

Bug Names

It's all well and good that the departments mentioned above get to keep their
old numbering system, but it's not that useful if they always need to enter
Bugzilla bug IDs on the web interface.  My solution was what I call "bug
names".  I have a table BUG_NAME with columns PRODUCT_ID, PREFIX, and
FIELD_ID.  For products in the table, bugs in that product can be identified
in many places with the corresponding custom field, prefixed with the given

Example, taken from our actual database:

mysql> select * from bug_name;
| product_id | prefix     | field_id |
|          2 | TLC Case # |        1 |
|         13 | AE Case #  |      224 |
|         14 | AppsUnit   |      260 |

Bug 1202 in our system is in product 2, and it has the title "TLC Case #2366"
at the top of its show_bug.cgi page, rather than "Bug #1202".

Instead of "[Find] bug #<input>" in our page footers as a means of going to a
specific bug, we have "{Bugzilla Bug ID,TLC Case #,AE Case #,AppsUnit} <input>

Various bits of code expect the field ID associated with a bug to have a
unique value.  So really, it only makes sense to define bug names for products
that have fields that are guaranteed to be unique, like the
automatically-generated alternate bug IDs mentioned above.


Links are arbitrary connections between bugs.

The schema supporting links is very simple, consisting of a single table,
LINKS, with two columns, BUG_FROM and BUG_TO.  On the show_bug page, there
is a short piece of text:

[Create] a {one-way,two-way} link to {Bugzilla Bug, at bugname} <input>.

A two-way link simply means to create a second link back from the target bug
to this one.

@bugname means all prefixes from the BUG_NAME table.

Links are indicated on the show_bug.cgi page with a list of short paragraphs
like this:

<i>Linked to <a href="show_bug.cgi?id=999">Summary of bug 999</a></i>


A workflow is a way of structuring the way a bug is worked by users.  For
products which have an associated workflow, one single-selection type custom
field is dubbed that product's "state field".  The workflow is a directed
graph for which the vertices are labelled with the possible values of the
product's state field, and the edges are the possible transitions between
states.  Transitions are named.  A special "initial transition" points from
outside the workflow to the state a bug will have when first created.

For bugs belonging to a product with a workflow, show_bug.cgi initially shows
the bug in a read-only fashion.  A user must press one of the buttons near the
top of the page, each bearing the name of one of the available transitions, to
initiate that transition.

Each transition between states is associated with a subset of the product's
custom fields in a particular order.  When a transition is initiated, the
page is redisplayed in an editable form, showing only those custom fields in
that transition's subset.  In addition, for each transition, each field in the
subset may be tagged read-only (the field is displayed but not editable) or
required (the field must be set to a non-null value or the update will fail).
As a visual aid, the names of read-only fields are shown in gray, and the
names of required fields are shown in red.

A special transition "Update" is always available.  It reloads the page
showing all fields as editable--even the state field, which is normally only
ever changed behind the scenes.

A bug's assignee can be changed automatically in a transition, either to the
original reporter, a specific user, or the user referred to in some custom
field.  The new assignee is only a default, and may be overridden.


All changes to custom fields, and a few standard fields, are logged with the
date/time and identity of the user making the change.  Each change to a bug is
assigned a unique transaction ID, which serves to group all the field changes
together into a single unit.  Currently, in our system, little use is made of
the logged information, except that on the show_bug.cgi page of each bug with
a workflow, a transition history table is displayed with four columns:
"User" (the one who created or changed the bug), (the new) "State", (the
new) "Owner", and "Date".  This feature is based on a similar one in our old
Teamshare system.

Journaling fields

Another Teamshare feature.  Long string fields may be tagged as "journaling".
Such fields are append-only.  In an update, new text entered by a user is
prefixed with a short, standard introduction: "YY/MM/DD HH:MM - realname
<username>:\n" and appended to the existing contents of a field.

This append-only behavior is enforced even during the special "update"
transition, unless the user is in a special "managers" group.

Quick search

This is a means of letting users avoid going through query.cgi by putting
common searches in the page footer.  A table QUICKSEARCH describes common
single-term queries.  Our table currently looks like this (squeezed a bit to
fit in 80 columns):

mysql> select * from quicksearch;
| p_id | query_name        | query_term            | query_op       | sortkey |
|    2 | Description       | any note              | allwordssubstr | 1       |
|    2 | FAE/Issue Contact | tlc_fae_issue_contact | allwordssubstr | 2       |
|    2 | Error LEDs        | tlc_error_leds        | allwordssubstr | 3       |
|    2 | Summary           | short_desc            | allwordssubstr | 4       |

The quick searches are grouped by product and displayed in the page footer to
the right of a user's predefined queries, if any.  The searches are selected
via a <select> form element, grouped by products in <optgroup> elements.  A
text input element provides the argument to the query operator.

Custom query results

The list displayed by buglist.cgi is grouped by product.  Furthermore, the
columns shown can vary by product.  The columns shown are described by the
table BUGLIST_COLUMNS.  Ours is shown here:

mysql> select * from buglist_columns order by user_id, product_id, sortkey;
| user_id | product_id | column_type | field_id | options   | sortkey |
|    NULL |       NULL |           1 |     NULL | NULL      |       1 |
|    NULL |       NULL |           2 |     NULL | NULL      |       2 |
|    NULL |       NULL |           0 |       62 | NULL      |       3 |
|    NULL |       NULL |           0 |      311 | NULL      |       4 |
|    NULL |         13 |           1 |     NULL | NULL      |       1 |
|    NULL |         13 |           0 |      221 | nowrap    |       2 |
|    NULL |         13 |           0 |      225 | NULL      |       4 |
|    NULL |         13 |           2 |     NULL | NULL      |       5 |
|    NULL |         13 |           0 |       62 | NULL      |       6 |
|    NULL |         13 |           0 |      311 | width=40% |       7 |
|    NULL |         16 |           1 |     NULL | NULL      |       1 |
|    NULL |         16 |           2 |     NULL | NULL      |       2 |
|    NULL |         16 |           0 |       62 | NULL      |       3 |
|    NULL |         16 |           0 |      305 | nocenter  |       4 |
|    NULL |         16 |           0 |      311 | NULL      |       5 |

NULLS in the USER_ID and PRODUCT_ID columns denote a default column.
Obviously we have no users who have expressed column preferences, but that's
only because I haven't built an interface for describing them yet.
COLUMN_TYPE can be 0 (a custom field), 1 (the bug's "name", as per "Bug Names"
above), or 2 (the bug's state, if it belongs to a product with a workflow).
OPTIONS contains a comma-separated list of HTML display options.  Some have
special meanings, like "nowrap" and "nocenter"; the rest are passed directly
as attributes to the <td> elements containing that column's data.

By the way: the custom fields with IDs 62 and 311 actually refer to the
standard fields assigned_to and summary, respectively.

If there are more columns for a particular product than some threshold value,
the text for that product is automatically reduced one font size.

We have dozens of other site-local customizations, but I think I mentioned all
of the major ones.


Sean McAfee -- etzwane at schwag.org

More information about the developers mailing list