An Apology

I noticed yesterday that this blog had become far too technical, with lots of posts about abstruse topics such as Oracle databases and Linux distros. Time, therefore, to apologise for that and to do something about it.

First, whilst I wouldn’t normally share this sort of thing with anyone, I think it appropriate to announce the fact that two nights ago, I had my first-ever lucid dream. This is a peculiar state that I first heard about when I was at Uni in the early 1980s: you are completely asleep, you are dreaming, and you are completely aware of the fact that you are dreaming. I was totally aware of the fact, for example, that I couldn’t move my fingers (immobility being a feature of even normal dream states), and that TOH was breathing er… “rather heavily” at my side! I also knew that Byron Road County Primary School couldn’t possibly look that way these days and that I would get into trouble if I walked past the headmaster’s office without putting my head round the door and saying hello. It is, as they say, a very strange state to be in altogether.

But the wonderful feature of this odd state is that you can control what you dream about. So, naturally, I did what all lucid dreamers tend to do the first time… and jumped out of the nearest window. Then, you see, you get to fly. I mean really fly: wind through the hair, arms outstretched, diving and soaring -with not the slightest hint of an aeroplane in sight. Flight au naturel, in short, as an eagle would do it… utterly exhilerating. In fact, it was a little too exhilerating, because I was so excited by the proceedings that I woke myself up, thus bringing my maiden flight to a sudden halt! This was a bit of a shame, because I’d planned that my next stop was to the pit of a world-renowned orchestra, which I would conduct in some serious Benjamin Britten. That, however, will have to wait for the next time.

If you’ve never had a lucid dream, I would encourage you to try to have one. It even turns out that there is a website that will help you have one! I can recommend the reality checks that page suggests, by the way: I noticed, for example, that I cannot read music in a dream, the notes going all indistinct and vague (which makes the chances of me conducting a good version of anything written by Britten pretty remote, I suppose!). Next time I look at a piece of vague, indistinct music I will either think, “Lay off the Whiskey!”, or “I’m dreaming!” (or, I suppose, that I’m holding a piece of Stockhausen in my hand).

Anyway, enough of lucid dreams (though I can tell you I’m as excited at finally having one as I was getting my first glimpse, after 30+ years of trying, of Saturn and noticing that, cor blimey! it’s got rings!!)

The other thing I thought I should share by way of utterly non-technicalities is the latest from the ever-growing clan of Wallabies. Ever-growing, incidentally, because Rachel is pregnant yet again.

I’ve had some emails wondering whether we’re doing the right thing, plying these marsupials with bread. Well, I offer this by way of evidence that it’s not just bread we give them (though bread tends to be the first thing they choose to eat!):

That’s Chandler, bread, birdseed and a rather nice rocket salad… and she picked the bread herself.

But it’s not just bread and birdseed (or even rocket salad). Oh no. Boise is rather partial to something completely different:

That will be him with his head in a bag of cat food, then! That would be the Ocean Platter flavour of cat food, too. I was unaware that wallabies would eat fish, but there you go!! I’d like to be able to say that I’ve checked the ingredients for this particular brand of cat food biscuit and discovered that it’s all lovely stuff that even the fussiest of wallabies (or cats) would be mad to turn their noses up at, but I’m afraid the ingredients list starts off with “Crude Protein 30%”, descends almost immediately to “Crude Fat 15%” and even takes in “Ash 8%”. Not exactly appetising stuff, methinks… but Boise thinks otherwise, apparently.

But he can be rather more ‘refined’ about the process, too:

That’s Boise being s a little more polite about it, disdaining the bag and preferring the ceramic bowl with insightful commentary! Classy, I think!

The cats, incidentally, do not apparently mind their food being purloined by what, to them, must seem like giant rats. But then again, I wouldn’t argue with a giant rat, so I don’t suppose they’re going to start, either!

The results of all this exoitic food are plain to see:

Robot eyes!

