Script to convert phpWebsite to geeklog
This is the perl script I used to convert my phpwebsite to geeklog. It's pretty smart. Note that it doesn't do 'polls' since I never really use them. You can download this zip file or read the below (if it renders ;)
#!/usr/bin/perl
#geek log notes
#
# I need to write a script to copy all of the data from my phpWebsite database
# into my new geek log database. The Items I'm interested in are.
#
#
#stories
#boxes (menu, ephemera, in the mix, on the press, capitalist flicka, friends and colleages)
use strict;
use DBI;
use Digest::MD5 qw( md5 md5_hex);
# geeklog config (g)
my $ghost = "localhost";
my $gdb = "geeklogjimwellernet";
my $guser = "jim";
my $gpass = "perfectpassword";
my $ghomedir = "/home/jim/jimweller.net/www/geeklog/public_html/"; # must have trailing slash
# appstate config (a)
my $ahost = "localhost";
my $adb = "jimweller";
my $auser = "jim";
my $apass = "perfectpassword";
my $ahomedir = "/home/jim/jimweller.net/www/phpwebsite/"; # must have trailing slash
my $geeklog_dbh = DBI->connect
("DBI:mysql:$gdb:$ghost::3306",
"$guser",
"$gpass",
);
my $appstate_dbh = DBI->connect
("DBI:mysql:$adb:$ahost::3306",
"$auser",
"$apass",
);
# for storing stuff for later use
my %usermap;
my @topicmap;
my @storymap;
&convert_blocks;
&convert_users;
&convert_topics;
&convert_stories;
&convert_comments;
$geeklog_dbh->disconnect();
$appstate_dbh->disconnect();
sub convert_users(){
# first we convert the users. I make a UID map for later reference
# to assign the right objects to the right user. I do this b/c I don't want to overwrite
# geeklogs builtin accounts
#
# appstate/geeklog
# ----------------
# uid/uid
# name/fullname
# uname/username
# email/email
# pass/passwd
my $query = "SELECT uid,name,uname,email FROM users";
my $sth = $appstate_dbh->prepare($query);
$sth->execute();
my $hashref;
my $usercount = $sth->rows;
while ( $hashref =$sth->fetchrow_hashref() ){
my ($uid,$name, $uname, $email) = @$hashref{'uid','name','uname','email'};
my $textpass = md5_hex("password");
$query = "INSERT INTO gl_users (fullname,username,email,passwd) VALUES ( '$name','$uname','$email','$textpass' )";
#print $query,"\n";
my $tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
my $uid = $geeklog_dbh->{'mysql_insertid'};
$usermap{$uname} = $uid;
$tempsth->finish();
$query = "INSERT INTO gl_group_assignments (ug_main_grp_id,ug_uid) VALUES (2,$uid);";
$tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
$query = "INSERT INTO gl_group_assignments (ug_main_grp_id,ug_uid) VALUES (13,$uid);";
$tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
$query="INSERT INTO gl_userprefs (uid) VALUES ($uid)";
$tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
$query="INSERT INTO gl_userindex (uid) VALUES ($uid)";
$tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
$query="INSERT INTO gl_usercomment (uid) VALUES ($uid)";
$tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
$query="INSERT INTO gl_userinfo (uid) VALUES ($uid)";
$tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
}
$sth->finish();
}
# Next we convert the topics. I also keep a topic map so later stories are assigned the
# right topic ID number
#
#
# appstate/geeklog
# ----------------
# topicid/tid
# topictext/topic
# topicimage/imageurl
sub convert_topics{
my $query = "SELECT topicid,topictext,topicimage FROM topics";
my $sth = $appstate_dbh->prepare($query);
$sth->execute();
my $hashref = undef;
while ( $hashref =$sth->fetchrow_hashref() ){
my ($tid, $topic, $imageurl) = @$hashref{'topicid','topictext','topicimage'};
$query = "INSERT INTO gl_topics (tid,topic,imageurl) VALUES ('$topic','$topic','/images/topics/$imageurl')";
my $cp_pix = "cp $ahomedir"."images/topics/"."$imageurl $ghomedir"."images/topics";
#print "$cp_pix : $query \n";
system($cp_pix);
my $tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$topicmap[$tid] = $topic;
$tempsth->finish();
}
$sth->finish();
}
# Next we convert the stories. Use the user and topic maps from earlier to line things up.
# We keep a story map here as well so we can make comments line up
#
# appstate/geeklog
# ----------------
# sid (integer)/sid (YYYYMMDDHHMMSS)
# aid/uid+owner_id
# time (YYYY-MM-DD HH:MM:SS )/date
# hometext/introtext
# bodytext/bodytext
# comments(?)/comments
# counter/hits
# topic/tid
# informant/
# notes/
# exp_date/
# NA/postmode (html)
# NA/group_id (3)
# NA/perm_owner (3)
# NA/perm_group (3)
# NA/perm_members (2)
# NA/perm_anon (2)
sub convert_stories {
my $query = "SELECT * FROM stories ORDER BY time";
my $sth = $appstate_dbh->prepare($query);
$sth->execute();
my $hashref = undef;
while ( $hashref =$sth->fetchrow_hashref() ){
my ($sid,$aid,$title,$time,$hometext,$bodytext,$comments,$counter,$topic,$informant,$notes) = @$hashref{'sid','aid','title','time','hometext','bodytext','comments','counter','topic','informant','notes'};
# geeklog uses a different format for most of there records so there is a bunch of mangling here
my $gl_user = $usermap{$informant};
if( $gl_user eq "" || $gl_user == undef ){
$gl_user = $usermap{'Jim'};
}
if( $comments eq "" || $comments == undef ){
$comments = 0;
}
if( $counter eq "" || $counter == undef ){
$counter = 0;
}
my $gl_sid = $time;
$gl_sid =~ s/[: -]//g;
my $rand_secs = sprintf("%02d",int rand(61));
$gl_sid =~ s/(\d\d)$/$rand_secs/g;
my $gl_topic = $topicmap[$topic];
$query = "INSERT INTO gl_stories (sid,uid,tid,date,title,introtext,bodytext,owner_id,group_id,comments,hits) VALUES ('$gl_sid',$gl_user, '$gl_topic','$time',".$geeklog_dbh->quote("$title").",".$geeklog_dbh->quote("$hometext").",".$geeklog_dbh->quote("$bodytext").",$gl_user,3,$comments,$counter)";
#print $query,"\n";
my $tempsth = $geeklog_dbh->prepare($query);
$storymap[$sid] = $gl_sid;
$tempsth->execute();
$tempsth->finish();
}
$sth->finish();
}
sub convert_comments {
my $query = "SELECT tid,pid,sid,date,name,email,url,host_name,subject,comment,score,reason FROM comments;";
my $sth = $appstate_dbh->prepare($query);
$sth->execute();
my $hashref = undef;
while ( $hashref =$sth->fetchrow_hashref() ){
my ($tid,$pid,$sid,$date,$name,$email,$url,$host_name,$subject,$comment,$score,$reason) = @$hashref{'tid','pid','sid','date','name','email','url','host_name','subject','comment','score','reason'};
# geeklog uses a different format for most of there records so there is a bunch of mangling here
my $gl_user = $usermap{$name};
if( $gl_user eq "" || $gl_user == undef ){
$gl_user = $usermap{'Jim'};
}
my $gl_sid = $storymap[$sid];
my $gl_topic = $topicmap[$tid];
$comment =~ s/<br \/>//g;
$query = "INSERT INTO gl_comments (type,sid,date,title,comment,uid) VALUES ('article','$gl_sid','$date',".$geeklog_dbh->quote("$subject").",".$geeklog_dbh->quote("$comment").", $gl_user)";
# print $query,"\n";
my $tempsth = $geeklog_dbh->prepare($query);
$storymap[$sid] = $gl_sid;
$tempsth->execute();
$tempsth->finish();
}
$sth->finish();
}
# convert all the left and right blocks to geek log blocks
# id/
# title
# content
# order_id
sub convert_blocks(){
# right blocks
my $query = "SELECT id,title,content,order_id FROM rblocks";
my $sth = $appstate_dbh->prepare($query);
$sth->execute();
my $hashref = undef;
while ( $hashref =$sth->fetchrow_hashref() ){
my ($id, $title, $content, $orderid) = @$hashref{'id','title','content','order_id'};
my $blockname = $title . "_block";
$blockname =~ s/ +/_/g;
$title =~ s/\W+//g;
$query = "INSERT INTO gl_blocks (title,name,content,onleft,owner_id,group_id) VALUES ('$title','$blockname',".$geeklog_dbh->quote("$content").",0,2,4)";
# print "$query\n";
my $tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
}
$sth->finish();
# left blocks
$query = "SELECT id,title,content,order_id FROM lblocks";
$sth = $appstate_dbh->prepare($query);
$sth->execute();
$hashref = undef;
while ( $hashref =$sth->fetchrow_hashref() ){
my ($id, $title, $content, $orderid) = @$hashref{'id','title','content','order_id'};
my $blockname = $title . "_block";
$blockname =~ s/ +/_/g;
$title =~ s/\W+//g;
$query = "INSERT INTO gl_blocks (title,name,content,onleft,owner_id,group_id) VALUES ('$title',".$geeklog_dbh->quote("$blockname").",".$geeklog_dbh->quote("$content").",1,2,4)";
# print "$query\n";
my $tempsth = $geeklog_dbh->prepare($query);
$tempsth->execute();
$tempsth->finish();
}
$sth->finish();
}
1;