PostNuke to Drupal Conversions: Basic conversion Reference

That said, here's the original post:

Recently this website was migrated from PHP-Nuke to Drupal. Importing the data from one CMS to another presented a number of problems since the database tables are quite different.

The biggest difference is that Drupal treats everything as a 'node' and therefore uses one table for most entries. PHP-Nuke has separate tables for most sections of the site. Migrating the data from PHP-Nuke to Drupal requires that the table ids be changed to avoid conflicts. The trick is to keep all of the IDs relative to each other so the comments and other entries match up properly.

The following snippets are the MySQL code I used to migrate Phrixus from PHP-Nuke to Drupal. Each snippet is a separate file and they should be executed in the order in which they appear.

Executing these scripts as a file requires copying the contents into a file and running the following command:

• The database names are 'drupal' and 'nuke'
  
• The user doing the migration has full access to both databases.
  
• The drupal database is empty aside from the entries created by database.mysql
  

1. Users
   The first step is to migrate all of the users.
   
   
   -- User Migration: PHP-Nuke to Drupal --
   
   -- Delete existing data --
   DELETE FROM drupal.users;
   
   INSERT INTO drupal.users
       ( uid, name, pass, mail, timestamp, status, init, rid )
     SELECT
       user_id, username, user_password, user_email, user_lastvisit, 1,
       user_email, 2
     FROM
       nuke.nuke_users ;
   
   
   
2. Stories
   The next step is to migrate all of the stories and their associated comments.
   
   
   -- Story Migration --
   
   -- Delete existing vocabulary and terms --
   DELETE FROM drupal.vocabulary;
   DELETE FROM drupal.term_data;
   DELETE FROM drupal.term_hierarchy;
   
   INSERT INTO drupal.vocabulary VALUES
       ( 1, "Content", "Articles, blogs, and other short entry-based content",
         0, 1, 0, 1, "blog,poll,story", 0 );
   
   INSERT INTO drupal.term_data
       ( tid, vid, name, description, weight )
     SELECT
       topicid, 1, topicname, topictext, 0
     FROM
       nuke.nuke_topics;
    
   INSERT INTO drupal.term_hierarchy
       ( tid, parent )
     SELECT
       topicid, 0
     FROM
       nuke.nuke_topics;
   
   
   -- Migrate Stories --
   
   -- Delete existing nodes --
   DELETE FROM drupal.node;
   DELETE FROM drupal.term_node;
   
   INSERT INTO drupal.node
       ( nid, type, title, uid, created, comment, promote, teaser, body, changed )
     SELECT
       s.sid, "story", s.title, u.user_id, UNIX_TIMESTAMP(s.time), 2, 1,
       s.hometext,
       CONCAT(s.hometext, "", s.bodytext), now()
     FROM
       nuke.nuke_stories s, nuke.nuke_users u
     WHERE
       s.informant=u.username;
    
   INSERT INTO drupal.term_node
       ( nid, tid )
     SELECT
       s.sid, s.topic
     FROM
       nuke.nuke_stories s;
   
   -- Migrate Story Comments --
   
   DELETE FROM drupal.comments;
   
   INSERT INTO drupal.comments
       ( cid, pid, nid, uid, subject, comment, hostname, timestamp )
     SELECT
       c.tid, c.pid, c.sid, u.user_id, c.subject, c.comment, c.host_name,
       UNIX_TIMESTAMP(c.date)
     FROM
       nuke.nuke_comments c, nuke.nuke_users u
     WHERE c.name=u.username;
   
   
   
3. Polls
   The next step is to migrate the polls. Since polls in Drupal are also considered nodes, some id offsets need to be set before this script is run.
   
   
   -- Migrate Polls --
   
   -- Make sure new polls don't conflict with existing NIDs --
   -- Use the following query to set the variable --
   -- SELECT MAX(sid) FROM nuke.nuke_stories --
   SET @POLL_NID_OFFSET=87;
   
   -- Make sure poll comments don't conflict with any existing CIDs --
   -- Use the following query to set the variable --
   -- SELECT MAX(tid) FROM nuke.nuke_comments  --
   SET @POLL_CID_OFFSET=368;
   
   
   -- delete any existing data --
   DELETE FROM drupal.poll;
   DELETE FROM drupal.poll_choices;
   
   INSERT INTO drupal.node
       ( nid, type, title, score, votes, uid, status, created, comment, promote,
         moderate, users, teaser, body, changed, revisions, static )
     SELECT
       pollID+@POLL_NID_OFFSET, "poll", pollTitle, 1, 1, 0, 1, timeStamp, 2, 1, 0,
       "", pollTitle, "", NOW(), "", 0
     FROM
       nuke.nuke_poll_desc;
   
   
   -- Migrate Polls --
   INSERT INTO drupal.poll
       ( nid, runtime, voters, active )
     SELECT
       pollID+@POLL_NID_OFFSET, timeStamp, voters, 1
     FROM
       nuke.nuke_poll_desc;
   
   INSERT INTO drupal.poll_choices
       ( chid, nid, chtext, chvotes, chorder )
     SELECT
       0, pollID+@POLL_NID_OFFSET, optionText, optionCount, voteID
     FROM
       nuke.nuke_poll_data;
   
   
   -- Migrate Poll Comments --
   
   INSERT INTO drupal.comments
       ( cid, pid, nid, uid, subject, comment, hostname, timestamp )
     SELECT
       c.tid + @POLL_CID_OFFSET, IF(c.pid, c.pid+@POLL_CID_OFFSET, 0),
       c.pollID+@POLL_NID_OFFSET, u.user_id, c.subject, c.comment, c.host_name,
       UNIX_TIMESTAMP(c.date)
     FROM
       nuke.nuke_pollcomments c, nuke.nuke_users u
     WHERE c.name=u.username;
   
   
   
