Bugzilla Schema

Quick links to table definitions:

1. Introduction

This document describes the Bugzilla database schema.

This document is generated automatically by a Perl script which constructs and colors the schema tables from a DBIx::Class::Schema object.

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

The intended readership is Bugzilla developers and administrators.

This document is not confidential.

Please send any comments and/or problem reports to emmanuel+bzschema@seyman.fr.

2. Bugzilla overview

Bugzilla is a web-based general-purpose bugtracker and testing tool licensed under the MPL license.

3. The schema

The "attach_data" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, FOREIGN KEY
thedata longblob NOT NULL

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
id foreign.attach_id => self.id

The "attachments" table

Name Type Info
attach_id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
bug_id mediumint NOT NULL, FOREIGN KEY
creation_ts datetime NOT NULL
modification_time datetime NOT NULL
description tinytext NOT NULL
mimetype tinytext NOT NULL
ispatch tinyint NOT NULL, DEFAULT VALUE '''''0'''''
filename varchar (255) NOT NULL
submitter_id mediumint NOT NULL, FOREIGN KEY
isobsolete tinyint NOT NULL, DEFAULT VALUE '''''0'''''
isprivate tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
primary attach_id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
submitter foreign.userid => self.submitter_id
might_have
Name Foreign Class Join Condition
attach_data foreign.id => self.attach_id
has_many
Name Foreign Class Join Condition
bugs_activities foreign.attach_id => self.attach_id
flags foreign.attach_id => self.attach_id

The "audit_log" table

Name Type Info
user_id mediumint FOREIGN KEY
class varchar (255) NOT NULL
object_id integer NOT NULL
field varchar (64) NOT NULL
removed mediumtext
added mediumtext
at_time datetime NOT NULL

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id

The "bugs" table

Name Type Info
bug_id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
assigned_to mediumint NOT NULL, FOREIGN KEY
bug_file_loc mediumtext NOT NULL, DEFAULT VALUE ''''''''''''
bug_severity varchar (64) NOT NULL
bug_status varchar (64) NOT NULL
creation_ts datetime
delta_ts datetime NOT NULL
short_desc varchar (255) NOT NULL
op_sys varchar (64) NOT NULL
priority varchar (64) NOT NULL
product_id smallint NOT NULL, FOREIGN KEY
rep_platform varchar (64) NOT NULL
reporter mediumint NOT NULL, FOREIGN KEY
version varchar (64) NOT NULL
component_id mediumint NOT NULL, FOREIGN KEY
resolution varchar (64) NOT NULL, DEFAULT VALUE ''''''''''
target_milestone varchar (64) NOT NULL, DEFAULT VALUE '''''---'''''
qa_contact mediumint FOREIGN KEY
status_whiteboard mediumtext NOT NULL, DEFAULT VALUE ''''''''''''
lastdiffed datetime
everconfirmed tinyint NOT NULL
reporter_accessible tinyint NOT NULL, DEFAULT VALUE '''''1'''''
cclist_accessible tinyint NOT NULL, DEFAULT VALUE '''''1'''''
estimated_time decimal (7.2) NOT NULL, DEFAULT VALUE '''''0.00'''''
remaining_time decimal (7.2) NOT NULL, DEFAULT VALUE '''''0.00'''''
deadline datetime

Unique Constraints

NameColumns
primary bug_id

Relationships

belongs_to
Name Foreign Class Join Condition
assigned_to foreign.userid => self.assigned_to
component foreign.id => self.component_id
product foreign.id => self.product_id
qa_contact foreign.userid => self.qa_contact
reporter foreign.userid => self.reporter
might_have
Name Foreign Class Join Condition
duplicates_dupe foreign.dupe => self.bug_id
has_many
Name Foreign Class Join Condition
attachments foreign.bug_id => self.bug_id
bug_group_maps foreign.bug_id => self.bug_id
bug_tags foreign.bug_id => self.bug_id
bug_user_last_visits foreign.bug_id => self.bug_id
bugs_activities foreign.bug_id => self.bug_id
bugs_aliases foreign.bug_id => self.bug_id
bugs_see_also foreign.bug_id => self.bug_id
ccs foreign.bug_id => self.bug_id
dependencies_blocked foreign.blocked => self.bug_id
dependencies_dependsons foreign.dependson => self.bug_id
duplicates_dupes_of foreign.dupe_of => self.bug_id
email_bugs_ignore foreign.bug_id => self.bug_id
flags foreign.bug_id => self.bug_id
keywords foreign.bug_id => self.bug_id
longdescs foreign.bug_id => self.bug_id
longdescs_tags_activities foreign.bug_id => self.bug_id

The "bug_group_map" table

Name Type Info
bug_id mediumint NOT NULL, FOREIGN KEY
group_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
bug_group_map_bug_id_idx bug_id, group_id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
group foreign.id => self.group_id

The "bug_see_also" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
bug_id mediumint NOT NULL, FOREIGN KEY
value varchar (255) NOT NULL
class varchar (255) NOT NULL, DEFAULT VALUE ''''''''''

