Upgrading an ArsDigita Community System

part of the ACS docs by Philip Greenspun
This document contains some tips that may be useful when migrating data from a legacy database into ACS Oracle tables or upgrading an ArsDigita Community System.

Migrating crud from legacy databases

There isn't any best way to migrate data. Here are some reasonable strategies that we've used.

Strategy 1: if the legacy data can be loaded into Oracle

Load your data in as a separate user, GRANT SELECT on these tables to your ACS Oracle user Once you've got all your data loaded in and owned by the "otherusername" user, construct statements of the following form:

insert into clickthrough_log (local_url, foreign_url, entry_date, click_count)
 select local_url, foreign_url, entry_date, click_count 
 from otherusername.clickthrough_log;

Strategy 2: if the legacy data aren't in Oracle and the data model isn't documented

Suppose that you've got a huge database in an Informix database that you don't really understand. Get hold of the Data Junction product (see http://www.datajunction.com/) and have it connect to Informix, grab all the data, and stuff them into Oracle.

Strategy 3: if you have lots of random losers giving you flat files in different formats

Suppose that you have lots of little collectible shops giving you their inventory data. You don't think they are sufficiently wizardly in Excel to massage their columns into your format. So you're forced into taking data in all kinds of quirky formats. Moreover, you don't want to have to hire a staff of really good programmers to write conversion scripts for each shop. What my friend Olin did at exchange.com was use Data Junction again (http://www.datajunction.com/). It has a little scripting language that was very convenient for this purpose.

Strategy 4: if it was your crud to begin with but it is another brand of RDBMS

Suppose that you've built up a big collection of data in Sybase and understand the data model but need to move it to Oracle. If you are an expert AOLserver developer, the easiest thing to do is configure a pool of connections to Sybase and a pool to Oracle. Then write scripts to select from Sybase and insert into Oracle.

Upgrading ACS

We're not going to pretend that upgrading an ACS-backed site is trivial, even if you've followed the religion of /doc/patches and /doc/custom. Does this mean that ACS is badly engineered? Maybe. But other major RDBMS-backed systems such as SAP suffer from the same problems. People have had 20 years to attack this problem and nobody has come up with a silver bullet. This is why IT departments tend to grow monotonically in size and budget. Also, if we're going to be innovative we want it to be in collaboration features that users can use, not in sysadmin/dbadmin or other areas that only programmers will see.

There are two basic strategies for upgrading an ACS. If you're doing a minor upgrade, say from 2.3 to 2.4, you'll want to apply the ALTER TABLE statements to your online Oracle database, create any new tables, add a few parameters to your ad.ini file, and update your scripts. If you're doing a major upgrade from 1.0 to 2.4, you might want to consider creating a new Oracle user and populating the fresh tables with data from the online system (see above section on migrating data). Then reapply any personality or customizations to a freshly untarred ACS. Finally you can reconfigure your public Web server to run against the new ACS installation.

Upgrading "stop and copy" style

The /admin/conversion/ directory contains the following scripts that are useful if you are stopping and copying:

Upgrading online style

If you're doing an upgrade to an online database, you'll want to look at any relevant /doc/sql/upgrade*.sql scripts. Also, you'll surely make use of /admin/conversion/parameter-differences.pl to see what parameters you must add. Lastly, /install/obsolete.txt lists files, by release, that you should remove.
philg@mit.edu