Migrating from Xoops

Here are some queries that I used in a recent migration from Xoops 2.0.13 to Drupal 4.7.0. This was my first real life experience using SQL so be warned. You might consider adapting the Xoops Migration Module for use with 4.7 instead.

Make database backups at every stage using mysqldump or phpmyadmin.

mysqlump --opt -u user -p database > backup.sql

You will need to have your xoops and Drupal tables in the same database for these to work. The whole transfer took me about 10 hours but I was learning as I went. This is not a complete migration solution but hopefully these will save you some time.

In each case change the drupal_users and xoops_users to reflect your table prefixes.

Users

Change the '-28800' to reflect the timezone of your users.

INSERT INTO drupal_users (uid, name, pass, mail, signature, 
  status, init, created, timezone, access, timezone_name, language) 
SELECT uid, uname, pass, email, user_sig,
  '1', email,  user_regdate, '-28800', last_login, 'America/Argentina/Buenos_Aires', 'en'
FROM xoops_users WHERE uid > 1;

If you want to transfer Occupation and User From then you'll need to setup fields in the profile.module and transfer these separately. Drupal supports user avatars but I didn't transfer these.

Stories from News 1.4

I deleted all nodes from my Drupal account before transferring stories so that node ids matched xoops story ids. Drupal 4.7 uses node and node_revision tables so you need to to this in two stages. Here the revision id is the same as the node id. You'll need to add the bbcode input filter to the Filtered HTML filter.

INSERT INTO drupal_node (nid, vid, type, title, uid, status, created, changed, comment, promote, moderate, sticky)
SELECT storyid, storyid, 'story', title, uid, '1', created, published, '2', '1', '0', '0' 
FROM xoops_stories WHERE published<>0;

INSERT into drupal_node_revisions (nid, vid, uid, title, body, teaser, log, timestamp, format) 
SELECT storyid, storyid, uid, title, concat(hometext, bodytext), concat(hometext, bodytext), '', published, '1' FROM xoops_stories WHERE published<>0;

Forum posts from NewBB | CBB 2.x

Enable the Drupal forum module and setup some categories to hold your xoops forum topics. Drupal's forum uses nodes for topics and comments for topic replies. At this point you will need to find the number of nodes you have from your story import - change #NODE below for this number.

INSERT into node (nid, vid, type, title, uid, status, created, changed, 
  comment, promote, moderate, sticky) 
