Nuke to Geeklog conversion

The following is an exerpt from an email I sent to the geeklog-developers list a few months back. It will explain what I know about this script:

Before doing *anything* you need to know that the script was made to convert a few Nuke DB's we have here. The script is *not* generic and will require some work on your part to make it work for you. I did not write this script, I'm only aware of what it does, not how it does it.

The first portion of the script is commented out which relates the user data. You need to run this *first* to get the users over to GL then comment it out otherwise you may end up with multiple copies of the same user info when it goes through and does the stories and so on. Keep in mind that everything is relational, if the users are not ported first then the stories won't know who at attribute the contribution to or to which topic it belongs. So it has to be done quite orderly.

The point is, this script is being provided in the hopes that it will spark someone's interest in making it more generic and resubmitting it to the GL community so that we have a tool to offer Nuke users to port their DB's over to GL.

The script does the following:

1) Transfers Nuke user data to GL
3) Transfers Nuke Topics to GL
2) Transfers Nuke stories AND comments while keeping dates intact and properly assigns them back to their topics. Contributors are keep intact as well.

That's it for now. We may add Nuke Reviews to our script, but so far this is all we needed to do since lots of work was already underway for the GL DB here at work (no need to port links and downloads since they were done by hand).

I feel that any generic script should stick to Nukes largest features like users, stories, reviews (Reviews can be converted to stories under a "Reviews" topic), and links. The rest is likely too unstable (in other words it changes too much from upgrade to upgrade). What Nuke users will want most in order to go to Geeklog is Users, Topics and Stories. The rest is icing on the cake.

Here's hoping we have some people willing to complete this in a more polished form!

#!/usr/bin/php -q
<?php

/**
* Convert Nuke-type database to GeekLog
* quick hack
*/

require_once 'PEAR.php'; // why am i doing this?!

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$gl_dbname = 'maxtinternal'; // new GL
$nuke_dbname = 'maxt_dev'; // old Nuke

$gl_usertable = 'users';
$gl_topictable = 'topics';
$gl_storytable = 'stories';
$gl_commenttable = 'comments';

$nuke_usertable = 'users'; // change this for maxt_man db
$nuke_topictable = 'topics';
$nuke_storytable = 'stories';
$nuke_commenttable = 'comments';


$dbh = connect_db($dbhost, $dbuser, $dbpass);;
if ( PEAR::isError($dbh) ) {
trigger_error($dbh->getMessage(), E_USER_ERROR);
} else {
//print "DB connection [ OK ]\n";
}

