{"id":50880,"date":"2011-03-20T16:43:46","date_gmt":"2011-03-20T05:43:46","guid":{"rendered":"http:\/\/riscy.biz\/?p=50880"},"modified":"2011-03-20T22:47:14","modified_gmt":"2011-03-20T11:47:14","slug":"converting-from-drupal-to-wordpress","status":"publish","type":"post","link":"https:\/\/riscy.biz\/index.php\/2011\/03\/20\/converting-from-drupal-to-wordpress\/","title":{"rendered":"Converting from Drupal to WordPress"},"content":{"rendered":"

Well after looking at this site<\/a> I modified the existing Drupal database to remove all references to the img_assist module. What a pain in the arse that module is, there is no way to easily migrate away from it so I wrote a Perl script to change the tags to <img> tags instead.<\/p>\n

use DBI;
\nmy $databasename = “drupal”;
\nmy $servername = “192.168.1.1”;
\nmy $port = “3306”;
\nmy $dsn = “dbi:mysql:$databasename:$servername:$port”;<\/p>\n

# set the user and password
\nmy $user = ‘xxxxx’;
\nmy $pass = ‘xxxxx’;<\/p>\n

# now connect and get a database handle
\nmy $dbh = DBI->connect($dsn, $user, $pass)
\nor die “Can\u2019t connect to the DB: $DBI::errstr\\n”;<\/p>\n

my $query1=”Select * from node_revisions LEFT JOIN node ON (node_revisions.nid=node.nid) WHERE node.type=’blog'”;
\nmy $action = $dbh->prepare($query1);
\n$action->execute();<\/p>\n

while (@row = $action->fetchrow()) {
\n$body=$row[4];
\nwhile ($body=~m\/\\[img_assist\\|nid=(\\d+)\\|.*\\]\/){
\n$vid=$row[1];
\n$width=450;
\nmy $query2=”Select filepath FROM files LEFT JOIN image ON (files.fid=image.fid) WHERE nid=$1 AND image_size=’_original'”;
\nmy $action2= $dbh->prepare($query2);
\n$action2->execute();
\n@row2 = $action2->fetchrow();
\n$body=~s\/\\[img_assist\\|nid=(\\d+)\\|.*\\]\/<p style=”text-align: center;”><img src=”$row2[0]” width=”$width”><\\\/p>\/;<\/p>\n

($escapequotes=$body)=~s\/\\’\/\\’\\’\/g;
\nmy $query3=”UPDATE node_revisions SET body=’$escapequotes’,teaser=’$escapequotes’ WHERE vid=$vid”;
\nprint “$vid\\n”;
\nmy $action3 = $dbh->prepare($query3);
\n$action3->execute();
\n}
\nwhile ($body=~m\/\\<img.*src\\=\\”\\?q\\=image\\\/view\\\/(\\d+).*\\>\/){<\/p>\n

$vid=$row[1];
\n$nid=$1;
\n$width=450;
\nmy $query2=”Select filepath FROM files LEFT JOIN image ON (files.fid=image.fid) WHERE nid=$nid AND image_size=’_original'”;
\nmy $action2= $dbh->prepare($query2);
\n$action2->execute();
\n@row2 = $action2->fetchrow();
\n$body=~s\/\\<img.*src\\=\\”\\?q\\=image\\\/view\\\/$nid.*\\>\/<img src=”$row2[0]” width=”450″>\/;<\/p>\n

($escapequotes=$body)=~s\/\\’\/\\’\\’\/g;
\nmy $query3=”UPDATE node_revisions SET body=’$escapequotes’,teaser=’$escapequotes’ WHERE vid=$vid”;
\nprint “$vid\\n”;
\nmy $action3 = $dbh->prepare($query3);
\n$action3->execute();
\n}
\n}
\nprint “Finished\\n”;<\/p><\/blockquote>\n

It’s a bit ugly but works, I had to go through twice because some of the tags were using older versions of img_assist, again what a dumb idea. If only the entries had been migrated as the module was updated. Anyway that was done. Then I modified the import script linked at the start of this post to this:<\/p>\n

# This set of instructions was updated by 9seeds.com based on
\n# a post by Mike Smullin at mikesmullin.com
\n#
\n# Mike’s original post can be found here:
\n# http:\/\/www.mikesmullin.com\/development\/migrate-convert-import-drupal-5-to-wordpress-27\/
\n#
\n# 9seeds’ updated post can be found here:
\n# http:\/\/9seeds.com\/news\/drupal-to-wordpress-migration<\/p>\n

# Clear all existing WordPress content
\nTRUNCATE TABLE wordpress.wp_comments;
\nTRUNCATE TABLE wordpress.wp_links;
\nTRUNCATE TABLE wordpress.wp_postmeta;
\nTRUNCATE TABLE wordpress.wp_posts;
\nTRUNCATE TABLE wordpress.wp_term_relationships;
\nTRUNCATE TABLE wordpress.wp_term_taxonomy;
\nTRUNCATE TABLE wordpress.wp_terms;<\/p>\n

# Create Categories
\nINSERT INTO wordpress.wp_terms (term_id, `name`, slug, term_group)
\nSELECT
\nd.tid, d.name, REPLACE(LOWER(d.name), ‘ ‘, ‘_’), 0
\nFROM drupal.term_data d
\nINNER JOIN drupal.term_hierarchy h
\nUSING(tid);<\/p>\n

# Add Taxonomies
\nINSERT INTO wordpress.wp_term_taxonomy (term_id, taxonomy, description, parent)
\nSELECT
\nd.tid `term_id`,
\n‘category’ `taxonomy`,
\nd.description `description`,
\nh.parent `parent`
\nFROM drupal.term_data d
\nINNER JOIN drupal.term_hierarchy h
\nUSING(tid);<\/p>\n

# Import posts\/pages
\nINSERT INTO wordpress.wp_posts (id, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, `post_status`)
\nSELECT DISTINCT
\nn.nid `id`,
\nFROM_UNIXTIME(n.created) `post_date`,
\nr.body `post_content`,
\nn.title `post_title`,
\nr.teaser `post_excerpt`,
\nIF(SUBSTR(a.dst, 11, 1) = ‘\/’, SUBSTR(a.dst, 12), a.dst) `post_name`,
\nFROM_UNIXTIME(n.changed) `post_modified`,
\nn.type `post_type`,
\nIF(n.status = 1, ‘publish’, ‘private’) `post_status`
\nFROM drupal.node n
\nINNER JOIN drupal.node_revisions r
\nUSING(vid)
\nLEFT OUTER JOIN drupal.url_alias a
\nON a.src = CONCAT(‘node\/’, n.nid)
\nWHERE n.type IN (‘blog’);<\/p>\n

# Turn articles in to posts
\nupdate wordpress.wp_posts set post_type=’post’ where post_type=’blog’;<\/p>\n

# Add post to category relationships
\nINSERT INTO wordpress.wp_term_relationships (object_id, term_taxonomy_id)
\nSELECT nid, tid FROM drupal.term_node;<\/p>\n

# Update category count
\nUPDATE wordpress.wp_term_taxonomy tt
\nSET `count` = (
\nSELECT COUNT(tr.object_id)
\nFROM wordpress.wp_term_relationships tr
\nWHERE tr.term_taxonomy_id = tt.term_taxonomy_id);<\/p>\n

# Import comments
\nINSERT INTO wordpress.wp_comments (comment_post_ID, comment_date, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url, comment_approved)
\nSELECT nid, FROM_UNIXTIME(timestamp), comment, thread, name, mail, homepage, status FROM drupal.comments;<\/p>\n

# Update comment counts
\nuse wordpress;
\nUPDATE `wp_posts` SET `comment_count` = (SELECT COUNT(`comment_post_id`) FROM `wp_comments` WHERE `wp_posts`.`id` = `wp_comments`.`comment_post_id`);<\/p>\n

# Fix breaks in post content
\nUPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ”, ”);<\/p>\n

# fix images in post content
\nUPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘”files\/’, ‘”wp-content\/uploads\/’);
\nUPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘”\/files’, ‘”wp-content\/uploads\/’);
\nUPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘”images\/’, ‘”wp-content\/uploads\/images\/’);<\/p>\n

