Programming languages

A while ago I inherited responsibility for a Drupal 6 instance and a rather out-of-date server. (You know it's not good when your production operating system is so old that it is no longer getting security updates).

I'm not a Drupal person. I dabbled with Drupal years and years ago when I was heavily into PHP, but it never stuck with me. Every time I poked around at the database schema, with serialized objects stuck inside columns, I found something else that I wanted to work on instead. Thus, inheriting a Drupal instance wasn't something I had been looking forward to. As this production server was running a number of different services that were in use by our library, I went through a number of trial runs to ensure that the base packages wouldn't introduce regressions or outages. Fast-forward past a reasonably successful early-morning upgrade from Debian Lenny to Squeeze and I was able to start looking at addressing the Drupal instance that was also approximately 18 months out of date.

Initially, after I worked out the how-to of Drupal upgrades (in short: upgrade just Drupal core, then upgrade the modules), I thought all was well. I even got over the hump of realizing that our instance had had all of the modules dumped into Drupal's core directory, rather than sites/all/modules, and (even more impressively) got over the problem that the core bluemarine them had been hacked directly rather than having been separated out into a new custom theme. After working through those learning pains, I realized that somewhere in all of the Drupal and module upgrades, that something got "more secure" and started truncating IMG links to files with spaces in them at the first space. So "foo%20bar.jpg" was becoming "foo.jpg" and we were getting 404s everywhere.

Did I mention that I didn't notice this until I upgraded our production instance? Oh yes, I went through iteration after iteration of upgrades on the test server, and dutifully fixed up the problems that I found in the subset of content that I was testing against. I discovered and fixed problems like the production server content linking directly to the test server (slight copy-and-paste errors on the part of the content creators, I suppose). But I didn't notice all of the 404s, because who uploads images with spaces in their filename?

Turns out, everyone else in my library does that. Of course! And from what I was able to piece together via Google and browsing drupal.org, there was supposed to be some sanitization of the incoming filenames so that spaces would be normalized, etc. But either that wasn't introduced until well after our content had been created, or my predecessor had lightly hacked one of the modules, or Drupal itself, and hadn't bothered to use a source code repository to track those customizations. So, realizing that I needed to make some bulk changes, I went at it with a two-step plan:

1. Create symbolic links for both the truncated filename and the spaces-normalized-to-underscores filenames. Creating symlinks for the truncated filenames would fix the 404s immediately, at the cost of some clash in the intended targets; there were plenty of Foo illustration.JPG and Foo info.JPG pairs of files, but like the Highlander, there can be only on Foo.JPG.
2. Munge the database entries so that all of those now apparently insecure %20-containing filenames would become underscores.

If you're a Drupal user or a Drupal with Panels module user, you might know that the database schema suffers from some fairly horrible tricks being played on it. In this case, the Panels module creates a panels_pane table with a configuration TEXT column. Based on the name alone, it might seem odd that column is used to store the HTML content of the corresponding panel. Even odder to me is that this is not just a TEXT column, it's a column that expects a very particular structure - something like:

a:5:{s:11:"admin_title";s:5:"RACER";s:5:"title";s:0:"";s:4:"body";s:639:"<p><img width="225" height [...]}

Ah, nothing like storing an object within a single database column. Of particular interest was the result that I had when I tested updating the column value with a basic "replace(configuration, '%20', '_')" - the panel showed only n/a, presumably because the size (defined by the s properties in the object) for the "body" text property was no longer a match. That would be an instance of http://drupal.org/node/926448 - so okay, clearly I had to change tactics and update the entire object.

I tried quickly finding the Drupal way to do this: clearly there's an API and there must be some simple way to retrieve an object, change it's values, and update it so that the serialized object gets stored in the database and Drupal is happy. However, I couldn't find a simple tutorial, and trying #drupal on Freenode was unfortunately fruitless as well (although some people did try to suggest running REPLACE() at the database level, that was nice but they didn't recognize that that would actually damage things significantly).

So... out came the Perl, and here's what I hacked together:

#!/bin/perl
use strict;
use warnings;

foreach (<DATA>) {
    chomp();
    my $i = 0;
    my $body = 0;
    my @fixed;
    my @row = split /\t/;
    my $pid = $row[1];
    my $configuration = $row[0];
    my @chunks = split /";s:/, $configuration;
    foreach my $chunk (@chunks) {
        if (!$i++) {
            push @fixed, $chunk;
            next;
        }
        if ($chunk =~ m/"body/) {
            $body = 1;
            push @fixed, $chunk;
            next;
        }
        if ($body) {
            my ($length, $content) = $chunk =~ m/^(\d+):"(.+)$/;
			for (my $j = 0; $j < 50; $j++) {
            $content =~ s{(/pictures/[^\./]+?)%20}{$1_}g;
			}
            $content =~ s{%20}{+}g;
            $length = length($content);
            $chunk = "$length:\"$content";
            $body = 0;
        }
        push @fixed, $chunk;
    }
    print 'UPDATE panels_pane SET configuration = $ashes$' . 
        join('";s:', @fixed) . '$ashes$' . " WHERE pid = $pid;\n";
}
__DATA__

Against the trusty database (I ? PostgreSQL!), I ran COPY (SELECT configuration, pid FROM panels_pane WHERE configuration ~ '%20') TO 'conf_pids.out';, then slapped the Perl code on top and generated a load of UPDATE statements. It's far from my best Perl code, but it worked and once I gave up on doing things the Drupal way I was able to put it together in a handful of minutes. I now have a functional Drupal 6 instance again, updated such that there are no known security vulnerabilities with either core or the modules we're using, and there are no broken image links.

And now I need to begin working towards either grokking Drupal, or finding a content management system that my tiny brain can comprehend, because I don't want to have to go through these kinds of contortions again with future upgrades... Suggestions welcome!

I've just released the PHP PEAR library File_MARC 0.6.0. This release brings two JSON serialization output methods for MARC to the table:

• toJSONHash() returns JSON that adheres to Bill Dueber's proposal for the array-oriented MARC-HASH JSON format at New interest in MARC-HASH JSON
• toJSON() returns JSON that adheres to Ross Singer's proposal for an object-oriented JSON format (I could only find a sample at this paste - not sure if there's a broader description anywhere, but really -- who needs it?)

