Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 1.1 Product Sources / Design

Perforce Defect Tracking Integration Project


Bugzilla database schema

Nick Barnes, Ravenbrook Limited, 2000-11-14

1. Introduction

This document describes the Bugzilla database schema.

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.

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.

Status Resolved? Description Transitions
UNCONFIRMED No A new bug, when a product has voting to NEW by voting or confirmation
to ASSIGNED by acceptance
to RESOLVED by resolution
NEW No Recently added or confirmed to ASSIGNED by acceptance
to RESOLVED by analysis and maybe fixing
to NEW by reassignment
ASSIGNED No Has been assigned to NEW by reassignment
to RESOLVED by analysis and maybe fixing
REOPENED No Was once resolved but has been reopened to NEW by reassignment
to ASSIGNED by acceptance
to RESOLVED by analysis and maybe fixing
RESOLVED Yes Has been resolved (e.g. fixed, deemed unfixable, etc. See "resolution" column) to REOPENED by reopening
to VERIFIED by verification
to CLOSED by closing
VERIFIED Yes The resolution has been approved by QA to CLOSED when the product ships
to REOPENED by reopening
CLOSED Yes Over and done with to REOPENED by reopening
Resolution Meaning
FIXED The bug has been fixed.
INVALID The problem described is not a bug.
WONTFIX This bug will never be fixed.
LATER This bug will not be fixed in this version.
REMIND This bug probably won't be fixed in this version.
DUPLICATE This is a duplicate of an existing bug. (a description comment is added to this effect).
WORKSFORME This bug could not be reproduced.

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 both cleartext and encrypted forms.

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:

Name Description
tweakparams Can tweak operating parameters
editusers Can edit or disable users
creategroups Can create and destroy groups
editcomponents Can create, destroy, and edit components
editkeywords Can create, destroy, and edit keywords
editbugs Can edit all aspects of any bug
canconfirm Can confirm a bug

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:

Value Intended meaning
Blocker Blocks development and/or testing work
Critical Crashes, loss of data, severe memory leak
Major Major loss of function
Minor Minor loss of function, or other problem where easy workaround is present
Trivial Cosmetic problem
Enhancement Request for enhancement

Email notification

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

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

Name Description
attachments Bug attachments.
bugs The bugs themselves.
bugs_activity Activity on the bugs table.
cc Users who have asked to receive email when a bug changes.
components Components of products
dependencies Which bugs depend on other bugs
fielddefs Descriptions of fields in the bugs table. Used for reporting activity.
groups User groups
keyworddefs Descriptions of keywords.
keywords Which bugs have which keywords.
logincookies Record of cookies used for authentication
longdescs Long bug descriptions
milestones Milestones
namedqueries Named queries
products Products
profiles The table of Bugzilla users.
profiles_activity A record of activity in the profiles table.
shadowlog A log of SQL activity; used for updating shadow databases.
versions Product versions.
votes votes
watch watchers

3. The schema

The "attachments" table

Field Type Default Properties Remarks
attach_id mediumint 0 auto_increment a unique ID.
bug_id mediumint 0   the bug to which this is attached (foreign key bugs.bug_id)
creation_ts timestamp None null the creation time.
description mediumtext None   a description of the attachment.
mimetype mediumtext None   the mime type of the attachment.
ispatch tinyint None null non-zero if this attachment is a patch file.
filename mediumtext None   the filename of the attachment.
thedata longblob None   the content of the attachment.
submitter_id mediumint 0   the userid of the attachment (foreign key profiles.userid)

Indexes:

Name Fields Unique?
primary attach_id yes
bug_id bug_id no
creation_ts creation_ts no

The "bugs" table

Field Type Default Properties Remarks
bug_id mediumint 0 auto_increment The bug ID.
groupset bigint 0   The groups which this bug occupies. See the discussion of the table "group".
assigned_to mediumint 0   The current owner of the bug.
bug_file_loc text None null A URL which points to more information about the bug.
bug_severity enum('blocker', 'critical', 'major', 'normal', 'minor', 'trivial', 'enhancement') blocker   See the notes.
bug_status enum('UNCONFIRMED', 'NEW', 'ASSIGNED', 'REOPENED', 'RESOLVED', 'VERIFIED', 'CLOSED') UNCONFIRMED   The workflow status of the bug.
creation_ts datetime 0000-00-00 00:00:00   The times of the bug's creation.
delta_ts timestamp None null The timestamp of the last update. This includes updates to some related tables (e.g. "longdescs").
short_desc mediumtext None null A short description of the bug.
op_sys enum('All', 'Windows 3.1', 'Windows 95', 'Windows 98', 'Windows 2000', 'Windows NT', 'Mac System 7', 'Mac System 7.5', 'Mac System 7.6.1', 'Mac System 8.0', 'Mac System 8.5', 'Mac System 8.6', 'Mac System 9.0', 'AIX', 'BSDI', 'HP-UX', 'IRIX', 'Linux', 'FreeBSD', 'OSF/1', 'Solaris', 'SunOS', 'Neutrino', 'OS/2', 'BeOS', 'OpenVMS', 'other') All   The operating system on which the bug was observed.
priority enum('P1', 'P2', 'P3', 'P4', 'P5') P1   The priority of the bug (P1 = most urgent, P5 = least urgent).
product varchar(64)     The product (foreign key products.product).
rep_platform enum('All', 'DEC', 'HP', 'Macintosh', 'PC', 'SGI', 'Sun', 'Other') None null The platform on which the bug was reported.
reporter mediumint 0   The user who reported this (foreign key profiles.user_id).
version varchar(16)     The product version (foreign key versions.value)
component varchar(50)     The product component (foreign key components.value)
resolution enum('', 'FIXED', 'INVALID', 'WONTFIX', 'LATER', 'REMIND', 'DUPLICATE', 'WORKSFORME')     The bug's resolution
target_milestone varchar(20) ---   The milestone by which this bug should be resolved. (foreign key milestones.value)
qa_contact mediumint 0   The QA contact (foreign key profiles.user_id)
status_whiteboard mediumtext None   This seems to be just a small whiteboard field.
votes mediumint 0   The number of votes.
keywords mediumtext None   A set of keywords (foreign keys keyworddefs.name) Note that this duplicates the information in the "keywords" table.
lastdiffed datetime 0000-00-00 00:00:00   The time at which information about this bug changing was last emailed to the cc list.
everconfirmed tinyint 0   1 if this bug has ever been confirmed. This is used for validation of some sort.

