Overview
FogBugz uses an open and unlocked Microsoft SQL database to store its information. Using this guide, you should be able to navigate your database to find the information that you need.
This article can be applied only by On-Premise customers since they host their FogBugz databases themselves.
On-Demand customers can retrieve custom data using FogBugz API. The naming conventions listed in this article also apply to the naming conventions used in the FogBugz API.
Introduction
If you are familiar with SQL queries, you can feed data into different reporting tools like Power BI Report Server, Microsoft SQL Server Reporting Services (SSRS), or Crystal Reports to create your custom reports based on the information stored in your FogBugz database.
Description
The FogBugz Naming Conventions
FogBugz uses a modified Hungarian Notation for naming the columns in tables. Almost every column in the FogBugz database has a prefix that tells you about that column. You will find the following prefixes in use:
Prefix | Description |
---|---|
c |
Numeric fields that record a count of something |
d |
Floating-point numeric fields (decimal or double) |
f |
Flags NOTE: You cannot assume that a flag has any particular data type. |
hrs |
Measurement in hours |
dt |
Date/Time columns |
i |
Integers |
ix |
Primary and foreign (IndeX) keys (Keys that can be joined will have the same name on both sides of the join.) |
n |
Numbers, typically enumerations |
s |
Character (string) data |
Locating Information on Cases
The core of the FogBugz database is the Bug
table and the BugEvent
table. If you understand the content of these two tables, then you should be able to find most of what you need in the database.
Even though they have the word Bug in their names, these tables contain information on Cases of all types (there are no separate tables for features and inquiries).
Cases are uniquely identified by the value in the ixBug
column. This is a numeric key that also serves as the FogBugz case number.
To find the history for a particular case, you need to locate all the rows in the BugEvent
table which has that case’s ixBug
value in the ixBug
column. The ixBug
value serves as a foreign key in that column.
- The
Bug
table contains information that describes the current state of each case in the system, past, and present (and links to the 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.
The Bug
Table
- The
Bug
table contains columns that record essential information about every case (refer to the data dictionary section below for details). In addition to the case number, this information includes: - The dates the case was opened, resolved, and closed on.
- The title of the case (the length for case titles is limited to 128 characters).
- The content 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 the elapsed time spent on the case.
- The release notes for the case.
- For cases that were submitted by email, the
Bug
table contains details of the source and the actions taken by the Bayesian email sorter. - For cases that came in via BugzScout, the
Bug
table tracks the number of times the case was submitted. - For cases ported from discussion lists, there is a link back to the discussion topic.
- The
Bug
table also contains foreign keys to several lookup tables with additional details on the case. These other tables include the following:
Table Description Area
Information on the area that the case is assigned to Category
Whether this case is a bug, feature, or inquiry FixFor
Information on the Fix For release assigned to the case Person
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
Information on the priority of the case Project
Information on the project in which the case was reported Status
Information on the current status of the case. There is also a flag column in Bug
table,fOpen
, which tells you at a glance whether the case is Open or Closed.
The BugEvent
Table
- The
BugEvent
table has its automatically-generated primary key calledixBugEvent
, which increases as the events are added to the table. Any given case is likely to have gaps in its sequence ofixBugEvent
values. However, if you order all the rows in theBugEvent
table that pertains to a single case from lowest to the highestixBugEvent
value, they will form the history of that case. - While the
Bug
table provides a static picture of the current state of a case, theBugEvent
table records the history of the case. If you collect all the rows in theBugEvent
table with a singleixBug
value, you will have the history of that case. - To show what happened, first sort the records by the
dt
column, which holds the date and time that each event was recorded. You can check thesVerb
column to see what each event represents (Opened, Edited, Resolved, Assigned and so on). - There is also a foreign key
ixPerson
to thePerson
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 an attached file, and automatically-generated system messages when the user changes things like estimates or categories.
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 should be familiar with the operation of FogBugz to see how all the pieces fit together. However, if you know how the application works, this data dictionary should aid you in finding the data that you are looking for.
Database Schema
FogBugz provides an advanced option for listing your latest database schema dictionary, which applies to the database version that you are currently using. Depending on your FogBugz version, you will need to use a different URL to access it.
Note: You will need to be logged in as Administrator, otherwise this link will not be available or it will respond with an error message.
FogBugz On-Demand
You can obtain the latest data dictionary along with the database version by:
- Navigating to your FogBugz URL, e.g.,
https://<someserver>.fogbugz.com/
. - Going to the Schema page, e.g.,
/f/page?pg=pgSchema
.
This combination would result in the following link describing the FogBugz schema:
https://<someserver>.fogbugz.com/f/page?pg=pgSchema
FogBugz On-Premise (> 8.15.xx)
You can obtain the latest data dictionary by:
- Navigating to your FogBugz URL, e.g.,
http://<instance>.<someserver>.com/
. - Going to the Schema page, e.g.,
/f/debug/database/schemacheck
.
This combination would result in the following link describing the FogBugz schema:
http://instance.someserver.com/f/debug/database/schemacheck
FogBugz For Your Server (<8.8.55)
You can obtain the latest data dictionary by:
- Navigating to your FogBugz URL, e.g.,
http://<someserver>.com/fogbugz
. - Going to the Schema page, e.g.,
default.asp?pg=pgSchema
.
This combination would result in the following link describing the FogBugz schema:
http://someserver.com/fogbugz/default.asp?pg=pgSchema