Search FogBugz

EnglishPortuguese

The FogBugz 6.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 6.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

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

Database Version: 632

Table: Setting

The Setting table contains key-value pairs for FogBugz site settings.

Columns

• sKey (nvarchar(64)) - The site setting's name.
• sValue (nvarchar(255)) - The site setting's value.

Table: ACL

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

Columns

• sSectionName (nvarchar(50)) - Name of the table the permission is for
• ixSection (int) - Foreign key to the table named in sSectionName
• 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, 3=admin.

Table: Area

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

Columns

• ixArea (int(11)) - AutoNumber primary key.
• ixProject (int) - Foreign key to the Project table.
• ixPersonOwner (int) - Foreign key to the Person table indicating primary contact for area (if -1, use Project's primary).
• sArea (nvarchar(50)) - Name of the area.
• fDeleted (int) - 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: 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(11)) - AutoNumber primary key.
• sEmail (nvarchar(255)) - E-mail address to which an autoresponse was sent.
• dt (datetime) - Date and time in UTC that the autoresponse was sent.

Table: BugEvent

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

Columns

• ixBugEvent (int(11)) - 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 in UTC 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 (smallint) - True if the event was generated by incoming e-mail, null otherwise.
• fExternal (smallint) - 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).

Table: Attachment

Files attached to cases.

Columns

• ixAttachment (int(11)) - AutoNumber primary key.
• sData (image) - Binary data of the attachment.
• sFilename (nvarchar(255)) - The filename of the attachment.
• fFileStatus (int) - Whether or not the file has been deleted: 1=deleted, 0 otherwise.
• ixBugEvent (int) - Foreign key to the BugEvent table telling us which BugEvent this file is attached to.
• ixWiki (int) - Foreign key to the Wiki table telling us which Wiki this file is attached to.
• ixTemplate (int) - Foreign key to the Template table telling us which Template this file is attached to.

Table: Bug

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

Columns

• ixBug (int(11)) - AutoNumber primary key.
• fOpen (int) - 0 if the bug is currently closed, 1 if the bug is currently open.
• dtOpened (datetime) - Date and time in UTC that the case was opened.
• sTitle (nvarchar(128)) - The title of the case.
• sOriginalTitle (nvarchar(128)) - The original title of the case.
• ixBugEventLatestText (int) - Foreign key to the BugEvent table telling us what was the most recent bug event that had a text note.
• sLatestTextSummary (nvarchar(255)) - A chunk of text from the most recent bug event that had a text note.
• 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.
• ixPersonResolvedBy (int) - Foreign key to the Person table, specifying who is the most recent resolver of the bug.
• ixPersonClosedBy (int) - Foreign key to the Person table, specifying who is the most recent closer of the bug.
• ixPersonLastEditedBy (int) - Foreign key to the Person table, specifying who is the most recent editor of the bug.
• ixPersonEstimator (int) - Foreign key to the Person table. FogBugz defines the estimator of a case as the last person to edit the estimate before time elapses on the case.
• 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) - The sum of all TimeIntervals for this bug and hrsElapsedExtra, in hours.
• hrsElapsedExtra (float) - Elapsed time not accounted for in TimeIntervals, 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.
• ixCategory (int) - Foreign key into the Category table.
• dtResolved (datetime) - Date and time in UTC that the case was resolved.
• dtClosed (datetime) - Date and time UTC that the case was closed.
• ixMailbox (int) - Foreign key to the Mailbox table. 0 if the case was not submitted 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.
• fEstLocked (smallint) - 1 if the original estimate and estimator are locked for this case.
• 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.
• sReleaseNotes (ntext) - Release notes for this case, if any.
• ixDiscussTopic (int) - Foreign key into the DiscussTopic table, indicating the discussion thread comment that initiated this bug, if any.
• dtDue (datetime) - Date and time in UTC that this case is due.
• ixBugEmail (int) - Foreign key for a simplified version of sCustomerEmail

Table: BugEmail

The BugEmail table tracks email addresses.

Columns