/* ****************************************************** //

// *** CONVERT USERS
// get existing GL user names
mysql_select_db($gl_dbname, $dbh);
$sql = "select uid, username from $gl_usertable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$gl_users = array();
while ( $row = mysql_fetch_assoc($res) ) {
$gl_users[$row["username"]] = $row["uid"];
}
mysql_free_result($res);
//print_r($gl_users);

// now get nuke users
mysql_select_db($nuke_dbname, $dbh);
$sql = "select * from $nuke_usertable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$nuke_users = array();
while ( $row = mysql_fetch_assoc($res) ) {
$nuke_users[] = $row;
}
mysql_free_result($res);
//print_r($nuke_users);

$common_pass = md5("password");
//print "pass = $common_pass\n";
$regdate = date("Y-m-d H:i:s"); // don't preserve regdate

$uids = array();
mysql_select_db($gl_dbname, $dbh); // back to GL database
foreach ( $nuke_users as $key => $hash ) {
if ( in_array($hash["uname"], array_keys($gl_users)) ) {
// printf("[%-15s] EXISTS\n", $hash['uname']);
// update nuke_uid
$sql = "update $gl_usertable set nuke_uid = {$hash['uid']} where uid = {$gl_users[$hash['uname']]}";
} else {
//printf("[%-15s] EXIST NOT\n", $hash['uname']);
// insert user
$sql = "insert into $gl_usertable (nuke_uid, username, fullname, passwd, email, regdate ) values ";
$sql .= sprintf("(%d, '%s', '%s', '%s', '%s', '%s')",
$hash['uid'], $hash['uname'], $hash['name'], $common_pass, $hash['email'], $regdate);


}
$uids[] = $gl_users[$hash["uname"]];
//printf("uid [%d]\n", $gl_users[$hash["uname"]]);
// print "$sql\n";
( mysql_query($sql, $dbh) ) or trigger_error("query failed: [$sql]", E_USER_WARNING);
}

// now cascade to all user* tables
// usercomment
// userindex
// userinfo
// userprefs

mysql_select_db($gl_dbname, $dbh); //make sure you're back to GL database
foreach ( $uids as $id ) {
$sqls = array();
$sqls[] = "replace into usercomment (uid) values ($id)";
$sqls[] = "replace into userindex (uid) values ($id)";
$sqls[] = "replace into userinfo (uid) values ($id)";
$sqls[] = "replace into userprefs (uid) values ($id)";
foreach ($sqls as $statement) {
( mysql_query($statement, $dbh) ) or trigger_error("query failed: [$statement]", E_USER_WARNING);
}
}


// *** CONVERT TOPICS
// get existing GL topic ids ( in GL they are varchars )
mysql_select_db($gl_dbname, $dbh);
$sql = "select tid from $gl_topictable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$gl_topics = array();
while ( $row = mysql_fetch_assoc($res) ) {
$gl_topics[] = $row["tid"];
}
mysql_free_result($res);
//print "GL existing topics:\n";
//foreach ( $gl_topics as $key => $topic ) {
// printf("\t%2d %s\n", ($key + 1), $topic);
//}

// now get nuke topics
mysql_select_db($nuke_dbname, $dbh);
$sql = "select * from $nuke_topictable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$nuke_topics = array();
while ( $row = mysql_fetch_assoc($res) ) {
$nuke_topics[] = $row;
}
mysql_free_result($res);
print "\nNUKE topics:\n";
foreach ( $nuke_topics as $key => $hash ) {
printf("\t%2d %s\n", ++$key, strtolower($hash["topicname"]) );
}

// make comparison case-insensitive
my_array_map("strtolower_wrapper", $gl_topics);
print "\nGL existing topics:\n";
foreach ( $gl_topics as $key => $topic ) {
printf("\t%2d %s\n", ($key + 1), $topic);
}
// back to GL database
mysql_select_db($gl_dbname, $dbh);
$imagepath = '/images/topics/';
$i = 0;
foreach ( $nuke_topics as $key => $hash ) {
if ( ! $hash["topicname"] ) continue;
$topicname = space_to_underscore(strtolower($hash["topicname"]));
if ( in_array($topicname, $gl_topics) ) {
printf("%2d [%-20s] EXISTS\n", ++$i, $topicname);
// update nuke_uid
$sql = "update $gl_topictable set nuke_tid = {$hash['topicid']} where LOWER(tid) = '$topicname'";
} else {
printf("%2d [%-20s] EXIST NOT\n", ++$i, $topicname);
// insert user
$sql = "insert into $gl_topictable (tid, nuke_tid, topic, imageurl) values ";
$sql .= sprintf("('%s', %d, '%s', '%s')",
$topicname, $hash['topicid'], $hash['topictext'],
($hash['topicimage']) ? $imagepath . $hash['topicimage'] : '' );

}
//print "$sql\n";
( mysql_query($sql, $dbh) ) or trigger_error("query failed: [$sql]", E_USER_WARNING);
}


// CONVERT STORIES
$aid_to_username = array(
'god' => 'admin',
'iano' => 'iano',
'simon' => 'slord'
);
// now get nuke stories
mysql_select_db($nuke_dbname, $dbh);
$sql = "select * from $nuke_storytable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$nuke_stories = array();
while ( $row = mysql_fetch_assoc($res) ) {
$nuke_stories[] = $row;
}
mysql_free_result($res);
//print "\nNUKE stories:\n";
//foreach ( $nuke_stories as $key => $hash ) {
// printf("%3d %3d %-10s %-80s\n", ++$key, $hash['sid'], $hash["aid"], $hash['title'] );
//}
// back to GL database
mysql_select_db($gl_dbname, $dbh);
// get GL users
$sql = "select username, uid from $gl_usertable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$gl_users = array();
while ( $row = mysql_fetch_assoc($res) ) {
$gl_users[$row['username']] = $row['uid'];
}
//print "\nGL users:\n";
//foreach ( $gl_users as $uname => $uid ) {
// printf("%-10s %3d\n", $uname, $uid );
//}
foreach ( $nuke_stories as $key => $hash ) {
// sleep(1) to make sure that a unique $gl_sid is generated
// quick hack ( not good if there are many records to convert )
sleep(1);
$gl_sid = COM_makesid();
printf("sid => %-20s ", $gl_sid);
$gl_uid = aid_to_gl_uid($hash['aid'], $aid_to_username, $gl_users);
printf("uid => %3d ", $gl_uid);
$gl_tid = get_gl_tid($hash['topic'], $dbh);
printf("tid => %-20s\n", $gl_tid);

$sql = "insert into $gl_storytable (sid, nuke_sid, uid, tid, date, title, introtext, bodytext) values ";
$sql .= sprintf("('%s', %d, %d, '%s', '%s', '%s', '%s', '%s')",
$gl_sid,
$hash['sid'],
$gl_uid,
$gl_tid,
$hash['time'],
addslashes($hash['title']),
addslashes($hash['hometext']),
addslashes($hash['bodytext'])
);

//print "$sql\n";
( mysql_query($sql, $dbh) ) or trigger_error("query failed: [$sql]", E_USER_WARNING);
}


// CONVERT COMMENTS
// now get nuke comments
mysql_select_db($nuke_dbname, $dbh);
$sql = "select * from $nuke_commenttable";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$nuke_comments = array();
while ( $row = mysql_fetch_assoc($res) ) {
$nuke_comments[] = $row;
}
mysql_free_result($res);
//print_r($nuke_comments);
print("total comments: " . count($nuke_comments) . "\n");

// back to GL database
$comment_count = array();
mysql_select_db($gl_dbname, $dbh);
foreach ( $nuke_comments as $key => $hash ) {
$gl_sid = get_gl_sid($hash['sid']);
if ( ! $gl_sid ) continue;
$gl_uid = get_gl_uid($hash['name']);

//printf("sid => %-20s ", $gl_sid);
//printf("uid => %3d \n", $gl_uid);

$comment_count[$gl_sid]++;

$sql = "insert into $gl_commenttable (nuke_cid, sid, date, title, comment, score, reason, uid) values ";
$sql .= sprintf("(%d, '%s', '%s', '%s', '%s', %d, %d, %d)",
$hash['tid'],
$gl_sid,
$hash['date'],
addslashes($hash['subject']),
addslashes($hash['comment']),
$hash['score'],
$hash['reason'],
$gl_uid
);

//print "$sql\n";
( mysql_query($sql, $dbh) ) or trigger_error("query failed: [$sql]", E_USER_WARNING);
}
// update stories.comments field in GL database
print_r($comment_count);
foreach ( $comment_count as $sid => $count ) {
$sql = "update $gl_storytable set comments = $count where sid = '$sid'";
//print $sql . "\n";
( mysql_query($sql, $dbh) ) or trigger_error("query failed: [$sql]", E_USER_WARNING);
}

print "oops, done :o)\n";

// **************************************** */