# fix images in post_excerpt\u00a0 content
\nUPDATE wordpress.wp_posts SET post_excerpt = REPLACE(post_excerpt, ‘”files\/’, ‘”wp-content\/uploads\/’);
\nUPDATE wordpress.wp_posts SET post_excerpt = REPLACE(post_excerpt, ‘”\/files’, ‘”wp-content\/uploads\/’);
\nUPDATE wordpress.wp_posts SET post_excerpt = REPLACE(post_excerpt, ‘”images\/’, ‘”wp-content\/uploads\/images\/’);<\/p>\n

# fix links
\nUPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘href=”?q=node\/’, ‘href=”?p=’);
\nUPDATE wordpress.wp_posts SET post_excerpt = REPLACE(post_excerpt, ‘href=”?q=node\/’, ‘href=”?p=’);<\/p>\n

#remove dud characters
\nUPDATE wordpress.wp_posts SET post_content = REPLACE(post_content, ‘\u00c2’, ”);
\nUPDATE wordpress.wp_posts SET post_excerpt = REPLACE(post_excerpt, ‘\u00c2’, ”);<\/p><\/blockquote>\n

I added some extra replacements which fixed any links to old nodes and I also had to include post type ‘blog’ so that all the entries got moved over. I don’t care about pages or stories.<\/p>\n

This is by no means a completed product, it works just enough for me to have a WordPress blog running without too much effort. The migration went better on littlefishcreations.com<\/a> this blog still has almost all the images failing. I might get around to fixing it one day.<\/p>\n","protected":false},"excerpt":{"rendered":"

Well after looking at this site I modified the existing Drupal database to remove all references to the img_assist module. What a pain in the arse that module is, there is no way to easily migrate away from it so … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1],"tags":[2,3],"class_list":["post-50880","post","type-post","status-publish","format-standard","hentry","category-computers","tag-drupal","tag-wordpress"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/posts\/50880","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/comments?post=50880"}],"version-history":[{"count":0,"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/posts\/50880\/revisions"}],"wp:attachment":[{"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/media?parent=50880"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/categories?post=50880"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/riscy.biz\/index.php\/wp-json\/wp\/v2\/tags?post=50880"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}