It occurred to me the other morning that life would be a bit easier if SQL was a bit more like Latin. And I don’t mean that it should include the subjunctive or the ablative absolute because a) I can’t remember what those things are and b) having just looked them up on Wikipedia they wouldn’t really be of any use whilst querying a database.

Instead, I wish that SQL was a bit more permissive when it comes to phrase order.  Here’s a popular Juvenal line:

quis custodiet ipsos custodes

which, if we were to follow the translitteral word order when being trans-littoral, would read:

who guards themselves the guards?

Juvenal was writing at the end of the 1st century when Latin, whilst not dead, had certainly been around for a while so he could get away with an (appropriate) amount of poetic licence.  It would be more common in Latin to see the verb at the end of the sentence:

quis custodes custodiet

How the hell does this relate to relational databases?

Everyone knows that you should never have unrestricted access to a production database. In certain circumstances it might be acceptable to run SELECT queries but obviously no-one in the right mind would ever allow anyone console access to a production database with permission to run UPDATE or DELETE statements, let alone DDL commands. At BPM, our standard practice is to use an automated patching tool called AutoPatch which, in conjunction with CruiseControl for continuous integration, allows all such statements to be tested on both a seed database and a recent copy of the production database. That way we can apply changes to our persistent storage as part of the deployment process and be certain that it’s going to work beforehand.

So, last week I was running an UPDATE statement on a live production database using the psql console tool (yes, bad Luke) and it occurred to me that it would be so much nicer if I could type in my WHERE clause first. That way I wouldn’t forget to enter it and accidentally update the ptfname field of every row in the table to “Chaz and Dave”*. It got me thinking that perhaps the clause order should really be irrelevant and if I really want to write SQL like Yoda then I damn well should be able to write SQL like Yoda:

WHERE age = 900 FROM jedi SELECT *;

Occasionally it would make sense to write your GROUP BY clause just after the SELECT clause as they’re usually very similar. When writing a long statement that involves multiple joins you will end up writing the SELECT clause last, because it’s only by then that you know the table aliases that you’ve defined.

What’s the catch? (or: why hasn’t anyone done this before?)

I can spot two obvious downsides to this, the first being that you will inevitably have a performance hit as the lexer will have to do more work. Logically the time it takes to parse the statement will increase if the statement is more difficult to parse. The second downside is that it will make your SQL statements more difficult to parse by other people. It’s bad enough having to put up with Java allowing people to put opening braces on new lines without having to cope with Jeffrey, the guy who insists on putting the OUTER JOINs at beginning and the INNER JOINs at the end. It won’t be long before I’m up the water tower with a sniper rifle.

It’s here that my Latin analogy breaks down somewhat. As PostgreSQL is an open source application I can change the way that it parses SQL and if I really wanted to I could even replace all the words it uses. Or I could add a pre-processor to reformat my garbled input into a sanitized SQL-compliant version. In fact, as with a lot of things in the programming world, someone’s probably already done this.

We were told at school that Latin is a dead language, so it won’t be changing any time soon. However it may not be as dead as I was led to believe:  Latin has influenced modern languages all over the globe and you still find Latin words and phrases in common usage in everyday English – all too often I find myself reading an agenda for yet another meeting. You can even browse a popular social networking site in Latin so I think we can assume it will be sticking around for some time.

quod erat demonstrandum

[ * My very own TheDailyWTF moment was when I did this on a production database but fortunately a particularly complex trigger kicked in and the statement timed-out and rolled-back before causing havoc, embarrassment and potentially my dismissal. ]

This entry was posted on Friday, October 9th, 2009 at 6:59 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.