Migreren/converteren/importeren van de Drupal 6 database naar WordPress 3

Dit artikel bevat de migratie stappen en het database conversie script.

Installatie van Wordpress

1. Download Wordpress en unzip dit bestand op je webserver. De details voor het installeren van Wordpress kun je hier vinden: Installing WordPress , installeer Wordpress in de /wordpress directory .
2. Maak eerst een lege database aan Wordpress, hiervoor kun je phpMyAdmin gebruiken
3. Open de URL naar waar je WordPress geinstalleerd hebt en volg hier de eerste configuratie stappen
4. Nadat je klaar bent met WordPress configuratie, kun je inloggen in Wordpress en kun je de post “Hello world” permanent verwijderen. Doe hetzelfde met de “About” demo pagina.

Nu zijn we klaar voor de volgende stap.

Het omzetten van de Drupal 6 tabellen in Wordpress 3-database tabellen

Met de volgende stappen, gaan we de Drupal database kopiëren naar de nieuwe Wordpress database

1. Login in phpMyAdmin met voldoende rechten
2. Selecteerd de oude Drupal database in phpMyAdmin en selecteer het tabblad “Handelingen“
3. Ga naar de sectie “Kopieer database naar:”, vul hier de naam van de nieuwe Wordpress database in
4. Laat de selectie op “Structuur en gegevens” staan
5. Deselecteer “CREATE DATABASE before copying”, want de database bestaat al
6. Selecteer “Add DROP TABLE / DROP VIEW“
7. Selecteer “Voeg AUTO_INCREMENT waarde toe“
8. Laat de andere selecties uit
9. Druk nu op de Start-knop om de database te kopiëren

In de volgende stappen gaan we de inhoud van de Drupal tabellen omzetten naar de Wordpress tabellen.

Het database conversie script is niet door mij geschreven, de basis van het script komt hier vandaan: http://blog.room34.com/archives/4530 , het script heb ik hier en daar aangepast.

Het originele script deed de conversie direct vanuit de  Drupal-database. Ik vond manier van kopiëren van de database handiger, want ik had wat problemen bij de conversie. In de kopie kon ik zonder problemen records verwijderen, zonder de originele Drupal site/database te wijzigen. Op deze manier had ik ook alle tijd om de migratie en conversie voor te bereiden, zonder dat de oude live Drupal site down ging. Het script vult nu ook de modification date’s in bij de posts, en nog wat kleine wijzigingetjes.

Hieronder staat het conversie script

# This assumes that WordPress and Drupal are copied in one databases, named 'wordpress'.
# The Drupal tables has the prefix ishk_
# The WordPress tables do not have a prefix
# If your database name or prefixes differ, adjust these accordingly.
# I had to comment out the VIDEO and IMAGE parts of the script. You can find it simply by
# searching VIDEO or IMAGE

# Empty previous content from WordPress database.
TRUNCATE TABLE wordpress.comments;
TRUNCATE TABLE wordpress.links;
TRUNCATE TABLE wordpress.postmeta;
TRUNCATE TABLE wordpress.posts;
TRUNCATE TABLE wordpress.term_relationships;
TRUNCATE TABLE wordpress.term_taxonomy;
TRUNCATE TABLE wordpress.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 admin user (user_id = 1) created during installation.
DELETE FROM wordpress.users WHERE ID > 1;
DELETE FROM wordpress.usermeta WHERE user_id > 1;

# TAGS
# Using REPLACE prevents script from breaking if Drupal contains duplicate terms.
REPLACE INTO wordpress.terms
    (term_id, `name`, slug, term_group)
    SELECT DISTINCT
    d.tid, d.name, REPLACE(LOWER(d.name), ' ', '_'), 0
    FROM wordpress.ishk_term_data d
    INNER JOIN wordpress.ishk_term_hierarchy h
    USING(tid)
    INNER JOIN wordpress.ishk_term_node n
    USING(tid)
    WHERE (1
     # This helps eliminate spam tags from import; uncomment if necessary.
     # AND LENGTH(d.name) < 50
    )
