Bugzilla Schema for Version 2.18

1. Introduction

This document describes the Bugzilla database schema for Bugzilla version 2.18.

This document is generated automatically by a Python script which constructs and colors the schema tables from the stored results of MySQL queries. For more information about the scripts, see code.html.

The purpose of this document is to act as a reference for developers of Bugzilla and of code which interacts with Bugzilla (e.g. P4DTI).

The intended readership is P4DTI developers and Bugzilla developers and administrators.

This document is not confidential.

Please send any comments to <p4dti-comments@ravenbrook.com>, and problem reports to <p4dti-support@ravenbrook.com>.

2. Bugzilla overview

Bugzilla is a defect tracking system, written in Perl with a CGI web GUI. By default it uses MySQL to store its tables.

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. The work for each product is in turn divided into the components of that product. Several properties of a new bug (e.g. ownership) are determined by the product and component to which it belongs. Each component is represented by a row in the components table. Each product is represented by a row in the products table.

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.

This table shows the possible values and valid transitions of the status field in the default workflow.

This table shows the allowable values of the resolution field. The values "FIXED", "MOVED", and "DUPLICATE" have special meaning for Bugzilla. The other values may be changed, by editing the schema of the bugs table, to add, remove, or rename values as necessary.

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.

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:

• they have both Bugzilla_login and Bugzilla_logincookie cookies;
• Their Bugzilla_login is the profiles.userid of a row in the profiles table;
• their Bugzilla_logincookie matches a row in the logincookies table;
• the userids of these two rows match;
• logincookies.ipaddr matches the CGI REMOTE_ADDR;
• 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).

Voting

Users may vote for bugs which they think are important. A user can vote for a bug more than once. Votes are recorded in the votes table.

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.

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 presented in a specific order in the user interface.

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 set in editparams.cgi. The current values are stored in data/params. They are not stored in the database.

The set of parameters is defined in defparams.pl.

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:

New groups may be added and used to control access to sets of bugs. These "bug groups" have groups.isbuggroup set to 1. A bug may be in any number of bug groups. To see a bug, a user must be a member of all the bug groups which the bug is in.

If the parameter "usebuggroups" is on, each product automatically has a bug group associated with it. If the parameter "usebuggroupsentry" is also on, a user must be in the product's bug group in order to create new bugs for the product.

Users may be added to a group by any user who has the "bless" property for that group. The "bless" property itself may only be conferred by an administrator.

Group membership for new users and new groups is determined by matching groups.userregexp against the user's email address.The default configuration has universal regexps for the "editbugs" and "canconfirm" groups.

User membership in a group is conferred by a row in the user_group_map table, with user_group_map.isbless set to 0. The bless privilege for a group is conferred by a row with user_group_map.isbless set to 1. Bug membership in a bug group is conferred by a row in the bug_group_map table.

Groups may be configured so that membership in one group automatically confers membership or the "bless" privilege for another group. This is controlled by the group_group_map table.

A product may be configured so that membership in one or more groups is required to perform certain actions on bugs in the product. Whether or not a new bug for the product is placed in a group is also configurable (note that user membership in a group is required to place an existing bug in that group). All this is controlled by the group_control_map table.

The group_control_map.membercontrol and group_control_map.othercontrol columns of that table determine the treatment of a given group for a new bug in a given product, depending on whether the bug is being created by a member or non-member of that group respectively. The possible values of these columns are as follows:

Only certain combinations of membercontrol/othercontrol are permitted, as follows:

Attachments

Users can upload attachments to bugs. An attachments can be marked as a patch. Attachments are stored in the attachments table.Attachment data is stored in attachments.thedata.

Attachment statuses are implemented with the flags system.

Flags

Bugs and attachments may be marked with "flags". The set of flag types is user-defined (using editflagtypes.cgi). For instance, a flag type might be "candidate for version 7.3 triage", or "7.3" for short. Flag types are recorded in the flagtypes table. Each flag type is either for bugs or for attachments, not both.

Actual flags are recorded in the flags table. Each flag has a status of "+" ("granted"), "-" ("denied") or "?" ("requested"). For instance, one bug might have flag "7.3+", and another might have flag "7.3-".

A status of "?" indicates that a user has requested that this item be given this flag. There is an special interface for viewing request flags (request.cgi). A request flag may be marked for the attention of a particular user, the "requestee".

A flag type may have a "CC list" of email addresses, of people to notify when a flag is requested.

By default, a single bug or attachment may receive several flags of the same type, with the same or different statuses and the same or different requestees. This may be disabled for any given flag type.

Particular flag types may only be available to bugs in certain products and components (or their attachments). This is recorded in the flaginclusions table. Particular flag types may not be available to bugs in certain products and components (or their attachments). This is recorded in the flagexclusions table.

Various features of flag types may be disabled: they can be made inactive, not requestable, not "requesteeable", not "multiplicable".

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 dependencies table.

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 built-in 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).

Individual users may filter these messages according to the way in which the bug changes and their relationship to the bug. These filtering preferences are recorded in profiles.emailflags.

This is handled by the Bugzilla::Bugmail module, which is invoked by the template system (from Bugzilla::Template) when it encounters a call to SendBugMail() in a template.

Long descriptions

Each bug has a number of comments associated with it. These are stored individually in the longdescs table.

They are 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. Links to named query pages appear in a navigation footer bar on most Bugzilla pages. A query named "(Default query)" is a user's default query. Named queries are stored in the namedqueries table.

Charts

Bugzilla can draw general time-series charts. There are a number of default time series. Each product has a default series for each bug status or resolution (for instance "how many bugs are INVALID in product Foo") and each component has a default series for all open bugs (UNCONFIRMED/NEW/ASSIGNED/REOPENED) and one for all closed bugs (RESOLVED/VERIFIED/CLOSED). A user can also define a new time series based on any query, and give it a "frequency" (actually a period, measured in days). The set of series is stored in the series table.

