Search FogBugz

EnglishPortuguese

The FogBugz 4.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 4.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 very 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

This section of the schema guide describes the detailed schema of a FogBugz 4.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 4.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.

Table: ACL

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

Columns

• ixGroup (int) - Foreign key to the Groups table.
• ixPerson (int) - Foreign key to the Person table. The special value -1 is used for "all users"
• iPermission (int) - Permissions level of the indicated user on the indicated group: 0=none, 1=read-only, 2=read/write.

Table: Area

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

Columns

• ixArea (int) - AutoNumber primary key.
• ixProject (int) - Foreign key to the Project table.
• sArea (nvarchar(50)) - Name of the area.
• fDeleted (smallint) - Set to 1 if the area has been deleted.
• nType (int) - Type of area: 0=normal, 1=not spam, 2=undecided, 3=spam.
• cDoc (int) - Number of documents that have been sorted into this area by the Bayesian e-mail classifier.

Table: Attachment

Files attached to cases.

Columns

• ixAttachment (int) - AutoNumber primary key.
• sData (image) - Binary data of the attachment.

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

• ixAutoResponseRecord (int) - AutoNumber primary key.
• sEmail (nvarchar(255)) - E-mail address to which an autoresponse was sent.
• dt (datetime) - Date and time that the autoresponse was sent.

Table: Bug

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

Columns

