Phil Factor's Phrenetic Phoughts

The biggest secret?

2008-06-13T12:22:00Z

The first programmable computer was invented by Charles Babbage and Augusta Ada King, Countess of Lovelace.

Charles Babbage’s story is so well known that I don’t need to relate it to you. I also believe that it isn’t completely true. It conceals a secret. We now, of course, are almost certain what part of that secret was.

The first part of the secret was that Charles Babbage broke the The Vigenère cipher some time before 1854. This cipher was the ‘unbreakable’ secret code that had been in use since the mid sixteenth century to transmit military and diplomatic messages. Even the mathematician Charles Lutwidge Dodgson (Lewis Carroll), in 1868, and the Scientific American, in 1917, described the Vigenère cipher as “unbreakable” or "impossible of translation”. The Vigenère polyalphabetic cipher was one of the main codes used by the Russians, and most other western nations, in relaying their military and diplomatic messages.

The value of breaking any code was to keep the fact quiet, so that unsuspecting users would continue to unwittingly reveal their military and diplomatic communications. Once it became known that the code was broken, nobody would use it. As it happened, the major powers continued to use The Vigenère cipher for ten years before a technique for cracking it was published in 1863 by Fridrich Wilhelm Kasiski. Even after that, variants were used until the end of the century. They could be read by the British and American government.

We would probably not even know now that the code had been cracked, were it not for Babbage’s fiery personality. John Hall Brock Thwaites, a Bristol dentist and amateur cryptographer, submitted a "new" cipher to the Journal of the Society of the Arts in 1853; Babbage was stung into pointing out that Thwaites' cipher was merely a variation of the Vigenère cipher, Thwaites then challenged Babbage to break his cipher. To his surprise, Babbage decrypted a sample, the poem "The Vision of Sin", by Alfred Tennyson, encrypted according to the keyword "Emily", the Christian name of Tennyson's wife. Babbage had evidently done it by analysing the cipher text for patterns of a higher order than simple letter frequencies (Doron Swade 2000)

Whenever an important military encryption system, such as the Enigma Code, is cracked, elaborate ways are devised to keep the fact secret. Selective use must me made of the military or diplomatic intelligence gained as not to alert a hostile agency of the fact that their military secrets were known to their enemy. All trace of Colossus, the first electronic computer, was removed, The existence of Room 40 at the Admiralty, which decoded the "Zimmermann intercept" in January 1917, was kept secret for many years.

Babbage, who was an outstanding mathematician, was fascinated by decryption techniques. At some point he must have realised that to decrypt a mass of routine dispatches, each with their own decryption key, the effort was huge, and the time taken intolerable. One could crack a single message, but to scan the mass of routine dispatches of other nations was impossible to do by hand. What was needed was a machine. The weakness of The Vigenère cipher was already widely suspected, but disregard because of the enormous time and difficulty in cracking a single message. What was needed was a machine that could do it repeatedly and rapidly. It was this idea that, one suspects, drove all Babbage’s energies.

Are there any signs that the British government actually made use of Babbage’s technology to decrypt messages? It is difficult to re-read the brilliant series of moves by Britain against the other European nations between 1850 and 1900 in Africa in consolidating the British Empire without wondering if it was more than just diplomatic skill. It is interesting to re-read the history of the Crimean War with the hindsight that the Russians probably held no secrets from us.

It may not have just been Britain that benefited. We now know that the turning point of the American civil war was Union’s access to the Confederacy’s secret communications, even though they were using the Vigenère polyalphabetic code. In one case they learned, by decoding a message, that the plates for Confederate currency were being manufactured in New York. There are, of course, many stories of the carelessness of the confederacy with their codes, and some of these stories may be true, but one wonders whether they were used to cover up the fact that the Union had got access to a rapid and effective decryption system.

The building of the Difference Engine certainly attracted a great deal of government attention. The work was funded by the British government. The Duke of Wellington, the Chancellor of the Exchequer and other members of the government visited Babbage to inspect the work for themselves. It seems a great deal of attention if the purpose, announced by both Babbage and the government, was merely that of calculating logarithmic, and other mathematical, tables. By February 1830 the government had allocated £9000 towards the project

In 1832 Sir David Brewster, in his " Natural Magic" wrote “Great as the power of mechanism is known to be, yet we venture to say that many of the most intelligent of our readers will scarcely admit it to be possible that astronomical and navigation tables can he accurately computed by machinery; that the machine may itself correct the errors which it may commit; and that the results of its calculations, when absolutely free from error, can be printed off, without the aid of human hands, or the operation of human intelligence. All this, however, Mr. Babbage's machine can do, and as I have had the advantage of seeing it actually calculate, and of studying its construction with Mr. Babbage himself, I am able to make the above statement on personal observation”.

In other words, it worked.

Babbage wrote in the "Ninth Bridgewater Treatise, " Early in the year 1833, a small portion of the machine was put together, and it performed its work with all the precision which had been anticipated."

In 1834, when the government had put £17000 into the project and Babbage had put £6000 we read that work stopped on the difference engine, against the advice of the entire scientific community.

Was the British government that stupid? What if the project then went underground amidst a smokescreen of pretended obduracy and foolishness? The tools and drawings that had been developed to build the machine mysteriously disappeared, apparently retained Clement, the contractor, in a dispute over payment. Babbage, we are told, lost interest in pursuit of his overambitious Analytical Engine.

Curiously, Babbage, who had just apparently wasted a vast sum of taxpayers' money, was offered a Knighthood in 1842. In June, 1843, the portion of the difference engine, as it existed, was placed in the Museum of King's College, Somerset House. "The portion of the engine was in order, and was capable of calculating to five figures and two orders of differences, at the rate of twelve or fourteen arguments and corresponding tabular numbers per minute; and neither the number of orders of differences, nor the number of digits, would make any difference in its rate of work”.

We know, of course that the government had a working Difference engine. One of Edward Scheutz difference engines, a copy of Babbage’s, and made by Messrs. Donkin for the English Government, later worked in the Registrar-General's Office in Somerset House. However, it would have been perfectly easy to have manufactured and used several others in complete secrecy without much trouble. After all, the British wartime government managed to construct, use, and dispose of, the first electronic computer, Colossus, without the news ever leaking out.

Could such a thing have been concealed. Well, it was. We didn’t know that Babbage counted code-breaking amongst his hobbies, or that he cracked the ‘uncrackable’ Vigenère cipher until a hundred and fifty years after he did it. What I can’t work out, though is how the Difference engine could be used for assisting in cracking the Vigenère cipher. To solve that puzzle needs a bigger brain than mine.

They call this "bed-work," "mapp'ry," "closet-war."
So that the ram that batters down the wall,
For the great swinge and rudeness of his poise
They place before his hand that made the engine,
Or those that with the finesse of their souls
By reason guide his execution.

Shakespeare, in Troilus and Cressida,’

