Generic Database Access

FogBugz Plugin API > Generic Database Access

This article will discuss how FogBugz plugins can:

Perform SELECT queries on Fogbugz core application tables.

Perform SELECT, INSERT, UPDATE, DELETE queries on plugin tables generated by IPluginDatabase (example).

The CQuery object

The CDatabaseApi can be used to acquire instances of any of 5 FogBugz query classes (CUpdateQuery, CDeleteQuery, CSelectQuery, CInsertQuery, CInsertSelectQuery), all of which inherit from CQuery. Each of these classes allow you to build a platform-agnostic query of a specific type:

CQuery
- CWhereQuery
  - CUpdateQuery
  - CDeleteQuery
  - CSelectQuery
- CInsertQuery
- CInsertSelectQuery

Queries are built by specifying a set of named parameters and a set of SQL clauses. Clauses and named parameters can be added in any order, allowing developers to implement a series of functions that conditionally add clauses to a "base query."

Special SQL Requirements

Because queries generated with CQuery must work on all FogBugz-supported database platforms, SQL syntax that is inconsistent between platforms is not currently supported. This means that "LIKE" and "IIF" clauses are disallowed. For a list of all allowed built-in SQL functions, see Allowed Built-In SQL Functions.
To prevent column name abiguity with joins, All column names must be fully-qualified in all SQL you write (selects, wheres, order bys, etc). For example:

SELECT ixBugEvent -- query.AddSelect("ixBugEvent")
FROM BugEvent
WHERE ixBugEvent = 2 -- query.AddWhere("ixBugEvent = 2")

needs to become:

SELECT BugEvent.IxBugEvent -- query.AddSelect("BugEvent.ixBugEvent")
FROM BugEvent
WHERE BugEvent.IxBugEvent = 2 -- query.AddWhere("BugEvent.ixBugEvent = 2")

Plugin Table Prefixes

To avoid name collisions between plugins in the FogBugz database, plugin table names (specified by the IPluginDatabase interface) are automatically prefixed by FogBugz. The prefixed table name can be accessed using the PluginTableName() method of CDatabaseApi (an instance of which is always available in CPluginApi.Database). You need only pass it the prefix-free table name:

string kiwiTableName = api.Database.PluginTableName("Kiwi"));

SELECT Query Example

This example uses CSelectQuery to get all the cases assigned to a particular user, as well as perform a join with the "Person" table in order to get data regarding the user:

//"api" is an instance of CPluginApi
CSelectQuery query = api.Database.NewSelectQuery("Bug");

/* ignorepermissions is required for queries against FogBugz tables */
query.IgnorePermissions = true;

/* all SQL must use fully qualified column names */
query.AddSelect("Bug.ixBug, Bug.ixPersonAssignedTo");
query.AddLeftJoin("Person", "Bug.ixPersonAssignedTo = Person.ixPerson");
query.AddSelect("Person.sFullName, Person.sEmail, Person.sPhone");
query.AddWhere("Person.ixPerson = @ixPerson");
query.SetParamInt("ixPerson", 3);

/* all SQL must use fully qualified column names */
query.AddOrderBy("Person.sFullName");
query.Limit = 50;
int count = query.Count();

System.Data.DataSet dataset = query.GetDataSet();

Whenever a plugin queries a FogBugz core application table (e.g. Bug or Person), IgnorePermissions must be set to true or a FogBugzSecurityException will be thrown (See Database Query Security for more information). Note also that all order by clauses must use fully-qualified column names (i.e. include the table name).

UPDATE, DELETE, and INSERT Example

Because a plugin cannot perform UPDATE, DELETE, and INSERT operations on a core FogBugz table, we'll perfom the operations on a plugin-specified "Kiwi" table.

To start, we change a particular kiwi's name to "David Fullerton:"