Oh, OK, it’s nearly twilight and wallaby eyes seem to be particularly prone to what, in humans, would be called ‘red eye’ but which in the marsupial world should probably be called ’silver eye’. The real point about this picture is not the un-Earthly look of a wallaby’s eyes when reflecting flashlight but that we see Chandler in the front of the picture being vigorously, er… “stroked” by Boise right behind her… and Boise appears to be wearing a particularly satisfied grin. Yes, it’s post-coital statisfaction that looms large over Boise’s face -and it’s nice to see that Chandler kept nibbling her bread throughout the proceedings!

Exotic the food may be, in other words, but it’s certainly not putting a damper on anyone’s normal, healthy non-food appetites!

Which no doubt explains this picture:

That looks like four wallabies: Rachel, Chandler, Boise and Ben. But, in fact, Rachel is carrying yet another Joey (as yet unnamed), so that’s a picture of five of them. I’m open to names for the new member of the troupe, incidentally, though names of Friends characters are now rather passé.

So there you have it. Dreams, Wallabies and very little Databases. I hope that counts as a sufficient apology for letting the technical push aside the non-technical!

Who would be a DBA?!

A contracted developer recently had me roll a piece of code into production that contained the following nugget:

if (orderRec.image_size >= vpMin_size or vpMin_size is null or vpMin_size = 0))

Nothing too outrageous, then: check image_size, and if it’s greater than or equal to some number supplied when the procedure is invoked, the record qualifies.

What I hadn’t noticed (black marks all round, then, because no-one else had spotted it either!) is that IMAGE_SIZE is a VARCHAR2 column. Yes, really. We’ll come to the reasons why in just a moment!

Meanwhile, we all soon found out about this when the website broke with the infamous ‘ORA-06502 character to number conversion’ error: a record with an image_size value of ‘4.7mb’ had fallen foul of the troubles that ensue when you try to perform mathematical comparisons on the phrase ‘mb’!

I suggested to the developer that we convert the column to a NUMBER data type. Can’t do that, he said, because the field had been deliberately created as free text, because (get this!) they needed to be able to see a “nice” rendering of the image size (that is, one qualified with ‘MB’ or ‘KB’ descriptions) on printed invoices. Turns out, indeed, that this field contains such size gems as “small”, “medium” and “large”!!

Instead, the developer put into production (without telling me first) this fix:

if (nvl(trim(regexp_replace(lower(orderRec.image_size), '[^[:digit:]]', '')), 1 >= vpMin_size or vpMin_size is null or vpMin_size = 0))

Now, I am not a fan of regular expressions at the best of times. They are difficult to read, harder to interpret. They make code dense and obscure -especially when (as in this case) not one word of commentary is made in code comments to explain what it’s trying to do. Moreover, they are a fairly new feature in Oracle, and I don’t like using new features unless they are compelling -providing something you couldn’t do any other way, for example. That’s mainly because Oracle has a habit of not implementing new features very well (think ‘ANSI-compliant join syntax’ in 9i, as a case in point!). I only glanced at it, but typing “REGEXP_REPLACE” into Metalink’s bug database got me 18 matches this morning -at which point, I’d just as sooner not use the things, really!

Anyway, that’s a side issue… because what really got me about this developer’s particular regular expression was that it wouldn’t work! Ostensibly, it seeks to strip out any non-numeric character from the field and turn NULLs into ‘1′ -and in those particular endeavours, it works well. The only slight fly in the ointment is that a full stop (period) counts as a non-numeric character, so that an entry of “4.7mb” gets turned into one of “47″ -inflating the size by a factor of 10 and thus rendering the comparison to ‘vpMin_size’ rather redundant!

When I pointed this out to the developer, I was sent this:

The regular expression is doing exactly what I want it to do… I cant believe we have wasted so much time discussing it.

Well, faced with such supreme confidence in his own coding abilities, what could I do, but repeat my objections to a piece of code which was (a) trying merely to work around a fundamental problem in the data model whereby free-form text was suddenly being used to perform mathematical comparisons and (b) getting it wrong by a factor of 10 in the process.

Happily, persistent paid off in this case:

After reviewing the code again, you are right in that it wasn’t working exactly as I wanted it too

Unhappily, this particular developer is reluctant to change his work habits and instead of implementing a proper numeric field to perform mathematical comparisons with, he came up with this suggestion:

