Category Archives: Code

Non-web related coding, mostly Python.

Sometimes, you just don’t want to be right

It’s been a few years since I “enjoyed” writing code to mangle Microsoft Office documents, but I’ve been getting back into such things at work lately. My team and I had lots of fun yesterday tracking down the source of a bug in POI which led to Excel mangling comments when the file was saved. Frankly, I wasn’t sure to be delighted or disgusted that the “fix” was changing an ID value in the XML output. Yes, it would seem that a so-called arbitrary identifier has significance to Excel beyond cross-referencing elements in the same file, and using the same not-so-arbitrary identifier written by Excel itself dissuades it from trashing the file on save.

Google and IPv6 e-mail

Update: The change described below does not seem to have reliably stopped Google from bouncing my e-mails. Time to ask them what they’re doing…

I obviously spoke too soon. Having complimented Google for finally enabling IPv6 on Google Apps, I was lying in bed this morning firing off a few e-mails from my phone when this bounce came back:

This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. The following address(es) failed:

SMTP error from remote mail server after end of data:
host ASPMX.L.GOOGLE.COM [2a00:1450:400c:c05::1a]:
550-5.7.1 [2001:41c8:10a:400::1 16] Our system has detected that this 550-5.7.1 message does not meet IPv6 sending guidelines regarding PTR records 
550-5.7.1 and authentication. Please review 550-5.7.1 for more 550 5.7.1 information. ek7si798308wic.60 - gsmtp

Hmm. The recipient address has been changed, but the rest of the above is verbatim. The page Google link to says:

“The sending IP must have a PTR record (i.e., a reverse DNS of the sending IP) and it should match the IP obtained via the forward DNS resolution of the hostname specified in the PTR record. Otherwise, mail will be marked as spam or possibly rejected.”

All of which is reasonable-ish, but the sending IP does have a PTR record which matches the IP obtained by forward resolution:

david@jade:~$ host 2001:41c8:10a:400::1 domain name pointer

david@jade:~$ host has address has IPv6 address 2001:41c8:10a:400::1

So what are they objecting to? Some Googling and some speculation suggests that they might be looking at all hosts in the chain handling the message (!). Further down the bounce in the original message text we find:

Received: from [2a01:348:1af:0:1571:f2fc:1a42:9b38]
	by with esmtpsa (TLS1.0:RSA_ARCFOUR_MD5:128)
	(Exim 4.80)
	id 1Vrm3Q-0002Ay-NH; Sat, 14 Dec 2013 10:02:36 +0000

Now, the IPv6 address given there is the one my phone had at the time. It doesn’t have reverse DNS because you can’t disable IPv6 privacy extensions in Android (also Google’s fault!), and assigning reverse DNS to my entire /64 would require a zone file many gigabytes big.

At this point, it’s probably best to stop speculating on Google’s opaque system and start working around it from my end. Others have resorted to disabling IPv6 for their e-mail server altogether – no thanks – or just for sending to This latter approach doesn’t work for me as the example above involves – and potentially lots of different domains will be using Google Apps for mail, so a simple domain-based white/blacklist isn’t going to cut it.

After spending some time with the excellent Exim manual, I’ve come up with a solution. It involves replacing the dnslookup router with two routers, one for mail to GMail/Google Apps hosted domains, and one for other traffic. Other settings on the routers are omitted for brevity, but you should probably keep the settings you found originally.

  debug_print = "R: dnslookup (non-google) for $local_part@$domain"
  # note this matches the host name of the target MX
  ignore_target_hosts = * : *
  # not no_more, because the google one might take it

  debug_print = "R: dnslookup (google) for $local_part@$domain"
  # strip received headers to avoid Google's silly IPv6 rules
  headers_remove = Received
  headers_add = X-Received: Authenticated device belonging to me or one of my users

SQL? Dude, you’re doing it wrong

I’ve increasingly formed the opinion over the past few years that (almost) anyone writing software, certainly in the SME or 90% of open-source space, simply shouldn’t be writing raw SQL.

