11.9. 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 groups.

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 profiles.

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.
email 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.