Last updated January 14, 2010. Created by johnmunro on March 22, 2008.
Edited by GreenReaper, Flu, VM. Log in to edit this page.

Use phpMyAdmin to perform the following SQL. This requires both the PostNuke site and the new Drupal site to exist in the same MySQL server instance.

Note that the Drupal site must be a fresh one with no existing content or users. If you do have such then you will need to add an increment to the ID's to avoid conflicting index numbers.

You will want to adjust or remove the database names drupal and postnuke. You may need to change the table prefix in these statements from "nuke_" to "pn_", depending on your version of PostNuke.

Users

Assuming anon and admin are already present from the installation . . .

INSERT INTO drupal.users (uid, name, pass, mail, signature, timezone, mode, sort, threshold, status, init, created, access, data, login) SELECT pn_uid, pn_uname, pn_pass, pn_email, pn_user_sig, IF(
  pn_timezone_offset = 0,
  NULL,
  (pn_timezone_offset - 12) * 3600), 0, 0, 0, 1, pn_email, pn_user_regdate, UNIX_TIMESTAMP('2007-01-01 01:01:01'), "N;", 0 FROM postnuke.nuke_users WHERE pn_uid!=1;

Note: This also assumes your system timezone has not changed between the migration. If it has, you may wish to change the NULL to an appropriate value for the prior timezone (such as -18000 for UTC-5) so that users experience the same zone.

Optional unoptimized query to import creation timestamps which may include the "Nov 1, 2001" style:

UPDATE users,
nuke_users SET users.created = ( SELECT CASE WHEN ISNULL( UNIX_TIMESTAMP( STR_TO_DATE( nuke_users.pn_user_regdate, '%b %d, %Y' ) ) )
THEN nuke_users.pn_user_regdate
ELSE UNIX_TIMESTAMP( STR_TO_DATE( nuke_users.pn_user_regdate, '%b %d, %Y' ) )
END
FROM nuke_users
WHERE users.uid = nuke_users.pn_uid )

or change the field using UNIX_TIMESTAMP('2007-01-01 01:01:01') in the first query to:

WHEN ISNULL( UNIX_TIMESTAMP( STR_TO_DATE( nuke_users.pn_user_regdate, '%b %d, %Y' ) ) )
THEN nuke_users.pn_user_regdate
ELSE UNIX_TIMESTAMP( STR_TO_DATE( nuke_users.pn_user_regdate, '%b %d, %Y' ) )

User profiles

PostNuke stores its user profile data in additional columns in its nuke_users table, while Drupal uses a separate many-to-many table to match users, profile items and profile values.

First, examine your nuke_users table for fields to transfer, then create the fields in the profile editor. Identify the fid values in profile_fields that match the columns in nuke_users, and run a variation of the following query for each, replacing 1 with the fid and both instances of pn_name with the column to be copied:

INSERT INTO drupal.profile_values (fid, uid, value)
SELECT 1, pn_uid, pn_name
FROM postnuke.nuke_users
WHERE pn_name != ''

News Items

INSERT INTO drupal.node (nid, vid, type, title, uid, status, created, comment, promote, moderate, changed)
SELECT pn_sid,pn_sid,"story",
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
pn_uid,1,unix_timestamp(pn_time),2,1,0,now()
FROM postnuke.nuke_stories s, postnuke.nuke_users u
WHERE s.pn_informant=u.pn_uname

INSERT INTO drupal.node_revisions (nid, vid, uid, title, teaser, body, log, timestamp, format)
SELECT pn_sid,pn_sid,pn_uid,pn_title,pn_hometext,
CONCAT(
  IF(
    pn_notes != '',
    CONCAT(pn_hometext,'<div class="node-note">',pn_notes,'</div>')
    ,pn_hometext),
  IF(
    pn_bodytext != '',
    CONCAT('<!--break-->',pn_bodytext),
    '')
),"",UNIX_TIMESTAMP( STR_TO_DATE( pn_time, '%Y-%m-%d %T') ),2
FROM postnuke.nuke_stories s, postnuke.nuke_users u
WHERE s.pn_informant=u.pn_uname

Note: To allow only filtered HTML, change the 2 above to 1.

Polls

Because stories and polls both reside in the nodes table, you will have to set an appropriate offset when importing. Consider using SELECT MAX( nid ) FROM node to determine this.

SET @POLL_NID_OFFSET=2600;
INSERT INTO drupal.node
(nid, vid, type, title, uid, status, created, changed, comment, promote)
SELECT
pn_pollid+@POLL_NID_OFFSET,
pn_pollid+@POLL_NID_OFFSET,
'poll',
pn_title,
2,
1,
pn_timestamp,
pn_timestamp,
2,
0
FROM postnuke.nuke_poll_desc

You also have to insert a revision. It will have a title, but no body or teaser text.

SET @POLL_NID_OFFSET=2600;
INSERT INTO drupal.node_revisions
(nid, vid, uid, title, timestamp, format)
SELECT
pn_pollid+@POLL_NID_OFFSET,
pn_pollid+@POLL_NID_OFFSET,
2,
pn_title,
pn_timestamp,
2
FROM postnuke.nuke_poll_desc

Now we can add the poll itself:

SET @POLL_NID_OFFSET=2600;
INSERT INTO drupal.poll
(nid, active)
SELECT pn_pollid+@POLL_NID_OFFSET,
0
FROM postnuke.nuke_poll_desc