4. Forums
   The next step is the forums. This was the most difficult script to create since it alters so many different tables (nodes, comments, term_data, term_hierarchy, and vocabulary).
   
   
   -- Migrate Forums --
   
   -- Make sure new forum containers don't conflict with existing TIDs --
   -- Use the following query to set the variable --
   -- SELECT MAX(tid) FROM drupal.term_data --
   SET @FORUM_CONTAINER_OFFSET=5;
   
   -- Make sure new forums don't conflict with existing TIDs --
   -- Use the SUM of the following two queries to set the variable --
   -- SELECT MAX(tid) FROM drupal.term_data --
   -- SELECT COUNT(*) FROM nuke.nuke_bbcategories  --
   SET @FORUM_TERM_OFFSET=7;
   
   -- Make sure new forum topics don't conflict with existing NIDs --
   -- Use the following query to set the variable --
   -- SELECT MAX(nid) FROM drupal.node --
   SET @FORUM_NID_OFFSET=101;
   
   -- Make sure new forum comments don't conflict with existing CIDs --
   -- Use the following query to set the variable --
   -- SELECT MAX(cid) FROM drupal.comments --
   SET @FORUM_CID_OFFSET=418;
   
   -- Create a new vocabulary ID for forums --
   -- Use the following query to set the variable --
   -- SELECT MAX(vid)+1 FROM drupal.vocabulary --
   SET @FORUM_VID=2;
   
   
   -- delete existing data --
   DELETE FROM drupal.forum;
   DELETE FROM drupal.vocabulary WHERE vid=@FORUM_VID;
   
   -- Create the Forums --
   
   INSERT INTO drupal.vocabulary
       VALUES ( @FORUM_VID, "Forums", "Topics for forums", 0, 1, 0, 1,
                "forum", 0 ) ;
   
   
   INSERT INTO drupal.term_data
       ( tid, vid, name, description, weight )
     SELECT
       cat_id + @FORUM_CONTAINER_OFFSET, @FORUM_VID, cat_title, cat_title, 0
     FROM
       nuke.nuke_bbcategories;
   
   
   INSERT INTO drupal.term_hierarchy
       ( tid, parent )
     SELECT
       cat_id + @FORUM_CONTAINER_OFFSET, 0
     FROM
       nuke.nuke_bbcategories;
   
   
   INSERT INTO drupal.term_data
       ( tid, vid, name, description, weight )
     SELECT
       forum_id + @FORUM_TERM_OFFSET, @FORUM_VID, forum_name, forum_desc, 0
     FROM
       nuke.nuke_bbforums;
   
   
   INSERT INTO drupal.term_hierarchy
       ( tid, parent )
     SELECT
       forum_id + @FORUM_TERM_OFFSET, cat_id + @FORUM_CONTAINER_OFFSET
     FROM
       nuke.nuke_bbforums;
   
      
   -- Add the forum topics (posts become comments to these) --
   
   INSERT INTO drupal.node
       ( nid, type, title, uid, status, created, comment, promote, moderate,
         users, teaser, body, changed, revisions, static )
     SELECT
       t.topic_id + @FORUM_NID_OFFSET, "forum", t.topic_title,
       t.topic_poster, 1, t.topic_time, 2, 1, 0, "",
       t.topic_title, t.topic_title, NOW(), "", 0
     FROM
       nuke.nuke_bbtopics t;
   
   
   INSERT INTO drupal.forum
       ( nid, tid )
     SELECT
       topic_id + @FORUM_NID_OFFSET, forum_id + @FORUM_TERM_OFFSET
     FROM
       nuke.nuke_bbtopics;
   
   INSERT INTO drupal.term_node
       ( nid, tid )
     SELECT
       topic_id + @FORUM_NID_OFFSET, forum_id + @FORUM_TERM_OFFSET
     FROM
       nuke.nuke_bbtopics;
   
   
   -- Add the forum posts as comments --
   
   INSERT INTO drupal.comments
       ( cid, pid, nid, uid, subject, comment, timestamp )
     SELECT
       c.post_id + @FORUM_CID_OFFSET, 0, c.topic_id + @FORUM_NID_OFFSET,
       c.poster_id, t.post_subject, t.post_text, c.post_time
     FROM
       nuke.nuke_bbposts c, nuke.nuke_bbposts_text t
     WHERE
       c.post_id=t.post_id;
   
   
   
