Soylent Red

Imagine my surprise and delight yesterday when I found out that one of my favourite blogs, Lifehacker, had featured my very own Make Link Firefox extension as its Download of the Day.

My joy was tempered somewhat by the fact that I've always been a little bit ashamed of the quality of that extension. I wrote it quickly and a long time ago so all I see when I look at it are flaws. The few comments on the site and by email inspired me to put a little bit of effort into polishing it up somewhat. The result is Version 2.1, which addresses three of the more widely reported bugs and feature requests.

• Permanent Link
• Toggle Comments [2]

You probably won't have noticed much if anything different about Soylent Red in the last day or so—I think the new URLs are the most visible change—but I did make quite a significant code change yesterday evening. The diff weighed in at over 2000 lines in fact. All of the code worked on my local testing setup, but I had a problem as soon as I tried to post a new entry on the live site. Only the date, the tags and the comment status (open or closed) were saved.

After a bit of wrangling—I had to check in a new copy of the code every time I wanted to test something—I discovered that the problem lay in the use of the mysql_real_escape_string() function, which is used to protect against injection attacks when putting text in a database. It was news to me that this PHP function actually calls an SQL function to do the work for it, so it requires an existing database connection.

After my big code changes there was one part of my code where I happened to try to use the function to create my query before the code to establish a connection to the database server. In this event PHP will try to connect as if calling mysql_connect() with no arguments, and failing that it does the only thing it can do: it throws a warning while returning an empty string. I have my live site set not to display errors, so it looked to be silently failing. Meanwhile my local testing setup never came across the warning because it is always possible to establish a connection (to a server on localhost with no username or password).

I love these bugs that lead to new learning.

• Permanent Link
• Toggle Comments [2]

When I was deciding how many recent posts to display on the main age of Soylent Red I had two concerns: that each post should have its place for a certain minimum time period, and that the page should not empty of content for lack of recent enough posts. The minimum time period right now is one week. The minimum number of posts at any time is five. That means that if I don't post for a week, the last five posts will still be here. If I post ten times in one day, there'll be a whole load of posts on here until they drop off the front page a week hence (assuming they're replaced with new content).

The SQL query for this is a straightforward union: (SELECT * FROM entries WHERE posttime>'cutoff' AND status='published' ORDER BY posttime DESC) UNION (SELECT * FROM entries WHERE status='published' ORDER BY posttime DESC LIMIT 5); Here I've simplified the long list of fields to return to a simple "*"—I don't recommend using the universal selector in practice. Also, "cutoff" represents a datetime value for one week ago computed in the PHP source. This is (essentially) the code I had in place until just now, but there's a bug in it.

This is a UNION of two SELECTs, each of which is ordered by descending timestamps, so the first item returned is the most recent post, as you would want it when displaying posts on a blog. But when the UNION operator is applied to the two sets of results, the order is no longer guaranteed. The posts can come out in any order at all. Up until now they had always been returned in the order I wanted, or at least I hadn't notced otherwise, but there had been no guarantee.

The solution to this ordering problem, once it has been identifieed, is easy. Just add an extra ORDER BY posttime DESC clause at the end of the statement and you're done. In fact, the first ORDER BY clause is made redundant by this, so it can be removed. The ORDER BY clause in the second SELECT interacts with the LIMIT clause, so it needs to remain.

After this jiggery-pokery the final, correct query is: (SELECT * FROM entries WHERE posttime>'cutoff' AND status='published') UNION (SELECT * FROM entries WHERE status='published' ORDER BY posttime DESC LIMIT 5) ORDER BY posttime DESC;

Next time: How to get the full content of every post and its associated comments in a single SELECT. (Not really. That's just something I have on my list of things to do now that I know how. Soylent Red does it in two queries.)

• Permanent Link