Refs:

The Science of Secrecy : The Secret History of Codes and Codebreaking. " Singh, S. Fourth Estate ; ., 2000.
The Cogwheel Brain Doron Swade Little, Brown, Marlborough, United Kingdom, 2000

Common Law, and The need for restraint.

2008-06-09T09:57:00Z

I’ll never forget the best DBA I ever appointed, because he thoughtfully taught me an effective technique for removing wheel-clamps from cars. It was after the interview, I seem to remember. We walked together to his car to get a file. There in the boot was some interesting equipment collected together to meet the needs of the urban DBA. He described the technique. I worried slightly over certain aspects. ‘Is this legal?’ He looked at me pityingly, and gave me a stirring speech about our duty to fight oppressive legislation, including the line, quoted from the Magna Carta;

‘No free man shall be seized or imprisoned, or stripped of his rights or possessions, or outlawed or exiled or deprived of his standing in any other way, nor will we proceed with force against him, or send others to do so, except by the lawful judgement of his equals, or by the law of the and.’

Hmm. He’ll go far. I thought. I was right, too; He proved to have an unparalleled zeal for mastering corporate regulations and compliance issues.

The other DBA who proved to be the master of any crisis was once a specially trained riot policeman. Bill changed career when the police force ‘went soft’. His highly trained skills of subduing rioters were no longer prized, and a future as a desk-bound social worker didn’t appeal. A career as a DBA beckoned.

Bill commanded instant respect amongst the developers. He never had to do any more than give ‘That Look’. This is because he was an instructor in martial arts and a champion weightlifter. He could lift the heaviest server in and out of the racks without effort or assistance. All the recalcitrant servers were soon pacified with the ease with which he once subdued over-excited Trotskyites

Once, whilst chatting in the server room, he showed me a grip which could restrain anyone so effectively that even talking, let alone struggling, was impossible.

One dreams wistfully of using this real-life Vulcan Nerve-Grip.

Phil: I gather that the development team has issues over the interface between the database and application layer?

Developer: Well, yes. I believe strongly that we should be using Hibernate, and should have table-level access to the databases so we can ‘persist’ our objects in the ‘data repository’. We can spray un-optimized dynamic SQL all over the database without any clear idea about what is actually being generated and can Nnnngggh!… (thud)
Phil (wiping his hands) Any other clever ideas?

I’ve never had to put either technique to the test. For the exceptional DBAs, the important skill is to plan for every conceivable problem and have a strategy in place to deal with it, even if the strategy involves crowbars and martial arts. For some reason, preparedness for an event makes it less likely to happen.

When the fever is over, and one's work is done.

2008-05-29T09:17:00Z

There comes a time in the life of anyone working with databases when they are struck with a peculiar mental aberration. For want of a better term, it is usually called ‘One True Lookup syndrome’ or OTLS. It is in the same league as the rather more severe affliction ‘EAV disease’ (Entity Attribute Value). It is like measles or Mumps in that it afflicts the sufferer just once and he, or she, is thereafter immune. The memory of the suffering and delirium is retained, however, and the feelings of embarrassment.

The sufferer is easy to recognise. He shakes hid head in wonder, saying ‘This is like such a cool idea, how come nobody has thought of it before, It is like wow! I mean it is so neat’. A classic symptom is that they will sidle up to you to ask how to patent software ideas. We grey-muzzles will shake our heads sadly, with a pitying smile, and just mutter solemnly ‘One true lookup table’.

OTLT syndrome comes about usually when one is forced to create a number of simple lookup tables which contain a code and a name. One gets the sudden flash of inspiration that one could roll them all into one lookup table, with a unique identifier for each row, and a type field, thereby saving the chore of creating and maintaining a number of tables. EAV disease, like OTLT syndrome both tend to be the result of pressure from programmers to push their OO designs back into the relational database, like a bathyscaphe in an alien environment . There is loose talk of ‘persisting object data in a data store’, as though one was storing apples in a shed. It also comes about when a development team attempts an ‘Agile’ development before they’ve fully understood the data model.

I remember the first symptoms well. You are under stress: in my case it was a systems analyst whose analysis skills were at about the same level as a dead sheep’s. The database was for an internet system for car dealerships, selling second-hand cars. It was supposed to value your stock, and to enable groups of dealers to supply a punter with the exact car he was looking for. The Systems analyst was making very heavy weather of understanding the business processes of the Dealers. He kept changing his mind, yet wanted a system that he could demonstrate to the dealers. It suddenly occurred to me that, instead of having a normalised and rational data model, one could store the ‘soft’ attributes of each object in a single table, the ‘Entity Attribute Value table’. One could then accommodate a changing schema without changing the structure of any table.

It all seems so easy. You have an ‘object’ table that stores the metadata of each object stored. If you are severely stricken, you make this hierarchical, so that you can implement inheritance. A swift lookup will tell you what attributes there are for any particular object and what their data type is. If you are heavily into Dynamic SQL, then you’d add rules, constraints and defaults.

You then have one instance table that is shared by all objects. The value is stored as a string, just as it is in SQLite. When a new object is created, it receives a unique identifier, and its attributes are written into this table

With this system, you can make changes to your data structures ‘on the fly’ without any apparent need for a-priori database design. You then will wish to record the relationships between your objects. I popped in a relationship table. One could then do fairly complex queries with remarkable ease. In my case, it would be ‘how many different models of car were manufactured by Chrysler in 2003’? , what is the current value of all the cars on the various forecourts?’ or ‘What Ford estate cars are currently for sale on any of the forecourts.’

I loaded the system with all the current data from ‘Glass’s guide’, so that I had all the necessary information for all the cars that had been in volume production for the past twenty years. Everything worked fine. I created test data by inventing several subscribing dealerships, and filled their imaginary forecourt with many imaginary cars. I then created an application interface based on stored procedures. I felt really pleased.

The application programmer who’d been assigned the task of creating the Internet site hated the interface. He was used to direct SQL access to tables via Cold Fusion. His first step was to create a scrollable listbox containing the details of all the cars on the forecourts. At first, he claimed that Cold Fusion didn’t support stored procedures: then he decided that my stored procedure wouldn’t work. After I’d tired of trying to explain how to go about it, I created a view for each object, created dynamically from the Object table every time a change was made. He was much happier then but decided he wanted to update the ‘Table’. The view wasn’t updateable. I wrote a stored procedure and showed him how to use it. He began to feel resentful that his programming knowledge had been ‘shown up’ and began to drop hints around the office that the database was no good.

For the Systems Analyst, who was struggling to explain the slow progress he’d made in coming up with the business and process model, the sudden rumour of problems with the database came as a sudden unexpected lifebelt. When pestered by the project manager, he put on his best ‘Mr Sincerity’ face and reported that it would have all been finished had it not been for the confusion caused by Phil Factor’s wild and wacky database design.