Unique Constraints

NameColumns
primary id
bug_see_also_bug_id_idx bug_id, value

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id

The "bug_severity" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
visibility_value_id smallint

Unique Constraints

NameColumns
bug_severity_value_idx value
primary id

The "bug_status" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
visibility_value_id smallint
is_open tinyint NOT NULL, DEFAULT VALUE '''''1'''''

Unique Constraints

NameColumns
primary id
bug_status_value_idx value

Relationships

has_many
Name Foreign Class Join Condition
status_workflow_new_statuses foreign.new_status => self.id
status_workflow_old_statuses foreign.old_status => self.id

The "bug_tag" table

Name Type Info
bug_id mediumint NOT NULL, FOREIGN KEY
tag_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
bug_tag_bug_id_idx bug_id, tag_id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
tag foreign.id => self.tag_id

The "bug_user_last_visit" table

Name Type Info
id integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
user_id mediumint NOT NULL, FOREIGN KEY
bug_id mediumint NOT NULL, FOREIGN KEY
last_visit_ts datetime NOT NULL

Unique Constraints

NameColumns
bug_user_last_visit_idx user_id, bug_id
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
user foreign.userid => self.user_id

The "bugs_activity" table

Name Type Info
id integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
bug_id mediumint NOT NULL, FOREIGN KEY
attach_id mediumint FOREIGN KEY
who mediumint NOT NULL, FOREIGN KEY
bug_when datetime NOT NULL
fieldid mediumint NOT NULL, FOREIGN KEY
added varchar (255)
removed varchar (255)
comment_id integer FOREIGN KEY

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
attach foreign.attach_id => self.attach_id
bug foreign.bug_id => self.bug_id
comment foreign.comment_id => self.comment_id
fieldid foreign.id => self.fieldid
who foreign.userid => self.who

The "bugs_aliases" table

Name Type Info
alias varchar (40) NOT NULL
bug_id mediumint FOREIGN KEY

Unique Constraints

NameColumns
bugs_aliases_alias_idx alias

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id

The "bugs_fulltext" table

Name Type Info
bug_id mediumint NOT NULL, PRIMARY KEY
short_desc varchar (255) NOT NULL
comments mediumtext
comments_noprivate mediumtext

Unique Constraints

NameColumns
primary bug_id

The "bz_schema" table

Name Type Info
schema_data longblob NOT NULL
version decimal (3.2) NOT NULL

The "category_group_map" table

Name Type Info
category_id smallint NOT NULL, FOREIGN KEY
group_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
category_group_map_category_id_idx category_id, group_id

Relationships

belongs_to
Name Foreign Class Join Condition
category foreign.id => self.category_id
group foreign.id => self.group_id

The "cc" table

Name Type Info
bug_id mediumint NOT NULL, FOREIGN KEY
who mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
cc_bug_id_idx bug_id, who

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
who foreign.userid => self.who

The "classifications" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL
description mediumtext
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
classifications_name_idx name
primary id

Relationships

has_many
Name Foreign Class Join Condition
products foreign.classification_id => self.id

The "components" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL
product_id smallint NOT NULL, FOREIGN KEY
initialowner mediumint NOT NULL, FOREIGN KEY
initialqacontact mediumint FOREIGN KEY
description mediumtext NOT NULL
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''

Unique Constraints

NameColumns
primary id
components_product_id_idx product_id, name

Relationships

