John Beales

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

I use Google Apps for my E-mail here on johnbeales.com. Earlier today I was updating some unimportant thing in the admin and noticed that I could turn on Google+ for johnbeales.com, something that I’ve been wanting to do for a while, woohoo! With Google+ up & running it was time to set up my circles.

For those of you who aren’t familiar with Google+ it’s Google’s answer to Facebook, and you can add your friends to groups, called “Circles.” There’s animation & stuff and it’s kind of fun.

After turning on Google+ for johnbeales.com I went ahead and added pretty much my whole address book, (Since Google already controls my E-mail it can show me anyone that E-mailed, pretty much ever), thinking that I was putting the people I know into groups that would make them easier to find and when if I needed to contact them again. What I hadn’t realized was that Google was E-mailing every single person I put into a group. So if I did business at some point with you years ago I might have put you into my “acquaintances” group, or perhaps into my “Vendors” or “Customers” group, and you would have received an E-mail because of this. If I’ve dealt with you at multiple E-mail addresses you may have receives an E-mail at each address. I did not mean for this to happen. Inboxes are polluted enough, and I’m sorry for polluting your inbox even more.

So of course I went and turned off the E-mail notifications, right? Wrong. I can’t figure out how. Once you’re already signed up with Google+ you can say that you don’t want notifications when other people add you to their circles, but it seems that if you’re not already a member you’re doomed to just keep getting this spam from Google. If Google is smart, and they often are, this will change. I’ll keep looking for a solution. In the meantime, I’ll be more selective about who I add to circles.

Last year when I was working on the redesign of BonzoBox I used HTML5 for the first time. The specification is being developed by two groups, and consists of a bunch of different modules, each with a different level of readiness, and the actual specifications, (yes, both of them!), are meant more for people who build web browsers than people who build websites. This led to some confusion. While I eventually wrapped my head around HTML5 I never felt that I had as good a grasp of the language as I would have liked to, so when I heard about Introducing HTML5 I added it to my to-read list. I finally had a chance to read it a couple of weeks ago.

Introducing HTML5 is the overview that I was looking for when I first started learning about HTML5. While I wish I had found it then, (although it wasn’t published at that point), and I learned a lot from it now. One thing that I had completely missed in my self-directed HTML5 studies was the outlining module, (if you’ve missed it too it dictates how an outline of an HTML5 document would be created, which allows you to figure out how important each element is in relation to each other element on the page). I also learned about some new elements that I hadn’t yet stumbled across, (like <mark>), and learned more about HTML5 form elements than I already knew, (although I was fairly familiar with those from the 24ways article).

There’s a chapter about the <canvas> element in Introducing HTML5. I was scared of the canvas element but the canvas chapter changed me from afraid to excited. It’s not so hard as it looks to work with and there’s a lot of really cool stuff that can be done with it. It’ll be fun to play with when I get a chance.

There’s also a chapter about using HTML5 apps offline. It’s possible to tell the browser to cache most, even all, of a website or webapp so that it’s completely usable offline. This cache seems to be a lot stickier than the normal browser cache and I wonder if it would be possible to also use it to drastically speed up online apps. This could lead to some real pain when updating websites, but if it’s planned well enough it might work really well.

Finally, websockets look great. They look fast, to the point that maybe we could make some stuff lightning fast on the web. Unfortunately a security problem was discovered in the websockets protocol so it was disabled in Firefox 4 and the latest Opera, and apparently Google Chrome is set to disable it if any attacks are carried out. I really hope that the security issues will be ironed out quickly so there can be wide adoption of websockets. I’m having problems finding out what the current status is of the security issue, I can’t find any articles more recent than those from December, so if anyone knows what’s going on please post a link.

I learned a lot from Introducing HTML5, and I’m excited to redesign a website, (or a few), using it, plus some CSS3, (I’ll be reading up on that soon).

Want to read it yourself? Get it from one of these places and I’ll receive a small kickback:

Amazon.com (USA) Amazon.ca (Canada) Amazon.co.uk (UK)

Bringing my reading back into the world of web design is Khoi Vinh’s book Ordering Disorder: Grid Principles for Web Design, which with Khoi’s grid-based layout and ample whitespace on each page I spent an unexpectedly short single day reading!

The slim book contains a mixture of information that is new to me and things I’ve seen before, but since I’m no grid master I have to right to say that I’m familiar with anything in the book. I appreciated the reminder that I should figure out what I want a website to do before I start writing code and the peek into Khoi’s design process was enlightening.

The book contains a brief history of grids then focuses on creating a grid and the obstacles that need to be overcome to do so. It is not a technical book that delves into the code required to make grids come to life on the world wide web. Once I realized this I appreciated the way it was written as a springboard to allow me to seek out more information on my own.

I think that I was in exactly the right place for Ordering Disorder to help me. I have read a bit about grids in the past, and have tried to use them in my projects with limited success, but reading Ordering Disorder improved my knowledge of grid principles to a level where I feel they can be much more useful when designing a website, even if I don’t take advantage of the springboard effect to launch my grid knowledge into the stratosphere and beyond.

Want to read it yourself? Get it from one of these places and I’ll receive a small kickback:

Amazon.com (USA) Amazon.ca (Canada) Amazon.co.uk (UK)

The number of people in my life affected by Inflammatory Bowel Disease keeps climbing, so I’ve been reading more about IBD and just finished the book The First Year: Crohn’s Disease and Ulcerative Colitis by Jill Sklar.

While reading about the inner workings of our guts, and what happens when things go wrong down there isn’t the most enjoyable past-time, Mrs. Sklar makes it relatively so. The First Year: Crohn’s Disease and Ulcerative Colitis primarily covers the “traditional” medical approach to treating IBD but doesn’t dismiss alternative therapies as completely irrelevant as many in the medical profession do. Mrs. Sklar seems at times to be angry that she has Crohns and she has a right to be: it’s really not a fun disease to have and it sounds like she’s had a rough time with it. But despite, (or perhaps because of), her anger she has managed to include sections that remind us of the human side of life, and these special moments outshine other, angrier, moments. For new IBD sufferers who may be angry themselves, realizing that they are not alone in their anger may actually help them feel better.

I spent some time reading the Amazon reviews of The First Year: Crohn’s Disease and Ulcerative Colitis and they range from “Very misleading book” to “Best Crohn’s Book Available.” While I’m still looking for the “best available” IBD book, (something that gives full consideration to both traditional western medicine and holistic, “big-picture” treatments including alternative, natural, therapy and diet), this book is very good, and I don’t think the science exists yet for my utopia of an IBD book, (and treatment plan). What was included in this book that has not been in other resources that I’ve discovered was information on the cutting edge of medical science related to IBD, including medicines that were not quite approved yet when the book was published, (2007 for the revised edition that I read), information about how new medicines are created and tested, and how to stay informed about the most recent developments in research into the causes and treatment of IBD including environmental and genetic factors. This is a far cry from the “IBD has nothing to do with diet. Take some anti-inflammatory drugs and prepare for surgery” approach that I’ve seen elsewhere, and also far from the “drugs don’t work” approach that many natural medicine proponents preach. In my limited exposure to IBD, I’ve seen both natural and medical methods succeed and fail, and it really does seem that more research needs to be done to figure out what’s actually going on in there, and what can be done to set it right.

So what’s the final verdict? The First Year: Crohn’s Disease and Ulcerative Colitis is packed with good information and worth reading. Even if you don’t believe in the traditional western medical approach to treating IBD, the resources and methods for coping with the disease in your, or your loved one’s, life make it worth the read.

For more information and support: Crohn’s & Colitis Foundation of Canada or Crohn’s & Colitis Foundation of America.

Want to read it yourself? Get it from one of these places and I’ll receive a small kickback:

Amazon.com (USA) Amazon.ca (Canada) Amazon.co.uk (UK)