To collect the data for the time series, the Bugzilla administrator needs to arrange for the collectstats.pl script to be run every day. This script stores the data in the series_data table.

Series have categories and subcategories, which are provided in order to make it easier to manage large numbers of series. They are normalized in the series_categories table.

By default, a time series is "private": only visible to the user who created it. An administrator may make a time series "public", or visible to other users.this is determined by series.public.

Visibility of a time series to a user is determined on a per-category basis using the groups system. The group memberships required to see a time series in a given category are recorded in the category_group_map table. A user may see a time series if they are in all the groups for the category and either ths user created the series or it is public.

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 email subsystem.

Time tracking

Bugzilla can track time for each bug, if the "timetrackinggroup" parameter is set. Members of that group get the ability to estimate the amount of effort (measured in hours) a bug will take to fix, either when creating or when editing the bug. Members of that group are also permitted to record hours of effort spent on the bug

longdescs.work_time records each increment of work. The sum of this column for a bug is computed to display as "Hours Worked" for the bug.

bugs.estimated_time is the estimate for how much time the bug will take in total, displayed as "Orig. Est.". This can be changed by members of the timetrackinggroup.

bugs.remaining_time is the current estimate for how much more time the bug will take to fix, displayed as "Hours Left". This can be changed by members of the timetrackinggroup.

The total of "Hours Left" and "Hours Worked" is shown as "Current Est.": the current estimate of the total effort required to fix the bug. "Hours Worked" as a percentage of "Current Est" is shown as "% Complete". "Current Est" deducted from "Orig. Est" is shown as "Gain"

The Whine System

Bugzilla has a system for sending "whine" email messages to specified users on a regular basis. This system relies on the administrator configuring the Bugzilla server to run a script at regular intervals (e.g. by using crontab).

The whineatnews.pl script should be run once a day. For each bug which has status NEW or REOPENED, and which has not changed for a certain number of days, it sends a message to the bug's owner. The number of days is controlled by a Bugzilla parameter called "whinedays". The content of the email message is controlled by a Bugzilla parameter called "whinemail".

Quips

Bugzilla supports "quips": small text messages, often humorous, which appear along with search results. The quips are selected at random from a set.

Quips may be entered or deleted using quips.cgi.

Quips may be entered by any user but must be approved by an administrator before they can be displayed.

List of tables

3. The schema

The "attachments" table

Bug attachments.

Indexes:

The "bug_group_map" table

Which bugs are in which groups. See the notes on groups.

Indexes:

The "bugs" table

The bugs themselves.

Indexes:

The "bugs_activity" table

Activity on the bugs table.

Indexes:

The "category_group_map" table

Which groups does a user have to be in to view chart data in a given category. See the notes on charts.

Indexes:

The "cc" table

Users who have asked to receive email when a bug changes.

Indexes:

The "components" table

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

Indexes:

The "dependencies" table

Which bugs depend on other bugs.

Indexes:

The "duplicates" table

Which bugs are duplicates of which other bugs.

Indexes:

The "fielddefs" table

The properties of each bug field.

Indexes:

The "flagexclusions" table

It may be forbidden to set a given flag on an item (bug or attachment) if that item is in a given product and/or component. This table records such exclusions. See the notes on flags.

Indexes:

The "flaginclusions" table

An item (bug or attachment) may be required to be in a given product and/or component for a flag to be set. This table records such requirements. See the notes on flags.

Indexes:

The "flags" table

This table records the flags set on bugs or attachments. See the notes on flags.

Indexes:

The "flagtypes" table

The types of flags available for bugs and attachments. See the notes on flags.

Indexes:

The "group_control_map" table

This table describes the relationship of groups to products (whether membership in a given group is required for entering or editing a bug in a given product). See the notes on groups.

Indexes:

The "group_group_map" table

Groups can be configured such that membership of one group automatically confers rights over some other groups. This table records that configuration. See the notes on groups.

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

Long bug descriptions.

Indexes:

The "milestones" table

Development milestones.

Indexes:

The "namedqueries" table

Named queries.

Indexes:

The "products" table

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

Indexes:

The "profiles" 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 group membership 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

A table of quips.

Indexes:

The "series" table

Properties of the time-series datasets available (e.g. for plotting charts). See the notes on charts.

Indexes:

The "series_categories" table

Indexes:

The "series_data" table

Data for plotting time-series charts. See the notes on charts.

Indexes:

The "tokens" table

Tokens are sent to users to track activities such as creating new accounts and changing email addresses or passwords. They are also sent to browsers and used to track workflow, to prevent security problems (e.g. so that one can only delete groups from a session last seen on a group management page).

Indexes:

The "user_group_map" table

This table records which users are members of each group, or can "bless" each group. See the notes on groups.

Indexes:

The "versions" table

Product versions.

The "versions" table has no indexes.

The "votes" table

votes.

Indexes:

The "watch" table

watchers.

Indexes:

4. Bugzilla History

Bugzilla releases

This table gives the dates of all the Bugzilla releases since 2.0.

Bugzilla Schema Changes

The schema is identical in Bugzilla releases 2.18rc3 and 2.18.

5. Example queries

To select bug number n:

select * from bugs where bug_id = n

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

select userid, login_name from profiles

To get the email address of user n:

select login_name from profiles where userid = n

To get the set of cc addresses of bug n:

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

To select the long descriptions of bug n, 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 = n order by longdescs.bug_when

To find out the groups of user n:

select group_id from user_group_map where userid = n and isbless=0

A. References

B. Document History

This document is copyright © 2001-2004 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.