Import serendipity entries into drupal

I repost some of my blog posts made @ liip. Please see here for the original post and comments:

I had to relaunch a web site using Serendipity with a new Drupal installation. The old site had about 100 blog posts and lots of comments we did not want to lose. After some googling, i found this excellent blog post on creating drupal nodes programmatically and this useful resource on drupal node fields. i knocked up the following script that does the job. Just copy this to s9y-import.php and place it in your web root and call it in your web browser. Please back up your database in case something goes wrong (believe me, if you have no backup, it will go wrong!). You might want to read the code comments to know about the current limitations.

updated on 14.7.2009

 * Import s9y blog posts along with comments into drupal
 * Simply put this into your drupal installation root and call it with a web browser
 * Known limitations:
 *   - You need to be logged into drupal as the admin user (id = 1) and all entries will appear to come from that user
 *   - Assumes that both s9y and drupal use the same encoding.
 *   - Requires s9y and drupal to share one database. If this is not the case, just copy the serendipity_entries and serendipity_comments tables to the drupal database
*   - If your s9y tables do not start with serendipity_ or your drupal tables not with drupal_ you will need to adjust the SQL statements
 *   - Does not take care of contained images. You either have to keep the <s9y>/uploads/ directory online or you copy the contents of that folder to the drupal path and adjust the src attributes using SQL.
 *   - Will make your whole body the teaser, meaning the complete entry is shown in the overview.
          The reason is that s9y handles teaser and full entry differently from drupal.
          You can edit over-long posts and just save them to have an automatic short teaser.
 * Written by <> on 18.6.2009
 * Inspired by this blog post:

/*** configuration     ***/
/** map s9y user id to drupal id.
 * the format is s9yID => drupalID
 * entries not found in this map are assigned to uid 1, which is the admin user.
$usermap = array(1=>1);

/*** end configuration ***/

require_once './includes/';

$mappings = ''; //hold url mapping info
$q = mysql_query("select id, title, timestamp, last_modified, CONCAT(body,extended) as body, authorid, comments from serendipity_entries order by timestamp asc");
while($f = mysql_fetch_assoc($q)) {
    $userid = isset($usermap[$f['authorid']]) ? $usermap[$f['authorid']] : 1;
    $newent = array('created' => $f['timestamp'], 'changed' => $f['last_modified'], 'title' => $f["title"], 'body' => $f["body"],
        'teaser' => $f["body"], 'format' => 2, 'uid' => $userid, 'type' => 'blog', 'status' => 1, "comment" => 2,
        'promote' => 1, 'sticky' => 0);
    $newento = (object)$newent;

    //we can not directly get the new node id :-(
    //we hope there is only one at that exact moment
    $qid = mysql_query("select nid from drupal_node where created = ".$f['timestamp']);
    $newid = mysql_fetch_assoc($qid);
    if(! $newid) {
        echo 'Could not retrieve newly inserted node<br />';
    $newid = $newid['nid'];

    //url for rewrite
    $qurl = mysql_query("select dst from drupal_url_alias where src='node/$newid'"); //if you use more than 1 language, you will need to consider language column
    $newurl = mysql_fetch_assoc($qurl);
    $newurl = $newurl['dst'];

    $qurl = mysql_query("select permalink from serendipity_permalinks where type='entry' and entry_id=".$f['id']);
    $oldurl = mysql_fetch_assoc($qurl);
    $oldurl = $oldurl['permalink'];

    $mappings .= "$oldurl\t$newurl\n";

    if ($f['comments'] > 0) {

        $c = mysql_query('select id, parent_id, timestamp, title, author, email, url, ip, body FROM serendipity_comments WHERE entry_id = '.$f['id'].' ORDER BY parent_id');
        $map = array(); //index: old parent id, value: new parent id
        while($com = mysql_fetch_assoc($c)) {
            $parent = 0;
            if ($com['parent_id'] != 0) $parent = $map[$com['parent_id']];
            $newcom = array('pid' => $parent,
                            'nid' => $newid,
                            'uid' => 0,
                            'subject' => $com['title'],
                            'comment' => $com['body'],
                            'hostname' => $com['ip'],
                            'timestamp' => $com['timestamp'],
                            'status' => COMMENT_PUBLISHED,
                            'format' => 1,
                            //thread field is determined by comment_save function
                            'name' => $com['author'],
                            'mail' => $com['email'],
                            'homepage' => $com['url']);
            //funny: comment_save returns the id, whereas node_save does not
            $newcid = comment_save($newcom);
            $map[$com['id']] = $newcid;
Copy the following into your apache config or .htaccess file and adjust paths
RewriteEngine on
RewriteMap urlmap "txt:/path/to/file/urlmap.txt"
RewriteRule ^/blog/(.*) /site/${urlmap:$1} [L,NC,QSA,R]
And copy this into urlmap.txt
<?php echo $mappings ?>

This script only does what I needed at the moment, it is not the perfect importer. It took about 10 seconds to import the 100 blog posts. Then it took me maybe 2 hours going through the blog and fixing links between blog posts and editing the long entries to enter the teaser border.

If you improved the script, please share your improvements here.

For comments, please go to the original post in the liip blog:

script drupal