<?php
/**

Version 1.1

MySQL Database conversion tool from Movable Type
to Nucleus CMS. Tested with the database
structures of Movable Type 2.64 and
Nucleus 2.0 (and PHP version 4.1.2). 

Imports all entries (title, body,
extended body, author, category, date) and
comments (name, email/url, ip, hostname)
from one blog to another. It can also just
echo the query so you can later apply it 
to a database of your choice. When just echoing
the query it's not possible to export the
comments, though.

This is a rather quick-and-dirty solution to the problem.
It assumes the blog only has one author - after the
conversion, all the entries will have the same
author. There are no plans to continue the development 
of this script, unless obvious bugs are found. 
You're free to develop it, though (=.

Written by Olli Savolainen,
http://pilpi.net/

Distribution, modification and publication of
new versions alloved as long as this text
stays intact. I would also appreciate to know
about developments and if this script has been any
help to you.

USAGE: Fill in the following variable values to
suit your use. Before continuing, add the desired
categories in the Nucleus blog.
You will probably find using phpMyAdmin
(http://www.phpmyadmin.net) or equivalent helpful
at times, since you will need to know the blog
numbers and the category numbers in Nucleus'
and Movable Type's tables, as well as the Nucleus
author number.

---

CHANGELOG

Changes in 1.1 (Dec. 6, 2003):
- Some small UI tweaks
- Support for using tables from different databases and/or database servers 
  for exporting from movable type and for importing to nucleus
- Support for Nucleus version 2.5 beta in addition to 2.0
- Ability to turn off resolving IPs to hostnames for Movable Type's entry
  comments

NOTE: this version (1.1) has NOT been tested with Nucleus 2.0 at all,
I'm just assuming it will work since the previous version did.
If you're using 2.0, set nucleus version in the configuration
of this script accordingly. 
If it doesn't work, try setting it to 2.5 or try version 1.0
of this script which has been tested to work with ONLY Nucleus
2.0 - it works fine, you just can't use multiple databases etc.
If you desperately need the features in 1.1, please contact me:
my contact information can be found at: http://pilpi.net/contact/

---

BEGIN USER CONFIGURATION

*/


// database server to export from (probably "localhost")
$data['export_server']="localhost";
// user
$data['export_user']="";
// password
$export_password="";
// database
$data['export_database']="";


// NEW in script version 1.1: two database support
// if any one of these is the same as above,
// you can just leave that one empty.

// database to import to
$data['import_server']="localhost";
// user
$data['import_user']="";
// password
$import_password="";
// database
$data['import_database']="";

// The database format to use; entering anything else than
// 2.0 will cause the script to export the entries in Nucleus
// 2.5beta database format. Tested to work only with 2.5beta,
// though.
$data['nucleus_version']="2.5";

// NEW in script version 1.1:
// The table prefix - leave empty if unsure
// (only for nucleus 2.5beta, if the version above is set to Nucleus 2.0,
//  this will be ignored.)
$data['nucleus_table_prefix']="";


// You will probably need to go crawl inside your databases
// to find the values for the following configuration variables.
// The conversion *will not* work unless you fill them.

// One good tool for browsing MySQL database contens is 
// phpMyAdmin, available at http://www.phpmyadmin.net/

// the id of the Movable Type blog from which to export
// This is the blog_id field in the MT database table
// that's usually called mt_blog. Hint: look at the 
// blog_name and blog_site_path fields. 
$data['mt_blog_id']=1;

// the id of the Nucleus blog to which to import the entries
// This is the bnumber field in the Nucleus database table
// that's usually called nucleus_blog. Hint: look at the 
// bname and burl fields. 
// If you've ever created just one blog, this will be 1.
$data['nucleus_blog_id']=1;

// the author id who will seem to have written the entries in Nucleus
// If you only have one author, this will be 1. If unsure, use 1.
// TODO: multiple author support
$data['nucleus_blog_author']=1;

// mapping the old category entries
// before you convert the databases, you need to
// create the wanted categories in Nucleus.
// then create the equivalencies here, for example:
// $mt_category[x]=y;
// would mean that if an entry has category id x
// in the Movable Type, it will have id y in
// Nucleus. You can use as many of these lines
// as you like.
// This is the category_id field in the MT database table 
// (x in the above example) and the catid 
// field in the Nucleus database table (y in the above example).
// Hint: look at the category_label field in the MT table
// and cname in the Nucleus table. 

$mt_category[1]=2;
$mt_category[2]=3;
$mt_category[3]=4;
$mt_category[4]=5;

