Migrate module: handling multiple user roles

UPDATE: In the latest version of the migrate module, the hook names have also changed - the word 'destination' has been removed.

One of the issues I encountered when migrating users from another CMS to Drupal, using the migrate module, was that I couldn't associate users with more than one role.

In the source data users were stored in one table (cms_users), roles were stored in another (cms_groups) and the relationship of users to roles were stored in yet another (cms_user_groups). The table structures are shown below. This is much the same setup as Drupal. It was straightforward to migrate the roles from the cms_groups table, as all it contained was the role id and name, and I didn't need to link it to any other table. So I just set up a simple migrate content set to migrate the roles, and ran that first.

Source table structures:

mysql> desc cms_users:
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| user_id       | int(11)      | NO   | PRI | 0       |       | 
| username      | varchar(25)  | YES  |     | NULL    |       | 
| password      | varchar(40)  | YES  |     | NULL    |       | 
| first_name    | varchar(50)  | YES  |     | NULL    |       | 
| last_name     | varchar(50)  | YES  |     | NULL    |       | 
| email         | varchar(255) | YES  |     | NULL    |       | 
| active        | tinyint(4)   | YES  |     | NULL    |       | 
+---------------+--------------+------+-----+---------+-------+

mysql> desc cms_groups;
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| group_id      | int(11)     | NO   | PRI | 0       |       | 
| group_name    | varchar(25) | YES  |     | NULL    |       | 
+---------------+-------------+------+-----+---------+-------+

mysql> desc cms_user_groups;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| group_id      | int(11)  | NO   | PRI | 0       |       | 
| user_id       | int(11)  | NO   | PRI | 0       |       | 
+---------------+----------+------+-----+---------+-------+

The problem arose when I wanted to migrate users, and their associated roles, into Drupal. My first idea was to use the Table Wizard module to create a Views relationship between the user_id in the cms_users table and the user_id in cms_user_groups, and then have another between the group_id in cms_user_groups and the group_id in cms_groups. For those of you familiar with MySQL, think of this Views relationship as a LEFT JOIN across the tables.

This seemed to be the perfect solution and I was able to create a view that contained one row for each user and role combination. So I set up my content set and pressed the import button, expecting everything to work. I was sadly mistaken. The migrate module is only able to handle data sets that contain one row for each entity. So it's currently only possible to migrate data where each row contains a different user. As it was I had multiple rows for each user, one for each role they were a member of. This caused the migrate module to try and import the same user more than once, leading to a whole bunch of errors.

The solution? One possibility is to (somehow) create one field in your source data that contains a comma separated list of all the roles associated with that user, ensuring there is only one row per user in the source content set view. Another solution is to implement hook_migrate_destination_prepare_user() to manipulate the user object before the user is imported. This is the solution I went with.

I implemented hook_migrate_destination_prepare_user() as follows:

<?php
/**
 * Implements hook_migrate_destination_prepare_user().
 *
 * Retrieve the user roles in the required format.
 */
function mymodule_migrate_destination_prepare_user(&$user, $tblinfo, $row) {
  static $drupal_user_roles;
  // Get the list of user roles in Drupal.
  if (!isset($drupal_user_roles)) {
    $drupal_user_roles = array_flip(user_roles(TRUE));
  }

  // Get the original user id.
  $username = $user['name'];
  $uid = db_result(db_query("SELECT user_id FROM {cms_users} WHERE username = '%s'", $username));

  // Get list of user roles for this user in source database.
  $result = db_query("SELECT g.group_name FROM {cms_groups} g, {cms_user_groups} ug WHERE g.group_id = ug.group_id AND ug.user_id = %d", $uid);
  while ($row = db_fetch_object($result)) {
    $roles[] = $row->group_name;
  }

  if (!empty($roles)) {
    $newvalue = array();
    foreach ($roles as $role) {
      $rid = $drupal_user_roles[trim($role)];
      if ($rid) {
        $newvalue[$rid] = $rid;
      }
      else {
        $newvalue[$role] = $role;
      }
    }
    $user['roles'] = $newvalue;
  }
}
?>

I configured a simple content set that just mapped data from the cms_users table to their equivalents in Drupal. Then as each user is being migrated, but before each row is processed, the above hook is invoked. For each user it fetches the original user id from the source database, uses that to retrieve the list of roles for that user and sets $user['roles'] appropriately.

It took a few attempts to get it right, but eventually I was able to migrate all users and their roles successfully.