Blog

Recently, Function1 was engaged to perform a Jive migration project to Drupal 7 and specifically the Open Atrium 2.0 distribution since Jive is a communication and collaboration platform. As a team, we are familiar with Open Atrium and have used it few times on multiple projects and know its different content types. The challenge was in grabbing the data from the PostgreSQL database, which had close to 300 tables, and formulating the data source to map to the target Open Atrium content types.

In performing the migration, we extended the Drupal Migrate module and were able to use PostgreSQL as a direct database source. So we didn’t need to migrate the tables to MySQL; that spared us countless hours on the project’s timeline. For that to work we grabbed the PGSQL driver and added a second database configuration in settings.php:

$databases['JiveDB'] = array (
    'default' => array(
        'database' => 'jiveDB',
        'username' => 'jiveUser',
        'password' => 'jivePassword',
        'host' => 'localhost',
        'port' => '5432',
        'driver' => 'pgsql',
        'prefix' => false,
    )
);

and then used that JiveDB connection in our base migration class as such:

public function getConnection($connection = 'JiveDB') {
    return Database::getConnection('default', $connection);
}

and in the different migration classes used:

$query = $this->getConnection()->select('view_<view-name>', 'source');

That approach allowed us to use the Migrate module existing MigrateSourceSQL plugin; which was great.

The mapping between source and destination in each migration class was, to a great extent, straightforward. Sometimes the destination content types in Open Atrium did not have all the necessary fields that the client needed to capture and we just added these. But the challenge was really in getting the source data from the Jive PostgreSQL database, which was scattered, in multiple tables. Tables in Jive were normalized to the n^th degree and formulating one data source in a migration class meant creating a PostgreSQL SQL view that referenced half a dozen tables with all sorts of relational joins. It was a major and complex task and I must give credit where credit is due; my colleague Jon did a great job in figuring out where to grab the data and creating these views. Without exception, all data sources were PostgreSQL SQL views, on few occasions we needed to write a stored procedure to generate staging tables whose structure can be mapped to destination. 

In the migration classes we used prepareRow() for conditional value setting in Open Atrium. Also in prepareRow() we handled the processing of creating file attachments to specific content types as one of the SQL view columns contained a concatenated series of files names; e.g. file1.docx, file2.pdf, file3.xlsx, file4.pptx, file5.zip and then these files were created as attachments using:

$this->addFieldMapping('field_oa_media', 'oa_filename')->separator(',');         
$this->addFieldMapping('field_oa_media:file_class')->defaultValue('MigrateFileUri');        
$this->addFieldMapping('field_oa_media:destination_dir')->defaultValue('private://');        
$this->addFieldMapping('field_oa_media:file_replace')->defaultValue('FILE_EXISTS_RENAME'); 
$this->addFieldMapping('field_oa_media:preserve_files')->defaultValue(FALSE);        
$this->addFieldMapping('field_oa_media:source_dir')->defaultValue(‘somepath’);

The major hurdle we encountered was handling embedded images in the body along with replacing other Jive specific elements. For that we used the Simple HTML DOM Parser library and created a sanitizeBody() function that takes the entire source body field as input and does all sorts of transformations. For instance, to replace the “src” attribute for the “img” tag with Drupal’s URL we did:

$html = str_get_html($body);
if (strpos($html,'__jive_id') == True) { 
    $htmlraw = str_replace('__jive_ID', 'id', $body);
    $html = str_get_html($htmlraw);
    foreach($html->find('img') as $element) {
        $element->src = '/sites/default/files/images/' . $element->id . '_' . $element->src;
    }
}

if(strpos($element,'__jive_macro_name="document"') == True) {
   $element->href = '/docs/DOC-' . $element->attr["__default_attr"];
   $element->innertext =  str_replace('</span>', '',str_replace('<span>','',$element->attr["data-orig-content"]));
}

$this->addFieldMapping('migrate_redirects', 'documentid');
public function prepareRow($row) {
    $row->documentid = 'docs/' . $row->documentid;




}

and sanitizeBody() was always referenced in prepareRow():

$row->bodyText = sanitize_body($row->bodyText); 

and then we created a separate migration class to create the images in the public direcotry

$this->addFieldMapping('destination_dir')->defaultValue('public://images');

The project entailed migrating a number of: users and their avatars, threaded comments, blogs, and organic group memberships that we will touch on in subsequent blogs. But if you have any questions, feel free to contact us.

Thanks.