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.