Php-Nuke to Drupal Story Conversion Script Anywhere?

Well, I have been kind of busy in the end, sorry for not being able to send you your patch, but I'm happy it wasn't that terrible anyway.

Talking about users... the content at Drupal's Administrators Guide is a bit outdated. I changed from phpNuke 6 to Drupal 4.3.2. Here is the sql command, if you want. It is tested and working (it assumes first user working in your Drupal site):

insert into users(uid,name,pass,mail,theme,signature,timestamp,status,timezone,language,rid) select uid,uname,pass,email,'xtemplate',user_sig,unix_timestamp(now()),1,7200,'es',1 from phpNuke.nuke_users where uid>1;

Also, check out your timezone and change it appropriately. If you have saved it in your account in Drupal, you can check it using:

select timezone from users where uid=1;

Regards,
Sergi

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.