Trac reports

As almost everybody knows, Trac is an enhanced wiki and issue tracking system for software development projects. Trac uses a minimalistic approach to web-based software project management.

The better part of it is that minimalistic approach. Is very easy getting started with Trac, and achieving great things with its help.

One of the features it has is the possibility to write reports with a slightly modified SQL syntax that let us to get views of the tickets, with an specific layout or style. I never needed more reports than those that trac brings us as examples, so I’m not an expert about them.

But today I faced an ugly problem. The {6} All Tickets By Milestone (Including closed) report doesn’t order the milestones by due date. So I modified it for ordering that, and this is the result:

SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE
        (CASE owner WHEN $USER THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status,
   resolution,version, t.type AS type, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  LEFT JOIN milestone m ON m.name = t.milestone
  ORDER BY (milestone IS NULL), m.due, (status = 'closed'),
        (CASE status WHEN 'closed' THEN modified ELSE (-1)*p.value END) DESC