Migration from Wordpress (and others) to Serendipity

Discussion corner for Developers of Serendipity.
Salat
Posts: 1
Joined: Tue Feb 22, 2005 12:47 am
Contact:

Postnuke .726 Phoenix to Serendipity

Post by Salat » Tue Feb 22, 2005 1:15 am

We recently had a fairly major crash on our site, www.teamctf.com. We were running PostNuke .726, but needed to quickly migrate to something new. I picked s9y because it seemed to be a great fit.

I imported about 130 user accounts and 100 stories from postnuke into serendipity the old fashioned way - I wrote a quick script that would generate the INSERT statements (our DB is MySQL). Since I needed to tweak the script repeatedly, I didn't want to have the script do the actual insert.

My Method:

1 - set up serendipity DB
2 - select all nuke_users and create insert statements into s9y quthors:

Code: Select all

<?php

/*
   This is a quick an dirty script to import PostNuke 0.726 Phoenix users into Serendipity 0.7 authors
*/

// Enter Database info:

$dbname="Phoenix";
$user="dbname";
$password="dbpass";
$server="localhost";

$getPMUserSQL="select pn_uname, pn_uid, pn_pass, pn_email from nuke_users";


function dbconnect ($dbname,$user,$password,$server)
{
   if (!($mylink = mysql_connect($server,$user,$password)))
   {
      print "<h3>could not connect to database</h3>\n";
      exit;
   }
   mysql_select_db($dbname);
}


function safe_query ($query = "")
{
   global $query_debug;

   if (empty($query)) { return FALSE; }

   if (!empty($query_debug)) { print "<pre>$query</pre>\n"; }


   $result = mysql_query($query)
      or die("ack! query failed: "
         ."<li>errorno=".mysql_errno()
         ."<li>error=".mysql_error()
         ."<li>query=".$query
      );
   return $result;
}


dbconnect($dbname,$user,$password,$server);

$useresult = safe_query($getPMUserSQL);

$users = array();
?>
<h2>Serendipity User SQL:</h2>
<pre>

<?


while ($myRow = mysql_fetch_array($useresult))
   {
?>
   INSERT INTO `serendipity_authors` VALUES ('<?= $myRow["pn_uname"] ?>', '<?= $myRow["pn_pass"] ?>', <?= $myRow["pn_uid"] ?>, 0, 0, '<?= $myRow["pn_email"] ?>', 0, 1);
<?
   }

?>
</pre>


3 - upload to my server and generate the inserts. I then verified the sql was correct, and pasted the INSERT statements into MySQLAdmin.

I did a similar thing to import postnuke stories into s9y as entries:

Code: Select all

<?php

/*
   This is a quick an dirty script to import PostNuke 0.726 Phoenix stories into Serendipity 0.7 entries
*/

// Enter Database info:

$dbname="Phoenix";
$user="dbuser";
$password="dbpass";
$server="localhost";

$storysql="select pn_sid as id, pn_aid as authorid, pn_title as title, UNIX_TIMESTAMP(pn_time) as timestamp, pn_bodytext as extended, pn_hometext as body from nuke_stories" ;


function dbconnect ($dbname,$user,$password,$server)
{
   if (!($mylink = mysql_connect($server,$user,$password)))
   {
      print "<h3>could not connect to database</h3>\n";
      exit;
   }
   mysql_select_db($dbname);
}


function safe_query ($query = "")
{
   global $query_debug;

   if (empty($query)) { return FALSE; }

   if (!empty($query_debug)) { print "<pre>$query</pre>\n"; }


   $result = mysql_query($query)
      or die("ack! query failed: "
         ."<li>errorno=".mysql_errno()
         ."<li>error=".mysql_error()
         ."<li>query=".$query
      );
   return $result;
}


dbconnect($dbname,$user,$password,$server);

$useresult = safe_query($storysql);

$users = array();
?>
<h2>Serendipity Entry SQL:</h2>
<pre>

<?


while ($myRow = mysql_fetch_array($useresult))
   {
   
   // do post processing conversions here -

   // -- remove the <br /> tags pn puts in there:
   $bodyStr = str_replace("<br />","",$myRow["body"]);

   $extendedStr = str_replace("<br />","",$myRow["extended"]);
   $exFlag = 0;
   
   if ($extendedStr != "")
   {
      $exFlag = 1;
   }
   
   
   $authorIDString = $myRow["authorid"]   ;
   

?>
   INSERT INTO `serendipity_entries` VALUES (<?= $myRow["id"] ?>, '<?= mysql_escape_string($myRow["title"]) ?>', <?= $myRow["timestamp"] ?>, '<?= mysql_escape_string($bodyStr) ?>', 0, 0, '<?= mysql_escape_string($extendedStr) ?>', <?= $exFlag ?>, '', <?= $authorIDString ?>, 'false', 'true', <?= $myRow["timestamp"] ?>, 'false');
   
<?
   }

