Following on from Drupal to WordPress migration explained, I will create a series of posts documenting 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
|
Drupal 6.x |
WordPress 3.x |
Notes |
|
|
|
node |
wp_posts |
|
|
|
nid |
id |
|
|
|
id |
post_author |
|
|
|
created |
post_date |
Create date from UNIX timestamp |
|
|
title |
post_title |
|
|
|
changed |
post_modified |
Create date from UNIX timestamp |
|
|
type |
post_type |
|
|
|
status |
post_status |
|
|
|
vid |
|
|
|
|
|
|
|
|
|
node_revisions |
|
|
|
|
body |
post_content |
|
|
|
teaser |
post_excerpt |
|
|
|
vid |
|
|
|
|
|
|
|
|
node |
url_alias |
|
|
|
nid |
dst |
post_name |
If dst field is NULL, use nid |
|
|
src |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
to_ping |
Whitespace string |
|
|
|
pinged |
Whitespace string |
|
|
|
post_content_filtered |
Whitespace string |
Query
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’);