Geeklog Forums

 

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

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

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'test';
$gl_dbname = 'sitting-ducks'; // new GL
$nuke_dbname = 'OLD'; // old Nuke

$gl_usertable = 'gl_users';
$gl_topictable = 'gl_topics';
$gl_storytable = 'gl_stories';
$gl_commenttable = 'gl_comments';

$nuke_usertable = 'nuke_users'; // change this for maxt_man db
$nuke_topictable = 'nuke_topics';
$nuke_storytable = 'nuke_stories';
$nuke_commenttable = 'nuke_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);

print "[ OK ]n";

// 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_passn";
$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] EXISTSn", $hash['uname']);
// update nuke_uid
$sql = "update $gl_usertable set nuke_uid = {$hash['uid']} where uid = {$gl_users[$hash['uname']]}";
} else {
//printf("[%-15s] EXIST NOTn", $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 "$sqln";
//( 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 %sn", ($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 %sn", ++$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 %sn", ($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] EXISTSn", ++$i, $topicname);
// update nuke_uid
$sql = "update $gl_topictable set nuke_tid = {$hash['topicid']} where LOWER(tid) = '$topicname'";
} else {
printf("%2d [%-20s] EXIST NOTn", ++$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 "$sqln";
//( 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 %-80sn", ++$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 %3dn", $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 => %-20sn", $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 "$sqln";
//( 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 "$sqln";
//( 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
$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
$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;
}

?>


 

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 => %-20sn", $gl_tid);

  $hash['title'] = addslashes($hash['title']);
  $hash['hometext'] = addslashes($hash['hometext']);
  $hash['bodytext'] = addslashes($hash['bodytext']);
  $sql = "INSERT INTO $gl_storytable (sid, nuke_sid, uid, tid, date, title, introtext, bodytext)
        values ('$gl_sid', '{$hash['sid']}','$gl_uid', '$gl_tid', '{$hash['time']}', '{$hash['title']}', '{$hash['hometext']}', '{$hash['bodytext']}')";

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


 

INSERT INTO gl_stories (sid, nuke_sid, uid, tid, date, title, introtext, bodytext)
values ('20040328060842303', '22','2', '', '2003-05-01 21:20:15', 'I am back', 'Hi all, I am back on track for my site.
My new server is online, and i am running some anti cheats tools. soon, you will get some details', '')