Convert WordPress (P2) Microblog Database to StatusNet

A few days ago , I tried to convert our WordPress blog , which was used to be our microblog with P2 theme , to StatusNet . StatusNet is a good microblog system , though it needs more developing .

I wrote a PHP script to convert the WordPress database to StatusNet . There're posts and comments in WP that needs to be converted . Firstly , I said that I had no idea how to convert the replies . And I got a pretty answer "There is alway blood in every revolution" . But finally , I found a  way to convert .

I had ONLY 2 users in WP , so it got a little simplier to do the converting job . If you have many users , you have to make a right users map about WP and SN , both in user IDs and reply "@"s .

The "wp_posts" tables in WP needs to be converted , and the usernames in it is easy to convert . However , we need to convert "wp_comments" tables as replies into StatusNet , and the names has no relationship with WP users . So I wrote a function called "getID" to map the commenters in WP to user IDs in SN . And the "getID" function has another funtion , which is to map the WP user IDs to SN user IDs .

I also import both WP db and SN db into one database to simplify the script .

Here are the codes :

<?php
/*
By Creke
http://blog.creke.net
*/

header('Content-Type: text/plain'); 

function getID($i)
{
	if($i == 2 || strtolower($i) == 'user1')
	{
		return 4;
	}
	else if($i == 3 || strtolower($i) == 'user2')
	{
		return 3;
	}
	else
	{
		return 1;
	}
}

	$mysql_a='dbhost';
	$mysql_u='dbuser';
	$mysql_p='dbpassword';
	$mysql_n='dbname';
	
	set_time_limit(10); 

		if($dblink = mysql_connect($mysql_a,$mysql_u,$mysql_p))
		{
			if(mysql_select_db($mysql_n))
			{

mysql_query("SET NAMES 'utf8'");
mysql_query('TRUNCATE notice ;');
mysql_query('TRUNCATE conversation ;');
mysql_query('TRUNCATE reply ;');

$oldID = 0;
$newID = 1;
$newConID = 1;

//$SQL = "";
//$result = "";
//$myres = 

for($i=0;$i<=505;$i++)
	{
		//post
		
		$oldID = $i;
		$SQL = 'SELECT * FROM wp_posts WHERE post_type = \'post\' AND ID = '.$oldID;
		if (!$result = mysql_query($SQL)){ 
						echo "Execution ERROR :".mysql_error()." "; 
						echo "SQL is :".$SQL." "; 
		}
		$myres = mysql_fetch_array($result);
		if($myres['post_content']==NULL){
		continue;
		}
		
		$mynewproid = getID($myres['post_author']);
		$myres['post_content'] = str_replace('\'','\\\'',$myres['post_content']);
				
		$SQL = 'INSERT INTO notice (id,profile_id,uri,content,rendered,created,modified,is_local,source,conversation) VALUES';
		$SQL .= '('.$newID.','.$mynewproid.',\''.'http://blog.recgo.com/notice/'.$newID.'\',\''.$myres['post_content'].'\',\''.$myres['post_content'].'\',\''.$myres['post_modified_gmt'].'\',\''.$myres['post_modified'].'\','.'1'.',\''.'RecgoWPmblog'.'\','.$newConID.')';
		
		if (!mysql_query($SQL)){ 
						echo "Execution ERROR :".mysql_error()." "; 
						echo "SQL is :".$SQL." "; 
		}
				
		$SQL = 'INSERT INTO conversation (id,uri,created,modified) VALUES';
		$SQL .= '('.$newConID.',\''.'http://blog.recgo.com/conversation/'.$newConID.'\',\''.$myres['post_modified_gmt'].'\',\''.$myres['post_modified'].'\')';
		
		//echo $SQL;
		if (!mysql_query($SQL)){ 
						echo "Execution ERROR :".mysql_error()." "; 
						echo "SQL is :".$SQL." "; 
		}
		
		$newID2=$newID;
		$newID++;
		
		//reply
		$SQL = 'SELECT * FROM wp_comments WHERE comment_approved = 1 AND comment_post_ID = '.$oldID.' ORDER BY comment_ID';
		if (!$result = mysql_query($SQL)){ 
						echo "Execution ERROR :".mysql_error()." "; 
						echo "SQL is :".$SQL." "; 
		}
		
		while($myres = mysql_fetch_array($result))
		{
			if($myres['comment_content']==NULL){
			continue;
			}
			
			$mynewproid = getID($myres['comment_author']);
			$commentID2newreplyto[$myres['comment_ID']]=$newID;
			if($myres['comment_parent']!=0)
			{
				$newreplyto=$commentID2newreplyto[$myres['comment_parent']];
			}
			else
			{
				$newreplyto=$newID2;
			}
			
			if($mynewproid == 3)
			{
				$myres['comment_content'] = '@user1 '.$myres['comment_content'];
				$myreplyproid = 4;
			}
			else if($mynewproid == 4)
			{
				$myres['comment_content'] = '@user2 '.$myres['comment_content'];
				$myreplyproid = 3;
			}
			$myres['comment_content'] = str_replace('\'','\\\'',$myres['comment_content']);
			$myres['comment_content'] = str_replace('\"','\\\"',$myres['comment_content']);
			
			$SQL = 'INSERT INTO notice (id,profile_id,uri,content,rendered,created,modified,is_local,source,conversation,reply_to) VALUES';
			$SQL .= '('.$newID.','.$mynewproid.',\''.'http://blog.recgo.com/notice/'.$newID.'\',\''.$myres['comment_content'].'\',\''.$myres['comment_content'].'\',\''.$myres['comment_date_gmt'].'\',\''.$myres['comment_date'].'\','.'1'.',\''.'RecgoWPmblog'.'\','.$newConID.','.$newreplyto.')';
			
			//echo $myres['comment_content'].'\n';
			if (!mysql_query($SQL)){ 
							echo "Execution ERROR :".mysql_error()." "; 
							echo "SQL is :".$SQL." "; 
			}
			
			$SQL = 'INSERT INTO reply (notice_id,profile_id,modified) VALUES';
			$SQL .= '('.$newID.','.$myreplyproid.',\''.$myres['comment_date'].'\')';
			
			if (!mysql_query($SQL)){ 
							echo "Execution ERROR :".mysql_error()." "; 
							echo "SQL is :".$SQL." "; 
			}
			
			$newID++;
		}
		$newConID++;
	}

			}
			else
			{
				$self_des = "";
				echo '<br>Linked to MySQL , Can\'t open database ';
			}
		}
		else
		{
			$self_des = "";
			echo '<br>Unable to link to Database ';
		}
		mysql_close($dblink);

exit();
?>

Good Luck ^_^