Home

Flexinode to CCK (4.7 to 5)

Thu, 2008-02-21 13:43 — Jeremy

First you should check out the flexiconvert module as that may meet your needs.

It didn't meet my needs because the site I was working with was very busy and the database was huge. I couldn't just pick one week to say, don't add anymore content and let me try to update this site and hope I get it all right. I needed an update process that would work alongside the live site, not force me to change completely in one step.

You will need a very good understanding of the CCK module, a decent understanding of Drupal core functions, and a little understanding of phpmyadmin (mysql). The example here is very specific to my needs; you will need to highly customize your own script.

This example requires that you setup your Drupal 5 site on the same server as your Drupal 4.7 site. I guess the 4.7 site doesn't have to be running, but in my case it was, as long as the database remains.

PHPMyAdmin (MySQL) Tasks:

If you want to keep the same nids, as I did in my example, you need to check out the sequences table and understand how drupal works with them, this page might help: http://cmsreport.com/node/1189

Before starting the import I went into the Drupal 5 sequences table and I set the node_nid and node_revisions_vid sequence values to be way higher than the highest nid & vid on the old site.

I also went into the old database and added a column to the node table named processed, and set the default value to 0. This will keep track of nodes that have been moved over.

Working with multiple databases:

Drupal can work with more than one database at time. In the settings files you can define additional databases. So the database settings in my settings.php file for my new Drupal 5 site looks like this: 

<?php
$db_url['default'] = 'mysql://user:password@localhost/drupal5';
$db_url['drupal4'] = 'mysql://user:password@localhost/drupal_live';
?>

The second database is the old drupal 4.7 site.

Then when you are working within Drupal you can switch to that database like this

 <?php
    db_set_active('drupal4');
    $result db_query('SELECT * FROM live_node WHERE nid = %d', $node->nid);
    db_set_active('default');
?>

That query would return the node information for that node from the old 4.7 site.

My 4.7 database had the prefix 'live_' on all the tables. Usually when you run a query in Drupal the table names should be wrapped in {} to allow for the system to prepend a prefix. I'm not sure if you can set a prefix when working with a second database so I called the tables directly.

CCK Content types:

Setup your CCK content types. Set them up however you like. No need to follow specifc naming conventions or values. Depending on your understanding of PHP and CKK you'll be able to do whatever you want with the data you get from the old database.

The Script:

Write a script that gets the old node and flexinode data, also you'll want to grab tables like the term_node table (unless you just copied the tables over).

Here's my script:

