Thursday, March 31, 2011

Converting Drupal to Wordpress

How to convert drupal 6.x(drupal) to wordpress 2.7(wordpress)

Login to mysql

mysql>use drupal;

Check if any duplicate posts are present(ex: same post having diff. teasers)

SELECT n.nid `ID`,count(*) FROM node n INNER JOIN node_revisions r USING(vid) LEFT OUTER JOIN url_alias a ON a.src = CONCAT('node/', n.nid) WHERE n.type IN ('post', 'page', 'blog', 'book') group by n.nid having count(*) >1;

If there are no duplicates.

insert into wordpress.wp_posts (ID, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, post_status) SELECT DISTINCT n.nid `ID`, FROM_UNIXTIME(n.created) `post_date`, 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`, 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 ('post', 'page', 'blog', 'book', 'story');

In case of duplicates, insert posts excluding those.

For example if 4,7 are the ID's having duplicates

insert into wordpress.wp_posts (ID, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, post_status) SELECT DISTINCT n.nid `ID`, FROM_UNIXTIME(n.created) `post_date`, 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`, 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 ('post', 'page', 'blog', 'book') and n.nid not in ('4', '7');

insert into wordpress.wp_posts (ID, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, post_status) SELECT DISTINCT n.nid `ID`, FROM_UNIXTIME(n.created) `post_date`, 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`, 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 ('post', 'page', 'blog', 'book') and n.nid='4' order by r.timestamp limit 1;

insert into wordpress.wp_posts (ID, post_date, post_content, post_title, post_excerpt, post_name, post_modified, post_type, post_status) SELECT DISTINCT n.nid `ID`, FROM_UNIXTIME(n.created) `post_date`, 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`, 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 ('post', 'page', 'blog', 'book') and n.nid='7' order by r.timestamp limit 1;

Set all the post type depending on whether they are pages or posts.

update wp_posts set post_type='post';

insert into techubs_com.wp_terms(name, slug) SELECT link_title, link_title from menu_links where has_children=1 and router_path='node/%';

insert into wordpress.wp_term_taxonomy(term_taxonomy_id, term_id) SELECT term_id, term_id from wordpress.wp_terms;

update wordpress.wp_term_taxonomy set taxonomy='category';

Run the below script-------

#!/bin/bash

mysql -hZZZZZ -uroot -pXXXXXX -Ddrupal -s -e "select mlid from menu_links where has_children=1 and router_path='node/%';" >/home/srk/drupaltowordpress/drupalmlids

touch /home/srk/drupaltowordpress/drupalpostcount

i=/home/srk/drupaltowordpress/drupalmlids

for a in `cat $i`

do

linktitle=`mysql -hZZZZZ -uroot -pXXXXXX -Ddrupal -s -e "select link_title from menu_links where mlid='$a';"`

mysql -hZZZZZ -uroot -pXXXXXX -Dwordpress -s -e "update wp_term_taxonomy set count = (select count(1) from drupal.menu_links where plid='$a') where wp_term_taxonomy.term_taxonomy_id = (select term_id from wordpress.wp_terms where name='$linktitle') ;" >>/home/srk/drupaltowordpress/drupalpostcount

done

mysql -hZZZZZ -uroot -pXXXXXX -Ddrupal -s -e "SELECT link_path from menu_links where plid='$a';" >/home/srk/drupaltowordpress/drupallinkno

sed 's/node\///g' < /home/srk/drupaltowordpress/drupallinkno >/home/srk/drupaltowordpress/drupallinknoedited

echo "use " wordpress";" >/home/srk/drupaltowordpress/drupalquery.sql

gid=`mysql -hZZZZZ -uroot -pXXXXXX -Dwordpress -s -e "select w.term_id from wp_terms w, drupal.menu_links m where m.mlid='$a' and m.link_title=w.name;"`

l=/home/srk/drupaltowordpress/drupallinknoedited

for m in `cat $l`

do

echo "INSERT INTO \`wordpress\`.\`wp_term_relationships\` (object_id, term_taxonomy_id) VALUES ('$m', '$gid');" >>/home/srk/drupaltowordpress/drupalquery.sql

done

/usr/bin/mysql -uroot -pXXXXXX -hZZZZZ -vv
done

-----------End Of Script----------------------

In case we need a post to be displayed in the front page (like the description of drupal)

Change that post to page and in wp-admin settings set it as default page.

select w.term_id from wordpress.wp_terms w, drupal.menu_links m where m.mlid='166' and m.link_title=w.name;

update wordpress.wp_posts set post_type='page' where ID='333';--------default drupal

For duplicate entries, where we added one teaser, create 301 re-directs manually.