Migrating from Drupal 7 to Habari .8

Lately I’ve been trying out the latest release of Habari and I really like it. They have created a very simple, yet functional and very clean interface with which to blog (not to mention its code implements the newest of just about everthing). With that, bitnode used to be run on Drupal, so converting from elenty billion articles (that’s the technical number) to posts in Habari was not looking too easy. After some searching, I found that the best way to convert without having to write some sql statements would be to migrate from Drupal 7 to Drupal 6, then from Drupal 6 to Wordpress 2.3; then from Wordpress 2.3 to Habari .8.

What?

So it seemed that manually copying the data from column to column with sql statements would be my best route. After some time (and so so many browser tabs), I finally came up with some queries that would migrate you from Drupal 7 to Habari .8. Please keep in mind that these will not migrate all of your data. These are only for migrating your posts and their related comments.

Assumptions:

  • Habari instance table prefix is habari_

  • Drupal instance table prefix is drupal_

  • Our author user id is 2

- Move our posts over using the drupal ids so we can relate our comments later
insert into `habari_posts` (id, title, slug, content, user_id, status, pubdate, updated) select nid,title,title,body_value, 2, status,  created, changed from drupal_node join drupal_field_data_body on drupal_node.nid=drupal_field_data_body.entity_id;

Here we are doing a simple insert into habari_posts from another table. However, due to Drupal’s robust database structure (not sure if it’s 3NF), we have to query another table for our remaining post data as the meta-data (post subject, various dates, status, etc) is stored in the drupal_node table and the actual post is stored in the drupal_field_data_body table.

Once again, in this query I have statically defined user id 2. You will need to change this to your user’s ID in Habari who you want to show up as posting everything. If you need to import multiple user’s posts, you will need to query for the Drupal user IDs and change the Habari user IDs to match the posts (that’s the easiest way).

- update our drupal published status to the habari version
update habari_posts set status=2 where status=1;
- update our drupal draft status to the habari version
update habari_posts set status=1 where status=0;

Here we are just converting our post statuses from Drupal values to Habari values. In Habari, status 1 is published and status 0 is draft (as of 2011.12.30).

-Now we migrate our comments
insert into habari_comments (post_id, name, email, url, ip, content, status, date) select nid, name, mail, homepage, hostname, comment_body_value, status, created from drupal_comment join drupal_field_data_comment_body on drupal_comment.cid=drupal_field_data_comment_body.entity_id;

Here we are grabbing the comments for each of the posts. Since we pulled in all the post IDs from the Drupal tables in our first query, we can do the same here and everything should line up perfectly. Once again, like with the posts, Drupal stores comment data in more than one table. In Drupal, the comment meta-data is stored in the drupal_comment table and the actual comment data is stored in the drupal_field_data_comment_body table.

And that should be it. You’ve just migrated all of your post and comment data to Habari .8. If you have any images used in your posts, you will also need to copy Drupal’s sites/default/files/ directory to the root directory of your Habari instance.

If anyone tries this out, please let me know how it worked for you. It worked fine for me (evidenced by the fact that bitnode is still viewable), but I’d like some input on how to better write these queries in case there are any additional fields I may have missed that people would be interested in. Thank’s for reading!

Category:Drupal Category:Habari Category:Blogging