SELECT (topic_id + #NODE), (topic_id + #NODE), 'forum', topic_title, topic_poster, '1', topic_time, topic_time, '2', '0', '0', '0' FROM xoops_bb_topics;

INSERT into drupal_node_revisions (nid, vid, uid, title, body, teaser, log, timestamp, format) 
SELECT (t.topic_id + #NODE), (t.topic_id + #NODE), t.uid, 
   t.subject, p.post_text, p.post_text, '', t.post_time, '1' 
FROM xoops_bb_posts AS t INNER JOIN xoops_bb_posts_text  AS p ON t.post_id = p.post_id 
AND pid = '0';

To set the topics from xoops to a category, setup your Drupal categories and note down the numbers. Here I didn't have many categories so I did it by hand - replace 15 below with your Drupal category number and 1 with your xoops topic id. You can repeat this for each topic.

INSERT into drupal_forum (nid, vid, tid) 
SELECT (topic_id + #NODE), (topic_id + #NODE), '15' FROM xoops_bb_topics 
WHERE forum_id='1'; 

INSERT into drupal_term_node (nid, vid, tid) 
SELECT (topic_id + #NODE), (topic_id + #NODE), '15' FROM xoops_bb_topics 
WHERE forum_id='1';

If your forum posts have attachments these will be lost in the above process.

To transfer forum replies (this assumes tables of both are in the same database):

Replace the #NODE below with the number of nodes previous to forum nodes (same as above). Then run this query.

INSERT INTO comments( pid, uid, nid, subject, timestamp, comment ) SELECT xoops_bb_posts.post_id, xoops_bb_posts.uid, (xoops_bb_posts.topic_id + #NODE), xoops_bb_posts.subject, xoops_bb_posts.post_time, xoops_bb_posts_text.post_text FROM xoops_bb_posts, xoops_bb_posts_text WHERE xoops_bb_posts.post_id = xoops_bb_posts_text.post_id AND xoops_bb_posts.pid > 0;

It would appear that topic starting posts have pid of 0 in XOOPS which is why the condition of importing only posts with pid bigger than 0 results in importing only actual replies.

Cleaning up

Statistics

In order to get the forum topics to show up I needed to add some information to other tables. I identified what needed to be added using a diff between mysqldumps. There may be an easier was to do this.

INSERT into drupal_history (uid, nid, timestamp) SELECT uid, nid, created FROM node; 
INSERT into drupal_node_comment_statistics 
(nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count)
SELECT nid, created, '', '1', '0' FROM node;

To get the Nodes to show the correct poster comments, You can try this Mysql Stored procedure. I encountered issues with the forums after populating the history, and comment_stats table. You need to modify the CREATE line, and set the Definer. (Note this is using the mysql Query Browser) Once this SP is created, you can do a
CALL fixforums();
to execute it

DELIMITER $$

DROP PROCEDURE IF EXISTS `fixforums` $$
CREATE DEFINER=`user`@`ip.add.res.s` PROCEDURE `fixforums`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE id,replies,postid INT;
  DECLARE cur1 CURSOR FOR select topic_id, topic_replies, topic_last_post_id from xoops_bb_topics;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO id,replies,postid;
    IF NOT done THEN
       select uid from xoops_bb_posts where post_id=postid into @lastpostid;
       update node_comment_statistics set last_comment_uid=@lastpostid where nid=@nodeid;
       update node_comment_statistics set comment_count=replies where nid=@nodeid;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;

END $$

DELIMITER ;

Avatars & Signatures

Drupal 6.x
Install/Enable the Author Pane Module for Drupal.
Enable User Pictures in admin/user/settings
Copy the avatars from wherever they are stored in your xoops system, into the Drupal 'sites/default/files/pictures'
Run this stored procedure to associate the xoops avatars with pictures on the Drupal system.
Again, You need to modify the CREATE line, and set the Definer.
CALL fixavatars();

DELIMITER $$

DROP PROCEDURE IF EXISTS `fixavatars` $$
CREATE DEFINER=`user`@`ipaddress` PROCEDURE `fixavatars`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE sig VARCHAR(255);
  DECLARE id INT;
  DECLARE cur1 CURSOR FOR SELECT uid,user_avatar FROM xoops_users where user_avatar <> 'blank.gif';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO id,sig;
    IF NOT done THEN
       Set @picture=concat('sites/default/files/pictures/',sig);
       update users set picture=@picture where uid=id;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;

END $$

DELIMITER ;

And to Fix the Signatures:

DELIMITER $$

DROP PROCEDURE IF EXISTS `fixsig` $$
CREATE DEFINER=`user`@`ipaddress` PROCEDURE `fixsig`()
BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE sig VARCHAR(255);
  DECLARE id INT;
  DECLARE cur1 CURSOR FOR SELECT uid,user_sig FROM xoops_users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO id,sig;
    IF NOT done THEN
       update users set signature=sig where uid=id;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;


END $$

DELIMITER ;

Sequences

NOTE: This is Not needed in Drupal 6.x
Once you have imported your nodes and comments you will need to edit the Drupal sequences table to reflect the new numbers. Warning: if you don't do this then new content will try and overwrite existing nodes.

BBcode

If you allowed your users to use BBcode in posts you will need to enable the bbcode module and enable it in the default input filter.

URL Aliases

You can use the path module to create url aliases from your old Xoops links.

modules/news --> node 
modules/newbb --> forum
register.php --> user/register
login.php --> user/login
backend.php --> rss.xml
modules/contact --> contact

However, this is rather disappointing since menu items now appear as the OLD links. I can't find a way to change alias precedence in path module - maybe someone else knows.

Search Index

You will need to index the site for searches in the admin - settings - search - re-index site.

Annoyances

Character Sets

I had problems moving between Latin and Unicode character sets / collations. When data was transferred titles or posts were truncated at the site of multibyte characters. I ended up hacking this badly by removing all these characters from my xoops as they were only '' and --.