show/hide this revision's text 4 deleted 10 characters in body

An online version of the schema (which may differ slightly from the DB Version on your FogBugz site) can be found here:

http://www.fogcreek.com/fogbugz/KB/dbsetup/FogBugzSchema.html

http://fogbugz.stackexchange.com/fogbugz-database-schema

If your FogBugz site is up and running, you can always see the exact schema in use by going here:

<YOUR FOGBUGZ SITE URL>/default.asp?pg=pgSchema
show/hide this revision's text 3 deleted 7407 characters in body; added 2 characters in body

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

An online version of the database, we've prepared this guide to the FogBugz 7.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

  • The FogBugz Naming Conventions
  • Locating Information on Cases
  • The FogBugz Data Dictionary
  • 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:

  • 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 may differ slightly 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:

  • 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 DB Version , but the on your FogBugz Administrator can rename these fieldssite)
  • 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 includecan be found here:

  • 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

    http://www.fogcreek.com/fogbugz/KB/dbsetup/FogBugzSchema.html

    If your 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 site is open or closed.

    • While the Bug table provides a static picture of the current state of a caseup and running, 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 always 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

    A detailed description of the database exact schema for your FogBugz site can viewed at any time in use by navigating togoing here:

    On this page every table (and every column within each table) is enumerated and briefly described. 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.

    Relationship Diagrams

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

    NOTE #1: 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.

    NOTE #2: These diagrams, while helpful, may not be 100% up-to-date. Consult your site's schema page for a perfectly accurate representation of your data schema.

    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.
    show/hide this revision's text 2 added 12 characters in body

    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 7.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:

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

    A detailed description of the database schema for your FogBugz site can viewed at any time by navigating to:

    <YOUR FOGBUGZ SITE URL>?pg=pgSchema>/default.asp?pg=pgSchema

    On this page every table (and every column within each table) is enumerated and briefly described. 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.

    Relationship Diagrams

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

    NOTE #1: 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.

    NOTE #2: These diagrams, while helpful, may not be 100% up-to-date. Consult your site's schema page for a perfectly accurate representation of your data schema.

    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.
    show/hide this revision's text 1