The JSON formats should be useful for developers who don't want to have to deal with the overhead and sluggishness of a MARC parsing library (yes, File_MARC, I'm looking at you) just to deal with MARC data. Both formats are round-trippable and compact, which is why I chose to support them.

The use of the json_encode() function bumps the minimum PHP version requirement for File_MARC up to 5.2.x from 5.1.x, which kind of sucks, but given that PHP 5.2.0 was released in 2006, I think it's worth it.

You can install File_MARC using the 'pear' command on most environments as follows:

pear install File_MARC-beta

I'm one of those people who actually keeps different passwords for every site and service I use. So far I'm up to over 400 passwords, so I'm dependent on a password manager. For a long, long time I have used Figaro's Password Manager (FPM) (and KedPM and most recently FPM2 as continuations of FPM), but now that I have an Android smartphone on which I can browse without wanting to die, I've been itching to get access to my passwords on that. I noticed that KeePassDroid was available, and that KeePassX would work on my desktop. I just had to get from FPM's password export format to one of KeePass's import formats. It turns out that nobody had made that particular leap before (or hadn't shared their conversion script).

Thus... I bring you the FPM to KeePass converter. A straightfoward Python script licensed under the GPL v3 that does a passable job of converting an FPM XML export to a KeePass 1.x or 2.x XML import file. It worked for me, and that's all that I needed; but maybe it will work for you, too.

I was tagged by Lukas for the "7 things" meme, and meant to do something about it, but I've been kind of preoccupied with the new baby and the sprinting toddler and work. Anyway, it seems like a heck of a lot more reasonable than the evil Facebook's "25 things" meme, so I'm going to take a few minutes to try to play along.

1. I was an early riser until I was around 15 or 16 years old and discovered the surrealists. At that point, I began experimenting with sleep deprivation as a means of stimulating my prose and poetry. This is also when I began drinking coffee. After about a month, I was no longer capable of being an early riser--and the fruit of writing experiments was, uh, not too impressive.
2. Rather than going directly to university after high school, I elected to take what is now termed a gap year. No trips to Europe for me, though; the goal was to refine my bass-playing and music-reading skills and head to a post-secondary music program. I recorded a few prog-rock tracks in a studio with a fantastic couple of guys (hey Pete and Mike!), but ultimately didn't put enough effort into my bass to carry out the plan. Let me assure you that a year of working night shifts at a convenience store in the entertainment district of a small city is not a waste of time; I can't count the number of experiences that I'm thankful for having had during that time.
3. Although I roast and grind my own coffee, I'm not a coffee snob. In fact, I possess almost no sense of smell and I suspect that my sense of taste is limited in comparison to most people, and I'm quite happy to drink diner coffee. I cannot stand the taste of Starbucks coffee, however.
4. The first time I was able to run a full kilometre without walking was when I was eighteen. Since then I've run a couple of 5K races and and a sprint duathlon.
5. I'm pretty sure I was destined to become a systems librarian. When I was 10, I used to hang out at the local college's computer room until the students would log me onto a completely restricted Gandalf mainframe account so I could pretend to be Matthew Broderick in WarGames. My first real job, when I was 14, was as a "computer page" at the Barrie Public Library Children's Annex. It was my responsibility to oversee the use of the bank of Commodore 64s that the library made available to children, luring them in with games but requiring them to complete their allotment of educational software first. Oh the power.
6. I occasionally wrote reviews for random CDs that came into the campus newspaper office. Nobody else wanted to review this orange CD called Tragic Kingdom by some West-coast band, so I took it on. I gave it a savage review; I wasn't impressed with faux-ska and couldn't stand the lead singer's voice. Six months later No Doubt's "SpiderWebs" was in high rotation on every radio station in North America (look, folks, that song is repetitive enough without being played twice an hour!). I'm sure that my negative review still gnaws at Gwen Stefani today as she weeps bitterly in her platinum mansion.
7. In grade one, my report card read Dan is too critical of his classmates. In my defence, if they weren't so stupid--come on, sound it out buddy--I wouldn't have been critical. Okay, not much of a defence.
8. I am not a very demanding friend. I (almost) never call, (almost) never write, and (almost) never visit. Okay, scratch that: I'm a crappy friend. Most of my close friends found out that we were expecting a second child only through Lynn's Facebook account. I called one couple shortly after Arik was born and his quasi-namesake (one of the Eric's in our life who bring honour to the noble name) asked me after a few minutes: "So, uhh... did we know that you were expecting a baby?". No, no you didn't, and that's not your fault. Man I suck.
9. I'm really good at arithmetic.

Link your original tagger(s), and list these rules on your blog.

• Share seven facts about yourself in the post — some random, some weird.
• Tag seven people at the end of your post by leaving their names and the links to their blogs.
• Let them know they’ve been tagged by leaving a comment on their blogs and/or Twitter.

Wow, that was fun. Lemme see, I'm going to break the rules and just tag two people: Helmut, because he's one of the only other people who worked on the ibm_db2 PHP driver out of passion rather than as a job assignment. And Gabriel because I like his style.