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

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.

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)

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)

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)

While these fast, subconscious thoughts and decisions happen all day every day in most of our lives, the area of first impressions is a great example of near-instant subconscious thought in action. When we first meet, or even first see or hear, someone our minds take in all kinds of information about that person and we form an opinion of, and often a reaction to, that person. The reaction might be to run if it’s someone brandishing a knife in a dark alley, or to stare if it’s someone really attractive at the beach, or anything in between. Often these reactions are exactly the right reaction, but often they’re not the right reaction at all, and that’s what Blink talks about: Our unconscious thoughts, how they work, how they can work much better than painstakingly working our way through a problem, and what to do to stop them from taking you down completely the wrong path.

Blink is easy to read and very interesting. Extremely interesting. After reading it I hope that I can learn how to make “Thinking without thinking” work in my life, especially now that there’s an election on.

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)

Even before I finished the High Performance MySQL my work was seeing the benefits of what I had learned partway through the book. There are a lot of quirks in MySQL that I wasn’t aware of, especially when it comes to joining tables and applying indexes. In one instance I was able to take a query that had been taking several minutes to execute and bring its execution time down to just a few seconds simply by rewriting some joins in such a way that, when thinking only about how data is related, appears to be quite bizarre, but when thinking about how a query will be executed by MySQL makes good sense.

I learned about how indexes are used, not just in theory but actually how MySQL looks for something within an index, and I learned about how I can use indexes to make it so MySQL doesn’t have to touch the underlying table data for some SELECT queries, (quite the speed enhancement there). I learned more about InnoDB than I thought I would know for quite some time. There’s a great appendix about the Sphinx search server, which is a product that I’ve been interested in for quite a while. The appendix gave me the introduction that I needed, and I hope to start using Sphinx to power some searches soon.

If you work with MySQL databases a lot, and especially if you are involved in designing them, then High Performance MySQL is a very good book to have on hand. Even if you don’t read it from cover to cover like I did it’s a great reference and will help you speed up your MySQL instances.

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)

Reading The Holcroft Covenant reminded me of older times and stories, when there were more people who remembered the World Wars than didn’t, and conspiracy theories could be created about secret Nazi societies run by generals who escaped prosecution after the war, and perhaps by their children. The story lines of The Holcroft Covenant weave themselves slowly together, and in true thriller fashion I frustratingly figured out what had to be done long before the characters did. For a book that will keep you on the edge of your seat, (or bed, or whatever), this is a good one, although don’t try to read it all in one sitting like some other books that I have read recently, it’s a bit long for that, (but every page earns its place).

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)

Time to get back to the computer-related brick I have on the nightstand.

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)

There are a bunch more of these on the shelf waiting to be read, but I don’t want to go too fast since I tend to read them in one, quite long, sitting, and I have other things that I have to do. I am looking forward to reading them, though.

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)