Database Schema of FogBugz


Follow

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

IMPORTANT! Modifying your FogBugz database directly may represent a risk to the integrity of the data and is not supported. You should not alter the contents of the tables in any way. Use this guide only as an aid in writing the queries to read the data.


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

 

Back to top


 

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.

     

Back to top 



The BugEvent Table

  • The BugEvent table has its automatically-generated primary key called ixBugEvent, which increases as the events are added to the table. Any given case is likely to have gaps in its sequence of ixBugEvent values. However, if you order all the rows in the BugEvent table that pertains to a single case from lowest to the highest ixBugEvent value, they will form the history of that case.
  • 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 the rows in the BugEvent table with a single ixBug 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 the sVerb column to see what each event represents (Opened, Edited, Resolved, Assigned and so on).
  • There is also a foreign key ixPerson 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 an attached file, and automatically-generated system messages when the user changes things like estimates or categories.

 

Back to top 


 

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.

NOTE: The table descriptions that are referred to commonly show the column name and the SQL Server data type for every column in every table in a standard FogBugz database. If you are using MySQL (older FogBugz versions), the column names will be the same, but the data types will differ.

 

Back to top


 

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:

  1. Navigating to your FogBugz URL, e.g., https://<someserver>.fogbugz.com/.
  2. 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


mceclip1.png

 



FogBugz On-Premise (> 8.15.xx)

You can obtain the latest data dictionary by:

  1. Navigating to your FogBugz URL, e.g.,http://<instance>.<someserver>.com/.
  2. 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

 

IMPORTANT: For security reasons, you need to access the link from within the FogBugz server and log in to FogBugz with an admin account. Otherwise, the system will return a "403 Forbidden" error message like the one below:

mceclip0.png

Back to top


FogBugz For Your Server (<8.8.55)

You can obtain the latest data dictionary by:

  1. Navigating to your FogBugz URL, e.g., http://<someserver>.com/fogbugz.
  2. 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

 

Back to the top


Related Articles