Migrating a Legacy Forum to vBulletin 4.0.3
Recently I’ve just finished migrating a custom forum to vBulletin, in doing this your mileage may vary, much of it depends on if the original forum was part of a bigger offering that should still exist, or if it was standalone. In my case the site is cardlovers.com, a poker rakeback site, where the new forum is located at cardlovers.com/forum
So the other cardlovers legacy stuff is still there and needs to interact with vBulletin, the most important interaction for me is that users are to be able to login to the main site and then not have to login specifically into vBulletin, in short; I needed to hack the login functionality of vBulletin, more on that later. The new forum needs to blend into the legacy site too and therefore quite a lot of template customizing was needed, we’ll go through that too.
Let’s walk through the process from start to beginning, first I installed vBulletin in the /forum sub-folder.
Then I ran this SQL:
TRUNCATE TABLE `vbpost`;
TRUNCATE TABLE `vbforum`;
TRUNCATE TABLE `vbthread`;
TRUNCATE TABLE `vbsigpic`;
TRUNCATE TABLE `vbcustomavatar`;
TRUNCATE TABLE `vbcustomprofilepic`;
ALTER TABLE `vbuser` CHANGE `userid` `userid` BIGINT( 21 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `vbpost` CHANGE `userid` `userid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `vbthread` CHANGE `postuserid` `postuserid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `vbthread` CHANGE `lastposterid` `lastposterid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `vbforum` CHANGE `lastposterid` `lastposterid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `vbcustomavatar` CHANGE `userid` `userid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `vbcustomprofilepic` CHANGE `userid` `userid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `vbsigpic` CHANGE `userid` `userid` BIGINT( 21 ) UNSIGNED NOT NULL DEFAULT '0';The reason for truncating the vbforum table and so on is that I wanted them to be clean before I imported the old data. I was not intending to generate new autoincremeted ids for these tables so any present data would have screwed up the process. The reason for the id changes that the present user table has very high id numbers and I needed to accomodate that since the default in vBulletin is only INT(5).
Once the install is done it’s also time to enter the admin control panel and do some work. First by turning on friendly mod rewrite urls: Settings -> Options -> Friendly URLs.
Then we need to create some plugins for the legacy header and footer which are now able to render in a standalone fashion, these to plugins will be assigned to the global_start position.
The header:
ob_start();
include('/var/www/cardlovers/cardlovers/html/topframe-standalone.php');
$cltopframe = ob_get_contents();
ob_end_clean();
vB_Template::preRegister('header',array('cltopframe' => $cltopframe));And the footer:
ob_start();
include('/var/www/cardlovers/cardlovers/html/footer-standalone.php');
$clfooter = ob_get_contents();
ob_end_clean();
vB_Template::preRegister('footer',array('clfooter' => $clfooter));Next it’s time to edit some templates first we put {vb:raw cltopframe} in the header and remove all the present code there. In the footer we replace some stuff we don’t need with {vb:raw clfooter}.
We also edit Navigation / Breadcrumb Templates -> navbar to get rid of some stuff we don’t need.
We also do Usergroup Manager -> Registered User -> enable avatars and signature pics. It wouldn’t make sense to migrate all the profile pics if people weren’t allowed to see them now would it?
Finally it’s time to run the conversion script:
$sql = phive()->sql();
function fileData($img_path){
$result = array(file_get_contents($img_path), filesize($img_path));
fclose($fh);
return $result;
}
function mapit($map, $from, $to){
foreach($map as $new => $old)
$to[ $new ] = $from[ $old ];
return $to;
}
$vbpost = array(
'postid' => 'post_id',
'threadid' => 'thread_id',
'userid' => 'user_id'
);
$vbuser = array(
'userid' => 'user_id',
'username' => 'username',
'email' => 'email',
'usertitle' => 'username',
'birthday_search' => 'birthdate'
);
$vbcustomprofilepic = array(
'filename' => 'filename',
'width' => 'width',
'height' => 'height'
);
foreach($sql->loadArray("select * from users, users_extended where users.user_id = users_extended.user_id") as $u){
$last_login = strtotime($u['last_login']);
$reg_time = strtotime($u['reg_time']);
$new_user = array(
'usergroupid' => 2,
'joindate' => $reg_time,
'lastvisit' => $last_login,
'lastactivity' => $last_login,
'lastpost' => $last_login,
'options' => 33570831,
'posts' => 0,
'showbirthday' => 2,
'showvbcode' => 2,
'logintype' => 'vb',
'showblogcss' => 1,
'customtitle' => 0,
'passworddate' => '2010-07-07'
);
$images = $sql->loadArray(
"select * from image_aliases imga, image_data imgd where imga.alias = 'user_avatar.{$u['username']}' and imga.image_id = imgd.image_id"
);
if(!empty($images)){
$the_image = $images[0];
foreach($images as $img){
if($img['width'] > 50 && $img['height'] > 50){
$the_image = $img;
break;
}
}
list($blob, $imgsize) = fileData('image_uploads/'.$the_image['filename']);
$new_image = mapit($vbcustomprofilepic, $the_image, array(
'userid' => $u['user_id'],
'filedata' => $blob,
'dateline' => time(),
'filesize' => $imgsize
));
if($sql->insertArray('vbcustomavatar', $new_image)){
$new_user['avatarid'] = $sql->insertId();
$sql->insertArray('vbsigpic', $new_image);
$sql->insertArray('vbcustomprofilepic', $new_image);
}
}
$posts = $sql->loadArray("select * from forum_posts where user_id = ".$u['user_id']);
if(!empty($posts)){
$post_count = count($posts);
$new_user['lastpostid'] = $posts[$post_count - 1]['post_id'];
$new_user['posts'] = $post_count;
}else{
$new_user['lastpostid'] = 0;
$new_user['posts'] = 0;
}
foreach($posts as $p){
$clean_content = preg_replace('|\[.*?\]|sim', '', $p['content']);
$post_title = preg_split('|[\.\?\!]|', $clean_content);
$post_title = $post_title[0];
if(strlen($post_title) > 100)
$post_title = substr($post_title, 0, 97).'...';
$new_post = mapit($vbpost, $p, array(
'username' => $u['username'],
'title' => $post_title,
'dateline' => strtotime($p['time_created']),
'visible' => 1,
'allowsmilie' => 1,
'parentid' => 0,
'pagetext' => $clean_content
));
$sql->insertArray('vbpost', $new_post);
}
$new_user = mapit($vbuser, $u, $new_user);
$sql->insertArray('vbuser', $new_user);
}
$vbthread = array(
'threadid' => 'thread_id',
'title' => 'subject',
'forumid' => 'forum_id',
'postuserid' => 'user_id',
'views' => 'visits',
'sticky' => 'sticky'
);
foreach($sql->loadArray("select * from users, users_extended where users.user_id = users_extended.user_id") as $u){
foreach($sql->loadArray("select * from forum_threads where user_id = ".$u['user_id']) as $t){
$posts = $sql->loadArray("select * from vbpost where threadid = ".$t['thread_id']);
$first_post = array_shift($posts);
$last_post = array_pop($posts);
$open = $t['locked'] == 1 ? 0 : 1;
$new_thread = mapit($vbthread, $t, array(
'firstpostid' => $first_post['postid'],
'lastpostid' => $last_post['postid'],
'lastpost' => $last_post['dateline'],
'open' => $open,
'replycount' => count($posts) + 1,
'postusername' => $u['username'],
'lastposter' => $last_post['username'],
'lastposterid' => $last_post['userid'],
'visible' => 1,
'dateline' => strtotime($t['time_created'])
));
$sql->insertArray('vbthread', $new_thread);
}
}
$vbforumgroup = array(
'title' => 'name',
'title_clean' => 'name',
'description' => 'description',
'description_clean' => 'description'
);
foreach($sql->loadArray("select * from forum_groups") as $g){
$children = $sql->loadArray('select * from forum_forums where group_id = '.$g['group_id']);
$childlist = $g['group_id'].',';
foreach($children as $c)
$childlist .= $c['forum_id'].',';
$new_forum = mapit($vbforumgroup, $g, array(
'forumid' => $g['group_id'] + 1000,
'parentlist' => "{$g['group_id']},-1",
'childlist' => "$childlist-1",
'daysprune' => -1,
'parentid' => -1,
'displayorder' => 1,
'options' => 97991
));
$sql->insertArray('vbforum', $new_forum);
}
$vbforum = array(
'forumid' => 'forum_id',
'title' => 'name',
'title_clean' => 'name',
'description' => 'description',
'description_clean' => 'description'
);
foreach($sql->loadArray("select * from forum_forums") as $f){
$threads = $sql->loadArray("select * from vbthread where forumid = {$f['forum_id']} order by dateline desc");
$lt = array_pop($threads);
$posts = $sql->loadArray("select * from vbpost where threadid
in(select threadid from vbthread where forumid = {$f['forum_id']}) order by dateline desc");
$lp = array_shift($posts);
$reply_count = 0;
foreach($threads as $t)
$reply_count += $t['replycount'];
$parent_id = $f['group_id']+1000;
$new_forum = mapit($vbforum, $f, array(
'parentlist' => "{$f['forum_id']},$parent_id,-1",
'childlist' => "{$f['forum_id']},-1",
'daysprune' => -1,
'parentid' => $parent_id,
'displayorder' => 1,
'options' => 97991,
'replycount' => $reply_count,
'lastpost' => $lp['dateline'],
'lastposter' => $lp['username'],
'lastposterid' => $lp['userid'],
'lastpostid' => $lp['postid'],
'lastthread' => $lt['title'],
'lastthreadid' => $lt['threadid'],
'threadcount' => count($threads) + 1
));
$sql->insertArray('vbforum', $new_forum);
}I won’t say much about the above code, it’s mostly for my own reference if I have to migrate some other forum to vBulletin.
After having run the above it’s time to copy the contents of the do not upload folder to the admincp folder, we will use the tools.php script regenerate bitfields ONLY.
Back to the mod rewirte friendly urls, in the do not upload folder there is a .htaccess file, we upload it to the /forum folder, it currently looks like this:
RewriteEngine on
RewriteCond %{REQUEST_FILENAME} -s [OR]
RewriteCond %{REQUEST_FILENAME} -l [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^.*$ - [NC,L]
RewriteRule ^threads/.* showthread.php [QSA]
RewriteRule ^forums/.* forumdisplay.php [QSA]
RewriteRule ^members/.* member.php [QSA]
RewriteRule ^blogs/.* blog.php [QSA]
ReWriteRule ^entries/.* entry.php [QSA]
ReWriteRule ^show/.* index.php [QSA]
RewriteCond %{REQUEST_FILENAME} -s [OR]
RewriteCond %{REQUEST_FILENAME} -l [OR]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^.*$ - [NC,L]
RewriteRule ^(?:(.*?)(?:/|$))(.*|$)$ $1.php?r=$2 [QSA]That ^show/.* rule is added by me. The old forum used /show/catid/forumid/threadid to show posts, we need to redirect from these old urls to the new ones:
$req = $_SERVER['REQUEST_URI'];
if($req == '/forum/'){
include 'forum.php';
exit;
}else if(strpos($req, '/forum/show/') === 0){
require_once dirname(__FILE__) . '/../../libs/inc.php';
$reqsplit = explode('/', $req);
$tmp = array();
foreach($reqsplit as $part){
if(is_numeric($part))
$tmp[] = $part;
}
if(count($tmp) == 2){
$el = phive('SQL')->loadArray("select * from vbforum where forumid = ".$tmp[1]);
$elid = $tmp[1];
$type = 'forums';
} else if(count($tmp) == 3){
$el = phive('SQL')->loadArray("select * from vbthread where threadid = ".$tmp[2]);
$elid = $tmp[2];
$type = 'threads';
}else{
echo "Strange, that url didn't exist.";
exit;
}
$regex = '*([\s$+,/:=\?@"\'<>%{}|\\^~[\]`\r\n\t\x00-\x1f\x7f]|(?(?<!&)#|#(?![0-9]+;))|&(?!#[0-9]+;)|(?<!&#\d|&#\d{2}|&#\d{3}|&#\d{4}|&#\d{5});)*s';
$flink = preg_replace($regex, '-', strip_tags($el['title']));
$flink = trim(preg_replace('#-+#', '-', $flink), '-');
$flink = "/forum/$type/$elid-$flink";
header("HTTP/1.1 301 Moved Permanently");
header("Location: ".$flink);
header("Connection: close");
exit;
}The above has been placed at the top of vBulletin’s index.php
It’s time to start hacking the login functionality, we create a new script called autologin.php which we include just after the global.php inclusion in various vBulletin scripts like showthread.php and newthread.php.
Contents of autologin.php:
require_once dirname(__FILE__) . '/includes/functions_login.php';
session_start();
global $vbulletin;
if(!empty($_SESSION['username']) && empty($vbulletin->userinfo['userid'])){
$user = $vbulletin->db->query_first(
"SELECT userid, usergroupid, membergroupids, infractiongroupids, username, password, salt FROM " . TABLE_PREFIX . "user WHERE username = '{$_SESSION['username']}'"
);
$user['securitytoken_raw'] = sha1($user['userid'] . sha1($user['salt']) . sha1(COOKIE_SALT));
$user['securitytoken'] = TIMENOW . '-' . sha1(TIMENOW . $user['securitytoken_raw']);
$user['logouthash'] =& $user['securitytoken'];
$vbulletin->GPC['securitytoken'] = $user['securitytoken'];
$vbulletin->userinfo = $user;
exec_unstrike_user($_SESSION['username']);
process_new_login($vbulletin->GPC['logintype'], $vbulletin->GPC['cookieuser'], $vbulletin->GPC['cssprefs']);
}A final redirect block is needed in global.php to avoid the forum from being displayed in the various language sub-domains of cardlovers.com, we also only want www:
$tmp = explode('.', $_SERVER['HTTP_HOST']);
$lang = $tmp[0];
$rurl = '';
if($lang == 'cardlovers')
$rurl = 'http://www.'.$_SERVER['HTTP_HOST'].$_SERVER['REQUEST_URI'];
else if ($lang != 'www')
$rurl = 'http://www.'.$tmp[1].'.'.$tmp[2].$_SERVER['REQUEST_URI'];
if(!empty($rurl)){
header("HTTP/1.1 301 Moved Permanently");
header("Location: ".$rurl);
header("Connection: close");
exit;
}A redirect in register.php to the normal register page is also needed:
header("HTTP/1.1 301 Moved Permanently");
header("Location: /signup/");
header("Connection: close");
exit;Finally we hack the CSS logic, we put the following at the bottom of the css.php script to be able to include our own stuff at the end:
$templates .= "\r\n\r\n".file_get_contents('css/cl.css')."\r\n\r\n";
echo $templates;Now apart from rewriting the legacy registration script to also insert new users into the vbuser table and some other small things here and there we’re basically done!