Improving Trac’s Tickets By Milestone Report

I entered a ton of tickets & milestones into a Trac installation today and when I was done the Active Tickets report was a mess. Tickets by Milestone was better, but still far from perfect.  Time for report customization. Google helped, and so did the #trac IRC channel. If you’re lazy & want to just jump to the solution, do it.

Here’s what I was looking for in my report:

• Group tickets by Milestone
• Order milestones by due date, (soonest first)
• If a milestone had no due date, put it at the end of the report, (if it’s important it’ll have a due date set, otherwise it’s a “backlog” item that hasn’t been prioritized yet.
• Display the due dates with the milestone names.

To get started, go to the Tickets by Milestone report that’s in Trac by default and click the “Copy Report” button, you’ll get a copy of Tickets by Milestone to play with. Click the Edit Report button and we’ll update the SQL to get the report we want. Grouping by Milestone is already done in this query, so we’ll start with ordering by milestone due date and putting milestones without a due date at the end of the report.

Order by Milestone Due Date

To order by date we need to join the milestone table. Add to the line after FROM ticket t:

LEFT JOIN milestone ms ON ms.name = t.milestone

Then to the beginning of the ORDER BY statement add (ms.due > 0) Desc,ms.due, so the ORDER BY is now:

ORDER BY (ms.due > 0) Desc,ms.due, (milestone IS NULL),milestone, CAST(p.value AS integer), t.type, time

The (ms.due > 0) Desc part makes milestones that have a due date come first, then ms.due orders those by due date with the soonest first.

Display Due Dates with Milestone Names

For Trac 0.12 and above replace the line

'Milestone '||milestone AS __group__,

with:

'Milestone '||(milestone || CASE WHEN ms.due > 0 THEN ', (due ' || datetime(ms.due/1000000, 'unixepoch')  || ' UTC)' ELSE '' END) AS __group__,

And for Trac versions below 0.12 replace the line with:

'Milestone '||(milestone || CASE WHEN ms.due > 0 THEN ', (due ' || datetime(ms.due, 'unixepoch')  || ' UTC)' ELSE '' END) AS __group__,

The difference is that in Trac 0.12 dates, (at least milestone due dates), started to be stored as mircoseconds since the unix epoch, and before that they were stored as a simple unix timestamp, so now, to use SQLite’s datetime function we have to divide the stored value by 1,000,000.

This statement makes milestone names look like this:

Milestone Page Style Updated, (due 2011-11-21 23:00:00 UTC)

Note that there’s a UTC time listed. This is because I can’t figure out how to get a user’s timezone offset preference into the query. It would be relatively simple if the time was attached to a ticket, but in this case it’s attached to a milestone. If anyone knows how to work the proper timezone offset into the SQLite query please let me know.

Bonus: Link the Milestone Titles to Reports Showing Only That Milestone

It’s possible to create a link a list of that milestone’s tickets. Just add this line after the line that you just altered:

(CASE WHEN(milestone IS NOT NULL) THEN '../query?group=status&milestone=' || milestone ELSE NULL END) AS __grouplink__,

The grouplink column is a magic column that Trac understands and uses as a link for the group title, (in this case, the milestones).

The Full Solution

For you lazy folks, here’s the full query:

SELECT p.value AS __color__,
   'Milestone '||(milestone || CASE WHEN ms.due > 0 THEN ', (due ' || datetime(ms.due/1000000, 'unixepoch')  || ' UTC)' ELSE '' END) AS __group__,
  (CASE WHEN(milestone IS NOT NULL) THEN '../query?group=status&milestone=' || milestone ELSE NULL END) AS __grouplink__,
   id AS ticket, summary, component, version, t.type AS type, 
   owner, status,
   time AS created,
   changetime AS _changetime, t.description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN milestone ms ON ms.name = t.milestone
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status <> 'closed' 
  ORDER BY (ms.due > 0) Desc,ms.due, (milestone IS NULL),milestone, CAST(p.value AS integer), t.type, time