We rather like our TRAC system. It’s simple, loads really fast, and doesn’t confuse our clients (too much).

Since migrating it to our new server, we’ve been able to play around with the functionality a little more. First, we looked for a way to have a more integrated login feature - something a bit nicer for the clients than the ugly old “challenge/response” box.We found the AccountManager pluginon Trac Hacks, which did the job extremely well.(Here’s how the login page looks now).

Next, we discovered there is a MySQL DB connector. Heathens, I hear the Python community cry! Well, that’s as maybe, but MySQL Gives us a great deal of flexibility for managing our TRAC data - now we’re not limited to TRAC’s slightly under performing reporting interface, nor SQLite’s rather spartan sql dialect.

Possibly the most useful aspect of the MySQL integration is that we can now aggregate tickets from across all of our TRAC projects. We’ve thus created a sort of “Meta” TRAC which acts as a “To do” list for all Loopo staff to view in the mornings. This required a bit of a hack to allow the ticket link to display HTML links - since TRAC normally converts a ticket ID into a relative link within the current project, and also some cunning sql to link tables across the projects. Here’s our report sql (tweezed of its project names, we don’t want to violate our NDAs now do we!):


select url, project, priority, severity, milestone, version, component, summary, date_created, last_edited, priority_enum as _p, severity_enum as _s from trac_project1.vOpenTickets where owner = '$USER'
union
select url, project, priority, severity, milestone, version, component, summary, date_created, last_edited, priority_enum as _p, severity_enum as _s from trac_project2.vOpenTickets where owner = '$USER'
union
(... and so on)


Note the fully qualified object names - trac_project1 is the database, and vOpenTickets is a view in that database. So, what’s in vOpenTickets, I hear you cry? Well, nothing too esoteric. Here it is:

create view vOpenTickets

as

select
id as ticket
, n.value as project
, concat(s.value, 'ticket/',id) as url
, priority
, p.value as priority_enum
, severity
, v.value as severity_enum
, milestone
, version
, summary
, component
, owner
, date_format(from_unixtime(time),'%d/%m/%y') as date_created
, date_format(from_unixtime(changetime),'%d/%m/%y %H:%i') as last_edited
from
ticket t left join

enum p on
t.priority = p.name and
p.type = 'priority' left join

enum v on
t.priority = v.name and
v.type = 'severity' cross join

system s cross join

system n

where
status in ('new','assigned','reopened') and
s.name = 'link' and
n.name = 'project'

You’ll notice a reference to a system value “project”. This isn’t actually created by the TRAC installation - we manually added it as a key to the system table, so the aggregated report can show the project name. We could of course have had all this sql in the TRAC report, and not bothered to create views at all, but that would have created a beast of an sql statement, not easy to maintain whatsoever.