select image_size,
(case
    when instr(lower(image_size),'kb') > 0 then to_number(nvl(trim(regexp_replace(image_size, '[^[:digit:].]', '')), 0))/1024
    when regexp_instr(lower(image_size),'mb|megabytes|meg') > 0 then to_number(nvl(trim(regexp_replace(image_size, '[^[:digit:].]', '')), 0))
    when isnumber(image_size) = 1 then to_number(nvl(image_size,0))
    else 0
end)  as image_size2
...

So, we get two calls to REGEXP_REPLACE to replace the original one, with an extra REGEXP_INSTR to keep things jolly. We still have the ‘strip out all non-numerics’, but by cunningly spotting whether the text being stripped out mentions ‘kays’ or ‘megs’, we can divide appropriately and come out winners. Of course, there is no mention of what to do if the user has entered “0.46Gb” instead of “460Mb”, but why quibble. As the developer puts it:

This will take care of kb (I pulled out TB because no-one would use it.)

Yup, we can just ignore the ‘TB’ (and presumably the ‘GB’ problem) because “no-one would use it”. Never mind that everyone could use it because free-text entry means exactly that: FREE. You can’t know what someone would or wouldn’t use and your code had better be able to deal with all eventualities, or it will break! Let’s just hope some user doesn’t ever a size of “4.7 m. bytes”, shall we?

My developer, however, remains sublimely confident:

This is a temporary but better workaround.

Er, no it isn’t. It’s more complex than ever, and thus no better than before! It seeks to fix the earlier error by ignoring possible conditions, rather than tackling them and thus is, again, no better than before. In fact, it makes assumptions about what things users might type into fields, which makes it much worse than before. It still doesn’t use a number field to do mathematical computations, which is the fundamental problem here.

So, to sum up.

  • A developer performs a mathematical computation on a field, not noticing it’s text;
  • When the inevitable data type conversion error pops up, his code workaround inflates image sizes by a factor of 10;
  • When I point this out, I am told I’m wasting his time and that the code does ‘exactly what I want it to do’;
  • When I re-point it out, I finally get acknowledgment that the logic flaw was not, after all, ‘exactly what I wanted’.
  • But instead of fixing the data model (”very, very expensive”!) we simply get more complex code than before -which still fails to deal with the logical fundamentals at all.

The DBA’s job can seem ever thus: to annoy and frustrate developers who think that their latest piece of whizz-bangery is ‘exactly what I want’ by pointing out that it probably isn’t (and definitely isn’t what the client who’s paying for it all wants!)

I must say, the old saw about DBAs and Developers being at each others’ throats is just that: a lame old joke with little truth in it. I have always got on fine with all the developers I’ve worked with, though I have seen notes of frustration in their eyes when I check their code for things I might not like instead of just rolling it ‘on trust’! My job is to slow them down, to some extent; or at least to act as a sort of ‘chamber of review’, a House of Lords (or Senate) to the developer’s House of Commons (or Representatives) -and until very recently, I would have said that all the developers it’s ever been my privilege to work with have recognised that, so there have been no major dramas.

But I do now see that there is at least one developer who will (apparently) always see the DBA as a problem, rather than a part of the solution.

Just another little cross for the DBA to bear, therefore…

Update: I’ve reluctantly agreed to another developer-proposed workaround. We’ll change the web page that is used to access this particular field. Users will now select entries from a list of purely numeric values displayed in a combo-box. The letters ‘MB’ will be hard-coded on the web form so users know what their number selection ‘means’. Only numeric data will therefore get sent to the database (though it will still be stored in a VARCHAR2 column). This effectively means a free-form text field is no longer free-form; it also means we’re pretending that a text field is actually a numeric one. But we are also not changing the structure of the table and thus potentially breaking the 1001 reports based on it -at least, not until the business makes an informed decision later on whether or not to go down the route of fixing the fundamentals. I can’t say I’m happy about it, especially because ‘making a decision later’ usually turns out to mean ‘forgetting all about it’… but at least we get rid of some distinctly iffy-looking regular expressions in the meantime.