Database Schema

phpBugTracker
Prev	Chapter 3. Developer's Guide

3.3. Database Schema

Table 3-1. Schema for table attachment

Field	Type	Key	Description
attachment_id	int(10) unsigned	Primary
bug_id	int(10) unsigned	Foreign
file_name	char(255)		The name of the file supplied by the user and the name as it is stored on disk.
description	char(255)		The description of the file as entered by the user.
file_size	bigint(20) unsigned
mime_type	char(30)		The MIME type as obtained by PHP when the file was uploaded
created_by	int(10) unsigned
created_date	bigint(20) unsigned

Table 3-2. Schema for table auth_group

Field	Type	Key	Description
group_id	int(10) unsigned	Primary
group_name	varchar(80)
locked	tinyint(1)		Accepts values 1 or 0 for whether or not a group can be edited from the admin tools.
created_by	int(10) unsigned
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned

This table defines the various groups to which a user may belong. The initial groups are Admin, User, and Developer.

Table 3-3. Schema for table auth_perm

Field	Type	Key
perm_id	int(10) unsigned	Primary
perm_name	varchar(80)
created_by	int(10) unsigned
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned

auth_perm stores the various permissions that can be assigned to users.

Table 3-4. Schema for table auth_user

Field	Type	Key	Description
user_id	int(10) unsigned	Primary
login	char(40)	Unique
first_name	char(40)
last_name	char(40)
email	char(60)
password	char(40)
active	tinyint(3) unsigned
bug_list_fields	char(255)		This contains a serialized array of the fields the user has selected to see in the bug list. If this field is empty the default fields will be shown.
created_by	int(10) unsigned
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned

Table 3-5. Schema for table bug

Field	Type	Key	Description
bug_id	int(10) unsigned	Primary
title	varchar(100)		The short summary.
description	text		The longer, more detailed description of a bug.
url	varchar(255)
severity_id	tinyint(3) unsigned	Foreign
priority	tinyint(3) unsigned
status_id	tinyint(3) unsigned	Foreign
resolution_id	tinyint(3) unsigned	Foreign
assigned_to	int(10) unsigned		The user_id of the user to whom the bug is assigned.
created_by	int(10) unsigned		The user_id of the user who reported the bug.
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned
project_id	int(10) unsigned	Foreign
version_id	int(10) unsigned	Foreign
component_id	int(10) unsigned	Foreign
os_id	tinyint(3) unsigned	Foreign
browser_string	varchar(255)		The value obtained from `$HTTP_USER_AGENT`.
close_date	bigint(20) unsigned

Table 3-6. Schema for table bug_cc

Field	Type	Key	Description
bug_id	int(10) unsigned	Foreign
user_id	int(10) unsigned	Foreign	The user to whom to send carbon copies of the emails sent due to changes in the bug status.
created_by	int(10) unsigned
created_date	bigint(20) unsigned

Table 3-7. Schema for table bug_dependency

Field	Type	Key	Description
bug_id	int(10) unsigned	Primary
depends_on	int(10) unsigned	Primary

Table 3-8. Schema for table bug_group

Field	Type	Key	Description
bug_id	int(10) unsigned	Primary
group_id	int(10) unsigned	Primary

This table isn't currently used.

Table 3-9. Schema for table bug_vote

Field	Type	Key
user_id	int(10) unsigned	Primary
bug_id	int(10) unsigned	Primary
created_date	bigint(20) unsigned

Table 3-10. Schema for table bug_history

Field	Type	Key	Description
bug_id	int(10) unsigned	Foreign
changed_field	char(20)		The field, such as version, component, etc., that changed.
old_value	char(255)
new_value	char(255)
created_by	int(10) unsigned
created_date	bigint(20) unsigned

Table 3-11. Schema for table comment

Field	Type	Key
comment_id	int(10) unsigned	Primary
bug_id	int(10) unsigned	Foreign
comment_text	text
created_by	int(10) unsigned
created_date	bigint(20) unsigned

Table 3-12. Schema for table component

Field	Type	Key	Description
component_id	int(10) unsigned	Primary
project_id	int(10) unsigned	Foreign
component_name	varchar(30)
component_desc	text
owner	int(10) unsigned		This is the user_id of the developer who should receive initial email notifications of bugs created for this component.
active	tinyint(1)
created_by	int(10) unsigned
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned

Table 3-13. Schema for table configuration

Field	Type	Key
varname	char(40)	Primary
varvalue	char(255)
description	char(255)
vartype	char(20)

This table stores the configuration for phpBugTracker

Table 3-14. Schema for table group_perm

Field	Type	Key	Description
group_id	int(10) unsigned	Primary
perm_id	int(10) unsigned	Primary

Table 3-15. Schema for table os

Field	Type	Key
os_id	int(10) unsigned	Primary
os_name	char(30)
sort_order	tinyint(3) unsigned
regex	char(40)

Table 3-16. Schema for table project

Field	Type	Key
project_id	int(10) unsigned	Primary
project_name	varchar(30)
project_desc	text
active	tinyint(1)
created_by	int(10) unsigned
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned

Table 3-17. Schema for table project_group

Field	Type	Key
project_id	int(10) unsigned	Primary
group_id	int(10) unsigned	Primary
created_by	int(10) unsigned
created_date	bigint(20) unsigned

Table 3-18. Schema for table resolution

Field	Type	Key
resolution_id	int(10) unsigned	Primary
resolution_name	varchar(30)
resolution_desc	text
sort_order	tinyint(3) unsigned

Table 3-19. Schema for table saved_query

Field	Type	Key
saved_query_id	int(10) unsigned	Primary
user_id	int(10) unsigned	Primary
saved_query_name	varchar(40)
saved_query_string	text

The saved_query table stores the queries saved by the users from the query page.

Table 3-20. Schema for table severity

Field	Type	Key	Description
severity_id	int(10) unsigned	Primary
severity_name	varchar(30)
severity_desc	text
sort_order	tinyint(3) unsigned
severity_color	varchar(10)		The background color displayed for a row in the bug list if the `USE_SEVERITY_COLOR` configuration option is enabled.

Table 3-21. Schema for table status

Field	Type	Key
status_id	int(10) unsigned	Primary
status_name	varchar(30)
status_desc	text
sort_order	tinyint(3) unsigned

Table 3-22. Schema for table user_group

Field	Type	Key
user_id	int(10) unsigned	Primary
group_id	int(10) unsigned	Primary
created_by	int(10) unsigned
created_date	bigint(20) unsigned

Table 3-23. Schema for table user_perm

Field	Type	Key	Description
user_id	int(10) unsigned	Primary
perm_id	int(10) unsigned	Primary

Table 3-24. Schema for table user_pref

Field	Type	Key	Description
user_id	int(10) unsigned	Primary
email_notices	tinyint(1)		Whether the user wants to receive email notices from phpBugTracker. Values are 1 for yes (default) or 0 for no.

Table 3-25. Schema for table version

Field	Type	Key
version_id	int(10) unsigned	Primary
project_id	int(10) unsigned
version_name	char(10)
active	tinyint(1)
created_by	int(10) unsigned
created_date	bigint(20) unsigned
last_modified_by	int(10) unsigned
last_modified_date	bigint(20) unsigned

Prev	Home
Development Standards	Up