The FogBugz Database Schema
If you have FogBugz for your server, than you may be aware that it uses an open and unlocked database to store its information. If you’re familiar with SQL queries, you can use a product such as Crystal Reports 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 schema. Using this guide, you should be able to navigate your database to find the information that you need.
NOTE: Modifying your FogBugz database directly is dangerous and 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.
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:
- c for numeric fields that record a count of something.
- d for floating-point numeric fields.
- f for flags. Note that you cannot assume that a flag has any particular data type.
- hrs for measurements in hours.
- dt for datetime columns.
- i for integers.
- ix for primary and foreign keys. Keys that can be joined will have the same name on both sides of the join.
- n for numbers, typically enumerations.
- s for character (string) data
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 below for details). In addition to the case number, these include:
- The dates the case was opened, resolved, and closed.
- The title of the case
- The contents of the extra fields (by default, these are Computer and Version, but the FogBugz Administrator can rename these fields)
- The original and current estimates, and elapsed time spent on the case
- The release notes for the case
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:
- Area, with information on the area that the case is assigned to.
- Category, which tells you whether this case is a bug, feature, or inquiry.
- FixFor, with information on the “Fix For” release assigned to the case.
- Person, which contains information on FogBugz users. There are two foreign keys to this table, one for the person who opened the case and one for the person to whom the case is currently assigned.
- Priority, with information on the case’s priority.
- Project, with information on the project where the case was reported.
- Status, with information on the current status of the case. There’s also a flag column, fOpen, which tells you at a glance whether the case is open or closed.
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 refers to the detailed schema of a FogBugz 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 are referred to commonly show the column name and SQL Server data type for every column in every table in a standard FogBugz database. If you’re using MySQL the column names will be the same, but the data types will differ.
You can obtain the latest data dictionary by navigating to your FogBugz URL (e.g. http://someserver.com/fogbugz), and going to the Schema (e.g. default.asp?pg=pgSchema) page. For example, http://someserver.com/fogbugz/default.asp?pg=pgSchema, will describe the FogBugz schema.