Things were getting difficult. A string of anxious managers demanded that I explain the database design to them. As they had no grounding in either object methodology or relational databases, this was always going to be a doomed mission.

It must have been around the time that I devised a meta-language to describe the objects and their relationships, that I had a sneaking suspicion that all I’d done was to write a database system in SQL Server. My tables were beginning to look eerily like the system tables in SQL Server.

Another problem loomed. Checks, constraints and rules provide the bedrock of maintaining the integrity of data. It is really tempting fate to say that, if there is an interface based on stored procedures, then bad data never gets into the system. One law of Relational Databases is that, if you put in all the necessary rules, checks and constraints, you never see bad data, and if you don’t, then it somehow insinuates itself in by some supernatural means. If you have mixed entities in one table, then the business of implementing these checks becomes difficult.

I was just reaching the point of crisis with the database, when I was facing the decision point of replacing my beautiful, elegant, complex model with something more conventional, that my guardian angel caused a buyer to appear who wished to purchase the system. My database had served its purpose, which was to provide a slick demonstration of a system before the hard work of understanding the real business processes had been done. The vision opened the purse-strings, and I bade farewell to my splendid database system. I did not pine for it, since the fever of OTLT and EAV had passed.

TSQL Spam-killer

2008-04-11T06:53:00Z

It's very refreshing to take a break from a tedious bit of routine code to try out something unusual or tricky. Sometimes one can be quite startled by finding out how easy it is to do in TSQL.

Jonathan Snook's blog is my favourite "developer's blog" of all. It is packed with ideas. I was reading it the other day and came across How I built an effective blog comment spam blocker. This, I thought, was great fun, because it told you how to build it but didn't give you the code. The algorithm scores a string according to how likely it is to be spam. For everything in a comment that the filter likes like, the string gets a point. For everything it don't like, the string loses points. If the string totals 1 or higher, it lands on the site as a valid comment. If it scores a 0, it's set for moderation, If it's below 0, it's marked as spam. Some of the things that score negative are rather amusing ( .pl or .cn sites for example) but done from experience.

With Jonathan's permission, here is a TSQL implementation. I've simplified it slightly, to fit it in a blog, by using temporary tables for the word-banks, dodgy URL suffixes and so on. Normally, you'd put these in permanent tables and fine-tune the system as the language of spam changes, without having to alter the code.

CREATE FUNCTION [dbo].[fnSpamScore] (@comment VARCHAR(MAX))
RETURNS INT
AS
BEGIN
DECLARE @links INT
DECLARE @LenComment INT
DECLARE @points INT
DECLARE @hit INT
DECLARE @ii INT
DECLARE @Start INT
DECLARE @Length INT
DECLARE @KeywordLength INT
DECLARE @Starta INT
DECLARE @urls TABLE(url VARCHAR(255))

DECLARE @SpamWords TABLE(spamword VARCHAR(30))
--stock the spam-words table
INSERT INTO @spamwords(spamword)
SELECT 'Levitra' UNION SELECT 'viagra' UNION SELECT 'casino' UNION 
SELECT 'cialis' UNION SELECT 'nude' UNION SELECT 'tramadol' UNION
SELECT 'phentermine' UNION SELECT 'xanax' UNION SELECT 'alprazolam' UNION
SELECT 'amoxicillin' UNION SELECT 'xxx' UNION SELECT 'porn'

DECLARE @commentstart TABLE(word VARCHAR(30))
---stock the comment-start table
INSERT INTO @commentstart(word)
SELECT 'interesting' UNION SELECT 'cool' UNION SELECT 'sorry' UNION 
SELECT 'nice' 

DECLARE @keyWords TABLE(keyword VARCHAR(30))
--stock the keyword table
INSERT INTO @keywords(keyword)
SELECT '.html' UNION SELECT 'free' UNION SELECT '?' UNION 
SELECT '&' UNION SELECT '.info' UNION SELECT '.pl' UNION 
SELECT '.de' UNION SELECT '.cn'

DECLARE @URLStarts TABLE(start VARCHAR(30),offset INT)
--stock the URL Start table
INSERT INTO @URLStarts(start,offset)
      SELECT 'HREF=',5--unquoted
UNION SELECT 'HREF="',6--quoted
UNION SELECT 'HREF=" ',7--I've seen this trick in spam
UNION SELECT 'HTTP://',0--not in an anchor
UNION SELECT 'HTTPS://',0--not in an anchor
UNION SELECT 'mailto://',0--not in an anchor

--Get the length of the comments and initialise things
SELECT @LenComment=LEN(REPLACE(@comment,' ','|')),@points=0,
   @ii=@LenComment,@links=0
WHILE @ii>0--find every URL in the comments and put them in a table
   BEGIN--check for the next HREF, possibly 'quoted'
   SELECT @start=0
   SELECT TOP 1 @start=hit,@keywordLength=offset FROM (
       SELECT [hit]=PATINDEX ('%'+start+'%',RIGHT(@comment,@ii)),offset 
              FROM @urlStarts)f 
    WHERE hit >0 ORDER BY hit ASC, offset DESC

   IF COALESCE(@start,0)=0 BREAK--no more?
   --so we isolate the actual URL in the anchor
   SELECT @Length= PATINDEX ('%["> ]%',
               RIGHT(@comment,@ii-@start-@keywordLength))
   SELECT @links=@links+1,--increment the URL tally
           @Length=CASE @length WHEN 0 THEN @ii ELSE @length END
                                                       --no termination?
   INSERT INTO @urls(url) --add to our URL table
           SELECT LEFT(SUBSTRING(RIGHT(@comment,@ii),
               @start+@keywordLength,@Length),255)
   --and reduce the length of the string we look at past the URL
   SELECT @ii=@ii-@start-@keywordLength-@Length
   END
--How many links are in the body?  if more than 2 then -1 point per link
--			           if Less than 2 then +2 points
SELECT @points=@points+ CASE WHEN @links < 2 THEN 2 ELSE -@links END
--How long is the comment?  More than 20 characters and there's no links 
                                                       --then + 2 points
--				   Less than 20 characters then -1 point
SELECT @points=@points+CASE WHEN @links=0 
       AND @lencomment>20 THEN +2 ELSE -1 END

--Number of previous comments from same ID Approved comments +1 point per
--                                          Marked as spam  -1 point per

--Keyword search in body of comments (viagra, casino, etc.) -1 point per
SELECT @points=@points-COUNT(*) 
          FROM @spamwords WHERE CHARINDEX(spamword,@comment)>0

--URLs that have certain words or characters in them 	
--                                .html, .info, ?, & or free -1 point per
--or URLs that have certain TLDs .de, .pl, or .cn (sorry guys) 	-1 point
SELECT @points=@points-COUNT(*) 
          FROM @keywords INNER JOIN @urls ON CHARINDEX(keyword,url)>0

