Bugzilla database schema

1. Introduction

This document describes the Bugzilla database schema for Bugzilla versions 2.10, 2.12, and 2.14.

The purpose of this document is to act as a reference while developing P4DTI code which interacts with Bugzilla.

The intended readership is P4DTI developers.

This document is not confidential.

2. Bugzilla overview

Bugzilla is a defect tracking system, written in Perl with a CGI web GUI. It uses MySQL to store its tables. We need to understand Bugzilla and MySQL to build a P4DTI Bugzilla integration.

Bugzilla Releases

Bugzilla releases we need to work with are 2.10, 2.12, and 2.14. Where the schema has been changed, the change is noted in this document. We need to be able to recognize the schema version.

Bugs

Each defect is called a bug and corresponds to one row in the bugs table. It is identified by its number, the "bug_id".

Products and components

The work managed by Bugzilla is divided into products. Each product is represented by a row in the products table. The work for each product is in turn divided into the components of that product. Each component is represented by a row in the components table.

Several properties of a new bug (e.g. ownership) are determined by the product and component to which it belongs.

Workflow

Each bug has a status. If a bug has a status which shows it has been resolved, it also has a resolution (otherwise the resolution field is empty). These tables show the possible values of these fields and the valid transitions of the status field.

Users

Bugzilla has users. Each user is represented by one row in the profiles table. Each user is referred by a number (the "userid") and an email address (the "login_name").

Authentication

Each user has a password, used to authenticate that user to Bugzilla. The password is stored in the profiles table in encrypted form. Before version 2.14, it was also stored in plaintext.

On a successful login, Bugzilla generates a pair of cookies for the user's browser. On subsequent accesses, a user gets access if these cookie checks pass (CGI.pl):

1. they have both Bugzilla_login and Bugzilla_logincookie cookies;
2. their Bugzilla_login is the login_name of a row in the profiles table;
3. their Bugzilla_logincookie matches a row in the logincookies table
4. the userids of these two rows match;
5. the cryptpasswords of these two rows match;
6. the hostname of the logincookies row matches the CGI REMOTE_HOST.

and also their account is not disabled (i.e. the disabledtext of the profiles row is empty).

If the cookie checks fail, the user has to login (with their password), in which case a new row is added to the logincookies table and the user gets a new pair of cookies.

Rows in the logincookies table are deleted after 30 days (at user login time; CGI.pl).

Voting

Users may vote for bugs which they think are important. The maximum number of votes per user is product-dependent. A user can vote for a bug more than once. Whether or not project managers pay any attention to votes is up to them, apart from the "confirmation by acclamation" process, which is as follows:

New bugs have the status UNCONFIRMED. To enter the main workflow, they need the status NEW. To get the status NEW, they need a particular number of votes which is product-dependent.

Votes are recorded in the votes table.

Milestones

Products may have "milestones" defined. The intention is that a milestone should be a point in a project at which a set of bugs has been resolved. An example might be a product release or a QA target. Milestones may be turned on and off with the parameter "usetargetmilestone".

If milestones are on, each bug has a "target milestone" (by which it should be fixed). A product may have a URL associated with it which locates a document describing the milestones for that product. This document itself is entirely outside Bugzilla. A product may also have a default target milestone, which is given to new bugs.

Milestones for a product have a "sort key", which allows them to be kept in an order. As far as I can tell, this order is only used for ordering user interface items (e.g. menu entries).

Milestones are kept in the milestones table.

Versions

Products may have versions. This allows more accurate bug reporting: "we saw it in 1.3.7b3". Versions are totally independent of milestones.

Parameters

The operation of Bugzilla is controlled by parameters. These are defined in defparams.pl, and set in editparams.cgi. The current values are stored in data/params. They are not stored in the database.

Groups

Bugzilla has "groups" of users. Membership of a group allows a user to perform certain tasks. Each group is represented by a row of the groups table.

There are a number of built-in groups, as follows:

Group membership is conferred by the bit being set in the "groupset" field of the profiles table. Group membership for new users is determined by matching the "userregexp" field against the new user's email address. The default configuration has universal regexps for the "editbugs" and "canconfirm" groups.

New groups may be added (editgroups.cgi) and used to control access to sets of bugs. These "bug groups" have "isbuggroup" set to 1. Members of a bug group may see bugs in this group (using the "groupset" field in the "bugs" table).

If the parameter "usebuggroups" is on, each product automatically has a bug group associated with it.

If the parameter "usebuggroupsentry" is also on, the product's bug group also determines the set of users able to enter bugs for the product.

Attachments

Users can upload attachments to bugs. An attachments can be marked as a patch. Attachments are stored in the attachmments table.

Keywords

Bugzilla users can define a number of keywords, and then give each bug a set of keywords. This is mainly for use in finding related bugs. The keywords are stored in the keyworddefs table, and the one-to-many mapping from bugs to keywords is stored in the keywords table, and also in the "keywords" field of the bugstable.

Dependencies

Bugs may depend on other bugs being fixed. That is, it may be impossible to fix one bug until another one is fixed. Bugzilla records and displays such information and uses it to notify users when a bug changes (all contacts for all dependent bugs are notified when a bug changes).

