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
| 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
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.
| Drupal 6.x | WordPress 3.x | Notes |
| term_data | wp_terms | |
| tid | term_id | |
| name | name | |
| name | slug | Make lower case and convert spaces to underscores |
| vid | term_group | Not used in a default WordPress installation |
| term_data | wp_term_taxonomy | |
| tid | term_taxonomy_id | |
| tid | term_id | |
| taxonomy | String: ‘post_tag’ or ‘category’ | |
| description | description | |
| parent | 0 (No parent) |
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.
| Drupal 6.x | WordPress 3.x |
| term_node | wp_term_relationships |
| nid | object_id |
| tid | term_taxonomy_id |
Table mapping for WordPress users
This maps Drupal user export to WordPress.
| Drupal 6.x | WordPress 3.x | Notes |
| users | wp_posts | |
| uid | ID | |
| name | user_login | Format to lowercase, replace spaces with underscores |
| pass | user_pass | |
| name | user_nicename | |
| user_email | ||
| created | user_registered | Formatted from UNIX time |
| name | display_name | |
| user_status | Whitespace string | |
| user_activation_key | Set to 0 | |
Table mapping for WordPress user meta values
User information like capabilities and roles in the wp_usermeta table.
| users | wp_usermeta | |
| uid | user_id | |
| meta_key | Set to string e.g. ‘wp_capabilities’ | |
| meta_value | Set to string e.g. ‘a:1:{s:6:”author”;s:1:”1″;}’ |
More information about the settings for appropriate meta_key and meta_value can be found in the WordPress Codex:
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
| Drupal 6.x | WordPress 3.x | Notes |
| comments | wp_posts | |
| cid | comment_ID | |
| nid | comment_post_ID | |
| timestamp | comment_date | Converted from UNIX timestamp |
| comment | comment_content | |
| pid | comment_parent | |
| name | comment_author | |
| comment_author_email | ||
| homepage | comment_author_url | Truncated to WordPress limit of 200 chars |
| status | comment_approved |
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.