// The rest of the configuration is more or less probably
// optional ;)


// if this string is contained in the author name in a comment...
// (leave blank if you wish not to use this feature)
$data['mt_special_user']="";
// ... the comment is marked to be by the
// nucleus user with the following user id
// TODO: multiple comment author support
$data['nucleus_special_user']=1;


// When tracking a user who comments entries, both of the
// applications store the IP address of the commentor.
// However, Nucleus also stores the hostname - but since we're
// converting from Movable Type, there is no hostname available
// for the old comments, just an IP.
//
// This script can try to determine
// the current hostnames based on the IPs stored in MT's database, however
// there is no way of knowing whether or not the hostname of the IP was
// the same when the user posted the comment, so you might end up having false
// hostnames. Still, it might be better than nothing.
//
// Also, querying the hostname requires you to have an internet
// connection available when running this script - otherwise an internet
// connection is not required, just a connection to the database(s).
//
// Should this script try to determine the hostnames of the IPs in the Movable
// Type database? If you're unsure, just leave it as it is. Possible options
// are true or false.

$data['resolve_ips']=true;


// the Movable Type table which contains the entries
// "mt_entry" if unsure
$data['mt_entry_table']="mt_entry";
// the Movable Type table which contains the comments
// "mt_comment" if unsure
$data['mt_comment_table']="mt_comment";
// the Movable Type "placement" table
// "mt_placement" if unsure
$data['mt_placement_table']="mt_placement";


// the Nucleus table which contains the entries
// "nucleus_item" if unsure
$data['nucleus_entry_table']="nucleus_item";
// the Nucleus Type table which contains the comments
// "nucleus_comment" if unsure
$data['nucleus_comment_table']="nucleus_comment";


// END OF USER CONFIGURATION.


// below this point, you shouldn't need to change anything
$data['mt_published_status']=2;
$data['nucleus_published_status']=0;
$data['nucleus_draft_status']=1;
if ($data['nucleus_version']!="2.0"){
    $data['nucleus_entry_table']=$data['nucleus_table_prefix'].
    $data['nucleus_entry_table'];
    $data['nucleus_comment_table']=$data['nucleus_table_prefix'].
    $data['nucleus_comment_table'];
}
if($data['import_database']==""){
    $data['import_database']=$data['export_database'];
}
if($data['import_server']==""){
    $data['import_server']=$data['export_server'];
}
if($data['import_user']==""){
    $data['import_user']=$data['export_user'];
}
if ($import_password==""){
    $import_password=$export_password;
}


// display the form
echo '<?xml version="1.0" encoding="iso-8859-1"?>';
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en">
<head>
<title>Movable Type -&gt; Nucleus database conversion tool</title>
</head><body>
<h1>Movable Type -&gt; Nucleus database conversion tool</h1>
<h2>Version 1.1</h2>
<p>Created by <a href="http://pilpi.net">Olli Savolainen</a>, 
inbox [ät] pilpi [dot] net</p>
<form action="" method="get"><div>
<?php 
if ($data['export_database']==""){
    echo "<hr /><p><strong>Before you can do anything with this script ";
    echo "you must edit the configuration variables in ";
    echo "the source of this file. Instructions are included in the ";
    echo "source as well.</strong></p><hr />";
}
else{ ?>
<input
type="checkbox"
value="1" name="commit"
<?php if ($_GET['commit']==1) echo "checked=\"checked\"";?> />Commit
conversion (entries and comments)<br /><input type="checkbox"
value="1" name="show"
<?php if ($_GET['show']==1) echo "checked=\"checked\"";?> />Show SQL query
(if not committing only entry queries  possible)<br />
<input type="submit" value="Go!"/>
<?php } ?>
</div></form>
</body></html>

<?php

if (!($data['import_database']==""
&& $data['import_server']==""
&& $data['import_user']==""
&& $import_password="")){
    $exportCon = connectDB($export_password,$import_password,$data,1);
    //mysql_select_db ($data['export_database'] ,$exportCon);
    $importCon = connectDB($export_password,$import_password,$data,0);
    //mysql_select_db ($data['import_database'] ,$importCon);
}
else{
    $importCon = $exportCon = 
    connectDB($export_password,$import_password,$data,1);
}
if (empty($_GET['commit']) && empty($_GET['show'])){
    echo "<p>";
    foreach ($data as $a=>$b){
        echo "# $a = $b <br />";
    }
    echo "</p>";
}
if ($_GET['commit']||$_GET['show']){
    // begin script, connect to database server
    echo "# Exporting data...</p>";
    
    
    // export the entries from Movable Type
    $query1="SELECT entry_id,entry_blog_id,entry_text, entry_status,";
    $query1.=" entry_category_id, entry_title, entry_text, entry_text_more,";
    $query1.=" entry_created_on FROM ".$data['mt_entry_table']." ORDER BY";
    $query1.=" entry_id ASC";
    mysql_select_db ($data['export_database'] ,$exportCon);
    $result = mysql_query($query1,$exportCon);
    //initialize entry- and comment counters
    $i=0;
    $c=0;
    echo "<p>";
    
}