<?php
  //the first part here is taken from node_load (4.7)
  $param = array('type' => 'flexinode-2');

  $arguments = array();
  // Turn the conditions into a query.
  foreach ($param as $key => $value) {
    $cond[] = 'n.'. db_escape_string($key) ." = '%s'";
    $arguments[] = $value;
  }
  $cond = implode(' AND ', $cond);
   // Retrieve the old node.
  // notice the processed column, to keep track of nodes that have been moved over
  db_set_active('drupal4');
  $node = db_fetch_object(db_query('SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, n.processed , r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM live_node n INNER JOIN live_users u ON u.uid = n.uid INNER JOIN live_node_revisions r ON r.vid = n.vid WHERE '. $cond .' AND n.processed = 0 ORDER BY n.nid ASC LIMIT 1', $arguments));
  db_set_active('default'); //Switch back to the default connection when finished.

  if (!$node->nid) {
      return 'no more nodes';
  }
  //get the flexinode data
    db_set_active('drupal4');
    $result = db_query('SELECT * FROM live_flexinode_data WHERE nid = %d', $node->nid);
    db_set_active('default'); //Switch back to the default connection when finished.
    if (db_num_rows($result) > 0) {
      while ($row = db_fetch_object($result)) {
        $flexinode[$row->field_id.'-text'] = $row->textual_data;
        $flexinode[$row->field_id.'-numeric] = $row->numeric_data;
      }
    }
    //get the term data
    //I've already moved over the categories, matching exactly the old ones, so all I need to do is get the tids
    $result = '';
    db_set_active('drupal4');
    $result = db_query('SELECT * FROM live_term_node WHERE nid = %d', $node->nid);
    db_set_active('default'); //Switch back to the default connection when finished.
    if (db_num_rows($result) > 0) {
      while ($term = db_fetch_object($result)) {
        $new['taxonomy'][$term->tid]->tid = $term->tid;
      }
    }
  //create new node data from the old data
  $new['nid'] = $node->nid;
  $new['created'] = $node->created;
  $new['status'] = $node->status;
  $new['promote'] = $node->promote;
  $new['name'] = $node->name;
  
  $new += array(
    'type' => 'headline',
    'changed' => $node->changed,
    'comment' => 0,
    'title' => $node->title,
  );
  //convert flexinode data to the cck values, this is a simple content type, a cck teaser field, the body, and a cck link field
  $new['field_teaser'][0]['value'] = $flexinode['13-text'];
  //here I'm putting the flexinode field into the node body field
  $new['body'] = $flexinode['7-text'];
  //don't forget the format of the field
  $new['format'] = $flexinode['7-numeric];
  //many cck fields don't use the value key, here the link field uses 'url' as the key
  //you'll want to investigate different cck fields to see how the data should be setup
  $new['field_link'][0]['url'] = $flexinode['8-text'];
  
  $new = (object)$new;

  //run the new data through the standard functions
  node_object_prepare($new);
  $new = node_submit($new);
  //only run the insert if the node validates and the action=process querystring has been passed
  //using the action query allows you to view the page and the next node without actually doing anything
  if (($new->validated == 1) && ($_GET['action'] == 'process')) {
    //the following is taken from the node_save (5) function
    //I'm doing this because I need to maintin the same nids
    //so first I need to create a node, then I update it
    $revisions_table_values = array('nid' => $node->nid, 'vid' => $node->vid,
                     'title' => 'temp', 'body' => '',
                     'teaser' => '', 'timestamp' => 1127233605,
                     'uid' => 1, 'format' => 1);
    $revisions_table_types = array('nid' => '%d', 'vid' => '%d',
                     'title' => "'%s'", 'body' => "'%s'",
                     'teaser' => "'%s'", 'timestamp' => '%d',
                     'uid' => '%d', 'format' => '%d');
    $node_table_values = array('nid' => $node->nid, 'vid' => $node->vid,
                    'title' => 'temp', 'type' => 'headline', 'uid' => 1,
                    'status' => 1, 'created' => 1127233605,
                    'changed' => 1127233605, 'comment' => 0,
                    'promote' => 0, 'sticky' => 0);
    $node_table_types = array('nid' => '%d', 'vid' => '%d',
                    'title' => "'%s'", 'type' => "'%s'", 'uid' => '%d',
                    'status' => '%d', 'created' => '%d',
                    'changed' => '%d', 'comment' => '%d',
                    'promote' => '%d', 'sticky' => '%d');
    //create a node using the old nid and vid values
    $node_query = 'INSERT INTO {node} ('. implode(', ', array_keys($node_table_types)) .') VALUES ('. implode(', ', $node_table_types) .')';
    $revisions_query = 'INSERT INTO {node_revisions} ('. implode(', ', array_keys($revisions_table_types)) .') VALUES ('. implode(', ', $revisions_table_types) .')';
    db_query($node_query, $node_table_values);
    db_query($revisions_query, $revisions_table_values);
    //save the node
    node_save($new);
    
    db_set_active('drupal4');
    //update the process column
    db_query('UPDATE live_node SET processed = 1 WHERE nid = %d', $node->nid);
    db_set_active('default'); //Switch back to the default connection when finished.
    //here I set a refresh so that it would run by itself.
    drupal_set_html_head('<meta http-equiv="refresh" content="3">');
  }
  //print the new node data to the screen for error checking
  print_r($new);
?>

I created a page on my drupal 5 site and put this code directly into the body field.

I didn't have any fields that saved data to the serialized_data field. To work with that data get an array using the unserialize function and go through it setting it up in the cck format.

Files

I wanted to update the location of the files directory, otherwise you could just move the files and file revisions tables over. Then when you're ready to go live with the site move the files directory.

You'd want to copy your old files directory since Drupal deletes the original path file during the node save process.

Here's how I moved the files saved with the standard upload module:

<?php
  //get the files
  $result = '';
  db_set_active('drupal4');
  $result = db_query('SELECT * FROM live_files f INNER JOIN live_file_revisions r ON f.fid = r.fid WHERE r.vid = %d ORDER BY f.fid', $node->vid);
  db_set_active('default'); //Switch back to the default connection when finished.
  if (db_num_rows($result) > 0) {
    while ($file = db_fetch_object($result)) {
      $new['files'][$file->fid] = $file;
      //update the path to copied directory so we don't erase files from the live site
      $new['files'][$file->fid]->filepath = '/nfs/home3/home3/b/files_copy/'.$file->filename;
      //set the fid to upload
      $new['files'][$file->fid]->fid = 'upload';      
    }
  } 
 ?>

Putting a file into a CCK field. 

<?php
  // If there's a picture move it over
//$picture would be a full path to the file
  if (file_exists($picture)) {
    $new['field_thumbnail'][0]['fid'] =  'upload';
    $new['field_thumbnail'][0]['filename'] = $picture_filename;
    $new['field_thumbnail'][0]['filepath'] = $picture;
    $new['field_thumbnail'][0]['alt'] = $title;
    $new['field_thumbnail'][0]['title'] = $title;
  }
?>

Dates

I wanted to move my old event module dates to the cck Date module. The cck date field was setup as select fields which is why each value is separate, otherwise you'd want to return whatever format your field is setup to handle.

 <?php
  $new['field_date'][0]['value']['mon'] = date('n', $event['start']);
  $new['field_date'][0]['value']['mday'] = date('j', $event['start']);
  $new['field_date'][0]['value']['year'] = date('Y', $event['start']);
  $new['field_date'][0]['value']['hours'] = date('h', $event['start'];
  $new['field_date'][0]['value']['minutes'] = date('i', $event['start']);
  $new['field_date'][0]['value']['ampm'] = date('a', $event['start']);
  
  $new['field_date'][0]['value2']['mon'] = date('n', $event['end']);
  $new['field_date'][0]['value2']['mday'] = date('j', $event['end']);
  $new['field_date'][0]['value2']['year'] = date('Y', $event['end']);
  $new['field_date'][0]['value2']['hours'] = date('h', $event['end']);
  $new['field_date'][0]['value2']['minutes'] = date('i', $event['end']);
  $new['field_date'][0]['value2']['ampm'] = date('a', $event['end']); 
 ?>

I know this is very specific to my needs and your content types will be much more complex.

Also, you could also use a similar script to move data from any database or CMS. The SQL SELECT statements could be grabbing the data from any database structure.