Advertisement:

Author Topic: Some easy fixes to the phpBB 3 to SMF 2 converter....  (Read 13315 times)

Offline dougiefresh

  • SMF Hero
  • ******
  • Posts: 2,437
    • XPtsp.com Community
Some easy fixes to the phpBB 3 to SMF 2 converter....
« on: July 28, 2013, 10:04:58 AM »
I noticed some fairly major problems using the converter and I've managed to fix them so that my forum is dang-near perfect....  I thought I'd share them so that the rest of the community can benefit from my "experiments"...  :P

At the end of the post, I have posted an altered copy of phpbb3_to_smf2.sql.  I hope this helps people....



Converting the attachments is an issue with the converter because sometimes attachments from private messages show up as attachments to messages in the forum.  It seems that they are store in the same table, so my first modification is to the attachments area....

We need to add a personal message attachment table to the database, so before this:
Code: [Select]
/******************************************************************************/
--- Converting personal messages (step 1)...
/******************************************************************************/
Add this:
Code: [Select]
/******************************************************************************/
--- Preparing for PM conversion...
/******************************************************************************/

DROP TABLE IF EXISTS {$to_prefix}pm_attachments;

CREATE TABLE IF NOT EXISTS {$to_prefix}pm_attachments (
  `id_attach` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_thumb` int(10) unsigned NOT NULL DEFAULT '0',
  `id_pm` int(10) unsigned NOT NULL DEFAULT '0',
  `pm_report` int(10) unsigned NOT NULL DEFAULT '0',
  `id_folder` tinyint(3) NOT NULL DEFAULT '1',
  `attachment_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `filename` tinytext NOT NULL,
  `file_hash` varchar(40) NOT NULL DEFAULT '',
  `fileext` varchar(8) NOT NULL DEFAULT '',
  `size` int(10) unsigned NOT NULL DEFAULT '0',
  `downloads` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `width` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `height` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `mime_type` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id_attach`),
  KEY `id_pm` (`id_pm`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
Also note that you need the PM Attachments mod in order to view your attachments in personal messages.

To properly seperate the private message attachments and public attachments, replace this:
Code: [Select]
/******************************************************************************/
--- Converting attachments...
/******************************************************************************/

---* {$to_prefix}attachments
---{
$no_add = true;
$keys = array('ID_ATTACH', 'size', 'filename', 'ID_MSG', 'downloads', 'width', 'height');

if (!isset($oldAttachmentDir))
{
$result = convert_query("
SELECT config_value
FROM {$from_prefix}config
WHERE config_name = 'upload_path'
LIMIT 1");
list ($oldAttachmentDir) = mysql_fetch_row($result);
mysql_free_result($result);

if (empty($oldAttachmentDir) || !file_exists($_POST['path_from'] . '/' . $oldAttachmentDir))
$oldAttachmentDir = $_POST['path_from'] . '/file';
else
$oldAttachmentDir = $_POST['path_from'] . '/' . $oldAttachmentDir;
}

// Get $ID_ATTACH.
if (empty($ID_ATTACH))
{
$result = convert_query("
SELECT MAX(ID_ATTACH) + 1
FROM {$to_prefix}attachments");
list ($ID_ATTACH) = mysql_fetch_row($result);
mysql_free_result($result);

$ID_ATTACH = empty($ID_ATTACH) ? 1 : $ID_ATTACH;
}

$newfilename = getLegacyAttachmentFilename($row['filename'], $ID_ATTACH);

// Set the default empty values.
$width = '0';
$height = '0';

// Is an an image?
$attachmentExtension = strtolower(substr(strrchr($row['filename'], '.'), 1));
if (in_array($attachmentExtension, array('jpg', 'jpeg', 'gif', 'png', 'bmp')))
list ($width, $height) = getimagesize($oldAttachmentDir . '/' . $row['physical_filename']);

$newfilename = getLegacyAttachmentFilename($row['filename'], $ID_ATTACH);
if (strlen($newfilename) <= 255 && copy($oldAttachmentDir . '/' . $row['physical_filename'], $attachmentUploadDir . '/' . $newfilename))
{
$rows[] = "$ID_ATTACH, " . filesize($attachmentUploadDir . '/' . $newfilename) . ", '" . addslashes($row['filename']) . "', $row[ID_MSG], $row[downloads], '$width', '$height'";
$ID_ATTACH++;
}
---}
SELECT
post_msg_id AS ID_MSG, download_count AS downloads,
real_filename AS filename, physical_filename, filesize AS size
FROM {$from_prefix}attachments;
---*
With this:
Code: [Select]
/******************************************************************************/
--- Converting personal messages (step 3)...
/******************************************************************************/

TRUNCATE {$to_prefix}pm_attachments;

---* {$to_prefix}pm_attachments
---{
$no_add = true;

if (!isset($pmAttachmentUploadDir))
{
$result = convert_query("
SELECT value
FROM {$to_prefix}settings
WHERE variable = 'pmAttachmentUploadDir'
LIMIT 1");
list ($pmAttachmentUploadDir) = convert_fetch_row($result);
convert_free_result($result);

if (empty($pmAttachmentUploadDir))
$pmAttachmentUploadDir = $_POST['path_from'] . '/pm_attachments';
}

if (!isset($oldAttachmentDir))
{
$result = convert_query("
SELECT config_value
FROM {$from_prefix}config
WHERE config_name = 'upload_path'
LIMIT 1");
list ($oldAttachmentDir) = convert_fetch_row($result);
convert_free_result($result);

if (empty($oldAttachmentDir) || !file_exists($_POST['path_from'] . '/' . $oldAttachmentDir))
$oldAttachmentDir = $_POST['path_from'] . '/file';
else
$oldAttachmentDir = $_POST['path_from'] . '/' . $oldAttachmentDir;
}

// Get $id_attach.
if (empty($id_attach))
{
$result = convert_query("
SELECT MAX(id_attach) + 1
FROM {$to_prefix}pm_attachments");
list ($id_attach) = convert_fetch_row($result);
convert_free_result($result);

$id_attach = empty($id_attach) ? 1 : $id_attach;
}

// Set the default empty values.
$width = 0;
$height = 0;

// Is an an image?
$attachmentExtension = strtolower(substr(strrchr($row['filename'], '.'), 1));
if (!in_array($attachmentExtension, array('jpg', 'jpeg', 'gif', 'png', 'bmp')))
$attachmentExtension = '';

$file_hash = getAttachmentFilename($row['filename'], $id_attach, null, true);
$physical_filename = $id_attach . '_' . $file_hash;

if (strlen($physical_filename) > 255)
return;
if (copy($oldAttachmentDir . '/' . $row['physical_filename'], $pmAttachmentUploadDir . '/' . $physical_filename))
{
// Is an an image?
if (!empty($attachmentExtension))
{
list ($width, $height) = getimagesize($pmAttachmentUploadDir . '/' . $physical_filename);
// This shouldn't happen but apparently it might
if(empty($width))
$width = 0;
if(empty($height))
$height = 0;
}
$rows[] = array(
'id_attach' => $id_attach,
'size' => filesize($pmAttachmentUploadDir . '/' . $physical_filename),
'filename' => $row['filename'],
'file_hash' => $file_hash,
'id_pm' => $row['id_pm'],
'downloads' => $row['downloads'],
'width' => $width,
'height' => $height,
);
$id_attach++;
}
---}
SELECT
at.post_msg_id AS id_pm, at.download_count AS downloads,
at.real_filename AS filename, at.physical_filename, at.filesize AS size
FROM {$from_prefix}attachments AS at
LEFT JOIN {$from_prefix}privmsgs AS pm ON ( at.post_msg_id = pm.msg_id )
WHERE at.topic_id = 0
ORDER BY at.post_msg_id ASC;
---*

/******************************************************************************/
--- Converting attachments...
/******************************************************************************/

---* {$to_prefix}attachments
---{
$no_add = true;

if (!isset($oldAttachmentDir))
{
$result = convert_query("
SELECT config_value
FROM {$from_prefix}config
WHERE config_name = 'upload_path'
LIMIT 1");
list ($oldAttachmentDir) = convert_fetch_row($result);
convert_free_result($result);

if (empty($oldAttachmentDir) || !file_exists($_POST['path_from'] . '/' . $oldAttachmentDir))
$oldAttachmentDir = $_POST['path_from'] . '/file';
else
$oldAttachmentDir = $_POST['path_from'] . '/' . $oldAttachmentDir;
}

// Get $id_attach.
if (empty($id_attach))
{
$result = convert_query("
SELECT MAX(id_attach) + 1
FROM {$to_prefix}attachments");
list ($id_attach) = convert_fetch_row($result);
convert_free_result($result);

$id_attach = empty($id_attach) ? 1 : $id_attach;
}

// Set the default empty values.
$width = 0;
$height = 0;

// Is an an image?
$attachmentExtension = strtolower(substr(strrchr($row['filename'], '.'), 1));
if (!in_array($attachmentExtension, array('jpg', 'jpeg', 'gif', 'png', 'bmp')))
$attachmentExtension = '';

$file_hash = getAttachmentFilename($row['filename'], $id_attach, null, true);
$physical_filename = $id_attach . '_' . $file_hash;

if (strlen($physical_filename) > 255)
return;
if (copy($oldAttachmentDir . '/' . $row['physical_filename'], $attachmentUploadDir . '/' . $physical_filename))
{
// Is an an image?
if (!empty($attachmentExtension))
{
list ($width, $height) = getimagesize($attachmentUploadDir . '/' . $physical_filename);
// This shouldn't happen but apparently it might
if(empty($width))
$width = 0;
if(empty($height))
$height = 0;
}
$rows[] = array(
'id_attach' => $id_attach,
'size' => filesize($attachmentUploadDir . '/' . $physical_filename),
'filename' => $row['filename'],
'file_hash' => $file_hash,
'id_msg' => $row['id_msg'],
'downloads' => $row['downloads'],
'width' => $width,
'height' => $height,
);
$id_attach++;
}
---}
SELECT
post_msg_id AS id_msg, download_count AS downloads,
real_filename AS filename, physical_filename, filesize AS size
FROM {$from_prefix}attachments
WHERE topic_id <> 0
ORDER BY post_msg_id ASC;
---*
Note that the "Converting personal messages (step 3)" can omitted if you aren't going to install the "PM Attachments" mod...  You will lose that information if you choose to install it later and don't have the phpBB tables around anymore....



When I convert my phpBB board to SMF, all of my PM conversations from the phpBB board are listed under a single conversation in the SMF board.  I did a little more digging into how phpBB works with it's personal messages with the help of a friend and figured out what to modify in the phpbb3_to_smf.sql file (found here).

Find this:
Code: [Select]
/******************************************************************************/
--- Converting personal messages (step 1)...
/******************************************************************************/

TRUNCATE {$to_prefix}personal_messages;

---* {$to_prefix}personal_messages
SELECT
pm.msg_id AS id_pm, pm.author_id AS id_member_from, pm.message_time AS msgtime,
SUBSTRING(uf.username, 1, 255) AS from_name, SUBSTRING(pm.message_subject, 1, 255) AS subject,
SUBSTRING(REPLACE(IF(pm.bbcode_uid = '', pm.message_text, REPLACE(REPLACE(pm.message_text, CONCAT(':1:', pm.bbcode_uid), ''), CONCAT(':', pm.bbcode_uid), '')), '\n', '<br />'), 1, 65534) AS body
FROM {$from_prefix}privmsgs AS pm
LEFT JOIN {$from_prefix}users AS uf ON (uf.user_id = pm.author_id);
---*
and change this line :
Code: [Select]
SUBSTRING(REPLACE(IF(pm.bbcode_uid = '', pm.message_text, REPLACE(REPLACE(pm.message_text, CONCAT(':1:', pm.bbcode_uid), ''), CONCAT(':', pm.bbcode_uid), '')), '\n', '<br />'), 1, 65534) AS body
to this:
Code: [Select]
SUBSTRING(REPLACE(IF(pm.bbcode_uid = '', pm.message_text, REPLACE(REPLACE(pm.message_text, CONCAT(':1:', pm.bbcode_uid), ''), CONCAT(':', pm.bbcode_uid), '')), '\n', '<br />'), 1, 65534) AS body,
CASE pm.root_level WHEN 0 THEN pm.msg_id ELSE pm.root_level END AS id_pm_head
This should group all conversations together in SMF after conversion from phpBB.

(Reference message = [SMF Converter] phpBB3)



During my conversion of my phpBB forum, the converter created a lot of entries for missing avatars on my forum.  My solution was to replace some code to check to see if the avatar exists before creating the avatar entry in the attachment table....

Find this:
Code: [Select]
// If the avatar type is uploaded (type = 1) copy avatar with the correct name.
elseif ($row['user_avatar_type'] == 1 && strlen($row['avatar']) > 0)
{
$smf_avatar_filename = 'avatar_' . $row['ID_MEMBER'] . strrchr($row['avatar'], '.');
@copy($phpbb_avatar_upload_path . '/' . $row['avatar'], $avatar_dir . '/' . $smf_avatar_filename);

convert_query("
INSERT INTO {$to_prefix}attachments
(ID_MSG, ID_MEMBER, filename, attachmentType)
VALUES (0, $row[ID_MEMBER], SUBSTRING('" . addslashes($smf_avatar_filename) . "', 1, 255), " . $attachmentType . ")");
$row['avatar'] = '';
}
and replace it with this:
Code: [Select]
// If the avatar type is uploaded (type = 1) copy avatar with the correct name.
elseif ($row['user_avatar_type'] == 1 && strlen($row['avatar']) > 0)
{
$phpbb_avatar_ext = substr(strchr($row['avatar'], '.'), 1);
$smf_avatar_filename = 'avatar_' . $row['id_member'] . strrchr($row['avatar'], '.');

if (file_exists($phpbb_avatar_upload_path . '/' . $phpbb_avatar_salt . '_' . $row['id_member'] . '.' . $phpbb_avatar_ext))
{
@copy($phpbb_avatar_upload_path . '/' . $phpbb_avatar_salt . '_' . $row['id_member'] . '.' . $phpbb_avatar_ext, $avatar_dir . '/' . $smf_avatar_filename);
convert_insert('attachments', array('id_msg', 'id_member', 'filename', 'attachment_type'),
array(0, $row['id_member'], substr(addslashes($smf_avatar_filename), 0, 255), $attachment_type)
);
}
elseif (file_exists($phpbb_avatar_upload_path . '/' . $row['avatar']))
{
@copy($phpbb_avatar_upload_path . '/' . $row['avatar'], $avatar_dir . '/' . $smf_avatar_filename);
convert_insert('attachments', array('id_msg', 'id_member', 'filename', 'attachment_type'),
array(0, $row['id_member'], substr(addslashes($smf_avatar_filename), 0, 255), $attachment_type)
);
}

$row['avatar'] = '';
}
Not only did this code edit eliminate the missing avatars, it makes the database a bit smaller (at least in my case!)



After converting everything, I noticed that my board descriptions, public posts, and private messages have an unnecessary \' in them.  At the end of the phpbb3_to_smf.sql file, add this:
Code: [Select]
/******************************************************************************/
--- Correcting a few issues on the forum....
/******************************************************************************/
UPDATE {$to_prefix}boards SET description = REPLACE(description, "\\\'", "\'");
UPDATE {$to_prefix}messages SET body = REPLACE(body, "\\\'", "\'");
UPDATE {$to_prefix}personal_messages SET body = REPLACE(body, "\\\'", "\'");
That'll take care of them "nasty" backslashes...  :P



phpBB supports inline attachments and SMF doesn't.  This results in stuff that looks like this:
Quote
[attachment=0:321a13]black03.png[/attachment:321a13]
So, let's fix them so that they are at least "normal" smf bbcodes.... 

Find this under Converting posts...:
Code: [Select]
'~<img src="{SMILIES_PATH}/(.+?)/(.+?)" alt="(.+?)" title="(.+?)" />~is',and add this beneath:
Code: [Select]
'~\[attachment=(.+?):(.+?)?\]~is',
'~\[/attachment:(.+?)?\]~is',
Then find this (same section):
Code: [Select]
'$3',add this beneath:
Code: [Select]
'[attachment=$1]',
'[/attachment]',

If you use the Attachment Positioning mod, this gets you to the half way mark.  Your posts will look like this:
Quote
[attachment=0]black03.png[/attachment]
We need it to look more like this:
Quote
[attachment=1]
So find this:
Code: [Select]
), $row['body']);and add this after:
Code: [Select]
$row['body'] = preg_replace('~\[attachment=(\d+?)\]([^\[]*)\[/attachment\]~is', "[attachment=" . '\1' . "]", $row['body']);This will get rid of the unnecessary text between the [attachment] and [/attachment] tags, as well as the [/attachment] tag.

If you have installed PM Attachments mod on your forum, you will need to do this again for the "Converting Personal Messages (Step 1)" section, too....

Special thanks goes out to emanuele for the regular expression that solved this particular problem!!   Thanks, emanuele!

(Reference Post = Help regarding Inline Attachments....)
(Reference Post = Error converting phpbb3 to smf 2)



In phpBB, the size bbcode is used to specify what size the font should be in the form of a percentage.  SMF doesn't support this form and the converter tries to convert everything to pixels, which results in VERY LARGE letters!  So to fix this, we need to do a few things....

In phpbb3_to_smf.sql, find this:
Code: [Select]
'[size=$1px]',Replace this line with the following:
Code: [Select]
'[size=$1]',This changes the default "translation" of the size bbcode back to what it is in the phpBB board.....

There is a problem with doing this, as SMF doesn't recognize this usage of the size bbcode.  So, we have to add the phpBB-style Font Size BBCode mod in order to fix this minor issue.

You will need to do this again for the "Converting Personal Messages (Step 1)" section, too....

(Reference topic = Conversion from PhpBB-3.0.8 to SMF-2.0 stable - problem with fonts size in posts)



I hope this helps somebody out there.... Enjoy...

EDIT: I've added a modified version of phpbb3_to_smf.sql to this post with the changes made by me.  I hope this helps someone....
« Last Edit: April 14, 2014, 06:21:28 PM by dougiefresh »
NOTICE:  My website is back up!

My Free Simple Machines mods on XPtsp.com and Simple Machines Customization Site!
My mods are being uploaded and are available at GitHub as well!

Offline dougiefresh

  • SMF Hero
  • ******
  • Posts: 2,437
    • XPtsp.com Community
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #1 on: July 28, 2013, 10:31:45 AM »
Oh, found something else....  Sometimes, the post member name, email address, and ip address don't get filled in OR don't get filled in correctly with the information from phpBB.  Here is a fix for that...

Find this:
Code: [Select]
m.username AS posterName, m.user_email AS posterEmail, p.poster_ip AS posterIP,and replace with this:
Code: [Select]
IFNULL(p.post_username, m.username) AS poster_name,
IFNULL(m.user_email, 'Unknown') AS poster_email,
IFNULL(p.poster_ip, '0.0.0.0') AS poster_ip,
For anonymous posts, this will fill in the name used in phpbb during posting (even as guest), marking the email address as "Unknown" for guests, and the IP as "0.0.0.0" when they aren't filled in.

Hope this helps!
NOTICE:  My website is back up!

My Free Simple Machines mods on XPtsp.com and Simple Machines Customization Site!
My mods are being uploaded and are available at GitHub as well!

Offline Bigguy

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,307
  • Gender: Male
  • Be nice, or else....
    • smfbigguy on GitHub
    • SMF Helper
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #2 on: July 28, 2013, 10:41:24 AM »
That is very cool. Looks like you did a lot of work here. Thank you. This will come in real handy for lots of people.
Tell us....Whats Ur Beef

Offline dougiefresh

  • SMF Hero
  • ******
  • Posts: 2,437
    • XPtsp.com Community
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #3 on: October 23, 2013, 07:52:54 PM »
I had to associate some standard phpBB emoticons with their SMF icon counterparts in order to resolve the appearance of some posts.  Here is the SQL statement to insert at the end of the phpbb3_to_smf.sql file:
Code: [Select]
INSERT INTO `smf_smileys` (`code`, `filename`, `description`, `smiley_row`, `smiley_order`, `hidden`) VALUES
(':-D', 'cheesy.gif', 'Cheesy', 0, 0, 1),
(':grin:', 'cheesy.gif', 'Cheesy', 0, 0, 1),
(':-)', 'smiley.gif', 'Smiley', 0, 0, 1),
(':smile:', 'smiley.gif', 'Smiley', 0, 0, 1),
(';-)', 'wink.gif', 'Wink', 0, 0, 1),
(':wink:', 'wink.gif', 'Wink', 0, 0, 1),
(':-(', 'sad.gif', 'Sad', 0, 0, 1),
(':sad:', 'sad.gif', 'Sad', 0, 0, 1),
(':-o', 'shocked.gif', 'Shocked', 0, 0, 1),
(':eek:', 'shocked.gif', 'Shocked', 0, 0, 1),
(':shock:', 'shocked.gif', 'Shocked', 0, 0, 1),
(':???:', 'huh.gif', 'Huh?', 0, 0, 1),
(':?', 'huh.gif', 'Huh?', 0, 0, 1),
(':-?', 'huh.gif', 'Huh?', 0, 0, 1),
('8-)', 'cool.gif', 'Cool', 0, 0, 1),
(':cool:', 'cool.gif', 'Cool', 0, 0, 1),
(':lol:', 'laugh.gif', 'Laugh', 0, 0, 1),
(':x', 'grin.gif', 'Grin', 0, 0, 1),
(':-x', 'grin.gif', 'Grin', 0, 0, 1),
(':mad:', 'grin.gif', 'Grin', 0, 0, 1),
(':-P', 'tongue.gif', 'Tongue', 0, 0, 1),
(':razz:', 'tongue.gif', 'Tongue', 0, 0, 1),
(':oops:', 'embarrassed.gif', 'Embarrassed', 0, 0, 1),
(':cry:', 'cry.gif', 'Cry', 0, 0, 1),
(':evil:', 'evil.gif', 'Evil', 0, 0, 1),
(':roll:', 'rolleyes.gif', 'Roll Eyes', 0, 0, 1);
Hope this helps somebody....

EDIT: This does not resolve all the standard phpBB emoticons to their SMF counterparts, because some phpBB emoticons don't have SMF counterparts....
NOTICE:  My website is back up!

My Free Simple Machines mods on XPtsp.com and Simple Machines Customization Site!
My mods are being uploaded and are available at GitHub as well!

Offline Aloupha

  • Newbie
  • *
  • Posts: 4
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #4 on: October 28, 2013, 07:37:11 PM »
This is awesome!

I didn't even notice some of these issues.

May I suggest that you attach your modified version of the conversion and the SQL file?

I did come across one issue. After conversion, user permission was not converted properly. I didn't even have access to SMF's administration panel. I had to run a query in phpMyAdmin for one user to get Admin access.

Offline dougiefresh

  • SMF Hero
  • ******
  • Posts: 2,437
    • XPtsp.com Community
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #5 on: April 07, 2014, 06:40:13 PM »
I found yet another "issue" that I've managed to fix in my copy of the phpbb3_to_smf.sql.  This one revolves around the fact that not only does the converter not import the ban list from phpBB, but it also omits all of the banned usernames from the member list!  I think this is kinda important, don't you?


REMINDER: If you make the first change but not the second, the banned members will be able to access/post/etc on your forum again!!
Find this:
Code: [Select]
WHERE u.group_id NOT IN (1, 6)Relace it with this:
Code: [Select]
WHERE u.group_id <> 6 AND user_id > 1What this does is includes the banned members without including the "Anonymous" username.

Add this after importing the members:
Code: [Select]
/******************************************************************************/
--- Converting ban list...
/******************************************************************************/

TRUNCATE {$to_prefix}ban_groups;
TRUNCATE {$to_prefix}ban_items;

---* {$to_prefix}ban_groups
---{
$ignore = true;
$row['cannot_access'] = 1;
$row['cannot_login'] = 1;
convert_insert('ban_items', array('id_ban_group', 'id_member'),
array($row['id_ban_group'], $row['id_member'])
);
unset($row['id_member']);
convert_insert('ban_items', array('id_ban_group', 'email_address'),
array($row['id_ban_group'], $row['email_address'])
);
unset($row['email_address']);
if ($row['expire_time'] == 0)
  unset($row['expire_time']);
---}
SELECT
  b.ban_id AS id_ban_group, m.username AS name,
  b.ban_start AS ban_time, b.ban_end as expire_time,
  b.ban_give_reason AS reason, b.ban_reason AS notes,
  SUBSTRING(m.user_email, 1, 255) as email_address, b.ban_userid AS id_member
FROM {$from_prefix}banlist AS b
LEFT JOIN {$from_prefix}users AS m ON (m.user_id = b.ban_userid)
ORDER BY b.ban_id ASC;
---*

ALTER TABLE {$to_prefix}ban_groups ORDER BY id_ban_group;
ALTER TABLE {$to_prefix}ban_items ORDER BY id_ban;

EDIT: Corrected ban list code, as old code resulted in error due to attempting to sort on incorrect (and non-existant) field....

Also, the converter doesn't convert some parts of the messages involving smilies correctly.  Find this:
Code: [Select]
'~<img src="{SMILIES_PATH}/(.+?)/(.+?)" alt="(.+?)" title="(.+?)" />~is',and add this after it:
Code: [Select]
'~<img src="{SMILIES_PATH}/(.+?)" alt="(.+?)" title="(.+?)" />~is',and find this:
Code: [Select]
'$3',and add this:
Code: [Select]
'$2',
This needs to be replaced at least twice, depending on your sql file....

I've also uploaded a copy of the modified SQL file at the end of the first post.  Hope this helps somebody!
« Last Edit: April 09, 2014, 06:44:02 PM by dougiefresh »
NOTICE:  My website is back up!

My Free Simple Machines mods on XPtsp.com and Simple Machines Customization Site!
My mods are being uploaded and are available at GitHub as well!

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #6 on: April 07, 2014, 06:50:54 PM »
Thank you for posting your findings.
I'll give the right persons a nudge ;)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

Quote
Over 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Offline dougiefresh

  • SMF Hero
  • ******
  • Posts: 2,437
    • XPtsp.com Community
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #7 on: April 13, 2014, 07:50:54 PM »
At the end of the first post, I have posted an altered copy of phpbb3_to_smf2.sql.  I hope this helps people...
NOTICE:  My website is back up!

My Free Simple Machines mods on XPtsp.com and Simple Machines Customization Site!
My mods are being uploaded and are available at GitHub as well!

Offline dougiefresh

  • SMF Hero
  • ******
  • Posts: 2,437
    • XPtsp.com Community
Re: Some easy fixes to the phpBB 3 to SMF 2 converter....
« Reply #8 on: April 14, 2014, 05:29:54 PM »
While I was looking for a few other "issues" to take care of, I found that the topic and board subscriptions weren't transferred to the SMF install.  I can think of a few people who would get annoyed by this "feature" of the conversion, myself included  :P

This code should be added somewhere in the sql file (location not important):
Code: [Select]
/******************************************************************************/
--- Converting topic subscriptions....
/******************************************************************************/

TRUNCATE {$to_prefix}log_topics;

---* {$to_prefix}log_topics
---{
$ignore = true;
---}
SELECT
  w.topic_id AS id_topic, w.user_id AS id_member,
  IFNULL(t.topic_last_post_id, 0) AS id_msg
FROM {$from_prefix}topics_watch AS w
  LEFT JOIN {$from_prefix}topics AS t ON (t.topic_id = w.topic_id)
ORDER BY w.topic_id ASC;
---*

/******************************************************************************/
--- Converting board subscriptions....
/******************************************************************************/

TRUNCATE {$to_prefix}log_boards;

---* {$to_prefix}log_boards
---{
$ignore = true;
---}
SELECT
  w.forum_id AS id_board, w.user_id AS id_member,
  IFNULL(f.forum_last_post_id, 0) AS id_msg
FROM {$from_prefix}forums_watch AS w
  LEFT JOIN {$from_prefix}forums AS f ON (f.forum_id = w.forum_id)
ORDER BY w.forum_id ASC;
---*

Now, I noticed that there were duplicate entries in the phpBB's forum_watch table.  In my database, there were over 2,000 entries in phpBB's forum_watch table.  In SMF, there were 1,300 entries....  I don't understand why this occurred, but I'm guessing it has something to do with the corruption of my phpBB system....  which is part of the reason why I'm converting to SMF  :o


Was looking at my personal messages as they were in the phpBB forum, and those in the SMF forum.  Noticed that the PMs didn't get translated quite right, especially the smilies....  So I took the post translation code and transplated it in the Personal Message (step 1) section.

I can't get this section of code to post properly, so I am just going to insert it into the sql file and let y'all figure it out....  Hint: It's after this:
Code: [Select]
---* {$to_prefix}personal_messages

Also updated the sql file attached to the first post....
NOTICE:  My website is back up!

My Free Simple Machines mods on XPtsp.com and Simple Machines Customization Site!
My mods are being uploaded and are available at GitHub as well!