Lastly, you add the poll choices. Unfortunately you won't know who voted for what, as this information is not recorded by PostNuke.

SET @POLL_NID_OFFSET=2600;
INSERT INTO drupal.poll_choices
(nid, chtext, chvotes, chorder)
SELECT pn_pollid + @POLL_NID_OFFSET, pn_optiontext, pn_optioncount, pn_voteid - 1
FROM postnuke.nuke_poll_data
WHERE pn_optiontext != ''
ORDER BY pn_pollid, pn_voteid

Poll comments are handled below.

Comments

INSERT INTO drupal.comments ( cid, pid, nid, uid, subject, comment, hostname, timestamp, status, format, thread, name, mail, homepage)
SELECT
  c.pn_tid,
  c.pn_pid,
  c.pn_sid,
  IF(STRCMP(c.pn_name,""),u.pn_uid, 0),
  c.pn_subject,
  c.pn_comment,
  c.pn_host_name,
  UNIX_TIMESTAMP(c.pn_date),
  0,
  2,
  "01/",
  IF(STRCMP(c.pn_name,""), c.pn_name, NULL),
  IF(STRCMP(c.pn_email,""), c.pn_email, NULL),
  IF(STRCMP(c.pn_url,""), c.pn_url, NULL)
FROM postnuke.nuke_comments c
LEFT JOIN postnuke.nuke_users u ON c.pn_name = u.pn_uname

Note: To allow only filtered HTML, change the 2 above to 1. If you prefer not to import anonymous comments, replace the last two lines with:

FROM postnuke.nuke_stories s, postnuke.nuke_users u
WHERE s.pn_informant=u.pn_uname

Poll comments

As with nodes, an increment must be applied to poll comments, as they use the same table as story comments. You must also use the same node increment that you used before.

SET @POLL_NID_OFFSET=2600;
SET @POLL_CID_OFFSET=39000;
INSERT INTO drupal.comments
( cid, pid, nid, uid, subject, comment, hostname, timestamp, format, thread, name, mail, homepage )
SELECT
pn_tid+@POLL_CID_OFFSET,
IF(pn_pid != 0, pn_pid+@POLL_CID_OFFSET, 0),
pn_pollid+@POLL_NID_OFFSET,
IF(STRCMP(c.pn_name,''), u.pn_uid, 0),
pn_subject,
pn_comment,
pn_host_name,
UNIX_TIMESTAMP(pn_date),
1,
'01/',
IF(c.pn_name != '', c.pn_name, NULL),
IF(c.pn_email != '', c.pn_email, NULL),
IF(c.pn_url != '', c.pn_url, NULL)
FROM postnuke.nuke_pollcomments c
LEFT JOIN postnuke.nuke_users u ON c.pn_name = u.pn_uname;

If you find you have doubled quotemarks in your poll comments, try the following fix:

SET @POLL_CID_OFFSET=39000;
UPDATE comments SET comment = REPLACE(comment, '\'\'', '\'') WHERE cid => POLL_CID_OFFSET

Comment statistics

INSERT INTO drupal.node_comment_statistics ( nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count)
SELECT pn_sid,UNIX_TIMESTAMP('2008-01-01 01:01:01'),"",0,pn_comments
FROM postnuke.nuke_stories
WHERE pn_sid > 1

Rather than perform this query, consider running this script with the devel php code block. This will also fix comment threading.

Tracker

Note: This assumes you are using the Tracker 2 module, rather than the Drupal 6 default.

INSERT INTO drupal.tracker2_node (nid, published, changed)
SELECT nid, status, changed
FROM postnuke.node
INSERT INTO drupal.tracker2_user (nid, uid, published, changed)
SELECT nid, uid, status, changed
FROM postnuke.node
REPLACE INTO drupal.tracker2_user (nid, uid, published, changed)
SELECT DISTINCT nid, uid, 1, timestamp
FROM postnuke.comments WHERE status = 0 ORDER BY timestamp DESC

Taxonomy

Set up a taxonomy and insert a single term into it. Look at your drupal.term_data table to identify the vid (here, it is 1). Use this in the following SQL statement:

INSERT INTO drupal.term_data (tid, vid, name, description) SELECT pn_topicid, 1, pn_topicname, pn_topictext FROM postnuke.nuke_topics

Then, insert the instances of this term - PostNuke has a simple category hierarchy, so you only have one term to import per story:

INSERT INTO drupal.term_node (nid, vid, tid) SELECT pn_sid, pn_sid, pn_topic FROM postnuke.nuke_stories

Beware: Community Tags relies on having its own table filled with details of existing tags. If you perform this query with a tag vocabulary after installing it, you may find these tags removed when other modifications are made by that module.

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

I get a syntax error at "status". Can you help?

Wow. You ain't wrong!

Running that caused my migration test VM to flatline the CPU for a couple of hours migrating ~12,000 users... All the other steps finished damn near instantly.

It's very well worth doing a quick check beforehand to see if it's necessary :-

select pn_uid,pn_name from nuke_users where !ISNULL( UNIX_TIMESTAMP( STR_TO_DATE( nuke_users.pn_user_regdate, '%b %d, %Y' ) ) );

If it comes back as an empty set, leave that out. Mine did...

It appears that my issue with users not remaining logged in is only applying to the users migrated from PostNuke.

Anybody had any similar experiences?
http://drupal.org/node/1092186