My annual new-year’s present to the SQL Server community is the Philip Factor Almanac of Prophesies for the coming year. Along with this blog will be an article which explains how it all came about, but here I’ll explain a bit about the technical side.

Firstly, here is your Almanac, or Horoscope for this coming year

This is almost entirely a SQL Server application, with a very thin layer of PHP and ADO. The SQL Server source is too long to show in its entirity here….

…but can be accessed here

The code for this is very similar to last years’ annual present, Phil Factor’s excuse generator. In fact, all I did was to allow macros to be more than one character, because I was having difficulty keeping track of them.

At the heart of the Prophesies is a function that returns one of a list of phrases. (ufsOneOf) This is called by a function that contains a bank of cliches and commonly-used sentence components, according to the macro requested (ufsSelectRandomPhrase) which is, in turn, iteratively by another function (ufsWaffle) This latter function doggedly looks through a string pulling out the macros and getting them interpreted by ufsSelectRandomPhrase.

ufsWaffle is fed a series of lines from a datanase which contains a number of sentences.

This system is capable of a lot of elaboration. I’ve used it for substituting values into text from a lookup table for sending out emails, as well as generating plausible random text.

You might have thought that it would be all a lot easier using recursion. It may be that the code would be shorter, and possibly easier to follow, but it turns out to be rather slow.