Simpleboard conversion - with attachments

[vavroom]

Hello,

I've found the thread that offers a conversion script for simpleboard to SMF.  Alas! That script doesn't appear to handle attachments.

We are running a rather large site that used to be phpBB, then became Simpleboard.  But we really need something more solid, and SMF seems to be it.

Our forum (http://internetbonsaiclub.org ) is meant primarly to exchange photos and have discussions of those photos.  So we can't really lose all the attachments, else the posts become pointless.  We cannot keep the current Simpleboard implementation as an archive either, as we have some load issues due to simpleboard.

Have I missed the offering of a conversion script from simpleboard to smf that handles the attachments?  If so, where might I find it?  If it's not done, how difficult might it be to adapt/create, considering I'm most certainly not a code jockey?

Thanks for any and all assistance.

Nic

[[Unknown]]

How are the attachments stored?  Are they in the database, in separate files...?

-[Unknown]

[vavroom]

If I understand correctly, the attachments are uploaded in a directory, and referred to in a table.  the SQL for the relevant table is below (I think that's the relevant ones anyway

I'm *really* not a SQL guru, not by a long shot.  Wouldn't know how to do the work properly, if at all...

Thanks for looking at it.

Code Select Expand

--
-- Table structure for table `mos_sb_attachments`
--

CREATE TABLE `mos_sb_attachments` (
  `mesid` int(11) NOT NULL default '0',
  `filelocation` text NOT NULL,
  KEY `mesid` (`mesid`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Table structure for table `mos_sb_messages`
--

CREATE TABLE `mos_sb_messages` (
  `id` int(11) NOT NULL auto_increment,
  `parent` int(11) default '0',
  `thread` int(11) default '0',
  `catid` int(11) NOT NULL default '0',
  `name` tinytext,
  `userid` int(11) NOT NULL default '0',
  `email` tinytext,
  `subject` tinytext,
  `time` int(11) NOT NULL default '0',
  `ip` varchar(15) default NULL,
  `topic_emoticon` int(11) NOT NULL default '0',
  `locked` tinyint(4) NOT NULL default '0',
  `hold` tinyint(4) NOT NULL default '0',
  `ordering` int(11) default '0',
  `hits` int(11) default '0',
  `future3` varchar(25) default NULL,
  PRIMARY KEY  (`id`),
  KEY `thread` (`thread`),
  KEY `id` (`id`),
  KEY `parent` (`parent`),
  KEY `catid` (`catid`),
  KEY `ip` (`ip`),
  KEY `userid` (`userid`)
) TYPE=MyISAM AUTO_INCREMENT=13928 ;

-- --------------------------------------------------------

--
-- Table structure for table `mos_sb_messages_text`
--

CREATE TABLE `mos_sb_messages_text` (
  `mesid` int(11) NOT NULL default '0',
  `message` text NOT NULL,
  UNIQUE KEY `mesid` (`mesid`)
) TYPE=MyISAM;
       

[vavroom]

Ok, I've looked at Kirby's conversion script, and it looks fairly straightforward.  One could almost assume that a bit of copy/pasting and changing a few things would allow to convert the attachments as well.  Hmmm.

On second look, my non-programmer's eyes find potential holes, big enough for a backhoe to fall in

The attachment's table in SMF seems to require much more info than that of SB. 

SMF:

Code Select Expand

CREATE TABLE {$db_prefix}attachments (
  ID_ATTACH int(11) unsigned NOT NULL auto_increment,
  ID_MSG int(10) unsigned NOT NULL default '0',
  ID_MEMBER int(10) unsigned NOT NULL default '0',
  filename tinytext NOT NULL default '',
  size int(10) unsigned NOT NULL default '0',
  downloads mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY (ID_ATTACH),
  UNIQUE ID_MEMBER (ID_MEMBER, ID_ATTACH),
  KEY ID_MSG (ID_MSG)
) TYPE=MyISAM;

SB:

Code Select Expand

CREATE TABLE `mos_sb_attachments` (
  `mesid` int(11) NOT NULL default '0',
  `filelocation` text NOT NULL,
  KEY `mesid` (`mesid`)
) TYPE=MyISAM;


Comparing both, it looks like it might be possible to do it relatively simply?  Before I start trying things out and killing my DB's (backup backup backup), could you confirm my thoughts are on the right path?

Using the basic structure of kirby's file, I would add another step.
In that step, I would convert SB's mesid to SMF's ID_MSG
I would then convert SB's filelocation to SMF's filename

I would need to somehow do a query to retrieve the associated member name in SB's messages tables so it could be inserted in SMF's ID_MEMBER.  This bit I don't know how to do at this point.  I don't see it

Caveat:  filelocation is much different data than filename.  in SB's table, it contains the full path to the file, and the filename (i.e. '/home/username/public_html//components/com_simpleboard/uploaded/images/Stress Symbol.gif')  So I guess on top of fancy query work to retrieve username, there needs to be some fancy footwork to strip all the path except the actual name, right?  How would one do that?  Any ideas?
Caveat: would leaving size as 0 cause problems?

Thoughts and assistance on this would be *greatly* appreciated.

[vavroom]

Ok, bear with me as I'm "thinking aloud" here...

To change the filelocation to filename, I think one could first run an update statement on that field, something along:

UPDATE mos_sb_attachments AS a SET a.filelocation = Right(a.filelocation ,Len(a.filelocation)-64);

Once that field was stripped of the path, then proceed with convert.

Is this right?  Is the syntax correct?

[[Unknown]]

Hmm, it would be as simple as mapping "msgid" to ID_MSG, and getting the basename and size of the filelocation.

Looks pretty straight forward.

-[Unknown]

[vavroom]

Hmm, it would be as simple as mapping "msgid" to ID_MSG, and getting the basename and size of the filelocation.

Hmm, I'm gonna have to hit "the books", because my SQL knowledge falls well short.   How do you get basename and size of filelocation? 

Also, don't you have to provide the ID_MEMBER somehow?

Looks pretty straight forward

LOL, I'm sure it is, when you know what you're doing <grin>

[Kirby]

hmm...im going to check this out.

[Kirby]

sorry about that (had to go somewhere), try the attached file (note it will reconvert EVERYTHING)
i really need to clean up that converter someday!

//edit: fixed a small issue!

[vavroom]

///edit: temporarily removed

You tease

Awaiting anxiously.  And *thank* you for that effort.

[Kirby]

up now

[Kirby]

oops, found an issue....let me fix it before you post about it

[vavroom]

oops, found an issue....let me fix it before you post about it

Hehehe

I don't know if you've fixed or not, but looked at the script that was posted and have a few questions before going forward

Encrypted bit...  I like the idea of encrypting filenames, this is, I believe, what allows you to have more than one copy of the same photo, right?  It was a major downfall of simpleboard.  The question is, is encrypting filenames a potential issue if that is not selected as a setting in SMF?

I think that I see that you are copying the images from one directory to the another?  That is, from where the actual photos are stored in Simpleboard to where they need to be stored in SMF.  Is this correct?  If so, great idea, but I might have to comment out that segment for my site.  I have nearly 300MB worth of photos to transfer, gonna do that by SSH .  Will commenting that section out cause the script to crash?

Thanks again

[Kirby]

oops, found an issue....let me fix it before you post about it

Hehehe

I don't know if you've fixed or not, but looked at the script that was posted and have a few questions before going forward

Fixed it

Encrypted bit...  I like the idea of encrypting filenames, this is, I believe, what allows you to have more than one copy of the same photo, right?  It was a major downfall of simpleboard.  The question is, is encrypting filenames a potential issue if that is not selected as a setting in SMF?

Not at all..encrypted filenames means more copies of things AND security with scripts like PHP files.
From Admin help box:

Encrypting attachment filenames allows you to have more than one attachment of the same name, to safely use .php files for attachments, and heightens security. It, however, could make it more difficult to rebuild your database if something drastic happened.

I think that I see that you are copying the images from one directory to the another?  That is, from where the actual photos are stored in Simpleboard to where they need to be stored in SMF.  Is this correct?  If so, great idea, but I might have to comment out that segment for my site.  I have nearly 300MB worth of photos to transfer, gonna do that by SSH .  Will commenting that section out cause the script to crash?

Thanks again

Replace:

Code Select Expand


// While this is most likely NOT the quickest way it is the easiest to debug/tell what is happening.
while ($row = mysql_fetch_assoc($result))
{
// Since Simpleboard stores the absolute path and doesnt keep a directory setting, we need to find the basename of the file.
$filename = basename($row['filelocation']);
// Frankly I don't care whether they want encrypted filenames - they having it - too dangerous.
$newfilename = $nextID . '_' . str_replace('.', '_', $filename) . md5($filename);

// In reality if this fails they have forgot to chmod the directory... tut tut.
if (!copy($row['filelocation'], $attachmentDir . '/' . $newfilename))
echo '
<div style="color: red;">Error: Could not copy ' . $filename . '</div>';
else
{
// Give the file it's original dates...not necessary but cool :P
@touch($attachmentDir . '/' . $newfilename, filemtime($row['filelocation']));

$fileSize = filesize($attachmentDir . '/' . $newfilename);
$setString .= "
($nextID, $fileSize, 0, '" . addslashes($filename) . "', $row[ID_MSG]),";

$nextID++;
}
}


with:

Code Select Expand


// While this is most likely NOT the quickest way it is the easiest to debug/tell what is happening.
while ($row = mysql_fetch_assoc($result))
{
// Since Simpleboard stores the absolute path and doesnt keep a directory setting, we need to find the basename of the file.
$filename = basename($row['filelocation']);

$fileSize = filesize($row['filelocation']);
$setString .= "
($nextID, $fileSize, 0, '" . addslashes($filename) . "', $row[ID_MSG]),";

$nextID++;
}


[vavroom]

Cool, thanks Kirby.  I'm going to do that replacement as you suggest, then try it!

One question though.  I assume the conversion script needs to be put in the root folder for the mambo install?  Or does it go in the SMF install directory?

[Kirby]

goes in SMF install directory

[vavroom]

All right. 

I'll be trying it out a a few moments (have a "real life" errand to run first), and I shall be reporting forthwith!

Crossing fingers and toes

Thanks

[vavroom]

Ok, running into a wee bit of a problem

And I think I know what the problem is, but I'm not sure how to fix it.  And it's not your script that's at play here I don't think...

I'm using the script on a test site, which is not on the same domain as the "real" site.  It was a backup/restore of the DB, so the DB is "as is" from the original site.  SB's path to file references the absolute path, including the username of the original domain.

The script is looking for that path, on the testing domain.

Code Select Expand

Warning: filesize(): Stat failed for /home/user1/public_html//components/com_simpleboard/uploaded/images/file.jpg
(errno=2 - No such file or directory) in /home/user2/public_html/ibc/smf/simpleboard_to_smf.php on line 1093

Where user1 is the name of the user on the "real" domain and user2 the name of the user for the testing domain.

Any idea on how I can relatively simply fix that?  Thanks

[[Unknown]]

Use, instead:

Code Select Expand

// While this is most likely NOT the quickest way it is the easiest to debug/tell what is happening.
while ($row = mysql_fetch_assoc($result))
{
// Since Simpleboard stores the absolute path and doesnt keep a directory setting, we need to find the basename of the file.
$filename = basename($row['filelocation']);
$fileSize = @filesize($row['filelocation']);

// This skips blank files too, but that should be okay.
if (empty($fileSize))
continue;

$setString .= "
($nextID, $fileSize, 0, '" . addslashes($filename) . "', $row[ID_MSG]),";

$nextID++;
}

-[Unknown]

[vavroom]

Thanks for that.

I must admit, I'm rather impressed that the lead developers do take the time to answer questions on the forum here.  Good sign

Alas!  I must report that this did not work.  Everything imported properly, except for the attachments.  There's nothing in the smf_attachments table.  not one single row of data, despite the message of the converter claiming success

<sigh>