A tweaked version of a script from modeling-languages.com to convert Drupal 6 blog posts to Wordpress

DrupalToWordpress.java

/*
 * 
 *
 * Simple Drupal 6 to Wordpress 3 migrating class for the modeling-languages.com portal
 * 
 * 
 * @version 0.1 10 June 2011
 * @author Jordi Cabot 
 *
 * 
 * Software licensed under Creative Commons Attribution Non-Commercial 3.0 License
 * 
 * If you are not familiar with this license please read the full details here: http://creativecommons.org/licenses/by-nc/3.0/
 * 
 *  
 */




import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DrupalToWordpress {
	

	/**
	 * @param args
	 */
	public static void main(String[] args) 
	{
	  try
	  {
		String wpPrefix="wp_";
		String drPrefix="";
		Connection dbConnect = getConnection();
		
		//Deleting old data
		deleteWPData(dbConnect,wpPrefix);
		System.out.println("Data cleaning finished");
		
		//Creating the category taxonomy
		createCategories(dbConnect,wpPrefix,drPrefix);
		System.out.println("====================== Categories created ====================");
		
		//Creating the posts
		createPosts(dbConnect,wpPrefix,drPrefix);
		System.out.println("====================== Posts created ====================");
		
		//Creating the comments
		createComments(dbConnect,wpPrefix,drPrefix);
		System.out.println("====================== Comments created ====================");
				
		//Connection to the database (we assume both drupal and wordpress tables are in the same database)
	  } catch (Exception e) {System.err.println(e.getClass()+ "  " + e.getMessage());System.exit(-1);}
	
	}
	
    //Establishing the connection with the database
	static Connection getConnection() throws Exception
	{	
    	// Loading the MySQL driver
	    Class.forName("com.mysql.jdbc.Driver");
	    return DriverManager.getConnection("jdbc:mysql://localhost/databasename?" + "user=databaseuser&password=databasepassword");
   	}
	
	//Truncating the data from wordpress tables
	static void deleteWPData(Connection dbConnect, String wpPrefix) throws SQLException
	{
		truncateTable(dbConnect,wpPrefix+"comments");
		truncateTable(dbConnect,wpPrefix+"links");
		truncateTable(dbConnect,wpPrefix+"posts");
		truncateTable(dbConnect,wpPrefix+"postmeta");
		truncateTable(dbConnect,wpPrefix+"term_relationships");
		truncateTable(dbConnect,wpPrefix+"term_taxonomy");
		truncateTable(dbConnect,wpPrefix+"terms");
		
		//We also delete all users except for the first one (the site administrator)
		removeWPUsers(dbConnect,wpPrefix);
	}

	static void createCategories(Connection dbConnect, String wpPrefix, String drPrefix) throws SQLException
	{
		//Retrieving term_data from Drupal
		//At least in my case, vid=3 indicates categories while vid=4 indicates forum topics
		//before launching the script make sure that term_data does not contain redundancies (which anyway indicate an anomalous situation)
		Statement stmt = dbConnect.createStatement();
	    String sqlQuery = "SELECT tid,vid,name FROM " + drPrefix+"term_data WHERE vid=3" ; 
		ResultSet resultSet = stmt.executeQuery(sqlQuery);
		String name, description,slug, sqlUpdate; int tid;
		Statement stmtUpdate = dbConnect.createStatement();
		while (resultSet.next()) 
		{
		  // For each Drupal category we create the corresponding category in wordpress
		  name=resultSet.getString("name");
		  tid=resultSet.getInt("tid");
		  slug=name.toLowerCase().replace(' ', '_'); //slug are lowercase and without blanks
	      sqlUpdate="INSERT INTO "+ wpPrefix + "terms (term_id, name, slug, term_group) VALUES " +
	      		"("+tid+",'"+name+"','"+slug+"',"+"0"+")";
	      stmtUpdate.executeUpdate(sqlUpdate);
	      System.out.println("Category " + name +" created" );
		}
		stmt.close();
	    stmtUpdate.close();
		
		//Now we add the taxonomy relations
		
		Statement stmtTax = dbConnect.createStatement();
	    String sqlQueryTax = "SELECT td.tid, td.description, th.parent FROM " +drPrefix+"term_data td, " + drPrefix+"term_hierarchy th  WHERE td.tid=th.tid and td.vid=3" ;
		ResultSet resultSetTax = stmtTax.executeQuery(sqlQueryTax);
		String descriptionTax,sqlUpdateTax; int tidTax,parentTax;
		Statement stmtUpdateTax = dbConnect.createStatement();
		while (resultSetTax.next()) 
		{
		  descriptionTax=resultSetTax.getString("description");
		  tidTax=resultSetTax.getInt("tid");
		  parentTax=resultSetTax.getInt("parent");
		  //We use as id of the taxonomy the same id as the term. This assumption is used afterwards
		  //when assigning posts to categories!!
		  sqlUpdateTax="INSERT INTO "+ wpPrefix + "term_taxonomy (term_taxonomy_id, term_id,taxonomy,description,parent,count) VALUES " +
	      		"("+tidTax+","+tidTax+",'"+"category"+"','"+descriptionTax+"',"+parentTax+"," + "0"+")";
	      stmtUpdateTax.executeUpdate(sqlUpdateTax);
	      System.out.println("Category hierarchy " + tidTax + "-" + parentTax+ " created" );
	     
		}
		stmtTax.close(); 
		stmtUpdateTax.close();
	}
	
	//Filling the wp_posts table
    static void createPosts(Connection dbConnect, String wpPrefix, String drPrefix) throws SQLException
    {
    	//Forum post are ignored in this method. All posts were created by the same admin user (if not you'll need to 
    	//migrate drupal users, not done here
    	
		Statement stmt = dbConnect.createStatement();
	    String sqlQuery = "SELECT n.nid, n.uid, FROM_UNIXTIME(n.created) created, FROM_UNIXTIME(n.changed) modified, n.TYPE, " +
	    		"n.status, n.title, r.teaser, r.body, '' url " +
	    		"FROM " + drPrefix+ "node n, "+ drPrefix + "node_revisions r "+ 
	    		"WHERE n.nid=r.nid AND n.TYPE IN ('blog','story','page','forum')";
		
		ResultSet resultSet = stmt.executeQuery(sqlQuery);
		int nid,uid,status;
		String created,modified, title,teaser,body,url,type, strStatus,strType;
			 
 	   //SQL insert statement that will be used for each post. The number of comments will be updated later on.
 	   //We use a prepared statement to avoid problems wiht ' and " in the body of the post
 	   String sqlUpdate="INSERT INTO "+ wpPrefix + "posts (id, post_author, post_date, post_date_gmt, post_content, post_title," +
 	   		"post_excerpt,post_status, comment_status, ping_status, post_name, post_parent,menu_order, post_type,comment_count,to_ping,pinged,post_content_filtered)" +
 	   		" VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
 	   PreparedStatement stmtUpdate = dbConnect.prepareStatement(sqlUpdate);
 	 
		while (resultSet.next()) 
		{
		System.out.println(resultSet.getString("title"));
    	   nid=resultSet.getInt("nid");
    	   uid=resultSet.getInt("uid");
    	   status=resultSet.getInt("status");
    	   created=resultSet.getString("created");
    	   modified=resultSet.getString("modified");
    	   title=resultSet.getString("title");
    	   teaser=resultSet.getString("teaser");
    	   body=resultSet.getString("body");
    	   url=resultSet.getString("url");
    	   type=resultSet.getString("type");
    	   //We check if the post is a draft or not
    	   if (status==1) strStatus="publish";
    	   else strStatus="draft";
    	   
    	   //Pages and stories are created as pages
    	   if (type.equals("page") || type.equals("story")) strType="page";
    	   else strType="post"; //forum posts and normal posts are both stored as posts
    	   
    	   //To identify forum posts (since we are not migrating them as a separate concept) we prefix the title
    	   if (type.equals("forum")) title="USER FORUM TOPIC " + title;
    		
    	   //URL modification: We take only the last part of the URL (the one belonging to the post itself). 
    	   //The pattern structure should be recreated using redirect regular expressions in the site (or 
    	   //the permalink wordpress pattern options if possible)
    	   url=url.substring(url.lastIndexOf("/")+1,url.length());
    	   
    	   //We now update the internal links to the images in the site
    	   body=body.replaceAll("/sites/default/files/contentImages/", "/wp-content/uploads/");
    	   
    	   stmtUpdate.setInt(1,nid);
    	   stmtUpdate.setInt(2,uid);
    	   stmtUpdate.setString(3,created);
    	   stmtUpdate.setString(4,created);
    	   stmtUpdate.setString(5,body);
    	   stmtUpdate.setString(6,title);
    	   stmtUpdate.setString(7,teaser);
    	   stmtUpdate.setString(8,strStatus);
    	   stmtUpdate.setString(9,"open");
    	   stmtUpdate.setString(10,"open");
    	   stmtUpdate.setString(11,url);
    	   stmtUpdate.setInt(12,0);
    	   stmtUpdate.setInt(13,0);
    	   stmtUpdate.setString(14,strType);
    	   stmtUpdate.setInt(15,0);
    	   stmtUpdate.setString(16,"");
    	   stmtUpdate.setString(17,"");
    	   stmtUpdate.setString(18,"");
    	   
    	   stmtUpdate.executeUpdate();
    	   System.out.println("Post " + title +" created" );
    	   
		}
    	
		Statement stmtPostsCat= dbConnect.createStatement();
    	//Link posts and categories with a global insert. We don't assign categories to spanish blog posts since
		//we don't want them to appear in category-based searches (the spanish version will be stopped, though
		//we still migrate the posts for historical reasons so that previous links to them still work)
		String sqlInsertCat="INSERT INTO "+wpPrefix+"term_relationships (object_id,term_taxonomy_id)" +
				" SELECT t.nid,t.tid FROM " +drPrefix + "term_node t, " + drPrefix + "node n "+
				"WHERE t.nid=n.nid and n.language='en' and n.type='blog'";
		stmtPostsCat.executeUpdate(sqlInsertCat);
		
		//Now we can update the count attribute for each category
		String sqlUpdateCount="UPDATE " + wpPrefix+"term_taxonomy tt SET count= " +
				"(SELECT COUNT(tr.object_id) FROM "+ wpPrefix +"term_relationships tr " +
						"WHERE tr.term_taxonomy_id=tt.term_taxonomy_id)"; 
		stmtPostsCat.executeUpdate(sqlUpdateCount);
		
		stmt.close();
	    stmtUpdate.close();
	    stmtPostsCat.close();
    }
	
  //Filling the wp_comments table
    static void createComments(Connection dbConnect, String wpPrefix, String drPrefix) throws SQLException
    {
    	//Forum post are ignored in this method. All posts were created by the same admin user (if not you'll need to 
    	//migrate drupal users, not done here
    	
		Statement stmt = dbConnect.createStatement();
		//I ignore the hierarchy in the comments. I also ignore comments beloging to forum posts, these will be imported later
	    String sqlQuery = "SELECT c.cid, c.nid, FROM_UNIXTIME(c.timestamp) created, c.comment, c.name, c.mail, c.homepage, c.status"+
	    		" FROM " + drPrefix+ "comments c , " + drPrefix + "node n WHERE c.nid=n.nid and n.type IN ('blog','story','page','forum')";		
		
		int cid,nid,status;
		String created, comment, thread, name, mail,homepage;
			 
 	   //SQL insert statement that will be used for each post. The number of comments will be updated later on.
 	   //We use a prepared statement to avoid problems wiht ' and " in the body of the post
	   //Since I don't migrate the users I ignore as well the uid
 	   String sqlUpdate="INSERT INTO "+ wpPrefix + "comments (comment_id, comment_post_id, comment_author, comment_author_email," +
 	   		"comment_author_url, comment_date, comment_date_gmt, comment_content,comment_approved)" +
 	   		" VALUES (?,?,?,?,?,?,?,?,?) ";
 	   PreparedStatement stmtUpdate = dbConnect.prepareStatement(sqlUpdate);
 	 
 	   ResultSet resultSet = stmt.executeQuery(sqlQuery);
	   while (resultSet.next()) 
	   {
			cid=resultSet.getInt("cid");
	    	nid=resultSet.getInt("nid");
	    	created=resultSet.getString("created");
	    	comment=resultSet.getString("comment");
	    	name=resultSet.getString("name");
	    	mail=resultSet.getString("mail");
	    	homepage=resultSet.getString("homepage");
	    	status=resultSet.getInt("status");
    	    if(status==0) status=1; //the value for approved comments is the reverse one
    	    else status=0;
    	    
    	   stmtUpdate.setInt(1,cid);
     	   stmtUpdate.setInt(2,nid);
     	   stmtUpdate.setString(3,name);
     	   stmtUpdate.setString(4,mail);
     	   stmtUpdate.setString(5,homepage);
     	   stmtUpdate.setString(6,created);
     	   stmtUpdate.setString(7,created);
     	   stmtUpdate.setString(8,comment);
     	   stmtUpdate.setInt(9,status);
     	 
     	  stmtUpdate.executeUpdate();
   	      System.out.println("Comment " + cid + "for " + nid+ " created" );
	   }
	   
	   Statement stmtCommentPosts= dbConnect.createStatement();
	   //Now we can update the comment count of the posts
	   String sqlUpdateCount="UPDATE " + wpPrefix+"posts p SET p.comment_count= " +
				"(SELECT COUNT(c.comment_post_id) FROM "+ wpPrefix +"comments c " +
						"WHERE c.comment_post_id=p.id)"; 
		stmtCommentPosts.executeUpdate(sqlUpdateCount);
    }
	
      
    //Truncate the given table
	static void truncateTable(Connection dbConnect, String name) throws SQLException
	{
		Statement stmt = dbConnect.createStatement();
	    String sql = "TRUNCATE " + name;
	    stmt.executeUpdate(sql);
	}
	
	static void removeWPUsers(Connection dbConnect, String wpPrefix) throws SQLException
	{
		Statement stmt = dbConnect.createStatement();
	    String sql = "DELETE FROM " + wpPrefix + "users WHERE id > 1";
	    stmt.executeUpdate(sql);
	    sql = "DELETE FROM " + wpPrefix + "usermeta WHERE user_id > 1";
	    stmt.executeUpdate(sql);
	}
	
	
		
	
}