Drupal to WordPress migration table mapping

Following on from Drupal to WordPress migration explained, this post documents the table mappings for a site migration from Drupal 6.x to WordPress 3.x.

To read the mapping, you look up the Drupal table on the left listing the fields we use for a migration. Directly to its right is the WordPress table with the corresponding field in the same row. So for example, the nid in Drupal’s node table is exported to the id field in the WordPress wp_posts table.

I have listed all the fields used in the query. If a Drupal field shows no mapping in the WordPress table, it is being used to match entries in another table for a join. Here we use the vid field in node and node_revisions for an INNER JOIN.

Table mapping for WordPress wp_posts

Query

REPLACE INTO wordpress.wp_posts (

		id,

		post_author,

		post_date,

		post_content,

		post_title,

		post_excerpt,

		post_name,

		post_modified,

		post_type,

		post_status,

		to_ping,

		pinged,

		post_content_filtered)

	SELECT DISTINCT

		n.nid ‘id’,

		n.uid ‘post_author’,

		DATE_ADD(FROM_UNIXTIME(0), interval n.created second) ‘post_date’,

		r.body ‘post_content’,

		n.title ‘post_title’,

		r.teaser ‘post_excerpt’,

		IF(a.dst IS NULL,n.nid, SUBSTRING_INDEX(a.dst, ‘/’, -1)) ‘post_name’,

		DATE_ADD(FROM_UNIXTIME(0), interval n.changed second) ‘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)

		WHERE n.type IN (

			/* List the content types you want to migrate */

			‘page’,

			‘story’,

			‘blog’,

			‘video’,

			‘forum’,

			‘comment’);

Table mapping for WordPress terms

This table mapping exports the Drupal terms into WordPress.

In the WordPress Taxonomy documentation, “term_group is a means of grouping together similar terms.” During a standard migration, the WordPress term_group is set to the Drupal vocabulary ID, which seems to make sense. Nevertheless, a default WordPress installation does not actually use the value for anything. It may have been included by the developers for future expandability or use by plugins.

term_group=0 is the default value when creating a term using the Drupal user interface.

Below, we associate posts with the newly migrated terms.

Table mapping for WordPress users

This maps Drupal user export to WordPress.

Table mapping for WordPress user meta values

User information like capabilities and roles in the wp_usermeta table.

More information about the settings for appropriate meta_key and meta_value can be found in the WordPress Codex:

• Roles and Capabilities
• User Levels

Node authors and comment authors

Drupal stores both node authors and comment authors in the users table. WordPress handles things differently. Page and post authors are stored in the wp_users table but comment authors are stored in wp_comments together with the comment data.

Table mapping for WordPress comments

Comment authors

A note about the different ways Drupal and WordPress store comment author information: Drupal stores comment authors in its users table alongside site users like node authors. In WordPress, comment authors are stored in its wp_comments together with the comment data. WordPress comment authors are not entered into the wp_users table.