belongs_to
Name Foreign Class Join Condition
initialowner foreign.userid => self.initialowner
initialqacontact foreign.userid => self.initialqacontact
product foreign.id => self.product_id
has_many
Name Foreign Class Join Condition
bugs foreign.component_id => self.id
component_ccs foreign.component_id => self.id
flagexclusions foreign.component_id => self.id
flaginclusions foreign.component_id => self.id

The "component_cc" table

Name Type Info
user_id mediumint NOT NULL, FOREIGN KEY
component_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
component_cc_user_id_idx component_id, user_id

Relationships

belongs_to
Name Foreign Class Join Condition
component foreign.id => self.component_id
user foreign.userid => self.user_id

The "dependencies" table

Name Type Info
blocked mediumint NOT NULL, FOREIGN KEY
dependson mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
dependencies_blocked_idx blocked, dependson

Relationships

belongs_to
Name Foreign Class Join Condition
blocked foreign.bug_id => self.blocked
dependson foreign.bug_id => self.dependson

The "duplicates" table

Name Type Info
dupe_of mediumint NOT NULL, FOREIGN KEY
dupe mediumint NOT NULL, PRIMARY KEY, FOREIGN KEY

Unique Constraints

NameColumns
primary dupe

Relationships

belongs_to
Name Foreign Class Join Condition
dupe foreign.bug_id => self.dupe
dupe_of foreign.bug_id => self.dupe_of

The "email_bug_ignore" table

Name Type Info
user_id mediumint NOT NULL, FOREIGN KEY
bug_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
email_bug_ignore_user_id_idx user_id, bug_id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
user foreign.userid => self.user_id

The "email_setting" table

Name Type Info
user_id mediumint NOT NULL, FOREIGN KEY
relationship tinyint NOT NULL
event tinyint NOT NULL

Unique Constraints

NameColumns
email_setting_user_id_idx user_id, relationship, event

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id

The "field_visibility" table

Name Type Info
field_id mediumint FOREIGN KEY
value_id smallint NOT NULL

Unique Constraints

NameColumns
field_visibility_field_id_idx field_id, value_id

Relationships

belongs_to
Name Foreign Class Join Condition
field foreign.id => self.field_id

The "fielddefs" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL
type smallint NOT NULL, DEFAULT VALUE '''''0'''''
custom tinyint NOT NULL, DEFAULT VALUE '''''0'''''
description tinytext NOT NULL
long_desc varchar (255) NOT NULL, DEFAULT VALUE ''''''''''
mailhead tinyint NOT NULL, DEFAULT VALUE '''''0'''''
sortkey smallint NOT NULL
obsolete tinyint NOT NULL, DEFAULT VALUE '''''0'''''
enter_bug tinyint NOT NULL, DEFAULT VALUE '''''0'''''
buglist tinyint NOT NULL, DEFAULT VALUE '''''0'''''
visibility_field_id mediumint FOREIGN KEY
value_field_id mediumint FOREIGN KEY
reverse_desc tinytext
is_mandatory tinyint NOT NULL, DEFAULT VALUE '''''0'''''
is_numeric tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
primary id
fielddefs_name_idx name

Relationships

belongs_to
Name Foreign Class Join Condition
value_field foreign.id => self.value_field_id
visibility_field foreign.id => self.visibility_field_id
has_many
Name Foreign Class Join Condition
bugs_activities foreign.fieldid => self.id
field_visibilities foreign.field_id => self.id
fielddefs_value_fields foreign.value_field_id => self.id
fielddefs_visibility_fields foreign.visibility_field_id => self.id
profiles_activities foreign.fieldid => self.id

The "flags" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
type_id smallint NOT NULL, FOREIGN KEY
status char (1) NOT NULL
bug_id mediumint NOT NULL, FOREIGN KEY
attach_id mediumint FOREIGN KEY
creation_date datetime NOT NULL
modification_date datetime
setter_id mediumint NOT NULL, FOREIGN KEY
requestee_id mediumint FOREIGN KEY

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
attach foreign.attach_id => self.attach_id
bug foreign.bug_id => self.bug_id
requestee foreign.userid => self.requestee_id
setter foreign.userid => self.setter_id
type foreign.id => self.type_id

The "flagexclusions" table

Name Type Info
type_id smallint NOT NULL, FOREIGN KEY
product_id smallint FOREIGN KEY
component_id mediumint FOREIGN KEY

Unique Constraints

NameColumns
flagexclusions_type_id_idx type_id, product_id, component_id

Relationships

belongs_to
Name Foreign Class Join Condition
component foreign.id => self.component_id
product foreign.id => self.product_id
type foreign.id => self.type_id

The "flaginclusions" table

Name Type Info
type_id smallint NOT NULL, FOREIGN KEY
product_id smallint FOREIGN KEY
component_id mediumint FOREIGN KEY

Unique Constraints

NameColumns
flaginclusions_type_id_idx type_id, product_id, component_id

Relationships

belongs_to
Name Foreign Class Join Condition
component foreign.id => self.component_id
product foreign.id => self.product_id
type foreign.id => self.type_id

The "flagtypes" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (50) NOT NULL
description mediumtext NOT NULL
cc_list varchar (200)
target_type char (1) NOT NULL, DEFAULT VALUE '''''b'''''
is_active tinyint NOT NULL, DEFAULT VALUE '''''1'''''
is_requestable tinyint NOT NULL, DEFAULT VALUE '''''0'''''
is_requesteeble tinyint NOT NULL, DEFAULT VALUE '''''0'''''
is_multiplicable tinyint NOT NULL, DEFAULT VALUE '''''0'''''
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
grant_group_id mediumint FOREIGN KEY
request_group_id mediumint FOREIGN KEY

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
grant_group foreign.id => self.grant_group_id
request_group foreign.id => self.request_group_id
has_many
Name Foreign Class Join Condition
flagexclusions foreign.type_id => self.id
flaginclusions foreign.type_id => self.id
flags foreign.type_id => self.id

The "groups" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (255) NOT NULL
description mediumtext NOT NULL
isbuggroup tinyint NOT NULL
userregexp tinytext NOT NULL, DEFAULT VALUE ''''''''''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
icon_url tinytext

