Database Schema¶
The following tables are now obsolete:
test_environment_*
profiles (replaced by auth_user), profiles_activity
groups (replaced by auth_groups).
Table A.1. TCMS
DATABASE |
TCMS |
DRIVER |
mysql |
DESCRIPTION |
TCMS database schema |
Table A.2. attach_data
NAME |
attach_data |
TYPE |
TABLE |
DESCRIPTION |
The content of attachments. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
MEDIUMINT |
NO |
PRI |
The attachment id (foreign key attachments.attach_id). |
|
thedata |
LONGBLOB |
NO |
The content of the attachment. |
Table A.3. attachments
NAME |
attachments |
TYPE |
TABLE |
DESCRIPTION |
Users can upload attachments to bugs. An attachment can be marked as a patch. Attachments are stored in the attachments table. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
attach_id |
MEDIUMINT |
NO |
PRI |
Unique ID. |
|
bug_id |
MEDIUMINT |
NO |
MUL |
The bug to which this is attached (foreign key bugs.bug_id). |
|
creation_ts |
DATETIME |
NO |
MUL |
The creation time. |
|
description |
MEDIUMTEXT |
NO |
Description of the attachment. |
||
mimetype |
MEDIUMTEXT |
NO |
MIME type of the attachment. |
||
ispatch |
TINYINT |
YES |
Non-zero if this attachment is a patch file. |
||
filename |
VARCHAR |
NO |
Filename of the attachment. |
||
submitter_id |
MEDIUMINT |
NO |
MUL |
Userid of the attachment (foreign key auth_user.id) |
|
isobsolete |
TINYINT |
NO |
Non-zero if attachment is obsolete. |
||
isprivate |
TINYINT |
NO |
Non-zero if attachment is private. |
||
isurl |
TINYINT |
NO |
Non-zero if this attachment is actually a URL. |
Table A.4. auth_group
NAME |
auth_group |
TYPE |
TABLE |
DESCRIPTION |
Django Groups table. Replaces the Testopia table |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Group ID. Auto increment. |
name |
VARCHAR(80) |
NULL |
NO |
UNI |
Group name. |
Table A.5. auth_group_permissions
NAME |
auth_group_permissions |
TYPE |
TABLE |
DESCRIPTION |
Permissions for the group. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Unique ID. Auto increment. |
group_id |
INT |
NULL |
NO |
MUL |
Foreign key auth_group.id |
permission_id |
INT |
NULL |
NO |
MUL |
Foreign key auth_permission.id |
Table A.6. auth_message
NAME |
auth_message |
TYPE |
TABLE |
DESCRIPTION |
Django message table. Used to exchange messages between users. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Unique ID. Auto increment. |
user_id |
INT(11) |
NULL |
NO |
Foreign key auth_user.id |
|
message |
LONGTEXT |
NULL |
NO |
Authorization message. |
Table A.7. auth_permission
NAME |
auth_permission |
TYPE |
TABLE |
DESCRIPTION |
Django permissions. The permissions are based on the app and models.There are three permissions for each model: add, change, and delete. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Unique ID. Auto increment. |
name |
VARCHAR(50) |
NULL |
NO |
Permission name. |
|
content_type_id |
INT(11) |
NULL |
NO |
MUL |
Foreign key django_content_type.id |
codename |
VARCHAR(100) |
NO |
MUL |
Table A.8. auth_user
NAME |
auth_user |
TYPE |
TABLE |
DESCRIPTION |
Django User/Group/ACL table. Replaces the Testopia table |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Unique ID. Auto increment. |
username |
VARCHAR(30) |
NULL |
NO |
UNI |
Unique username. Generally Kerberos uname. |
first_name |
VARCHAR(30) |
NULL |
NO |
User’s first name. |
|
last_name |
VARCHAR(30) |
NULL |
NO |
User’s last name. |
|
VARCHAR(75) |
NULL |
NO |
User’s email. |
||
password |
VARCHAR(128) |
NULL |
NO |
User’s password hash. |
|
is_staff |
TINYINT(1) |
NULL |
NO |
1 is staff, 0 otherwise. |
|
is_active |
TINYINT(1) |
NULL |
NO |
1 is active user, 0 otherwise. |
|
is_superuser |
TINYINT(1) |
NULL |
NO |
1 is super user, 0 otherwise. |
|
last_login |
DATETIME |
NULL |
NO |
Date of last login. |
|
date_joined |
DATETIME |
NULL |
NO |
Date user account added to system. |
Table A.9. auth_user_groups
NAME |
auth_user_groups |
TYPE |
TABLE |
DESCRIPTION |
Mapping of user to groups. . |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Unique ID. Auto increment. |
user_id |
INT |
NULL |
NO |
MUL |
Foreign key auth_user.id |
group_id |
INT |
NULL |
NO |
MUL |
Foreign key auth_group.id |
Table A.10. auth_user_user_permissions
NAME |
auth_user_user_permissions |
TYPE |
TABLE |
DESCRIPTION |
Mapping of user to permissions. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
INT(11) |
NULL |
NO |
PRI |
Unique ID. Auto increment. |
user_id |
INT(11) |
NULL |
NO |
MUL |
Foreign key auth_user.id |
permission_id |
INT(11) |
NULL |
NO |
MUL |
Foreign key auth_permission.id |
Table A.11. bug_group_map
NAME |
bug_group_map |
TYPE |
TABLE |
DESCRIPTION |
Mapping of bugs to groups. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
bug_id |
MEDIUMINT |
NO |
PRI |
Bug ID (foreign key bugs.bug_id). |
|
group_id |
MEDIUMINT |
NO |
PRI |
The group ID (foreign key groups.id). |
Table A.12. bug_severity
NAME |
bug_severity |
TYPE |
TABLE |
DESCRIPTION |
The serverity values for bugs. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
SMALLINT |
NO |
PRI |
Unique ID. |
|
value |
VARCHAR |
NO |
UNI |
Severity value. |
|
sortkey |
SMALLINT |
NO |
MUL |
Determines the order in which values are shown. |
|
isactive |
TINYINT |
1 |
NO |
1 if available in the UI, 0 otherwise. |
Table A.13. bug_status
NAME |
bug_status |
TYPE |
TABLE |
DESCRIPTION |
The status values for bugs. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
id |
SMALLINT |
NO |
PRI |
Unique ID. |
|
value |
VARCHAR |
NO |
UNI |
Status value. |
|
sortkey |
SMALLINT |
NO |
MUL |
Determines the order in which values are shown. |
|
isactive |
TINYINT |
1 |
NO |
1 if available in the UI, 0 otherwise. |
Table A.14. bugs
NAME |
bugs |
TYPE |
TABLE |
DESCRIPTION |
The bug details. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
bug_id |
MEDIUMINT |
NO |
PRI |
Bug ID. |
|
assigned_to |
MEDIUMINT |
NO |
MUL |
Current owner of the bug (foreign key auth_user.id). |
|
bug_file_loc |
TEXT |
YES |
A URL which points to more information about the bug. |
||
bug_severity |
VARCHAR |
NO |
MUL |
Severity value of a bug (foreign key bug_severity.value). |
|
bug_status |
VARCHAR |
NO |
MUL |
Workflow status of the bug (foreign key bug_status.value). |
|
creation_ts |
DATETIME |
YES |
MUL |
Time of bug creation. |
|
delta_ts |
DATETIME |
NO |
MUL |
The timestamp of the last update. This includes updates to some related tables. |
|
short_desc |
VARCHAR |
NO |
Short description of the bug. |
||
op_sys |
VARCHAR |
NO |
MUL |
Operating system on which the bug was observed (foreign key op_sys.value). |
|
priority |
VARCHAR |
NO |
MUL |
The priority of the bug (foreign key priority.value). |
|
product_id |
SMALLINT |
NO |
MUL |
Product the bug relates to (foreign key products.id). |
|
rep_platform |
VARCHAR |
NO |
Platform on which the bug was reported (foreign key rep_platform.value). |
||
reporter |
MEDIUMINT |
NO |
MUL |
User who reported the bug (foreign key auth_user.id). |
|
version |
VARCHAR |
NO |
MUL |
Product version (foreign key versions.value). |
|
component_id |
SMALLINT |
NO |
MUL |
Product component (foreign key components.id). |
|
resolution |
VARCHAR |
NO |
MUL |
Bug resolution (foreign key resolution.value). |
|
target_milestone |
VARCHAR |
NO |
MUL |
Milestone by which this bug should be resolved (foreign key milestones.value). |
|
qa_contact |
MEDIUMINT |
NO |
MUL |
The QA contact (foreign key auth_user.id). |
|
status_whiteboard |
MEDIUMTEXT |
NO |
Small whiteboard field. |
||
votes |
MEDIUMINT |
NO |
MUL |
The number of votes. |
|
keywords |
MEDIUMTEXT |
NO |
A set of keywords. Note: duplicates information in the keywords table (foreign key keyworddefs.name). |
||
lastdiffed |
DATETIME |
YES |
The time at which bug change information was last emailed to the CC list. |
||
everconfirmed |
TINYINT |
NO |
1 if this bug has ever been confirmed. Used for validation of some sort. |
||
reporter_accessible |
TINYINT |
1 |
NO |
1 if the reporter can see this bug (even if in the wrong group), 0 otherwise. |
|
cclist_accessible |
TINYINT |
1 |
NO |
1 if people on the CC list can see this bug (even if in the wrong group), 0 otherwise. |
|
estimated_time |
DECIMAL |
0.00 |
NO |
The original estimate of the total effort required to fix this bug (in hours). |
|
remaining_time |
DECIMAL |
0.00 |
NO |
The current estimate of the remaining effort required to fix this bug (in hours). |
|
deadline |
DATETIME |
YES |
Used by hwcert to record the hwcert public cert date. |
||
alias |
VARCHAR |
YES |
UNI |
An alias for the bug which can be used instead of the bug number. |
Table A.15. bugs_activity
NAME |
bz_activity |
TYPE |
TABLE |
DESCRIPTION |
Records activity on the bug. |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
bug_id |
MEDIUMINT |
NO |
MUL |
Bug ID (foreign key bugs.bug_id). |
|
attach_id |
MEDIUMINT |
YES |
If the change was to an attachment (foreign key attachments.attach_id). |
||
who |
MEDIUMINT |
NO |
MUL |
User (foreign key auth_user.id). |
|
bug_when |
DATETIME |
NO |
MUL |
Date the change occurred. |
|
fieldid |
MEDIUMINT |
NO |
MUL |
Field ID of the change (foreign key fielddefs.id). |
|
added |
TINYTEXT |
YES |
The new value of this field. |
||
removed |
TINYTEXT |
YES |
The old value of this field. |
Table A.16. bz_schema
NAME |
bz_schema |
TYPE |
TABLE |
DESCRIPTION |
The Bugzilla database schema |
Table Info:
FIELD |
TYPE |
DEFAULT |
NUL |
KEY |
REMARK |
---|---|---|---|---|---|
schema_data |
LONGBLOB |
NO |
A serialized version of the abstract schema. Format is readable in Perl. |
||
version |
DECIMAL |
NO |
The version number of the abstract schema data structures. This is not the schema version, it does not change as tables, columns, and indexes are added and removed. |
Note
Is there any specific reason why a User Guide contains a database schema? Converting 200 tables into Confluence is very laborious and I’m not sure will be of benefit. Converting 15 took approximate 5 hours.