5. Journals
   PHP-Nuke has a journals module that I regrettably made use of. The journal section of PHP-Nuke was not well-designed especially with regard to the database schema. Because of this, the migration to drupal was not as smooth as it could have been despite being a very easy query to execute. The problem is that the PHP-Nuke journal table uses VARCHAR for its date fields instead of DATE. While it's possible these dates could be salvaged, I gave up after trying numerous queries. The following script migrates all of the journal content but sets a static date of Jan 01, 2003 for all journals.
   
   
   -- Migrate Journals to Personal Blog Entries --
   
   -- Make sure new journals (blogs) don't conflict with existing NIDs --
   -- Use the following query to set the variable --
   -- SELECT MAX(nid) FROM drupal.node --
   SET @JOURNAL_NID_OFFSET=179;
   
   INSERT INTO drupal.node
       ( nid, type, title, uid, status, created, comment, promote, moderate,
         users, teaser, body, changed, revisions, static )
     SELECT
       j.jid + @JOURNAL_NID_OFFSET, "blog", j.title, u.user_id, 1,
       UNIX_TIMESTAMP('2003-01-01'), 2, 1, 0, "", j.title, j.bodytext,
       UNIX_TIMESTAMP('2003-01-01'),"", 0
     FROM
       nuke.nuke_journal j, nuke.nuke_users u
     WHERE
       j.status='yes'
     AND
       j.aid=u.username;
   
   
   
6. Private Messages
   The migration of private messages requires the use of the privatemsg module in Drupal.
   
   
   -- Migrate Private Messages --
   
   -- delete existing data --
   DELETE FROM drupal.privatemsg;
   
   INSERT INTO drupal.privatemsg
       ( id, author, recipient, subject, message, timestamp )
     SELECT
       p.privmsgs_id, p.privmsgs_from_userid, p.privmsgs_to_userid,
       p.privmsgs_subject, t.privmsgs_text, p.privmsgs_date
     FROM
       nuke.nuke_bbprivmsgs p, nuke.nuke_bbprivmsgs_text t
     WHERE
       t.privmsgs_text_id = p.privmsgs_id ;
   
   
   
   
7. Sequences and Database Fixes
   The last step is to update the sequences table so new entries can be created and to fix some of the migration discrepancies that occurred.
   
   
   -- Fix some Nuke/Drupal discrepancies --
   
   -- Set the Drupal site admin username/uid here --
   SET @SITE_ADMIN='david';
   SET @SITE_ADMIN_NUKE_UID=2;
   
   -- Get the max IDs for various tables in order to update drupal.sequences --
   -- Use the following queries to set the variables --
   
   -- SELECT MAX(uid) FROM drupal.users --
   SET @MAX_UID=57;
   
   -- SELECT MAX(nid) FROM drupal.node --
   SET @MAX_NID=256;
   
   -- SELECT MAX(cid) FROM drupal.comments --
   SET @MAX_CID=947;
   
   -- SELECT MAX(vid) FROM drupal.vocabulary --
   SET @MAX_VID=2;
   
   -- SELECT MAX(tid) FROM drupal.term_data --
   SET @MAX_TID=16;
    
   
   -- PHP-Nuke has UID 1 as 'Anonymous'. Replace with the drupal site admin --
   DELETE FROM drupal.users WHERE uid='1';
   UPDATE drupal.users SET uid='1' WHERE name=@SITE_ADMIN;
   UPDATE drupal.node SET uid=1 WHERE uid=@SITE_ADMIN_NUKE_UID;
   UPDATE drupal.comments SET uid=1 WHERE uid=@SITE_ADMIN_NUKE_UID;
   UPDATE drupal.privatemsg SET author=1 WHERE author=@SITE_ADMIN_NUKE_UID;
   UPDATE drupal.privatemsg SET recipient=1 WHERE recipient=@SITE_ADMIN_NUKE_UID;
      
    
   -- Add the UID 0 so the drupal Anonymous user works properly --
   INSERT INTO drupal.users (uid,rid) VALUES (0,1);
      
   
   -- Update the sequences table so new entries can be created --
   INSERT INTO drupal.sequences (name, id) VALUES ('users_uid', @MAX_UID);
   INSERT INTO drupal.sequences (name, id) VALUES ('node_nid', @MAX_NID);
   INSERT INTO drupal.sequences (name, id) VALUES ('comments_cid', @MAX_CID);
   INSERT INTO drupal.sequences (name, id) VALUES ('vocabulary_vid', @MAX_VID);
   INSERT INTO drupal.sequences (name, id) VALUES ('term_data_tid', @MAX_TID);