This is the 21st century, and all the major programming languages have these things called ORMs. Since all you actually wanted from your database was some kind of load/save/search for the objects that make up your software’s state, it turns out encoding that metaphor at the object level is much nicer than writing the code to do it all yourself.

The added bonus of this is that when you want support for a new DBMS, you just need to see if your ORM has support. Generate a schema, run through all your tests, a couple of minor fixes, job done. And you’ll make your sysadmin a happy man by not dictating a choice of DBMS to him or her which makes their life harder.

 You may be drawing breath to argue that writing the raw SQL yourself by hand is ‘more efficient’, but come on. Your blog has a couple of hundred posts and maybe a few thousand comments. The daily hit rate of ten and a half people isn’t going to tax even MySQL – so whether you use it, PostgreSQL, or even the free edition of IBM’s DB2 (eight-character limit on database names, anyone?) really doesn’t matter. It’s an implementation detail you shouldn’t worry too much about, and certainly shouldn’t prematurely optimize by getting too familiar with.

Django has led the way in the Python world for years on the ORM question, but all the other languages have them too. Make the jump, and you can always escape to raw SQL if you really need it in one corner of your application.

Dear Internet, any ideas for a free(ish) room booking system?

I’ve been tasked with finding a room booking/hiring system for St Columba’s.


  • Booking of different spaces by different users
  • Ability to generate a report of billable hours outstanding for a given user
  • Ability to send invoices and reminders to users and internal staff
  • Integration with Google Calendar, as that’s how we publish hiring timetables on the website at the moment

At the moment, the most promising free option out there seems to be MRBS, but I’d have to add the Google Calendar and invoicing integration myself.

Does anyone have any bright ideas? Let me know if so.

The port 0 trick

The port 0 trick came in handy when writing eximunit, and it’s something surprisingly few developers know about, so I thought it worth recounting here:

The problem: you want to set up a web/mail/whatever server programmatically (e.g. as part of some tests). This server wants to bind to port 80/25/whatever. Your first problem is that it can’t bind to these because you’re not running your tests as root (or as an administrator on Windows).

The lazy approach at this point is to hard-code a port number over 1024, which you don’t have to be privileged to bind to. But this all falls to bits if you want to run the same test simultaneously on the same machine, or you need lots of different ports during the course of one test.

At this point, you can reach for the port 0 trick: on both Windows and Linux, if you bind a socket to port 0, the kernel will assign it a free port number somewhere above 1024. Truly well-written software (e.g. Jetty) will not only let you configure it to bind to port 0, but will make it easy to parse its logs to obtain the actual port number it got assigned. Less helpful software (Tomcat) will let you configure it to bind to port 0, but print 0 in all its logs, never the actual number. And the majority of software just won’t let you put 0 as a port number in its configuration.

At this point (subject to a slight race condition), you can grab some port numbers yourself and feed them to whatever you’re trying to configure:

def findFreePorts(howMany=1):
    """Return a list of n free port numbers on localhost"""
    results = []
    sockets = []
    for x in range(howMany):
        s = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        s.bind(('localhost', 0))
        # work out what the actual port number it's bound to is
        addr, port = s.getsockname()

    for s in sockets:

    return results

The above is written in Python, but it translates trivially to any programming language which knows what a socket is.

You’ll note that the correct way to get, say, five free port numbers is to call the above method once with 5 as its argument. If you wrote a simple method which just returned one number, there would be nothing to stop it returning the same number each time you called it (the Linux kernel is rather more helpful, and will usually hand out a different number to each port 0 request, but I wouldn’t  rely on this).

Introducing eximunit

For a few years now, I’ve run a hosting co-operative with a few friends. Although the cost savings versus all renting VMs individually are probably marginal at best these days, one of the nice things about it is the chance to run things like our incoming MX on one machine only, instead of all having to run our own anti-spam and other measures. The incoming mail is handled by Exim, and each user of our system can add domains for which mail is processed. They get to toggle SMTP-time rejection of spam and viruses, and specify the final destination machine for incoming mail to their domain.

