Friday, October 16, 2009

CiviCRM Import: Advanced Techniques

I'm almost finished a big new CiviCRM import/installation that I've been working on for longer that I'd planned. That's pretty normal, and of course there were a number of things that should have been warnings:
  1. it's a huge mass of data from an established non-profit: more than 5000 constituents, more than 50,000 contributions, from more than 10 years.
  2. it's being imported from Raiser's Edge, the cadillac of desktop fundraising software.
  3. the clients are very attached to the detailed minutae they have accumulated about their constituents, all of which were faithfully entered into Raiser's Edge.
After a round of a sample import and looking at what the data might look like in CiviCRM, we were able to whittle it down a little bit, but the last month I've been working on the big final import and have developed some techniques that I expect would be generally useful with big CiviCRM imports.

Two Scripting Techniques

For my first test import, I pulled the CSV files into an OpenOffice spreadsheet and manually cleaned things up [e.g. dates, bad characters, etc.]. That works for small imports, but not for large ones, because:
  1. it's not reliably reproduceable [and/or it takes a long time]
  2. there are some things it doesn't do well [like splitting fields off to separate imports].
For my scripts in this project, I used two simple techniques:

a. add a column type row

before processing the CSV files, I added a row underneath the header, and inserted one of several 'column types' that my script could use to parse the data. For example, "Date" columns need to be converted into ISO-8601 format. "Select" columns are custom fields with option groups - so the labels in this column get cleaned up into values and those pairs get added into the database as valid options. "Note" fields get written to a different file and then imported as activities. I wrote separate scripts for each of the client files to process [i.e. constituents, notes, gifts, and extra attributes]. Some of the scripts also refer to the column header.

b. chunking

i still use the standard civicrm import technique [because I'm too lazy to script to write directly to the database, and also I'd like to not have to reinvent my wheels each time the database structures change], but the files I got from the client are way to huge - it would time out. I probably should have checked out the option of importing from a database via sql, but instead, my script just breaks the files up into no more than 1000 0r 2000 records at a time [reinsterting the header each time]. That means manually uploading a lot of files [e.g. 17 contribution files], so I have a little piece of paper handy on which i'm writing the upload file number and the time beside it so I can keep track while I'm editing this blog between uploads.
I should also say that the first time I did the sample import, I was really stretching my little staging machine. I'm now doing this import on a virtual server running off my desktop which I upgraded in the interim so it's a little less painful, though I still get a fair amount of time to edit my blog. You certainly don't want to do big imports on a live server!

Documentation