• ixBugEmail (int(11)) - AutoNumber primary key.
• sEmail (nvarchar(255)) - Email
• sFullName (nvarchar(255)) - Real Name

Table: BugRelation

The BugRelation table tracks related cases.

Columns

• ixBugFrom (int) - Case number that contains the reference (0 if wiki->bug relation).
• ixBugTo (int) - The case number of the bug referred to in the 'From' case.
• ixWikiPageFrom (int) - Wiki page number that contains the reference to a case (0 if bug->bug relation).

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(11)) - AutoNumber primary key.
• sCategory (nvarchar(50)) - Name of the category.
• sPlural (nvarchar(50)) - Plural name of the category.
• ixStatusDefault (int) - Foreign key to the Status table, indicating the initial status for new cases in this category.
• fIsScheduleItem (int) - Whether the category is a schedule item.

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(11)) - 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.
• sRepo (nvarchar(255)) - Repository

Table: DiscussGroup

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

Columns

• ixDiscussGroup (int(11)) - 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.
• 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.
• ixGroup (int) - Foreign key to the Groups table (optional).

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(11)) - AutoNumber primary key.
• sTopHTML (ntext) - HTML for top of page.
• sBottomHTML (ntext) - HTML for bottom of page.
• sStylesCSS (ntext) - CSS to add to the discussion page.
• cPixelsLeftSidebar (int) - Left sidebar width in pixels.
• cPixelsMain (int) - Main body width in pixels.

Table: DiscussTopic

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

Columns

• ixDiscussTopic (int(11)) - 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 in UTC that 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.
• sRemoteIP (nvarchar(128)) - The remote user's IP address.
• sRemoteSubNetIP (nvarchar(128)) - The remote user's IP subnet 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 used in training Bayesian sorting for this Discussion Group.

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 in UTC that the e-mail was sent.
• sRemoteIP (nvarchar(128)) - The remote 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: 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: Notification

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

Columns

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

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(11)) - AutoNumber primary key.
• fOpenBugs (nvarchar(4)) - "ON" if the filter includes open bugs, "OFF" if the filter does not include open bugs.
• 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.
• ixPersonResolvedBy (int) - Foreign key to the Person table indicating the Person Resolved By for this filter of -1 for anyone.
• ixPersonClosedBy (int) - Foreign key to the Person table indicating the Person Closed By for this filter of -1 for anyone.
• ixPersonLastEditedBy (int) - Foreign key to the Person table indicating the Person Last Edited By for this filter of -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.
• ixGroup (int) - Foreign key to Group table indicating the Group selected for this Filter or -1 for all groups.
• 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.
• 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.
• 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.
• 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).
• 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.
• dueInNext (int) - Enumeration indicating when the case should be due, or -1 to include all.
• ixCategory (int) - Foreign key to the Category table indicating the Category selected for this filter or -1 for all categories.
• fMissingEstimate (int) - Filters for cases missing estimates.
• fSubscribedBugs (int) - Filters to show only cass to which the user is subscribed.
• fSeenByMe (int) - Filters to show only bugs I have viewed before.
• fGlobal (smallint) - 1 for a global filter, available to all users.
• fGridView (int)
• sSearchFor (ntext) - The search to combine with the filter results.

Table: FixFor

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

Columns

• ixFixFor (int(11)) - AutoNumber primary key.
• sFixFor (nvarchar(255)) - Name of the release.
• dt (datetime) - The planned date in UTC of the release, or Null for (None).
• bDeleted (int) - 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.

Table: Groups

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

Columns

• ixGroup (int(11)) - AutoNumber primary key.
• sName (nvarchar(255)) - Name of the client or department.
• iType (int) - Group type: 1=client 2=dept
• 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(11)) - AutoNumber primary key.
• ixPerson (int) - The person taking this holiday, or 0 for site-wide.
• sHoliday (nvarchar(255)) - Name of the holiday.
• dtHoliday (datetime) - The date and time when the holiday occurs. Timezone-agnostic.
• dtHolidayEnd (datetime) - The date and time when the holiday ends. Timezone-agnostic.

