Over the past few months, it's become clear that the website for the First Parish in Cambridge, Unitarian Universalist, badly needs a couple of upgrades.  One is an upgrade from Drupal 4.7 to Drupal 5.x.  Another is a modification of the way content types are implemented on the site, from Flexinode to a combination of the Content Construction Kit (CCK) and Views.

I've worked on this project on a pro-bono basis for Welcoming Websites.  It's been a long, somewhat difficult, journey, but now that it's almost complete, I thought I'd share my thoughts and tips on how best to proceed.  I imagine that many other folks whose Drupal sites are aging might be going through the same thing, and I hope these tips prove useful to them.

The first thing I did was create a separate copy of the website.  This is a basic but quite important step, because the upgrade is a fairly length process, and it's unlikely that you'll be able to complete it in a time span that's so short that you can reasonably expect to keep your site down for its entire duration.  What this means is that you'll almost certainly need to migrate some content from your live site to the new Drupal site before re-launching with Drupal 5.  That's a shame, but it also gives you additional motivation to finish the project as quickly as possible.

Once the new site has been created, there's a basic question to answer: should you upgrade to Drupal 5 first and then upgrade to CCK, or upgrade to CCK first and then upgrade to Drupal 5?  I recommend upgrading to Drupal 5 first, and leaving your content types alone.  The reason is that Drupal 5's CCK module, and some of the other modules you might be using like Event, are likely to be considerably more robust in Drupal 5 than they are in Drupal 4.7.  In our case in particular, we tried upgrading from Flexinode to CCK first, and discovered trouble when we couldn't apply Event-related settings to CCK content types in Drupal 4.7 (although that is possible in Drupal 5.)

Upgrading to Drupal 5 is a bit of a beastly process, but thankfully a fairly clean one.  I found the instructions in UPGRADE.txt perfectly suitable and, thanks to the fact that we didn't patch the core engine or modules too much, and didn't have a whole lot of modules in the old 4.7 site, relatively painless.

I wish I could say the same for the Flexinode to CCK upgrade, but I can't.  Naturally I began by downloading the excellent Flexiconvert module, but that module only takes you so far.  There were  a few other steps I had to take once the module was installed.

1. Dependent modules.  I needed to install Email, Date, and Filefield modules in order to make the upgrade successful.
2. Creating new content types.  I had to create new CCK-based content types to mirror the old Flexinode-based types.  This was a bit awkward, since it meant I would temporarily have two content types with the same name in the system.  I chose to name the types with "CCK" at the end, and to use a "_cck" suffix in the type handle, in order to prevent any accidental confusion during the conversion process.  There was also the minor pain in the neck of setting up each field anew.  In the future, a module like CoCKTaiL might help minimize this trouble, but it wasn't a big deal.
3. Creating date fields.  In some cases, date fields in the old site had not been properly created in the first place, so I had to create new ones and port data into them, before running the conversion.
4. Removing body fields and creating them anew.  Flexinode doesn't automatically include a body field with each content type, but CCK does.  Consequently, all of our old content types had body-like fields manually created, and those fields had to be mapped to a non-body CCK field.  That meant that I had to create CCK text fields rather than just use the built-in CCK body field.  That's a little annoying at first blush, but on deeper reflection, it's not so bad, since I was able to have a bit more user-friendly control over the display and weight of body fields in nodes and teasers than I would have otherwise had.

The next thing to do was run the actual conversion.  Happily, this went smoothly in most cases, except for a few content items where I got SQL errors of the following sort:

user warning: Unknown column 'field_description_value' in 'field list' query: UPDATE node_content_group_cck SET field_description_value = 'asdf' WHERE nid = 297 in /home/lightbul/public_html/clients/fpcamb_47/includes/database.mysql.inc on line 120.

The problem was usually caused by Flexiconvert's assumption that every CCK textfield has a _value column (this assumption is incorrect, I believe, for Plain text fields, and correct for Filtered text fields.)  Fortunately, the problem is easily solved by re-running the errant query and eliminating the "_value" suffix.

Once Flexiconvert is done doing its magic, the next step is to "de-Flexinode" your site.  De-Flexinoding can mean a bunch of different things:

1. Removing all Flexinode content types.  Fortunately, it doesn't mean removing all old Flexinode content; Flexiconvert does that for you.
2. Replacing all your old flexinode/table and flexinode/list pages with the equivalent Views tables.  I'd recommend only doing this for the Flexinode pages which are actively linked somewhere in your menu system, url aliases, and body text.  You can, happily, discover these URLs with the following SQL commands:
   

   SELECT * FROM menu WHERE path like '%flexinode%';
   SELECT * FROM url_alias WHERE src like '%flexinode%';
   SELECT * FROM node_revisions WHERE body like '%flexinode%';

   Please note that you might also need to run queries on your CCK text and/or link fields to make sure you don't have stray flexinode links in there.  Once you've identified your active flexinode URLs, create one View for each, and replace the URL in the menu item, URL alias, or node body as appropriate.  Building the new views can be a tedious and painfully slow process, but look at it this way - at least you have much more control on your multiple-node pages now!

3. Re-establishing connections between vocabularies and your CCK types.  Unfortunately, Flexiconvert doesn't do it for you.
4. Poke around your "display fields" settings in your CCK types to make sure that field label, teaser, and full-body settings are set appropriately.  If you've been fine-tuning your theme with node-flexinode-xyz.tpl.php files, you'll need to convert those too.

At this point, it's wise to take a look at your whole site and make sure that everything is looking right.  Of course, train a keen eye on the new views that you've created, and the teaser and full-node displays of at least one content item per new content type.  Check out the node-creation form for each of your new content types, and make sure that it's reasonably easy for your site admins to understand.

And that should be it!  At this point, you should migrate all the new or updated content from your old 4.7 site to your new 5.x site.  Backup and archive the old 4.7 site, swap in the files and database for the new 5.x site, and you should be done.

The site upgrade is sort of a thankless process since, if you do it right, your end users and site admins will have no idea that you did it at all.  That's the bad news.  The good news is that you're now ready to take advantage of all the advantages that CCK/Views overs on top of Flexinode.  You'll also be able to start playing around with the shiny new Drupal 5 modules, many of which are much more feature-rich and robust than their 4.7 cousins.

In our case, there's a lot we want to do once the site upgrade is done (ETA: this coming Saturday.)  We'll start by adding podcasting on top of the sermon archive, using a rich archive of MP3s several stalwart church members have built up.  We will certainly be looking at Panels as a more robust way of creating two-column pages.  We'll also want to use Organic Groups to provide mini-home pages to each committee and group in the congregation.  We might try to move the front-page Flash-based photo slideshow into a new Javascript-based, CCK/imagefield/jCarousel-powered slideshow.  And in the long term, we will take a look at rental payments and perhaps even store functionality using the eCommerce module.

As you can see, the Drupal 4.7/5.x and Flexinode/CCK upgrade are not particularly pleasant.  But it's certainly well worth it!