Dirt Readme

Dirt is a reporting frontend to Best Practical Request Tracker. It was written to facilitate my work at Directi, who kindly let me work on it during regular working hours.

Features

• An arbitrary number of dashboards
• A wiki for documentation
• Tabular reports
• Prettier than RT (IMO)
• … more to come (see TODO.textile)

Installation

Installation is still pretty clunky:

• Clone the source
• Install Ruby 1.9.3 if you don’t already have it. Using rvm for this is highly recommended.
• Run bundle install to fetch dependencies. Using rvm’s gemsets feature to isolate these dependencies is a good idea.
• Copy config/database.yml.sample to config/database.yml and edit. Note that there are two database configurations:
  • rt : Your existing Request Tracker database. With the current featureset this can be read-only access. This will change with later releases.
  • dirt : A new empty database that you have created for dirt.
• Run RACK_ENV='production' rake db:migrate to create the dirt schema. Ensure the RACK_ENV environment has been defined in database.yml.
• Run cd db; ruby create_views.rb < mapping.json > create-views.sql
• Review create-views.sql — TL;DR It creates a few views so we have a schema that is ORM friendly and to make report queries more intuitive.
• Run create-views.sql against your RT database. Consult your RDBMS documentation to find out how to do this. Note: create-views.sql uses MySQL’s dialect of SQL.
• Run: RACK_ENV=<env> ruby application.rb. Ruby rack integration is currently broken.

Using Dirt

• Navigate to http://your.host.name
• Add a project
• Use textile syntax to add text content to the page.

Table Macro

• Add a query using the following syntax:

<~
{
  "type": "table",
  "sql":"SELECT COUNT(id) AS Count, Owner AS Name FROM expanded_tickets WHERE Queue = 'linux-hosting' AND Status IN('new', 'open', 'stalled') GROUP BY Owner",
  "caption": "Ticket Ownership"
}
~>

Any arbitrary SQL statement querying the ‘expanded_tickets’ view can be used to report on tickets.

Aging Macro

Example:

<~
{
  "type": "aging",
  "queues":["linux-hosting", "linux-hosting-issues"],
  "caption": "Ticket Aging"
}
~>

Note that queues must be passed an Array, even if it is a single item array.

Simple Taskboard Macro

Example:

<~
{
  "type":"simple_task_board",
  "caption":"Taskboard",
  "group_by":"Status",
  "group_sequence":["new","open","stalled"],
  "ticket_selector":"Queue = 'linux-hosting'",
  "resolved_after":"1 week ago"
}
~>

SQL Interpolation

In most places where you provide an complete or partial SQL statement as a parameter to a macro you can use the following macros to simplify things. Note that the macros are case sensitive.

%DATE()

SQL Statements typically expect static dates:

SELECT * FROM expanded_tickets WHERE LastUpdated > '2012-08-15'

But for a dashboard you’ll typically need a relative date. To answer the question, “show me a list of tickets updated in the last week” you can do this:

SELECT * FROM expanded_tickets WHERE LastUpdated > %DATE(last week)

You can write stuff like:

• last week
• yesterday
• last week thursday
• 4 o’clock last monday

Dirt uses the Chronic gem to parse these description. See Chronic’s examples for a complete listing.

%AVG_DAYS_SINCE()

Calculate the average number of days since a date field. Example:

SELECT Owner, %AVG_DAYS_SINCE(Created) FROM expanded_tickets GROUP BY Owner

to tabulate the average age of tickets per user.

Bugs

1. When editing a new page, clicking ‘Cancel’ will return to the edit page.
2. Ruby Rack support is not working
3. Project Tabs aren’t working well

TODO List

See TODO.textile

Contributing

Standard way: fork, modify and send pull request. User visible changes must have documentation patches.

License

Dirt – A reporting frontend to Best Practical Request Tracker

Copyright © 2012 Biju Philip Chacko

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.