Table: IndexDelta

The change information that does not fit into BugEvents

Columns

• ixIndexDelta (int(11)) - AutoNumber primary key.
• sType (nvarchar(255)) - The type of index delta, eg case
• ix (int) - The index of the item based on the sType case -> ixBug
• fDeleted (int) - 1 if the change was that the item was removed from the database

Table: IndexFile

The files that comprise the full text index

Columns

• ixIndexFile (int(11)) - AutoNumber primary key.
• sPrefix (nvarchar(255)) - The name of the directory
• sFilename (nvarchar(255)) - The name of the file
• sLockKey (nvarchar(255)) - The unique key used to hold a lock
• nLastModifiedLo (int) - The date the file was last modified
• nLastModifiedHi (int) - The date the file was last modified
• cbLengthLo (int) - The length of the file
• cbLengthHi (int) - The length of the file
• ixFirstGeneration (int) - The first generation where this file exists
• ixLastGeneration (int) - The last generation where this file exists
• dtExpires (datetime) - The time after which the lock can be force unlocked

Table: IndexFilePage

The page of a file in the full text index

Columns

• ixIndexFilePage (int(11)) - AutoNumber primary key.
• ixIndexFile (int) - The file the page belongs to
• nPage (int) - The n'th page of the file
• sPage (binary(8000)) - The data for the page

Table: Licenses

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

Columns

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

Table: Mailbox

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

Columns

• ixMailbox (int(11)) - 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.
• sTemplate (ntext) - Message template for replies.
• 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 (int) - 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.
• sReplyMessage (ntext) - Message 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.
• dtLastChecked (datetime) - When the mailbox was last checked in UTC.
• nInterval (int) - How many seconds to wait between mailbox checks.
• fTrialMailbox (smallint) - 1 if this is a special FogBugz trial mailbox

Table: MailQueue

The MailQueue contains messages being sent by FogBugz itself.

Columns

• ixMailQueue (int(11)) - AutoNumber primary key.
• dt (datetime) - The date in UTC that 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: ID

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

Columns

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

Table: TimeInterval

Holds information about time intervals used by developers working on bugs.

Columns

• ixInterval (int(11)) - AutoNumber primary key.
• ixPerson (int) - The person for whom this time interval applies.
• ixBug (int) - The bug that the person was working on.
• dtStart (datetime) - Date in UTC that the interval began.
• dtEnd (datetime) - Date in UTC that the interval ends, or null if it's the current interval.

Table: Person

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

Columns

