Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
A tweaked version of a script from modeling-languages.com to convert Drupal 6 blog posts to Wordpress
/*
*
*
* 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);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment