Bugzilla database schema

1. Introduction

This document describes the Bugzilla database schema for Bugzilla versions 2.10, 2.12, 2.14, 2.14.1, 2.14.2, 2.14.3, 2.14.4, 2.14.5, 2.16rc1, 2.16rc2, 2.16, 2.16.1, 2.16.2, 2.16.3, 2.16.4, 2.17.1, 2.17.3, 2.17.4, 2.17.5, and 2.17.6.

This document is now generated automatically by a Python script which constructs and colours the schema tables from the results of MySQL queries.

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

This table gives the dates of all the Bugzilla releases since 2.0, and of each P4DTI release which changed the set of supported Bugzilla releases, and shows the history of P4DTI support for each release.

The P4DTI currently supports Bugzilla 2.14.4, 2.14.5, 2.16.1, 2.16.2, 2.16.3, and 2.16.4. During the lifetime of this document it has also had some form of support for Bugzilla 2.10, 2.12, 2.14, 2.14.1, 2.14.2, 2.14.3, and 2.16. It is intended to support 2.18 when it arrives as the culmination of the 2.17.x series of releases.

Bugzilla Schema Changes

Where the Bugzilla schema has been changed since Bugzilla 2.10, the change is noted in this document. We need to be able to recognize the schema version.

In the schema tables themselves, changed fields are noted and colored as follows:

In Bugzilla release 2.12, the following schema changes were made:

• The duplicates table was added.
• profiles.emailflags was added.
• The bugs.resolution value MOVED was added.
• A number of additional values were permitted in bugs.op_sys.
• components.initialowner and components.initialqacontact changed from "tinytext" (foreign key profiles.login_name) to "mediumint" (foreign key profiles.userid), default 0.
• profiles.disabledtext changed from "not null" to "null".
• The default value of profiles.newemailtech changed from 0 to 1.

In Bugzilla release 2.14, the following schema changes were made:

• The tokens table was added.
• profiles.password was removed.
• profiles.cryptpassword and logincookies.cryptpassword were both changed from varchar(64) to varchar(32).
• profiles.newemailtech was removed.
• profiles.emailnotification was removed.
• bugs.reporter_accessible, bugs.assignee_accessible, bugs.qacontact_accessible, and bugs.cclist_accessible were added.
• bugs.version changed from varchar(16) to varchar(64).
• bugs_activity.oldvalue and bugs_activity.newvalue were replaced by bugs_activity.removed and bugs_activity.added.
• groups.isactive was added.
• longdescs.who became an index field.
• profiles.disabledtext changed back to "not null".

The schema is identical in Bugzilla releases 2.14 and 2.14.1.

In Bugzilla release 2.14.2, the following schema change was made:

• logincookies.hostname was replaced by logincookies.ipaddr.

The schema is identical in Bugzilla releases 2.14.2, 2.14.3, 2.14.4, and 2.14.5.

In Bugzilla release 2.16 (and the release candidates 2.16rc1 and 2.16rc2), the following schema changes were made:

• The attachstatuses andattachstatusdefs tables were added.
• attachments.isobsolete was added.
• The values permitted in bugs.op_sys changed.
• bugs.assignee_accessible and bugs.qacontact_accessible were removed.
• bugs_activity.attach_id was added.
• logincookies.cryptpassword was removed.
• The possible values of tokens.tokentype changed, to include 'emailold' and 'emailnew' (used when changing the email address of a Bugzilla user).

The schema is identical in Bugzilla releases 2.16rc1, 2.16rc2, 2.16, 2.16.1, 2.16.2, 2.16.3, and 2.16.4.

In Bugzilla release 2.17.1, the following schema changes were made:

## Note: I am still working on updating the schema remarks to reflect all these changes. Changes which have not yet been reflected in the remarks are marked here with "##".

• ## The groups system was radically changed. This included the following detailed schema changes:

  • ## The bug_group_map, user_group_map, group_group_map were added.
  • ## groups.bit was replaced with groups.id.
  • ## groups.last_changed was added.
  • bugs.groupset, profiles.groupset and profiles.blessgroupset were dropped.
• ## The attachstatuses andattachstatusdefs tables were replaced with the flags, flagtypes, flaginclusions, flagexclusions tables. This replaces the "attachment statuses" feature introduced in 2.16 with a new "status flags" feature, allowing status flags to be defined and used on both attachments and bugs.
• The quips table was added.
• Products got IDs in addition to names, and product name columns were replaced with product ID columns: bugs.product was replaced with bugs.product_id, components.program was replaced with components.product_id, milestones.product was replaced with milestones.product_id, versions.program was replaced with versions.product_id, and products.product was replaced with products.id and products.name.
• Components got IDs in addition to names, and the component name column was replaced with a component ID column: bugs.component was replaced with bugs.component_id, and components.value was replaced with components.id and components.name.
• ## bugs.estimated_time, bugs.remaining_time, and longdescs.work_time were added.
• attachments.isprivate and longdescs.isprivate were added.
• attachments.creation_ts changed from a timestamp to a datetime default '0000-00-00 00:00:00'.
• attachments.filename changed from a mediumtext to a varchar(100).
• ## The values permitted in bugs.op_sys changed.
• ## bugs.alias was added.
• ## namedqueries.watchfordiffs was added.
• ## profiles.refreshed_when was added.