--URL length 	More than 30 characters 	-1 point
SELECT @points=@points-COUNT(*) 
          FROM @urls WHERE LEN(url)>30
--Body starts with... 	Interesting, Sorry, Nice or Cool. -10 points
SELECT @points=@points-(10*COUNT(*)) 
          FROM @commentStart WHERE CHARINDEX(word,@comment)>0

--Random character match 	5 consonannts 	-1 point per
SELECT @ii=@LenComment
WHILE @ii>0
   BEGIN
   SELECT @hit= PATINDEX ('%'+REPLICATE('[bcdfghjklmnpqrstvwxyz]',5)
                       +'%',RIGHT(@comment,@ii))
   IF @hit=0 BREAK
   SELECT @points=@points-1, @ii=@ii-(@hit+5)
   END
RETURN @points
END

SELECT dbo.fnSpamScore('this is a perfectly legitimate comment that
points out that phil''s code is horribly broken due to him being called
out for a beer half way through writing it.')
--  +4
SELECT dbo.fnSpamScore('Cool. Buy herbal viagra at http:\\DodgySite.cn 
and impress your neighbours.')
--  -7

The system seems pretty effective. If a spam slips through, or a legitimate comment falls foul of the system, you can tweak it very simply . It is in place in the forthcoming Simple-Talk/SSC Wiki which I'm currently helping to build.

As a strange by-product of developing this, I've taken a sudden liking to spam, purely to test the efficiency of the algorithm. Unfortunately, Simple-Talk is a martyr to it, but I can't find a way of plugging this routine into community server. Still, it means we have a vast test-bank of the stuff to check the routine with!

It occurs to me that there must be a number of ways of writing the SQL code to Jonathan Snooks algorithm. What is the fastest and most effective way? Perhaps we should have a 'Lionel-style' competition.

For anyone without Firefox who can't copy n' paste, here is the source file

The Glittering Concrete

2008-04-02T10:01:00Z

I called in to Red-Gate the other day to see Andrew Clarke, the Simple-Talk subeditor. As I arrived, he was excitedly rummaging in Tony Davis's shoebox. This shoebox contains several contributions that time forgot. When Tony forgets one of my articles, it is usually his polite way of saying that it is beyond even his powers of rescue. What treasures lie therein? As we chatted, Andrew pulled out the following gem from an anonymous contributor whose identity is lost in the mists of time. I can see entirely how the healing process of amnesia kicked into Tony's conscious brain, but I feel that, at long last, it should be given life.

It goes as follows...

My Visit To Red-Gate

After all that time using SQL Compare and SQL Data Compare, it was exciting to get invited to go to Red Gate's prestigious International Headquarters in Cambridge, to give a short talk on 'Advanced Paging in SQL Server 2008'.

Ah, Cambridge. What thoughts well up in one's minds' eye. What images are conjured up? The stone cloisters, the glittering spires, musty bookshops, vibrant Cafe-culture, and college chapels. Keen students in scarves and blazers, riding on bicycles, the sound of distant church bells.

I eagerly imagined Red Gate's offices. The ivy clad walls, the oak bookcases, oil paintings of the founders severe in their Victorian dignity, the shambling donnish figures in tweed jackets and bottle glasses clutching well-thumbed books. A few museum cases lit by gothic stone windows.

So it was rather a shock to arrive at the Science Park, on the Milton road, full of concrete and steel architecture. The Jeffries Building was a typical bold, uncompromising, modernist statement in chrome and glass by, one imagines, a little known architect determined to make a name as a radical force in the profession. I stepped up to the door which didn't open. A sign indicated that I should go to a door at the side, where were placed a number of bell pushes and a speakerphone grill. Inside the building, through the glass, I could see a deserted hallway, lightly furnished with 'calming potted trees' , made of plastic, from the Viking Catalogue. Strangely, the fallen leaves in the hall were real.

I may be sneered at as a country cousin, but I know what to do in such circumstances. You press the right button and, eventually, a strange squeaky voice like an owl being strangled comes from the grill. You say your name, there is a disembodied click, and the door opens.

I stumbled across the hall to the toilet. Now, here is a tip for any visitor eager to impress the Red Gate people he has come to see. Be careful with the taps of the basin. Turn the tap on with anything less than extreme caution and a Niagara Falls of water sprays the front on ones trousers in an uncompromisingly modernist way. My trousers were drenched. I stared in the full-length mirror in horror: it looked as if I had just had an episode of severe incontinence.

After hurriedly dabbing myself down with paper towels, I felt ready for the next task: finding Red Gate. No sign visible.

This new task turned out to be easy, however. I simply resolved to follow the first geeky-looking person I spotted. Luckily, I stumbled across a perfect specimen, like a 'Far Side' cartoon made flesh. He wandered across the hall lost in strange esoteric thoughts and led me, inexorably, to Red Gate.

Now, any follower of the old blog of 'Disgruntled Brian' might mistakenly expect the Red Gate offices to be ringing with diabolic laughter as marketing people hatched duplicitous, cunning, plans, the air reeking with sulphur and the crackle of static electricity. Unfortunately the marketing department were all out that day on a team building excercise. Instead, the sunlight flooded through the gaunt steel windows, illuminating a tranquil, meditative, scene as deskbound geeks tapped away thoughtfully at several keyboards and screens at once, as they constructed huge, complex applications

Still dripping slightly down my trousers, I made my way to the stairs at the back of the office to get to the meeting room upstairs where my talk was to be held, nodding amiably at the Geeks. The door closed behind me with a click. I went upstairs. The door on the first floor required a swipe card. No human effort would persuade it to open. It was immovable. I retreated back downstairs. To my dismay, I realized that, to get back in, also required a swipe-card! Trapped in the stairwell. Through the window of the door, I could see the Geeks placidly keying in intricate code. I tapped on the door, but their concentration was complete, and their iPods were on . After ten minutes, a startled geek found me sitting disconsolately on the stairs as the audience for my talk sat stolidly in the meeting room upstairs, talking amongst themselves.

Back in the office, the next task was to get a swipe card so I could move unhindered through all doors. This was given to me by a charming lady who apologized for my predicament. Back up the stairs I went, and presented the card smartly to the offending door, only to be assaulted by a series of beeps followed shortly by a loud ringing noise. I was aghast. The Architect of the building, as an act of vengeance on a humanity he obviously despised, had put two sensors on the door, one of which opens the door, and the other of which sets the burglar alarm. In my haste to get through the door to start my talk, I got it wrong. The alarm had gone off, and as an added bonus, also triggered a buzzer in the local police station. Everyone was very nice about it.

And so it was, that fifteen minutes late, dripping from my trousers, and leaving chaos downstairs as the car-park filled with patrol cars, I burst into the meeting room, to be met by the startled gaze of my audience.

Someone leapt to his feet and shook my hand warmly. 'Did you find the place easily?' he asked affably. I stepped back to shake his hand . My back struck the partition wall of the meeting room. It made a crashing noise like the last trump, and woke a nearby dozing support engineer in the open-plan area outside the meeting room. Various sales people looked up, startled, from their work.

'Oh yes, fine, no problems at all', I replied.

The Technically minded subclass, and the fog of misperception.

2008-02-06T18:45:00Z

I spent several years in a team that advised a large international manufacturing company on their software-purchasing strategy. It always amazed me how far the software companies misunderstood our core requirements, but never really took the time to find out what they really were. All their information about us was gleaned from their own non-technical sales force whose scientific knowledge stopped short of ruling out the existence of fairies. And even these guys talked only to our senior management who understood nothing of the details of what went on in their empires, but were happy to bluff their way through in return for a hearty lobster lunch. The technically minded subclass who actually understood the business processes and the technical details of product development were treated with general contempt. They were usually kept concealed from visitors due, it is said, to their bad-tempered candour and their propensity for blurting out embarrassing details of management mistakes.

One Hardware and software supplier, in particular, based their continued prosperity on supplying the company. We were visiting their development Labs one day when they excitedly guided us into a large room where a number of developers were working on a wonderful and exciting top-secret project. They’d been at it for two years. Basically, it was a system for modelling complex castings on the computer, and putting all sorts of stresses on them to see how the structure would flex; based on finite element analysis. For two hours, expert after expert presented aspects of the application. It would, they told us, save huge sums of money in building prototypes, and test equipment. It would revolutionise our manufacturing processes. At the end, there was an expectant hush, and all eyes were on us to respond. I said that any company who purchased the system would have a handy tool, but that it would be no use to us. There was an amazed silence. I asked, in some alarm, who on earth had they had in mind as customers for the product?. 'Why you, of course', they replied pathetically. We then had to explain that we already had a CAE system that was far more sophisticated than the one that they’d shown which was able to take development of components from concept through to development, prototyping, test, costing, release and manufacture, all from the same basic wire-frame data model. It was designed to allow multidisciplinary teams in different organisations and countries to work together on complex design processes. Their application could never be made to fit into the way we did business.

There were emotional scenes. They’d spent two years, and enormous expense (investment they called it, somewhat optimistically), doing this work with only us as customers. They had created purpose-built graphical workstations. They had relied only on the occasional remark by senior management of our company, but had never thought to penetrate through to the people who were actually doing the work. We left rather hurriedly, and they were immediately on the phone to those managers in our company who had lulled them into proceeding with the project. Their initial ploy in maintaining that we were mere running dogs, incapable of understanding corporate strategy, didn’t wash. In the end, everyone had to admit that this was another project that would bite the dust.

This story is re-enacted over and over again, with different details, in government and industry throughout the world. Almost without exception, the same mistake is made every time. It is not entirely the case that development team doesn’t do their research first, although cutting code is much more fun than understanding in detail what the application needs to achieve. More commonly, they do not have the experience to identify where the real knowledge about business requirements and processes lies. They never penetrate the fog of misperception that exists in every large company.At an intellectual level, it is obvious that IT initiatives have to be fit for purpose, but emotionally, there is always the siren voice saying ‘This time it will be different, we can evolve prototypes and elicit the full requirements along the way; we can re-engineer business processes, rather than to fit with the existing ones.’ It never is different. Sadly, history just goes on repeating itself.

Actionable waffle

2008-02-04T20:08:00Z

One of the funnier malapropisms that have recently emerged from Microsoft’s Marketing arm is the word ‘Actionable’ . You can see how it happened. They wanted a word that sounded vaguely a ‘good thing’ without being too precise. We all like things we can act on don’t we. ‘Actionable' sounds good eh?

‘… to provide IT managers with an actionable set of prescriptive guidelines for improving operations…’

(http://www.microsoft.com/Downloads/details.aspx?familyid=A030626B-C2E4-4D7C-AB75-832D360C86ED&displaylang=en)

‘… Deliver Actionable Insight Throughout Your Organization with Data Mining…’

(http://msevents.microsoft.com/cui/WebCastEventDetails.aspx?culture=en-US&EventID=1032355543&CountryCode=US)

‘… Actionable audience intelligence and effective targeting technologies are essential for marketers.’

(http://advertising.microsoft.com/research/travel-marketing-i)

‘… delivered real-time actionable information needed to help grow a business through search engine…’

(http://advertising.microsoft.com/uk/advertising-events?Adv_EventID=97)

‘…It offers easy inventory, powerful assessment and actionable recommendations for Windows Server 2008, …’

(http://connect.microsoft.com/site/sitehome.aspx?SiteID=297)

Oops. Typically of the purveyors of word salad, they didn’t check their dictionaries first. In this case they got it comically wrong.

Actionable has a definite and precise meaning in both American English and in Queens English that is well known in the Legal Profession. If you say, or do, something Actionable, it means you could be sued for it. To quote from Webster, ‘furnishing grounds for a lawsuit’. www.TheFreeDictionary.com talks of Actionable meaning ‘affording grounds for legal action’, giving the example "slander is an actionable offense", and gives the synonym ‘unjust - violating principles of justice’. The Oxford English Dictionary yields only one meaning: ‘Subject or liable to an action at law. Of such a character that an action on account of it will lie’, and gives examples going back to the seventeenth century. To be fair, recent Websters have listed an ancillary meaning of ‘capable of being acted upon or readily used’, but it may just be a response to a frequently used malapropism. No, if you key the word ‘actionable’ into the Wikipedia, you are straight into the realms of Litigation.

So sit back and read the marketing blurb with a lighter heart and a new cheerfulness. They are, unintentionally, telling you that the stuff they’re trying to sell you will get you sued!

Microsoft Boy announces his School Homework

2008-01-27T17:21:00Z

Continuing in our series of attempts to imagine how Microsoft Marketing people relate to their fellow men outside work, we give you a glimpse of Microsoft Boy at school, before the start of his splendid career at Redmond.

Scene: The History lesson in school. The teacher wearily calls Microsoft Boy to his desk to try to discover where his homework is.
_______________________________________________

Teacher:

"Well, young William, (looks over his glasses severely) where is your homework? It should have been handed in today, I'm afraid."

Microsoft Boy:

(with a smug ingratiating smile redolent of sincerity) "The past week has been an amazing time for the me as I geared up to announce the delivery of my essay. The response to my announcement from friends and parents has been overwhelmingly positive – in fact, even my aunt Edith wants to read it. What is catching users' eyes? Legibility, correctness, conciseness….the list goes on and on. Simply put, this history essay is a significant release for me – one that builds on all of the great things that I was able to deliver last year in the Lower fifth. I see it as a critical step forward for my academic life here, and the foundation of the broader vision for my school career. Based on what we are hearing from people who have seen the current version of my essay, it seems that everyone agrees."

Teacher

(impatiently) "Well, that may be the case, but you haven't actually handed your work in. Where is it for heavens sake? The others have managed to hand their work in!"

Microsoft Boy:

(earnestly) Not surprisingly, one of the top areas of focus for me is always to deliver high quality homework, and in a very predictable manner. This is vital for my dazzling school career – which is why I've frequently discussed my goal of releasing my history essay within three months of the last one. I am on track to reach this goal. (folds his arms with a smile of achievement)

Teacher: (whilst rustling about, searching on his desk)

"I don’t see it, I really can't find your essay on my desk. It was supposed to have been handed in today."

Microsoft Boy: (sensing something not quite right in his relationship)

"To continue in this spirit of open communication between us, I want to provide clarification on the roadmap for my essay. Over the coming months, you, and the other teaching staff here can look forward to significant milestones in the delivery of my homework. I am excited to deliver a release candidate of the essay in a month's time, at Scout Camp, with final Release of the entire homework expected in another couple of months. My goal is to deliver the highest quality History essay possible and I simply want to use the time to reach the high bar that you, my teacher, has set."

Teacher: (Head in hands, dispairingly)

"I really don't understand. Have you handed in your homework or not?"

Microsoft Boy

"I have not, in any way, changed my plans for launching the essay today. What I have done today is to announce to you the delivery of my essay, and I'm proud to have met this target. Please keep the great feedback coming and thank you again for your ongoing support of my 'best-in-class' academic work!" (Proudly walks out of the classroom)

The Three Little Pigs in Java

2008-01-22T14:19:00Z

Once upon a time, in a land .far off to the west of Krakatoa called Java, .there lived three little pigs. They were called by the unique identifiers John, jOhn, and johN. The first little pig was known to his devoted parents as piglet[0], the second as piglet[1] and the third as piglet[2].

Unfortunately the pigsty was too small, due to inadequate requirements-gathering at the time of construction, a familiar anti-pattern. The old sow said, ‘Now my dears, upon reflection, we seem to have a scalability problem. We need therefore create a distributed architecture. I have no inheritance, so you must to go off into the woods and build your own homes.. But, whatever you do, make them resilient and beware of the Big Bad Tester, for he will huff, and puff, and blow them away’.

So the three little piglets all went tearfully out onto the road to look for a suitable platform on which to base their architecture. The first little pig met a man with some java beans.

“Would you like to buy some of my beans in return for a cow?”

“No fear, My mother told me the story of a little pig called Jack who bought some of your beans, had a big scalability issue with the resulting tree structure and had Gigantic problems as a result.’

Then the little pig, piglet[‘John’] ( or piglet[0]), met a man with a cart of straw. ‘This looks to me to be a component that can be manipulated visually as a builder tool.’ He said gleefully. I can make a nice property with this! So he bought the straw, and extended the house base class with straw, using late bindings, and settled down contentedly in a local field, relishing the persistence of his house object.

Then along came the tester, who said ‘Is this house of production quality? The little pig said, ‘Of course, lets’ ship it, by the hair of my chinny chin chin’.. So the tester smiled with glee and caused an unhandled exception by huffing and puffing and causing an array-out-of-bounds. Piglet[0] wiped the straw from his hair, exclaiming ‘It must be a hardware fault. I haven’t touched that module in weeks’. Perhaps he should have used curly braces.

The second little pig wandered down a lane and came across a farmer hauling a load of Sticks I shall build a fantastick house! He cried, and bought the sticks from the farmer. The house class had multiple constructors, and he overrode the wall methods for sticks..

After great effort, he sat down in his house of sticks, contented. At that moment the big bad tester knocked on the door. ‘Little Pig, Little pig, let me come in.

“No, No, said the little pig, By the hair of my chinny chin chin, I will not let you come in. The door is a critical section with an exclusive lock.’

“But we have to check the house’s resilience.” Pleaded the Tester,”It needs to undergo stress-testing”’.

“..but I’ve used Agile Methodology in developing this house!”, the pig expostulated.

But the Tester just laughed. He then huffed and he puffed, causing a null reference exception thrown up to the top level thereby causing premature object decomposition.

Piglet[1] ran squealing from a pile of sticks exclaiming “I thought I fixed that. Somebody must have changed the code”.

The third little pig, piglet[2], decided that his two brothers had practiced a number of antipatterns, and determined to select a resilient architecture with bricks. As you generally hear far more about the successful developments in Java, I suspect I have no need to go into the details because you'll have already been told them. You will know that the Testers even climbed down the chimney to try to cause the system integrity to fail. All to no avail. The Big Bad Tester had an unfortunate accident whilst regression-testing the roof Struts, and the third little pig lived happily ever after.

A great programmer.

2008-01-18T17:01:00Z

I sat back in my chair the other day, and my eye happened to fall on a bookshelf with some old A4 binders in it. The brain works in mysterious ways, well mine does certainly. I'd been brooding recently on the thought that the real breakthroughs in software are all made by individuals, not teams. Teams, by their very nature, try to perpetuate the status quo, not change it. I was struck with the strange urge to see what was in those old A4 binders.

A dimly remembered individual had, in 1980, carefully and neatly bound the documentation and source code of a program called STOIC, written in 1977. That person was me. It came flooding back, the memory of that wonderful craftsmanlike code that I used to pore over. It taught me so much. STOIC was a language, like a rationalised and tidied dialect of FORTH. STOIC code was written in RPN, and most of the language itself was written in STOIC with just a small kernel in machine code. The program had been written on a Nova Minicomputer and Cross-assembled for the new 8080 chip, ancestor of the current Intel and ADM range. It was written by John Sachs the MIT and Harvard Biomedical Engineering Centre in Boston, (part of the Health, Science and Technology Division) in February 1977. It had a built-in Operating system, assembler, floating-point package, interrupt handler, and display editor. It was a work of brilliance. I wondered what happened to John Sachs, programmer. Is he now a grey bearded professor at MIT? I had the urge to contact him and thank him for the great help he gave me when I was a novice programmer.

A quick 'google' brought up the fact that the famous programmer who created the first modern spreadsheet, Lotus 123, and thereby launched the first PC revolution, was also called Jonathan Sachs. (Visicalc was the first spreadsheet as such) Single-handed, he had worked for almost a year, crafting it from machine code. When launched, it was bug-free and ran on any old PC. It sold so fast that it caused a shortage of ring-binders for documentation. Jonathan Sachs had been a co-founder of Lotus Corp. Sachs, left Lotus in 1985, less than three years after the program was launched. He now develops photo-editing software such as PictureWindow for his own company, Digital Light and Color.

I was curious. Could this be the same programmer who, five years after writing STOIC, went on to write what is generally acknowledged to be the definitive PC application? I was so intrigued that I asked him via email. The reply came back 'Yes, I am the same one'. So, thirty years later than I should have done, I thanked him for writing such wonderful code as was in STOIC. I read all through it again. It was just as great as I remembered it.

It set me thinking that behind many of today's applications lay stories of individual feats of creativity and intellectual endurance. It would be a great idea to try to tell some of these stories and dispel the myth that truly great applications could ever be written by a committee.

Taking the Soup

2008-01-02T23:04:00Z

A while back, I had to review a book by an ‘award-winning’ author. It was an excellent book. I phoned the writer to get some background information and to try to ascertain whether my assumptions about the author’s background knowledge were right. ‘That award you won… What was it?’

‘Oh, just one of those awards you know, like the Whitbread, or the Booker.’

‘…and its name?’ I pressed, baffled by his vagueness,

‘Well, if you must know, I awarded it to myself,’ He confessed.

I remarked on his initiative.

‘Yes, the established literary cartels were slow in coming forward to recognise new and interesting talent, due to their entrenched self-interest, so I pre-empted them and awarded myself a literary prize. It has done wonders for my book sales and everyone seems impressed. Even my mother refers to it to her friends.’

We moved on to other topics, but before we ended the call, he said ' You know, there were two strange things about that award, …Firstly, after I awarded it to myself, I felt oddly elated, as if some august academic body had suddenly realised my true worth as an author and had strained every sinew to ensure that my talent was acknowledged. ' ’

Pause

‘… and what was the other strange thing?’

‘You are the first person ever to have asked me precisely what award it was that I’d won. Everybody else has just taken it for granted.’

‘I work in IT. It makes one cautious of trusting qualifications and awards.’

We parted on excellent terms. He recently sent me his latest book, a bestseller by an award-winning novelist.

Awards are tricky things to get right. For an award to be universally supported, it has to have a transparent and scrupulously independent selection process. Nobel, Booker and Whitbread occasionally get it right, but then you’d be very hard pushed to find a Nobel prize-winner for Literature in your bookshelf or made into a Movie. Elias Canetti? Toni Morrison? Pearl Buck? Heinrich Böll?

Professional awards in general are a minefield. The checks and balances that have to be put in place in order to make the selection and nomination process fair and visible are Byzantine in their complexity. The election processes are always constructed to prevent any hint of a possibility of unfair influence.

I find the Microsoft MVP ‘award’ troubling. This is nothing to do with the MVPs themselves. I have many good friends who are annually, and with excellent judgement, awarded MVP status for their excellent work. People who are MVPs seem to know their specialised subject well. For all you know, I may even be an MVP myself. That has nothing to do with the fact that the MVP ‘gong’ itself is an insidious thing. The M at the beginning is enough to impress anybody’s aunt. MSc, MBE, MBA, MVP. It looks like it is a Master of something or other. It masquerades as an academic or professional award. Traditionally, MVP was the ‘most valued player’ in a baseball team, chosen by the rest of the team. In IT, the MVP is promoted and funded by one commercial organisation, and the final selection of MVPs is made by employees of that organisation in closed session. The M either stands for ‘Microsoft’ or ‘Most’, depending on who you ask.

‘Each year a panel of Microsoft employees reviews the contributions of each nominee for quality, quantity, and level of impact on the technical community.’ (from the MVP website)

The award is given annually, and a recipient cannot claim the ‘qualification’ after the year is up unless re-nominated and re-selected, the ‘Lord giveth and the Lord taketh away’. It is, I suppose, a good way of ensuring ‘the expectations of courtesy, professionalism, code of conduct, and adherence to the community rules’. However, ask yourself this, could it also be a way of pitching the loose canons overboard?

Imagine that Microsoft has decided to take yet another slice of the IT market, in an area previously dominated by another player. Let us, for the sake of argument, imagine that Microsoft has produced a product, we'll call it Silverlight for the sake of argument, which takes on Adobe's Flash. This isn't our concern. We can look at the two offerings, and look at Adobe AIR, the commercial response to Silverlight, and make a judgement, based on merit, on how the two products fit with requirements. However, as an MVP, can one then blog on the superiority of the Adobe AIR package without the nagging thought that the panel of those Microsoft employees are going to make sucking sounds through their teeth, shake their heads sadly, and move onto the next nominee the next time your name comes up?

‘Eloquent, yes, but is he really singing from the same hymn sheet? Is he really the sort of Microsoft Valued Professional we want?’ The influence of Marketing has an insidious way of penetrating ‘Chinese walls’.

In reality, Microsoft would never actually have to ‘pull the choke-chain’ at all. There will always be that small lingering doubt in the mind of the MVP who wants to stay an MVP. ‘Would that upset them?’ It is the whole basis of the award which is wrong rather than the way it is implemented and run.

I realise that the vast majority of people who receive the award have put good selfless service into assisting the community of people who are using Microsoft products. Nobody is saying that the award is equivalent to Reginald Molehusband’s CDM. Can, though, anyone who puts the letters MVP after their name be, in addition, accepted as an independent industry expert, or do the three letters just mean that the person has 'got the ring though the nose’?

Naturally, the answer is ‘The thought of Microsoft’s reaction makes not a hoot of difference in what the average MVP will do or say. The letters MVP mean that the recipient has worked hard to support the community, rather than act as an unpaid cheerleader for Microsoft. The award merely shows that the recipient knows the particular Microsoft product he got the award for, and has used it to help the community of users: nothing more.’ Why then dress it up to make it look like more than it really is? Sadly, perception is everything, and it could well be the perception of the IT indiustry as a whole that you’ve ‘Taken the Soup’.

A temporary inconvenience

2007-11-12T11:08:00Z

Here is an interesting interview question. You have a PC in front of you, switched off, with a database on it. You don’t know any of the passwords and you want to get at the database. Is this possible? If so, then how?

This happened to me recently, due to a freakish accident concerning me reacting stupidly and impetuously to the death of a domain. I was left with a development database I had to get to urgently, (Backup of development work? Of course, on the local hard disk!) and I had no idea of any of the passwords. Normally, I'd never have bothered to find out by trying.

In my case, it was ridiculously easy, once the feelings of panic had subsided. I just downloaded a utility from the internet that blanked out all the Windows passwords. Because the BIOS was not secured by any password, I could boot up with a CDROM, blank out the Windows passwords, and then, once more, I was god in this little PC world. At first, I stopped the SQL Service and copied the MDF files off and re-attached them to another SQL Server. Then I realised that I had gained admin rights to the database anyway through a local account. If all else had failed the backups weren’t encrypted anyway, so I could have got at them without any bother.

I was just chucking to myself over a cup of coffee about my foolishness in getting in a panic about losing the database. It then occurred to me how wise it is to treat server rooms like forts. I could immediately think of several commercial databases with unsecured BIOSs.

The problem with Database Developers and DBAs dealing with security issues at this level is that they have the wrong mindset. Finding security loopholes is a job for a different sort of thinking. The best security experts I know have a built-in malicious streak. They are like hunters that thrill to run down, and kill, a beautiful wild creature.

In the meantime, we innocents carry on believing that intruders cannot get at our data by gaining admin rights to the database. I realise that most production servers are properly nailed down and their server rooms secure and monitored, but for the rest of us, maybe it is time to think again.

On the trail with the Cowboy Coders.

2007-10-24T10:43:00Z

One of the signs of increasing age in the IT industry is that of 'déjà vu'. New things that are laboriously explained to you ring all sorts of bells. The past flashes before one's eyes.

I visited a company developing Internet-based applications the other day. They were proud of their progressiveness and explained to me that they developed database systems using the radical new Agile XP (Extreme Programming) methodology. Wow, I thought. That must have impressed the shareholders. Perhaps they don't realize that Agile XP is known in the industry as 'Cowboy Coding'.

I feigned ignorance, which is not usually hard for me to do.

'Well,' said my genial and pleasant host, 'one of the great things about Agile XP is that one tests every part of your code as soon as you've written it and you don't go on to the next bit until it has been thoroughly tested. Flaws in the system are easily communicated by writing a unit test that proves a certain piece of code will break.'

'Gosh! You mean you test your code rather than just write it?'.

'Ingenious isn't it. When writing code, the unit test provides direct feedback as to how the system reacts to the changes one has made. If, for instance, the changes affect a part of the system that is not in the scope of the programmer who made them, that programmer will not notice the flaw. There is a large chance that this bug will appear when the system is in production.'

'Funny how nobody has ever thought of this before.' My face goes red when I'm desperately trying to stop myself guffawing with laughter. What I love best about working in IT is its rich unintended comedy. My host misunderstood my visceral difficulties as excitement

'Because you can never be certain that what you coded is what you meant XP uses what it calls 'Unit Tests'. These are automated tests that test the code. The programmer will try to write as many tests he or she can think of that might break the code he or she is writing; if all tests run successfully then the coding is complete, and the programmer can then go on to develop more code.'

My eyes bulged. My face flushed red.

Seeing how interested I was he proudly produced an elaborate Java-based module he'd written that provided a test harness for SQL queries and procedure. 'It is great, you can change the parameters in the query (Klop Klop Klop on the keyboard) just type in what results you expect', (Klop Klop Klop Klop), 'and then Run the test harness' (Klop Klop Klop Klop Klop Klop)

Various windows flashed on the screen in front of me, full of inscrutable commands. It was all very 'Seventies'.

Finally a message popped up from the depths saying 'OK'. He was so proud; it could have been his baby.

'Look, all we have to do is to change SQL code a bit in order to simulate an error and you'll see what happens!'

(Klop Klop Klop Klop Klop Klop)

Wow. An error message popped up saying there was an error in the code.

'To think that, for all these years, we've been doing it so stupidly' I sighed, shaking my head in mock-regret. I'm afraid I wasn't able to show him the way that a SQL Programmer like myself would go around doing the same thing in their humble rudimentary way. I hope I explained enough to suggest that that perhaps there has been some sort of primitive thought before the genesis of Agile XP.

Before you write code, you write a test-harness. (it is often best to get someone else to do it, preferably someone with malice in their soul). When you write the piece of SQL code, such as a function, you append the test harness for unit-testing purposes. In some places I've worked, you'd be given a rough time at code-review if these were missing or inadequate. Here is an example…. (normally, you comment out the test harness when the code is 'put to bed' and the GO moved to the end, so that the harness stays with the code, or you can, in SQL Server, save it as an extended property.)


-- =============================================
-- Author:		Phil Factor
-- Create date: 
-- Description:	Reverse Charindex. Gives the index
-- INTO the string OF the LAST occurence OF the 
-- substring
-- =============================================
ALTER FUNCTION [dbo].[rCharindex] --reverse Charindex
(
   -- Add the parameters for the function here
   @needle VARCHAR(80),    --the substring
   @HayStack VARCHAR(8000) --the string
)
RETURNS INT
AS
BEGIN
   IF @needle+@Haystack IS NULL RETURN NULL
   IF CHARINDEX(@needle,@haystack)=0 RETURN 0
   RETURN COALESCE(LEN(REPLACE(@haystack,' ','|'))
             -CHARINDEX(REVERSE(@needle),
                       REVERSE(@needle+@haystack)
                       )
              -LEN(REPLACE(@needle,' ','|'))+2,0)

END
GO
DECLARE @Test VARCHAR(80)
SET @Test='First test'----------------------------
IF dbo.rcharindex ('this','This can NOT be a thistle ')
   <> 19
   RAISERROR ('Failed the %s',16,1,@test)
SET @Test='Second test'---------------------------
IF dbo.rcharindex ('pr',
  '  May the lord preserve me from the priests of the IT industry  ')
   <> 37
   RAISERROR ('Failed the %s',16,1,@test)
SET @Test='Third test'---------------------------
IF dbo.rcharindex ('l',
  '...who maintain an evangelical following.. ')
   <> 35
   RAISERROR ('Failed the %s',16,1,@test)
SET @Test='Fourth test'---------------------------
IF dbo.rcharindex (NULL,
  '...by waving their hands a lot.. ')
   IS NOT NULL
   RAISERROR ('Failed the %s',16,1,@test)
SET @Test='Fifth test'---------------------------
IF dbo.rcharindex (' without their ties on.',NULL)
   IS NOT NULL
   RAISERROR ('Failed the %s',16,1,@test)
SET @Test='Sixth test'---------------------------
IF dbo.rcharindex ('','')
   <>0
   RAISERROR ('Failed the %s',16,1,@test)   
SET @Test='Seventh test'---------------------------
IF dbo.rcharindex ('','and looking sincere')
   <>0
   RAISERROR ('Failed the %s',16,1,@test)   
SET @Test='Eighth test'---------------------------
IF dbo.rcharindex ('but it adds to life''s rich humour','')
   <>0
SET @Test='Ninth test'---------------------------
IF dbo.rcharindex ('Klop','so perhaps one should be tolerant')
   <>0
   RAISERROR ('Failed the %s',16,1,@test)   
SET @Test='Tenth test'---------------------------
IF dbo.rcharindex ('Klop','KlopKlopKlop ')
   <>9
   RAISERROR ('Failed the %s',16,1,@test)   
SET @Test='Eleventh test'---------------------------
IF dbo.rcharindex (' Klop ','Klop Klop Klop ')
   <>10
   RAISERROR ('Failed the %s',16,1,@test)   
SET @Test='Twelveth test'---------------------------
IF dbo.rcharindex (' ','      ')
   <>6
   RAISERROR ('Failed the %s',16,1,@test)   

SET @Test='Thirteenth test'---------------------------
IF dbo.rcharindex (' ! ',' ! !!!  !  !!!     ')
   <>8
   RAISERROR ('Failed the %s',16,1,@test)

There are many cleverer ways of doing it but I blush to think of the stupid bugs that this sort of system catches, that slip through casual testing.