;

INSERT INTO wordpress.term_taxonomy
    (term_id, taxonomy, description, parent)
    SELECT DISTINCT
    d.tid `term_id`,
    'post_tag' `taxonomy`,
    d.description `description`,
    h.parent `parent`
    FROM wordpress.ishk_term_data d
    INNER JOIN wordpress.ishk_term_hierarchy h
    USING(tid)
    INNER JOIN wordpress.ishk_term_node n
    USING(tid)
    WHERE (1
     # This helps eliminate spam tags from import; uncomment if necessary.
     # AND LENGTH(d.name) < 50
    )
;

# POSTS
# Keeps private posts hidden.
INSERT INTO wordpress.posts
    (id, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_name, post_modified, post_modified_gmt, post_type, `post_status`)
    SELECT DISTINCT
    n.nid `id`,
    n.uid `post_author`,
    FROM_UNIXTIME(n.created) `post_date`,
    FROM_UNIXTIME(n.created-3600) `post_date_gmt`,
    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`,
    FROM_UNIXTIME(n.changed-3600) `post_modified_gmt`,
    n.type `post_type`,
    IF(n.status = 1, 'publish', 'private') `post_status`
    FROM wordpress.ishk_node n
    INNER JOIN wordpress.ishk_node_revisions r
    USING(vid)
    LEFT OUTER JOIN wordpress.ishk_url_alias a
    ON a.src = CONCAT('node/', n.nid)
    # Add more Drupal content types below if applicable.
    WHERE n.type IN ('post', 'page', 'blog')
;

# Fix post type; http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/#comment-17826
# Add more Drupal content types below if applicable.
UPDATE wordpress.posts
    SET post_type = 'post'
    WHERE post_type IN ('blog')
;

# Set all pages to "pending".
# If you're keeping the same page structure from Drupal, comment out this query
# and the new page INSERT at the end of this script.
UPDATE wordpress.posts SET post_status = 'pending' WHERE post_type = 'page';

# POST/TAG RELATIONSHIPS
INSERT INTO wordpress.term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT nid, tid FROM wordpress.ishk_term_node
;

# Update tag counts.
UPDATE term_taxonomy tt
    SET `count` = (
    SELECT COUNT(tr.object_id)
    FROM term_relationships tr
    WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
    )
;

# COMMENTS
# Keeps unapproved comments hidden.
# Incorporates change noted here: http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/#comment-32169
INSERT INTO wordpress.comments
    (comment_post_ID, comment_date, comment_date_gmt, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url, comment_approved)
    SELECT DISTINCT
    nid, FROM_UNIXTIME(timestamp), FROM_UNIXTIME(timestamp-3600), comment, thread, name,
    mail, homepage, ((status + 1) % 2)
    FROM wordpress.ishk_comments
;

# Update comments count on posts table.
UPDATE wordpress.posts
    SET `comment_count` = (
    SELECT COUNT(`comment_post_id`)
    FROM wordpress.comments
    WHERE wordpress.posts.`id` = wordpress.comments.`comment_post_id`
    )
;

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

# Fix taxonomy; http://www.mikesmullin.com/development/migrate-convert-import-drupal-5-to-wordpress-27/#comment-27140
UPDATE IGNORE wordpress.term_relationships, wordpress.term_taxonomy
    SET wordpress.term_relationships.term_taxonomy_id = wordpress.term_taxonomy.term_taxonomy_id
    WHERE wordpress.term_relationships.term_taxonomy_id = wordpress.term_taxonomy.term_id
;

# OPTIONAL ADDITIONS — REMOVE ALL BELOW IF NOT APPLICABLE TO YOUR CONFIGURATION

# CATEGORIES
# These are NEW categories, not in wordpress.ishk_ Add as many sets as needed.
INSERT IGNORE INTO wordpress.terms (name, slug)
    VALUES
    ('First Category', 'first-category'),
    ('Second Category', 'second-category'),
    ('Third Category', 'third-category')
;

# Set category names to title case (in case term already exists [as a tag] in lowercase).
UPDATE wordpress.terms SET name = 'First Category' WHERE name = 'first category';
UPDATE wordpress.terms SET name = 'Second Category' WHERE name = 'second category';
UPDATE wordpress.terms SET name = 'Third Category' WHERE name = 'third category';

# Add categories to taxonomy.
INSERT INTO wordpress.term_taxonomy (term_id, taxonomy)
    VALUES
    ((SELECT term_id FROM terms WHERE slug = 'first-category'), 'category'),
    ((SELECT term_id FROM terms WHERE slug = 'second-category'), 'category'),
    ((SELECT term_id FROM terms WHERE slug = 'third-category'), 'category')
;

# Auto-assign posts to category.
# You'll need to work out your own logic to determine strings/terms to match.
# Repeat this block as needed for each category you're creating.
INSERT IGNORE INTO wordpress.term_relationships (object_id, term_taxonomy_id)
    SELECT DISTINCT p.ID AS object_id,
    (SELECT tt.term_taxonomy_id
    FROM wordpress.term_taxonomy tt
    INNER JOIN wordpress.terms t USING (term_id)
    WHERE t.slug = 'enter-category-slug-here'
    AND tt.taxonomy = 'category') AS term_taxonomy_id
    FROM wordpress.posts p
    WHERE p.post_content LIKE '%enter string to match here%'
    OR p.ID IN (
    SELECT tr.object_id
    FROM wordpress.term_taxonomy tt
    INNER JOIN wordpress.terms t USING (term_id)
    INNER JOIN wordpress.term_relationships tr USING (term_taxonomy_id)
    WHERE t.slug IN ('enter','terms','to','match','here')
    AND tt.taxonomy = 'post_tag'
    )
;

# Update category counts.
UPDATE term_taxonomy tt
    SET `count` = (
    SELECT COUNT(tr.object_id)
    FROM term_relationships tr
    WHERE tr.term_taxonomy_id = tt.term_taxonomy_id
    )
;

# AUTHORS
INSERT IGNORE INTO wordpress.users
    (ID, user_login, user_pass, user_nicename, user_email,
    user_registered, user_activation_key, user_status, display_name)
    SELECT DISTINCT
    u.uid, u.mail, NULL, u.name, u.mail,
    FROM_UNIXTIME(created), '', 0, u.name
    FROM wordpress.ishk_users u
    INNER JOIN wordpress.ishk_users_roles r
    USING (uid)
    WHERE (1
    # Uncomment and enter any email addresses you want to exclude below.
    # AND u.mail NOT IN ('test@example.com')
    )
;

# Assign author permissions.
# Sets all authors to "author" by default; next section can selectively promote individual authors
INSERT IGNORE INTO wordpress.usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
    u.uid, 'capabilities', 'a:1:{s:6:"author";s:1:"1";}'
    FROM wordpress.ishk_users u
    INNER JOIN wordpress.ishk_users_roles r
    USING (uid)
    WHERE (1
    # Uncomment and enter any email addresses you want to exclude below.
    # AND u.mail NOT IN ('test@example.com')
    )
;
INSERT IGNORE INTO wordpress.usermeta (user_id, meta_key, meta_value)
    SELECT DISTINCT
    u.uid, 'user_level', '2'
    FROM wordpress.ishk_users u
    INNER JOIN wordpress.ishk_users_roles r
    USING (uid)
    WHERE (1
    # Uncomment and enter any email addresses you want to exclude below.
    # AND u.mail NOT IN ('test@example.com')
    )
;

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

# Reassign post authorship.
UPDATE wordpress.posts
    SET post_author = NULL
    WHERE post_author NOT IN (SELECT DISTINCT ID FROM wordpress.users)
;

# VIDEO – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# If your Drupal site uses the content_field_video table to store links to YouTube videos,
# this query will insert the video URLs at the end of all relevant posts.
# WordPress will automatically convert the video URLs to YouTube embed code.;
UPDATE IGNORE wordpress.posts p, wordpress.ishk_content_field_video v
    SET p.post_content = CONCAT_WS('\n',post_content,v.field_video_embed)
    WHERE p.ID = v.nid
;

# IMAGES – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# If your Drupal site uses the content_field_image table to store images associated with posts,
# but not actually referenced in the content of the posts themselves, this query
# will insert the images at the top of the post.
# HTML/CSS NOTE: The code applies a "drupal_image" class to the image and places it inside a <div>
# with the "drupal_image_wrapper" class. Add CSS to your WordPress theme as appropriate to
# handle styling of these elements. The <img> tag as written assumes you'll be copying the
# Drupal "files" directory into the root level of WordPress, NOT placing it inside the
# "wp-content/uploads" directory. It also relies on a properly formatted <base href="" /> tag.
# Make changes as necessary before running this script!
UPDATE IGNORE qb102200_arjans_logboek.posts p, qb102200_arjans_logboek.ishk_content_field_image i, qb102200_arjans_logboek.ishk_files f
   SET p.post_content =
   CONCAT(
       CONCAT(
           '<div class="drupal_image_wrapper"><img src="files/',
           f.filename,
           '" class="drupal_image" /></div>'
       ),
       p.post_content
    )
    WHERE p.ID = i.nid
    AND i.field_image_fid = f.fid
    AND (
    f.filename LIKE '%.jpg'
    OR f.filename LIKE '%.jpeg'
    OR f.filename LIKE '%.png'
    OR f.filename LIKE '%.gif'
    )
;

# Fix post_name 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.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.posts
    SET post_content = REPLACE(post_content,'

','')
;
UPDATE wordpress.posts
    SET post_content = REPLACE(post_content,'

','')
;

# NEW PAGES – READ BELOW AND COMMENT OUT IF NOT APPLICABLE TO YOUR SITE
# MUST COME LAST IN THE SCRIPT AFTER ALL OTHER QUERIES!
# If your site will contain new pages, you can set up the basic structure for them here.
# Once the import is complete, go into the WordPress admin and copy content from the Drupal
# pages (which are set to "pending" in a query above) into the appropriate new pages.
INSERT INTO wordpress.posts
    (`post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`,
    `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`,
    `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`,
    `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`,
    `post_mime_type`, `comment_count`)
    VALUES
    (1, NOW(), NOW(), 'Page content goes here, or leave this value empty.', 'Page Title',
    '', 'publish', 'closed', 'closed', '',
    'slug-goes-here', '', '', NOW(), NOW(),
    '', 0, 'http://full.url.to.page.goes.here', 1, 'page', '', 0)
;

Om het conversie script te draaien, moet je het volgende doen:

1. Login in phpMyAdmin met voldoende rechten
2. Klik op de nieuwe Wordpress database
3. Klik op het tabblad “SQL”
4. Selecteer het gehele bovenstaande script en kopieer deze (Ctrl-C)
5. Open je favoriete editor en plak (Ctrl-V) het script in de editor
6. Je kunt in het script de conversie van VIDEO en IMAGE uit commentariëren met #, als dit niet gebruikt wordt
7. Selecteer het volledige script in de editor (Ctrl-A) en plak deze in de “SQL” tab
8. Druk op de Start-knop om de conversie te starten
9. Het is mogelijk om het script meerdere keren te draaien, totdat de conversie succesvol is

Ik had de volgende problemen tijdens het draaien van het script:

• 4 van de 200 posten hadden een key foutmelding tijdens het draaien van het script. Ik heb deze deze records gewoon verwijderd, en het script weer gestart tot het succesvol afgerond was
• Ik heb de 4 verwijderde artikelen met de de hand vanuit de oude Drupal 6 website naar de nieuwe Wordpress 3 website gekopieerd
• Ik heb in het script het VIDEO en IMAGE deel uitgecommentarieerd
• Het script biedt geen ondersteuning voor CCK