Migracion PHP-Nuke a Drupal

- Instalado drupal 4.3.2 con la base de datos vacia (bdd drupal)
- Instalada la copia de la base de datos de phpnuke (bdd nuke)
- Se ejecuta el script migration.mysql
------- INICIO DE migration.mysql
-- 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 ;

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

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

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

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

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

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

--- FIN --------------

- Se ha creado la primera cuenta en el Drupal para administrador despues de la instalacion.
- Se ha borrado en vw.nuke_users el usuario de id=1 (Anonymous) para poder ejecutar el script de migracion de usuarios (se usa
el retocado)
- Los usuarios migran sin problemas pero no pueden logear y da problema al crear un nuevo usuario en Drupal.
- En drupal.sequences se rectifica el primer campo, de nombre users_uid poniendo el ultimo id que aparece en la table drupal.us
ers despues de la migracion (se soluciona la creacion de nuevos registros)
- En drupal.users se pone el campo rid a 2 en todos los registros salvo el primero (id=0) (ahora todos los usuarios aparecen co
mo activos)
- En drupal.users se pone el campo timezone a 3600 en todos los registros salvo el primero (para poner todos a GMT+1)
- No hace falta ejecutar fix.sql puesto que, al trabajar con una base de datos nueva, solo hay usuarios y la rectificacion en d
rupal.sequences ya la hemos hecho a mano, asi como borrar el usuario Anonymous en vw.nuke_users