TracQueryPlugin

This plugin allows you to query Trac for tickets and other data and format the returned results. This plugin was derived from TWiki:Plugins.BugzillaQueryPlugin, but as the two databases differ significantly, so do the details of these two plugins.

Syntax Rules

%TRAC%

%TRAC% is the basic query into the trac database. A %TRAC% query applies to the whole database.

Parameter	Description	Default value
(empty)	Comma separated list of table or tables to be queried. If one table is given, this table is retrieved. If more than one table is given, the additional tables are joined to the first table on their key (supported only for milestone, component, and version tables).	ticket
format	Optional. Determines which attributes are shown in the result and how they are formatted. See below.
newline	Used in format. Defines the text to be used when a newline character is encountered in a retrieved value.	%BR%
separator	Used in format. Defines the text to be used to separate rows returned by the query	newline
(any other)	Restricts the rows returned. The parameter must correspond to the name of an attribute of the selected table. Restrictions are written as <attribute>="<value>" or <attribute>="<value1>|<value2>|...". The latter functions as an OR. Custom fields and attributes in joined tables cannot be restricted.

By default, the 'ticket' table is queried, but the default parameter of the %TRAC% tag can be used to select any table in the trac data base. The other parameters of the %TRAC% tag refer to the columns in the selected table and can be used to narrow the data retrieved.

Examples:

• %TRAC{"ticket"}%
• %TRAC{status = "new, assigned"}%
• %TRAC{"component" owner="TWikiGuest"}%
• %TRAC{"ticket, milestone"}%

The first query above just retrieves all rows in the ticket table. The second query retrieves those rows in the ticket table, where the status column is either 'new' or 'assigned'. The third query searches the component table for rows where the owner is 'TWikiGuest'. The final query retrieves all rows in the ticket table, with the attributes of the milestone table joined and accessible for output.

The trac data base scheme is described at trac db.

The output is formatted as specified in the FORMAT setting. The parameter format can be used to override that setting for a given query.

Syntax and variables in format attribute

The format attribute determines the rendering of a single row returned by the query. Columns in the table are referred to with the same naming conventions as for attributes, preceded by the '$' symbol. Custom fields defined in the trac database can be referenced by their name but need to be enabled through settings (see below). Attributes in joined tables are referenced by prefixing them with the name of the table, separated by a dot.

Examples:

• %TRAC{ format="| $id | $status | $owner |" }%
• %TRAC{ "ticket, milestone" format="| $id | $status | $milestone.due |" }%
• %TRAC{ format="   * Bug $id is in status $status and was assigned to $owner" }%

%TRACSUM%, %TRACMIN%, %TRACMAX%, %TRACCOUNT%, %TRACAVG%

Applies an aggregate function to a selected field of the ticket table. Supported are SUM (summation), MAX (maximum), MIN (minimum), COUNT (numbmer of matched rows), or AVG (average) of the values of the selected field across the matched rows.

Parameter	Description	Default value
(empty)	Field the aggregate function is to be applied to. Must be a field in the ticket table.
<field>="<value>"	Restriction on rows. Only one such parameter is allowed.

The limit on restrictions will be removed in a later release.

Examples:

• %TRACSUM{ "totalhours" milestone="Goal One" }%

Nested Queries

Queries can be nested. For example, retrieve some rows and compute the sum of a field in a related table. The idea is to build the nested search string using a formatted search in the first search. Note that quotes must be escaped by preceding them with slash, the nested query must be protected from execution by using $percnt in the tag, and format fields can be prevented from early evaluation by substituting $dollar when needed.

Examples:

%TRAC{ "milestone" format = "| $name | $percntTRACSUM{ \"totalhours\" milestone=\"$name\" }$percnt | $percntTRACSUM{ \"estimatedhours\" milestone=\"$name\" }$percnt | $due |" }%

Retrieves the milestone table, and for each milestone, finds the sum of the totalhours and estimatedhours attribute, respectively (this custom field is applied by the trac estimation plugin).

TracQueryPlugin Settings

Plugin settings are stored as preferences variables. To reference a plugin setting write %TRACQUERYPLUGIN_<setting>%, e.g., %TRACQUERYPLUGIN_SHORTDESCRIPTION%

• Set SHORTDESCRIPTION = Query and display data from Trac databases
• Set FORMAT = | $id | $severity | $priority | $status | $resolution | $reporter | $component | $summary |
  • The default format for queries. Can be overridden by the format attribute for a specific query.
• Set CUSTOM = totalhours, estimatedhours
  • A comma-separated list of field names of trac custom ticket fields. If these are given, and the custom fields are defined, the query can also refer to the custom fields. Note that this query implies a number of join operations, as the custom fields are stored in a separate table.
• Set DEBUG = 0

Plugin Installation Instructions

Note: You do not need to install anything on the browser to use this plugin. The following instructions are for the administrator who installs the Plugin on the TWiki server.

• Download the ZIP file from the Plugin web (see below)
• Unzip TracQueryPlugin.zip in your twiki installation directory. Content:

  File:	Description:
  lib/TWiki/Plugins/TracQueryPlugin.pm	Plugin Perl module.
  data/TWiki/TracQueryPlugin.txt	Plugin topic.
  lib/TWiki/Plugins/TracQueryPlugin/Config.spec	Configuration specification file.

• Configure and enable the Plugin:
  • TWiki 4.0 and up: Run the configure script to enable the Plugin
  • Configure the settings for the trac database. Select the type of data base (SQLite or MySQL), and enter the appropriate settings. If your data base requires that a user is defined for access, configure TRAC_USER and TRAC_PASSWD. It might be best to give this user only read-only permissions, via the trac permission settings.
• Change the Plugin settings above, if required.

Plugin Info

Plugin Author:	TWiki:Main.ThomasWeigert
Copyright:	© 2007, TWiki:Main.ThomasWeigert
License:	GPL (GNU General Public License)
Plugin Version:	1.02
Change History:
8 Aug 2008	Added aggregate functions. Added joins to ticket table. Refactored queries so that this plugin could be leveraged from within other plugins.
TWiki Dependency:	$TWiki::Plugins::VERSION 1.2
CPAN Dependencies:	Name Version Description DBI >=1.35 Required DBD::SQLite >=1.14 Required
Other Dependencies:	none
Perl Version:	5.0
Benchmarks:	GoodStyle nn%, FormattedSearch nn%, TracQueryPlugin nn%
Plugin Home:	http://TWiki.org/cgi-bin/view/Plugins/TracQueryPlugin
Feedback:	http://TWiki.org/cgi-bin/view/Plugins/TracQueryPluginDev
Appraisal:	http://TWiki.org/cgi-bin/view/Plugins/TracQueryPluginAppraisal

Related Topics: TWiki:Plugins.BugzillaQueryPlugin

-- TWiki:Main.ThomasWeigert - 02 Aug 2008