Indexes:

Name Fields Unique?
primary bug_id yes
assigned_to assigned_to no
creation_ts creation_ts no
delta_ts delta_ts no
bug_severity bug_severity no
bug_status bug_status no
op_sys op_sys no
priority priority no
product product no
reporter reporter no
version version no
component component no
resolution resolution no
target_milestone target_milestone no
qa_contact qa_contact no
votes votes no

The "bugs_activity" table

Field Type Default Properties Remarks
bug_id mediumint 0   Which bug (foreign key bugs.bug_id)
who mediumint 0   Which user (foreign key profiles.user_id)
bug_when datetime 0000-00-00 00:00:00   When was the change made?
fieldid mediumint 0   What was the fieldid (foreign key fielddefs.fieldid)
oldvalue tinytext None null The head of the old value
newvalue tinytext None null The head of the new value

Indexes:

Name Fields Unique?
bug_id bug_id no
bug_when bug_when no
fieldid fieldid no

The "cc" table

Field Type Default Properties Remarks
bug_id mediumint 0   The bug (foreign key bugs.bug_id)
who mediumint 0   The user (foreign key profiles.userid)

Indexes:

Name Fields Unique?
who who no
bug_id bug_id, who yes

The "components" table

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

Field Type Default Properties Remarks
value tinytext None null The component name.
program varchar(64) None null The product (foreign key products.product).
initialowner tinytext None   The default initial owner of bugs in this component. On component creation, this is set to the user who creates the component.
initialqacontact tinytext None   The initial "qa_contact" field for bugs of this component. Note that the use of the qa_contact field is optional, parameterized by Param('useqacontact').
description mediumtext None   A description of the component.

The "dependencies" table

Field Type Default Properties Remarks
blocked mediumint 0   Which bug is blocked (foreign key bugs.bug_id)
dependson mediumint 0   Which bug does it depend on (foreign key bugs.bug_id)

Indexes:

Name Fields Unique?
blocked blocked no
dependson dependson no

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.

Field Type Default Properties Remarks
fieldid mediumint 0 auto_increment primary key for this table
name varchar(64)     field name or definition (some fields are names of other tables or of fields in other tables).
description mediumtext None   long description
mailhead tinyint 0   whether or not to send the field description in mail notifications.
sortkey smallint 0   the order of fields in mail notifications.

Indexes:

Name Fields Unique?
primary fieldid yes
name name yes
sortkey sortkey no

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.

Field Type Default Properties Remarks
bit bigint 0   2^n for some n. Assigned automatically.
name varchar(255)     A short name for the group.
description text None   A long description of the group.
isbuggroup tinyint 0   1 if this is a group controlling access to a set of bugs.
userregexp tinytext None   a regexp used to determine membership of new users.

Indexes:

Name Fields Unique?
bit bit yes
name name yes

The "keyworddefs" table

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

Field Type Default Properties Remarks
id smallint 0   A unique number identifying this keyword.
name varchar(64)     The keyword itself.
description mediumtext None null The meaning of the keyword.

Indexes:

Name Fields Unique?
primary id yes
name name yes

The "keywords" table

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

Field Type Default Properties Remarks
bug_id mediumint 0   The bug (foreign key bugs.bug_id)
keywordid smallint 0   The keyword ID (foreign key keyworddefs.id)

Indexes:

Name Fields Unique?
keywordid keywordid no
bug_id bug_id, keywordid yes

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.

Field Type Default Properties Remarks
cookie mediumint 0 auto_increment The cookie
userid mediumint 0   The user id; (foreign key profiles.userid)
cryptpassword varchar(64) None null The encrypted password used on this login.
hostname varchar(128) None null The CGI REMOTE_HOST for this login.
lastused timestamp None null The timestamp of this login.