Unique Constraints

NameColumns
groups_name_idx name
primary id

Relationships

has_many
Name Foreign Class Join Condition
bug_group_maps foreign.group_id => self.id
category_group_maps foreign.group_id => self.id
flagtypes_grant_groups foreign.grant_group_id => self.id
flagtypes_request_groups foreign.request_group_id => self.id
group_control_maps foreign.group_id => self.id
group_group_map_grantors foreign.grantor_id => self.id
group_group_map_members foreign.member_id => self.id
namedquery_group_maps foreign.group_id => self.id
user_group_maps foreign.group_id => self.id

The "group_control_map" table

Name Type Info
group_id mediumint NOT NULL, FOREIGN KEY
product_id smallint NOT NULL, FOREIGN KEY
entry tinyint NOT NULL, DEFAULT VALUE '''''0'''''
membercontrol tinyint NOT NULL, DEFAULT VALUE '''''0'''''
othercontrol tinyint NOT NULL, DEFAULT VALUE '''''0'''''
canedit tinyint NOT NULL, DEFAULT VALUE '''''0'''''
editcomponents tinyint NOT NULL, DEFAULT VALUE '''''0'''''
editbugs tinyint NOT NULL, DEFAULT VALUE '''''0'''''
canconfirm tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
group_control_map_product_id_idx product_id, group_id

Relationships

belongs_to
Name Foreign Class Join Condition
group foreign.id => self.group_id
product foreign.id => self.product_id

The "group_group_map" table

Name Type Info
member_id mediumint NOT NULL, FOREIGN KEY
grantor_id mediumint NOT NULL, FOREIGN KEY
grant_type tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
group_group_map_member_id_idx member_id, grantor_id, grant_type

Relationships

belongs_to
Name Foreign Class Join Condition
grantor foreign.id => self.grantor_id
member foreign.id => self.member_id

The "keywords" table

Name Type Info
bug_id mediumint NOT NULL, FOREIGN KEY
keywordid smallint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
keywords_bug_id_idx bug_id, keywordid

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
keywordid foreign.id => self.keywordid

The "keyworddefs" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL
description mediumtext NOT NULL

Unique Constraints

NameColumns
keyworddefs_name_idx name
primary id

Relationships

has_many
Name Foreign Class Join Condition
keywords foreign.keywordid => self.id

The "login_failure" table

Name Type Info
user_id mediumint NOT NULL, FOREIGN KEY
login_time datetime NOT NULL
ip_addr varchar (40) NOT NULL

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id