• ixPerson (int(11)) - AutoNumber primary key.
• sFullName (nvarchar(50)) - Person's name.
• sEmail (nvarchar(255)) - Person's email address.
• sPassword (nvarchar(50)) - Person's password.
• fNotify (int) - 1 if the user wants email notifications, 0 if they do not
• fEscalationReport (int) - 1 if this user should get escalation reports via e-mail.
• 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 (int) - 1 if the user is running in expert mode.
• fAdministrator (int) - 1 if the user is an administrator.
• fDeleted (int) - 1 if the user has been deleted.
• sFrom (nvarchar(255)) - From address to use on e-mail from this user.
• fGridView (int) - 1 if this user views cases in grid view, 0 for list view.
• ixArea (int) - Last area that this user entered a case for.
• ixBugWorkingOn (int) - Bug on which developer is currently working.
• sVersionSeen (nvarchar(255)) - Last version upgrade message seen (for administrators only)
• sContractSeen (nvarchar(255)) - Last support contract message seen (for administratros only)
• sPasswordVersion (nvarchar(255)) - Controls which version of the password library FogBugz uses.
• sPasswordResetCode (nvarchar(255)) - Code to allow password reset via 'Forgotten Password'
• sSnippetKey (nvarchar(2)) - Keystroke used to activate snippets.
• sLDAPUid (nvarchar(50)) - This person's LDAP UID.
• fMostRecentEventFirst (int) - 1=Show the most recent BugEvents first when viewing a bug.
• fCommunity (smallint) - 1 if this is a community (discussion and wiki only) user.
• fConfirmed (smallint) - (Community users only) 1 if this user has confirmed email by logging in.
• fVirtual (smallint) - 1 if this is a virtual (cannot log in) user.
• sHomepage (nvarchar(255)) - User's homepage (optional)
• dtRegistered (datetime) - Date in UTC that the user first registered
• FILTER_fOpenBugs (nvarchar(4))
• FILTER_fClosedBugs (nvarchar(4))
• FILTER_ixProject (int)
• FILTER_ixArea (int)
• FILTER_ixPersonOpenedBy (int)
• FILTER_ixPersonAssignedTo (int)
• FILTER_ixPersonResolvedBy (int)
• FILTER_ixPersonClosedBy (int)
• FILTER_ixPersonLastEditedBy (int)
• FILTER_ixStatus (int)
• FILTER_ixPriority (int)
• FILTER_maxrecords (int)
• FILTER_sort1 (int)
• FILTER_sort2 (int)
• FILTER_sort3 (int)
• FILTER_ixFixFor (int)
• FILTER_openInLast (int)
• FILTER_priorityRange (int)
• FILTER_sentBy (nvarchar(255))
• FILTER_sComputer (nvarchar(255))
• FILTER_sVersion (nvarchar(255))
• FILTER_resolvedInLast (int)
• FILTER_closedInLast (int)
• FILTER_dueInNext (int)
• FILTER_ixCategory (int)
• FILTER_fMissingEstimate (int)
• FILTER_fSubscribedBugs (int)
• FILTER_fSeenByMe (int)
• FILTER_ixGroup (int)
• FILTER_sFilterName (nvarchar(40)) - The name of the last filter the person loaded
• FILTER_sSearchFor (ntext)
• sLocale (nvarchar(11)) - The locale code for the date/number language (i.e., "en-us")
• sLanguage (nvarchar(11)) - The locale code for the UI language (i.e., "en-us")
• sTimeZoneKey (nvarchar(255)) - A key that defines the time zone setting for this person's account (i.e., "Eastern Standard Time"). The values for this setting are operating system dependent.
• dtLastDailyTask (datetime) - Date and time in UTC that the last daily task was run for this person.
• dtLastScheduleMaint (datetime) - The date ScheduleMaint was last run for this person. (in UTC)

Table: Prediction

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

Columns

• ixPrediction (int(11)) - 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(11)) - AutoNumber primary key.
• sPriority (nvarchar(255)) - Text description of the priority
• fDefault (smallint) - 1 if this is the default priority for new cases, 0 otherwise.

Table: Project

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

Columns

• ixProject (int(11)) - AutoNumber primary key.
• sProject (nvarchar(128)) - The name of the project.
• ixPersonOwner (int) - Foreign key to the Person table indicating the primary contact for this project.
• fDeleted (int) - 0 if this project is active, -1 if it has been deleted.
• fAllowPublicSubmit (int) - True if this project allows public submissions, False if it does not.
• 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: ProjectView

The ProjectView remembers each user's last view of the project status page.

Columns

• ixProjectView (int(11)) - AutoNumber primary key.
• ixPerson (int) - The person who viewed.
• ixProject (int) - The Project viewed.
• ixFixFor (int) - The FixFor the person last viewed for this project.
• ixPriority (int) - The Priority the person last viewed for this project.
• nGraphType (int) - The GraphType the person last viewed for this project.

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

The Shipdate table contains low, middle, and high confidence shipdates for each Priority for each assignable FixFor, calculated daily.

Columns

• ixShipDate (int(11)) - AutoNumber primary key.
• ixFixFor (int) - Foreign key to the FixFor table indicating the FixFor that this ShipDate applies to.
• ixPriority (int) - Foreign key to the Priority table indicating the Priority that this ShipDate applies to.
• dtCalc (datetime) - The date in UTC on which this ShipDate was calculated
• dtLow (datetime) - Low confidence ship date in UTC
• dtMid (datetime) - Middle confidence ship date in UTC
• dtHigh (datetime) - High confidence ship date in UTC
• dtOfficial (datetime) - Official ship date in UTC
• fCompleted (int) - There are no more tasks for this fixfor with open time against them.

