Drupal -> Wordpress MySQL migration script

Drupal-Wordpress-MySQL-migration.sql

/* 

Drupal -> Wordpress migration script

Author: 
Mustafa Arif
www.mustafa.arif.me.uk
14 March 2006


README:

Adapted from SQL statements proposed by Panayotis' Notes [vrypan.net] as well as comments (particularly from Leon Atkinson) in blog post below:

http://vrypan.net/log/archives/2005/03/10/migrating-from-drupal-to-wordpress/

This is not a complete SQL script. It needs parameterisation, and probably needs completion in terms of functionality (I never even remotely used all of Drupal's capabilities.) I am publishing this so that others may use it as a starting point for their own migrations and in case someone has the interest in developing it into a more general migration tool.

Therefore, in so far as there is any original copyright in this work, it is released under the BSD license.



Pre-requisites:

This script was written to work with the database structures used by Drupal 4.5.2 and Wordpress 1.5.2. It may or may not work with older or newer installations. I suggest you check the database structures of your existing installations and check that the SQL below makes sense, first.

This is not complete


Instructions:

1. Setup a fresh wordpress installation.

2. The SQL below assumed that you have setup Wordpress tables into a database called "wp" and Drupal into a database named "drupal". If not, you will need to modify the "[database name]." prefix of each table reference in the SQL statements. The SQL also assumes that all Wordpress table names begin with the "wp_" prefix. Again, if you changed the installation defaults you may need to alter this.

3. In some cases the SQL may need to be modified to reflect your timezone. Wordpress stores two timestamps for each post (local time and GMT) whereas Drupal only stores local time. The author's local timezone is GMT hence there is no addition/subtraction to the times.

4. Run the SQL statements below.

*/


/* Clear Wordpress content

*/

This does not clear all WP tables - just those tables that are unnecessarily populated after a fresh install with sample content.

delete from wp.wp_categories ;
delete from wp.wp_posts;
delete from wp.wp_post2cat ;
delete from wp.wp_comments ;



/* Import users from Drupal

This does not map privleges. Imported users are given the default access level (i.e. can write comments but not actually create blog posts or pages).

It is assuemd that you picked the same username and id for your WP admin account as for Drupal. (Normally the first account created on both systems.)

*/

INSERT INTO wp.wp_users (
ID, user_login, user_pass, user_nickname, user_nicename, user_email,  user_registered)
SELECT uid, name, pass, name, name, mail, FROM_UNIXTIME(created)
FROM drupal.users
WHERE uid > 1;



/* Import Drupal Taxonomy -> Wordpress Categories 

Please note that there appears to be a bug in the Wordpress wp_categories table. The cat_ID colun is a bigint(20) but category_parent (which refers to to cat_ID is only an int(4). This is unlikely to cause a problem unless you have a very complicated taxonomy structure but worth bearing in mind!

Please also remember that Wordpress categories operate with a simple hierarchy. The more advanced Drupal features such as 'synonyms' for taxonomy are not available.

*/

INSERT INTO wp.wp_categories (
cat_ID, cat_name, category_nicename, category_description, category_parent)
SELECT term_data.tid, name, name, description, parent
FROM drupal.term_data, drupal.term_hierarchy
WHERE drupal.term_data.tid=drupal.term_hierarchy.tid;



/* Import Drupal blog posts and forum topics

No obvious analog to Drupal forums in Wordpress. Hence they will be copied to Wordpress as blog posts. All posts will be assume Wordpress default status as published, pingable and open to comments.

*/

INSERT INTO wp.wp_posts (
ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_name, post_modified, post_modified_gmt)
SELECT nid, uid, FROM_UNIXTIME(created), FROM_UNIXTIME(created), body, title, teaser, concat('node/', nid), FROM_UNIXTIME(changed), FROM_UNIXTIME(changed)
FROM drupal.node WHERE type IN ('blog', 'forum');



/* Import Drupal static pages

These are imported as Wordpress pages. The 'page slug' needs to be manually set after import to ensure the URIs work.

*/

INSERT INTO wp.wp_posts (
ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, post_name, post_modified, post_modified_gmt)
SELECT nid, uid, FROM_UNIXTIME(created), FROM_UNIXTIME(created), body, title, teaser, 'static', concat('node/', nid), FROM_UNIXTIME(changed), FROM_UNIXTIME(changed)
FROM drupal.node WHERE type = 'page';



/* Import Drupal category associations

*/

INSERT INTO wp.wp_post2cat (
post_id, category_id)
SELECT nid,tid 
FROM drupal.term_node;



/* Import Drupal post comments

Unlike Drupal, Wordpress doesn't have a subject lines for comments so these are simply included in the body of the comment. Also, Wordpress doesn't support threaded comments, so comment threads are collapsed. Assumes you asked Drupal to just store IP addresses and not do a DNS lookup.

*/


INSERT INTO wp.wp_comments (
comment_ID, comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_parent, user_id)
SELECT cid, nid, name, mail, homepage, hostname, FROM_UNIXTIME(timestamp), FROM_UNIXTIME(timestamp), concat('', subject, '<br />', comment), CONVERT(thread, UNSIGNED), uid
FROM drupal.comments;


/* For some reason comment authors need fixing up - Probably because a previous version of Drupal didn't do this properly. */

UPDATE wp.wp_comments LEFT JOIN wp.wp_users ON wp_comments.user_id = wp_users.ID
SET wp_comments.comment_author = wp_users.user_nicename;