<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>Bugzilla database schema</title>
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#000099" vlink="#660066" alink="#FF0000">
<div align="center">
<p>
<a href="http://www.ravenbrook.com/">Ravenbrook</a> /
<a href="http://www.ravenbrook.com/project/">Projects</a> /
<a href="http://www.ravenbrook.com/project/p4dti/">Perforce Defect Tracking Integration</a> /
<a href="../../index.html">Version 2.0 Product Sources</a> /
<a href="../index.html">Design</a>
</p>
<p><i><a href="http://www.ravenbrook.com/project/p4dti/">Perforce Defect Tracking Integration Project</a></i></p>
<hr />
<h1>Bugzilla database schema</h1>
<address>
<a href="mailto:nb@ravenbrook.com">Nick Barnes</a>,
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>,
2000-11-14
</address>
</div>
<h2><a id="section-1" name="section-1">1. Introduction</a></h2>
<p>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.16, 2.16.1, 2.16.2, and 2.16.3.</p>
<p>The purpose of this document is to act as a reference while
developing P4DTI code which interacts with Bugzilla.</p>
<p>The intended readership is P4DTI developers.</p>
<p>This document is not confidential.</p>
<h2><a id="section-2" name="section-2">2. Bugzilla overview</a></h2>
<p>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.</p>
<h3><a id="notes-releases" name="notes-releases">Bugzilla Releases</a></h3>
<p>We currently support Bugzilla 2.14.4, 2.14.5, 2.16.1, 2.16.2, and
2.16.3. During the lifetime of this document we have also supported
Bugzilla 2.10, 2.12, 2.14, 2.14.1, 2.14.2, 2.14.3, and 2.16. Where the
schema has been changed, the change is noted in this document. We
need to be able to recognize the schema version.</p>
<p>In Bugzilla release 2.12, the following schema changes were
made:</p>
<ul>
<li>The <a href="#table-duplicates">duplicates</a> table was
added.</li>
<li><a href="#table-profiles">profiles</a>.emailflags was
introduced.</li>
<li>The <a href="#table-bugs">bugs</a>.resolution value <b>MOVED</b>
was introduced.</li>
<li>A number of additional values were permitted in <a
href="#table-bugs">bugs</a>.op_sys.</li>
<li><a href="#table-components">components</a>.initialowner and <a
href="#table-components">components</a>.initialqacontact changed
from "tinytext" (foreign key <a
href="#table-profiles">profiles</a>.login_name) to "mediumint"
(foreign key <a href="#table-profiles">profiles</a>.user_id).</li>
<li><a href="#table-profiles">profiles</a>.disabledtext changed to
"not null".</li>
<li>The default value of <a
href="#table-profiles">profiles</a>.newemailtech changed from 0 to
1.</li>
</ul>
<p>In Bugzilla release 2.14, the following schema changes were
made:</p>
<ul>
<li>The <a href="#table-tokens">tokens</a> table was
introduced.</li>
<li><a href="#table-profiles">profiles</a>.password was removed.</li>
<li><a href="#table-profiles">profiles</a>.cryptpassword and <a
href="#table-logincookies">logincookies</a>.cryptpassword were both
changed from varchar(64) to varchar(32).</li>
<li><a href="#table-profiles">profiles</a>.newemailtech was
removed.</li>
<li><a href="#table-profiles">profiles</a>.emailnotification was
removed.</li>
<li><a href="#table-bugs">bugs</a>.reporter_accessible, <a
href="#table-bugs">bugs</a>.assignee_accessible, <a
href="#table-bugs">bugs</a>.qacontact_accessible, and <a
href="#table-bugs">bugs</a>.cclist_accessible were introduced.</li>
<li><a href="#table-bugs">bugs</a>.version changed from varchar(16)
to varchar(64).</li>
<li><a href="#table-bugs_activity">bugs_activity</a>.oldvalue and <a
href="#table-bugs_activity">bugs_activity</a>.newvalue were replaced
by <a href="#table-bugs_activity">bugs_activity</a>.removed and <a
href="#table-bugs_activity">bugs_activity</a>.added.</li>
<li><a href="#table-groups">groups</a>.isactive was introduced.</li>
<li><a href="#table-longdescs">logincookies</a>.who became an index
field.</li>
<li><a href="#table-profiles">profiles</a>.disabledtext changed back
to "null".</li>
</ul>
<p>The schema is identical in Bugzilla releases 2.14 and 2.14.1.</p>
<p>In Bugzilla release 2.14.2, the following schema change was
made:</p>
<ul>
<li><a href="#table-logincookies">logincookies</a>.hostname was
replaced by <a
href="#table-logincookies">logincookies</a>.ipaddr.</li>
</ul>
<p>The schema is identical in Bugzilla releases 2.14.2, 2.14.3,
2.14.4, and 2.14.5.</p>
<p>In Bugzilla release 2.16, the following schema changes were
made:</p>
<ul>
<li>The <a href="#table-attachstatuses">attachstatuses</a> and<a
href="#table-attachstatusdefs">attachstatusdefs</a> tables were
introduced.</li>
<li><a href="#table-attachments">attachments</a>.isobsolete was
introduced.</li>
<li>The values permitted in <a href="#table-bugs">bugs</a>.op_sys
changed.</li>
<li><a href="#table-bugs">bugs</a>.assignee_accessible and <a
href="#table-bugs">bugs</a>.qacontact_accessible were removed.</li>
<li><a href="#table-bugs_activity">bugs_activity</a>.attach_id was
introduced.</li>
<li><a href="#table-logincookies">logincookies</a>.cryptpassword was
removed.</li>
<li>The possible values of <a
href="#table-tokens">tokens</a>.tokentype changed, to include
'emailold' and 'emailnew' (used when changing the email address of a
Bugzilla user).</li>
</ul>
<p>The schema is identical in Bugzilla releases 2.16, 2.16.1, 2.16.2,
and 2.16.3.</p>
<h3><a id="notes-bugs" name="notes-bugs">Bugs</a></h3>
<p>Each defect is called a <b>bug</b> and corresponds to one row in the
<a href="#table-bugs">bugs table</a>. It is identified by its number,
the "bug_id".</p>
<h3><a id="notes-products" name="notes-products">Products and components</a></h3>
<p>The work managed by Bugzilla is divided into products. Each
product is represented by a row in the <a
href="#table-products">products</a> 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 <a
href="#table-components">components</a> table.</p>
<p>Several properties of a new bug (e.g. ownership) are determined by
the product and component to which it belongs.</p>
<h3><a id="notes-workflow" name="notes-workflow">Workflow</a></h3>
<p>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.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Status</th>
<th>Resolved?</th>
<th>Description</th>
<th>Transitions</th>
</tr>
<tr valign="top" align="left">
<td>UNCONFIRMED</td>
<td>No</td>
<td>A new bug, when a product has voting</td>
<td>to NEW by voting or confirmation<br />
to ASSIGNED by acceptance<br />
to RESOLVED by resolution<br />
</td>
</tr>
<tr valign="top" align="left">
<td>NEW</td>
<td>No</td>
<td>Recently added or confirmed</td>
<td>to ASSIGNED by acceptance<br />
to RESOLVED by analysis and maybe fixing<br />
to NEW by reassignment<br />
</td>
</tr>
<tr valign="top" align="left">
<td>ASSIGNED</td>
<td>No</td>
<td>Has been assigned</td>
<td>to NEW by reassignment<br />
to RESOLVED by analysis and maybe fixing<br />
</td>
</tr>
<tr valign="top" align="left">
<td>REOPENED</td>
<td>No</td>
<td>Was once resolved but has been reopened</td>
<td>to NEW by reassignment<br />
to ASSIGNED by acceptance<br />
to RESOLVED by analysis and maybe fixing<br />
</td>
</tr>
<tr valign="top" align="left">
<td>RESOLVED</td>
<td>Yes</td>
<td>Has been resolved (e.g. fixed, deemed unfixable, etc. See "resolution" column)</td>
<td>to REOPENED by reopening<br />
to VERIFIED by verification<br />
to CLOSED by closing<br />
</td>
</tr>
<tr valign="top" align="left">
<td>VERIFIED</td>
<td>Yes</td>
<td>The resolution has been approved by QA</td>
<td>to CLOSED when the product ships<br />
to REOPENED by reopening<br />
</td>
</tr>
<tr valign="top" align="left">
<td>CLOSED</td>
<td>Yes</td>
<td>Over and done with</td>
<td>to REOPENED by reopening</td>
</tr>
</table>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Resolution</th>
<th>Meaning</th>
</tr>
<tr>
<td>FIXED</td>
<td>The bug has been fixed.</td>
</tr>
<tr>
<td>INVALID</td>
<td>The problem described is not a bug.</td>
</tr>
<tr>
<td>WONTFIX</td>
<td>This bug will never be fixed.</td>
</tr>
<tr>
<td>LATER</td>
<td>This bug will not be fixed in this version.</td>
</tr>
<tr>
<td>REMIND</td>
<td>This bug probably won't be fixed in this version.</td>
</tr>
<tr>
<td>DUPLICATE</td>
<td>This is a duplicate of an existing bug. (a description comment
is added to this effect, and from 2.12 a record is added to the <a href="#table-duplicates">duplicates</a> table).</td>
</tr>
<tr>
<td>WORKSFORME</td>
<td>This bug could not be reproduced.</td>
</tr>
<tr>
<td>MOVED</td>
<td><b>(introduced in 2.12)</b>This bug has been moved to another database.</td>
</tr>
</table>
<h3><a id="notes-users" name="notes-users">Users</a></h3>
<p>Bugzilla has users. Each user is represented by one row in the <a
href="#table-profiles">profiles table</a>. Each user is referred by a
number (the "userid") and an email address (the "login_name").</p>
<h3><a id="notes-authentication" name="notes-authentication">Authentication</a></h3>
<p>Each user has a password, used to authenticate that user to
Bugzilla. The password is stored in the <a
href="#table-profiles">profiles table</a> in encrypted form. Before
version 2.14, it was also stored in plaintext.</p>
<p>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):</p>
<ol>
<li>they have both Bugzilla_login and Bugzilla_logincookie cookies;</li>
<li>their Bugzilla_login is the login_name of a row in the <a href="#table-profiles">profiles table</a>;</li>
<li>their Bugzilla_logincookie matches a row in the <a
href="#table-logincookies">logincookies table</a></li>
<li>the userids of these two rows match;</li>
<li>(before 2.16) the cryptpasswords of these two rows match;</li>
<li>(before 2.14.2 and 2.16) the hostname of the logincookies row
matches the CGI REMOTE_HOST;</li>
<li>(from 2.14.2 and 2.16) the IP address of the logincookies row
matches the CGI REMOTE_ADDR.</li>
</ol>
<p>and also their account is not disabled (i.e. the disabledtext of
the profiles row is empty).</p>
<p>If the cookie checks fail, the user has to login (with their
password), in which case a new row is added to the <a
href="#table-logincookies">logincookies table</a> and the user gets a
new pair of cookies.</p>
<p>Rows in the <a href="#table-logincookies">logincookies table</a>
are deleted after 30 days (at user login time; CGI.pl).</p>
<h3><a id="notes-voting" name="notes-voting">Voting</a></h3>
<p>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:</p>
<p>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.</p>
<p>Votes are recorded in the <a href="#table-votes">votes table.</a></p>
<h3><a id="notes-milestones" name="notes-milestones">Milestones</a></h3>
<p>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".</p>
<p>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.</p>
<p>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).</p>
<p>Milestones are kept in the <a href="#table-milestones">milestones</a> table.</p>
<h3><a id="notes-versions" name="notes-versions">Versions</a></h3>
<p>Products may have versions. This allows more accurate bug
reporting: "we saw it in 1.3.7b3". Versions are totally independent
of milestones.</p>
<h3><a id="notes-parameters" name="notes-parameters">Parameters</a></h3>
<p>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 <b>not</b> stored in the
database.</p>
<h3><a id="notes-groups" name="notes-groups">Groups</a></h3>
<p>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 <a href="#table-groups">groups</a> table.</p>
<p>There are a number of built-in groups, as follows:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr align="left" valign="top">
<th>Name</th>
<th>Description</th>
</tr>
<tr align="left" valign="top">
<td>tweakparams</td>
<td>Can tweak operating parameters</td>
</tr>
<tr align="left" valign="top">
<td>editusers</td>
<td>Can edit or disable users</td>
</tr>
<tr align="left" valign="top">
<td>creategroups</td>
<td>Can create and destroy groups</td>
</tr>
<tr align="left" valign="top">
<td>editcomponents</td>
<td>Can create, destroy, and edit components</td>
</tr>
<tr align="left" valign="top">
<td>editkeywords</td>
<td>Can create, destroy, and edit keywords</td>
</tr>
<tr align="left" valign="top">
<td>editbugs</td>
<td>Can edit all aspects of any bug</td>
</tr>
<tr align="left" valign="top">
<td>canconfirm</td>
<td>Can confirm a bug</td>
</tr>
</table>
<p>Group membership is conferred by the bit being set in the
"groupset" field of the <a href="#table-profiles">profiles</a> 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.</p>
<p>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).</p>
<p>If the parameter "usebuggroups" is on, each product automatically
has a bug group associated with it.</p>
<p>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.</p>
<h3><a id="notes-attachments" name="notes-attachments">Attachments</a></h3>
<p>Users can upload attachments to bugs. An attachments can be marked
as a patch. Attachments are stored in the <a
href="#table-attachments">attachments</a> table. From 2.16,
attachments can be marked as "obsolete".</p>
<p>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 <a
href="#table-attachstatusdefs">attachstatusdefs</a> table. Whether a
given attachment has a given status keyword is defined in the <a
href="#table-attachstatuses">attachstatuses</a> table.</p>
<h3><a id="notes-keywords" name="notes-keywords">Keywords</a></h3>
<p>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 <a
href="#table-keyworddefs">keyworddefs</a> table, and the one-to-many
mapping from bugs to keywords is stored in the <a
href="#table-keywords">keywords</a> table, and also in the "keywords"
field of the <a href="#table-bugs">bugs</a>table.</p>
<h3><a id="notes-dependencies" name="notes-dependencies">Dependencies</a></h3>
<p>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).</p>
<p>Dependencies are recorded in the <a
href="#table-dependencies">dependencies</a>table.</p>
<h3><a id="notes-activity" name="notes-activity">Activity</a></h3>
<p>Bugzilla keeps a record of changes made to bugs. This record is in
the <a href="#table-bugs_activity">bugs_activity</a> table. Each row
in this table records a change to a field in the <a
href="#table-bugs">bugs</a> table. The fields are referred to by a
number which is looked up in the <a
href="#table-fielddefs">fielddefs</a> table. This table records the
name of the field and also a longer description used to display
activity tables.</p>
<h3><a id="notes-severity" name="notes-severity">Severity</a></h3>
<p>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:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr align="left" valign="top">
<th>Value</th>
<th>Intended meaning</th>
</tr>
<tr align="left" valign="top">
<td>Blocker</td>
<td>Blocks development and/or testing work</td>
</tr>
<tr align="left" valign="top">
<td>Critical</td>
<td>Crashes, loss of data, severe memory leak</td>
</tr>
<tr align="left" valign="top">
<td>Major</td>
<td>Major loss of function</td>
</tr>
<tr align="left" valign="top">
<td>Minor</td>
<td>Minor loss of function, or other problem where easy workaround is present</td>
</tr>
<tr align="left" valign="top">
<td>Trivial</td>
<td>Cosmetic problem</td>
</tr>
<tr align="left" valign="top">
<td>Enhancement</td>
<td>Request for enhancement</td>
</tr>
</table>
<h3><a id="notes-email" name="notes-email">Email notification</a></h3>
<p>When a bug changes, email notification is sent out to a number of
users:</p>
<ul>
<li>The bug's owner (the "assigned_to" field)</li>
<li>The bug's reporter</li>
<li>The bug's QA contact (if the "useqacontact" parameter is set)</li>
<li>All the users who have explicitly asked to be notified when the bug changes (these users are stored in the <a href="#table-cc">cc</a> table).</li>
<li>All the users who have voted for this bug</li>
</ul>
<p>This is handled by the "processmail" script.</p>
<h3><a id="notes-descriptions" name="notes-descriptions">Long descriptions</a></h3>
<p>Each bug has a number of comments associated with it. These are
stored in the <a href="#table-longdescs">longdescs</a> 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.</p>
<h3><a id="notes-namedqueries" name="notes-namedqueries">Named queries</a></h3>
<p>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 <a
href="#table-namedqueries">namedqueries</a> table.</p>
<h3><a id="notes-watchers" name="notes-watchers">Watchers</a></h3>
<p>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 <a
href="#notes-email">processmail</a> script.</p>
<h3><a id="notes-shadow" name="notes-shadow">Shadow database</a></h3>
<p>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
<a href="#table-shadowlog">shadowlog</a> table keeps a record of SQL
activity since the last reflection.</p>
<h3><a id="notes-tables" name="notes-tables">List of tables</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Description</th>
</tr>
<tr valign="top" align="left">
<td><a href="#table-attachments">attachments</a></td>
<td>Bug <a href="#notes-attachments">attachments</a>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-attachstatusdefs">attachstatusdefs</a></td>
<td>Attachment status definitions <b>(From 2.16)</b>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-attachstatuses">attachstatuses</a></td>
<td>Attachment statuses <b>(From 2.16)</b>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-bugs">bugs</a></td>
<td>The bugs themselves.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-bugs_activity">bugs_activity</a></td>
<td><a href="#notes-activity">Activity</a> on the bugs table.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-cc">cc</a></td>
<td>Users who have asked to receive <a href="#notes-email">email</a> when a bug changes.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-components">components</a></td>
<td>Components of <a href="#notes-products">products</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-dependencies">dependencies</a></td>
<td>Which bugs <a href="#notes-dependencies">depend</a> on other bugs</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-duplicates">duplicates</a></td>
<td>Which bugs are duplicates of which other bugs <b>(From 2.12)</b></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-fielddefs">fielddefs</a></td>
<td>Descriptions of fields in the <a href="#table-bugs">bugs</a> table. Used for reporting <a href="#notes-activity">activity</a>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-groups">groups</a></td>
<td>User <a href="#notes-groups">groups</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-keyworddefs">keyworddefs</a></td>
<td>Descriptions of <a href="#notes-keywords">keywords</a>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-keywords">keywords</a></td>
<td>Which bugs have which <a href="#notes-keywords">keywords</a>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-logincookies">logincookies</a></td>
<td>Record of cookies used for <a href="#notes-authentication">authentication</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-longdescs">longdescs</a></td>
<td><a href="#notes-descriptions">Long bug descriptions</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-milestones">milestones</a></td>
<td><a href="#notes-milestones">Milestones</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-namedqueries">namedqueries</a></td>
<td><a href="#notes-namedqueries">Named queries</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-products">products</a></td>
<td><a href="#notes-products">Products</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-profiles">profiles</a></td>
<td>The table of Bugzilla <a href="#notes-users">users</a>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-profiles_activity">profiles_activity</a></td>
<td>A record of activity in the profiles table.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-shadowlog">shadowlog</a></td>
<td>A log of SQL activity; used for updating shadow databases.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-tokens">tokens</a></td>
<td>Email tokens <b>(from 2.14)</b>.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-versions">versions</a></td>
<td>Product versions.</td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-votes">votes</a></td>
<td><a href="#notes-voting">votes</a></td>
</tr>
<tr valign="top" align="left">
<td><a href="#table-watch">watch</a></td>
<td><a href="#notes-watchers">watchers</a></td>
</tr>
</table>
<h2><a id="section-3" name="section-3">3. The schema</a></h2>
<h3><a id="table-attachments" name="table-attachments">The "attachments" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>attach_id</td>
<td>mediumint</td>
<td>0</td>
<td>auto_increment</td>
<td>a unique ID.</td>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>the bug to which this is attached (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>creation_ts</td>
<td>timestamp</td>
<td>None</td>
<td>null</td>
<td>the creation time.</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>a description of the attachment.</td>
</tr>
<tr valign="top" align="left">
<td>mimetype</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>the mime type of the attachment.</td>
</tr>
<tr valign="top" align="left">
<td>ispatch</td>
<td>tinyint</td>
<td>None</td>
<td>null</td>
<td>non-zero if this attachment is a patch file.</td>
</tr>
<tr valign="top" align="left">
<td>isobsolete</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td><b>Added in 2.16.</b> Non-zero if this attachment is marked as
obsolete.</td>
</tr>
<tr valign="top" align="left">
<td>filename</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>the filename of the attachment.</td>
</tr>
<tr valign="top" align="left">
<td>thedata</td>
<td>longblob</td>
<td>None</td>
<td> </td>
<td>the content of the attachment.</td>
</tr>
<tr valign="top" align="left">
<td>submitter_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>the userid of the attachment (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>attach_id</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>bug_id</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>creation_ts</td>
<td>creation_ts</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-attachstatusdefs" name="table-attachstatusdefs">The "attachstatusdefs" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>id</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>a unique ID.</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>varchar(50)</td>
<td>''</td>
<td> </td>
<td>the name of the attachment status.</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>mediumtext</td>
<td>None</td>
<td>null</td>
<td>The description of the attachment status.</td>
</tr>
<tr valign="top" align="left">
<td>sortkey</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>A number used to determine the order in which attachment
statuses are shown.</td>
</tr>
<tr valign="top" align="left">
<td>product</td>
<td>varchar(64)</td>
<td>''</td>
<td> </td>
<td>The product for which bugs can have attachments with this
status (foreign key <a
href="#table-products">products</a>.product).</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>id</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-attachstatuses" name="table-attachstatuses">The "attachstatuses" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>attach_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The id of the attachment (foreign key <a
href="#table-attachments">attachments</a>.attach_id).</td>
</tr>
<tr valign="top" align="left">
<td>statusid</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>The id of the status (foreign key <a
href="#table-attachstatusdefs">attachstatusdefs</a>.id).</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>attach_id, statusid</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-bugs" name="table-bugs">The "bugs" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td>auto_increment</td>
<td>The bug ID.</td>
</tr>
<tr valign="top" align="left">
<td>groupset</td>
<td>bigint</td>
<td>0</td>
<td> </td>
<td>The groups which this bug occupies. See the discussion of the
table "group".</td>
</tr>
<tr valign="top" align="left">
<td>assigned_to</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The current owner of the bug.</td>
</tr>
<tr valign="top" align="left">
<td>bug_file_loc</td>
<td>text</td>
<td>None</td>
<td>null</td>
<td>A URL which points to more information about the bug.</td>
</tr>
<tr valign="top" align="left">
<td>bug_severity</td>
<td>enum('blocker', 'critical', 'major', 'normal', 'minor', 'trivial', 'enhancement')</td>
<td>blocker</td>
<td> </td>
<td>See the <a href="#notes-severity">notes</a>.</td>
</tr>
<tr valign="top" align="left">
<td>bug_status</td>
<td>enum('UNCONFIRMED', 'NEW', 'ASSIGNED', 'REOPENED', 'RESOLVED', 'VERIFIED', 'CLOSED')</td>
<td>UNCONFIRMED</td>
<td> </td>
<td>The <a href="#notes-workflow">workflow</a> status of the bug.</td>
</tr>
<tr valign="top" align="left">
<td>creation_ts</td>
<td>datetime</td>
<td>0000-00-00 00:00:00</td>
<td> </td>
<td>The times of the bug's creation.</td>
</tr>
<tr valign="top" align="left">
<td>delta_ts</td>
<td>timestamp</td>
<td>None</td>
<td>null</td>
<td>The timestamp of the last update. This includes updates to
some related tables (e.g. "longdescs").</td>
</tr>
<tr valign="top" align="left">
<td>short_desc</td>
<td>mediumtext</td>
<td>None</td>
<td>null</td>
<td>A short description of the bug.</td>
</tr>
<tr valign="top" align="left">
<td>op_sys</td> <td><b>2.10 has:</b> 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');
<b>2.12</b> adds 'NetBSD', 'OpenBSD', 'Windows ME'; <b>2.16</b>
replaces 'Mac System 9.0' with 'Mac System 9.x' and adds 'MacOS X'
and 'Windows XP'.</td> <td>All</td> <td> </td>
<td>The operating system on which the bug was observed.</td>
</tr>
<tr valign="top" align="left">
<td>priority</td>
<td>enum('P1', 'P2', 'P3', 'P4', 'P5')</td>
<td>P1</td>
<td> </td>
<td>The priority of the bug (P1 = most urgent, P5 = least urgent).</td>
</tr>
<tr valign="top" align="left">
<td>product</td>
<td>varchar(64)</td>
<td> </td>
<td> </td>
<td>The product (foreign key <a
href="#table-products">products</a>.product).</td>
</tr>
<tr valign="top" align="left">
<td>rep_platform</td>
<td>enum('All', 'DEC', 'HP', 'Macintosh', 'PC', 'SGI', 'Sun', 'Other')</td>
<td>None</td>
<td>null</td>
<td>The platform on which the bug was reported.</td>
</tr>
<tr valign="top" align="left">
<td>reporter</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user who reported this (foreign key <a
href="#table-profiles">profiles</a>.user_id).</td>
</tr>
<tr valign="top" align="left">
<td>version</td>
<td><b>2.10, 2.12:</b>varchar(16); <b>from 2.14:</b>varchar(64)</td>
<td> </td>
<td> </td>
<td>The product version (foreign key <a
href="#table-versions">versions</a>.value)</td>
</tr>
<tr valign="top" align="left">
<td>component</td>
<td>varchar(50)</td>
<td> </td>
<td> </td>
<td>The product component (foreign key <a
href="#table-components">components</a>.value)</td>
</tr>
<tr valign="top" align="left">
<td>resolution</td>
<td><b>2.10:</b> enum('', 'FIXED', 'INVALID', 'WONTFIX', 'LATER', 'REMIND', 'DUPLICATE', 'WORKSFORME'); <b>2.12 adds</b> 'MOVED'</td>
<td> </td>
<td> </td>
<td>The bug's <a href="#notes-workflow">resolution</a></td>
</tr>
<tr valign="top" align="left">
<td>target_milestone</td>
<td>varchar(20)</td>
<td>---</td>
<td> </td>
<td>The milestone by which this bug should be resolved. (foreign
key <a href="#table-milestones">milestones</a>.value)</td>
</tr>
<tr valign="top" align="left">
<td>qa_contact</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The QA contact (foreign key <a
href="#table-profiles">profiles</a>.user_id)</td>
</tr>
<tr valign="top" align="left">
<td>status_whiteboard</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>This seems to be just a small whiteboard field.</td>
</tr>
<tr valign="top" align="left">
<td>votes</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The number of votes.</td>
</tr>
<tr valign="top" align="left">
<td>keywords</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>A set of keywords (foreign keys <a
href="#table-keyworddefs">keyworddefs</a>.name) Note that this
duplicates the information in the "keywords" table.</td>
</tr>
<tr valign="top" align="left">
<td>lastdiffed</td>
<td>datetime</td>
<td>0000-00-00 00:00:00</td>
<td> </td>
<td>The time at which information about this bug changing was last
emailed to the cc list.</td>
</tr>
<tr valign="top" align="left">
<td>everconfirmed</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>1 if this bug has ever been confirmed. This is used for
validation of some sort.</td>
</tr>
<tr valign="top" align="left">
<td>reporter_accessible</td>
<td>tinyint</td>
<td>1</td>
<td> </td>
<td><b>Introduced in 2.14.</b> 1 if the reporter can see this bug (even if in the wrong group); 0 otherwise</td>
</tr>
<tr valign="top" align="left">
<td>assignee_accessible</td>
<td>tinyint</td>
<td>1</td>
<td> </td>
<td><b>Introduced in 2.14. Removed in 2.16.</b> 1 if the assignee can see this bug (even if in the wrong group); 0 otherwise</td>
</tr>
<tr valign="top" align="left">
<td>qacontact_accessible</td>
<td>tinyint</td>
<td>1</td>
<td> </td>
<td><b>Introduced in 2.14. Removed in 2.16.</b> 1 if the QA contact can see this bug (even if in the wrong group); 0 otherwise</td>
</tr>
<tr valign="top" align="left">
<td>cclist_accessible</td>
<td>tinyint</td>
<td>1</td>
<td> </td>
<td><b>Introduced in 2.14.</b> 1 if people on the CC list can see this bug (even if in the wrong group); 0 otherwise</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>bug_id</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>assigned_to</td>
<td>assigned_to</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>creation_ts</td>
<td>creation_ts</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>delta_ts</td>
<td>delta_ts</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_severity</td>
<td>bug_severity</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_status</td>
<td>bug_status</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>op_sys</td>
<td>op_sys</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>priority</td>
<td>priority</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>product</td>
<td>product</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>reporter</td>
<td>reporter</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>version</td>
<td>version</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>component</td>
<td>component</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>resolution</td>
<td>resolution</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>target_milestone</td>
<td>target_milestone</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>qa_contact</td>
<td>qa_contact</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>votes</td>
<td>votes</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-bugs_activity" name="table-bugs_activity">The "bugs_activity" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>Which bug (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>Which user (foreign key <a
href="#table-profiles">profiles</a>.user_id)</td>
</tr>
<tr valign="top" align="left">
<td>bug_when</td>
<td>datetime</td>
<td>0000-00-00 00:00:00</td>
<td> </td>
<td>When was the change made?</td>
</tr>
<tr valign="top" align="left">
<td>fieldid</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>What was the fieldid (foreign key <a
href="#table-fielddefs">fielddefs</a>.fieldid)</td>
</tr>
<tr valign="top" align="left">
<td>attach_id</td>
<td>mediumint</td>
<td>None</td>
<td>null</td>
<td><b>Added in 2.16.</b> If the change was to an attachment, was
was the ID of the attachment (foreign key <a
href="#table-attachments">attachments</a>.attach_id). Otherwise,
NULL.</td>
</tr>
<tr valign="top" align="left">
<td>oldvalue</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td>The head of the old value. <b>Removed in 2.14 (replaced by 'removed').</b></td>
</tr>
<tr valign="top" align="left">
<td>newvalue</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td>The head of the new value. <b>Removed in 2.14 (replaced by 'added').</b></td>
</tr>
<tr valign="top" align="left">
<td>added</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td><b>Introduced in 2.14 (replaces 'newvalue')</b>. The new value of this field, or values which have been added for multi-value fields such as keywords, cc list, and dependencies.</td>
</tr>
<tr valign="top" align="left">
<td>removed</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td><b>Introduced in 2.14 (replaces 'oldvalue')</b>. The old value of this field, or values which have been removed for multi-value fields such as keywords, cc list, and dependencies.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>bug_id</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_when</td>
<td>bug_when</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>fieldid</td>
<td>fieldid</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-cc" name="table-cc">The "cc" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The bug (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>who</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>bug_id, who</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-components" name="table-components">The "components" table</a></h3>
<p>One row for each component. See <a href="#notes-products">the
notes on products and components.</a></p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>value</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td>The component name.</td>
</tr>
<tr valign="top" align="left">
<td>program</td>
<td>varchar(64)</td>
<td>None</td>
<td>null</td>
<td>The product (foreign key <a
href="#table-products">products</a>.product).</td>
</tr>
<tr valign="top" align="left">
<td>initialowner</td>
<td><p><b>2.10:</b>tinytext</p>
<p><b>from 2.12:</b>mediumint</p></td>
<td>None</td>
<td> </td>
<td>The default initial owner of bugs in this component. On
component creation, this is set to the user who creates the
component.</td>
</tr>
<tr valign="top" align="left">
<td>initialqacontact</td>
<td><p><b>2.10:</b>tinytext</p>
<p><b>from 2.12:</b>mediumint</p></td>
<td>None</td>
<td> </td>
<td>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').</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>A description of the component.</td>
</tr>
</table>
<h3><a id="table-dependencies" name="table-dependencies">The "dependencies" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>blocked</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>Which bug is blocked (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>dependson</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>Which bug does it depend on (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>blocked</td>
<td>blocked</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>dependson</td>
<td>dependson</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-duplicates" name="table-duplicates">The "duplicates" table</a></h3>
<p>This table identifies bugs which are duplicates of other bugs. It
was introduced in <b>2.12</b></p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr>
<td>dupe_of</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The bug which is duplicated (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr>
<td>dupe</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The duplicate bug (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>dupe</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-fielddefs" name="table-fielddefs">The "fielddefs" table</a></h3>
<p>This is a table of the fields for which we update the <a
href="#table-bugs_activity">bugs_activity</a> table. It's used to
turn bugs_activity entries into useful text.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>fieldid</td>
<td>mediumint</td>
<td>0</td>
<td>auto_increment</td>
<td>primary key for this table</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>varchar(64)</td>
<td> </td>
<td> </td>
<td>field name or definition (some fields are names of other
tables or of fields in other tables).</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>long description</td>
</tr>
<tr valign="top" align="left">
<td>mailhead</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>whether or not to send the field description in mail
notifications.</td>
</tr>
<tr valign="top" align="left">
<td>sortkey</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>the order of fields in mail notifications.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>fieldid</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>name</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>sortkey</td>
<td>sortkey</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-groups" name="table-groups">The "groups" table</a></h3>
<p>This table describes a number of user groups. Each group allows
its members to perform a restricted activity. See <a
href="#notes-groups">the notes on groups</a>. </p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>bit</td>
<td>bigint</td>
<td>0</td>
<td> </td>
<td>2^n for some n. Assigned automatically.</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>varchar(255)</td>
<td> </td>
<td> </td>
<td>A short name for the group.</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>text</td>
<td>None</td>
<td> </td>
<td>A long description of the group.</td>
</tr>
<tr valign="top" align="left">
<td>isbuggroup</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>1 if this is a group controlling access to a set of bugs.</td>
</tr>
<tr valign="top" align="left">
<td>userregexp</td>
<td>tinytext</td>
<td>None</td>
<td> </td>
<td>a regexp used to determine membership of new users.</td>
</tr>
<tr valign="top" align="left">
<td>isactive</td>
<td>tinyint</td>
<td>1</td>
<td> </td>
<td><b>from 2.14.</b> 1 if the bugs can be added to this group; 0 otherwise.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>bit</td>
<td>bit</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>name</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-keyworddefs" name="table-keyworddefs">The "keyworddefs" table</a></h3>
<p>Names and definitions of the keywords. See <a
href="#notes-keywords">the notes on keywords</a>.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>id</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>A unique number identifying this keyword.</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>varchar(64)</td>
<td> </td>
<td> </td>
<td>The keyword itself.</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>mediumtext</td>
<td>None</td>
<td>null</td>
<td>The meaning of the keyword.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>id</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>name</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-keywords" name="table-keywords">The "keywords" table</a></h3>
<p>Bugs may have keywords. This table defines which bugs have which
keywords. The keywords are defined in the <a
href="#table-keyworddefs">keyworddefs</a> table..</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The bug (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>keywordid</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>The keyword ID (foreign key <a
href="#table-keyworddefs">keyworddefs</a>.id)</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>keywordid</td>
<td>keywordid</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>bug_id, keywordid</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-logincookies" name="table-logincookies">The "logincookies" table</a></h3>
<p>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 <a href="#notes-authentication">the notes on authentication</a>.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>cookie</td>
<td>mediumint</td>
<td>0</td>
<td>auto_increment</td>
<td>The cookie</td>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user id; (foreign key <a href="#table-profiles">profiles</a>.userid)</td>
</tr>
<tr valign="top" align="left">
<td>cryptpassword</td>
<td><b>2.10, 2.12:</b>varchar(64);
<b>from 2.14:</b>varchar(34)</td>
<td>None</td>
<td>null</td>
<td>The encrypted password used on this login. <b>Removed in
2.16</b>.</td>
</tr>
<tr valign="top" align="left">
<td>hostname</td>
<td>varchar(128)</td>
<td>None</td>
<td>null</td>
<td>The CGI REMOTE_HOST for this login. <b>Removed in 2.16 and 2.14.2</b>
(replaced by ipaddr).</td>
</tr>
<tr valign="top" align="left">
<td>ipaddr</td>
<td>varchar(40)</td>
<td>''</td>
<td> </td>
<td>The CGI REMOTE_ADDR for this login. <b>Added in 2.16 and 2.14.2</b>
(replacing hostname).</td>
</tr>
<tr valign="top" align="left">
<td>lastused</td>
<td>timestamp</td>
<td>None</td>
<td>null</td>
<td>The timestamp of this login.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>cookie</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>lastused</td>
<td>lastused</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-longdescs" name="table-longdescs">The "longdescs" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>the bug (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>the user who added this text (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
<tr valign="top" align="left">
<td>bug_when</td>
<td>datetime</td>
<td>0000-00-00 00:00:00</td>
<td> </td>
<td>when the text was added</td>
</tr>
<tr valign="top" align="left">
<td>thetext</td>
<td>mediumtext</td>
<td>None</td>
<td>null</td>
<td>the text itself.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>bug_id</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td><b>from 2.14:</b>who</td>
<td>who</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_when</td>
<td>bug_when</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-milestones" name="table-milestones">The "milestones" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>value</td>
<td>varchar(20)</td>
<td> </td>
<td> </td>
<td>The name of the milestone (e.g. "3.1 RTM", "0.1.37", "tweakfor
BigCustomer", etc).</td>
</tr>
<tr>
<td>product</td>
<td>varchar(64)</td>
<td> </td>
<td> </td>
<td>The product (foreign key <a
href="#table-products">products</a>.product)</td>
</tr>
<tr valign="top" align="left">
<td>sortkey</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>A number used for sorting milestones for a given product.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>product</td>
<td>product, value</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-namedqueries" name="table-namedqueries">The "namedqueries" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user whose query this is.</td>
</tr>
<tr valign="top" align="left">
<td>name</td>
<td>varchar(64)</td>
<td> </td>
<td> </td>
<td>The name of the query.</td>
</tr>
<tr valign="top" align="left">
<td>watchfordiffs</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>Unused.</td>
</tr>
<tr valign="top" align="left">
<td>linkinfooter</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>Whether or not the query should appear in the foot of every
page.</td>
</tr>
<tr valign="top" align="left">
<td>query</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>The query (text to append to the query page URL).</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>userid, name</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>watchfordiffs</td>
<td>watchfordiffs</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-products" name="table-products">The "products" table</a></h3>
<p>One row for each product. See <a href="#notes-products">the notes
on products.</a></p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>product</td>
<td>varchar(64)</td>
<td>None</td>
<td>null</td>
<td>The name of the product</td>
</tr>
<tr valign="top" align="left">
<td>description</td>
<td>mediumtext</td>
<td>None</td>
<td>null</td>
<td>The description of the product</td>
</tr>
<tr valign="top" align="left">
<td>milestoneurl</td>
<td>tinytext</td>
<td>None</td>
<td> </td>
<td>The URL of a document describing the product milestones.</td>
</tr>
<tr valign="top" align="left">
<td>disallownew</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>New bugs can only be created for this product if this is
0.</td>
</tr>
<tr valign="top" align="left">
<td>votesperuser</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>Total votes which a single user has for bugs of this
product.</td>
</tr>
<tr valign="top" align="left">
<td>maxvotesperbug</td>
<td>smallint</td>
<td>10000</td>
<td> </td>
<td>Maximum number of votes which a bug may have.</td>
</tr>
<tr valign="top" align="left">
<td>votestoconfirm</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>How many votes are required for this bug to become NEW.</td>
</tr>
<tr valign="top" align="left">
<td>defaultmilestone</td>
<td>varchar(20)</td>
<td>---</td>
<td> </td>
<td>The default milestone for a new bug.</td>
</tr>
</table>
<h3><a id="table-profiles" name="table-profiles">The "profiles" table</a></h3>
<p>This table describes Bugzilla users. One row per user.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>mediumint</td>
<td>0</td>
<td>auto_increment</td>
<td>A unique identifier for the user. Used in other tables to identify this user.</td>
</tr>
<tr valign="top" align="left">
<td>login_name</td>
<td>varchar(255)</td>
<td> </td>
<td> </td>
<td>The user's email address. Used when logging in or providing mailto: links.</td>
</tr>
<tr valign="top" align="left">
<td>password</td>
<td>varchar(16)</td>
<td>None</td>
<td>null</td>
<td>The user's password, in plaintext. <b>Removed in 2.14.</b></td>
</tr>
<tr valign="top" align="left">
<td>cryptpassword</td>
<td><b>2.10, 2.12:</b>varchar(64);
<b>from 2.14:</b>varchar(34)</td>
<td>None</td>
<td>null</td>
<td>The user's password. Before 2.14, the MySQL function
<code>encrypt</code> was used to encrypt passwords. From 2.14,
the Perl function <code>crypt</code> is used instead.</td>
</tr>
<tr valign="top" align="left">
<td>realname</td>
<td>varchar(255)</td>
<td>None</td>
<td>null</td>
<td>The user's real name.</td>
</tr>
<tr valign="top" align="left">
<td>groupset</td>
<td>bigint</td>
<td>0</td>
<td> </td>
<td>The set of groups to which the user belongs. Each group
corresponds to one bit and confers powers upon the user. See the <a
href="#table-groups">"groups" table</a>.
</td>
</tr>
<tr valign="top" align="left">
<td>emailnotification</td>
<td>enum('ExcludeSelfChanges', 'CConly', 'All')</td>
<td>ExcludeSelfChanges</td>
<td> </td>
<td>Controls when email reporting bug changes is sent to this user. <b>Removed in 2.14 (replaced in part by emailflags)</b>
</td>
</tr>
<tr valign="top" align="left">
<td>disabledtext</td>
<td>mediumtext</td>
<td>None</td>
<td>null except in <b>2.12</b></td>
<td>If non-empty, indicates that this account has been disabled
and gives a reason. </td>
</tr>
<tr valign="top" align="left">
<td>newemailtech</td>
<td>tinyint</td>
<td><b>2.10:</b>0; <b>2.12:</b> 1</td>
<td> </td>
<td>is non-zero if the user wants to user the "new" email
notification technique. <b>Removed in 2.14</b></td>
</tr>
<tr valign="top" align="left">
<td>mybugslink</td>
<td>tinyint</td>
<td>1</td>
<td> </td>
<td>indicates whether a "My Bugs" link should appear at the bottom
of each page.</td>
</tr>
<tr valign="top" align="left">
<td>blessgroupset</td>
<td>bigint</td>
<td>0</td>
<td> </td>
<td>indicates the groups into which this user is able to introduce
other users.</td>
</tr>
<tr valign="top" align="left">
<td>emailflags</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>Flags controlling when email messages are sent to this user.
<b>Introduced in 2.12</b></td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>userid</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>login_name</td>
<td>login_name</td>
<td>yes</td>
</tr>
</table>
<h3><a id="table-profiles_activity" name="table-profiles_activity">The "profiles_activity" table</a></h3>
<p>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.</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The profile which has changed (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user who changed it (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
<tr valign="top" align="left">
<td>profiles_when</td>
<td>datetime</td>
<td>0000-00-00 00:00:00</td>
<td> </td>
<td>When it was changed</td>
</tr>
<tr valign="top" align="left">
<td>fieldid</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The ID of the changed field</td>
</tr>
<tr valign="top" align="left">
<td>oldvalue</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td>The old value</td>
</tr>
<tr valign="top" align="left">
<td>newvalue</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td>The new value.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>userid</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>profiles_when</td>
<td>profiles_when</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>fieldid</td>
<td>fieldid</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-shadowlog" name="table-shadowlog">The "shadowlog" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>id</td>
<td>int</td>
<td>0</td>
<td>auto_increment</td>
<td>unique id</td>
</tr>
<tr valign="top" align="left">
<td>ts</td>
<td>timestamp</td>
<td>None</td>
<td>null</td>
<td>timestamp</td>
</tr>
<tr valign="top" align="left">
<td>reflected</td>
<td>tinyint</td>
<td>0</td>
<td> </td>
<td>0</td>
</tr>
<tr valign="top" align="left">
<td>command</td>
<td>mediumtext</td>
<td>None</td>
<td> </td>
<td>SQL command</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>id</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>reflected</td>
<td>reflected</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-tokens" name="table-tokens">The "tokens" table</a></h3>
<p>Used for managing the tokens sent to users who wish to change their
password (or email address, <b>from 2.16</b>. <b>Introduced in
2.14.</b></p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user to whom the token was issued. (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
<tr valign="top" align="left">
<td>issuedate</td>
<td>datetime</td>
<td>0000-00-00 00:00:00</td>
<td> </td>
<td>The date at which the token was issued</td>
</tr>
<tr valign="top" align="left">
<td>token</td>
<td>varchar(16)</td>
<td>''</td>
<td> </td>
<td>The token itself.</td>
</tr>
<tr valign="top" align="left">
<td>tokentype</td>
<td>varchar(8)</td>
<td>''</td>
<td> </td>
<td>The type of the token. <b>Before 2.16:</b> only
'password'.<b>From 2.16:</b> also 'emailold' and 'emailnew'.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>primary</td>
<td>token</td>
<td>yes</td>
</tr>
<tr valign="top" align="left">
<td>userid</td>
<td>userid</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-versions" name="table-versions">The "versions" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>value</td>
<td>tinytext</td>
<td>None</td>
<td>null</td>
<td>The name of the version</td>
</tr>
<tr valign="top" align="left">
<td>program</td>
<td>varchar(64)</td>
<td> </td>
<td> </td>
<td>The product (foreign key <a
href="#table-products">products</a>.product)</td>
</tr>
</table>
<h3><a id="table-votes" name="table-votes">The "votes" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The user (foreign key <a
href="#table-profiles">profiles</a>.userid).</td>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The bug (foreign key <a
href="#table-bugs">bugs</a>.bug_id)</td>
</tr>
<tr valign="top" align="left">
<td>count</td>
<td>smallint</td>
<td>0</td>
<td> </td>
<td>How many votes.</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>who</td>
<td>who</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>bug_id</td>
<td>bug_id</td>
<td>no</td>
</tr>
</table>
<h3><a id="table-watch" name="table-watch">The "watch" table</a></h3>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Field</th>
<th>Type</th>
<th>Default</th>
<th>Properties</th>
<th>Remarks</th>
</tr>
<tr valign="top" align="left">
<td>watcher</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The watching user (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
<tr valign="top" align="left">
<td>watched</td>
<td>mediumint</td>
<td>0</td>
<td> </td>
<td>The watched user (foreign key <a
href="#table-profiles">profiles</a>.userid)</td>
</tr>
</table>
<p>Indexes:</p>
<table border="1" cellspacing="0" cellpadding="5">
<tr valign="top" align="left">
<th>Name</th>
<th>Fields</th>
<th>Unique?</th>
</tr>
<tr valign="top" align="left">
<td>watched</td>
<td>watched</td>
<td>no</td>
</tr>
<tr valign="top" align="left">
<td>watcher</td>
<td>watcher, watched</td>
<td>yes</td>
</tr>
</table>
<h2><a id="section-4" name="section-4">4. Example queries</a></h2>
<p>To select a bug given the bug number:</p>
<blockquote><code>
select * from bugs where bug_id = %d
</code></blockquote>
<p>To select the long descriptions of a bug, together with the name
and email address of the commenters:</p>
<blockquote><code>
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
</code></blockquote>
<p>To get a complete list of user ids and email addresses:</p>
<blockquote><code>
select userid, login_name from profiles
</code></blockquote>
<p>To find out a user's groupset:</p>
<blockquote><code>
select groupset from profiles where userid = %d
</code></blockquote>
<p>To get a user's email address:</p>
<blockquote><code>
select login_name from profiles where userid = %d
</code></blockquote>
<p>To get the set of cc addresses for a given bug:</p>
<blockquote><code>
select login_name from cc, profiles
where cc.bug_id = %d
and profiles.userid = cc.who
</code></blockquote>
<h2><a id="section-A" name="section-A">A. References</a></h2>
<table>
<tr valign="top">
<td>[<a id="ref-RB-2000-08-30" name="ref-RB-2000-08-30" href="http://www.ravenbrook.com/project/p4dti/doc/2000-08-30/design-document-structure/">RB 2000-08-30</a>]</td>
<td>
"Design document structure" (e-mail message);
<a href="mailto:rb@ravenbrook.com">Richard Brooksby</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-08-30.
</td>
</tr>
<tr valign="top">
<td>[<a id="ref-RB-2000-10-05" name="ref-RB-2000-10-05" href="http://www.ravenbrook.com/project/p4dti/procedure/design-document/">RB 2000-10-05</a>]</td>
<td>
"P4DTI Project Design Document Procedure";
<a href="mailto:rb@ravenbrook.com">Richard Brooksby</a>;
<a href="http://www.ravenbrook.com/">Ravenbrook Limited</a>;
2000-10-05.
</td>
</tr>
</table>
<h2><a id="section-B" name="section-B">B. Document History</a></h2>
<table>
<tr valign="top">
<td>2000-11-14</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Created.</td>
</tr>
<tr valign="top">
<td> 2001-03-02 </td>
<td> <a href="mailto:rb@ravenbrook.com">RB</a> </td>
<td> Transferred copyright to Perforce under their license. </td>
</tr>
<tr valign="top">
<td> 2001-04-06 </td>
<td> <a href="mailto:nb@ravenbrook.com">NB</a> </td>
<td> Added sample queries. </td>
</tr>
<tr valign="top">
<td>2001-09-12</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Updated to reflect schema updates in Bugzilla 2.12 and 2.14</td>
</tr>
<tr valign="top">
<td>2002-01-31</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Added notes on Bugzilla 2.14.1.</td>
</tr>
<tr valign="top">
<td>2002-05-31</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Updated for Bugzilla 2.16 (based on 2.16rc1).</td>
</tr>
<tr valign="top">
<td>2002-09-26</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Updated for Bugzilla 2.16/2.14.2/2.14.3.</td>
</tr>
<tr valign="top">
<td>2002-10-04</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Added notes on Bugzilla 2.14.4 and 2.16.1, and on identical schemas.</td>
</tr>
<tr valign="top">
<td>2003-05-14</td>
<td><a href="mailto:nb@ravenbrook.com">NB</a></td>
<td>Added extensive notes on schema changes, in section 2.</td>
</tr>
</table>
<hr />
<p> <small>This document is copyright © 2001, 2002 Perforce Software, Inc. All rights reserved.</small> </p>
<p> <small>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.</small> </p>
<p> <small> <strong> 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. </strong> </small> </p>
<div align="center">
<p><code>$Id: //info.ravenbrook.com/project/p4dti/version/2.0/design/bugzilla-schema/index.html#2 $</code></p>
<p>
<a href="http://www.ravenbrook.com/">Ravenbrook</a> /
<a href="http://www.ravenbrook.com/project/">Projects</a> /
<a href="http://www.ravenbrook.com/project/p4dti/">Perforce Defect Tracking Integration</a> /
<a href="../../index.html">Version 2.0 Product Sources</a> /
<a href="../index.html">Design</a>
</p>
</div>
</body>
</html>