Upgrade glitches - phpRecipeBook

Recently, I upgraded my server from Slackware 13.0 to Slackware 14.0, and ran into compatibility issues with a few of the web applications served from that system. PhpRecipeBook, a recipe manager application running under the Apache web server and PHP, failed spectacularly.

My very backlevel installation of PhpRecipeBook was not compatible with the new level of PHP, and I lost access to the handful of (to me, important) recipes that I had collected over the years. The developers of PhpRecipeBook provide no upgrade path from the level I run at to any supported level of the application. To install the current level (or any level that there is a migration path to the current level from), I have to wipe out my existing recipe collection and start from scratch.

But, before I do that, I need to recover the existing recipes, in order to input them (again) into the new, upgraded recipe book.

A Programmers Strategy

Perhaps it would have been "saner" to develop something in PHP. Certainly, in retrospect, read-only web access to my recipes would have been simpler to format. But, where's the challenge in that? And, I wasn't interested in preserving web access as much as getting a sanely-formatted offline, printed, copy of all the recipes before I wiped the database. And, so, I decided to write a C program to generate printed version of the entire recipe book.

While I have experience in writing database access programs, I have none in writing database access programs in C, for MySQL. The tools that I took for granted in my professional life as a mainframe developer don't exist in the hobbiest world that I now occupy. So, while I knew the theory behind my goal, I certainly had to learn the practice.

So, my strategy became to

• determine the data relationships between the various tables and rows in the existing recipe book database
• plot out the appropriate SQL queries to retrieve the details of every recipe, and
• develop a C program that used the mysql API to retrieve, format, and print the recipe data in a clear, human-readable form

My tools were my experience, some MySQL manuals, a MySQL mysqldump of the existing, populated database, and a Slackware development system with MySQL installed (but not configured or enabled).

Setting up the Development database environment

First, I needed a development MySQL environment to work in.

Since I had MySQL already installed on the development system, I only had to configure and activate it. First, I followed the MySQL installation instructions to run the MySQL internal database setup process.

With the initial, internal databases initialized, I moved on to the MySQL configuration. I crafted a new configuration file from the supplied example, and (while not entirely necessary) activated the innodb database support.

After completing the configuration file, I temporarily started the MySQL database manager. MySQL came up without error, so I knew it was safe to set the MySQL startup runscript up for automatic execution. Security concerns (and the installation document) suggest to change the default MySQL "root" password, and to add a new user for development purposes, which I did at this point.

Finally, using an SQL backup of my production databases, I created the development databases, loaded my recipe data into the new tables and granted access to them to my development user. I now had a mirror of my PhPRecipeBook recipe database to develop against.

Finding The Data

I examined the existing PhpRecipeBook DDL ("Data Definition Language") and data dump, and determined that, for my purposes, I could limit my work to a specific handful of the existing tables. Some had been populated with reference data, and some with recipe data; in all, I isolated my needs to three recipe data tables and five reference data tables.

The prime recipe table used foreign keys to connect individual recipes in the table with their ingredients and common information. This was my startingpoint; I would process this table row-by-row, using the related information in each row to provide the details for each recipe in turn.

From this information, I developed four SQL queries to collect the relevant recipe data. In retrospect, I only needed three queries; the fourth query added a bit of programmer convenience at the cost of some run-time inefficiency.

Developing the Program

I had been trained as an "old-school" programmer; writing top-down structured code. For this effort, I used those "old-school" skills, and developed a program that would, for each recipe in the database, print some recipe summary information, the list of ingredients, the directions, and a list of related recipes. It wasn't a pretty sight; I wrote single-purpose, bulletproof, unoptimized code, and followed the API requirements as close as I could. Within a couple of hours, I had a program that successfully recovered all my recipes, writing them out in a human-readable form.

I had considered generating some sort of structured, machine-readable "interchange" file, but the documentation on the up-to-date PhpRecipeBook only mentioned a very basic format ("Meal-Master"), for which documentation was very lacking. The newer PhPRecipeBook applications have an export and import function, but do not document the export/import file format at all. So, it looks like I'll have to stick with human-readable data, and rekey all my recipes.

I spent a few more hours polishing the program and it's output, until I had something that would generate "cookbook quality" recipes from my old database. This included de-HTMLizing some of the recipe text fields, rejustifying direction lines to fit the page, and re-interpreting ingredient amounts so that a cook could understand them. A couple of commandline tweaks would take the output of my program and build it into a nicely formatted PDF file.

And, that was that. I've got my recipes back, if only in printed form. And, I've learned something about developing successful MySQL programs for Linux, at the same time.

Now, on to installing the new PhpRecipeBook, and getting those recipes back online.

Addendum

Or not. The latest version of PhpRecipebook (v3.0 "beta 2", released March 2012) encounters critical errors under PHP 5.4.19. I dug into some of the errors, and patched the code, but finally ran into a brick wall. It looks like I have to abandon PhpRecipeBook and find a new way to record recipes online.