Drupal to WordPress Migration

drupal-to-wordpress.sql

# DRUPAL-TO-WORDPRESS CONVERSION SCRIPT

# Changelog

# 10.02.2012 - Updated by Kelly Dwan !! THIS VERSION HAS NOT BEEN TESTED !!
# 07.29.2010 - Updated by Scott Anderson / Room 34 Creative Services http://blog.room34.com/archives/4530
# 02.06.2009 - Updated by Mike Smullin http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/
# 05.15.2007 - Updated by D’Arcy Norman http://www.darcynorman.net/2007/05/15/how-to-migrate-from-drupal-5-to-wordpress-2/
# 05.19.2006 - Created by Dave Dash http://spindrop.us/2006/05/19/migrating-from-drupal-47-to-wordpress/

# [STEP 1] Install WordPress using normal install process. Set up multisite, but don't create other sites yet
# This assumes that WordPress and Drupal are in separate databases, named `wordpress` and `drupal`.
# Also assuming the current `drupal` database is only one single site (the "main" site)

# [STEP 2] Run the following script to migrate pages & users.
# Empty previous content from WordPress database.
TRUNCATE TABLE wordpress.wp_comments;
TRUNCATE TABLE wordpress.wp_links;
TRUNCATE TABLE wordpress.wp_postmeta;
TRUNCATE TABLE wordpress.wp_posts;
TRUNCATE TABLE wordpress.wp_term_relationships;
TRUNCATE TABLE wordpress.wp_term_taxonomy;
TRUNCATE TABLE wordpress.wp_terms;

# If you're not bringing over multiple Drupal authors, comment out these lines and the other
# author-related queries near the bottom of the script.
# This assumes you're keeping the default WP admin user (user_id = 1) created during installation.
DELETE FROM wordpress.wp_users WHERE ID > 1;
DELETE FROM wordpress.wp_usermeta WHERE user_id > 1;

# POSTS
# Keeps private posts hidden.
INSERT INTO wordpress.wp_posts
	(id, post_author, post_date, post_content, post_title, post_excerpt,
	post_name, post_modified, post_type, `post_status`)
	SELECT DISTINCT
		n.nid `id`,
		n.uid `post_author`,
		FROM_UNIXTIME(n.created) `post_date`,
		r.body `post_content`,
		n.title `post_title`,
		r.teaser `post_excerpt`,
		IF(SUBSTR(a.dst, 11, 1) = '/', SUBSTR(a.dst, 12), a.dst) `post_name`,
		FROM_UNIXTIME(n.changed) `post_modified`,
		n.type `post_type`,
		IF(n.status = 1, 'publish', 'private') `post_status`
	FROM drupal.node n
	INNER JOIN drupal.node_revisions r
		USING(vid)
	LEFT OUTER JOIN drupal.url_alias a
		ON a.src = CONCAT('node/', n.nid)
	# Add more Drupal content types below if applicable.
	WHERE n.type IN ('page')
;

# Fix images in post content; uncomment if you're moving files from "files" to "wp-content/uploads".
UPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, '"/files/', '"/wp-content/uploads/');

# USERS
# Bring over all drupal users. [TODO] Check what the user_login needs to be for LDAP verification. 
#  Could probably manipulate this even after import.
# (IGNORE here skips already-existing users)
INSERT IGNORE INTO wordpress.wp_users
	(ID, user_login, user_pass, user_nicename, user_email,
	user_registered, user_activation_key, user_status, display_name)
	SELECT DISTINCT
		u.uid, u.name, NULL, u.name, u.mail,
		FROM_UNIXTIME(created), '', 0, u.name
	FROM drupal.users u
	INNER JOIN drupal.users_roles r
		USING (uid)
	WHERE (1
		# Uncomment and enter any email addresses you want to exclude below.
		# AND u.mail NOT IN ('[email protected]')
	)
;

# Assign author permissions.
# Sets all authors to "author" by default; next section can selectively promote individual authors
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
	SELECT DISTINCT
		u.uid, 'wp_capabilities', 'a:1:{s:6:"author";s:1:"1";}'
	FROM drupal.users u
	INNER JOIN drupal.users_roles r
		USING (uid)
	WHERE (1
		# Uncomment and enter any email addresses you want to exclude below.
		# AND u.mail NOT IN ('[email protected]')
	)
;
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
	SELECT DISTINCT
		u.uid, 'wp_user_level', '2'
	FROM drupal.users u
	INNER JOIN drupal.users_roles r
		USING (uid)
	WHERE (1
		# Uncomment and enter any email addresses you want to exclude below.
		# AND u.mail NOT IN ('[email protected]')
	)
;

# Change permissions for admins.
# Add any specific user IDs to IN list to make them administrators.
# User ID values are carried over from Drupal.
UPDATE wordpress.wp_usermeta
	SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
	WHERE user_id IN (1) AND meta_key = 'wp_capabilities'
;
UPDATE wordpress.wp_usermeta
	SET meta_value = '10'
	WHERE user_id IN (1) AND meta_key = 'wp_user_level'
;

# Wipe post author if that user does not exist
UPDATE wordpress.wp_posts
	SET post_author = NULL
	WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users)
;