The "logincookies" table

Name Type Info
cookie varchar (16) NOT NULL, PRIMARY KEY
userid mediumint NOT NULL, FOREIGN KEY
ipaddr varchar (40)
lastused datetime NOT NULL

Unique Constraints

NameColumns
primary cookie

Relationships

belongs_to
Name Foreign Class Join Condition
userid foreign.userid => self.userid

The "longdescs" table

Name Type Info
comment_id integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
bug_id mediumint NOT NULL, FOREIGN KEY
who mediumint NOT NULL, FOREIGN KEY
bug_when datetime NOT NULL
work_time decimal (7.2) NOT NULL, DEFAULT VALUE '''''0.00'''''
thetext mediumtext NOT NULL
isprivate tinyint NOT NULL, DEFAULT VALUE '''''0'''''
already_wrapped tinyint NOT NULL, DEFAULT VALUE '''''0'''''
type smallint NOT NULL, DEFAULT VALUE '''''0'''''
extra_data varchar (255)

Unique Constraints

NameColumns
primary comment_id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
who foreign.userid => self.who
has_many
Name Foreign Class Join Condition
bugs_activities foreign.comment_id => self.comment_id
longdescs_tags foreign.comment_id => self.comment_id
longdescs_tags_activities foreign.comment_id => self.comment_id

The "longdescs_tags" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
comment_id integer FOREIGN KEY
tag varchar (24) NOT NULL

Unique Constraints

NameColumns
primary id
longdescs_tags_idx comment_id, tag

Relationships

belongs_to
Name Foreign Class Join Condition
comment foreign.comment_id => self.comment_id

The "longdescs_tags_activity" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
bug_id mediumint NOT NULL, FOREIGN KEY
comment_id integer FOREIGN KEY
who mediumint NOT NULL, FOREIGN KEY
bug_when datetime NOT NULL
added varchar (24)
removed varchar (24)

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
bug foreign.bug_id => self.bug_id
comment foreign.comment_id => self.comment_id
who foreign.userid => self.who

The "longdescs_tags_weights" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
tag varchar (24) NOT NULL
weight mediumint NOT NULL

Unique Constraints

NameColumns
longdescs_tags_weights_tag_idx tag
primary id

The "mail_staging" table

Name Type Info
id integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
message longblob NOT NULL

Unique Constraints

NameColumns
primary id

The "milestones" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
product_id smallint NOT NULL, FOREIGN KEY
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''

Unique Constraints

NameColumns
primary id
milestones_product_id_idx product_id, value

Relationships

belongs_to
Name Foreign Class Join Condition
product foreign.id => self.product_id

The "namedqueries_link_in_footer" table

Name Type Info
namedquery_id mediumint NOT NULL, FOREIGN KEY
user_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
namedqueries_link_in_footer_id_idx namedquery_id, user_id

Relationships

belongs_to
Name Foreign Class Join Condition
namedquery foreign.id => self.namedquery_id
user foreign.userid => self.user_id

The "namedqueries" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
userid mediumint NOT NULL, FOREIGN KEY
name varchar (64) NOT NULL
query mediumtext NOT NULL

Unique Constraints

NameColumns
namedqueries_userid_idx userid, name
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
userid foreign.userid => self.userid
might_have
Name Foreign Class Join Condition
namedquery_group_map foreign.namedquery_id => self.id
has_many
Name Foreign Class Join Condition
namedqueries_links_in_footer foreign.namedquery_id => self.id

The "namedquery_group_map" table

Name Type Info
namedquery_id mediumint NOT NULL, FOREIGN KEY
group_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
namedquery_group_map_namedquery_id_idx namedquery_id

Relationships

belongs_to
Name Foreign Class Join Condition
group foreign.id => self.group_id
namedquery foreign.id => self.namedquery_id

The "op_sys" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
visibility_value_id smallint

Unique Constraints

NameColumns
op_sys_value_idx value
primary id

The "priority" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
visibility_value_id smallint

Unique Constraints

NameColumns
priority_value_idx value
primary id

The "products" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL
classification_id smallint NOT NULL, DEFAULT VALUE '''''1''''', FOREIGN KEY
description mediumtext NOT NULL
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
defaultmilestone varchar (64) NOT NULL, DEFAULT VALUE '''''---'''''
allows_unconfirmed tinyint NOT NULL, DEFAULT VALUE '''''1'''''

