Okay, so you may have noticed that FATE has 3 more machines/platforms from which it is actively accumulating build/test results: FreeBSD/x86_32, DragonFly BSD/x86_32, and Linux/AVR32 (thanks to Michael K. and Måns for running the FATE script on those platforms). I was wondering when a volunteer would step forth to continuously run FATE on some BSD platform.

But the FATE main page is now unbearably unwieldy. When I first put FATE into service, the entire front page was dynamically generated using some horrifically unoptimized queries. A little over a year ago, I rushed a highly naive caching mechanism into production to address this problem. The way it operated was as follows (you might want to be sitting down for this): From a cron job run every 15 minutes, run a Python script that connects to the database server, performs all the queries for fetching data for the main page, and builds a file called main-page-cache.php which is then transferred to the server where it overwrites the old copy. The main index.php script driving the site simply includes the file. Okay, don’t all go contributing this to The Daily WTF at once. Hey, it has performed rather quickly for the last year or so. But it also explains why I couldn’t modify it very easily to allow arbitrary sorting.

Fun fact: At this point, index.php did not strictly need to connect to the database in order to simply render the main page. However, I quickly learned to do a no-op database connection anyway; without it, the end user’s browser would cache the output of the page. I’m sure there must be better browser cache control mechanisms available.

Until I switched web hosts recently, I had to run the Python script on a local machine and scp the results to the server. This explains why, if there was a power failure at home that outlasted the UPS, the main FATE page wouldn’t get updated until I returned home. That problem is solved now by the fact that the cron job and script run on the same server as FATE. At least, the problem was solved until I had to switch back to my prior web host.

After I deployed the caching system, I wondered about ways I could possibly trigger cache page rebuilds automatically after entering new results. Last autumn, I revised the FATE architecture so that results are received through a PHP script. I started to think that, when a configuration sends results through this script, it could update a new PHP cache data file specific to that configuration. The main script could dynamically include a series of these PHP cache files and sort out the data inside.

Then it dawned on me to store the data in an SQLite database with a single, highly non-normalized table. And just when I was about to go to code on that idea, it occurred to me that, as much as I love SQLite, there’s really no reason I can’t put the cache table straight into the main MySQL FATE database. The big advantages will be: No more up-to-15-minute delay before new results show up on main page; and more flexible sorting of the results on the main page.

So the plan goes like this:

1. Create a non-normalized cache table in the main FATE database that includes config ID, machine ID. OS, architecture, compiler string, latest build ID, timestamp build ID was logged, SVN revision it corresponds to, status of the build, number of tests total, number of tests passed.
2. Create a script that performs an initial population of this table based on the configurations marked active and their latest build records in the database. It will be necessary to re-run this script where there are new configurations added to the database, or when configuration data is updated (most often happens when I compile a new gcc from SVN).
3. Modify the data receiver PHP script so that it properly updates the correct row in the table.
4. Go crazy with the main FATE page. Sorting the data by different fields is as straightforward as an ORDER BY clause in the SELECT statement.

The main page should also send a cookie so that the page “remembers” the user’s last (and presumably preferred) sorting order. That assumes that any of FATE’s users actually browse the web with cookies enabled (doesn’t strike me as likely).

Writing out these ideas is useful for motivating further brainstorms. I just realized that I may as well create a simple method for accessing the latest FATE data via HTTP, perhaps output in a CSV format (no XML, thanks). Perhaps others can think of creative ways to interpret the data and act on it. E.g., maybe someone else can figure out creative ways of sending email and IRC notifications before I can get time to solve those problems.