All the Perl that's Practical to Extract and Report

SQL Keywords Are Not Good Column Names

Ovid on 2009-07-24T09:29:49

In retrospect, my accidentally naming a column after an SQL reserved word ("key") was pretty stupid and now I have a fair chunk of code to change.

But I briefly toyed with the idea of keeping a straight face and telling people it was defense-in-depth against SQL injection attacks. It would be fun to see how many people would buy that rationale.

Table names either

runrig on 2009-07-24T15:41:05

I once had to deal with a table named "group" (though I didn't name it). The database had been around far too long to think about changing it.

Re:Table names either

Ovid on 2009-07-24T15:52:55

We ran into that problem ourselves. We have a table named "grouping".

bad SQL column names

n1vux on 2009-07-24T21:36:44

I too feel your pain. It is quite natural in ham radio to abbreviate Callsign (our government issued not-quite* unique ids) as 'call'. That was a poor choice for a column name for such things, since user defined procedures in SQL have to be invoked by a call keyword. I had to expand the column name such that it is wider than the longest likely value (except Roman Italic compresses Callsign more than Roman Bold does WB1GOF) http://fd.ema.arrl.org/History.php

* why 'not quite unique? Two issues --

  1. A call can be reissued after a period of disuse**, so is only unique id within a time range.
  2. There is an overlap in FCC + treaty definitions for US callsigns
    • $ham = qr/[WANK][A-Z]? \d [A-Z]{2,3}/xism;
    • $plane=qr/N \d+ [A-Z]{1,2}/xism;

** this over simlifies several ways. the US has q(AA)..q(AL) but not A1 .. A9, AM .. AZ. http://en.wikipedia.org/wiki/ITU_prefix. There is more than a year between disuse and re-issue of personal callsigns to persons. Exceptions include bequething or memorializing a callsign to/with a club or relative after death, or special event callsigns, which flip week to week: in the US, volunteers loan out so-called 1x1 callsigns > $spe=qr/[WNK] \d [A-Z]/xism; to special events, eg W4H is sed in Bosten once a year but is used for other events elsewhen elsewhere, eg Knoxville TN (Fourth of July), Hunterdon Co 4H Fair, and Key Biscayne FL (in 4th call district), so the uniqueness is but for a weekend, not for a decade. And the equivalence range isn't even autoritatively defined online.

Moral of the story is that seemingly unique identifiers that you don't control issuance of are as likely to bite as seemingly good English word column names that turn out to be SQL reserved words.