Unique Constraints

NameColumns
primary id
products_name_idx name

Relationships

belongs_to
Name Foreign Class Join Condition
classification foreign.id => self.classification_id
has_many
Name Foreign Class Join Condition
bugs foreign.product_id => self.id
components foreign.product_id => self.id
flagexclusions foreign.product_id => self.id
flaginclusions foreign.product_id => self.id
group_control_maps foreign.product_id => self.id
milestones foreign.product_id => self.id
versions foreign.product_id => self.id

The "profiles" table

Name Type Info
userid mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
login_name varchar (255) NOT NULL
cryptpassword varchar (128)
realname varchar (255) NOT NULL, DEFAULT VALUE ''''''''''
disabledtext mediumtext NOT NULL, DEFAULT VALUE ''''''''''''
disable_mail tinyint NOT NULL, DEFAULT VALUE '''''0'''''
mybugslink tinyint NOT NULL, DEFAULT VALUE '''''1'''''
extern_id varchar (64)
is_enabled tinyint NOT NULL, DEFAULT VALUE '''''1'''''
last_seen_date datetime

Unique Constraints

NameColumns
profiles_extern_id_idx extern_id
profiles_login_name_idx login_name
primary userid

Relationships

has_many
Name Foreign Class Join Condition
attachments foreign.submitter_id => self.userid
audit_logs foreign.user_id => self.userid
bug_user_last_visits foreign.user_id => self.userid
bugs_activities foreign.who => self.userid
bugs_assigned_to foreign.assigned_to => self.userid
bugs_qa_contacts foreign.qa_contact => self.userid
bugs_reporters foreign.reporter => self.userid
ccs foreign.who => self.userid
component_ccs foreign.user_id => self.userid
components_initialowners foreign.initialowner => self.userid
components_initialqacontacts foreign.initialqacontact => self.userid
email_bugs_ignore foreign.user_id => self.userid
email_settings foreign.user_id => self.userid
flags_requestees foreign.requestee_id => self.userid
flags_setters foreign.setter_id => self.userid
login_failures foreign.user_id => self.userid
logincookies foreign.userid => self.userid
longdescs foreign.who => self.userid
longdescs_tags_activities foreign.who => self.userid
namedqueries foreign.userid => self.userid
namedqueries_links_in_footer foreign.user_id => self.userid
profile_searches foreign.user_id => self.userid
profile_settings foreign.user_id => self.userid
profiles_activities_who foreign.who => self.userid
profiles_activity_userids foreign.userid => self.userid
quips foreign.userid => self.userid
reports foreign.user_id => self.userid
series foreign.creator => self.userid
tags foreign.user_id => self.userid
tokens foreign.userid => self.userid
user_api_keys foreign.user_id => self.userid
user_group_maps foreign.user_id => self.userid
watch_watchers foreign.watcher => self.userid
watches_watched foreign.watched => self.userid
whine_events foreign.owner_userid => self.userid

The "profile_search" table

Name Type Info
id integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
user_id mediumint NOT NULL, FOREIGN KEY
bug_list mediumtext NOT NULL
list_order mediumtext

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id

The "profile_setting" table

Name Type Info
user_id mediumint NOT NULL, FOREIGN KEY
setting_name varchar (32) NOT NULL, FOREIGN KEY
setting_value varchar (32) NOT NULL

Unique Constraints

NameColumns
profile_setting_value_unique_idx user_id, setting_name

Relationships

belongs_to
Name Foreign Class Join Condition
setting_name foreign.name => self.setting_name
user foreign.userid => self.user_id

The "profiles_activity" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
userid mediumint NOT NULL, FOREIGN KEY
who mediumint NOT NULL, FOREIGN KEY
profiles_when datetime NOT NULL
fieldid mediumint NOT NULL, FOREIGN KEY
oldvalue tinytext
newvalue tinytext

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
fieldid foreign.id => self.fieldid
userid foreign.userid => self.userid
who foreign.userid => self.who

The "quips" table

Name Type Info
quipid mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
userid mediumint FOREIGN KEY
quip varchar (512) NOT NULL
approved tinyint NOT NULL, DEFAULT VALUE '''''1'''''

Unique Constraints

NameColumns
primary quipid

Relationships

belongs_to
Name Foreign Class Join Condition
userid foreign.userid => self.userid

The "rep_platform" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
visibility_value_id smallint

Unique Constraints

NameColumns
rep_platform_value_idx value
primary id

The "reports" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
user_id mediumint NOT NULL, FOREIGN KEY
name varchar (64) NOT NULL
query mediumtext NOT NULL

Unique Constraints

NameColumns
primary id
reports_user_id_idx user_id, name

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id

The "resolution" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''
visibility_value_id smallint

Unique Constraints

NameColumns
primary id
resolution_value_idx value

The "series" table

Name Type Info
series_id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
creator mediumint FOREIGN KEY
category smallint NOT NULL, FOREIGN KEY
subcategory smallint NOT NULL, FOREIGN KEY
name varchar (64) NOT NULL
frequency smallint NOT NULL
query mediumtext NOT NULL
is_public tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
primary series_id
series_category_idx category, subcategory, name

Relationships

belongs_to
Name Foreign Class Join Condition
category foreign.id => self.category
creator foreign.userid => self.creator
subcategory foreign.id => self.subcategory
has_many
Name Foreign Class Join Condition
series_datas foreign.series_id => self.series_id

The "series_categories" table

Name Type Info
id smallint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL

Unique Constraints

NameColumns
series_categories_name_idx name
primary id

Relationships

has_many
Name Foreign Class Join Condition
category_group_maps foreign.category_id => self.id
series_categories foreign.category => self.id
series_subcategories foreign.subcategory => self.id

The "series_data" table

Name Type Info
series_id mediumint NOT NULL, FOREIGN KEY
series_date datetime NOT NULL
series_value mediumint NOT NULL

Unique Constraints

NameColumns
series_data_series_id_idx series_id, series_date

Relationships

belongs_to
Name Foreign Class Join Condition
series foreign.series_id => self.series_id

The "setting" table

Name Type Info
name varchar (32) NOT NULL, PRIMARY KEY
default_value varchar (32) NOT NULL
is_enabled tinyint NOT NULL, DEFAULT VALUE '''''1'''''
subclass varchar (32)

Unique Constraints

NameColumns
primary name

Relationships

has_many
Name Foreign Class Join Condition
profile_settings foreign.setting_name => self.name
setting_values foreign.name => self.name

The "setting_value" table

Name Type Info
name varchar (32) NOT NULL, FOREIGN KEY
value varchar (32) NOT NULL
sortindex smallint NOT NULL

Unique Constraints

NameColumns
setting_value_ns_unique_idx name, sortindex
setting_value_nv_unique_idx name, value

Relationships

belongs_to
Name Foreign Class Join Condition
name foreign.name => self.name

The "status_workflow" table

Name Type Info
old_status smallint FOREIGN KEY
new_status smallint NOT NULL, FOREIGN KEY
require_comment tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
status_workflow_idx old_status, new_status

Relationships

belongs_to
Name Foreign Class Join Condition
new_status foreign.id => self.new_status
old_status foreign.id => self.old_status

The "tag" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
name varchar (64) NOT NULL
user_id mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
primary id
tag_user_id_idx user_id, name

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id
has_many
Name Foreign Class Join Condition
bug_tags foreign.tag_id => self.id

The "tokens" table

Name Type Info
userid mediumint FOREIGN KEY
issuedate datetime NOT NULL
token varchar (16) NOT NULL, PRIMARY KEY
tokentype varchar (16) NOT NULL
eventdata tinytext

Unique Constraints

NameColumns
primary token

Relationships

belongs_to
Name Foreign Class Join Condition
userid foreign.userid => self.userid

The "ts_error" table

Name Type Info
error_time integer NOT NULL
jobid integer NOT NULL
message varchar (255) NOT NULL
funcid integer NOT NULL, DEFAULT VALUE '''''0'''''

The "ts_exitstatus" table

Name Type Info
jobid integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
funcid integer NOT NULL, DEFAULT VALUE '''''0'''''
status smallint
completion_time integer
delete_after integer

Unique Constraints

NameColumns
primary jobid

The "ts_funcmap" table

Name Type Info
funcid integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
funcname varchar (255) NOT NULL

Unique Constraints

NameColumns
primary funcid
ts_funcmap_funcname_idx funcname

The "ts_job" table

Name Type Info
jobid integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
funcid integer NOT NULL
arg longblob
uniqkey varchar (255)
insert_time integer
run_after integer NOT NULL
grabbed_until integer NOT NULL
priority smallint
coalesce varchar (255)

Unique Constraints

NameColumns
primary jobid
ts_job_funcid_idx funcid, uniqkey

The "ts_note" table

Name Type Info
jobid integer NOT NULL
notekey varchar (255)
value longblob

Unique Constraints

NameColumns
ts_note_jobid_idx jobid, notekey

The "user_api_keys" table

Name Type Info
id integer NOT NULL, PRIMARY KEY, AUTO_INCREMENT
user_id mediumint NOT NULL, FOREIGN KEY
api_key varchar (40) NOT NULL
description varchar (255)
revoked tinyint NOT NULL, DEFAULT VALUE '''''0'''''
last_used datetime

Unique Constraints

NameColumns
user_api_keys_api_key_idx api_key
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
user foreign.userid => self.user_id

The "user_group_map" table

Name Type Info
user_id mediumint NOT NULL, FOREIGN KEY
group_id mediumint NOT NULL, FOREIGN KEY
isbless tinyint NOT NULL, DEFAULT VALUE '''''0'''''
grant_type tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
user_group_map_user_id_idx user_id, group_id, grant_type, isbless

Relationships

belongs_to
Name Foreign Class Join Condition
group foreign.id => self.group_id
user foreign.userid => self.user_id

The "versions" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
value varchar (64) NOT NULL
product_id smallint NOT NULL, FOREIGN KEY
isactive tinyint NOT NULL, DEFAULT VALUE '''''1'''''

Unique Constraints

NameColumns
primary id
versions_product_id_idx product_id, value

Relationships

belongs_to
Name Foreign Class Join Condition
product foreign.id => self.product_id

The "watch" table

Name Type Info
watcher mediumint NOT NULL, FOREIGN KEY
watched mediumint NOT NULL, FOREIGN KEY

Unique Constraints

NameColumns
watch_watcher_idx watcher, watched

Relationships

belongs_to
Name Foreign Class Join Condition
watched foreign.userid => self.watched
watcher foreign.userid => self.watcher

The "whine_events" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
owner_userid mediumint NOT NULL, FOREIGN KEY
subject varchar (128)
body mediumtext
mailifnobugs tinyint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
owner_userid foreign.userid => self.owner_userid
has_many
Name Foreign Class Join Condition
whine_queries foreign.eventid => self.id
whine_schedules foreign.eventid => self.id

The "whine_queries" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
eventid mediumint NOT NULL, FOREIGN KEY
query_name varchar (64) NOT NULL, DEFAULT VALUE ''''''''''
sortkey smallint NOT NULL, DEFAULT VALUE '''''0'''''
onemailperbug tinyint NOT NULL, DEFAULT VALUE '''''0'''''
title varchar (128) NOT NULL, DEFAULT VALUE ''''''''''

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
eventid foreign.id => self.eventid

The "whine_schedules" table

Name Type Info
id mediumint NOT NULL, PRIMARY KEY, AUTO_INCREMENT
eventid mediumint NOT NULL, FOREIGN KEY
run_day varchar (32)
run_time varchar (32)
run_next datetime
mailto mediumint NOT NULL
mailto_type smallint NOT NULL, DEFAULT VALUE '''''0'''''

Unique Constraints

NameColumns
primary id

Relationships

belongs_to
Name Foreign Class Join Condition
eventid foreign.id => self.eventid

4. Bugzilla History

Bugzilla releases

A table giving the dates of all the Bugzilla releases will soon be generated.

5. Example queries

To select bug number n:

select * from bugs where bug_id = n

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

select userid, login_name from profiles

To get the email address of user n:

select login_name from profiles where userid = n

To get the set of cc addresses of bug n:

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

To select the long descriptions of bug n, together with the name and email address of the commenters:

select profiles.login_name, profiles.realname, longdescs.bug_when, longdescs.thetext from longdescs, profiles where profiles.userid = longdescs.who and longdescs.bug_id = n order by longdescs.bug_when

To find out the groups of user n:

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