Table: Snippet

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

Columns

• ixSnippet (int(11)) - 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.
• s (ntext) - The text of the snippet.
• sComment (nvarchar(80)) - Comment describing the snippet.
• fDeleted (smallint) - 1 if the snippet has been deleted.

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

The SearchLog table contains information about searches

Columns

• ixSearchLog (int(11)) - AutoNumber primary key.
• ixPerson (int) - The id of the person who did the search
• sSearchFor (nvarchar(255)) - The search string that was executed
• nLogType (int) - 0 to log the query, 1 for happy with results, 2 for unhappy with results

Table: SorterToken

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

Columns

• ixToken (int(11)) - AutoNumber primary key.
• sToken (nvarchar(40))
• 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.

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(11)) - AutoNumber primary key.
• sStatus (nvarchar(50)) - 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.
• fWorkDone (int) - 1 indicates that for the purposes of EBS, bugs with this status code should be considered as historical data for completed bugs.

Table: Subscriptions

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

Columns

• ixSubscription (int(11)) - AutoNumber primary key.
• ixPerson (int) - Foreign key to the Person table.
• ixBug (int) - Foreign key to the Bug table.
• ixWikiPage (int) - Foreign key to the WikiPage 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(11)) - AutoNumber primary key.
• fbToken (nvarchar(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(11)) - 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

• ixPerson (int) - The person who owns this calendar.
• fSunday (int) - Whether there is work done on Sunday.
• fMonday (int) - Whether there is work done on Monday.
• fTuesday (int) - Whether there is work done on Tuesday.
• fWednesday (int) - Whether there is work done on Wednesday.
• fThursday (int) - Whether there is work done on Thursday.
• fFriday (int) - Whether there is work done on Friday.
• fSaturday (int) - Whether there is work done on Saturday.
• nWorkdayStarts (float) - What time work begins. (in UTC hours)
• nWorkdayEnds (float) - What time work ends. (in UTC hours)
• nHoursPerDay (float) - How many hours are worked per day.
• fHasLunch (int) - Whether the employee takes time off for lunch.
• nLunchStarts (float) - What time lunch starts. (in UTC hours)
• hrsLunchLength (float) - Length of lunch, in hours.
• dtLastUpdated (datetime) - The date the schedule was last updated so that daylight savings time can be accounted for. (in UTC)
• fAutoClock (int) - Whether or not FogBugz should automatically start/stop the clock for this schedule.

Table: GridColumn

The GridColumn table contains one row for each grid view column associated with either a Person or a Filter.

Columns

• ixPerson (int) - Foreign key to the Person table, or -1 if this grid column is associated with a Filter.
• ixFilter (int) - Foreign key to the Filter table, or -1 if this grid column is associated with a Person.
• iType (int) - Type of grid column: ... 2 = case, 3 = title, ... see GridColumn.asp for full list.
• iOrder (int) - Order in which the grid column is displayed (0 = leftmost, 1 = second-to-left, etc).
• pxWidth (int) - Width, in pixels, of grid column.
• fVisible (int) - True if grid column is visible for the associated Filter or Person.

Table: FBLanguage

The Language table contians one row for each language. Every language might not be completely supported, but it will be listed for completeness and locale selection.

Columns

• ixLanguage (int(11)) - AutoNumber primary key.
• sName (nvarchar(50)) - Unique name for a language (i.e., "English US").
• sLCID (nvarchar(10)) - The Windows LCID as a string
• sLangFile (nvarchar(20)) - The name of the language file for localized UI.
• sLocaleCode (nvarchar(11)) - The locale code for the language (i.e., "en-us")
• sGenericLocaleCode (nvarchar(5)) - The generic locale code (i.e., "en"), this code is used when the accept-language header specifies only the language.

Table: Revision

The Revision table contains a single row for each revision to either a WikiPage or Template in FogBugz.

Columns

• ixRevision (int(11)) - AutoNumber primary key.
• ixWikiPage (int) - Foreign key to the WikiPage table (null if Template revision).
• ixTemplate (int) - Foreign key to the Template table (null if WikiPage revision).
• ixPerson (int) - Foreign key to the Person table representing the revision's editor (may be null if externally edited).
• sRemoteIP (nvarchar(128)) - The remote user's IP address.
• nRevision (int) - n'th revision of WikiPage or Template.
• sTitle (nvarchar(128)) - The revision's WikiPage headline or Template's title.
• sBody (ntext) - The revision's body. This can contain either the full body, or (more likely), a diff from the previous revision.
• sComment (nvarchar(255)) - The editor's revision comment.
• fDiff (smallint) - True if this revision's body stores a diff from the previous version, false if it stores the entire document body.
• dt (datetime) - Date in UTC that the revision was made.

Table: WikiLink

The WikiLink table contains a single row for each link from one Wiki page to another.

Columns

• ixWikiLink (int(11)) - AutoNumber primary key.
• ixWikiPageFrom (int) - Foreign key to the WikiPage table (link source).
• ixWikiPageTo (int) - Foreign key to the WikiPage table (link destination).

Table: Template

The Template table contains a single row for each wiki template in FogBugz.

Columns

• ixTemplate (int(11)) - AutoNumber primary key.
• ixRevision (int) - Foreign key to the Revision table.
• sTemplate (nvarchar(128)) - The unique name for this template.
• sBody (ntext) - The body of this template.
• fDeleted (smallint) - True if this template has been deleted.

Table: WikiPage

The WikiPage table contains a single row for each wiki page in FogBugz.

Columns

• ixWikiPage (int(11)) - AutoNumber primary key.
• ixRevision (int) - Foreign key to the Revision table.
• ixWiki (int) - Foreign key to the Wiki table indicating which Wiki this page belongs to.
• sHeadline (nvarchar(128)) - The unique headline/title for this wiki page.
• sBody (ntext) - The body of this wiki page.

Table: Wiki

The Wiki table contains a single row for each wiki in FogBugz.

Columns

• ixWiki (int(11)) - AutoNumber primary key.
• sWiki (nvarchar(128)) - The unique name of this wiki page.
• sTagLineHTML (ntext) - HTML for tagline.
• ixWikiPageRoot (int) - Foreign key to the WikiPage table indicating the root wiki page.
• ixTemplate (int) - Foreign key to the Template table indicating the template to be used for this wiki.
• ixDictionary (int) - Foreign key to the Dictionary table indicating the spellchecking dictionary to be used for this wiki.
• fDeleted (smallint) - True if this Wiki has been deleted.
• ixGroup (int) - Foreign key to the Groups table (optional).

Table: Dictionary

The Dictionary table contains one row for each language that is spellcheckable.

Columns

• ixPKDictionary (int(11)) - AutoNumber primary key.
• ixDictionary (int) - Index of Dictionary table.
• sDictionary (nvarchar(128)) - The name of this dictionary.
• sDescription (nvarchar(128)) - The description of this dictionary.

Table: Word

The Word table contains a row for each word in each language.

Columns

• ixWord (int(11)) - AutoNumber primary key.
• sWord (nvarchar(255)) - The word itself
• ixDictionary (int) - Foreign key to the Dictionary table indicating which dictionary this word is in.
• ixPerson (int) - Foreign key to the Person table (creator of added word, null if word exists by default).
• sMetaphonePrimary (nvarchar(255)) - The word's primary Double Metaphone
• sMetaphoneSecondary (nvarchar(255)) - The word's secondary Double Metaphone, or same as sMetaphonePrimary if none

Table: IgnoredWord

The IgnoredWord table contains a row for each ignored word on a Wiki Page.

Columns

• ixIgnoredWord (int(11)) - AutoNumber primary key.
• sIgnoredWord (nvarchar(255)) - The ignored word
• ixWikiPage (int) - Foreign key to the WikiPage table indicating on which Wiki Page this word is ignored

Table: BugView

BugView tracks the most recent view of each bug by each user.

Columns

• ixBugView (int(11)) - AutoNumber primary key.
• ixPerson (int) - Foreign key to the Person table for the person who viewed the bug.
• ixBug (int) - Foreign key to the Bug table for the bug viewed.
• ixBugEvent (int) - Foreign key to the BugEvent table for the ixBugEventLatest on the last viewing.
• dt (datetime) - The date in UTC of the most recent viewing.

Table: WikiPageView

WikiPageView tracks the most recent view of each wiki page by each user.

Columns

• ixWikiPageView (int(11)) - AutoNumber primary key.
• ixPerson (int) - Foreign key to the Person table for the person who viewed the wiki page.
• ixWikiPage (int) - Foreign key to the WikiPage table for the wiki page viewed.
• ixRevision (int) - Foreign key to the Revision table for the ixRevision on the last viewing.
• dt (datetime) - The date in UTC of the most recent viewing.

Table: DiscussTopicView

DiscussTopicView tracks the most recent view of each discussion topic by each user.

Columns

• ixDiscussTopicView (int(11)) - AutoNumber primary key.
• ixPerson (int) - Foreign key to the Person table for the person who viewed the wiki page.
• ixDiscussTopicFirst (int) - Foreign key to the DiscussTopic table for the first discuss topic in the thread.
• ixDiscussTopicLast (int) - Foreign key to the DiscussTopic table for the last discuss topic on the last viewing.
• dt (datetime) - The date in UTC of the most recent viewing.

Table: DocList

DocList contains lists of cases for each person

Columns

• ixDocList (int(11)) - AutoNumber primary key.
• ixPerson (int) - Foreign key to the Person table for the person who owns this list.
• sName (nvarchar(40)) - The name of the list.
• fFavorites (int) - Is this a starred item?

Table: DocListItem

DocListItem maps doc list items to DocLists

Columns

• ixDocListItem (int(11)) - AutoNumber primary key.
• ixDocList (int) - Foreign key to the DocList table for which DocList this DocListItem belongs to.
• sType (nvarchar(40)) - One of either "Bug", "WikiPage", or "DiscussTopic".
• ixItem (int) - Foreign key to the WikiPage, Bug, or DiscussTopic tables.
• nPos (int) - This item's position in the doc list.

Table: Draft

The Draft table contains a single row for each draft in FogBugz.

Columns

• ixDraft (int(11)) - AutoNumber primary key.
• ixPerson (int) - Foreign key to the Person table.
• ixItem (int) - Foreign key to the WikiPage, Bug, or DiscussTopic tables.
• nRevision (int) - If sType = "WikiPage", this is the latest nRevision at which the Draft was saved
• sType (nvarchar(128)) - One of either "Bug", "WikiPage", or "DiscussTopic".
• sDraft (ntext) - The body of the draft.
• dt (datetime) - The date in UTC of draft creation.

Table: FragmentCache

The FragmentCache table contains a single row for each fragment of cached HTML per user.

Columns

• ixPerson (int) - Foreign key to the Person table.
• sKey (nvarchar(128)) - Key to identify fragment
• sParams (nvarchar(128)) - Parameters to identify fragment
• sValue (ntext) - Cached HTML fragment.
• dt (datetime) - The date this fragment was cached.

Table: FragmentDependency

The FragmentDependency table contains one row for each association between a FragmentCache and a cache dependency.

Columns

• sKey (nvarchar(128)) - Key to identify fragments in FragmentCache table.
• sDependency (nvarchar(128)) - String dependency. CFragmentCache uses dependencies to clear dirtied pieces of FragmentCache.

Table: FBLock

Holds unique locks.

Columns

• ixFBLock (int(11)) - AutoNumber primary key.
• sLock (nvarchar(255)) - The name of the lock.
• sKey (nvarchar(255)) - The unique key for the lock (generated). The empty string means not locked.
• dtExpires (datetime) - The date when the lock will be considered stale and should be broken.
• fBrokeLock (int) - Set to 1 if this lock had to break the previous lock to get the lock.

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.