This has all been working well for over  two years, but occasionally something has to change: a few months ago, we got rid of sender verify callouts, now widely considered abusive by SMTP server admins, and more recently we added support for tagging messages with headers to say if they passed or failed DKIM verification. And every time I make such a change, I worry that I might have inadvertently broken something. This server handles mail for 30 domains and 8 people, some of who rely on it to run businesses! Panic!

I usually end up reassuring myself by doing some ad-hoc testing by hand after reconfiguring the server. At the most basic level, whatever your SMTP server is, you can use netcat to have a conversation with it on port 25:

d@s:~$ nc localhost 25
220 ESMTP Exim 4.71 Sat, 17 Mar 2012 09:51:20 +0000
HELO localhost
250 Hello localhost []
250 OK
550-Callout verification failed:
550 550 Unrouteable address
221 closing connection

And there, I’ve just convinced myself that one of our features is still working: the mailserver should call forward to the final destination for mail to addresses to check the local part (‘someaddress’ in this case) is valid, and reject the message up-front if it’s not.

Exim also has a load of other toys you can take advantage of: say I want to check how mail to is routed:

d@s:~$ exim4 -bt
R: hubbed_hosts for
 router = hubbed_hosts, transport = remote_smtp
 host []

(IP addresses changed for example purposes, obviously)

And finally, there’s debug mode: you can run

exim4 -bhc <ip address>

to run a complete ‘fake’ SMTP session as though you were connecting from the given IP address. You can send messages, but they won’t actually go through, and exim prints a lot of debug output to give you a clue as to its inner workings as it decides how to route the message.

This is all very well, but a quick brainstorming session gives a list of over 30 things I might want to check about my mailserver:

  • Basic check that mail is accepted to our domains
  • Only existent addresses on our domains should have mail accepted
  • Domains with SMTP-time spam rejection on should have spam rejected
  • Same for viruses
  • Same for greylisting

Testing all these by hand isn’t going to fly, so what tools can we find for automating it? A bit of Googling turns up swaks, which looks quite handy, but suffers from two drawbacks for me: first, it’s a bit low-level, and a collection of scripts calling it will be a bit difficult to read and maintain for testing all 30 of my assertions. Second, it really sends the e-mails in the success case, and I don’t want my users to get test messages or have to set up aliases for receiving and discarding them. swaks will definitely become my tool of choice for ad-hoc testing in future, but meanwhile…

The other promising Google result is Test::MTA::Exim4, which is a Perl wrapper for testing an exim config file. However, a few problems: (1) it’s Perl, and I Don’t Do Perl. (2), it’s limited to testing the routing of addresses, so it’s not going to cut it for checking spam rejection etc.

Having at least pretended not to be suffering from NIH syndrome, let’s spec out a fantasy system for doing what I want: I would like to be able to write some nice high-level tests in my favourite language, Python, which look a bit like this

class HubbedDomainTests(EximTestCase):
    Tests for domains our server acts as the 'proxy MX' for, doing
    scanning etc before forwarding the mail to the destination machine

    def testProxiedMailAccepted(self):
        """Proxied mail should be accepted"""
        session = self.newSession()

    def testLocalPartsVerifiedWithDestinationMachine(self):
        """Local parts should be verified with the destination machine"""
        session = self.newSession()

I could then run these in the usual manner for Python unit tests, and lastly, I want them backed by an exim4 -bhc session so that they’re as realistic as possible without actually sending messages.

This post is long enough already, so I’ll cut to the chase and say that I’ve made a start on writing it, and you can find out more at Bitbucket. In a follow-up post, I’ll talk about how it was done.

Over-engineering and how it makes people’s lives worse

There’s an acid test that we as engineers should always subject our creations to: do they make life better for the end user? “Better” is perhaps quite difficult to quantify, but you can always approach the problem from the opposite direction and see if you’ve made things worse.