Dependencies are recorded in the dependenciestable.

Activity

Bugzilla keeps a record of changes made to bugs. This record is in the bugs_activity table. Each row in this table records a change to a field in the bugs table. The fields are referred to by a number which is looked up in the fielddefs table. This table records the name of the field and also a longer description used to display activity tables.

Severity

Each bug has a "severity" field, indicating the severity of the impact of the bug. There is no code in Bugzilla which distinguishes the values of this field, although it may naturally be used in queries. The intended meanings of the values of this field are as follows:

Email notification

When a bug changes, email notification is sent out to a number of users:

• The bug's owner (the "assigned_to" field)
• The bug's reporter
• The bug's QA contact (if the "useqacontact" parameter is set)
• All the users who have explicitly asked to be notified when the bug changes (these users are stored in the cc table).
• All the users who have voted for this bug

This is handled by the "processmail" script.

Long descriptions

Each bug has a number of comments associated with it. These are stored in the longdescs table and displayed as the "Description" on the bug form, ordered by date and annotated with the user and date. Users can add new comments with the "Additional comment" field on the bug form.

Named queries

Users can name queries. Named queries appear in a pop-up on the query page. A query named "(Default query)" is a user's default query. Named queries are stored in the namedqueries table.

Watchers

Bugzilla lets users "watch" each other; receiving each other's Bugzilla email. For instance, if Sam goes on holiday, Phil can "watch" her, receiving all her Bugzilla email. This is set up by the user preferences (userprefs.cgi) and handled by the processmail script.

Shadow database

Bugzilla can keep a shadow, read-only copy of everything in another database (with the parameter "shadowdb"). If the parameter "queryagainstshadowdb" is on, queries are run against the shadow. The shadowlog table keeps a record of SQL activity since the last reflection.

List of tables

3. The schema

The "attachments" table

Indexes:

The "bugs" table

Indexes:

The "bugs_activity" table

Indexes:

The "cc" table

Indexes:

The "components" table

One row for each component. See the notes on products and components.

The "dependencies" table

Indexes:

The "duplicates" table

This table identifies bugs which are duplicates of other bugs. It was introduced in 2.12

Indexes:

The "fielddefs" table

This is a table of the fields for which we update the bugs_activity table. It's used to turn bugs_activity entries into useful text.

Indexes:

The "groups" table

This table describes a number of user groups. Each group allows its members to perform a restricted activity. See the notes on groups.

Indexes:

The "keyworddefs" table

Names and definitions of the keywords. See the notes on keywords.

Indexes:

The "keywords" table

Bugs may have keywords. This table defines which bugs have which keywords. The keywords are defined in the keyworddefs table..

Indexes:

The "logincookies" table

Bugzilla generates a cookie each time a user logs in, and uses it for subsequent authentication. The cookies generated are stored in this table. For more information, see the notes on authentication.

Indexes:

The "longdescs" table

Indexes:

The "milestones" table

Indexes:

The "namedqueries" table

Indexes:

The "products" table

One row for each product. See the notes on products.

The "profiles" table

This table describes Bugzilla users. One row per user.

Indexes:

The "profiles_activity" table

This table is for recording changes to the "profiles" table. Currently it only records changes to the "groupset" column made with editusers.cgi. This allows the administrator to track group inflation. There is currently no code to inspect this table; only to add to it.

Indexes:

The "shadowlog" table

Indexes:

The "tokens" table

Used for managing the tokens sent to users who wish to change their password. Introduced in 2.14.

Indexes:

The "versions" table

The "votes" table

Indexes:

The "watch" table

Indexes:

4. Example queries

To select a bug given the bug number:

select * from bugs where bug_id = %d

To select the long descriptions of a bug, together with the name and email address of the commenters:

select profiles.login_name, profiles.realname, longdescs.bug_when, longdescs.thetext from longdescs, profiles where profiles.userid = longdescs.who and longdescs.bug_id = %d order by longdescs.bug_when

To get a complete list of user ids and email addresses:

select userid, login_name from profiles

To find out a user's groupset:

select groupset from profiles where userid = %d

To get a user's email address:

select login_name from profiles where userid = %d

To get the set of cc addresses for a given bug:

select login_name from cc, profiles where cc.bug_id = %d and profiles.userid = cc.who

A. References

B. Document History

This document is copyright © 2001 Perforce Software, Inc. All rights reserved.

Redistribution and use of this document in any form, with or without modification, is permitted provided that redistributions of this document retain the above copyright notice, this condition and the following disclaimer.

This document is provided by the copyright holders and contributors "as is" and any express or implied warranties, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose are disclaimed. In no event shall the copyright holders and contributors be liable for any direct, indirect, incidental, special, exemplary, or consequential damages (including, but not limited to, procurement of substitute goods or services; loss of use, data, or profits; or business interruption) however caused and on any theory of liability, whether in contract, strict liability, or tort (including negligence or otherwise) arising in any way out of the use of this document, even if advised of the possibility of such damage.