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
(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__,
'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
Chitchat about “Improving Trac’s Tickets By Milestone Report”
Thanks John. This was very useful and a big improvement in my trac ticket workflow.
Adding the ‘localtime’ modifier option to the call to datetime fixes the UTC vs localtime issue. 🙂
This is almost exactly what I worked out. It’s good to post it for others to use.
If your Trac database is hosted in MySQL, then substitute
from_unixtime(ms.due/1000000, format), where format is an optional format, in place of
This was a huge help, I was tearing my hair out trying to get the un-milestoned tickets to appear at the bottom of the list. This fixes it plus so much more!