///////////////////////////////////////////////////////////////////////////

function get_gl_uid ($username)
{
global $dbh, $gl_dbname, $gl_usertable;
mysql_select_db($gl_dbname, $dbh);
$sql = "select uid from $gl_usertable where LOWER(username) = '"
. strtolower($username) . "'";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$rv = @mysql_result($res, 0, 0);
$rv = trim($rv);

return (($rv) ? $rv : 1 ); // default 1 => Anonymous
}

function get_gl_sid ($nuke_sid)
{
global $dbh, $gl_dbname, $gl_storytable;
mysql_select_db($gl_dbname, $dbh);
$sql = "select sid from $gl_storytable where nuke_sid = '$nuke_sid'";
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");

return ( @mysql_result($res, 0, 0) );
}

function get_gl_tid ($nuke_tid, $dbh)
{
global $gl_dbname, $gl_topictable;
$sql = "select tid from $gl_topictable where nuke_tid = '$nuke_tid'";

mysql_select_db($gl_dbname, $dbh);
$res = mysql_query($sql, $dbh) or die("query failed [$sql]\n");
$gl_tid = @mysql_result($res, 0,0);

return ($gl_tid) ? $gl_tid : 'General';
}

function aid_to_gl_uid ($aid, $aid_to_username, $gl_users)
{
$rv = 1; // default for maxtinternal.stories.uid
$username = $aid_to_username[strtolower($aid)];
printf("username => %-10s ", $username);
foreach ( $gl_users as $uname => $uid ) {
if ( strtolower($username) == strtolower($uname) ) {
$rv = $uid;
break;
}
}

return $rv;
}

function space_to_underscore ($str)
{
$rv = NULL;
for ( $i = 0; $i < strlen($str); $i++ ) {
$char = $str[$i];
if (ord($char) == 32) { $char = '_'; } // space
$rv .= $char;
}
return $rv;
}

function my_array_map ($func, &$ary)
{
settype($ary, "array");
for ($i = 0; $i < count($ary); $i++) {
$ary[$i] = $func($ary[$i]);
}
}

function strtolower_wrapper ($str)
{
return strtolower($str);
}

function connect_db ($dbhost, $dbuser, $dbpass)
{
$dbh = mysql_pconnect($dbhost, $dbuser, $dbpass);
if ( !is_resource($dbh) ) {
return new PEAR_Error('DB connection failed');
}

return $dbh;
}

function COM_makesid ()
{
$sid = date("YmdHis");
srand((double)microtime()*1000000);
$sid .= rand(0,999);

return $sid;
}

?>