Indexes:

Name Fields Unique?
primary cookie yes
lastused lastused no

The "longdescs" table

Field Type Default Properties Remarks
bug_id mediumint 0   the bug (foreign key bugs.bug_id)
who mediumint 0   the user who added this text (foreign key profiles.userid)
bug_when datetime 0000-00-00 00:00:00   when the text was added
thetext mediumtext None null the text itself.

Indexes:

Name Fields Unique?
bug_id bug_id no
bug_when bug_when no

The "milestones" table

Field Type Default Properties Remarks
value varchar(20)     The name of the milestone (e.g. "3.1 RTM", "0.1.37", "tweakfor BigCustomer", etc).
product varchar(64)     The product (foreign key products.product)
sortkey smallint 0   A number used for sorting milestones for a given product.

Indexes:

Name Fields Unique?
product product, value yes

The "namedqueries" table

Field Type Default Properties Remarks
userid mediumint 0   The user whose query this is.
name varchar(64)     The name of the query.
watchfordiffs tinyint 0   Unused.
linkinfooter tinyint 0   Whether or not the query should appear in the foot of every page.
query mediumtext None   The query (text to append to the query page URL).

Indexes:

Name Fields Unique?
userid userid, name yes
watchfordiffs watchfordiffs no

The "products" table

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

Field Type Default Properties Remarks
product varchar(64) None null The name of the product
description mediumtext None null The description of the product
milestoneurl tinytext None   The URL of a document describing the product milestones.
disallownew tinyint 0   New bugs can only be created for this product if this is 0.
votesperuser smallint 0   Total votes which a single user has for bugs of this product.
maxvotesperbug smallint 10000   Maximum number of votes which a bug may have.
votestoconfirm smallint 0   How many votes are required for this bug to become NEW.
defaultmilestone varchar(20) ---   The default milestone for a new bug.

The "profiles" table

This table describes Bugzilla users. One row per user.

Field Type Default Properties Remarks
userid mediumint 0 auto_increment A unique identifier for the user. Used in other tables to identify this user.
login_name varchar(255)     The user's email address. Used when logging in or providing mailto: links.
password varchar(16) None null The user's password, in plaintext.
cryptpassword varchar(64) None null The user's password, encrypted with the MySQL encrypt() function.
realname varchar(255) None null The user's real name.
groupset bigint 0   The set of groups to which the user belongs. Each group corresponds to one bit and confers powers upon the user. See the "groups" table.
emailnotification enum('ExcludeSelfChanges', 'CConly', 'All') ExcludeSelfChanges   Controls when email reporting bug changes is sent to this user.
disabledtext mediumtext None   If non-empty, indicates that this account has been disabled and gives a reason.
newemailtech tinyint 0   is non-zero if the user wants to user the "new" email notification technique.
mybugslink tinyint 1   indicates whether a "My Bugs" link should appear at the bottom of each page.
blessgroupset bigint 0   indicates the groups into which this user is able to introduce other users.

Indexes:

Name Fields Unique?
primary userid yes
login_name login_name yes

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.

Field Type Default Properties Remarks
userid mediumint 0   The profile which has changed (foreign key profiles.userid)
who mediumint 0   The user who changed it (foreign key profiles.userid)
profiles_when datetime 0000-00-00 00:00:00   When it was changed
fieldid mediumint 0   The ID of the changed field
oldvalue tinytext None null The old value
newvalue tinytext None null The new value.

Indexes:

Name Fields Unique?
userid userid no
profiles_when profiles_when no
fieldid fieldid no

The "shadowlog" table

Field Type Default Properties Remarks
id int 0 auto_increment unique id
ts timestamp None null timestamp
reflected tinyint 0   0
command mediumtext None   SQL command

Indexes:

Name Fields Unique?
primary id yes
reflected reflected no

The "versions" table

Field Type Default Properties Remarks
value tinytext None null The name of the version
program varchar(64)     The product (foreign key products.product)

The "votes" table

Field Type Default Properties Remarks
who mediumint 0   The user (foreign key profiles.userid).
bug_id mediumint 0   The bug (foreign key bugs.bug_id)
count smallint 0   How many votes.

Indexes:

Name Fields Unique?
who who no
bug_id bug_id no

The "watch" table

Field Type Default Properties Remarks
watcher mediumint 0   The watching user (foreign key profiles.userid)
watched mediumint 0   The watched user (foreign key profiles.userid)

Indexes:

Name Fields Unique?
watched watched no
watcher watcher, watched yes

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

[RB 2000-08-30] "Design document structure" (e-mail message); Richard Brooksby; Ravenbrook Limited; 2000-08-30.
[RB 2000-10-05] "P4DTI Project Design Document Procedure"; Richard Brooksby; Ravenbrook Limited; 2000-10-05.

B. Document History

2000-11-14 NB Created.
2001-03-02 RB Transferred copyright to Perforce under their license.
2001-04-06 NB Added sample queries.

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.

$Id: //info.ravenbrook.com/project/p4dti/version/1.1/design/bugzilla-schema/index.html#5 $

Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 1.1 Product Sources / Design