This is something British Gas’s man clearly failed to do when fixing my grandparents’ central heating recently. I don’t know the full details of the problem, but I do know that their thermostat was broken, so he installed a new one.

Fair enough, but it turns out that the iron march of progress has changed a thermostat from a knob with some numbers on it to something ‘smart':



Central heating thermostat, circa 1990



Wireless super-blingy modern thermostat, circa 2011


And how does the new wireless thermostat make life worse for my nonagenarian grandparents? Let us count the ways:

  • Because it’s wireless, it has batteries in it which need replacing every so often. This is achieved by opening a flimsy plastic door on the bottom of the unit which is fiddly to access once it’s wall mounted, then scrabbling on the carpet as the batteries fall to earth. It also means the thermostat will mysteriously stop working once every n months until someone younger sorts it out for them, since there’s no way they’ll hear a low-battery beep or spot an indicator on the screen.
  • Since it’s superglued to the wall just inches away from the hot water tank it controls, the only advantage of wirelessness is to save the drilling of one hole and the running of a six-inch bit of cabling – and even these could presumably have been avoided by replacing the original thermostat instead of leaving it screwed to the wall but not doing anything.
  • Instead of reading the numbers round a knob, you see them on an LCD display which is not backlit and not very big, thus making it perfect for people with poor eyesight to see in a not-very-well-lit hallway.
  • Pressing the middle of it resets it to a pre-programmed ‘preset temperature’ (“ideal for the poorly sighted”, the manual claims with no sense of irony) – an unnecessary recipe for confusion if you knock the middle by mistake
  • It doesn’t go ‘click’ as it passes the current room temperature like an electromechanical thermostat would, so you have to read the screen instead
  • By default the display shows the current room temperature, meaning you can’t tell without adjusting the knob what temperature the thermostat is currently set at

Somewhat more subjectively, I think it’s more likely to malfunction than an electromechanical device with two moving parts, and presumably it has to fight for spectrum with all surrounding cordless phones, WiFi units and garage door openers – let’s hope the base station does something sensible in the face of losing contact with the unit.

Well done, lads. Another triumph of engineering.

The joys of shared houses…

Being an organised sort of person (and a sucker who can’t say no), I tend to do a lot of the admin and paperwork for the house I share with a few friends. One of the things I do is draw up the chores rota. I’ve typically done it like this*:

Week      10/09 17/09 24/09 ...
Bathroom  Tom   David Harry ...
Kitchen   Dick  Tom   David ...
Bins      Harry Dick  Tom   ...
Hoovering David Harry Dick  ...

Careful and studious readers will have no difficulty in spotting the pattern.

Inevitably, though, instead of being grateful for the minutes I slaved over my desk putting this together, t’housemates complained. Specifically, Tom complained that ‘I always do something the week after Dick’s supposed to have done it, and he makes a mess of everything’.

Given the choice between speaking to Dick to correct the problem, or writing some code, I wrote some code which starts with the above layout, then shuffles the columns until the following constraints are satisfied:

  • A given person never does the same thing two weeks running
  • For any given pair of people (p1, p2), this pair never appears twice in a given row

Careful and studious readers (with Maths or CS degrees) will have no difficulty working out the number of weeks of chores rota I’ve limited myself to doing at a time, becuse the constraints become impossible for a greater number.

* No, my housemates aren’t really called Tom, Dick and Harry. After five years at an all-boys school, there’s no way I’d live in an all-male household.

Java programmers

I don’t much care for Simon Willison‘s (or in this case, the person he’s linking to’s) implication that Java programmers are misguided fools in need of help (as opposed to engineers trying to earn a living by using the best tool for the job on hand, rather than getting all evangelical about their language of choice).

However, the quote he posted recently makes a good point – using immutability and other functional ideas does make for better-written and less error-prone Java. And many of us have moved in that direction already, mostly without any prompting (although since Haskell was the first language they taught me at Oxford, that doubtless influences my world view).