• 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 ^_^