Importing into CiviCRM usually involves some degree of judgement calls. Some of the fields migrate naturally into CiviCRM fields [last name, province, ...] but all imports I've done include fields that have no built-in CiviCRM equivalent. For that, the standard advice is to turn it into a custom field, which can then be manipulated further after the import [e.g. turn it into a group, create equivalent functionality via a smart group, etc.].
This is where it usually pays to hire a professional - you have to consider not only how to get the data into CiviCRM, but you have to understand what kind of functionality you need from that data and import it somewhere where you can use it properly.
But more than that - what happens in a couple of months? In this case, there are so many fields and functions being imported that the mass of detail is almost overwhelming. So documenting what export field goes into what field/function in CiviCRM is essential not only for me [since I have to redo things with gaps of a month or two between imports], but even more for the staff and future staff of the client who are going to say "where did such and such go"?.
So my second technique is a simple one for documentation. I set up a new node type [I'm using Drupal w/ CCK and views of course], with these fields:

$content['type']  = array (
 'name' => 'CiviCRM Field Migration',
 'type' => 'civimigrate',
 'description' => 'Description of mapping of raiser\'s edge fields to civicrm fields.',
 'title_label' => 'Raiser\'s Edge Field',
 'body_label' => 'Description',
 'min_word_count' => '0',
 'help' => '',
 'node_options' =>
 array (
   'status' => true,
   'promote' => false,
   'sticky' => false,
   'revision' => false,
 ),
 'language_content_type' => 0,
 'upload' => '0',
 'scheduler' => 0,
 'scheduler_touch' => 0,
 'i18n_node' => '0',
 'old_type' => 'civimigrate',
 'orig_type' => '',
 'module' => 'node',
 'custom' => '1',
 'modified' => '1',
 'locked' => '0',
 'image_attach' => '0',
 'image_attach_size_teaser' => 'thumbnail',
 'image_attach_size_body' => 'thumbnail',
);
$content['fields']  = array (
 0 =>
 array (
   'label' => 'civicrm_field',
   'field_name' => 'field_civicrm_field',
   'type' => 'text',
   'widget_type' => 'text_textfield',
   'change' => 'Change basic information',
   'weight' => '1',
   'rows' => '1',
   'size' => 60,
   'description' => '',
   'default_value' =>
   array (
     0 =>
     array (
       'value' => '',
     ),
   ),
   'default_value_php' => '',
   'default_value_widget' => NULL,
   'group' => false,
   'required' => 0,
   'multiple' => '0',
   'text_processing' => '0',
   'max_length' => '',
   'allowed_values' => '',
   'allowed_values_php' => '',
   'op' => 'Save field settings',
   'module' => 'text',
   'widget_module' => 'text',
   'columns' =>
   array (
     'value' =>
     array (
       'type' => 'text',
       'size' => 'big',
       'not null' => false,
       'sortable' => true,
       'views' => true,
     ),
   ),
   'display_settings' =>
   array (
     'label' =>
     array (
       'format' => 'inline',
     ),
     'teaser' =>
     array (
       'format' => 'hidden',
       'exclude' => 0,
     ),
     'full' =>
     array (
       'format' => 'default',
       'exclude' => 0,
     ),
   ),
 ),
 1 =>
 array (
   'label' => 'civicrm_note',
   'field_name' => 'field_civicrm_note',
   'type' => 'text',
   'widget_type' => 'text_textfield',
   'change' => 'Change basic information',
   'weight' => '2',
   'rows' => '1',
   'size' => 60,
   'description' => '',
   'default_value' =>
   array (
     0 =>
     array (
       'value' => '',
     ),
   ),
   'default_value_php' => '',
   'default_value_widget' =>
   array (
     'field_civicrm_note' =>
     array (
       0 =>
       array (
         'value' => '',
         '_error_element' => 'default_value_widget][field_civicrm_note][0][value',
       ),
     ),
   ),
   'group' => false,
   'required' => 0,
   'multiple' => '0',
   'text_processing' => '0',
   'max_length' => '',
   'allowed_values' => '',
   'allowed_values_php' => '',
   'op' => 'Save field settings',
   'module' => 'text',
   'widget_module' => 'text',
   'columns' =>
   array (
     'value' =>
     array (
       'type' => 'text',
       'size' => 'big',
       'not null' => false,
       'sortable' => true,
       'views' => true,
     ),
   ),
   'display_settings' =>
   array (
     'label' =>
     array (
       'format' => 'inline',
     ),
     'teaser' =>
     array (
       'format' => 'hidden',
       'exclude' => 0,
     ),
     'full' =>
     array (
       'format' => 'default',
       'exclude' => 0,
     ),
   ),
 ),
 2 =>
 array (
   'label' => 'civicrm_transformation',
   'field_name' => 'field_civicrm_transformation',
   'type' => 'text',
   'widget_type' => 'text_textfield',
   'change' => 'Change basic information',
   'weight' => '3',
   'rows' => '1',
   'size' => 60,
   'description' => '',
   'default_value' =>
   array (
     0 =>
     array (
       'value' => '',
     ),
   ),
   'default_value_php' => '',
   'default_value_widget' =>
   array (
     'field_civicrm_transformation' =>
     array (
       0 =>
       array (
         'value' => '',
         '_error_element' => 'default_value_widget][field_civicrm_transformation][0][value',
       ),
     ),
   ),
   'group' => false,
   'required' => 0,
   'multiple' => '0',
   'text_processing' => '0',
   'max_length' => '',
   'allowed_values' => '',
   'allowed_values_php' => '',
   'op' => 'Save field settings',
   'module' => 'text',
   'widget_module' => 'text',
   'columns' =>
   array (
     'value' =>
     array (
       'type' => 'text',
       'size' => 'big',
       'not null' => false,
       'sortable' => true,
       'views' => true,
     ),
   ),
   'display_settings' =>
   array (
     'label' =>
     array (
       'format' => 'inline',
     ),
     'teaser' =>
     array (
       'format' => 'hidden',
       'exclude' => 0,
     ),
     'full' =>
     array (
       'format' => 'default',
       'exclude' => 0,
     ),
   ),
 ),
 3 =>
 array (
   'label' => 'civicrm_todo',
   'field_name' => 'field_civicrm_todo',
   'type' => 'text',
   'widget_type' => 'optionwidgets_onoff',
   'change' => 'Change basic information',
   'weight' => '4',
   'description' => '',
   'default_value' =>
   array (
     0 =>
     array (
       'value' => 0,
     ),
   ),
   'default_value_php' => '',
   'default_value_widget' =>
   array (
     'field_civicrm_todo' =>
     array (
       'value' => false,
     ),
   ),
   'group' => false,
   'required' => 0,
   'multiple' => '0',
   'text_processing' => '0',
   'max_length' => '',
   'allowed_values' => '0|
1|*',
   'allowed_values_php' => '',
   'op' => 'Save field settings',
   'module' => 'text',
   'widget_module' => 'optionwidgets',
   'columns' =>
   array (
     'value' =>
     array (
       'type' => 'text',
       'size' => 'big',
       'not null' => false,
       'sortable' => true,
       'views' => true,
     ),
   ),
   'display_settings' =>
   array (
     'label' =>
     array (
       'format' => 'hidden',
     ),
     'teaser' =>
     array (
       'format' => 'hidden',
       'exclude' => 0,
     ),
     'full' =>
     array (
       'format' => 'default',
       'exclude' => 0,
     ),
   ),
 ),
);
And then I imported the headers of my spreadsheets, grouping them with a taxonomy term corresponding to the spreadsheet name. Then a simple view allows me to list all the fields in each spreadsheet, and document the following information for each input field:

a. Input field name [the node title]
b. Note - description of the use of the field
c. CiviCRM field - name of the field in CiviCRM it's being migrated to
d. CiviCRM note - additional information about the civicrm field [e.g. custom, etc.]
e. CiviCRM transformation - processing of the original field before putting it into CiviCRM
f. Todo [bonus field - just use it as you need to for keeping track of loose ends].
At the end of this, I've got a nice table for each input spreadsheet documenting where that value is ending up in CiviCRM and what happens to it along the way.

Thursday, October 15, 2009

Open Source Showcase for Non-Profits in Toronto

On Monday October 26th, I'll be at the "Open Source Showcase for Non-Profits" at the Centre for Social Innovation here in Toronto.

The showcase is a low-cost event where members of the non-profit sector can learn about open source projects relevant to their work. I'm helping organize and will do some presentations as well.

The idea came from Julian Egelstaff about a month ago, and he, Jane Zhang, Joe Murray, Reema Tarzi, and I met just a couple of weeks ago and have put it all together with remarkably little work. That's a tribute to the viability of the idea, the excellent organizing tools that are now available for such events, and the quality of the organizing committee. My own contribution was to set up a CiviCRM install with CiviEvents to do registration, which was impressively easier that I expected - part of my motivation was that I'd never set up a CiviEvents page, and now I'm not afraid of it anymore.

So, visit the information page and register.

Tuesday, September 15, 2009

Toronto CiviCRM Coaching Sessions for Mozilla Week

For the Mozilla Service Week, I'll be at the Centre for Social Innovation on Wednesday morning, to provide 1-1 coaching for anyone interested in using CiviCRM.
Yes, that's now tomorrow, Wednesday September 16, 2009, starting at 10 am, I hope you can come. You're supposed to sign up, as early as 9:30 for 15 minute sessions, but if you want to just drop by, you can join whoever's there.
Details about where and more details about what are here.

Friday, July 17, 2009

Toronto Drupalcamp 2009

I'm sad to say that Toronto's Drupal Camp [which I helped organize for it's first 3 years] is happening while I'm out of town. It's kind of a good thing, since I had decided to take a little sabbatical from the organizing anyway. But in case you're breathlessly wondering, check out the 2009 toronto drupal camp site. It's not ready yet, but hopefully will be by the time you read this. The dates are set for the weekend of Aug 15.

Friday, July 03, 2009

The Tyee: Bricolage and Drupal Integration

The Tyee is a site I've been involved with since 2006 when I wrote the first, 4.7 version of a Drupal module to integrate Drupal content into a static site that was being generated from bricolage. About a year ago, I met with Dawn Buie and Phillip Smith and we mapped out a number of ways to improve the Drupal integration on the site, including upgrading the Drupal to version 5 from 4.7. Various parts of that grand plan have been slowly incorporated into the site, but as of next week, there'll be a big leap forward that coincides with a new design [implemented in Bricolage by David Wheeler who wrote and maintains Bricolage] as well as a new Drupal release of the Bricolage integration module.

Plans

Application integration is tricky, and my first time round had quite a few issues. Here's a list of the improvements in the latest version:

  • File space separation. Before, Drupal was installed in the apache document root, which is where bricolage was publishing it's content. This was dangerous and confusing because of the risk of Bricolage overwriting a Drupal file and vice-versa, and the mess that it left us for version control since bricolage versioning was best maintained within the bricolage application on another machine. So in the new version, Drupal is installed in it's own non-document root directory and Drupal pages are accessible via an Apache alias command like this:
    Alias /cms /var/www/drupal-dir
    This change also allows us to be more specific about which of the bricolage url get passed through Drupal, because that mechanism has it's own mod_rewrite rule, something like:
    RewriteRule ^(.*)\.html$ /index.php?fid=%{REQUEST_FILENAME}&q=$1 [L,QSA]
  • Drupal file discovery. Drupal 'discovers' bricolage files using the Drupal custom not found mechanism. This is probably not always the best way to do it - instead Bricolage could publish a csv file of new articles that Drupal processes, or maybe even push data directly into the Drupal database. But file discovery is the mechanism that we inherited on this site, and it's robust and relatively simple. When Drupal does discover a new page, there are a few pieces of information that Drupal likes to know about, such as a page title, a unique bricolage id (if a page gets republished with a new name, it knows how to move the comments over), and whether comments are allowed for the page, to name just a few. In my first version, these bits of information were translated via some php defines, which aside from being ugly, meant that the bricolage page had to be php. So in the new version, all these values are now in meta tags.
  • Template files The best thing this version does is to get rid of the extra file that was required for each bricolage page. Previously, because of trying to reimplement the integration on a live site with existing comments in vb3, i resorted to getting bricolage to output separate template files from the original html files. Because we were starting fresh here, Bricolage can now just output one page per file and use the meta tag mechanism for all it's drupal-specific stuff. The nice result is that to remove Drupal integration, you can just update the apache mod rewrite command and the site suddenly becomes a regular php or html site.

Mainstreaming?

So in spite of failing to release early or often, i'm hoping that the new release will be appreciated and used outside of The Tyee. In that spirit, here are some step-by-step instructions for a simple install that adds static page integration to an existing Drupal installation.

  1. Create the static page directory if you don't already have one. I just added a subdirectory called 'static' to my site directory, and then added an alias so I could address pages within that directory more simply /static/. By default, these pages would not be processed by Drupal because they actually exist.
  2. Download and install the module. This won't break or do anything.
  3. Add a mod rewrite to send your 'static' files through the drupal bricolage module. See above for an example, which maps static urls like /static/pathname/filename.html to the Drupal path 'pathname/filename'. For the tyee, all the filenames are index.html, so we remove that (because each index.html file has a print.html version which doesn't need to go through Drupal).
  4. Set up the discovery mechanism. In the Drupal admin -> site config -> error reporting, put in "bricolage/notfound" as the 404 page.

With those steps complete, urls like /static/test/blah.html that correspond to an actual html page will get mapped via the url_alias mechanism to an internal Drupal path like 'bricolage/id' and display those pages as if they were phptemplate pages after running through the Drupal bootstrap and generating appropriate values (e.g. the user, blocks, etc.). To get commentability on your static pages, you'd also need to add the appropriate meta tags and on your static pages.

How is this useful?

The original use case of this module is to add Drupal commenting to a static site. Since you get a full Drupal bootstrap for each page, you also get blocks and users, and nodes if you want. Which means that really, you're injecting any Drupal-generated dynamic content into a site who's design and primary content can be controlled via another mechanism [like Bricolage].

Of course, intergration is always complicated, and the Tyee example is instructive in that bricolage is outputting php, which, without Drupal, would be reinterpreted on each page load. By running it through Drupal, you can get the static page cache for anonymous users, which has the potential to also speed up the site [but you have to consider whether the dynamic content in the page php really should be cached ...].

Monday, January 26, 2009

CentOS4 and CiviCRM 2.1

With the new year, a new resolution to upgrade some sites to the new CiviCRM 2.1. CiviCRM 2.1 is particularly special because it requires Drupal 6 and it's the first version that supports Drupal 6. So upgrades of existing Drupal 5 sites are difficult, particularly if any custom modules or themes involved.
As it turned out, my procrastination was justified. I asked my friend Rob Ellis to help with Maquila Solidarity Network, who I've been working with for a few months, and who decided that the new features in 2.1 were too good to postpone any longer. Rob did the upgrade and discovered two issues on my CentOS 4 server:
  • The CiviCRM installer insists on PHP 5.2.x
  • CiviCRM requires a version of PCRE with unicode
None of this sounds very interesting, and I wouldn't post about it, except that I would have thought it wouldn't be as hard to fix as it was. So here's what I did, in case there's someone else out there with CentOS4 (or RHEL4) trying to run CiviCRM 2.1.
Running CiviCRM 2.1 on a normal CentOS4
The original RHEL4 (and hence CentOS4) comes with php4, which is really not okay any more, but the CentOS 'extras' repository has php 5.1, which is what I've been using for the past 2 years on this server. Unfortunately, there don't seem to be any plans to upgrade this to 5.2.
From my brief reading, it looked like there wasn't a big difference from 5.1 to 5.2, and the CiviCRM maintainers didn't promise that it wouldn't work on 5.1. So the first thing Rob did was just fiddle with a couple of installation files to allow the installation to procede with 5.1. Not too surprisingly, it worked, almost.
What actually created errors, was a problem with PCRE, which is the Perl Regular Expression library. So, rob found the file that was generating the errors (packages/IDS/Converter.php) and patched it in a few places (hey, it's an external library) where it thought it cared about unicode, and voila, it worked.
Conclusion: CiviCRM 2.1 can run with slight modifications on CentOS4 (and RHEL4). Yay rob!
On The Other Hand
But I really didn't relish maintaining these modifications to CiviCRM through multiple installs and upgrades, and updating my php to 5.2 and my pcre to unicode both seemed like sensible things to do. Whether it was sensible remains to be seen, but here's how I did it.
PHP 5.2.x on CentOS4
When I googled this, I ended up being pointed to a repository called "utter ramblings", which I tried to use. While I appreciate the work Jason did on this, it didn't work. The problem arose that his upgrade also required a version of a library on which subversion depended, and he chose the standard CentOS/RHEL4 version of subversion, so his upgrade was incompatible with my up to date subversion package from Dag. I also just wasn't quite convinced that he really wanted to be keeping his repository going for a long time. As an aside, the php upgrade also thought it needed to upgrade my apache to 2.2, which wasn't a bad thing, but made the whole upgrade a little more risky and complicated.
There was another repository by a french-speaking guy called 'remi' that many people praised, but I found myself shying away from it also, based on a fear of language confusion and the fact that he was hosting his repository on a domain called 'family collette'. This was probably unfounded paranoia on my part, and his repository might have been completely adequate.
What I did end up finding, though much less prominent, is the 'atomic rocket turtle' repository, which you might think i'd avoid even more because of it's name. But it was very impressive technically - he must have a pretty good understanding of what he's doing and a good automatic build environment because he had the latest version of php 5.2 compiled shortly after it had come out in December.
So - i just followed the instructions, ran the update and it did a nice clean minimal update of php 5.2 and just a couple of small dependencies that didn't break anything else.
Of course, I had to upgrade my version of APC, but that was to be expected since I don't maintain it from YUM.
PCRE with Unicode on CENTOS4
I had hoped my php 5.2 upgrade would solve the pcre problem, but it didn't. That was because the php installed (as well as the previous one - presumably a CentOS/RHEL standard) uses the option that tells php to use the installed OS library. So I had to go learn about my CentOS version of PCRE (which was dated 2003) and why it didn't support unicode.
That turned out to be confusing on google, because it seems I'm not the only one to be messed up about the difference between UTF-8 and unicode. The version I had did have UTF-8. but not unicode support.
The solution turned out to be a combination of these two posts:
http://devblog.jasonhuck.com/2009/01/08/installing-lasso-on-centos-5/
Look for "Add Unicode Properties Support to PCRE".
The only problem with it was it was for the wrong version of CentOS, so I found:
http://www.centos.org/modules/newbb/viewtopic.php?topic_id=6833
which pointed me at a broken link for a fedora 6 src rpm, which i eventually found here:
http://archives.fedoraproject.org/pub/archive/fedora/linux/core/6/source/SRPMS/pcre-6.6-1.1.src.rpm
Using that, with jason huck's instructions, turned out to work just fine.
Conclusion: you can update your CentOS4 to run CiviCRM 2.1 without modification, though some assembly is required. Specifically: recompiling source RPMs.