while ($row = mysql_fetch_assoc($result)) {
    //echo $row['entry_blog_id'] . $data['mt_blog_id']."IIII<br />";
    
    if ($row['entry_blog_id']==$data['mt_blog_id']){
        $i++;
        
        foreach ($row as $a=>$b){
            if ($data['nucleus_version']!="2.0"){
                $n=html_entity_decode($b,ENT_NOQUOTES);
            }
            $n=str_replace("\n","<br />\n", $n);
            $row[$a]=mysql_escape_string($n);
        }
        mysql_select_db ($data['export_database'] ,$exportCon);
        // MT stores the categories of its entries in a special table
        $placement_query="SELECT placement_entry_id,placement_category_id";
        $placement_query.=" FROM ".$data['mt_placement_table']." WHERE";
        $placement_query.=" placement_entry_id='".$row['entry_id']."'";
        $placement_result = mysql_query($placement_query,$exportCon)
        or die ("Error: ".mysql_error());
        $placement_row = mysql_fetch_assoc($placement_result);
        $catid = $placement_row['placement_category_id'];
        $catid=$mt_category[$catid];
        // category id acquired, so free the resources taken up by that query
        mysql_free_result($placement_result);
        
        // if it's a draft in MT, make it a draft in Nucleus as well
        // (too bad you currently can't make a published entry a draft
        // in Nucleus like you can in MT)
        if ($row['entry_status']==$data['mt_published_status']){
            $draft=$data['nucleus_published_status'];
        }
        else{
            $draft=$data['nucleus_draft_status'];
        }
        
        
        // generate the main query to insert the entry
        // excuse the strange variable name ;)
        $gogo="INSERT into ".$data['nucleus_entry_table'];
        $gogo.="(ititle,ibody,imore,iblog,iauthor,itime,iclosed,idraft,";
        $gogo.="ikarmapos,icat,ikarmaneg) VALUES ";
        $gogo.="('".$row['entry_title']."','".$row['entry_text']."','";
        $gogo.=$row['entry_text_more']."','".$data['nucleus_blog_id']."','";
        $gogo.=$data['nucleus_blog_author']."','".$row['entry_created_on'];
        $gogo.="','0','".$draft."','0','".$catid."','0')";
        
        
        if ($_GET['commit']==1){
            echo "<p># Importing data...</p>";
            //mysql_close();
            //$connection = connectDB($export_password,$import_password,$data,0);
            
            mysql_select_db ($data['import_database'] ,$importCon);
            mysql_query($gogo,$importCon) 
              or die("# Could not insert: " . mysql_error());
            // get the inserted entry's new id to be used while inserting
            // the comments of the entry
            $citem=mysql_insert_id();
            echo "# Entry: ".$row['entry_id']."=&gt;$citem";
            echo " titled \"".$row['entry_title']."\"<br />";
        }
        
        // if requested, display the INSERT query for the entry
        // html entities need to be converted for the web page,
        // but if the entries already contain entities, those should
        // _not_ be converted again
        if ($_GET['show']==1) echo htmlentities2($gogo)."<br />";
        //mysql_close();
        //$connection = connectDB($export_password,$import_password,$data,1);
        // get the comments for the entry
        mysql_select_db ($data['export_database'] ,$exportCon);
        $comments_query = "SELECT comment_entry_id,comment_ip,comment_author";
        $comments_query .= ",comment_email,comment_url,comment_text,";
        $comments_query .= "comment_created_on FROM ".
                           $data['mt_comment_table'];
        $comments_query .= " WHERE comment_entry_id='".$row['entry_id']."'";
        $comments_result = mysql_query($comments_query,$exportCon)
        or die ("# ...".mysql_error());
        
        // process all the comments for a particular entry
        
        while ($comments_row = mysql_fetch_assoc($comments_result)){

            $c++;
            $cmember="";$cuser="";$cmail="";
            foreach ($comments_row as $a=>$b){
                if ($data['nucleus_version']!="2.0"){
                    $n=html_entity_decode($b,ENT_NOQUOTES);
                }
                //echo "<strong style=\"color:blue;\">".$a."---".$b."</strong>";
                $comments_row[$a]=mysql_escape_string($n);
            }
            $cip=$comments_row['comment_ip'];
            
            // since MT only stores the ip, get the _current_
            // hostname for it - not ideal, but will do
            if ($data['resolve_ips']){
                $chost=gethostbyaddr($cip);
            }
            // if the comment is by the special user, (determined by the string
            // defined by the user in $data['mt_special_user'] )
            // let's not insert name or email, just the member id in
            // $data['nucleus_special_user']
            $su=stristr($comments_row['comment_author'],
                        $data['mt_special_user']);
            if ((!empty($data['mt_special_user'])) && ($su)){
                $cmember=$data['nucleus_special_user'];
            }
            else{
                $cmember=0;
                $cuser=$comments_row['comment_author'];
                if (strcmp($comments_row['comment_url'],""))
                    $cmail=$comments_row['comment_email'];
                else
                    $cmail=$comments_row['comment_url'];
            }

            // the INSERT query for the comment
            $cgogo="INSERT into ".$data['nucleus_comment_table'];
            $cgogo.="(cbody,cuser,";
            $cgogo.="cmail,cmember,citem,ctime,chost,cip,cblog) VALUES ";
            $cgogo.="('".$comments_row['comment_text']."','".$cuser."','";
            $cgogo.=$cmail."','".$cmember."','".$citem."','";
            $cgogo.=$comments_row['comment_created_on']."','".$chost."','";
            $cgogo.=$cip."','1')";
    
            // echo the INSERT row only if the conversion is being committed
            // (it's not possible to know what the INSERT row for the 
            // conversion is before actually importing the entries first, 
            // since only after importing the entries do we know the numbers
            // of the new entries with which the comments should be associated.
    
            if ($_GET['commit']==1){
                echo "# &nbsp;&nbsp;--Comment at: ";
                echo $comments_row['comment_created_on']."<br />";
                if ($_GET['show']==1) echo htmlentities2($cgogo).";<br />";
                mysql_select_db ($data['import_database'] ,$importCon);
                mysql_query($cgogo,$importCon) 
                 or die("# Could not insert: " .  mysql_error());
            }
        }
        mysql_free_result($comments_result);
    }
}