• ixBug (int) - AutoNumber primary key and FogBugz case number.
• fOpen (smallint) - 0 if the bug is currently closed, 1 if the bug is currently open.
• dtOpened (datetime) - Date and time the case was opened.
• dtResolved (datetime) - Date and time the case was resolved.
• dtClosed (datetime) - Date and time the case was closed.
• sTitle (nvarchar(128) - The title of the case.
• ixProject (int) - Foreign key to the Project table.
• ixArea (int) - Foreign key to the Area table.
• ixPersonOpenedBy (int) - Foreign key to the Person table, specifying who opened the case.
• ixPersonAssignedTo (int) - Foreign key to the Person table, specifying who the case is currently assigned to.
• ixStatus (int) - Foreign key to the Status table.
• ixPriority (int) - Foreign key to the Priority table.
• ixFixFor (int) - Foreign key to the FixFor table.
• sVersion (nvarchar(40)) - Contents of the first extra field (by default, this field is named Version, but you can change the field name on the FogBugz Site Configuration page).
• sComputer (nvarchar(80) - Contents of the second extra field (by default, this field is named Computer, but you can change the field name on the FogBugz Site Configuration page).
• hrsOrigEst (float) - Original time estimate, in hours.
• hrsCurrEst (float) - Current time estimate, in hours.
• hrsElapsed (float) - Elapsed time, in hours.
• c (int) - Number of times this case has been reported by BugzScout; 0 for other cases.
• sCustomerEmail (nvarchar(255)) - E-mail address of the person submitting the case, or null if the case was not submitted by e-mail.
• sGPFid (nvarchar(255) - Unique identifier generated by BugzScout used to consolidate multiple occurences of the same crash into a single bug.
• ixMailbox (int) - Foreign key to the Mailbox table. 0 if the case was not submitted by e-mail.
• ixCategory (int) - Foreign key to the Category table.
• sBugDispatchoID (nvarchar(30)) - Hash value used to identify cases opened by e-mail.
• ixBugEventLatest (int) - Number of the most recent event in the BugEvent table pertaining to this case.
• fTrained (smallint) - 1 for e-mail cases that have been trained.
• fReplied (smallint) - 1 for e-mail cases that have been replied.
• fForwarded (smallint) - 1 for e-mail cases that have been forwarded.
• ixOriginalPrediction (int) - Foreign key to the Prediction table indicating the e-mail sorter's original prediction for the disposition of this case.
• nStatType (int) - Null if this case did not come through the e-mail sorter. Otherwise, set by the e-mail sorter to a value based on training feedback: 0=untrained, 1=correct, 2=false positive, 3=false negative, 4=undecided marked spam, 5=undecided marked non-spam, 6=sorted to wrong area.
• sTicket (nvarchar(40) - Ticket number issued to customer who submitted the case via e-mail.
• ixDiscussTopic (int) - Foreign key into the DiscussTopic table.
• dtDue (datetime) - Date and time the case is due.
• sReleaseNotes (ntext) - Release notes for this case, if any.

Table: BugEvent

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

Columns

• sFilename (nvarchar(50)) - Name of attached file, if any.
• ixBugEvent (int) - AutoNumber primary key.
• ixBug (int) - Foreign key to the Bug table.
• sVerb (nvarchar(128)) - The verb shown on the user interface for this event (such as "Opened," "Edited," or "Closed.")
• dt (datetime) - The date and time that the event was recorded.
• ixPerson (int) - Foreign key to the Person table indicating the person who saved this event.
• s (ntext) - The notes for the event.
• fEmail (bit) - True if the event was generated by incoming e-mail, null otherwise.
• fExternal (bit) - True if the event was generated by an external script (such as BugzScout) rather than manually by a user, null otherwise.
• sChanges (ntext) - Automatic description of changes made to the case (such as changes to estimates or priority).
• ixAttachment (int) - Foreign key to the Attachment table.

Table: BugRelation

The BugRelation table tracks duplicate cases.

Columns

• ixBugFrom (int) - Case number that was closed as a duplicate.
• ixBugTo (int) - Case that it was closed as a duplicate of.

Table: Category

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

Columns

• ixCategory (int) - AutoNumber primary key.
• sCategory (nvarchar(255)) - Name of the category.
• ixStatusDefault (int) - Foreign key to the Status table, indicating the initial status for new cases in this category.

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

• ixCVS (int) - AutoNumber primary key.
• ixBug (int) - Foreign key to the Bug table.
• sFile (nvarchar(255) - Source file containing change related to this case.
• sPrev (nvarchar(255)) - Previous version number of the file.
• sNew (nvarchar(255) - New version number of the file.

Table: DBUpdate

The DBUpdate table tracks changes made to the FogBugz database by updates to FogBugz.

Columns

• ixDBUpdate (int) - AutoNumber primary key.
• sUpgradeKey (nvarchar(30)) - Unique key identifying a particular upgrade.
• nChangeType (int) - Enumeration indicating the type of change made to the database.
• sChangeSpecifics (nvarchar(80)) - Description of the change.
• fReversed (int) - Flag indicating whether the change has been backed out.

Table: DiscussEmail

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

Columns

• dt (datetime) - Date and time the e-mail was sent.
• nRemoteIP1 (tinyint) - First octet of user's IP address.
• nRemoteIP2 (tinyint) - Second octet of user's IP address.
• nRemoteIP3 (tinyint) - Third octet of user's IP address.
• nRemoteIP4 (tinyint) - Fourth octet of user's IP address.
• ixDiscussTopic (int) - Foreign key to the DiscussTopic table.
• sUniqueID (nvarchar(40)) - Cookie sent to a user who sends email via the discussion groups.
• sUniqueEmail (nvarchar(40)) - Unique random value used to prevent the same email being sent twice, if, for example, the user hits "Refresh" on a web page after sending email.
• sFullName (nvarchar(128)) - Sender's full name.
• sEmail (nvarchar(128)) - Sender's e-mail address.

Table: DiscussGroup

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

Columns

• ixDiscussGroup (int) - AutoNumber primary key.
• sFullName (nvarchar(255)) - Full name of the discussion group.
• sURLName (nvarchar(32)) - Short name of the discussion group, used in URLs.
• sTagLineHTML (ntext) -HTML for tagline.
• sSidebarHTML (ntext) - HTML for sidebar.
• sPostHelpHTML (ntext) - HTML for posting guidelines.
• ixAreaSpam (int) - Foreign key to the Area table, indicating the area to autosort spam posts.
• ixAreaNonSpam (int) - Foreign key to the Area table, indicating the area to autosort non-spam posts.
• cDaysHomePage (int) - Number of days worth of topics that will be maintained on the home page for this group.
• fDeleted (smallint) - 1 if this group has been deleted.
• fPublic (smallint) - 1 if this group is open to the public.
• fRequireApproval (smallint) - 1 if all posts in this group require approval.
• ixAreaUndecided (int) - Foreign key to the Area table, indicating the area to autosort non-spam posts.

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

• ixDiscussLayout (int) - AutoNumber primary key.
• cPixelsLeftSidebar (int) - Left sidebar width in pixels.
• cPixelsMain (int) - Main body width in pixels.
• sTopHTML (ntext) - HTML for top of page.
• sBottomHTML (ntext) - HTML for bottom of page.
• sStylesCSS (ntext) - CSS to add to the discussion page.

Table: DiscussTopic

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

Columns

• ixDiscussTopic (int) - AutoNumber primary key.
• ixDiscussGroup (int) - Foreign key to the DiscussGroup table.
• sHeadline (nvarchar(64) - Subject of the discussion topic.
• ixDiscussTopicParent (int) - Foreign key back to this same table, pointing to the first post in the thread.
• sFullName (nvarchar(128)) - Full name of the poster.
• sURL (nvarchar(128)) - Home page of the poster.
• sEmail (nvarchar(128)) - E-mail address of the poster.
• dt (datetime) - Date and time the post was submitted.
• sUniqueID (nvarchar(40)) - Cookie sent to a user who posts a message to the discussion groups.
• sUniquePost (nvarchar(40)) - Unique random value used to prevent the same post being sent twice, if, for example, the user hits "Refresh" on a web page after posting a new topic.
• sUserAgent (nvarchar(255)) - User agent of the browser making the post.
• sPost (ntext) - Text of the post.
• ixPerson (int) - Foreign key to the Person table, if the post was made by a logged-in user.
• nRemoteIP1 (tinyint) - First octet of the remote user's IP address.
• nRemoteIP2 (tinyint) - Second octet of the remote user's IP address.
• nRemoteIP3 (tinyint) - Third octet of the remote user's IP address.
• nRemoteIP4 (tinyint) - Fourth octet of the remote user's IP address.
• ixPersonDeletedBy (int) - Foreign key to the Person table indicating who deleted this post (if it was deleted).
• ixArea (smallint) - Foreign key to the Area table, indicating the area that this post was sorted to by the Bayesian e-mail sorter.
• fTrained (smallint) - 1 if this post was trained on.

Table: Duplicates

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

Columns

• ixBugDupe (int) - Foreign key to the Bug table for the duplicate case.
• ixBugDupeOf (int) - Foreign key to the Bug table for the original case.

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

• ixFilter (int) - AutoNumber primary key.
• fOpenBugs (nvarchar(4)) - "ON" if the filter includes open bugs, "OFF" if the filter does not include open bugs.
• ixCategory (int) - Foreign key to the Category table indicating the Category selected for this filter or -1 for all categories.
• ixProject (int) - Foreign key to the Project table indicating the Project selected for this filter or -1 for all projects.
• ixArea (int) - Foreign key to the Area table indicating the Area selected for this filter or -1 for all areas.
• ixPersonOpenedBy (int) - Foreign key to the Person table indicating the Person Opened By selected for this filter or -1 for anyone.
• ixPersonAssignedTo (int) - Foreign key to the Person table indicating the Person Assigned To selected for this filter or -1 for anyone.
• ixStatus (int) - Foreign key to the Status table indicating the Status selected for this filter or -1 for all statuses.
• ixPriority (int) - Foreign key to the Priority table indicating the Priority selected for this filter or -1 for all priorities.
• maxrecords (int) - Maximum number of cases to show, or 0 to show all matching cases.
• sort1 (int) - Enumeration which specifies the first field to sort by.
• sort2 (int) - Enumeration which specifies the second field to sort by.
• sort3 (int) - Enumeration which specifies the third field to sort by.
• ixFixFor (int) - Foreign key to the FixFor table indicating the Fix For selected for this filter or -1 for all fix fors.
• openInLast (int) - Enumeration indicating how recently the case should have been opened, or -1 to include all.
• resolvedInLast (int) - Enumeration indicating how recently the case should have been resolved, or -1 to include all.
• closedInLast (int) - Enumeration indicating how recently the case should have been closed, or -1 to include all.
• ixPerson (int) - Owner of this filter.
• sFilterName (nvarchar(40)) - Name of this filter.
• fClosedBugs (nvarchar(4)) - "ON" if the filter includes closed bugs, "OFF" if the filter does not include closed bugs.
• fGridView (int) - 1 for grid view, null for list view.
• priorityRange (int) - Controls how the ixPriority column is interpreted: -1 = less than or equal to priority, 0 = equal to priority, 1 = greater than or equal to priority.
• sentBy (nvarchar(255)) - Full or partial e-mail address of person submitting the case for this filter.
• fMissingEstimate (bit) - Filters for cases missing estimates.
• fSubscribedBugs (bit) - Filters to show only cass to which the user is subscribed.
• ixGroup (int) - Foreign key to Group table indicating the Group selected for this Filter or -1 for all groups.
• sComputer (nvarchar(255)) - Value to filter on in the first Extra field (labeled "Computer" by default).
• sVersion nvarchar(255)) - Value to filter on in the second Extra field (labeled "Version" by default).
• dueInNext (int) - Enumeration indicating when the case should be due, or -1 to include all.
• fGlobal (smallint) - 1 for a global filter, available to all users.

For openInLast, resolvedInLast, closedInLast, and dueInNext, these values are valid: -1=any time, 0=today, 1=today and yesterday, 2=this week, 3=this month, 4=2 months, 5=3 months 6=6 months, 7=1 year.

Table: FixFor

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

Columns

• ixFixFor (int) - AutoNumber primary key.
• dt (datetime) - The planned date of the release, or Null for (None).
• bDeleted (smallint) - 0 if cases can be assigned to this release, 1 if cases cannot be assigned to this release.
• ixProject (int) - Foreign key to the Project table. -1 for releases that are available to all projects.
• sFixFor (nvarchar(255) - Name of the release.

Table: Groups

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

Columns

• ixGroup (int) - AutoNumber primary key.
• sName (nvarchar(255) - Name of the client or department.
• iType (int) - Group type: 1=client, 2=department.
• sNotes (ntext) - Notes describing the group.

Table: Holiday

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

Columns

• ixHoliday (int) - AutoNumber primary key.
• sHoliday (int) - Name of the holiday.
• dtHolidate (datetime) - The date the holiday occurs.

Table: ID

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

Columns

• id (varchar(30)) - The unique identifier for this FogBugz database.

Table: Licenses

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

Columns

• ixLicense (int) - AutoNumber primary key.
• sLicense (nvarchar(512))
• sSignature (binary)
• sLicense2 (nvarchar(512))
• sSignature2 (binary)
• sLicense3 (nvarchar(512))
• sSignature3 (binary)

Table: Mailbox

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

Columns

• ixMailbox (int) - AutoNumber primary key.
• sEmail (nvarchar(255)) - E-mail address of the mailbox.
• sFullName (nvarchar(255)) - Full name to be used when sending mail from this mailbox.
• sUser (nvarchar(255)) - Username on the mail server.
• sPass (nvarchar(255)) - Password on the mail server.
• sServer (nvarchar(255)) - Mail server name.
• sPort (nvarchar(255)) - Port to use on the mail server.
• sURLPrefix (nvarchar(255)) - Top-level URL of your FogBugz installation.
• ixProject (int) - Foreign key to the Project table, indicating the default for new cases created by mail.
• ixArea (int) - Foreign key to the Area table, indicating the default for new cases created by mail.
• ixFixFor (int) - Foreign key to the FixFor table, indicating the default for new cases created by mail.
• ixPriority (int) - Foreign key to the Priority table, indicating the default for new cases created by mail.
• ixPersonOpenedBy (int) - Foreign key to the Person table, indicating the default user opened by for new cases created by mail.
• ixPersonAssignedTo (int) - Foreign key to the Person table, indicating the default user assigned to for new cases created by mail.
• nDeleteSPAM (int) - Delete closed messages marked as spam that are older than this many days.
• nDeleteInquiries - Delete closed messages marked as inquiries that are older than this many days.
• fDeleted (smallint) - 1 if this mailbox has been deleted.
• fAutoSort (smallint) - 1 if mail to this mailbox should be autosorted.
• fAutoReply (smallint) - 1 if mail to this mailbox should generate an autoreply.
• sReplySubject (ntext) - Subject line for autoreplies.
• fDueDate (smallint) - 1 if cases should automatically generate due dates.
• nDue (int) - Number of time units until a newly created case is due.
• nDueTime (int) - Which time unit to use when generating due dates (1=hours, 2=working hours, 3=days, 4=working days).
• sLastMessageDownloaded (nvarchar(255) - UIDL of last message downloaded from the server.
• sTemplate (ntext) - Message template for replies.
• sReplyMessage (ntext) - Message for autoreplies.

Table: MailQueue

The MailQueue contains messages being sent by FogBugz itself.

Columns

• ixMailQueue (int) - AutoNumber primary key.
• dt (datetime) - Date the message was placed in the queue.
• sRecipients (ntext) - Recipients of the message.
• sHeaders (ntext) - SMTP headers for the message.
• sMessage (ntext) - Message text.

Table: Notification

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

Columns

• ixNotification (int) - AutoNumber primary key.
• sShortDesc (ntext) - Short description of the error.
• sURL (nvarchar(255)) - URL associated with the error.
• sLongDesc (ntext) - Time the error occured and details of the error.

Table: Person

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

Columns

• ixPerson (int) - AutoNumber primary key.
• sFullName (nvarchar(50)) - Person's name.
• sEmail (nvarchar(255)) - Person's e-mail address.
• sPassword nvarchar(50)) - Person's password.
• fNotify (smallint) - 1 if the user wants e-mail notifications, 0 if they do not.

Note: See the Filter table for more details on the Filter columns.

• FILTER_fOpenBugs (nvarchar(4)) - Saved filter open bugs value.
• FILTER_fClosedBugs (nvarchar(4)) - Saved filter closed bugs value.
• FILTER_ixCategory (int) - Saved filter category value.
• FILTER_ixProject (int) - Saved filter project value.
• FILTER_ixArea (int) - Saved filter area value.
• FILTER_ixPersonOpenedBy (int) - Saved filter opened by value.
• FILTER_ixPersonAssignedTo (int) - Saved filter assigned to value.
• FILTER_ixStatus (int) - Saved filter status value.
• FILTER_ixPriority (int) - Saved filter priority value.
• FILTER_maxrecords (int) - Saved filter max records value.
• FILTER_sort1 (int) - Saved filter sort1 value.
• FILTER_sort2 (int) - Saved filter sort2 value.
• FILTER_sort3 (int) - Saved filter sort3 value.
• FILTER_ixFixFor (int) - Saved filter fix for value.
• FILTER_openInLast (int) - Saved filter open in last value.
• FILTER_resolvedInLast (int) - Saved filter resolved in last value.
• FILTER_closedInLast (int) - Saved filter closed in last value.
• FILTER_fGridView (int) - Saved filter grid view value.
• FILTER_priorityRange (int) - Saved filter priority range value.
• FILTER_sentBy (nvarchar(255)) - Saved filter sent by value.
• FILTER_fMissingEstimate (bit) - Saved filter missing estimate value.
• FILTER_fSubscribedBugs (bit) - Saved filter subscribed bugs value.
• sVersion (nvarchar(40)) - Last value entered in the first Extra field.
• sComputer (nvarchar(80)) - Last value entered in the second Extra field.
• sPhone (nvarchar(32)) - User's phone number.
• fExpert (smallint) - 1 if the user is running in expert mode.
• fAdministrator (smallint) - 1 if the user is an administrator.
• fDeleted (smallint) - 1 if the user has been deleted.
• sFrom (nvarchar(255)) - From address to use on e-mail from this user.
• ixArea (int) - Last area that this user entered a csse for.
• sVersionSeen (nvarchar(255)) - Last version upgrade message seen (for administrators only)
• sContractSeen (nvarchar(255)) - Last maintenance contract message seen (for administrators only)
• sPasswordVersion (nvarchar(255)) - Controls which version of the password library FogBugz uses.
• fEscalationReport (int) - 1 if this user should get escalation reports via e-mail.
• fGridView (int) - 1 if this user views cases in grid view, 0 for list view.
• FILTER_sComputer (nvarchar(255)) - Saved filter computer value.
• FILTER_sVersion (nvarchar(255)) - Saved filter version value.
• FILTER_dueInNext (int) - Saved filter due in next value.
• FILTER_ixGroup (int) - Saved filter group value.
• sSnippetKey (nvarchar(2)) - Keystroke used to activate snippets.
• viewCol1 (int) - Enumeration used to control which columns are viewable in filters.
• viewCol2 (int) - Enumeration used to control which columns are viewable in filters.
• viewCol3 (int) - Enumeration used to control which columns are viewable in filters.
• viewCol4 (int) - Enumeration used to control which columns are viewable in filters.
• viewCol5 (int) - Enumeration used to control which columns are viewable in filters.
• viewCol6 (int) - Enumeration used to control which columns are viewable in filters.
• viewCol7 (int) - Enumeration used to control which columns are viewable in filters.
• FILTER_sFilterName (nvarchar(40)) - Saved filter name.

Table: Prediction

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

Columns

• ixPrediction (int) - AutoNumber primary key.
• ixBug (int) - Foreign key to the Bug table.
• ixAreaA (int) - Foreign key to the Area table for the first candidate area.
• ixAreaB (int) - Foreign key to the Area table for the second candidate area.
• dProb (float) - Probability of the prediction.
• sCluesA (nvarchar(255)) - Clues indicating that the case belongs in Area A.
• sCluesB (nvarchar(255)) - Clues indicating that the case belongs in Area B.
• fWinner (smallint) - Flag indicating prediction: 0=tie, 1=Area A, 2=Area B.

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

• ixPriority (int) - AutoNumber primary key, ranging from 1 to 7.
• sPriority (nvarchar(255)) - Text description of the priority

Table: Project

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

Columns

• ixProject (int) - AutoNumber primary key.
• sProject (nvarchar(128)) -
• ixPersonOwner (int) - Foreign key to the Person table indicating the primary contact for this project.
• fAllowPublicSubmit (bit) - True if this project allows public submissions, False if it does not.
• fDeleted (real) - 0 if this project is active, -1 if it has been deleted.
• ixGroup (int) - Foreign key to the Groups table.
• fInbox (smallint) - 1 if this project can be used as an inbox to receive mail, 0 otherwise.

Table: Scout

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

Columns

• Description nvarchar(255)) - Error number and text description unique to this crash.
• ixBug (int) - Foreign key to the Bug table indicating the bug that this Scout entry applies to.
• sMessage (ntext) - Message (if any) to send back to the user reporting the crash.
• fStopReporting (smallint) - If set to 1, new reports matching this description will be ignored.

Table: Snippet

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

Columns

• ixSnippet (int) - AutoNumber primary key .
• ixPerson (int) - Foreign key to the Person table indicating the person who created the snippet.
• fGlobal (smallint) - 0 for a personal snippet, 1 for a global snippet.
• sName (nvarchar(16)) - The name of the snippet.
• fDeleted (smallint) - 1 if the snippet has been deleted.
• sComment (nvarchar(80)) - Comment describing the snippet.
• s (ntext) - The text of the snippet.

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

• sKey (nvarchar(15)) - Setting name.
• nValue (float) - Setting value.

Table: SorterToken

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

Columns

ixToken (int) - AutoNumber primary key.

nLatestDoc (int) - Number of the n'th latest trained document to contain this token, used to determine the age of tokens by evaluating how recently they have been trained.

sToken (nvarchar(40)) - String value of the token.

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

• ixStatus (int) - AutoNumber primary key.
• sStatus (nvarchar(30)) - Description of the status
• ixCategory (int) - Foreign key to the category table, indicating the category of cases to which this status applies. The special value -1 indicates a status that can apply to any category of case.

Table: Subscriptions

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

Columns

• ixPerson (int) - Foreign key to the Person table.
• ixBug (int) - Foreign key to the Bug table.

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

• ixToken (int) - Foreign key to the SorterToken table, indicating the SorterToken being associated with an Area.
• ixProject (smallint) - Foreign key to the Project table, indicating the Project of the associated Area.
• ixArea (smallint) - Foreign key to the Area table, indicating the Area being associated with a SorterToken.
• cGood (int) - Number of messages containing the SorterToken that have been trained into the associated Area.
• cBad (int) - Number of messages containing the SorterToken that have been trained into any of the Project's Areas other than the associated Area.

Table: Tokens

The Tokens table records persistent user login information.

Columns

• ixToken (int) - AutoNumber primary key.
• fbToken (char(30)) - Unique token indicating a persisted session.
• sIPAddress (nvarchar(50)) - IP address the user logged in from.
• ixPerson (int) - Foreign key to the persons table.

Table: TrainingRequest

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

Columns

• ixRequest (int) - AutoNumber primary key.
• ixBug (int) - Foreign key to the Bug table, indicating the case being trained on, or -1 if no specific case is involved.
• ixDiscussTopic (int) - Foreign key to the DiscussTopic table, indicating the post being trained on, or -1 is no specific post is involved.
• ixAreaFrom (int) - Foreign key to the Area table, indicating the area to be untrained from, or -1 if an entire new area is being inserted.
• ixAreaTo (int) - Foreign key to the Area table, indicating the area to be trained to, or -1 if an entire area is being deleted.

Table: Version

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

Columns

• ixVersion (int) - The current database version.

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

• sKey (nvarchar(15)) - Setting name.
• nValue (float) - Setting value.

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.