//To get the name of a plugin table, you need to use api.Database.PluginTableName()
CUpdateQuery queryUpdate = api.Database.NewUpdateQuery(api.Database.PluginTableName("Kiwi"));
queryUpdate.UpdateString("sKiwiName", "David Fullerton");
queryUpdate.AddWhere("ixKiwi = 3");
queryUpdate.Execute();

Then, we decide to delete that kiwi:

CDeleteQuery queryDelete = api.Database.NewDeleteQuery(api.Database.PluginTableName("Kiwi"));
queryDelete.AddWhere("ixKiwi = 3");
queryDelete.Execute();

Finally, we have a change of heart and decide to add back a new kiwi named "David Fullerton:"

CInsertQuery queryInsert = api.Database.NewInsertQuery(api.Database.PluginTableName("Kiwi"));
queryInsert.InsertString("sKiwiName", "David Fullerton");
int ixKiwiInserted = queryInsert.Execute();

Note that the Execute method on a CInsertQuery instance returns the primary key of the row inserted.

It should also be noted that for columns which allow NULL values, to Insert or Update the column to a NULL value you need to simply set UpdateValue([the col name], "NULL") or InsertValue([the col name], "NULL").

Entity Queries

The plugin api also exposes methods returning an entity-specific select query object. These objects return an array of the specified FogBugz entity (rather than .NET DataSet) as a convenience, to enforce permissions and to allow modification of the entities (direct insert, update and delete queries are not allowed against non-plugin tables. See FogBugz Permissions in Plugins for more information). Note that when QueryField is used, one must set IgnorePermissions to true, as we did with generic queries above (See Database Query Security for more information).

Here's an example SELECT query on the Bug table:

CBugQuery query = api.Bug.NewBugQuery();
query.AddLeftJoin("Category", "Bug.ixCategory = Category.ixCategory");
/* entity queries involving joins require fully-qualified column names */
query.AddSelect("Category.fDeleted AS fDeletedCategory");
CBug[] rgBug = query.List();
StringBuilder sb = new StringBuilder();
foreach (CBug bug in rgBug)
{
    /* IgnorePermissions required for queryfield access */
    bug.IgnorePermissions = true;
    sb.Append(bug.ixBug + ": ");
    // we use bug.QueryField to access fields in the SELECT that are not a normal part of the object
    sb.Append(Convert.ToInt32(bug.QueryField("fDeletedCategory")) == 0 ? "OK" : "ERROR");
    bug.IgnorePermissions = false;
    sb.Append("<br/>");
}

In this example, we use the plugin's ability to edit cases to add the same BugEvent to all our selected CBug objects. For more on editing cases, see How To Edit Cases. This example overrides the default behavior of only returning cases that are readable by the current user by setting ExcludeUnreadable to false.

// Use .NET reflection to access the current plugin's assembly attributes
FogCreek.Plugins.PluginAttributes pluginAttributes =
    new FogCreek.Plugins.PluginAttributes(
        System.Reflection.Assembly.GetExecutingAssembly()
    );
string sPluginName = pluginAttributes.sName.ToString();
CBugQuery query = api.Bug.NewBugQuery();
query.AddLeftJoin("Category", "Bug.ixCategory = Category.ixCategory");

// Get all the cases with category 'Bug'
query.AddWhere("Category.sCategory = 'Bug'");
CBug[] rgBug = query.List();
foreach (CBug bug in rgBug)
{
    bug.IgnorePermissions = true;
    bug.Commit(string.Format("Plugin '{0}' wuz here", sPluginName));
    bug.IgnorePermissions = false;
}

Note that for entities which can be deleted (have an fDeleted column), their entity queries have an ExcludeDeleted property that defaults to true. To return all objects regardless of fDeleted status, set ExcludeDeleted to false. The classes with this property are:

CAreaQuery
CCategoryQuery
CFixForQuery
CPersonQuery
CProjectQuery
CStatusQuery
CTemplateQuery
CTimeIntervalQuery
CWikiQuery
CWorkflowQuery

Wiki