Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 1.0 Product Sources / Design
Perforce Defect Tracking Integration Project
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.
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.
Each defect is called a bug and corresponds to one row in the bugs table. It is identified by its number, the "bug_id".
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.
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. |
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").
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):
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).
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.
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.
Products may have versions. This allows more accurate bug reporting: "we saw it in 1.3.7b3". Versions are totally independent of milestones.
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.
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.
Users can upload attachments to bugs. An attachments can be marked as a patch. Attachments are stored in the attachmments table.
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.
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.
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.
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 |
When a bug changes, email notification is sent out to a number of users:
This is handled by the "processmail" script.
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.
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.
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.
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.
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 | |
shadowlog | |
versions | |
votes | |
watch |
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 |
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 (status = RESOLVED, VERIFIED, or CLOSED) | ||
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 |
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 |
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 |
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. |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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. |
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 |
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 |
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 |
Field | Type | Default | Properties | Remarks |
---|---|---|---|---|
value | tinytext | None | null | The name of the version |
program | varchar(64) | The product (foreign key products.product) |
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 |
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 |
placeholder
[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. |
2000-11-14 | NB | Created. |
2001-03-02 | RB | Transferred copyright to Perforce under their license. |
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.0/design/bugzilla-schema/index.html#6 $
Ravenbrook / Projects / Perforce Defect Tracking Integration / Version 1.0 Product Sources / Design