?>
</pre>
 


Note here that you have to escape the various text fields, and convert the pn_time to an unsigned int. Also - since there's html in the data, I ran this script, did a "View Source" in my browser, and then pasted the raw SQL into MySQLAdmin. Took about 2 hours, but in the end, I was able to kill my postnuke distribution, saving tons of disk space.

I didn't need to import comments, but I assume that would be pretty straightforward.
Hope this helps someone.

Guest

Post by Guest » Sun Oct 30, 2005 5:14 am

Westpoint wrote:Do not need a converter any more.

I 'converted' the db using copy'n'paste in a text-editor - 8,5 hours of work, headache and finding errors... But now it works, I even had the time to 'recreate' my template so that it looks like my boastMachine-template.

Very nice peace of work you made :D Thx!

- Vincent
çööhjmhjm

SHRIKEE
Regular
Posts: 128
Joined: Tue Feb 21, 2006 3:49 am
Location: Netherlands
Contact:

Post by SHRIKEE » Sun Mar 05, 2006 6:02 pm

ive used the wp import script and found out that all my users, guest accont suddenly became admins... if not for a honest subscriber i wouldnt even have noticed it. :(

maybe an idea to pay little more attention to userrights instead of just creating everyone as admin :)
My kingdom For i am king of my heap of trash

Developing code on:
Workstation: Windows 2000 sp4, TSW webcoder 2005
Server: fedora core 4 amd64, apache 2.0.54, php 5.0.4, mysql 4.1.11.

User avatar
u1amo01
Regular
Posts: 267
Joined: Fri Sep 28, 2007 3:55 pm
Location: Kurpfalz, Germany
Contact:

Post by u1amo01 » Thu Oct 18, 2007 4:06 pm

Looks like import from the new version WordPress 2.3 doesn't work properly. The categories were not imported. IMHO because the structure of the tables has changed.
WordPress 2.3 introduces our new taxonomy schema. This new schema replaces the categories, post2cat, and link2cat tables with three new tables that are more flexible.


See WordPress 2.3 Taxonomy Schema for details.

[edit]
Comments are imported correctly, but the counter at each posting shows "0 comments"

:-(

User avatar
garvinhicking
Core Developer
Posts: 30014
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Thu Oct 18, 2007 5:52 pm

Hi!

Do you maybe have an example dump of the taxonomy table so that I can see how it actually looks like? We only can import categories, not the tags (at least for now, because that would require the freetag plugin integration with the importer).

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

User avatar
u1amo01
Regular
Posts: 267
Joined: Fri Sep 28, 2007 3:55 pm
Location: Kurpfalz, Germany
Contact:

Post by u1amo01 » Thu Oct 18, 2007 6:45 pm

Hmm, if you tell me how to do this - I'm not an phpMyAdmin expert, sorry.

A dump of wp_term_taxonomy, right?

When I go to "Exportieren", there is the following marked by default:

export as: SQL

SQL-Optionen:

Struktur:
AUTO_INCREMENT-Wert hinzufügen
Tabellen- und Feldnamen in einfachen Anführungszeichen

Daten:
Hexadezimalschreibweise für Binärfelder verwenden

Is this ok? And should I send it by mail? (not the whole table for sure ;-))

User avatar
garvinhicking
Core Developer
Posts: 30014
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Thu Oct 18, 2007 7:46 pm

Hi!

It should be three tables: wp_term_taxonomy, wp_term_relationships and wp_term.

Your PMA options would be fine, you need to both check "Struktur" and "Daten" so that everything is contained in the dump file. You can send it via mail to me, blog at garv dot in.

Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

User avatar
u1amo01
Regular
Posts: 267
Joined: Fri Sep 28, 2007 3:55 pm
Location: Kurpfalz, Germany
Contact:

Post by u1amo01 » Thu Oct 18, 2007 8:50 pm

Mail sent.

BTW, WordPress also offers to export Data as XML in a so called "WordPress eXtended RSS / WXR" to exchange content between two WordPress-Blogs. Maybe this could also be a way to import to s9y?

Cheers,
Klaus

User avatar
garvinhicking
Core Developer
Posts: 30014
Joined: Tue Sep 16, 2003 9:45 pm
Location: Cologne, Germany
Contact:

Post by garvinhicking » Thu Oct 18, 2007 10:12 pm

Hi!

It surely would, but I'm quite sure it's some amount of work to be done. Maybe some volunteer steps up to check it out? It would be nice if s9y could also export its data in this WXR format. Maybe a good projects for Google Summer of Code. :)

I'll check out your mail and see what I can do!

Best regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/

Post Reply