Category: code

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

Getting Started with HTML5

I’m working on a project now were we’ve decided to go with as pure HTML5 as posible, and it’s a breath of fresh air. Things work more or less how they should, and Internet Explorer is even playing along, with a little help. Getting started was a bit of a trick, though, as it can be hard to find information on how HTML5 works without diving into specification documents, which is never fun, or easy, (if you don’t want to read the story, skip straight to the resources).

I hadn’t been following the development of HTML5 with more than a passing interest. I figured that when it was ready, then I would start using it. I also understood that there were different parts that may reach completion at different times, and was keeping my eye open for some sort of “completion” signal. 2009’s 24 Ways was that signal for me. There were several articles on using HTML5 features along with their CSS3 counterparts, and enough evidence that browser support is there to start my investigation.

Here’s the deal: Basic HTML5 support is pretty good in webkit-based browsers, alright, (read usable), in Gecko, and kind of lacking in Internet Explorer. However, if you can rely on Javascript being present, (which I can in my project), there’s an HTML5 Shiv Javascript by Remy Sharp that makes it so that you can style HTML5 in Internet Explorer. Add it using a conditional comment and you’re good to go.

So, we have useable cross-browser support, but where do we turn to learn about which tags are in, which are out, the correct doctype and mime-type, and all that? We could read the specification, (and we will have to read a bit, at least), but it would be nice if there was an introduction to HTML5 somewhere. It turns out that Robert Nyman has written an Introduction to HTML5. It’s detailed enough to get you started, but not so detailed that you get lost, (like the spec), and if you’re looking to be convinced of the value of HTML5, check out HTML5: Tool of Satan, or Yule of Santa?, Have a Field Day with HTML5 Forms, and Breaking out the Edges of the Browser from 24 Ways 2009.

Once you dive a little deeper you’ll find that there are elements of HTML5 that you need more in-depth information for, so it’s time to turn to the spec. However, there are 2 groups, (W3C and WHATWG), working on HTML5, and therefore 2 spec documents, (fun!). Fortunately, the two groups have the same editor, so they’re more or less working on the same thing. I find the WHATWG HTML5 document easier to read, but if you prefer the W3C version, go nuts.

Finally, the whole content-type debate that’s been going on for what seems like centuries is still a mess. In HTML5 you’re supposed to include a Document Type Definition and there should be no namespaces on the HTML element if you’re serving as text/html, and you’re supposed to serve in application/xhtml+xml if you want to use namespaces, or force XML validation, or anything like that. The problem is that Internet Explorer really doesn’t like application/xhtml+xml, (it shows the raw XML document), so if you need a namespace for some reason, (for example, you want to use Facebook Connect on the site), you can’t serve valid markup.

So, that’s it. HTML5 has arrived, or at least parts of it. If you can rely on Javascript being present, or rely on IE users not using your web app, you can go ahead and start using it. Here’s a quick recap of the resources:

PHP’s mysql_connect() Reuses Connections by Default

As I mentioned yesterday, I’m doing some work in WordPress right now, and a few minutes ago I tweeted that my custom code is messing with WP’s wp_get_archives() and wp_list_categories() functions, well, I found the problem.

I am including the 4RoadService.com header & utilities files in my WordPress theme, and I am using the same user here on my test server for both the main 4RoadService.com database and the WordPress database. It turns out that when the 4RoadService.com database connection was initialized, since it uses the same connection info as the WordPress database, the existing connection was just reused, (this behaviour, by the way, is well described in the PHP documentation), then when the 4RoadService.com connection was told to use the main 4RoadService database it did, thus switching our one and only connection away from the WordPress database, and making WordPress think that there were no posts on the blog.

Fortunately, there is a quick workaround, just add one more attribute to the mysql_connect() function so it looks like this:

$dblink = mysql_connect($host, $user, $pass, true);

This way a new connection is established, and the WordPress connection is left alone.

I am left wondering why, in the loop, WordPress was able to see my posts, perhaps it establishes a second database connection in there. However, I’m not going to spend the afternoon poking through the guts of WordPress.

Announcing MyMap Explorer for Google Maps

Today I am announcing the release of MyMap Explorer for Google Maps. This small javascript allows you to embed a map created using Gooogle Maps’ My Maps feature into any web page with more information and flexibility than Google’s iFrame embed code.

Back in November, Heri asked for a relatively simple way to integrate his Technology Map of Montreal into Montréal Tech Watch, and MyMap Explorer is the result, (see it live on the Technology Map of Montréal), It takes the KML description of a map from Google Maps and adds it to a map created using the Google Maps API. It also provides an alphabetized, clickable list of the points on the map so that your users don’t have to click on each marker to find the location that they are looking for.

The KML is loaded live from Google Maps so if you make a change to your map on Google Maps it will be shown in all embedded versions of your map as well. This script has no dependencies, other than Javascript and a Google Maps API key. Just insert it into a web page where you want to see your map and it appears!

I have some features that I still plan on adding, but want to get the basic script out there and into use. I’ve released the code on Google Code under an MIT license so you are all free to use MyMap Explorer, and contribute if you feel up to it.

The demo is here.