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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 | Description
--- | --- | --- | ---
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 |