mysql_free_result($result);
echo "</p>";
echo "<p># Conversion successful with $i entries and $c comments.</p>";

function connectDB($export_password,$import_password,$data,$export){
    $result;
    //global $data;
    if($export==1){
        $current_password=$export_password;
        $current_user=$data['export_user'];
        $current_server=$data['export_server'];
    }
    else{
        $current_password=$import_password;
        $current_user=$data['import_user'];
        $current_server=$data['import_server'];
    }
    
    $result=mysql_connect($current_server, $current_user, $current_password)
    or die("# Could not connect: " . mysql_error());
    
    // select the database
    if($data['export_database']!=$data['import_database']){
        if ($export) $s="export";
        else $s="import";
        echo "<p># Connected to $s database <tt>".$data[$s.'_database'];
        echo "</tt> at <tt>".$data[$s.'_server']."</tt>.</p>";
    }
    else if ($export==1){
        echo "<p># Connected to database <tt>".$data['export_database'];
        echo "</tt> at <tt>".$data['export_server']."</tt>.</p>";
    }
    //or die("Could not connect: " . mysql_error());
    
    
    return $result;
}
function htmlentities2($myHTML) {
    // from http://php.net/manual/en/function.htmlentities.php
    $translation_table=get_html_translation_table (HTML_ENTITIES,ENT_QUOTES);
    $translation_table[chr(38)] = '&';
    $regexp="/&(?![A-Za-z]{0,4}\w{2,3};|#[0-9]{2,3};)/";
    $result=preg_replace($regexp,"&amp;", strtr($myHTML, $translation_table));
    return $result;
}
function html_entity_decode( $given_html, $quote_style = ENT_QUOTES ) {
    // from http://php.net/manual/en/function.html-entity-decode.php
    $trans_table = get_html_translation_table( HTML_SPECIALCHARS, $quote_style );
    if( $trans_table["'"] != '&#039;' ) { 
        # some versions of PHP match single quotes to &#39;
        $trans_table["'"] = '&#039;';
    }
    return ( strtr( $given_html, array_flip( $trans_table ) ) );
}

?></body></html>