# Fix post_name (slug) to remove paths.
# If applicable; Drupal allows paths (i.e. slashes) in the dst field, but this breaks
# WordPress URLs. If you have mod_rewrite turned on, stripping out the portion before
# the final slash will allow old site links to work properly, even if the path before
# the slash is different!
UPDATE wordpress.wp_posts
	SET post_name =
	REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1))
;

# Miscellaneous clean-up.
# There may be some extraneous blank spaces in your Drupal posts; use these queries
# or other similar ones to strip out the undesirable tags.
UPDATE wordpress.wp_posts
	SET post_content = REPLACE(post_content,'<p>&nbsp;</p>','')
;
UPDATE wordpress.wp_posts
	SET post_content = REPLACE(post_content,'<p class="italic">&nbsp;</p>','')
;

# [STEP 3] If other sites need to be migrated, create those via WP UI

# [STEP 4] Run the following script, assuming your next drupal site DB is `drupal_2` & 
# WP is still `wordpress` (which shouldn't change).

# Empty previous content from WordPress database.
TRUNCATE TABLE wordpress.wp_2_comments;
TRUNCATE TABLE wordpress.wp_2_links;
TRUNCATE TABLE wordpress.wp_2_postmeta;
TRUNCATE TABLE wordpress.wp_2_posts;
TRUNCATE TABLE wordpress.wp_2_term_relationships;
TRUNCATE TABLE wordpress.wp_2_term_taxonomy;
TRUNCATE TABLE wordpress.wp_2_terms;

# !! We are not re-deleting the users table, since we've already imported some users there

# POSTS
# Keeps private posts hidden.
INSERT INTO wordpress.wp_2_posts
	(id, post_author, post_date, post_content, post_title, post_excerpt,
	post_name, post_modified, post_type, `post_status`)
	SELECT DISTINCT
		n.nid `id`,
		n.uid `post_author`,
		FROM_UNIXTIME(n.created) `post_date`,
		r.body `post_content`,
		n.title `post_title`,
		r.teaser `post_excerpt`,
		IF(SUBSTR(a.dst, 11, 1) = '/', SUBSTR(a.dst, 12), a.dst) `post_name`,
		FROM_UNIXTIME(n.changed) `post_modified`,
		n.type `post_type`,
		IF(n.status = 1, 'publish', 'private') `post_status`
	FROM drupal_2.node n
	INNER JOIN drupal_2.node_revisions r
		USING(vid)
	LEFT OUTER JOIN drupal_2.url_alias a
		ON a.src = CONCAT('node/', n.nid)
	# Add more Drupal content types below if applicable.
	WHERE n.type IN ('page')
;

# Fix images in post content; uncomment if you're moving files from "files" to "wp-content/uploads".
UPDATE wordpress.wp_2_posts SET post_content = REPLACE(post_content, '"/files/', '"/wp-content/uploads/');

# Assign author permissions. All users are in the site from main import
# Sets all authors to "author" by default; next section can selectively promote individual authors
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
	SELECT DISTINCT
		u.uid, 'wp_2_capabilities', 'a:1:{s:6:"author";s:1:"1";}'
	FROM drupal_2.users u
	INNER JOIN drupal_2.users_roles r
		USING (uid)
	WHERE (1
		# Uncomment and enter any email addresses you want to exclude below.
		# AND u.mail NOT IN ('[email protected]')
	)
;
INSERT IGNORE INTO wordpress.wp_usermeta (user_id, meta_key, meta_value)
	SELECT DISTINCT
		u.uid, 'wp_2_user_level', '2'
	FROM drupal_2.users u
	INNER JOIN drupal_2.users_roles r
		USING (uid)
	WHERE (1
		# Uncomment and enter any email addresses you want to exclude below.
		# AND u.mail NOT IN ('[email protected]')
	)
;

# Change permissions for admins.
# Add any specific user IDs to IN list to make them administrators.
# User ID values are carried over from Drupal.
UPDATE wordpress.wp_usermeta
	SET meta_value = 'a:1:{s:13:"administrator";s:1:"1";}'
	WHERE user_id IN (1) AND meta_key = 'wp_2_capabilities'
;
UPDATE wordpress.wp_usermeta
	SET meta_value = '10'
	WHERE user_id IN (1) AND meta_key = 'wp_2_user_level'
;

# Wipe post author if that user does not exist
UPDATE wordpress.wp_2_posts
	SET post_author = NULL
	WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.wp_users)
;

# Fix post_name (slug) to remove paths.
# If applicable; Drupal allows paths (i.e. slashes) in the dst field, but this breaks
# WordPress URLs. If you have mod_rewrite turned on, stripping out the portion before
# the final slash will allow old site links to work properly, even if the path before
# the slash is different!
UPDATE wordpress.wp_2_posts
	SET post_name =
	REVERSE(SUBSTRING(REVERSE(post_name),1,LOCATE('/',REVERSE(post_name))-1))
;

# Miscellaneous clean-up.
# There may be some extraneous blank spaces in your Drupal posts; use these queries
# or other similar ones to strip out the undesirable tags.
UPDATE wordpress.wp_2_posts
	SET post_content = REPLACE(post_content,'<p>&nbsp;</p>','')
;
UPDATE wordpress.wp_2_posts
	SET post_content = REPLACE(post_content,'<p class="italic">&nbsp;</p>','')
;

[STEP 5] Repeat 3/4 replacing the _2 with your new site ID.