PostNuke to Drupal Conversions: Basic conversion Reference

This is drawn from http://www.phrixus.net/migration, which at some point drew information from one of my Drupal comments and postnuke forums to drupal forums script.

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:

$ mysql -p drupal < file.sql


Assumptions
  • 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


Credits: Much of the following code was based on the PostNuke to Drupal migration scripts (pn2drupal.sql and pn2drupal_forums.sql) created by David Poblador Garcia and kitt (from drupal.org).

  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);


Hopefully these scripts will be useful to others facing a similar situation. Just as note, these scripts do not come with any warranty and are not guaranteed to work. That said, they did work for my migration and with minimal tweaking should at least make a PHP-Nuke to Drupal migration easier.


End of post from other site.