The FogBugz 5.0 Database Format (Schema)

FogBugz uses an open and unlocked database to store its information. If you're familiar with SQL queries, you can use a product such as Microsoft Access to create your own custom reports based on the information stored in your FogBugz database. To help you in understanding the contents of the database, we've prepared this guide to the FogBugz 5.0 schema. Using this guide, you should be able to navigate your database to find the information that you need.

Modifying your FogBugz database through any means other than the use of the FogBugz user interface or the BugzScout interface is not supported. You should not alter the contents of the tables in any way. Use this guide as an aid to writing queries to read the data only.

Contents

  1. The FogBugz Naming Conventions
  2. Locating Information on Cases
  3. The FogBugz Data Dictionary
  4. Relationship Diagrams

The FogBugz Naming Conventions

FogBugz uses a modified Hungarian naming convention for columns within tables. Almost every column in the FogBugz database has a prefix that tells you about that column. You'll find these prefixes in use:

Locating Information on Cases

The core of the FogBugz database is the Bug and BugEvent tables. If you understand the contents of those two tables, you should be able to find most of what you need in the database. Even though they have "Bug" in their names, these tables contain information on cases of all types (there aren't separate tables for features and inquiries). The Bug table contains information that describes the current state of each case in the system, past and present (and links to lookup tables that help you interpret this information). The BugEvent table contains the history of each case - the history that appears at the bottom of the case when you view its details in FogBugz.

Cases are uniquely identified by the value in the ixBug column. This is a numeric key which also serves as the FogBugz case number. To find the history for a particular case, you need to locate all of the rows in the BugEvent table which have that case's ixBug value in the ixBug column, which serves as a foreign key in that column. The BugEvent table has its own automatically-generated primary key, ixBugEvent, which increases as events are added to the table. Any given case will likely have gaps in its sequence of ixBugEvent values, but if you order all the rows in the BugEvent table that pertain to a single case from lowest to highest ixBugEvent value, they will form the history of that case.

The Bug table contains columns that directly record some of the essential information about every case (refer to the data dictionary section of this guide for details). In addition to the case number, these include:

For cases that were submitted by e-mail, the Bug table contains details of the source and the actions taken by the Bayesian e-mail sorter. For cases that came in via BugzScout, the Bug table tracks the number of times that the case was submitted. For cases ported from discussion lists, there's a link back to the discussion topic.

The Bug table also contains foreign keys to a number of lookup tables with additional details on the case. These other tables include:

While the Bug table provides a static picture of the current state of a case, the BugEvent table records the history of the case. If you collect all of the rows in the BugEvent table with a single ixBug value, you'll have the history of that case. To tell what happened, first sort the records by the dt column, which holds the date and time that each event was recorded. You can check the sVerb column to see what each event represents ("Opened," "Edited," "Resolved," and so on). There's also a foreign key to the Person table to tell you who was responsible for each event. Other information in the BugEvent table includes the notes that were typed in for the event, the name of any attached file, and automatically-generated system messages when things like estimates or categories are changed by the user.

The FogBugz Data Dictionary

This section of the schema guide describes the detailed schema of a FogBugz 5.0 database. Every table, and every column within each table, is enumerated and briefly described here. You'll need to be familiar with the operation of FogBugz to see how all of the pieces fit together, but if you know how the application works, this data dictionary should aid you in finding the data that you're looking for.

Note: The table descriptions that follow show the column name and SQL Server data type for every column in every table in a standard FogBugz 5.0 database. If you're using a different database server (such as Microsoft Access) the column names will be the same, but the data types will differ.

Database Version: 502

Table: ACL

The ACL table maps users to groups and indicates their permissions.

Columns

Table: Area

The area table holds the list of areas to which bugs can be assigned in each project.

Columns

Table: Attachment

Files attached to cases.

Columns

Table: AutoResponseRecord

The AutoResponseRecord table holds recently-sent (within the last hour) e-mail autoresponses. FogBugz automatically flushes older records out of this table.

Columns

Table: Bug

The Bug table holds one record for every case ever opened in your FogBugz installation.

Columns

Table: BugEvent

The BugEvent table holds one record for every event that happens to a case.

Columns

Table: BugRelation

The BugRelation table tracks related cases.

Columns

Table: CVS

The CVS table tracks integration between FogBugz cases and changes in your source-code control system (if you have set up source-code control integration with FogBugz).

Columns

Table: Category

The Category table lists the possible categories of cases. It ships with rows for the standard categories (bug, feature, and inquiry).

Columns

Table: DiscussEmail

The DiscussEmail table logs all e-mails sent by FogBugz on behalf of discussion group participants who are not logged in.

Columns

Table: DiscussGroup

The DiscussGroup table contains one row for each discussion group set up on your FogBugz server.

Columns

Table: DiscussLayout

The DiscussLayout table contains a single row that records the settings made by the FogBugz administrator on the "Discussion Group Appearance" customization page.

Columns

Table: DiscussTopic

The DiscussTopic table contains one row for each post within a discussion group.

Columns

Table: Duplicates

The Duplicates table holds one row for each case that is closed as being a duplicate of another case.

Columns

Table: Filter

The Filter table contains one row for each saved filter. Note that the user's current filter is stored in the Person table.

Columns

Table: FixFor

The FixFor table holds one row for each release of a project in the database.

Columns

Table: GridColumn

The GridColumn table contains one row for each grid view column associated with either a Person or a Filter.

Columns

Table: Groups

The Groups table holds one row for each client or department that you define for your security structure.

Columns

Table: Holiday

The Holidays table contains one row for each holiday you have created when customizing your working schedule.

Columns

Table: ID

The ID table contains a single row with a unique identifier for this FogBugz database.

Columns

Table: MailQueue

The MailQueue contains messages being sent by FogBugz itself.

Columns

Table: Licenses

The Licenses table contains the licenses for your copy of FogBugz. Its structure is undocumented.

Columns

Table: Mailbox

The Mailbox table contains one row for each POP3 mailbox defined within FogBogz.

Columns

Table: Notification

The Notification table contains error notifications from FogBugz to the FogBugz administrator.

Columns

Table: Person

The Person table holds one row for each licensed user of FogBugz.

Columns

Table: Prediction

The Prediction table contains one row for each prediction made by the Bayesian classifier.

Columns

Table: Priority

The Priority table holds one row for each priority that can be assigned to a case. It will always hold exactly seven rows.

Columns

Table: Project

The Project table holds one row for each project in the FogBugz database.

Columns

Table: Scout

The rows in this table determine how to treat incoming crash reports submitted via BugScout

Columns

Table: Setting

The Setting table contains key-value pairs for FogBugz site settings.

Columns

Table: Snippet

The Snippet table contains one row for each snippet defined in the database.

Columns

Table: SorterSetting

The SorterSetting table contains various settings used by the Bayesian e-mail classifier. It is organized as a series of key-value pairs, so that each row of the table actually contains a different piece of information. The actual settings here are undocumented.

Columns

Table: SorterToken

The SorterToken table contains one row for each token used by the Bayesian e-mail classifier in sorting messages.

Columns

Table: Status

The Status table holds one row for each status that can be assigned to a case. It is populated by the system and its contents never change.

Columns

Table: Subscriptions

The Subscriptions table holds one row for each case that a person has subscribed to for e-mail notifications.

Columns

Table: TokenAssociation

The TokenAssociation table contains one row for each association between a SorterToken and an Area. These associations are used by the Bayesian classifier to automatically sort e-mail messages into areas.

Columns

Table: Tokens

The Tokens table records persistent user login information.

Columns

Table: TrainingRequest

The TrainingRequest table contains information used to train the Bayesian e-mail sorter.

Columns

Table: Version

The Version table contains a single row that indicates the version of your FogBugz database.

Columns

Table: WorkingSchedule

The WorkingSchedule table contains various settings that define the FogBugz work week. It is organized as a series of key-value pairs, so that each row of the table actually contains a different piece of information.

Columns

Relationship Diagrams

The following relationship diagrams will help you find the data that you need for the most common reporting scenarios.

NOTE: The join lines between tables are shown to help you visualize the one- and many-side of the relationships. FogBugz does not actually create joins in its database; instead, it enforces relationships in code. In many cases, the relationship between tables is optional rather than mandatory.

Figure 1 shows the core case-tracking tables: Bug, BugEvent, and the various lookup tables that they use.

Figure 2 shows the tables involved in setting up security groups (clients and departments).

Figure 3 shows the tables used by FogBugz to manage discussion groups.