## We also need more extensive notes on some aspects of Bugzilla which have been changed in this release, in particular the groups system. ##

The schema is identical in Bugzilla releases 2.17.1 and 2.17.2.

In Bugzilla release 2.17.3, the following schema changes were made:

## Note: I am still working on updating the schema remarks to reflect all these changes. Changes which have not yet been reflected in the schema remarks are marked here with "##".

• ## The group_control_map table was added.
• The shadowlog table was removed.

In Bugzilla release 2.17.4, the following schema changes were made:

• quips.approved was added.

In Bugzilla release 2.17.5, the following schema changes were made:

## Note: I am still working on updating the schema remarks to reflect all these changes. Changes which have not yet been reflected in the schema remarks are marked here with "##".

• ## The series, series_categories, series_data, and user_series_map tables were added.
• ## votes.count was replaced with votes.vote_count.
• ## The values permitted in bugs.op_sys changed.
• ## bugs.short_desc became a fulltext index field.
• ## longdescs.thetext became a fulltext index field.

The schema is identical in Bugzilla releases 2.17.5 and 2.17.6.

Bugs

Each defect is called a bug and corresponds to one row in the bugs table. It is identified by its number, bugs.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 (bugs.bug_status). If a bug has a status which shows it has been resolved, it also has a resolution (bugs.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 (profiles.userid) and an email address (profiles.login_name).

Authentication

Each user has a password, used to authenticate that user to Bugzilla. The password is stored in profiles.cryptpassword in encrypted form. Before 2.14, it was also stored in profiles.password as 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. (before 2.16) the cryptpasswords of these two rows match;
6. (before 2.14.2) the hostname of the logincookies row matches the CGI REMOTE_HOST;
7. (from 2.14.2) the IP address of the logincookies row matches the CGI REMOTE_ADDR.

and also their account is not disabled (i.e. profiles.disabledtext 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 profiles.groupset. Group membership for new users is determined by matching groups.userregexp 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 group.isbuggroup set to 1. Members of a bug group may see bugs in this group (using bugs.groupset).

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 attachments table. From 2.16, attachments can be marked as "obsolete".

From 2.16, each attachment may have one of a number of "status" keywords associated with it. The status keywords are user-defined on a per-product basis. The set of status keywords is defined in the attachstatusdefs table. Whether a given attachment has a given status keyword is defined by the attachstatuses 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 bugs.keywords.

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, bugs.bug_severity, 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 (bugs.assigned_to)
• The bug's reporter (bugs.reporter)
• The bug's QA contact, if the "useqacontact" parameter is set (bugs.qa_contact)
• 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 (recorded in the votes table).

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), recorded in the watch table and handled by the processmail script.

Shadow database

Note: Shadow databases were removed in Bugzilla 2.17.3. Administrators can use MySQL's replication facilities to provide a similar benefit.

Until Bugzilla 2.17.3, Bugzilla could maintain a shadow, read-only copy of everything in another database (with the parameter "shadowdb"). If the parameter "queryagainstshadowdb" is on, queries were run against the shadow. The shadowlog table kept a record of SQL activity since the last reflection.

List of tables

3. The schema

Note: this schema description is now automatically generated.

The "attachments" table

Indexes:

The "attachstatusdefs" table

Added in 2.16. Removed in 2.17.1 (replaced by the flag tables).

Indexes:

The "attachstatuses" table

Added in 2.16. Removed in 2.17.1 ( replaced by the flag tables).

Indexes:

The "bug_group_map" table

Added in 2.17.1.

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.

Indexes:

The "dependencies" table

Indexes:

The "duplicates" table

This table identifies bugs which are duplicates of other bugs.Added 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 "flagexclusions" table

Added in 2.17.1.

Indexes:

The "flaginclusions" table

Added in 2.17.1.

Indexes:

The "flags" table

Added in 2.17.1.

Indexes:

The "flagtypes" table

Added in 2.17.1.

Indexes:

The "group_control_map" table

Added in 2.17.3.

Indexes:

The "group_group_map" table

Added in 2.17.1.

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.

Indexes:

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 profiles.groupset 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 "quips" table

Added in 2.17.1.

Indexes:

The "series" table

Added in 2.17.5.

Indexes:

The "series_categories" table

Added in 2.17.5.

Indexes:

The "series_data" table

Added in 2.17.5.

Indexes:

The "shadowlog" table

Removed in 2.17.3 (similar functionality now available using MySQL's replication facilities).

Indexes:

The "tokens" table

Used for managing the tokens sent to users who wish to change their password (or email address, from 2.16).Added in 2.14.

Indexes:

The "user_group_map" table

Added in 2.17.1.

Indexes:

The "user_series_map" table

Added in 2.17.5.

Indexes:

The "versions" table

The "versions" table has no indexes.

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-2003 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.