#!/usr/bin/php -q
 <?php
 
 /**
 * Convert Nuke-type database to GeekLog
 * quick hack
 */
 
 require_once '/var/www/html/sites/geeklog/system/pear/PEAR.php'; // why am i doing this?!
 
 $dbhost = 'localhost';
 $dbuser = 'tester';
 $dbpass = 'test123';
 $gl_dbname = 'cadena'; // new GL
 $nuke_dbname = 'ccnet'; // 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);
 
 // 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 < 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;
 }
 
 ?>