phpBB2 to SMF 1.1 - additional conversion utilites

[davo88]

[Unknown] kindly wrote the following script and queries which will help you make a more complete conversion from phpBB2 to SMF 1.1.

1  Convert Internal Links

This script converts most internal links – ie to other parts of the forum.  It will also convert the links from one domain name to another in case you happen to be changing domain names as part of the conversion process.

- Copy the code and paste it into a text file with a name like link_converter.php.
- Insert your phpBB forum details where it says - http://www.example.com/phpbb.
- Upload it to your new SMF directory.
- Run it.
- Nothing appears on the screen. It's all over in a few seconds.

To check it has converted your links, suggest you put a test post in your phpBB forum before you convert it to SMF containing samples of the links you'd like to convert.

Note: I made two versions of this script to convert two forms of internal link – those with http://www.example.com and those with just www.example.com. Just edit this line in two ways and save two versions.

$from_url = 'http://www.example.com/phpbb';
$from_url = 'www.example.com/phpbb';

If you'd like to read the post where this script was born, please see here
http://www.simplemachines.org/community/index.php?topic=42260.0

----------------------------------------------------------------------------------------
<?php

require_once('SSI.php');

$_REQUEST['start'] = (int) @$_REQUEST['start'];

$from_url = 'http://www.example.com/phpbb';

$replace = array(
   '~' . preg_quote($from_url . '/index.php', '~') . '~' => $scripturl,
   '~' . preg_quote($from_url . '/faq.php', '~') . '~' => $scripturl . '?action=help',
   '~' . preg_quote($from_url . '/search.php', '~') . '~' => $scripturl . '?action=search',
   '~' . preg_quote($from_url . '/memberlist.php', '~') . '~' => $scripturl . '?action=mlist',
   '~' . preg_quote($from_url . '/profile.php?mode=editprofile', '~') . '~' => $scripturl . '?action=profile',
   '~' . preg_quote($from_url . '/privmsg.php?folder=inbox', '~') . '~' => $scripturl . '?action=pm',
   '~' . preg_quote($from_url . '/privmsg.php?folder=outbox', '~') . '~' => $scripturl . '?action=pm;f=outbox',
   '~' . preg_quote($from_url . '/privmsg.php?folder=sentbox', '~') . '~' => $scripturl . '?action=pm;f=outbox',
   '~' . preg_quote($from_url . '/privmsg.php?folder=savebox', '~') . '~' => $scripturl . '?action=pm',
   '~' . preg_quote($from_url . '/privmsg.php?mode=post', '~') . '~' => $scripturl . '?action=pm;sa=send',
   '~' . preg_quote($from_url . '/profile.php?mode=register', '~') . '~' => $scripturl . '?action=register',
   '~' . preg_quote($from_url . '/login.php', '~') . '~' => $scripturl . '?action=login',
   '~' . preg_quote($from_url . '/viewforum.php?f=', '~') . '([\d]+)&amp;st=([\d]+)~' => $scripturl . '?board=$1.$2',
   '~' . preg_quote($from_url . '/viewforum.php?f=', '~') . '([\d]+)~' => $scripturl . '?board=$1.0',
   '~' . preg_quote($from_url . '/viewtopic.php?t=', '~') . '([\d]+)~' => $scripturl . '?topic=$1.0',
   '~' . preg_quote($from_url . '/viewtopic.php?t=', '~') . '([\d]+)&amp;st=([\d]+)~' => $scripturl . '?topic=$1.$2',
   '~' . preg_quote($from_url . '/posting.php?mode=newtopic&amp;f=', '~') . '([\d]+)~' => $scripturl . '?action=post;board=$1.0',
   '~' . preg_quote($from_url . '/posting.php?mode=reply&amp;t=', '~') . '([\d]+)~' => $scripturl . '?action=post;topic=$1.0',
);


$from = array_keys($replace);
$to = array_values($replace);

while (true)
{
   $request = db_query("
      SELECT ID_MSG, body
      FROM {$db_prefix}messages
      LIMIT $_REQUEST[start], 20", __FILE__, __LINE__);

   if (mysql_num_rows($request) == 0)
      break;

   while ($row = mysql_fetch_assoc($request))
      db_query("
         UPDATE {$db_prefix}messages
         SET body = '" . addslashes(preg_replace($from, $to, $row['body'])) . "'
         WHERE ID_MSG = $row[ID_MSG]
         LIMIT 1", __FILE__, __LINE__);
   mysql_free_result($request);

   $_REQUEST['start'] += 20;
}

?>
----------------------------------------------------------------------------------------

(Didn't use the 'code' bbcode coz the font is too small for me )

To convert other internal links eg email addresses from one domain to another, I used a tool called PowerGrep to perform a list of search and replace operations on a text dump of the database. You can set up "actions" which are a stored list of search and replace strings. So you can have practice runs before the big conversion day and then run it in a short space of time knowing you've got everything covered.

2. Mark all topics for all members as read

After doing your conversion from phpBB to SMF, ALL topics for ALL members will appear as unread - ie they'll have a 'new' flag next to them and the New Topic icons will be showing next to every board. That means all your members will need to click on the Mark ALL messages as read link in the bottom right hand corner of the index page.

But if you want to run a couple of short queries, you can mark ALL messages for ALL members as read, save everyone having to do it themselves and make your conversion just that little bit smoother. Using phpMyAdmin or similar, run thesen two queries on your new SMF database. (If you're new to phpMyAdmin, have a look here - http://www.simplemachines.org/community/index.php?topic=21919.0)

INSERT INTO smf_log_mark_read
   (ID_MEMBER, ID_BOARD, logTime)
SELECT mem.ID_MEMBER, b.ID_BOARD, UNIX_TIMESTAMP()
FROM smf_members AS mem, smf_boards AS b;

... then run this one

REPLACE INTO smf_log_boards
   (ID_MEMBER, ID_BOARD, logTime)
SELECT mem.ID_MEMBER, b.ID_BOARD, UNIX_TIMESTAMP()
FROM smf_members AS mem, smf_boards AS b;


[Unknown] may have incorporated this code in his latest version of the convert.php script. If messages appear as unread after your conversion, you'll know he hasn't.

If you'd like to read how these queries were born, it's here –
http://www.simplemachines.org/community/index.php?topic=38966.msg310283#msg310283

Davo

[James0254]

Hi all

I cant get this to work.  My error is#1054 - Unknown column 'logTime' in 'field list'

Im trying to reset the unread messages because after conversion, when a new member joins, the forum show loads of unread messages.

[ThorstenE]

James0254, I believe your SMF is Version 2.0 RC1.2? This topic and the related SQL queries are for SMF 1.1.x only..

I think there is an inconsitancy in your smf_log_topics table.. This should help you:
http://www.simplemachines.org/community/index.php?topic=319010.msg2144625#msg2144625

[James0254]

James0254, I believe your SMF is Version 2.0 RC1.2? This topic and the related SQL queries are for SMF 1.1.x only..

I think there is an inconsitancy in your smf_log_topics table.. This should help you:
http://www.simplemachines.org/community/index.php?topic=319010.msg2144625#msg2144625

Hi, Thanks for the info.  Iv just checked and its ver 1.1.9 im using.  Would also like to add that when a new user registers, even tho most of the board has the unread icon, if they goto show all unread, it comes up with no messages.  Its almost like what ever messages were in my phpbb board before the conversion are always unread for new users.

[SleePy]

LogTime doesn't exist in SMF 1.1 for that query, you can either drop it from the query or add it as a temp column to that table until you run those queries.